常用的Sql语句

sql语句练习

表：
major (专业表）: mno（专业号） mname（专业名称）
stu（学生表） : sno（学号） sname(姓名） age sex mno
cou（课程表） : cno（课程号） cname（课程名称） ctime（学时） ccredit（学分）
sc（成绩表） : sno cno grade（成绩）
外键可以为空,主键不能为空；

创建数据库：

create database stuinfo

建表：
专业表：

use stuinfo
create table major(
mno int,
manme varchar(20),
pramary key(mno) //主键
)

学生表：

create table stu(
sno varchar(20),
sname varchar(30),
age smallint,
sex bit,
mno int,
primary key(sno),
foreign key(mno) references major(mno) //外键
)

课程表：

create table cou(
cno varchar(20),
cname varchar(30),
ctime smallint,
ccredit decimal(4,2) --4位,两个小数点
primary key(cno)
)

成绩表：

create table sc(
sno varchar(20),
cno varchar(20),
grade decimal(5,2),
primary key(sno，cno)
foreign key(sno) references stu(sno)
)

删除表：

drop table table_name

表字段的操作：
添加字段：

alter table stu add qq varchar(20)

删除字段：

alter table stu drop column qq

添加外键：
在sc表中，cno是一个外键，所以需要对sc表添加一个外键：

alter table sc add constraint fk_sc foreign key(cno) references cou(cno)

表内容的操作：
插入信息：

insert into major(mno,mname) values(1,'计算机科学与技术')
--或者：insert into major values(5,'aa')

删除信息：

delete from major where mno = 1

删除本条信息时，要考虑其他表中是否有把mno当作外键的数据信息，有的话需要先设置为null才能删除；update stu set mno = null where mno = 1
例如：（删除专业号为1的专业时，先考虑学生表中是否有选了这个专业的，如果有你删除了那这个学生的专业号代表哪个专业？？？）

修改信息：把stu表中外键mno为1的全部置为null

update stu set mno = null where mno = 1

查询信息：
单表查询：

where: (where 多个条件用and连接）
条件：
不等于：!=、<>
在**之间：between 15 and 20
查询学生表中名字为小明的全部信息

select * from stu where sname = '小明'
select * from stu where sname like '小明'

as:
查询所有学生的出生年份：

select sname, 2021-age as birth_year from stu

distinct：
查询成绩表中学号并且去重

select distinct sno from sc

in:

select * from stu where age in (12,23,13)
select * from stu where age not in (12,23,13)

like:（如果没有通配符：%、_则like和=相同）
查询姓氏彭的人

select * from stu where sname like '彭%'

查询第二个名字为小的人

select * from stu where sname like '_小%'

group by:
查看各个课程号的选课人数：

select cno ,count(sno) as number from sc
group by cno

order by:(默认是升序（asc) ,降序是desc)

select * from stu
order by sno desc

having:(可以出现聚集函数，where字句不能用聚集函数作为条件表达式）having前面一定要有group by ，没有group by时和where一样
where过滤行，having过滤分组
having支持所有where操作符
where语句在数据分组前进行过滤，having在数据分组后进行过滤，where排除的行不包括在分组中

select sno ,avg(grade) from sc
group by sno
having avg(grade)>=90

优先级：

SELECT column1, column2
FROM table
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

多表查询：
表：
major (专业表）: mno（专业号） mname（专业名称）
stu（学生表） : sno（学号） sname(姓名） age sex mno
cou（课程表） : cno（课程号） cname（课程名称） ctime（学时） ccredit（学分）
sc（成绩表） : sno cno grade（成绩）

连接查询：

①等值查询：
查询每个学生的信息和成绩的信息：

select stu.*,sc.* from stu,sc where stu.sno = sc.sno

查询选修'20201'学生的姓名sname;

select stu.sname from stu,sc where stu.sno=sc.sno and sc.cou='20201'

查询每个学生的信息和选修课程的信息和学时：

select stu.*,sc.*,cou.ctime from stu,sc,cou where stu.sno= sc.sno and sc.cno=cou.cno

②左外连接：(把cno（主键）为空的数据也要查询出来）
查询所有的学生信息和选课信息，但是没有选修的学生也要显示出来

select stu.*,sc.* from stu left outer join sc on stu.sno = sc.sno

查询每个专业的学生人数，假设每个专业都有人：

select mno,count(stu) from stu group by mno having mno between 1 and 4

查询每个专业的学生人数，但有的专业可能没有人：
插入空的专业号：insert into major values(5,'aa')
保留空的专业号，只有major表中有所以要保留major表所以把major表放在左外连接的左边：

select major.mno,count(sno) from major left outer join stu on major.mno= stu.mno group by major.mno

嵌套查询：
①不相关嵌套查询：（子查询不依赖父查询）
查询选修'20201'学生的姓名sname;

原来：select stu.sname from stu,sc where stu.sno=sc.sno and sc.cou='20201'
现在：select stu.sname from stu,where sno in (select sno from sc where cno = '20201')

in:返回多个结果，=：返回一个结果
统一用in

②相关嵌套查询（将连接放在子查询里面）
查询选修'20201'学生的姓名sname;

原来：select stu.sname from stu,sc where stu.sno=sc.sno and sc.cou='20201'
select sname from stu where '20201' in (select cno from sc where stu.sno = sc.sno)

查询选修'C语言'课程的学生学号：

select sno from sc where 'C语言' in (select cname from cou where sc.cno = cou.cno)

查询每个学生超过他平均分的课程号，第二种方法用派生表实现
1：

select sno,cno
from sc x
where
grade>(select AVG(grade) from sc y group by sno having x.sno = y.sno)
2：
select sno,cno
from sc,(select sno, AVG(grade) from sc group by sno) as avg_sc(avg_sno,avg_grade) //查询时建的派生表
where sc.sno = avg_sc.avg_sno and grade>avg_grade

带有exists(涉及2个表也需要连接）
查询选修'20201'学生的姓名sname;

原来：select stu.sname from stu,sc where stu.sno=sc.sno and sc.cou='20201'
现在：select sname from stu where exists(select * from sc where cno = '20201' and stu.sno = sc.sno)

返回true , false,每次取一个sno连接；

集合查询：
union并集 intersect交集 except差(去除）
查询年龄是18且mno=1 的学生学号 intersect

select sno from stu where age = 18 and mno = 1
select sno from stu where age = 18 intersect select sno from stu where mno = 1

查询年龄是18且mno=1 的学生学号 except

select sno from stu where age = 18 except where sno from stu where mno != 1

查询选修'20201'号课程或'20203'的学生学号

select distinc sno from sc where cno in ('20201','20203') --需要加上distinct去重
select sno from sc where cno= '20201' union select sno from sc where cno= '20203' --union自动去重

查询选修'20201'号课程且'20203'的学生学号

select sno from sc where cno= '20201' intersect select sno from sc where cno= '20203'