MySQL 回顾

¶创建数据库

CREATE DATABASE 数据库名即可创建相应的数据库，命令不限定大小写，注意在创建含有特殊字符的数据库名的数据库的时候，需要使用反标记字符 (`) 用于引用标识符，数据表名同理。

1	create database `my.contacts`

可以在连接数据库的同时就指定要连接的数据库，如 mysql -uroot -p 数据库名，这样一连接上就进入了该数据库。

进入mysql后，使用 use 数据库名，进入和切换相应的数据库，使用 SELECT DATABASE(); 可以查看当前进入的数据库是哪个，注意需要添加分号。

数据库被创建为数据目录中的一个目录，使用 SHOW VARIABLES LIKE 'datadir'; ，可以查看到该目录所在的文件夹位置。

¶创建表

在表定义的时候，应该指定列的名称、数据类型（整型、浮点型、字符串等）和默认值（如果有的话）。

mysql支持的类型:

数字：tinyint、smallint、mediumint、int、bigint 和 big
浮点型： decimal、float和double
字符串： char、varchar、binary、varbinary、blob、text、enum 和 set
spatial数据类型
json数据类型

示例：

CREATE TABLE IF NOT EXISTS `company`.`customers` (
    `id` int unsigned AUTO_INCREMENT PRIMARY KEY,
    `first_name` varchar(20),
    `last_name` varchar(20),
    `country` varchar(20)    
) ENGINE=InnoDB;

解释：
句点符号：表可以使用 database.table 的形式来引用，如果使用use进入到了该database下，则可以直接使用table。
IF NOT EXISTS: 若在创建表的时候已经存在了一个同名的数据表，则指定了IF NOT EXISTS的情况下，只会抛出一个警告，否则若不指定，则报异常。
ENGINE：用于指定创建表的数据引擎。InnoDB是唯一的事务引擎，也是默认引擎。
注意：最后一个value后不能有逗号

¶可以在同一的数据库中创建多个表

CREATE TABLE payments (
    `customer_name` varchar(20) PRIMARY KEY,
    `payment` float
);

¶列出所有的表

使用 SHOW TABLES

¶查看表结构

SHOW CREATE TABLE 表名\G

DESC 表名

创建表后，MySQL会在数据库目录内创建相应的.ibd文件，使用SHOW VARIABLES LIKE 'datadir'，可以查看到该目录所在的文件夹位置。

克隆表结构（创建一个和已有的表具有相同结构的表）：CREATE TABLE 新表名 LIKE 已存在的表的表名

¶操作数据库

¶插入

INSERT IGNORE INTO `company`.`customers` (first_name, last_name, country) 
VALUES
('Mike', 'Christensen', 'USA'),
('Andy', 'Hollands', 'Australia'),
('Ravi', 'Vedantam', 'India'),
('Rajiv', 'Perera', 'Sri Lanka');

解释：
IGNORE：如果该行已经存在，此时加入了IGNORE,新添加的数据将被忽略，且INSERT操作被显示为成功，同时生成一个警告和重复数据的数目，否则报错。
若插入的是完整的数据内容，即不使用自增的值和默认值的话，可以不写(first_name, last_name, country)

¶更新

1	UPDATE customers SET first_name='Rajiv', country='UK' WHERE id=4;

解释：
WHERE:这是用于过滤的字句。用于筛选出特定的数据。若不添加，则默认更改所有的数据。
WHERE字句是强制性的。建议在事务中修改数据，以便在发现任何错误的时候轻松的回滚这些更改

¶删除

1	DELETE FROM customers WHERE id=4 AND first_name='Rajiv';

解释：
WHERE字句是强制性的，若没有WHERE，则会删除表中所有的数据，建议在事务中修改数据，以便在发现任何错误的时候轻松的回滚这些更改

¶REPLACE、INSERT和ON DUPLICATE KEY UPDATE

在很多情况下，我们需要处理重复项。行的唯一性有主键标识。如果行已经存在，则REPLACE会简单地删除行并重新插入新行；如果该行不存在，则此时的REPLACE等同于INSERT。
使用REPLACE在插入重复项（主键是不能重复的）的时候，会显示更新了两行，这是因为，先删除了原先的记录，又添加了新的记录，所以显示改变了两行。
如果想在行已经存在的情况下处理重复项，则需要使用ON DUPLICATE KEY UPDATE。如果指定了ON DUPLICATE KEY UPDATE选项，并且INSERT语句在PRIMARY KEY中引发了重复值，则MySQL会用新值更新已有行。
使用场景（类似于update操作，但是是以插入数据的形式进行的）

1
2

INSERT INTO payments VALUES ('Mike Christensen', 200) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment);
INSERT INTO payments VALUES ('Mike Christensen', 300) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment);

解释：
第一条语句直接在表中创建了一条新的数据，执行第二条语句时会发现customer_name这一项的值重复了，此时ON DUPLICATE KEY UPDATE就会生效，会覆盖原有的值，相当于对数据进行了更新，此时值应该是500。同时，我们发现显示修改了两条数据，这是因为，先删除了原先的记录，又添加了新的记录，所以显示改变了两行。
VALUES(payment)代表的是INSERT语句给的值，而payment是表中的列的值。

¶TRUNCATE TABLE

* 删除整个表需要很长时间，因为MySQL需要逐行执行操作。删除表中的所有行（保留表结构）的最快方法是使用TRUNCATING TABLE语句。
`TRUNCATE TABLE 表名`，是DDL操作，一旦数据被清楚，不能回滚。

¶查询

¶操作符

mysql支持许多操作符来筛选结果。

equality:
IN:检查一个值是否再一组值内：找出姓氏为Christ和Lamba的所有员工数

1	select COUNT(*) from employees where last_name in ('christ','Lamba');

BETWEEN AND: 找出1986年12月入职的员工数

1	select COUNT(*) FROM employees WHERE hire_date BETWEEN '1986-12-01' AND '1976-12-31';

NOT：否定结果，找出不是在1986年12月入职的员工数

1	select COUNT(*) FROM employees WHERE hire_date NOT BETWEEN '1986-12-01' AND '1976-12-31';

¶简单模式匹配

可以使用LIKE运算符来是实现简单的模式匹配。使用下划线 _ 来精准匹配一个字符，使用 % 来匹配任意数量的字符。

找出名字以Christ开头的所有员工总数

1	select count(*) from employees where first_name like 'christ%';

找出名字以Christ开头并以ed结尾的所有员工总数

1	select count(*) from employees where first_name like 'christ%ed';

找出名字中包含str的所有员工的数

1	select count(*) from employees where first_name like '%str%'

找出名字中以ser的所有员工的数: %er
以任意两个字符开头、后面跟随ka、再后面跟随任意数字符的所有员工数：like '__ka%'

¶正则表达式

可以利用 RLIKE 或 REGEXP 运算符在 WHERE 子句中使用正则表达式。（RLIKE和REGEXP使用方法一样，即可以相互替代）

查找出姓氏以ba结尾的所有员工数

1	select count(*) from employees where last_name rlike 'ba$';

查找姓氏不包含元音（a、e、i、o和u）的所有员工的人数

1	select count(*) from employees where last_name not regexp '[aeiou]';

¶限定结果

使用limit来进行设置

查询hire_data在1986年之前的任何10名员工的姓名

1	select first_name, last_name from employees where hire_data < '1996-01-01' limit 10;

¶使用表别名

使用AS来设置别名

对COUNT(*)使用AS来设置别名

1	select count(*) AS count from employees;

¶对结果进行排序

对结果进行排序（order by），可以根据列或者别名对结果进行排序，也可以用DESC指定按降序或用ASC指定升序来排序。默认情况下，按照升序排序。可以将 limit 子句与 ordery by 结合使用以限定结果集。

查找薪水最高的前5名员工的编号

1	select emp_no, salary from salaries order by salary limit 6;

若想降序，则使用

1	select emp_no, salary from salaries order by salary desc limit 6;

也可以通过指定要进行排序的列所有表中的位置进行指定，如本次排序的salary列在表中的位置为2

1	select emp_no, salary from salaries order by 2 limit 6;

¶对结果进行分组（聚合函数）

可以使用 group by 子句对结果进行分组，然后使用 AGGREGATE（聚合函数），如 COUNT、MAX、MIN 和 AVERAGE。还可以在 group by 子句的列上使用函数。

分别找出男性和女性员工的人数

1	select gender, count(gender) as count from employees group by gender;

找出员工名字（first_name）中最常见的10个名字

1	select first_name, count(*) as count from employees group by first_name order by count desc limit 10;

查找每年给予员工的薪水总额

1	select year(from_date), sum(salary) as sum from salaries group by year(from_date) order by sum desc;

分析，这句查询实际上是按照from_date字段进行查询的，只是使用了year()函数，将每个全日期显示为年份。

查找平均工资最高的10名员工

1	select emp_no, avg(salary) as avg from salaries group by emp_no order by avg desc limit 10;

可以使用 DISTINCT 过滤掉相同的数据，如过滤掉不同的书籍

1	select distinct title from titles;

相同效果

1	select title from titles group by title;

使用 HAVING 过滤，可以通过添加 HAVING 子句来过滤 GROUP BY 子句的结果，如找到平均工资超过140000美元的员工

1	select emp_no, avg(salary) as avg from salaries group by emp_no having avg >140000 order by avg desc;

可以发现 having 和 where 的区别在于：where是对select的范围进行过滤，过滤的是查询的范围，而 having 是对查询到的结果进行过滤，过滤的是查询到的结果。

¶权限管理

¶创建用户

事实上不应该使用 root 用户连接到 mysql 并执行语句，除非是 localhost 的管理任务。我们应该创建用户，限制访问，限制资源使用等等。创建用户需要拥有 CREATE USER 权限。

1
2

# 使用 root 连接到 mysql 并执行 create user 命令来创建新用户
CREATE USER IF NOT EXISTS 'company_read_only'@'localhost' IDENTIFIED WITH mysql_native_password BY 'company_pass' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_FER_HOUR 100;

参数说明：

用户名：company_read_only
仅从localhost访问，可以限制对IP范围的访问，例如10.148.%.%。通过给出%，用户可以从任何主机访问。
密码：company_pass
使用mysql_native_password（默认）身份验证，还可以指定任何可选的身份验证，例如sha256_password、LDAP 或 Kerberos。
用户可以在一小时内执行的最大查询数为500。
用户可以在一小时内执行的最大更新次数为100次。

¶密码策略修改

由于mysql8密码策略的问题，导致简单的密码是不能通过验证的，下面我们通过修改密码验证的策略，使其能够使用简单的密码进行登录验证。

查看密码的验证策略：show variables like 'validate_password%'进行查看。
首先设置密码的验证强度等级，设置 validate_password_policy 的全局参数为LOW即可，设置语句为 set global validate_password_policy=LOW; 进行设置。
使用类似的方法还可以设置密码的长度，但是默认最短不能低于4，所以一旦低于4，也是会默认为4的。
设置好后的效果，修改了密码验证的等级为 LOW，修改了要求的特殊字符数为0。

关于 mysql 密码策略相关参数：

validate_password.length 固定密码的总长度；
validate_password.dictionary_file 指定密码验证的文件路径；
validate_password.mixed_case_count 整个密码中至少要包含大/小写字母的总个数；
validate_password.number_count 整个密码中至少要包含阿拉伯数字的个数；
validate_password.policy 指定密码的强度验证等级，默认为 MEDIUM；
关于 validate_password_policy 的取值：
- LOW：只验证长度；
- MEDIUM：验证长度、数字、大小写、特殊字符；
- STRONG：验证长度、数字、大小写、特殊字符、字典文件；
validate_password.special_char_count 整个密码中至少要包含特殊字符的个数；

¶授予和撤销用户的访问权限

可以限制用户的访问特定数据库或表，或限制特定操作，如 SELECT、 INSERT 和 UPDATE。需要拥有 GRANT 权限，才能为其他用户授予权限。
如何做：可以通过root用户来授予权限，同时还可以创建管理员账户来管理用户。

将 READ ONLY（只读权限SELECT）权限授予刚刚创建的company_read_only用户

1	grant select on company.* to 'company_read_only'@'localhost';

该语句表示将对数据库 company 及其所有的表对本地访问的名为 company_read_only的用户授予查询的权限

将INSERT权限授予新的company_insert_only用户

1	grent insert on company.* to 'company_insert_only'@'localhost' indentified by 'xxx';

该语句在授予权限的时候同时创建新的用户。普通的创建用户的语句格式为

1	create user "username"@"host" identified by "password";

将WRITE（即写权限，INSERT、UPDATE和DELETE）权限授予新建的 company_write 用户

1	grant insert, delete, update on company.* to 'company_write'@'%' identified with mysql_native_password by 'xxxx';

限制查询指定的表。将 employees_read_only 用户限制为仅能查询employees表

1	grant select on employees.employees to 'employees_read_only'@'%' identified with mysql_native_password by 'xxxx';

可以进一步将访问权限设置为仅能查询指定列。

1	grant select(first_name, last_name) on employees.employees to 'employees_read_only'@'%' identified with mysql_native_password by 'xxx';

注意：在以上的语句在授予权限的同时都是创建新的用户，但是这是不被推荐的，在8.0.18版本已经不能这样使用了，推荐用法是先创建用户，再赋予权限。

扩展授权。可以通过执行新授权来扩展授权。将权限扩展到employees_col_ro用户，以访问薪资（salaries）表中的薪水

1	grant select(salary) on employees.salaries to 'employees_ro'@'%';

创建SUPER用户。需要一个管理员账户来管理该服务器。ALL表示出了GRANT权限之外的所有权限

1	create user 'dbadmin'@'%' identified with mysql_native_password by 'DB@adln';

赋予权限

1	grant all on . to 'dbadmin'@'%';

授予 GRENT 权限，用户拥有 GRANT OPTION 权限才能授予其他用户权限。可以将 GRENT 特权扩展到 dbadmin 超级用户

1	grant grant option on . to 'dbadmin'@'%';

注意权限的全称为grant option。

总结：授予权限使用的关键字是grant，必须指明权限能访问的数据库和表on 数据库.表，必须指明授权的对象to 用户。

¶检查权限

检查所有用户的授权。检查employee_col_ro用户的授权

1	show grants for 'employee_col_ro'@'%';

¶撤销权限

撤销’company_write’@‘%’用户的DETELE访问权限

1	revoke delete on company.* from 'company_write'@'%';

撤销employee_ro用户对薪水列的访问权限

1	revoke select(salary) on employees.salaries from 'employees_ro'@'%';

¶修改mysql.user表

所有用户的信息以及权限都存储在mysql.user表中，如果你有权访问mysql.user表，则可以直接通过修改mysql.user表来创建用户并授予权限。
如果你使用GRANT、REVOKE、SET PASSWORD和RENAME USER等账户管理语句间接修改授权表，则服务器会通知这些更改，并立即再次将授权表加载到内存中。
如果使用的是INSERT、UPDATE或者DELETE等语句直接修改授权表，则更改不会影响权限检查，除非重新启动服务器或指示其重新加载表。如果直接更改授权表，当忘记了重新加载表，那么在重新启动服务器之前，这些更改无效。
可以使用查询语句进行查询mysql表，可看到某个用户的具体权限的信息

1	select * from mysql.user where user='dbadmin'/G;

使用update即可对权限进行更新

1	update mysql.user set host='localhost' where user='dbadmin';

因为是对mysql表进行操作的，所以想要立即生效，必须使用 flush privileges; 使其立即生效。

¶设置用户密码的有效期

可以设置一段时间作为用户密码的有效期，当有效期过后用户需要修改密码