sql语句学习

完整例子

drop database stuinfo
create database stuinfo
use stuinfo
create table major(
	mno int primary key,
	mname varchar(30)
)
create table stu(
	sno varchar(13) primary key,
	sname varchar(30) not null,
	age int,
	sex bit,
	mno int
	foreign key(mno) references major(mno)
)
create table cou(
	cno varchar(13) primary key,
	cname varchar(30) not null,
	ctime int,
	ccredit decimal(5,2)
)
create table sc(
	cno varchar(13),
	sno varchar(13),
	grade decimal(5,2)
	primary key(cno,sno)
	foreign key(cno) references cou(cno),
	foreign key(sno) references stu(sno)
)

完整插入用例数据

insert into major(mno,mname) values(1,'计算机科学与技术');
insert into major(mno,mname) values(2,'软件工程');
insert into major(mno,mname) values(3,'网络工程');
insert into major(mno,mname) values(4,'智能科学与技术');
select * from major
insert into stu values('2020001','小一',18,0,1);
insert into stu values('2020002','小二',18,1,1);
insert into stu values('2020003','小四',18,1,1);
insert into stu values('2020004','小五',18,1,1);
insert into stu values('2020005','小六',18,0,2);
insert into stu values('2020006','小七',18,1,2);
insert into stu values('2020007','小八',18,0,2);
insert into stu values('2020008','小九',18,1,2);
insert into stu values('2020009','小十',19,0,3);
insert into stu values('20200010','小十',20,0,3);
insert into stu values('20200011','小快',19,0,3);
insert into stu values('20200012','小冬',21,0,3);
insert into stu values('20200013','小宇',19,0,null);
insert into stu values('20200014','小点',19,0,4);
insert into stu values('20200015','彭杰',21,0,4);
insert into stu values('20200016','彭小杰',21,0,4);
select * from  stu
insert into cou values('20201','C语言',32,5);
insert into cou values('20202','C#',32,3);
insert into cou values('20203','数据结构',16,5);
insert into cou values('20204','大学英语1',32,3.5);
insert into cou values('20205','大学英语2',32,3.5);
insert into cou values('20206','大学英语3',32,3.5);
insert into cou values('20207','大学英语4',32,3.5);
select * from  cou
insert into sc(sno,cno,grade) values('2020006','20201',null);
insert into sc(sno,cno,grade) values('2020005','20201',null);
insert into sc(sno,cno,grade) values('2020001','20201',90);
insert into sc(sno,cno,grade) values('2020002','20201',90);
insert into sc(sno,cno,grade) values('2020003','20201',90);
insert into sc(sno,cno,grade) values('2020004','20201',58);
insert into sc(sno,cno,grade) values('2020004','20202',98);
insert into sc(sno,cno,grade) values('2020004','20203',90);
insert into sc(sno,cno,grade) values('2020005','20203',90);
select * from sc

创建stuinfo数据库

create database stuinfo

创建major表

use stuinfo
create table major(
	mno int,
	mname varchar(20)
	primary key(mno)
)

查询major表全部信息

select * from major

创建stu表

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

查询stu表全部信息

select * from stu

创建cou表

create table cou(
	cno int,
	cnmae varchar(30),
	ctime smallint,
	ccredit decimal(4,2)
	primary key(cno)
)

创建sc表，并添加外键操作

create table sc(
	sno int,
	cno int,
	grade decimal(5,2)
	primary key(sno,cno)
	foreign key(sno) references stu(sno)
)
alter table sc add constraint fk_sc foreign key(cno) references cou(cno)

在stu表添加qq

use stuinfo
alter table stu add qq varchar(20)

在stu表删除qq

alter table stu drop column qq

删除t表

drop table t

在major表添加信息

insert into major(mno,mname)values(1,'计算机科学与技术')

在stu表添加信息

insert into stu values(1,'kk',20,0,1)

在stu表删除信息

delete from stu where sno=1

在删除外键信息时，先修改该外键存在其他表的信息
（或可利用cascade外键约束）

update stu set mno=null where mno=1
delete from major where mno=1

查询stu表中sno，sname的信息

select sno,sname from stu

查询stu表中sno，并根据年龄算出出生年份的信息

select sno,2019-age as birth_date from stu

查询sc表中sno且不重复的信息

select distinct sno from sc

查询stu表中名为小十的学生信息

select * from stu where sname = '小十'
//或者
select * from stu where sname like '小十'

查询sc表中sno为20201且成绩大于80的信息

select * from sc where cno='20201' and grade > 80

查询stu表中年龄在18和19的学生姓名和年龄

select sname,age from stu where age>=18 and age<=19
//或者
select sname,age from stu where age between 18 and 19

查询stu表中mno为1、2、4的信息

select * from stu where mno=1 or mno=2 or mno=4
select * from stu where mno in(1,2,4)

查询stu表中mno不为1、2、4的信息

select * from stu where mno!=1 and mno!=2 and mno!=4
select * from stu where mno not in(1,2,4)

查询stu表中姓为‘小’的学生信息

select * from stu where sname like '小%'

查询stu表中第二个字为‘小’的学生信息

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

查询stu表中名字里有‘小’的学生信息

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

查询sc表中grade为空的信息

select * from sc where grade is null

查询sc表中grade为的信息以升序显示

select * from sc order by grade

查询sc表中grade为的信息以降序显示

select * from sc order by grade desc

查询sc表中有多少行数据

select count(*) from sc

查询sc表中有多少个不同的sno数

select count(distinct sno)from sc

查询sc表中cno为20201的成绩平均值

select AVG(grade) from sc where cno=20201

查询sc表中cno为20201的成绩最大值（最小值）MIN

select MAX(grade) from sc where cno=20201

查询sc表中各个课程号以及数量

select cno ,COUNT(sno)as num from sc group by cno

查询sc表中平均成绩大于90的学号与平均成绩

select sno,AVG(grade) as avg_grade from sc 
group by sno having AVG(grade)>=90

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

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

查询选修‘20201’学生的姓名

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

exists查询选修‘20201’学生的姓名

select sname from stu where exists
(select * from sc where sc.cno='20201'and sc.sno=stu.sno)

嵌套查询选修‘20201’学生的姓名

select sname from stu where sno in
(select sno from sc where cno='20201')

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

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

左连接查询所以学生信息和选课信息，包括没选修学生

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

查询每个专业的学生人数

select mno,count(sno) from stu group by mno

查询每个专业的学生人数，包括无人数的专业

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

嵌套查询选择‘C语言’课程的学生学号

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

嵌套查询学生课程成绩大于该学生的平均成绩的学号和课程号

select sno,cno from sc x where grade>(select AVG(grade) from sc y 
group by sno having x.sno=y.sno)

派生表查询学生课程成绩大于该学生的平均成绩的学号和课程号

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 sc.grade>avg_sc.avg_grade

interesct查询年龄为18且mno为1的学生的学号

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

intersect查询同时选修了20201和20203的学生的学号

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

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

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

union查询选修课程为20201或者20203的学生的学号

select sno from sc where sc.cno='20201' union select sno from sc where sc.cno='20203'