MySQL基础笔记

基本

一、常见概念

二、MySQL语法规范

单行注释：#注释文字
单行注释：--注释文字
多行注释：/*注释文字*/

三、服务启动/登录

启动服务：net start 服务名
停止服务：net stop 服务名

退出：exit
登录：mysql [-h 主机名 -p端口号] -u 用户名 -p密码
/*端口号与p之间无空格*/

四、常见命令

SHOW DATABASES; # 查看所有数据库
USE 库名 # 打开指定库
SHOW TABLES; # 查看当前库所有表
SHOW TABLES FROM 库名; # 查看指定库所有表
DESC 表名; # 查看表结构
SELECT VERSION(); # 查看服务器版本
SHOW ENGINES; # 查看存储引擎
SELECT @@tx_isolation; # 查看隔离界别（8.0之前）
SELECT @@transaction_isolation; # 隔离级别（8.0之后）
SHOW CREATE TABLE 表名 # 查看表创建过程
SHOW CREATE VIEW 视图名 # 查看视图创建过程
SHOW PROCEDURE 存储过程名 # 查看存储过程创建过程

DQL语言

含义：数据查询语言 Data Query Language

一、基础查询

• SELECT

  特点

• 查询列表可以是：字段、常量值、表达式、函数
• 查询结果是一个虚拟表格

SELECT 字段 FROM 表; # 查询单个字段
SELECT 字段1, 字段2 FROM 表; # 查询多个字段
SELECT * FROM 表; # 查询所有字段
/* 注：`XX`（着重号，非单引号）代表XX为字段名 */

SELECT 常量值/表达式/函数;
/* 例子 */
SELECT 100;
SELECT 100%3;
SELECT VERSION();

(1) 便于理解
(2) 查询字段若有重名可以用别名区分

SELECT 字段 AS 别名 FROM 表; # 用AS
SELECT 字段 别名 FROM 表; # 用空格
/* 注：别名含有特殊符号或关键字时，添加双引号或单引号 */

字段前加 DISTINCT 即可结果去重

SELECT DISTINCT 字段 FROM 表;

(1) 两个操作数都为数值型做加法运算
(2) 其中一个为 NULL 则结果为 NULL
(3) 其中一个为字符型，则视图转换为数值型

SELECT \'12\'+1; # 转换成功做加法运算
SELECT \'A\'+1; # 转换失败，将字符型转换成0

二、条件查询

SELECT 查询列表 FORM 表 WHERE 筛选条件

• 按条件表达式筛选
  条件运算符：> < = != <> >= <=

• 按表达式筛选
  逻辑运算符：&& || ! and or not

• 模糊查询

LIKE
/*
一般搭配通配符使用，前面可加NOT
通配符：
% 代表任意多个字符（包括0个）
- 代表任意单个字符
注：若查询名字中包含通配符的字段需要用 \\ 转义
ESCAPE可自定义转义字符
*/
BETWEEN AND
/* BETWEEN A AND B 包含 A 和 B */
IN
/* IN(值1, 值2, ...) 列表的值必须一致或兼容 */
IS NULL/IS NOT NULL
/* = 和 <> 不能用于判断NULL值，需要用IS NULL/IS NOT NULL */
/* 注：<=> 为安全等于，可以判断NULL和普通数值 */

三、排序查询

SELECT 字段 FROM 表 [WHERE 条件]
ORDER BY 排序列表 ASC|DESC
/* ASC 升序 DESC 降序 不写默认升序 */

• 排序列表可以是表达式或函数
• 排序列表可设置多个，按列表先后顺序排序
• 排序列表可写别名
• ODER BY 语句在其他语句后面 LIMIT 语句前面

四、常见函数

五、分组函数

六、分组查询

SELECT 查询列表 FROM 表
GROUP BY 分组列表
[HAVING 筛选条件]
/* HAVING 可用于分组后数据筛选 */

• 分组前筛选用 WHERE 放 GROUP BY 前面
  分组后筛选用 HAVING 放 GROUP BY 后面

• 支持单个字段分组，多个字段分组（逗号隔开），表达式分组

七、连接查询

• 连接查询又称多表查询

• 笛卡尔乘积现象：表1 m 行 表2 n 行，结果 m*n 行
  发生原因：没有有效的连接条件

• 连接查询分类

  按年代分类
  按功能分类
  

  sql92 标准：内连接	内连接： 等值连接 非等值连接 自连接
  sql99 标准：MySQL支持 内连接、外连接（左外 右外）、交叉连接	外连接： 左外连接 右外连接 全外连接
  	交叉连接（笛卡尔积）

SQL92 标准

• 特点
  (1) 多表等值连接结果为多表的交集部分
  (2) n表连接至少需要n-1个连接条件
  (3) 一般需要为表起别名
  (4) 可以使用筛选、排序、分组等
  (5) 多表顺序无要求

案例：查询员工名、工种号、工种名

SELECT e.job_id, j.job_id, j.job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id;

案例：查询员工的工资和工资级别

SELECT salary, grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.lowest AND g.highest;

• 特点：一张表当作多张表使用（用别名）

案例：查询员工及其领导名字

SELECT e.name 员工, m.name 领导
FROM employees e, employees m
WHERE e.`manager_id` IS NOT NULL
AND e.`manager_id` = m.`employee_id`

SQL99 标准

SELECT 查询列表
FROM 表1 别名 [连接类型]
JOIN 表2 别名
ON 连接条件
[WHERE 筛选条件]

• 特点：
  (1) INNER 可以省略
  (2) JOIN 连接和 SQL92 标准中连接效果一样
  (3) 连接条件放 ON 后面
  (4) 除连接写法不同外，其他同 SQL92

案例：查询员工及其领导名字（自连接）

SELECT e.name 员工, m.name 领导
FROM employees e, employees m
WHERE e.`manager_id` IS NOT NULL
AND e.`manager_id` = m.`employee_id`

应用场景：查询一个表中有，另一个表中无的记录（相对补集）

• 特点：
  (1) 外连接分主从表
  (2) 外连接的查询结果为主表的所有记录
  若从表中有与主表相匹配的，则显示匹配值，否则显示NULL
  (3) 左外连接：LEFT JOIN 左边为主表
  右外连接：RIGHT JOIN 右边为主表

查询结果即笛卡尔乘积

八、子查询

• 含义
  出现在其他语句中的 SELECT 语句，称为子查询或内查询
  外部的查询语句，称为主查询或外查询

• 分类

按结果集的行列数

按子查询出现位置

• 子查询优先于主查询
• 子查询放在小括号内
• 子查询一般放在条件的右侧
• 标量子查询，一般搭配单行操作符使用 > < = 等
• 列子查询，一般搭配多行操作符使用 IN ANY/SOME ALL

• 标量子查询（单行子查询）
  案例：查询比 Abel 工资高的员工

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

• 列子查询（多行子查询）
  案例：返回 location_id 为1400或1700的部门的员工

SELECT name FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400, 1700)
);

• 行子查询（多列子查询）
  案例：查询 id 最小且工资最高的员工

SELECT * FROM employees
WHERE (employee_id, salary) = (
SELECT MIN(employee_id), MAX(salary)
FROM employees
);

• 标量子查询
  案例：查询每个部门信息及其员工个数

SELECT d.*, (
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.department_id
)
FROM departments d;

• 表子查询：将查询结果作为一张表，必须起别名

案例：查询每个部门平均工资的工资等级

SELECT ag_dep.*, g.grade_level
FROM (
SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id
) ag_dep # 此处必须为表起别名
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN g.lowest AND g.highest;

• 结果不为空 返回 1 结果为空 返回 0
  案例：查询有员工的部门

SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id = e.department_id
);
/* 注：EXISTS 通常可用 IN 替代 */

九、分页查询

应用场景：当显示数据一页显示不全，需要分页提交sql请求

SELECT 查询列表 FROM 表
...
LIMIT offset, size;
/* 注：offset: 起始索引（从0开始） size: 每页条目数 */

• LIMIT 语句放在所有语句的后面
• 要显示的页数 page，每页条目数 size
  offset = (page-1)*size

十、联合查询

应用场景：查询结果来自多个表，查询信息一致

查询语句1
UNION
查询语句2
UNION
...
/* 默认去重，使用 UNION ALL 显示重复项 */

• 要求查询语句结果列数一致
• 要求查询语句的每一列类型和顺序一致
• UNION 默认去重，使用 UNION ALL 可包含重复项

DML 语言

含义：数据操作语言 Data Manipularion Language

一、插入语句

语法一:

INSERT INTO 表[(字段1, ...)] VALUES(值1, ...);

(1) 插入的值类型与字段的类型一致或兼容
(2) 不可为NULL的列必须插入值
(3) 列的顺序可以调换
(4) 列数和值数必须一致
(5) 可以省略字段，默认为所有列，字段顺序和表中顺序一致

语法二：

INSERT INTO 表 SET 字段1 = 值1, …, 字段n = 值n;

两种方式对比：

• 语法一支持多行插入，语法二不支持
  VALUES(...), (...), (...)
• 语法一支持子查询，可插入查询结果，语法二不支持
  INSERT INTO 表(字段, ...) SELECT ...;

二、修改语句

UPDATE 表 SET 字段1 = 新值, 字段2 = 新值, ...
[WHERE 筛选条件]

/* SQL99 */
UPDATE 表1 别名
连接类型 JOIN 表2 别名
ON 连接条件
SET 字段 = 新值, ...
[WHERE 筛选条件]

三、删除语句

DELETE FROM 表 [WHERE 筛选条件]
/* 多表删除类似于 UPDATE */

TRUNCATE TABLE 表; # 清空表中数据

• DELETE 可以加 WHERE 条件，TRUNCATE 不能加
• TRUNCATE 删除效率略高于 DELETE
• 若要删除的表中有自增长列，DELETE 删除后再插入数据，自增长列的值从断点开始；TRUNCATE 删除后，再插入数据，自增长从1开始
• DELETE 有返回值（有几行受影响），TRUNCATE 无返回值
• DELETE 删除在事务中能回滚，TRUNCATE 删除不能回滚

DDL 语言

含义：数据定义语言 Data Definition Language

一、库的管理

CREATE DATABASE 库;
CREATE DATABASE IF NOT EXISTS 库; # 防止重复创建报错

/* 一般库不进行修改 */
ALTER DATABASE 库 CHARACTER SET 字符集; # 修改字符集

DROP DATABASE 库;
DROP DATABASE IF EXISTS 库; # 防止不存在时删除报错

二、表的管理

CREATE TABLE 表(
字段 字段类型 [长度 约束],
…
);

/* 修改表名 */
ALTER TABLE 表 RENAME TO 新表名;
/* 修改字段名 （COLUMN 可以省略，同时可更改字段类型）*/
ALTER TABLE 表 CHANGE COLUMN 字段 新字段 字段类型;
/* 修改字段名 */
ALTER TABLE 表 MODIFY COLUMN 字段 新类型 [约束];
/* 添加字段 */
ALTER TABLE 表 ADD COLUMN 字段 类型;
/* 删除字段 */
ALTER TABLE 表 DROP COLUMN 字段;

/* 表的删除 */
DROP TABLE 表;
DROP TABLE IF EXISTS 表;

/* 复制表的结构 */
CREATE TABLE 表 LIKE 复制的表;
/* 复制表的部分结构 */
CREATE TABLE 表
SELECT 字段1, 字段2, …
FROM 复制的表
WHERE 不成立的条件（如0）
/* 复制表的结构和数据 */
CREATE TABLE 表
SELECT * # 可以复制全部或部分数据
FROM 复制的表

三、常见数据类型

整型

• 特点：
  (1) 类型后加 UNSIGNED 可设置为无符号
  (2) 若不设置长度，则使用默认长度
  (3) 长度代表显示的最大宽度，若不够使用0左填充，使用 ZEROFILL 可以显示填充的0（使用了 ZEROFILL 会变为无符号）

小数

• 特点：
  (1) M：整数位数+小数位数 D：小数位数
  (2) (M, D) 可省略， DECIMAL 的 M 默认为10，D 默认为0
  (3) 定点型精确度较高，插入数据精度要求高则优先定点型

较短字符串

• 特点：
  (1) M 代表最大字符数（非字节数）
  (2) CHAR 为固定长度字符串 效率高，VARCHAR 可变长度字符串 效率低
  (3) CHAR 的 M 可省略，默认为1，VARCHAR 的 M 不可省略

• 其他：
  (1) TEXT 用于较长文本
  (2) BLOB 用于较大二进制（如图片）
  (3) BINARY 和 VARBINARY 用于较短二进制
  (4) ENUM 用于保存枚举
  (5) SET 用于保存集合

• 特点：
  TIMESTAMP 和实际时区有关，更能反应实际的日期，DATETIME 则只能反应出插入时的当地时区，TIMESTAMP 受MySQL 版本和 SQLMode 的影响很大

四、常见约束

约束：一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性

• 外键
  (1) 要求在从表上设置外键
  (2) 主表的关联列必须是一个key（主键或唯一）
  (3) 插入数据时，先插入主表再插入从表，删除时先删除从表再删除主表

• 主键与唯一键

唯一性
是否允许为空
一个表中数量

• 又称为又称为自增长列，可以不用手动插入值，系统提供默认的序列值

设置标识列：AUTO_INCREMENT

更改标识列步长：SET auto_increment_increment = 值

• 特点
  (1) 标识列必须和Key一起使用
  (2) 一个表中最多一个标识列
  (3) 标识列的类型只能是数值型
  (4) 可设置步长，手动插入值设置起始值

列级约束

在创建或求改表时类型的后面添加，六个约束语法上都支持，但外键约束无效果

表级约束

在所有字段最下面，非空和默认不支持表级约束

[CONSTRAINT 约束名] 约束类型(字段);
/* 不设置约束名时默认为字段名，主键设置约束名无效果 */
/* 添加外键 */
ALTER TABLE 从表 ADD CONSTRAINT 外键名
FOREIGN KEY(字段) REFERENCES 主表(字段);

CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型 列级约束,
表级约束
);

修改 / 添加

/* 列级约束 */
ALTER TABLE 表 MODIFY COLUMN 字段 类型 约束;
/* 表级约束 */
ALTER TABLE 表 ADD [CONSTRAINT 约束名] 约束(字段);

删除

/* 删除非空 */
ALTER TABLE 表 MODIFY COLUMN 字段 类型 NULL;
/* 删除默认 */
ALTER TABLE 表 MODIFY COLUMN 字段 类型;
/* 删除主键 */
ALTER TABLE 表 DROP PRIMARY KEY;
/* 删除唯一键 */
ALTER TABLE 表 DROP INDEX 字段;
/* 删除外键 */
ALTER TABLE 表 DROP FOREIGN KEY 外键名;

级联删除：
在添加外键语句后添加ON DELETE CASCADE设置级联删除
删除主表记录的同时删除从表相关联记录

级联置空：
在添加外键语句后添加ON DELETE SET NULL设置级联置空
删除主表记录的同时将从表相关联记录的值置为 NULL

TCL 语言

含义：事务控制语言 Transaction Control Language

一、事务

• 事务：事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的

• 存储引擎：mysql中用的最多的存储引擎有：innodb, myisam, memory等，其中innodb支持事务，其他不支持

• 原子性(Atomicity)：一个事务不可再分割，要么执行要么都不执行
• 一致性(Consistency)：一个事务执行会使数据从一个一致状态切换到另一个一致状态
• 隔离性(Isolation)：一个事务的执一个事务一旦提交，则会永久改变数据库行不受到其他事务的干扰
• 持久性(Durability)：一个事务一旦提交，则会永久改变数据库

二、事务处理

隐式事务

• 事务没有明显的开启和结束的标记，自动提交 如：insert update delete语句

显式事务

• 前提必须先设置自动提交功能为禁用SET autocommit = 0

/* 步骤1：开启事务 */
SET autocommit = 0;
START TRANSACTION; # 此语句可省略

/* 步骤2：编写事务中的 SQL 语句
如 SELECT INSERT UPDATE DELETE etc. */

/* 步骤3：结束事务（提交或回滚） */
COMMIT; # 提交事务
ROLLBACK; # 回滚事务

SAVEPOINT的使用：
事务中可设置保存点用于回滚

SAVEPOINT a; # 设置保存点 a
ROLLBACK TO a; # 回滚到保存点 a

• 同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：
  (1) 脏读
  (2) 不可重复读
  (3) 幻读

脏读
不可重复读
幻读

设置当前 mysql 连接的隔离级别
SET TRANSACTION ISOLATION LEVEL 隔离级别;
设置数据库系统的全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;

• 注意
  (1) Mysql 默认的事务隔离级别为：REPEATABLE READ（可重复读）
  (2) 每启动一个 mysql 程序，就会获得一个单独的数据库连接。每个数据库连接都有一个全局变量 @@transaction_isolation 表示当前的事务隔离级别

视图

含义：虚拟表（只保存 sql 逻辑），和普通表一样使用。mysql5.1 版本后的新特性，是通过表动态生成的数据

• 应用场景
  (1) 多个地方用到同样的查询结果
  (2) 该查询结果使用的sql语句较复杂

一、VIEW 语法

/* 创建方式一 */
CREATE VIEW 视图名 AS 查询语句;

/* 创建方式二 */
ALTER VIEW 视图名 AS 查询语句;

/* 修改视图 */
CREATE OR REPLACE VIEW 视图名 AS 查询语句;

/* 删除视图 */
DROP VIEW 视图名, 视图名, …;

/* 查看视图 */
DESC 视图名; # 查看结构
SHOW CREATE VIEW 视图名; # 查看创建过程

/* 注：视图创建后和表使用方式相同 */

变量

一、变量分类

系统变量：全局变量 会话变量
自定义变量：用户变量 局部变量

二、系统变量

由系统提供，属于服务器层面

• 查看所有系统变量
  SHOW GLOBAL VARIABLES;
  SHOW [SESSION] VARIABLES;

• 查看满足条件的系统变量
  可使用 LIKE 进行筛选

• 查看指定的某个系统变量的值
  会话
  SELECT @@[SESSION.]系统变量名;
  全局
  SELEC @@GLOBAL.系统变量;

• 为某个系统变量赋值
  SET GLOBAL/[SESSION]系统变量=值;
  SET @@GLOBAL/[SESSION].系统变量=值;

注：全局级别需要加GLOBAL，会话级别加SESSION，两者都不加默认会话级别
会话变量：
仅仅作用于当前会话（连接）
全局变量：
作用于所有会话，服务器每次启动时将为所有全局变量赋初始值

三、自定义变量

作用于当前会话（连接），同会话变量作用域

/* 声明并初始化 */
SET @用户变量名 = 值
SET @用户变量名 := 值
SELECT @用户变量名 := 值

/* 赋值（更新变量值） */
SELECT 字段 INTO 变量名 FROM 表
/* 也可用声明初始化方式赋值 */

仅仅在定义它的 begin end 中有效，应用在 begin end 第一句话

/* 声明 */
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFALT 值;

/* 赋值 */
SELECT 字段 INTO 变量名 FROM 表;
SET 变量名 = 值
SET 变量名 := 值
SELECT 变量名 := 值

存储过程与函数

一、存储过程

含义：一组预先编译好的SQL语句的集合

• 作用
  (1) 提高代码重用性
  (2) 简化操作
  (3) 减少了编译次数并且减少了和数据库服务器的连接次数，调高效率

/* 创建存储过程 */
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体
END

/* 调用存储过程 */
CALL 存储过程名(实参列表);

/* 删除存储过程 */
DROP PROCEDURE 存储过程名;

/* 查看存储过程信息 */
SHOW CREATE PROCEDURE 存储过程名;

• 参数列表
  参数参数列表包含三部分 参数模式 参数名 参数类型

• 注意
  (1) 若存储过程体仅有一句话，BEGIN END可以省略
  (2) 存储过程体中的每条SQL语句结尾必须加分号
  (3) 可以使用 DELIMITER 结束标记 重新设置存储过程结尾的结束标记

二、函数

/* 创建函数 */
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END

/* 调用函数 */
SELECT 函数名(参数列表)

/* 删除函数 */
DROP FUNCTION 函数名;

/* 查看函数信息 */
SHOW CREATE FUNCTION 函数名;

• 存储过程：可以有0个返回，也可以多个返回，适合做批量插入，批量更新
• 函数：有且仅有一个返回，适合做处理数据后返回一个结果

流程控制结构

一、分支结构

功能：简单的双分支

语法：IF(表达式1, 表达式2, 表达式3)
若表达式1成立则返回表达式2，否则返回表达式3

功能：实现多重分支

语法：

IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
ELSE 语句n;
END IF;

• 特点：只能在 BEGIN END 中使用

功能：类似java的switch语句，实现等值判断；类似java的多重if语句，实现区间判断

语法:

CASE 变量/表达式/字段
WHEN 判断的值 THEN 返回值1 或 语句
…
ELSE 返回值n 或 语句
END CASE;

• 特点
  (1) 可以作为表达式，嵌套其他语句使用，可以放任何地方
  (2) 可以作为独立语句使用，只能放在BEGIN END中
  (3) ELSE可省略

二、循环结构

分类：
WHILE LOOP REPEAT
循环控制：ITERATE（类似continue）LEAVE（类似于break)

/* WHILE 先判断后执行 */
WHILE 循环条件 DO
循环体;
END WHILE [标签];

/* LOOP 死循环 */
LOOP
循环体;
END LOOP[标签];

/* REPEAT 先执行后判断 */
REPEAT
循环体;
UNTIL 结束循环条件
END REPEAT [标签];

/* 以上循环都需要在 BEGIN END 中 */

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