SQL查询语句用法及实例资料课件.pptx

1、以下查询基于这3张表进行操作。Create Table S(sno varchar(10) Primary Key,sname varchar(10) not null,sex varchar(2),birthday datetime,sdeptartment varchar(100),Check (Sex=男 or Sex=女)Create Table C(cnovarchar(10) Primary Key,cnamevarchar(100) not null,teachervarchar(10)Create Table SC(snovarchar(10),cnovarchar(10),g

2、radesmallint,Primary Key(sno, cno) 格式： Select column_name ,n From 例如：从学生表中输出学号、姓名和性别。使用语句：例如：从学生表中输出学号、姓名和性别。使用语句： Select sno, sname, sex From S 通过这个查询可以看出SQL语句操作的是数据集合。 让输出的结果以一定的顺序排序，可以给输出的列重新指定列名，使用的Select语句为： Select column_name As new_ column_name,n From Order By Asc|Desc ,n Select sno as 学号, sn

3、ame as 姓名, sex as 性别 From S Order By sex Asc, sno Desc 列出表S中学生的学号和出生年份情况。 列出表S中学生的学号和出生年份情况。 Select sno, year(birthday) as 出生年份 From S Order By 出生年份 Asc year()返回日期所对应的年份。 Distinct关键字出去重复的列名记录，All关键字输出所有的查询结果，默认时为All。 Select Distinct sdeptartment From S 列出表SC中选修了课程的学生学号。 列出表SC中选修了课程的学生学号。 Select Dist

4、inct sno From SC 指定只返回前面一定数量的数据。当查询到的数据量非常庞大，但没有必要对所有数据进行浏览时，使用Top关键字可以大大减少查询花费的时间。 Select Top integer|Top integer Percent column_name,n From tablename Select Top 5 sno, sname, sex From S Select Top 5 percent sno, sname, sex From S 查询SC表中学号为“00011101”的同学的成绩为最高的课程号。 查询SC表中学号为“00011101”的同学的成绩为最高的课程号。

5、Select Top 1 sno, cno, grade From SC Where sno=00011101 Order By grade Desc =、 、=、!=、! 列出表SC中成绩在大于等于87分的学生学号和其选修的课程号。 Select sno,cno From SC Where Grade=87 或 Select sno,cno From SC Where Grade!87 列出表S中“汉语言文学”系年龄小于27岁的学生的学号和出生年份情况。 列出表S中“汉语言文学”系年龄小于27岁的学生的学号和出生年份情况。 Select sno, year(birthday) From S

6、Where sdeptartment=汉语言文学 and (year(getdate()-year(birthday)27 函数getdate()返回当前机器日期 常用的聚合函数有：Sum、Avg、Count、Max、Min Select Count(*) From S Select Count(distinct sdeptartment) From S 查询选修了课程的学生人数 查询选修了课程的学生人数 Select Count(Distinct sno) From SC 计算1号课程的学生平均成绩 计算1号课程的学生平均成绩 Select AVG(Grade) From SC Where

7、Cno=1 查询选修1号课程的学生最高分数 查询选修1号课程的学生最高分数 Select MAX(Grade) From SC Where cno=1 查询组数据的属性，Group By子句为数据分组，然后按每组数据返回查询结果。 Select Sex, Count(*) From S Group By Sex 使用Group By子句后，Select子句的列名列表只能出现分组属性和集函数。 比如：Select sno,cno From SC Group By cno 是错误的。 求选修了各课程的学生人数 求选修了各课程的学生人数 Select cno, count(*) From SC Gr

8、oup By cno 一般的条件查询。 Select column_name ,n From tablename Where Select * From S Where Sex=女 Like关键字指示在搜索条件中相匹配的模式，Like前也可以前置Not，返回不匹配的所有行。语法形式为： match_expression Not Like pattern 如果模式表达式match_expression匹配模式pattern，则返回True，否则返回False。模式pattern通常被定义为字符串和通配符，通配符主要有： %：匹配0个或多个字符的字符串 _：匹配任何单个的字符 ：排列通配符，匹配任

9、何在范围或集合中的单个字符。如a-z，az的所有单个字符。 ：排除通配符，匹配不在范围或集合中的单个字符。如q-t，除qt的任何字符。 在表S中查询“计算机科学与技术”系学生的姓名。 Select sname as 姓名, sdeptartment as 所在系 From S Where sdeptartment Like 计算机科学与技术Select * From S Where sname Like 王%Select * From S Where sname Like 王_Select * From S Where sname Like %王% 当用户要查询的字符串本身就含有%和_时，要使

10、用Escape短语对通配符进行转义。 查询DB_Design课程的所有信息 Select * From C Where cname Like DB_Design Escape 如查询包含5%的字符串可以写成： Select * From C Where cname Like %5% Escape 或 Select * From C Where cname Like %5% 用于组或者聚合函数的搜索条件。它常用于GROUP BY子句后，用于搜索条件必须在结果分组之后测试的情况。如果没有GROUP BY子句，则HAVING仅在选择列表用于聚合函数。 语法结构为： HAVING 在 HAVING 子

11、句中不能使用 text、image 和 ntext 数据类型。 查询90分以上的学生的学号及课程数。 查询90分以上的学生的学号及课程数。 Select sno, Count(*) as 课程数 From SC Where Grade = 90 Group By sno 列出选修课程超过3门的学生学号及选修门数 列出选修课程超过3门的学生学号及选修门数 Select sno, count(*) From SC Group By sno Having count(*) 3 查询有三门以上课程是90分以上的学生的学号及课程数。 查询有三门以上课程是90分以上的学生的学号及课程数。 Select s

12、no, Count(*) as 课程数 From SC Where Grade = 90 Group By sno Having Count(*)=3 用于搜索条件判定所给的测试表达式是否与子查询或者值列表中的任何值匹配，如果在子查询或者值列表中发现了与测试表达式匹配的结果，则返回TRUE值，NOT IN返回IN的否定结果。 查询“数学与应用数学”与“计算机科学与技术”系的学生名单。 查询“数学与应用数学”与“计算机科学与技术”系的学生名单。 Select sno, sname From S Where sdeptartment in(数学与应用数学,计算机科学与技术) 查询选修了 “C01”

13、号课程的学生学号和姓名。 查询选修了 “C01”号课程的学生学号和姓名。 Select sno, sname From S Where sno in( Select sno From SC Where cno=C01) 查询叶芬琴同学的学号及所选修的课程号。 查询叶芬琴同学的学号及所选修的课程号。 Select sno, cno From SC Where sno in(Select sno From S Where sname=叶芬琴) 这里in可用=代替 查询没有选修C02课程的学生姓名。 查询没有选修C02课程的学生姓名。 Select sname From S Where sno no

14、t in(Select sno From SC Where cno=C02) 规定一个范围，在搜索条件中测试，如果测试表达式的值在范围中返回TRUE；若前置NOT，则返回否定的结果。 查询成绩在60-100分之间的学生学号。 查询成绩在60-100分之间的学生学号。 Select sno From SC Where Grade Between 60 and 100 用来测试跟随的子查询中现有的行。如果某些行满足子查询，则返回TRUE。 查询所有选修了“C01”号课程的学生姓名。 Select sname From S Where Exists ( Select * From SC Where

15、sno=s.sno and cno=C01) 查询所有选修了“C01”号课程的学生姓名。 Select sname From S Where sno in ( Select sno From SC Where cno=C01) 检索没有选修课程“数据库”的学生姓名和年龄。 检索没有选修课程“数据库”的学生姓名和年龄。 Select sname,year(getdate() - year(birthday) From S where not Exists( Select * From SC Where S.sno = SC.sno and SC.cno=(Select cno From C wh

16、ere cname=数据库) 当设置datetime或smalldatetime值时，如果只指定时间，则日期默认为1900年1月1日。如果只指定日期，则时间默认为午夜。 若要搜寻日期或时间的一部分，请使用Like运算符。SQL Server首先把数据转换为datetime格式，然后再转换为varchar格式。 SQL Server可以识别下列括在单引号中的日期和时间格式。 4/15/1998 、1978-01-05、19981207 在基本表S中检索1982-3-12出生的学生情况。 在基本表S中检索1982-3-12出生的学生情况。 Select * From S Where Birthda

17、y=1982-3-12 在基本表S中检索出生日期包含17日的学生情况。 在基本表S中检索出生日期包含17日的学生情况。 Select * From S Where birthday like %17% 在基本表S中检索在19801982年之间出生的学生情况。 在基本表S中检索在19801982年之间出生的学生情况。 Select * From S Where year(birthday)1980 and year(birthday)1982 用户还可以把两个或多个表连接起来一起进行多表查询。支持的连接操作有：内连接、左外连接、右外连接、全外连接、交叉连接。 是默认的连接类型，它指定只有与ON条

18、件匹配的表行才能包含在结果集中，不匹配的行丢弃。语法格式为： Select column_name,n From tablenameA INNER JOIN tablenameB ON 查询选修了C01号课程的学生学号和姓名。 查询选修了C01号课程的学生学号和姓名。 Select S.sno, sname From S inner Join SC on S.sno=SC.sno Where SC.cno=C01 或 Select S.sno, sname From S Join SC on S.sno=SC.sno Where SC.cno=C01 查询选修了C01号课程的学生学号和姓名。

19、Select S.sno, sname From S Join SC on S.sno=SC.sno Where SC.cno=C01 等同于 Select S.sno, sname From S, SC Where S.sno=SC.sno and SC.cno=C01 WHERE子句中使用的连接语句，被称为隐性连接，INNER JOIN子句产生的连接称为显性连接 查询“Liu”老师所教课程的学生学号、姓名和成绩。 查询“Liu”老师所教课程的学生学号、姓名和成绩。 Select S.sno, sname, grade From (S inner Join SC on S.sno=SC.sn

20、o) inner Join C On SC.cno=C.cno) where C.teacher=Liu 查询“Liu”老师所教课程的学生学号、姓名和成绩。 Select S.sno, sname, grade From (S inner Join SC on S.sno=SC.sno) inner Join C On SC.cno=C.cno) Where C.teacher=Liu 或 Select S.sno, sname, grade From S, SC, C Where S.sno=SC.sno and SC.cno=C.cno and C.teacher=Liu 左表中的记录全部

21、会出现在结果集中，匹配不上的显示NULL。语法格式为： Select column_name,n From tablenameA Left Outer JOIN tablenameB ON 检索每个学生的姓名、选修课程号和成绩，没有选修的同学也列出。 检索每个学生的姓名、选修课程号和成绩，没有选修的同学也列出。 Select S.sname, SC.cno, grade From S Left Join SC on S.sno=sc.sno 右表中的记录全部会出现在结果集中，匹配不上的显示NULL。语法格式为： Select column_name,n From tablenameA Righ

22、t Outer JOIN tablenameB ON 检索每个学生的姓名、选修课程号和成绩，没有选修的同学也列出。 检索每个学生的姓名、选修课程号和成绩，没有选修的同学也列出。 Select S.sname, SC.cno, grade From SC Right Join S on S.sno=sc.sno 返回两个表中的匹配和不匹配的所有记录。 Select column_name,n From tablenameA FULL Outer JOIN tablenameB ON 检索每个学生的姓名和选修课程号，没有选修的同学和没有被选修的课程也列出。 检索每个学生的姓名和选修课程号，没有选修

23、的同学和没有被选修的课程也列出。 Select S.sname, C.cno From (S Full Join SC on S.sno=SC.sno) Full Join C on SC.cno=C.cno) 不能加筛选条件，即不能带Where子句。结果表是第一个表的每行与第二个表的每行拼接后形成的表，结果表的行数等于两个表行数之积。 Select column_name,n From tablenameA CROSS JOIN tablenameB 列出所有学生所有可能选课的情况。 列出所有学生所有可能选课的情况。 Select S.sname, C.cno From S Cross Jo

24、in C 等同于 Select S.sname, C.cno From S, C Select A.column_name, B. column_name,n From tablenameA as A, tablenameA as B Where 查询与“管丹萍”在同一个系学习的学生。 查询与“管丹萍”在同一个系学习的学生。 Select A.* From S as A, S as B Where A.sdeptartment=B.sdeptartment and B.sname=管丹萍 查询至少选修课程号为C01和C03的学生学号。 查询至少选修课程号为C01和C03的学生学号。 Selec

25、t A.sno From SC as A, SC as B Where A.cno=C01 and B.cno=C03 and A.sno=B.sno 数据库表如下： Product(Maker, Model, Type) PC(Model, Speed, RAM, HD, CD, Price) LapTop(Model, Speed, RAM, Screen, Price) Printer(Model, Color, Type, Price) 找出价格在8000元以下的PC机的型号、内存容量和硬盘容量。 找出价格在8000元以下的PC机的型号、内存容量和硬盘容量。 Select Model,

26、 RAM, HD From PC Where Price 8000 找出生产打印机的所有厂家。 找出生产打印机的所有厂家。 Select * From Product Where Type=“Printer” 找出那些或者是24X或者是48X、并且价格低于1000元的CD。 找出那些或者是24X或者是48X、并且价格低于1000元的CD。 找出那些或者是24X或者是48X、并且价格低于1000元的CD。 Select CD From PC Where (CD=“24X” or CD=“48X”) and Price =450 and PC. Model=Product.Model and Ty

27、pe=“PC” 找出价格最高的打印机。 找出价格最高的打印机。 Select Top 1 * From Printer Order By Price Desc 找出其运行速度低于PC的LapTop的所有元组。 找出其运行速度低于PC的LapTop的所有元组。 Select * From LapTop Where Speed 2) 找出每一个生产厂商的PC机的最高价格。 找出每一个生产厂商的PC机的最高价格。 Select Maker, Max(Price) From Product,PC Where Product.Model=PC.Model and Product.Type=“PC” Group By Maker 计算生产打印机的厂商所生产的PC机的硬盘的平均价格。 计算生产打印机的厂商所生产的PC机的硬盘的平均容量。 Select Maker, AVG(HD) From PC, Product Where Product.Model=PC.Model and Product.Type=“PC” and Maker in (Select Maker From Product Where Type=“Printer”)Group By Maker