Mysql数据库学习笔记(2020-04-18 星期六)

最新推荐文章于 2022-03-18 14:40:15 发布

Only*^o^*ME

最新推荐文章于 2022-03-18 14:40:15 发布

阅读量337

点赞数

文章标签：经验分享

本文链接：https://blog.csdn.net/THAT_flowers/article/details/105597879

版权

一：创建数据库

1、创建数据库：

create database test;

use test;

2、查看数据库中所有的数据表：

show tables;

3、创建一个数据表：

create table pet(

name varchar(20),

owner varchar(20),

species varchar(20),

sex char(1),

birth date,

death date);

4、查看创建好的数据表的结构：

5、查看数据库中的记录：

mysql> select * from pet;
+----------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+-------+
| puffball | diane | hamster | f | 1999-03-30 | NULL |
| 旺财 | 周星驰 | 狗 | 公 | 1990-01-01 | NULL |
| 忠犬 | 巴哥 | 狗 | 公 | 2000-03-20 | NULL |
| fluffy | harold | cat | f | 1993-02-04 | NULL |
| claws | gwen | cat | m | 1994-03-17 | NULL |
| buffy | harold | dog | f | 1989-05-13 | NULL |
| fang | benny | dog | m | 1990-08-27 | NULL |
| bowser | diane | dog | m | 1979-08-31 | NULL |
| chirpy | gwen | bird | f | 1998-09-11 | NULL |
| chirpy | gwen | bird | f | 1998-09-11 | NULL |
| slim | benny | snake | m | 1996-04-29 | NULL |
| puffball | diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+-------+

6、往数据表中添加数据记录：

mysql> insert into pet values('忠犬','巴哥','狗','公','2000-03-20',null);
Query OK, 1 row affected (0.01 sec)

7、mysql常用数据类型：

数值、日期/时间、字符串类型

8、数据类型如何选择？

日期：选择按照格式

数值和字符串按照大小

9、删除数据：

mysql> delete from pet where name='fluffy';
Query OK, 1 row affected (0.01 sec)

10、修改数据：

mysql> update pet set name='旺财' where owner='周星驰';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

11、数据记录常见操作：

增加：insert

删除：delete

修改：update

查询：select

二：mysql建表约束

1、主键约束

它能够唯一确定一张表中的一条记录，也就是我们通过给某个字段添加约束，就可以使得该字段不重复且不为空

1）在一个字段上添加约束

mysql> create table user( id int primary key, name varchar(20));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into user values(1,'张三');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user values(1,'张三');
ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'

mysql> insert into user values(null,'李四');
ERROR 1048 (23000): Column 'id' cannot be null

2）在两个字段上添加约束

联合主键：只要联合的主键值加起来不重复就可以(联合主键中任何一个字段都不能为空)

mysql> create table user2(id int,name varchar(20),password varchar(20),primary key(id,name));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into user2 values(1,'张三','123');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user2 values(1,'张三','123');
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'user2.PRIMARY'
mysql> insert into user2 values(2,'张三','123');
Query OK, 1 row affected (0.01 sec)

2、自增约束

mysql> create table user3(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into user3 (name) values('张三');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user3;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
+----+------+
1 row in set (0.00 sec)

mysql> insert into user3 (name) values('张三');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user3;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 张三 |
+----+------+
2 rows in set (0.00 sec)

---创建表的时候，忘记创建主键约束了？该怎么办？

mysql> create table user4(id int,name varchar(20));
Query OK, 0 rows affected (0.09 sec)

------a.修改表结构，添加主键

mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

------b.删除主键

mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

------c.使用modify,修改字段,添加主键

mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

3、唯一约束：约束修饰的字段的值不可以重复

mysql> create table user5(id int,name varchar(20));
Query OK, 0 rows affected (0.09 sec)

方法一：

mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

方法二：

mysql> create table user6(id int,name varchar(20),unique(name));
Query OK, 0 rows affected (0.10 sec)

方法三：

mysql> create table user7(id int,name varchar(20)unique);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into user5 values(1,'张三');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user5 values(1,'张三');
ERROR 1062 (23000): Duplicate entry '张三' for key 'user5.name'

方法四：

mysql> create table user8(id int,name varchar(20),unique(id,name));
Query OK, 0 rows affected (0.11 sec)

------删除唯一约束：

mysql> alter table user7 drop index name;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

方法五：modify添加

mysql> alter table user7 modify name varchar(20) unique;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

总结：

1、建表的时候就添加约束

2、可以使用alter...add...

3、alter...modify...

4、删除 alter...drop...

4、非空约束:修饰的字段不能为空 NULL

mysql> create table user9(id int,name varchar(20) not null);
Query OK, 0 rows affected (0.09 sec)

mysql> select * from user10;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | 张三 | 10 |
+------+------+------+
1 row in set (0.00 sec)

5、默认约束：就是当我们插入字段值的时候，如果没有传值，就会使用默认值

mysql> insert into user10(id,name) values(1,"张三");
Query OK, 1 row affected (0.01 sec)

mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | 10 | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from user10;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | 张三 | 10 |
+------+------+------+
1 row in set (0.00 sec)

------传了值，就不会使用默认值

mysql> insert into user10 values(1,"张三",19);
Query OK, 1 row affected (0.01 sec)

mysql> select * from user10;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | 张三 | 10 |
| 1 | 张三 | 19 |
+------+------+------+
2 rows in set (0.00 sec)

6、外键约束(涉及到两个表：父表、子表或主表、副表)

---主表：班级表(classes)

mysql> create table classes(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.11 sec)

mysql> select * from classes;
+----+------+
| id | name |
+----+------+
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
| 4 | 四班 |
+----+------+
4 rows in set (0.00 sec)

---副表：学生表(students)

mysql> create table students(
-> id int primary key,
-> name varchar(20),
-> class_id int,
-> foreign key(class_id) references classes(id));
Query OK, 0 rows affected (0.10 sec)

mysql> select * from students;
+------+------+----------+
| id | name | class_id |
+------+------+----------+
| 1001 | 张三 | 1 |
| 1002 | 张三 | 2 |
| 1003 | 张三 | 3 |
| 1004 | 张三 | 4 |
+------+------+----------+

---总结：

1.主表(父表) classes 中没有的数据值，在副表(子表)中，是不可以使用的

2.主表中的记录被副表引用，是不可以被删除的

三：数据库的三大设计范式

1.第一范式(1NF)：数据表中的所有字段都是不可分割的原子值

mysql> create table student(
-> id int primary key,
-> name varchar(20),
-> address varchar(30));

mysql> select * from student;
+----+------+-------------------------------------+
| id | name | address |
+----+------+-------------------------------------+
| 1 | 张三 | 中国四川省成都市武侯区武侯大道100号 |
| 2 | 李四 | 中国四川省成都市武侯区京城大道200号 |
| 3 | 王五 | 中国四川省成都市高新区天府大道90号 |
+----+------+-------------------------------------+
3 rows in set (0.00 sec)

---字段值还可以继续拆分的，就不满足第一范式

mysql> create table student1(
-> id int primary key,
-> name varchar(20),
-> cuntry varchar(30),
-> privence varchar(30),
-> city varchar(30),
-> details varchar(30));

mysql> select * from student1;
+----+------+--------+----------+--------+---------------------+
| id | name | cuntry | privence | city | details |
+----+------+--------+----------+--------+---------------------+
| 1 | 张三 | 中国 | 四川省 | 成都市 | 武侯区武侯大道100号 |
| 2 | 李四 | 中国 | 四川省 | 成都市 | 武侯区京城大道200号 |
| 3 | 王五 | 中国 | 四川省 | 成都市 | 高新区天府大道90号 |
+----+------+--------+----------+--------+---------------------+
3 rows in set (0.00 sec)

---范式，设计的越详细，对于某些实际操作可能更好，但是不一定都是好处

address->> cuntry | privence | city | details

2.第二范式

---必须是满足第一范式的前提下，第二范式要求，除主键外的每一列都必须完全依赖于主键

---如果要出现不完全依赖，只可能发生在联合主键的情况下

mysql> create table myorder(
-> product_id int,
-> customer_id int,
-> product_name varchar(20),
-> customer_name varchar(20),
-> primary key(product_id,customer_id));

---除主键以外的其他列，只依赖与主键的部分字段。不符合第二范式

---需要拆表

mysql> create table myorder(
-> order_id int primary key,
-> product_id int,
-> customer_id int);

mysql> create table product(
-> id int primary key,
-> name varchar(20));

mysql> create table customer(
-> id int primary key,
-> name varchar(20));

---分成三个表之后，就满足了第二范式的设计

3.第三范式(3NF)

---必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系

mysql> create table myorder(
-> order_id int primary key,
-> product_id int,
-> customer_id int);

mysql> create table customer(
-> id int primary key,
-> name varchar(20),
-> phone varchar(15));

四：MYSQL查询练习

学生表(Student，学号，姓名，性别，出生年月日，所在班级)

mysql> create table student(
-> sno varchar(20) primary key ,
-> sname varchar(20) not null,
-> ssex varchar(10) not null,
-> sbirthday datetime,
-> class varchar(20));

+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 杨幂 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 佟大为 | 女 | 1975-02-10 00:00:00 | 95031 |
| 109 | 杨紫 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

教师表(Teacher，教师编号，教师名字，教师性别，出生年月日，职称，所在部门)

mysql> 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);

+-----+-------+------+---------------------+--------+------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+-------+------+---------------------+--------+------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+-----+-------+------+---------------------+--------+------------+

课程表(Course，课程号，课程名称，教师编号)

mysql> create table course(
-> cno varchar(20) primary key,
-> cname varchar(20) not null,
-> tno varchar(20) not null,
-> foreign key(tno) references teacher(tno));

+-------+------------+-----+
| cno | cname | tno |
+-------+------------+-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
+-------+------------+-----+

成绩表(Score，学号，课程号，成绩)

mysql> 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 course(cno));

+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+

---查询练习：

---1.查询student表的所有记录

mysql> select * from student;
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 杨幂 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 佟大为 | 女 | 1975-02-10 00:00:00 | 95031 |
| 109 | 杨紫 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

---2.查询student表中的所有记录的sname、ssex和class列

mysql> select sname,ssex,class from student;
+--------+------+-------+
| sname | ssex | class |
+--------+------+-------+
| 曾华 | 男 | 95033 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 李军 | 男 | 95033 |
| 王芳 | 女 | 95031 |
| 陆君 | 男 | 95031 |
| 杨幂 | 男 | 95033 |
| 佟大为 | 女 | 95031 |
| 杨紫 | 男 | 95031 |
+--------+------+-------+

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

---distinct 排除重复

---4.查询score表中成绩在60到80之间的所有记录

查询区间：between...and...

mysql> select * from score where degree between 60 and 80;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+

直接使用运算符比较

mysql> select * from score where degree > 60 and degree < 80;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
4 rows in set (0.00 sec)

---5.查询score表中成绩为85，86或88的记录

mysql> select * from score where degree in(85,86,88);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
+-----+-------+--------+

---6.查询student表中"95031"班或性别为"女"的同学记录

mysql> select * from student where class="95031"or ssex="女";
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+--------+------+---------------------+-------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 佟大为 | 女 | 1975-02-10 00:00:00 | 95031 |
| 109 | 杨紫 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

---7.以class降序查询student表中的所有记录

---升序，降序

mysql> select * from student order by class desc;
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 杨幂 | 男 | 1976-02-20 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 佟大为 | 女 | 1975-02-10 00:00:00 | 95031 |
| 109 | 杨紫 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

---8.以cno升序、degree降序查询score表的所有记录

mysql> select * from score order by cno asc,degree desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 103 | 6-166 | 85 |
| 109 | 6-166 | 81 |
| 105 | 6-166 | 79 |
+-----+-------+--------+

---9.查询"95031"的学生人数

---统计 count

mysql> select count(*) from student where class="95031";
+----------+
| count(*) |
+----------+
| 5 |
+----------+

---10.查询score表中的最高分的学生学号和课程号(子查询或者排序)

mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno |
+-----+-------+
| 103 | 3-105 |
+-----+-------+

---子查询的方法

---1) 找到最高分

select max(degree) from score

---2)找最高分的sno和cno

select sno,cno from score where degree=(select max(degree) from score);

---排序的方法

mysql> select sno,cno,degree from score order by degree;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 109 | 3-245 | 68 |
| 105 | 3-245 | 75 |
| 109 | 3-105 | 76 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
| 103 | 6-166 | 85 |
| 103 | 3-245 | 86 |
| 105 | 3-105 | 88 |
| 103 | 3-105 | 92 |
+-----+-------+--------+

mysql> select sno,cno,degree from score order by degree desc limit 0,1;

---limit 第一个数字表示从多少开始

---第二个数字表示查多少条
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
+-----+-------+--------+

---11.查询每门课的平均成绩

mysql> select * from course;
+-------+------------+-----+
| cno | cname | tno |
+-------+------------+-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
+-------+------------+-----+

---avg()

mysql> select avg(degree) from score where cno="3-105";

mysql> select avg(degree) from score where cno="3-245";

mysql> select avg(degree) from score where cno="6-166";

mysql> select avg(degree) from score where cno="9-888";

---上面的四条语句可以用一个sql语句代替：

mysql> select cno,avg(degree) from score group by cno;

---group by 分组
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+-------------+

---12.查询score表中至少有2名学生选修的并以3开头的课程的平均分数

mysql> select cno,avg(degree),count(*) from score

group by cno

having count(cno)>=2

and cno like"3%";
+-------+-------------+----------+
| cno | avg(degree) | count(*) |
+-------+-------------+----------+
| 3-105 | 85.3333 | 3 |
| 3-245 | 76.3333 | 3 |
+-------+-------------+----------+

---13.查询分数大于70，小于90的sno列

---方法一：where

mysql> select sno,degree from score
-> where degree >70 and degree <90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
+-----+--------+

---方法二：between...and

mysql> select sno,degree from score
-> where degree between 70 and 90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
+-----+--------+

---14.查询所有学生的sname、cno和degree列

---分析：先从每张表开始查询

mysql> select sno,sname from student;
+-----+--------+
| sno | sname |
+-----+--------+
| 101 | 曾华 |
| 102 | 匡明 |
| 103 | 王丽 |
| 104 | 李军 |
| 105 | 王芳 |
| 106 | 陆君 |
| 107 | 杨幂 |
| 108 | 佟大为 |
| 109 | 杨紫 |
+-----+--------+

mysql> select sno,cno,degree from score;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+

---多表查询(两张表)

mysql> select sname,cno,degree from student,score
-> where student.sno=score.sno;
+-------+-------+--------+
| sname | cno | degree |
+-------+-------+--------+
| 王丽 | 3-105 | 92 |
| 王丽 | 3-245 | 86 |
| 王丽 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 杨紫 | 3-105 | 76 |
| 杨紫 | 3-245 | 68 |
| 杨紫 | 6-166 | 81 |
+-------+-------+--------+

---15.查询所有学生的sno、cname和degree列

---分析：先从单表查询

mysql> select cno,cname from course;
+-------+------------+
| cno | cname |
+-------+------------+
| 3-105 | 计算机导论 |
| 3-245 | 操作系统 |
| 6-166 | 数字电路 |
| 9-888 | 高等数学 |
+-------+------------+

mysql> select * from score;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+

---多表查询(两张表)

mysql> select sno,cname,degree from course,score

where course.cno=score.cno;
+-----+------------+--------+
| sno | cname | degree |
+-----+------------+--------+
| 103 | 计算机导论 | 92 |
| 105 | 计算机导论 | 88 |
| 109 | 计算机导论 | 76 |
| 103 | 操作系统 | 86 |
| 105 | 操作系统 | 75 |
| 109 | 操作系统 | 68 |
| 103 | 数字电路 | 85 |
| 105 | 数字电路 | 79 |
| 109 | 数字电路 | 81 |
+-----+------------+--------+

---16.查询所有学生的sname、cname和degree列

---sname -> student

---cname -> course

---degree -> score

---多表查询(三张表)

mysql> select sname,cname,degree from student,score,course

where student.sno=score.sno

and score.cno=course.cno;
+-------+------------+--------+
| sname | cname | degree |
+-------+------------+--------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 操作系统 | 86 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 计算机导论 | 88 |
| 王芳 | 操作系统 | 75 |
| 王芳 | 数字电路 | 79 |
| 杨紫 | 计算机导论 | 76 |
| 杨紫 | 操作系统 | 68 |
| 杨紫 | 数字电路 | 81 |
+-------+------------+--------+

mysql> select sname,cname,degree,student.sno as stu_sno,score.sno,course.cno as cou_cno,score.cno

from student,course,score

where student.sno=score.sno

and course.cno=score.cno;
+-------+------------+--------+---------+-----+---------+-------+
| sname | cname | degree | stu_sno | sno | cou_cno | cno |
+-------+------------+--------+---------+-----+---------+-------+
| 王丽 | 计算机导论 | 92 | 103 | 103 | 3-105 | 3-105 |
| 王丽 | 操作系统 | 86 | 103 | 103 | 3-245 | 3-245 |
| 王丽 | 数字电路 | 85 | 103 | 103 | 6-166 | 6-166 |
| 王芳 | 计算机导论 | 88 | 105 | 105 | 3-105 | 3-105 |
| 王芳 | 操作系统 | 75 | 105 | 105 | 3-245 | 3-245 |
| 王芳 | 数字电路 | 79 | 105 | 105 | 6-166 | 6-166 |
| 杨紫 | 计算机导论 | 76 | 109 | 109 | 3-105 | 3-105 |
| 杨紫 | 操作系统 | 68 | 109 | 109 | 3-245 | 3-245 |
| 杨紫 | 数字电路 | 81 | 109 | 109 | 6-166 | 6-166 |
+-------+------------+--------+---------+-----+---------+-------+

---17.查询"95031"班学生每门课的平均分

---第一步：

mysql> select sno from student

where class="95031";
+-----+
| sno |
+-----+
| 102 |
| 105 |
| 106 |
| 108 |
| 109 |
+-----+

---第二步：

mysql> select * from score

where sno in

(select sno from student where class="95031");
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+

---第三步：

mysql> select cno,avg(degree) from score

where sno in

(select sno from student where class="95031")

group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
+-------+-------------+

---18.查询选修"3-105"课程的成绩高于"109"号同学"3-105"成绩的所有同学的记录

---第一步：

mysql> select degree from score

where sno="109" and cno="3-105";
+--------+
| degree |
+--------+
| 76 |
+--------+

---第二步：

mysql> select * from score

where degree >

(select degree from score where sno="109" and cno="3-105");
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
+-----+-------+--------+

---第三步：

mysql> select * from score

where cno="3-105"

and degree>

(select degree from score where sno="109" and cno="3-105")

group by sno;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
+-----+-------+--------+
2 rows in set (0.00 sec)

---19.查询成绩高于学号为"109"、课程号为"3-105"的成绩的所有记录

mysql> select * from score

where degree>

---20.查询和学号为108、101的同学同年出生的所有学生的sno、sname和sbirthday列

---第一步：

mysql> select * from student

where sno in(108,101);
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 108 | 佟大为 | 女 | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

---第二步：

mysql> select year(sbirthday) from student

where sno in(108,101);
+-----------------+
| year(sbirthday) |
+-----------------+
| 1977 |
| 1975 |
+-----------------+

---第三步：

mysql> select sno,sname,sbirthday from student

where year(sbirthday)

in (select year(sbirthday)

from student

where sno in(108,101));
+-----+--------+---------------------+
| sno | sname | sbirthday |
+-----+--------+---------------------+
| 101 | 曾华 | 1977-09-01 00:00:00 |
| 102 | 匡明 | 1975-10-02 00:00:00 |
| 105 | 王芳 | 1975-02-10 00:00:00 |
| 108 | 佟大为 | 1975-02-10 00:00:00 |
+-----+--------+---------------------+

---21.查询"张旭"教师任课的学生成绩

---第一步：

mysql> select tno from teacher

where tname="张旭";
+-----+
| tno |
+-----+
| 856 |
+-----+

---第二步：

mysql> select cno from course

where tno=( select tno from teacher where tname="张旭");
+-------+
| cno |
+-------+
| 6-166 |
+-------+

---第三步：

mysql> select * from score

where cno=( select cno from course where tno=( select tno from teacher where tname="张旭"));
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 6-166 | 85 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
+-----+-------+--------+

---22.查询选修某课程的同学人数多于5人的教师姓名

---插入三条数据：insert into score values("101","3-105","90")；

insert into score values("102","3-105","91")；

insert into score values("104","3-105","89");

mysql> select * from score;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+

---第一步：

mysql> select cno from score

group by cno

having count(*)>5;
+-------+
| cno |
+-------+
| 3-105 |
+-------+

---第二步：

mysql> select tname from teacher,course

where cno=(select cno from score group by cno having count(*)>5)

and course.tno=teacher.tno;
+-------+
| tname |
+-------+
| 王萍 |
+-------+

---23.查询95033班和95031班全体学生的记录

---插入一条数据：insert into student values("110","张飞","男","1974-06-03","95038");

mysql> select * from student;
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 杨幂 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 佟大为 | 女 | 1975-02-10 00:00:00 | 95031 |
| 109 | 杨紫 | 男 | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
+-----+--------+------+---------------------+-------+

mysql> select * from student where class in("95031","95033");
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 杨幂 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 佟大为 | 女 | 1975-02-10 00:00:00 | 95031 |
| 109 | 杨紫 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

---24.查询存在有85分以上成绩的课程cno

mysql> select cno,degree from score

where degree>85;
+-------+--------+
| cno | degree |
+-------+--------+
| 3-105 | 90 |
| 3-105 | 91 |
| 3-105 | 92 |
| 3-245 | 86 |
| 3-105 | 89 |
| 3-105 | 88 |
+-------+--------+

---26.查询出"计算机系"教师所教课程的成绩表

---第一步：

mysql> select tno from teacher

where depart="计算机系";
+-----+
| tno |
+-----+
| 804 |
| 825 |
+-----+

---第二步：

mysql> select cno from course

where tno in(select tno from teacher where depart="计算机系");
+-------+
| cno |
+-------+
| 3-245 |
| 3-105 |
+-------+

---第三步：

mysql> select * from score

where cno in ( select cno from course where tno in

(select tno from teacher where depart="计算机系"));
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+

---26.查询"计算机系"与"电子工程系"不同职称的教师tname和prof

---union 求并集

---第一步：

mysql> select prof from teacher

where depart="电子工程系";
+------+
| prof |
+------+
| 助教 |
| 讲师 |
+------+

---第二步：

mysql> select * from teacher

where depart="计算机系"

and prof not in (select prof from teacher where depart="电子工程系");
+-----+-------+------+---------------------+--------+----------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+-------+------+---------------------+--------+----------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
+-----+-------+------+---------------------+--------+----------+

---第三步：

mysql> select prof from teacher

where depart="计算机系";
+--------+
| prof |
+--------+
| 副教授 |
| 助教 |
+--------+

---第四步：

mysql> select * from teacher

where depart="电子工程系"

and prof not in (select prof from teacher where depart="计算机系");
+-----+-------+------+---------------------+------+------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+-------+------+---------------------+------+------------+
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+-----+-------+------+---------------------+------+------------+

---第五步：

mysql> 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="计算机系");
+-----+-------+------+---------------------+--------+------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+-------+------+---------------------+--------+------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+-----+-------+------+---------------------+--------+------------+

---27.查询选修编号为"3-105"课程且成绩至少高于选修编号为"3-245"的同学的cno、sno和degree，并按degree从高到低次序排序

---至少：大于其中至少一个，any

---第一步：

mysql> select * from score

where cno="3-245";
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
+-----+-------+--------+

---第二步：

mysql> select * from score

where cno="3-105";
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+-----+-------+--------+

---第三步：

mysql> select * from score

where cno="3-105"

and degree > any(select degree from score where cno="3-245")

order by degree desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 101 | 3-105 | 90 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+-----+-------+--------+

---28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学的cno、sno和degree

---且：all表示所有的关系

mysql> select * from score

where cno="3-105"

and degree > all(select degree from score where cno="3-245")

---29.查询所有教师和同学的name、sex和birthday

---union：求并集

---别名：as

---第一步：

mysql> select sname,ssex,sbirthday from student;
+--------+------+---------------------+
| sname | ssex | sbirthday |
+--------+------+---------------------+
| 曾华 | 男 | 1977-09-01 00:00:00 |
| 匡明 | 男 | 1975-10-02 00:00:00 |
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 李军 | 男 | 1976-02-20 00:00:00 |
| 王芳 | 女 | 1975-02-10 00:00:00 |
| 陆君 | 男 | 1974-06-03 00:00:00 |
| 杨幂 | 男 | 1976-02-20 00:00:00 |
| 佟大为 | 女 | 1975-02-10 00:00:00 |
| 杨紫 | 男 | 1974-06-03 00:00:00 |
| 张飞 | 男 | 1974-06-03 00:00:00 |
+--------+------+---------------------+

---第二步：

mysql> select tname,tsex,tbirthday from teacher;
+-------+------+---------------------+
| tname | tsex | tbirthday |
+-------+------+---------------------+
| 李诚 | 男 | 1958-12-02 00:00:00 |
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 张旭 | 男 | 1969-03-12 00:00:00 |
+-------+------+---------------------+

---第三步：

mysql> select sname as name,

ssex as sex,sbirthday as birthday

from student

union

select tname,tsex,tbirthday

from teacher;
+--------+-----+---------------------+
| name | sex | birthday |
+--------+-----+---------------------+
| 曾华 | 男 | 1977-09-01 00:00:00 |
| 匡明 | 男 | 1975-10-02 00:00:00 |
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 李军 | 男 | 1976-02-20 00:00:00 |
| 王芳 | 女 | 1975-02-10 00:00:00 |
| 陆君 | 男 | 1974-06-03 00:00:00 |
| 杨幂 | 男 | 1976-02-20 00:00:00 |
| 佟大为 | 女 | 1975-02-10 00:00:00 |
| 杨紫 | 男 | 1974-06-03 00:00:00 |
| 张飞 | 男 | 1974-06-03 00:00:00 |
| 李诚 | 男 | 1958-12-02 00:00:00 |
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 张旭 | 男 | 1969-03-12 00:00:00 |
+--------+-----+---------------------+

---30.查询所有"女"教师和"女"同学的name、sex和birthday

---第一步：

mysql> select sname,ssex,sbirthday from student

where ssex="女";
+--------+------+---------------------+
| sname | ssex | sbirthday |
+--------+------+---------------------+
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 王芳 | 女 | 1975-02-10 00:00:00 |
| 佟大为 | 女 | 1975-02-10 00:00:00 |
+--------+------+---------------------+

---第二步：

mysql> select tname,tsex,tbirthday from teacher

where tsex="女";
+-------+------+---------------------+
| tname | tsex | tbirthday |
+-------+------+---------------------+
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
+-------+------+---------------------+

---第三步：

mysql> select sname as name,ssex as sex,sbirthday as birthday from student where ssex="女"

union

select tname,tsex,tbirthday from teacher where tsex="女";
+--------+-----+---------------------+
| name | sex | birthday |
+--------+-----+---------------------+
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 王芳 | 女 | 1975-02-10 00:00:00 |
| 佟大为 | 女 | 1975-02-10 00:00:00 |
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
+--------+-----+---------------------+

---31.查询成绩比该课程平均成绩低的同学的成绩表

---第一步：

mysql> select cno,avg(degree) from score

group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 87.6667 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+-------------+

---第二步：

mysql> select * from score;

a表：
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+

b表：

+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+

mysql> select * from score a

where degree < (select avg(degree) from score b where a.cno=b.cno);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+

---32.查询所有任课教师的Tname和Depart

---课程表中安排了课程

mysql> select tno from course;
+-----+
| tno |
+-----+
| 804 |
| 825 |
| 831 |
| 856 |
+-----+

mysql> select tname,depart from teacher

where tno in (select tno from course);
+-------+------------+
| tname | depart |
+-------+------------+
| 李诚 | 计算机系 |
| 王萍 | 计算机系 |
| 刘冰 | 电子工程系 |
| 张旭 | 电子工程系 |
+-------+------------+

---33.查询至少有2名男生的班号

---第一步：

mysql> select * from student;
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 杨幂 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 佟大为 | 女 | 1975-02-10 00:00:00 | 95031 |
| 109 | 杨紫 | 男 | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
+-----+--------+------+---------------------+-------+

---第二步：

mysql> select class from student

where ssex="男"

group by class

having count(*)>1;
+-------+
| class |
+-------+
| 95031 |
| 95033 |
+-------+

---34.查询student表中不姓"王"的同学记录

mysql> select * from student

where sname not like "王%";
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 杨幂 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 佟大为 | 女 | 1975-02-10 00:00:00 | 95031 |
| 109 | 杨紫 | 男 | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
+-----+--------+------+---------------------+-------+

---35.查询student表中每个学生的姓名和年龄

---年龄=当前年份-出生年份

---第一步：

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2020 |
+-------------+

---第二步：

mysql> select year(sbirthday) from student;
+-----------------+
| year(sbirthday) |
+-----------------+
| 1977 |
| 1975 |
| 1976 |
| 1976 |
| 1975 |
| 1974 |
| 1976 |
| 1975 |
| 1974 |
| 1974 |
+-----------------+

---第三步:

mysql> select sname,year(now())-year(sbirthday) as "年龄" from student;
+--------+------+
| sname | 年龄 |
+--------+------+
| 曾华 | 43 |
| 匡明 | 45 |
| 王丽 | 44 |
| 李军 | 44 |
| 王芳 | 45 |
| 陆君 | 46 |
| 杨幂 | 44 |
| 佟大为 | 45 |
| 杨紫 | 46 |
| 张飞 | 46 |
+--------+------+

---36.查询student表中最大和最小的sbirthday日期值

mysql> select max(sbirthday) as "最大",min(sbirthday) as "最小" from student;
+---------------------+---------------------+
| 最大 | 最小 |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+

---37.以班号和年龄从大到小的顺序查询student表中的全部记录

mysql> select * from student order by class desc ,sbirthday;
+-----+--------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+--------+------+---------------------+-------+
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 107 | 杨幂 | 男 | 1976-02-20 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 109 | 杨紫 | 男 | 1974-06-03 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 108 | 佟大为 | 女 | 1975-02-10 00:00:00 | 95031 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

---38.查询"男"教师及其所上的课程

---第一步：

mysql> select * from teacher where tsex="男";
+-----+-------+------+---------------------+--------+------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+-------+------+---------------------+--------+------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+-----+-------+------+---------------------+--------+------------+

---第二步：

mysql> select * from course

where tno in (select tno from teacher where tsex="男");
+-------+----------+-----+
| cno | cname | tno |
+-------+----------+-----+
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
+-------+----------+-----+

---39.查询最高分同学的sno、cno和degree列

---第一步：

mysql> select max(degree) from score;
+-------------+
| max(degree) |
+-------------+
| 92 |
+-------------+

---第二步：

mysql> select * from score

where degree=(select max(degree) from score);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
+-----+-------+--------+

---40.查询和"李军"同性别的所有同学的sname

---第一步：

mysql> select ssex from student

where sname="李军";
+------+
| ssex |
+------+
| 男 |
+------+

---第二步：

mysql> select sname from student

where ssex=(select ssex from student where sname="李军");
+-------+
| sname |
+-------+
| 曾华 |
| 匡明 |
| 李军 |
| 陆君 |
| 杨幂 |
| 杨紫 |
| 张飞 |
+-------+

---41.查询和"李军"同性别并同班的同学sname

mysql> select sname from student

where ssex=(select ssex from student where sname="李军")
and class=(select class from student where sname="李军");
+-------+
| sname |
+-------+
| 曾华 |
| 李军 |
| 杨幂 |
+-------+

---42.查询所有选修"计算机导论"课程的"男"同学的成绩表

---第一步：

mysql> select * from student where ssex="男";
+-----+-------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 杨幂 | 男 | 1976-02-20 00:00:00 | 95033 |
| 109 | 杨紫 | 男 | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
+-----+-------+------+---------------------+-------+

---第二步：

mysql> select * from course where cname="计算机导论";
+-------+------------+-----+
| cno | cname | tno |
+-------+------------+-----+
| 3-105 | 计算机导论 | 825 |
+-------+------------+-----+

---第三步：

mysql> select * from score

where cno=(select cno from course where cname="计算机导论")

and sno in (select sno from student where ssex="男");
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 104 | 3-105 | 89 |
| 109 | 3-105 | 76 |
+-----+-------+--------+

---43.假如使用如下命令建立一个grade表：

mysql> 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列

---第一步：

mysql> select * from grade;
+------+------+-------+
| low | upp | grade |
+------+------+-------+
| 90 | 100 | A |
| 80 | 89 | B |
| 70 | 79 | C |
| 60 | 69 | D |
| 0 | 59 | E |
+------+------+-------+

---第二步：

mysql> select sno,cno,grade from score,grade

where degree between low and upp;
+-----+-------+-------+
| sno | cno | grade |
+-----+-------+-------+
| 101 | 3-105 | A |
| 102 | 3-105 | A |
| 103 | 3-105 | A |
| 103 | 3-245 | B |
| 103 | 6-166 | B |
| 104 | 3-105 | B |
| 105 | 3-105 | B |
| 105 | 3-245 | C |
| 105 | 6-166 | C |
| 109 | 3-105 | C |
| 109 | 3-245 | D |
| 109 | 6-166 | B |
+-----+-------+-------+

五：MYSQL连接查询

---SQL的四种连接查询

---内连接：inner join 或者 join

---外连接：

1.左连接：left join 或者 left outer join

2.右连接：right join 或者 right outer join

3.完全外连接：full join 或者 full outer join

---创建两个表：

---person表(id，name，cardId)

---插入数据：

insert into person values(1,"张三",1);

insert into person values(2,"李四",3);

insert into person values(3,"王五",6);

mysql> select * from person;
+------+------+--------+
| id | name | cardId |
+------+------+--------+
| 1 | 张三 | 1 |
| 2 | 李四 | 3 |
| 3 | 王五 | 6 |
+------+------+--------+

---card表(id，name)

---插入数据：

insert into card values(1,"饭卡");

insert into card value(2,"建行卡");

insert into card value(3,"农行卡");

insert into card value(4,"工商卡");

insert into card value(5,"邮政卡");

mysql> select * from card;
+------+--------+
| id | name |
+------+--------+
| 1 | 饭卡 |
| 2 | 建行卡 |
| 3 | 农行卡 |
| 4 | 工商卡 |
| 5 | 邮政卡 |
+------+--------+

---并没有创建外键

---1、inner join(内连接)

mysql> select * from person

inner join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
+------+------+--------+------+--------+

---内联查询：其实就是两张表中的数据，通过某个字段相等，查询出相关记录数据

mysql> select * from person

join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
+------+------+--------+------+--------+

---2.left join(左外连接：会把左边表里面的所有数据取出来，而右边表中的数据，如果有相等的，就显示出来；

如果没有，就会补NULL)

mysql> select * from person

left join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
+------+------+--------+------+--------+

---left outer join

mysql> select * from person

left outer join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
+------+------+--------+------+--------+

---3.right join(右外连接：会把右边表里面的所有数据取出来，而左边表中的数据，如果有相等的，就显示出来；

如果没有，就会补NULL)

mysql> select * from person

right join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+------+------+--------+------+--------+

mysql> select * from person

right outer join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+------+------+--------+------+--------+

---4.full join(全外连接)

---mysql 不支持 full join

mysql> select * from person full join card on person.cardId=card.id;
ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'

mysql> select * from person

left join card on person.cardId=card.id

union

select * from person

right join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+------+------+--------+------+--------+

六、MYSQL事务

---1.mysql事务

mysql中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性

比如我们的银行转账：

a -> -100

update user set money=money -100 where name="a";

b -> +100

update user set money=money+100 where name="b";

---实际的程序中，如果只有一条语句执行成功了，而另外一条没有执行成功？

---出现的数据前后不一致

update user set money=money-100 where name="a";

update user set money=money+100 where name="b";

---多条sql语句，可能会有同时成功的要求，要么就同时失败

---2.mysql中如何控制事务？

---a.mysql默认是开启事务的(自动提交)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+

---默认事务开启的作用是：当我们去执行一个sql语句的时候，效果会立即体现出来，且不能回滚

mysql> create database bank;

mysql> create table user(
-> id int primary key,
-> name varchar(20),
-> money int);
mysql> insert into user values(1,"a",1000);

---rollback 事务回滚：撤销sql语句执行结果

mysql> rollback;

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+

---b.若想事务回滚，设置mysql自动提交为false

set autocommit=0；

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)

mysql> insert into user values(2,"b",1000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
1 row in set (0.00 sec)

---c.若设置自动提交为false，插入数据后不想回滚，可直接在插入数据后进行手动提交(commit)，

再撤销时，是不可以撤销的

---d.自动提交：@@autocommit=1

手动提交：commit;

事务回滚：rollback;

---如果说这个时候转账：

update user set money=money-100 where name="a";

update user set money=money+100 where name="b";

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> update user set money=money-100 where name="a";

update user set money=money+100 where name="b";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)

---事务给我们提供了一个返回的机会

---3.手动开启事务

mysql> set autocommit=1;

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+

---begin;或者start transaction;都可以帮我们手动开启一个事务

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> update user set money=money-100 where name="a";

update user set money=money+100 where name="b";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
+----+------+-------+
2 rows in set (0.00 sec)

---事务回滚

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

---没有被撤销

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
+----+------+-------+
2 rows in set (0.00 sec)

---手动开启事务(1)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money=money-100 where name="a";

update user set money=money+100 where name="b";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
+----+------+-------+
2 rows in set (0.00 sec)

---手动开启事务(2)

---事务开启之后，一旦commit提交，就不可以回滚(也就是当前的这个事务在提交的时候就结束了)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money=money-100 where name="a";

update user set money=money+100 where name="b";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
+----+------+-------+
2 rows in set (0.00 sec)

---4.事务的ACID和使用

---事务的四大特征：

A 原子性：事务是最小的单位，不可以再分割

C 一致性：事务要求，同一事务中的sql语句，必须保证同时成功或者同时失败

I 隔离性：事务1 和事务2 之间是具有隔离性的

D 持久性：事务一旦结束(commit，rollback)，就不可以返回

---事务开启：

1.修改默认提交 set autocommit=0；

2.begin；

3.start transaction；

---事务手动提交：commit；

---事务手动回滚：rollback；

---事务的隔离性

1.read uncommitted; 读未提交的

2.read committed; 读已经提交的

3.repeatable read; 可以重复读

4.serializable; 串行话

1--read uncommitted

如果有事务a，和事务b，a事务对数据进行操作，在操作的过程中，事务没有被提交，但是

b可以看到a操作的结果

------举例子：

bank数据库 user表

insert into user values(3,"小明",1000);

insert into user values(4,"淘宝店",1000);

mysql> select * from user;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+----+--------+-------+

---如何查看数据库的隔离级别？

mysql8.0版本：

---系统级别的

select @@global.transaction_isolation;

---会话级别的

select @@transaction_isolation;

mysql5.x版本

---mysql 默认隔离级别 REPEATABLE-READ

mysql> select @@global.tx_isolation;

mysql> select @@tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+

---如何修改隔离级别？(mysql5.x版本)

mysql> set global transaction isolation level read uncommitted;

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED |
+-----------------------+

------举例子转账：a.小明在淘宝店买鞋子：800块钱

小明-->成都 ATM

淘宝店-->广州 ATM

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money=money-800 where name="小明";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update user set money=money+800 where name="淘宝店";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from user;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
+----+--------+-------+
4 rows in set (0.00 sec)

---b.给淘宝店打电话，说你去查一下，是不是到账了

---c.淘宝店在广州查账

---d.发货，晚上消费了1800

---e.小明->成都

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+----+--------+-------+
4 rows in set (0.00 sec)

---f.结账的时候发现钱不够

---如果两个不同的地方，都在进行操作，如果事务a开启之后，他的数据可以被其他事务读取到

---这样就会出现(脏读)

---脏读：一个事务读到了另一个事务没有提交的数据，就叫做脏读

---实际开发是不允许脏读出现的

2--read committed;

set global transaction isolation level read committed;

select @@global.tx_isolation;

---修改隔离级别为：READ-COMMITTED

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED |
+-----------------------+
1 row in set (0.00 sec)

------bank 数据库 user 表

---a.小张：银行的会计

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

---b.小张出去了

---c.小王：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values(5,"c",100);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
+----+--------+-------+
5 rows in set (0.00 sec)

---d.小张回来后

mysql> select avg(money) from user;
+------------+
| avg(money) |
+------------+
| 820.0000 |
+------------+
1 row in set (0.00 sec)

---e.money的平均值不是1000，变少了？

---虽然我只能读到另外一个事务提交的数据，但还是会出现问题，就是

---读取同一个表的数据，发现前后不一致

---不可重复读现象：read commited

3---repeatable read;

set global transaction isolation level repeatable read;

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set (0.00 sec)

---在 REPEATABLE-READ 隔离级别下又会出现什么问题？

---a.小李-成都

start transaction;

---b.小牛-北京

start transaction;

---c.小李-成都

insert into user values(6,"d",1000);

commit;

---d.小牛-北京

mysql> insert into user values(6,"d",1000);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

---这种现象就叫做幻读

---事务a和事务b 同时操作一张表，事务a提交的数据，也不能被事务b读到，就可能造成幻读

4---serializable; 串行话

set global transaction isolation level serializable;

---修改级别为串行话

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| SERIALIZABLE |
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from user;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
+----+--------+-------+
6 rows in set (0.00 sec)

---a.小李-成都

start transaction;

---b.小牛-北京

start transaction;

---c.小李-成都

insert into user values(7,"赵铁柱",1000);

commit;

mysql> select * from user;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
| 7 | 赵铁柱 | 1000 |
+----+--------+-------+
7 rows in set (0.00 sec)

---d.小牛-北京

---e.小李-成都
start transaction;

insert into user values(8,"王小丫",1000);

---sql语句卡住了？

mysql> insert into user values(8,"王小丫",1000)；

---当user表被另外一个事务操作的时候，其他事务里面的写操作，是不可以进行的。

---进入排队状态(串行话)，指导小牛那边事务结束之后，小李这个的写入操作才会执行。

---在没有等待超时的情况下

---f.小牛-北京

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

---g.小李-成都

Query OK, 1 row affected (0.00 sec)

---串行话问题是，性能特差！

read uncommitted > read committed > repeatable read > serializable

---隔离级别越高，性能越差

mysql 的默认隔离级别是 repeatable read

Only*^o^*ME

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql数据库学习笔记(2020-04-18 星期六)

一：创建数据库1、创建数据库：create database test;use test;2、查看数据库中所有的数据表：show tables;3、创建一个数据表：create table pet( name varchar(20), owner varchar(20), species ...
复制链接

扫一扫