MySQL数据库管理-云计算学习笔记整理

数据库

类型：

生产环境主流的关系型数据库有 Oracle、Microsoft SQL Server、MySQL/MariaDB等。

生产环境主流的非关系型数据库有 MongoDB Memcached Redis

#增
创建数据库 
create database <数据库> default character set utf8;
创建表 
create table <表名>(字段名 数据类型，字段名 数据类型，...);
插入数据 
insert into <表名>(字段名，字段名) values(字段值，字段值)
连续插入
insert into <表名>(字段名，字段名) values(字段值，字段值),(字段值，字段值),...,(字段值，字段值);
向表里增加字段
alter table <表名> add 字段名 数据类型;
在指定字段后面添加新字段
alter table <表名> add column 新字段 数据类型 after 指定字段;

#删
删除表内某字段值 
delete from <表名> where 筛选条件;
删除表内所有数据，不删除表结构 
truncate <表名>;
删除表字段 
alter table <表名> drop 字段名
删除表 
drop table <表名>
删除数据库 
drop database 数据库名

#改
修改表名 
alter table <旧表名> rename <新表名>;
修改字段数据类型 
alter table <表名> modify <字段> 数据类型 <类型约束>;
修改字段名 
alter table <表名> change <旧字段> <新字段> 数据类型 <类型约束>;
更新字段值 
update <表名> set 字段名=更新字段值 where 筛选条件;
修改表的储存引擎 
alter table 表名 engine=MyISAM

#查
查看当前使用的数据库
select database();或者show tables;第一行为当前使用数据库
查看表结构
desc <表名>;

insert必须的向每个字段都添加值，不能单独给一个字段添加值

mysql> select * from test_user;
+-----+--------+--------+
| id  | name   | number |
+-----+--------+--------+
|   1 | qfedu  |   1001 |
+-----+--------+--------+
1 rows in set (0.00 sec)

mysql> update test_user set name='qfedu1' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_user;
+-----+--------+--------+
| id  | name   | number |
+-----+--------+--------+
|   1 | qfedu1 |   1001 |
+-----+--------+--------+
1 rows in set (0.00 sec)

MySQL数据类型约束

非空约束

设置字段值不允许为空

创建test_null表，并username不允许为空值。
mysql> create table test_null(id int,username varchar(20) not null) charset=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_null values(1,'qfedu');
Query OK, 1 row affected (0.00 sec)

mysql> insert  into test_null(id) values(2);
ERROR 1364 (HY000): Field 'username' doesn't have a default value

mysql> select * from test_null;
+------+----------+
| id   | username |
+------+----------+
|    1 | qfedu    |
+------+----------+
1 row in set (0.00 sec)
mysql> desc test_null;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| username | varchar(20) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• 注意：如果约束不生效可以设置一下sql_mode

  mysql> set session sql_mode='STRICT_TRANS_TABLES';

  唯一约束

  字段添加唯一约束之后，该字段的值不能重复，也就是说在一列当中不能出现一样的值。

  mysql> alter table test_null add unique(id);
  Query OK, 0 rows affected (0.01 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> desc test_null;(可以看到key-id为unique唯一值)
  +----------+-------------+------+-----+---------+-------+
  | Field    | Type        | Null | Key | Default | Extra |
  +----------+-------------+------+-----+---------+-------+
  | id       | int(11)     | YES  | UNI | NULL    |       |
  | username | varchar(20) | NO   |     | NULL    |       |
  +----------+-------------+------+-----+---------+-------+
  2 rows in set (0.01 sec)
  
  
  mysql> insert into test_null(id,username) values(1,'zhangsan');
  ERROR 1062 (23000): Duplicate entry '1' for key 'id'
  
  mysql> insert into test_null(id,username) values(2,'zhangsan');
  Query OK, 1 row affected (0.00 sec)# 主键约束
  

  主键保证记录的唯一性，主键自动为not null

  每张数据表只能存在一个主键 not null+ unique key

  一个unique key又是一个not null的时候，那么它被当做unique key主键

  当一张表里没有一个主键的时候，第一个出现的非空且为唯一的列被视为有主键。

  主键：非空+唯一性

mysql> create table test_user(id int primary key,name varchar(20),number int) charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> desc test_user;(可以看到key-id为primary key 主键)
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| number | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into test_user(id,name,number) values(1,'qfedu',1001);
Query OK, 1 row affected (0.00 sec)
重复id值，报错
mysql> insert into test_user(id,name,number) values(1,'qfedu1',1002);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
置空值，报错
mysql> insert into test_user(name,number) values('qfedu1',1002);
ERROR 1364 (HY000): Field 'id' doesn't have a default value

mysql> insert into test_user(id,name,number) values(2,'qfedu2',1002);
Query OK, 1 row affected (0.00 sec)

主键约束

单字段主键

语法规则：字段名 数据类型 primary key [默认值]

定义表数据创建主键
create table tb_emp1(id int(11) primary key,name varchar(10));
定义表后添加主键
alter table tb_emp1 add primary key(id);

多字段联合主键

主键由多个字段里阿尼和组成,语法规则：primary key [字段1,字段2,字段3,...,字段n]

create table tb_emp2
(
	name varchar(10),
	deptId int(11),
	salary float,
	primary key(name,deptId)
); 

自增长

自增长 auto_increment自动编号，且必须与主键组合使用 默认情况下，起始值为1，每次的增量为1。 当插入记录时，如果为auto_increment数据列明确指定了一个数值，则会出现两种情况：

• 如果插入的值与已有的编号重复，则会出现出错信息，因为auto_increment数据列的值必须是唯一的；

• 如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。如果自增序列的最大值被删除了，则在插入新记录时，该值被重用。

mysql> alter table test_user change id id int auto_increment;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc test_user;(Extra-id为auto_increment自增长)
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                |
| number | int(11)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from test_user;
+----+--------+--------+
| id | name   | number |
+----+--------+--------+
|  1 | qfedu  |   1001 |
|  2 | qfedu2 |   1002 |
+----+--------+--------+
2 rows in set (0.00 sec)

不插入id值，实现id自增长。
mysql> insert into test_user(name,number) values('qfedu3',1003);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_user;
+----+--------+--------+
| id | name   | number |
+----+--------+--------+
|  1 | qfedu  |   1001 |
|  2 | qfedu2 |   1002 |
|  3 | qfedu3 |   1003 |
+----+--------+--------+
3 rows in set (0.00 sec)

第二点验证
mysql> insert into test_user(id,name,number) values(100,'qfedu5',1005);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_user(name,number) values('qfedu6',1006);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_user(name,number) values('qfedu7',1007);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_user;
+-----+--------+--------+
| id  | name   | number |
+-----+--------+--------+
|   1 | qfedu  |   1001 |
|   2 | qfedu2 |   1002 |
|   3 | qfedu3 |   1003 |
| 100 | qfedu5 |   1005 |
| 101 | qfedu6 |   1006 |
| 102 | qfedu7 |   1007 |
+-----+--------+--------+
7 rows in set (0.00 sec)

删除自增长
mysql> alter table test_user change id id int;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

默认约束

添加删除默认约束

mysql> alter table test_user alter number set default 0;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> insert into test_user(id,name) values(4,'qfedu4');
Query OK, 1 row affected (0.00 sec)
当不给number设置值时，它使用设定的默认值
mysql> select * from test_user;
+----+--------+--------+
| id | name   | number |
+----+--------+--------+
|  1 | qfedu  |   1001 |
|  2 | qfedu2 |   1002 |
|  3 | qfedu3 |   1003 |
|  4 | qfedu4 |      0 |
+----+--------+--------+
4 rows in set (0.00 sec)

外键约束

外键用来在两个表的数据之间建立连接，可以是一个字段或者多个字段，一个表可以有一个或者多个外键。外键对应的是引用完整性，一个表的外键可以为空值，若不为空值，则每个外键值必须等于另一个表中主键的某个值。

• 外键：首先它是表中的一个字段，可以不是本表的主键，但对应另一个表的主键。外键的主要作用是保证数据引用的完整性。定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保证数据的一致性、完整性。
• 主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
• 从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

外键约束要求数据表的存储引擎只能为 InnoDB
查看当前mysql服务器支持的存储引擎mysql> show engines;

可以看到第一行：innodb为默认存储引擎。

如果不是则修改配置文件

创建部门表
mysql> create table tb_dept1
(
id int(11) primary key,
name varchar(22) not null,
location varchar(50)
);
定义数据表tb_emp3,让它的主键deptId作为外键关联到tb_dept1的主键。
mysql> create table tb_emp3
    -> (
    -> id int(11) primary key,
    -> name varchar(25),
    -> deptId int(11),
    -> salary float,
    -> constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)
    -> );
向主键id插入数值
mysql> insert into tb_dept1(id,name,location) values(123,'zhangsan','shanghai');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_dept1;
+-----+----------+----------+
| id  | name     | location |
+-----+----------+----------+
| 123 | zhangsan | shanghai |
+-----+----------+----------+
1 row in set (0.00 sec)
当关联外键插入的值不等同于主键的值，则报错，保证了数据的一致性。
mysql> insert into tb_emp3(id,name,deptId,salary) values(456,'lisi',111,'4500.00');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`tb_emp3`, CONSTRAINT `fk_emp_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`))

mysql> insert into tb_emp3(id,name,deptId,salary) values(456,'lisi',123,'4500.00');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_emp3;
+-----+------+--------+--------+
| id  | name | deptId | salary |
+-----+------+--------+--------+
| 456 | lisi |    123 |   4500 |
+-----+------+--------+--------+
1 row in set (0.00 sec)

删除外键
alter table empolyees drop foreign key 外键名;

mysql建表后添加约束

1.主键约束
添加:alter table table_name add primary key (字段)
删除:alter table table_name drop primary key

2.唯一约束
添加:alter table table_name add unique 约束名(字段)
删除:alter table table_name drop key 约束名

3 .非空约束
添加:alter table table_name modify 列名 数据类型 not null
删除:alter table table_name modify 列名 数据类型 null

4.自动增长
添加:alter table table_name modify 列名 int auto_increment
删除:alter table table_name modify 列名 int

5.外键约束
添加:alter table table_name add constraint 约束名 foreign key(外键列)
references 主键表(主键列)

删除:

第一步:删除外键
alter table table_name drop foreign key 约束名

第二步:删除索引
alter table table_name drop index 索引名


6.默认值
添加:alter table table_name alter 列名 set default '值'
删除:alter table table_name alter 列名 drop default

MySQL数据结构

整型

[外链图片转存中…(img-NuieFel4-1639580814347)]

mysql> create table test_tinyint(num tinyint) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)
定义正值最大只能到127，最小到-128
mysql> insert into test_tinyint values(127);
Query OK, 1 row affected (0.04 sec)

使用unsigned无符号插入，但无法表示负数
mysql> create table test_tinyint1(num tinyint unsigned) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test_tinyint1 values(200);
Query OK, 1 row affected (0.01 sec)

[外链图片转存中…(img-IKqJYDpj-1639580814353)]

浮点型

[外链图片转存中…(img-teJaMtfP-1639580814356)]

创建精度为5个数，2个小数位的浮点型数据，那么小数点前只能是三位，小数点后可任意，但最终保留2位，超过精度的部分进行四舍五入。
mysql> create table test_float(num float(5,2)) engine=innodb charset=utf8;

mysql> insert into test_float values(1.234);

mysql> insert into test_float values(10.234);

mysql> insert into test_float values(100.234);

mysql> insert into test_float values(1000.234);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

mysql> insert into test_float values(100.237891);

mysql> select * from test_float;
+--------+
| num    |
+--------+
|   1.23 |
|  10.23 |
| 100.23 |
| 100.24 |
+--------+
5 rows in set (0.00 sec)

定点数

• 浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。
• decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。
• 定点型的数据类型decimal类型，超出小数位，四舍五入保留小数位。

mysql> create table test_decimal(float_num float(10,2),double_num double(20,2),decimal_num decimal(20,2)) engine=innodb charset=utf8;

mysql> insert into test_decimal values(1234567.111,123456789123456789.11,123456789123456789.11);

mysql> select * from test_decimal;
+------------+-----------------------+-----------------------+
| float_num  | double_num(精度丢失)    | decimal_num           |
+------------+-----------------------+-----------------------+
| 1234567.12 | 123456789123456780.00 | 123456789123456789.11 |
+------------+-----------------------+-----------------------+
1 row in set (0.00 sec)

• decimal 无论写入数据中的数据是多少，都不会存在精度丢失问题，decimal 类型常见于银行系统、互联网金融系统等对小数点后的数字比较敏感的系统中。

• float/double 在db中存储的是近似值，而decimal则是以字符串形式进行保存的

• decimal(M,D)的规则和float/double相同，但区别在float/double在不指定M、D时默认按照实际精度来处理而decimal在不指定M、D时默认为decimal(10, 0)

字符串

[外链图片转存中…(img-U6nDGoUO-1639580814357)]

[外链图片转存中…(img-hW8A4Djk-1639580814359)]

char与varchar对空格的处理

mysql> create table test_string(char_value char(5),varchar_value varchar(5)) engine=innodb charset=utf8;

mysql> insert into test_string values('a','a');		#正常输入

mysql> insert into test_string values(' a',' a');	#前面插入空格

mysql> insert into test_string values('a ','a ');	#后面插入空格

mysql> insert into test_string values(' a ',' a ');	#前后面插入空格

mysql> select * from test_string;
+------------+---------------+
| char_value | varchar_value |
+------------+---------------+
| a          | a             |
|  a         |  a            |
| a          | a             |
|  a         |  a            |
+------------+---------------+
4 rows in set (0.00 sec)

查看占用字符
mysql> select length(char_value),length(varchar_value) from test_string;
+--------------------+-----------------------+
| length(char_value) | length(varchar_value) |
+--------------------+-----------------------+
|                  1 |                     1 |
|                  2 |                     2 |
|                  1 |                     2 |
|                  2 |                     3 |
+--------------------+-----------------------+
4 rows in set (0.00 sec)
得知char类型忽略字符后面的空格占位，而varchar不忽略任何空格占位。

varchar允许最大字节

mysql> create table test_varchar(varchar_value varchar(100000)) engine=innodb charset=utf8;
ERROR 1074 (42000): Column length too big for column 'varchar_value' (max = 21845); use BLOB or TEXT instead
mysql> create table test_varchar(varchar_value varchar(21845)) engine=innodb charset=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table test_varchar(varchar_value varchar(21844)) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)

text类型

text类型的字段用于保存非二进制字符串，如文章内容、评论等。当保存或查询text字段的值时，不删除字符串尾部的空格。text类型分为4种：tinytext,text,mediumtext和longtext.

不同的text类型它们各自的存储空间和数据长度是不同的。

• tinytext最大长度为255(2⁸-1)个字符
• text最大长度为65535(2¹⁶-1)个字符
• mediumtext最大长度为16 777 215(2²⁴-1)个字符
• longtext最大长度为4 294 967 295 (2³²-1)个字符

枚举

mysql> create table enum(id int,gender enum('male','female'));

mysql> insert into enum(id,gender) values(1,'male');

mysql> insert into enum(id,gender) values(2,'female');

mysql> select * from enum;
+------+--------+
| id   | gender |
+------+--------+
|    1 | male   |
|    2 | female |
+------+--------+
2 rows in set (0.00 sec)

[外链图片转存中…(img-B9XBBgcY-1639580814361)]

SET类型

set类型（集合类型）也用于定义一种字符串的对象，它可以有另个值或者多个值，set集合中最多有64个成员，其中的值是表创建时为字段的取值规定的一组值。语法格式如下：

set('值1','值2','值3',...,'值n')

set值在内部用整数表示，set列表中的每一值都有一个索引编号，当建表时，set成员值的尾部空格将自动删除。set类型的字段可从定义的set列表值中选择多个字符的联合值来插入。

mysql> create table tb1(s set('a','b','c','d'));

mysql> desc tb1;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| s     | set('a','b','c','d') | YES  |     | NULL    |       |
+-------+----------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into tb1 values('a'),('a,b,a'),('c,b,a');
# 重复值自动删除，并且自动排序。
mysql> select * from tb1;
+-------+
| s     |
+-------+
| a     |
| a,b   |
| a,b,c |
+-------+
3 rows in set (0.00 sec)

日期时间类型

[外链图片转存中…(img-lutih19M-1639580814363)]

mysql> create table test_time(date_value date,time_value time,year_value year,datetime_value datetime,timestamp_value timestamp) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_time values(now(),now(),now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)

[外链图片转存中…(img-ZLrDvsDC-1639580814364)]

[外链图片转存中…(img-f9SE5dQ4-1639580814366)]

运算符

算数运算符 （+，-，*，/，%）

比较运算符 （>，<，=，>=，<=，!=，in，between and，is null，greatest，least，like，regexp）

逻辑运算符 （true，false, not/!,and , &&, or/||）

位运算符 位与（&）,位或（|）,位非（~），位异或（^）,左移（<<）,右移（>>）

位运算

位或（|）

mysql> select 10|15,9|4,4|2;
+-------+-----+-----+
| 10|15 | 9|4 | 4|2 |
+-------+-----+-----+
|    15 |  13 |   6 |
+-------+-----+-----+
1 row in set (0.00 sec)
数据的二进制位进行或运算，一个1或两个1都为1时，结果为1。

位与（&）

数据的二进制位进行与运算，两个1都为1时，结果为1。

位异或（^）

数据的二进制位进行位异或运算，对应的二进制位数不同时，对应位的运算结果为1。

位左移（<<）

将指定的二进制的所有位都左移指定的位数，左移指定位数之后，左边高位的数值将移出丢弃，右边低位空出的位置用0补齐，语法格式：expr << n

mysql> select 1<<2;
+------+
| 1<<2 |
+------+
|    4 |
+------+
1 row in set (0.00 sec)
1的二进制值为0000 0001 左移两位之后变成0000 0100

位右移（>>）同理

位取反（~）

对应的二进制逐位反转，即1取反变为0、0取反变为1

mysql> select 5 &~ 1;
+--------+
| 5 &~ 1 |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)
由于位取反运算符“~”的优先级高于位与运算符“&”的优先级，因此先对1取反运算，然后与5进行二进制数的位与运算，结果为0100，即整数4。

位运算的优先级

[外链图片转存中…(img-wuK8jmHT-1639580814368)]

查询数据select

语法格式：

select
	{* | <字段列表>}
	[
		from <表1>,<表2>...]
		[where <表达式>
		[group by <group by definition>]
		[having <expression> [{<operator> <expression>}...]]
		[order by <order by definition>]
		[limit [<offset>,] <row count>]
	]
select [字段,字段,...,字段n]
from [表或视图]
where [查询条件];

转到MYSQL基础语法.md篇

MySQL索引

• 普通索引（INDEX）：索引列值可重复
• 唯一索引（UNIQUE）：索引列值必须唯一，可以为NULL
• 主键索引（PRIMARY KEY）：索引列值必须唯一，不能为NULL，一个表只能有一个主键索引
• 全文索引（FULL TEXT）：给每个字段创建索引
• 查看索引 show index from 表名；

普通索引

两种创建索引的方式
建表时创建
mysql> create table student (id int not null,name varchar(50) not null,birthday date,sex char(1) not null,index sindex(name(50)));
Query OK, 0 rows affected (0.01 sec)
建表后创建
mysql> create index tindex on student(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
修改表结构创建索引
mysql> alter table student add index xIndex(name(50));

唯一索引

# 在创建表时指定
mysql> create table student (
   id int not null,
   name varchar(100) not null,
   birthday date,
   sex char(1) not null,
    unique idIndex (id)
);
# 基于表结构创建
mysql> create unique index idIndex ON student(id)

主键索引

# 创建表时时指定
mysql> create table student (
   id int,
   name varchar(100) not null,
   birthday date,
   sex char(1) not null,
    primary key(id)
);
# 修改表结构创建
mysql> alter table student add primary key(id);

删除索引

# 直接删除
mysql> DROP INDEX nameIndex ON student;
# 修改表结构删除
mysql> ALTER TABLE student DROP INDEX nameIndex;

主键不能采用直接删除的方式删除。
mysql> ALTER TABLE student DROP PRIMARY KEY;

选择索引的原则

常用于查询条件的字段较适合作为索引，例如WHERE语句和JOIN语句中出现的列

唯一性太差的字段不适合作为索引，例如性别

更新过于频繁（更新频率远高于检索频率）的字段不适合作为索引

使用索引的好处是索引通过一定的算法建立了索引值与列值直接的联系，可以通过索引直接获取对应的行数据，而无需进行全表搜索，因而加快了检索速度

但由于索引也是一种数据结构，它需要占据额外的内存空间，并且读取索引也加会大IO资源的消耗，因而索引并非越多越好，且对过小的表也没有添加索引的必要

MySQL权限控制

创建用户

创建用户zhangsan
mysql> create user 'zhangsan'@'localhost' identified by 'Zs123.com';
Query OK, 0 rows affected (0.00 sec)

创建用户lisi，只允许该用户在192.168.1.0网段登录，并授予全部权限
mysql> grant all on *.* to 'lisi'@'192.168.1.%' identified by 'Ls123.com';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> select user,host from mysql.user;
+---------------+-------------+
| user          | host        |
+---------------+-------------+
| lisi          | 192.168.1.% |
| mysql.session | localhost   |
| mysql.sys     | localhost   |
| root          | localhost   |
| zhangsan      | localhost   |
+---------------+-------------+
5 rows in set (0.00 sec)

[root@localhost ~]# mysql -uzhangsan -hlocalhost -pZs123.com
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
因为权限不够，只能查看部分数据库

[root@localhost ~]# mysql -ulisi -h192.168.1.136 -pLs123.com;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qfedudb            |
| sys                |
| testdb             |
+--------------------+
6 rows in set (0.00 sec)

删除和修改用户

用户删除
mysql> drop user 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+-------------+
| user          | host        |
+---------------+-------------+
| lisi          | 192.168.1.% |
| mysql.session | localhost   |
| mysql.sys     | localhost   |
| root          | localhost   |
+---------------+-------------+
4 rows in set (0.00 sec)

修改用户名
mysql> rename user 'lisi'@'192.168.1.%' to 'wangwu'@'192.168.1.%';
Query OK, 0 rows affected (0.01 sec)

修改root用户密码
[root@localhost ~]# mysqladmin -uroot -p'Syw.123com' password 'Root123.com';
或者内部修改
mysql> alter user 'root'@'localhost' identified by 'Root.123com';
Query OK, 0 rows affected (0.00 sec)
或者
mysql> set password=password('NewPasswd123.com');
Query OK, 0 rows affected, 1 warning (0.00 sec)

修改其他用户密码
[root@localhost ~]# mysql -uwangwu -h192.168.1.136 -pLs123.com;
mysql> alter user 'wangwu'@'192.168.1.%' identified by 'Ww123.com';
Query OK, 0 rows affected (0.00 sec)
或者登录该户后
mysql> set password=password('Wu123.com');
Query OK, 0 rows affected, 1 warning (0.00 sec)

找回密码

找回root密码

绕过mysql授权表，实现mysql数据库密码召回
vim /etc/my.cnf		在mysqld里添加一行
[mysqld]
skip-grant-tables

[root@localhost ~]# systemctl restart mysql.service
[root@localhost ~]# mysql		直接登录到数据库

更新密码
mysql> update mysql.user set authentication_string=password('Root666.com') where user='root';
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

把my.cnf的skip-grant-tables重新注释
重启mysql服务，就可以用新密码进行登录

密码复杂度

mysql默认已安装，不必理会！

查看密码规则

mysql> show variables like 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |检测用户名关闭
| validate_password_dictionary_file    |        |没有字典文件
| validate_password_length             | 8      |密码长度
| validate_password_mixed_case_count   | 1      |8个字符里至少包含一个大写字母和小写字母
| validate_password_number_count       | 1      |8个字符里至少包含一个数字
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

mysql> set global validate_password_policy=0;	将密码策略关闭
Query OK, 0 rows affected (0.00 sec)
mysql>  grant all on *.* to 'admin'@'localhost' identified by '12345678';
Query OK, 0 rows affected, 1 warning (0.01 sec)

规定mysql密码长度限制至少为4位
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'validate%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 4     |
| validate_password_mixed_case_count   | 1     |
| validate_password_number_count       | 1     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

权限回收

查看权限
mysql> show grants for admin@'localhost';

权限个别回收
mysql> revoke select,insert on *.* from admin@'localhost';
Query OK, 0 rows affected (0.00 sec)

全部权限回收
mysql> revoke all on *.* from admin@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for admin@'localhost';
+-------------------------------------------+
| Grants for admin@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)

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

• flush privileges 命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。

• MySQL用户数据和权限有修改后，搜索希望在"不重启MySQL服务"的情况下直接生效，那么就需要执行这个命令。

MySQL日志管理

错误日志

作用：记录MySQL启动及工作过程中，状态、报错、警告

vim /etc/my.cnf记录有错误日志文件位置，也可进行自定义错误日志位置，但需要给自定义的错误日志位置权限，如在/data/下自定义chown -R mysql.mysql /data/

二进制日志

传统二进制日志数据

作用：数据备份，同步的日志，记录整个数据库操作的记录

server-id=2作为每台mysql服务器的标识
log-bin：二进制文件位置，也可以自定义位置和授权

查看二进制日志内容
[root@mysql ~]# cd /var/log/mysql
[root@mysql mysql]# ls
[root@mysql mysql]# mysqlbinlog bin_log.000023

mysql> show variables like '%log_bin%';		查看二进制日志的配置信息
mysql> show binary logs;	#查看总的日志
mysql> show master status;	#查看当前使用的日志
mysql> show binlog events in 'bin-log.000019'; #查看日志事件

# mysqlbinlog --start-position=123 --stop-position=336 bin_log.000023 > aa.sql
查看事件起始点到终止点的内容

利用二进制文件恢复数据

创建binlog的数据库并进入
mysql> create database binlog charset utf8mb4;
mysql> use binlog;

创建t1表，插入六行数据并提交
mysql> create table t1(id int) engine=innodb charset=utf8mb4;
mysql> insert into t1 values(1),(2),(3);
mysql> insert into t1 values(11),(12),(13);
mysql> commit;

更新数据
mysql> update t1 set id=10 where id>10;

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|   10 |
|   10 |
+------+
6 rows in set (0.01 sec)

删除数据库
mysql> drop database binlog;

查看当前使用日志
mysql> show master status;

查看日志里的事件，找到要恢复数据的起点值和终点值
mysql> show binlog events in 'bin-log.000020';
如起点pos对应的位置数（219）
| bin-log.000020 | 219 | Query | 2 | 335 | create database binlog charset utf8mb4|
终点pos对应的位置数（1413）
| bin-log.000020 | 1413 | Query |2 | 1511 | drop database binlog     
------------------------------------------------------------------
[root@mysql ~]# cd /var/log/mysql
[root@mysql mysql]# ls
 bin-log.000020 可以看到用于恢复数据的日志文件
 
 截取日志
[root@mysql mysql]# mysqlbinlog --start-position=219 --stop-position=1413 bin-log.000020 > bb.sql
[root@mysql mysql]# ls
bb.sql  bin-log.000020   
------------------------------------------------------------------
恢复日志
mysql> set sql_log_bin=0;	将记录日志文件功能临时关闭

mysql> source /var/log/mysql/bb.sql;	进行数据恢复

mysql> set sql_log_bin=1;	将记录日志文件功能开启

mysql> show databases;	可以看到数据库已恢复，并且表数据也还原。

Gtid二进制日志数据

• 全局唯一的事务编号

• 幂等性。

  GtID包括两部分：
  Server_uuid
  Tx_id

默认情况关闭，需要借助配置文件开启

vim /etc/my.cnf 添加一下内容
gtid_mode=on		开启gtid
enforce_gtid_consistency=true	强制GTID一致性
log_slave_updates=1		主从复制中从库记录 binlog，并统一GTID信息

重启
systemctl restart mysqld

查看 gtid信息
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

mysql> show master status;
+-------+----------+-----------+-------------+---------------+
| File| Position| Binlog_Do_DB |Binlog_Ignore_DB |Executed_Gtid_Set |
+------+--------+-------------+---------------+---------------+
| bin-log.000022 | 154 |      |               |                |
+------+---------+-----------+----------+------------+
1 row in set (0.00 sec)

mysql> create database test3;
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------+-------+-----------+---------------+------------------------+
| File | Position |Binlog_Do_DB |Binlog_Ignore_DB |Executed_Gtid_Set|
+-----+---------+-----------+----------------+----------------------+
| bin-log.000022| 316 | | | cd417022-08e8-11ec-94b2-000c29be1221:1 |
+----------------+----------+--------------+------------------+-----+
1 row in set (0.00 sec)

-----------------------------------------------------
cd417022-08e8-11ec-94b2-000c29be1221		server的UID
:1 	事务id编号，每产生一个操作，事务编号+1

gtid实例

mysql> create database gtid charset utf8mb4;
mysql> use gtid;

mysql> create table t1(id int) engine=innodb charset=utf8mb4;
mysql> insert into t1 values(1),(2),(3);
mysql> insert into t1 values(11),(12),(13);
mysql> commit;

mysql> show master status;

mysql> show binlog events in 'bin-log.000022';
看到从创建test3起始gtid为2（server的UID后面【最后的数字】）
+----------------+-------+------+----+-----+------------------------+
| bin-log.000022 |  219 | Query | 2 |  316 | create database test3  |
| bin-log.000022 |  316 | Gtid  | 2 |  381 | SET @@SESSION.GTID_NEXT= 'cd417022-08e8-11ec-94b2-000c29be1221:2' |
+----------------+------+-------+---+-----+------------------------+
从删除test3结束gtid为6
+----------------+------+-------+---+-----+------------------------+
| bin-log.000022 | 1209 | Gtid  |  2 | 1274 |SET @@SESSION.GTID_NEXT= 'cd417022-08e8-11ec-94b2-000c29be1221:6' |
| bin-log.000022 | 1274 | Query  | 2 | 1366 | drop database gtid  
+----------------+------+-------+---+-----+------------------------+
基于 gtid 截取日志
--include-gtids= 包含
--exclude-gtids= 排除
--skip-gtids= 跳过
截取1-3号事务
因为事务id为6是删除操作，应在还原到上一个编号
[root@mysql mysql]# mysqlbinlog --skip-gtids --include-gtids='cd417022-08e8-11ec-94b2-000c29be1221:2-5' bin-log.000022 > gtid.sql

mysql> set sql_log_bin=0;
mysql> source /var/log/mysql/gtid.sql
mysql> set sql_log_bin=1;

mysql> show databases;	可以看到数据库已恢复，并且表数据也还原。

二进制日志其他操作

自动清理日志
临时设置清理周期
mysql> set global expire_logs_days=8;

永久生效
[root@mysql mysql]# vim /etc/my.cnf
[mysqld] 
expire_logs_days=15
[root@mysql mysql]# systemctl restart mysqld

查看清理周期
mysql> show variables like '%expire_logs_days%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 15    |
+------------------+-------+
1 row in set (0.01 sec)

慢日志

• 记录运行较慢的语句记录slowlog中。
• 功能是辅助优化的工具日志（数据库调优）。
• 应激性的慢可以通过show processlist进行监控一段时间的慢可以进行slow记录、统计

#默认未开启，开启会影响性能，mysql重启会失效
mysql> show variables like '%slow_query%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)

查看阈值（执行操作超过10s记录在慢日志里）
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 10.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

修改阈值（临时生效）
mysql> SET GLOBAL long_query_time=3;

修改阈值（永久生效，通过配置文件修改）
vim /etc/my.cnf
slow_query_log=1		开启慢日志
slow_query_log_file=/var/log/mysql/slow.log		慢日志位置
long_query_time=1		执行超过1s则记录慢日志
log_queries_not_using_indexes=1		

systemctl restart mysqld

mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

测试慢日志

mysql> select sleep(4);
mysql> select sleep(6);
记录有两个慢日志
mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 2     |
+---------------+-------+
1 row in set (0.00 sec)

使用慢日志分析工具mysqldumpslow
[root@mysql mysql]# mysqldumpslow -s r -t 10 slow.log

Reading mysql slow query log from slow.log
Count: 2  Time=5.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost
  select sleep(N)

Died at /usr/bin/mysqldumpslow line 167, <> chunk 2.
--------------------------------------------------------------
s：表示按何种方式排序 
c：访问次数 
l：锁定时间 
r：返回记录 
t：查询时间 
al：平均锁定时间 
ar：平均返回记录数 
at：平均查询时间 
t：返回前面多少条的数据 
g：后边搭配一个正则匹配模式，大小写不敏感

下一章笔记 mysql逻辑备份与物理备份

作者：月光染衣袂
转发请加上该地址 ： https://blog.csdn.net/weixin_46860149/article/details/120043487
如果笔记对您有用，请帮忙点个赞！