mysql

登录

mysql -uroot -p -h 192.168.1.10 -P 3306

忘记密码

1. 关闭mysqld的服务

2. 在cmd中执行：mysqld --skip-grant-tables ===》 不用密码就可以登录，绕过密码验证的权限

3. 在cmd中执行：mysql ===》 mysql -uroot -p

4. 执行如下sql指令：

   	update mysql.user set authentication_string=password('') where user = 'root';
   	flush privileges; （一定要操作）
   

5. tskill mysqld #或taskkill -f /PID 7832

6. 重新启动mysql服务

数据库操作

增

create database  数据库名称 charset utf8;

命名规范：

1. 可以由字母、数字、下划线、＠、＃、＄
2. 区分大小写
3. 唯一性
4. 不能使用关键字如 create select
5. 不能单独使用数字
6. 最长128位

删

drop database 数据库名称;

改

alter database 数据库名称 charset utf8;

查

//显示所有数据库
show databases;
//显式某个数据库的具体信息
show create database 数据库名;

使用

use 数据库名;

数据表操作

增

//直接创建
create table 表名(
	字段名  列类型 [可选的参数],  ### 记住加逗号
	字段名  列类型 [可选的参数],  ### 记住加逗号
	字段名  列类型 [可选的参数]  ### 最后一行不加逗号
	.....
)charset=utf8;  #### 后面加分号

//复制创建表结构
mysql> create table t89 like t88;
Query OK, 0 rows affected (0.33 sec)

删

drop table 表名;  #### 线上禁用
mysql> drop table t9;
Query OK, 0 rows affected (0.18 sec)

改

修改表名 
ALTER TABLE 旧表名 RENAME 新表名;
mysql> alter table t8 rename t88;
Query OK, 0 rows affected (0.19 sec)


增加字段
//在最有一列添加列
ALTER TABLE 表名
ADD 字段名 列类型 [可选的参数],
ADD 字段名 列类型 [可选的参数];
mysql> alter table t88 add name varchar(32) not null default '';
Query OK, 0 rows affected (0.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

//在第一列添加列
ALTER TABLE 表名
ADD 字段名 列类型 [可选的参数] FIRST;
mysql> alter table t88 add name3 varchar(32) not null default '' first;
Query OK, 0 rows affected (0.83 sec)
Query OK, 0 rows affected (0.83 sec)

//在某个字段后添加列
ALTER TABLE 表名
ADD 字段名 列类型 [可选的参数] AFTER 字段名;
mysql> alter table t88 add name4 varchar(32) not null default '' after d;
Query OK, 0 rows affected (0.68 sec)
Records: 0  Duplicates: 0  Warnings: 0


删除字段
ALTER TABLE 表名  DROP 字段名;
mysql> alter table t88 drop name4;
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0


修改字段
//修改字段参数
ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
mysql> alter table t88 modify name2 char(20);
Query OK, 1 row affected (0.88 sec)
Records: 1  Duplicates: 0  Warnings: 0

//修改字段名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
mysql> alter table t88 change name2 name22 varchar(32) not null default '';
Query OK, 1 row affected (0.82 sec)
Records: 1  Duplicates: 0  Warnings: 0	

mysql> alter table t88 change name22 name23;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

查

//查看有哪些数据表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

//查看某一数据表的所有列
SHOW COLUMNS FROM 表名;
mysql> show columns from t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   | UNI |         |                |
| age   | int(11)     | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

列约束

auto_increment :  自增 1
primary key : 主键索引，加快查询速度, 列的值不能重复
NOT NULL    标识该字段不能为空
DEFAULT    为该字段设置默认值

例子1：
	create table t1(
		id int,
		name char(5)
	)charset=utf8;

	Query OK, 0 rows affected (0.72 sec)   #### 如果回显是queryok，代表创建成功

​
例子2:

	create table t2(
		id int auto_increment primary key,
		name char(10) 
	)charset=utf8;

	insert into t2 (name) values ('agsol');
	
例子3: (推荐)
	create table t3(
		id  int unsigned auto_increment primary key,
		name char(10) not null default 'xxx',
		age int not null default 0
	)charset=utf8;
	
	mysql> insert into t3 (age) values (10);
	Query OK, 1 row affected (0.05 sec)
		
	mysql> select * from t3;
	+----+------+-----+
	| id | name | age |
	+----+------+-----+
	|  1 | xxx  |  10 |
	+----+------+-----+

列类型

- 数字
	- 整型
		tinyint
		smallint
		int   （************************） 推荐使用
		mediumint
		bigint
		
		a.整数类型
		b.取值范围
		c.unsigned  加上代表不能取负数  只适用于整型
		
		应用场景：
			根据公司业务的场景，来选取合适的类型
							
	- 浮点型 （***********）
		create table t5(
			id int auto_increment primary key,
			salary decimal(16,10),
			num float
		)charset=utf8;
		
		float:  不一定精确 
		decimal: 非常的精确的数字 (5000.23)  decimal(6, 2) m是数字总个数（负号不算），d是小数点后个数。
		
		正好 10 位：
		mysql> insert into t5 (salary, num) values (500023.2312345678, 5000.2374837284783274832);
		Query OK, 1 row affected (0.04 sec)

		mysql> select * from t5;
		+----+-------------------+---------+
		| id | salary            | num     |
		+----+-------------------+---------+
		|  1 | 500023.2312345678 | 5000.24 |
		+----+-------------------+---------+
		1 row in set (0.00 sec)
		
		少于10位：
		mysql> insert into t5 (salary, num) values (500023.231234567, 5000.2374837284783274832);
		Query OK, 1 row affected (0.04 sec)

		mysql> select * from t5;
		+----+-------------------+---------+
		| id | salary            | num     |
		+----+-------------------+---------+
		|  1 | 500023.2312345678 | 5000.24 |
		|  2 | 500023.2312345670 | 5000.24 |
		+----+-------------------+---------+
		
		多于10位：
		mysql> insert into t5 (salary, num) values (500023.23123456789, 5000.2374837284783274832);
		Query OK, 1 row affected, 1 warning (0.03 sec)

		mysql> select * from t5;
		+----+-------------------+---------+
		| id | salary            | num     |
		+----+-------------------+---------+
		|  1 | 500023.2312345678 | 5000.24 |
		|  2 | 500023.2312345670 | 5000.24 |
		|  3 | 500023.2312345679 | 5000.24 |
		+----+-------------------+---------+

- 字符串
	
	- char(长度) ： 定长
		create table t6(
			id unsigned int auto_increment primary key,
			name char(10) not null default 'xxx',
		)charset=utf8;
		
	- varchar(长度)：变长
		create table t6(
			id  int auto_increment primary key,
			name varchar(10) not null default 'xxx'
		)charset=utf8;
	
	mysql> insert into t6 (name) values ('hello');
	Query OK, 1 row affected (0.03 sec)

	mysql> select * from t6;
	+----+-------+
	| id | name  |
	+----+-------+
	|  1 | hello |
	+----+-------+
	1 row in set (0.00 sec)

	mysql> insert into t6 (name) values ('hellodbsabdsjabjdsa');
	ERROR 1406 (22001): Data too long for column 'name' at row 1
	
	区别：
		char: 定长， 无论插入的字符是多少个，永远固定占规定的长度
		场景：
			1. 身份证
			2. 手机号 char(11)
			3. md5加密之后的值，比如密码 等 char(32)
		
		varchar: 变长， 根据插入的字符串的长度来计算所占的字节数，但是有一个字节是用来保存字符串的大小的
		
		注意：如果， 不能确定插入的数据的大小， 一般建议使用 varchar(255)
									
- 时间日期类型
	YEAR
		YYYY（1901/2155）

	DATE
		YYYY-MM-DD（1000-01-01/9999-12-31）

	TIME
		HH:MM:SS（'-838:59:59'/'838:59:59'）

	DATETIME  (***************************)

		YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

	TIMESTAMP

		YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
	
	例子：
		create table t8(
			d date,
			t time,
			dt datetime
		);	
		mysql> insert into t8 values(now(),now(),now());
		Query OK, 1 row affected, 1 warning (0.08 sec)

		mysql> select * from t8;
		+------------+----------+---------------------+
		| d          | t        | dt                  |
		+------------+----------+---------------------+
		| 2019-10-29 | 10:49:51 | 2019-10-29 10:49:51 |
		+------------+----------+---------------------+
		1 row in set (0.00 sec)
		insert into t8 values(now(),now(),now());
				
- 枚举
	列出所有的选项
	
	create table t9 (
		id int auto_increment primary key,
		gender enum('male','female')
	)charset utf8;
	
	mysql> insert into t9 (gender) values ('male');
	Query OK, 1 row affected (0.04 sec)

	mysql> insert into t9 (gender) values ('female');
	Query OK, 1 row affected (0.03 sec)

	mysql> insert into t9 (gender) values ('dshajjdsja');

数据行操作

增

用一条insert语句插入多个值比多条insert插入语句执行效率高

//insert into 表名 (列1, 列2) values (值1,'值2');
insert into t1 (id, name) values (1, 'agsol');
insert into t1 (id, name) values (1, 'a1'),(2, 'a2'),(3,'a3');

//一行插入多个数据
mysql> INSERT INTO customers(
			cust_name,
			cust_addr,
			cust_city,
			cust_state,
			cust_zip,
			cust_country)
	   VALUES(
       		'PEP E.LAPEW',
       		'100 MAIN STREET',
       		'LOS ANGELES',
       		'CA',
       		'90096',
       		'USA'
       		),
       		(
       		'AGSOL',
       		'100 ZHUJIANG STREET',
       		'CHANGYANG',
       		'CY',
       		'10024',
       		'CHN'
            );  		

删

//根据条件删除
delete from 表名 where 条件;
mysql> delete from t5 where id=1;
mysql> delete from t5 where id>1;
mysql> delete from t5 where id>=1;
mysql> delete from t5 where id<1;
mysql> delete from t5 where id<=1;
mysql> delete from t5 where id>=1 and id<10;


//清空表
truncate 表名;
mysql> truncate t5;
Query OK, 0 rows affected (0.25 sec)

delete和truncate的区别

1. delete删除了id(主键)为n的数据,后面再插入数据时,主键自增1(n+1);truncate清空表后会从头开始计算主键的值
2. delete是按照条件删除某些行,truncate是清空表
3. truncate的效率高于delete

改

//update 表名 set 列名1=新值1,列名2=新值2 where 条件;
mysql> update t66 set name='xxxx' where id=30;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t66 set name='xxxx' where id<30;
mysql> update t66 set name='xxxx' where id<=30;
mysql> update t66 set name='xxxx' where id>=30;
mysql> update t66 set name='xxxx' where id>30;
mysql> update t66 set name='xxxx' where id>20 and id<32;
mysql> update t66 set name='xxxx' where id>20 or name='agsol';

查

select * from 表名 where 条件 group by 条件 having 条件 order by 条件 limit 条件;

where
//select 列1, 列2 from 表名;  （*代表查询所有的列）
select * from t66 where id>30 and id<40;
select * from t66 where id<30;
select * from t66 where id<=30;
select * from t66 where id>=30;
select * from t66 where id!=30;

//between..and...: 取值范围是闭区间
mysql> select * from t66 where id between 31 and 33;

//避免重复DISTINCT,将查询后的结果去重
mysql> select distinct name from t66;

//in(80,90,100):
mysql> select * from t66 where id in (23,34,11);

//like : 模糊查询
//以x开头：
mysql> select * from t66 where name like 'x%';
//以x结尾：
mysql> select * from t66 where name like '%x';
//包含x的：
mysql> select * from t66 where name like '%x%';

group by 必须和 聚合函数(count) 一起使用
//1. 以性别为例， 进行分组， 统计一下男生和女生的人数是多少个：
				
mysql> select count(id), gender from  employee group by gender;
+-----------+--------+
| count(id) | gender |
+-----------+--------+
|        10 | male   |
|         8 | female |
+-----------+--------+
2 rows in set (0.00 sec)

mysql> select gender, count(id) as total from  employee group by gender;
+--------+-------+
| gender | total |
+--------+-------+
| male   |    10 |
| female |     8 |
+--------+-------+
2 rows in set (0.00 sec)

//2. 对部门进行分组， 求出每个部门年龄最大的那个人？
mysql> select depart_id,max(age) from employee group by depart_id;
+-----------+----------+
| depart_id | max(age) |
+-----------+----------+
|         1 |       81 |
|         2 |       48 |
|         3 |       28 |
+-----------+----------+
3 rows in set (0.01 sec)

having where是group by分组前的过滤,having是group by分组后的过滤
mysql> select depart_id,avg(age) from employee group by depart_id ;
+-----------+----------+
| depart_id | avg(age) |
+-----------+----------+
|         1 |  45.2500 |
|         2 |  30.0000 |
|         3 |  20.0000 |
+-----------+----------+
3 rows in set (0.00 sec)

mysql> select depart_id,avg(age) from employee group by depart_id having avg(age) > 35;
+-----------+----------+
| depart_id | avg(age) |
+-----------+----------+
|         1 |  45.2500 |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select depart_id,avg(age) as pj from employee group by depart_id having pj > 35;
+-----------+---------+
| depart_id | pj      |
+-----------+---------+
|         1 | 45.2500 |
+-----------+---------+
1 row in set (0.00 sec)

order by 字段名 asc （升序） desc(降序)   
//不写asc或者desc参数默认升序
//如果对多个字段进行排序，
//比如：
//	age desc, id asc;
//	表示： 先对age进行降序， 如果age有相同的行， 则对id进行升序
mysql> SELECT order_num,SUM(quantity*item_price) AS ordertotal
	   FROM orderitems
	   GROUP BY order_num
	   HAVING SUM(quantity*item_price) >= 50
	   ORDER BY ordertotal;
	   
	   
limit offset， size  
//offset： 行数据索引  size: 取多少条数据
//limit行数从0开始计算
//下面语句表示返回5个数据
mysql> SELECT prod_name 
	   FROM products
	   LIMIT 5;
	   
//下面语句表示从第3行开始的4个数据行
mysql> SELECT prod_name
	   FROM products
	   LIMIT 3,4;
//mysql5之后上面的语句可以写成下面的样子,更容易理解.注意3和4的意义
mysql> SELECT prod_name
	   FROM products
	   LIMIT 4 OFFSET 3;

聚合函数

AVG()函数
mysql> SELECT AVG(prod_price) AS avg_price
FROM products;
mysql> SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

COUNT()函数
mysql> SELECT COUNT(*) AS num_cust
FROM customers;

MAX()函数
mysql> SELECT MAX(prod_price) AS max_price
FROM products;

MIN()函数
mysql> SELECT MIN(prod_price) AS min_price
FROM products;

SUM()函数
mysql> SELECT SUM(quantity) AS items_orderd
FROM orderitems
WHERE order_num BETWEEN 1000 AND 2000;

DISTINCT去重
//会先去重再进行函数运算
mysql> SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_idBETWEEN 1000 AND 2000;

组合使用
mysql> SELECT COUNT(*) AS num_items,
			  MIN(prod_price) AS price_min,
			  MAX(prod_price) AS price_mAX,
			  AVG(prod_price) AS price_avg
	   FROM products;

多表查询

外键

constraint 外键名 foreign key (本表被约束的字段) references 约束的表(约束的字段)
//一对多
create table department(
	id int auto_increment primary key,
	name varchar(32) not null default ''
)charset utf8;

create table userinfo (
	id int auto_increment primary key,
	name varchar(32) not null default '',
	depart_id int not null default 1,
	
	constraint fk_user_depart foreign key (depart_id) references department(id),
	#constraint fk_user_depart foreign key (depart_id) references department(id),
	#constraint fk_user_depart foreign key (depart_id) references department(id),
)charset utf8;

//多对多
create table boy (
	id int auto_increment primary key,
	bname varchar(32) not null default ''
)charset utf8;


create table girl (
	id int auto_increment primary key,
	gname varchar(32) not null default ''
)charset utf8;

create table boy2girl (
	id int auto_increment primary key,
	bid int not null default 1,
	gid int not null default 1,
	
	constraint fk_boy2girl_boy foreign key (bid) references boy(id),
	constraint fk_boy2girl_girl foreign key (gid) references girl(id)
)charset utf8;

//一对一
create table user (
	id int auto_increment primary key,
	name varchar(32) not null default ''
)charset=utf8;

create table priv(
	id int auto_increment primary key,
	salary int not null default 0,
	uid int not null default 1,
	
	constraint fk_priv_user foreign key (uid) references user(id),
	unique(uid)
)charset=utf8;

等值联结

mysql> SELECT vend_name,prod_name,prod_price
	   FROM vender,products
	   WHERE vender.vend_id = products.vend_id
	   ORDER BY vend_name,prod_name;

内部联结

下面的语句与上面的语句等价,用ON关键字代替WHERE关键字

其中INNER JOIN可以简写成JOIN

mysql> SELECT vend_name,prod_name,prod_price
	   FROM vender INNER JOIN products
	   ON vender.vend_id = products.vend_id;

使用表别名

mysql> SELECT cust_name,cust_contact 
	   FROM  customers AS c,orders AS o,orderitems AS oi
	   WHERE c.cust_id = o.cust_id
	     AND oi.order_num = o.order_num
	     AND prod_id = 'TNT2';

多个表联结

mysql> SELECT prod_name,vend_name,prod_price,quantity
	   FROM orderitems,products,vendors
	   WHERE products.vend_id = vendors.vend_id
	   AND orderitems.prod_id = products.prod_id
	   AND order_num = 20005;

自联结

mysql> SELECT prod_id,prod_name 
	   FROM products
	   WHERE vend_id = (SELECT vend_id
                       FROM products
                       WHERE prod_id = 'DTNTR');
                       
mysql> SELECT p1.prod_id,p1.prod_name
	   FROM product AS p1,products AS p2
	   WHERE p1.vend_id = p2.vend_id
	     AND p2.prod_id = 'DTNTR';
	     
//上面两个语句的查询结果是一致的,但是自联结的效率比子查询的效率高很多                   
                      

外部联结

LEFT OUTER JOIN可以简写成LEFT JOIN,同理,RIGHT OUTER JOIN也可以简写成RIGHT JOIN

其中左连接是以左侧表为基础,右侧表向左侧表匹配,匹配不上的用NULL显示

LEFT OUTER JOIN和OUT JOIN可以通过交换表的顺序进行等效代替

mysql> SELECT customers.cust_id, orders.order_num
	   FROM customers LEFT OUTER JOIN orders
	    ON customers.cust_id = orders.cust_id;
	    
mysql> SELECT customers.cust_id, orders.order_num
	   FROM customers RIGHT OUTER JOIN orders
	    ON customers.cust_id = orders.cust_id;    

内联结和外联结的区别

inner join只显示匹配的数据

outer join以其中一个表为标准,另一个表对其进行匹配,匹配不上的用NULL占位

索引

索引的种类

主键索引： 加速查找 + 不能重复 + 不能为空 primary key
唯一索引： 加速查找 + 不能重复 unique(name)
联合唯一索引：unique(name, email)
例子：
tom tom@cat.com
jerry jerry@mouse.com

普通索引： 加速查找 index (name)
联合索引： index (name, email)

索引的创建

主键索引：
	
	新增主键索引：
		create table xxx(
			id int auto_increment ,
			primary key(id)
		)
		
		alter table xxx change id id int auto_increment primary key;
		
		alter table t1 add primary key (id);
		
	删除主键索引：
		mysql> alter table t1 drop primary key;
	

唯一索引：
	
	新增：
		1.
		create table t2(
			id int auto_increment primary key,
			name varchar(32) not null default '',
			unique u_name (name)
		)charset utf8
		
		2.
		CREATE  UNIQUE   INDEX  索引名 ON 表名 (字段名) ;
			create  unique index ix_name on t2(name);
		
		3. 
		alter table t2 add unique index ix_name (name)
		
	删除：	
		alter table t2 drop index u_name;

普通索引：	
	
	新增：
		1.
		create table t3(
			id int auto_increment primary key,
			name varchar(32) not null default '',
			index u_name (name)
		)charset utf8
		
		2.
		CREATE  INDEX  索引名 ON 表名 (字段名) ;
			create   index ix_name on t3(name);
		
		3. 
		alter table t3 add  index ix_name (name)
		
	删除：	
		alter table t3 drop index u_name;

索引的优缺点

通过观察 *.ibd文件可知：

1. 索引加快了查询速度
2. 但加了索引之后，会占用大量的磁盘空间

索引未命中的情况

1. 在sql语句中使用运算或者范围(>,<,<=,>=,between A and B,like)等,会导致查询速度下降

2. 使用函数,会导致查询速度下降

3. 类型不一致 查不出

   如果列是字符串类型，传入条件是必须用引号引起来，不然…

   select * from tb1 where email = 999;

4. order by 建议加有索引的字段,否则速度极慢

   select name from s1 order by email desc;
   当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
   
   select email from s1 order by email desc;
   特别的：如果对主键排序，则还是速度很快：
   	select * from tb1 order by nid desc;
   

5. 用count(列)代替count(*)

6. 组合索引最左前缀匹配

   什么时候会创建联合索引？
   	
   	根据公司的业务场景， 在最常用的几列上添加索引
   	
   	select * from user where name='zekai' and email='zekai@qq.com';
   	
   	如果遇到上述业务情况， 错误的做法：
   		alter table user add index ix_name (name);
   		alter table user add index ix_email(email);
   	
   	正确的做法：
   		alter table user add index ix_name_email(name, email);
   
   如果组合索引为：ix_name_email (name,email) ************
   
   where name='zekai' and email='xxxx'       -- 命中索引
   
   where name='zekai'   					  -- 命中索引
   where email='zekai@qq.com'                -- 未命中索引
   
   
   最左前缀匹配
   
   例子：	
   
   	index (a,b,c,d)
   	
   	where a=2 and b=3 and c=4 and d=5   --->命中索引
   	
   	where a=2 and c=3 and d=4   ----> 未命中  没有b
   

   ​

慢查询日志

查询时间超过x秒的称为慢查询,公司自己定义

mysql> show variables like '%slow%';
+---------------------------+-----------------------------------------------+
| Variable_name             | Value                                         |
+---------------------------+-----------------------------------------------+
| log_slow_admin_statements | OFF                                           |
| log_slow_slave_statements | OFF                                           |
| slow_launch_time          | 2                                             |
| slow_query_log            | OFF   ### 默认关闭慢SQl查询日志， on                                          |
| slow_query_log_file       | D:\mysql-5.7.28\data\DESKTOP-910UNQE-slow.log | ## 慢SQL记录的位置
+---------------------------+-----------------------------------------------+
5 rows in set, 1 warning (0.08 sec)

mysql> show variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |      # 超过10s才会记录


配置慢SQL的变量：
	set global 变量名 = 值
	 
	set global slow_query_log = on;
	
	set global slow_query_log_file="D:/mysql-5.7.28/data/myslow.log";
	
	set global long_query_time=1;

sql注入

因为过于相信用户输入的内容， 根本没有做任何的检验

explain工具

sql语句分析工具

mysql> select * from t3;
+----+------+---------+-----+--------+
| id | name | gender3 | age | gender |
+----+------+---------+-----+--------+
|  1 | xxx  | female  |  10 | female |
+----+------+---------+-----+--------+
1 row in set (0.16 sec)

mysql> explain select * from t3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.14 sec)

mysql> explain select * from t3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ALL						索引指向 all表示全表扫描
possible_keys: NULL						可能用到的索引
          key: NULL						确实用到的索引
      key_len: NULL						索引长度
          ref: NULL
         rows: 1						扫描的长度
     filtered: 100.00
        Extra: NULL						使用到了索引
1 row in set, 1 warning (0.00 sec)

事务

使用事务：
	start transaction;
		sql语句
	commit/rollback;
	
例子：	
	commit成功：
	mysql> start transaction;
	Query OK, 0 rows affected (0.00 sec)

	mysql> update user set salary=900 where name='zekai';
	Query OK, 1 row affected (0.01 sec)
	Rows matched: 1  Changed: 1  Warnings: 0

	mysql> select * from user;
	+----+-------+--------+
	| id | name  | salary |
	+----+-------+--------+
	|  1 | zekai |    900 |
	|  2 | min   |   1000 |
	+----+-------+--------+
	2 rows in set (0.00 sec)

	mysql> update user set salary=1100 where name='min';
	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0

	mysql> select * from user;
	+----+-------+--------+
	| id | name  | salary |
	+----+-------+--------+
	|  1 | zekai |    900 |
	|  2 | min   |   1100 |
	+----+-------+--------+
	2 rows in set (0.00 sec)

	mysql> #2.提交
	mysql> commit;
	Query OK, 0 rows affected (0.06 sec)
	
	rollback回滚：
		mysql> start transaction;
		Query OK, 0 rows affected (0.00 sec)

		mysql>
		mysql>
		mysql> update user set salary=800 where name='zekai';
		Query OK, 1 row affected (0.01 sec)
		Rows matched: 1  Changed: 1  Warnings: 0

		mysql> select * from user;
		+----+-------+--------+
		| id | name  | salary |
		+----+-------+--------+
		|  1 | zekai |    800 |
		|  2 | min   |   1100 |
		+----+-------+--------+
		2 rows in set (0.00 sec)

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

		mysql> select * from user;
		+----+-------+--------+
		| id | name  | salary |
		+----+-------+--------+
		|  1 | zekai |    900 |
		|  2 | min   |   1100 |
		+----+-------+--------+
		2 rows in set (0.00 sec)
	
	rollback回滚，影响所有：
		
		mysql> start transaction;
		Query OK, 0 rows affected (0.00 sec)
		
		mysql> update user set salary=800 where name='zekai';
		Query OK, 1 row affected (0.00 sec)
		Rows matched: 1  Changed: 1  Warnings: 0

		mysql> update user set salary=700 where name='zekai';
		Query OK, 1 row affected (0.00 sec)
		Rows matched: 1  Changed: 1  Warnings: 0

		mysql> select * from user;
		+----+-------+--------+
		| id | name  | salary |
		+----+-------+--------+
		|  1 | zekai |    700 |
		|  2 | min   |   1100 |
		+----+-------+--------+
		2 rows in set (0.00 sec)

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

		mysql> select * from user;
		+----+-------+--------+
		| id | name  | salary |
		+----+-------+--------+
		|  1 | zekai |    900 |
		|  2 | min   |   1100 |
		+----+-------+--------+
		2 rows in set (0.00 sec)

数据库特性ACID

存储引擎

InnoDB  ： 保时捷引擎
	
MyIsam  ： 奔奔引擎

建表的时候,
	create table user (
		id int auto_increment primary key,
		name varchar(32) not null default '',
		salary int not null default 0
	)engine=Innodb charset utf8;

mysql5.5以上， 默认用到就是InnoDB,之前用MyISAM

两个引擎的区别：（**************）
	1. InnoDb支持事务，MyISAM不支持
	2. InnoDB支持行锁，MyISAM支持的表锁

视图

场景:如果有多个sql语句都是相同的,就可以创建一个视图

一个视图就是将某一个sql语句查询的数据抽象成一个虚拟的表

增加视图：
	create view 视图名 as SQL语句;

删除：	
	drop view v1;
	
例子：
	mysql> select * from user where name='zekai';
	+----+-------+--------+
	| id | name  | salary |
	+----+-------+--------+
	|  1 | zekai |    900 |
	+----+-------+--------+
	1 row in set (0.00 sec)


	mysql> create view v1 as select * from user where name='zekai';
	Query OK, 0 rows affected (0.07 sec)


	mysql> show tables;
	+-----------------+
	| Tables_in_test3 |
	+-----------------+
	| user            |
	| v1              |    ##这里的v1就是select * from user where name='zekai';这条sql产生的虚拟表
	+-----------------+
	2 rows in set (0.00 sec)

	mysql> select * from v1;
	+----+-------+--------+
	| id | name  | salary |
	+----+-------+--------+
	|  1 | zekai |    900 |
	+----+-------+--------+
	1 row in set (0.00 sec)

触发器

两张表：
	订单表     库存表
	
场景：
	当我下一个订单的时候， 订单表中需要增加一个记录， 同时库存表中需要减1
	这两个操作是同时发生的，  并且前一个操作出发后一个操作

使用方法：
	
	增加：
		delimiter // #更改终止符
		
		CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON t1 FOR EACH ROW
		BEGIN
			INSERT INTO t2 (NAME) VALUES ('aa');
		END //
		
		delimiter ;  #更改终止符
	
	### 当向tb1表中添加一条数据的同时， 向tb2表添加一条数据
	
	查看：
	 show triggers\G
		*************************** 1. row ***************************
					 Trigger: tri_before_insert_tb1
					   Event: INSERT
					   Table: t2
				   Statement: BEGIN
		INSERT INTO t3 (NAME) VALUES ('aa');
		END
					  Timing: BEFORE
					 Created: 2019-11-01 11:47:20.65
					sql_mode: ONLY_FULL_GROUP_BY
					 Definer: root@localhost
		character_set_client: gbk
		collation_connection: gbk_chinese_ci
		  Database Collation: latin1_swedish_ci
	
	删除：drop trigger 触发器名；
	
	例子：
		mysql> select * from t2;
		Empty set (0.00 sec)

		mysql> select * from t3;
		Empty set (0.00 sec)
		mysql> insert into t2 (name) values ('zekai');
		Query OK, 1 row affected (0.06 sec)

		mysql> select * from t2;
		+----+-------+
		| id | name  |
		+----+-------+
		|  1 | zekai |
		+----+-------+
		1 row in set (0.00 sec)

		mysql> select * from t3;
		+----+------+
		| id | name |
		+----+------+
		|  1 | aa   |
		+----+------+
		1 row in set (0.00 sec)

存储过程

像  一个 SQL函数

创建：
	
	delimiter //

	create procedure p1()
	BEGIN
		select * from user where id=2;
	END //
	
	delimiter ;

例子：
	
	mysql> delimiter //

	mysql> create procedure p1()
		-> BEGIN
		-> select * from user where id=2;
		-> END //
	Query OK, 0 rows affected (0.10 sec)

	mysql> delimiter ;

	mysql> call p1();
	+----+------+--------+
	| id | name | salary |
	+----+------+--------+
	|  2 | min  |   1100 |
	+----+------+--------+
	1 row in set (0.00 sec)

	Query OK, 0 rows affected (0.01 sec)
	
删除：
	drop procedure p1;

函数

CHAR_LENGTH(str)
	返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
	对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

CONCAT(str1,str2,...)
	字符串拼接
	如有任何一个参数为NULL ，则返回值为 NULL。
FORMAT(X,D)
	将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
	例如：
		SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSTR(str,substr)
	返回字符串 str 中子字符串的第一个出现位置。
LEFT(str,len)
	返回字符串str 从开始的len位置的子序列字符。
LOWER(str)
	变小写
UPPER(str)
	变大写
LTRIM(str)
	返回字符串 str ，其引导空格字符被删除。
RTRIM(str)
	返回字符串 str ，结尾空格字符被删去。
SUBSTRING(str,pos,len)
	获取字符串子序列
LOCATE(substr,str,pos)
	获取子序列索引位置
REPEAT(str,count)
	返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
	若 count <= 0,则返回一个空字符串。
	若str 或 count 为 NULL，则返回 NULL 。
REPLACE(str,from_str,to_str)
	返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
REVERSE(str)
	返回字符串 str ，顺序和字符顺序相反。
RIGHT(str,len)
	从字符串str 开始，返回从后边开始len个字符组成的子序列

并发事务带来的问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。

  即一个事务读取到了另外一个事务未提交的数据.

• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

  即两个事务同时修改同一个数据,结果只保留了一个事务的处理结果,而另一个事务的处理结果被丢失.

• 不可重复读（Unrepeatableread）: 指在一个事务A内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中A的两次读数据之间，由于第二个事务B的修改导致第一个事务A两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况,因此称为不可重复读。

• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读的重点是修改，幻读的重点在于新增或者删除。

事务隔离级别

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读.
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生.
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生.[mysql默认的级别]
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读.

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;许念安穆延霆命令来查看,MySQL 8.0 该命令改为SELECT @@transaction_isolation;

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

修改mysql的隔离级别

在配置文件中, 将transcation-isolation改变为对应级别.

乐观锁与悲观锁

乐观锁

乐观锁不是数据库本身自带的锁，需要程序员自己实现。

通常的实现方式是：对表的数据进行操作时，同时将数据表的版本字段取出，等到操作完毕进行提交时，将数据版本号与表内的数据版本号进行比较，如果相等，说明这段时间内没有别的事务对数据表进行操作，则将版本号加一，并予以更新。否则认为是过期数据，进行回滚。

悲观锁

悲观锁，正如其名，它指的是对数据被外界（包括当前系统的其它事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排它性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

mysql中利用 select … for update 的悲观锁语法锁住记录

django中,User.objects.select_for_updata.fileter().first()

总结

	悲观锁	乐观锁
概念	查询时直接锁住记录使得其它事务不能查询，更不能更新	提交更新时检查版本或者时间戳是否符合
语法	select … for update	使用 version 或者 timestamp 进行比较
实现者	数据库本身	开发者
适用场景	并发量大	并发量小

数据备份

用法：
	#语法：
	# mysqldump -h 服务器 -u用户名 -p密码 数据库名 表名,  表名,.... > aaa.sql

	#示例：
	#单库备份
	mysqldump -uroot -p123 db1 > db1.sql
	mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

	#多库备份
	mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

	#备份所有库
	mysqldump -uroot -p123 --all-databases > all.sql

重新导入：
	mysql> source D:/test3.sql;