MySQL①

用户180554289221
148 阅读11分钟

概括

表关联关系

一对一关联、一对多关联、多对多关联、自我引用

SQL的分类

分三条主线

DDL 数据定义语言

针对表
CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE

DML 数据操作语言

针对记录
INSERT \ DELECT \ UPDATE \ SELECT(DQL,查询)

DCL 数据控制语言

针对事务、权限
COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE

SQL语言规则与规范

MySQL 在 Linux 环境下是大小写敏感的
数据库名、表名、表的别名、变量名是严格区分大小写的
关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。

推荐采用统一的书写规范：
数据库名、表名、表别名、字段名、字段别名等都小写
SQL 关键字、函数名、绑定变量等都大写

每条命令以 ; 或 \g 或 \G 结束
单条语句最后可不写；，但多条语句时必须写；

字符串型和日期时间类型的数据可以使用单引号（' '）表示
列的别名，尽量使用双引号（" "），而且不建议省略as

注释

单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */

数据导入指令

source+全路径，也可以执行SQL脚本

mysql> source d:\mysqldb.sql
mysql> desc employees;

SELECT

AS 别名

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

SELECT last_name AS name, commission_pct comm
FROM employees;

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

salary*12 Annual Salary只会把Annual作为别名而多了Salary这个关键字，因此必须使用“”
必须使用双引号！！！！字符串是单引号！！！！

DISTINCT 去除重复行

# 没有意义，这是组合去重
SELECT DISTINCT department_id,salary
FROM employees;

# 报错，因为salary全查出来了
SELECT salary, DISTINCT department_id
FROM employees;

null 空值

null不等同与0，空值参与运算就为空值，但是计算结果不是0，可以用函数IFNULL，可用0替换null

'' 着重号

关键字比如ORDER表，就是转义号。

常数

在查询结果中加入一列常数

DESCRIBE或DESC 命令显示表结构

where 过滤

运算符

算术运算符

+

在MySQL中+没有连接的作用，只能做加法
CONCAT是拼接关键字

/

结果全是浮点型

%

正负只与模数有关，与被模数无关

比较运算符

= <=>

没有赋值所以=就是=了
a与数值比较会隐式转换为0，所以0 = 'a' -> 1，但字符串比较就不会转换了。
null参与比较结果就是null,<=>安全等于就会辨别出null了，null <=> null -> 1

LEAST GREATEST

比较字段长度时需要用LENGTH(字段)

BETWEEN AND

包含边界且上下界必须顺序填写
NOT BETWEEN AND

LIKE 模糊查询

%代表0~多个字符 _一个不确定的字符

  • 若要匹配‘_’怎么办？ 使用转义字符‘/’也可以'_$_' ESCAPE '$';

正则表达式

（1）‘^’匹配以该字符后面的字符开头的字符串。

（2）‘$’匹配以该字符前面的字符结尾的字符串。

（3）‘.’匹配任何一个单字符。

（4）“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。

（5）‘’匹配零个或多个在它前面的字符。例如，“x”匹配任何数量的‘x’字符，“[0-9]”匹配任何数量的数字，而“”匹配任何数量的任何字符。

逻辑运算符

与或非

位运算符

优先级（遵从内心，背太难了！）

image.png

排序与分页

ORDER BY 排序

默认ASC，降序DESC
SELECT中设置列别名只能在ORDER BY中使用，不能在WHERE中使用，因为WHERE在别名前就执行了，而且ORDER BY必须在WHERE后面。 ORDER BY 字段1 DESC, 字段2 ASC

LIMIT 分页

LIMIT {pageNo - 1}*pageSize, pageSize;
WHERE / OREDER BY / LIMIT的声明顺序：WHERE / ORDER BY / LIMIT\

  • MySQL 8.0的新特性 MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录

约束返回结果的数量可以 减少数据表的网络传输量 ，也可以 提升查询效率 。如果我们知道返回结果只有1 条，就可以使用 LIMIT 1 ，告诉SELECT语句只需要返回一条记录即可。这样的好处就是SELECT不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

多表查询

笛卡尔积（或交叉连接）的理解

image.png

一定要有连接条件才能避免上述结果。
注意null数据！

在sql优化的角度建议多表查询时，每个字段都指明其所在的表！（这样省去执行时遍历每个表字段这个步骤）

可以给表起别名，在SELCET和WHERE中使用表的别名

等值连接 vs 非等值连接

WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接 vs 非自连接

SELECT CONCAT(worker.last_name ,' works for ', manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

内连接 vs 外连接

外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

假如说了所有一定是外连接！

MySQL只支持SQL99语法（join on）

join on内外连接都可以

内连接

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

外连接

左外连接

左满，右补null

右外连接

右满，左补null

满外连接

左满右满
需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。
使用集合！！

image.png

左中图和右上图结合实现满外连接的情况会更高效。

左上图：左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.'department_id' = d.`department_id`;

右上图：右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d 
ON e.'department_id' = d.`department_id`;

左中图：
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.'department_id' = d.`department_id`
WHERE d.`department_id` IS NULL;

右中图：
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d 
ON e.'department_id' = d.`department_id`
WHERE e.'department_id' IS NULL;

左下图：满外连接
方式1：左上图 UNION 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.'department_id' = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d 
ON e.'department_id' = d.`department_id`
WHERE e.'department_id' IS NULL;
方式2：右上图 UNION 左中图

右下图：
左中图 UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.'department_id' = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d 
ON e.'department_id' = d.`department_id`
WHERE e.'department_id' IS NULL;

99语法新特性

NATURAL 自然连接

你自动查询两张连接表中 所有相同的字段 ，然后进行等值连接 。

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING 连接

使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。不适用于自连接！

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

总结

表连接的约束条件可以有三种方式：WHERE, ON, USING

  • WHERE：适用于所有关联查询
  • ON：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
  • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

image.png

相当于嵌套for，三层太少见了！

函数

内置函数和自定义函数

单行函数

单行函数：\

  • 操作数据对象
  • 接受参数返回一个结果
  • 只对一行进行变换
  • 每行返回一个结果
  • 可以嵌套
  • 参数可以是一列或一个值

image.png

字符串

image.png

image.png

image.png

SQL字符串索引从1开始

时间函数

image.png image.png image.png image.png image.png image.png image.png

image.png

流程控制

image.png

SELECT last_name,salary,IF(salary >= 6000,'高工资'，'低工资')"details"
FROM employees;

SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0)"annul_sal"
FROM employees;

SELECT last_name,commission_pct,IFNULL(commission_pct,0)"details"
FROM employees;

SELECT last_name,salary,CASE WHEN salary>=15000 THEN '高薪'
                             WHEN salary>=10000 THEN '潜力股'
                             WHEN salary>=8000 THEN '屌丝'
                             ELSE '草根' END "描述"
FROM employees;

加密与解密

image.png

MD5、SHA不可逆

除了PASSWORD都在8.0版本中被弃用了。

信息函数

image.png

其他函数

image.png

聚合函数（多行函数）

输入一组数据的集合但输出一个结果

常见的聚合函数

聚合函数类型

  • AVG()
  • SUM()
  • MAX()
  • MIN()
  • COUNT()

image.png

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

SELECT MIN(hire_date), MAX(hire_date)
FROM employees;

AVG() \ SUM()假如操作字符串那都返回0，毫无意义,只作用于数值。
MAX() \ MIN()作用于字符串和数值

  • COUNT(*)

  • COUNT(1)

  • COUNT(具体字段)：不计算空值！！！

  • AVG() = SUM() / COUNT()

这些都会过滤null值！！！

  • AVG() = SUM() / COUNT(IFNULL(字段，0))

问题：用count(*)，count(1)，count(列名)谁好呢?

其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为Innodb真的要去数一遍。但好于具体的count(列名)。

问题：能不能使用count(列名)替换count(*)?

不要使用 count(列名)来替代count(*)， count(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
说明：count(*)会统计值为NULL的行，而 count(列名)不会统计此列为 NULL 值的行。

GROUP BY的使用

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中
包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

GROUP BY声明在FROM / WHERE后，ORDER BY / LIMIT前

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

HAVING的使用

过滤分组：HAVING子句

  1. 行已经被分组。
  2. 使用了聚合函数必须使用HAVING子句。
  3. 满足HAVING子句中条件的分组将被显示。
  4. HAVING不能单独使用，必须要跟 GROUP BY 一起使用。

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

#方式一：

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

#方式二：自连接

SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`

#方式三：子查询

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

子查询的分类

单行子查询 vs 多行子查询

()内查询返回结果是单行还是多行

单行子查询

子查询编写技巧：从里往外，从外往里

单行子查询比较操作符

image.png

HAVING 中的子查询
  • 首先执行子查询。
  • 向主查询中的HAVING 子句返回结果。 题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
                    (SELECT MIN(salary)
                    FROM employees
                    WHERE department_id = 50);
CASE中的子查询

题目：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。

SELECT employee_id, last_name,
            (CASE department_id
            WHEN
                (SELECT department_id FROM departments
                WHERE location_id = 1800)
            THEN 'Canada' ELSE 'USA' END) location
FROM employees;
单行子查询的特殊情况
  • 子查询中的空值问题：不报错但没结果
  • 非法使用子查询：子查询多行结果匹配单行子查询操作符
SELECT employee_id, last_name
FROM employees
WHERE salary =
            (SELECT MIN(salary)
            FROM employees
            GROUP BY department_id);
多行子查询
  • 也称为集合比较子查询
  • 内查询返回多行
  • 使用多行比较操作符
多行子查询比较操作符

image.png

空值问题
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
                        SELECT manager_id
                        FROM employees
                        # WHERE manager_id is not NULL;
                        );

相关子查询 vs 不相关子查询

相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。 image.png

image.png

EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。 如果在子查询中不存在满足条件的行：

  • 条件返回 FALSE
  • 继续在子查询中查找

如果在子查询中存在满足条件的行：

  • 不在子查询中继续查找
  • 条件返回 TRUE

NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

  • 查询字段设置别名
SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

当出现此种情况时AVG(salary) dept_avg_sal必须取一个别名将其固定下来！

avatar
文章
阅读
粉丝