MySql

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; 串行化