2021-06-18SQL语言复习总结

最新推荐文章于 2022-06-10 18:58:14 发布
Charonvous
最新推荐文章于 2022-06-10 18:58:14 发布
阅读量283
点赞数
文章标签：数据库
本文链接：https://blog.csdn.net/m0_48674547/article/details/118028167
版权
这里写自定义目录标题

CREATE DATABASE StudentDatebase
--新建Student表
CREATE TABLE Student
(	Sno CHAR (6) PRIMARY KEY,
	Sname NVARCHAR(20) NOT NULL,
	Ssex NCHAR(1) NOT NULL DEFAULT '男' CHECK (Ssex IN ('男','女')),--默认值、约束
	Sbirthday SMALLDATETIME NULL,
	Sdept NVARCHAR(20) NULL,
	Memo TEXT NULL,
)
--新建Course表
CREATE TABLE Course
(	Cno CHAR (3) PRIMARY KEY,
	Cname NVARCHAR(20) NOT NULL,
	PreCno CHAR(3),
	Credit TINYINT NULL,
	Semester TINYINT NULL,
)
--新建SC表
CREATE TABLE SC
(	Sno CHAR (6) FOREIGN KEY REFERENCES Student(Sno),--外键约束
	Cno CHAR(3) FOREIGN KEY REFERENCES Course(Cno),--外键约束
	Grade SMALLINT CHECK (Grade IN ('0','100')),--范围约束
	PRIMARY KEY(Sno,Cno),--属性组为主键
)
/*
		选择表中若干列
		查询指定列
*/
--1\查询学生姓名和学号
SELECT Sno,Sname FROM Student
--2\查询学生所有信息
SELECT Sno,Sname,Ssex,Sbirthday,Sdept,Memo  FROM Student
--等于
SELECT * FROM Student
/*
		查询表没有的列
*/
--1\查询学生姓名和年龄
SELECT Sname,YEAR(GETDATE())-YEAR(Sbirthday) FROM Student
--2\查询学生姓名，年龄，今年是，年份
--指定别名：列名|【AS】列别名   。as可省略
SELECT Sname AS 姓名,YEAR(GETDATE())-YEAR(Sbirthday) 年龄,'今年是' AS 今年是,YEAR(GETDATE()) 年份, FROM Student
/*
		选择表中若干行
		查询满足条件的元组 WHERE语句
*/
--比较
--1\查询计算机系的姓名
SELECT Sname FROM Student
WHERE Sdept='计算机系'
--2\查询成绩大于90的学生
SELECT Sno,Cno,Grade FROM SC
WHERE Grade>90
--3\查询学分2-3的课程的课程名称，学分，开课学期
SELECT Cname,Credit,Semester FROM Course
WHERE Credit BETWEEN 2 AND 3 
--等于
SELECT Cname,Credit,Semester FROM Course
WHERE Credit>= 2 AND Credit<= 3 
--4\查询学分不在2-3的课程的课程名称，学分，开课学期
SELECT Cname,Credit,Semester FROM Course
WHERE Credit NOT BETWEEN 2 AND 3 
--等于
SELECT Cname,Credit,Semester FROM Course
WHERE Credit>= 2 OR Credit<= 3 
--5\查询1997出生的学生信息
SELECT * FROM Student
WHERE Sbirthday BETWEEN '1997-01-01' AND '1997-12-31'
--确定集合
--查询'计算机系','机电系'的学生信息
/*
--in在集合在true，notin 不在集合中为true：列名 [NOT] IN(常量,常量,常量,...)
*/
SELECT Sname,Sno,Sdept FROM Student
WHERE Sdept IN ('计算机系','机电系')
/*
--字符串匹配  使用LIKE语句   列名 [NOT] LIKE <匹配串>
_下划线：匹配任何一个字符
%：匹配0到多个字符
[]:匹配[]中的任意一个字符
[^]:不匹配[]中的任意一个字符
*/
--查询姓 李 的学生
SELECT Sname,Sno,Sdept FROM Student
WHERE Sname LIKE '李%'
--查询姓名中第二个字是 冲 的学生
SELECT Sname,Sno,Sdept FROM Student
WHERE Sname LIKE '_冲%'
--查询学号最后一位不是 2，3 的学生
SELECT Sname,Sno,Sdept FROM Student
WHERE Sname NOT LIKE '%[2,3]'
/*
ESCAPE 转义字符
例如：查询包含“30%”的字符串。！号后为转义字符
WHERE field1 LIKE '%30%%' ESCAPE '!'
*/
--空值查询
--查询没考试的学生
SELECT Cn0,Sno FROM SC
WHERE Grade IS NULL 
--查询有备注的学生
SELECT Sname,Sno,Memo FROM Student
WHERE Memo IS NOT NULL
--多重条件查询  OR/AND语句   OR优先级小于and
--查询机电系有备注的学生
SELECT Sname,Sno,Sdept,Memo FROM Student
WHERE Memo IS NOT NULL AND Sdept='机电系'
--查询机电系和计算机系1997出生的学生
SELECT Sname,Sno,Sdept,Memo FROM Student
WHERE (Sdept='机电系' OR Sdept='计算机系')
AND Sbirthday BETWEEN '1997-01-01' AND '1997-12-31'
/*
--取消取值相同的行
*/
--查询考试挂科的学生
SELECT DISTINCT Sno FROM SC
WHERE Grade<60
/*
	对查询结果排序
	语句：ORDER BY <列名> [ASC|DESC] [,..n]--ASC升序默认/DESC降序
*/
--将C01课程成绩按升序排列
SELECT Cno,Grade FROM
WHERE Cno='C01' ORDER BY Grade
--将060101号学生成绩按降序排列
SELECT Cno,Grade FROM
WHERE Sno='060101' ORDER BY Grade DESC
/*
	使用聚合函数统计
	语句：
		COUNT(*):统计表中元组个数
		COUNT([DISTINCT] <列名>):统计本列的列值个数，DISTINCT去重复值
		SUM(<列名>):计算列值的和
		AVG(<列名>):计算列值的平均值--不计算null值，返回被计算的数据类型
		MAX(<列名>):计算列值的最大值
		MIN(<列名>):计算列值的最小值
*/
--统计学生总人数
SELECT COUNT(*) 学生总人数 FROM Student
--统计060101的总成绩
SELECT SUM(Grede) 总成绩 FROM SC WHERE Sno='060101'
--统计060101的平均成绩
SELECT AVG(Grede) 平均成绩 FROM SC WHERE Sno='060101'
--统计C01的最高分和最低分
SELECT MAX(Grade) 最高分,MIN(Grade) 最低分
FROM SC WHERE Cno='C01'
/*
		对数据分组
		语句：GROUP BY <分组依据列>[，...n]
					[HAVING <组提前条件>]
*/
--统计每门课的选课人数
SELECT Cno 课程号,COUNT(Sno) 选课人数
FROM SC GROUP BY Cno
--统计每个学生的选课门数
SELECT Sno 学号,COUNT(Cno) 选课门数,AVG(Grade) 平均成绩
FROM SC GROUP BY Sno
--统计每个系男女生人数，按系名升序
SELECT Sdept,Ssex,COUNT(*) 人数 FROM Student
GROUP BY Sdept,Ssex,ORDER BY Sdept
--使用where分组
--统计每个系男生人数
SELECT Sdept,COUNT(*) 男生人数 FROM Student
WHERE Ssex='男' GROUP BY Sdept--先where语句，再执行分组
--使用HAVING分组
--查询选超过3门的学生	
SELECT Sno,COUNT(Cno) 选课门数 FROM SC 
GROUP BY Sno HAVING COUNT(*)>3 --先分组再统计再比较
/*
WHERE筛选from的数据
GROUP BY对where的数据分组
HAVING对分组后的数据再筛选，尽量使用where筛选
*/
/*
多表连接查询
*/
/*
--内连接
语句：
--1、ANSI方式
	FROM 表1 [INNER] JOIN 表2 ON <连接条件>
		连接条件语句：[<表名1>.] <列名1> <比较运算符> [<表名2>.] <列名2>
--2、Theta方式
	FROM 表1,表2 WHERE <连接条件>
*/
--查询学生选课的详细
SELECT * FROM Student INNER JOIN SC
ON Student.Sno=SC.Sno
--查询学生选课的详细，去掉重复列
SELECT S.Sno,Sname,Ssex,Sbirthday,Sdept,Memo,Cno,Grade FROM Student S INNER JOIN SC
ON S.Sno=SC.Sno
--查询计算机系选修数据库原理的学生成绩单（三表连接）
SELECT Sname,Cname,Grade FROM Student S INNER JOIN SC
ON S.Sno=SC.Sno JOIN Course C ON SC.Cno=C.Cno
WHERE Sdept='计算机系' AND Cname='数据库原理'
--查询选了数据库原理的学生姓名和系
SELECT Sname,Sdept
FROM Student S INNER JOIN SCON S.Sno=SC.Sno JOIN Course C ON SC.Cno=C.Cno
WHERE Cname='数据库原理'
--统计每个系的学生平均成绩
SELECT Sdept,AVG(Grade) 系平均成绩 
FROM Student S INNER JOIN SCON S.Sno=SC.Sno JOIN Course C ON SC.Cno=C.Cno
GROUP BY Sdept
--统计计算机系每门课的选课人数，平均分，最高分，最低分
SELECT Cno,COUNT(*) 选课人数,AVG(Grade) 平均分,MAX(Grade) 最高分,MIN(Grade) 最低分
FROM Student S JOIN SC ON S.Sno=SC.Sno
WHERE Sdept='计算机系'
GROUP BY Cno
/*
--自连接
一定取别名
*/
--查询数据库原理的先修课程
SELECT C1.Cname 课程名,C2.Cname 先修课程名
FROM Course C1 JOIN Course C2 ON C1.PreCno=C2.Cno
WHERE C1.Cname='数据库原理'
--查询与钟文辉再同一系学习的学生姓名和系
SELECT S2.Sname,S1.Sdept
FROM Student S1 JOIN Student ON S1.Sdept=S2.Sdept
WHERE S1.Sname='钟文辉' AND S2.Sname!='钟文辉'
/*
--自连接
语句：
		--ANSI方式SQLserver
		FROM 表1 LEFT|RIGHT [OUTER] JOIN 表2 ON <连接条件>--left限制表2必须满足条件，right限制表1满足条件
		--theta方式oracle
		左：FROM 表1,表2 WHERE [表1.] 列名 (+)=[表2.] 列名
		右：FROM 表1,表2 WHERE [表1.] 列名 =[表2.] 列名(+)
*/
--查询学生选课情况，包含未选课
SELECT S.Sno,Sname,Sdept,SC.Cno
FROM Student S LEFT JOIN SC ON S.Sno=SC.Sno
WHERE Sdept='计算机系'
ON S1.Sdept=S2.Sdept
--查询没人选的课程名
SELECT Cname,Sno FROM Course C JOIN SC ON C.Cno=SC.Cno
WHERE SC.Cno IS NULL
--统计计算机系每个学生的选课门数，包含未选课的
SELECT S.Sno 学号,COUNT(SC.Cno) 选课门数
FROM Student S LEFT JOIN SC ON S.Sno=SC.Sno
WHERE Sdept='计算机系'
GROUP BY S.Sno
--统计机电系选课少于3门的，包含未选，按门数降序
SELECT S.Sno 学号,COUNT(SC.Cno) 选课门数
FROM Student S LEFT JOIN SC ON S.Sno=SC.Sno
WHERE Sdept='机电系'
GROUP BY S.Sno
HAVING COUNT(SC.Cno)<3
ORDER BY COUNT(SC.Cno) DESC--连接》where》group》having》order
/*
TOP的使用
语句：
		TOP n[PERCENT] [WITH TIES]
			n:未非负整数
			top n：取查询结果前n行
			top n percent：取查询结果前n%行
			WITH TIES：包含并列的结果,使用后必须使用ORDER BY
*/
--查询C04前三名
SELECT TOP 3 Sno,Grade FROM SC
WHERE Cno='C04'
ORDER BY Grade DESC
--查询学分最低4门课
SELECT TOP 4 Cname,Credit,Semester
FROM Course
ORDER BY Credit DESC
--查询选课最多的两门
SELECT TOP 2 WITH TIES Cno,COUNT(*) 选课人数
FROM SC GROUP BY Cno
ORDER BY COUNT(Cno) DESC
/*
CASE表达式
语句：
--1\简单case函数
   CASE 测试表达式
		WHEN 简单表达式1 THEN 结果表达式2
		WHEN 简单表达式1 THEN 结果表达式2
		WHEN 简单表达式1 THEN 结果表达式2
		...
		END--列名
--2\搜索case函数
 CASE
		WHEN 布尔表达式1 THEN 结果表达式2
		WHEN 布尔表达式1 THEN 结果表达式2
		WHEN 布尔表达式1 THEN 结果表达式2
		...
		END--列名
*/
--查询学生，所在系用代称--简单
SELECT Sno 学号,Sname 姓名,Ssex 性别
	CASE Sdept
		WHEN '计算机系' THEN 'CS'
		WHEN '机电系' THEN 'JD'
		WHEN '信息管理系' THEN 'IM'
	END 所在系
FROM Student
--查询学生，所在系用代称--布尔
SELECT Sno 学号,Sname 姓名,Ssex 性别
	CASE 
		WHEN Sdept='计算机系' THEN 'CS'
		WHEN Sdept='机电系' THEN 'JD'
		WHEN Sdept='信息管理系' THEN 'IM'
	END 所在系
FROM Student
/*
将结果保存的表中
语句：
	SELECT 查询列表序列 INTO <新表名>
	FROM 数据源
*/
--将计算机系学生的姓名、学号、性别、年龄保存到Student_CS中
SELECT Sno,Sname,Ssex,YEAR(GETDATE())-YEAR(Sbirthday) Sage
INTO Student_CS
FROM Student
WHERE Sdept='计算机系'
/*
子查询,通常出现再主查询的WHERE\HAVING句中
查询块：SELECT-FROM-WHERE
*/
/*
--子查询进行基于集合的测试
	语句：WHERE <列名> [NOT] IN (子查询)
*/
--查询与钟文辉再同一个系的学生学号、姓名、性别、所在系
SELECT Sno,Sname,Ssex,Sdept FROM Student
WHERE Sdept IN(SELECT Sdept FROM Student WHERE Sname='钟文辉')

/*
--子查询进行比较测试
	语句：WHERE <列名> 比较运算符 (子查询)
*/
--查询选了C04且成绩高于平均成绩的学生
SELECT Sno,Grade FROM SC
WHERE Cno='C04' AND Grade > (SELECT AVG(Grade) FROM SC WHERE Cno='C04')
/*
带有any和all的子查询
*/
--查询比C03成绩高且选了C04的学生
SELECT Sno,Grade FROM SC
WHERE Cno='C04' AND Grade > ALL 
(SELECT Grade FROM SC WHERE Cno='C03')
/*
带有EXISTS的子查询
存在量词
语句：
	WHERE [NOT] EXISTS (子查询)--有满足条件的值返回真；否则返回假
*/
--查询选了C04的学生
SELECT Sname FROM Student
WHERE EXISTS(SELECT * FROM SC WHERE SC.Sno=Student.Sno AND Cno='C04')
--查询至少选择了第三学期开设的全部课程的学生
SELECT Sname FROM Student
WHERE NOT EXISTS
(SELECT * FROM Course WHERE Semester=3 AND NOT EXISTS
(SELECT * FROM SC WHERE SC.Sno=Student.Sno AND Course.Cno=SC.Cno))
/*
查询的集合运算
UNION--并
INTERSECT--交
EXCEPT--差
*/
--查询计算机系和机电系所有学生
(SELECT Sno,Sname,Ssex,Sdept
FROM Student WHERE Sdept='计算机系')
UNION
(SELECT Sno,Sname,Ssex,Sdept
FROM Student WHERE Sdept='机电系')
--查询同时选了C03，C04的学生
(SELECT Sno FROM SC WHERE Cno='C03')
INTERSECT
(SELECT Sno FROM SC WHERE Cno='C04')
--查询选了C01但没C02的学生
(SELECT Sno FROM SC WHERE Cno='C01')
EXCEPT
(SELECT Sno FROM SC WHERE Cno='C02')
/*
视图
创建语句：
	CREATE VIEW <视图名> [(列名[,...n])]
	AS
		SELECT 语句--不包含ORDER/DISTINCT 语句
修改语句：
	ALTER VIEW <视图名> [(列名[,...n])]
	AS
		SELECT 语句--不包含ORDER/DISTINCT 语句
删除视图：
DROP VIEW <视图名>
*/
--创建一个包含计算机系的成绩视图
CREATE VIEW V_Grade_CS
AS
	SELECT S.Sno,Sname,C.Cno,Cname,Grade
	FROM Student S,SC,Course C
	WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno
		AND Sdept='计算机系'
--修改上一个视图的年龄
ALTER VIEW V_Grade_CS
	AS
		SELECT S.Sno,Sname,YEAR(GETDATE())-YEAR(Sbirthday) Sage,C.Cno,Cname,Grade
	FROM Student S,SC,Course C
	WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno
		AND Sdept='计算机系'
--删除上一个视图
DROP VIEW V_Grade_CS
/*
数据更改
增：
	单行插入语句：INSERT [INTO] <表名> [(<列表名>)] VALUES (值列表)
	多行插入语句：INSERT [INTO] <表名> [(<列表名>)] SELECT 语句--将查询结果插入到表中
删：
	删除语句：
	DELETE [FROM] <表名>
	[FROM <条件表名> [,...n]]
	[WHERE <更新条件> ]
--1、无条件删除
--2、有条件删除
		1\基于本表条件删除
		2\基于其他表条件删除
改：
	更新语句：UPDATE <表名> SET <列名> = {表达式| DEFAULT|NULL} [,...n]
	[FROM <条件表名> [,...n]]
	[WHERE <更新条件> ]
--1、无条件更新
--2、有条件更新
		1\基于本表条件更新
		2\基于其他表条件更新
*/
--1、向student表插入（050101，赵林，男，1999-09-08，计算机系）
INSERT INTO Student VALUES (050101,赵林,男,1999-09-08,计算机系,NULL)
--2、新建StudentBAK表，向StudentBAK表添加Student计算机系学生信息
CREATE TABLE StudentBAK
( 
	Sn CHAR(6) PRIMARY KEY,
	Sname NVARCHAR(20),
	Sdept NVARCHAR(20),
)
INSERT INTO StudentBAK
SELECT Sno,Sname,Sdept FROM Student WHERE Sdept='计算机系'
--2、将Student_CS表学生年龄加1
UPDATE Student_CS SET Sage+1
--3、将C04学分加1
UPDATE Course SET Credit=Credit+1
	WHERE Cno='C04'
--4、将数据库原理成绩减3
--1\子查询
UPDATE SC SET Grade=Grade-3
	WHERE Cno IN
(SELECT Cno FROM Course WHERE Cname='数据库原理')
--2\多表连接
UPDATE SC SET Grade=Grade-3
FROM SC JOIN Course ON SC.Cno=Course.Cno
WHERE Cname='数据库原理'
INSERT INTO StudentBAK
SELECT Sno,Sname,Sdept FROM Student WHERE Sdept='计算机系'
--5、将Student_CS数据删除
DELETE FROM Student_CS
--6、将StudentBAK表学生050101删除
DELETE StudentBAK WHERE Sno='050101'
--4、将数据库原理选课记录
--1\子查询
DELETE FROM SC
	WHERE Cno IN
(SELECT Cno FROM Course WHERE Cname='数据库原理')
--2\多表连接
DELETE FROM SC
FROM SC JOIN Course ON SC.Cno=Course.Cno
WHERE Cname='数据库原理'
/*
数据控制
授权：
	语句：
	GRANT <权限> [,<权限>] ...
	[ON <对象类型> <对象名>]
	TO<用户> [,<用户>]...
	[WITH GRANT OPTION] [AS用户];
回收授权：
	语句：
	REVOKE [GRANT OPTION FOR] <权限> [,<权限>] ...
	[ON <对象类型> <对象名>]
	TO<用户> [,<用户>]...
	[CASCANDE] [AS用户];
拒权:
	语句：
	DENY <权限> [,<权限>] ...
	[ON <对象类型> <对象名>]
	TO<用户> [,<用户>]...;
*/
--给Mary和John授权创建数据库和表的权限
GRANT CREATE DATABASE,CREATE TABLE
TO Mary,John
--给Mary和John回收创建数据库和表的权限
REVOKE CREATE DATABASE,CREATE TABLE
TO Mary,John
--拒绝给Mary和John授权创建数据库和表的权限
DENY CREATE DATABASE,CREATE TABLE
TO Mary,John