mysql常用命令笔记。

一、mysql安装

1、mysql官网下载：https://dev.mysql.com/downloads/mysql/

2、mysql安装：

(1)windows端根据提示安装即可
(2)linux端输入命令sudo apt-get install mysql-server mysql-client，然后按照提示输入

3、mysql目录结构：

  • bin目录：存储可执行文件
  • data目录：存储数据文件
  • lib目录：存储库文件
  • include目录：存储包含的头文件
  • share目录：错误消息和字符集文件
  • docs目录：文档

4、启动/停止mysql服务：

(1) windows端:

  • 在windows服务列表里启动/停止mysql服务。
  • 通过cmd命令启动/停止mysql服务：
    • 启动mysql：net start mysql
    • 停止mysql：net stop mysql

(2)linux端:

  • 启动：service mysql start
  • 停止：service mysql stop
  • 重启：service mysql restart

5、允许远程连接

(1)找到找到mysql配置文件并修改

  • sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf，将bind-address=127.0.0.1注释

(2)登录mysql，运行命令

  • grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;
  • flush privileges;

(3)重启mysql

二、mysql操作

1、查看数据库版本：mysql -V

2、登录数据库：mysql -uroot -p123456 -P3306 -h127.0.0.1

参数 描述
-D, –database=name 打开指定数据库
–delimiter = name 指定分隔符
-h, –host=name 服务器名称
-p, –password[=name] 密码
-P, –port=# 端口号
–prompt=name 设置提示符
-u, –user=name 用户名
-V, –version 输出版本信息并退出

3、退出数据库：

  • exit;
  • quit;
  • \q;

4、修改mysql提示符：

  • 连接客户端时通过参数指定：mysql -uroot -p123456 --prompt 提示符
  • 连接上客户端后通过命令修改：prompt 提示符
参数 描述
\D 完整的日期
\d 当前数据库
\h 服务器名称
\u 当前用户

5、显示当前服务器版本：SELECT VERSION();
6、显示当前日期时间：SELECT NOW();
7、显示当前用户：SELECT USER();

三、数据库操作

注意，本文的命令格式中：

  • {}是必选项
  • |是选项中做选择
  • []是可选项

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

1
2
CREATE DATABASE test;
CREATE DATABASE test1 IF NOT EXISTS CHARACTER SET gbk;

2、查看数据库列表：
SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]

1
SHOW DATABASES;

3、显示数据库创建时所使用的指令：SHOW CREATE DATABASE test

4、打开数据库：USE db_name

5、查看当前数据库：SELECT DATABASE()

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

1
ALTER DATABASE test1 CHARACTER SET utf8;

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

1
2
DROP DATABASE test1;
DROP DATABASE IF EXISTS test1;

8、查看警告：SHOW WARNINGS;

四、数据表操作

1、数据类型:

(1)数值类型

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 字节 (-128，127) (0，255) 小整数值
SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度，浮点数值
DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度，浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

(2)字符串类型

类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过255个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据
ENUM(‘value1’,’value2’,…) 1或2个字节，取决于枚举值的个数(最多65535个值)
SET(‘value1’,’value2’,…) 1、2、3、4或8个字节，取决于set成员的数目(最多64个)

(3)日期类型

类型 大小 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2037-12-31 23:59:59 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

2、创建数据表：CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type, ...)

1
CREATE TABLE tb1( username VARCHAR(20),age TINYINT UNSIGNED,salary FLOAT(8,2) UNSIGNED);

3、查看数据表列表：SHOW TABLES [FROM db_name] [LINK 'pattern' | WHERE expr]

4、查看数据表结构：SHOW COLUMNS FROM table_name

1
SHOW COLUMNS FROM test;

5、约束条件：
约束保证数据的完整性、唯一性。约束分为表级约束和列级约束。

  • 主键约束：PRIMARY KEY
  • 空与非空：NULL/NOT NULL
  • 唯一约束：UNIQUE
  • 默认值：DEFAULT
  • 自动增加：AUTO_INCREMENT
  • 外键约束：FOREIGN KEY

(1)主键约束：PRIMARY KEY
主键约束不允许重复，也不允许为空。每个表只允许存在一个主键。
主键约束可以在列级别创建，也可以在表级别创建。

1
2
3
4
# 基本模式
CREATE TABLE test(id SMALLINT UNSIGNED PRIMARY KEY, username VARCHAR(30) NOT NULL;
# 组合模式
CREATE TABLE test1(id INT, name VARCHAR(20), pwd VARCHAR(20), primary key(id, name));

(2)空与非空：NULL/NOT NULL
非空约束保证当前列的值不为空值。非空约束只能列级别创建。
空约束是代表字段值可以为空，一般省略不写。

1
CREATE TABLE test(username VARCHAR(20) NOT NULL, age TINYINT UNSIGNED);

(3)唯一约束：UNIQUE
唯一约束保证记录的唯一性。唯一约束的字段可以为空。每个表可以许存在多个唯一约束。
唯一约束可以在列级别创建，也可以在表级别创建。

1
2
3
4
# 基本模式
CREATE TABLE test(id SMALLINT UNSIGNED, username VARCHAR(20) UNIQUE KEY, age TINYINT UNSIGNED);
#组合模式
CREATE TABLE test1(id INT NOT NULL, name VARCHAR(20), password VARCHAR(10), UNIQUE(name,password));

(4)默认值：DEFAULT
插入记录时，如果没有为字段赋值，则自动赋值默认值。默认值只能列级别创建。

1
CREATE TABLE test(id SMALLINT UNSIGNED, username VARCHAR(30), age TINYINT DEFAULT 0);

(5)自动增加：AUTO_INCREMENT
自动编号须与主键配合，只能在数值类型中使用。默认起始值为1，每次增量为1。

1
CREATE TABLE test(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20) NOT NULL);

(6)外键约束：FOREIGN KEY
外键约束保证一个或两个表之间的参照完整性。实现一对一或一对多的关系。
外键约束可以在列级别创建，也可以在表级别创建。

外键约束的要求：

  • 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
  • 数据表的存储引擎只能为InnoDB。(MySQL配置文件 default-storage-engine=INNODB)
  • 外键列和参照列必须具有相似的数据类型。而且数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
  • 外键列和参照列必须创建索引，如果外键列不存在索引的话MySQL将自动创建索引。
1
2
3
4
5
6
# 父表
CREATE TABLE provinces(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(20) NOT NULL);
# 子表
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES provinces(id));
# 查看数据表索引
SHOW INDEXES FROM provinces;

外键约束的参照操作：

  • CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行。
  • SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL。
  • RESTRICT：拒绝对父表的删除或更新操作。
  • NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同。
1
2
3
4
# 父表
CREATE TABLE provinces(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(20) NOT NULL);
# 子表
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE CASCADE);

6、修改数据表：

(1)数据表更名：
方法一：ALTER TABLE table_name RENAME [TO | AS] new_table_name
方法二：RENAME TABLE table_name TO new_table_name [,table_name1 TO new_table_name2,...]

1
2
ALTER TABLE test RENAME test1;
RENAME TABLE test TO test1;

(2)添加单列：
ALTER TABLE table_name ADD [COLUMN] column_name column_definition [FIRST | AFTER column_name1]

1
2
3
ALTER TABLE test ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
ALTER TABLE test ADD password VARCHAR(32) NOT NULL AFTER username;
ALTER TABLE test ADD truename VARCHAR(20) NOT NULL FIRST;

(3)添加多列：
ALTER TABLE table_name ADD [COLUMN] (column_name column_definition,...)

(4)删除列：
ALTER TABLE table_name DROP [COLUMN] column_name

1
2
ALTER TABLE test DROP truename;
ALTER TABLE test DROP password, DROP age;

(5)修改列名称：
ALTER TABLE table_name CHANGE [COLUMN] column_name new_column_name column_definition [FIRST | AFTER column_name1]

1
ALTER TABLE test CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

(6)修改列定义：
ALTER TABLE table_name MODIFY [COLUMN] column_name column_definition [FIRST | AFTER column_name1]

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

(7)添加主键约束：
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_column_name,...)

1
ALTER TABLE test ADD CONSTRAINT PK_test_id PRIMARY KEY (id);

(8)删除主键约束：
ALTER TABLE table_name DROP PRIMARY KEY

1
ALTER TABLE test DROP PRIMARY KEY;

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

1
ALTER TABLE test ADD UNIQUE (username);

(10)删除唯一约束：
ALTER TABLE table_name DROP {INDEX | KEY} index_name

1
ALTER TABLE test DROP INDEX username;

(11)添加默认约束：
ALTER TABLE table_name ALTER [COLUMN] column_name SET DEFAULT literal

1
ALTER TABLE test ALTER age SET DEFAULT 10;

(12)删除默认约束：
ALTER TABLE table_name ALTER [COLUMN] column_name DROP DEFAULT;

1
ALTER TABLE test ALTER age DROP DEFAULT;

(13)添加外键约束：
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_column_name,...) reference_name

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

(14)删除外键约束：
ALTER TABLE table_name DROP FOREIGN KEY fk_symbol

1
2
3
#首先查看user表的外键约束的名称
SHOW CREATE TABLE user;
ALTER TABLE user DROP FOREIGN KEY user_ibfk_1;

五、数据操作

1、插入记录:
方法一：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...), (...), ...
方法二：INSERT [INTO] table_name SET column_name={expr | DEFAULT}, ...

  • 说明：一次只能插入一行，此方法可以使用子查询(SubQuery)

方法三：INSERT [INTO] table_name [(column_name,...)] SELECT ...

  • 说明：此方法可以将查询结果插入到指定数据表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 对于自动增加的主键id可以赋值NULL
INSERT test VALUES(NULL,'Tom','123',25,1);
# 对于自动增加的主键id也可以赋值DEFAULT
INSERT test VALUES(DEFAULT,'Tom','123',25,1);
# 字段可以插入表达式
INSERT test VALUES(DEFAULT,'Tom','123',3*7+4,1);
# 字段有默认值的可以赋值DEAFULT
INSERT test VALUES(DEFAULT,'Tom','123',DEFAULT,1);
# 同时插入多行记录
INSERT test VALUES(DEFAULT,'Tom','123',3*7+4,1),(NULL,'Rose',md5('123'),DEFAULT,0);
# 方法二插入记录
INSERT test SET username='Ben',password='456';
# 方法三插入记录
INSERT test(username) SELECT username FROM user WHERE age>=20;

2、删除记录：
DELETE FROM table_name [WHERE where_condition]

1
DELETE FROM users WHERE id=6;

3、更新记录
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET column_name1={expr1 | DEFAULT}, [,column_name2={expr2 | DEFAULT}], ... [WHERE where_condition]

1
2
3
4
5
6
# 更新所有age字段增加5
UPDATE user SET age=age+5;
# 同时更新多个字段
UPDATE user SET age=age-id, sex=0;
# 根据条件更新字段
UPDATE user SET age=age+5 WHERE id%2=0;

4、查询记录：
SELECT select_expr [select_expr1,...] [FROM table_references]

  • [WHERE where_condition]
  • [GROUP BY {column_name | position} [ASC | DESC],...]
  • [HIVING where_condition]
  • [ORDER BY {column_name | expr | position} [ASC | DESC],...]
  • [LIMIT {[offset,] row_count | row_count OFFSET offset}]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 查询所有字段
SELECT * FROM test;
# 查询部分字段，显示结果集的字段顺序可以和表中字段顺序不同
SELECT username,id FROM user;
# 查询某个表中某个字段
SELECT user.id, user.username FROM USER;
# 使用AS个查询字段取别名
SELECT id AS uId,username AS uName FROM user;
# WHERE条件
SELECT id,username FROM user WHERE age>10;
# GROUP BY分组
SELECT sex FROM user GROUP BY sex;
SELECT sex FROM user GROUP BY 1;#按照位置分组
# HIVING分组条件
SELECT sex,age FROM user GROUP BY sex HIVING age>20;#条件字段必须在查询字段列表里
SELECT sex FROM user GROUP BY sex HIVING count(age)>20;#聚合函数，条件字段可以不在查询字段列表里
# ORDER BY排序
SELECT * FROM user ORDER BY id DESC;
SELECT * FROM user ORDER BY age,id DESC;#一个字段排序不能满足需求，可以再加字段排序
# LIMIT显示查询数量
SELECT * FROM user LIMIT 2;
SELECT * FROM user LIMIT 3,2;#从第4条开始返回2条

六、子查询和连接

1、子查询：
子查询(SubQuery)是指出现在其他SQL语句内的SELECT子句。
子查询必须在小括号()内。
ps:SET NAMES gbk;#设置客户端显示格式

(1)使用比较运算符的子查询：
=、>、<、>=、<=、<>、!>、<=>

1
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

使用ANY、SOME、ALL修饰比较运算符:

ANY SOME ALL
>、>= 最小值 最小值 最大值
<、<= 最大值 最大值 最小值
= 任意值 任意值
<>、!= 任意值 
1
2
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ALL (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超极本');
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price < ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超极本');

(2)使用 [NOT] IN 的子查询：

  • IN 与 ANY 等效
  • NOT IN 与 !=ALL、<>ALL等效
1
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price NOT IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超极本');

(3)使用 [NOT] EXISTS 的子查询：
如果子查询返回任何行，EXISTS将返回TRUE,否则将返回FALSE。

2、连接：
table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} table_reference ON conditional_expr

  • 内连接：INNER JOIN 和 CROSS JOIN 和 JOIN 是等价的。
  • 外连接：LEFT JOIN 是左外连接; RIGHT JOIN 是右外连接。

(1)内连接 INNER JOIN：
显示左表及右表符合连接条件的记录。(交集)

1
SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

(2)左外连接 LEFT JOIN:
显示左表的全部记录及右表符合连接条件的记录。(右表以左表为准)

1
SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

(3)右外连接 RIGHT JOIN:
显示右表的全部记录及左表符合连接条件的记录。(左表以右表为准)

1
SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

3、多表连接：
连续使用INNER JOIN ... ON ...进行多表连接

1
2
3
# 当不同表中字段名称相同时，需使用AS给表取别名
# \G以表格美化显示
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;

4、自身连接：
同一个数据表对其自身进行连接。

1
2
3
4
5
6
#无限分类的数据表设计
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); 
#查找所有分类及其父类
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;
#查找所有分类及其子类
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;

5、多表更新：
UPDATE table_references SET column_name1={expr1 | DEFAULT} [,column_name2={expr2 | DEFAULT}],... [WHERE where_condition]

1
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;

注意：多表更新需要一步到位：
ps：创建数据表的同时将查询结果写入到数据表：
CREATE TABLE [IF NOT EXISTS] table_name [(create_definition,...)] SELECT select_statement

1
2
3
4
5
6
# 1.查询结果创建表
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;
# 2.连表更新
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;
# 3.修改字段名称和字段类型
ALTER TABLE tdb_goods CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL, CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

6、多表删除：
DELETE table_name[.*] [,table_name1[.*]]... 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 HIVING COUNT(goods_name) >= 2) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;

七、mysql函数

1、字符函数：

名称 描述
CONCAT() 字符连接
CONCAT_WS() 使用指定的分隔符进行字符连接
FORMAT() 数字格式化
LOWER() 转换成小写字母
UPPER() 转换成大写字母
LEFT() 获取左侧字符
RIGHT() 获取右侧字符
LENGTH() 获取字符串长度
RIGHT() 获取右侧字符
LTRIM() 删除前导空格
RTRIM() 删除后续空格
TRIM() 删除前导和后续
SUBSTRING() 字符串截取
[NOT]LIKE() 模式匹配
REPLACE() 字符串替换 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#CONCAT()连接用户名'Tom'和密码'123'，输出'Tom123'
SELECT CONCAT(username,password) AS usinfo FROM user;

#CONCAT_WS()指定分隔符'_'连接用户名'Tom'和密码'123'，输出'Tom_123'
SELECT CONCAT_WS('_',username,password) AS usinfo FROM user;

#FORMAT()格式化数字保留一位小数，输出'1234.6'
SELECT FORMAT(1234.56,1);

#LOWER()大写变小写，输出'mysql'
SELECT LOWER('Mysql');

#LEFT()取左侧2位字符，输出'My'
SELECT LEFT('Mysql',2);

#LENGTH()获取字符串长度包含空格，输出'7'
SELECT LENGTH('Mysql  ');

#LTRIM()删除前导空格，输出'Mysql  '
SELECT LTRIM('  Mysql  ');

#TRIM()删除前导和后续空格，输出'Mysql'
SELECT TRIM('  Mysql  ');

#TRIM()可以使用LEADING删除前导字符，输出'Mysql!!'
SELECT TRIM(LEADING '!' FROM '!!Mysql!!');

#TRIM()可以使用TRAILING删除后续字符，输出'!!Mysql'
SELECT TRIM(TRAILING '!' FROM '!!Mysql!!');

#TRIM()可以使用BOTH删除前导和后续字符，输出'Mysql'
SELECT TRIM(BOTH '!' FROM '!!Mysql!!');

#REPLACE()替换指定字符，输出'Mysql'
SELECT REPLACE('!!My!!sql!!', '!', '');

#SUBSTRING()截取字符串，注意索引第一位是1，不是0。输出'My'
SELECT SUBSTRING('Mysql', 1, 2);

#SUBSTRING()截取字符串可以从倒数开始，但截取长度不能为负数。输出'l'
SELECT SUBSTRING('Mysql',-1);

#LIKE匹配，%代表通配符，/代表任意一个字符。输出'Tom'
SELECT * FROM user WHERE first_name LIKE '%o%';

#如果匹配的字符是%，则需要在%任意放置一个字符，用ESCAPE表述忽略字符后面的%。输出'To%m'
SELECT * FROM user WHERE first_name LIKE '%1%%' ESCAPE '1';

2、数值运算符与函数：

名称 描述
CEIL() 进一取整，向上取整
DIV 整数除法
FLOOR() 舍一取整，向下取整
MOD 取余数（取模）
POWER() 幂运算
ROUND() 四舍五入
TRUNCATE() 数字截取 
1
2
3
4
5
6
7
8
SELECT CEIL(3.01); # 向上取整,输出'4'
SELECT FLOOR(3.99); # 向下取整,输出'3'
SELECT 3 DIV 4; # 整数除法,输出'0'
SELECT 5 MOD 3; # 取余，输出'2'
SELECT POWER(3,3); # 幂运算,输出'27'
SELECT ROUND(3.652,2); # 四舍五入，输出'3.65'
SELECT truncate(123.45,0); #数字截断，到小数点后0位，输出'123'
SELECT truncate(123.45,-1); # 数字截取，把那一位整个去掉.输出'120'

3、比较运算符与函数：

名称 描述
[NOT] BETWEEN…AND.. 【不】在范围之内
[NOT] IN() 【不】在列出值范围内
IS [NOT] NULL 【不】为空 
1
2
3
4
5
SELECT 15 BETWEEN 10 AND 20; # 返回1，TRUE
SELECT 15 NOT BETWEEN 10 AND 20; # 返回0，FALSE
SELECT 15 IN (10,15,20,25); # 返回1，TRUE
SELECT 5 IN (10,15,20,25); # 返回0，FALSE
SELECT * FROM user WHERE username IS NULL; # 返回用户名为空的用户

4、日期时间函数：

名称 描述
NOW() 当前日期和时间
CURDATE() 当前日期
CURTIME() 当前时间
DATE_ADD() 日期变化
DATEDIFF() 日期差值
DATE_FORMAT() 日期格式化 
1
2
3
4
5
6
7
8
SELECT NOW();
SELECT CURDATE();
SELECT CURTIME;
SELECT DATE_ADD('2017-12-27', INTERVAL 365 DAY); # 日期增加365天
SELECT DATE_ADD('2017-12-27', INTERVAL 1 YEAR); # 日期增加1年
SELECT DATE_ADD('2017-12-27', INTERVAL -3 WEEK); # 日期减少3周
SELECT DATEDIFF('2016-12-27','2017-12-27'); # 查看两个日期之间相差多少天
SELECT DATE_FORMAT('2017-2-3','%m/%d/%Y'); # 格式化为'02/03/2017'

5、信息函数：

名称 描述
CONNECTION_ID() 连接ID
DATABASE() 当前数据库
LAST_INSERT_ID() 最后插入记录的id号
USER() 当前用户
VERSION() 版本信息 
1
2
3
4
5
6
SELECT CONNECTION_ID(); # 查看当前连接ID
SELECT DATABASES(); # 查看当前数据库
DESC test_table; # 查看数据表，看看是否有id字段
SELECT LAST_INSERT_ID(); # 可以查看最后写入的id号，同时写入多条记录时只显示第一条记录的id号
SELECT USER(); # 当前登录的用户
SELECT VERSION(); # 当前数据库版本

6、聚合函数：

名称 描述
AVG() 平均值
COUNT() 计数
MAX() 最大值
MIN() 最小值
SUM() 求和 
1
2
3
SELECT AVG(age) FROM user;
SELECT COUNT(id) AS counts FROM user;
SELECT MAX(age) FROM user;

7、加密函数：

名称 描述
MD5() 信息摘要算法
PASSWORD() 密码算法 
1
2
SELECT MD5('admin'); # 信息摘要算法，如果为web页面做准备，推荐MD5
SET PASSWORD = PASSWORD('654321'); # 主要用来修改用户密码

8、自定义函数：
CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} rourine_body

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 不带参数的函数
CREATE FUNCTION f1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %h点：%i分：%s秒');
SELECT f1();

# 带参数的函数
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED) RETURNS FLOAT(10,2) UNSIGNED RETURN (num1 + num2)/2;
SELECT f2(10,20);

# 有复合结构函数体的函数
DELIMITER // # 重定义结束符，所有命令都以//结束。因为函数体中有的语句末尾带有;会导致函数结束。
#符合函数结构体需要使用begin...end语句。
CREATE FUNCTION adduser(username VARCHAR(20)) RETURNS INT UNSIGNED BEGIN INSERT user(username) values(username); RETURN LAST_INSERT_ID(); END //

#删除函数
DROP function adduser;

八、存储过程

mysql执行过程：SQL命令——>Mysql引擎——>(分析)语法正确——>可识别命令——>(执行)执行结果——>(返回)客户端

1、存储过程：
存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。
存储过程是预编译的，只在第一次执行时执行mysql执行过程，后续的执行会大大提高效率。

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

2、创建存储过程：
CREATE [DEFINER = {user | CURRENT_USER}] PROCEDURE sp_name ([proc_parameter[,…]]) [characteristic…] routine_body

(1)参数：

  • proc_parameter: [IN| OUT |INOUT] parameter_name type
  • IN，表示该参数的值必须在调用存储过程时指定
  • OUT，表示该参数的值可以被存储过程改变，并且可以返回
  • INOUT，表示该参数的值调用时指定，并可以被改变和返回

(2)特性：

  • COMMENT:注释
  • CONTAINS SQL:包含SQL语句，但不包含读或写数据的语句
  • NO SQL：不包含SQL语句
  • READS SQL DATA：包含读数据的语句
  • MODIFIES SQL DATA：包含写数据的语句
  • SQL SECURITY{DEFINER|INVOKER}指明谁有权限来执行

(3)过程体:

  • 由合法的SQL语句构成（记录的增删改查和多表连接，不能创建数据库或数据表）
  • 复合结构：BEGIN…END
  • 可包含声明，循环，控制结构

(4)调用存储过程：

  • CALL sp_name([parameter[,…]])
  • CALL sp_name[()]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 创建不带参数的存储过程
CREATE PROCEDURE sp1() SELECT VERSION();
CALL sp1;
CALL SP1();

# 创建带有IN类型参数的存储过程
DELIMITER // # 修改当前定界符为//
CREATE PROCEDURE removeUserById(IN id INT UNSIGNED) BEGIN DELETE FROM users WHERE id = id; END //
DELIMITER ; # 修改当前定界符回;
CALL removeUserById(3);
SELECT * FROM users; # 返回空集是因为where id = id,所以参数不能和数据表的字段相同。
DROP PROCEDURE removeUserById; # 存储过程不能修改过程体，只能先删除
# 重新创建一遍
DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED) BEGIN DELETE FROM users WHERE id = p_id; END //
DELIMITER ;
CALL removeUserById(22);
SELECT * FROM users WHERE id=22;

# 创建带有IN类型和OUT类型参数的存储过程
DELIMITER //
CREATE PROCEDURE removeAndReturnNums(IN p_id INT UNSIGNED, OUT countNum  INT UNSIGNED) BEGIN DELETE FROM users WHERE id =p_id; SELETE COUNT(id) FROM users INTO countNum; END //
DELIMITER ;
CALL removeAndReturnNums(27,@nums); # @的是用户变量,用于接受返回信息，只对当前mysql客户端生效
SELECT @nums;

# 创建带有多个OUT类型参数的存储过程
DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteNums INT UNSIGNED, OUT userCount SMALLINT UNSIGNED) BEGIN DELETE FROM users WHERE age = p_age; SELECT ROW_COUNT() INTO deleteNums; SELECT COUNT(id) FROM users INTO userCount; END //
DELIMITER ;
CALL  removeUserByAgeAndReturnInfos(23, @a, @b);
SELECT @a,@b;

九、存储引擎

1、存储引擎：
Mysql可以将数据以不同的技术存储在文件（内存）中，这种技术称为存储引擎。

  • MyISAM：
  • INNODB：
  • Memory：
  • CSV：
  • Archive：

2、相关概念：

(1)并发控制：多个连接对记录进行修改时保证数据的一致性和完整性。

(2)锁：

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

(3)锁颗粒：

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

(4)事务：事务用于保证事务的完整性。

(5)外键：保证数据一致性的策略。

(6)索引：是对数据表中的一列或多列的值进行排序的一种结构

3、各种存储引擎的特点：

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

4、修改存储引擎的方法：
方法一：通过修改Mysql配置文件
default-storage-engine = engine
方法二：通过创建数据表命令
CREATE TABLE table_name(... ...) ENGINE = engine_name
方法三：通过修改数据表命令
ALTER TABLE table_name ENGINE [=] engine_name

1
2
3
4
5
6
7
#通过创建数据表命令修改存储引擎
CREATE TABLE test(name VARCHAR(20)) ENGINE = MyISAM;
SHOW CREATE TABLE test;

#通过修改数据表命令修改存储引擎
ALTER TABLE test ENGINE = InnoDB;
SHOW CREATE TABLE test;

十、事务

Mysql事务主要用于处理操作量大，复杂度高的数据。

  • 在Mysql中只有使用了Innodb数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
  • 事务用来管理insert,update,delete语句

1、事务是必须满足4个条件：

  • 原子性(Atomicity，或称不可分割性)：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性(Consistency)：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性(Isolation，又称独立性)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性(Durability)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

2、事务控制语句：

  • BEGIN或START TRANSACTION：显式地开启一个事务。
  • COMMIT或COMMIT WORK：COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的。
  • ROLLBACK或ROLLBACK WORK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
  • SAVEPOINT identifier：SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT。
  • RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常。
  • ROLLBACK TO identifier；把事务回滚到标记点。
  • SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

3、事务处理方法：
方法一：用BEGIN, ROLLBACK, COMMIT来实现

  • BEGIN：开始一个事务
  • ROLLBACK：事务回滚
  • COMMIT：事务确认

方法二：直接用 SET 来改变 MySQL 的自动提交模式:

  • SET AUTOCOMMIT=0：禁止自动提交
  • SET AUTOCOMMIT=1：开启自动提交
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE test(id INT(5)) ENGINE=innodb;

BEGIN; # 开始事务
INSERT INTO test VALUE(5);
COMMIT; # 提交事务
SELECT * FROM test;

BEGIN; # 开始事务
INSERT INTO test VALUE(6);
ROLLBACK; # 回滚
SELECT * FROM test;

十一、备份还原

1、mysqldump参数

(1)参数详解

  • -d 结构(–no-data:不导出任何数据，只导出数据库表结构)
  • -t 数据(–no-create-info:只导出数据，而不添加CREATE TABLE 语句)
  • -n (–no-create-db:只导出数据，而不添加CREATE DATABASE 语句）
  • -B (–databases:导出数据库列表，单个库时可省略）
  • -R (–routines:导出存储过程以及自定义函数)
  • -E (–events:导出事件)
  • --triggers (默认导出触发器，使用–skip-triggers屏蔽导出)
  • --tables 表列表（单个表时可省略）

(2)参数组合

  • 同时导出结构以及数据时可同时省略-d和-t
  • 同时不导出结构和数据可使用-ntd
  • 只导出存储过程和函数可使用-R -ntd
  • 导出所有(结构&数据&存储过程&函数&事件&触发器)使用-R -E
  • 只导出结构&函数&事件&触发器使用-R -E -d

2、导出数据库(备份)

(1)导出一个数据库的结构：
mysqldump -d dbname -uroot -p > dbname.sql
(2)导出多个数据库的结构：
mysqldump -d -B dbname1 dbname2 -uroot -p > dbname.sql
(3)导出一个数据库中数据（不包含结构）：
mysqldump -t dbname -uroot -p > dbname.sql
(4)导出多个数据库中数据（不包含结构）：
mysqldump -t -B dbname1 dbname2 -uroot -p > dbname.sql
(5)导出一个数据库的结构以及数据：
mysqldump dbname -uroot -p > dbname.sql
(6)导出多个数据库的结构以及数据：
mysqldump -B dbname1 dbname2 -uroot -p > dbname.sql
(7)导出一个数据库中一个表的结构：
mysqldump -d dbname1 tablename -uroot -p > tablename.sql
(8)导出一个数据库中多个表的结构：
mysqldump -d -B dbname1 --tables tablename1 tablename2 -uroot -p > tablename.sql
(9)导出一个数据库中一个表的数据（不包含结构）：
mysqldump -t dbname1 tablename -uroot -p > tablename.sql
(10)导出一个数据库中多个表的数据（不包含结构）：
mysqldump -t -B dbname1 --tables tablename1 tablename2 -uroot -p > tablename.sql
(11)导出一个数据库中一个表的结构以及数据：
mysqldump dbname1 tablename -uroot -p > tablename.sql
(12)导出一个数据库中多个表的结构以及数据：
mysqldump -B dbname1 --tables tablename1 tablename2 -uroot -p > tablename.sql

3、导出存储过程和函数操作

(1)只导出存储过程和函数(不导出结构和数据，要同时导出结构的话，需要同时使用-d)：
mysqldump -R -ndt dbname -u root -p > dbname.sql
(2)只导出事件：
mysqldump -E -ndt dbname -u root -p > dbname.sql
(3)不导出触发器（触发器是默认导出的–triggers，使用–skip-triggers屏蔽导出触发器）：
mysqldump --skip-triggers dbname1 -u root -p > dbname.sql

4、导入数据库(还原)

方法一：
mysql -uroot -p123456 -h127.0.0.1 -P3306 dbname < dbname.sql
方法二：

1
2
3
4
5
6
7
8
# 1.登录数据库
mysql -uroot -p -hlocalhost -P3306
# 2.选择数据库
use dbname;
# 3.设置数据库编码(根据需要)
set names utf8;
# 4、导入数据（注意sql文件的路径）
source /home/tester/dbname.sql;

持续更新…

最后更新： 2018年11月04日 16:22

原始链接： http://pythonfood.github.io/2017/12/29/mysql/

赏
× 多少都行~
打赏二维码