MySQL基础篇

总纲

MySQL概述

相关概念

• 数据库：存储数据的仓库，数据是有组织的进行存储
• 数据库管理系统：操纵和管理数据库的大型软件
• SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准
• 关系型数据库：建立在关系模型基础上，由多张相互连接的二维表组成的数据库

数据模型

SQL

通用语法

• 可以单行也可以多行，以分号结尾
• 可以使用空格缩进来增强语句的可读性
• 不区分大小写，关键字建议用大写
• 单行注释 – 或 #
• 多行注释 /* */

分类

• DDL：Data Definition Language 数据定义语言，用来定义数据库、表、字段
• DML：Data Manipulation Language 数据操作语言，用来对数据库表中的数据进行增删改
• DQL：Data Query Language数据查询语言，用来查询数据表中的记录
• DCL：Data Control Language数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

• 查询所有数据库
  show databases;
• 查询当前数据库
  select database();
• 创建数据库
  create database databasename;
• 删除数据库
  drop database databasename;
• 使用数据库
  use databaseName
• 查询当前数据库所有表
  show tables;
• 查询表结构
  desc 表名;
• 查询指定表的建表语句
  show create table 表名;
• 创建表结构
  CREATE TABLE 表名(
  字段1 字段1类型 [ COMMENT 字段1注释 ],
  字段2 字段2类型 [COMMENT 字段2注释 ],
  字段3 字段3类型 [COMMENT 字段3注释 ],
  …
  字段n 字段n类型 [COMMENT 字段n注释 ]
  ) [ 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 表名;

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 条件 ] ;
• 条件可以有也可以没有，没有条件会删除整张表所有数据
• 不能删除某一个字段的值，可以使用update将该字段设为null
• 当进行删除全部数据时，datagrip会提示我们，询问是否确认删除

DQL

基础查询

• SELECT 字段1, 字段2, 字段3 … FROM 表名 ;
• SELECT * FROM 表名 ;
• *号代表查询所有字段，在实际开发中尽量少用，不直观、影响效率
• 字段设置别名
  • SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] … FROM 表名;
  • SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] … FROM 表名;
• 去除重复记录
  • SELECT DISTINCT 字段列表 FROM 表名;

条件查询

• 语法
  select 字段列表 from 表名 where 条件列表；
• 条件
  
  

聚合函数

• 将一列数据作为一个整体，进行纵向计算
• 
• 语法
  SELECT 聚合函数(字段列表) FROM 表名 ;
• 注意：NULL值不参与所有聚合函数的计算

分组查询

• 语法
  SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组
  后过滤条件 ];
• where和having的区别
  • 执行时机不同，where分组之前进行过滤，不满足where条件，不参与分组，而having是分组之后进行对分组结果进行过滤
  • 判断条件不同，where不能对聚合函数进行判断，而having可以
• 注意事项
  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段没有意义
  • 执行顺序 where > 聚合函数 > having
  • 支持多字段分组

排序查询

• order by 字段名 asc 升序，默认是升序，可以不写
• order by 字段名 desc,降序
• 可以多个字段排序 order by 字段1 asc, 字段2 desc

分页查询

• 语法
  SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
• 起始索引从0开始，起始索引（查询页码 - 1） * 每页显式的记录数
• 分页查询是数据库的方言，不同的数据库实现方式不同
• 如果查询是第一页数据，起始索引可以省略

执行顺序

1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit

DCL

管理用户

• 查询用户
  select * from mysql.user;
• 创建用户
  CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
• 删除用户
  DROP USER ‘用户名’@‘主机名’ ;
• 需要用用户名@主机名的方式，来唯一标识一个用户
• 主机名可以使用%通配

权限控制

• 查询权限
  SHOW GRANTS FOR ‘用户名’@‘主机名’ ;
• 授予权限
  GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’;
• 撤销权限
  REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’;

函数

字符串函数

数值函数

日期函数

流程函数

约束

概述

• 约束是作用于表中字段上的规则，用于限制存储在表中的数据
• 目的是保证数据库中的数据正确性、有效性、完整性
• 分类
• 

外键约束

• 用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
• 新增外键约束
  ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)
  REFERENCES 主表 (主表列名) ;
• 删除外键约束
  ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除/更新行为

• 语法
  alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
  dept(id) on update cascade on delete cascade ;

多表查询

多表关系

• 一对多（多的一方建立外键，指向一的一方的主键）
• 多对多（建立第三张表，中间表至少包含两个外键，分别关联两方主键）
• 一对一 （用于单表拆分，在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的（UNIQUE））

概述

• 连接查询
  • 内连接：相当于查询A、B交集部分数据
  • 外连接：
    • 左外连接：查询左表所有数据，以及两张表交集部分
    • 右外连接：查询右表所有数据，以及两张表交集部分
    • 自连接：当前表与自身表连接的查询，自连接必须使用表别名
• 子查询
  • 

内连接

• 内连接查询的是两张表交集部分的数据，也就是绿色部分的数据
• 隐式内连接
  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会将全部的数据直接合并在一起，union会对合并之后的数据去重

子查询

• SQL语句中嵌套SELECT语句
• SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
• 子查询外部的语句可以是INSERT/ UPDATE/ DELETE/SELECT
• 根据子查询结果不同，分为：
  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一行）
  • 表子查询（子查询结果为多行多列）
• 根据子查询位置分为：
  • where之后
  • from之后
  • select之后

标量子查询

• 子查询返回的结果是单个值（数字、字符串、日期）
• 常用操作符 = <> > >= < <=

列子查询

• 子查询返回的结果是一列（可以是多行）
• 常用操作符 IN, NOT IN, ALL, SOME, ANY
• 

行子查询

• 子查询返回的结果是一行（可以是多列）
• 常用操作符 = ，<>, IN, NOT IN

表子查询

• 子查询返回的结果是多行多列的
• 常用操作符 in
• 还会将子查询作为一张表与其他表连接，子查询放在from之后

事务

事务简介

• 一组操作的组合，是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
• Mysql的事务是自动提交的，当支持完一条DML语句时，MySQL会立即隐式的提交事务

事务操作

• 查看、设置事务提交方式
  • select @@autocommit;
  • set @@autocommit = 0;
• 提交事务
  • commit;
• 回滚事务
  • rollback;
• 开启事务
  • start transaction 或 begin

事务四大特性

并发事务问题

事务隔离级别

• 查看事务隔离级别
  • select @@TRANSACTION_ISOLATION;
• 设置隔离级别
  • SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
    READ COMMITTED | REPEATABLE READ | SERIALIZABLE }