Mysql 学习笔记查询手册

发表于 更新于 分类于 ， 阅读次数： Waline：

管理

结构化查询语句分类

名称 解释 命令
DDL（数据定义语言） 用于定义和管理数据对象，如数据库，数据表等 create/drop/alter
DML（数据操作语言） 用于操作数据库对象中所包含的数据 insert/update/delete
DQL（数据查询语言） 用于查询数据库数据 select
DCL（数据控制语言） 用于管理数据库权限及数据更改 grant/commit/rollback

注释

1
2
3
4
5
# 单行注释
SELECT * FROM mytable; -- 单行注释
/* 多行注释
   多行注释
   多行注释 */

保留字不能用于字段名，有MYSQL保留字作为字段的，必须加上 反引号 来区分

用户权限

密码策略

查看密码策略

1
mysql> show variables like '%validate%';

查看当前用户身份验证插件

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

修改策略

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--密码验证策略低要求(0或LOW代表低级)
set global validate_password.policy=MEDIUM;

--密码至少要包含的小写字母个数和大写字母个数
set global validate_password.mixed_case_count=1;

--密码至少要包含的数字个数。
set global validate_password.number_count=1; 

--密码至少要包含的特殊字符数
set global validate_password.special_char_count=1; 

-- 密码长度
set global validate_password.length=8;

创建账户

创建本地用户

1
create user '账户名'@'localhost' identified by '密码';

修改账户名

1
rename user '旧账户名'@'localhost' to '新账户名'@'localhost';

查看所有账户

1
2
3
4
5
6
7
8
9
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+------------------------------------+
| query                              |
+------------------------------------+
| User: 'mysql.session'@'localhost'; |
| User: 'mysql.sys'@'localhost';     |
| User: 'ndemo'@'localhost';         |
| User: 'root'@'localhost';          |
+------------------------------------+

删除账户

1
drop user '账户名'@'localhost';

分配权限

1
grant 权限列表 on 数据库名.表名 to '用户名'@'连接地址' [identified by password];

权限列表

  • select、insert、update、create、drop
  • all privileges 表示所有权限
  • *.* 表示赋予用户操作所有数据库和表的权限
  • 库名.表名 表示某库下面的某表

创建demo用户并分配操作hello数据库所有权限

1
grant all privileges on `hello`.* to 'demo'@'localhost' identified  by '123456';

赋予demo用户对hello数据库的所有权限

1
grant all privileges on `hello`.* to 'demo'@'localhost';

赋予select权限

1
grant select on `hello`.* to 'demo'@'localhost';

刷新权限

1
flush privileges;

查看权限

1
2
3
4
5
6
7
8
# show grants for '账户名'@'localhost';
mysql> show grants for `demo`@localhost;
+---------------------------------------------------------+
| Grants for demo@localhost                               |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'demo'@'localhost'                |
| GRANT ALL PRIVILEGES ON `hello`.* TO 'demo'@'localhost' |
+---------------------------------------------------------+

撤销权限

1
2
revoke 权限列表 on 数据库.表名 from '账户名'@'localhost';
revoke all privileges, grant option from '账户名'@'localhost'; -- 撤销所有权限

设置密码

不同版本语法可能不同

1
2
3
4
5
6
7
8
9
10
11
set password for '账户名'@'localhost' = password('密码');
update mysql.user SET password=password("qwe123qwe") where user='bfuser' and Host='localhost';
flush privileges;

-- mysq 5.7+
update mysql.user set authentication_string=password('qwe123qwe') where user='bfuser' and Host='localhost';
flush privileges;

-- mysql 8
alter user 'bfuser'@'localhost' identified with caching_sha2_password by 'qwe123qwe';
flush privileges;
权限列表 表头
ALL [PRIVILEGES] 设置除GRANT OPTION之外的所有简单权限
ALTER 允许使用ALTER TABLE
ALTER ROUTINE 更改或取消已存储的子程序
CREATE 允许使用CREATE TABLE
CREATE ROUTINE 创建已存储的子程序
CREATE TEMPORARY TABLES 允许使用CREATE TEMPORARY TABLE
CREATE USER 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW 允许使用CREATE VIEW
DELETE 允许使用DELETE
DROP 允许使用DROP TABLE
EXECUTE 允许用户运行已存储的子程序
FILE 允许使用SELECT…INTO OUTFILE和LOAD DATA INFILE
INDEX 允许使用CREATE INDEX和DROP INDEX
INSERT 允许使用INSERT
LOCK TABLES 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS 允许使用SHOW FULL PROCESSLIST
REFERENCES 未被实施
RELOAD 允许使用FLUSH
REPLICATION CLIENT 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT 允许使用SELECT
SHOW DATABASES 显示所有数据库
SHOW VIEW 允许使用SHOW CREATE VIEW
SHUTDOWN 允许使用mysqladmin shutdown
SUPER 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句
UPDATE 允许使用UPDATE
USAGE “无权限”的同义词
GRANT OPTION 允许授予权限

操作数据库

连接数据库

1
2
mysql -u root -p
:输入密码

在登录成功后会出现 mysql> 命令提示窗口

创建数据库

1
2
create database [if not exists] 数据库名;
mysqladmin -u用户名 -p密码 create 数据库名;

删除数据库

1
2
drop database [if exists] 数据库名;
mysqladmin -u用户名 -p密码 drop 数据库名;

执行以上删除数据库命令后，会出现一个提示框，来确认是否真的删除数据库，输入 y 回车即可

选择数据库

1
use 数据库名;

操作数据表

数据类型

规定数据库中该列存放的数据类型

类型 大小 范围 用途
tinyint 1 byte 有符号：-128, 127 无符号：0, 255 非常小的数据
smallint 2 bytes 有符号：-32768, 32767 无符号：0, 65535 较小的数据
mediumint 3 bytes 有符号：-8388608, 8388607 无符号：0, 16777215 中等大小的数据
int 4 bytes 有符号：-2^23 ~ 2^23-1 无符号：0 ~ 2^23-1 标准数据
bigint 8 bytes 有符号：-2^63 ~ 2^63-1 无符号：0 ~ 2^64-1 极大整数值
float 4 bytes 单精度浮点数值
double 8 bytes 双精度浮点数值

字符串类型

类型 大小 用途
char 0-255 bytes 定长字符串，检索快但浪费空间
varchar 0-65535 bytes 变长字符串
tinytext 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
blob 0-65 535 bytes 二进制形式的长文本数据
longblob 0-4 294 967 295 bytes 二进制形式的极大文本数据
mediumblob 0-16 777 215 bytes 二进制形式的中等长度文本数据
text 0-65 535 bytes 长文本数据
longtext 0-4 294 967 295 bytes 极大文本数据
mediumtext 0-16 777 215 bytes 中等长度文本数据

日期和时间

类型 大小（bytes） 范围 说明
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/2038-1-19 03:14:07 YYYYMMDD HHMMSS 时间戳

操作表

创建表

1
2
3
4
create table `表名` (
  列名 数据类型,
  列名2 数据类型,
);

删除表

1
drop table 表名;

重命名表名

1
alter table 旧表名 rename to 新表名;

查看表结构

1
2
3
4
5
6
7
8
9
10
# desc 表名;
mysql> desc img;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| type   | varchar(30) | NO   |     | NULL    |                |
| imgdat | blob        | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

克隆表结构和数据

1
create table 新表名 select * from 旧表;

克隆表结构

1
create table 新表名 like 旧表;

操作列

添加列

1
alter table `表名` add 字段 数据类型;

更改列

1
alter table `表名` modify column 字段 数据类型;

删除列

1
alter table `表名` drop column 字段;

克隆列

1
2
# UPDATE 表名 SET 目标类名=源列名;
UPDATE `content` SET path_nei = path;

操作数据

插入数据

1
insert into 表名[(`字段1`,`字段2`,...)] values ('值1','值2',...);
  • 字段或值之间用英文逗号隔开
  • ‘字段1,字段2,…’ 该部分可省略，但添加的值务必与表结构，数据列，顺序相对应且数量一致
  • 可同时插入多条数据，values 后用英文逗号隔开

查询数据

1
2
3
4
5
6
7
8
9
select 字段[,字段2,...] from 表名 [as 表别名]
  [left | right | inner join 表名2 ] -- 联合查询
  [where 条件 ...]                   -- 指定结果需满足的条件
  [group by 字段 ...]                -- 指定结果按哪几个字段来分组
  [having]                           -- 过滤分组的记录必须满足的次要条件
  [where ...]                        -- 指定结果需满足的条件
  [order by 条件 ...]                -- 指定查询记录按一个或多个条件排序
  [where ...]                        -- 指定结果需满足的条件
  [LIMIT 开始 结束 ...]              -- 指定查询的记录从哪条至哪条

更新数据

1
update 表名 set 字段 = 值 [,字段2 = 值2] [where 条件];
  • 值 为修改后的数据，可以为变量，具体指，表达式或者嵌套的SELECT结果
  • where 为筛选条件，如不指定则修改该表的所有列数据

删除数据

1
delete from 表名 [where 条件];

提示：删、改操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏

清空数据

1
truncate 表名;

会完全清空表数据，但表结构，索引，约束等不变

TRUNCATE 跟 DELETE的区别

  • 相同
    都能删除数据，不删除表结构，但TRUNCATE速度更快
  • 不同
    使用 TRUNCATE TABLE 会重新设置AUTO_INCREMENT计数器
    使用 TRUNCATE TABLE 不会对事务有影响

导出数据

1
select * from hello into outfile '\root\hello.sql';

as 别名

作用

  • 可给数据列取一个新别名
  • 可给表取一个新别名
  • 可把经计算或总结的结果用另一个新名称来代替
1
2
3
4
5
6
7
8
9
# 这里是为列取别名(当然as关键词可以省略)
SELECT studentno AS 学号,studentname AS 姓名 FROM student;

# 使用as也可以为表取别名
SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;

# 使用as,为查询结果取一个新名字
# CONCAT() 函数拼接字符串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

Where

作用：用于检索数据表中 符合条件 的记录，搜索条件可由一个或多个逻辑表达式组成，结果一般为真或假

运算符 含义 结果
= 等于。检测两个值是否相等 如果相等返回true
<>, != 不等于。检测两个值是否相等 如果不相等返回true
> 大于。检测左边的值是否大于右边的值 如果左边的值大于右边的值返回true
< 小于。检测左边的值是否小于右边的值 如果左边的值小于右边的值返回true
>= 大于等于。检测左边的值是否大于或等于右边的值 如果左边的值大于或等于右边的值返回true
<= 小于等于。检测左边的值是否小于或等于右边的值 如果左边的值小于或等于右边的值返回true
逻辑操作符 语法 描述
AND 或 && a AND b 或 a && b 逻辑与，同为真，结果才为真
OR 或 || a OR b 或 a || b 逻辑或，只要一个为真，则结果为真
NOT 或 ! NOT a 或 !a 逻辑非，若操作数为假，则结果为真 
1
2
3
4
5
6
7
8
9
10
select * from users where id = 1 && name = 'hello';

# 多条件
select * from users where id in(1,3,15,7,75);

# 区分小写
select * from users where binary username = "AbC";

# 模糊查询
select * from users where id between 10 and 20;

Like

  • 你可以在 WHERE 子句中指定任何条件
  • 你可以在 WHERE 子句中使用 LIKE 子句
  • 你可以使用 LIKE子句 代替等号 =
  • % 匹配任意个字符；
  • _ 匹配任意1字符；
  • [ ]可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符
  • LIKE 通常与 % 一同使用，类似于一个元字符的搜索
  • 你可以使用 AND 或者 OR 指定一个或多个条件
  • 你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件
1
2
select * from 表名 where name like '[^AB]%'  -- 不以 A 和 B 开头的任意文本
select id,author from xx where title like "%com";

Union

用于连接两个以上的 select 语句的结果，组合到一个结果集合里，多个select语句会自动删除重复的数据

1
select 列1,列2,列3 from 表名 [where] union [all] select 列1,列2,列3 from 表名2 [where]

排序

  • asc 升序
  • desc 降序
1
2
3
4
5
6
7
8
9
10
11
12
mysql> select name,date from hello order by id desc;
+--------+---------------------+
| name   | date                |
+--------+---------------------+
| 小明   | 2021-04-04 15:13:21 |
| 小明   | 2021-04-11 15:23:47 |
| 小王   | 2021-04-07 15:26:47 |
| 小丽   | 2021-04-19 15:26:07 |
| 小王   | 2021-04-20 15:25:47 |
| 小明   | 2021-04-22 15:25:33 |
+--------+---------------------+
6 rows in set (0.00 sec)

分组

group by 语句根据一个或多个列对结果进行分组，再分组的列上我们可以使用 count, sum, avg等函数

1
2
3
4
5
6
7
8
9
10
11
12
13
select 列1,列2 from 表名 [where] group by 列1;
mysql> select * from hello;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  1 | 小明   | 2021-04-22 15:25:33 |      1 |
|  2 | 小王   | 2021-04-20 15:25:47 |      3 |
|  3 | 小丽   | 2021-04-19 15:26:07 |      2 |
|  4 | 小王   | 2021-04-07 15:26:47 |      4 |
|  5 | 小明   | 2021-04-11 15:23:47 |      4 |
|  6 | 小明   | 2021-04-04 15:13:21 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

按 name 进行分组，并统计每个人有多少条记录

1
2
3
4
5
6
7
8
9
mysql> select name,count(*) from hello group by name;
+--------+----------+
| name   | count(*) |
+--------+----------+
| 小明   |        3 |
| 小王   |        2 |
| 小丽   |        1 |
+--------+----------+
3 rows in set (0.00 sec)

{dotted startColor=”#ff6c6c” endColor=”#1989fa”/}

having

对分完组之后的数据进一步过滤，不能单独使用，不能代替 where，必须和 group by 联合使用

SQL 语句的书写顺序

1
select --> from --> where --> group by --> having --> order by -->

SQL 语句的执行顺序

1
from --> where --> group by --> having --> select --> order by -->
  1. 从某张表中查询数据
  2. 先经过 where 条件筛选出有价值的数据
  3. 对这些有价值的数据进行分组
  4. 分组之后可以使用 having 继续筛选
  5. 使用 select 查询出来
  6. 最后排序输出

但是 WHERE 和 HAVING 关键字也存在以下几点差异

  • 一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组
  • WHERE 查询条件中不可以使用聚合函数，如 avg() 等，而 HAVING 查询条件中可以使用聚合函数
  • WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤
  • WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤
  • WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名

如

1
...

JOIN

在两个或多个表中查询数据

JOIN 大体分三类

  • inner join (内连接或等值连接)：获取两个表中字段匹配关系的记录
  • left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录
  • right joi（右连接）：与 left join 相反

子查询

在查询语句中的 WHERE 条件子句中，又嵌套了另一个查询语句，求解的方式是由里及外。子查询返回的结果一般都是集合，故而建议使用 IN 关键字

正则表达式

Mysql 使用 regexp 操作符来进行正则表达式匹配

模式 描述 注释
^ 匹配开始位置 如果设置了 RegExp 对象的 Multiline 属性^也匹配\n或\r之后的位置
$ 匹配结束位置 如果设置了 RegExp 对象的 Multiline 属性$也匹配\n或\r 之前的位置
. 匹配除 \n 之外的任何单个字符 要匹配包括\n在内的任何字符，请使用像 [.\n] 的模式
* 匹配零次或多次 例如 zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}
+ 匹配一次或多次 例如 zo+ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}
{n} n 是一个非负整数。匹配确定的 n 次 例如 o{2} 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个’o’
{n,m} m和 n均为非负整数，最少匹配 n 次且最多匹配 m 次
[...] 字符集合。匹配所包含的任意一个字符 例如 [abc] 可以匹配 “plain” 中的 ‘a’
[^...] 负值字符集合。匹配未包含的任意字符 例如 [^abc] 可以匹配 “plain” 中的’p’
p1 | p2 | p3 匹配 p1或 p2 或p3 例如 z | food 能匹配 “z” 或 “food”。(z|f)ood 则匹配”zood”或”food”

正则详细学习，可参考 点击前往

1
2
3
4
5
6
7
8
# 查找name字段中以 a 开头的所有数据
select name from hello where cid regexp "^a";

# 查找name字段中以 b 结尾的所有数据
select name from hello where cid regexp "b$";

# 查找name字段中包含 abc 的所有数据
select name from hello where cid regexp "abc";

0x04 事务

什么是事务

  • 事务就是将一组SQL语句放在同一批次内去执行
  • 如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行
  • MySQL事务处理只支持 InnoDB和 BDB数据表类型

事务的ACID原则

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

默认设置下，事务都是自动提交的

基本语法

关键词 注释
begin 开启一个事务
commit 提交事务，并使已对数据进行的所有修改改为永久性的
rollback 事务回滚，并撤销正在进行所有未提交的修改
savepoint 保存点名称 创建一个保存点，一个事务中可以创建多个保存点
release savepoint 保存点名称 删除一个保存点，当没有指定保存点时，会抛出异常
rollback 保存点名称 把事务回滚到标记点
set transaction 设置事务的隔离级别

例子

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
/*
课堂测试题目

A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000

创建数据库shop和创建表account并插入2条数据
*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)

-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# rollback;
SET autocommit = 1; -- 恢复自动提交

0x05 索引

索引的作用

  • 提高查询速度
  • 确保数据的唯一性
  • 可以加速表和表之间的连接 ,实现表与表之间的参照完整性
  • 使用分组和排序子句进行数据检索时 ,可以显著减少分组和排序的时间
  • 全文检索字段进行搜索优化
  • 索引可以大大提高 Mysql的检索速度

分类

  • 主键索引 (Primary Key)
  • 唯一索引 (Unique)
  • 常规索引 (Index)
  • 全文索引 (FullText)

主键索引

索引值必须是唯一的，且不能为 NULL

添加主键

1
alter table 表名 add primary key (列名);

唯一索引

同一个表中索引列值必须时唯一的，但 允许为空值 。如果时组合索引，则列值的组合必须唯一

创建索引

1
2
3
alter table 表名 add unique 索引名称(索引字段);
  
create unique index 索引名称 on 表名(索引字段);

注：唯一索引使用 UNIQUE ，索引字段可以有多个用逗号分隔，多个时即为联合唯一索引

1
2
3
4
5
6
mysql> desc users;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  | UNI | NULL    |       |
+-------+------+------+-----+---------+-------+

删除索引

1
2
alter table 表名 drop index 索引名称;
drop index 索引名称 ON 表名;

查询索引

1
2
SHOW INDEX FROM 表名;
SHOW KEYS FROM 表名;

常规索引

这是最基本的索引，它没有任何限制，索引列值可出现多次，但不宜添加太多常规索引，影响数据的插入、删除和修改操作

创建索引

1
2
3
alter table 表名 add index 索引名称(索引字段);

create index 索引名称 on 表名(索引字段);

注：添加常规索引使用 index ，索引字段可以为多个用逗号分隔即为联合索引

1
2
3
4
5
6
mysql> desc users;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  | UNI | NULL    |       |
+-------+------+------+-----+---------+-------+

删除索引

1
2
3
alter table 表名 drop index 索引名称;

drop index 索引名称 ON 表名;

全文索引

快速定位特定数据

注意

  • 只能用于 MyISAM类型的数据表
  • 只能用于CHAR ,VARCHAR ,TEXT数据列类型
  • 适合大型数据集

创建全文索引

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
/*
#方法一：创建表时
  　　CREATE TABLE 表名 (
               字段名1 数据类型 [完整性约束条件…],
               字段名2 数据类型 [完整性约束条件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (字段名[(长度)] [ASC |DESC])
               );

#方法二：CREATE在已存在的表上创建索引
       CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                    ON 表名 (字段名[(长度)] [ASC |DESC]) ;

#方法三：ALTER TABLE在已存在的表上创建索引
       ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            索引名 (字段名[(长度)] [ASC |DESC]) ;

#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;


#显示索引信息: SHOW INDEX FROM student;
*/

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);

/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/

临时表

临时表只在当前连接可见，关闭连接时，自动删除临时表并释放所有空间

1
2
insert into 表(字段1, 字段2, 字段3) values (值1, 值2, 值3);
select * from 表;

0x06 函数

数学函数

函数 说明
ABS() 绝对值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
CEILING() 向上取整
FLOOR() 向下取整
SIGN() 符号函数，负数返回-1，正数返回1，0返回0
RAND() 函数用于产生0（包含）到1（不包含）的随机数值

rand()

给表中的某个字段插入随机数数据，取值500~2000

1
update 表名 set 字段名 = floor(500 + rand()*1500) where 条件;

此处内容需要评论回复后（审核通过）方可阅读。

聚合函数

常用于 GROUP BY 从句的 SELECT 查询中

函数 说明
AVG(col) 返回指定列的平均值
COUNT(col) 返回指定列中非NULL值的个数
MIN(col) 返回指定列的最小值
MAX(col) 返回指定列的最大值
SUM(col) 返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

字符串函数

函数 说明
ASCII(char) 返回字符的ASCII码值
BIT_LENGTH(str) 返回字符串的比特长度
CONCAT(s1,s2…,sn) 将s1,s2…,sn连接成字符串
CONCAT_WS(sep,s1,s2…,sn) 将s1,s2…,sn连接成字符串，并用sep字符间隔
INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
FIND_IN_SET(str,list) 分析逗号分隔的list列表，如果发现str，返回str在list中的位置
LCASE(str) 或 LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
LEFT(str,x) 返回字符串str中最左边的x个字符
LENGTH(s) 返回字符串str中的字符数
LTRIM(str) 从字符串str中切掉开头的空格
POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置
QUOTE(str) 用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr) 返回字符串str重复x次的结果
REVERSE(str) 返回颠倒字符串str的结果
RIGHT(str,x) 返回字符串str中最右边的x个字符
RTRIM(str) 返回字符串str尾部的空格
STRCMP(s1,s2) 比较字符串s1和s2
TRIM(str) 去除字符串首部和尾部的所有空格
UCASE(str) 或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果

replace()

字符串替換

1
UPDATE `表名` set `列名` = replace (`列名`,'旧字符串','新字符串') where `列名` like '%旧字符串%';

文本处理

函数 说明
LEFT() 左边的字符
RIGHT() 右边的字符
LOWER() 转换为小写字符
UPPER() 转换为大写字符
LTRIM() 去除左边的空格
RTRIM() 去除右边的空格
LENGTH() 长度
SOUNDEX() 转换为语音值

日期时间

日期时间 函数 说明
CURRENT_DATE() 返回当前的日期
CURRENT_TIME() 返回当前的时间
DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYNAME(date) 返回date的星期名
FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名
NOW() 返回当前的日期和时间
QUARTER(date) 返回date在一年中的季度(1~4)
WEEK(date) 返回日期date为一年中第几周(0~53)
YEAR(date) 返回日期date的年份(1000~9999)

0x07 备份

数据库备份必要性

  • 保证重要数据不丢失
  • 数据转移

MySQL数据库备份方法

  • 使用 mysqldump 备份工具
  • 数据库管理工具，如 SQLyog
  • 直接拷贝数据库文件和相关配置文件

其他

mysqldump

mysqldump 是 MySQL 自带的逻辑备份工具，虽然太适用于大数据量的备份，但因其具有灵活方便、可根据场景定制参数等优点，还是被广泛应用在数据导出领域

其他的，可参考

XtraBackup

Xtrabackup（PXB）是一款用于MySQL数据库物理热备份的开源备份工具，具有备份速度快、支持备份数据压缩、自动校验备份数据、支持流式输出、备份过程中几乎不影响业务等特点，是目前各个云厂商普遍使用的MySQL备份工具