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();