MYSQL
初识MYSQL
mysql是目前使用很广泛的关系型数据库。
安装教程我就不详细写了，可以参考 https://www.cnblogs.com/Eva-J/articles/9676220.html。
起/停服务(windows)
net start mysql
net stop mysql
本文使用5.6.44版本。此版本root没有初始密码。
客户端登录
mysql -uroot -p #初次登录可以这样直接登
mysql -uguest -p -h127.0.0.1 #演示使用guest用户登录某个ip的mysql
账户&授权相关
权限类型:
- 插入数据INSERT
- 删除数据DELETE
- 更新数据UPDATE
- 查询数据SELECT
- 所有权限*
# 查看当前用户
mysql> select user();
#设置当前用户密码为123
mysql> set password = password('123');
# 创建新用户 guest，只允许来自 127.0.0.1的连接访问，设置用户密码为1212；
mysql> create user 'guest'@'127.0.0.1' identified by '1212';
# 127.0.0.1的guest用户添加对confluence库的查询权限
mysql> grant select on confluence.* to 'guest'@'127.0.0.1';
# 授权的同时创建用户
mysql> grant select on confluence.* to 'xifan'@'127.0.0.1' identified by '1212';
mysql> flush privileges; # 刷新使授权立即生效
# '用户名'@'xxxx' 可以用百分号表示任意匹配
mysql> create user 'guest'@'%'; # 允许表示任意来源ip
mysql> create user 'guest'@'192.168.10.%'; # 允许表示192.168.10 网段的ip来源
#查看某个来源ip用户的授权情况
mysql> show grants for 'xifan'@'127.0.0.1';
基本库表操作
增删改查
1. 库
增: create database d1 charset utf8; #也可以不加字符集，有默认的
删: drop database d1;
改: alter database db1 charset latin1; #修改字符集为latin1
查: show databases;
切换: use 库名; #切换到某个库下
1. 表
增: create table t1(id int,name char(12),age int); #新建名为t1的表，char类型需要写上长度
删: drop table t1;
改: alter table t1 modify name char(10); #修改表头某项的字符长度
alter table t1 change name name1 char(12); #修改表头某项的名称与字符长度
查: show tables; #查看该库下有哪几张表
desc t1; #查看某张表的结构
describe t1; #与desc一样
1. 表内容
增: insert into t1 values(1,'杨稀饭',2),(2,'谭棉花',0); #向表t1中插入两行值
删: delete from t1 where id=2;
改: update t1 set age=3 where name="杨稀饭";
查: select * from t1;
清空表:
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的 最大的值+1 作为起始。
truncate table t1; #数据量大，删除速度比上一条快，且自增id字段也直接从零开始
引擎介绍
什么是存储引擎？
MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。
这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。
MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。
选择如何存储和检索你的数据的这种灵活性是MySQL为什么如此受欢迎的主要原因。其它数据库系统(包括大多数商业选择)仅支持一种类型的数据存储。
遗憾的是，其它类型的数据库解决方案采取的“一个尺码满足一切需求”的方式意味着你要么就牺牲一些性能，要么你就用几个小时甚至几天的时间详细调整你的数据库。使用MySQL，我们仅需要修改我们使用的存储引擎就可以了
mysql支持哪些存储引擎？
mysql中有多种存储引擎，不同的存储引擎也有着不同的适用场景和功能。
mysql5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表。
mysql5.6 之前默认的存储引擎是 MyISAM , 5.6 开始默认存储引擎是 InnoDB。
三种常用存储引擎介绍
InnoDB
用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。
MyISAM
如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。
Memory
将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。
InnoDB
MySql 5.6 版本默认的存储引擎。
存储一份数据需要2个文件：数据和索引、表结构
支持数据持久化
支持事务: 为保证数据完整性，将多个操作绑定为一个原子性操作 --保证数据安全性
支持行级锁: 修改较少行时使用 --适用于频繁地少量多次修改数据的场景
支持表级锁: 批量修改多行时使用 --适用用大量数据同时修改的场景
支持外键: 约束两张表的关联字段不能随意添加删除 --能降低数据删改时的出错率
适用场景: 应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作
Myisam
MySql 5.5 及之前版本默认的存储引擎。
存储一份数据需要3个文件：数据、索引、表结构
支持数据持久化
只支持表锁
适用场景: 以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高的场景。
Memory
存储一份数据只用1个文件，包括表结构和内容
数据存在内存，停服即无
只支持表锁
访问速度快
适用场景: 适用于能快速记录与读取数据，且能容忍数据丢失的场景。
| InnoDB | MyISAM | MEMORY | |
|---|---|---|---|
| 事务 | 支持 | - | - |
| 行级锁 | 支持 | - | - |
| 表级锁 | 支持 | 支持 | 支持 |
| 外键 | 支持 | - | - |
| 数据持久化 | 支持 | 支持 | 停服即无 |
| 存储介质 | 硬盘 | 硬盘 | 内存 |
TRANSACTION(事务)
是指作为单个逻辑单元执行的一系列操作。他也是一个执行单元，要么全部执行，要么全部不执行。
例如:
A给B转账200元，总共分为几步
1. 数据库查询A账户余额发现大于200元
2. A账户 - 200
3. B账户 + 200
假设第二步执行后，第三步由于数据库崩溃未执行成功。A减了钱但B没收到，这是不能接收的情况，所以，将以上三步绑定为一个事务,要么全执行成功，要么都失败。
事务的特性:
- 原子性: 要么都执行成功，要么都失败。
- 隔离性: 一个事务执行时，不会被其他事务干扰。
- 持久性: 事务一旦提交它的改变就是永久性的。
- 一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
(我理解是:一致性是事务执行前与完成后,数据库中的数据处于一个符合常理与规则的状态，不会存在，一方扣了钱一方却没收到的情况。上面三个特性其实都是在为一致性服务的)
事务导致的一些问题:
（这里直接用了别人博客的内容:https://www.cnblogs.com/Kevin-ZhangCG/p/9038371.html）
脏读：脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
幻读也叫虚读：一个事务执行两次查询，第二次结果集包含第一次中没有或某些行已经被删除的数据，造成两次结果不一致，只是另一个事务在这两次查询中间插入或删除了数据造成的。幻读是事务非独立执行时发生的一种现象。
不可重复读：一个事务两次读取同一行的数据，结果得到不同状态的结果，中间正好另一个事务更新了该数据，两次结果相异，不可被信任。与脏读的区别是脏读读的是未提交的事务数据。
例如
脏读:
小明的银行卡余额里有100元。现在他打算用手机点一个外卖饮料，需要付款10元。但是这个时候，他的女朋友看中了一件衣服95元，她正在使用小明的银行卡付款。于是小明在付款的时候，程序后台读取到他的余额只有5块钱了，根本不够10元，所以系统拒绝了他的交易，告诉余额不足。但是小明的女朋友最后因为密码错误，无法进行交易。小明非常郁闷，明明银行卡里还有100元，怎么会余额不足呢？（他女朋友更郁闷。。。）
幻读:
例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
不可重复读:
例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。
事务的隔离级别有4种，由低到高分别为：
读未提交:就是一个事务可以读取另一个未提交事务的数据。读提交，重复读，串行化(序列化)
读提交:顾名思义，就是一个事务要等另一个事务提交后才能读取数据,可避免脏读的发生。
重复读:就是在开始读取数据（事务开启）时，不再允许修改操作。可避免脏读、不可重复读的发生。
串行化:事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。可避免脏读、不可重复读、幻读的发生。
事务通常以begin（start） transaction 开始，以commit 或 rollback 结束。
commit 提交，将事务中所有对数据库内存的更新写到磁盘中，事务正常结束。
rollback 回滚，即在事务运行的过程中发生了某种故障，事务不能继续进行，系统将事务中对数据库的所有已完成的操作全部撤消，滚回到事务开始的状态。
表介绍
基础操作
#创建表
create table 表名 (
字段名1 类型(长度) 约束条件,
字段名2 类型(长度) 约束条件,
字段名3 类型(长度) 约束条件;
);
长度与约束条件可选。
#插入数据
insert into 表名 values ("字段1","字段2","字段3"); #插入单行数据
insert into 表名 values ("字段1-1","字段2-1","字段3-1"),("字段1-2","字段2-2","字段3-2"); #插入多行数据
insert into 表名 (字段名2,字段名3) values ("字段2-3","字段3-3"); #指定字段插入数据
#查询数据
desc 表名; #查询表结构
describe 表名; #查询表结构，和desc一样。
show create table 表名\G; #查询表结构字段名，数据类型，及约束、编码等。\G为旋转90°变为纵向结构，方便看。
select * from 表名 where xx=xx; #查询表内容
#删除数据
delete from 表名 where 条件;
使用举例
建表与查看
mysql> create table stu (id int,name char(10),age int(3),sex enum("male","female"),phone bigint(11));
Query OK, 0 rows affected (0.02 sec)
mysql> desc stu;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> show create table stu\G;
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(11) DEFAULT NULL,
`name` char(10) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
`phone` bigint(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
插入内容与查看删除
mysql> insert into stu values (1,"huandada",16,'female',1111111);
Query OK, 1 row affected (0.05 sec)
mysql> insert into stu values (2,"huan",12,'female',22222222222),(3,'dada',10,'male',13908764567);
Query OK, 2 rows affected (0.01 sec)
mysql> insert into stu (id,name) values (4,"huan"),(5,'dada');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from stu;
+------+----------+------+--------+--------------+
| id | name | age | sex | phone |
+------+----------+------+--------+--------------+
| 1 | huandada | 16 | female | 1111111 |
| 2 | huan | 12 | female | 22222222222 |
| 3 | dada | 10 | male | 13908764567 |
| 4 | huan | NULL | NULL | NULL |
| 5 | dada | NULL | NULL | NULL |
+------+----------+------
mysql> delete from stu where age is NULL;
Query OK, 2 rows affected (0.01 sec)
数据类型
mysql支持多种数据类型。
数值类型
| 类型 | 大小 | 可表示范围（有符号） | 可表示范围（无符号）unsigned约束 | 用途 |
|---|---|---|---|---|
| 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 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 字节 float(255,30) | (-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 字节 double(255,30) | (-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和D的值 | 小数值 |
常用的数值类型 tinyint int。
float(m,n)，double(5,2)，decimal(5,2) 表示一共 m 位，其中小数占 n 位。
若设置的小数位数超过，会自动四舍五入。
不指定位数，在有小数位的情况下，float精度默认为小数+整数共5位，double默认精度为小数加整数共17位，decimal默认会四舍五入取整。
int也可以设置长度 int(5),但其存储的长度不会受这个值限制。
默认为有符号的，如果没有负数的情况，可以把值设置为unsigned。
create table t5 (id int unsigned);
日期和时间类型
| 类型 | 大小(字节) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| 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 | 混合日期和时间值，时间戳,因结束时间接近，最好不要使用 |
使用举例
点击查看代码
mysql> create table t6 (y year,d date,t time,dt datetime,ts timestamp);
Query OK, 0 rows affected (0.01 sec)
# now() 为当前时间
mysql> insert into t6 values(now(),now(),now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t6;
+------+------------+----------+---------------------+---------------------+
| y | d | t | dt | ts |
+------+------------+----------+---------------------+---------------------+
| 2021 | 2021-12-27 | 16:46:48 | 2021-12-27 16:46:48 | 2021-12-27 16:46:48 |
+------+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)
# 给timestamp插入空值仍会插入当前时间。
mysql> insert into t6 (ts) values(null);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t6;
+------+------------+----------+---------------------+---------------------+
| y | d | t | dt | ts |
+------+------------+----------+---------------------+---------------------+
| 2021 | 2021-12-27 | 16:46:48 | 2021-12-27 16:46:48 | 2021-12-27 16:46:48 |
| NULL | NULL | NULL | NULL | 2021-12-27 16:49:39 |
+------+------------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)
#插入某个时间的几种方式
mysql> insert into t6 (y,d,t,dt) values(2019,20191221,160559,20191221160559);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t6 (y,d,t,dt) values('2019','20191221','160559','20191221160559');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t6 (y,d,t,dt) values(2019,'2019-12-21','16:05:59','2019-12-21 16:05:59');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t6 (y,d,t,dt) values('2019','2019/12/21','16:05:59','2019/12/21 16+05+59');
Query OK, 1 row affected (0.01 sec)
timestamp即将停用，怎么将它为null时会插入当前时间的特性转移到datetime呢？
将timestamp的约束条件用在datetime上。
点击查看代码
mysql> show create table t6\G;
*************************** 1. row ***************************
Table: t6
Create Table: CREATE TABLE `t6` (
`y` year(4) DEFAULT NULL,
`d` date DEFAULT NULL,
`t` time DEFAULT NULL,
`dt` datetime DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> create table t7 (y year,dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t7 (y) values (2021);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t7;
+------+---------------------+
| y | dt |
+------+---------------------+
| 2021 | 2021-12-27 17:06:25 |
+------+---------------------+
1 row in set (0.00 sec)
字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| 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字节 | 极大文本数据 |
其中char、varchar用的最多。
char(10)
最多只能表示255个字符
长度默认为声明的长度。定长存储，浪费空间，节约时间。
适用场景：身份证号，手机号，qq号，用户名，密码等
例如: char(10)，存储yhh这个字段，会默认用空格占满7个字符位置。
'yhh '
varchar(10)
最多能表示65535个字符
变长存储，节省空间，速度较慢
适用场景：评论 朋友圈 微博等
例如: 存储yhh这个字段，会计算长度并存储在最后一个字符的位置
'yhh3'
相对来说 char写入/读取效率 更高， varchar 更省空间
ENUM和SET类型
ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。ENUM只允许从值集合中选取单个值，而不能一次取多个值。
SET和ENUM非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。
使用举例
点击查看代码
mysql> create table t8 (name char(10),sex enum('male','female'),hobby set('work','play_game','sport'));
Query OK, 0 rows affected (0.02 sec)
#set 重复的项会去重，选择多项时在同一个引号中以逗号分割
mysql> insert into t8 values ("xifan","female","work,work,sport");
Query OK, 1 row affected (0.01 sec)
mysql> select * from t8;
+-------+--------+------------+
| name | sex | hobby |
+-------+--------+------------+
| xifan | female | work,sport |
+-------+--------+------------+
1 row in set (0.00 sec)
# enum与set填写超出范围的内容会报错
mysql> insert into t8 values ("xifan","cat","work,work,sport");
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
mysql> insert into t8 values ("xifan","female","work,eatfish");
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
表的完整性约束
为了保证数据的规范性，在用户对数据进行插入、修改、删除等操作时，DBMS(数据库管理系统)自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
常用的约束条件有以下几种：
# UNSIGNED ：无符号的，即只有正数；
# NOT NULL ：非空约束，指定某列不能为空；
# DEFAULT ：默认值
# UNIQUE ：唯一约束，指定某列或者几列组合不能重复
# PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录
# FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性
NOT NULL
not null - 不可空
null - 可空,非字符串。默认项
NOT NULL + DEFAULT
当需要某个字段不能为空，用户没输入时，就使用默认值
例如：给大学入学新生设置age默认值为18
点击查看代码
mysql> create table t9 (name char(10),age int NOT NULL DEFAULT 18);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t9(name) VALUES ('xifan');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t9;
+-------+-----+
| name | age |
+-------+-----+
| xifan | 18 |
+-------+-----+
1 row in set (0.00 sec)
若遇到NOT NULL不生效的情况，需要设置严格模式
设置严格模式后：
不支持对not null字段插入null值，即NOT NULL生效。
不支持对自增字段插入null值
不支持text字段有默认值
group by 分组查询的select字段 ，除了group by 的字段之外其他的字段都要有聚合函数包裹
查看当前是否为严格模式:
mysql> select @@sql_mode;
ONLY_FULL_GROUP_BY... #为严格模式
设置方式:
直接在mysql中生效(重启失效):
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
配置文件[mysqld]下添加(永久生效)：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
UNIQUE
唯一约束，指定某列或者几列组合不能重复。对null没有限制，可以插入多个null。
设置方式
方法一：
create table t13 (
id int,
name char(10),
part char(10),
unique(id)
);
方法二：
create table t13 (
id int UNIQUE,
name char(10),
part char(10)
);
# id 的Key字段显示为UNI，表示非重复
mysql> DESC t13;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | char(10) | YES | | NULL | |
| part | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.02 sec)
mysql> insert into t13 values (1,"yy","IT");
Query OK, 1 row affected (0.01 sec)
# 有id=1的值，所以无法再插入相同的值
mysql> insert into t13 values (1,"yZ","IT");
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
not null + unique
若该张表未设置主键，第一个设置not null + unique的键为主键
点击查看代码
create table t14 (
id int not null unique,
name char(10) not null unique,
part char(10),
unique(id)
);
# PRI表示主键
mysql> desc t14;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | NO | UNI | NULL | |
| part | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
联合唯一：表示多项结合的值唯一，例如 ip可重复，port可重复，ip+port 不能重复。
点击查看代码
create table service (
id int primary key,
service char(10),
ip char(14),
port char(5),
unique(ip,port)
);
# 设置联合唯一的第一个字段名为联合唯一键 MUL 的key值
mysql> desc service;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| service | char(10) | YES | | NULL | |
| ip | char(14) | YES | MUL | NULL | |
| port | char(5) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> insert into service values(1,'mysql','192.168.10.5','3306');
Query OK, 1 row affected (0.01 sec)
# ip重复，端口不重复，能正常插入
mysql> insert into service values(2,'mysql','192.168.10.5','3307');
Query OK, 1 row affected (0.01 sec)
# 端口重复，ip不重复，能正常插入
mysql> insert into service values(3,'mysql','192.168.10.6','3306');
Query OK, 1 row affected (0.01 sec)
# ip+端口重复，报错
mysql> insert into service values(4,'mysql','192.168.10.5','3306');
ERROR 1062 (23000): Duplicate entry '3306-192.168.10.5' for key 'ip'
PRIMARY KEY
主键为了保证表中的每一条数据的该字段都是表格中的非空、唯一值。所以其两个特点是：唯一，非空。
当未设置主键时，第一个not null unqiue为主键，当明确设置主键时，不遵循此法则。一张表只有一个主键，如果要有多个字段为主键，必须设置联合主键。
主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键,例如一些公司都会避免同一个部门有重名的人,将name与part设置为联合主键，name 与 part都不能为空，name + part的组合唯一 。
单字段主键演示
# 方法一:not null + unique 定义主键
create table t14 (
id int not null unique, # id为主键
name char(10) not null unique,
part char(10)
);
# 方法二:primary key 定义主键
create table t15 (
id int primary key, # id为主键
name char(10) not null unique,
part char(10)
);
# 方法三:在所有字段后单独定义primary key
create table t16 (
id int,
name char(10) not null unique,
part char(10),
primary key(id) # id为主键
);
# 方法四：给已经建成的表添加主键约束
create table t17 (
id int,
name char(10),
part char(10)
);
mysql> alter table t17 modify id int primary key;
Query OK, 0 rows affected (0.03 sec)
联合主键演示
create table t18 (
id int,
name char(10),
part char(10),
primary key(name,part)
);
mysql> insert into t18 values (1,'yh','技术');
Query OK, 1 row affected (0.05 sec)
# name + part不能重复
mysql> insert into t18 values (2,'yh','技术');
ERROR 1062 (23000): Duplicate entry 'yh-技术' for key 'PRIMARY'
# part可以重复
mysql> insert into t18 values (2,'yy','技术');
Query OK, 1 row affected (0.05 sec)
#name可以重复
mysql> insert into t18 values (2,'yh','IT');
Query OK, 1 row affected (0.01 sec)
#各自都不能为空
mysql> insert into t18 values (2,null,'it');
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into t18 values (2,'ii',null);
ERROR 1048 (23000): Column 'part' cannot be null
AUTO_INCREMENT
自增字段，会自动按顺序添加值。自增自带not null，必须至少自行添加unique字段，或主键。
使用举例
点击查看代码
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
create table student(
id int not null unqiue auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
# 自增主键默认从1开始
mysql> insert into student(name) values('yy');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | yy | male |
+----+------+------+
1 row in set (0.00 sec)
# 不指定值时会默认+1
mysql> insert into student(name) values('yh');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | yy | male |
| 2 | yh | male |
+----+------+------+
2 rows in set (0.00 sec)
# 可以自行插入不重复的值，后续其他值接着最大的值增长
mysql> insert into student values(5,'yy','female');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | yy | male |
| 2 | yh | male |
| 5 | yy | female |
+----+------+--------+
3 rows in set (0.00 sec)
mysql> insert into student(name) values('xf');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | yy | male |
| 2 | yh | male |
| 5 | yy | female |
| 6 | xf | male |
+----+------+--------+
# 插入指定不重复的较小值时，会自动排序到对应的行
mysql> insert into student values(4,'tt','female');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | yy | male |
| 2 | yh | male |
| 4 | tt | female |
| 5 | yy | female |
| 6 | xf | male |
+----+------+--------+
5 rows in set (0.00 sec)
FOREIGN KEY
将某张表的某个字段a关联到另一张表b的某个字段，表b的字段先存在才能被a关联，这个a中的关联字段就叫做外键。
例如: 有三个班，每个班有几十号学生。
建两张表:
班级表: id：1，2，3
class: 一班，二班，三班
... (班级相关的其他信息)
学生表: name:xxx,xxx
class: 作为外键，关联到班级表的id
使用如下:
点击查看代码
# 创建班级表
mysql> create table class (id int primary key,class char(10) not null unique);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into class values (1,'一班'),(2,'二班'),(3,'三班');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc class;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| class | char(10) | NO | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
# 创建学生表关联到class id
# 表类型必须是innodb存储引擎，且被关联的字段，即此处class表的id字段，必须保证唯一
mysql> create table stu (
-> id int primary key AUTO_INCREMENT,
-> name char(10) not null,
-> class int,
-> foreign key(class) references class(id)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc stu;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| class | int(11) | YES | MUL | NULL | |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> insert into stu (name,class) values ('yy',2);
Query OK, 1 row affected (0.01 sec)
#设置class id没有的值报错
mysql> insert into stu (name,class) values ('yy',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`confluence`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class`) REFERENCES `class` (`id`))
# 被外键关联的值的那一行不能删
mysql> delete from class where id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`confluence`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class`) REFERENCES `class` (`id`))
# 还未被外键关联的值的那一行能删
mysql> delete from class where id=1;
Query OK, 1 row affected (0.00 sec)
#所以要删除class表中的行，需要先删除外键的关联内容
mysql> delete from stu where class=2;
Query OK, 1 row affected (0.01 sec)
mysql> delete from class where id=2;
Query OK, 1 row affected (0.05 sec)
####################################################
mysql> insert into stu (name,class) values ('yh',3);
Query OK, 1 row affected (0.01 sec)
# 更新class id的字段，必须也要删除/修改stu中关联的行
mysql> update class set id=4 where id=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`confluence`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class`) REFERENCES `class` (`id`))
mysql> delete from stu where class = 3;
Query OK, 1 row affected (0.01 sec)
mysql> update class set id=4 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
外键删除和修改很麻烦，那么有什么办法可以简化删除和修改的操作呢？
使用 级连删除，和级连更新。不安全，不建议使用。
点击查看代码
create table stu2 (
id int primary key AUTO_INCREMENT,
name char(10) not null,
class int,
foreign key(class) references class(id)
on delete cascade
on update cascade
);
mysql> select * from stu2;
+----+------+-------+
| id | name | class |
+----+------+-------+
| 1 | yy | 1 |
| 2 | yh | 2 |
| 3 | yy | 1 |
+----+------+-------+
3 rows in set (0.00 sec)
# class表删除了id=2的行，stu表class=2的行也一并删除了
mysql> delete from class
Query OK, 1 row affected
mysql> select * from stu2
+----+------+-------+
| id | name | class |
+----+------+-------+
| 1 | yy | 1 |
| 3 | yy | 1 |
+----+------+-------+
2 rows in set (0.00 sec)
# 把 class表id=1的行改为id=4，stu中class=1的也一并改为了4
mysql> update class set id=4 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from stu2;
+----+------+-------+
| id | name | class |
+----+------+-------+
| 1 | yy | 4 |
| 3 | yy | 4 |
+----+------+-------+
2 rows in set (0.00 sec)
on delete/on update 相关配置
. cascade方式
在父表上update/delete记录时，同步update/delete掉子表的匹配记录
. set null方式
在父表上update/delete记录时，将子表上匹配记录的列设为null
要注意子表的外键列不能为not null
. No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
. Restrict方式
同no action, 都是立即检查外键约束
. Set default方式
父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别
修改表结构
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加多个字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
5.修改字段排列顺序/在增加的时候指定字段位置
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
ALTER TABLE 表名
CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
6. 修改表的字符集为 utf8
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8;
使用举例
点击查看代码
mysql> create table tt (id1 int,id2 int,name char(10));
Query OK, 0 rows affected (0.02 sec)
# 修改表名
mysql> alter table tt rename stu_list;
Query OK, 0 rows affected (0.01 sec)
# 添加新字段 age,sex
mysql> alter table stu_list add (age int not null default 18,sex enum('male','female') not null);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu_list;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| id2 | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| age | int(11) | NO | | 18 | |
| sex | enum('male','female') | NO | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
# 删除字段id2
mysql> alter table stu_list drop id2;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table stu_list\G;
*************************** 1. row ***************************
Table: stu_list
Create Table: CREATE TABLE `stu_list` (
`id1` int(11) DEFAULT NULL,
`name` char(10) DEFAULT NULL,
`age` int(11) NOT NULL DEFAULT '18',
`sex` enum('male','female') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
# 修改name字段的长度与约束条件
mysql> alter table stu_list modify name char(12) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 修改id1 为id 并设为主键
mysql> alter table stu_list change id1 id int primary key;
Query OK, 0 rows affected (0.03 sec)
# 将sex字段调到name后面
mysql> alter table stu_list modify sex enum('male','female') NOT NULL after name;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu_list;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(12) | NO | | NULL | |
| sex | enum('male','female') | NO | | NULL | |
| age | int(11) | NO | | 18 | |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
重新设置主键
# 删除表中之前的组件约束
mysql> alter table stu_list drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu_list;
+----------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | char(12) | NO | | NULL | |
| sex | enum('male','female') | NO | | NULL | |
| nationl | char(10) | YES | | 汉 | |
| age | int(11) | NO | | 18 | |
| national | char(10) | YES | | 汉 | |
+----------+-----------------------+------+-----+---------+-------+
6 rows in set (0.02 sec)
#设置id为主键
mysql> alter table stu_list add primary key(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
多表结构的创建与分析
如果两张表之间有关系，如何找出它们时什么关系?
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表
#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
建立表之间的关系
# 多对一
多个学生是一个班级的；
多本身是一个作者写的；
多个商品在一个订单中；
多本书同一个出版社；
关联方式: foreign key
多对一建表
# 创建出版社表
create table press (id int primary key auto_increment,press_name varchar(20) not null unique);
#创建书籍表
create table books (id int primary key auto_increment,name varchar(20),press_id int,foreign key (press_id) references press(id));
#插入内容
insert into press(press_name) values ("家里蹲大学出版社"),("人民种冬瓜出版社"),("欢大大一时兴起办的出版社");
insert into books(name,press_id) values ('被窝睡觉如何更保温',1),('撸猫的1326种方式',3),('冬瓜的品质养成记',2),('酸菜干饭锅巴变脆的秘诀',3),('怎样同时看三个电视剧',1),('冬瓜知道自己姓冬',2);
mysql> select * from press;
+----+--------------------------------------+
| id | press_name |
+----+--------------------------------------+
| 2 | 人民种冬瓜出版社 |
| 1 | 家里蹲大学出版社 |
| 3 | 欢大大一时兴起办的出版社 |
+----+--------------------------------------+
3 rows in set (0.00 sec)
mysql> select * from books;
+----+-----------------------------------+----------+
| id | name | press_id |
+----+-----------------------------------+----------+
| 11 | 被窝睡觉如何更保温 | 1 |
| 12 | 撸猫的1326种方式 | 3 |
| 13 | 冬瓜的品质养成记 | 2 |
| 14 | 酸菜干饭锅巴变脆的秘诀 | 3 |
| 15 | 怎样同时看三个电视剧 | 1 |
| 16 | 冬瓜知道自己姓冬 | 2 |
+----+-----------------------------------+----------+
6 rows in set (0.00 sec)
# 多对多
服务和机器
一个服务可能被部署到多台机器上，一台机器上也可以部署多个服务
学生和课程
一个学生可以选择多门课程，一门课程也可以被多个学生选择
一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
关联方式：foreign key+一张新的表
以下两张图分别是书的多个作者，与作者的多本书，多对多的关系，需要用一张表把这些关系存起来。
多对多建表
# 创建书表
create table books (id int primary key auto_increment,bookname varchar(20) not null);
# 创建作者表
create table author (id int primary key auto_increment,authorname varchar(20) not null);
# 创建书与作者的关系表
create table author_books (
id int unique auto_increment,
bookid int not null,
authorid int not null,
foreign key (bookid) references books(id),
foreign key (authorid) references author(id),
primary key(bookid,authorid)
);
#插入数据
insert into books values (1,'被窝睡觉如何更保温'),(2,'撸猫的1326种方式'),(3,'冬瓜的品质养成记'),(4,'怎样同时看三个电视剧'),(5,'酸菜干饭锅巴变脆的秘诀');
insert into author values (1,'欢大大'),(2,'谭狗子'),(3,'杨稀饭'),(4,'冬专家');
insert into author_books(bookid,authorid) values (1,2),(1,3),(2,1),(2,2),(3,1),(3,4),(4,2),(5,1),(5,4);
# 查看内容
mysql> select * from books;
+----+-----------------------------------+
| id | bookname |
+----+-----------------------------------+
| 1 | 被窝睡觉如何更保温 |
| 2 | 撸猫的1326种方式 |
| 3 | 冬瓜的品质养成记 |
| 4 | 怎样同时看三个电视剧 |
| 5 | 酸菜干饭锅巴变脆的秘诀 |
+----+-----------------------------------+
5 rows in set (0.00 sec)
mysql> select * from author;
+----+------------+
| id | authorname |
+----+------------+
| 1 | 欢大大 |
| 2 | 谭狗子 |
| 3 | 杨稀饭 |
| 4 | 冬专家 |
+----+------------+
4 rows in set (0.00 sec)
mysql> select * from author_books;
+----+--------+----------+
| id | bookid | authorid |
+----+--------+----------+
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 2 | 2 |
| 5 | 3 | 1 |
| 6 | 3 | 4 |
| 7 | 4 | 2 |
| 8 | 5 | 1 |
| 9 | 5 | 4 |
+----+--------+----------+
9 rows in set (0.00 sec)
#一对一
两张表：客户表和学生表
一对一：一个学生是一个客户
关联方式：foreign key+unique
一对一建表
# 创建客户表
create table customers (id int primary key auto_increment,cuname char(10) not null unique);
# 创建学生表,因为是一对一，所以外键的字段要加上不能重复的限制
create table stu (id int primary key auto_increment,cuid int unique,foreign key (cuid) references customers(id));
insert into customers(cuname) values ("lisa"),("tom"),('mike'),('jerry'),('sala'),('tigger'),('semon');
insert into stu (cuid) values (2),(4),(5),(7);
作业
分析:
1. 多个学生对应一个班级，学生表与班级表 多对一；
2. 一个老师对应多个课程，老师表与课程表 一对多；
3. 一个学生对应多门课程，一个课程对应多个学生，学生表与课程表 多对多，成绩表就是他们的第三张关系表。
答案 点击查看代码
create table class (
cid int primary key auto_increment,
caption char(10) not null unique
);
create table teacher (
tid int primary key auto_increment,
tname char(10) not null unique
);
create table student (
sid int primary key auto_increment,
sname char(10) not null,
gender enum('女','男') not null,
class_id int,
foreign key (class_id) references class(cid)
);
create table course (
cid int primary key auto_increment,
cname char(10) not null unique,
teacher_id int,
foreign key (teacher_id) references teacher(tid)
);
create table score (
sid int primary key auto_increment,
student_id int,course_id int,
number int,
foreign key (student_id) references student(sid),
foreign key (course_id) references course(cid)
);
#插入数据
insert into class(caption) values ('三年一班'),('三年二班'),('三年三班');
insert into teacher(tname) values ('王老师'),('李老师'),('张老师');
insert into student(sname,gender,class_id) values ('钢弹','男',1),('铁锤','女',1),('山炮','男',2);
insert into course(cname,teacher_id) values ('生物',1),('体育',1),('物理',2);
insert into score(student_id,course_id,number) values (1,1,60),(1,2,59),(2,2,100);
插入数据INSERT
1. 按顺序插入
INSERT INTO 表名 VALUES (值1,值2,值3…值n); # 插入一行值
INSERT INTO 表名 VALUES (值1,值2,值3…值n),...,(值1,值2,值3…值n); # 插入多行值
2. 指定字段查询
INSERT INTO 表名(字段2,字段3…字段n) VALUES (值2,值3…值n); # 插入一行值
INSERT INTO 表名(字段2,字段3…字段n) VALUES (值2,值3…值n),...,(值2,值3…值n); # 插入多行值
3. 插入查询结果
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE 条件;
插入查询结果 演示
点击查看代码
mysql> select * from student;
+----+------+--------+--------+
| id | name | sex | class |
+----+------+--------+--------+
| 1 | yy | male | 一班 |
| 2 | yh | female | 二班 |
| 3 | tt | female | 一班 |
| 4 | xf | male | 二班 |
| 5 | ty | male | 二班 |
+----+------+--------+--------+
5 rows in set (0.00 sec)
# 将student表中二班的 name，sex插入 studenta表的 name，sex
mysql> insert into studenta (name,sex) select name,sex from student where class='二班';
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from studenta;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | yh | female |
| 2 | xf | male |
| 3 | ty | male |
+----+------+--------+
3 rows in set (0.00 sec)
更新数据UPDATE
语法：
UPDATE 表名 SET
字段1=值1,
字段2=值2,
WHERE 条件;
示例：
UPDATE mysql.user SET password=password(‘123’)
where user=’root’ and host=’localhost’;
删除数据DELETE
1. 删除某些/某条数据
DELETE FROM 表名
WHERE 条件;
示例：
DELETE FROM mysql.user WHERE password=’’;
2. 清空整张表
delete from 表名;
# 会清空表但不会清空自增字段的offset(偏移量)值。即清空后插入的数据还是从之前最大的数+1开始。
truncate table 表名;
# 清空表与自增字段的偏移量，后续插入数据从0开始。
单表查询
#distinct 表示去重，可选
SELECT DISTINCT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field 分组
HAVING 筛选
ORDER BY field 排序，默认
LIMIT 限制条数
SELECT语句
# 最简单的select
select * from 表名;
select 字段1,字段2,... from 表名;
# 对查询到的字段重命名显示
两种方式，as newname 与 空格newname
select 字段1 as newname1,字段2 as newname2,... from 表名;
select 字段1 newname from 表名;
# 去重，是将查询到的内容以行为单位去重
select distinct 字段1 from 表名; # 对单个字段去重，单个字段视为一行
select distinct 字段1,字段2,... from 表名; #对多个字段去重，多个字段视为一行
# 使用函数
concat() 拼接
select concat('aa','bb','cc'); # 输出 aabbcc
select concat('姓名:',name,' ','性别:',sex) from studenta; # 输出内容格式为 姓名:xf 性别:male
concat_ws() 以什么为分割拼接
select concat_ws('|','aa','bb','cc'); # 输出 aa|bb|cc
select concat_ws('|',name,sex,class) from student; # 输出行的内容都为 xf|male|二班
# 四则运算
select name,salary*12 annual_salary from salary_list;
# CASE语句 用的不多
select (
CASE
WHEN name='xifan' THEN
salary+1000
WHEN name='ty' THEN
salary*12
ELSE
concat(name,' ','who’s this?')
END
) from salary_list;
WHERE约束
where字句中可以使用：
1. 比较运算符：> < >= <= <> != (<> !=都是不等于，用法一样)
2. between 80 and 100 值在80到100之间,闭区间,只能小的值写前面，大的值写后面。
3. in(80,90,100) 值是80或90或100
4. 模糊匹配 like 'e%'
通配符可以是%或_，
%表示任意多字符
_表示一个字符
5. 使用正则表达式
REGEXP '匹配规则';
只能使用一些简单的匹配规则，不识别\d这种。
6. is null,is not null
7. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
使用举例:
select emp_name,salary from employee where salary > 10000;
select emp_name,salary from employee where salary between 10000 and 20000;
select emp_name,salary from employee where salary in (10000,20000);
select emp_name from employee where emp_name like "%咬%";
select emp_name from employee where emp_name like "程__";
select emp_name from employee where emp_name regexp "[a-z]{6}";
select emp_name from employee where emp_name regexp "^程";
select emp_name from employee where post_comment is null;
select emp_name,salary from employee where emp_name like "%咬%" and salary > 18000;
GROUP BY分组
单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数
GROUP BY关键字和GROUP_CONCAT()函数一起使用
按照岗位分组，并查看组内成员名
SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;
GROUP BY与聚合函数一起使用(分组聚合)
#按照岗位分组，并查看每个组有多少人
select post,count(id) as count from employee group by post;
select post,avg(salary) as avg_salary from employee group by post;
注意:
使用group by之后获取的字段只是该组的第一个，不一定和其他后续字段为一行，例如:
select emp_name,post,max(salary) from employee group by post;
+----------+-----------------------------------------+-------------+
| emp_name | post | max(salary) |
+----------+-----------------------------------------+-------------+
| 张野 | operation | 20000.00 |
| 歪歪 | sale | 4000.33 |
| alex | teacher | 1000000.31 |
+----------+-----------------------------------------+-------------+
以上的emp_name 和salary并不是对应的，歪歪正好是sale的第一个而已，而4000是所有sale中最大的那一个值。
聚合函数:
count() 求个数
max() 求最大值
min() 求最小值
sum() 求和
avg() 求平均数
使用举例点击查看代码
mysql> select post from employee group by post;
+-----------------------------------------+
| post |
+-----------------------------------------+
| operation |
| sale |
| teacher |
| 老男孩驻沙河办事处外交大使 |
+-----------------------------------------+
4 rows in set (0.01 sec)
mysql> select post,count(id) from employee group by post;
+-----------------------------------------+-----------+
| post | count(id) |
+-----------------------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+-----------------------------------------+-----------+
4 rows in set (0.00 sec)
mysql> select post,group_concat(emp_name) from employee group by post;
+-----------------------------------------+---------------------------------------------------------+
| post | group_concat(emp_name) |
+-----------------------------------------+---------------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| sale | 格格,星星,丁丁,丫丫,歪歪 |
| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
| 老男孩驻沙河办事处外交大使 | egon |
+-----------------------------------------+---------------------------------------------------------+
mysql> select emp_name,post,max(salary) from employee where post like "%e%" group by post ;
+----------+-----------+-------------+
| emp_name | post | max(salary) |
+----------+-----------+-------------+
| 张野 | operation | 20000.00 |
| 歪歪 | sale | 4000.33 |
| alex | teacher | 1000000.31 |
+----------+-----------+-------------+
HAVING过滤
HAVING与WHERE与GROUP BY‘
#！！！执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
语法：
select xxx from 表名 group by xxx having xxx;
select xxx from 表名 having xxx; #把整个表视为一组
使用举例:
mysql> select post,avg(salary) as count from employee group by post having avg(salary) > 10000;
+-----------+---------------+
| post | count |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00 sec)
ORDER BY 查询排序
根据某项按行排列,不会打乱单行的内容
按单列排序
SELECT xxx FROM 表名 ORDER BY xxx; # 默认 ASC 从小到大排序
SELECT xxx FROM 表名 ORDER BY xxx ASC;
SELECT xxx FROM 表名 ORDER BY xxx DESC; # DESC 从大到小排序
eg:
select emp_name,age,salary from employee order by salary desc; #按照薪资从大到小排序
按多列排序
先按照age从小到大排序，如果年纪相同，则按照薪资从大到小排序
SELECT * from employee ORDER BY age,salary DESC;
LIMIT限制
表示从第m行开始(不包括),往后获取n行
两种写法:
limit m,n 不写m，默认从第0行开始
limit n offset m m,n不能省略
eg:
select * from employee limit 1; # 获取第一行
select * from employee limit 5; # 获取前5行
# 从第二行开始(不包括)，往后获取3行
select * from employee limit 2,3;
select * from employee limit 3 offset 2;
优先级
from > where > group by > having > select > order by > limit
当select中对某字段重命名后:
- where 不能使用新的字段名；
- order by可以直接使用新名字；
- having经过mysql特殊处理，可以感知到select语句中的重命名。
在执行查询语句时，实际使通过where，group by，having锁定对应的行，再循环每一行执行select语句。
多表查询
多表查询也叫联表查询，分为多表连接查询与子查询,连接查询的效率高于子查询,所以更推荐多用连接查询。
建表与数据准备 两张表: department 与 employee
点击查看代码
#建表
create table department(
id int,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
#查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
多表连接查询
表与表之间的连接方式
#交叉连接 (不常用)
select * from 表1，表2 where 条件;
#内连接
select * from 表1 inner join 表2 on 条件;
# 可以将from 表1 inner join 表2 on 条件视为一张表
select * from 表1 inner join 表2 on 条件 where/group by/having/order by/limit ...;
select t1.xx t2.xx from 表1 t1 inner join 表2 t2 on t1.字段=t2.字段; #对表重命名
#外连接
#左外连接
select * from 表1 left join 表2 on 条件;
#右外连接
select * from 表1 right join 表2 on 条件;
#全连接 (使用union 间接实现)
select * from 表1 left join 表2 on 条件
union
select * from 表1 right join 表2 on 条件;
1. 交叉连接：不适用任何匹配条件。生成笛卡尔积
生成笛卡尔积
mysql> select * from department,employee;
+------+--------------+----+------------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+------------+--------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 201 | 人力资源 | 1 | egon | male | 18 | 200 |
| 202 | 销售 | 1 | egon | male | 18 | 200 |
| 203 | 运营 | 1 | egon | male | 18 | 200 |
| 200 | 技术 | 2 | alex | female | 48 | 201 |
| 201 | 人力资源 | 2 | alex | female | 48 | 201 |
| 202 | 销售 | 2 | alex | female | 48 | 201 |
| 203 | 运营 | 2 | alex | female | 48 | 201 |
| 200 | 技术 | 3 | wupeiqi | male | 38 | 201 |
| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |
| 202 | 销售 | 3 | wupeiqi | male | 38 | 201 |
| 203 | 运营 | 3 | wupeiqi | male | 38 | 201 |
| 200 | 技术 | 4 | yuanhao | female | 28 | 202 |
| 201 | 人力资源 | 4 | yuanhao | female | 28 | 202 |
| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |
| 203 | 运营 | 4 | yuanhao | female | 28 | 202 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
| 201 | 人力资源 | 5 | liwenzhou | male | 18 | 200 |
| 202 | 销售 | 5 | liwenzhou | male | 18 | 200 |
| 203 | 运营 | 5 | liwenzhou | male | 18 | 200 |
| 200 | 技术 | 6 | jingliyang | female | 18 | 204 |
| 201 | 人力资源 | 6 | jingliyang | female | 18 | 204 |
| 202 | 销售 | 6 | jingliyang | female | 18 | 204 |
| 203 | 运营 | 6 | jingliyang | female | 18 | 204 |
+------+--------------+----+------------+--------+------+--------+
24 rows in set (0.01 sec)
因为mysql不知道employee中每一行的员工部门与department中部门列表的对应关系，所以，每一个员工都分别对应了所有部门生成一行。
然后需要从笛卡尔积中筛选出正确的数据，加上条件 where department.id=dep_id。
这样得到了一张合并的表，如果需要对这张表再做操作，例如查询age=18的行，因为使用了一个where,无法再加where，(当然可以使用and添加多个条件，这个先忽略)所以说这种查询方式不能添加额外条件。
因为两张表表都有id字段，所以需要使用 表名.id
mysql> select * from department,employee where department.id=dep_id;
+------+--------------+----+-----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+-----------+--------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 201 | 人力资源 | 2 | alex | female | 48 | 201 |
| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |
| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
+------+--------------+----+-----------+--------+------+--------+
5 rows in set (0.00 sec)
2. 内连接：只连接匹配的行
mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
6 rows in set (0.00 sec)
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
4 rows in set (0.00 sec)
# 只连接匹配行，employee中dep_id=204的行 与 department 中id=203的行都没有
mysql> select * from department d inner join employee e on d.id=dep_id;
+------+--------------+----+-----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+-----------+--------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 201 | 人力资源 | 2 | alex | female | 48 | 201 |
| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |
| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
+------+--------------+----+-----------+--------+------+--------+
5 rows in set (0.00 sec)
3. 外连接之左连接：优先显示左表全部记录
# 以左表为准，左边的那张表是department,id=203的行尽管不符合d.id=dep_id条件，也会全部显示
mysql> select d.id,d.name,e.name from department d left join employee e on d.id=dep_id;
+------+--------------+-----------+
| id | name | name |
+------+--------------+-----------+
| 200 | 技术 | egon |
| 201 | 人力资源 | alex |
| 201 | 人力资源 | wupeiqi |
| 202 | 销售 | yuanhao |
| 200 | 技术 | liwenzhou |
| 203 | 运营 | NULL |
+------+--------------+-----------+
3. 外连接之右连接：优先显示右表全部记录
与左连接类似,其实一个左连接就可以解决问题，把右表放左边就行。
5. 全外连接：显示左右两个表全部记录
全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：mysql不支持全外连接 full JOIN
#强调：mysql可以使用以下方式间接实现全外连接
mysql> select d.id,d.name,e.name from department d left join employee e on d.id=dep_id union select d.id,d.name,e.name from department d right join employee e on d.id=dep_id;
+------+--------------+------------+
| id | name | name |
+------+--------------+------------+
| 200 | 技术 | egon |
| 201 | 人力资源 | alex |
| 201 | 人力资源 | wupeiqi |
| 202 | 销售 | yuanhao |
| 200 | 技术 | liwenzhou |
| 203 | 运营 | NULL |
| NULL | NULL | jingliyang |
+------+--------------+------------+
7 rows in set (0.01 sec)
习题:
1. 找到技术部的所有人的姓名
2. 找到人力资源部年龄大于40的人的姓名
3. 找到年龄大于25岁的员工名字及所在部门
4. 以内连接的方式查询两张表，并以age升序方式显示
5. 求每一个部门的人数，并按照人数降序排序
答案，点击查看代码
select e.name from employee e inner join department d on e.dep_id=d.id where d.name='技术';
select e.name from employee e inner join department d on e.dep_id=d.id where d.name='人力资源' and e.age > 40;
select e.name,d.name from employee e inner join department d on d.id=e.dep_id where age>25;
select * from employee e inner join department d on d.id=e.dep_id order by age;
select d.name,count(e.id) num from department d left join employee e on d.id=e.dep_id group by d.name order by num desc;
子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等
- 带IN关键字的子查询
#查询平均年龄在25岁以上的部门名
1. 查询员工表中平级年龄大于25的部门id
select dep_id from employee group by dep_id having avg(age)>25;
2. 查询部门表中第一步的部门id对应的部门name
select name from department where id in (select dep_id from employee group by dep_id having avg(age)>25);
#查看技术部员工姓名
1. 根据部门表查到技术部对应的部门id
select id from department where name='技术';
2. 到员工表根据部门id查到员工姓名
select name from employee where dep_id=(select id from department where name='技术');
#查看不足1人的部门名(子查询得到的是有人的部门id)
1. 获取员工表存在的部门id(存在即至少有一个人)
select dep_id from employee group by dep_id;
或者 查询员工表中人数大于0的部门id
select dep_id from employee group by dep_id having count(id)>0;
2. 查询部门表中不属于第一步查到的值的部门id对应的部门
select name from department where id not in (select dep_id from employee group by dep_id);
- 带比较运算符的子查询
#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
select name,age from employee where age > (select avg(age) from employee);
#查询大于部门内平均年龄的员工名、年龄
1. 获取每个部门的平均年龄得到一张表
select dep_id,avg(age) avg_age from employee group by dep_id
2. 将员工表与第一步的表进行连接查询
select name,age from employee e inner join (select dep_id,avg(age) avg_age from employee group by dep_id) a on e.dep_id=a.dep_id where age > avg_age;
- 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。
#department表中不存在id=333，False
select * from employee where exists (select id from department where id=333);
#department表中存在id=200，Ture
select * from employee where exists (select id from department where id=200);
索引
Eva老师的博客:
mysql索引原理 https://www.cnblogs.com/Eva-J/articles/10126413.html
概念
索引在MySQL中是一种“键”，也是存储引擎用于快速找到记录的一种数据结构。当表中的数据量越来越大时，通过索引优化能够轻易将查询性能提高好几个数量级。
原理
类似于查找书籍目录,先定位到章，再定位到小节，再到页。本质都是: 通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
对于一个7200转的机械硬盘来说，一次IO操作约9ms。考虑到磁盘IO是非常高昂的操作，计算机操作系统做了预读操作，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。
索引结构
索引的根本目的就是缩短查询时间，也就是减少IO操作的次数。innodb的索引默认使用b+树结构，由b树(平衡树,左子树与右子树的高度差不能大于1)与二叉树结合演变而来。IO操作的次数取决于b+树的高度，B+树的高度一般都在2~4层。还有一种hash类型的索引，这里不作过多介绍。
特征:
- 有序性: 通过一定算法将索引字段有序排列；
- 平衡树:能够让每一次的查询时间尽量平衡；
- 分支节点不存储数据，让其更多存储索引,使树的高度尽量矮,IO操作次数尽量少；
- 在所有叶子节点之间加入了双向的地址链接，使查找范围更快。
聚集索引与辅助索引
数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。
聚集索引
也叫聚簇索引。innodb存储引擎将数据存储在聚集索引的叶子节点，所以每张表都会有且只有一个聚集索引。
聚集索引是根据主键生成的b+树，若这张表未设置主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键。若也没有这样的列，InnoDB就自己产生一个隐藏的六个字节长度的ID值，使其作为聚簇索引。
辅助索引
一张表除了聚集索引外，其他的索引都是辅助索引，也叫做非聚集索引。
辅助索引的叶子节点不包含行记录的全部数据，而是记录该数据对应的聚集索引字段对应的值。在查询有辅助索引的某个字段时，会先找到辅助索引的叶子节点上的聚集索引的值，再找一遍聚集索引，拿到所需行。
聚集索引与辅助索引的不同之处：
聚集索引
1.记录的索引顺序与物理顺序相关，且叶子节点有双向地址链接
更适合between and和order by操作
2.叶子结点直接对应数据
从中间级的索引页的索引行直接对应数据页
3.每张表只能创建一个聚集索引
非聚集索引
1.索引顺序和物理顺序无关
2.叶子结点不直接指向数据页
3.每张表可以有多个非聚集索引，需要更多磁盘和内容
多个索引会影响insert和update的速度(因为插入/更新数据后，需要调整保持树的有序与平衡，当索引较多所需时间就会长，所以没用的非聚集索引最好删掉)
索引分类
- 普通索引：INDEX 没有任何限制，唯一任务就是加快系统对数据的访问速度
- 主键索引：PRIMARY KEY 加速查找+约束（不为空、不能重复）
- 唯一索引：UNIQUE 加速查找+约束（不能重复,多个null不算重复）
- 联合索引：
-PRIMARY KEY(id,name):联合主键索引
-UNIQUE(id,name):联合唯一索引
-INDEX(id,name):联合普通索引
联合索引的最左前缀原则：
例如对(id,name,age) 创建了联合索引，索引匹配的顺序也是从id开始，由左至右匹配，当查询的条件有id时能很好的匹配上索引，若没有id则不能匹配索引。当遇到范围查询（> ， < ，between，like）时，索引失效。
建立索引（a,b,c,d）
select * from 表 where a = 1; #命中索引
select * from 表 where a = 1 and b = 6 and c = 9; #命中索引
select * from 表 where b = 6 and c = 9; #不命中索引
select * from 表 where a = 3 and b = 4 and c > 5 and d = 0; #abc命中索引，d不命中。
建立索引（a,b,d,c),a，b，d 的顺序可以随意调换.
select * from 表 where a = 3 and b = 4 and c > 5 and d = 0; #都命中索引
即联合索引是按照索引字段（a,b,c,d）的顺序从左至右匹配，where后的and连接多个条件会优先查找有索引的或者范围小的条件。
创建索引
#方法一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引：DROP INDEX 索引名 ON 表名字;
示范，点击查看代码
#方式一
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index ix_name(name) #index没有key
);
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index(name) #index没有key
);
#方式二
create index ix_age on t1(age);
#create index mix_id on t1(id,name,age);
#方式三
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);
#查看
mysql> show create table t1;
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(1) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
UNIQUE KEY `uni_id` (`id`),
KEY `ix_name` (`name`),
KEY `ix_age` (`age`),
KEY `ix_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
测试索引
准备数据:
点击查看代码
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1 values(i,'eva','female',concat('eva',i,'@oldboy'));
set i=i+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
#3. 查看存储过程
show create procedure auto_insert1\G
#4. 调用存储过程
call auto_insert1();
添加索引后查询速度显著增快
参与计算不命中索引
范围越大查询时间越长
like 指定开头的能匹配索引
总结**！！！
正确地使用mysql数据库
从库地角度:
1. 搭建集群；
2. 读写分离；
3. 分库；
从表的角度:
1. 合理安排表之间的关系:该拆的拆，该合的合
2. 把固定长度的字段放在前面
3. 尽量使用char而不是varchar
从操作数据的角度:
1. 尽量在where字段就约束到一个比较小的范围: 分页
2. 尽量使用连表查询代替子查询；(连表查询效率更高)
3. 删除和修改数据时条件尽量使用主键；
4. 合理的创建索引与使用索引。
创建索引:
1. 选择区分度大的列，重复度小于1/10； （更好的缩小范围）
2. 尽量选择短的字段； （能更快地根据算法有序化，以及每个片存储更多的数据，减小树的高度）
3. 不要创建不必要的索引，不用的索引及时删除。（更新、写入数据时会调整索引结构，索引太多写入时间长，也占空间）
使用索引:
1. 查询语句的条件没有索引字段不命中索引；
2. 在条件中使用范围,范围越大,查询速度越慢;
between...and...,limit,!=,> 等
3. like 'a%' 能命中索引，like '%a' 不能命中；
4. 条件不能参与计算，不能使用函数；eg: where id*10=10000 会将id值挨个儿带进去算, where id=10000/10 会先算出结果，再匹配索引。
5. and/or
# and多个条件相连，会优先匹配有索引的条件，缩小范围后再用其他条件筛选；
# or 所有条件的字段都有索引才能命中；
6. 联合索引
遵循最左前缀原则，且从出现范围开始失效。
7. 条件中的数据类型和实际字段的类型必须一致。
8. select 字段中应该包含order by的字段。（因为select先筛选出字段,order by 再根据字段排序。若order by的字段不包含于select字段中，还得再筛一遍）
查询优化神器-explain
explain的官方介绍: http://dev.mysql.com/doc/refman/5.5/en/explain-output.html
eva老师的详细教程: https://www.cnblogs.com/Eva-J/articles/10145093.html
此处只做简单介绍。
explain(执行计划)，可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句。explain主要用于分析查询语句或表结构的性能瓶颈。
语法为: explain sql语句。
对于type项的值，越靠后执行速度越快：
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
mysql> explain select * from student;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 16 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
分页查询性能优化
在前端页面中经常见到分页：
我们知道分页的最基础操作就是使用limit
但所谓limit m,n,MySQL 并不是跳过m行,直接取n行,而是先取m+n行,然后返回放弃前m行,返回n 行,那当m的值特别大的时候,此时使用limit m,n效率就非常的低下。
例如:
有3000000条数据，每页展示20条。
select * from s1 limit 0,20; #第一页
select * from s1 limit 20,20; #第二页
...
select * from s1 limit 2397600,20; #第n页
...
但是我们发现数据量很大时，越往后查越慢
mysql> select * from s1 limit 0,20;
...
20 rows in set (0.00 sec)
mysql> select * from s1 limit 2890000,20;
...
20 rows in set (1.62 sec)
所以需要优化查询方法。
当查询方法为点击上一页/下一页的方式，我们可以获取到当前页的最大/最小id值,使用索引id查询。
假如当前的最大id为2890000，最小id为2889981。
点击上一页sql为:
mysql> select * from s1 where id between 2889981-20 and 2889981;
20 rows in set (0.00 sec)
mysql> select * from s1 where id < 2889981 and id >= 2889981-20;
20 rows in set (0.00 sec)
点击下一页sql为:
mysql> select * from s1 where id between 2890000+1 and 2890000+20;
20 rows in set (0.00 sec)
mysql> select * from s1 where id <= 2890000+20 and id > 2890000;
20 rows in set (0.00 sec)
当随意点击一页，例如第140008页时:
mysql> select * from s1 where id between ((140008-1)*20)+1 and 140008*20;
20 rows in set (0.00 sec)
这里只是简单的举了两个优化的例子，具体要用到相关知识时，还需要深入学习。
数据备份
这里只简单介绍以下mysqldump
# 单库备份(命令行执行)
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
# 恢复某个库
# 方法一:命令行执行
# mysql -h 服务器 -u用户名 -p密码 < 备份文件.sql
# 方法二:mysql中执行
# mysql> use 数据库名; #切到要恢复的库中
# mysql> SET SQL_LOG_BIN=0; #关闭二进制日志，只对当前session生效
# mysql> source 备份文件.sql
#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql
#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql
pymysql模块
在python中可使用此模块操作数据库。
安装模块
pip install PyMySQL
连接数据库与查询
- fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
- fetchmany(n): 接收n个返回结果.
- fetchall(): 接收全部的返回结果行.
- rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。
import pymysql
conn=pymysql.connect(host='127.0.0.1',
user='root',
password='123',
database='school')
#声明默认类型游标,查询结果会返回元组格式：(1, '钢弹', '男', 1)
cur=conn.cursor()
#声明游标,查询结果返回DictCursor类型对象,格式为 {'sid': 1, 'sname': '钢弹', 'gender': '男', 'class_id': 1}
# cur=conn.cursor(cursor=pymysql.cursors.DictCursor)
try:
cur.execute('select * from student;') #这里的分号可加可不加,它会自动补全
print('fetchone1')
print(cur.fetchone()) # fetchone()获取一行值
print('fetchone2')
print(cur.fetchone()) # fetchone()获取一行值
print('fetchmany')
print(cur.fetchmany(2)) # fetchmany() 获取多行值
print('fetchall')
print(cur.fetchall()) #获取全部值
except Exception as e:
print(e)
cur.close() # 释放游标资源
conn.close() # 关闭数据库连接
#########################################################################
输出:
fetchone1 #用fetchxx方法每一次都从上一次取值的地方接着取
(1, '钢弹', '男', 1)
fetchone2
(2, '铁锤', '女', 1)
fetchmany
((3, '山炮', '男', 2), (4, 'tom', '男', 2))
fetchall
((5, 'jerry', '男', 1), (6, 'lily', '女', 1), (7, 'xifan', '男', 1), (8, 'steven', '男', 2))
cur.rowcount 获取查询到的行数
import pymysql
from pymysql import cursors
conn=pymysql.connect(host='127.0.0.1',
user='root',
password='123',
database='school')
cur=conn.cursor()
try:
cur.execute('select * from student;')
for i in range(cur.rowcount): #根据cur.rowcount，结合fetchone，for循环获取结果
print(cur.fetchone())
except Exception as e:
print(e)
cur.close() # 释放游标资源
conn.close() # 关闭数据库连接
增删改
import pymysql
conn=pymysql.connect(host='127.0.0.1',
user='root',
password='123',
database='school')
cur=conn.cursor() #声明游标
try:
cur.execute('update student set class_id=2 where sname="铁锤"') #sql语句的分号可加可不加,它会自动补全
cur.execute('insert into student(sname,gender,class_id) values ("张小小","女",2)')
cur.execute('delete from student where sid=5')
conn.commit() #这里的提交操作会更新到数据库
except Exception as e:
print(e)
conn.rollback() #清除在内存中的还未提交到数据库的更改,即只要 conn.commit() 还未执行或执行失败，上面的多条修改都作废。
cur.close() # 释放游标资源
conn.close() # 关闭数据库连接
登录操作
获取外部输入字符拼接为sql语句，这里列举两种方式:
方式一: 使用python中字符串拼接的方法，拼接成sql再执行。此种方法易被sql注入，不安全。
import pymysql
from pymysql import cursors
iname=input("please input urname:")
ipassword=input("please input urnpwd:")
conn=pymysql.connect(host='127.0.0.1',
user='root',
password='123',
database='school')
cur=conn.cursor()
sql='select * from userinfo where name="%s" and password="%s";' %(iname,ipassword) # 拼接字符串为sql语句,不要忘了引号
print(sql)
cur.execute(sql)
print(cur.fetchone())
cur.close()
conn.close()
两句sql注入的语句为:
select * from userinfo where name="yy";-- " and password="66666"; # --类似于#，表示注释掉后面的内容
select * from userinfo where name="666" or 1=1;-- " and password="66666"; # 使用or 1=1,则where true,相当于select * from userinfo
方式二: 使用pymysql自带的拼接方式
import pymysql
from pymysql import cursors
iname=input("please input urname:")
ipassword=input("please input urnpwd:")
conn=pymysql.connect(host='127.0.0.1',
user='root',
password='123',
database='school')
cur=conn.cursor()
sql='select * from userinfo where name=%s and password=%s' #只能使用%s占位
cur.execute(sql,(iname,ipassword)) #将变量按顺序放在元组中，作为第二个参数
print(cur.fetchone())
cur.close()
conn.close()
