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

 

posted @ 2021-04-25 10:23  点终将连成线  阅读(254)  评论(0编辑  收藏  举报