create table student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)
);
create table teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirthday datetime,
prof varchar(20) not null,
depart varchar(20) not null
);
create table course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
create table score(
sno varchar(20) not null,
cno varchar(20) not null,
degree decimal,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno),
primary key(sno,cno)
);
1.查询student表中的所有记录
select * from student;
2.查询student表中所有记录的sname,ssex和sno
select sno,sname,ssex from student;
3.查询教师所有的单位即不重复的depart序列
select distinct depart from teacher;
4.查询score表中成绩在60到80 之间的所有记录
select * from score where degree between 60 and 80;
5.查询score表中成绩为64,66,69的记录
select * from score where degree in(64,66,69);
6.查询student表中‘95031’班或性别为‘女’的同学记录
select * from student where class='95031' or ssex='女';
7.以class降序查询student表中的所有记录
asc升序(默认) desc 降序
select * from student order by class desc;
8.以cno升序,degree降序查询score表的所有记录
select * from score order by cno asc,degree desc;
9.查询’95031‘班的学生人数
select count(*) from student where class='95031';
10.查询score表中的最高分的学生学号和课程号
select sno,cno from score where degree=(select max(degree) from score);
11.查询每门课的平均成绩
select cno,avg(degree) from score group by cno;
12.查询表中至少有两名学生选修并且以3开头的课程的平均分数
select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like '3%';
13.查询分数大于70小于90的sno列
select sno,degree from score where degree between 70 and 90;
14.查询所有学生的sname,cno和degree列
select sname,cno,degree from student,score where student.sno=score.sno;
15.查询所有学生的sno,cname和degree列
select sno,cname,degree from course,score where course.cno=score.cno;
16.查询所有学生的sname,cname,和degree列
select sname,cname,degree from student,course,score
where student.sno=score.sno and course.cno=score.cno;
17.查询’95031‘班学生每门课的平均成绩
select sno from student where class='95031';
select cno,avg(degree)
from score
where sno in (select sno from student where class='95031')
group by cno;
18.查询选修’3-105’课程的成绩高于’102‘号同学’3-105‘成绩的所有同学的记录
select * from score where cno='3-105' and degree>(select degree from score where sno='102' and cno='3-105');
19.查询成绩高于学号为’102‘,课程号为’3-105‘的所有成绩
select * from score where degree>(select degree from score where sno='102' and cno='3-105');
20.查询和学号为103,101的同年出生的所有学生的sno,sname和sbirthday列
select * from student where year(sbirthday) in (select select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno in (103,101));
21.查询’王平‘教师任课的学生的成绩
select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='王平'));
22.查询选修某课程的同学人数多于5人的教师姓名
select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>5));
23.查询95033班和95031班全体学生的记录
select * from student where class in('95031','95033');
24.查询存在有85分以上成绩的课程的cno
select cno from score where degree>85;
25.查出’计算机系‘教师所教课程的成绩表
select * from score where cno in (select cno from course where tno in (select tno from teacher where depart='计算机系'));
26.查询’计算机系‘与’电子工程系‘不同职称的教师的tname和prof
–union 求并集
select * from teacher where depart='计算机系' and prof not in(select prof from teacher where depart='电子工程系')
union
select * from teacher where depart='电子工程系' and prof not in(select prof from teacher where depart='计算机系');
27.查询选修编号为“3-105”课程并且成绩至少高于选修编号为“3-245”的同学的cno,sno和degree 并且degree从高到低次序排序
desc从高到低 默认asc从低到高
all表示所有的关系
select * from score
where cno='3-105'
and degree>any(select degree from score where cno='3-245')
order by degree desc;
28.查询选修编号为“3-105”课程并且成绩至少高于选修编号为“3-245”的同学的cno,sno和degree
any表示任意的关系
select * from score
where cno='3-105'
and degree>all(select degree from score where cno='3-245');
29.查询所有教师和同学的name,sex和birthday
as别名
select tname as name,tsex as sex,tbirthday as birthday from teacher
union
select sname,ssex,sbirthday from student;
30.查询所有女教师和女同学的name,sex和birthday
select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex='女'
union
select sname,ssex,sbirthday from student where ssex='女';
31.查询成绩比该课程平均成绩低的同学的成绩表
select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);
32.查询所有任课教师的tname和depart
select tname,depart from teacher where tno in (select tno from course);
33.查询至少有两名男生的班号
select class from student group by class having count(*)>2;
34.查询student表中不姓王的同学的记录
select * from student where sname not like '王%';
35.查询student表中每个学生的姓名和年龄
select sname,year(now())-year(sbirthday) as age from student;
36.查询student表中最大和最小的sbirthday日期值
select max(sbirthday),min(sbirthday) from student;
37.以班号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by sbirthday,class desc;
38.查询男教师及其所上的课程
select * from course where tno in(select tno from teacher where tsex='男');
39.查询最高分同学的sno,cno,degree列
select * from score where degree=(select max(degree) from score);
40.查询所有和李军同性别的所有同学的sname
select sname from student where ssex=(select ssex from student where sname='李军');
41.查询所有和李军同性别的并且同班的同学的sname
select sname from student
where ssex=(select ssex from student where sname='李军')
and class=(select class from student where sname='李军');
42.查询所有选修‘计算机导论’课程的‘男’同学的成绩表
select * from score
where cno=(select cno from course where cname='计算机导论')
and sno in (select sno from student where ssex='男');
43.假设使用如下命令建立了一个grade表:
create table grade(
low int(3),
upp int(3),
grade char(1)
);
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
现查询所有同学的sno,cno,grade列
select sno,cno,grade from score,grade where degree between low and upp;
SQL四种连接查询
内连接:
inner join或者join
两张表中的数据,通过某个字段相对,查询出相关记录数据
person表
card表
select * from person inner join card on person.cardId=card.id;
外连接:
1.左连接 left join或者left outer join
会把左边的数据都取出来,而右边的数据,如果有相等的就显示出来,如果没有就显示NULL
select * from person left join card on person.cardId=card.id;
2.右连接right join或者right outer join
会把右边的数据都取出来,而左边的数据,如果有相等的就显示出来,如果没有就显示NULL
select * from person right join card on person.cardId=card.id;
3.完全外连接full join或者full outer join
mysql不支持full join
select * from person left join card on person.cardId=card.id
union
select * from person right join card on person.cardId=card.id;
mysql事务:最小的不可分割的单元。事务能保证一个业务的完整性。 多条sql语句,可能同时成功,要么同时失败
mysql中如何控制事务?
1.mysql中事务是默认开启的(自动提交)
select @@autocommit;
事务默认开启的作用?
执行sql的时候,效果会即刻体现出来,且不能回滚 事务回滚,撤销sql语句执行效果。 rollback;
设置mysql自动提交为false:set autocommit=0;
插入数据后提交(commit),再撤销是不可以撤销的(持久性)
自动提交?@@autocommit=1;
手动提交?commit;
事务回滚?rollback;
手动开启事务
begin;或者start transaction;都可以帮我们手动开启一个事务;事务的四大特征? A 原子性:事务是最小单位,不可以再分割 C 一致性:事务要求,同一事务的sql语句,必须保持同时成功或者同时失败
I 隔离性:事务1和事务2具有隔离性 D 持久性:事务一旦结束(commit rollback),就不可以返回事务开启:
1.修改默认提交set sutocommit=0;
2.begin;
3.start transaction; 事务手动提交: commit 事务手动回滚: rollback;事务的隔离性:
1.read uncommited; 读未提交
2.read commited; 读已提交
3.repeatable read; 可以重复读
4.serializable; 串行化