MYSQL8.0

概念

关系型数据库

建立在关系模型基础上,由多张相互关联的二维表组成的数据库

关联

特点
1. 使用表存储数据,格式统一,便于维护
2. 使用SQL 语言操作,标准统一,使用方便

数据模型
- 数据库
  - 表

MYSQL8.0 安装

官网下载

https://dev.mysql.com/downloads/mysql/

点击如下选框位置(①)	离线安装(②)

不登陆直接进行下载(③)

安装

选择自定义

自定义(①)

添加服务

服务(②)

安装位置

修改安装位置

配置环境变量

添加`bin` 目录到`path`

创建一个测试数据库,查看MYSQL 默认字符集

mysql> show create table aa;
-------------------------------------------------------+
| Table | Create Table                                                                                                                                      |
+-------+----------------------------------------------+
| aa    | CREATE TABLE `aa` (
  `id` int DEFAULT NULL,
  `name` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------+
1 row in set (0.00 sec)

my.inf 配置文件位置:D:\software\MYSQL8.0\dataDirectory(自定义的数据文件安装目录下)

MYSQL8.0 如果出现关于Navicat 可视化工具连接出现如下的报错的解决

1	# 在安装过程中,如果 MYSQL8.0 选择新版的验证方式,使用 Navicat 等可视化工具会出现 “Authentication plugin 'caching_sha2_password'”

解决方案

运行dos： windows + R => cmd

未配置环境变量时进入bin 目录,否之直接执行

1 2	# 连接 MYSQL mysql -u root -p password

进入后,执行如下

1 2	# 修改新密码 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

在MySQL 8.0 中，新创建的用户默认使用 'caching_sha2_password' 插件。因此，可能需要在创建用户时指定'mysql_native_password'插件。

在MySQL 服务器上启用旧的认证插件：如果您无法更改用户的认证插件，您可以在MySQL 服务器上启用旧的认证插件。要启用旧的认证插件，请在MySQL 服务器配置文件中添加以下行

1 2	[mysqld] default_authentication_plugin=mysql_native_password

然后，重新启动MySQL 服务器以使更改生效。

请注意，将认证插件更改为旧的'mysql_native_password'可能会降低安全性。因此，建议尽可能使用支持'caching_sha2_password'插件的客户端。

忘记登录密码的解决方案

通过任务管理器或者服务管理,关掉mysqld 服务进程

通过命令行+特殊参数开启

1	mysqld mysqld -- defaults-file="D:\software\MYSQL8.0\dataDirectory\my.ini" --skip-grant-tables

此时，mysqld 服务进程已经打开。并且不需要权限检查
mysql -uroot 无密码登陆服务器。另启动一个客户端进行

修改权限表

1
2
3

use mysql;
update user set authentication_string=password('新密 码') where user='root' and Host='localhost'; 
flush privileges;

过任务管理器，关掉mysqld 服务进程
再次通过服务管理，打开mysql 服务
即可用修改后的新密码登陆。

理解
- DB： database 数据库文件
- DBMS: 数据库管理系统

MYSQL5.7

字符集

查看

1
2
3

show variables like '%character%';
# 比较规则字符集
show variables like 'collation_%';

修改

[mysql]
default-character-set=utf8 # 默认字符集
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

通用语法

SQL 语句可以单行或多行书写,以分号结尾
SQL 语句可以使用空格/缩进来增强语句的可读性
MYSQL 数据库的SQL 语句不区分大小写,关键词建议使用大写
注释
- 单行注释:-- 注释内容或 # 注释内容(MYSQL特有)
- 多行注释:/* 注释内容 */

SQL 分类

DDL 数据定义语言:create、alter、drop、rename、truncate
DML 数据操作语言: insert、delete、update、select

DCL 数据控制语言: commit、rollback、savepoint(保存点)、grant、revoke

分类	全称	说明
`DDL`	`Data Definition Language`	数据定义语言,用来定义数据库对象(`数据库、表、字段`)
`DML`	`Date Manipulation Language`	数据操作语言,用来对数据库表中的数据进行增删改
`DQL`	`Data Query Language`	数据查询语言,用来查询数据库中表的记录
`DCL`	`Data Control Language`	数据控制语言,用来创建数据库用户、控制数据库的访问权限

SQL语言的规则与规范

基本规则

SQL 可以卸载一行或者多行。为了提高可读性、各子句分行写，必要时使用缩进
关键字不能被缩写也不能分行
关于标点符号
- 必须保证所有的( )、单引号，双引号成对的结束
- 必须使用英文状态下的半角输入方式
- 字符串型和日期类型的数据可以使用单引号表示
- 列的别名、尽量使用双引号,而且不建议省略as
SQL 大小写规范
- windows 环境下大小写不敏感
- Linux 环境下是大小写敏感的
  - 数据库名、表名、表的别名、变量名是严格区分大小写的
  - 关键字、函数名、列名(或字段名)、列的别名(字段的别名)是忽略大小写的
- 推荐采用统一的书写规范
  - 数据库名、表名、表别名、字段名、字段别名等都小写
  - SQL 关键字、函数名、绑定变量都大写

数据的导入

1 2	# 连接客户端后执行 source sql文件位置

数据源之间的关系

关系

关联关系

基本的 SELECT 语句

查询员工12 个月的工资总和.并起别名为ANNUAL SALARY

-- 理解一: 查询基本工资
SELECT
	salary * 12 AS "ANNUAL SALARY" 
FROM
	employees;
	
-- 理解二: 计算 12 个月的基本工资和奖金
SELECT
	employee_id,
	last_name,
	salary * 12 *(
	1+ifnull ( commission_pct, 0 )) AS "ANNUAL SALARY" 
FROM
	employees;

查询employees 表中去除重复的job_id 以后的数据

SELECT DISTINCT
	job_id 
FROM
	employees;

查询工资大于12000 的员工的姓名和工资

SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	salary > 12000;

查询员工工号为176 的员工的姓名和部门号

SELECT
	last_name,
	department_id 
FROM
	employees 
WHERE
	employee_id = '176';

显示表departments 的结构,并查询其中的全部数据

DESC departments;

SELECT
	* 
FROM
	departments;

运算符练习

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

SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	salary < 5000 OR salary > 12000;

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

SELECT
	last_name,
	department_id 
FROM
	employees 
WHERE
	department_id NOT IN ( 20, 50 );

选择公司在没有管理者的员工姓名以及job_id

SELECT
	last_name,
	job_id 
FROM
	employees 
WHERE
	manager_id IS NULL;

选择公司中有奖金的员工姓名、工资和奖金级别

SELECT
	last_name,
	salary,
	commission_pct 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL;

选择员工姓名的第三个字母是a 的员工姓名

SELECT
	last_name 
FROM
	employees 
WHERE
	last_name LIKE '__a%';

选择姓名中有字母a 和k 的员工姓名

SELECT
	last_name 
FROM
	employees 
WHERE
	last_name LIKE '%a%' 
	and last_name LIKE '%k%';

显示出表employees 表中first_name 以e 结尾的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	first_name LIKE '%e';
	
-- 正则(^e) 以 e 开头
SELECT
	* 
FROM
	employees 
WHERE
	first_name REGEXP 'e$';

显示出表employees 部门编号在80-100 之间的姓名、工种

SELECT
	last_name,
	job_id 
FROM
	employees 
WHERE
	department_id BETWEEN 80 
	AND 100;

显示出表employees 的manager_id 是100,101,110 的员工姓名、工资、管理者id

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

排序与分页

排序规则
- ASC 升序
- DESC 降序

分页

-- 每页显示 20 条记录,当前为第一页
SELECT
	employee_id,
	last_name 
FROM
	employees 
	LIMIT 0,20;
	
-- 计算公式 pageNo: 页码
limit (pageNo - 1) * pageSize,pageSize

声明顺序

1
2
3

WHERE ...
ORDER BY ...
LIMIT ...

MYSQL8.0 分页新特性

-- LIMIT 显示条数  OFFSET 偏移量
SELECT
	employee_id,
	last_name 
FROM
	employees 
	LIMIT 5 OFFSET 10;

左侧(全查) \| 右侧(分页查询)

排序与分页练习

查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序显示

SELECT
	last_name,
	department_id,
	salary * 12 AS "annual_salary" 
FROM
	employees 
ORDER BY
	annual_salary DESC,
	last_name ASC;

选择工资不在8000 到17000 的员工的姓名和工资,按工资降序,显示第21 到40 位置的数据

SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	salary NOT BETWEEN 8000 
	AND 17000 
ORDER BY
	salary DESC 
	LIMIT 21,40;

查询邮箱中包含e 的员工信息,并先按邮箱的字节数降序，再按部门号升序

SELECT
	last_name,
	department_id,
	length( email ) 
FROM
	employees 
WHERE
	email REGEXP '[e]'  -- email like '%e%'
ORDER BY
	length( email ) DESC,
	department_id ASC;

多表查询

自连接

-- 查询员工id,姓名及其管理者的id和姓名
SELECT
	emp.employee_id,
	emp.last_name,
	mgr.employee_id,
	mgr.last_name 
FROM
	employees emp
	INNER JOIN employees mgr ON emp.employee_id = mgr.manager_id

内连接和外连接
- 内连接: 合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行
- 外连接: 两个表在连接过程中除了返回满足条件的行以外还返回左(或右)表中不满足条件的行,这种连接成为左(或右)外连接,没有匹配的行时,结果表中相应的列为NULL
- 如果是左外连接,则连接条件中左边的表也称为主表，右边的表称为从表
- 如果是右外连接,则连接条件中右边的表为主表，左边的表称为从表

外连接(99 语法)

-- 查询所有员工的 last_name,department_name  (所有 => 隐含条件(左外))
SELECT
	last_name,
	department_name 
FROM
	employees emp
	LEFT OUTER JOIN departments dept ON emp.department_id = dept.department_id;

7种 SQL JOINS 的实现

实现图

`SQL JOINS`

UNION 的使用
- 合并查询结果集
  
  利用UNION 关键字,可以给出多条SELECCT 语句,并将他们的结果组合成单个结果集,合并时,两个表对应的列数和数据库类型必须相同,并且相互对应,各个SELECT 语句之间使用UNION 或UNION ALL 关键字分割
内连接(inner join【交集】)
外连接
- 左外连接: 表一所有和表二与表一的交集数据
- 右外连接: 表二所有和表二与表一的交集数据

多表查询练习

显示所有员工的姓名、部门号和部门名称

SELECT
	last_name,
	emp.department_id,
	department_name 
FROM
	employees emp
	LEFT OUTER JOIN departments dept ON emp.department_id = dept.department_id;

查询90 号部门员工的job_id 和90 号部门的location_id

SELECT
	job_id,
	dept.location_id 
FROM
	employees emp
	INNER JOIN departments dept ON emp.department_id = dept.department_id 
WHERE
	dept.department_id = '90';

选择所有有奖金的员工的last_name、department_name、location_id、city

SELECT
	last_name,
	department_name,
	d.location_id,
	city 
FROM
	employees emp
	LEFT JOIN departments d ON emp.department_id = d.department_id
	LEFT JOIN locations l ON d.location_id = l.location_id 
WHERE
	commission_pct IS NOT NULL;

选择city 在Toronto 工作的员工的last_name、job_id、department_id、department_name

SELECT
	last_name,
	job_id,
	d.department_id,
	department_name,
	city 
FROM
	employees emp
	INNER JOIN departments d ON emp.department_id = d.department_id
	INNER JOIN locations l ON d.location_id = l.location_id 
WHERE
	city = 'Toronto';

查询员工所在部门名称、部门地址、姓名、工作、工资、其中员工所在部门的部门名称为Executive

SELECT
	department_name,
	location_id,
	last_name,
	job_id,
	salary,
	department_name 
FROM
	employees emp
	INNER JOIN departments d ON emp.department_id = d.department_id 
WHERE
	department_name = 'Executive';

选择指定员工的姓名、员工号、以及他的管理者的姓名的员工号

SELECT
	emp.last_name,
	emp.employee_id,
	emp.manager_id,
	mgr.last_name,
	mgr.employee_id 
FROM
	employees emp
	JOIN employees mgr ON emp.manager_id = mgr.employee_id;

查询那些部门没有员工

SELECT
	* 
FROM
	employees emp
	RIGHT JOIN departments d ON emp.department_id = d.department_id 
WHERE
	emp.department_id IS NULL;

查询那个城市没有部门

SELECT
	city,
	department_name 
FROM
	employees emp
	RIGHT JOIN departments d ON d.department_id = emp.department_id
	RIGHT JOIN locations l ON d.location_id = l.location_id 
WHERE
	department_name IS NULL;

查询部门名为Sales 或IT 的员工信息

SELECT
	employee_id,
	last_name,
	department_name 
FROM
	employees emp
	INNER JOIN departments d ON emp.department_id = d.department_id 
WHERE
	department_name IN ( 'Sales', 'IT' );

函数

字符串函数

介绍

函数	用法
`length(s)`	返回字符串`s` 的字节数,和字符集有关
`concat(s1,s2,sn,...)`	连接字符串
`insert(str,idx,len,replacestr)`	将字符串`str` 从`idx` 位置开始,`len` 个字符长的子串替换为字符串`replacestr`(起始索引为`1`)
`replace(str,a,b)`	用字符串`b` 替换`a`
`upper`	字符串转换为大写
`lower`	字符串转换为小写
`lpad(str,len,pad)`	用字符串`pad` 对`str` 左边进行填充,直到`str` 的长度为`len` 个字符
`rpad(str,len,pad)`	用字符串`pad` 对`str` 右边进行填充,直到`str` 的长度为`len` 个字符

数值函数

说明

函数	功能
`ceil(x)`	向上取整
`floor(x)`	向下取整
`mod(x,y)`	返回`x/y` 的模
`rand()`	返回`0-1` 内的随机数
`round(x,y)`	求参数`x` 的四舍五入的值,保留`y` 位小数

-- 8.1 => 9
select ceil(8.1) from dual;

-- 8.1 =>
select floor(8.1) from dual;

-- 12%5=2...2
select mod(12,5) from dual;

-- round
select round(100/99,2) from dual;

日期和时间函数

获取日期、时间

函数	用法
`curdate(),current_date()`	返回当前的日期，只包含年、月、日
`curtime()`	返回当前的时间，只包含时、分、秒
`now()`	返回当前系统的日期和时间

日期与时间戳的转换

函数	用法
`unix_timestamp()`	以`unix` 时间戳的形式返回当前时间
`unix_timestamp(date)`	将时间`date` 以`unix` 时间戳的形式返回
`from_unixtime(stamp)`	将`unix` 时间戳的时间转换为普通格式的时间

获取月份、星期、星期数、天数等函数、

函数	用法
`year(date)/month(date)/day(date)`	返回具体的日期值
`hour(time)/minute(time)/second(time)`	返回具体的时间值
`monthname(date)`	返回月份:`September`
`dayname(date)`	返回星期几:`Thursday`
`weekday(date)`	返回周几:`周一是 0,...`
`quarter`	返回日期对应的季度,范围`1~4`
`week(date)、weekofyear(date)`	返回一年中的第几周
`dayifyear(date)`	返回日期是一年中的第几天
`dayofmonth(date)`	返回日期位于所在月份的第几天
`dayofweek`	返回周几,周日是`1`，周一是`2`，周六是`7`

-- 获取年月日
mysql> select year(now()) AS "年",month(now()) AS "月",day(now()) AS "日" from dual;
+------+----+----+
| 年   | 月 | 日 |
+------+----+----+
| 2022 |  9 | 15 |
+------+----+----+


-- 获取时分秒
mysql> select hour (now()) AS "时",minute(now()) AS "分",second(now()) AS "秒" from dual;
+----+----+----+
| 时 | 分 | 秒 |
+----+----+----+
|  9 | 16 |  3 |
+----+----+----+

mysql> select monthname(now()) from  dual;
+------------------+
| monthname(now()) |
+------------------+
| September        |
+------------------+

mysql> select dayname(now()) from dual;
+----------------+
| dayname(now()) |
+----------------+
| Thursday       |
+----------------+

mysql> select weekday(now()) from dual;
+----------------+
| weekday(now()) |
+----------------+
|              3 |
+----------------+

日期的操作函数

函数	用法
`extract(type from date)`	返回指定日期中特定的部分,`type` 指定返回的值

1 2	-- 获取年份 select extract(year from now()) from dual;

`type` 取值	含义
`microsecond`	返回毫秒数
`second`	返回秒数
`minute`	返回分钟数
`hour`	返回小时数
`day`	返回天数
`week`	返回日期在一年中的第几个星期
`month`	返回日期在一年中的第几个月
`quarter`	返回日期在一年中的第几个季度
`year`	返回日期的年份
`second_microsecond`	返回秒和毫秒值
`minute_microsecond`	返回分钟和毫秒值
`hour_minute`	返回小时和分钟值
`day_microsecond`	返回天和毫秒值
`day_second`	返回天和秒值
`day_minute`	返回天和分钟值
`day_hour`	返回天和小时
`year_month`	返回年和月

时间和秒钟转换

函数	用法
`time_to_sec(time)`	将`time` 转化为秒并返回结果值,转换公式为`小时 * 3600+分钟*60+秒`
`sec_to_time`	将`seconds` 描述转换为包含小时、分钟、和秒的时间

1 2	-- 输出当前时间的时分秒 select sec_to_time(time_to_sec(now())) from dual;

计算日期和时间函数

第一组

函数	用法
`date_add(datetime,interval expr type)`	返回与给定日期时间相差`interval` 时间段的日期函数
`date_sub(date,interval expr type),subdate(date,interval expt type)`	返回与`date` 相差`interval` 时间间隔的日期

间隔类型	含义
`hour`	小时
`minute`	分钟
`second`	秒
`year`	年
`month`	月
`day`	日
`year_month`	年和月
`day_hour`	日和小时
`day_minute`	日和分钟
`day_second`	日和秒
`hour_minute`	小时和分钟
`hour_second`	小时和秒
`minute_second`	分钟和秒

1 2	-- 年后和年前 select now(),date_add(now(),interval 1 year ) AS "一年后",date_add(now(),interval -1 year ) AS "一年前" from dual;

年后和年前

第二组

函数	用法
`addtime(time1,time2)`	返回`time1` 加`time2` 的时间,当`time2` 为一个数组时,代表的是秒,可以为负数
`subtime(time1,time2)`	返回`time1` 减去`time2` 后的时间,当`time2` 为一个数字时,代表的是秒,可以为负数
`datediff(date1,date2)`	返回`date1-date2` 的日期间隔天数
`timediff(time1,time2)`	返回`time1-time2` 的时间间隔
`from_days(n)`	返回从`0000` 年`1` 月`1` 日起,`N` 天以后的日期
`to_days(date)`	返回日期`date` 距离`0000` 年`1` 月`1` 日的天数
`last_day(date)`	返回`date` 所在月份的最后一天的日期
`makedate(year,n)`	针对给定年份所在年份中的天数返回一个日期
`maketime(hour,minute,second)`	将给定的小时，分钟和秒数组合成时间并返回
`period_add(time1,n)`	返回`time` 加上`n` 后的时间

select addtime(now(), 20),
       subtime(now(), 30),
       subtime(now(), '1:1:10'),
       datediff(now(), '2023-09-16'),
       timediff(now(), '2021-09-16 12:00:00'),
       from_days(366),
       to_days('0000-12-25'),
       last_day(now()),
       makedate(year(now()), 12),
       maketime(10, 21, 23),
       period_add(20200101010101, 10)
from dual;

日期和格式化与解析

函数	用法
`date_format(date,fmt)`	按照字符串`fmt` 格式化日期`date` 值`fmt: %Y-%m-%d`
`time_format(time,fmt)`	按照字符串`fmt` 格式化时间`time` 值
`get_format(date_type,format_type)`	返回日期字符串的显示格式
`str_to_date(str,fmt)`	按照字符串`fmt` 对`str` 进行解析,解析为一个日期

流程控制函数

IF

1 2	select if(commission_pct is not null,commission_pct,0 ) from employees; select if(salary>12000,'A','B' ) from employees;

IFNULL

1	select ifnull(commission_pct,0 ) from employees;

case when then when then

-- if else if ... when 条件
select last_name,
       salary,
       case
           when salary > 12000 then 'A'
           when salary < 8000 then 'B'
           else 'C'
           end "别名"
from employees
where salary is not null;

+-------------+----------+-----------+
| King        | 24000.00 | A                                                                                                                  |
| Kochhar     | 17000.00 | A                                                                                                                  |
| De Haan     | 17000.00 | A                                                                                                                  |
| Hunold      |  9000.00 | C                                                                                                                  |
| Ernst       |  6000.00 | B                                                                                                                  |
| Austin      |  4800.00 | B                                                                                                                  |
| Pataballa   |  4800.00 | B                                                                                                                  |
| Lorentz     |  4200.00 | B                                                                                                                  |
| Greenberg   | 12000.00 | C                                                                                                                  |
| Faviet      |  9000.00 | C                                                                                                                  |
| Chen        |  8200.00 | C                                                                                                                  |
+-------------+----------+------------+

练习

-- switch case
-- 部门id 为 10，20，30  when 常量值
SELECT last_name,
       department_id,
       salary,
       CASE
           department_id
           WHEN 10 THEN salary * 1.1
           WHEN 20 THEN salary * 1.2
           WHEN 30 THEN salary * 1.3
           END "new salary"
FROM employees
WHERE department_id IN (10, 20, 30);

加密与解密函数

加密

1	select md5('mysql'),sha('mysql') from dual;

信息函数

1	select version(),connection_id(),schema(),user(),current_user from dual;

单行函数练习

显示系统时间(日期+时间)

SELECT
	now(),
	sysdate(),
	CURRENT_TIMESTAMP (),
LOCALTIMESTAMP 
FROM
	DUAL;

查询员工号、姓名、工资、以及工资提高20% 之后的结果

SELECT
	employee_id,
	last_name,
	salary * 1.2 AS "new salary" 
FROM
	employees;

将员工的姓名按首字母排序、并写出姓名的长度(length)

SELECT
	last_name,
	length( last_name ) AS "length" 
FROM
	employees 
ORDER BY
	last_name;

查询员工id，姓名、工资,并作为一个列输出，别名为out_put

SELECT
	concat( employee_id, ' ', last_name, ' ', salary ) AS "out_put" 
FROM
	employees;

查询公司各员工工作的年数、工作的天数、并按工作年数降序排序

SELECT
	last_name,
	datediff( curdate(), hire_date ) / 365 AS "work_years",
	datediff( curdate(), hire_date ) AS "work_days1",
	TO_DAYS(CURDATE())- TO_DAYS( hire_date ) AS "work_days2"
FROM
	employees 
ORDER BY
	work_years DESC;

查询员工姓名、入职日期、员工编号、满足以下条件: 雇佣时间为1997 年之后,department_id 为80 | 90 | 110,commission_pct 不为空

SELECT
	last_name,
	hire_date,
	department_id 
FROM
	employees 
WHERE
	hire_date >= '1997-01-01' # 隐式转换
	AND DATE_FORMAT( hire_date, '%Y-%m-%d' ) >= '1997-01-01' # 格式化: 日期 => 字符串
	AND DATE_FORMAT( hire_date, '%Y' ) >= '1997' # 格式化: 日期 => 字符串
	AND hire_date >= STR_TO_DATE( '1997-01-01', '%Y-%m-%d' ) 
	AND department_id IN ( 80, 90, 110 ) 
	AND commission_pct IS NOT NULL;

查询公司中入职超过10000 天的员工姓名、入职时间

SELECT
	last_name,
	hire_date 
FROM
	employees 
WHERE
	datediff( curdate(), hire_date ) >= 10000;

做一个查询，产生下面的结果

-- <last_name> earns <salary> monthly but wants <salaty*3>
SELECT
	concat( last_name, ' earns ', TRUNCATE ( salary, 0 ), ' monthly but wants ', salary * 3 ) AS "Dream Salary" 
FROM
	employees;

case when then

SELECT last_name,
       salary,
       CASE
           job_id
           WHEN 'AD_PRES' THEN 'A'
           WHEN 'ST_MAN' THEN 'B'
           WHEN 'IT_PROG' THEN 'C'
           WHEN 'SA_REP' THEN 'D'
           WHEN 'ST_CLERK' THEN 'E'
           END "Grade"
FROM employees;

聚合函数

sum
avg
max
min
count
- count(*)
- count(1)
- count(字段) => 不计算 null 值
  
  如果使用MyISAM 存储引擎、则三者效率相同,都是O(1)
  
  如果是InnoDB 存储引擎，则三者效率COUNT(*) = COUNT(1) > COUNT(字段)

group by

查询各个部门的平均工资、最高工资

SELECT
	avg( salary ),
	max( salary ),
	department_id 
FROM
	employees 
GROUP BY
	department_id;

查询各个部门，工种的平均工资

SELECT
	avg( salary ),
	max( salary ),
	department_id,
	job_id 
FROM
	employees 
GROUP BY
	job_id,
	department_id;

总结

SELECT 中出现的非组函数的字段必须声明在GROUP BY 中

反之，GROUP BY 中声明的字段可以不出现在SELECT 中

GROUP BY 生命在FROM 后面,WHERE 后面,ORDER BY 前面,LIMIT 前面

having 用来过滤数据

查询各个部门中最高工资比10000 高的部门信息

SELECT
	department_id,
	max( salary ) 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	max( salary ) > 10000;

总结

结论一: 如果过滤条件中使用了聚合函数，则必须使用HAVING 来替换WHERE，否则报错

结论二: HAVING 必须声明在GROUP BY 之后

尽可能GROUP BY 与HAVING 结合使用

查询部门id 为10~40 这4 个部门中最高工资比10000 高的部门信息

SELECT
	department_id,
	max( salary ) 
FROM
	employees 
WHERE
	department_id BETWEEN 10 AND 40 
GROUP BY
	department_id 
HAVING
	max( salary ) > 10000;

where 和having 的对比

区别1: where 可以直接使用表中的字段作为筛选条件,但不能使用分组中的计算函数作为筛选条件,having 必须要与group by 配合使用,可以把分组计算的函数和分组字段作为筛选条件

区别2: 如果需要通过连接从关联表中获取需要的数据，where 是先筛选后连接,而having 是先连接后筛选

小结

	优点	缺点
`where`	先筛选数据后在关联，执行效率高	不能使用分组中计算函数进行筛选
`having`	可以使用分组中的计算函数	在最后的结果集中进行筛选,执行效率较低

SQL 底层执行原理

SQL 语句的完整结构

select ...
	from xx
where xx
	group by xxx
	having xxx
	order by xxx
	limit x,xx

SQL 语句的执行流程

from ...xxx,xxx(cros join) -> on 
	-> (left/right join)-> 
	where -> group by -> having 
	-> select -> distinct -> order by -> limit

聚合函数练习

where 子句可否使用组函数进行过滤?不可以

查询公司员工工资的最大值、最小值、平均值、求和

SELECT
	max( salary ),
	min( salary ),
	avg( salary ),
	sum( salary ) 
FROM
	employees;

查询各job_id 的员工的最大值、最小值、平均值、总和

SELECT
	max( salary ),
	min( salary ),
	avg( salary ),
	sum( salary ) 
FROM
	employees 
GROUP BY
	job_id;

选择具有各个job_id 的员工人数

SELECT
	job_id,
	count(*) 
FROM
	employees 
GROUP BY
	job_id;

查询员工最高工资和最低工资的差距difference

SELECT
	max( salary ) - min( salary ) AS "difference" 
FROM
	employees;

查询各个管理者手下员工的最低工资,其中最低工资不能低于6000，没有管理者的员工不计算在内


SELECT
	manager_id,
	min( salary ) 
FROM
	employees 
WHERE
	manager_id IS NOT NULL 
GROUP BY
	manager_id 
HAVING
	min( salary ) >= 6000;

查询所有部门的名字，location_id 员工数量和平均工资、并按平均工资降序

SELECT
	department_name,
	location_id,
	count( e.employee_id ),
	avg( salary ) 
FROM
	departments dept
	LEFT JOIN employees e ON e.department_id = dept.department_id 
GROUP BY
	department_name,
	location_id 
ORDER BY
	avg( salary ) DESC;

查询每个(所有)工种、每个(所有)部门的部门名、工种名和最低工资

SELECT
	job_id,
	department_name,
	min( salary ) 
FROM
	employees emp
	RIGHT JOIN departments dept ON emp.department_id = dept.department_id 
GROUP BY
	job_id,
	department_name;

子查询

根据子查询结果不同,分为
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)

谁的工资比Abel 的高

SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' );

多行子查询

in

any

-- 返回其他 job_id 中比 job_id 为 "IT_PROG"部门任一工资低的员工的员工号
SELECT
	last_name,
	salary,
	job_id,
	employee_id 
FROM
	employees 
WHERE
	job_id <> 'IT_PROG' 
	AND salary < ANY ( SELECT salary FROM employees WHERE job_id = 'IT_PROG' );

all

-- 返回其他 job_id 中比 job_id 为 "IT_PROG"部门所有工资低的员工的员工号
SELECT
	last_name,
	salary,
	job_id,
	employee_id 
FROM
	employees 
WHERE
	job_id <> 'IT_PROG' 
	AND salary < ALL ( SELECT salary FROM employees WHERE job_id = 'IT_PROG' );

some(any)

查询平均工资最低的部门id

-- 
SELECT
	min( salary ),
	department_id 
FROM
	( SELECT avg( salary ) "salary", department_id FROM employees GROUP BY department_id ) dept_avg_salary;

必须有别名

子查询练习

1.查询和Zlotkey 相同部门的员工姓名和工资
2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
5.查询在部门的location_id为1700 的部门工作的员工的员工号
6.查询管理者是King 的员工姓名和工资
7.查询工资最低的员工信息: last_name, salary
8.查询平均工资最低的部门信息
9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
10.查询平均工资最高的job 信息
11.查询平均工资高于公司平均工资的部门有哪些?
12.查询出公司中所有 manager 的详细信息
13.各个部门中最高工资中最低的那个部门的最低工资是多少?
14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
1. 查询部门的部门号，其中不包括job_id 是”ST_CLERK“的部门号
1. 选择所有没有管理者的员工的last_name
17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 ‘De Haan‘
18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询

根据需求创建表

设计合理的数据类型

设计一张员工表,要求如下

编号(纯数字)
员工工号(字符串类型,长度不超过10 位)
员工姓名(字符串类型，长度不超过10 位)
性别(男/女,存储一个汉字)
年龄(正常人年龄,不可能存储负数)
身份证号(二代身份证号均为18 位,身份证中有X 这样的字符)
入职时间(取值年月日即可)

create table emp(
	id int COMMENT '编号',
	workno varchar(10) COMMENT '工号',
	name varchar(10) COMMENT '姓名',
	gender char(1) COMMENT '性别',
    -- 年龄没有负数 tinyint: 无符号范围 0-255
	age tinyint unsigned COMMENT '年龄',
	idcard char(18) COMMENT '身份证号',
	entrydate date COMMENT '入职时间'
);

数据库的创建、修改与删除(DDL:数据定义语言)

创建数据库

CREATE DATABASE
IF
	NOT EXISTS aa CHARACTER 
	SET 'utf8';

使用数据库

查看当前所有的数据库

1	show databases;

查看当前正在使用的数据库

1	select database();

查看指定库下的所有表

1	show tables from atguigudb;

查看数据库的创建信息

1	show create database atguigudb;

切换数据库

1	use database_name;

更改数据库字符集

1	alter database xxx character set 'utf8';

表

创建表

create table if not exists emp
(
    id        int,
    emp_name  varchar(15),
    hire_date date
);

查看表结构

desc emp;

查看建表语句

1	show create table emp;

基于现有的表创建新表

-- 携带数据
create table copy_employees
as
select * from emp;

commit | rollback
- commit 提交数据、一旦执行，则数据就永久被保存在数据库中,不可以回滚
- rollback 回滚数据，一旦执行，则可以实现数据的回滚，回滚到最近的一次COMMIT 之后
truncate table | delete from
- 相同点: 都可以实现对表中所有数据的删除,同时保留表结构
- 不同点
  - truncate table 一旦执行此操作、表数据全部清除，同时，数据是不可以回滚的
  - delete from 一旦执行此操作，表数据可以全部清除(不带 where)，同时，数据是可以回滚的

MYSQL8 新特性：计算列

create table test1
(
    a int,
    b int,
    -- 新特性使用
    c int generated always As (a + b) virtual
);

修改数据类型

1	alter table 表名 modify 字段名新数据类型(长度);

`modify`

修改字段名和字段类型

1	alter table 表名 change 旧字段名新字段名类型(长度) [comment 注释] [约束];

1 2	-- workno(旧) => wkno(新) alter table emp change workno wkno varchar(10) comment '员工号' not null

删除字段

alter table 表名 drop 字段名;

-- 删除表中的 id 字段
alter table emp drop id;

添加字段

-- 在第一列(first)添加字段 id
alter table userinfo add column id int not null primary key first ;

-- after 字段 [在某个字段之后添加该字段]
alter table userinfo add column uuid int not null  after age;

添加字段

修改表名

1
2
3

alter table 旧表名 rename to 新表名
-- 将 emp 修改为 userinfo
alter table emp rename to userinfo;

删除表

1	drop table [if exists] 表名;

删除指定表，并重新创建该表

1	truncate table 表名;

注意: 在删除表时,表中的全部数据也会被删除

数据类型

分为三大类
- 数值类型
- 字符类型
  - char(n) 定长,比如性比(char(1)):使用时性能比较高
  - varchar(n) 可变长,用户名等使用时
- 日期时间类型

日期与时间类型

year 通常用来表示年
date 表示年、月、日(生日字段可推荐使用)
time 用来表示时、分、秒
datetime 用来表示年、月、日、时、分、秒
timestamp 用来表示带有时区的年、月、日、时、分、秒

类型	大小	范围	格式	描述
`DATE`	3	`1000-01-01至999-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	`1971-01-01 00:00:01 至 2038-01-19 03:14:07`	`YYYY-MM-DD HH:MM:SS`	混合日期和时间值、时间戳

小结
- 在定义数据类型时、如果确定时整数就用INT，如果是小数,一定要用定点数类型DECIMAL 如果是日期与时间类型就用DATETIME
- 规范
  - 任何字段如果为非负数，必须是UNSIGNED
  - 【强制】小数类型为DECIMAL，禁止使用float 和double
    - 在存储的时候,float 和double 都存在精度损失的问题,很可能在比较直的时候,得到不正确的结果,如果存储的数据范围超过DECIMAL 的范围,建议将数据拆成整数和小数并分开存储
  - 【强制】如果存储的字符串长度几乎相等，使用char 定长字符串类型
  - 【强制】 varchar 是可变长字符串，不预先分配存储空间，长度不要超过5000 如果存储长度大于此值，定义类型为text，独立出来一张表,用主键来对应,避免影响其他字段索引效率

DQL-编写顺序和执行顺序

顺序

编写顺序	执行顺序

约束

为什么需要约束

数据完整性是指数据的精确性和可靠性。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或信息错误而提出的
概念

约束是作用于表中字段上的规则,用于限制存储在表中的数据
目的

保证数据库中数据的正确、有效性和完整性

分类

约束	描述	关键字
非空约束	限制字段的数据不能为`null`	`not null`
唯一约束	保证该字段的所有数据都是唯一的,不重复的	`unique`
主键约束	主键是一行数据的唯一标识，要求非空且唯一	`primary key`
默认约束	保存数据时，如果未指定该字段的值,则采用默认值	`default`
检查约束	保证字段值满足某一个条件	`check`
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	`foreign key`

外键

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

-- 创建表时添加外键
create table 表名(

	[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
)

-- 添加
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);

删除外键

1	alter table 表名 drop foreign key 外键名称;

视图

创建视图

CREATE VIEW test_emp AS SELECT
* 
FROM
	employees;

查看视图

1 2	-- 查看数据库的表对象、视图对象 show tables;

查看视图结构

-- 全
describe test_emp;
-- 简
desc test_emp;

查看表结构

查看视图的属性信息

1	show table status like 'test_emp';

查看视图的详细定义信息

1	show create view test_emp;

更改视图的数据会影响基表

-- 更新视图
update  test_emp set last_name='King k' where last_name='King';
-- 查看基表

更新视图

存储过程与函数

存储过程: 一组经过预先编译的SQL 语句的封装
分类
- 无参数返回
- 有参数无返回(仅仅带有IN 类型)
- 无参数有返回(仅仅带有OUT 类型)
- 有参数有返回(INOUT)
参数符号
- IN 当前参数为输入参数、也就表示入参,存储过程只是读取这个参数的值，如果参数没有定义种类、默认就是IN，表示输入参数
- OUT 当前参数为输出参数、也就是表示出参、执行完成后,调用这个存储过程的客户端或者应用程序就可以读取这个参数的返回值了
- INOUT 当前参数既可以为输入参数,也可以为输出参数
- characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

调用存储过程

1	call 存储过程名();

存储函数

创建函数

-- 配置
set global log_bin_trust_function_creators = 1;

create function test_function(参数名 参数类型)
returns int -- 返回值类型
begin 
    -- 函数体
end;

调用函数

select 函数();

delimiter //
create function count_info()
returns int
begin
    return (select count(*) from employees);
end //;

-- 添加参数
set @emp_id = 102
select emp_info(@emp_id );


select emp_info(101);

存储过程和存储函数的查看、修改、删除

查看

1	show create (procedure \| function) name;

查看存储过程和函数的状态信息

show procedure|function status like 'count_info';


mysql> show function status like 'count_info'\G;
*************************** 1. row ***************************
                  Db: atguigudb
                Name: count_info
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2022-09-18 12:46:17
             Created: 2022-09-18 12:46:17
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

删除

1	drop { procedure \| function } 存储过程\|函数;

事务

概念

是一组操作的集合,它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

事务操作

-- 开启事务
start transaction;
-- 提交/回滚事务
commit / rollback;

事务的四大特性
- 原子性: 事务是不可分割的最小操作单元，要么全部成功，要么全部失败
- 一致性: 事务完成时,必须使所有的数据都保持一致状态
- 隔离性: 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
- 持久性: 事务一旦提交或回滚,它对数据库中的数据的改变就是永久的

并发事务问题

脏读

`脏读: 一个事务读到另一个事务还没有提交的数据`

在事务A 这个整体中,第一条SQL 执行了查询操作,第二条执行了更新操作,但在此次过程中,整个事务A 中有未提交的事务,而在事务B 中执行了查询，由于事务A 中有未提交事务,此时发生脏读

不可重复读

`不可重复读：一个事务先后读取到同一条记录,但两次读取的数据不同,称之为不可重复读`

在事务A 中,先执行了查询操作，在并发的场景下,又执行了事务B 的更新操作,当在事务A 在执行第一次的查询操作时,由于事务B 对数据发生了更新,此时在执行同一条SQL 时得到了两种不一致的结果,称之为不可重复读

幻读

`幻读：一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了一个幻影`

事务A 首先执行id=1(id为主键) 的查询操作,此时数据不存在,在事务B 中执行插入id=1 的操作,由于之前在事务A 中查询数据时不存在,那么此时在事务A 中执行id=1 的插入操作,而由于事务B 已经执行了id=1 的插入操作,而且id 还是主键,那么事务A 中插入数据时将会显示主键冲突问题,但是在事务A 中再次查询时数据又不存在,插入时又会出现冲突，此时好像出现了一个幻影,称之为幻读

事务的隔离级别

作用: 解决并发事务出现的问题

MYSQL 的默认隔离级别:可重复读

隔离级别	脏读	不可重复读	幻读
`Read uncommitted(读未提交)`	`✓`	`✓`	`✓`
`Read committed(读已提交)【oracle默认】`	x	`✓`	`✓`
`Repeatable Read(可重复读【mysql默认】)`	x	x	`✓`
`Serializable(串行化)`	x	x	x

✓表示会出现的问题,x 表示解决的问题

-- 查看事务隔离级别
select @@transaction_isolation;
-- 设置隔离级别
set [session(会话级别)|global] transaction isolation level {read uncommited|read committed|repeatable read|serializable}

隔离级别查看

Linux

MYSQL卸载

关闭mysql 服务

1	systemctl stop mysqld.service

查看当前MYSQL 安装情况

1
2
3

rpm -qa|grep -i mysql #【mariadb】
或
yum list installed | grep mysql

卸载上述查询出的已安装程序

1	yum remove mysql-xx

删除MYSQL 相关文件

查找相关文件

1	find / -name mysql

删除上述找到的文件

MYSQL 安装

下载选择

`wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar`

解压

# 创建目录
mkdir mysql80
# 解压
tar -xvf mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar -C ./mysql80

红色内容(前3个,倒数第2,倒数4)保留,其余内容可以不用，可删除

检查/tmp 临时目录权限

1
2
3

chmod -R 777 /tmp
chmod -R 777 /var/log/
chmod -R 777 /var/lib/

rpm -qa|grep libaio
rpm -qa|grep net-tools
# 未出现安装
yum install -y libaio net-tools

正常

按照顺序执行(必须)

rpm -ivh mysql-community-common-8.0.27-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.27-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.27-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.27-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.27-1.el7.x86_64.rpm

检测安装

1	mysqladmin --version

安装成功

初始化

1 2	# 为了保证数据库目录与文件的所有者为 mysql 登录用户,会生成一份临时密码 mysqld --initialize --user=mysql

查看临时密码

1	cat /var/log/mysqld.log

查看密码

启动服务

启动服务:`systemctl start mysqld`

查看mysql 服务是否自启动

1 2	# enable => ok systemctl list-unit-files\|grep mysqld.service

如果不是enable

1	systemctl enable mysqld.service

连接测试

1 2	mysql -u root -p # 回车 => 临时密码

修改密码

1	alter user 'root'@'localhost' identified by 'root';

设置远程登录

1
2
3

use mysql;
update user set host = '%' where user = 'root';
flush privileges;

远程访问

1	alter user 'root'@'%' identified with mysql_native_password by 'root';

关闭防火墙

1 2	systemctl stop firewalld; systemctl disable firewalld;

大小写敏感

1
2
3

# 0 => 敏感
show variables like '%lower_case_table_name%';

1
2
3

# 5.7/ 8 不建议修改
[mysqld]
lower_case_table_name=1

可视化连接测试

可视化连接测试

用户与权限管理

用户管理

创建用户并指定连接服务器

1	create user 'coder-itl'@'%' identified by 'coder-itl';

修改用户是修改user 表的内容

删除用户

# 默认删除 % 服务的用户
drop user 'coder-itl';

# 删除指定
drop user 'coder-itl'@'localhost';

设置当前用户的密码

1
2
3

set password = password('5.7有效');
-- 8.0
alter user user() identified by 'new password';

1 2	# 设置指定用户的密码 alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

权限

查看权限

1	show privileges;

权限授权的原则
1. 只授予能满足需要的最小权限，防止用户干坏事,比如用户只是需要查询,那就只给select 权限，不要给用户赋予update\insert\delete 权限
2. 创建用户的时候限制用户的登录主机，一般是限制指定IP 或者内网IP 段
3. 为每个用户设置满足密码复杂度的密码
4. 定期清理不需要的用户，回收权限或者删除用户

授予权限

1	grant 权限1,权限2 on 数据库名.表名称 to 用户名@'地址'

1 2	-- 赋予查询和更新权限 grant select,update on atguigudb.employees to 'coder-itl'@'%';

测试

测试删除

查看权限

1	show grants;

特殊权限

1	grant all privileges on . to 'coder-itl'@'%';

回收权限

1 2	-- revoke 权限1,权限2 on 数据库.表名称 from 'coder-itl'@'%'; revoke all privileges on . from 'coder-itl'@'%';

权限表

MYSQL 服务器通过权限表来控制用户对数据库访问,权限表存放在mysql 数据库中,MYSQL 数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user，db，table_priv,column_priv,... 等表,在MYSQL 启动时,服务器会将这些数据库表中的权限信息的内容读入内存中

角色

创建角色

1	create role 'role-name'@'%';

查看角色的权限

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

回收角色权限

1	revoke update on 数据库名.表名 from '角色';

SQL的执行流程

架构

简化

执行流程

流程

MYSQL 查询流程
- 查询缓存: Server 如果在查询缓存中发现了这个SQL 语句,就会直接将结果返回给客户端、如果没有,就进入到解析器阶段。需要说明的是,因为查询缓存存在往往效率不高，所以在MYSQL8.0 之后就抛弃了这个功能

确认profiling 是否开启

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

开启

1 2	-- 临时开启 set profiling = 1;

mysql> show profiles;
+----------+------------+---------------------+
| Query_ID | Duration   | Query               |
+----------+------------+---------------------+
|        2 | 0.00021275 | SELECT DATABASE()   |
|        3 | 0.00085600 | show databases      |
|        4 | 0.00136100 | show tables         |
|        5 | 0.00025725 | SELECT DATABASE()   |
|        6 | 0.00150600 | show tables         |
|        7 | 0.00011475 | select * from order |
|        8 | 0.00011550 | select * from order |
|        9 | 0.00013350 | select * from order |
|       10 | 0.00010475 | desc order          |
|       11 | 0.00085975 | show databases      |
|       12 | 0.00143250 | show tables         |
|       13 | 0.00141300 | show tables         |
|       14 | 0.00035400 | select * from jobs  |
|       15 | 0.00036275 | select * from jobs  |
|       16 | 0.00039600 | select * from jobs  |
+----------+------------+---------------------+
15 rows in set, 1 warning (0.00 sec)

-- 执行指定
show profile for query Query_ID;

存储引擎

是什么

存储引擎就是存储数据,建立索引,更新/查询数据等技术的实现方式。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被成为表类型

查看存储引擎

1	mysql> show engines;

`9` 类

修改存储引擎

1 2	# 配置文件修改 default-storage-engine=MyISAM

1 2	-- 临时 set default_storage_engine=MyISAM;

InnoDB 引擎:具备外键支持功能的事务存储引擎

>=5.5 之后默认支持InnoBD 引擎
InnoDB 是MYSQL 的默认事务引擎，他被设计用来处理大量的短期事务，可以确保事务的完整提交和回滚
除了增加和查询外,还需要更新、删除操作,应优先选择InnoDB 引擎
对比MyISAM 的存储引擎,InnoDB 写的处理效率差一些,并且会占用更多的磁盘空间以保存数据和索引

InnoDB 的逻辑结构

逻辑结构

MYISAM 只缓存索引，不缓存真实数据，InnoDB 不仅缓存索引还要缓存真实数据,对内存要求极高，而且内存大小对性能有决定性的影响
MYISAM 引擎: 主要的非事务处理存储引擎
- MYISAM 提供了大量的特性,包括全文索引,压缩，空间函数等，但MYISAM 不支持事务、行级锁、外键,有一个毫无疑问的缺陷就是崩溃后无法安全恢复
- 优势是访问速度快，对事务完整性没有要求或者以select,insert 为主的应用
- 针对数据统计有额外的常数存储，故而count(*) 的查询效率很高
- 应用场景: 只读应用或者以读为主的业务
Memory
- 介绍
  
  Memory 引擎的表数据存储在内存中,由于受到硬件问题、或断电问题的影响,只能将这些表作为临时表或缓存使用
- 特点
  - 内存存放
  - hash索引(默认)
- 文件
  
  xxx.sid: 存储表结构信息
Archive 引擎: 用于数据存档
- archive 是归档的意思,仅仅支持插入和查询两种功能(行被插入后不能再修改)
- 在MYSQL5.5 以后支持索引功能
- 拥有很好的压缩机制，使用zlib 压缩库,在记录请i去的时候实时的进行压缩，经常被用来作为仓库使用
- 创建archive 表时,存储引擎会创建名称以表名开头的文件，数据文件的扩展名为.ARZ
- archive 存储引擎采用了行级锁,该archive 存储引擎支持AUTI_INCREMENT 列属性,AUTO_INCREMENT 列可以具有唯一索引或非唯一索引.尝试在任何其他列上创建索引会导致错误
- archive 表适合日志和数据采集类应用,适合存储大量的独立的作为历史记录的数据拥有很高的插入速度，但是对查询支持较差
Blackhole 引擎: 丢弃写操作，读操作会返回空内容
- blackhole 引擎没有实现任何存储机制，它会丢弃所有插入的数据,不做任何保存
- 但服务器会记录blackhole 表的日志，所以可以用于赋值数据到备库，或者简单的记录到日志。但这种应用方式会碰到很多问题，因此并不推荐
CSV 引擎: 存储数据时,以逗号分割各个数据项
- CSV 引擎可以将普通的 CSV文件作为MYSQL的表来处理但不支持索引
- CSV 引擎可以作为一种数据交换的机制非常有用
- CSV 存储的数据可以直接在操作系统里，用文本编辑器,或者excel 读取
- 对于数据的快速导入,导出有明显的优势的

各大存储引擎的特点

注意区分`InnoDB与MyISAM` 区别

索引

索引概述

索引概述

索引(Index)是帮助MYSQL 高效获取数据的数据结构
索引的本质

索引是数据结构，你可以理解为排好序的快速查找的数据结构，满足特定查找算法。这些数据结构以某种方式指向数据。这样就可以在这些数据结构的基础上实现高级查找算法

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定每个表的最大索引数和最大索引长度,所有存储引擎支持每个表至少16 个索引，总索引长度至少为256 字节，有些存储引擎支持更多的索引数和更大的索引长度
优点
- 索引类似于图书馆建书目索引，提高数据检索的效率，降低数据库的 IO 成本,这也是创建索引最主要的原因
- 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
- 在实现数据的参考完整性方面,可以加速表和表之间的连接换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度
- 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间,降低了CPU 的消耗
缺点
- 创建索引和维护索引要耗费时间，并且锁着数据量的增加，所耗费的时间也会增加
- 索引需要占用磁盘空间除了数据表站数据空间之外，每一个索引还要站一定的物理空间,存储在磁盘上如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，当表中的数据进行增加、删除和修改时，索引也要动态的维护，这样就会降低了数据的维护速度
因此，选择索引时需要综合考虑优缺点:

索引可以提高查询的速度，但是会影响插入记录的速度，这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后在创建索引

索引

索引的分类
- 从功能逻辑上说，索引主要有4 种,分别是普通索引、唯一索引、主键索引、全文索引
- 按照物理实现方式，索引可以分为2 种，聚簇索引和非聚簇索引
- 按照字段个数进行划分，分成单列索引和联合索引
聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式(所有用户记录都存储在叶子节点),也就是所谓的索引即数据，数据即索引

聚簇表示数据行和相邻的键值聚簇的存储在一起
- 特点
  - 使用记录主键值的大小进行记录和页的排序,这包括三方面的含义
    - 业内的记录是按照主键的大小顺序排成一个单向链表
    - 各个存放用户记录的页也就是根据页中用户记录的主键大小顺序拍成一个双向链表
    - 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项的主键大小排成一个双向链表
  - B+ 树的叶子节点存储的是完整的用户记录
    
    所谓完整的用户记录,就是指这个记录中存储了所有列的值(包括隐藏列)
    
    我们把具有这两种特性的B+ 树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MYSQL 语句中显示的使用index 语句去创建，InnoDB 存储引擎会自动的为我们创建聚簇索引
- 优点
  - 数据访问更快因为聚簇索引和数据保存在同一个B+ 树中,因此从聚簇索引中获取数据比聚簇索引更快
  - 聚簇索引对于主键的排序查找和范围查找速度非常快
  - 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连,数据库不用从多个数据块中提取数据，所以节省了大量的 IO操作
- 缺点
  - 插入速度严重依赖于插入顺序按照主键的顺序插入是最快的方式,否则将会出现页分裂，严重影响性能。因此,对于InnoDB 表，我们一般都会定义一个自增的 ID列为主键
  - 更新主键的代价很高因为将会导致将被更新的行移动。因此，对于InnoDB 表，我们一般定义为主键不可更新
  - 二级索引访问需要两次索引查找第一次找打主键值，第二次根据主键值找到行数据
- 限制
  - 对于MYSQL 数据库目前只有InnoDB 数据引擎支持聚簇索引，而MYISAM 并不支持聚簇索引
  - 由于数据物理存储排序方式只能有一种，所以每个MYSQL 的表只能有一个聚簇索引一般情况下就是该表的主键
  - 如果没有定义主键，InnoDB 会选择非空的唯一所以代替，如果没有这样的索引，InnoDB 会隐式的定义一个主键来作为聚簇索引
  - 为了充分利用聚簇索引的特性，所以InnoDB 表的主键列尽量选择有序的顺序id,而不建议用无序的id，比如uuid,md5,hash,字符串列作为主键无法保证数据的顺序增长
非聚簇索引
小结
1. 聚簇索引的叶子节点存储的就是我们的数据记录,非聚簇索引的叶子节点存储的就是数据位置,非聚簇索引不会影响数据表的物理存储顺序
2. 一个表只能有一个聚簇索引,因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供的数据检索
3. 使用聚簇索引的时候，数据查询效率高，但如果对数据进行插入、删除、更新等操作，效率表非聚簇索引低

行转列实现

查询所有的学生姓名、课程名称及分数

-- 查询所有的学生姓名、课程名称及分数 并进行行列转换（面试题）
SELECT
	stuName,
	cname,
	score 
FROM
	classes c
	INNER JOIN student s ON c.bjid = s.bjid
	INNER JOIN grade g ON g.stuid = s.stuId
	INNER JOIN course cc ON g.cid = cc.cid;

一个学生学习了多门课程,多门成绩,下一个目标将数据转换为报表

使用除了count 以外的聚合函数

-- 查询所有的学生姓名、课程名称及分数 并进行行列(推荐: SUM)转换（面试题）
-- 虽然其他聚合函数可以实现行转列,但是会出现一些额外的问题,这些问题都是不固定的，比如使用 avg 聚合函数实现如下,结果是出乎意料的
SELECT
	stuName,
	-- sum 此时的作用是行转列,查询 cname 是 HTML 的,有成绩显示成绩,没有成绩的显示 0
	sum( CASE WHEN cc.cname = 'HTML' THEN g.score ELSE 0 END ) AS 'HTML',
	sum( CASE WHEN cc.cname = 'JAVA' THEN g.score ELSE 0 END ) AS 'JAVA',
	sum( CASE WHEN cc.cname = 'SpringBoot' THEN g.score ELSE 0 END ) AS 'SpringBoot',
	sum( CASE WHEN cc.cname = 'MySQL' THEN g.score ELSE 0 END ) AS 'MySQL'
FROM
	classes c
	INNER JOIN student s ON c.bjid = s.bjid
	INNER JOIN grade g ON g.stuid = s.stuId
	INNER JOIN course cc ON g.cid = cc.cid 
GROUP BY
	stuName;

行转列

AVG 结果并非理想结果

SELECT
	stuName,
	-- sum 此时的作用是行转列,查询 cname 是 HTML 的,有成绩显示成绩,没有成绩的显示 0
	avg( CASE WHEN cc.cname = 'HTML' THEN g.score ELSE 0 END ) AS 'HTML',
	avg( CASE WHEN cc.cname = 'JAVA' THEN g.score ELSE 0 END ) AS 'JAVA',
	avg( CASE WHEN cc.cname = 'SpringBoot' THEN g.score ELSE 0 END ) AS 'SpringBoot',
	avg( CASE WHEN cc.cname = 'MySQL' THEN g.score ELSE 0 END ) AS 'MySQL'
FROM
	classes c
	INNER JOIN student s ON c.bjid = s.bjid
	INNER JOIN grade g ON g.stuid = s.stuId
	INNER JOIN course cc ON g.cid = cc.cid 
GROUP BY
	stuName;

非理想结果

总体表结构

classes

表结构

grade

表结构

course

表结构

student

表结构