常用的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'
