SQL SERVER数据库(一)：SQL语句增删查改
1. SQL语句简介
SQL即结构化查询语言，是关系数据库管理系统的标准语言。
SQL语句是与DBMS“交互”的专用语句，不同的DBMS都认SQL语法。
SQL中字符串用单引号。
SQL语句是大小写不敏感的（默认情况不区分大小写，但可设置为大小写敏感）。
SQL主要分DDL、DCL、DML，开发人员重点放在DML上。
1.1 DDL（数据定义语言）
DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。包括结构定义，操作方法定义等。
建库建表：create database 库名 create table 表名
修改表结构：alter table 表名
删库删表：drop database 库名 drop table 表名
删库和删表时，可在前面加一个判断if db_id（’库名’）is not null
修改表的约束：
增加非空约束：alter table Employee alter column EMP_NAME varchar(200) not null
增加主键约束：alter table Employee add constraint PK_EMPLOYEE_EMP_ID primary key(EMP_ID)
增加唯一约束：alter table Employee add constraint UQ_EMPLOYEE_EMP_NAME unique(EMP_NAME)
增加默认约束：alter table Employee add constraint DF_EMPLOYEE_EMP_GENDER default('男') for EMP_GENDER
增加检查约束：alter table Employee add constraint CK_EMPLOYEE _EMP_AGE check(EMP_AGE>=0 and EMP_AGE<=120)
删除约束：alter table EMPLOYEE drop constraint PK_EMPLOYEE_EMP_ID
删除多个约束，约束名用逗号分开：alter table EMPLOYEP drop constraint CK_EMPLOYEE_EMP_AGE , DF_EMPLOYEE_EMP_GENDER
增加多个约束：alter table EMPLOYEE add constraint PK_EMPLOYEE_EMP_ID primary key(EMP_ID)
constraint UQ_EMPLOYEE_EMP_NAME unique(EMP_NAME)
constraint CK_EMPLOYEE_EMP_AGE check(EMP_AGE<=0 and EMP_AGE>=120)
1.2 DCL（数据控制语言）
DCL（Data Control Language）是数据库控制语言。用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL。
1.3 DML（数据操纵语言）
数据操纵语言DML（Data Manipulation Language），对数据库的基本操作，对表中的数据增删查改排序等等。
2 增删改查操作
2.1 SQL语句语法
增：Insert into 表名（字段1，字段2，字段3，…） values（值1，值2，N值3，…）；
默认的是非Unicode字符，前面加N表示插入的是Unicode字符
删：delete from 表名 where 字段名 = value
删除的数据只能从日志文件中去获取恢复（难）
truncate table表名------最少日志模式，删除很快
改：update 表名 set 字段1=value where 字段2=value
查：select 字段1，字段2，字段3，…from 表名
在查询分析界面可以拖动表名和字段名
复制表结构：
select *
into 新表名
from 数据源表名 where条件
2.2 select查询语句
2.2-1 简单select语法
（1）select * from 表名（杜绝这种用法）
写SQL语句不要写 *，写 *是极其糟糕的习惯。
（2）select 字段名 from 表名（推荐如下示例写法：）
指示准确的字段名，比模糊查询效率高）
表结构发生变化（如增加字段等）后，不容易发生出错
（3）select 字段名 from表名 where 条件
where 条件说明：
（A）满足条件的数据才提取出来。
（B）有多个条件，用and或or 连接起来
由于or 的优先级比较低，所以最后才会计算or
所以 Fmath<60 or English<60 and FGender='男'的意思是
[数学成绩小于60分的同学] 或者[英语成绩小于60分的男同学]
IsDel字段的应用，IsDel默认为0，删除时先将IsDel标识为1，之后定期清理。
2.2-2 复杂select语法
（1）列名
(A)select字段 as 列名 from 表名
(B)select字段列名 from 表名，写法不推荐
(C)select列名=字段 from 表名
(D)输出额外的内容，如下例的‘哇咔咔’， 1 2。
Select '哇咔咔',1 2,姓名=stuname
From Student
where
Stuaddress like'%西%'
And year(StuInputTime)=2012
（2）top 数字
--只查询表中的前十条数据
select TOP 10 * from EMPLOYEE
--查询表中前百分之二十的数据
select TOP 20 PERCENT * from EMPLOYEE
--得到前指定行数的数据 order by排序 使用asc升序，使用desc降序 默认升序
select TOP 10 * from EMPLOYEE order by EMP_Name
数据库是基于集合概念（唯一性，无序性）
就有了无规律特征，使用top n 表示数据在哪里读数据，就查询前n行
通过没有order by字句的数据查询得到的结果，称为结果集
top与order by连用时，起到两个作用：一个筛选，一个排序，还是一个结果集。
但仅仅使用order by以后，数据就有了顺序（就不是集合），称为“游标”
（3）distinct
select distinct 字段 from 表名将结果集由多集变成单集
select distinct 字段列表 from 表名将结果集由多集变成单集
--distinct 表示对查询出的结果集，进行去除重复数据
--只有当查询出的结果集中存在完全重复的多行，这时才会把所有重复的行去掉，只保留一行，其他都取去除掉。
（4）聚合函数（*）
--min、max、 count、 avg 、sum
聚合行数的结果只有一行，不能同时查询其他列或有多行结果的其他数据项
select count（*或字段名）from 表名
select sum（字段名）from 表名
select max（字段名）from 表名
select min（字段名）from 表名
select avg（字段名）from 表名
聚合函数会忽略结果为null的记录
使用avg时，求和结果除以学员总数，不计入分数为null的学员
count(*) 记录所有数据，即使结果为null的记录
count(字段) 忽略结果为null的记录
Select count(*)as总人数
From Student
Where stustudydate='2012-07-15';
select
sum(testBase)AS基础总分,
sum(testBeyond)AS中级总分,
sum(testPro)AS高级总分,
From score
Where stuid=1
Select avg(year(getdate())-year(stubirthdate))as平均年龄
From Student
Where stustudydate='2012-07-15';
Select max(year(getdate())-year(stubirthdate))as最大年龄
From Student
Where stustudydate='2012-07-15';
Select min(year(getdate())-year(stubirthdate))as最小年龄
From Student
Where stustudydate='2012-07-15';
（5）null值判断处理
SQL中的三值逻辑：
在SQL中逻辑表达式的可能值包括TRUE、FALSE、UNKNOWN。他们被称为三值逻辑。三值逻辑是SQL所特有的。大多数的变成语言的逻辑表达式只有TRUE或FALSE两种值。SQL中的UNKNOWN通常出现在包含NULL值的逻辑表达式中。
NULL值通常表示丢失或不相关的值。当比较丢失值和另一个值（这个值也可能是NULL）时，逻辑结果总是UNKNOWN。 (即字段=null，字段<>null都得不到true，也得不到false，因为否定的UNKNOWN（NOT UNKNOWN）还是UNKNOWN。)
在筛选器中比较两个NULL值将得到UNKNOWN，会被当作FALSE处理，就好像其中一个NULL不等于另一个NULL。而UNIQUE约束、排序操作和分组操作认为两个NULL值是相等的。
如果表中有一列定义了UNIQUE约束，将无法向表中插入该列值为NULL的两行。
GROUP BY子句把所有的NULL值分到一组。
ORDER BY子句把所有NULL值排列在一起。
由于三值逻辑的存在和Null 表示“不知道”，所以用is null或is not null判断字段的值是否为null
is null、is not null
--查询出所有FMath的值不为null的数据
select * from MyStudent where FMath is not null
--查询出所有Fmath为null的人
select * from MyStudent where FMath is null
isnull函数替换null值
null用别的值替换，如int 类提替换成-1，字符串的替换为’未知’
isnull函数实现替换null值
Select id, isnull(SName,’未知’) from Student
（6）条件查询
精确条件 =、<>（！=）、 >、 >=（！<） 、<、<=(!>)
Select * from Student Where SName <>’张三’
模糊条件 between …and…、in( ) 、not in( ) 和 like 、not like
between …and…： 包含两边的边界值
Select * From Student
Where stuID between 3 and 12 (含3和12)
in( ) 、not in( )： 括号中可以是值的列表，也可以是查询语句的单列结果集
Select * From Student
Where stuID not in（5，7，9）
and stuID in（select stuId from Student where SName like ‘张%’）
like 、not like：
符号使用like(优化角度少用)，几个关键字（%，_ ，[]，[^]）
% 零个或多个任意字符
_ 一个任意字符
[] 括号中的任意一个字符，（不在[^]括号中）的任意一个字符
用[_]来表示下划线这个字符本身 本身就是通配符的字符想要表示一个普通字符，可以用[]括起来
--查询出所有名字带下划线的人的信息，
select * from MyStudent where FName like '%[_]%'
--^ 表示非
select * from MyStudent where FName like 'a[^a-z]b'
（7）分组group by 和having
--请按照班级ID分组，并统计每组的人数，最后请将每组人数大于250的组的信息显示出来
select FClassID as 班级ID,count(*) as 班级总人数 group by FClassID having count(*)>250
group by 后可以是多个字段，多个字段值完全相同的记录会分到一组
在select语句中使用了group by 子句以后，只能select聚合函数和分组的字段
（8）排序order by 和游标Cursor
--order by排序不仅可以按照某列来排序，也可以按照表达式排序
select *,(FMath FEnglish) as 总分，平均分=(FMaht FEnglish)/2 from MyStudent order by (FMath FEnglish)/2 DESC
--由于order by 在最后才执行，所以列也已经起名了，可以在order by中直接使用列的别名
select *,(FMath FEnglish) as 总分 from MyStudent order by 总分 DESC
排序以后得到的数据成为游标。
执行顺序：where——〉group by——〉having——〉order by
（9）联合结果集union
union取对多个结果的所有行取并集，union all 取并集并且保留重复项
就是对结果的行并运算，要求结果集的列数和结构相同。
select
'平均分' as 统计,
avg(testBase)as 基础,
avg(testBeyond) as 中级,
avg(testPro) as 高级
from dbo.Score
where stuid = 9
union all
select
'总分',
sum(testBase),
sum(testBeyond),
sum(testPro)
from Score
where stuid = 9
--使用union实现汇总
--使用union联合数据的时候
--1.必须保证多个查询的列的个数相同
--2.必须保证多个查询的列的数据类型一一对应(至少是可以隐式转换的)
--3.使用union的时候，如果不加all关键字，则会默认去掉重复数据,要想保留重复数据,则使用union all来联合，使用union all,这样会相对高效（如果没有要求去除重复，则加all）
--请查询出学生成绩中，英语成绩的最高分、最低分、平均分
select '英语成绩最高分:',max(FEnglish) from MyStudent
union all
select '英语成绩最低分:',min(FEnglish) from MyStudent
union all
select '英语成绩平均分:',avg(FEnglish) from MyStudent
2.3 insert插入语句
（1）插入时使用查询结果
insert into 表（列）
select 列
这个是一个显示方法，相当于将一个一行的结果集插入到表中
可以使用union将结果集联合以后得到一个表值结果集，插入
create table test
(
id int identity (1,1) primary key,
name nvarchar(20)
)
insert into test (name)
select stuname from student where stuid = 9
union
select stuname from student where stuname like '%陆%'
union
select stuname from student where stuid = 11
union
select stuname from student where stuid = 12
（2）select into 复制表结构和数据
select * into NewContact from Contact
（3）表值插入
SQL2008可以使用value引导表值构造函数
Values
（
（行），
（行），
（行）
）as …
如：
Select * from
（values
(1,张三),
(2，李四),
(3，王五)
）as student (id,name)
（4）identity_insert设置项
语法
SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }
SET IDENTITY_INSERT{ table } { ON | OFF }
建立一个有标识列的表：
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
尝试在表中做以下操作：
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
结果会导致错误：“当 IDENTITY_INSERT 设置为 OFF 时，不能向表 'products' 中的标识列插入显式值。”
A) 启用IDENTITY_INSERT设置
SET IDENTITY_INSERT products ON
INSERT INTO products (id, product) VALUES(1, 'garden shovel')
返回正确。
建立另外一个表products2，尝试相同插入操作：
CREATE TABLE products2 (id int IDENTITY PRIMARY KEY, product varchar(40))
然后执行：
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 (id, product) VALUES(1, 'garden shovel')
导致错误：“表 'material.dbo.products' 的 IDENTITY_INSERT 已经为 ON。无法对表 'products2' 执行 SET 操作。”
B) 先关闭一个表设置,再开启另一个表的设置
SET IDENTITY_INSERT products OFF --先关闭一个设置
SET IDENTITY_INSERT products2 ON --再开启另一个设置
INSERT INTO products2 (id, product) VALUES(2, 'garden shovel')
执行通过。
尝试以下操作：
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 SELECT * FROM products
导致错误：“仅当使用了列的列表，并且 IDENTITY_INSERT 为 ON 时，才能在表 'products2' 中为标识列指定显式值。”
C) 插入语句中，必须使用主键列
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 (id, product) SELECT * FROM products
执行通过。
总结：
1.每一次连接会话中的任一时刻，只能对一个表设置IDENTITY_INSERT ON，且设置只对当前会话有效；
2.在对标识列执行插入操作进，一定要列出此标识列（当然，同时也就需要列出相关的其他列了）。
2.4 update、delete修改和删除
修改和删除，主要是在基本的update 或delete语法基础上，使用与select相同用法的where条件进行限制。
3.MSSQL高级查询
3.1表连接查询
（1）交叉连接
求两个表的笛卡尔积，第一个表的每一行数据都要与第二张表的所有行连接。
select t2.num,t1.num from t1 crossjoin t2
（2）内连接
在交叉连接的基础之上，使用on做条件筛选。结果中只包含满足条件的记录。
例如查询学生的课程信息（根据班级Id筛选）
select s.stuId,s.stuName,c.courceName
from student as s
inner join course as c on s.classId=c.classId
（3）外连接（左外连接和右外连接）
左外连接left join，相当于做完内连接之后，将不满足条件但是左表中有的数据行补充到结果中，没有数据的列，补NULL。
3.2 联合查询（union）
联合结果集就是对两次或多次查询的结果，取所有行合并。要求列的结构和列数相同。
例如统计数学、英语、物理的平均分和总分：
select '平均分' ,avg(math),avg(english),avg(physics)
from Score
union all
select '总分',sum(math),sum(english),sum(physics)
from Score
3.3 子查询
（1）独立子查询
有独立标量子查询值和独立多值子查询值两种。
独立标量子查询值，根据学生名查询特定学生的成绩（假定没有重名）：
select testId,testName,testDate,stuId,classId,math,english,physics
from Score
where stuId=(select id from student where name='萧晓')
独立多值子查询值，查询当年入学的学生成绩：
select testId,testName,testDate,stuId,classId,math,english,physics
from Score
where stuId in (select id from student where DATEDIFF(year,studydate,GETDATE())=0)
（2）相关子查询
子查询语句使用主查询结果作为条件。因此，相关子查询不能作为简单子查询独立执行。由于对外部查询评估的每一行重复执行一次相关子查询，因此相关子查询也叫重复子查询。
例如查找数学成绩为班级里最高分的学生的成绩信息：
SELECT stuName, math, classId
FROM Score as c1
WHERE math IN
( SELECT MAX (math) FROM Score as c2
WHERE c2.classId = c1.classId)
ORDER BY classId , stuName;
3.4 表表达式
（1）派生表
将一个查询的到的结果作为另外一个查询的数据源
select s.stuId,s.stuName,s.gender
from （
select s1.stuId,s1.stuName,
case s1.gender
when 0 then '男'
when 1 then '女'
when 2 then '保密'
end as gender
from student as s1
）as s
where s.gender ='男'
（2）公用表表达式
with 表别名 as
（
派生表的查询
）
引用别名进行查询
示例：
With stu as
（
select s1.stuId,s1.stuName,
case s1.gender
when 0 then '男'
when 1 then '女'
when 2 then '保密'
end as gender
from student as s1
）
select * from stu where s.gender ='男'