数据库总结

学生表
学号
姓名
性别
出生年月
所在班级
创建database
create database selectTest；
show databases；
use selectTest；

create table student（
sno vachar（20） primary key，
sname varchar（20） not null，
sex varchar（10） not null，
sbrithday datetime,
class varchar(20)；
show tables

课程表
course
课程号
课程名称
教师编号
create table course（
cno varchar（20） primary key，
cname varchar（20） not null，
tno varchar（20） not null，
foreign key（tno） references teacher（tno）；

成绩表
score
学好
课程号
成绩
create table score（
sno varchar（20） primary key，
cno varchar（20） not null，
degree decimal，
foreign key （sno） references student（sno），
foreign key （cno） references score（cno））；

教师表
teacher
教师编号
教师名字
教师性别
出生年月日
职称
所在部门
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）；

添加学生数据
insert into student values（“101”,“小明”，”男“，”1996.0827“，”三班“）
insert into student values（“102”,“小宏”，”男“，”1996.0827“，”三班“）
insert into student values（“103”,“小蓝”，”男“，”1996.0827“，”三班“）
insert into student values（“103”,“小平”，”女“，”1996.0827“，”三班“）

2-------------------------------------数据库查询练习-------------------------------------------------------------
1.查询student表的所有记录

select * from student；

2.查询student表中所有记录的sname，ssex和class
select sname ，ssex，class from student；

3.查询教师所有单位即不重复的depart列

select distinct depart from teacher ；

4.查询score表中成绩在60到80之间所有记录
select score from student where degree between 60 and 80；

select score from student where score >60 and score <80;

5.查询score表中成绩为85，86或者88的记录
select * from score where degree in (85,86,88);

6.查询student表中“95031”班级或性别为“女”的同学记录
select * from student where class=“95031” or ssex="女“ ;

7.以class降序查询student表的所有记录
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 ssno,cno from score where degree=(select max(degree) form score )；
子查询
limit 0，1 前面一个是表示从哪个位置开始，后面表示取多少个
1.查询score表中分数最高分，表排序
2.查询学生学号和课程号
select sno，cno from score order by degree desc limit 0，1；

3.分组查询
11.查询每门课程的平均成绩
1.select avg（degree） from score where cno=”10500“；
有很多们课程，那么就要写很多次，所以用到一个思想就是分组
group by
2.select avg（degree） from score group by cno；
先按课程号分组，再计算每个组里面的平均成绩

12.查询成绩表中至少有两名学生选修了的课程，并且课程号是以3开头的平均成绩
select cno ，avg（dregree），count（） from score group by cno having count（）>=2 and cno
like “3%” ;
#分析，首先采用课程号先分组，在这些分组中用having再选择至少有两位同学选修了的课程，再通过like 匹配3后面的%是通配符，匹配任意字符串

13.多表查询，查询sname cno dregree
发现这三个属性来自学生表和成绩表
分步
1.select sname ，sno from student；
2.select cno dregree from score ；
但是连接不上啊 ，没有共同的东西
select sname ， sno from student ；
select sno，cno， dregree from score ；
所以要通过sno来链接
select sname ，cno，dregree from student，score where student.sno
=score.sno ;
14.多表查询cname，sno，degree
查询得知cname来自course表，sno和degree来自score表
于是：
select cname，cno from course；
select cno，sno，degree from score；
发现这两个表可以通过cno一一对应并且连接在一起
select cname，sno，degree from course，score where course.cno = score.cno;
15.多表查询sname，cname，和degree
发现sname–》student
cname–》course
degree–》score
要通过三个表的共同字段联系在一起
select sname，cname，degree from student，course，score where student.sno=score.sno and course.cno=score.cno;
16.查询班级为“95031”的每门课平均成绩
分析先找到95031班级的学生记录从student表中
select * from student where class=“95031”；
select sno from student where class=“95031”；
然后再是查班级所有学生的所有课程的成绩，从score表中
select cno degree from score where sno in （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；
17.查询选修课程“3-105”，成绩高于“109”号同学，课程为“3-105”的所有同学记录
select * from score where cno=“3-105”
109号同学选修3-105的成绩
select degree from score where cno=“3-105” and sno=“105”；
select * from score where cno= “3-105” and degree >（ select degree from score where cno=“3-105” and sno=“105”）；
18.查询和“108”和”101“同学同年出生，所有同学的sno，sname和sbrithday
首先查询出”108“”101“的同学出生年份
select year（sbrithday） from student where sno in （108，101）；
因为sbrithday有年月日，所以用year，而且不能用=，是因为有两个以上的记录，所以用in
select sno，sname，sbrithday from student where year（brithday） in（select year（sbrithday） from student where sno in （108，101））；
19.查询“张旭”老师所教的学生的成绩
第一步：查询张旭老师教了哪门课
第二步：把所教的课对应成绩表里面的记录
select tno from teacher where tname=“张旭”；
发现里面有一个tno与course里面关联起来
查询张旭老师教的哪门课
select cno from course where tno=（select tno from teacher where tname=“张旭”）
查询所教的这门课的成绩
select * from score where cno = （select cno from course where tno=（select tno from teacher where tname=“张旭”））；
20.查询某门选修课程超过5位同学的教师姓名
1.选出了选修课程超过五个同学的课程
select cno from score group by cno having count（*）>5;
2.这门课程所教的老师的编号
select tno from course where cno=（select cno from score group by cno having count（sno）>5）
3.这个老师编号对应的老师名字
select tname from teacher where tno=（select tno from course where cno=（select cno from score group by cno having count（sno）>5））；

21.查询“95033”和“95031”班级的学生记录
select * from student where class in（95031，95033）
22.查询计算机系老师所教的课程的全部学生成绩
首先查询计算机系老师所教的课程
那查到计算机系有哪些老师
select * from teacher where department=“计算机系”；
得到了有哪些老师，可以用tno标记
select tno from teacher where department=“计算机系”；
再次查看计算机系的老师教了哪些课程？
select cno from course where tno in（select tno from teacher where department=“计算机系”）；
再查看这些课程的学生成绩
select * from score where cno in（select cno from course where tno in（select tno from teacher where department=“计算机系”））；

23.查询“计算机系”和“电子工程系”职称不同的教师信息
如下图，需要查找两个系里面职称不同的为副教授和讲师

首先：查询计算机系教师的职称不在电子工程系里面的教师信息
select * from teacher where depart=“计算机系” and prof not in （电子工程系的职称）
查找电子工程系的职称有哪些
select prof from teacher where depart=“电子工程系” ；
所以：
select * from teacher where depart=“计算机系” and prof not in（select prof from teacher where depart=“电子工程系” ）；那么这个就找到了计算机和电子学院不同的职称的名字，那么还有反过来找电子工程系中和计算机系不同的

select * from teacher where depart =“电子工程系” and prof not in（select prof from teacher where depart=“计算机系” ）；
那么把这两个联合在一起
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=“计算机系” ）；
这样两个就可以一起输出啦
24.查询选修课程为“3-645”的成绩至少大于选修课程为“3-105”的成绩，并采用降序排序
至少大于，则为大于3-105课程的其中一门课程的成绩-则用any
select * from score where cno =“3-645” and degree> any (select degree from score where cno =“3-105”） order by degree desc；
25…查询选修课程为“3-645”的成绩且大于选修课程为“3-105”的成绩，并采用降序排序**
有一个且，则说明是选修课程为“3-645”都大于选修课程为“3-105”的成绩，则用all
则表明：
select * from score where cno =“3-645” and degree>all (select degree from score where cno =“3-105”）；
26.查询所有教师和同学的name sex 和brithday
select tname tsex tbrithday from teacher
select sname ssex sbrithday from student
但是这些记录都想同时输出来，则采用union ，但是输出的为tname 所以要取别名，用as
select tname as name，tsex as sex，tbrithday as brithday from teacher
union select sname ssex sbrithday from student；

27.查询成绩表里面低于该课程平均分的学生的课程成绩
采用复制表来进行条件查询
首先看每门课程的平均成绩是多少
select cno avg（degree） from score group by cno；
这个语句知道了每门课程的平均成绩，在成绩表里面从上到下依次查询，看该成绩是否大于该课程的平均分，如果大于则保留，所以要用复制两个表
score a score b

select sno cno degree from score a where degree <(该门课程的平均成绩）
该门课程的平均成绩怎么查？
先查所有课程的平均成绩，再加上条件：select sno cno degree from score a where degree <(select avg（degree） from score b group by cno where a.cno=b.cno ）;
28.查询班级至少有两名男生的班级号
首先先看看学生表长什么样
select * from student ；
然后再查询看看都是男生的信息
select class from student where sex=“nan” ；
再在男生这些学生信息中按班级分组，统计男生数量》2的班级
select class from student where sex=“nan” group by class having count（*）>2;
29.查询班级中不姓王的学生的信息；不什么。。。。就用not
select * from student where sname not like “王%”；
29.查询学生表学生的姓名和年龄；
年龄是：当前年份-出生年份
当前年份是用year（now（））
出生年份是year（sbrithday）
select sname year（now（））-year（sbrithday） as “年龄” from student；
30.查询最大最小就用max 和min
最大的出生年月max（sbrithday） min（sbrithday）
31.查询最高分同学的sno、cno和degree；
首先查询成绩表里面的最高分是多少
select max（degree） from score；
查询了最高分，然后就是查询最高分成绩的学生记录
select * from score where degree=（select max（degree） from score）；
32.查询与李鑫同性别的同同学的sname
先查询李鑫的性别
select ssex from student where sname=“李鑫”；
select sname from student where ssex=（select ssex from student where sname=“李鑫”）；
33.查询与李鑫同同性别并且同班的男生
select sname from student where ssex=（select ssex from student where sname=“李鑫”）and class=（select class from student where sname=“李鑫”）

34.查询所有选修计算机导论课的男生的成绩；
首先查看计算机导论课是怎么样的
select * from course where cname=“计算机导论”；

再者就是查看学生表里面性别为男生的信息是怎样的
select * from student where ssex=“男” 这里有很多记录

然后再score表里面查找性别为男sno和cno为计算机导论的
select * from score where sno in（select sno from student where ssex=“男”）and cno=（select cno from course where cname=“计算机导论”）；
35.按等级查询
创建grade表
create table grade（low varchar（10），upp varchar（10），grade varchar（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");
查询成绩表里面学生课程以及成绩的等级
select sno cno grade from score，grade where degree betweed low and upp；
36.内连接 外连接，左连接，右连接
内连接：inner join 或者 join；
外连接：
1.左连接：left join 或者 left outer join
2.右连接：right join 或者 right outer join
3.完全外连接： full join 或者full outer join
–创建两个表：

person 表
id，name，carId
create table person（
id int ，name varchar（20
），
cardId int）；
–card 表
id
name
create table card（id int ，name varchar（20））；

insert into card values （1，“饭卡”）；
insert into card values （2，“农行卡”）；
insert into card values （3，“建行卡”）；
insert into card values （4，“工商卡”）；
insert into card values （5，“招商卡”）；

insert into person values（1，“张三”，1）；
insert into person values（2，“李四”，2）；
i’insert into person values（1，“王五”，6）；

没有创建外键，进行内链接
select * from person inner join card on person.cardId=card.id;

person里面的id与card里面的id相等，然后连接，把两个表里面有关系的数据连接到一起

内联查询就是两张表的数据，通过某个字段相等，查询出相关记录
select * from person join card on person.id=card.id;

2.left join 左外连接
select * from person left join card on person.id=card.id;

select * from person left outer join card on person.id=card.id;
就是把左边的这个表全部显示出来，如果有相等的就会显示，如果左边的表里面的字段与右边的表没有匹配的，那么久会显示null
select * from person right join card where person.id=card.id;

select * from person right outer join card on person.id=card.id;

那么他会把右边这个表全部显示出来，如果有相同，则会显示出来，左边的表没有与他匹配的，就都显示null

4.full join 全外连接，mysql不支持全外连接
–mysql不支持全外联结

inner join

left join

左边全部显示，右边有相等的就显示，没有相等的就不显示，就直接表示为null
右连接

4.全连接
可以用这样表示
右连接和左连接的并集
select * from person right join card where person.id=card.id
union
select * from person left join card on person.id=card.id;

全连接效果

连接的好处就是不用加外键，可以通过两个表的字段相等来进行连接，求交集并集

事务，其实是一个最小的不可分割的工作单元，事务能够保证一个业务的完整性
比如银行转账
a-》 -100
update user set money = money-100 where name=”a";
b-》 +100
update user set money = money+100 where name=“b”;
必须保证两个都成功执行，如果一个执行成功，另外一个没有执行成功，那么会出错，出现数据前后不一致；
多条sql语句，可能会有同时完成的要求，要么同时失败

mysql中如何控制事务
1.mysql中，默认是开启事务的（自动提交）
select @@autocommit

当我们执行sql的时候，效果会自动体现出来，不能回滚（撤销事物的效果）。

create database bank；
create table user（
id int primary key，
name varchar（20），
money int）；

insert into user values（1，”a“，1000）；
select * from user
#事物回滚，撤销sql语句执行效果
rollback；
即使用了rollback，里面记录还是存在，所以不能回滚
设置mysql自动提交为false，则就可以通过rollback来回滚
set autocommit =0；
insert into user values（2，”b“，1000）；
用一下rollback，则没有插入成功。

如果插入的时候，用一下commit，再用rollback，则回滚无效，只要提交了，回滚怎么样也无效了。

用mysql的时候看看自动提交是怎么样的，
如果没有提交，并且autocommit为0，是可以回滚的

–手动开启事务
begin或者start transcation
如果autocommit=1
加上begin手动开启事务，那么即使添加进去，rollback也是可以事务回滚
update user set money=money-100 where name =“a”；
update user set money=money+100
where name=“b”
–事务开启之后，commit了的话，即事务结束了，即使rollback也是不能回滚的

事务保证两个交易的完整性，同时成功或者同时失败
事务-ACID特征与使用
原子性，不可分
一致性，同一事务中的sql语句，保证同时成功，或者同时失败
隔离性，事务1和事务2之间，是有隔离性的，张三在这边操作事务，李四在那边操作另外一个事务，是隔离的
持久性，事务一旦结束，就不可以返回

事务的开启：1.set autocommit=0；
2.begin 3.start transaction
事务手动提交
commit；
事务回滚
rollback；

事务的隔离性
1.read uncommitted；读未提交的
2.read committed ；读已经提交的
3.repeatable read； 可重复读
3.serializable；串行化

1-----read uncommitted
如果有事务a或者事务b，a对数据进行操作，在操作的过程中，事务没有被提交，但是b可以看见a操作的结果

bank数据库 user表
insert into user values（3，“小明”，1000）；
insert into user values（4，淘宝店“，1000）；

select *from user
--------如何查看数据库的隔离级别

mysql 8.0
系统级别的
select @@global.transaction_isolation;
select @@transaction_isolation;会话级别
如何查看隔离级别
系统默认的隔离级别是：REPEATABLE-READ

mysql 5.x
sekect @@global.tx_isolation;
select@@tx_isolation;

------如何修改隔离级别
set global
transaction isolation level read uncommitted;
修改完，则系统的隔离级别为：READ-UNCOMMITTED

变成read-uncommitted

–转账：小明在淘宝店买鞋子：800
小明–成都-atm
淘宝–广州-ATM
start transaction；
update user set money=money-800 where name=”小明“；
update user set money=money+800 where name=”淘宝店“；

打电话给淘宝店去查看银行信息，淘宝店查看到了小明未提交的数据。

发现钱不够，为什么，小明在成都rollback

–结账的时候发现钱不够
update user set money=money-1800 where name=”淘宝店“

如果两个人在同的地方交易，同时开启两个事务，淘宝店是可以读到小明未提交的数据，淘宝店读到未提交的数据，则叫脏读，一个事务读到了另外一个事务没有提交的数据

多个事务对数据进行操作，在uncommit
当事务的隔离级别为read uncommitted的情况下会出现脏读