MySQL 思维导图模板_ProcessOn思维导图、流程图

数据类型

整型

TINYINT 1字节

SMALLINT 2字节

MEDIUMINT 3字节

INT 4字节

BIGINT 8字节

浮点型

FLOAT [(M,D)]

M：数字总位数

D：小数点后的位数

ps：M和D被省略则根据硬件
允许的来保存数值

日期时间型
括号内为存储需求

YEAR(1)

TIME(3)

DATE(3)

DATETIME(8)

TIMESTAMP(4)

字符型

CHAR(m) m字节, 0<=m<=255

VARCHAR(m) L+1字节, L<=m且0<=m<=255

TINYTEXT L+1字节, L<2^8

TEXT L+3字节, L<2^16

MEDIUMTEXT L+3字节, L<2^24

LONGTEXT L+4字节, L<2^32

ENUM('Value 1', 'Value2', ...) 1或2字节, 最多枚举25535个

SET('Value 1', 'Value2', ...) 1,2,3,4或8字节, 取决于set成员数(最多64)

约束

默认值 DEFAULT：

当插入记录时，如果没有明确字段赋值则自动赋予默认值。

例：CREATE TABLE tb4(..., ..., ..., sex ENUM('1', '2', '3') DEFAULT '3');

主键约束 PRIMARY KEY：

每张表只能存在一个主键

保证记录的唯一性

自动为 NOT NULL

唯一约束 UNIQUE KEY：

每张表可以存在多个唯一约束

保证记录的唯一性

可以为 NULL

例：CREATE TABLE tb4(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL UNIQUE KEY,
age TINYINT UNSIGNED);
INSERT tb4(name, age) VALUES ('Tom', 22); p.s:再写一条一样的tom会出错

外键约束 FOREIGN KEYp

保持数据一致性和参照完整性

实现一对一或一对多的关系

要求

父表（被参照表）和子表（参照表）用相同的存储引擎，禁止使用临时表

引擎只能为InnoDB

外键列和参照列具有线割弄得数据类型
（数字的长度或是否有符号位这些必须相同，字符长度可以不同）

外键列和参照列必须创建索引，外键列索引没有的话MySQL会自动创建

修改引擎：在my.ini文件中改 default-strorage-engine=INNODB

外键约束的参照操作

CASCADE(级联)：从父表删除/更新时自动删除/更新子表中匹配的行

SET NULL：从父表中删除/更新行，并设置子表外键为NULL（需要相应的子表列没有设置NOT NULL）

RESTRICT(拒绝)：拒绝对父表进行删除/更新操作

NO ACTION(无动作)：标准的SQL关键字，与RESRICT作用相同

例：在定义表时有这样一条语句（删除级联）
FOREIGN KEY (pid) REFERENCES province (id) ON DELETE CASCADE

表级/列级约束：

列级约束：对一个数据列建立的约束（可以在列定义时声明也可以在列定义后声明，如上面tb4的主键）

表级约束：对多个数据列建立的约束（只可以在列定义后声明）

子查询

定义

定义：子查询（SubQuery）是指出现在其他SQL语句内的SLECT子句
例：SELECT * FROM t1 WHERE col=(SELECT col2 FROM t2);

备注

子查询嵌套在查询内部，且必须始终出现在圆括号内

子查询可以包含多个关键字或条件
如 DISTINCT, GROUP BY, ORDER BY等

子查询的外层查询可以是SELECT, INSERT, UPDATE, SET或DO

子查询可以返回标量、一行、一列或子查询

使用比较运算符的子查询

比较运算符 operand：
=, <, >, >=, <=, <>, !=, <=>
语法：operand comparison_operator subquery

用ANY, SOME或ALL修饰的比较运算符

语法

operand comparison_operator ANY(subquery)

operand comparison_operator SOME(subquery)

operand comparison_operator ALL(subquery)

例：SELECT goods_id, goods_name, goods_price FROM tdb_goods
WHERE goods_price >= ANY(SELECT goods_price FROM tdb_goods
WHERE goods_cate='超极本');

返回值表

使用 [NOT] IN的子查询

语法：operand comparison_operator [NOT] IN (subquery)

备注：“=ANY”运算符与 IN 等效
“!=ALL”或“<>ALL”运算符与 NOT IN 等效

INSERT ... SELECT 将查询结果写入数据表：
INSERT [INTO] tbl_name [(col_name, ...)]
SELECT ...;
例：INSERT tdb_goods_cates(cate_name)
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

连接

定义

MySQL在SELECT语句，多表更新，多表删除语句中支持JOIN操作

语法（表的参照关系）：
table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} table_reference
ON conditional_expr;
p.s：JOIN=CROSS JOIN=INNER JOIN
INNER表示内连接，LEFT/RIGHT OUTER表示左/右外连接
例：UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate=cate_name
SET goods_cate=cate_id;

内连接（INNER JOIN）
A与B的交集

ON 关键字设定连接条件，也可以用WHERE代替
通常用ON设定连接条件，用WHERE进行结果记录的过滤
例：查询所有商品的详细信息
SELECT goods_id, goods_name, cate_name, brand_name,goods_price
FROM tdb_goods AS g
INNER JOIN tdb_goods_cates AS c ON g.cate_id=c.cate_id
INNER JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id\G;

外连接

左外连接（LEFT JOIN）：显示左表全部的记录以及右表符合条件的记录

右外连接（RIGHT JOIN）：显示右表全部的记录以及左表符合条件的记录

多表连接（两张表以上的连接）
参照内连接例子的写法

自身连接
（无限级分类表设计）

同一数据表对其自身进行连接（想象有相同的一张表当左表或右表进行多表连接）
例：SELECT s.type_id, s.type_name, p.type_name
FROM tdb_goods_types AS s
LEFT JOIN tdb_goods_types AS p
ON s.parent_id=p.type_id;

关于连接的说明

A LEFT JOIN B join_condition

表B结果集依赖表A

A的结果集根据左连接条件依赖所有数据表（B除外）

左外连接条件决定如何检索表B（没有WHERE）

如果A的某条记录符合WHERE条件，但在B不存在符合条件的记录，
将生成所有列为空的额外B行

如果用内连接查找的记录在连接数据表中不存在，且在WHERE子句中尝试以下操作：
col_name IS NULL时，如果col_name被定义为NOT NULL，
MySQL将在符合连接条件的记录后停止搜索更多行

运算符和函数

一些内置函数和运算符

字符函数

例：1)SELECT CONCAT('imooc', '-', 'MySQL'); =>imooc-MySQL
2)SELECT CONCAT(first_name, last_name) AS fullname FROM test; #first_name和last_name是列名
3)SELECT CONCAT_WS('|', 'A', 'B', 'C'); =>A|B|C #第一个是指定分隔符
4)SELECT LOWER('MySQL'); =>mysql
5)SELECT LEFT('MySQL', 2); =>My #2代表位数
6)SELECT LOWER(LEFT('MySQL', 2)); =>my #函数嵌套

7)SELECT TRIM(' MySQL '); =>MySQL #去掉了空格
8)SELECT LENGTH(LTRIM(' MySQL ')); =>8
9)SELECT TRIM(LEADING '?' FROM '???MySQL??'); =>MySQL??
  SELECT TRIM(TAILING '?' FROM '???MySQL??'); =>???MySQL
SELECT TRIM(BOTH '?' FROM '???MySQL??'); =>MySQL
10)SELECT REPLACE('??My??SQL???', '?', ''); =>MySQL #将?替换成了空字符
  SELECT REPLACE('??My??SQL???', '?', '!!'); =>!!!!My!!!!SQL!!!!!!
  SELECT REPLACE('??My??SQL???', '??', '!'); =>!My!SQL!?
11)SELECT SUBSTRING('MySQL', 1, 2); =>My #1表示起始位置，2表示截取长度，长度不指定则截取到最后
  SELECT SUBSTRING('MySQL', 3); =>SQL
  SELECT SUBSTRING('MySQL', -1); =>L
12)SELECT * FROM test WHERE first_name LIKE '%o%'; =>Tom%
  SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1'; =>Tom% #告诉其1后面的%不是通配符

数值运算符及其函数

例：1)SELECT 3+4; =>7
2)SELECT CEIL(3.01); =>4
SELECT FLOOR(3.01); =>3
3)SELECT POWER(3, 3); =>27
4)SELECT ROUND(3.652, 1); =>3.7
5)SELECT TRUNCATE(125.89, 1); =>125.8

比较运算符及函数

例：1)SELECT 5 BETWEEN 1 AND 22; =>1 #1表示true
2)SELECT 10 IN (5, 10, 15, 20); =>1
3)SELECT NULL IS NULL; =>1
4)SELECT '' IS NULL; =>0 #0表示false

日期时间函数

例：1)SELECT DATE_ADD('2018-4-5', INTERUAL 365 DAY); =>2019-4-5
#与365 DAY相似的表达还有1 YEAR; 3 WEEK; 2 MONTH等
SELECT DATE_ADD('2018-4-5', INTERUAL -365 DAY); =>2017-4-5
2)SELECT DATEDIFF('2018-4-5', '2019-4-5'); =>-365
3)SELECT DATE_FORMAT('2018-4-5', '%m/%d/%Y'); =>04/05/2018

信息函数

聚合函数

例：SELECT AUG(goods_price) AS AUG_PRICE FROM tdb_goods;

加密函数

例：1)SELECT MD5('admin');
2)SET password=PASSWORD('dimitar'); #改密码

MySQL自定义函数

定义

用户自定义函数（user-defined function，UDF）是一种对MySQL扩展的途径，其语法和内置函数类似

两个必要条件

参数

返回值

可以返回任意类型的值，同样也可以接收任意类型的参数

创建自定义函数

语法：
CREATE FUNCTION function_name
RETURNS
{STRING | INTERGER | REAL | DECIMAL}
routine_body;

p.s

routine_body是函数体

函数体有合法的SQL语句构成

函数体可以是简单的SELECT或INSERT语句

函数体如果为复合结构则使用BEGIN ... END结构体

复合结构可以还包含声明、循环、控制结构

创建不带参数的自定义函数
例：CREATE FUNCTION f1()
RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(), '%y年%m月%d月 %H点%i分%s秒');

创建带有参数的自定义函数
例：CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10, 2) UNSIGNED
RETURN (num1+num2)/2;

创建复合结构的自定义函数
例：DELIMITER //
CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT test(username) VALUES(username);
RETURN LAST_INSERT_ID();
END //
DELIMITER ;

删除自定义函数

语法：
DROP FUNCTION [IF EXISTS] function_name;

简单的操作

停止/启动MySQL服务

net stop mysql

net start mysql

MySQL登录与退出

登录（参数）

-D, --database=name 打开指定数据库

--delimiter=name 指定分隔符

-h, --host=name 服务器名称

-p, --password[=name] 密码

-P, --port=# 端口号

--prompt=name 设置提示符

-u, --user=name 用户名

-V, --version 输出版本信息并退出

举例

登录：mysql -uroot -p -P3306 -h

修改提示符

mysql> prompt \u@\h \D>

p.s:\D 完整日期
\d 当前数据库
\h 服务器名称
\u 当前用户

退出MySQL的三种方式

exit;

quit;

\q;

操作数据库

打开数据库：
USE db_name;
例：USE TEST;

创建数据库：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[DEFAULT] CHARACTER SET [=] charset_name;
例：CERATE DATABASE t1;

查看当前服务器下的数据表列表：
SHOW {DATABASE | SCHEMAS} [LIKE 'pattern' | WHERE expr];
例：SHOW DATABASE;

修改数据库：
ALTER {DATABASE | SCHEMA} [db_name]
[DEFAULT] CHARACTER SET [=] charset_name;

删除数据库：
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

数据表操作

定义：数据表（或表）是数据库的重要组成部分，为其他对象的基础

创建表：
CREATE TABLE [IF NOT EXISTS] table_name(colum_name data_type, ...);
例：CREATE TABLE tb1(username VARCHAR(20),
userage TINYINT UNSIGNED,
salary FLOAT(8,2) UNSIGNED);

AUTO_INCREMENT：自动编号且须与主键(primary key)组合使用，默认起始为1，每次增1，自动编号可以不用赋值
例：CREATE TABLE tb3(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL);

空值与非空

NULL

NOT NULL

插入记录(INSERT)：
INSERT [INTO] tbl_name [(col_name, ...)] VALUES (Val, ...);
例：INSERT tb1(username, userage, salary) VALUES ('Jhon', 25, 10000);

INSERT [INTO] tbl_name [(col_name, ...)] {VALUES | VALUE}
({expr | DEFAULT}, ...), (...), ...;
p.s:col_name省略的话默认为要全部字段都赋值
如果有一张表的第一个字段为：id SMALLINT PRIMARY KEY NOT NULL AUTO_INCREMENT
则可以赋值如 INSERT tbl VALUES (NULL, 'Tom', '123', 25);
INSERT tbl VALUES (NULL, 'Jhon', '456', 21); 这样的话就不会影响AUTO_INCREMENT的值

INSERT [INTO] tbl_name SET col_name={expr | DEFAULT};
p.s：和上面的语句相比，这条语句可以使用子查询(SubQuery)
例：INSERT users SET username='Bam', password='456', sex=1;
INSERT test SELECT username FROM users WHERE password='456';

INSERT [INTO] tbl_name [(col_name, ...)] SELECT ...;
此语句可以将查询结果插入到指定的数据表

更新记录(UPDATE)：

单表更新：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET
col_name1={expr1 | DEFAULT} [, col_name2={expr2 | DEFAULT}] ...
[WHERE where_condition];
例：UPDATE users SET age=age+5, sex=0;
UPDATE users SET age=age+10 WHERE id%2=0;

多表更新：
UPDATE tbl_references SET col_name1={expr1 | DEFAULT} [, col_name2={expr2 | DEFAULT}, ...]
[WHERE where_condition];

一步到位多表更新：
创建数据表的同时将查询结果写入到数据表（CREATE ... SELECT）
CREATE TABLE [IF NOT EXISTS] tdl_name [(create_definition, ...)] select_statement;

删除记录(DELETE)：

单表删除：
DELETE FROM tbl_name [WHERE where_condition];
p.s：在where表达式中可以使用MySQL支持的函数或运算符
例：DELETE FROM users WHERE id=6;

多表删除：
DELETE tbl_name1[.*] [, tbl_name2[.*], ...]
FROM tbl_references [WHERE where_condition];
例：DELETE t1 FROM tdb_goods AS t1
LEFT JOIN (SELECT goods_id, goods_name
FROM tdb_goods
GOUP BY goods_name HAVING count(goods_name) >= 2) AS t2
ON t1.goods_name=t2.goods_name
WHERE t1.goods_id > t2.goods_id;

查找(SELECT)：
SELECT expr, ... FROM tbl_name;

完整的select语句：
SELECT select_expr [, select_expr ...]
[ FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | position} [ASC | DESC], ...]
[HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC | DESC], ...]
[LIMIT {[offset, ] row_count | row_count OFFSET offset}]
];

查询表达式每一个表示想要的一列，多列间用逗号分隔

“*”表示所有列，tbl_name.* 可以命名所有列

查询表达式可以用 [AS] alias_name 为其赋予别名

别名可以用于 ORDER BY, GROUP BY 或 HAVING子句

查询结果分组(GROUP BY)：
[GROUP BY {col_name | position} [ASC | DESC], ...];
列名位置升序降序
例：SELECT name FROM users GROUP BY id DESC; (不写的话默认为升序)

分组条件(HAVING)：
[HAVING where_condition];
p.s：having后加聚合函数
例：SELECT name,age FROM users GROUP BY id HAVING age>10;
SELECT sex FROM users GROUP BY id HAVING count(id)>=2;

对查询结果进行排序(ORDER BY)：
[ORDER BY {col_name | expr | position} [ASC | DESC], ...];

限制查询结果返回的数量(LIMIT)：
[LIMIT {[offset, ] row_count | row_count OFFSET offset}];
例：SELECT * FROM users LIMIT 2;
SELECT * FROM users LIMIT 2,2; (第一个2为下标从0开始的第2行，与升降序无关；第二个2为返回两条结果)

修改数据表
(ALTER)

说明：ALTER命令是对建立好的数据表进行修改

数据表更名：

方法1：
ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name;

方法2：
RENAME TABLE tbl_name TO new_tbl_name;

添加列：

添加单列：
ALTER TABLE tbl_name ADD [COLUMN] col_name
col_definition [FIRST | AFTER col_name];
p.s：first为插入成为第一列；after col_name插入到指定的列后面
例：ALTER TABLE user1 ADD age
TINYINT UNSIGNED NOT NULL AFTER name;

添加多列（在现有的最后一列后面添加新列）：
ALTER TABLE tbl_name ADD [COLUMN] (col_name, col_definition);

删除列：
ALTER TABLE tbl_name DROP [COLUMN] col_name;
例：ALTER TABLE user1 DROP name, age;

修改列：

修改列定义：
ALTER TABLE tbl_name MODIFY [COLUMN] col_name
column_definition [FIRST | AFTER col_name];
例：ALTER TABLE user2 MODIFY id
SMALLINT UNSIGNED NOT NULL FIRST;

修改列名称（功能比修改列定义高一级）：
ALTER TABLE tbl_name CHANGE [COLUMN]old_col_name
new_col_name col_definition [FIRST | AFTER col_name];

添加约束：

添加主键约束：
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol] ]
PRIMARY KEY [index_type] (index_rol_name);
例：ALTER TABLE user2 ADD CONSTRAINT PK_user2_id
PRIMARY KEY(id);

添加唯一约束：
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol] ]
UNIQUE [INDEX | KEY] [index_name] [index_type]
(index_col_name, ...);

添加外键约束：
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol] ]
FOREIGN KEY [index_name] (index_col_name, ...) reference_definition;
例：ALTER TABLE user2 ADD FOREIGN KEY (pid) REFERENCES (id);

添加默认约束DEFAULT：
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal};
例：ALTER TABLE user2 ALTER age SET DEFAULT 15;

删除约束：

删除主键约束：ALTER TABLE tbl_name DROP PRIMARY KEY;

删除唯一约束：
ALTER TABLE tbl_name DROP {INDEX | KEY} index_name;
例：ALTER TABLE user2 DROP INDEX name;

删除外键约束：
ALTER TABLE tbl_name DROP FOREIGN KEY fk_name;
p.s：可以用 'SHOW CREATE TABLE tbl_name' 命令查看外键名
例：ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1;

删除默认约束：ALTER TABLE tbl_name ALTER [COLUMN] col_name {DROP DEFAULT};

SQL存储过程

过程

定义与特点

存储过程（procedure）是SQL语句和控制语句的预编译集合，
以一个名称存储并作为一个单元处理，只在第一次进行语句分析和编译，之后调用结果即可

特点

增强SQL语句的功能和灵活性

实现较快的执行速度

减少网络流量

创建存储过程：
CREATE
[DEFINER={user | CURRENT_USER}] （默认为当前用户）
PROCEDURE sp_name ([proc_parameter [, ...] ])
[characteristic ...] routine_body;
例：CREATE PROCEDURE sp1()
SELECT VERSION();

p.s：
proc_parameter定义为
[IN | OUT | INOUT] param_name type
过程体的语法结构和函数体要求相似，
routine_body里面如果要写多条语句则放到 BEGIN ... END 结构体里面

IN：输入input，该参数值在调用存储过程时指定
例：DELIMITER // #修改分隔符为 // 免得和 ; 冲突
CREATE PROCEDURE removeUserById (IN D_id INT UNSIGNED)
BEGIN
DELETE FROM user WHERE id=D_id;
END //
DELIMITER ; #改回 ; 为分隔符

CALL removeUserById(3);

OUT：输出output，该参数值可以被存储过程改变且可以返回
例：创建带有多个OUT类型参数的存储过程
DELIMITER //
CREATE PROCEDURE rmUserByIdAndRetInfos
(IN p_age SMALLINT UNSIGNED,
OUT deleteUsers SMALLINT UNSIGNED,
OUT userCounts SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age=p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM users INTO userCounts;
END //
DILIMITER ;

CALL rmUserByAgeAndRetInfos(20, @delu, @count);
SELECT @delu, @count;

INOUT：该参数在调用是指定且可以被改变和返回
例：DELIMITER //
CREATE PROCEDURE rmUserAndRetNums
(IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id=p_id;
SELECT count(id) FROM users INTO userNums;
END //
DELIMITER ;

CALL rmUserAndRetUserNums(18, @nums);
SELECT @nums;

MySQL变量

局部变量：
作用范围在begin和end之间，在语句块里面设置

用户变量：
以“@”开始，形式为“@变量名”
用户变量和MySQL客户端绑定，仅仅对当前使用客户端有效

全局变量

定义语法

set GLOBAL 变量名

set @@global.变量名

对所有客户端生效，只有具有super权限才能设置该变量

会话变量：只对连接的客户端生效

declare语句专门定义局部变量，set语句是设置不同类型的变量包括会话变量和全局变量

调用存储过程

CALL sp_name ([parameter [, ...] ]);
例：CALL sp1();

CALL sp_name [()];
例：CALL sp1;

存储过程和函数的区别

插入表格截图

MySQL存储引擎

简介

MySQL可以将数据以不同技术存储在文件（内存）中，这种技术成为存储引擎
每种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能

MySQL存储引擎分类

MyISAM

InnoDB

Memory

CSV