简述

本文是宋红康老师的mysql基础篇结合了自己的思绪整理笔记

注意

windows环境下大小写不敏感，包括字符串的大小写

字符串从1开始

基本概念

数据库DB

存储数据的仓库，本质是一个文件系统，保存了一系列有组织的数据

数据库管理系统DBMS

操纵和管理数据的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制

结构化查询语言SQL

用于与数据库通信的语言

关系型数据库

把复杂的数据结构归结为简单的二元关系

表间关系

一对一

一对多

多对多

自我引用

SQL

语法法规

windows环境下大小写不敏感，包括字符串的大小写

;、\g、\G结束

关键字不能被缩写也不能被分行

数据库名、表名不得超过30个字符(A-Z、a-z、0-9、_，共63个字符)，变量名的长度限制为29(不包括标识字符@)

统一规范

数据库名、表名、表别名、字段名、字段别名小写

关键字、函数、绑定变量大写

DDL(数据定义语言)

一旦执行，就不可回滚,自动COMMIT

CREATE

ALATER

DROP

RENAME

TRUNCATE

DML(数据操作语言)

默认情况下也是不可回滚，但可以执行 SET autocommit = FALSE(指令对DDL操作失效)，关闭自动COMMIT就可以实现回滚

DQL(数据查询语言)

SELECT

INSERT

DELETE

UPDATE

DCL(数据控制语言)

TCL(事务控制语言)

COMMIT

ROLLBACK

SAVEPOINT

GRANT

REVOKE

DDL-CREATE

数据库

创建

默认创建

CEATE DATABSE 数据名

创建数据库并使用默认字符集，注意8+的默认字符为utf8mb4，而5.7为latin

设置字符集

CEATE DATABSE 数据名 CHARACTER SET ‘gbk’

不存在就创建

CEATE DATABSE IF NOT EXISTS 数据名 CHARACTER SET ‘gbk’

管理

查看连接中的数据库

SHOW DATABASES

切换数据库

USE 数据库名

显示当前数据中的表

SHOW TABLES

显示指定数据中的表

SHOW TABLES FROM 数据库名

重命名

数据库不能改名，可视化工具是通过复制表到新库再删除旧库

查看当前数据库名

SELECT DATABASE() FROM DUAL

修改字符集

ALTER DATABASE 数据库名 CHARACTER SET ‘gbk’

删除

普通删除

DROP DATABASE 数据库名

存在删除

DROP DATABASE IF EXISTS 数据库名

表

创建

没有指定字符集就使用数据库默认的字符集

默认创建

CREATE TABLE 表名(字段名列表)

不存在就创建

CREATE TABLE IF NOT EXISTS 表名(字段名列表)

复制创建

只创建表结构（没有索引约束等），保留查询到的记录，

CREATE TABLE 表名 AS SELECT * FROM demo

设置字符集

CREATE TABLE tb(id INT) CHARACTER SET 'utf8' # 表指定字符集

管理

字段

设置字符集

CREATE TABLE tb(id INT CHARACTER SET 'utf8')  # 字段指定字符集

添加

ALTER TABLE 表名 ADD 字段名 类型;
默认添加到字段列表最后

ALTER TABLE 表名 ADD 字段名 类型 FIRST;
添加到字段列表最前

ALTER TABLE 表名 ADD 字段名 类型 AFTER 字段名x;
添加到字段名x的后面

修改

ALTER TABLE myemp1 MODIFY emp VARCHAR(35) DEFAULT 'aaa';

重命名

ALTER TABLE myemp1 CHANGE id i_d DOUBLE(10,2);

删除

ALTER TABLE myemp1 CHANGE DROP COLUMN my_email;

重命名

RENAME TABLE emp TO myemp;

或

ALTER TABLE emp RENAME [TO] myemp;
# TO可以省略

删除

普通删除

DROP TABLE myemp;

存在删除

DROP TABLE IF EXISTS myemp;

清空

只是清空表的记录

TRUNCATE属于DDL不可回滚

TRUNCATE TABLE emp;

DELETE属于DML可以设置回滚

DELETE FROM emp;

总结TRUNCATE速度要比DELETE快，但是TRUNCATE无事务且不触发TRIGGER，有可能造成事故，不建议在开发中使用此代码

DDL-ALTER

见DDL-CREATE中数据库和表的管理

DDL-DROP

见DDL-CREATE中数据库和表的删除

DDL-RENAME

由于数据库不能重命名，所以见DDL-CREATE表的重命名

DDL-TRUNCATE

见DDL-CREATE中表的清空

DML-DQL-SELECT

基本使用

SELECT 字段名1, 字段名2 FROM 表名

去重

DISTINCT只能在字段名前作用于所有的字段

SELECT DISTINCT 字段名1 FROM 表名

查询常数

不能在查询所有字段*中使用

在字段名前增加表达式，会以表达式的字面量为列名，表达式值为列值生成一列，可以通过AS关键字重新命名。

SELECT '张三' AS '姓名',字段名1 FROM demo

多表查询

从sql优化的角度，建议多表查询时，每个字段前都指明所在的表

内连接

合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行

交叉连接

CROSS JOIN

得到笛卡尔积，可以通过where字句赛选满足条件的数据

SELECT * FROM demo1 CROSS JOIN demo2 WHERE demo1.id = demo2.id

也可以省略关键字

SELECT * FROM demo1,demo2 WHERE demo1.id = demo2.id

INNER JOIN

注意INNER关键字可以省略

SELECT * FROM demo1 INNER JOIN demo2 ON demo1.id = demo2.id

外连接

两个表在连接过程中除返回满足的行外还返回其它不满足条件的行，称为外连接

左外连接

在左表全部显示的基础上，返回满足条件的记录

注意OUTER关键字可以省略

SELECT * FROM demo1 LEFT OUTER JOIN demo2 ON demo1.id = demo2.id

右外连接

在右表全部显示的基础上，返回满足条件的记录

注意OUTER关键字可以省略

SELECT * FROM demo1 RIGHT OUTER JOIN demo2 ON demo1.id = demo2.id

全外连接

在左右表的基础上，返回满足条件的记录

根据sql标准全外连接关键字为FULL OUTER JOIN，但mysql并不支持。需要通过UNION操作符实现

SELECT * FROM demo1 LEFT OUTER JOIN demo2 ON demo1.id = demo2.id UNION SELECT * FROM demo1 RIGHT OUTER JOIN demo2 ON demo1.id = demo2.id

新特性

NATURAL JOIN

自动查询两个表中所有相同的字段，并使其在INNER JOIN的ON中对应相等，各个逻辑用AND连接。

SELECT * FROM demo1 NATURAL JOIN demo2

USING

只能配合JOIN使用，需要指定相同的字段，其它同NATURAL JOIN相同

SELECT * FROM demo1 NATURAL JOIN demo2 USING(id)

子查询

一个查询语句嵌套在另一个查询语句内部的查询

不能再GROUP BY 和 LIMIT中声明子查询

在外层的查询为外查询(主查询)

在内层的查询为内查询(子查询)

询结果数

单行子查询

操作符

=

>

>=

<

<=

<>

多行子查询

操作符

IN

等于列表中的任意一个

ANY(SOME)

需要和单行操作符一起使用，和子查询返回的任一值比较

num < ANY (SELECT ...)

ALL

需要和单行操作符一起使用，和子查询返回的所有值比较

num < ALL (SELECT ...)

执行次数

相关子查询

子查询的执行依赖于外部查询。

子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次。

不相关子查询

DML-DQL-SELECT执行过程

0. FROM及其字句，如 JOIN ON

1. WHERE条件

2. GROUP BY

3. HAVING

4. SELECT字段

5. DISTINCT去重

6. ORDER BY

7. LIMIT

   总体上来说就是先过滤行，再过滤列

DML- INSERT

没有插入值的字段为NULL

INSERT INTO emp1 VALUES(1,'tom',3400); # 隐式转换，没有指明字段，值与字段列表相对应

INSERT INTO emp1(id,name,age) VALUES(1,'tom',12); # 隐式转换，指明字段，值与指定相对应

INSERT INTO emp1(id,name,age) VALUES(1,'tom',12),(2,'tom',12); # 插入多组记录,效率高于多条INSERT语句

查询结果插入

INSERT INTO emp1(id,name,age) SELECT * from d1; # 注意没有VALUES关键字

DML- DELETE

DELETE FROM emp1 WHERE id=1;

DML- UPDATE

UPDATE emp1 SET name='zs',age=15; # 没有WHERE条件，更新所有记录

UPDATE emp1 SET name='zs',age=15 WHERE id=2; # 只更新id=2的记录

DCL-TCL-COMMIT

用于提交数据，一旦执行了COMMIT，则数据就永久的保存在了数据库中，意味着数据不可以回滚

COMMIT

DCL-TCL-ROLLBACK

回滚数据，一旦执行了ROLLBACK，则可以实现数据的回滚，回滚到最近的一次COMMIT之后

ROLLBACK

DCL-SAVEPOINT

DCL-GRANT

DCL-REVOKE

数据类型

数字

在MySql5.7中所有数字都可以指定宽度(用于显示),在Mysql8中只有定点数才需要指定精度和标度

CREATE TABLE d(age INT(4)) # 只是指定显示的宽度(符号要占一位)，与实际的存储大小限制并没有关系

整数

浮点数

浮点数存在精度问题

定点数

以字符串存储

DECIMAL(10,0):表示0位小数10-0个整数，小数超出部分四舍五入,整数超出直接报错

比特位

通过函数BIN和HEX可以按二进制 十六进制来展示数据

日期与时间

YEAR

YEAR在取值时可以是字符串或整数，但它们在只书写两位注意区别，也不推荐书写两位，推荐书写四位

DATE

TIME

DATETIME

TIMESTAMP

DATETIME和TIMESTAMP区别

文本

实际存储长度不确定的不能做主键，只有CHAR是固定长度

注意

M和L都表示字符数，M需要指明，L不需要指明

长度范围：表示输入内容个数范围

占用的存储空间：计算时先将对应的M或L转换成字节再计算

CHAR

固定长度，如果实际字符小于规定的，还在数据右边填充空格，在去除时会去除右边的空格，所以会导致输入的字符数小规定的并且右边有空格，在读取时会导致空格丢失

VARCHAR

可变长度，如果实际字符数小于规定的，就以实际长度为M

TINTYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

ENUM

CREATE TABLE mp(s ENUM('春','夏')) # 取值时只能是其中一个(可以是NULL)，忽略大小写

INSERT INTO em VALUES (1),(2),('1') # 插入时可以通过枚举索引插入

SET

CREATE TABLE mp(s SET('a','b'))

INSERT INTO mp VALUES ('a,b,a') # 集合重复的a会被忽略

BINARY

VARBINGARY

TINYBLOB

BLOB

MEDIUMBLOB

LONGBLOB

空间

单值

GEOMETRY

POINT

LINESTRING

POLYGON

集合

MULTIPOINT

MULTILINESTRING

MULTIPOLYGON

JSON

数据过滤

where

条件过滤，必须紧跟在FROM关键字后，不能使用多行函数

在进行多表连接时，where在连接完成后执行，即在on之后执行

JOIN ON

多表查询时使用，但JOIN ON先于WHERE执行

HAVING

在WHERE之后执行，可以使用多行函数。

一般配合GROUP BY使用，并声明在GROUP BY的后面。GROUP BY不存在时使用也不会报错

排序

ORDER BY

在GROUP BY之后执行

没有设置ORDER BY排序情况下，数据的显示按照添加的顺序显示

设置了ORDER BY没有指明排序规则，默认升序

可以指定多个字段名和规则(用逗号分割)，当相等时按照后续字段和规则。

升序

ASCEND，可缩写ASC

ORDER BY 字段名 ASC

降序

DESCEND，可缩写DESC

ORDER BY 字段名 DESC

分组

GROUP BY

在ORDER BY之前执行

可以指定多个字段名和规则(用逗号分割)，当相等时按照后续字段和规则

WITH ROLLUP

对分组的信息汇总

GROUP BY 字段名1 WITH ROLLUP

在表

分页

LIMIT

LIMIT需要放到SELECT语句的最后面

LIMIT 启始(从0开始),数量

LIMIT 0,20

如果从0开始可以省略

LIMIT 20

自增列

AUTO_INCREMENT

初值为1，每次在最大值(持久化)加1

约束

数据完整性

实体完整性

例如:同一个表中不能存在完全相同无法区分的记录

域完整性

例如:性别只能是男或女

引用完整性

例如:员工所在部门，在部门表中要能找到这个部门

用户自定义完整性

例如:用户名唯一

查看约束

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称'

约束分类

唯一约束、主键约束、外键约束都会创建索引

非空约束

创建

修改字段时，如果对应的字段为NULL修改会失败

ALTER TABLE t MODIFY a VARCHAR(25) NOT NULL;

删除

ALTER TABLE t MODIFY a VARCHAR(25) NULL;

唯一性约束

创建

表创建完成后再添加，可能添加失败，因为可能存在不满足的记录

UNIQUE和UNIQUE KEY是相同的，但建议加KEY

在创建复合约束时，如果没有指定索引名字，会以第一个字段为索引名

删除

删除唯一约束需要删除对应的唯一索引

ALTER TABLE USER DROP INDEX uk_user-name-pwd;

主键约束

创建

注意主键约束只能是PRIMARY KEY，而唯一约束可以加KEY也可以不加，建议统一都加KEY

创建原理和唯一索引一样

删除

ALTER TABLE t DROP PRIMARY KEY; # 删除后字段的 NOT NULL是保留的，包括复合的

外键约束

外键约束不能跨引擎

创建

FOREIGN KEY ... REFERENCES ...

删除

约束等级

总结

检查约束

创建

删除

ALTER TABLE 表名 DROP 约束名;

默认值约束

创建

删除

列级约束

声明在对应字段的后面，只对该列有用

非空约束

唯一性约束

主键约束

外键约束

检查约束

默认值约束

表级约束

声明在字段列表的后面，可以对所有字段有效

唯一性约束

主键约束

外键约束

检查约束

运算

mysql和c语言一样用0和1表示逻辑真假

算术运算符

+

只是加法，没有字符串拼接功能

100+’1‘ 的结果为101。对'1'作隐式转换

100+'a'的结果为100。对'a'作隐式转换失败，当作0处理

-

*

/(DIV)

无论是否结果为整数，结果都将转换为浮点数保留4位小数

%(MOD)

取模运算符

比较运算

比较符

mysql比较结果没有布尔值，真返回1假返回0其他返回NULL

=

普通等于，在进行数字和字符串比较会进行隐式转换1=‘1dw’返回1

<=>

安全等于，没有NULL的情况下和普通等于相同，操作数均为NULL返回1，一个操作数为NULL返回0

!=(<>)

不等于

<

<=

>

>=

比较关键字

IS NULL

判断表达式是否为空

IS NOT NULL

判断表达式是否不为空

BETWEEN AND

判断表达式的值在两个值之间

ISNULL

判断表达式是否为空

LIKE

表达式是否满足模糊匹配

%

任意个数字符

_

一个_表示一个字符

REGEXP

表达式是否满足正则规则

RLIKE

表达式是否满足正则规则

比较函数

LEAST

返回多个值中的最小值

GREATEST

返回多个值中的最大值

IN

表达式的值在列表中

NOT IN

表达式的值不在列表中

逻辑运算

与

AND、&&

或

OR、||

非

NOT、!

异或

XOR

位运算符

符号位要参与运算

按位与

&

按位或

|

按位异或

^

按位取反

~

按位右移

>>

按位左移

<<

集合运算

集合运算的注意事项

1、作为运算对象的记录的列数必须相同 2、作为运算对象的记录中列的类型必须一致 3、可以使用任何select语句，但order by子句只能在最后使用一次（注意此时的字段是运算完成后的字段，不能在以运算完成前的表名.字段名的方式来选择字段）

并集

非严格意义上的并集，只是将多个表的记录放在一起，存在重复的记录

UNION

对两个表的数据求并集，并去掉重复多余的数据

SELECT * FROM demo1 LEFT OUTER JOIN demo2 ON demo1.id = demo2.id UNION SELECT * FROM demo1 RIGHT OUTER JOIN demo2 ON demo1.id = demo2.id

UNION ALL

对两个表的数据求并集，不会去掉重复多余的数据

SELECT * FROM demo1 LEFT OUTER JOIN demo2 ON demo1.id = demo2.id UNION ALL SELECT * FROM demo1 RIGHT OUTER JOIN demo2 ON demo1.id = demo2.id

交集

MySql不支持。

关键字：intersect

差集

MySql不支持。

关键字：except

空值运算

空值NULL参与的任何运算都是空值NULL

IFNULL

IFNULL(表达式,0) # 如果表示为空值NULL就返回0，否则返回表达式

常见数据库对象

表

表是存储数据的逻辑单元，以行和列的形式纯在，列就是字段，行就是记录

数据字典

即系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护，程序员通常不应该修改，只可查看

约束

执行数据校验的规则，用于保证数据完整性的规则

视图

一个或者多个数据表的逻辑表示，视图并不存储数据

索引

用于提高查询性能，相当于书的目录

存储过程

用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境

存储函数

用于完成一次特定的计算，具有一个返回值

触发器

相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

函数

注意：MySQL中，字符串的位置是从1开始的

单行函数

对一行进行操作，产生一个结果

(37条消息) MySQL学习笔记8：MySQL中函数的定义及分类（尚硅谷）小马同学AAA的博客-CSDN博客mysql中的函数定义

---

多行(聚合)函数

对多行进行操作，产生一个结果

聚合函数不能嵌套，因为聚合是接收多行，但其结果只产生一行，所以不能嵌套

多行函数的执行应该是在GROUP BY之后执行。

以下函数会自动跳过值为NULL的记录

AVG

平均值

SUM

求和

MAX

最大值

MIN

最小值

COUNT

计数

视图

一个或者多个数据表的数据的逻辑显示，视图并不存储数据，占用很少的内存空间，视图建立在已有表的基础上，视图依赖建立的这些表称为基表,默认对视图和基表都存在且(不满足不可更新情况) 的记录修改是双向的。不建议对视图数据的修改，应该对基表进行数据的修改

视图是一张表，但并不存储数据本身，但它会存储创建时定义的表结构，如果对基表的表结构修改，会导致视图的结构和基表的结构不一致就会报错

创建

SELECT * FROM 视图名 # 查看视图内容

查看视图结构

不可更新情况

修改视图

删除视图

DELIMITER

指定SQL语句的结束符

存储过程与存储函数

characteristice(函数特性)

存储过程

定义

经过预先编译的SQl语句，预先存储在MySql服务器上，需要执行的时候，客户端需要向服务器端发送调用存储过程的命令，服务器就可以把预先存储的一系列SQL全部执行

不能在查询语句中使用，因为没有返回值

创建

调用

优点

缺点

存储函数

用户自定义函数，必须有返回值，参数默认只能是IN模式

创建

注意

存储函数可能报错，原因是需要指定characteristice函数特征

解决方案

调用

查看

修改

删除

变量

系统变量

所有系统变量以@@开头

全局系统变量(global)

全局系统变量也称全局变量，对所有的会话(连接)有效，但不能跨重启

@@global.变量名

持久化

会话系统变量(session)

会话系统变量也称local变量，默认为会话系统变量，只对当前会话(连接)有效

@@session.变量名

查看

查看指定

修改

只对当前MySql实例有效，重启失效

用户自定义变量

所有用户自定义变量以@开头

会话用户变量

只对当前连接会话有效

创建赋值

局部变量

只在BEGIN和END语句块中有效，只能在存储过程和存储函数有效。局部变量必须指定类型，一般不在局部变量前加@

创建赋值

错误处理

错误码分类

自定义错误

注意使用sqlstate_value字符串错误相信时要加SQLSTATE关键字

错误处理程序

主动抛出

抛出一个sqlstate_value为'HY000'错误信息为(薪资...)的错误

流程控制

条件判断

IF

格式

案例

注意END IF后有分号

CASE

格式

案例

循环

LOOP

WHILE

REPEAT

至少执行一次

注意UNITL没有分号

跳转

ITERATE

LEAVE

跳出BEGIN时BEGIN需要定义标签名 begin_lable:BEGIN

游标

声明

游标必须在声明处理程序之前，而且变量和条件还必须在声明游标或处理程序之前被声明

打开

使用

注意接收值的变量必须在游标声明前定义

关闭

案例

触发器

概述

外键约束不会触发触发器

创建

案例

NEW-OLD

NEW：传入的值

OLD：表中的值

对于INSERT语句,只有NEW是合法的

对于DELETE语句，只有OLD才合法

UPDATE语句可以在和NEW以及

OLD同时使用

查看删除

Mysql8新特性

MySql从5.7直接跳跃发布8.0

DDL原子化

在低版本的MySql中

DROP TABLE d1,d2 # 如果d2不存在，虽然会报错但是d1会被删除

在8本的MySql中

DROP TABLE d1,d2 # 如果d2不存在，虽然会报错但是d1依然纯在(回滚)

计算列

某一列的值通过其它列计算得来

CREATE TABLE t1(a INT,b INT, C INT GENERATED ALWAYS AS (a + b) VIRTUAL) # GENERATED ALWAYS AS (a + b)定义计算列

窗口函数

PARTITION BY 窗口函数作用和GROUP BY类似都是进行分组，但是窗口函数分组不会合并记录，并且可以排序。窗口函数可以对这些个记录进行相关操作

静态窗口函数

窗口函数的大小固定，不会因为记录的不同而不同

动态窗口函数

窗口函数的大小随着记录的不同而变化

创建

函数列表

实例

通用表表达式(CTE)

CTE是一个命名的临时结果集，CTE可以理解成一个可以复用的子查询，CTE可以被其它CTE引用，但子查询不能被其它子查询引用

普通

创建、使用

递归

创建、使用

注意RECURSIVE是递归关键字

UNION ALL前为初始化部分，UNION ALL后为递归部分，没有记录返回时递归停止

伪表

SELECT 1+1 FROM DUAL # DUAL伪表，只是为了sql结构的完整

别名

表和字段都可以取别名

列别名

列别名不能再where中使用，因为where先于查询字段别名执行。原列名依然可以使用

表别名

一旦指定了表别名在SELECT和WHERE必须使用表的别名，原表名失效

空格

SELECT 字段名1 别名1, 字段名2 FROM 表名

AS(首选)

SELECT 字段名1 AS 别名1, 字段名2 FROM 表名

双引号

在mysql可以使用单引号，但在标准里不允许，不建议使用单引号

SELECT 字段名1 "别名1", 字段名2 FROM 表名

着重号

``,当与保留字、关键字冲时使用着重号

SELECT * FROM `order`

转义字符

默认以\为转义字符，例如配合LIKE的通配符使用

ESCAPE

用以指定转义字符

SELECT * FROM user WHERE id LIKE '_$_1' ESCAPE '$' 

显示表结构

DESCRIBE

DESC

两者效果相同

DESCRIBE 表名

隐式转换

在数字和字符串进行运算时会进行隐式转换，从字符串第一位开始提取连续数字，将其作为返回值，没有就返回0

字符集

数据库、表、字段都可以指定字符集。如果没有设置默认向上，my.ini为最终设置(utf8)

CREATE DATABASE db CHARACTER SET ‘gbk’ # 数据库指定字符集

CREATE TABLE tb(id INT) CHARACTER SET 'utf8' # 表指定字符集

CREATE TABLE tb(id INT CHARACTER SET 'utf8')  # 字段指定字符集

字段相关操作