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 ] ]
2、选择表中的若干列
(1)查询指定列
例1:查询全体学生的学号和姓名
SELECT Sno, Sname FROM Student
例2:查询全体学生的姓名、学号、所在系,查询结果列改名
SELECT Sname AS 姓名, Sno 学号, 所在系 = Sdept FROM Student
(2)查询全部列
例3:查询Student表的全部列
SELECT * FROM Student
(3)查询经过计算的列
例4:查询全体学生的姓名及其出生年份
--GETDATE():得到系统的当前日期;YEAR():得到指定日期的年号。
SELECT Sname, YEAR(GETDATE()) – Sage AS 出生年份
FROM Student
3、选择表中的若干元组
(1)取消重复元组
使用DISTINCT短语,可以让相同的元组只显示一个
例5: 查询选修了课程的学生学号
SELECT Sno FROM SC
(2)比较大小
查询满足条件的元组:查询满足指定条件的元组可以通过WHERE子句实现;
例6:查询计算机系全体学生的名单
SELECT Sname FROM Student WHERE Sdept = 'CS'
例7:查询所有年龄在20岁以下的学生姓名及其年龄
SELECT Sname, Sage FROM Student WHERE Sage < 20
例8: 查询考试成绩有不及格的学生的学号
SELECT DISTINCT Sno FROM SC WHERE Grade < 60
(2)确定范围
例9: 查询年龄在20至23岁之间的学生的姓名、系别、和年龄
SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23
SELECT Sname, Sdept, Sage FROM Student WHERE Sage >= 20 AND Sage <= 23
例10: 查询年龄不在20至23岁之间的学生的姓名、系别、和年龄
SELECT Sname, Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23
SELECT Sname, Sdept, Sage FROM Student WHERE Sage < 20 OR Sage > 23
(3)确定集合
例11: 查询信息系(IS)和计算机科学系(CS)的学生的姓名和性别
SELECT Sname, Ssex FROM Student WHERE Sdept IN('IS', 'CS')
例12: 查询既不是信息系(IS),也不是计算机系(CS)的学生的姓名和性别
SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN('IS', 'CS')
(4)字符匹配
谓词LIKE用来进行全部或部分字符串匹配。
在进行部分字符串匹配时要用通配符“%”和“_”,
“%”匹配0个或多个字符,
“_”匹配1个字符,查询时使用LIKE和通配符,可以实现模糊查询。
例13:查询学号为2017001的学生的情况
--注:若LIKE后面的匹配串不含通配时,则可以用“=”运算符取代谓词LIKE。
SELECT * FROM Student WHERE Sno LIKE '2017001'
SELECT * FROM Student WHERE Sno = '2017001'
例14:查询所有姓“刘”的学生的姓名、学号和性别
SELECT Sname, Sno, Ssex FROM Student
WHERE Sname LIKE '刘%'
例15: 查询所有不姓“刘”的学生姓名
SELECT Sname FROM Student WHERE Sname NOT LIKE '刘%'
例16:查询名字中第二字为“桃”字的学生的姓名和学号
SELECT Sname, Sno FROM Student
WHERE Sname LIKE '_桃%'
例17:查找姓氏在李与王之间的学生信息
SELECT * FROM Student
WHERE Sname LIKE '[李-王]%'
例18:查询DB_Design课程的课程号和学分
通过ESCAPE指定“\”字符是一个转义字符。
SELECT Cno, Ccredit FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\'
(5)涉及空值的查询
例19:查询选修了课程但没有成绩的学生学号和课程号
SELECT Sno, Cno FROM SC
WHERE Grade IS NULL
例20:查询所有有成绩的学生学号和课程号
SELECT Sno, Cno FROM SC
WHERE Grade IS NOT NULL
(6)多重条件查询
使用逻辑运算符AND和OR来联结多个查询条件,AND的优先级高于OR。
例21:查询CS系年龄在20岁以下的学生姓名
SELECT Sname, Ssex FROM Student
WHERE Sdept = 'CS' AND Sage < 20
扩展:SQL注入
用户名:' OR 1=1 --'
密 码:随意
SELECT COUNT(*) FROM Users
WHERE userName = '' OR 1=1 --'' and userPwd = 'AA'
用户名:随意
密 码:' OR ''='
SELECT COUNT(*) FROM Users
WHERE userName = 'AA' and userPwd = '' or ''=''
4、对查询结果排序
如果没有指定查询结果的显示顺序,DBMS将按其最方便的顺序(通常是元组在数据表中的先后顺序)输出查询结果;
用户也可以用ORDER BY子句指定按照一个或多个属性列的升序(ASC)或降序(DESC)重新排列查询结果,其中升序ASC为缺省值;
例22:查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列
SELECT Sno, Grade FROM SC
WHERE Cno = '3'
ORDER BY Grade DESC
例23:查询全体学生情况,查询结果按所在系升序排列,对同一系中的学生按年龄降序排列
SELECT * FROM Student
ORDER BY Sdept, Sage DESC
5、使用集函数
SQL提供了许多集函数,主要包括:SUM、COUNT、MAX、MIN、AVG。
例24:查询学生总人数
SELECT COUNT(*) FROM Student
例25:查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno) FROM SC
例26:计算1号课程的学生平均成绩
SELECT AVG(Grade) FROM SC
WHERE Cno = '1'
例27: 查询学习1号课程的学生最高分数
SELECT MAX(Grade) FROM SC
WHERE Cno = '1'
6、对查询结果分组
GROUP BY子句可以将查询结果表的各行按一列或多列值分组,值相等的为一组。
对查询结果分组的目的是为了细化集函数的作用对象。
如果未对查询结果分组,集函数将作用于整个查询结果,即整个查询结果只有一个函数值。否则,集函数将作用于每一个组,即每一组都有一个函数值。
WHERE子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或视图,从中选择满足条件的元组。HAVING短语作用于组,从中选择满足条件的组。
例28:查询各个课程号与相应的选课人数
SELECT Cno, COUNT(Sno) FROM SC
GROUP BY Cno
例29:查询选修了2门以上课程的学生的学号
先用GROUP BY子句按Sno分组,再用集函数COUNT对每一组计数。HAVING短语指定选择组的条件,只有满足条件(即元组个数>3,表示此学生选修的课超过3门)的组才会被选出来。
SELECT Sno FROM SC
GROUP BY Sno
HAVING COUNT(*) > 2
三、连接查询
若一个查询同时涉及两个以上的表,则称之为连接查询;
连接查询主要包括:① 等值连接、非等值连接查询;② 自身连接查询;③ 外连接查询;④ 复合条件连接查询;
1、等值与非等值连接
例30:查询每个学生及其选修课程的情况
SELECT Student.*, SC.* FROM Student, SC
WHERE Student.Sno = SC.Sno
等价于:
SELECT Student.*, SC.*
FROM Student INNER JOIN SC ON Student.Sno = SC.Sno
例31:自然连接Student和SC表
若属性列在两个表中是唯一的,可以省略表名前缀。
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC WHERE Student.Sno = SC.Sno
2、自身连接
例32:查询每一门课的间接先修课(即先修课的先修课)
SELECT A.Cno, B.Cpno
FROM Course A, Course B
WHERE A.Cpno = B.Cno
3、外连接
内连接 INNER JOIN:结果集仅包含那些满足连接条件的数据行;使用INNER JOIN 连接运算符,使用ON 关键字指定连接条件。
左外连接 LEFT [OUTER] JOIN:左外连接对连接条件中左边的表不加限制;
右外连接 RIGHT [OUTER] JOIN:右外连接对连接条件中右边的表不加限制;
全外连接 FULL [OUTER] JOIN:全外连接对两个表都不加限制,所有两个表中的行都会包括在结果集中;
交叉连接 CROSS JOIN:称为笛卡尔成绩,返回两个表的乘积,结果集中包含了所连接的两个表中所有行的全部组合;
例33:查询所有选修了1号的学生的姓名、课程号和成绩
SELECT Sname, Cno, Grade
FROM Student, SC
WHERE Student.Sno = SC.Sno AND Cno = '1'
--或者
SELECT Sname, Cno, Grade
FROM Student INNER JOIN SC
ON Student.Sno = SC.Sno AND Cno = '1'
例34:查询每个学生及其选修课程的情况(即使没有选课也列出该学生的基本情况)
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT JOIN SC
ON Student.Sno = SC.Sno
比较下面两条语句的区别
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT JOIN SC
ON Student.Sno = SC.Sno AND Cno = '1'
比较:
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT JOIN SC
ON Student.Sno = SC.Sno
WHERE Cno = '1'
例35:全外连接。查询每个学生及其选修课程的信息,包含未选课的学生 信息及未被选修的课程信息
SELECT Student.Sno, Sname, Course.Cno, Cpno, Ccredit, Cname, Grade
FROM Course FULL JOIN SC ON Course.Cno = SC.Cno
FULL JOIN Student ON Student.Sno = SC.Sno
4、复合条件连接
例36:查询选修2号课程且成绩在85分以上的所有学生
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND Cno = '2' AND Grade > 85
例37:查询每个学生及其选修的课程名及其成绩
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, Course, SC
WHERE Student.Sno = SC.Sno AND Course.Cno = SC.Cno
四、嵌套查询
在SQL语言中,一个SELECT-FROM-WHERE语句称为一个查询块。
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。
1、无关子查询
子查询的执行不依赖于外部查询;
子查询在外部查询之前执行,然后返回数据提供给外部查询使用;
子查询中不包含对于外部查询的任何引用;
子查询的SELECT 语句中不能使用ORDER BY子句,ORDER BY子句只能对最终查询结果进行排序;
例38:查询与“刘晨”在同一个系学习的学生
SELECT * FROM Student
WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname = '刘晨')
--注:“=”只能用于查询结果中只有一个元组的情况
SELECT * FROM Student
WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname = '刘晨')
例39:查询选修了课程名为“信息系统”的学生学号和姓名
SELECT Sno, Sname FROM Student WHERE Sno IN(
SELECT Sno FROM SC WHERE Cno IN(
SELECT Cno FROM Course WHERE Cname = '信息系统'
)
)
例40:查询所有未修1号课程的学生姓名
SELECT Sname FROM Student
WHERE Sno NOT IN(
SELECT Sno FROM SC WHERE Cno = '1'
)
2、相关子查询
子查询的执行依赖于外部查询,多数情况下是子查询的 WHERE 子句中引用了外部查询的表;
子查询的执行过程:(1)子查询为外部查询的每一行执行一次,外部查询将子查询引用的列的值传给子查询;(2)如果子查询的任何行与其匹配,外部查询就返回结果行;(3)再回到第一步,直到处理完外部表的每一行。
例41:查询成绩比该课的平均成绩低得学生的学号,课程号和成绩
SELECT Sno, Cno, Grade FROM SC A
WHERE Grade < (SELECT AVG(Grade) FROM SC B WHERE A.Cno = B.Cno)
例42:查询所有选修了1号课程的学生姓名
带有EXISTS谓词的子查询不返回任何实际数据,它只产生逻辑真值“true”或逻辑假值“false”。
SELECT Sname FROM Student WHERE EXISTS(
SELECT * FROM SC WHERE Sno = Student.Sno and Cno = '1')
或者
SELECT Sname FROM Student WHERE Sno IN(
SELECT Sno FROM SC WHERE Cno = '1'
)
或者
SELECT Sname FROM Student, SC
WHERE Student.Sno = SC.Sno AND SC.Cno = '1'
例43:查询所有未修1号课程的学生姓名
--用带IN谓词的子查询
SELECT Sname FROM Student WHERE Sno NOT IN(
SELECT Sno FROM SC WHERE Cno = '1'
)
--用带EXISTS谓词的子查询
SELECT Sname FROM Student WHERE NOT EXISTS(
SELECT * FROM SC WHERE SC.Sno = Student.Sno AND Cno = '1'
)
例44:查询选修了全部课程的学生姓名
SELECT Sname FROM Student WHERE NOT EXISTS(
SELECT * FROM Course WHERE NOT EXISTS(
SELECT * FROM SC WHERE Cno = Course.Cno
AND Sno = Student.Sno
)
)
五、集合查询
1、并UNION
例45:查询计算机科学系的学生及年龄小于19岁的学生
SELECT * FROM Student WHERE Sdept = 'CS'
UNION
SELECT * FROM Student WHERE Sage < 19
2、交INTERSECT
例46:查询计算机科学系年龄小于19岁的学生
SELECT * FROM Student WHERE Sdept = 'CS'
INTERSECT
SELECT * FROM Student WHERE Sage < 19
3、差EXCEPT
例47:查询计算机科学系年龄大于19岁的学生
SELECT * FROM Student WHERE Sdept = 'CS'
EXCEPT
SELECT * FROM Student WHERE Sage < 19
六、数据更新
1、插入数据
(1)插入一个元组
例48:将一个新学生记录(学号:2017007;姓名:王桃群;性别:女;所在系:CS;年龄:18岁)插入Student表中
INSERT INTO Student VALUES('2017007', '王桃群', '女', 'CS', 18)
例49:插入一条选课记录('2017007 ', '1')
INSERT INTO SC(Sno, Cno) VALUES('2017007', '1')
--或者
INSERT INTO SC VALUES('2017007', '1', NULL)(2)插入多个元组
例50:将每个系学生的平均年龄插入到DeptAge表中
--方法1:先创建DeptAvgAge表
CREATE TABLE DeptAvgAge(
Sdept CHAR(2),
AvgAge INT
)
INSERT INTO DeptAvgAge
SELECT Sdept, AVG(Sage)
FROM Student
GROUP BY Sdept
--方法2:不必先创建DeptAvgAge表
SELECT Sdept, AVG(Sage) AvgAge
INTO DeptAvgAge
FROM Student GROUP BY Sdept
比较下面语句的作用
SELECT * INTO TABLE2 FROM TABLE2 WHERE 1 = 2
INSERT TABLE2 SELECT * FROM TABLE1
SELECT * INTO TABLE2 FROM TABLE1
2、修改数据
例51:将学生2017007的年龄改为22岁
UPDATE Student
SET Sge = 22
WHERE Sno = '2017007'
例52:将计算机科学系全体学生的成绩置0
--方法1
UPDATE SC
SET Grade = 0
WHERE Sno IN(
SELECT Sno
FROM Student
WHERE Sdept = 'CS')
--方法2:
UPDATE SC
SET Grade = 0
FROM Student JOIN SC
ON Student.Sno = SC.Sno and Sdept = 'CS'
--方法3
UPDATE SC
SET Grade = 50
FROM Student, SC
WHERE Student.Sno = SC.Sno and Sdept = 'CS'
3、删除数据
例53:删除所有成绩不及格的选课记录
DELETE FROM SC WHERE Grade < 60
例54:删除所有的选课记录
DETELE FROM SC
--或者
TRUNCATE TABLE SC
SQL Server Manager Studio 查看数据 sql server怎么看数据库的数据
转载文章标签 Sage 子查询 元组 文章分类 SQL Server 数据库
-
sql server 数据库日志还原
角色是一个权限的集合,只要将用户加入到角色成员(给用户分配一个角色),就可以给这个用户分配这个角色所具有的全部权限。角色的出现极大的简化了权限的管理。
服务器角色 数据库角色 用户定义数据库角色 架构