MySQL基础总结

基本操作命令


#显示全部已有的数据库
show databases;

#创建一个新的数据库
create database test;

#选择/切换要使用的某一个数据库
use test;

#显示选中的数据库中的全部表
show tables;

#在数据库test中创建一个新的表
create table pet(name VARCHAR(20),owner VARCHAR(20),species VARCHAR(20),sex CHAR(1),birth DATE,death DATE);

#查看表的结构
mysql> desc(ribe) pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

#插入几条数据
insert into pet values('Puffball','Diane','hamster','f','1999-03-30',NULL);
insert into pet values('旺财','周星驰','狗','公','1990-01-01',NULL);
insert into pet values('Fluffy','Harold','cat','f','1993-02-04',NULL);
insert into pet values('Claws','Gwen','cat','m','1994-03-17',NULL);
insert into pet values('Buffy','Harold','dog','f','1989-05-03',NULL);
insert into pet values('Fang','Benny','dog','m','1979-08-31','1995-07-29');

#查询表pet中的所有元素
mysql> select * from pet;
+----------+-----------+---------+------+------------+------------+
| name     | owner     | species | sex  | birth      | death      |
+----------+-----------+---------+------+------------+------------+
| Puffball | Diane     | hamster | f    | 1999-03-30 | NULL       |
| 旺财     | 周星驰    | 狗      | 公   | 1990-01-01 | NULL       |
| Fluffy   | Harold    | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen      | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold    | dog     | f    | 1989-05-03 | NULL       |
| Fang     | Benny     | dog     | m    | 1979-08-31 | 1995-07-29 |
+----------+-----------+---------+------+------------+------------+
6 rows in set (0.00 sec)

#删除一条数据
mysql> delete from pet where name='Fluffy';
Query OK, 1 row affected (0.04 sec)

mysql> select * from pet;
+----------+-----------+---------+------+------------+------------+
| name     | owner     | species | sex  | birth      | death      |
+----------+-----------+---------+------+------------+------------+
| Puffball | Diane     | hamster | f    | 1999-03-30 | NULL       |
| 旺财     | 周星驰    | 狗      | 公   | 1990-01-01 | NULL       |
| Claws    | Gwen      | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold    | dog     | f    | 1989-05-03 | NULL       |
| Fang     | Benny     | dog     | m    | 1979-08-31 | 1995-07-29 |
+----------+-----------+---------+------+------------+------------+
5 rows in set (0.00 sec)

#修改一条数据
mysql> update pet set name='旺旺财' where owner='周星驰';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from pet;
+-----------+-----------+---------+------+------------+------------+
| name      | owner     | species | sex  | birth      | death      |
+-----------+-----------+---------+------+------------+------------+
| Puffball  | Diane     | hamster | f    | 1999-03-30 | NULL       |
| 旺旺财    | 周星驰    | 狗      | 公   | 1990-01-01 | NULL       |
| Claws     | Gwen      | cat     | m    | 1994-03-17 | NULL       |
| Buffy     | Harold    | dog     | f    | 1989-05-03 | NULL       |
| Fang      | Benny     | dog     | m    | 1979-08-31 | 1995-07-29 |
+-----------+-----------+---------+------+------------+------------+
5 rows in set (0.00 sec)

#查询满足特定条件的元组
mysql> select name from pet where sex='m';
+-------+
| name  |
+-------+
| Claws |
| Fang  |
+-------+
2 rows in set (0.00 sec)

建表约束

主键约束

作为主键的字段，其值不能重复，不能为空。

声明某个属性作为主键，常用有以下方法：

#方法1
mysql> create table user(
    -> id int primary key,#主键是id
    -> name varchar(20)
    -> );

#方法2
mysql> create table user(
    -> id int,
    -> name varchar(20),
    -> primary key (id)#主键是id
    -> );

如果一开始并没有定义主键，也可以之后再添加(两种方法)

#一开始并没有声明主键
mysql> create table user(
    -> id int,
    -> name varchar(20)
    -> );

#现在使id作为主键
#方法1
alter table user add primary key(id);
#方法2
alter table user modify id int primary key;

当然也可以删除主键

1	alter table user drop primary key;

还有一种联合主键，这种主键是由两个或两个以上的字段形成一个元组，把这个元组整体作为主键

在添加一条记录时，只要联合主键中有一个字段的值不一样就能添加成功。

mysql> create table user2(
    -> id int,
    -> name varchar(20),
    -> password varchar(20),
    -> primary key (id,name)#id与name作为联合主键
    -> );

mysql> desc user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

自增约束

主键由系统自动递增分配

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

这样就可以在添加元组时只添加name字段

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

mysql> insert into user3 values(1,'狗子');
Query OK, 1 row affected (0.04 sec)

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

mysql> insert into user3 (name)values('小猫');#只写了name字段
Query OK, 1 row affected (0.03 sec)

mysql> select * from user3;
+----+--------+
| id | name   |
+----+--------+
|  1 | 狗子   |
|  2 | 小猫   |
+----+--------+
2 rows in set (0.00 sec)

唯一约束

使用unique来表明某字段必须是唯一的

可以在定义表时就说明

mysql> create table user4(
    -> id int,
    -> name varchar(20),
    -> unique(name)#name具有唯一性
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

也可以在定义完一个表之后再添加唯一约束

mysql> create table user5(
    -> id int ,
    -> name varchar(20)
    -> );

#方法1
alter table user5 add unique(name);
#方法2
alter table user5 modify name varchar(20) unique;

删除唯一约束可以使用以下命令

1	alter table user5 drop index name;

类似联合主键，也可以定义如下

mysql> create table user7(
    -> id int,
    -> name varchar(20),
    -> unique(id,name)#保证了id和name不能同时一样
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> describe user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

非空约束

表明某字段不能为空

mysql> create table user9(
    -> id int,
    -> name varchar(20) not null#name字段不允许为空
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into user9 (name)values('狗');
Query OK, 1 row affected (0.01 sec)

#添加失败，因为name字段没有传值，默认为空了
mysql> insert into user9 (id)values(2);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

默认约束

当插入字段值时，如果没有传值，就会使用默认值

mysql> create table user10(
    -> id int,
    -> name varchar(20),
    -> age int default 10#默认值为10
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user10 (id,name ) values(1,'猫咪');#没有给age传值
Query OK, 1 row affected (0.01 sec)

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

外键约束

涉及到两个表

子表的操作要参照父表，父表只有在未被子表refer时才能变动

#父表
mysql> create table classes(
    -> id int primary key,
    -> name varchar(20)
    -> );

#子表
mysql> create table students(
    -> id int primary key,
    -> name varchar(20),
    -> class_id int,
    -> foreign key(class_id) references classes(id)
    -> );

#在父表中插入数据
mysql> insert into classes values(1,'一班');
Query OK, 1 row affected (0.04 sec)

mysql> insert into classes values(2,'二班');
Query OK, 1 row affected (0.04 sec)

mysql> insert into classes values(3,'三班');
Query OK, 1 row affected (0.00 sec)

mysql> insert into classes values(4,'四班');
Query OK, 1 row affected (0.04 sec)

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

#在子表中插入数据
mysql> insert into students values(1001,'张安',1);
Query OK, 1 row affected (0.04 sec)

mysql> insert into students values(1002,'张安',2);
Query OK, 1 row affected (0.04 sec)

mysql> insert into students values(1003,'张安',3);
Query OK, 1 row affected (0.04 sec)

mysql> insert into students values(1004,'张安',4);
Query OK, 1 row affected (0.03 sec)

但是当插入(1005,'李四'，5)时会报错，因为父表的id字段中只有1，2，3，4而没有5

1
2

mysql> insert into students values(1005,'李四',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

总结一下：

父表classes中没有的数据值，在子表中，是不能使用的

父表中的记录若被子表引用，是不可被删除的

数据库的三大设计范式

第一范式

数据包中的所有字段都是不可分割的原子值

第二范式

在满足第一范式的前提下，除主键以外的其他列都必须完全依赖于主键列。如果出现不完全依赖，只可能发生在联合主键的情况下

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

在表myorder中，product_name 只依赖于 product_id ，customer_name 只依赖于 customer_id 。也就是说，product_name 和 customer_id 是没有关系的，customer_name 和 product_id 也是没有关系的

这就不满足第二范式：其他列都必须完全依赖于主键列！

可以通过拆分的方法使其满足第二范式

mysql> create table myorder(
    -> order_id int primary key,
    -> product_id int,
    -> customer_id int
    -> );
Query OK, 0 rows affected (0.08 sec)

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

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

这三个表都分别满足第二范式

第三范式

在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系

也就是要确保数据表中的每一列数据都和主键直接相关，而不能间接相关，这样能减小数据冗余

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    customer_phone VARCHAR(15)
);

表中的 customer_phone 有可能依赖于 order_id （主键）、 customer_id（非主键）两列，也就不满足第三范式的设计：其他列之间不能有传递依赖关系。

同样可以通过拆分来使其满足第三范式

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    phone VARCHAR(15)
);

修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！

查询练习

准备数据

#创建一个新的数据库
create database selectTest;
#选择该数据库
use selectTest;

#创建学生表
mysql> create table student(
    -> sno varchar(20) primary key,
    -> sname varchar(20) not null,
    -> ssex varchar(10) 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, -- 教师编号
    -- 表示该 tno 来自于 teacher 表中的 tno 字段值
    FOREIGN KEY(tno) REFERENCES teacher(tno) 
);

#创建成绩表
CREATE TABLE score (
    sno VARCHAR(20),-- 学生编号
    cno VARCHAR(20) NOT NULL, -- 课程号
    degree DECIMAL,	-- 成绩
    -- 表示该 sno, cno 分别来自于 student, course 表中的 no 字段值
    FOREIGN KEY(sno) REFERENCES student(sno),	
    FOREIGN KEY(cno) REFERENCES course(cno),
    PRIMARY KEY(sno,cno)
);

-- 添加学生表数据
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');

-- 添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');

-- 添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');

-- 添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

1-10

#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 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)

#2.查询student表中的所有记录的sname、ssex和class列
mysql> select sname ,ssex,class from student;
+-----------+------+-------+
| sname     | ssex | class |
+-----------+------+-------+
| 曾华      | 男   | 95033 |
| 匡明      | 男   | 95031 |
| 王丽      | 女   | 95033 |
| 李军      | 男   | 95033 |
| 王芳      | 女   | 95031 |
| 陆军      | 男   | 95031 |
| 王尼玛    | 男   | 95033 |
| 张全蛋    | 男   | 95031 |
| 赵铁柱    | 男   | 95031 |
+-----------+------+-------+
9 rows in set (0.00 sec)

# 3.查询教师的所有单位，即不重复的depart列
mysql> select distinct depart from teacher;#distinct用于去重
+-----------------+
| depart          |
+-----------------+
| 计算机系        |
| 电子工程系      |
+-----------------+
2 rows in set (0.00 sec)

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

### 方法1：使用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 |
+-----+-------+--------+
4 rows in set (0.00 sec)

### 方法2：使用比较运算符
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 |
+-----+-------+--------+
3 rows in set (0.00 sec)


# 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 |
+-----+-----------+------+---------------------+-------+
6 rows in set (0.00 sec)

# 7.以class降序查询student表的所有记录
mysql> select * from student order by class desc;#默认是升序asc，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 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)

# 8.以cno升序、degree降序(当cno相同时)查询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 rows in set (0.00 sec)


# 9.查询“95031”班的学生人数
mysql> select count(*) from student where class='95031';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

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

# 子查询方式
mysql> select sno,cno from score where degree =(select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.01 sec)

#排序方式
mysql> select sno,cno from score order by degree (desc) limit 0,1;
+-----+-------+
| sno | cno   |
+-----+-------+
| 109 | 3-245 |
+-----+-------+
1 row in set (0.00 sec)
#注释：limit和python中的range作用类似

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

#先看一下总共有哪些课程
mysql> select * from course;
+-------+-----------------+-----+
| cno   | cname           | tno |
+-------+-----------------+-----+
| 3-105 | 计算机导论      | 825 |
| 3-245 | 操作系统        | 804 |
| 6-166 | 数字电路        | 856 |
| 9-888 | 高等数学        | 831 |
+-------+-----------------+-----+
4 rows in set (0.00 sec)

#然后可以使用avg函数求平均值
mysql> select avg(degree) from score where cno='3-105';
+-------------+
| avg(degree) |
+-------------+
|     85.3333 |
+-------------+
1 row in set (0.00 sec)

mysql> select avg(degree) from score where cno='3-245';
+-------------+
| avg(degree) |
+-------------+
|     76.3333 |
+-------------+
1 row in set (0.00 sec)

mysql> select avg(degree) from score where cno='6-166';
+-------------+
| avg(degree) |
+-------------+
|     81.6667 |
+-------------+
1 row in set (0.00 sec)

mysql> select avg(degree) from score where cno='9-888';
+-------------+
| avg(degree) |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

#也可以写在一条SQL语句中（使用group by）
mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+
3 rows in set (0.00 sec)

#12.查询score表中至少有2名学生选修的并以3开头的课程的平均分数
mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+
3 rows in set (0.00 sec)
#这样查出来是所有课程的平均成绩，接下来只选出以3开头的(使用like)
mysql> select cno,avg(degree) from score group by cno
    -> having cno like '3%';
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
+-------+-------------+
2 rows in set (0.00 sec)

#那还有"至少有两名同学选修"这个条件，这个例子只是刚好凑巧了，我们需要这样写才对：
mysql> select cno,avg(degree) from score group by cno
    -> having count(cno)>=2 and cno like '3%';
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
+-------+-------------+
2 rows in set (0.00 sec)

注释：having(过滤的意思) 用于分组后，where用于分组前

# 13. 查询分数大于70，小于90的sno列
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 |
+-----+--------+
7 rows in set (0.00 sec)
#或者
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 |
+-----+--------+
7 rows in set (0.00 sec)

多表查询

# 14.查询所有学生的sname、cno和degree列
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 |
+-----------+-------+--------+
9 rows in set (0.01 sec)

# 15.查询所有学生的sno、cname和degree列
mysql> select sno,cname,degree from course,score where course.cno=score.cno;
+-----+-----------------+--------+
| sno | cname           | degree |
+-----+-----------------+--------+
| 103 | 计算机导论      |     92 |
| 103 | 操作系统        |     86 |
| 103 | 数字电路        |     85 |
| 105 | 计算机导论      |     88 |
| 105 | 操作系统        |     75 |
| 105 | 数字电路        |     79 |
| 109 | 计算机导论      |     76 |
| 109 | 操作系统        |     68 |
| 109 | 数字电路        |     81 |
+-----+-----------------+--------+
9 rows in set (0.00 sec)

#16. 查询所有学生的sname、cname和degree列
mysql> select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;
+-----------+-----------------+--------+
| sname     | cname           | degree |
+-----------+-----------------+--------+
| 王丽      | 计算机导论      |     92 |
| 王丽      | 操作系统        |     86 |
| 王丽      | 数字电路        |     85 |
| 王芳      | 计算机导论      |     88 |
| 王芳      | 操作系统        |     75 |
| 王芳      | 数字电路        |     79 |
| 赵铁柱    | 计算机导论      |     76 |
| 赵铁柱    | 操作系统        |     68 |
| 赵铁柱    | 数字电路        |     81 |
+-----------+-----------------+--------+
9 rows in set (0.00 sec)

#more,用as给字段起个别名
mysql> select sname,cname,degree ,student.sno as stu_no,course.cno as cour_no from student,course,score where student.sno=score.sno and course.cno=score.cno;
+-----------+-----------------+--------+--------+---------+
| sname     | cname           | degree | stu_no | cour_no |
+-----------+-----------------+--------+--------+---------+
| 王丽      | 计算机导论      |     92 | 103    | 3-105   |
| 王丽      | 操作系统        |     86 | 103    | 3-245   |
| 王丽      | 数字电路        |     85 | 103    | 6-166   |
| 王芳      | 计算机导论      |     88 | 105    | 3-105   |
| 王芳      | 操作系统        |     75 | 105    | 3-245   |
| 王芳      | 数字电路        |     79 | 105    | 6-166   |
| 赵铁柱    | 计算机导论      |     76 | 109    | 3-105   |
| 赵铁柱    | 操作系统        |     68 | 109    | 3-245   |
| 赵铁柱    | 数字电路        |     81 | 109    | 6-166   |
+-----------+-----------------+--------+--------+---------+
9 rows in set (0.00 sec)
#more，不指明哪个表的sno和cno会报错，因为有多个表(如score表中有这两个字段)都有该字段
mysql> select sname,cname,degree ,student.sno,course.cno from student,course,score where student.sno=score.sno and course.cno=score.cno;
+-----------+-----------------+--------+-----+-------+
| sname     | cname           | degree | sno | cno   |
+-----------+-----------------+--------+-----+-------+
| 王丽      | 计算机导论      |     92 | 103 | 3-105 |
| 王丽      | 操作系统        |     86 | 103 | 3-245 |
| 王丽      | 数字电路        |     85 | 103 | 6-166 |
| 王芳      | 计算机导论      |     88 | 105 | 3-105 |
| 王芳      | 操作系统        |     75 | 105 | 3-245 |
| 王芳      | 数字电路        |     79 | 105 | 6-166 |
| 赵铁柱    | 计算机导论      |     76 | 109 | 3-105 |
| 赵铁柱    | 操作系统        |     68 | 109 | 3-245 |
| 赵铁柱    | 数字电路        |     81 | 109 | 6-166 |
+-----------+-----------------+--------+-----+-------+
9 rows in set (0.00 sec)

子查询

# 17.查询“95031”班学生每门课的平均分
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 |
+-------+-------------+
3 rows in set (0.00 sec)

# 18.查询选修“3-105”课程的成绩高于“109”号同学“3-105”成绩的所有同学的记录
mysql> select * from score where cno='3-105'and  degree>(select degree from score where sno='109' and cno='3-105');
+-----+-------+--------+
| 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>(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 |
+-----+-------+--------+
6 rows in set (0.00 sec)

# 20. 查询和学号为108、101的同学同年出生的所有学生的sno、sanme和sbirthday列
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 |
+-----+-----------+---------------------+
4 rows in set (0.00 sec)

# 21.查询“张旭”教师任课的学生成绩
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 |
+-----+-------+--------+
3 rows in set (0.00 sec)

# 22. 查询选修某课程的同学人数多于5人的教师姓名
#先再插入几条数据
mysql> insert into score values('101','3-105','90');
Query OK, 1 row affected (0.04 sec)

mysql> insert into score values('102','3-105','91');
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values('104','3-105','89');
Query OK, 1 row affected (0.04 sec)

#开始查询
mysql> select tname from teacher where tno in (select tno from course where cno=(select cno from score  group by cno having count(*)>5));
+--------+
| tname  |
+--------+
| 王萍   |
+--------+
1 row in set (0.00 sec)

# 23.查询95033班和95031班全体学生的记录
#先插入几条数据
mysql> insert into student values('110','张飞','男','1974-06-03','95038');
Query OK, 1 row affected (0.04 sec)
#查询
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 |
+-----+-----------+------+--
9 rows in set (0.00 sec)

# 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 |
+-------+--------+
6 rows in set (0.00 sec)

# 25. 查询出“计算机系”教师所教课程的成绩表
mysql> select * from score where cno in (select cno from course where tno in (select tno from teacher where depart="计算机系"));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+
9 rows in set (0.00 sec)

union(求并集) 和not in

# 26. 查询“计算机系”与“电子工程系”不同职称的教师的tname和prof(取交集之外的记录)
mysql> select tname,prof from teacher where depart ='计算机系' and prof not in (select prof from teacher where depart ='电子工程系')
    -> union
    -> select tname,prof from teacher where depart ='电子工程系' and prof not in (select prof from teacher where depart ='计算机系');
+--------+-----------+
| tname  | prof      |
+--------+-----------+
| 李诚   | 副教授    |
| 张旭   | 讲师      |
+--------+-----------+
2 rows in set (0.01 sec)

any：至少一个（只要有一个就行）

# 27. 查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学的成绩中的任意一个的cno、sno和degree
mysql> select cno,sno,degree from score
    -> where cno='3-105'
    -> and degree>any(select degree from score where cno='3-245')
    -> order by degree desc;
+-------+-----+--------+
| cno   | sno | degree |
+-------+-----+--------+
| 3-105 | 103 |     92 |
| 3-105 | 102 |     91 |
| 3-105 | 101 |     90 |
| 3-105 | 104 |     89 |
| 3-105 | 105 |     88 |
| 3-105 | 109 |     76 |
+-------+-----+--------+
6 rows in set (0.00 sec)

all：全部（都要满足）

# 28.查询编号为‘3-105’且成绩高于编号为‘3-245’课程的同学的cno、sno和degree
mysql> select cno,sno,degree from score
    -> where cno='3-105'
    -> and degree>all(select degree from score where cno='3-245')
    -> order by degree desc;
+-------+-----+--------+
| cno   | sno | degree |
+-------+-----+--------+
| 3-105 | 103 |     92 |
| 3-105 | 102 |     91 |
| 3-105 | 101 |     90 |
| 3-105 | 104 |     89 |
| 3-105 | 105 |     88 |
+-------+-----+--------+
5 rows in set (0.00 sec)

as取别名

# 29.查询所有教师和同学的name、sex和birthday
mysql> select tname as 姓名,tsex as 性别,tbirthday 生日 from teacher
    -> union#列名跟随第一个
    -> select sname,ssex,sbirthday from student;
+-----------+--------+---------------------+
| 姓名      | 性别   | 生日                |
+-----------+--------+---------------------+
| 李诚      | 男     | 1958-12-02 00:00:00 |
| 王萍      | 女     | 1972-05-05 00:00:00 |
| 刘冰      | 女     | 1977-08-14 00:00:00 |
| 张旭      | 男     | 1969-03-12 00:00:00 |
| 曾华      | 男     | 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 |
+-----------+--------+---------------------+
14 rows in set (0.00 sec)

union求并集

# 30.查询所有“女”教师和“女”同学的name，sex和birthday
mysql> select tname as 姓名,tsex as 性别,tbirthday 生日 from teacher where tsex='女'
    -> union
    -> select sname,ssex,sbirthday from student where ssex='女';
+--------+--------+---------------------+
| 姓名   | 性别   | 生日                |
+--------+--------+---------------------+
| 王萍   | 女     | 1972-05-05 00:00:00 |
| 刘冰   | 女     | 1977-08-14 00:00:00 |
| 王丽   | 女     | 1976-01-23 00:00:00 |
| 王芳   | 女     | 1975-02-10 00:00:00 |
+--------+--------+---------------------+
4 rows in set (0.00 sec)

# 31. 查询成绩比该课程平均成绩低的同学的成绩表
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 |
+-----+-------+--------+
5 rows in set (0.00 sec)
-- 但是我感觉上面的不对，正确的如下：
mysql> select * from score a where degree<(select avg(degree) from score b group by cno having 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 |
+-----+-------+--------+
5 rows in set (0.00 sec)

子查询

# 32.查询所有任课教师的tname和depart
mysql> select tname ,depart from teacher where tno in (select tno from course);
+--------+-----------------+
| tname  | depart          |
+--------+-----------------+
| 李诚   | 计算机系        |
| 王萍   | 计算机系        |
| 刘冰   | 电子工程系      |
| 张旭   | 电子工程系      |
+--------+-----------------+
4 rows in set (0.00 sec)

# 33. 查询至少有两名男生的班号
mysql> select class from student  where ssex='男' group by class having count(class)>=2;
+-------+
| class |
+-------+
| 95031 |
| 95033 |
+-------+
2 rows in set (0.00 sec)

not like

# 查询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 |
| 108 | 张全蛋    | 男   | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男   | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞      | 男   | 1974-06-03 00:00:00 | 95038 |
+-----+-----------+------+---------------------+-------+
7 rows in set (0.00 sec)

year和now函数

# 35.查询student表中每个学生的姓名和年龄
mysql> select sname,year(now())-year(sbirthday) as '年龄' from student;#as可省略
+-----------+--------+
| sname     | 年龄   |
+-----------+--------+
| 曾华      |     43 |
| 匡明      |     45 |
| 王丽      |     44 |
| 李军      |     44 |
| 王芳      |     45 |
| 陆军      |     46 |
| 王尼玛    |     44 |
| 张全蛋    |     45 |
| 赵铁柱    |     46 |
| 张飞      |     46 |
+-----------+--------+
10 rows in set (0.00 sec)

max与min

# 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 |
+---------------------+---------------------+
1 row in set (0.04 sec)

多字段排序

# 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 |
| 104 | 李军      | 男   | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛    | 男   | 1976-02-20 00:00:00 | 95033 |
| 101 | 曾华      | 男   | 1977-09-01 00:00:00 | 95033 |
| 106 | 陆军      | 男   | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男   | 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 |
+-----+-----------+------+---------------------+-------+
10 rows in set (0.00 sec)

子查询

# 38.查询“男”教师及其所上的课程
mysql> select * from course where tno in (select tno from teacher where tsex='男');
+-------+--------------+-----+
| cno   | cname        | tno |
+-------+--------------+-----+
| 3-245 | 操作系统     | 804 |
| 6-166 | 数字电路     | 856 |
+-------+--------------+-----+
2 rows in set (0.00 sec)

max函数与子查询

# 39.查询最高分同学的sno、cno和degree列
mysql> select sno,cno,degree from score where degree =(select max(degree) from score);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+
1 row in set (0.00 sec)

继续子查询

# 40.查询和“李军”同性别的所有同学的sname
mysql> select sname from student where ssex =(select ssex from student where sname='李军');
+-----------+
| sname     |
+-----------+
| 曾华      |
| 匡明      |
| 李军      |
| 陆军      |
| 王尼玛    |
| 张全蛋    |
| 赵铁柱    |
| 张飞      |
+-----------+
8 rows in set (0.00 sec)

# 41.查询和"李军"同性别且同班级的同学的sname
mysql> select sname from student
    -> where ssex =(select ssex from student where sname='李军')
    -> and
    -> class=(select class from student where sname ='李军');
+-----------+
| sname     |
+-----------+
| 曾华      |
| 李军      |
| 王尼玛    |
+-----------+
3 rows in set (0.01 sec)

# 42.查询所有选修“计算机导论”课程的“男”同学的成绩
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 |
+-----+-------+--------+
4 rows in set (0.00 sec)

按等级查询

# 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');

SELECT * FROM grade;
+------+------+-------+
| low  | upp  | grade |
+------+------+-------+
|   90 |  100 | A     |
|   80 |   89 | B     |
|   70 |   79 | C     |
|   60 |   69 | D     |
|    0 |   59 | E     |
+------+------+-------+

# 现在查询所有同学的sno,cno和grade列
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     |
+-----+-------+-------+
12 rows in set (0.00 sec)

SQL的四种连接查询

内连接：inner join 或者join

外连接：

左连接：left join 或者left outer join

右连接：right join 或者right outer join

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

# 先准备一些数据，用于连接查询练习
CREATE DATABASE testJoin;

CREATE TABLE person (
    id INT,
    name VARCHAR(20),
    cardId INT
);

CREATE TABLE card (
    id INT,
    name VARCHAR(20)
);

INSERT INTO card VALUES (1, '饭卡'), (2, '建行卡'), (3, '农行卡'), (4, '工商卡'), (5, '邮政卡');
SELECT * FROM card;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 饭卡      |
|    2 | 建行卡    |
|    3 | 农行卡    |
|    4 | 工商卡    |
|    5 | 邮政卡    |
+------+-----------+

INSERT INTO person VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);
SELECT * FROM person;
+------+--------+--------+
| id   | name   | cardId |
+------+--------+--------+
|    1 | 张三   |      1 |
|    2 | 李四   |      3 |
|    3 | 王五   |      6 |
+------+--------+--------+

注意：

我们并没有创建外键

王五的cardID不能对应id

# 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 | 农行卡    |
+------+--------+--------+------+-----------+
2 rows in set (0.00 sec)

# left join (左外连接)查询，这种方式会把左边表的所有数据取出来，而右边的数据，如果有相等的就取出来，如果没有就会补NULL
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 rows in set (0.00 sec)

# right join(右外连接)，这种方式会把右边表的所有数据取出来，而左边的数据，如果有相等的就显示出来，如果没有就会补NULL
mysql> select * from person right (outer) join card on person.cardId =card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+
5 rows in set (0.00 sec)

#  full join (全外连接)：没有就补NULL，一个也不能落下
-- mysql不支持

事务

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

在实际项目中，假设只有一条 SQL 语句执行成功，而另外一条执行失败了，就会出现数据前后不一致。

因此，在执行多条有关联 SQL 语句时，事务可能会要求这些 SQL 语句要么同时执行成功，要么就都执行失败。

如何控制事务

在MySQL中，事务的自动提交默认开启

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

开启之后，不能回滚(无法反悔)，可以设置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)

此时可以通过rollback回滚来回到上一次提交的状态，通过commit来手动提交，举个例子

CREATE DATABASE bank;

USE bank;

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    money INT
);

INSERT INTO user VALUES (1, 'a', 1000);
INSERT INTO user VALUES (2, 'b', 1000);

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

#由于关闭了autocommit，所以可以回滚到上一次提交的状态（上一次提交时我们还没有创建这里的表，所以是空了）
mysql> rollback;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from user;
Empty set (0.00 sec)

#现在再把数据插入一条
mysql> INSERT INTO user VALUES (1, 'a', 1000);
Query OK, 1 row affected (0.00 sec)

mysql>

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

#然后手动提交
mysql> commit;
Query OK, 0 rows affected (0.04 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.04 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)
#由于手动提交了一次，所以现在只能回到上次提交时的状态了

手动开启一个事务(局部)

可以使用 begin 或者 start transaction

-- 使用 BEGIN 或者 START TRANSACTION 手动开启一个事务
#先开启自动提交
mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

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

#开启一个事务
-- START TRANSACTION;
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

-- 由于手动开启的事务没有开启自动提交，
-- 此时发生变化的数据仍然是被保存在一张临时表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 测试回滚
ROLLBACK;
#看，在开启自动提交时，使用beigin或者start transaction可以回滚
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

仍然使用 COMMIT 提交数据，提交后无法再发生本次事务的回滚。

#此时已开启自动提交，但是之后又开启了一个事务，当事务结束时，我们可以通过手动提交的方式来结束事务，现在再来测试
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 手动提交数据
COMMIT;

-- 测试回滚（无效，因为表的数据已经被提交）
ROLLBACK;
mysql> SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
2 rows in set (0.00 sec)

事务的四大特性

A 原子性

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

C 一致性

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。　拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

I 隔离性

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

D 持久性

事务一旦结束，就不可返回(比如commit之后就不能rollback)

事务的隔离性可分为4种(性能由低到高)：隔离级别越高，性能越低

READ UNCOMMITTED ( 读取未提交 )

如果有多个事务，那么任意事务都可以看见其他事务的未提交数据。
READ COMMITTED ( 读取已提交 )

只能读取到其他事务已经提交的数据。
REPEATABLE READ ( 可被重复读 ),默认是这个

如果有多个连接都开启了事务，那么事务之间不能共享数据记录，否则只能共享已提交的记录。
SERIALIZABLE ( 串行化 )

所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作。

查看当前数据库的默认隔离级别：

-- MySQL 8.x, GLOBAL 表示系统级别，不加表示会话级别。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                | -- MySQL的默认隔离级别，可以重复读。
+--------------------------------+

-- MySQL 5.x
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;

修改隔离级别：

-- 设置系统隔离级别，LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 查询系统隔离级别，发现已经被修改。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+

1.脏读（READ UNCOMMITTED）

测试 READ UNCOMMITTED ( 读取未提交的 ) 的隔离性：

INSERT INTO user VALUES (3, '小明', 1000);
INSERT INTO user VALUES (4, '淘宝店', 1000);

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

-- 开启一个事务操作数据
-- 假设小明在淘宝店买了一双800块钱的鞋子：
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

-- 然后淘宝店在另一方查询结果，发现钱已到账。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+

由于小明的转账是在新开启的事务上进行操作的，而该操作的结果是可以被其他事务（另一方的淘宝店）看见的，因此淘宝店的查询结果是正确的，淘宝店确认到账。但就在这时，如果小明在它所处的事务上又执行了 ROLLBACK 命令，会发生什么？

-- 小明所处的事务
ROLLBACK;

-- 此时无论对方是谁，如果再去查询结果就会发现：
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

这就是所谓的脏读，一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。

2.不可重复读：READ COMMITTED ( 读取已提交的)

把隔离级别设置为 READ COMMITTED ：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+

这样，再有新的事务连接进来时，它们就只能查询到已经提交过的事务数据了。但是对于当前事务来说，它们看到的还是未提交的数据，例如：

-- 正在操作数据事务（当前事务）
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

-- 虽然隔离级别被设置为了 READ COMMITTED，但在当前事务中，
-- 它看到的仍然是数据表中临时改变数据，而不是真正提交过的数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+


-- 假设此时在远程开启了一个新事务，连接到数据库。
$ mysql -u root -p

-- 此时远程连接查询到的数据只能是已经提交过的
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

但是这样还有问题，那就是假设一个事务在操作数据时，其他事务干扰了这个事务的数据。例如：

-- 小张在查询数据的时候发现：
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+

-- 在小张求表的 money 平均值之前，小王做了一个操作：
START TRANSACTION;
INSERT INTO user VALUES (5, 'c', 100);
COMMIT;

-- 此时表的真实数据是：
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

-- 这时小张再求平均值的时候，就会出现计算不相符合的情况：
SELECT AVG(money) FROM user;
+------------+
| AVG(money) |
+------------+
|  820.0000  |
+------------+

虽然 READ COMMITTED 让我们只能读取到其他事务已经提交的数据，但还是会出现问题，就是在读取同一个表的数据时，可能会发生前后不一致的情况。这被称为不可重复读现象 ( READ COMMITTED )。

3.幻读：REPEATABLE READ ( 可被重复读 )

将隔离级别设置为 REPEATABLE READ ( 可被重复读取 ) :

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+

测试 REPEATABLE READ ，假设在两个不同的连接上分别执行 START TRANSACTION :

-- 小张 - 成都
START TRANSACTION;
INSERT INTO user VALUES (6, 'd', 1000);

-- 小王 - 北京
START TRANSACTION;

-- 小张 - 成都
COMMIT;

当前事务开启后，没提交之前，查询不到，提交后可以被查询到。但是，在提交之前其他事务被开启了，那么在这条事务线上，就不会查询到当前有操作事务的连接。相当于开辟出一条单独的线程。

无论小张是否执行过 COMMIT ，在小王这边，都不会查询到小张的事务记录，而是只会查询到自己所处事务的记录：

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

这是因为小王在此之前开启了一个新的事务 ( START TRANSACTION ) ，那么在他的这条新事务的线上，跟其他事务是没有联系的，也就是说，此时如果其他事务正在操作数据，它是不知道的。

然而事实是，在真实的数据表中，小张已经插入了一条数据。但是小王此时并不知道，也插入了同一条数据，会发生什么呢？

1 2	INSERT INTO user VALUES (6, 'd', 1000); -- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

报错了，操作被告知已存在主键为 6 的字段。这种现象也被称为幻读，一个事务提交的数据，不能被其他事务读取到。

4.串行化：SERIALIZABLE

顾名思义，就是所有事务的写入操作全都是串行化的。什么意思？把隔离级别修改成 SERIALIZABLE :

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+

还是拿小张和小王来举例：

-- 小张 - 成都
START TRANSACTION;

-- 小王 - 北京
START TRANSACTION;

-- 开启事务之前先查询表，准备操作数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
+----+-----------+-------+

-- 发现没有 7 号王小花，于是插入一条数据：
INSERT INTO user VALUES (7, '王小花', 1000);

此时会发生什么呢？由于现在的隔离级别是 SERIALIZABLE ( 串行化 ) ，串行化的意思就是：假设把所有的事务都放在一个串行的队列中，那么所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作 ( 这意味着队列中同时只能执行一个事务的写入操作 ) 。

根据这个解释，小王在插入数据时，会出现等待状态，直到小张执行 COMMIT 结束它所处的事务，或者出现等待超时。

参考：

https://www.bilibili.com/video/BV1Vt411z7wy?p=64

https://github.com/hjzCy/sql_node/blob/master/mysql/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0.md

登录