首页 sql

  1. SQL 语法

    • SQL 语句可以单行或多行书写，以分号结尾。

    • SQL 语句可以使用空格 / 缩进来增强语句可读性

    • MySQL 数据库的 SQL 语句不区分大小写，关键字建议大写

    • 注释：

      ​ 单行注释： -- 注释内容 或 # 注释内容 ( mysql 特有)

      ​ 多行注释： /* 注释内容 */

  2. SQL 分类

    分类全称说明
    DDLData Definition Language数据定义语言，用来定义数据库对象（数据库，表，字段）
    DMLData Manipulation Language数据操作语言，用来对数据库表中的数据进行增删改
    DQLData Query Language数据查询语言，用来查询数据库中表的记录
    DCLData Control Language数据控制语言，用来创建数据库用户，控制数据库的访问权限

  3. 字段类型

    • 数值类型

      类型大小有符号范围（SIGNED）无符号范围（UNSIGNED）描述
      TINYINT1 bytes(-128,127)(0,255)小整数值
      SMALLINT2 bytes(-32768,32767)(0,65535)大整数值
      MEDIUMINT3 bytes(-8388608, 8388607)(0,16777215)大整数值
      INT 或 INTEGER4 bytes(-2147483648, 2147483647)(0,4294967295)大整数值
      BIGINT8 bytes(-2^63, 2^63-1)(0, 2^64-1)极大整数值
      FLOAT4 bytes(-3.402823466 E+38, 3.402823466351 E+38)0 和 (1.75494351 E-38, 3.402823466 E+38)单精度浮点数
      DOUBLE8 bytes(-1.7976931348623157 3+308, 1.7976931348623157 E+308)0 和 (2.225073858072014 E-308, 1.7976931348623157 E +308)双精度浮点数
      DECIMAL依赖于 M (精度) 和 D (标度) 的值赖于 M (精度) 和 D (标度) 的值小数值

    • 字符串类型

      类型大小 (单位：bytes)描述
      CHAR0-255定长字符串
      VARCHAR0-65535变长字符串
      TINYBLOB0-255不超过 255 个字符的二进制数据
      TINYTEXT0-255短文本字符串
      BLOB0-65535二进制形式的长文本数据
      TEXT0-65535长文本数据
      MEDIUMBLOB0-16777215二进制形式的中等长度文本数据
      MEDIUMTEXT0-16777215中等长度文本数据
      LONGBLOB0-4294967295二进制形式的极大文本数据
      LONGTEXT0-4294967295极大文本数据

    • 日期类型

      类型大小范围格式描述
      DATE31000-01-01 到 9999-12-31YYYY-MM-DD日起值
      TIME3-838:59:59 到 838:59:59HH:MM:SS时间值或持续时间
      YEAR11901 到 2155YYYY年份值
      DATETIME81000-01-01 00:00:00 到 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
      TIMESTAMP41970-01-01 00:00:01 到 2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS混合日期和时间值，时间戳

  4. DDL - 数据库操作

    • 查询所有数据库

      SHOW DATABASES;

    • 查询当前数据库

      SELECT DATABASE();

    • 创建，字符集建议选 utf8mb4 可以占 4 个字节

      CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT SET 字符集] [COLLATE 排序规则];

    • 删除

      DROP DATABASE [IF EXISTS] 数据库名;

    • 切换到指定数据库

      USE 数据库名;

  5. DDL - 表操作

    • 查询当前数据库所有表

      SHOW TABLES;

    • 查询表结构

      DESC 表名;

    • 查询指定表的建表语句

      SHOW CREATE TABLE 表明;

    • 创建表

      CREATE TABLE 表名(
      	字段1 类型[comment 注释],
      	字段2 类型[comment 注释],
      	字段3 类型[comment 注释],
      	...
      	字段N 类型[comment 注释]
      	)[COMMENT 表注释];

    • 添加字段

      ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释][约束];

    • 修改数据类型

      ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

    • 修改字段名和字段类型

      ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释][约束];

    • 删除字段

      ALTER TABLE 表名 DROP 字段名;

    • 修改表名

      ALTER TABLE 表名 RENAME TO 新表名;

    • 删除表

      DROP TABLE [IF EXISTS] 表名;

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

      TRUNCATE TABLE 表名;

  6. DML 数据操作

    • 给指定字段添加数据

      INSERT INTO 表名(字段名1，字段名2, ...) VALUES (值1, 值2, ...);

    • 给全部字段添加数据

      INSERT INTO 表名 VALUES (值1, 值2, ...);

    • 批量添加数据

      INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);	-- 指定字段添加数据
      INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);		-- 全部字段添加数据

    • 修改数据

      UPDATE 表名 SET 字段名1=值1, 字段名2=值2, ...[WHERE 条件];

    • 删除数据

      DELETE FROM 表名 [WHERE 条件];

  7. DQL 查询操作

    SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数 
    -- 基本查询：SELECT 字段列表 FROM 表名列表
    -- 条件查询：WHERE
    -- 聚合函数：COUNT、MAX、MIN、AVG、SUM
    -- 分组查询：GROUP BY
    -- 排序查询：ORDER BY
    -- 分页查询： LIMIT

    • 查询多个字段

      SELECT 字段1, 字段2, 字段3, ... FROM 表名;
      SELECT * FROM 表名;

    • 常规查询，设置别名

      SELECT 字段1 [AS 别名], 字段2 [AS 别名2] ... FROM 表名;

    • 去除重复记录

      SELECT DISTINCT 字段列表 FROM 表名;

    • 条件查询

      SELECT 字段列表 FROM 表名 WHERE 条件列表;
      比较运算符功能逻辑运算符功能
      >大于AND 或 &&并且（多个条件同时成立）
      >=大于等于OR 或 \|\|或者（多个条件任意一个成立）
      <小于NOT 或 ！非
      <=小于等于
      =等于
      <> 或 1=不等于
      BETWEEN ... AND ...在某个范围之内（含最小，最大值）
      IN(...)在 in 之后的列表中的值，多选一
      LIKE 占位符模糊匹配（_ 匹配单个字符，% 匹配任意个字符）
      IS NULL是 NULL

    • 聚合函数

      SELECT 聚合函数(字段列表) FROM 表名;

    • 分组查询

      SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
      -- where 与 having 区别
      /* 执行时机不同：where 是分组之前进行过滤，不满足 where 条件，不参与分组；而 having 是分组之后对结果进行过滤。
         判断条件不同：where 不能对聚合函数进行判断，而 having 可以。*/
    • 排序查询
      SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式, 字段2 排序方式2;
      /* 排序方式： ASC 升序 (默认值)
      		   DESC 降序	*/
    • 分页查询
      SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;  -- 第一页可省略，页码从 0k
  8. DCL 控制语句
    • 查询用户
    USE mysql;
    SELECT * FROM user;
    • 创建用户
      CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
    • 修改用户密码
      ALTER USER '用户名'@'主机名' IDENTIFIED BY mysql_native_password BY '新密码';
    • 删除用户
      DROP USER '用户名'@'主机名';
  9. 权限控制
    权限说明
    ALL , ALL PRIVILEGES所有权限
    SELECT查询数据
    INSERT插入数据
    UPDATE修改数据
    DELETE删除数据
    ALTER修改表
    DROP删除数据库 / 表 / 视图
    CREATE创建数据库 / 表
    • 查询权限
      SHOW GRANTS FOR '用户名'@'主机名';
    • 授予权限
      GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
    • 撤销权限
      REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
      多个权限之间，使用逗号分隔；授权是，数据库名和表名可以使用 * 通配，代表所有；主机名用 % 代表所有。
  10. 常用函数
    • 字符串函数
      函数功能
      CONCAT(S1,S2, ... Sn)字符串拼接，将 S1，S2，... Sn 拼接成一个字符串
      LOWER(str)将字符串 str 全部转为小写
      UPPER(str)将字符串 str 全部转为大写
      LPAD(str, n, pad)左填充，用字符串 pad 对 str 的左边进行填充，达到 n 个字符串长度
      RPAD(str, n, pad)右填充，用字符串 pad 对 str 的右边进行填充，达到 n 个字符串长度
      TRIM(str)去掉字符串头部和尾部的空格
      SUBSTRING(str, start, len)返回字符串 str 从 start 位置起的 len 个长度的字符串
    • 数值函数
      函数功能
      CEIL(x)向上取整
      FLOOR(x)向下取整
      MOD(x, y)返回 x/y 的模
      RAND()返回 0~1 内的随机数
      ROUND(x, y)求参数 x 的四舍五入的值， 保留 y 位小数
    • 日期函数
      函数功能
      CURDATE()返回当前日期
      CURTIME()返回当前时间
      NOW()返回当前日期和时间
      YEAR(date)获取指定 date 的年份
      MONTH(date)获取指定 date 的月份
      DAY(date)获取指定 date 的日期
      DATE_ADD(date, INTERVAL expr type)返回一个日期 / 时间值加上一个时间间隔 expr 后的时间值
      DATEDIFF(date1, date2)返回起始时间 date1 和结束时间 date2 之间的天数
    • 流程函数
      函数功能
      IF(value, t, f)如果 value 为 true，则返回 t，否则返回 f
      IFNULL(value1, value2)如果 value1 不为空，返回 value1，否则返回 value2
      CASE WHEN [val1] THEN [res1] ... ELSE [default] END如果 val1 为 true，返回 res1，... 否则返回 default 默认值
      CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END如果 expr 的值等于 val1，返回 res1，... 否则返回 default 默认值
  11. 约束
    • 概念：约束是作用于表中字段上的规则，用于限制存储表中的数据
    • 目的：保证数据库中的数据正确，有效，完整。
    • 分类
      约束描述关键字
      非空约束限制该字段的数据不为 nullNOT NULL
      唯一约束保证该字段的所有数据都为一，不重复UNIQUE
      主键约束主键是一行数据的唯一标识，要求非空且唯一PRIMARY KEY
      默认约束保存数据时，如果未指定该字段的值，采用默认值DEFAULT
      检查约束 (8.0.16 版本之后)保证字段值满足某一个条件CHECK
      外键约束用来让两张表的数据之间简历连接，保证数据的一致性和完整性FOREIGN KEY
    • 添加外键
      # 创建时添加
      CREATE TABLE 表名(
      	字段名 数据类型, ...
      	[CONSTRAINT][外键名称]) REFERENCES 主表(主表列名)
      );
      # 创建之后添加
      ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名 )
    • 外键删除 / 更新行为
      行为说明
      NO ACTION当在父表中删除 / 更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除 / 更新 (与 RESTRICT 一致)
      RESTRICT当在父表中删除 / 更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除 / 更新
      CASCADE当在父表中删除 / 更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除 / 更新外键在子表中的记录
      SET NULL当在父表中删除对应记录时，首先检查给记录是否有对应外键，如果有则设置子表中该外键值为 null (这就要求该外键允许取 null)
      SET DEFAULT父表有变更时，子表外键列设置成一个默认的值（ Innodb 不支持）
  12. 多表查询
    • 多表关系
      基本分三种：一对多、多对多、一对一
    • 多表查询：从多张表查询，可能会出现笛卡尔积，需要添加条件
    • 分类：
      名称描述
      内连接查询相当于查询 A，B 交集部分
      左外连接查询查询左表所有数据，包括两表交集部分
      右外连接查询查询右表所有数据，包括两表交集部分
      自连接查询当前表与自身的连接查询，自连接必须使用表别名
      子查询sql 语句嵌套 select 语句，称为嵌套查询，又称子查询
    • 内连接查询
      # 隐式内连接
      SELECT 字段列表 FROM 表1，表2 WHERE 条件 ...;
      # 显式内连接
      SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;
    • 外连接查询
      # 左外连接
      SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;
      # 右外连接
      SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;
    • 自连接查询：可以是内连接查询，也可以是外连接查询
      SELECT 字段列表 FROM 表A 别名a JOIN 表A 别名b ON 条件 ...;
    • 联合查询： union / union all , 就是把多次查询的结果合并起来，形成一个新的查询结果集。
      SELECT 字段列表 FROM 表A ...
      UNION[ALL]		# union 自动去重，union all 会显示所有数据，两个条件都满足会显示两次
      SELECT 字段列表 FROM 表B ...;
    • 子查询：
      查询分类描述
      标量子查询查询结果为一个值，常用操作符： = <> > >= < <=
      列子查询查询结果为一列，常用操作符： IN NOT IN ANY SOME ALL
      行子查询查询结果为一行，常用操作符： = <> IN NOT IN
      表子查询查询结果为多行多列，常用操作符： IN
      SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
  13. 事务
    • 事务时一组操作的集合，是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
    • 事务的四大特性 (ACID)
      特性描述
      原子性 ( Atomicity )事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
      一致性 ( Consistency )事务完成时，必须使所有的数据都保持一致状态。
      隔离性 ( Isolation )数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
      持久性 ( Durability )事务一旦提交成功或回滚，它对数据库中的数据的改变就是永久的。
    • 提交方式修改为手动提交
      SET @@AUTOCOMMIT=0;		# 这是为手动提交
      ...;
      ...;
      ...;	# 执行一组事务
      COMMIT; 	# 如果成功手动提交
      ROLLBACK;	# 如果报错，则执行回滚操作
    • 指令开启事务
      START TRANSACTION 或 BEGIN;	# 开启事务
      ...
      COMMIT;		# 提交事务
      ROLLBACK;	# 回滚事务
    • 并发事务问题
      问题描述
      脏读一个事务读到另一个事务还没有提交的数据。
      不可重复读一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
      幻读一个事务按照条件查询数据时。没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。
    • 事务隔离级别（解决并发的事务问题）
      隔离级别脏读不可重复读幻读
      Read uncommited√√√
      Read commited×√√
      Repeatable Read(默认)××√
      Serializable×××
      # 查看事务的隔离级别
      SELECT @@TRANSACTION_ISOLATION;
      # 设置事务隔离级别
      SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
 更新于   阅读次数  次
请我喝[茶]~(￣▽￣)~*
渣渣文 微信支付
微信支付
渣渣文 支付宝
支付宝