数据库概论四

¶SQL操纵

¶单表查询

• 数据查询语句关键词

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] … FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句) [AS]<别名> [ WHERE <条件表达式> ] [ GROUP BY <列名1> [ HAVING <条件表达式> ] ] [ ORDER BY <列名2> [ ASC|DESC ] ];
Select 语句可以对列进行计算、使用聚合函数

#计算学生出生年份
select name,2019-age as birthday  from student;

Where可以用在连接条件,不能使用聚合函数
Group By 用于最终表
Having 用于分组筛选,并且可以使用聚合函数
order dy 用户最终排序，可以使用聚合函数

• 常见的查询条件

Like | NOT Like 使用%表示多个字符，——表示单个字符

#匹配姓 '刘'
select * from  student where name like  '刘%';
  

IN | NOT IN

#表示 计算机系 数学系 信息系
select * from student where dept not in ('is','ma','cs');
  

IS NULL |IS NOT NULL 作为条件是不能 使用=，update语句set x=null

• order by子句：该子句使用一条查询的最后，可按照多列排序

# 查询学生，结果由系号升序，同一系按年龄降序
select * from student order by dept ,age desc;
  

- 聚合函数,聚合函数不能使用在where条件中

- group by

如未分组，则聚合函数作用于整个结果
如分组，聚合函数则作用于每个分组
按照单列或者多列，值相同的分为一组
分组函数默认返回给查询的是分组中的第一个元组，分组多和聚合函数连用

#查询选课数大于1的学生
select * from sc group by sid having count(*)>1;
#使用条件子查询实现，注意<后一定要加括号
select * from student where  1< (select count(*) from sc where sc.sid=student.id);
#查询平均分大于90学生的学号和平均成绩
select name,avg(grade) from sc group by sid having avg(grade)>90;
#如果在where中使用聚合函数则是错误的
select name,avg(grade) from sc where avg(grade>90 group by sid;
  

¶连接查询

SELECT 列名 FROM 表名1,表名2.... WHERE 连接条件 AND 选择条件

• 连接的原理: 1.使用嵌套循环 2.排序归并 3.索引连接

#选修2号课程,且成绩在90分以上的所有学生 学号和姓名
select name,id from student,sc where student.id=sc.sid and cid=2 and grade >90;
  

• 自连接:使用于无限分级,该例子中的先修课

#查看每一门课程的先修课
select * from sc c ,sc p where c.pid=p.id;
#查看每一门课的后选课
select * from sc sc1 p,sc2 c where p.id=s.pid;
  

• 外连接
  LEFT JOIN … on| RIGHT JOIN …ON

#查看学生选课 (外连接)
SELECT * FROM student LEFT JOIN  sc ON student.id=sc.sid; 
 

• 多表连接

#查询每个学生学号,姓名,课程号,和成绩
SELECT student.id,name,course.id,grade FROM student,sc,course WHERE student.id=sc.sid AND sc.cid=course.id;
#不使用自然连接
SELECT student.id,name,course.id,grade FROM student left join sc on student.id=sc.sid left join course on course.id=sc.cid;
 

不明确使用连接语句并且不写出连接谓语的情况,连接结果就是nmj…数量个元组结果,连接谓语就是从这个结果集中进行筛选

¶子查询

子查询分为相关子查询和不相关子查询

• 带有in的子查询

#查询于'刘晨'一个系的学生
SELECT * FROM student WHERE dept IN (SELECT dept FROM student WHERE name ='刘晨');
#通过自连接完成
SELECT * FROM student s1,student s2 WHERE s1.name='刘晨' AND s1.dept=s2.dept;
#查询选修了'信息系统'的学生学号和姓名
SELECT student.id,student.name FROM student  WHERE id IN (SELECT sid FROM sc WHERE sc.cid=(SELECT course.id FROM course WHERE course.name='信息系统'));
#通过连接完成
SELECT * FROM student,sc,course WHERE student.id=sc.sid AND sc.cid=course.id AND course.name ='信息系统';
 

• 带有比较运算的子查询(>,<,-,>=,<=.!=,<>)

#找出每个学生超过他学修课平均成绩的课程
SELECT cid FROM sc sc1 WHERE sc1.grade >(SELECT AVG(grade) FROM sc sc2 WHERE  sc2.cid=sc1.id);

• 带有ANY,SOME,ALL的查询

-	ANY	SOME	ALL
> >=	最小值	最小值	最大值
< <=	最大值	最大值	最小值
=	任意值	任意值	-
<> !=	-	-	任意值

#查询非计算机科学系中比计算机科学系任意一个 学生年龄小的学生姓名和年龄 
SELECT name,age FROM student WHERE age <ANY(SELECT age FROM student WHERE dept='cs');
#使用聚合函数
SELECT name,age FROM student WHERE < (SELECT max(age) FROM student WHERE dept ='cs');

• 带有EXIST的子查询
  • 带有exists的谓语仅仅产生ture|false
  • 由EXISTS引起的子查询一般SELECT 使用*,因为列名一般无意义

#查询选修了1号课程的学生姓名
SELECT name FROM student WHERE EXISTS (SELECT * FROM sc WHERE sid=student.id AND cid=1);
#使用连接
SELECT name FROM student,sc WHERE student.id=sc.sid AND cid=1;
#查询选修了所有课程的学生
SELECT name FROM student WHERE NOT EXISTS (SELECT * FROM course WHERE NOT EXISTS (SELECT * FORM sc WHERE student.id=sc.sid AND sc.cid=courese.id));
#查看选择了A和B课程的学生
 select student.name,cid from sc sc1,student where student.id=sc1.sid and  exists (select * from sc sc2 where sc1.sid=sc2.sid and sc2.cid=1) and exists (select * from sc sc3 where sc3.sid=sc1.sid and  sc3.cid=2)
#查询列可以创建临时表
select * from sc a,(select * from sc  where cid=1)b,(select * from sc where cid=2)c where a.sid=b.sid and b.sid=c.sid;

¶数据更新

• 插入数据
  插入元组或通过子查询插入
  INSERT INTO <表名> [(<属性列1>[,<属性列2 >…)] VALUES (<常量1> [,<常量2>]… );

#创建系-平均年龄表并插入数据
creaete dept_age (dept int unique,age smallint)
insert into dept_age (select dept,avg(age) from student group by dept having dept is not null);

• 修改数据
  UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>]… [WHERE <条件>];
• 删除数据
  DELETE FROM <表名> [WHERE <条件>];
• NULL处理
  NOT NULL限制的列不能取空值
  UNIQUE属性不能取空值
  主码不能取空
  空值和其他值算术运算为NULL
  空值和其他值逻辑运算为UNKNOWN