数据库学习（三）：SQL

最新推荐文章于 2024-04-19 21:12:24 发布

原创最新推荐文章于 2024-04-19 21:12:24 发布 · 553 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

MySQL 专栏收录该内容

3 篇文章

订阅专栏

SQL

结构化查询语言

功能：

(1)数据查询：SELECT
(2)数据定义：CREATE、DROP(删除)、ALTER(修改，中有ADD)
(3)数据操纵：INSERT、DELETE、UPDATE(改)
(4)数据控制：GRANT(授权)、REVOKE(撤销)。

一个数据库管理实例中可以定义多个数据库，一个数据库中可建立多个模式，一个模式下可有多个表、索引、视图。

UPDATE,INSERT,DELETE是修改，插入，删除。

(行)UPDATE Student SET Sage=2;

而DROP,ALTER是删除修改结构。DROP Student;
字段(属性)也是结构，但是是对数据库的修改，所以要结合ALTER删除

ALTER Student DROP sno

查询的目标表达式还可以是算数表达式(2019-Sage),字符串常量(‘year of birth’),函数(LOWER(Sdept))
还可以别名输出列(Sname NAME)，(Sname AS NAME),(‘NAME’=Sname)
还可两列加起来以别名输出SELECT firstname+'.'+secondname AS name
DISTINCT去掉(投影)时重复的行SELECT DISTINCT Sno FROM SC; 若没有则默认为ALL
WHERE实现条件查询。常用查询条件。比较：>,<,=(等于)，!=与<>(不等于),!>,!<(不大于。不小于)
注意：where子句中不能用聚集函数当条件表达式的。
集合IN,NOT IN (可以用OR替代)WHERE Sdept IN ('cs','se') 。集合写在()里。区间BETWEEN AND,NOT BETWEEN AND(WHERE sno BETWEEN 10 AND 20)
逻辑AND,OR,NOT
字符匹配LIKE,NOT LIKE
(其中字符串中可以用通配符用%代表任意长度字符，代表1字节字符，可用\转义，用如[3-7]表示[]里中的一个，如[ ^ 1,2]表示不在[^]中的一个)LIKE ‘2017%’; 注意引号一定有，因为是匹配字符串。WHERE sname LIKE 'li'
- TOP限制固定行数输出
  前五行 SELECT TOP 5 Sname FROM Student;
  前20% SELECT TOP 20 PERCENT Sname FROM Student;
并操作UNION将多次查询结果取并输出，等价于逻辑OR
交操作INTERSECT将多次查询结果取并输出，等价于逻辑AND
差操作EXCEPT
ORDERED BY
将查询数据按一个或多个属性(按顺序)升序(ASC)或降序(DESC)，默认为升序。
SQL提供很多聚集函数：COUNT(),SUM(),AVG(),MAX(),MIN()。

SELECT MAX(sno) FROM Student；
GROUP BY
对查询结果进行分组，用于细化聚集函数结果，聚集函数的计算结果为分组后的每一组计算。
Having 短语给出选择组的条件。

连接查询

(写起来简单，但效率低。)如连接表a,b.过程是从a表第一个元组开始遍历，选定一组然后在b表从第一个元组开始遍历找到满足条件的元组。由于连接时遍历a表时，每次都要遍历b表，十分耗时。
自连接：备份两份表first,second,然后连接这两表。

子查询

查询嵌套
写起来比连接查询难到

典例

在这里插入图片描述

查询计算机系的学生与年龄不大于19岁的学生的差集

select * 
from Student 
where Sdept='cs' 
except 
select * 
from Student 
where Sage <= 19;

同

selsct * 
from Student 
where Sdept='cs' and Sage>19;

查询与刘晨在同一个系学习的学生

不相关子查询：子查询的结果不依赖于父查询

select *
from Student 
where Sdept in (select Sdept 
				from Student 
				where Sname='刘晨');

自身连接:

select S1.Sname,S1.Sdept 
from Student S1,Student S2 
where S1.Sdept=S2.Sdept and S2.Name='刘晨';

(这里不能直接用*，要分开如S1.*,S2.*因为连接是做笛卡尔乘积，会有重复列)

查询选修了课程名为"信息系统"的学生学号和姓名

select Sno,Sname 
from Student where Sno in (select Sno 
							from SC 
							where Cno in(select Cno 
												from Course 
												where Cname='信息系统'));

连接查询：

select Student.Sno,Sname 
from Student,SC,Course 
where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Course.Cname='信息系统';

查询每个学生超过他自己选修课程平均成绩的课程号。

相关子查询:

select Sno,Cno
from SC x
where Grade>(select avg(Grade) 
			from SC y where x.Sno=y.Sno);

查询非计算机系中比计算机系任意一个学生年龄小的学生姓名和年龄。

any:

select Sname,Sage
from Student
where Sdept !='cs' and Sage< any(select Sage 
								 from Student 
								 where Sdept ='cs');

查询非计算机系中比所有计算机系学生年龄小的学生姓名和年龄。

all:

  select Sname,Sage
    from Student
    where Sdept !='cs' and Sage< all(select Sage 
    								 from Student 
    								 where Sdept ='cs');

查询选修了至少一门与张力课程一样的学生的学号与姓名。

exists:

select Sno,Sname
from Student x
where exists
(select Cno from CS y 
where x.Sno=y.Sno and y.cno in (select Cno from CS 
					  where Sno in(select Sno from Student 
									where Sname='张力')));

只选修了数据库与数据结构两门课的学生的基本信息。

(注意是只，所以是and 或用 intersect 先分别找出选的是数据库的学生和选的是数据结构的学生再交)

select * 
from Student x
where Sno in(select Sno from SC 
			 where Cno in(select Cno from Course where Cname='数据库')) 
			 and
			 Sno in(select Sno from SC 
			 where Cno in(select Cno from Course where Cname='数据结构')) 
			 and 
			 Sno in (select Sno from Student y 
			 group by Sno 
			 having count(Cno)=2);

至少选修数据库或数据结构课程的学生的信息。

or：

select * 
from Student
where Sno in(select Sno from SC 
					where Cno in(select Cno from Course where Cname='数据库'))   
					or 
					Sno in(select Sno from SC
							where Cno in(select Cno from Course where Cname='数据结构'));

查询只被一名学生选修的课程的课程号和课程名.

having：

select Cno,Cname 
from Course
where Cno in (select Cno from CS  
						group by(Cno) 
						having count(Sno)=1);

查询至少选修了学生201215122选修的全部课程的学生号码。

选出的列与条件无关可以用exsist
选出的人不存在没选他选了的课。
因为sql里没有全称量词for all:∀ 所以用:
转化为:选出学生x,使得不存在这样的课程c,使得201215122选了，而x没选。

select Sno 
from SC x
where not exists (select * from SC y where y.Sno='201215122' and not exists (select * from SC z where z.Sno=x.Sno and z.Cno=y.Cno));

查询所有选修了1号课程的学生姓名。

带exists的相关子查询
exists引出的子查询，目标列表达式通常用*表示，因为exists语句返回值为真值，给出列名与实际意义。

select Sname
from Student 
where exists
(select * from SC  where Sno=Student.Sno and Cno='1');

查询和刘晨在同一个系学习的学生。

子查询:

select * 
from Student
where Sdept =(select Sdept feom Student where Sname='刘晨');

连接查询:

select First.* 
from Student first,Student second
where second.name='刘晨' and first.Sdept=second.Sdept;
where Sdept =(select Sdept feom Student where Sname='刘晨');

查询每一门课的间接先行课。

自连接:(看似选的是一张表上的列，其实操作的是两张表)

select first.Cno,second.Cpno
from Course first,Course second
where first.Cpno=second.Cno;

查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。

select Sno,Sname
     from Student x
     where exsist
     (select * from SC y 
     where x.Sno=y.Sno and Grade>90 and Cno=2);

插入子查询:insert into tablename(要插入的属性) values(对应的值);
省略(要插入的属性）则values里必须指定表的全部属性值，未插入的属性会置为NULL,所以非空属性必须插入。
对每一个系，求学生的平均年龄，并把结果存入数据库。
先建一个表avg_age:create avg_age(Sdept varchar(20),avg int);

 insert into avg_age select Sdept,avg(Sage)
    								from Student
    								group by Sdept;

修改数据UPDATE <表名> set
主码不能修改

将学生201215121年龄改为22

修改一个元组:

update Student
set Sage=22
where Sno='201215121';

将所有学生年龄加1

修改多个元组:

update Student
set Sage+=1;

将计算机系全体学生的成绩置0

带子查询的修改

update SC
set Grade=0
where Sno in(select Sno in Student whete Sdept='CS');

删除数据delete from <表名> [where]
没where就是清空整张表，注意删的是表的数据，表的定义还在。

删除学号为201215121的学生记录

delete from Student where Sno='201215121';

删除计算机系所有学生的选课记录

delete from SC 
where Sno in(select Sno in Student whete Sdept='CS');

*求各个课程号及对应的选课人数

group by:对查询结果分组。
选出的目标列要么是分组依据，要么是聚集函数

select Cno,count(Sno)
from SC
group by Cno;

查询选修了三门以上课程的学生学号

where语句作用范围为整个查询表，所以不能跟聚集函数，而having范围为每个分组。

select Sno
from SC
group by Sno
having count(Cno)>=3;//count(*)一样

查询平均成绩≥90分的学生学号与平均成绩
avg:

select Sno,avg(Grade)
from SC
group by Sno
having avg(Grade)>=90;