mysql实战

mosh

• Mysql实战
• 1 终端操作数据库
  • 1.1 登陆数据库
  • 1.2 查询数据库服务器中所有的数据
  • 1.3 选中某一个数据库进行操作
  • 1.4 退出数据库服务器
  • 1.5 在数据库服务器中创建数据库
  • 1.6 查看某个数据库中所有的数据表
  • 1.7 创建一个数据表
  • 1.8 查看数据表是否创建成功
  • 1.9 查看创建好的数据表的结构
  • 1.10 查看表中的记录
  • 1.11 往数据表中添加数据记录
  • 1.12 mysql常用数据类型
  • 1.13 数据类型如何选择
  • 1.14 删除数据
  • 1.15 修改数据
  • 总结
• 2 数据库建表约束
  • 2.1 主键约束
  • 2.2 自增约束
  • 2.3 唯一约束
  • 2.4 非空约束
  • 2.5 默认约束
  • 2.6 外键约束
• 3 数据表设计范式
  • 3.1 第一范式1NF
  • 3.2 第二范式2NF
  • 3.3 第三范式3NF
• 4 查询练习
  • 4.1 数据准备
  • 4.2 1到10
  • 4.3 10过程解析
  • 4.4 分组计算平均成绩
  • 4.5 分组条件与模糊查询
  • 4.6 范围查询的两种方式
  • 4.7 多表查询
  • 4.8 三表关联查询
  • 4.9 子查询加分组求平均分
  • 4.10 子查询
  • 4.11 year函数与待in关键字的子查询
  • 4.12 多层嵌套子查询
  • 4.13 多表查询
  • 4.14 in表示或者关系
  • 4.15 where条件查询
  • 4.16 子查询
  • 4.17 union和notin的使用
  • 4.18 any表示至少一个desc降序
  • 4.19 all表示所有
  • 4.20 as取别名-union求并集
  • 4.21 union求并集
  • 4.22 复制表数据做条件查询
  • 4.23 子查询
  • 4.24 条件加分组筛选
  • 4.25 notlike模糊查询取反
  • 4.26 year函数与now函数
  • 4.27 max与min函数
  • 4.28 多字段排序
  • 4.29 子查询
  • 4.30 max函数与子查询
  • 4.31 子查询
  • 4.32 按等级查询
• 5 索引
  • 5.1 普通索引
  • 5.2 唯一索引
• 6 连接查询
  • 6.1 内连接
  • 6.2 外连接
• 7 事务
  • 7.1 事务用来做什么
  • 7.2 如何开启事务
  • 7.3 commit-rollback
  • 7.4 手动开启事务begin-start-transaction
  • 7.5 ACID特征与使用
• 8 事务隔离性
  • 8.1 脏读
  • 8.2 不可重复读
  • 8.3 幻读
  • 8.4 串行化

---

关系型数据库

1 终端操作数据库

1.1 登陆数据库

mysql -u root -p 83848521

1.2 查询数据库服务器中所有的数据

show databases;
#显示
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

1.3 选中某一个数据库进行操作

use sys;
#显示
mysql> use sys
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

1.4 退出数据库服务器

mysql> exit
Bye

1.5 在数据库服务器中创建数据库

create database test;
#显示
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

1.6 查看某个数据库中所有的数据表

show tables;
#显示
mysql> show tables;
Empty set (0.00 sec)

1.7 创建一个数据表

create table pet (
	name varchar(20),
	owner varchar(20),
	species varchar(20),
	sex char(1),
	birth date,
	death date
);
#显示
mysql> create table pet (
-> name varchar(20),
-> owner varchar(20),
-> species varchar(20),
-> sex char(1),
-> birth date,
-> death date);
Query OK, 0 rows affected (0.00 sec)

1.8 查看数据表是否创建成功

show tables;
#显示
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet            |
+----------------+
1 row in set (0.00 sec)

1.9 查看创建好的数据表的结构

describe pet;
#显示
mysql> describe 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.00 sec)

1.10 查看表中的记录

select * from pet;
#显示
mysql> select * from pet;
Empty set (0.01 sec)

1.11 往数据表中添加数据记录

insert into pet values('Puffbal', 'Diane', 'hamster', 'f', '1999-03-30', NULL);
#显示
mysql> insert into pet
-> values('Puffbal', 'Diane', 'hamster', 'f', '1999-03-30', NULL);
Query OK, 1 row affected (0.00 sec)

#再次查询
select * from pet;
#显示
mysql> select * from pet;
+---------+-------+---------+------+------------+-------+
| name    | owner | species | sex  | birth      | death |
+---------+-------+---------+------+------------+-------+
| Puffbal | Diane | hamster | f    | 1999-03-30 | NULL  |
+---------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

#再次插入
insert into pet values('旺财', '周星驰', '狗', '公', '1990-01-01', NULL);
#显示
mysql> insert into pet
-> values('旺财', '周星驰', '狗', '公', '1990-01-01', NULL);
Query OK, 1 row affected (0.00 sec)

#再次查询
mysql> select * from pet;
+---------+-----------+---------+------+------------+-------+
| name    | owner     | species | sex  | birth      | death |
+---------+-----------+---------+------+------------+-------+
| Puffbal | Diane     | hamster | f    | 1999-03-30 | NULL  |
| 旺财    | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
+---------+-----------+---------+------+------------+-------+
2 rows in set (0.00 sec)

1.12 mysql常用数据类型

• 数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

create table testType (
	number tinyint
);
#显示
mysql> create table testType (
-> number tinyint
-> );
Query OK, 0 rows affected (0.00 sec)

#查看表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet            |
| testType       |
+----------------+
2 rows in set (0.01 sec)

#查看
mysql> describe testType;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| number | tinyint | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

#插入值超过表示范围
mysql> insert into testType values(128);
ERROR 1264 (22003): Out of range value for column 'number' at row 1

• 日期和时间类型

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

• 字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

1.13 数据类型如何选择

• 日期选择按照格式
• 数值和字符串按照大小

1.14 删除数据

delete from pet where name = 'Puffbal';
#显示
mysql> delete from pet where name = 'Puffbal';
Query OK, 1 row affected (0.00 sec)

#查看
mysql> select * from pet;
+--------+-----------+---------+------+------------+-------+
| name   | owner     | species | sex  | birth      | death |
+--------+-----------+---------+------+------------+-------+
| 旺财   | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
+--------+-----------+---------+------+------------+-------+
1 row in set (0.00 sec)
#已删除

1.15 修改数据

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

#查看
mysql> select * from pet;
+-----------+-----------+---------+------+------------+-------+
| name      | owner     | species | sex  | birth      | death |
+-----------+-----------+---------+------+------------+-------+
| 旺旺财    | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
+-----------+-----------+---------+------+------------+-------+
1 row in set (0.00 sec)
#已修改

总结

• 数据库中的常见操作

#增
insert
#删
delete
#改
update
#查
select

2 数据库建表约束

2.1 主键约束

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

mysql> create table user (
	id int primary key,
	name varchar(20)
);
#显示
mysql> create table user (
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.00 sec)

#查看
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet            |
| testType       |
| user           |
+----------------+
3 rows in set (0.00 sec)

#内容
mysql> describe user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• 联合主键

  只要联合主键的主键值加起来不重复就可以

mysql> create table user2 (
	id int,
	name varchar(20),
	password varchar(20),
	primary key(id, name)
);
#显示
mysql> create table user2 (
-> id int,
-> name varchar(20),
-> passeord varchar(20),
-> primary key(id, name)
-> );
Query OK, 0 rows affected (0.00 sec)

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

#插入
mysql> insert into user2 values(2, '张三', '123');
Query OK, 1 row affected (0.00 sec)

#插入
mysql> insert into user2 values(1, '李四', '123');
Query OK, 1 row affected (0.00 sec)

2.2 自增约束

自动管控ID值，自动增加

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

#插入
mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)

#查看
mysql> select * from user3;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

#插入
mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)

#查看
mysql> select * from user3;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
+----+----------+
2 rows in set (0.00 sec)

如果说创建表的时候，忘记添加主键

#新建表
mysql> create table user4 (
 -> id int,
 -> name varchar(20)
 -> );
Query OK, 0 rows affected (0.01 sec)
#查看
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#添加主键
mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
#查看
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#删除主键
mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
#查看
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#修改主键
mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
#查看
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2.3 唯一约束

• 约束修饰的字段的值不可以重复

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

#设置唯一性
mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

#查看
mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#插入
mysql> insert into user5 values(1, 'zhengsan');
Query OK, 1 row affected (0.00 sec)

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

• 直接设置唯一性

  mysql> create table user6 (
   -> id int,
   -> name varchar(20),
   -> unique(name)
   -> );
  Query OK, 0 rows affected (0.00 sec)
  
  #查看
  mysql> desc user6;
  +-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | id    | int         | YES  |     | NULL    |       |
  | name  | varchar(20) | YES  | UNI | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set (0.01 sec)
  

• 删除唯一性

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

• 总结

1、建表的时候可以添加约束

2、可以使用alter、add添加约束

3、可以使用alter、modif添加约束

4、删除alter、drop

2.4 非空约束

• 修饰字段不能为空 NULL

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

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

#插入
mysql> insert into user7 (id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

2.5 默认约束

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

mysql> create table user8 (
 -> id int,
 -> name varchar(20),
 -> age int default 10
 -> );
Query OK, 0 rows affected (0.01 sec)

#查看
mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| 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> insert into user8 (id, name) values(1, 'zhangsan');
Query OK, 1 row affected (0.00 sec)

#查看
mysql> select * from user8;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
+------+----------+------+
1 row in set (0.00 sec)

2.6 外键约束

• 涉及到两个表：父表，子表
• 主表，副表

#班级
mysql> create table classes (
 -> id int primary key,
 -> name varchar(20)
 -> );
Query OK, 0 rows affected (0.01 sec)
#学生表
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.01 sec)

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

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| class_id | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

#插入
mysql> insert into classes values(1, '一班');
Query OK, 1 row affected (0.00 sec)

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

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

mysql> insert into classes values(4, '四班');
Query OK, 1 row affected (0.00 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.00 sec)

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

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

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

#错误
mysql> insert into students values(1004, '张三', 5);
ERROR 1062 (23000): Duplicate entry '1004' for key 'students.PRIMARY'

#查看
mysql> select * from students;
+------+--------+----------+
| id   | name   | class_id |
+------+--------+----------+
| 1001 | 张三   |        1 |
| 1002 | 张三   |        2 |
| 1003 | 张三   |        3 |
| 1004 | 张三   |        4 |
+------+--------+----------+
4 rows in set (0.00 sec)

• 总结
• 主表(父表)，classes中没有的数据值，在副表(子表)中，是不可以使用的。
• 主表中的记录被副表引用，是不可以被删除的

#错误
mysql> delete from classes where id = 4;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

3 数据表设计范式

3.1 第一范式1NF

• 数据表中的字段都是不可分割的原子值？

mysql> create table student2(
 -> id int primary key,
 -> name varchar(30),
 -> address varchar(30)
 -> );
Query OK, 0 rows affected (0.00 sec)

#插入
mysql> insert into student2 values(1, '张三', '黑龙江省绥化市100');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student2 values(2, '张三', '黑龙江省绥化市99');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student2 values(3, '张三', '黑龙江省绥化市98');
Query OK, 1 row affected (0.00 sec)

#查看
mysql> select * from student2;
+----+--------+--------------------------+
| id | name   | address                  |
+----+--------+--------------------------+
|  1 | 张三   | 黑龙江省绥化市100        |
|  2 | 张三   | 黑龙江省绥化市99         |
|  3 | 张三   | 黑龙江省绥化市98         |
+----+--------+--------------------------+
3 rows in set (0.00 sec)
#字段值还可以拆分，就不属于第一范式
#理论上，原子值拆分的越细，可操作性越好

3.2 第二范式2NF

• 满足第一范式的条件下
• 除主键外的每一列都必须完全依赖于主键
• 如果出现不完全依赖，只可能发生在联合主键的情况下

#订单表
create table myorder (
	product_id int,
	customer_id int,
	product_name varchar(20),
	customer_name varchar(20),
	primary key (product_id, customer_id)
);
#问题？
#除主键外的其他列，只依赖于主键的部分字段
#拆表
create table myorder (
 	order_id int primary key,
	product_id int,
	customer_id int
);

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

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

#分成三个表之后，就满足了第二范式的要求

3.3 第三范式3NF

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

create table myorder (
 	order_id int primary key,
	product_id int,
	customer_id int
 	product_phone varchar(15),#错误
 	customer_phone varchar(15),#错误
);

create table product (
	id int primary key,
	name varchar(20),
 	phone varchar(15)
);

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

4 查询练习

4.1 数据准备

#创建数据库
mysql> create database selectTest;
Query OK, 1 row affected (0.01 sec)
#使用数据库
mysql> use selectTest
Database changed

• 学生表

#学生表
#学生编号
#学生名字
#学生性别
#出生日期
#所在班级
create table student (
	sno varchar(20) primary key,
	sname varchar(20) not null,
	ssex varchar(10) not null,
	sbirthday datetime,
	class varchar(20)
);
#显示
mysql> create table student (
 -> sno varchar(20) primary key,
 -> sname varchar(20) not null,
 -> ssex varchar(10) not null,
 -> sbirthday datetime,
 -> class varchar(20)
 -> );
Query OK, 0 rows affected (0.01 sec)

#添加数据
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-01-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-01-10', '95031');
insert into student values('109', '赵铁柱', '男', '1974-06-03', '95031');

#查看
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-01-10 00:00:00 | 95031 |
| 106 | 陆君      | 男   | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛    | 男   | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋    | 女   | 1975-01-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)

• 教师表

#教师表
#教师编号
#教师名字
#教师性别
#出生年月日
#职称
#所在部门
create table teacher (
	tno varchar(20) primary key,
	tname varchar(20) not null,
	tsex varchar(20) not null,
	tbirthday datetime,
	prof varchar(20) not null,
	depart varchar(20) not null
);
#显示
mysql> create table teacher (
 -> tno varchar(20) primary key,
 -> tname varchar(20) not null,
 -> tsex varchar(20) not null,
 -> tbirthday datetime,
 -> prof varchar(20) not null,
 -> depart varchar(20) not null
 -> );
Query OK, 0 rows affected (0.01 sec)
#添加数据
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', '助教', '电子工程系');
#查看
mysql> select * from teacher;
+-----+--------+------+---------------------+-----------+-----------------+
| 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 | 讲师      | 电子工程系      |
+-----+--------+------+---------------------+-----------+-----------------+
4 rows in set (0.00 sec)

• 课程表

#课程表
#课程号
#课程名称
#教师编号
create table course (
	cno varchar(20) primary key,
	cname varchar(20) not null,
	tno varchar(20) not null,
	foreign key(tno) references teacher(tno)
);
#显示
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)
 -> );
Query OK, 0 rows affected (0.01 sec)

#添加数据
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');
#查看
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)

• 成绩表

#成绩表
#学号
#课程号
#成绩
create table score (
	sno varchar(20) not null,
	cno varchar(20) not null,
	degree decimal,
	foreign key(sno) references student(sno),
	foreign key(cno) references course(cno),
 	primary key (sno, cno)
);
#显示
mysql> create table score (
 -> sno varchar(20) not null,
 -> cno varchar(20) not null,
 -> degree decimal,
 -> foreign key(sno) references student(sno),
 -> foreign key(cno) references course(cno),
 -> primary key (sno, cno)
 -> );
Query OK, 0 rows affected (0.01 sec)
#添加数据
insert into score values('103', '3-245', '86');
insert into score values('105', '3-245', '75');
insert into score values('109', '3-245', '68');
insert into score values('103', '3-105', '92');
insert into score values('105', '3-105', '88');
insert into score values('109', '3-105', '76');
insert into score values('103', '6-166', '85');
insert into score values('105', '6-166', '79');
insert into score values('109', '6-166', '81');
#查看
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 |
+-----+-------+--------+
9 rows in set (0.00 sec)

4.2 1到10

• 1、查询student表的所有记录

select * from 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-01-10 00:00:00 | 95031 |
| 106 | 陆君      | 男   | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛    | 男   | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋    | 女   | 1975-01-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列

select sname,ssex,class from student;
#显示
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列，使用distinct

select depart from teacher;
#查看有重复
mysql> select depart from teacher;
+-----------------+
| depart          |
+-----------------+
| 计算机系        |
| 计算机系        |
| 电子工程系      |
| 电子工程系      |
+-----------------+
4 rows in set (0.00 sec)

#修改命令
select distinct depart from teacher;
#查看
mysql> select distinct depart from teacher;
+-----------------+
| depart          |
+-----------------+
| 计算机系        |
| 电子工程系      |
+-----------------+
2 rows in set (0.00 sec)

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

#查询区间between ... and ...
select * from score where degree between 60 and 80; 
#显示
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)

#另一种操作，运算符比较
select * from score where degree > 60 and degree < 80; 
#显示
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的记录

#表示的或者关系的查询 in
select * from score where degree in(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”班或者性别为“女”的同学记录

#不同字段的或者关系 or
select * from student where class='95031' or ssex='女'; 
#查看
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-01-10 00:00:00 | 95031 |
| 106 | 陆君      | 男   | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋    | 女   | 1975-01-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
6 rows in set (0.00 sec)

• 7、以class降序查询student表的所有记录

#升序
select * from student order by class desc; 
#查看
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-01-10 00:00:00 | 95031 |
| 106 | 陆君      | 男   | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋    | 女   | 1975-01-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)

#升序，一般是默认的不需要写
select * from student order by class asc；

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

select * from score order by cno asc, degree desc;
#查看
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”班的学生人数

#统计 count
select count(*) from student where class = '95031';
#查看
mysql> select count(*) from student where class = '95031';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

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

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

4.3 10过程解析

#1、找到最高分
select max(degree) from score;
#2、找到最高分的sno和cno
select sno, cno from score where degree=(select max(degree) from score);

#排序的做法
select sno,cno,degree from score order by degree;
#显示
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 |
+-----+-------+--------+
9 rows in set (0.00 sec)
#limit 0，1 参数0代表从0开始，参数1代表第一个元素
select sno,cno,degree from score order by degree desc limit 0,1;
#显示
mysql> select sno,cno,degree from score order by degree desc limit 0,1;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+
1 row in set (0.00 sec)

4.4 分组计算平均成绩

• 11、查看每门课的平均成绩

select * from course;
#avg()
select avg(degree) from score where cno='3-105';
#显示
mysql> select avg(degree) from score where cno='3-105';
+-------------+
| avg(degree) |
+-------------+
|     85.3333 |
+-------------+
1 row in set (0.00 sec)

#使用分组 group
select cno, avg(degree) from score group by cno;
#显示
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)

4.5 分组条件与模糊查询

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

select cno from score group by cno having count(cno) >= 2;
#显示
mysql> select cno from score group by cno having count(cno) >= 2;
+-------+
| cno   |
+-------+
| 3-105 |
| 3-245 |
| 6-166 |
+-------+
3 rows in set (0.00 sec)

select cno from score group by cno having count(cno) >= 2 and cno like '3%';
#显示
mysql> select cno from score group by cno having count(cno) >= 2 and cno like '3%';
+-------+
| cno   |
+-------+
| 3-105 |
| 3-245 |
+-------+
2 rows in set (0.00 sec)

select cno,avg(degree) from score group by cno having count(cno) >= 2 and cno like '3%';
#显示
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)

select cno,avg(degree),count(*) from score group by cno having count(cno) >= 2 and cno like '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 |
+-------+-------------+----------+
2 rows in set (0.00 sec)

4.6 范围查询的两种方式

• 13、查询分数大于70，小于90的sno列

select sno,degree from score 
where degree>70 and degree<90;
#显示
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)

#between...and...
select sno,degree from score 
where degree between 70 and 90;
#显示
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)

4.7 多表查询

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

select sno,sname from student;
#显示
mysql> select sno,sname from student;
+-----+-----------+
| sno | sname     |
+-----+-----------+
| 101 | 曾华      |
| 102 | 匡明      |
| 103 | 王丽      |
| 104 | 李军      |
| 105 | 王芳      |
| 106 | 陆君      |
| 107 | 王尼玛    |
| 108 | 张全蛋    |
| 109 | 赵铁柱    |
+-----+-----------+
9 rows in set (0.00 sec)

select sno,cno,degree from score;
#显示
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 |
+-----+-------+--------+
9 rows in set (0.00 sec)

#多表查询
select sname,cno,degree from student,score
where student.sno = score.sno;
#显示
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.00 sec)

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

select cno,cname from course;
#显示
mysql> select cno,cname from course;
+-------+-----------------+
| cno   | cname           |
+-------+-----------------+
| 3-105 | 计算机导论      |
| 3-245 | 操作系统        |
| 6-166 | 数字电路        |
| 9-888 | 高等数学        |
+-------+-----------------+
4 rows in set (0.00 sec)

select cno,sno,degree from score;
#显示
mysql> select cno,sno,degree from score;
+-------+-----+--------+
| cno   | sno | degree |
+-------+-----+--------+
| 3-105 | 103 |     92 |
| 3-245 | 103 |     86 |
| 6-166 | 103 |     85 |
| 3-105 | 105 |     88 |
| 3-245 | 105 |     75 |
| 6-166 | 105 |     79 |
| 3-105 | 109 |     76 |
| 3-245 | 109 |     68 |
| 6-166 | 109 |     81 |
+-------+-----+--------+
9 rows in set (0.00 sec)

#多表查询
select sno,cname,degree from course,score
where course.cno = score.cno;
#显示
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 |
+-----+-----------------+--------+
9 rows in set (0.00 sec)

4.8 三表关联查询

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

#sname->student
#cname->scouse
#degree->score
select sname,cname,degree from student,course,score
where student.sno=score.sno and course.cno=score.cno;
#显示
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)

4.9 子查询加分组求平均分

• 17、查询“95031”班学生每门课的平均分

select * from student where class='95031';
#显示
mysql> select * from student where class='95031';
+-----+-----------+------+---------------------+-------+
| sno | sname     | ssex | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 102 | 匡明      | 男   | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳      | 女   | 1975-01-10 00:00:00 | 95031 |
| 106 | 陆君      | 男   | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋    | 女   | 1975-01-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
5 rows in set (0.00 sec)

select * from score where sno in (select sno from student where class='95031');
#显示
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 |
+-----+-------+--------+
6 rows in set (0.00 sec)

select cno,avg(degree) from score where sno in (select sno from student where class='95031') group by cno;
#显示
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)

4.10 子查询

• 18、查询选修“3-105”课程的成绩高于“109”号同学“3-105”成绩的所有同学的记录

select degree from score where sno='109' and cno='3-105';
#显示
mysql> select degree from score where sno='109' and cno='3-105';
+--------+
| degree |
+--------+
|     76 |
+--------+
1 row in set (0.00 sec)

#完成
select * from score where cno='3-105' and degree>(select degree from score where sno='109' and cno='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”的成绩的所有记录

select * from score where degree>(select degree from score where sno='109' and cno='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)

4.11 year函数与待in关键字的子查询

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

select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101)); 
#显示
mysql> select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));
+-----+-----------+------+---------------------+-------+
| sno | sname     | ssex | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华      | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明      | 男   | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳      | 女   | 1975-01-10 00:00:00 | 95031 |
| 108 | 张全蛋    | 女   | 1975-01-10 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
4 rows in set (0.00 sec)

4.12 多层嵌套子查询

• 21、查询“张旭”教师任课的学生成绩

#查看张旭的信息
select * from teacher where tname='张旭';
#查看张旭的课程号
select cno from course where tno=(select tno from teacher where tname='张旭');
#完成
select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='张旭'));
#显示
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)

4.13 多表查询

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

#选择人数大于5的课程编号
select cno from score group by cno having count(*)>5;

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

4.14 in表示或者关系

• 23、查询“95033”班和“95031”班全体学生的记录

#增加一个信息
insert into student values('110', '张飞', '男', '1974-06-03', '95038');
#完成
select * from student where class in ('95033', '95031');
#显示
mysql> select * from student where class in ('95033', '95031');
+-----+-----------+------+---------------------+-------+
| 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-01-10 00:00:00 | 95031 |
| 106 | 陆君      | 男   | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛    | 男   | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋    | 女   | 1975-01-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)

4.15 where条件查询

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

select cno,degree from score where degree>85;
#显示
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)

4.16 子查询

• 25、查询出“计算机系”教师所教课程的成绩表

#查询老师
select * from teacher where depart='计算机系';
#显示
mysql> select * from teacher where depart='计算机系';
+-----+--------+------+---------------------+-----------+--------------+
| tno | tname  | tsex | tbirthday           | prof      | depart       |
+-----+--------+------+---------------------+-----------+--------------+
| 804 | 李成   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系     |
| 825 | 王萍   | 女   | 1972-05-05 00:00:00 | 助教      | 计算机系     |
+-----+--------+------+---------------------+-----------+--------------+
2 rows in set (0.00 sec)

#查询课程
select * from course where tno in(select tno from teacher where depart='计算机系');
#显示
mysql> select * from course where tno in(select tno from teacher where depart=' 计算机系');
+-------+-----------------+-----+
| cno   | cname           | tno |
+-------+-----------------+-----+
| 3-245 | 操作系统        | 804 |
| 3-105 | 计算机导论      | 825 |
+-------+-----------------+-----+
2 rows in set (0.00 sec)

#完成
select * from score where cno in (select cno from course where tno in(select tno from teacher where depart='计算机系'));
#显示
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)

4.17 union和notin的使用

• 26、查询“计算机系”和“电子工程系”不同职称的教师的tname和prof

select prof from teacher where depart='电子工程系';

select * from teacher where depart='计算机系' and prof not in(select prof from teacher where depart='电子工程系')
union
select * from teacher where depart='电子工程系' and prof not in(select prof from teacher where depart='计算机系');

#显示
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 | 讲师      | 电子工程系      |
+-----+--------+------+---------------------+-----------+-----------------+
2 rows in set (0.00 sec)

4.18 any表示至少一个desc降序

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

#选择3-245
select * from score where cno = '3-245';
#选择3-105
select * from score where cno = '3-105';

#完成
select * from score
where cno = '3-105'
and degree>any(select degree from score where cno='3-245')
order by degree desc;

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

4.19 all表示所有

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

select * from score
where cno = '3-105'
and degree>all(select degree from score where cno='3-245');

#显示
mysql> select * from score
 -> where cno = '3-105'
 -> and degree>all(select degree from score where cno='3-245');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
+-----+-------+--------+
5 rows in set (0.00 sec)

4.20 as取别名-union求并集

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

select tname as name, tsex as sex, tbirthday as birthday from teacher
union
select sname,ssex,sbirthday from student;

#显示
mysql> select tname as name, tsex as sex, tbirthday as birthday from teacher
 -> union
 -> select sname,ssex,sbirthday from student;
+-----------+-----+---------------------+
| name      | sex | birthday            |
+-----------+-----+---------------------+
| 李成      | 男  | 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-01-10 00:00:00 |
| 陆君      | 男  | 1974-06-03 00:00:00 |
| 王尼玛    | 男  | 1976-02-20 00:00:00 |
| 张全蛋    | 女  | 1975-01-10 00:00:00 |
| 赵铁柱    | 男  | 1974-06-03 00:00:00 |
| 张飞      | 男  | 1974-06-03 00:00:00 |
+-----------+-----+---------------------+
14 rows in set (0.00 sec)

4.21 union求并集

• 30、查询所有“女”教师和“女”同学的name，sex和birthday

select tname as name, tsex as sex, tbirthday as birthday from teacher where tsex='女'
union
select sname,ssex,sbirthday from student where ssex='女';
#显示
mysql> select tname as name, tsex as sex, tbirthday as birthday from teacher where tsex='女'
 -> union
 -> select sname,ssex,sbirthday from student where ssex='女';
+-----------+-----+---------------------+
| name      | sex | birthday            |
+-----------+-----+---------------------+
| 王萍      | 女  | 1972-05-05 00:00:00 |
| 刘冰      | 女  | 1977-08-14 00:00:00 |
| 王丽      | 女  | 1976-01-23 00:00:00 |
| 王芳      | 女  | 1975-01-10 00:00:00 |
| 张全蛋    | 女  | 1975-01-10 00:00:00 |
+-----------+-----+---------------------+
5 rows in set (0.00 sec)

4.22 复制表数据做条件查询

• 31、查询成绩比该课程平均成绩低的同学们的成绩表

#每门课程的平均成绩
select cno,avg(degree) from score group by cno;
#显示
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 |
+-------+-------------+
3 rows in set (0.00 sec)

#查看成绩
select * from score;
#显示
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 |
+-----+-------+--------+
12 rows in set (0.00 sec)

#完成
select * from score a where degree < (select avg(degree) from score b where a.cno=b.cno);
#显示
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)

4.23 子查询

• 32、查询所有任课教师的tname和depart

select tname,depart from teacher where tno in (select tno from course);
#显示
mysql> select tname,depart from teacher where tno in (select tno from course);
+--------+-----------------+
| tname  | depart          |
+--------+-----------------+
| 李成   | 计算机系        |
| 王萍   | 计算机系        |
| 刘冰   | 电子工程系      |
| 张旭   | 电子工程系      |
+--------+-----------------+
4 rows in set (0.00 sec)

4.24 条件加分组筛选

• 33、查询至少有两名男生的班号

select class from student where ssex='男' group by class having count(*)>1;
#显示
mysql> select class from student where ssex='男' group by class having count(*)>1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
2 rows in set (0.00 sec)

4.25 notlike模糊查询取反

• 34、查询student中不姓“王”的同学的记录

select * from student where sname not like '王%';
#显示
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-01-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)

4.26 year函数与now函数

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

#年龄=当前年份-出生年份
select year(now());
#显示
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2020 |
+-------------+
1 row in set (0.00 sec)

select year(sbirthday) from student;
#显示
mysql> select year(sbirthday) from student;
+-----------------+
| year(sbirthday) |
+-----------------+
|            1977 |
|            1975 |
|            1976 |
|            1976 |
|            1975 |
|            1974 |
|            1976 |
|            1975 |
|            1974 |
|            1974 |
+-----------------+
10 rows in set (0.00 sec)

#做差
select sname,year(now())-year(sbirthday) as '年龄' from student;
#显示
mysql> select sname,year(now())-year(sbirthday) as '年龄' from student;
+-----------+--------+
| sname     | 年龄   |
+-----------+--------+
| 曾华      |     43 |
| 匡明      |     45 |
| 王丽      |     44 |
| 李军      |     44 |
| 王芳      |     45 |
| 陆君      |     46 |
| 王尼玛    |     44 |
| 张全蛋    |     45 |
| 赵铁柱    |     46 |
| 张飞      |     46 |
+-----------+--------+
10 rows in set (0.01 sec)

4.27 max与min函数

• 36、查询student表中最大最小的sbirthday日期值

select max(sbirthday) as '最大', min(sbirthday) as '最小' from student;
#显示
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.00 sec)

4.28 多字段排序

• 37、以班号和年龄从大到小顺序查询student表中的全部数据

select * from student order by class desc, sbirthday;
#显示
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-01-10 00:00:00 | 95031 |
| 108 | 张全蛋    | 女   | 1975-01-10 00:00:00 | 95031 |
| 102 | 匡明      | 男   | 1975-10-02 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
10 rows in set (0.00 sec)

4.29 子查询

• 38、查询“男教师”及其所上的课程

select * from course where tno in (select tno from teacher where tsex='男');
#显示
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)

4.30 max函数与子查询

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

select * from score where degree=(select max(degree) from score);
#显示
mysql> select * from score where degree=(select max(degree) from score);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+
1 row in set (0.00 sec)

4.31 子查询

• 40、查询和“李军”同性别的所有同学

select sname from student where ssex = (select ssex from student where sname='李军');
#显示
mysql> select sname from student where ssex = (select ssex from student where sname='李军');
+-----------+
| sname     |
+-----------+
| 曾华      |
| 匡明      |
| 李军      |
| 陆君      |
| 王尼玛    |
| 赵铁柱    |
| 张飞      |
+-----------+
7 rows in set (0.00 sec)

• 41、查询和“李军”同性别并且同班的同学sname

select sname from student 
where ssex = (select ssex from student where sname='李军')
and class = (select class from student where 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.00 sec)

• 42、查询所有选修“计算机导论”课程的“男”同学的成绩表

select * from score
where cno=(select cno from course where cname='计算机导论')
and sno in (select sno from student where ssex='男');
#显示
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)

4.32 按等级查询

• 43、假设使用如下明令建立一个grade表

create table grade (
	low int(3),
	upp int(3),
	grade char(1)
);

insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');

#现查询所有同学的sno、cno和grade列
select sno,cno,grade from score,grade where degree between low and upp;
#显示
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)

5 索引

5.1 普通索引

#在已知表上添加索引
create index indexID on student(sno); 
或者
alter table student add index indexID(sno);

#显示
mysql> create index indexID on student(sno);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#显示添加的索引
show index from student;
#显示
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY  |            1 | sno         | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | indexID  |            1 | sno         | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

#删除索引
drop index [indexID] ON student; 
#显示
mysql> drop index indexID ON student;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY  |            1 | sno         | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length

#创建表的时候直接指定
create table indexTest (
    id int not null,
    name varchar(20) not null,
    index iname (name(20))  
); 
#显示
mysql> create table indexTest (
    ->     id int not null,
    ->     name varchar(20) not null,
    ->     index iname (name(20))
    -> );
Query OK, 0 rows affected (0.01 sec)

#查看一下
mysql> desc indexTest;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | NO   | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#查看索引
mysql> show index from indexTest;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| indexTest |          1 | iname    |            1 | name        | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

---

5.2 唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| sno       | varchar(20) | NO   | PRI | NULL    |       |
| sname     | varchar(20) | NO   |     | NULL    |       |
| ssex      | varchar(10) | NO   |     | NULL    |       |
| sbirthday | datetime    | YES  |     | NULL    |       |
| class     | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#创建索引
create unique index indexID ON student(sno(20)); 
或者
alter table student add unique indexID (sno(20));
#显示
mysql> alter table student add unique indexID (sno(20));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
#查询
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY  |            1 | sno         | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          0 | indexID  |            1 | sno         | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

#创建表的时候直接指定
create table indexTest (
    id int not null,
    name varchar(20) not null,
    unique iname (name(20))  
);  
#显示
mysql> create table indexTest (
    ->     id int not null,
    ->     name varchar(20) not null,
    ->     unique iname (name(20))
    -> );
Query OK, 0 rows affected (0.01 sec)
#查看
mysql> show index from indexTest;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| indexTest |          0 | iname    |            1 | name        | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

---

6 连接查询

#创建数据库
create database testJoin;
#person
#id
#name
#cardId
create table person (
	id int,
	name varchar(20),
	cardId int
);
#card表
#id
#name
create table card (
	id int,
	name varchar(20)
);
#插入数据
insert into card values(1, '饭卡');
insert into card values(2, '建行卡');
insert into card values(3, '农行卡');
insert into card values(4, '工商卡');
insert into card values(5, '邮政卡');

insert into person values(1, '张三', 1);
insert into person values(2, '李四', 3);
insert into person values(3, '王五', 6);

6.1 内连接

• inner join 或者 join
• 内连接查询，其实就是两张表中的数据，通过某个字段相对，查询出相关记录数据

select * from person inner join card on person.cardId=card.id;
#显示
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)

select * from person join card on person.cardId=card.id;
#效果是一样的

6.2 外连接

• 左外连接 left join 或者 left outer join
• 以左表为基础，匹配右表

select * from person left join card on person.cardId=card.id;
#显示
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      |
+------+--------+--------+------+-----------+
3 rows in set (0.00 sec)
select * from person left outer join card on person.cardId=card.id;
#效果是一样的

• 右外连接 right join 或者 right outer join
• 以右表为基础，匹配左表

select * from person right join card on person.cardId=card.id;
#显示
mysql> select * from person right 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)
select * from person right outer join card on person.cardId=card.id;
#效果是一样的

• 全外连接 full join 或者 full outer join

#mysql不支持全外连接
select * from person left join card on person.cardId=card.id
union
select * from person right join card on person.cardId=card.id;
#显示
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 | 邮政卡    |
+------+--------+--------+------+-----------+
6 rows in set (0.00 sec)

7 事务

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

7.1 事务用来做什么

#比如我们的银行转账：
a->-100
update user set money=money-100 where name ='a';
b->+100
update user set money=money+100 where name ='b';
#在实际的程序中，如果只有一条语句执行成功了，而另一天没有执行成功？
#出现数据前后不一致
#所以完成sql语句的过程中，要么全部完成，要么全部失败

7.2 如何开启事务commit-rollback

#mysql是默认开启事务的(自动提交)
select @@autocommit;
#显示
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
#默认事务开启的作用是什么？
#当我们去执行一个sql语句的时候，效果会立即体现出来，且不能回滚。

#创建一个数据库
create database bank;
#创建一个数据表
create table user (
	id int primary key,
	name varchar(20),
	money int
);
insert into user values(1, 'a', 1000);

#事务回滚：撤销sql语句执行效果
rollback;

#显示
mysql> insert into user values(1, 'a', 1000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row 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)

#关闭自动提交
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.01 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)

#手动提交
#插入数据
mysql> insert into user values(2, 'b', 1000);
Query OK, 1 row affected (0.00 sec)
#手动提交
mysql> commit;
Query OK, 0 rows affected (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)

7.3 手动开启事务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)

#begin 或者 start transaction;
#都可以帮我们手动开启一个事务
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.01 sec)

#转账
mysql> update user set money=money-100 where name ='a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set money=money+100 where name ='b';
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> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money=money-100 where name ='a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set money=money+100 where name ='b';
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)

7.4 ACID特征与使用

A 原子性(Atomicity):事务中的全部操作在数据库中是不可分割的，要么全部完成，要么全部不执行。

C 一致性(Consistency):几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。

I 夺隔离性(Isolation):事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。

D 夺持久性(Durability):对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障。

8 事务隔离性

8.1 脏读

• read uncommitted 读为提交的

#如果有事务a, 和事物b，
#a事物对数据进行操作，在操作的过程中，事务没有被提交，但是b可以看见a操作的结果

insert into user values(3, '小明', 1000);
insert into user values(4, '淘宝店', 1000);
#显示
mysql> insert into user values(3, '小明', 1000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(4, '淘宝店', 1000);
Query OK, 1 row affected (0.00 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)

#查看隔离级别
#系统级别
select @@global.transaction_isolation;
#会话级别
select @@transaction_isolation;
#显示
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

#如果两个不同的地方，都在进行操作，如果事务a开启之后，他的数据可以被其它事物读取到。
#这样就会出现（脏读）
#脏读：一个事务读到了另外一个事务没有提交的数据，就叫做脏读
#实际开发是不允许脏读出现的

8.2 不可重复读

修改隔离级别

set global transaction isoation level read uncommitted;

select @@global.transaction_isolation;

read committed 读已提交的

#虽然我只能读到另外一事务提交的数据，但还是会出现问题，就是
#就是同一个表的数据，发现前后不一致

8.3 幻读

修改隔离级别

set global transaction isoation level repeatable read;

select @@global.transaction_isolation;

repeatable 可以重复读

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

8.4 串行化

修改隔离级别

set global transaction isoation level serializable

select @@global.transaction_isolation;

serializable 串行化

#当数据表被另一个事务操作的时候，其他事务里面的写操作，是不可以进行的
#进入排队状态(串行化),直到事务结束之后，本地执行者的操作才会被执行
#在没有等待超时的情况下