SQLServer从入门到精通（二）

CH01:使用T-SQL建库建表加约束

一.创建数据库

USE master

GO

--创建数据库之前先检查该数据库是否存在 若存在则删除

IF EXISTS(select * from sysdatabases where name='MySchool')

DROP DATABASE MySchool

--创建数据库

CREATE DATABASE MySchool

--创建数据文件

ON PRIMARY

(

name='MySchool_data',

fileName='D:\Project\MyShool_data.mdf',

size=5MB,

MAXSIZE=100MB,

FILEGROWTH=15%

),

FILEGROUP FG

(

name='MySchools_data',

fileName='D:\Project\MyShools_data.ndf',

size=5MB,

MAXSIZE=100MB,

FILEGROWTH=15%

)

--创建日志文件

LOG ON

(

name='MySchool_log',

fileName='D:\Project\MyShool_log.ldf',

size=2MB,

FILEGROWTH=1MB

),

(

name='MySchools_log',

fileName='D:\Project\MyShools_log.ldf',

size=2MB,

FILEGROWTH=1MB

)

GO

--删除数据库

DROP DATABASE MySchool

--如何查询数据库是否存在

select * from sysdatabases where name='MySchools'

二.创建数据库表

USE MySchool

GO

--检查 若存在则删除

IF EXISTS(select * from sysobjects where name='Student')

DROP TABLE Student

--创建数据库表

CREATE TABLE Student

(

studentNo int IDENTITY(1,1) NOT NULL,

studentName nvarchar(20) NOT NULL,

loginPwd nvarchar(20) NOT NULL,

Sex bit NOT NULL,

gradeId int NOT NULL,

phone nvarchar(20) NULL,

address nvarchar(255) NULL,

bornDate datetime NOT NULL,

email nvarchar(50) NULL,

identityCard varchar(18) NOT NULL

)

GO

CREATE TABLE Grade

(

gradeId int IDENTITY(1,1) NOT NULL,

gradeName nvarchar(50) NOT NULL

)

三.添加约束

--添加主键约束

ALTER TABLE Grade

ADD CONSTRAINT PK_gradeId PRIMARY KEY(gradeId)

GO

ALTER TABLE Student

ADD CONSTRAINT PK_studnetNo PRIMARY KEY(studentNo), --主键约束

CONSTRAINT CK_bornDate CHECK(bornDate>='1980-01-01'), --检查约束

CONSTRAINT UQ_identityCard UNIQUE(identityCard), --唯一约束

CONSTRAINT DF_address DEFAULT('深圳嘉华') FOR address, --默认约束

CONSTRAINT FK_gradeId FOREIGN KEY(gradeId) references Grade(gradeId) --外键约束

--删除约束

ALTER TABLE Grade

DROP CONSTRAINT PK_gradeId

四.拓展

--1.为表单独添加一列

ALTER TABLE Grade

ADD gradeNum int NOT NULL

--2.单独删除表中一列

ALTER TABLE Grade

DROP COLUMN gradeNum

--3.添加文件组

ALTER DATABASE MySchool

ADD FILEGROUP FG2

--4.向新的文件组中添加数据文件

ALTER DATABASE MySchool

ADD FILE

(

name='MySchoolss_data',

fileName='D:\Project\MyShoolss_data.ndf',

size=5MB,

MAXSIZE=100MB,

FILEGROWTH=15%

)TO FILEGROUP FG2

--5.删除文件组

ALTER DATABASE MySchool

REMOVE FILE MySchools_data

ALTER DATABASE MySchool

REMOVE FILEGROUP FG

--6.创建登录名

USE master

GO

CREATE LOGIN shuaige WITH PASSWORD='123456' --1.CREATE创建

execute sp_addlogin 'zhangsan','123456' --2.系统存储过程

--7.创建用户名

USE MySchool

GO

CREATE USER shauige FOR LOGIN shuaige --1.CREATE创建

execute sp_grantdbaccess 'zhangsan','zhangsan'--2.系统存储过程

--8.授权(向用户授权 增INSERT 删DELETE 改UPDATE 查SELECT )

GRANT SELECT,UPDATE,DELETE,INSERT ON Grade TO shauige

--GRANT ALL ON Grade TO shauige

--9.收回权限

REVOKE DELETE,UPDATE ON Grade FROM shauige

CH02:T-SQL程序

USE MySchool

GO

CREATE TABLE Score(

studentNo int identity(1,1) NOT NULL,

studentName nvarchar(20) NOT NULL,

score int NOT NULL,

gradeId int NOT NULL

)

ALTER TABLE Score

ADD CONSTRAINT DF_score DEFAULT(0) FOR score,

CONSTRAINT CK_score CHECK(score>=0 AND score

CONSTRAINT FK_gradeId FOREIGN KEY(gradeId) REFERENCES Grade(gradeId)

ALTER TABLE Score

ADD CONSTRAINT PK_SstudentNo PRIMARY KEY(studentNo)

INSERT INTO Score

SELECT '张三',89,1 UNION

SELECT '李四',99,3 UNION

SELECT '王武',75,2 UNION

SELECT '赵柳',68,1 UNION

SELECT '陈琦',32,3

select * from Score

/*

变量：

局部变量：以@为前缀 是有操作者声明的 需要先声明后赋值

全局变量：以@@为前缀 是由系统自带的，属于系统管理 不能修改

*/

--1.声明变量

declare @name varchar(10),@age int

--2.赋值

--select @name=studentName from Score where studentNo=3

set @name='张三'

select @age=18

--4.set和select赋值的区别

--1.set不能同时对多个变量赋值，。select则可以

--set @name='李四',@age=19

--select @name='李四',@age=19

--2.当表达式返回多个值时，使用SET赋值编译没有问题，运行时出错

-- 表达式返回多个值时，使用select 将返回的最后一个值赋给变量

--set @name=(select studentName from Score)

--select @name=studentName from Score

--3.当表达式没有返回值时，使用set为变量赋值，则会赋null(空值)

-- 使用select，则变量保持原值

--set @name=(select studentName from Score where studentNo=6)

select @name=studentName from Score where studentNo=6

--3.打印输出

print @name

select @age

GO

select @@ERROR --0

select @@IDENTITY --5

insert into Score values('周八',68,3)

select @@ROWCOUNT --1

select @@IDENTITY --6

insert into Score values('王二',-8,3)

select @@ERROR AS 错误号

select @@LANGUAGE 当前使用语言

select @@VERSION 版本

select 服务器名称=@@SERVERNAME

--打印错误号

print '错误号'+@@Error

--'+'当加号两边有一个为int型数据 则表示相加

-- 当加号两边都为字符类型时，表示连接

--数据类型转换

print '错误号:'+CONVERT(varchar(10),@@Error)

print '错误号:'+CAST(@@ERROR AS varchar(10))

print CAST(GETDATE() AS varchar(50))

print CONVERT(varchar(50),GETDATE(),111)

--CAST转换和CONVERT转换的区别

/*

1.CAST使用两个参数 CONVERT使用三个参数

2.CONVERT在进行日期转换时可以设置样式

所以在使用CONVERT进行日期转换时更有优势

*/

--print输出和select输出的qubie

/*

1.print是以文本形式输出 select是以网格形式输出

变量（局部变量 全局变量）-->赋值->输出-->数据类型转换

*/

--逻辑控制

/*

流程结构

1.顺序结构

BEGIN ... END {}

2.选择结构

IF...ELSE

CASE...END

3.循环结构

WHILE...

*/

--顺序结构

DECLARE @name varchar(10),@score int

BEGIN

set @name='张三'

set @score=88

print @name

print @score

END

GO

--选择结构

--案例：小名考试成绩 如果大于90分 老师说：你真棒！ 否则，回去抄写代码100遍！

DECLARE @score int

set @score=95

IF(@score>90)

BEGIN

print '老师说：你真棒！'

print '给个糖吃'

END

ELSE

print '回去抄写代码100遍！'

GO

--多重if

DECLARE @score int

set @score=85

IF(@score>90)

BEGIN

print '老师说：你真棒！'

print '给个糖吃'

END

ELSE IF(@score>80)

print '可以回去休息啦！'

ELSE

print '回去抄写代码100遍！'

GO

--区间判断

--案例：90-A 80-B 70-C 60-D 60...E

DECLARE @score int

set @score=58

SELECT @score AS 考试成绩,等级=CASE

WHEN(@score>90)THEN 'A'

WHEN(@score>80)THEN 'B'

WHEN(@score>70)THEN 'C'

WHEN(@score>60)THEN 'D'

ELSE 'E'

END

GO

--等值判断

declare @abc int

declare @xyz int

set @abc=10

while @abc

begin

set @xyz=case (floor(@abc/30))

when 0 then @abc*5

when 1 then @abc*10

else @abc*20

end

set @abc=@xyz

end

print @xyz

GO

--循环结构

--案例：求1到100之间所有数之和

DECLARE @i int,@sum int

set @i=1

set @sum=0

WHILE(@i

BEGIN

set @sum+=@i

set @i+=1

END

print @sum

GO

--WHILE continue

--案例：求求1到100之间所有偶数之和

DECLARE @i int,@sum int

set @i=1

set @sum=0

WHILE(@i

BEGIN

set @i+=1

IF(@i%2!=0)

CONTINUE

set @sum+=@i

END

print @sum

GO

--WHILE break

--案例：求求1到80之间所有数之和

DECLARE @i int,@sum int

set @i=1

set @sum=0

WHILE(@i

BEGIN

BREAK

set @sum+=@i

set @i+=1

END

print @sum

GO

print '程序结束！'

--WHILE return

--案例：当i>80时程序退出

DECLARE @i int,@sum int

set @i=1

set @sum=0

WHILE(@i

BEGIN

RETURN

set @sum+=@i

set @i+=1

END

print @sum

GO

print '程序结束！'

--打印九九乘法表

DECLARE @i int,@j int,@str nvarchar(255)

set @i=1

set @j=1

set @str=''

WHILE(@i

BEGIN

WHILE(@j

BEGIN

select @str+= cast(@i AS char(2))+'*'+cast(@j AS char(2))+'='+cast(@i*@j AS char(2))+' '

set @j+=1

END

print @str

set @str=''

set @i+=1

set @j=1 --重置j

END

GO

CH03:T-SQL高级查询

USE MySchool

GO

select * from Student

select * from Subject

select * from Grade

select * from Result

/*

子查询特点：

1.子查询一般放在小括号中使用

2.子查询查找的数据一般是单列或单个值

3.当子查询与比较运算符一起使用时，子查询返回的结果不能多于1个

4.当子查询返回多个值时，此时我们需要使用关键字in 去代替"="

5.子查询的列必须在上一级查询中出现

6.Exists子查询 当查询结果有记录时返回true 没有记录返回false

*/

--案例1：

/*

1.声明变量 存放胡灵的出生日期

2.查询年龄小于胡灵的学生

*/

DECLARE @bornDate datetime

select @bornDate=bornDate from Student where studentName='胡灵'

select * from Student where bornDate>@bornDate order by bornDate desc

GO

--使用子查询来解决

select * from Student where bornDate>

(

select bornDate from Student where studentName='胡灵'

)

--案例2：查询'设计MySchool数据库'考试成绩为60分的学生信息

--方式一：使用多表连接查询

--分析：1.学生信息表

--2.科目表 得到科目编号

--3.成绩表 通过成绩和科目编号得到学生学号

select * from Student AS S

inner join Result AS R on S.studentNo=R.studentNo

inner join Subject AS J on R.subjectId=J.subjectId

where J.subjectName='设计MySchool数据库' and R.score=60

--可不可以使用子查询来实现？ 可以

--1.先通过科目信息得到科目编号 3

select subjectId from Subject where subjectName='设计MySchool数据库'

--2.通过科目编号+成绩 得到学生学号

select studentNo from Result where subjectId=3 and score=60

--3.通过学生学号得到学生信息 子查询转换 从外往内

select * from Student where studentNo in

(

select studentNo from Result where subjectId=

(

select subjectId from Subject where subjectName='设计MySchool数据库'

) and score=60

)

--案例3：查询'设计MySchool数据库'最近一次未参加考试的学生信息

select studentNo,studentName from Student where gradeId=2

--分析：1.通过科目名称得到科目编号

select subjectId from Subject where subjectName='设计MySchool数据库'

--2.通过科目编号得到最近一次'设计MySchool数据库'课程考试日期

select MAX(examDate) from Result where subjectId=3

--3.得到最近一次考试的学生学号

select studentNo from Result where examDate=

(

select MAX(examDate) from Result where subjectId=

(

select subjectId from Subject where subjectName='设计MySchool数据库'

)

)

--4.得到未参加考试的学生学号

select studentNo,studentName from Student where studentNo not in

(

select studentNo from Result where examDate=

(

select MAX(examDate) from Result where subjectId=

(

select subjectId from Subject where subjectName='设计MySchool数据库'

)

)

)AND gradeId=

(

select gradeId from Subject where subjectName='设计MySchool数据库'

)

--案例4：查询’设计MySchool数据库‘最近一次考试成绩

-- 如果有大于80的每人加两分 没有的话每人加五分

--1.根据科目名称得到科目编号

select subjectId from Subject where subjectName='设计MySchool数据库'

--2.根据科目编号得到最近一次考试的日期

select MAX(examDate) from Result where subjectId=3

--3.根据日期得到最近一次考试的最高分

select MAX(score) from Result where examDate=

(

select MAX(examDate) from Result where subjectId=

(

select subjectId from Subject where subjectName='设计MySchool数据库'

)

)

--4.根据最高分使用If判断 更新成绩

IF EXISTS(

select score from Result where examDate=

(

select MAX(examDate) from Result where subjectId=

(

select subjectId from Subject where subjectName='设计MySchool数据库'

)AND subjectId=

(

select subjectId from Subject where subjectName='设计MySchool数据库'

)

)AND score>80

)

UPDATE Result set score+=2 where subjectId=

(

select subjectId from Subject where subjectName='设计MySchool数据库'

)AND examDate=

(

select MAX(examDate) from Result where subjectId=

(

select subjectId from Subject where subjectName='设计MySchool数据库'

)

)AND score

ELSE

UPDATE Result set score+=5 where subjectId=

(

select subjectId from Subject where subjectName='设计MySchool数据库'

)AND examDate=

(

select MAX(examDate) from Result where subjectId=

(

select subjectId from Subject where subjectName='设计MySchool数据库'

)

)

--查询修改之后的成绩

select * from Result where examDate=

(

select MAX(examDate) from Result where subjectId=

(

select subjectId from Subject where subjectName='设计MySchool数据库'

)

)

--拓展

--1.联合查询

/*

联合查询注意事项：

1.联合查询表 要查询的数据列要保存数目相同，数据类型相同或相兼容

2.联合查询只显示第一张表所起的别名或标题

3.联合查询使用UNION时会根据第一列进行自动排序 并且不显示重复的行

要想显示所有的数据 则需要使用UNION ALL

4.联合查询的记录数是多个表中的记录数相加

连接查询时记录数是多张表的记录数相乘（笛卡尔积）

5.联合查询的排序 可以根据第一列的数据进行自动排序，也可以使用''(空值进行排序)

还可以使用order by 进行排序

但是 order by一定要放在最后 并且第一列的列名称要一致 或者保持别名一致

*/

select '' AS 学号,gradeId AS 年纪编号,address AS 地址 from Student --60

UNION

select studentNo AS 学号,score AS 成绩,CAST(examDate AS nvarchar(50)) AS 考试日期 from Result --89

order by 学号

--2.分页SQL语句

--查询学生信息表中第11条到第20条的学生信息

-- 10代表是每页显示的数据条数 pageCount

-- 当前页 n

-- 20代表的是前(n-1)页的数据 20=(n-1)*pageCount

select * from Student

select top 10 * from Student where studentNo not in

(

select top 20 studentNo from Student

)

--3.all(全部) any(任意一个) some(某一个)

select * from T1 --1 2 3 4

select * from T2 --2 3

--案例1：得到T1中大于T2中所有的数的数字有 --4

select * from T1 where x>ALL

(

select x from T2

)

--案例2：T1中大于T2中任意一个数的数字有那些 --3 4

select * from T1 where x>any

(

select x from T2

)

select * from T1 where x>some

(

select x from T2

)

--案例3：查询T1中不在T2中存在的数字有那些？ --1 4

select * from T1 where x not in

(

select x from T2

)

select * from T1 where xsome --1 2 3 4 T1中不等于T2中任意一个数的数字有？

(

select x from T2

)

select * from T1 where xany --1 2 3 4 T1中不等于T2中任意一个数的数字有？

(

select x from T2

)

select * from T1 where xall --1 4 T1中不等于T2中全部数字的数字有？

(

select x from T2

)

--在某些情况下 not in 等价于 all