MySQL-Notes

发表于 更新于 分类于 Disqus：

尚硅谷MySQL学习笔记

3. SQL分类

  • DDL（Data Definition Languages)：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。

    • 主要的语句关键字包括CREATE、DROP、ALTER等。

  • DML（Data Manipulation Language）：数据操作语言，用于添加、删除、更新和查询数据库记录，并检查数据完整性。

    • 主要的语句关键字包括INSERT、DELETE、UPDATE、SELECT等。
    • SELECT是SQL语言的基础，最为重要。

  • DCL（Data Control Language）：数据控制语言，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。

    • 主要的语句关键字包括GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。

第2章：数据处理之查询

2.1 基本的SELECT语句

2.1.2 列的别名

  • 重命名一个列
  • 便于计算
  • 紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。

使用双引号””

使用别名

SELECT last_name AS name, commission_pct comm
FROM   employees;

SELECT last_name "Name", salary*12 "Annual Salary"
FROM   employees;

2.1.3 去除重复行

默认情况下，查询会返回全部行，包括重复行。

SELECT department_id
FROM   employees;

在SELECT语句中使用关键字DISTINCT去除重复行。

SELECT DISTINCT department_id
FROM   employees;

2.1.4 空值参与运算

  • 所有运算符或列值遇到null值，运算的结果都为null
SELECT employee_id,salary,commission_pct,
12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;

2.1.5 显示表结构

使用DESCRIBE 或 DESC 命令，表示表结构。

DESCRIBE employees;

2.2 过滤数据

2.2.1 SELECT … FROM … WHERE

背景：

  • 使用WHERE 子句，将不满足条件的行过滤掉
  • WHERE子句紧随 FROM子句
SELECT employee_id, last_name, job_id, department_id
FROM   employees
WHERE  department_id = 90 ;

在查询中使用字符串、日期型变量时，需要使用一对’’表示

如何判断列的类型

DESC employees;

使用一对’’表示的字符串本身，在SQL标准中是区分大小写的，在MySQL中不区分

2.2.2 比较运算符

① 基本操作符
操作符 含义
= 等于(不是==)
> 大于
>= 大于、等于
< 小于
<= 小于、等于
<> 或 != 不等于

说明：赋值符号使用 :=

SELECT last_name, salary
FROM   employees
WHERE  salary <= 3000;

② 其它比较运算符
操作符 含义
BETWEEN … AND 在两个值之间(包含边界)，闭区间
IN(set) 等于值列表中的一个
LIKE 模糊查询
IS NULL 空值

1）BETWEEN … AND

使用 BETWEEN 运算来显示在一个区间内的值

SELECT last_name, salary
FROM   employees
WHERE  salary BETWEEN 2500 AND 3500;

2）IN

使用 IN运算显示列表中的值。

SELECT employee_id, last_name, salary, manager_id
FROM   employees
WHERE  manager_id IN (100, 101, 201);

3）LIKE

  • 使用 LIKE 运算选择类似的值

  • 选择条件可以包含字符或数字:

    • % **代表零个或多个字符(任意个字符)**。
    • _ 代表一个字符。
SELECT	first_name
FROM 	employees
WHERE	first_name LIKE 'S%';
  • ‘%’和‘_’可以同时使用。
SELECT last_name
FROM   employees
WHERE  last_name LIKE '_o%';

ESCAPE

  • 回避特殊符号的：使用转义符。例如：将[%]转为[$%]、[]转为[$]，然后再加上[ESCAPE‘$’]即可。
SELECT job_id
FROM   jobs
WHERE  job_id LIKE ‘IT\_%‘;

如果使用\表示转义，要省略ESCAPE。如果不是\，则要加上ESCAPE。

SELECT job_id
FROM   jobs
WHERE  job_id LIKE ‘IT$_%‘ escape ‘$‘;

4）NULL

使用 IS (NOT) NULL 判断空值。

SELECT last_name, manager_id
FROM   employees
WHERE  manager_id IS NULL;
-- WHERE  manager_id IS NOT NOT;

2.2.3 逻辑运算符

操作符 含义
&& （或AND） 逻辑且
||（或OR） 逻辑或
NOT 逻辑否
XOR 逻辑异或

1）&& （或AND）

AND要求并的关系为真。

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >=10000
AND    job_id LIKE '%MAN%';

2）||（或OR）

OR要求或关系为真。

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >= 10000
OR     job_id LIKE '%MAN%';

3）NOT

SELECT last_name, job_id
FROM   employees
WHERE  job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

4）XOR

select last_name,department_id,salary 
from employees
where department_id in (10,20) XOR salary > 8000;

2.2.4 算术运算符

运算符 说明
+ 加法
- 减法
* 乘法
/ （或div） 除法
%（或mod） 取模 
SELECT employee_id,salary,department_id
FROM employees
WHERE department_id MOD 2 = 0;

2.3 排序数据和分页

2.3.2 分页

  • MySQL中使用limit实现分页

  • 背景：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？

  • 分页原理

    所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。

--前10条记录：
SELECT * FROM table LIMIT 0,10;

--第11至20条记录：
SELECT * FROM table LIMIT 10,10;

--第21至30条记录： 
SELECT * FROM table LIMIT 20,10;
  • 公式：（当前页数-1）*每页条数，每页条数
SELECT * FROM table 
LIMIT(PageNo - 1)*PageSize,PageSize;
  • 注意：limit子句必须放在整个查询语句的最后！

练习

  • 别名使用””

5.选择工资不在5000到12000的员工的姓名和工资

SELECT first_name, last_name, salary
FROM employees
WHERE salary < 5000 OR salary > 12000
SELECT first_name, last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000

6.选择在20或50号部门工作的员工姓名和部门号

SELECT first_name, department_id
FROM employees
WHERE department_id = 20 OR department_id = 50
# WHERE department_id IN(20, 50)
SELECT first_name, department_id
FROM employees
WHERE department_id IN(20, 50)

10.选择姓名中有字母a和e的员工姓名

SELECT first_name
FROM employees
WHERE first_name LIKE '%a%' OR first_name LIKE '%e%'
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

2.4 多表查询

2.4.1 笛卡尔积错误

举例：

从多个表中获取数据：

select last_name, department_name
from employees, departments;

演示笛卡尔积的错误情况：

select count(employee_id) from employees;
输出107行
select count(department_id)from departments;
输出27行
select 107*27 from dual;

  • 笛卡尔积会在下面条件下产生：

    • 省略连接条件
    • 连接条件无效
    • 所有表中的所有行互相连接

  • 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

2.4.2 分类1：等值连接 vs 非等值连接

① 等值连接
SELECT employees.employee_id, employees.last_name, 
       employees.department_id, departments.department_id,
       departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

区分重复的列名

  • 使用表名前缀在多个表中区分相同的列。

  • 在不同表中具有相同列名的列可以用表的别名加以区分。

表的别名

  • 使用别名可以简化查询。

  • 使用表名前缀可以提高执行效率。

SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;

连接多个表

连接 n个表,至少需要n-1个连接条件。
例如：连接三个表，至少需要两个连接条件。

练习：查询出公司员工的 last_name,department_name, city

② 非等值连接
SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;

MySQL安装及其他操作

MySQL的一些杂项配置

sql_mode

sql_mode定义了对Mysql中sql语句语法的校验规则！

sql_mode是个很容易被忽视的变量，如果设置为空值（非严格模式），在这种情况下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。

sql_mode常用的值

ONLY_FULL_GROUP_BY 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO 该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了
STRICT_TRANS_TABLES 在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
NO_ZERO_IN_DATE 在严格模式下，不允许日期和月份为零
NO_ZERO_DATE 设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告
ERROR_FOR_DIVISION_BY_ZERO 在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER 禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION 如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
PIPES_AS_CONCAT 将”||”视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
ANSI_QUOTES 启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符
ORACLE 设置等同于PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER

查看当前的sql_mode

select @@sql_mode;

临时修改 sql_mode

set @@sql_mode='';

永久修改，需要在配置文件my.cnf中修改

[root@hadoop102 ~]$ vim /etc/my.cnf
#添加下列配置，然后重启mysql即可
[mysqld]
sql_mode=''

sql_mode的影响案例：group by 查询语法错误！

CREATE TABLE mytbl2 (id INT,NAME VARCHAR(200),age INT,dept INT);
INSERT INTO mytbl2 VALUES(1,'zhang3',33,101);
INSERT INTO mytbl2 VALUES(2,'li4',34,101);
INSERT INTO mytbl2 VALUES(3,'wang5',34,102);
INSERT INTO mytbl2 VALUES(4,'zhao6',34,102);
INSERT INTO mytbl2 VALUES(5,'tian7',36,102);

# 查询每个dept中年龄最大的人：
# 错误结果：
SELECT NAME,dept,MAX(age) FROM mytbl2 GROUP BY dept;
# 分组：group by以后，select后面只能跟 组标识 及 聚合函数(组函数)
# 正确结果：
SELECT id,name,ab.dept,ab.maxage FROM mytbl2 m INNER JOIN(SELECT dept,MAX(age)maxage FROM mytbl2 GROUP BY dept)ab ON ab.dept=m.dept AND m.age=ab.maxage;

MySQL的用户管理

MySQL的用户管理在 mysql库中的user表中

需要了解的列: Host,User, authentication_string等， 可通过 desc user 查看user表结构

相关命令

命令 描述 备注
create user zhang3 identified by ‘123123’; 创建名称为zhang3的用户，密码设为123123；
select host, user, password, select_priv, insert_priv, drop_priv from mysql.user; 查看用户和权限的相关信息
set password =password(‘123456’) 修改当前用户的密码
update mysql.user set authentication_string=password(‘123456’) where user=’li4’; 修改其他用户的密码。
mysql 5.7 通过authentication_string表示密码列		 所有通过user表的修改，必须用flush privileges;命令才能生效
update mysql.user set user=’li4’ where user=’wang5’; 修改用户名 所有通过user表的修改，必须用flush privileges;命令才能生效
drop user li4 删除用户 不要通过delete from user u where user=’li4’ 进行删除，系统会有残留信息保留。

示例说明

use mysql;

show tables;

desc user;

// xxx_priv 代表与权限相关

authentication_string，是用户密码

SELECT host, user, authentication_string
FROM user;

host：表示连接类型

  • %：表示所有远程通过TCP方式的连接
  • IP地址：如192.168.1.2/10.85.10.2通过指定ip地址进行的TCP方式的连接
  • 机器名：通过指定网络中的机器名进行的TCP方式的连接
  • ::1：IPV6的本地ip地址等同于IPV4的127.0.0.1
  • localhost：本地方式通过命令行方式的连接，比如mysql -u root -p 123456方式的连接

user：表示用户名

  • 同一用户通过不同方式连接的权限是不一样的

password：密码

  • 所有密码串通过password生成的密文字符串。
  • 加密算法为MYSQLSHA1

select_priv, insert_priv等：

  • 该用户所拥有的权限

MySQL的权限管理

授予权限

命令 描述
grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’ 该权限如果发现没有该用户，则会直接新建一个用户。
示例：grant select,insert,delete,drop on atguigudb.* to li4@localhost ;
给li4用户用本地命令行方式下，授予atguigudb这个库下的所有表的插删改查的权限。
grant all privileges on *.* to joe@’%’ identified by ‘123’; 授予通过网络方式登录的的joe用户 ，对所有库所有表的全部权限，密码设为123.

收回权限

命令 描述
show grants 查看当前用户权限
revoke [权限1,权限2,…权限n] on 库名.表名 from 用户名@用户地址 ; 收回权限命令
REVOKE ALL PRIVILEGES ON mysql.* FROM joe@localhost; 收回全库全表的所有权限
REVOKE select,insert,update,delete ON mysql.* FROM joe@localhost; 收回mysql库下的所有表的插删改查权限

提示：权限收回后，必须用户重新登录后，才能生效。

刷新权限

mysql> flush privilage;

查看SQL的执行周期

查看profile是否开启

mysql> show variables like '%profiling%';

开启profiling

mysql> set profiling=1;

使用profile，可以查看最近几次查询

mysql> show profiles;

根据Query_ID，查看SQL的具体执行步骤

mysql> show profile cpu, block io for query 2;

大致查询流程

mysql客户端通过协议与mysql服务器建连接，发送查询语句，

先检查查询缓存，如果命中，直接返回结果，否则进行语句解析,

也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。

如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。

语法解析器和预处理：

首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；

预处理器则根据一些mysql规则进一步检查解析数是否合法。

查询优化器当解析树被认为是合法的了，并且由优化器将其转化成Explian。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的Explian。

然后，mysql默认使用的BTREE索引，并且一个大致方向是:无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。

SQL的书写顺序

SELECT- DISTINCT- FROM- JOIN ON- WHERE- GROUP BY- HIVING- ORDER BY- LIMIT

真正执行的顺序

随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序：

FROM- ON- JOIN- WHERE- GROUP BY- HAVING- SELECT- DISTINCT- ORDER BY- LIMIT

查询缓存

查看查询缓存相关的设置

查看缓存相关的设置

mysql> show variables like "%query_cache%";

相关参数解释

  • query_cache_limit：超过此大小的查询将不再缓存
  • query_cache_min_res_unit：缓存块的最小值
  • query_cache_size：缓存大小值
  • query_cache_type：缓存类型，决定缓存什么样的查询
    • 0，表示关闭查询数据库
    • 1，表示开启查询缓存ON
    • 2，表示SQL语句中有SQL_CACHE关键词时才缓存
      • select SQL_CACHE name from t_user where id = 1001;
  • query_cache_wlock_invalidate：表示当有其它客户端正在对MyISAM表进行写操作时，读请求是要等write lock释放资源后再查询还是允许直接从query cache中读取结果

开启MySQL的查询缓存

  • 在MySQL的配置文件中 /etc/my.cnf中[mysqld] 节点下添加如下配置：

    [mysqld]
    query_cache_type = 1

  • 重启MySQL服务

    systemctl restart mysqld.service

使用查询缓存

  • 开启profiling

    mysql> set profiling =1 ;

  • 在MySQL中执行两条相同的SQL

    mysql> select * from mytbl2;
    mysql> select * from mytbl2;

  • 查看最近执行的SQL

    mysql> show profiles;

  • 查看两条相同SQL的执行周期

    • 查看第一次执行的SQL

    • 查看第二次执行的SQL

    • 可以看出第二次执行的SQL结果是从缓存中查询

查询不使用缓存

如果在开启了查询缓存的情况, 某条SQL执行时不想使用缓存，可在SQL中显示执行SQL_NO_CACHE

mysql> select SQL_NO_CACHE  * from mytbl2;

SQL预热

常见Join查询图

image-20210308141925417

MySQL的索引结构

索引

索引的优劣势

  • 优点

    • 提高数据检索的效率，降低数据库的IO成本。
    • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

  • 劣势

    • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

B-Tree与B+Tree 的区别

  • B-树的关键字和记录是放在一起的， B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
  • 在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。

为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

  • B+树的磁盘读写代价更低
    • B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
  • B+树的查询效率更加稳定
    • 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

聚簇索引和非聚簇索引

聚簇索引的好处

按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

聚簇索引的限制

对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。

由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。

为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。

索引分类

单值索引INDEX