一、用户操作
- MySQL 数据库,最高权限管理者是 root 用户。
包括select
、update
、delete
、grant
等权限操作。
1. 查看用户
-- 查看所有用户。
SELECT User,Host FROM mysql.user;
SELECT DISTINCT User FROM mysql.user;
-- 查看当前登录用户。
SELECT USER();
2. 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
-- username:创建的用户名。
-- host:指定用户在那台主机上可以登陆(`localhost`为仅限于本机,`%`通配符为任意远程主机)。
-- password:指定用户密码(密码可以为空,为不需要密码)。
-- 先刷新一下权限表。
flush privileges;
-- 创建`test`用户,并指定`本主机`可访问。
CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';
-- 创建`test`用户,并指定`192.168.1.10开头`可访问。
CREATE USER 'test'@'192.168.1.10_' IDENTIFIED BY 'test';
-- 创建`test`用户,并指定`192.168.1.开头`可访问。
CREATE USER 'test'@'192.168.1.%' IDENTIFIED BY 'test';
-- 创建`test`用户,并指定`任何主机`可访问。
CREATE USER 'test'@'%' IDENTIFIED BY 'test';
-- 密码可以为空。
CREATE USER 'test'@'%' IDENTIFIED BY '';
-- 不设置密码。
CREATE USER 'test'@'%';
3. 删除用户
DROP USER 'username'@'host';
DROP USER 'test'@'%';
4. 修改用户
RENAME USER '用户名'@'IP地址' TO '新用户名'@'IP地址';
5. 用户授权
GRANT '权限' ON 'db_name'.'table_name' TO 'username'@'host';
-- 权限:权限符(多个`,`号分割)。
-- db_name:库名(`*`通配符任意数据库)。
-- table_name:表名(`*`通配符任意表)。
-- 授权`test`用户,对`test.user`表,有`查、增、改`权限。
GRANT SELECT,INSERT,UPDATE ON `test.user` TO 'test'@'localhost';
-- `ALL`所有的权限,除了`grant`权限(`grant`命令是`root`用户才有的)。
GRANT ALL ON `test.user` TO 'test@localhost';
-- 授权`test`用户,对`test`数据库所有表,有`增、删、改、查`权限。
GRANT ALL ON test.* TO 'test'@'%';
-- 授权`test`用户,对`所有数据库`,有`增、删、改、查`权限。
GRANT ALL ON *.* TO 'test'@'%';
5.1 授权 GRANT
权限
- 注意:以上用户授权命令,不能给其它用户再授权。如果想让该用户可以授权,用以下命令。
GRANT '权限' ON 'db_name'.'table_name' TO 'username'@'host' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'test'@'%' WITH GRANT OPTION;
5.2 查看用户权限
SHOW GRANTS FOR 'test'@'%';
5.2 撤销用户权限
REVOKE '权限' ON 'db_name'.'table_name' FROM 'username'@'host';
-- 授权1。
GRANT SELECT ON *.* TO 'test'@'%';
-- 撤权1。
REVOKE SELECT ON *.* FROM 'test'@'%';
-- 撤权2。
REVOKE INSERT ON *.* FROM 'test'@'%';
-- 撤权3。
REVOKE ALL ON *.* FROM 'test'@'%';
- 注意:下面两种撤销都无效。
-- 授权1。
GRANT SELECT ON `test.user` TO 'test'@'%';
-- 撤权1。
REVOKE SELECT ON *.* FROM 'test'@'%';
-- 授权2。
GRANT SELECT ON *.* TO 'test'@'%';
-- 撤权2。
REVOKE SELECT ON `test.user` FROM 'test'@'%';
6. 设置密码
6.1 登录状态设置
SET PASSWORD FOR 'username'@'host' = PASSWORD('新密码');
-- 设置指定用户的密码。
mysql>SET PASSWORD FOR 'test'@'%' = PASSWORD("123456");
-- 设置当前登录用户的密码。
mysql>SET PASSWORD = PASSWORD("123456");
-- 修改表方式设置新密码。
UPDATE mysql.user SET password=PASSWORD('新密码') WHERE User='root';
mysql>UPDATE mysql.user SET password=PASSWORD('123456') WHERE User='test' AND Host='localhost';
-- 刷新权限表。
mysql>FLUSH PRIVILEGES;
6.2 未登录状态设置
mysqladmin -u'用户名' -p'旧密码' password '新密码'
mysqladmin -u test -p test -password 123456
6.3 忘记密码
# 1、关闭正在运行的`MySQL`服务。
# 2、打开`DOS`窗口,切换目录。
`cd mysql\bin`
# 3、输入命令回车。
`mysqld --skip-grant-tables`
# `--skip-grant-tables`是启动`MySQL`服务的时候,跳过权限表认证。
# 3.1、my.cnf文件加入配置。
`vim /etc/my.cnf` > `skip-grant-tables`
# 3.2、重启`MySQL`服务。
`systemctl restart mysqld` 或 `service mysqld restart`
# 4、再开一个`DOS`窗口(因为刚才那个`DOS`窗口已经不能动了)切换目录。
`cd mysql\bin`
# 5、输入`mysql`回车,如果成功将出现`MySQL`提示符`>`。
# 6、连接权限数据库。
`show databases;`
`use mysql;`
# 7、改密码。
`update user set password=password("123456") where user="root";`。
# 8、刷新权限。
`flush privileges;`
# 9、退出。
`quit`
# 9.1、修改`MySQL`配置文件。
# 注释掉`skip-grant-tables`。
# 9.2、并重启`MySQL`服务。
`systemctl restart mysqld`。
# 10、注销`MySQL`重启再进入,使用用户名`root`和刚才设置的新密码`123456`重新登录。
# 11、测试不带密码登录`MySQL`,发现还是能够登陆上。
# 但显示数据库时只能看到两个数据库了,说明重启之后跳过密码验证已经被取消了(是因为数据库里存在无须口令的账户)。
二、数据库操作
1. 创建库
-- 创建数据库。
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] '库名'
[[DEFAULT] CHARACTER SET[=]'字符集名']
[[DEFAULT] COLLATE[=]'校对规则名'];
-- 创建`test`库。
CREATE DATABASE `test`
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
-- 使用数据库。
USE '库名';
-- 查看正在使用的库。
SELECT DATABASE();
-- 修改`test`库。
ALTER DATABASE `test`
DEFAULT CHARACTER SET gb2312
DEFAULT COLLATE gb2312_chinese_ci;
2. 查看库
-- 查看所有库。
SHOW {DATABASES|SCHEMAS}
[LIKE '%模糊匹配%'|WHERE 'expr'];
SHOW DATABASES LIKE '%test%';
-- 查看数据库(非系统表)。
SHOW TABLES;
3. 删除库
DROP {DATABASE|SCHEMA} [IF EXISTS] '库名';
DROP DATABASE `test`;
三、数据表操作
1. 创建表
CREATE [TEMPORARY] TABLE '表名' (
'主键名' '数据类型' ['约束'] ['自增'],
'字段1' '数据类型' ['约束'],
...
[, '表级完整性约束条件']
) [ENGINT='引擎类型'];
-- TEMPORARY:临时表。
-- AUTO_INCREMENT:设置自增属性,自动编号。
-- DEFAULT:指定默认值。
-- PRIMARY KEY('主键字段'):指定主键。
CREATE TABLE IF NOT EXISTS `customers`
(
id INT NOT NULL AUTO_INCREMENT,
name CHAR(50) NOT NULL,
sex CHAR(1) NOT NULL DEFAULT '0',
address CHAR(100) NULL,
contact CHAR(50) NULL,
PRIMARY KEY (id)
);
- 数值型:int、double、float(n)
- 字符型:char(n) 定长字符串、varchar(n) 变长字符串
- 位串型:bit(n)、bit varying(n)
- 时间型:date、time
-- 查看创建表语句。
SHOW CREATE TABLE '表名';
-- 查看本库所有表
SHOW TABLES;
2. 查看表
SHOW [FULL] COLUMNS {FROM|IN} '表名' [{FROM|IN} '库名'] [LIKE 'pattern'|WHERE 'expr'];
SHOW COLUMNS FROM `customers`;
{DESCRIBE|DESC} '表名' ['列名'|wild];
DESC `customers` name;
-- 查看指定表结构。
DESC '表名';
3. 修改表
- ALTER TABLE
- 重命名 表名。
- 新增、修改、删除 字段和字段类型。
- 创建、修改、删除 索引和外键。
- 修改 表注释、引擎类型。
- 创建 表触发器、存储过程。
3.1 修改表名
RENAME TABLE '原表名' [TO] '新表名';
ALTER TABLE '原表名' RENAME TO '新表名';
-- 写法1。
RENAME TABLE `customers` TO `customers_bk`;
-- 写法2。
ALTER TABLE `customers` RENAME TO `customers_bk`;
3.2 修改表字符集
-- 修改表字符集(gbk、utf8)。
ALTER TABLE '表名' CHARACTER SET '字符集';
3.3 添加列
-- ADD [COLUMN] 新增列。
ALTER TABLE '表名' ADD '列名' '数据类型(长度)' ['约束'];
ALTER TABLE '表名' ADD COLUMN '列名' '数据类型(长度)' ['约束'];
-- 添加一个列。
ALTER TABLE `customers`
-- `sex`列后新增`city`列
ADD COLUMN `city` CHAR(50) NOT NULL DEFAULT 'ShangHai' AFTER `sex`;
-- 添加多个列。
ALTER TABLE '表名'
ADD COLUMN '列名1' '数据类型(长度)' ['约束'] COMMENT '注释1',
ADD COLUMN '列名2' '数据类型(长度)' ['约束'] COMMENT '注释2';
3.4 修改列
-- CHANGE [COLUMN] 修改列。
ALTER TABLE '表名' CHANGE '原列名' '新列名' '数据类型(长度)' ['约束'];
-- 修改列名。
ALTER TABLE `customers`
-- `sex`列重命名为`sex_bk`
CHANGE COLUMN `sex` `sex_bk` CHAR(1) NULL DEFAULT 'M';
-- MODIFY [COLUMN] 只修改指定列的数据类型,不涉及列名。
ALTER TABLE '表名' MODIFY '列名' '数据类型(长度)' ['约束'];
-- 修改列属性。
ALTER TABLE `customers`
-- `name`列修改类型
MODIFY COLUMN `name` CHAR(20) FIRST;
3.5 删除列
-- DROP [COLUMN] 删除列。
ALTER TABLE '表名' DROP '列名';
ALTER TABLE '表名' DROP COLUMN '列名';
ALTER TABLE `customers` DROP COLUMN `contact`;
4. 删除表
DROP [TEMPORARY] TABLE [IF EXISTS] '表名'[',表名2'...] [RESTRICT|CASCADE];
DROP TABLE `test`;
DROP TABLE IF EXISTS `student`;
四、索引操作
- 索引分类:
- 单列索引。
- 组合索引。
- 索引类型:
- 普通索引:index、key
- 唯一索引:unique
- 主键索引:primary key
1. 查看索引
SHOW {INDEX|INDEXS|KEYS} {FROM|IN} '表名'
[{FROM|IN} '库名'] [WHERE 'expr'];
SHOW INDEX FROM '表名';
SHOW KEYS FROM '表名';
SHOW INDEX FROM `dept`;
- Table:
表的名称。- Non_unique:
如果索引不能包括重复词,则为0。
如果可以,则为1。- Key_name:
索引的名称。- Seq_in_index:
索引中的列序列号,从1开始。- Column_name:
列名称。- Collation:
列以什么方式存储在索引中。
在 MySQL 中,有值A(升序)或NULL(无分类)。- Cardinality:
索引中唯一值的数目的估计值。通过运行 ANALYZE TABLE 或 myisamchk -a 可以更新。
基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。
基数越大,当进行联合时,MySQL 使用该索引的机 会就越大。- Sub_part:
如果列只是被部分地编入索引,则为被编入索引的字符的数目。
如果整列被编入索引,则为NULL。- Packed:
指示关键字如何被压缩。
如果没有被压缩,则为NULL。- Null:
如果列含有NULL,则含有YES。
如果没有,则该列含有NO。- Index_type:
用过的索引方法(BTREE、FULLTEXT、HASH、RTREE)。- Comment:
说明。- Index_comment:
索引说明。
1.2 查看索引大小
SELECT
DATA_LENGTH / (1024 * 1024 * 1024) AS 'DATA_SIZE(GB)',
INDEX_LENGTH / (1024 * 1024 * 1024) AS 'INDEX_SIZE(GB)',
TABLE_SCHEMA,
TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_NAME = 'test_1';
2. 创建索引
CREATE [UNIQUE] INDEX '索引名'
ON '表名'('索引描述');
-- UNIQUE:唯一索引。
-- `customers`表`name`列前三个字符,创建一个升序索引。
CREATE INDEX `index_customers`
ON `test`.`customers`(name(3) ASC);
-- 组合索引。
CREATE INDEX `index_cust`
ON `test`.`customers`(cust_name,cust_id);
-- 创建表,同时创建`主键索引`。
[CONSTRAINT['symbol']] PRIMARY KEY('index_col_name'),
-- 创建表,同时创建`唯一索引`。
[CONSTRAINT['symbol']] UNIQUE [INDEX|KEY] ['索引名']('index_col_name', ...),
-- 创建表,同时创建`外键索引`。
[CONSTRAINT['symbol']] FOREIGN KEY ['索引名']('index_col_name', ...),
-- 修改表,同时添加`主键索引`。
ADD [CONSTRAINT['symbol']] PRIMARY KEY('index_col_name', ...),
-- 修改表,同时添加`普通索引`。
ADD {INDEX|KEY} ['索引名']('index_col_name', ...),
-- 修改表,同时添加`唯一索引`。
ADD [CONSTRAINT['symbol']] UNIQUE [INDEX|KEY] ['索引名']('index_col_name', ...),
-- 修改表,同时添加`外键索引`。
ADD [CONSTRAINT['symbol']] FOREIGN KEY ('index_col_name', ...),
CREATE TABLE `seller` (
`seller_id` int not null auto_increment,
`seller_name` char(50) not null,
`seller_address` char(50) null,
`seller_contact` char(50) null,
`product_type` int(5) null,
`sales` int null,
primary key(seller_id,product_type),
index index_seller(sales)
);
-- `seller`表`姓名`列,添加`普通索引`。
ALTER TABLE `test`.`seller`
ADD INDEX `index_seller_name`('seller_name');
3. 删除索引
-- DROP INDEX 删除各种类型索引。
DROP INDEX '索引名';
DROP INDEX '索引名' ON '表名';
-- 删除主键(表中只有一个主键,可以不加主键名)。
DROP PRIMARY KEY;
-- 删除外键。
DROP FOREIGN KEY;
DROP INDEX `index_cust` ON `test`.`customers`;
-- 删除`customers`表,的`主键索引`和`普通索引`。
ALTER TABLE `customers`
DROP PRIMARY KEY,
DROP INDEX `index_customers`;
五、单表操作
1. 新增
INSERT ... VALUES
语句,插入 单行 或 多行 数据。
- 数据是字符型,必须使用 单引号 或 双引号(如:“张三”)。
- AUTO_INCREMENT 列,无需赋值 或 0,自动生成。
-- 插入指定列的值。
INSERT INTO '表名' ('列名1', '列名1', ..., '列名n')
VALUES ({'值1'|DEFAULT}, '值1', ..., '值n');
-- 插入所有列的值。
INSERT INTO '表名' VALUES ('值1', '值2', ...);
INSERT INTO `customers` VALUES (0, '张三', 'F', '北京', '朝阳');
INSERT INTO `customers` VALUES (0, '李四', DEFAULT, '上海', NULL);
INSERT ... SET
语句。
INSERT [INTO] '表名'
SET '列名1'={'expr'|DEFAULT}, ..., '列名n'='值n';
INSERT INTO `customers`
SET cus_name = '李四', cus_address = '西安', cus_sex = DEFAULT;
INSERT ... SELECT
语句。
INSERT [INTO] '表名' [('列名1', ..., '列名n')]
SELECT ...
- 注意:两个表的 字段类型 和 个数 要匹配。
INSERT INTO `customers` (cus_name, cus_sex)
SELECT name, sex FROM `customers_bk`;
2. 修改
UPDATE '表名'
SET '列名1' = {'expr'|DEFAULT}, ..., '列名n' = {'expr'|DEFAULT}
[WHERE '条件']
[ORDER BY '排序字段']
[LIMIT '行数'];
-- 修改记录。
UPDATE '表名' SET '列名1'='值1', '列名2'='值2' [WHERE '条件'];
UPDATE `customers`
SET `cus_address` = '深圳'
WHERE `cus_name` = '张三';
3. 删除
DELETE FROM '表名'
[WHERE '条件']
[ORDER BY '排序字段']
[LIMIT '行数'];
-- 删除记录。
DELETE FROM '表名' [WHERE '条件'];
DELETE FROM `customers`
WHERE cus_name = '李四';
TRUNCATE TABLE '表名';
4. 单表查询
- SQL 查询语句顺序:
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT;
SELECT [ALL|DISTINCT] '*|列名'
FROM '表名'
[WHERE '条件']
[GROUP BY '分组字段']
[HAVING '组条件']
[ORDER BY '排序字段' [ASC|DESC]]
[LIMIT '行数'];
-- `*`通配符。
子句 | 说明 | 是否必须 |
SELECT | 要返回的列或表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按组计算聚合时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
LIMIT | 要检索的行数 | 否 |
SELECT cus_name AS '姓名', cus_address AS '地址'
FROM `customers`;
4.1 聚合函数
函数名 | 说明 |
COUNT | 求组中项数,返回INT类型整数 |
MAX | 求最大值 |
MIN | 求最小值 |
SUM | 返回表达式中所有值的和 |
AVG | 求组中值的平均值 |
STD或STDDEV | 返回给定表达式中所有值的标准值 |
VARIANCE | 返回给定表达式中所有值的方差 |
GROUP_CONCAT | 返回由属于一组的列值连接组合而成的结果 |
BIT_AND | 逻辑与 |
BIR_OR | 逻辑或 |
BIT_XOR | 逻辑异或 |
4.2 条件过滤
比较运算符 | 说明 |
= | 等于 |
<> 或 != | 不等于 |
< | 小于 |
> | 大于 |
<= | 小于等于 |
>= | 大于等于 |
<=> | 不会返回 UNKNOWN |
- WHERE:
- 条件关键字:
>
、<
、=
、>=
、<=
、<>
。- LIKE 中可以使用占位符:
_
:下划线匹配一个字符。%
:可以匹配任意多个字符。[NOT] IN ('子查询')
后跟着一组值。- AND、OR、NOT。
BETWEEN ... AND
。IS NULL
、IS NOT NULL
。expression {=|<|<=|!=|>=...} {ALL|SOME|ANY} ('子查询')
。
-- like
like '张%';
like '张_';
like '%明';
like '%明%';
-- in
id in (1, 2, 3);
- 子查询分类:
- 标量子查询:返回单一值的标量,最简单的形式。
- 列子查询:返回的结果集时 N行一列。
- 行子查询:返回的结果集是 一行N列。
- 表子查询:返回的结果集是 N行N列。
'expression' {=|<|<=|!=|>=...} {ALL|SOME|ANY} ('子查询')
-- ALL:满足所有。
-- SOME、ANY:满足任意一个。
SELECT 's1' FROM 'table1' WHERE 's1' > ANY(SELECT s2 FROM 'table2');
SELECT 's1' FROM 'table1' WHERE 's1' > SOME(SELECT s2 FROM 'table2');
-- EXISTS('子查询')
SELECT *
FROM 'table1'
WHERE 'city' = '上海'
AND EXISTS(
SELECT *
FROM 'table2'
WHERE 'table1.cus_id' = 'table2.cus_id'
);
4.3 分组
GROUP BY '列名' [ASC|DESC] [WITH ROLLUP]
-- WITH ROLLUP:分组的汇总行。
-- 分组
GROUP BY '列名...';
-- 结果再过滤
HAVING '条件';
-- 对数据进行排序,`ASC`升序默认,`DESC`降序
ORDER BY '列名' [ASC|DESC];
4.4 案例
-- 将成绩`+10`分进行显示
select name, english+10, chinese+10, math+10 from exam;
-- 显示这个人的名称和对应总成绩的分数
select name, english+chinese+math from exam;
-- 使用`as`起别名`[AS]`可以省略
select name, english+chinese+math as sum from exam;
-- 查询学生的信息,按照英语成绩降序排序,如果英语成绩相同,按照语文降序
select * from exam order by english desc, chinese desc;
六、多表操作
1. 多表创建
- 一对多关系 的建表原则:
在多的一方创建一个字段,这个字段作为 外键,指向一的一方的 主键。
- 多对多关系 的建表原则:
创建 中间表,表中两个字段分别作为 外键,指向 多对多双方 的各自的 主键。
- 一对一关系 的建表原则:
- 唯一外键对应,假设 一对一 的双方是 一对多 的关系,在多的一方创建 外键 指向一的一方的主键,需要在外键上添加
unique
唯一约束;- 主键对应,将一对一的双方的主键建立映射。
- 交叉连接。
2. 交叉连接
- 交叉连接后返回的查询结果集的记录行数,为两张表记录行数的乘积。
-- 笛卡尔积。
SELECT * FROM '表1' CROSS JOIN '表2';
-- [CROSS JOIN] 可以省略。
SELECT * FROM 'A','B';
3. 内连接(INNER JOIN
)
INNER
可以省略。
-- 显式内连接
SELECT * FROM 'A' INNER JOIN 'B' ON '条件';
-- 隐式内连接
SELECT * FROM A,B WHERE '条件';
SELECT * FROM `customer` c INNER JOIN `orders` o ON c.cid = o.cid;
SELECT * FROM `customer` c, `orders` o WHERE c.cid = o.cid;
4. 外连接(OUTER JOIN
)
OUTER
可以省略。
-- 左外连接 LEFT [OUTER] JOIN
SELECT * FROM A LEFT OUTER JOIN B ON '条件';
-- 右外连接 RIGHT [OUTER] JOIN
SELECT * FROM A RIGHT OUTER JOIN B ON '条件';
SELECT * FROM `customer` c LEFT OUTER JOIN orders o ON c.cid = o.cid;
SELECT * FROM `customer` c RIGHT OUTER JOIN orders o ON c.cid = o.cid;
-- 多表查询的子查询,一个SQL语句查询的过程中需要依赖另一个查询语句
SELECT * FROM customer c, orders o
WHERE c.cid = o.cid
AND c.cid IN (SELECT cid FROM orders WHERE addr LIKE '海淀%');
七、导入导出
1. 导出数据
- 按库导出数据:
/usr/local/mysql/bin/mysqldump
-- 按库导出`表结构 + 数据`。
mysqldump -h'ip' -P'port' -u'用户名' -p'密码' '库名' > '库名.sql'
-- 指定库。
mysqldump -u'root' -p'密码' --databases 'db_name' > '/root/db_name.sql'
-- 所有库。
mysqldump -u'root' -p'密码' --all-databases > '/root/db_name.sql'
- 按表导出数据:
mysqldump -u'用户名' -p'密码' '库名' 'table1' 'table2' 'table3' > '表名.sql'
-- 按表导出`user表`。
mysqldump -u'root' -p'密码' '库名' '表名' > '表名.sql'
-- 导出`指定表`。
mysqldump -u'root' -p'密码' '库名' --tables 'table1' 'table_2' 'table_3' > '表名.sql'
-- 导出`忽略指定表`。
mysqldump -u'root' -p'密码' --databases '库名' --default-character-set=utf8
--ignore-table='db_name.table_1'
--ignore-table='db_name.table_2'
--ignore-table='db_name.table_3' > 'db.sql'
-d
:表结构。-t
:数据。
-- 按库导出`表结构`。
mysqldump -u'root' -p'密码' -d '库名' > '库名.sql'
-- 按库导出`数据`。
mysqldump -u'root' -p'密码' -t '库名' > '库名.sql'
--add-drop--database
:
CREATE DATABASE 语句前添加 DROP DATABASE 语句(默认:开启)。--add-drop-table
:
CREATE TABLE 语句前添加 DROP TABLE 语句(默认:开启)。--skip-add-drop-table
:
不加 DROP TABLE 语句。
mysqldump -u'root' -p'密码' -d --add-drop-table '库名' > '库名.sql'
# 不加`DROP TABLE语句`。
mysqldump -u'root' -p'密码' -d --skip-add-drop-table '库名' > '库名.sql'
2. 导入数据
- 未登录导入:
# 1、先创建一个新的数据库
create database 'db2';
# 2、将`db1.sql数据库文件`导入到`db2数据库`中。
mysqldump -u'root' -p'密码' '库名2' < '库名.sql'
mysqldump -u'root' -p'密码' '库名2' '表名' < '表名.sql'
mysqldump -u'root' -p'密码' -d '库名2' < '库名.sql'
mysql -u'root' -p'密码' '库名' < '库名.sql'
- 登录后导入:
# 1、登录。
mysql -h'192.168.0.1' -P'3306' -u'root' -p'123456'
-- 切换`db1库`。
mysql>use db1;
-- 执行`user.sql文件`。
mysql>source /root/user.sql
- 导入到远程数据库:
mysqldump -u'root' -p'密码' 'database_name' \
| mysql -h'192.168.1.2' 'database_name'
八、其他
1. MySQL——数据库表信息
-- 查看数据库表基本信息
show table status like 'employees';
select *
from information_schema.TABLES
where information_schema.TABLES.TABLE_SCHEMA = 'learn_mysql'
and information_schema.TABLES.TABLE_NAME = 'employees';
2. MySQL——数据库大小
-- 查看`MySQL`数据库大小(单位字节,除以`1024 * 1024`为`MB`)
SELECT sum(DATA_LENGTH) + sum(INDEX_LENGTH)
FROM information_schema.TABLES
where TABLE_SCHEMA = 'learn_mysql';
3. MySQL——表的最后修改时间
-- 查看`MySQL`表的最后修改时间
select TABLE_NAME, UPDATE_TIME
from information_schema.TABLES
where TABLE_SCHEMA = 'learn_mysql'
order by UPDATE_TIME desc
limit 1;
select TABLE_NAME, UPDATE_TIME
from information_schema.TABLES
where TABLE_SCHEMA = 'learn_mysql'
and information_schema.TABLES.TABLE_NAME = 'employees';
4. MySQL——时区和时间
-- 查看数据库时间
select now();
SELECT CURRENT_TIMESTAMP;
-- 查看数据库时区(默认`SYSTEM`、`UTC`)
show variables like "%time_zone%";
- UTC:协调世界时。
- SYSTEM:MySQL 使用 SYSTEM 的时区(SYSTEM 是 UTC 时区)。
-- 修改`MySQL`全局时区为北京时间(即东8区)
set global time_zone = '+8:00';
-- 修改当前会话时区
set time_zone = '+8:00';
set global time_zone = '+8:00';
-- 立即生效
flush privileges;
- 东 8 区:
5. MySQL——最大 SQL 长度
- mysql允许最大sql语句长度配置
- 由一个抓瞎的SQL引申出去
- MySQL 对每个客户端的连接:都会分配 连接Buffer 和 结果集Buffer
- 连接Buffer:
接收 客户端 发送的 SQL语句,控制语句大小,实现 慢SQL 相关优化。
- 初始分配大小是
net_buffer_length
(默认:16k)。- 可以动态增长,最大到
max_allow_packet
(默认:4M)。max_allow_packet
是 MySQL 控制 网络包大小的参数。- 这个参数是会话只读的,言外之意就是只能全局修改,新建连接才生效。
-- 连接`buffer`大小(默认: 16384 = 16k)
show global variables like 'net_buffer_length';
-- `SQL`最大大小(默认: 4194304 = 4m)
show global variables like 'max_allowed_packet';
5.1 问题分析
-- `in_list`元素最多个数
select * from 'table' where 'conditions' and `id` in ('in_list');
5.2 测试脚本
-- 修改`max_allowed_packet`为128K
set global max_allowed_packet = 1024*128;
- 测试 test.py:
#!/bin/env python
#coding:utf-8
import pymysql
def get_in_list(i):
in_list = ''
for i in range(1,i):
in_list = str(i) + ',' + in_list
in_list = in_list + str(i+1)
return in_list
def exec_mysql(sql):
conn = pymysql.connect(host='127.0.0.1', user='root',
password='123456', database='learn_mysql', charset='utf8')
cursor = conn.cursor()
cursor.execute(sql)
results = cursor.fetchall()
if results:
print("get results")
conn.close()
if __name__ == "__main__":
in_list = get_in_list(100)
sql = "select * from qs_user where id in ({})".format(in_list)
# 打印出`sql`占用的字节数
print(len(in_list.encode()))
exec_mysql(sql)
- 当 i = 4200 时:
- SQL 大小为 19892 个字节(大于
net_buffer_length
)。- 能正常返回数据,结果如下。
[root@qs python_scripts]# python test.py 19892get results
- 当 i = 23690 时:
- SQL 大小为 131033 个字节(小于
max_allowed_packet
)。- 能正常返回数据,结果如下。
[root@qs python_scripts]# python test.py 131033get results
- 当 i = 23691 时:
- SQL 大小为
131039
个字节(小于max_allowed_packet
)。- 抛出异常。
[root@qs python_scripts]# python test.py
131039
Traceback (most recent call last):
File "test.py", line 29, in <module>
exec_mysql(sql)
File "test.py", line 19, in exec_mysql
cursor.execute(sql)
File "/usr/lib64/python2.7/site-packages/pymysql/cursors.py", line 170, in execute
result = self._query(query)
File "/usr/lib64/python2.7/site-packages/pymysql/cursors.py", line 328, in _query
conn.query(q)
File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 732, in _read_query_result
result.read()
File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 1075, in read
first_packet = self.connection._read_packet()
File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 684, in _read_packet
packet.check_error()
File "/usr/lib64/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
File "/usr/lib64/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.InternalError: (1153, u"Got a packet bigger than 'max_allowed_packet' bytes")
- MySQL 错误日志如下:
2020-03-10T09:07:46.992043Z 32 [Note] Aborted connection 32 to db: 'sbtest' user: 'root' host: '127.0.0.1' (Got a packet bigger than 'max_allowed_packet' bytes)
5.3 测试结果
- 为什么 SQL 字节数 131039 < 131072。
小于max_allowed_packet
MySQL 还是报错了呢?
- 因为这里还需要加上 MySQL 的包头大小,这个包头的大小是多少呢?
还是用 i = 23690 的例子,在 MySQL 服务器上抓包。- 可以看到 MySQL 接收到的包大小是 131070 字节。
那么这个 包头大小 就为 37 字节了(未深入研究,留到以后研究)。
- 上述测试中 i = 23691 的例子,SQL 大小为 131039 字节,加上包头的大小 37 字节。
- 总大小为 131076 字节,大于
max_allowed_packet
的 131072。- 所以 MySQL 报错,符合逻辑。
5.4 测试结论
- 虽然 MySQL 没有限制 子查询 内元素个数。
- 但还是不建议元素太多,会影响执行计划。
- 同时也会影响 SQL解析的效率 和 内存占用。
- 适当调大
net_buffer_length
。
- 最好能够一次性 缓存SQL,无需再分配内存。
- 且这是一个全局性参数。
- 适当调大
max_allowed_packet
。
- 但是也不要分配过大。
- 这是一个会话级变量,生产建议为
32M
最佳。
- 学会利用 抓包,解决网络相关的问题。
6. MySQL——最大连接
-- `MySQL实例`的最大连接数(上限值是16384)。
SHOW VARIABLES LIKE '%max_connections%';
set global max_connections = 1000;
-- `数据库用户`的最大连接数。
SHOW VARIABLES LIKE '%max_user_connections%';
- MySQL 会为每个连接提供缓冲区,意味着消耗更多的内存。
- 如果连接数设置太高硬件吃不消,太低又不能充分利用硬件。
- 一般要求两者比值超过 10%。计算方法如下:
max_used_connections / max_connections * 100% = 3 / 100 * 100% ≈ 3%
- my.cnf 配置文件,修改最大连接数:
[mysqld]
max_connections = 100
max_used_connections = 20
-- 当前连接数
SHOW STATUS LIKE '%Threads_connected%';
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE '%Connection%';
-- 进程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
-- 查询非`Sleep`状态的链接,按消耗时间倒序展示。
SELECT id, db, user, host, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;
-- 查询执行时间超过`2`分钟的线程,然后拼接成`kill`语句。
SELECT concat('kill ', id, ';')
FROM information_schema.processlist
WHERE command != 'Sleep'
AND time > 2*60
ORDER BY time DESC;
-- 半小时
set global wait_timeout=1800;
7. MySQL——缓存机制
-- 是否开启缓存(默认: OFF关闭、ON打开、DEMAND只有明确写了`SQL_CACHE`的查询才会写入缓存)
show variables like "%query_cache_type%";
-- 缓存使用的总内存空间大小(单位字节,默认:1048576 = 1m)
-- 必须是`1024`的整数倍,否则`MySQL`实际分配大小可能跟这个数值不同(这个应该跟文件系统的`blcok`大小有关)
show variables like "%query_cache_size%";
-- 分配内存块时的最小单位大小(默认: 4096 = 4k)
show variables like "%query_cache_min_res_unit%";
-- `MySQL`能够缓存的最大结果,如果超出,则增加`Qcache_not_cached`的值,并删除查询结果
show variables like "%query_cache_limit%";
-- 如果某个数据表被锁住,是否仍然从缓存中返回数据(默认: OFF关闭,表示仍然可以返回)
show variables like "%query_cache_wlock_invalidate%";
-- 缓存池中空闲块的个数
SHOW STATUS LIKE '%Qcache_free_blocks%';
-- 缓存中空闲内存量
SHOW STATUS LIKE '%Qcache_free_memory%';
-- 缓存命中次数
SHOW STATUS LIKE '%Qcache_hits%';
-- 缓存写入次数
SHOW STATUS LIKE '%Qcache_inserts%';
-- 因内存不足删除缓存次数
SHOW STATUS LIKE '%Qcache_lowmen_prunes%';
-- 查询未被缓存次数(例如: 查询结果超出缓存块大小,查询中包含可变函数等)
SHOW STATUS LIKE '%Qcache_not_cached%';
-- 当前缓存中缓存的`SQL`数量
SHOW STATUS LIKE '%Qcache_queries_in_cache%';
-- 缓存总`block`数
SHOW STATUS LIKE '%Qcache_total_blocks%';
九、异常
1. ERROR 1105 (HY000): unsupported yet: subquery in select list with aggregation
show variables like "%mode%";
- 错误:
- 标准:
set global sql_mode=NO_ENGINE_SUBSTITUTION;