MySQL

MySQL 概述

MySQL 背景

MySQL的前身是属于MySQL AB，08年被SUN公司收购，09年SUN公司又被Oracle公司收购

数据库常见概念

数据库常见分类

SQL语言的分类

数据库的作用

• 持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级用，数据持久化意味着将内存中的数据保存到硬盘上加固化，而持久化的实现过程大多通过各种关系数据库来完成。

• 持久化的主要作用：将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数

  据文件中。

数据库与数据库管理系统的关系

数据库管理系统(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存

应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。

MySQL 安装

版本：8.0

网址：www.mysql.com

个人练习下载社区版：免费

安装包版本选择

安装选项

安装完成

配置环境变量

例：下面是我自己的安装 bin 目录

Path D:\\Program Files\\MySQL\\MySQL Server 8.0\\bin

测试 CMD 登陆成功

MySQL 目录结构

MySQL 数据类型

查看数据库

show databases;

`

MySQL 编码设置

查看编码命令

show variables like \'character_%\';
show variables like \'collation_%\';

修改配置文件 my.ini

[mysql] # 大概在63行左右，在其下添加
...
default-character-set=utf8 #默认字符集

[mysqld] # 大概在76行左右，在其下添加
...
character-set-server=utf8 # 字符集
collation-server=utf8_general_ci # 比较规则

连接工具

# 使用mysql数据库
USE mysql;

# 修改\'root\'@\'localhost\'用户的密码规则和密码
ALTER USER \'root\'@\'localhost\' IDENTIFIED WITH mysql_native_password BY \'pwd\';

# 刷新权限
FLUSH PRIEGES;

# 查看表创建信息
show create table 表名\\G;

# 查看数据库创建信息
show create database 数据库名\\G;

# 删除修改之前建立的数据库和表，因为它们的编码还是latin1,或者使用下面语句修改

alter database 数据库名 charset utf8; # 修改数据库的字符编码为utf8
alter table student modify name varchar(20) charset utf8; # 修改表字段

导入SQL数据

mysql> source 文件全路径名;

SQL语言大小规范

MySQL 在 Windows 环境下是大小写不敏感的

MySQL 在 **Linux ** 环境下是大小写敏感的

• 数据库名、表名、表的别名、变量名是严格区分大小写的
• 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的

MySQL DQL语言

基础查询

/*
特点：
1.查询列表可以是字段、常量、函数、表达式
2.查询结果是一个虚拟表
3.SELECT 后面可以跟字段名，字面量
*/
SELECT field1，field2，field3... FROM tablename;

# 起别名
# 别名可以使用单引号、双引号引起来，当只有一个单词时，可以省略引号，当有多个单词且有空格或特殊符号时，不能省略，AS (alias) 可以省略
# 在所的数据库中，所有的字符串统一用单引号，单引号是标准，双引号在Oracle数据库中用不了，但是在MySql中可以用
# 中文在mysql里面自动识别了，加不加引号都一样可以运行。
# 列名不能在 where 后面用, 可以在 order by 后面用
SELECT field1 AS \'alias\' ,feield2 AS 字段2 FROM tablename;

# 字段去重 distinct 只能出现在所有字段的最前面， 有多个字段表示联合起来去重；
SELECT DISTINCT field1 FROM tablename;

# 两个字段联合去重
select distinct field1,field2 from tablename;

# 可以使用函数
select count(distinct(field)) from tablename;

# 查询常量
SELECT 常量值;

# 查询函数
SELECT 函数名(param);

# 查询表达式
SELECT 100/20;

# 伪表
SELECT * FROM DUAL;

# 数学运算 加 减 乘 除 都可以
SELECT 数值+数值 直接是运算
SELECT 字符+数值 首先将字符转换成数值类型（转换失败默认 0），在做运算
SELECT NULL+数值 值为null

# `` 着重号，有些定义名字是 数据库中关键字，需要要用

# LEAST() 取最小值, GREATEST() 取最大值
SELECT LEAST(\'a\',\'b\',\'c\') as \'min\' ,GREATEST(\'a\',\'b\',\'c\') as \'max\' ;

# 查看表结构 DESCRIBE
DESC 表名;

Field：表示字段名称。

Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的。

Null：表示该列是否可以存储NULL值。

Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一

部分；MUL表示在列中某个给定值允许出现多次。

Default：表示该列是否有默认值，如果有，那么值是多少。

Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

条件查询

• in 列表的值类型必须一致或兼容，in 列表中不支持通配符%和_
• =、!= 不能用来判断null (不会产生任何结果)、而 <=>、is null 、 is not null可以用来判断null
• <=> 也可以判断普通类型的数值 可以判断 null
• != 和 <> 都是判断不等于的意思，但是MySQL推荐使用 <>

/*
where 关键字后面可以加 字段名 使用以下运算法：
null 在数据库中不能使用等号衡量，因为他不是一个值，在数据库中代表什么也没有；

条件运算符：>、>=、<、<=、=、<=>、!=、<>
逻辑运算符：and &&、or ||、not !、 （and 和 or 同时出现时 and 优先级比 or 高，若需要 or
　　　　　　先执行就加小括号，以后不确定优先级 可以加小括号）
　　　　　　XOR 异或: 两个条件只能同时满足一个
模糊运算符：
like：%任意多个字符、_任意单个字符，如果有特殊字符，需要使用escape转义 ，\\ 转义
between and 必须遵循左小右大
in
is null
is not null
*/

SELECT * FROM employees WHERE id = 1;

SELECT * FROM employees WHERE salary > 12000

# 注意：!=和<>都是判断不等于的意思，但是MySQL推荐使用 <>
SELECT * FROM employees WHERE employee_id <> 100;
SELECT * FROM employees WHERE employee_id != 100;

# 查询员工编号在100到120之间的员工信息
SELECT * FROM employees WHERE employee_id > 100 AND employee_id < 120;

# between and 包含头和尾
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

# not between and 不包含头和尾
SELECT * FROM employees WHERE employee_id NOT BETWEEN 100 AND 120;

# 查询员工编号不在100到120之间的员工信息
SELECT * FROM employees WHERE employee_id NOT BETWEEN 100 AND 120;

# 查询员工的工种编号是 100,120,140中的员工名和工种编
# in列表的值类型必须一致或兼容，in列表中不支持通配符%和_
SELECT employee_id,first_name,job_id FROM employees WHERE employee_id IN (100,120,130,140);

# 查询没有奖金的员工名和奖金率
SELECT first_name FROM employees WHERE ISNULL(commission_pct);
SELECT first_name FROM employees WHERE commission_pct IS NULL;

# 查询有奖金的员工名和奖金率
SELECT first_name FROM employees WHERE commission_pct IS NOT NULL;

排序查询

• 没有排序，默认情况下返回的添加时候的顺序

• 排序列表可以是单个字段、多个字段、别名、函数、表达式

• asc代表升序，desc代表降序，如果不写，默认是asc

• order by的位置一般放在查询语句的最后（除limit语句之外）

# 按单个字段排序：查询员工信息，要求按工资降序
SELECT * FROM employees ORDER BY salary;

# 按多个字段查询：查询员工信息，要求先按工资降序，再按员工编号升
# 多个字段,分隔
SELECT * FROM employees ORDER BY salary , DESC employee_id ASC;

# sal 在前，起主导，只有sal相等时，才会考虑执行 ename
SELECT ename,sal FROM emp ORDER BY sal,ename ;

分页查询

# limit
# Oracle 中不支持 limit;
# 将查询结果集的一部分取出来，通常使用在分页查询当中。
# 完整用法：limit startIndex, length;
# 缺省用法：limit 5; 取前5
# mysql 当中limit在order by之后执行！
# 按照薪资降序，取出排名在前5名的员工？
SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 0,5;

# 2表示起始位置从下标2开始，就是第三条记录
# 3表示长度
SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 2,3;

# 查询邮箱中包含e的员工的信息，并且先按邮箱的字节数降序，在按部门号升序，前显示20条
select * from employees
where # emali like \'%e%\'
email regexp \'[e]\'
order by email desc,department_id asc
limit 20;

# 通用分页
# 每页显示3条记录
# 第1页：limit 0,3 [0 1 2]
# 第2页：limit 3,3 [3 4 5]
# 第3页：limit 6,3 [6 7 8]
# ... limit (page - 1) * pageSize , pageSize
# 每页显示 pageSize 条记录
# 第 pageNo 页： limit (page -1) * pageSize , pageSize

连接查询

• 多张表联合起来查询数据；
• 表关系：一对一，一对多，多对多，自连接
• 语法：92语法，99语法；
• 连接方式：内连接（等值连接，非等值连接，自连接），外连接（左外连接 - 左连接，右外连接 - 右连接），全连接；
• 我们要控制连接表的数量，多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。
• 需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。

# 连接查询
# 92语法
# 当两张表进行连接查询时，没有任何条件限制发什么现象;
# 例；查询每个员工所在部门名称 （最终查询结果条数，是两张表的乘积，这种现象被称为笛卡尔积现象）;
SELECT ename,dname FROM emp,dept;

# 怎么避免笛卡尔积，连接查询是加条件，满足条件的记录被筛选出来;
# 思考：没有条件和有条件时候，查询次数有减少吗？（没有 都是56次，只不过4选1）;
# 通过笛卡尔积的出现，表连接的次数越多效率越低，尽量避免表连接的次数;
# 有 n 个表连接少需要 n-1 个条件
SELECT ename,dname FROM emp a,dept b WHERE a.deptno = b.`DEPTNO`;

# 比上面的效率会优一点，（上面的字段会去两个表中去查询）;
SELECT a.ename,b.dname FROM emp a,dept b WHERE a.deptno = b.`DEPTNO`;

内连接

# 内连接
# 特点：内连接完全能够匹配上条件的查出来
# 内连接 - 等值连接 （条件作为等量关系）：
# 例：查询每个员工所在部门名称，显示员工名和部门名？
# 92 缺点：结构不清晰，表连接条件和后期进一步筛选条件，都发在了 where 后面；
SELECT a.ename,b.dname FROM emp a,dept b WHERE a.deptno = b.`DEPTNO`;

# 99 优点：表连接的条件是独立的，连接之后，如果需要进一步筛选，在添加 where;
SELECT a.ename,b.dname FROM emp a JOIN dept b ON a.`DEPTNO` = b.`DEPTNO`;

# INNER 可以省略，带着INNER 可读性更好
SELECT a.ename,b.dname FROM emp a INNER JOIN dept b ON a.`DEPTNO` = b.`DEPTNO`;

# 内连接 - 非等值连接 （条件非等量关系）：
# 例：找出每个员工的薪资等级，要求显示员工名，薪资，薪资等级
SELECT a.`ENAME`,a.`SAL`,b.`GRADE` FROM emp a INNER JOIN salgrade b ON a.`SAL` BETWEEN b.`LOSAL` AND b.`HISAL`;

# 内连接 - 自连接（技巧：一张表看出两张表）
# 例：查询员工的上级领导，要求显示员工名和对应的领导名;
SELECT a.`ENAME`,a.`ENAME`,a.`MGR`,b.`ENAME`,b.`JOB` FROM emp a INNER JOIN emp b ON a.`MGR` = b.`EMPNO`;

# NATURAL JOIN 会自动查询两张连接表中 所有相同的字段，然后进行等值连接 （了解）
select a.employee_id,b.employee_id,a.last_name,b.last_name from employees a natural join employees b;

# USING SQL99语法的新特性,USING指定具有两个相同的字段名称，
select a.employee_id,b.employee_id,a.last_name,b.last_name from employees a join employees b using(department_id);

外连接

# 外连接
# 外连接 - 右外连接 - 右连接
# righti 表示将 join 右边的的这张表看成主表，主要是为了将张表的数据全查出来，捎带关联查询左边的表
# left 左连接同理
# outer 可以省略，在外连接中两张表产生了主次关系
SELECT
a.`ENAME`,
b.`DNAME`
FROM
emp a
RIGHT OUTER JOIN dept b ON a.`DEPTNO` = b.`DEPTNO`;

SELECT
a.`ENAME`,
b.`ENAME`
FROM
emp a
LEFT JOIN emp b ON a.`MGR` = b.`EMPNO`;

# 一条SQL中内连接和外连接可以混合用，都可以出现
# 例：找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名，部门名，薪资，薪资等级
SELECT
a.`ENAME`,
a1.`ENAME`,
b.`DNAME`,
a.`SAL`,
c.`GRADE`
FROM
emp a
JOIN dept b ON a.`DEPTNO` = b.`DEPTNO`
LEFT JOIN salgrade c ON a.`SAL` BETWEEN c.`LOSAL`
AND c.`HISAL`
LEFT JOIN emp a1 ON a.`MGR` = a1.`EMPNO`;

子查询

# 子查询
# select 中嵌套 select 语句，被嵌套的 select 语句称为子查询
# 子查询可以出现位置：select 后面，from 后面，where 后面
# select 后面的子查询，结果集不能大于1条
# from 后面的子查询，可以将子查询看做一张临时表

# 例：找出比最低员工工资高的员工
SELECT
ENAME,SAL
FROM
emp
WHERE
SAL > ( SELECT min( sal ) FROM emp );

# 找出每个岗位的平均工资的薪资等级
SELECT
a.job,a.avgsal,b.GRADE
FROM (SELECT job, avg( SAL ) AS \'avgsal\' FROM emp GROUP BY JOB) as a
LEFT JOIN salgrade b ON a.avgsal BETWEEN b.LOSAL AND b.HISAL;

union

# union 合并查询结果集
# 例：查询工作岗位是MANAGER和SALESMAN的员工
# union 的效率要高一些，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻
# 但是union可以减少匹配的次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接
# a 连接 b 连接 c, a 10条记录，b 10条记录，c 10条记录 匹配次数是：1000
# a 连接 b 一个结果：10 * 10 --> 100 次；a 连接 c 一个结果：10 * 10 --> 100 次；
# 使用union的话是：100次 + 100次 = 200次；
# 注意：使用 UNION 两张表的字段类型和数量必须相同
# 1
select ENAME,JOB from emp where job = \'MANAGER\' or job = \'SALESMAN\';
# 2
select ENAME,JOB from emp where job in (\'MANAGER\',\'SALESMAN\');
# 3
select ENAME,JOB from emp where job = \'MANAGER\'
union
select ENAME,JOB from emp where job = \'SALESMAN\';

数据处理函数

• 数据处理函数 又称 单行处理函数，特点：一个输入，对应1个输出；
• 和单行处理函数对应的是：多行处理函数，特点： 多个输入，对应1个输出；

单行函数

# 单行处理函数
# lower 转换小写
SELECT LOWER(ename) AS ename FROM emp ;

# upper 转换大写
SELECT UPPER(ename) AS ename FROM emp ;

# substr(srcStr, startIndex, length) 取子串 起始索引从 1 开始 没有0
SELECT SUBSTR(ename,1,2) AS ename FROM emp ;
SELECT ename FROM emp WHERE SUBSTR(ename,1,1) = \'A\';

# concat 字符串拼接
# 首字母大写
SELECT CONCAT(UPPER(SUBSTR(ename,1,1)),TRIM(LOWER(SUBSTR(ename,2,LENGTH(ename) - 1)))) AS ename FROM emp;

# length 取长度

# trim 去前后空格

# str_to_date 将字符串转换成日期
# MySql 日期格式：%Y 年，%m 月，%d 日，%h 时，%i 分，%s 秒 ，MySql 默认格式：%Y-%m-%d
str_to_date(strDate,格式化)
# data_format 格式化日期 这个函数可以将日期类型转换成特定格式的字符串
data_format(date,格式化）

# format 设置千分位，数字格式化
SELECT format(sal,\'$999.999\')

# round 四舍五入
SELECT ROUND(123.39) AS resutl FROM emp;
SELECT ROUND(123.561,1) AS result FROM emp;

# rand 生成随机数
SELECT RAND() FROM emp;
# 100 以内的随机数
SELECT ROUND(RAND() * 100) FROM emp;

# ifnull 可以将 null 转换成一个具体的值，空处理函数, null 值参与运算 值为 null
SELECT ename,(sal * IFNULL(comm,0)) * 12 AS yearsal FROM emp;

# case when then when then else end
# 当员工的工作岗位是 MANAGER 的时候工资上调 10% , 当工作岗位是 SALESMAN 的时候，工资上调50%，其他正常；
SELECT
ename,job,( CASE job WHEN \'MANAAGER\' THEN sal * 1.1 WHEN \'SALESMAN\' THEN sal * 1.5 ELSE sal END ) newsal
FROM
emp;

多行函数

# 分组函数（多行处理函数）
# 注意：分組函數使用時必須先進行分組，然後才能用，如沒有分組，整張表默認為一組；
# 注意：分組函數會自動忽略 NULL 值，不許要提前對 null 處理；
# 注意：分組函數 不能直接使用在 where 子句中；

# count
SELECT COUNT(comm) FROM emp;
# sum

SELECT SUM(comm) FROM emp;
# max

# min

# avg

# 可以合起來一起使用
SELECT COUNT(sal),SUM(sal),AVG(sal),MAX(sal),MIN(sal) FROM emp;

分组查询

• 这些关键字只能按照这个顺序：SELECT FROM WHERE GROPU BY HAVING ORDER BY；
• 执行顺序：FROM > WHERE > GRUOP BY > HAVING > SELECT > ORDER BY；
• 从某张表中查询数据，先经过 WHERE 条件筛选出有价值的数据，在对数据进行分组，分组之后可以使用 HAVING 继续筛选，SELECT 查询出来，最后排序；
• 能用 WHERE 和 HAVING, 优先选择WHERE，WHERE 完成不了的，在考虑 HAVING;
  GROUP BY 后面可以根据多个字段分组；
• SELECT 后面只能出现 分组函数 和 GROUP BY 后面出现的 字段；

# 找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排；
SELECT job, AVG(sal) AS \'avgsal\' FROM emp
WHERE
job <> \'MANAGER\'
GROUP BY
job
HAVING
AVG(sal) > 1500;
ORDER BY
avgsal DESC;

# 当过滤条件中聚合函数时，则此过滤条件必须写在HAVING后面,当过滤条件没有聚合函数时，过滤条件可以在WHERE 或 HAVING 后面，建议写在 WHERE后面;

# WITH ROLLUP 多生成一条记录，分组后的总和，和 ORDER BY 慎重使用 报错
select department_id,avg(salary),sum(salary),max(salary) from employees where department_id is not null group by department_id with rollup;

SQL 执行过程

FROM > ON > LEFT/RIGHT JOIN> WHERE > GRUOP BY > HAVING > SELECT >DISTINCT> ORDER BY>LIMIT；

SELECT DISTINCT player_id, player_name, COUNT(*) AS num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

# 在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

SQL 的执行原理

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得
到是我们的原始数据。

来源：https://www.cnblogs.com/oioi/p/15015275.html
图文来源于网络，如有侵权请联系删除。