SQL Server基础

一、数据库的操作语句

1、创建数据库
创建一个名称为test1的数据库，并设定数据文件为“G:\SQL DB\测试数据1.MDF”,大小为10MB，最大为50MB，每次增长5MB；事务日志文件为"G:\SQL DB\测试数据1日志.LDF"，大小为10MB，最大为20MB，每次增长5MB。

create database test1
on(
	name=测试数据1,filename='G:\SQL DB\测试数据1.MDF',
	size=10MB,maxsize=50MB,filegrowth=5MB
)
log on(
	name=测试数据1日志,filename='G:\SQL DB\测试数据1日志.LDF',
	size=10MB,maxsize=20MB,filegrowth=5MB
)

2、修改数据库
alter database 数据库名
add file子句指定要添加的文件。
add log file子句指定要添加的日志文件。
remove file子句指定文件为空时才允许删除。
modify file指定要更改的文件，更改选项包括filename，size，filegrowth和maxsize。一次只能更改这些属性中的一种。
modify name=new_dbname用于重命名数据库。

为test1数据库新增一个逻辑名为“测试数据2”的数据文件，其大小及其最大值分别为5MB和50MB，每次增长5MB。

alter database test1
add file(
	name=测试数据2,filename='G:\SQLDB\测试数据2.MDF',
	size=5MB,maxsize=50MB,filegrowth=5MB
)

3、删除数据库
drop database 数据库名

二、表的操作语句

1、创建表
create table 表名
首先创建一个authors表，然后创建book表，并将作者编号列关联到authors表的作者编号列。

create table authors
(
	作者编号 int not null primary key,
	作者姓名 char(20),
	作者地址 char(30)
)
create table book
(
	图书编号 int not null primary key,
	书号 char(8) not null,
	作者编号 int foreign key references authors(作者编号)
)

2、由其他表创建新表
select 列名表 into 表1 from 表2 由表2的列名表创建新表表1
从student表创建student1表，它包含student表的“学号”、“姓名”和“班号”3个列和对应的记录。

select 学号,姓名,班号 into student1 from student 

3、修改表结构
alter table 表名
add子句用于增加列，后面为属性参数设置。
drop子句用于删除约束或者列。constraint表示删除约束；column表示删除列。
给school数据库中的student1表增加一个“民族”列，其数据类型为char(10)。

use school
alter table student1 add 民族 char(10)

4、删除关联和表
drop table 表名
给出删除school数据库中student1表的程序。

use school
drop table student1

三、数据操纵语言

DML主要用于在数据表中插入、修改和删除记录等。
1、insert语句
给出向student表中插入一个学生记录(‘200’,‘曾雷’,‘女’,‘1993-2-3’,‘1004’)的T-SQL程序。

insert into student values('200','曾雷','女','1993-2-3','1004')

2、update语句
用于修改数据表或视图中特定记录或列的数据。
set子句给出要修改的列及其修改后的数据值；where子句指定要修改的行应当满足的条件，当where子句省略时，则修改表中所有行。
给出将student表中插入的学生记录性别修改为“男”的T-SQL程序。

update student
set 性别='男'
where 学号='200'

3、delete语句
用于删除表或视图中的一行或多行记录。
where子句省略时，则删除表中所有行。
删除学号为200的学生记录。

delete student where 学号='200'

四、数据查询语言

select 列名表
from 表或视图名
where 查询限定条件
1、投影查询
（1）查询列
查询student表中所有记录的姓名、性别和班号列。

select 姓名,性别,班号 from student

（2）去除重复列
查询教师的所有单位，即不重复的单位列。

select distinct 系名 from teacher

（3）更改列名
as后为新名，as可省。
查询student表的所有记录，用as子句显示相应的列名。

select 学号 as 'SNO',姓名 as 'SNAME',性别 as 'SSEX'，出生日期 as 'SBIRTHDAY',班号 as 'SCLASS'
from student

2、选择查询
where子句查询条件是一个逻辑表达式，由多个关系表达式通过逻辑运算符（and、or、not）连接而成。
查询score表中成绩在60~80之间的所有记录。

select * from score
where 分数 between 60 and 80

3、排序查询
order by子句：升序（ASC）、降序（DESC）。默认升序。
以课程号升序、分数降序显示score表的所有记录。

select * from score
order by 课程号,分数 desc

输出score表中分数为第2到第5名的成绩记录。
(跳过两行，显示4行 3，4，5，6)

select * from score
order by 课程号,分数 desc
offset 2 rows fetch next 4 rows only

4、使用聚合函数
avg 平均值
count(*) 统计查询输出的行数
min 最小值
max 最大值
sum 总和
查询1003班的学生人数。

select count(*) as '1003班人数'
from student
where 班号='1003'

计算至少选修一门课程的人数。

select count(distinct 学号) as ' 至少选修一门课程的人数'
from score

查询score表中各门课程的最高分。

select 课程号,max(分数) as '最高分'
from score
group by 课程号

查询score表中至少有5名学生选修的课程号以3开头的平均分数。

select 课程号,avg(分数) as '平均分' from score 
where 课程号 like '3%'
group by 课程号 having count(*)>5

查询最低分大于70、最高分小于90的学号列。

select 学号 from score
group by 学号
having min(分数)>70 and max(分数)<90

5、连接查询
（1）等值连接
查询所有学生的姓名、课程号和分数列。

select student.姓名,score.课程号,score.分数
from student,score
where student.学号=score.学号

（2）非等值连接
查询所有学生的学号、课程号和等级列。

select 学号,课程号,等级 from score,grade
where 分数 between low and upp
order by 等级

（3）自连接
将同一个表进行连接，将一个表的某行与同一表的另一行连接起来。
查询选学3-105课程的成绩高于109号学生成绩的所有学生记录，并将成绩从高到低排列。

select x.课程号,x.学号,x.分数
from score x,score y
where x.课程号='3-105' and x.分数>y.分数 and y.学号='109' and y.课程号='3-105'
order by x.分数 desc

（4）内连接
查找两个表的交集。
输出所有课程的任课教师姓名（课程和教师名无空值）。

select course.课程名,teacher.姓名
from course inner join teacher on(course.任课教师编号=teacher.编号)

（5）外连接
左外连接、右外连接、全外连接
left join、right join、full join
输出所有课程的任课教师姓名（查询哪门课程没有任课教师，教师名可以有空值）。

select course.课程名,teacher.姓名
from course left join teacher on(course.任课教师编号=teacher.编号)

（6）交叉连接
cross join
使用交叉连接产生课程和教师所有可能的组合。

select course.课程名,teacher.姓名
from course cross join teacher

6、子查询
（1）简单子查询
一个查询是另一个查询的条件时使用。
查询与学号为101的学生同年出生的所有学生的学号、姓名和出生日期列。

select 学号,姓名,出生日期 from student
where year(出生日期)=
	(select year(出生日期)
	 from student
	 where 学号='101')

查询分数高于平均分的所有学生成绩记录。

select 学号,课程号,分数 from score
where 分数>
	 (select avg(分数)
	  from score)

（2）相关子查询
一个表。返回结果是一个集合。
查询比该课程平均成绩低的学生成绩表。

select 学号,课程号,分数 from score a
where 分数<
		(select avg(分数)
		 from score b
		 where a.课程号=b.课程号)

（3）复杂子查询
使用 in 或 not in 引入子查询；
使用any或all：any 任意一个，all 所有的；
使用exists引入存在测试。选择列表通常由星号(*)组成。
查询选修6-166课程号的学生学号和姓名。（没有：not in）

select student.学号,student.姓名 from student
where student.学号 in
	(select score.学号 from score
	 where score.课程号='6-166')

查询课程号为3-105的学生的课程号、学号和分数，只输出分数至少高于课程号为3-245的学生分数之一的记录，并要求按分数从高到低次序排列。

select 课程号,学号,分数 from score
where 课程号='3-105' and 分数>any
		(select 分数 from score
		 where 课程号='3-245')
order by 分数 desc

查询课程号为3-105的学生的课程号、学号和分数，只输出分数高于课程号为3-245的所有学生分数的记录，并要求按分数从高到低次序排列。

select 课程号,学号,分数 from score
where 课程号='3-105' and 分数>all
		(select 分数 from score
		 where 课程号='3-245')
order by 分数 desc

查询所有任课教师的姓名和所在系名。(未讲课not exists)

select 姓名,系名
from teacher where exists
	(select *
	 from course where teacher.编号=course.任课教师编号)

（4）查询结果的并
union子句，将多个select命令连接起来查询单个sql无法做到的结果集合。
查询所有女教师和女学生的姓名、性别和出生日期。

select 姓名,性别,出生日期
from teacher where 性别='女'
union
select 姓名,性别,出生日期
from student where 性别='女'

7、空值及其处理
空值就是未知的值。
（1）检测空值
查询所有未参加考试的学生成绩记录。

select * from score
where 分数 is null

（2）处理空值
isnull将空值转换为有效的值。isnull(空值,有效的值)
查询所有学生成绩记录，并将空值作为0处理。

select 学号,课程号,isnull(分数,0) as '分数'
from score