SQL server （四）数据的增删改查
1.查询数据
1. SELECT语句
SELECT select_list
[INTO new_table_name]
FROM table_list
[WHERE search_conditions]
[GROUP BY group_by_list]
[HAVING search_conditions]
[ORDER BY order_list [ASC|DESC]]
use sc_db
--查询学生表中学生的姓名
SELECT Sname
FROM Student
--查询三列
SELECT Sno,Sname,Ssex
FROM Student
--查询所有
SELECT *
FROM Student
关键字DISTINCT
作用：去重
SELECT DISTINCT 列名称 FROM 表名称
--去重复数据
SELECT DISTINCT Sdept
FROM Student
关键字TOP
用于规定要返回的记录的数目
SELECT TOP n *|列名 FROM 表名
--查询所有列的前三行
SELECT TOP 3 *
FROM Student
--查询系名的前三行
SELECT TOP 3 Sdept
FROM Student
--去掉重复数据后，后面的数据会往前补
SELECT DISTINCT TOP 3 Sdept
FROM Student
使用别名查询
1.在列的表达式中给出别名
SELECT 列名 ‘别名’ FROM 表名
SELECT Sno 'Sno1',Sname 'Sname1',Ssex 'Ssex1',Sage 'Sage1',Sdept 'Sdept1'
FROM Student
2.使用'='表达式
SELECT '别名' =列名 FROM 表名
SELECT 'Sno1' =Sno,'Sname1'=Sname
FROM Student
3.使用AS关键字来连接列表达式和指定的别名
SELECT 列名 AS '别名' FROM 表名
SELECT Sno AS 'Sno1',Cno AS 'Cno1',Grade AS 'Grade1'
FROM SC
计算列
SELECT Sno,Cno,'调整前的成绩'=Grade,
'调整后的成绩'=Grade-10
FROM SC
在查询基础上创建新表
SELECT 选择列表
INTO 新表名
FROM 表名
2.选择查询
SELECT 列名
FROM 表名
WHERE 条件
比较搜索条件
=，>，<，>=，<=，<>(不等于)，!>(不大于)，!<(不小于)，!=(不等于)
--查询计算机科学系的学生信息
SELECT *
FROM Student
WHERE Sdept='CS'
--查询计算机科学系的男学生信息
SELECT *
FROM Student
WHERE Sdept='CS' AND Ssex='男'
范围搜索条件
BETWEEN和NOT BETWEEN
--查询成绩在80到90之间的学生 [80,90]闭区间
SELECT *
FROM SC
WHERE Grade BETWEEN 80 AND 90
列表搜索条件
IN关键字使用户可以选择与列表中的任意值匹配的行
--查询计算机科学系和数学系的学生
SELECT *
FROM Student
WHERE Sdept in ('CS','MA')
搜索条件中的字符匹配符
LIKE 关键字搜索与指定模式匹配的字符串，字符串中可包含4种通配符
%：代替零个或多个字符
-：仅替代一个字符
[]：代表指定范围内的单个字符，[]中可以是单个字符（如[acef]），也可以字符范围（如[a-f]）
[]：代表不在指定范围内的单个字符，如[acef]，[^a-f]
涉及空值的查询
列名 IS NULL
列名 IS NOT NULL
--查询课程表中先行课为空的课程
SELECT *
FROM Course
WHERE Cpno IS NULL
3.聚合函数
聚合函数对一组值执行计算，并返回单个值
SUM([DISTINCT] <列名>)：求和
AVG([DISTINCT] <列名>)：平均值
MAX([DISTINCT] <列名>)：最大值
MIN([DISTINCT] <列名>)：最小值
COUNT(*)：统计表中元组个数
COUNT([DISTINCT] <列名>)：统计本列列值个数
除COUNT(*)外，其他函数在计算过程中均忽略NULL值。
SELECT 聚合函数 FROM 表名
--求成绩的总和
SELECT SUM(Grade) AS '总成绩'
FROM SC
--求成绩的平均值
SELECT AVG(Grade) AS '平均成绩'
FROM SC
--最大值
SELECT MAX(Grade) AS '最高成绩'
FROM SC
--最小值
SELECT MIN(Grade) AS '最低成绩'
FROM SC
--统计学生的总人数
SELECT COUNT(*) AS '总人数'
FROM Student
--查询有成绩的学生个数
SELECT COUNT(DISTINCT Sno)
FROM SC
4.数据分组
GROUP BY 语句用于聚合函数，根据一个或多个列对结果集进行分组。
SELECT 列名, 聚合函数(列名) FROM 表名
GROUP BY 列名
--对系分组，查询每个系的学生人数
SELECT Sdept,COUNT(Sdept) AS '系数量'
FROM Student
GROUP BY Sdept
--查询每个系学生的最大年龄和最小年龄
SELECT Sdept,MAX(Sage) AS '最大年龄',MIN(Sage) AS '最小年龄'
FROM Student
GROUP BY Sdept
HAVING通常与GROUP BY子句一起使用。相当于一个用于组的WHERE子句，制定组的搜索条件。HAVING子句可以包含聚合函数，但WHERE不可以。
--查询总成绩小于400分的学号
SELECT Sno,SUM(Grade) AS '总成绩'
FROM SC
GROUP BY Sno
HAVING SUM(Grade)<400
5.数据排序
使用ORDER BY 语句对结果集排序
默认升序，降序可以使用DESC关键字
--查看学生表，按年龄升序
SELECT *
FROM Student
ORDER BY Sage
--查看学生表，按年龄降序
SELECT *
FROM Student
ORDER BY Sage DESC
6.子查询
子查询在其他查询结果的基础上提供了一种有效的方式来表示WHERE子句的条件。
子查询用圆括号括起来。
嵌套子查询
--查询计算机科学系学生选修了哪些课程
SELECT *
FROM SC
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept='CS'
)
--查询修了‘2’号课程
--且成绩高于此课程平均分的学生的学号和成绩
SELECT Sno,Grade
FROM SC
WHERE Cno='2'
AND Grade>
(SELECT AVG(Grade)
FROM SC
WHERE Cno='2'
)
WHERE子句后的条件要什么，子查询就查什么。
相关子查询
也叫单值子查询。只返回一个值，然后将一列值与查询返回的值进行比较。
--查询和李勇一个系的学生
SELECT Sname
FROM Student
WHERE Sdept=(SELECT Sdept
FROM Student
WHERE Sname='李勇'
)
7.表连接
在实际查询应用中，用户所需要的数据并不全在一个表中，可能存在多个表中，这时就需使用多表查询。
在数据库应用中，经常需要从多个相关的表中查询数据，这就需要进行表连接。
内部连接
两种语法：
SELECT select_list FROM 表名1,表名2
WHERE 表1.列=表2.列
SELECT select_list FROM 表1 [INNER] JOIN 表2
ON 表1.列=表2.列
--查询学生信息和成绩
SELECT Student.Sno,Student.Sname,Student.Sdept,SC.Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno
--使用别名
SELECT Stu.Sno,Stu.Sname,Stu.Sdept,SC.Grade
FROM Student AS Stu,SC
WHERE Stu.Sno=SC.Sno
SELECT Student.Sno,Sname,Sdept, SC.Grade
FROM Student INNER JOIN SC
ON Student.Sno=SC.Sno
只用匹配到的结果才会有输出
外部连接
返回FROM子句中提到的至少一个表或视图中的所有行
分为左外部连接、右外部连接、全外连接
（先写的是左表）
左外连接
使用LEFT OUTER JOIN关键字。左外部连接对连接条件中左边的表不加限制。如果左表的某行在右表中没有找到匹配的行，则结果集中的右表的相对应的位置为NULL。
--查询所有学生的学号，姓名，系名，成绩
SELECT Student.Sno,Student.Sname,Student.Sdept,SC.Grade
FROM Student LEFT OUTER JOIN SC
ON Student.Sno=SC.Sno
右外连接
使用RIGHT OUTER JOIN关键字。右外部连接对连接条件中右边的表不加限制。如果右表的某行在左表中没有找到匹配的行，则结果集中的左表的相对应的位置为NULL。
完全连接
使用FULL OUTER JOIN关键字。对两个表都不加限制，所有两个表中的行都会包括在结果集中。当某行在一个表中没有匹配的行时，则另一个表与之相对应列的值为NULL。
2.添加数据
1.使用INSERT和VALUES插入行
INSERT [INTO] 表名 [(列名)]
VALUES(data_values,...n)
--往学生表添加学生信息
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201215121','李勇','男',20,'CS')
INSERT INTO Student VALUES('201215122','刘晨','女',19,'CS')
INSERT INTO Student VALUES('201215123','王敏','女',18,'MA')
INSERT INTO Student VALUES('201215125','张立','男',19,'IS')
--往课程表添加课程信息
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('1','数据库','5',4)
INSERT INTO Course VALUES('2','数学',NULL,4)
INSERT INTO Course VALUES('3','信息系统','1',4)
INSERT INTO Course VALUES('4','操作系统','6',3)
INSERT INTO Course VALUES('5','数据结构','7',4)
INSERT INTO Course VALUES('6','数据处理',NULL,2)
INSERT INTO Course VALUES('7','PASCAL语言','6',4)
--往学生-课程表添加信息
INSERT INTO SC(Sno,Cno,Grade) VALUES('201215121','1',92)
INSERT INTO SC VALUES('201215121','2',85),('201215121','3',88),
('201215121','2',90),('201215121','2',80)
2.使用INSERT和SELECT插入行
在INSERT语句中使用SELECT子句可以将一个或多个表或视图中的值添加到另一个表中。使用SELECT子句还可以同时插入多行。
INSERT [INTO] table_name [(column_list)]
SELECT select_list FROM table_name
--创建一个学号-姓名表
CREATE TABLE Sno_Sname
(
Sno VARCHAR(50),
Sname VARCHAR(50)
)
INSERT INTO Sno_Sname
SELECT Sno,Sname FROM Student
3.修改数据
UPDATE 表名 SET 列名=表达式 [WHERE限定条件]
--修改数学课程的学分
UPDATE Course SET Ccredit=2 WHERE Cno='2'
--将计算机系全体学生成绩+5分
--使用子查询
UPDATE SC SET Grade=Grade+5
WHERE Sno IN
(SELECT Sno FROM Student
WHERE Sdept='CS'
)
--使用多表连接
UPDATE SC SET Grade=Grade+5
FROM SC JOIN Student ON SC.Sno=Student.Sno
WHERE Sdept='CS'
4.删除数据
DELETE FROM 表名 WHERE 选择条件
--删除90分以上的学生
DELETE FROM SC
WHERE Grade>90
--删除前20%的学生成绩
DELETE TOP (20) PERCENT FROM SC
--删除前5行
DELETE TOP (5) FROM SC
