优化MySchool数据库设计之【巅峰对决】

优化MySchool数据库设计

之独孤九剑

船舶停靠在港湾是很安全的，但这不是造船的目的

By：北大青鸟五道口原玉明老师

1.学习方法：

01.找一本好书

初始阶段不适合，可以放到第二个阶段，看到知识点时，要进行验证

02.查看帮助文档和搜索引擎的使用

借助网络上的Blog文章

03.不要急功近利

不要抱着速成的想法，否则必然走弯路
学习编程不是一蹴而就的事情
付出的成本高，才能有一个高薪的收入

04.不要浮躁

不要什么都学，只学有用的

浮躁的程度和技术水平成反比的
走自己的路，让别人去浮躁去吧！

每天问问自己,我是不是开始浮躁了,不能人云亦云.

05.学习语言基础很重要

基础不扎实，开发出的程序就很危险
将所学的用到开发中去

06.什么时候去看别人的代码？

揣摩编写代码人的思路，远比自己写软件要难得多
初学者不要去看别人的代码

在纸上把调用关系画出来,抓主线.

07.如何提高？

有主动性。学习的欲望要强。上网找别人的问题，为自己来提高

08.培养发现问题、思考问题和解决问题的能力

出错信息，异常信息
养成发现问题、思考问题和解决问题的能力

这种能力受益一辈子，且不随时间变化

09编程的思想

多用心，多动手
程序运行的原理和机制
编程的目的，函数的调用，内存模型

10.先学脉络，再学枝叶

第一章 数据库设计

一、Chp01数据库设计

01.简述数据库完整性及其作用？

解析：数据的准确性，保证数据中数据的准确性。

02.基本操作语句(DML DDL DCL)有哪些？语法是？

DML（data manipulation language）：自动提交的数据库操作语言
它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样

DDL（data definition language）：自动提交的数据库定义语言
主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用

DCL（Data Control Language）：
是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

DQL：数据库查询语言，关键字：select

Insert、update、delete、select

这里以student表为例：

Insert into student(name,age) values(‘张三’,18)

Update student set name=’ 李四’ where id=1

Delete from student where id=2

见到update和delete，必须有where

Select * from student where id=1

03.常用的聚合函数有哪些？

Avg（）：求平均值

Sum（）：求和

Max（）;求最大值

Min（）:求最小值

Count()：求总的记录数，count（1）和count(*)等价，一般认为count(1)效率高。

04.表联接分为哪几种类型？

内连接，外连接和交叉联接（数据库原理）

一、 预习检查

1. 在数据库计时，使用什么方式表示数据库实体之间的关系？

E-R图

2. 实体集X和Y存在哪几种关系？

3. 数据库三大范式的作用是什么？

二、 本章目标

1. 了解设计数据库的步骤

2. 掌握如何绘制数据库的E——R图

3. 掌握如何绘制数据库模型图

4. 使用三大范式实现数据库设计规范化

三、 数据库

1. 为什么要设计数据库？

首先，良好的数据库设计：

01. 可以节省数据的存储空间

02. 能够保证数据的完整性

03. 方便进行数据库应用系统的开发

糟糕的数据库设计：

01. 数据冗余、存储空间浪费

02. 内存空间浪费

03. 数据更新和插入的异常

由此，我们得出一个结论，当数据库比较复杂时，我们需要设计数据库

2. 软件项目开发周期中数据库设计

从上图我们可以看出，软件开发中设计数据库的步骤：

首先，我们要从现实世界的需求构建出一个模型，这种模型通过E——R图的方式反映，如果确定E——R满足了客户的需求，那么我们需要将E-R图规范化，构建出数据库模型图，当然在这个图表中要反映出各个数据对象之间的关系，最后我们根据数据库模型图构建出符合我们需求的数据库。

需求分析阶段：分析客户的业务和数据处理需求

概要设计阶段：设计数据库的E-R模型图，确认需求信息的正确和完整

详细设计阶段：应用三大范式审核数据库结构

代码编写阶段：物理实现数据库，编码实现应用

软件测试阶段：……

安装部署：……

3. 设计数据库的步骤

01. 收集信息

与该系统有关的人员进行交流，座谈，充分了解用户需求，理解数据库需要完成的任务。

02. 标识实体(Entity)

标识数据库要管理的关键对象或者实体，实体一般都是名词

03. 标识每个实体的属性

04. 标识实体之间的关系

4.E—R图

映射基数：

主要分为以下几类：

一对一：一辆车只能对应一个车位

一对多：一个客房可以入住多个客人

多对一：多个客人可以入住一个客房

多对多：一本书可以被多个人接，一个人也可以借多本书

或者是一个老师可以教多个班级，一个班级可以被多个老师教

一个关系的属性名的集合称为关系模式

Rdbms：

relational database management system 关系型数据库管理系统

01. 仅有好的RDBMS并不足以避免数据冗余，必须在数据的设计中创建好的表结构

02. Dr  E.F.codd，一个IBM研究员。最初定义了规范化的三个级别，范式是具有最小冗余的表结构

03. 这些范式是：

第一范式(1ST NF—First  Normal   Form)

第一范式的目标是确保每列的原子性

如果没列都是不可再分的最小单元(也称为最小的原子单元)，则满足第一范式(1nf)

第二范式(2nd NF—Second  Normal  Form)

如果一个关系满足1NF，并且除了主键以外的其他列，都依赖于该主键，则满足第二范式(2NF),第二范式要求每个表只描述一件事情。

第三范式(3rd NF—Third  Normal  Form)

如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式(3NF)

今天总算理解清楚了一点，：

第二范式是其他列都要依赖于主键列，但是没有说明是直接依赖还是间接依赖。也就是直接依赖和间接依赖均可以。但是第三范式明确指出只能是直接依赖。

如果出现间接依赖的情况，要单独独立出来一张表.

5，规范化和性能的关系

为满足某种商业目标，数据库性能比规范化数据库更重要

具体策略和方法：

01. 通过在给定的表中添加额外字段，以大量减少需要从中搜索信息所需的时间

02. 通过在给定的表中插入计算列(比如成绩总分)，以方便查询

在数据规范化同时，要综合考虑数据库的性能。

05.设计数据库步骤

第一步：需求分析（收集信息）

第二步：绘制E-R图 （标示实体 ，找到实体的属性 ， 标注实体间的关系）

第三步:将E-R图转换成数据库模型图

第四步：将数据库模型图转换成数据表

06.如何绘制E-R图

矩形;实体

椭圆形：属性

菱形：关系

07.如何绘制数据库模型图

PowerDesigner ：选择PhysicalModel

如有有人不小心关掉了Platter，对工具栏点击右键，勾选Platter即可。

当我们将数据库模型图设计完毕后，可以通过菜单中的database下的

Generate Database来生成对应的sql

08.三大范式规范数据库设计

第一范式：保证每列的原子性，不可再被拆分

第二范式：在满足第一范式的基础上，一张表只能描述一件事情

第三范式：在满足第二范式的基础上，除了主键列之外其他列都要直接依赖于主键

要在规范化和性能之间取一个平衡

四、 课程总结

01. 在需求分析阶段，设计数据库的一般步骤是什么？

收集信息

标识实体

标识每个实体的属性

标识实体之间的关系

02. 在概要设计阶段和详细设计阶段，设计数据库的步骤是什么？

绘制E——R图

将E—R图转化为数据库模型图

应用三大范式规范化表设计

03. 为了设计结构良好的数据库，需要遵守一些专门的规则，称为数据库的设计范式，分别是什么？

三大范式

第二章 数据库的实现

二、Chp02 数据库的实现

1.单词

2.第一章内容回顾

3.T-SQL语句回顾

--重点内容

01.代码创建数据库

02.代码创建数据表

03.代码创建约束

04.代码删除数据库、表和约束

课程任务：

--如果有些同学是Win7系统，那么不要往C盘写入文件

--*******************************1.创建一个名称为S2220的数据库(王建琦)*

--第一步执行sp configure 启用xp_cmddshell

exec sp_configure 'show advanced options',1

go

reconfigure

go

--这个哥们儿很牛X，

--检测你的密码多久能被破解

--https://howsecureismypassword.net/

exec sp_configure 'xp_cmdshell',1

go

reconfigure

go

--01.Server版操作系统

--02.杀毒软件全盘杀毒

--第二步以下字符串代表DOS 命令

exec xp_cmdshell 'mkdir C:\aaaaa'

create database S2218 --新建 数据库 名称

on primary --通向

(

name='S2218_data', --逻辑文件名

filename='c:\aaaaa\S2218_data.mdf', --物理文件名--挂盘符，有后缀

size=5mb, --初始大小

filegrowth=15% --文件增长量

)

log on

(

name='S2218_log',

filename='c:\aaaaa\S2218_log.ldf',

size=2mb,

filegrowth=1mb

)

go

create database S2220 --新建 数据库 名称

on primary --通向

--*******************************2.创建一个学生表Student(梅新园)***************************************************************

Student(Sid,Sname,Sage,Sremark,Cid)

use S2218

--如何判定数据库中有没有某张表？？？sysobjects

if exists(select * from sysobjects where name='Student')

--删除表

drop table Student

create table Student1 --创建表的基础语句‘Student’是表的名称

(

Sid int identity(1,1) primary key NOT NULL,

Sname nvarchar(32) not null,

Sage int NOT NULL,

Sremark nvarchar(255) not null default('无备注'),

Cid int not null,

address nvarchar(255) not null

)

--*********************************3.创建一个年级表(邢妮萍) Grade(Cid,Cname)

-- 如果表存在，删除表

if exists(select* from sysobjects where name ='grade' )

drop table grade

create table Grade

(

cid int identity(1,1) primary key not null,

cName nvarchar(20) not null

)

--******************************4.代码方式给Student表中添加条数据(郭楠)*

use S2218

insert into Grade(cName) values('精英班')

insert into Grade(cName) values('牛X班')

insert into Grade(cName) values('冲刺班')

select* from grade

insert into Student(Sname,Sage,Sremark,Cid,address) values('李小龙',1,'xxxxx',1,'xxxxxx')

--****************************************5.添加主键约束（阮群杰）

给Student表添加主键约束，如果有，删除原约束再添加

给Grade表添加主键约束 ，如果有 ，删除再添加

select * from sysobjects

where type='k' and name='pk_sid'

--Grade表添加一个逐渐

alter table grade

add constraint PK_cid primary key(cid)

alter table Student

add constraint pk_Sid primary key(Sid)

alter table Student

drop constraint PK__Student__CA1E5D7808EA5793

--******************************** 6.添加外键约束(张硕)

/*常见的约束：

1：主键约束

2：唯一约束

3：检查约束

4：外键约束

5：默认约束

6：非空约束

*/

搞清楚谁是主键表谁是外键表

alter table student --student代表从表

add constraint FK_foreign

foreign key(cid) references grade(cid)

--*******************7.添加唯一约束，保证学生姓名唯一(李京涛)

use S2218

alter table student

add constraint uq_sname unique (sname)

select * from Student1

insert into Student(Sname,Sage,Sremark,address,Cid)values('小明','20','北京啊','北京',1)

--********************8.添加默认约束(备注默认值为：无备注)（王承风）

alter table Student

add constraint DF_Sremark default('无备注') for Sremark

insert into Student1(Sname,Sage,address,Cid)values('小明','20','北京',1)

--**********************9.添加检查约束学生年龄>=18岁（张辉）

alter table Student

add constraint CK_Sage Check(Sage>=18)

update Student set Sage=18

where Sage=20

select * from Student

10.删除数据库，删除表，删除约束(高宏兵)

--回闪

--删除约束

alter table Student

drop constraint PK_SID

--删除表

if exists(select * from sysobjects where name='student1')

drop table student1

--删除数据库

use master

if exists(select * from sysdatabases where name='s2218')

drop database s2218ss

第三章 SQL编程

三、SQL编程

01.全局变量

@@：

全局变量都是系统定义的，程序员不能自己手动定义全局变量

*01.@@Identity:返回上一条insert语句的主键值

*02.@@rowcount：返回上一条SQL语句受影响的行数

*03.@@Servername: 返回Server名称

*04.@@Version:获取SQL Server版本信息

*05.@@error

--全局变量:程序员不会自己定义全局变量,一般使用系统现有的全局变量.

--@@error:特别十分非常极其之强大(能捕获上一条SQL语句的错误号)

将来大家需要做一个存盘操作

可能中间涉及到的业务有至少有3个

如果select @@error，结果>0，肯定有一个环节出错了，那么我就让所有的环节统统取消，保证数据库数据的准确性。

----001 @@Error:获取最后一条SQL语句的错误号,一般用@@Error捕获约束错误，而不是SQL

--语句本身的语法错误。

--作用：当我们处理非常复杂的业务流程的时候，单条SQL语句已经无法满足要求，所以

--我们要将一堆SQL语句封装成一个存储过程。该存储过程中，可能有多个增删改操作。如

--果其中某一个操作由于违反约束，造成执行失败，那么我们就需要将存储过程中所有的N

---项操作一次性回滚，这个时候@@Error就起到了至关重要的作用。每执行一个操作就获取一下

--@@Error的值，到最后，看@@Error的值是否大于，如果大于，就证明其中某一个环节(操作)

--出现错误，要整体回滚。

02.局部变量

01.语法

Declare @age int

--赋值

Set @age=20

--在屏幕上输出

Print @age

02.通过select给变量赋值

Declare @stuNo int

Select @stuNo=studentno from student

Where studentno=23270

03.sql server 中常见数据类型的赋值

Int

Nvarchar(32)

Datetime

小数：decimal(18,2)

布尔类型:bit

03.select 和set赋值方式的区别

区别1：set不支持一次性给多个变量赋值，但select支持

区别2：如果给一个变量赋多个值，set报错，select返回最后一个

区别3：如果结果集为空值，set方式返回null，而select返回原值

04什么场景下选用select，什么场景下选用set

--如果是从某一张表中获取数据赋值给某个变量，那么只能用select

--其他的场景下两个等价

05.datediff(按年月日计算,小日期,大日期)

Datepart(按年月日计算，日期)

Dataadd()

select dateadd(yy,2,getdate())

15K/月以上

05.类型转换

为什么要进行类型转换？

Cast(要转换的变量 as 目标类型)

Convert(目标类型,要转换的变量,120)

Ceiling和floor（向下取整）

If --else

回顾：

01.局部变量

Declare @num int

Set @num=5

Print @num

02.全局变量

@@error：上一条违反约束的SQL语句的错误号

@@identity:获取上条insert语句的自增列的值

@@servername:服务器的名称

@@version:版本

@@rowcount:返回上一条sql的受影响行数

03.select 和set

01.select可以一次性对多个变量赋值， 而set不可以

02.当表达式返回多个值，select返回最后一个，set报错。

03.当表达式没有返回值，select保持原值 ，set为null

04.cast(表达式 as 数据类型)

Convert(数据类型，表达式，120)

05.if-else

If(){

} else if(){

}

--2007年

--6.2 ---6.3

/*第三章SQL笔记

回顾：.数据库的完整性包括哪些内容？

1.域完整性(列)例如：age>=50

2.实体完整性(行)

3.引用完整性(外键)

4.自定义完整性(针对业务需求而言)

实体完整性约束 就是主键约束

引用完整性约束 就是外键约束

02.如何为数据表添加约束？

解析：*/

--01.非空约束

alter table Student

add constraint Gender check(Gender is not null)

--02.主键约束(PK) primary key constraint 唯一且不为空

alter table Student

add constraint PK_stuNo primary key(stuid)

--03.唯一约束(UQ)unique constraint 唯一，允许为空，但只能出现一次

alter table Student

add constraint UQ_Address unique(Address)

--04.默认约束(DF)default constraint 默认值

alter table Student

add constraint DF_Address default('地址不详') for address

--05.检查约束(CK)check constraint 范围以及格式限制

alter table Student

add constraint CK_age Check(age>20)

--06.外键约束(FK)foreign key constraint 表关系

alter table score

add constraint FK_stuid foreign key(stuid)

references student(stuid)

--03.数据库的一般实现步骤有哪些(代码创建数据库)？

解析：

create database S2218

on primary

(

name='S2218',--逻辑文件名：任意取，可以

filename='d:\S2218.mdf',--物理文件名：硬盘上的真实文件的名称

size=50mb,--文件的初始大小

maxsize=100mb,--文件最大的大小（出现数据存取异常）

filegrowth=15%--文件的增长量，可以用百分比，也可以用大小(MB)

)

log on

(

name='S2218_log',

filename='d:\S2218_Log.ldf',

size=5mb,

maxsize=10mb,

filegrowth=1mb

)

/*预习检查

01.使用T-SQL语句如何声明一个变量？

解析：

1.declare @name int

set @name=4

02.T-SQL语言中，WHILE循环结构如果包含多条语句，需要使用什么标识？

解析：begin end

03.T-SQL语言中，Case语句有什么作用？

解析：跳转执行SQL语句

*/

精彩课程

1.F1调用帮助文件

2.变量定义

--两类：局部(@)和全局(@@)

--在SQL Server中我们也可以定义变量

--局部变量通过@标识

--

select @@identity

select @@IDENTITY

select @@SERVERNAME

select @@VERSION

insert into admin values('1','1')

select @@ERROR

--全局变量:程序员不会自己定义全局变量,一般使用系统现有的全局变量.

--@@error:特别十分非常极其之强大(能捕获上一条SQL语句的错误号)

----001 @@Error:获取最后一条SQL语句的错误号,一般用@@Error捕获约束错误，而不是SQL

--语句本身的语法错误。

--作用：当我们处理非常复杂的业务流程的时候，单条SQL语句已经无法满足要求，所以

--我们要将一堆SQL语句封装成一个存储过程。该存储过程中，可能有多个增删改操作。如

--果其中某一个操作由于违反约束，造成执行失败，那么我们就需要将存储过程中所有的N

---项操作一次性回滚，这个时候@@Error就起到了至关重要的作用。每执行一个操作就获取一下

--@@Error的值，到最后，看@@Error的值是否大于，如果大于，就证明其中某一个环节(操作)

--出现错误，要整体回滚。！

--02.@@IDENTITY:返回上一条insert语句的自增列的值

select * from grade

insert into grade values('牛X班')

select @@identity

--03.@@serveraname:获取服务器名称

select @@SERVERNAME

--04.@@version:获取版本号

select @@VERSION

use MySchool

update student set gender='阿达费随碟附送'

where studentno=23

declare @sum int

select @@ERROR as 错误号

课程之前：

--我们知道在C#中变量分成两类：局部变量和成员变量。

--局部变量：在方法中定义的变量

--成员变量：在类中定义的变量，和方法同一级别(出现在方法的花括号之外)

--01.在SQL中如何定义一个变量？？

--变量分为局部变量和全局变量：

局部变量：@作为前缀，如@age

全局变量以@@作为前缀,如@@version

全部变量由系统定义和维护，我们只能读取，不能修改

--SQL中变量也分成两类：局部变量和全局变量

--011.局部变量

--int

declare @num int

set @num=0

print @num

--字符串

declare @name nvarchar(32)

set @name='张三'

print @name

--日期

declare @mydate datetime

set @mydate=getdate()

print @mydate

--小数

declare @price decimal(18,2)

set @price=18.89

print @price

--程序的boolean对应数据库中的bit   在SQL Server中认为就是假，非就是真

declare @sex bit

set @sex=-1000

print @sex

insert into admin

values('1112','1112',20.59,1000)

use MySchool

select * from Student

--查询和微冷的雨相邻的学生信息

--定义一个变量，保存微冷的雨的学号

declare @stuno int

select @stuno=StudentNo from student

where studentno=23311

print @stuno

--李小龙

select * from student

where studentno=@stuno-41

--张靓颖

select * from student

where studentno=@stuno+5

--局部变量练习一

--set与select区别

--01.set不支持给多个变量赋值的，但是select支持

declare @num1 int,@num2 int

select @num1=studentno,@num2=gradeid from student

where studentno=23270

--02.表达式返回多个值的时候，set会出错，但是select会将最后一个值赋值给变量

declare @result nvarchar(20)

select @result=studentname from student

print @result

select * from student

declare @myresult nvarchar(20)

set @myresult=(select studentname from student)

print @myresult

--03.当表达式没有返回值时，set变量会被赋值为null

declare @myresult nvarchar(20)

set @myresult='李四'

set @myresult=(select studentname from student where 0>100)

if(@myresult is null)

begin

print '是空'

end

USE ajax

select * from admin

where uaddress=null

null--不代表空，代表不知道。

use myschool

declare @myresult nvarchar(20)

set @myresult='张三'

select @myresult=studentname from student where 0>100

select @myresult

--03.什么场景下选用select，什么场景下选用set

--如果是从某一张表中获取数据赋值给某个变量，那么只能用select

--其他的场景下两个等价

declare @tag nvarchar(2)

set @tag='★'

print @tag

print @tag+@tag

print @tag+@tag+@tag

print @tag+@tag+@tag+@tag

print @tag+@tag+@tag+@tag+@tag

--go的位置:结论:如果语句是select,那么go关键字可以和select语句位于同一行,其他语句要另起一行.

--go的含义是将go之前所有的语句看成是一个可执行单独,用白话来说,就是将一堆sql语句,一次性的交给SQL Server引擎处理.

select * from student

select * from grade

go

create table test

(

id int primary key not null,

name nvarchar(20)

)

go

select dateadd(yy,2,getdate())

--*************************1.局部变量案例*********************************************

--**************************练习********************************************

--练习：查询学号是(李小龙)的学生姓名和年龄，并输出比他小岁的学员信息(姓名和年龄)

--01.先找出李小龙的年龄@age

declare @age int

select @age=datediff(yy,birthday,getdate()) from student

where studentno=23270

--02.where 年龄=@age-1

select studentname,datediff(yy,birthday,getdate())

from student

where datediff(yy,birthday,getdate())=@age-1

--**************************3.SQL Server数据类型转换**********************************

--在SQL Server中数据类型转换有两种方式

--方式一：Cast(要转换的变量as 目标类型)

declare @num int

set @num=20

print '我的年龄是'+cast(@num as nvarchar(20))+'岁'

print '我的年龄是'+convert(nvarchar(20),@num)+'岁'

--日期类型转字符串

declare @date datetime

set @date=getdate()

print '今天是'+convert(nvarchar(20),@date,120)

--方式二：Convert(目标类型,要转换的变量,style)

--**************************4.floor和ceiling函数********************************

--floor:

select floor(1.92) --向下取整

--ceiling

select ceiling(1.01) --向上取整

--**************************5.逻辑控制语句********************************************

--********************if else    while     case end************************************************

--现在定义一个变量，判断该变量的值是否大于

declare @num int

set @num=6

if(@num>=50)

print '请根据您的身体状况选择性观看'

else

begin

print '年龄小于，不能观看'

print '你能看到我吗'

end

05.if--else的使用

--练习：

--统计并显示-08-09 的oop考试平均分

--如果平均分在以上，显示“考试成绩优秀”，并显示前三名学生的考试信息

--如果在以下，显示“考试成绩较差”，并显示后三名学生的考试信息

--定义一个变量保存oop对应的课程编号

declare @subid int

select @subid=SubjectId from Subject

where subjectname='oop'

--定义一个变量，保存平均分

declare @avg int

--如何求2013-08-09   oop  平均分

select @avg=AVG(StudentResult) from result

where examdate>='2013-08-09' and examdate<'2013-08-10'

and subjectid=@subid

--判定@avg和的关系

if(@avg>=70)

begin

--01.打印成绩优秀

print '成绩优秀'

--02.显示前名学生成绩信息

select top 3 studentno,studentresult from result

where examdate>='2013-08-09' and examdate<'2013-08-10'

and subjectid=@subid

order by studentresult desc

end

else

begin

print '成绩好烂！罚吃番薯'

end

declare @num int

set @num=5

if(@num=1)

begin

print '1'

end

else if(@num=2)

begin

print '2'

end

else

begin

print 'aa'

end

06.while 循环

--在SQL 中，只有while一种循环，没有do-while和for循环

--并且没有while(true)的写法，可以使用while(1=1)替代。

--注意：SQL中比较是否相等，用单等号（=）

--求出到之间所有偶数的和

while(9=9)

begin

print '11'

end

--int count = 0;

--          for (int i = 0; i <= 100; i++)

--          {

--              if (i%2==0)

--              {

--                  count += i;

--              }

--          }

--          Console.WriteLine(count);

--          Console.ReadLine();

--注意点:每一次更改变量值的时候,都要加上set关键字

--在SQL Server中不支持++,支持+=

--在SQL Server中，对比变量使用=

01.1到100之间所有偶数的和

--01.定义一个变量，保存总和

declare @sum int

set @sum=0

--02.定义一个初始变量

declare @num int

set @num=1

--03.进行while循环

while(@num<=100)

begin

--判定

if(@num%2=0) --代码执行到这里，证明@num是偶数

begin

set @sum+=@num

end

set @num+=1

end

--出了循环，打印总和sum

print @sum

02.打印直角三角形

*

**

***

****

*****

--用循环的方式打印出直角三角形（不是重点，是难点）

declare @i int

declare @j int

declare @str nvarchar(100)

set @i=1

set @j=1

set @str=''

while(@i<=5)

begin

while(@j<=@i)

begin

set @str+='*'

set @j+=1

end

print @str

set @i+=1

end

declare @str2 nvarchar(200)

declare @count int

declare @result nvarchar(200)

set @str2='*'

set @count=0

set @result=''

while(@count<5)

begin

set @result+=@str2

print @result

set @count+=1

end

print '你好'+char(9)+'吃饭'

07.设置输出结果的格式

--****************************6.设置输出结果的格式***************************************

--如果我们想让【结果】和【消息】窗口在同一个窗口显示，那么我们可以

--点击工具→选项→查询结果→SQL Server界面，将选项改成以文本形式显示结果

--*****************************************************************************

use myschool

select * from student

--内容回顾

/*

1.局部变量

declare @num int

set @num=5

print @num

2.全局变量：一般不会自己定义全局变量

@@error:捕获上一条SQL宇语句的错误号。如果返回证明上一条语句没有问题

3.while:

while(1=1)

begin

end

if --else

4.类型转换

convert(目标数据类型,要转换的变量,120)  cast(变量as 目标数据类型)

*/

select * from student

--****************** 7.经典while循环加分题目***************************************

--检查学生“oop”课最近一次考试是否有不及格（分及格）的学生。

--如有，每人加分，高于分的学生不再加分，直至所有学生这次考试成绩均及格

--***********************************************************************

--01.看一下oop课程最近一次考试分以下人数

--准备一个变量，保存不及格人数，科目编号，最近考试时间

--01.科目编号

select * from result

declare @subid int

select @subid=subjectid from subject

where subjectname='oop'

--02.最近一次考试时间

declare @mydate datetime

select @mydate=max(examdate) from result

where subjectid=@subid

--03.不及格人数

declare @num int

select @num=count(1) from result

where subjectid=@subid

and examdate=@mydate

and studentresult<70

--04.

while(@num>0) --有成绩低于分的学员，

begin

--每个人+2分，但是分以上不加分

update result set studentresult+=2

where studentresult<95

and subjectid=@subid

and examdate=@mydate

select @num=count(1) from result

where subjectid=@subid

and examdate=@mydate

and studentresult<70

print @num

end

select * from result

order by subjectid,examdate

08.Case End 经典练习

--Case End在面试的时候，会换一种方式考察，就是行转列。

--*************************8.Case End 经典练习******************************************

--采用美国ABCDE五级打分制显示学生oop课最近一次考试成绩（姓名等级）

--A级:   90分以上

--B级：80－分

--C级:   70－分

--D级：60－分

--E级：60分以下

--*****************************************************************************

declare @subid int

declare @maxdate datetime

select @subid=subjectid

from subject

where subjectname='oop'

select @maxdate=max(examdate)

from result

where subjectid=@subid

select studentname,等级=

case

when studentresult>=90 then 'A'

when studentresult>=80 then 'B'

when studentresult>=70 then 'C'

when studentresult>=60 then 'D'

else 'E'

end

from student,result

where student.StudentNo=result.StudentNo

and subjectid=@subid

and examdate=@maxdate

select * from result

--方式二

declare @subid int

declare @maxdate datetime

select @subid=subjectid

from subject

where subjectname='oop'

select @maxdate=max(examdate)

from result

where subjectid=@subid

select studentname,等级=

case

StudentResult

when 90 then 'A'

when 80 then 'B'

when 70 then 'C'

when 60 then 'D'

else 'E'

end

from student,result

where student.StudentNo=result.StudentNo

and subjectid=@subid

and examdate=@maxdate

--方式二:when后面不是区间，而是定值的时候

--可以使用该方式

declare @subid int

declare @date datetime

select @subid=subjectid from subject

where subjectname='oop'

select @date=max(examdate)

from result

where subjectid=@subid

select studentname,等级=

case studentresult

when 90 then 'A'

when 80 then 'B'

when 70 then 'C'

when 60 then 'D'

else 'E'

end

from student,result

where student.studentno=result.studentno

and subjectid=@subid

and examdate=@date

select * from result

--Go

select * from student

select * from subject

go

--go必须独占一行

create table myttttt3

(

id int identity(1,1) primary key not null,

name nvarchar(20)

)

go

select * from sysobjects where name='mm'

/*1.一道SQL语句面试题，关于group by

表内容：

2005-05-09 胜

2005-05-09 胜

2005-05-09 负

2005-05-09 负

2005-05-10 胜

2005-05-10 负

2005-05-10 负

如果要生成下列结果, 该如何写sql语句?

胜负

2005-05-09 2 2

2005-05-10 1 2

------------------------------------------

*/

create table tmp(rq varchar(10),shengfu nchar(1))

select * from tmp

insert into tmp values('2005-05-09','胜')

insert into tmp values('2005-05-09','胜')

insert into tmp values('2005-05-09','负')

insert into tmp values('2005-05-09','负')

insert into tmp values('2005-05-10','胜')

insert into tmp values('2005-05-10','负')

insert into tmp values('2005-05-10','负')

select * from tmp

select rq as 日期,sum

(case

when shengfu='胜' then 1

else 0

end) as 胜,sum

(

case

when shengfu='负' then 1

else 0

end

) as 负

from tmp

group by rq

第四章   高级查询

四、高级查询

本章依次讲解四个问题：

01.简单子查询

02.in/not in 子查询

03.Exists子查询

04.T-SQL语句的综合应用

01.简单子查询

案例1：查询学生表中比"美洋洋"小的学员信息(姓名，地址)

--不用变量

--所想即所得

select studentname,address

from Student

where Birthday>(select Birthday from Student where StudentName='美洋洋')

案例2：查询“oop”课程至少一次考试刚好等于分的学生(姓名，成绩)

select Studentname,studentresult

from Student,result,Subject

where Student.StudentNo=Result.StudentNo

and Result.SubjectId=Subject.SubjectId

and SubjectName='oop'

and StudentResult=60

--用子查询如何实现？？？

--用到什么条件，交给子查询解决

select studentname,studentresult

from Student,Result

where Student.StudentNo=Result.StudentNo

and SubjectId=(select SubjectId from Subject where SubjectName='oop')

and StudentResult=60

--只检索学生姓名

select studentname

from Student

where StudentNo in

(select StudentNo

from Result

where StudentResult=60 and SubjectId=

(select SubjectId

from Subject

where SubjectName='oop'

)

)

--悲伤，痛苦，折磨，郁闷，坚韧，忍辱负重。凤凰涅槃，破茧成蝶

--01.简单子查询(嵌套子查询)的执行机制：

--将子查询的结果作为外层父查询的一个条件。

--也就意味着先执行子查询，再执行父查询

--子查询:子查询语句必须用小括号括起来，然后通过

--比较运算符：>、<,=等连接起来

--注意点：.子查询必须用小阔号括起来

--2.子查询先执行出一个结果，然后将该结果作为父查询

--的一个条件而存在

select * from Student

where Birthday>

(

select Birthday from Student

where StudentName='张靓颖'

)

--01.查询参加最近一次“OOP”考试成绩最高分和最低分

--人家要什么数据你就在select后写什么

--02.查询“oop”课程至少一次考试刚好等于分的学生姓名

--分析思路：查询结果是是什么，就写到Select后，

--然后根据需要的条件一步一步往下写，用到某个变量，需要

--跨表访问，让子查询帮我们完成。

--子查询要想用的熟练，必须对表之间的公共字段(外键)特别清晰。

--在脑海中能随意存取各表之间的对应关系。

--子查询引用场景要比表连接广

02.in/not in 子查询

案例1：.查询参加“oop”课程最近一次考试的在读学生名单(学生姓名，学生编号)

--既然查询在读学生名单，必须是成绩表中出现的学号

select studentname,studentno

from student

where studentno in

(

select studentno from result

where subjectid in

(

select subjectid from subject

where subjectname='oop'

)

and examdate=

(

select max(examdate) from result

where subjectid in

(

select subjectid from subject

where subjectname='oop'

)

)

)

案例2：.查询S1开设的课程

select * from subject

select * from grade

select subjectname from subject

where gradeid in

(

select gradeid from grade where gradename='S1'

)

select * from result

order by subjectid,examdate

select * from student

案例3：查询最近一次未参加oop考试的学生名单（studentname）

select studentname

from student

where studentno not in

(

select studentno from result

where subjectid in

(

select subjectid from subject where subjectname='oop'

)

and examdate=

(

select max(examdate) from result where subjectid in

(

select subjectid from subject where subjectname='oop'

)

)

)

and gradeid= --过滤掉本身就不用参加该门课程考试的学生

(

select gradeid from grade

where gradename='S1'

)

03.Exists和Not  Exists子查询

use master

if exists (select * from sysdatabases where name='的人工费为挖坟热瓦尔五二五二五')

begin

print '你存在！@'

end

else

print '不存在'

--判定符合条件的数据是否存在，如果存在，执行分支A，如果不存在，执行

--分支B

案例1：检查“oop”课程最近一次考试。

--如果有80分以上的成绩，则每人提分；

--否则，每人提分。最终的成绩不得大于分

--01.保证课程是oop

--02.最近一次

--检查是否有分以上的成绩

use myschool

select * from result

order by subjectid ,examdate

if exists

(

select * from result where subjectid=

(

select subjectid from subject

where subjectname='oop'

)

and examdate=

(

select max(examdate) from result

where subjectid=

(

select subjectid from subject

where subjectname='oop'

)

)

and studentresult>=80

)--如果有结果，有分以上的学员，每人加分

begin

--存在，加分

--99

--分情况，如果分数<=98加分，否则直接update成分   97

--将大于的分数置成

update result set studentresult=100

where studentresult>98

and subjectid=

(

select subjectid from subject

where subjectname='oop'

)

and examdate=

(

select max(examdate) from result

where subjectid=

(

select subjectid from subject

where subjectname='oop'

)

)

update result set studentresult+=2

where studentresult<=98

and subjectid=

(

select subjectid from subject

where subjectname='oop'

)

and examdate=

(

select max(examdate) from result

where subjectid=

(

select subjectid from subject

where subjectname='oop'

)

)

end

else --每人加分

begin

--每人加分

update result set studentresult+=5

where studentresult<=95

and subjectid=

(

select subjectid from subject

where subjectname='oop'

)

and examdate=

(

select max(examdate) from result

where subjectid=

(

select subjectid from subject

where subjectname='oop'

)

)

--将大于的分数置成

update result set studentresult=100

where studentresult>95

and subjectid=

(

select subjectid from subject

where subjectname='oop'

)

and examdate=

(

select max(examdate) from result

where subjectid=

(

select subjectid from subject

where subjectname='oop'

)

)

end

--使用union关键字进行表的联合查询

select studentno,studentname from student

union

select gradeid,gradename from grade

--使用distinct关键字进行去重操作

--product  (id,proname,category)

select distinct(gradename) from grade

--重要：if exists(子查询) 子查询返回的必须是一个结果集，而不是一个bool值。

--结果集(用一个表结构将数据呈现出来，如果没有结果，返回的是一个空表)

--子查询的列可以跟单个列名，也可以跟星号，但是不能跟聚合函数，因为聚合函数

--返回的值永远是真，因为聚合函数也是结果集的一种，不能作为Exists判定的依据。

--06：为每个学生制作在校期间每门课程的成绩单，

--要求每个学生参加每门课程的最后一次考试成绩作为该生本课程的最终成绩

--成绩单中包括：

--学生姓名

--课程所属的年级名称

--课程名称

--考试日期

--考试成绩

--01.最终我们是要获取成绩单:包含信息（学生姓名,课程年级名称,课程名称,考试日期,考试成绩)

--02.【每个学员】的【每门课程】     【最后一次】三者都是限定

--相关子查询

SELECT StudentName 姓名,

GradeName 课程所属年级,

SubjectName 课程名称, ExamDate 考试日期, StudentResult 成绩

FROM Result

INNER JOIN Student ON Result.StudentNo=Student.StudentNo

INNER JOIN Subject ON Subject.Subjectid=Result.Subjectid

inner join grade on subject.gradeid=grade.gradeid

WHERE Result.ExamDate = (

SELECT Max(ExamDate) FROM Result

WHERE Subjectid=Subject.Subjectid AND

StudentNo=Student.StudentNo

)

ORDER BY Result.StudentNo ASC,Result.Subjectid ASC

--只要知道子查询不一定先执行.还有相关子查询

SELECT StudentName 姓名,

( SELECT GradeName FROM Grade

WHERE GradeId=Subject.GradeId ) 课程所属年级,

SubjectName 课程名称, ExamDate 考试日期, StudentResult 成绩

FROM Result

INNER JOIN Student ON Result.StudentNo=Student.StudentNo

INNER JOIN Subject ON Subject.Subjectid=Result.Subjectid

WHERE Result.ExamDate = (

SELECT Max(ExamDate) FROM Result

WHERE Subjectid=Subject.Subjectid AND

StudentNo=Student.StudentNo

)

ORDER BY Result.StudentNo ASC,Result.Subjectid ASC

--查询大于平均成绩的学号,分数

select studentno,studentresult,subjectid

from result a

where studentresult>

(

select avg(studentresult)

from result b

where b.subjectid=a.subjectid

)

order by studentno,subjectid

select * from result

04.分页

--中国电信通话记录有亿条数据

--服务器压力太大

--客户端等待时间过长

use zongjie

select * from info

分页目的:为了加快网站对数据的查询（检索）速度,我们引入了分页的概念.

--在SQL Server分页一般两种思路

--你会怎么搞呢？

use myschool

--每页条数据，我想要第二页的数据。

select * from student

方式一：跳过几条取几条（双top 双order by 方式）

select top 3 * from student

where studentno not in

(

select top 3 studentno from student

order by studentno

)

order by studentno

方式二：局限性（SQL Server2005之后的版本支持该写法，因为我们要用到row_number() over()函数，在之前是没有该函数）

select * from

(select *,row_number() over(order by studentno) as myid from student) as temp

where myid between 4 and 6

--第五章

--下次课程听写，如果有一个都没有写出来的，唱一首歌

--学生表中第到第条数据：每页条数据，取第二页的数据

--跳过条取条数据

--注意点：子查询的排序方式，必须和父查询排序的方式一致

--SQL Server数据库下哪个版本都可以使用

select top 3 * from student

where studentno not in

(

select top 3 studentno from student order by studentno

)

order by studentno

--方式二：在SQL Server2005 后， 2012

--row_number() over()函数方式：实现分页

--原理：在原表的基础上加多了一列ID，ID列从开始给值，我们就可以使用

--Between and 给值。

select * from

(select *,row_number() over(order by studentno) as myid from student) as temp

where myid between 4 and 6

--下次课听写两个分页的方式，如果有一个没写对，那么唱歌一首！

第六章 事务、视图和索引

六、事务、视图和索引

01.事务

★什么是事务？

解析：事务是一个不可分割的整体，事务中的多个执行过程，同生共死。要么都执行成功，要么都执行失败。

Webcast:一级讲师

★事务的四个特性？

解析：ACID原则

原子性(Atomicity )

一致性( Consistency )

隔离性( Isolation) ：两个事务之间

永久性(Durabilily)

★参与事务的几个关键字

Begin  transaction

Comit  transaction

Rollback tran

★通过银行转账的案例说明在SQL中如何开启一个事务

@@Error

★:事务分类

显式事务;自己写的事务都是显式事务

隐式事务

自动提交事务

02.视图

视图本质：是一张虚拟表，真正保存的是一堆SQL语句

能不能对视图进行增删改操作。（答题的话可以，面试不可以）

Drop view VW_Test

视图中能包含增删改语句吗？

解析：不可以

视图出现的价值：

开发中，很多人对SQL的掌握很差，不可能写出多表联查语句，这个时候可以让DBA封装一个复杂的多表联查语句，提供给对SQL不熟悉的开发人员使用。封装起来的这个内容就是视图。

★：如何创建视图

Create view VW_StuInfo

As

Select * from student

Select I* from VW_StuInfo

03.索引

必须上过大学

计算机专业

数据结构：二叉树

★：索引：是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据

文章 3万个字

我爱你

Select  * from Aritcle

Where content like ‘%我爱你%’

★：填充因子

★：索引会降低增删改效率

-多看SQL优化的书

第七章 存储过程

七、存储过程

--互联网是非常危险的（专门攻击他人电脑，肉鸡，）。

--小孩儿们请不要随意接触互联网.

01.存储过程定义：

在数据库服务器上存储的预先编译好的一堆SQL语句

02.存储过程的优点

--**************************1.存储过程的优点********************************

/*

1.执行速度更快(预编译：可以看成是编译成和中间代码)

--存储过程会在SQL Server服务器上进行预编译

2.允许模块化程序设计

3.提高系统安全性

4.减少网络流通量

*/

03.存储过程的分类

--*************************2.存储过程的分类**************************************

/*1.系统存储过程

系统存储过程的名称一般以“sp_”开头

由SQLServer创建、管理和使用

存放在Resource数据库中

类似C#语言类库中的方法

2.扩展存储过程(Extends)

扩展存储过程的名称通常以“xp_”开头

使用编辑语言（如C#)创建的外部存储过程

以DLL形式单独存在

set nocount off

sp:system proc

xp_extends proc

3.用户自定义存储过程

由用户在自己的数据库中创建的存储过程

类似C#语言中用户自定义的方法

*/

04.常用系统存储过程

--**********************3.常用系统存储过程****************************

系统存储过程 说 明

sp_databases 列出服务器上的所有数据库

exec sp_databases

sp_helpdb 报告有关指定数据库或所有数据库的信息

sp_renamedb 更改数据库的名称

sp_tables 返回当前环境下可查询的对象的列表

sp_columns 返回某个表列的信息

sp_help 查看某个表的所有信息

sp_helpconstraint 查看某个表的约束

sp_helpindex 查看某个表的索引

sp_stored_procedures 列出当前环境中的所有存储过程

sp_password 添加或修改登录帐户的密码

sp_helptext 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本

--常见系统系统存储过程举例

--例：对数据库进行重命名的存储过程

exec sp_renamedb 'MySchool’','YourSchool'

use myschool

--例：获取当前环境中所有系统存储过程

exec sp_stored_procedures

--获取数据库中表的相关信息

exec sp_tables @table_name ='%dent%',@fUsePattern = 1

--sp_tables [ [ @table_name = ] 'name' ]

--     [ , [ @table_owner = ] 'owner' ]

--   [ , [ @table_qualifier = ] 'qualifier' ]

-- [ , [ @table_type = ] "type" ]

-- [ , [@fUsePattern = ] 'fUsePattern'];

05.通过execute执行存储过程

06.用户自定义存储过程

--自定义存储过程(重点)

--语法重点剖析：

--Create Procedure usp_info

--as

--select

/*

这里需要注意的是：

01.参数置于as前，并且变量前不需要加declare关键字

02.as后的变量需要declare关键字

*/

07.无参数的存储过程

--*********************4.无参数的存储过程***************************************

--我想创建一个可以查询所有学生信息的存储过程

CREATE PROCEDURE

AS

select * from Student

08.带输入参数的存储过程

--*********************5.带输入参数的存储过程***************************************

--2.带输入参数的存储过程

--从Result,Student表中查询成绩大于分的学生的  姓名和成绩

--从外界传入一个及格的标准（比如分，根据这个标准来判断

--学生是否及格）

--如果输入参数有多个，并且某个参数有默认值，那么在调用存储过程的时候，有默认值的参数，可以使用default关键字代替。

--也可以不对有默认值的参数重新赋值，只需要在调用存储过程的时候指定参数名='值'

alter procedure usp_GetStuResult

@PassScore int=90,

@name nvarchar(20)

--as之前给参数

as

if(@PassScore>=0 and @PassScore<=100)

begin

select studentname,studentresult

from student,result

where student.studentno=result.studentno

and

studentresult>@PassScore

end

else

begin

raiserror('及格线输入有误',16,1)

end

--开始测试存储过程书写是否存在问题

exec usp_GetStuResult @name='张三'

--001.嵌套层问题

--raiserror用法：

--raiserror('错误消息',16,1)

--第二个参数范围到，第三个参数范围到(查看课本)

09.带out参数的存储过程

/***************************带out参数的存储过程**************************/

--分页存储过程

--每页条数据

--查询第二页的数据 4-6

alter procedure usp_GetPageList

@PageIndex int,--当前第几页

@PageSize int,--每页显示的记录数

@PageCount int output--总页数

as

select * from

(

select *,row_number() over( order by studentno )as myid

from student

) as temp

where myid between (@PageIndex-1)*@PageSize+1 and @PageIndex*@PageSize

--求出总的记录数

declare @RecordCount int

select @RecordCount=count(1) from student

--求出总的页数

set @PageCount=ceiling(@RecordCount*1.0/@PageSize)

declare @PageCount int

set @PageCount=0

exec usp_GetPageList 1,5,@PageCount output

print @PageCount

select * from student

select ceiling(6/3)

pageIndex pageSize :3

1          1

4          2

7          3

10.distinct和Compute...  by ....补充

select distinct(studentno),studentresult from result

SELECT studentno, studentresult

FROM result

ORDER BY studentno, subjectid

COMPUTE SUM(studentresult) BY studentno

--一、xp_cmdshell的删除及恢复

--1、判断xp_cmdshell是否存在

--and 1=(SELECT count(*) FROM master.dbo.sysobjects WHERE xtype = ’X’AND name = ’xp_cmdshell’)

--select count(*) from master.dbo.sysobjects where xtype=’x’and

--返回结果为就ok

--2、恢复xp_cmdshell的方法

--删除扩展存储过过程xp_cmdshell的语句

--exec sp_dropextendedproc ’xp_cmdshell’

--恢复cmdshell的sql语句

--exec sp_addextendedproc xp_cmdshell ,@dllname =’xplog70.dll’

--exec master.dbo.addextendedproc ’xp_cmdshell’,’xplog70.dll’;select count(*) from master.dbo.sysobjects where xtype=’x’and

--返回结果为就ok

--否则需上传c:\inetput\web\xplog70.dll后

--exec master.dbo.sp_addextendedproc ’xp_cmdshell’,’c:\inetput\web\xplog70.dll’;

--如果是用以下方法删除

--drop procedure sp_addextendedproc

--drop procedure sp_oacreate

--exec sp_dropextendedproc ’xp_cmdshell’

--则可以用以下语句恢复

--dbcc addextendedproc ("sp_oacreate","odsole70.dll")

--dbcc addextendedproc ("xp_cmdshell","xplog70.dll")

--这样可以直接恢复，不用去管sp_addextendedproc是不是存在