MySql学习笔记

这是我之前在慕课网学习 与MySQL的零距离接触 时的笔记,整理了一下发上来。

修改MySQL提示符

参数 描述
\p 完整的日期
\d 当前数据库
\h 服务器名称
\u 当前用户

各个参数可以结合使用。如prompt \u@\h \d>

  • 连接客户端时指定

    1
    mysql -uroot -p123456 --prompt \h
  • 连接上客户端后

    1
    PROMPT "提示符"

MySQL常用命令

  • 显示当前服务器版本SELECT VERSION();
  • 显示当前日期时间SELECT NOW();
  • 显示当前用户SELECT USER();
  • 可用SHOW WARNINGS;查看警告信息

MySQL语句规范

  • 关键字与函数名称全部大写
  • 数据库名称、表名称、字段名称全部小写
  • SQL语句必须以分号结尾

操作数据库

“{}”表示必选项, “|”表示或, “[]”表示可选项

  • 创建数据库
    1
    CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;

例如:

1
CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET utf8;

  • 查看当前服务器下数据表列表

    1
    SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr];
  • 修改数据库

    1
    ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name;

例如:

1
ALTER DATABASE db_name CHARACTER SET = gbk;

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

数据类型

  • 整型
数据类型 字节
TINYINT 1
SMALLINT 2
MEDIUMINT 3
INT 4
BIGINT 8
  • 浮点型
    • FLOAT[(M,D)]
    • DOUBLE[(M,D)]

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<=65535
TINYTEXT L+1个字节,其中L<2^8
TEXT L+2个字节,其中L<2^16
MEDIUMTEXT L+3个字节,其中L<2^24
LONGTEXT L+4个字节,其中L<2^32
ENUM(‘value1’,’value2’,…) 1或2个字节,取决于枚举值的个数(最多65536个值)
SET(‘value1’,’value2’,…) 1、2、3、4或8个字节,取决于set成员数目(最多64个成员)

数据表

创建数据表

1
2
3
4
CREATE TABLE [IF NOT EXISTS] table_name (
column_name data_type,
...
);

例如:

1
2
3
4
5
CREATE TABLE table_name(
username VARCHAR(20),
age TINYINT UNSIGNED,
salary FLOAT(8,2) UNSIGNED
);

UNSIGNED表示无符号位,即始终为正数。使用SHOW CREATE TABLE table_name;可以查看建表语句

查看数据表

1
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];

查看数据表结构

1
SHOW COLUMNS FROM table_name;

或:

1
DESC table_name;

插入记录

1
INSERT [INTO] table_name[(col_name,...)] VALUES(val,...);

查找记录

1
SELECT expr,... FROM table_name;

空值与非空

  • NULL,字段值可以为空(可以不写,默认可以为空)
  • NOT NULL,字段值禁止为空

自动编号

AUTO_INCREMENT ,必须与主键组合使用,默认情况下,起始值为1,每次增量为1,不需要赋值

主键约束

PRIMARY KEY ,每张表只能存在一个主键,用于保证记录的唯一性(不能有重复值),主键自动为NOT NULL

唯一约束

UNIQUE KEY ,可以保证记录唯一性,唯一约束的字段可以为空(NULL),每张表可以存在多个唯一约束

默认约束

DEFAULT ,默认值,插入记录时,如果没有明确为字段复制,则自动赋予默认值

外键约束

作用:保证数据的一致性、完整性;实现一对一或一对多关系

要求:

  • 父表(子表所参照的表)和子表(具有外键列的表)必须使用相同的存储引擎(InnoDB),且禁止使用临时表
  • 外键列(有FOREIGN KEY关键字的列)和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度可以不同。否则会报错(errorno:150)
  • 外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL会自动创建索引(主键都会自动创建索引).以网格形式查看索引:SHOW INDEXES FROM db_name\G;

编辑数据表默认存储引擎在安装MySQL的磁盘的programData\MySQL\MySQL Server 5.7\my.ini文件里,修改为default-storage-engine=INNODB之后重启MySQL服务。

例:

1
2
3
4
CREATE TABLE provinces(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL
);

1
2
3
4
5
6
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id)
);

外键约束的参照操作

  • CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
  • SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果适用此项,必须保证兹表列没有制定NOT NULL
  • RESTRICT:拒绝对父表的删除或更新操作
  • NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同

例:

1
2
3
4
5
6
CREATE TABLE users1(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
);

表示级联删除,此时删除provinces表中的记录,user1中相关的记录也会被删除,级联更新ON UPDATE CASCADE同理

表级约束与列级约束

  • 对一个数据列建立的约束,称为列级约束。既可以在列定义时声明,也可以在列定义后声明(NOT NULLDEFAULT约束只有列级约束,其他几种约束则可以有表级也可以有列级约束)
  • 对多个数据列建立的约束,称为表级约束。只能在列定义后声明

修改数据表

添加单列

1
ALTER TABLE table_name ADD [COLUMN] column_name column_definition [FIRST | AFTER column_name;

其中,FIRST指把新列放在最前面,AFTER指把新列放在指定列,不加此参数默认添加到所有列最后面

添加多列

1
ALTER TABLE table_name ADD [COLUMN] (column_name column_definition, ...);

添加多列时,要加小括号,且不能制定添加的位置

删除列

1
ALTER TABLE table_name DROP [COLUMN] column_name;

可以同时操作几个语句,用逗号隔开,如:

1
ALTER TABLE table_name DROP [COLUMN] column_name,DROP [COLUMN] column_name,ADDADD [COLUMN] column_name column_definition;

添加主键约束

1
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, ...);

例:

1
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);

删除主键约束

1
ALTER TABLE table_name DROP PRIMARY KEY;

添加唯一约束

1
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name, ...);

例:

1
ALTER TABLE users2 ADD UNIQUE (username);

删除唯一约束

要删除唯一约束需要先知道约束的名字:

1
SHOW INDEXES FROM table_name\G;

1
ALTER TABLE table_name DROP {INDEX | KEY} index_name;

添加外键约束

1
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference_difinition;

例:

1
ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);

删除外键约束

删除外键约束也要先知道约束的名字:

1
SHOW CREATE TABLE table_name;

1
ALTER TABLE table_name DROP FOREIGN KEY fk_symbol;

添加/删除默认约束

1
ALTER TABLE table_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};

例:

1
2
ALTER TABLE users2 ALTER age SET DEFAULT 15;
ALTER TABLE users2 ALTER age DROP DEFAULT;

修改列定义

1
ALTER TABLE table_name MODIFY [COLUMN] col_name column_difinition [FIRST | AFTER col_name];

例:

1
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;

注意: 由于存储范围不同,数据类型由大类型改为小类型时可能会导致数据丢失

修改列名称

1
ALTER TABLE table_name CHANGE [COLUMN] old_col_name new_col_name column_difinition [FIRST | AFTER col_name];

使用CHANGE语句既可以修改列名称也可以修改列定义

修改数据表名

1
ALTER TABLE table_name RENAME [TO | AS] new_table_name;

或:

1
RENAME TABLE table_name TO new_table_name [, table_name2 TO new_table_name2] ... ;

RENAME TABLE这个语句可以修改多个数据表的名字

例:

1
2
ALTER TABLE users2 RENAME users3;
RENAME TABLE users3 TO users2,users1 TO users4;

尽量少使用修改数据列或数据表名的命令,因为在创建了索引或使用了视图或存储过程后,表名和列名被引用的情况下,更名可能会导致视图等无法正常工作

操作记录

插入记录

第一种方式:

1
INSERT [INTO] table_name [(col_name, ...)] {VALUES | VALUE} ({expr | DEFAULE},...),(...),...;

插入的值可以写数学表达式也可以写一些函数如md5('123'),也可以一次插入多条记录
插入时把AUTO_INCREMENT的字段赋值为NULLDEFAULT都会自动递增

第二种方式:

1
INSERT [INTO] table_name SET col_name={expr | DEFAULT},...;

与第一种方式的区别在于,此方法可以使用子查询(SubQuery),而且一次只能插入一条记录

第三种方式:

1
INSERT [INTO] table_name [(col_name,...)] SELECT ...;

此方法可以将SELECT查询结果插入到指定数据表,见下文子查询

更新记录(单表更新)

1
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1 | DEFAULT} [, col_name2={expr2 | DEFAULT}] ... [WHERE where_condition];

可以一次更新多条记录,不加WHERE会更新所有记录

例:

1
UPDATE users SET age = age + 10 WHERE id % 2 = 0;

删除记录(单表删除)

1
DELETE FROM table_name [WHERE where_condition];

查询记录

1
2
3
4
5
6
7
8
9
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}]
];
查询表达式select_expr
  • 每一个表达式表示想要的一列,必须有至少一个
  • 多个列之间以英文逗号分隔
  • 星号(*)表示所有列。table_name.*可以表示命名表的所有列
  • 查询表达式可以使用[AS] alias_name为其赋予别名
  • 别名可用于GROUP BY, ORDER BYHAVING子句
条件表达式

对记录进行过滤,如果没有指定WHERE子句,则显示所有记录。在WHERE表达式中,可以使用MySQL支持的函数或运算符

查询结果分组

[GROUP BY {col_name | position} [ASC | DESC],...]ASC为升序DESC为降序,默认是升序

分组条件

[HAVING where_condition],条件中若使用字段,要保证字段出现在select_expr中,或者使用聚合函数,如count()

对查询结果进行排序

[ORDER BY {col_name | expr |position} [ASC | DESC],...]position指字段在select_expr中出现的位置,推荐尽量直接指定字段名

限制查询结果返回的数量
1
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

例:
SELECT * FROM users LIMIT 3,2;表示从第四条开始(编号从0开始,且顺序是查询结果的顺序,受ORDER BY等影响),返回共两条数据

子查询

子查询(SubQuery) 是指出现在其他SQL语句内的SELECT子句
如:

1
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);

其中 SELECT * FROM t1 称为 Outer Query[外查询](或者Outer Statement) , SELECT col2 FROM t2 称为 SubQuery[子查询]

  • 子查询是嵌套在外查询内部,也有可能在子查询内部再嵌套子查询。而且子查询必须出现在圆括号之间
  • 子查询可以包含多个关键字或条件,如DISTINCT, GROUP BY, ORDER BY, LIMIT, 函数等
  • 子查询的外层查询可以是SELECT, INSERT, UPDATE, SET或DO
  • 子查询的返回值可以是标量、一行、一列或子查询

    使用比较运算符的子查询

    比较运算符:=, >, <, >=, <=, <>, !=, <=>

语法结构:operand comparison_operator [ANY | SOME | ALL] subquery

例:

1
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods) ORDER BY goods_price DESC;

AVG(), MAX(), MIN(), COUNT(), SUM()等为聚合函数,聚合函数只有一个返回值
使用比较运算符时,如果子查询返回值多于一行记录可能会报错,这时需要用ANY, SOME或ALL修饰:

ANY SOME ALL
>, >= 最小值 最小值 最大值
<, <= 最大值 最大值 最小值
= 任意值 任意值
<>, != 任意值

例:

1
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;

使用[NOT] IN 的子查询

语法结构:operand comparison_operator [NOT] IN (subquery)

注意: =ANY=SOME运算符与IN等效;!=ALL或<>ALL运算符与NOT IN等效

使用[NOT] EXISTS的子查询

如果子查询返回任何行,EXISTS将返回TRUE,否则返回FALSE

使用INSERT...SELECT插入记录

例:

1
INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

多表更新

1
UPDATE table_references SET col_name1={expr1 |DEFAULT} [, col_name2={expr2 | DEFAULT}]... [WHERE where_condition];

多表更新时可能会出现字段名混淆,这时候一般会使用AS给表起别名,如:

1
UPDATE tdb_goods AS  g  INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;

创建表同时插入记录CREATE...SELECT

1
CREATE TABLE [IF NOT EXISTS] table_name [{create_definition,...)] select_statement;

例:

1
2
3
4
CREATE TABLE tdb_goods_brands (
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
) SELECT brand_name FROM tdb_goods GROUP BY brand_name;

连接

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

连接类型:

  • INNER JOIN内连接。MySQL中,JOIN, CROSS JOIN和INNER JOIN是等价的
  • LEFT [OUTER] JOIN左外连接
  • RIGHT [OUTER] JOIN右外连接

连接条件ONWHERE

  • 通常使用ON关键字来设定连接条件
  • 使用WHERE关键字进行结果集记录的过滤

语法结构:table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} table_reference ON conditional_expr

数据表参照table_reference

1
table_name [[AS] alias] | table_subquery [AS] alias
  • 数据表可以使用table_name AS alias_nametable_name alias_name赋予别名
  • table_subquery可以作为子查询使用在FROM子句中,这样的子查询必须为其赋予别名

内连接

显示左表及右表符合连接条件的记录,即两表的公共部分

左外连接

显示左表的全部记录及右表符合连接条件的记录

右外连接

显示右表的全部记录及左表符合连接条件的记录

多表连接

通过内连接实现查询所有商品的详细信息:

1
2
3
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;

通过左外连接实现查询所有商品的详细信息:

1
2
3
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
LEFT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
LEFT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

通过右外连接实现查询所有商品的详细信息:

1
2
3
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
RIGHT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
RIGHT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

一些说明

  1. 对于左外连接A LEFT JOIN B join_condition(右外连接类似):
    • 数据表B的结果集依赖数据表A
    • 数据表A的结果集根据左连接条件以来所有数据表(B表除外)
    • 左外连接条件决定如何检索数据表B(在没有制定WHERE条件的情况下)
    • 如果数据表A的某条记录符合WHERE条件,但是数据表B不存在符合连接条件的记录,将生成一个所有列为空的额外B行
  2. 如果使用内连接查找的记录在连接数据表中不存在,并且在WHERE子句中尝试以下操作:col_name IS NULL时,如果col_name被定义为NOT NULL,MySQL将在找到符合连接条件的记录后停止搜索更多的行

无限分类的数据表设计

例:

1
2
3
4
5
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);

parent_id 就是该类的父类,来自 type_id,没有父类(即最大类)的 type_id为0

这种数据表的查询用到自身连接(同一个数据表对其自身进行连接)如:

  • 查找所有分类及其父类:

    1
    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;
  • 查找所有分类及其子类:

    1
    SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS  s ON s.parent_id = p.type_id;
  • 查找所有分类及其子类的数目

    1
    SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;

多表删除

1
DELETE table_name[.*] [, table_name[.*]] ... FROM table_references [WHERE where_condition];

例如删除重复记录:

1
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2  ON t1.goods_name = t2.goods_name  WHERE t1.goods_id > t2.goods_id;

字符函数

函数名称 描述
CONCAT() 字符连接
CONCAT_WS() 使用指定的分隔符进行字符连接
FORMAT() 数字格式化,此函数返回值是字符型
LOWER() 转换成小写字母
UPPER() 转换成大写字母
LEFT() 获取左侧字符
RIGHT() 获取右侧字符
LENGTH() 获取字符串长度(包括空格)
LTRIM() 删除字符串的前导空格
RTRIM() 删除字符串的后续空格
TRIM() 删除前导和后续空格
SUBSTRING() 字符串截取
[NOT] LIKE 模式匹配
REPLACE() 字符串替换

注意:表示字符位置的数字是从1开始而不是从0开始的,起始位置的数值可以是负值,表示从后往前数
例:

SELECT CONCAT_WS('|','A','B','C');结果:A|B|C

SELECT FORMAT(12560.75, 1);结果:12,560.8

SELECT LOWER(LEFT('MySQL', 2));结果:my。表示把截取的前两位字符变为小写

SELECT TRIM(LEADING '?' FROM '??My??SQL???');结果:My??SQL???。这里的LEADING表示删除前导的指定字符,TRAILING表示删除后续,BOTH表示前导和后续都删除

SELECT REPLACE('??My??SQL???', '?', '');结果:MySQL

SELECT SUBSTRING('MySQL', 1, 2);结果:My。这里表示从第一位截取,截取两位

SELECT 'MYSQL' LIKE 'M%';结果:11表示TRUE%表示任意0个或多个字符,另外还有_下划线表示任意一个字符

假设数据表test有个first_nametom%的数据,要查找出此记录,就要SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1';,表示1后面的%为普通字符串而非通配符

数值运算符与函数

名称 描述
CEIL() 进一取整(向上取整)
DIV 整除,如3 DIV 40
FLOOR() 舍一取整(向下取整)
MOD 取余数(取模,与%相同)
POWER() 幂运算
ROUND() 四舍五入
TRUNCATE() 数字截取

例:

SELECT POWER(2,3); 结果:8

SELECT TRUNCATE(125.59,1);结果:125.5。保留小数点后一位,舍去后面的,与四舍五入不同

比较运算符与函数

名称 描述
[NOT] BETWEEN…AND… [不]在范围之内
[NOT] IN() [不]在列出值范围内
IS [NOT] NULL [不]为空

例:

SELECT 35 BETWEEN 1 AND 35;结果:1

SELECT 13 NOT IN(5, 10, 15);结果:1

SELECT 0 IS NOT NULL;结果:1

日期时间函数

名称 描述
NOW() 当前日期和时间
CURDATE() 当前日期
CURTIME() 当前时间
DATE_ADD() 日期变化
DATEDIFF() 日期差值
DATE_FORMAT() 日期格式化

例 :

SELECT DATE_ADD('2017-11-23', INTERVAL -365 DAY);结果:2016-11-23

SELECT DATE_ADD('2017-11-23', INTERVAL 3 WEEK);结果:2017-12-14

SELECT DATEDIFF('2017-11-23', '2018-11-23');结果:-365

SELECT DATE_FORMAT('2017-11-23', '%m/%d/%Y');结果:11/23/2017

信息函数

名称 描述
CONNECTION_ID() 连接ID
DATABASE() 当前数据库
LAST_INSERT_ID() 最后插入的记录的ID号
USER() 当前用户
VERSION() 版本信息

对于LAST_INSERT_ID()函数,需要数据表里有一个AUTO_INCREMENTid字段。另外,若是同时插入多条记录,如INSERT test(name,age) VALUES('Tom',20),('Jack',22);,这个函数只会返回第一个插入的也就是Tomid

聚合函数

名称 描述
AVG() 平均值
COUNT() 计数
MAX() 最大值
MIN() 最小值
SUM() 求和

聚合函数的典型特点:只有一个返回值

信息函数

名称 描述
MD5() 信息摘要算法
PASSWORD() 密码算法

如果密码是为WEB页面准备的,建议使用MD5(),而PASSWORD()主要用于修改MySQL的用户密码

自定义函数

  • 用户自定义函数(user-defined function, UDF)是一种对MySQL扩展的途径,其用法与内置函数相同
  • 两个必要条件:参数(可以有0个或多个)、返回值(只能有一个)
  • 函数可以返回任意类型的值,同样可以接收这些类型的参数;参数与返回值之间没有必然的内在联系

创建自定义函数

1
CREATE FUNCTION function_name RETURNS {STRING | INTEGER | REAL | DECIMAL} routine_body;

删除自定义函数用DROP FUNCTION [IF EXISTS] function_name;

关于函数体routine_body

  • 函数体由合法的SQL语句构成
  • 函数体可以是简单的SELECTINSERT语句
  • 函数体如果为复合结构则使用BEGIN...END语句
  • 复合结构可以包含声明、循环、控制结构

例:

1
2
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');

1
2
3
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;

有时候需要创建有复合结构函数体的自定义函数,由于分隔符;被认为是SQL语句的结束,所以要先执行DELIMITER //修改SQL语句的结束符

1
2
3
4
5
6
7
CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT test(username) VALUES (username);
RETURN LAST_INSERT_ID();
END
//

之后就可以把分隔符修改回来和使用函数了:

1
2
DELIMITER ;
SELECT adduser('Rose');

存储过程

  • 存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理
  • 存储过程存储在数据库内,可以由应用程序调用执行,允许用户声明变量以及进行流程控制
  • 存储过程可以接收输入和输出类型的参数且可以存在多个返回值
  • 存储过程只在第一次进行语法分析和编译,以后都直接调用编译结果,省略了编译环节

优点:

  • 增强SQL语句的功能和灵活性
  • 实现较快的执行速度
  • 减少网络流量

创建存储过程

1
2
3
4
5
CREATE
[DEFINER = {user | CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...]
routine_body
  • DEFINER是创建者,省略的话默认是当前登录到MySQL的用户
  • sp_name是存储过程的名字
  • proc_parameter是参数,可以有0到多个,写法:[IN | OUT | INOUT]param_name type注意: 参数名不要和数据表字段名重名
    • IN,表示该参数的值必须在调用存储过程时指定,该值不能被返回
    • OUT,表示该参数的值可以被存储过程改变,并且可以被返回
    • INOUT,表示该参数的值在调用时指定,并且可以被改变和返回
  • characteristic特性,与自定义函数相似:

    • COMMENT:注释
    • CONTAINS SQL:包含SQL语句,但不包含读或写数据的语句
    • NO SQL:不包含SQL语句
    • READS SQL DATA:包含读数据的语句
    • MODIFIES SQL DATA:包含写数据的语句
    • SQL SECURITY {DEFINER | INVOKER}:指明谁有权限来执行
      1
      COMMENT 'string' | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY {DEFINER | INVOKER}
  • routine_body过程体:

    • 过程体由合法的SQL语句构成
    • 过程体可以是任意SQL语句(主要指对记录的增删改查及多表连接等而非对数据库数据表本身的修改)
    • 过程体如果为复合结构则使用BEGIN...END语句
    • 复合结构可以包含声明、循环、控制结构

调用存储过程

1
2
CALL sp_name([parameter[,...]]);
CALL sp_name[()];

修改存储过程

和修改自定义函数相似,只能修改以下简单的选项,不能修改过程体,要修改过程体只能删除该存储过程然后重新创建

1
ALTER PROCEDURE sp_name [characteristic ...] COMMENT 'string' | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY {DEFINER | INVOKER};

删除存储过程

删除时不用带参数,只用存储过程名称

1
DROP PROCEDURE [IF EXISTS] sp_name;

创建不带参数的存储过程

例:

1
CREATE PROCEDURE sp1() SELECT VERSION();

调用:因为不带参数,所以CALL sp1;CALL sp1();都可以

创建带有IN类型参数的存储过程

和创建自定义函数一样要先使用DELIMITER //修改结束符

1
2
3
4
5
6
7
8
9
DELIMITER //

CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM test WHERE id = p_id;
END
//

DELIMITER ;

创建带有IN和OUT类型参数的存储过程

1
2
3
4
5
6
7
8
9
10
DELIMITER //

CREATE PROCEDURE removeUserAndReturnUserCount(IN p_id INT UNSIGNED,OUT userCount INT UNSIGNED)
BEGIN
DELETE FROM test WHERE id = p_id;
SELECT count(id) FROM test INTO userCount;
END
//

DELIMITER ;

调用:

1
2
CALL removeUserAndReturnUserCount(37,@nums);
SELECT @nums;

其中,@nums是变量,在BEGIN...END间用DECLEAR声明的变量叫局部变量,作用域只在BEGIN...END之间。而且在BEGIN...ENDDECLEAR语句必须位于第一行。而像上面SELECT...INTO para_nameSET @para_name = ...声明的变量叫用户变量,对当前用户使用的客户端有效

创建带有多个OUT类型参数的存储过程

ROW_COUNT() 函数可以得到被影响(即插入、删除或更新)的记录数

1
2
3
4
5
6
7
8
9
10
11
DELIMITER //

CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED, OUT deleteUsers SMALLINT UNSIGNED, OUT userCounts SMALLINT UNSIGNED)
BEGIN
DELETE FROM test WHERE age = p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM test INTO userCounts;
END
//

DELIMITER ;

调用:

1
2
CALL removeUserByAgeAndReturnInfos(20,@a,@b);
SELECT @a,@b;

存储过程与自定义函数的区别

  • 存储过程实现的功能要复杂一些;而函数的针对性更强(针对表做操作一般使用存储过程)
  • 存储过程可以返回多个值;函数只能有一个返回值
  • 存储过程一般独立来执行;而函数可以作为其他SQL语句的组成部分来出现

二者注意事项:

  • 创建存储过程或者自定义函数时需要通过delimiter语句修改定界符
  • 如果函数体或过程体有多个语句,需要包含在BEGIN...END语句块中

存储引擎

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

分类:

  • MyISAM
  • InnoDB
  • Memory
  • Archive
  • CSV(不支持索引)
  • BlackHole:黑洞引擎,写入的数据都会消失,一般用于做数据复制的中继

并发控制

当多个连接对记录进行修改时要保证数据的一致性和完整性,就要使用并发控制。处理并发读或并发写时系统会使用锁系统:

  • 共享锁(读锁):在同一时间段内,多个用户可以读取同一个资源,读取过程中数据不会发生任何变化
  • 排它锁(写锁):在任何时候只能有一个用户写入资源,当进行写锁时会阻塞其他的读锁或者写锁操作

锁颗粒:

  • 表锁,是一种开销最小的锁策略
  • 行锁,是一种开销最大的锁策略

事务处理

事务用于保证数据库的完整性

事务的特性:

  • 原子性(Atomicity)
  • 一致性(Consistency)
  • 隔离性(Isolation)
  • 持久性(Durability)

外键和索引

外键是保证数据一致性的策略;索引是对数据表中一列或多列的值进行排序的一种结构,包括:普通索引、唯一索引、全文索引、btree索引、hash索引……

各种存储引擎的特点

特点 MyISAM InnoDB Memory Archive
存储限制 256TB 64TB
事务安全 - 支持 - -
支持索引 支持 支持 支持 -
锁颗粒 表锁 行锁 表锁 行锁
数据压缩 支持 - - 支持
支持外键 - 支持 - -

使用得比较多的是MyISAM和InnoDB。MyISAM适用于事务的处理不多的情况;InnoDB适用于事务处理较多,需要有外键支持的情况

修改存储引擎

  • 通过修改MySQL配置文件实现:default-storage-engine= engine_name,默认是InnoDB
  • 通过创建数据表或修改数据表命令实现:
    1
    2
    3
    4
    CREATE TABLE table_name(
    ...
    ...
    )ENGINE = engine_name;
1
ALTER TABLE table_name ENGINE [=] engine_name;