实用数据库教程--第5章 数据查询与更新

第 5 章 数据查询与更新
浙江工业大学 张端
5.1.1 概述
5.1.2 SELECT子句
Select后列的内容 * 列名 表达式, 聚集函数 系统函数
SELECT CNAME AS 课程号 FROM course
s_c表中含有同一名学生选多门课，以下示例显示了 查询选课的学生学号。 SELECT DISTINCT SNO FROM s_c ORDER BY SNO; --DISTINCT表示不重复显示
串函数 Select substring('wardrobe',1,4); ='ward'---取子串 Select stuff('Notebook',1,4,'My'); ='Mybook'
系统函数 select datalength(地址)--返回列中字段长度 from student; select host_id();--返回服务器id select host_name();--返回服务器名
3. in 和Between 在只有s1,s2,s3,s4这4个同学的情况下，下面3个 SQL结果是一致的 Select * From student Where SNO in ('s1','s2','s3'); In后的()中是一个集合 Select * From student Where SNO Between 's1' And 's3' 注意Between与and的配合 Select * From student Where SNO>='s1' and SNO<='s3'
时间函数 select getdate() 系统时间 datepart(item, date) 返回时间的一部分
item : yy年，mm第几月，dd月中第几日，dw 星期几，wk一年中第几星期，hh小时，mi分，ss秒， ms毫秒 datediff(item, startdate , enddate) 时间 差
As 在From子句中的应用： SELECT * FROM student; Go SELECT * FROM student as s; Go SELECT * FROM student s; Go
5.1.4 where 子句
查询课程c2成绩，列出学号、课号，成绩 SELECT SNO, CNO, GRADE FROM s_c WHERE CNO = 'c2';
round(n,p,t)四舍五入，p表示舍入的位置，t可选 t=0时四舍五入，当t<>0时表示截尾，缺省t=0。 select round(751.326,2)
=751.330 select round(751.326,-1)
=750.000 select round(751.326,2,9)
=751.320 select SNO,CNO,round(sqrt(GRADE)*10,1) from s_c;
数值函数： abs(x), ceiling(x)向上取整, floor(x) 向下 取整,power(x,y) x的y次方, sin(x), cos(x), asin(x)反正弦, acos(x), sign(x)符号函数, sqrt(x)平方根(square rooting), square(x) 平方, round(n,p,t)
临时表 创建临时表并把查询结果 select * from student 放入临时表#temp_t1中 select * into #temp_t1 from student go select * from #temp_t1 go 临时表#temp_t1放在tmepdb数据库中
5.1.3 From 子句
3. 多个表的自然联接---找出学生学号、姓名、所选 课名和成绩 Select s.SNO, s.SNAME, AME, s_c.GRADE From student s, course c,s_c Where s.SNO=s_c.SNO and O=s_O 或 Select s.SNO, s.SNAME, AME, s_c.GRADE From s_c join student as s on s.SNO=s_c.SNO
数值列或常量使用算术运算符或函数进行的计算和运 算。 查询同学s2的c1课程成绩 SELECT GRADE FROM s_c WHERE SNO='s2' AND CNO='c1'; GO SELECT GRADE+15 AS GradeUpdate FROM s_c WHERE SNO='s2' AND CNO='c1'; GO
SELECT 语句将从 course 表中返回 3门学分最多 的课程。 SELECT TOP 3 CNO, CNAME, CREDIT FROM course ORDER BY CREDIT DESC go SELECT TOP 3 CNO, CNAME, CREDIT FROM course Go ------TOP ( expression ) [ PERCENT ] [ WITH TIES ] 其中，expression 是指定返回行数的数值表达式， 如果指定了 PERCENT，则是指返回的结果集行的百 分比(由 expression 指定)。
或 Select * From student cross join s_c
2. 自然联接: 找出学生的学号、姓名、所选课号和成绩 Select student.SNO, student.SNAME, s_O, s_c.GRADE From student, s_c Where student.SNO=s_c.SNO 或 Select s.SNO, s.SNAME, s_O, s_c.GRADE From student s, s_c Where s.SNO=s_c.SNO 或 Select s.SNO, s.SNAME, s_O, s_c.GRADE From student as s join s_c on s.SNO=s_c.SNO
5.1.8 联接查询
联接查询是 From 子句中涉及多个表的查询： 笛卡尔积 自然联接 Theta 联接
1. 笛卡尔积：两个表(集合)相乘。
学生表和课程表的笛卡尔积 Select * From student, course
学生表和选课表的笛卡尔积 Select * From student, s_c
找出平均分大于等于75分的同学 SELECT SNO, AVG(GRADE) AS avg_grade FROM s_c GROUP BY SNO HAVING AVG(GRADE)>=75 ORDER BY SNO ; 比较：找出剔除小于 70 分的科目后，平均分大于等 于 75 分的同学 SELECT SNO, AVG(GRADE) AS avg_grade FROM s_c where Grade>=70--对单条记录的筛选 GROUP BY SNO HAVING AVG(GRADE)>=75--对一个学生的筛选 ORDER BY SNO ;
5.1.5 order by 子句
按SNO升序，CNO降序列出s_c的所有记录 SELECT * FROM s_c ORDER BY SNO ASC, CNO DESC; 或 SELECT * FROM s_c ORDER BY 1 ASC, 2 DESC;
5.1.6 group by 子句
Group by把一列或多列，这一组在Group by定义 的组内相同。因此在使用Group by的查询中select 后或者出现Group by已出现的列或者出现组内的聚 集函数 在s_c中以SNO分组， Select SNO From s_c Group by SNO 在s_c中查询每个学生的平均成绩 SELECT SNO, AVG(GRADE) AS avg_grade FROM s_c WHERE GRADE>=60 GROUP BY SNO ORDER BY SNO; 以下语句出错 SELECT SNO, GRADE
1. 比较运算符： =，<>或!=不等于, <,>,<=,>=,!>不大于,!< 不 小于 这些符号不光适用于数字，还可用于字符串 找出同学s1的基本信息 Select * From student Where SNO='s1';
2. 逻辑运算符： NOT非，AND与，OR或 找出学号大于s1小于s4的同学的基本信息 Select * From student Where SNO>'s1' AND SNO<'s4'
4. 表达式
dateadd(item,number, date)返回date之后的 number个item单位后的时间
CREDIT*18(表示课时)表CREDIT*18 AS CREDIT_HOUR FROM course
select datediff(ss,'2008-10-20 10:30:30.252','2008-09-05 10:31:31.255'); select dateadd(hh,10,getdate());
FROM s_c WHERE GRADE>=60 GROUP BY SNO ORDER BY SNO; 再对比如下语言，它表示所有学生和课程的平均成绩 SELECT AVG(GRADE) AS avg_grade FROM s_c;
5.1.7 having 子句
格式：HAVING 条件 修饰GROUP by子句, 条件中如果用到不出现在 GROUP by子句中的列只能用这个列的聚集函数。
%--代表任何字符串 _ --代表任意单个字符 ‘aabb%cc__dd%’则 ‘aabbyuioccjhdd’符合模式