mysql基础

MySql

操作数据库

SHOW VARIABLES LIKE 'datadir';		# 查看数据目录

show create table students \G		# 查看sql语句

SHOW ENGINES;
SHOW VARIABLES LIKE 'default_storage_engine%';
SET default_storage_engine=< 存储引擎名 >;

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}

DROP DATABASE [ IF EXISTS ] <数据库名>;

ALTER TABLE <表名> ENGINE=<存储引擎名>;	# 设置表的引擎

# 当发现数据无效时回滚
SET sql_mode = 'STRICT_ALL_TABLES' ;
SET sql_mode = 'STRICT_TRANS_TABLES';

数据类型

整数
- tinyint 1字节
- SMALLINT 2字节
- MEDIUMINT 3字节
- INT 4字节
- BIGINT 8字节
浮点数
- FLOAT 4字节
- DOUBLE 8字节
定点数
- DECIMAL(M, D), DEC 取值范围与double相同，以字符串形式存放

日期时间

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901 ~ 2155	1 个字节
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3 个字节
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3 个字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8 个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 个字节

字符串

CHAR(M)	固定长度非二进制字符串	M 字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M和 1<=M<=255
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	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目 (最大值为65535)
SET	一个设置，字符串对象可以有零个或多个SET成员	1、2、3、4或8个字节，取决于集合成员的数量（最多64个成员）

二进制

类型名称	说明	存储需求
BIT(M)	位字段类型	大约 (M+7)/8 字节
BINARY(M)	固定长度二进制字符串	M 字节
VARBINARY (M)	可变长度二进制字符串	M+1 字节
TINYBLOB (M)	非常小的BLOB	L+1 字节，在此，L<2^8
BLOB (M)	小 BLOB	L+2 字节，在此，L<2^16
MEDIUMBLOB (M)	中等大小的BLOB	L+3 字节，在此，L<2^24
LONGBLOB (M)	非常大的BLOB	L+4 字节，在此，L<2^32

SQL

# DDL 数据定义语言
create table students (
	student_id int unsigned not null auto_increment,
	name varchar(30) default 'nobody',
    class_id int,
	sex char(1),
	birth date null,
	primary key(student_id [, name]),	# 这种方式可以设置多字段主键
    constraint students foreign key (class_id) references class(id)
    # 外键
) engine=innodb 
default charset=gb2312
auto_increment=100;	# 初始值为100

desc students;

# DML 数据操作语言
insert into students(student_id, name, sex, birth)
values (41408101, 'mysql教程', '1', '2020-7-27');

ALTER TABLE <表名>
ADD COLUMN <列名> <类型> [FIRST][AFTER <已存在表名>]# 没有first则添加在末尾
CHANGE COLUMN <旧列名> <新列名> <新列类型>
ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
MODIFY COLUMN <列名> <类型>
DROP COLUMN <列名>
DROP FOREIGN KEY fk_tab1_tab2	# 删除外关联的表前先删除外键
RENAME TO <新表名>
CHARACTER SET <字符集名>
COLLATE <校对规则名>

DROP TABLE IF EXISTS table1, table2, ...

# DQL 数据查询语言

# DCL 数据控制语言

约束

主键

# 设置联合主键
create table user(
	'id' int,
    'name' varchar(20),
    primary key(id, name)
);

# 添加主键约束
alter table tb_user
add primary key(id);

# 删除主键约束
alter table tb_user
drop primary key;

外键

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

create table tb_user(
	id int primary key,
    class_id int,
    class_name varchar(24),
    constraint fk_user_class 
    foreign key(class_id, class_name) 
    references tb_class(id, name)
);

# 添加外键
alter table tb_user
add constraint fk_user_class
foreign key(class_id)
references tb_class(id);

# 删除外键
alter table tb_user
drop foreign key fk_user_class;

唯一约束

与主键不同：

主键只能有一个，唯一约束可以有多个;

主键不允许为空，唯一约束允许一个值为空。

<字段> <数据类型> unique

# 添加唯一约束
alter table tb_table
add constraint unique_name UNIQUE(name);

# 删除唯一约束
alter table tb_table
drop index unique_name;

检查约束

create table tb_table(
	'salary'  float,
    check(salary>0 and salary<0)
);

# 添加检查约束
alter table tb_table
add constraint check_id check(id>0);

# 删除检查约束
alter table tb_table
drop constraint check_id;

默认值

create table tb_table(
	name char default 'none'
);
# 增加
alter table tb_table
change column name name varchar(20) default 'nil';
# 删除
alter table tb_table
change column name name varchar(20) default NULL;

非空约束

create table tb_table(
	name char not null
);
# 增加
alter table tb_table
change column name name char not null;
# 删除
alter table tb_table
change column name name char null;

常用运算符

算数

逻辑

NOT ！
AND &&
OR ||
XOR

比较运算符

运算符	作用
=	等于
<=>	安全的等于
<> 或者 !=	不等于
<=	小于等于
>=	大于等于
>	大于
IS NULL 或者 ISNULL	判断一个值是否为空
IS NOT NULL	判断一个值是否不为空
BETWEEN AND	判断一个值是否落在两个值之间

位运算符

运算符	说明	使用形式	举例
\|	位或	a \| b	5 \| 8
&	位与	a & b	5 & 8
^	位异或	a ^ b	5 ^ 8
~	位取反	~a	~5
<<	位左移	a << b	5 << 2，表示整数 5 按位左移 2 位
>>	位右移	a >> b	5 >> 2，表示整数 5 按位右移 2 位

操作表中数据

查询

SELECT *|<字段名>
FROM t_table1 as t1, t_table2 as t2
WHERE <表达式>
GROUP BY <字段>
HAVING <expression>
ORDER BY <字段> [ASC|DESC]
LIMIT num offset num / LIMIT offset_num, limit_num

# distinct 去重
select distinct name from user;	
# 通配符，%匹配任意长度字符串，_ 匹配单一字符。
select name from user where name (not) like 'T%' or 'a_' 
# 范围查询
select name, age from users where age between 18 and 30;
# 空值查询
select name from users where class is null;
# group by 单独使用
select name, sex from users group by sex;
# group by 与 group_concat()
select sex, group_concat(name) from users group by sex;
# group by 与 聚合函数
select sex, count(sex) from users group by sex;
# group by 与 with rollup(统计记录总量)
select sex, group_concat(name) from users group by sex with rollup;
# having 用于过滤分组
select group_concat(name), sex, height from tb_student
group by height
having avg(height) > 170;

# 交叉连接
select * from tb_table1 cross join tb_table2;
select * from tb_course as tb1, tb_students as tb2
where tb1.id = tb2.class_id;

# 内连接
# 通过关键字连接两张表，没有ON连接条件，inner join 与 cross join 等价
select s.name c.course_name
from tb_students s inner join tb_course c
	on s.course_id = c.id
	
# 左连接
# 以左侧为基表，当右侧表无符合项时设置为null
select s.name, c.course_name
from tb_students s left join tb_course c
	on s.course_id=c.id
# 右连接，与左连接相反

# 子查询
select * 
from tb_students
where age>24 and exists(
	select course_name
	from tb_course
	where id=1)
	
SELECT * FROM (SELECT * FROM result) AS Temp;

-- ### 函数
-- 条件函数
CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2 …
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

SELECT
    CASE
    WHEN age < 25 OR age IS NULL 
        THEN '25岁以下' 
    WHEN age >= 25 
        THEN '25岁及以上' 
    END age_cut,
    COUNT(*) number 
FROM
    user_profile 
GROUP BY
    age_cut

-- 日期函数 day() month() year()
select
    day(date) as day,
    count(question_id) as question_cnt
from question_practice_detail
where month(date)=8 and year(date)=2021
group by date

-- 文本函数
-- 窗口函数

日期函数

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间的单独部分
DATE_ADD()	向日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

各种函数：https://www.runoob.com/mysql/mysql-functions.html

窗口函数

https://blog.csdn.net/CoderSharry/article/details/135063960

窗口函数([参数]) OVER (
  [PARTITION BY <分组列>]            -- 指定分组列
  [ORDER BY <排序列 ASC/DESC>]       -- 指定排序列
  [ROWS BETWEEN 开始行 AND 结束行]   -- 指定窗口的范围
)

-- 例子
SELECT product_id, order_date, quantity,
    SUM(quantity) OVER 
       (
        PARTITION BY product_id 
        ORDER BY order_date 
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
       ) AS sum_surrounding_quantities
FROM orders

-- 聚合函数 sum avg count max min
-- 专用窗口函数 RANK DENSE_RANK ROW_NUMBER PERCENT_RANK CUME_DIST

| 函数名 | 分类 | 说明 |
|--------- ----|------------|--------------------------------------------------------------|
| RANK | 排序函数 | 类似于排名，并列的结果序号可以重复，序号不连续 |
| DENSE_RANK | 排序函数 | 类似于排名，并列的结果序号可以重复，序号连续 |
| ROW_NUMBER | 排序函数 | 对该分组下的所有结果作一个排序，基于该分组给一个行数 |
| PERCENT_RANK | 分布函数 | 每行按照公式 (rank-1) / (rows-1) 进行计算 |
| CUME_DIST | 分布函数 | 分组内小于、等于当前 rank 值的行数 / 分组内总行数 |

正则

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	'^b' 匹配以字母 b 开头的字符串	book、big、banana、bike
$ \| 匹配文本的结束字符 \| 'st$' 匹配以 st 结尾的字符串	test、resist、persist
.	匹配任何单个字符	'b.t' 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配零个或多个在它前面的字符	'f*n' 匹配字符 n 前面有任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	'ba+' 匹配以 b 开头，后面至少紧跟一个 a	ba、bay、bare、battle
<字符串>	匹配包含指定字符的文本	'fa' 匹配包含‘fa’的文本	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字符	'[xz]' 匹配 x 或者 z	dizzy、zebra、x-ray、extra
[^]	匹配不在括号中的任何字符	'[^abc]' 匹配任何不包含 a、b 或 c 的字符串	desk、fox、f8ke
字符串	匹配前面的字符串至少 n 次	'b{2}' 匹配 2 个或更多的 b	bbb、bbbb、bbbbbbb
字符串	匹配前面的字符串至少 n 次，至多 m 次	'b{2,4}' 匹配最少 2 个，最多 4 个 b	bbb、bbbb

select *
from tb_users
where name regexp '^J';

插入

insert into tb_users(name, grade)
values('name1', '1'), ('name2', '2');

# 复制表
insert into tb_students(name, sex, info)
select name, sex, info
from tb_users;

修改

update table_name
set name='value', name2='value2'
where id>0
order by id
limit 2,9

删除

delete from table_name
where id>0
order by id
limit 3,9

清空表

# DDL 数据定义语句
# 直接删除表，再建一张新表;delete是一条一条删除
# delete可配合事务回滚，truncate删除后无法找回
# delete返回删除数据行数，truncate返回0
truncate tabel_name

视图

视图都是存储在 information_schema 数据库的 views 表下。SELECT * FROM information_schema.views;

视图的使用方法与表类似。

视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。
存储在数据库中的查询操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。
视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。
视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。
视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。
视图的建立和删除只影响视图本身，不影响对应的基本表。
ORDER BY 子句可以用在视图中，但若该视图检索数据的 SELECT 语句中也含有 ORDER BY 子句，则该视图中的 ORDER BY 子句将被覆盖。

创建视图时，select语句的要求：

用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
SELECT 语句不能引用系统或用户变量。
SELECT 语句不能包含 FROM 子句中的子查询。
SELECT 语句不能引用预处理语句参数

当视图的语句含有特定结构时，不可更新：

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
位于选择列表中的子查询。
FROM 子句中的不可更新视图或包含多个表。
WHERE 子句中的子查询，引用 FROM 子句中的表。
ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。

# 创建视图
create view view_name as <select_语句>

CREATE VIEW view_students_info 
AS SELECT * FROM tb_students_info;

CREATE VIEW v_students_info (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
AS SELECT id,name,dept_id,age,sex,height,login_date
   FROM tb_students_info;

# 查询视图
desc v_students_info;

# 查看视图详细信息
show create view view_name \G;

# 修改视图
alter view view_name as <select_语句>

ALTER VIEW view_students_info
AS SELECT id,name,age FROM tb_students_info;

# 删除视图
drop view view_name [, view_name2 ...]

DROP VIEW IF EXISTS v_students_info;

索引

普通索引使用 index, key CREATE INDEX index_id ON tb_student(id);
唯一索引使用 unique
主键索引使用 primary key
空间索引使用 spatial CREATE SPATIAL INDEX index_line ON tb_student(line);

索引设计原则

选择唯一性索引
为经常需要排序、分组和联合操作的字段建立索引
为常作为查询条件的字段建立索引
限制索引的数目
尽量使用数据量少的索引
数据量小的表最好不要使用索引
尽量使用前缀来索引
删除不再使用或者很少使用的索引

# 创建索引
CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

# 在使用 create table 创建表时添加 primary key, 
# key, index, unique, foreign key

# 在使用 alter table 修改表时，使用 add index/primary key/unique 等

# 查看索引
SHOW INDEX FROM <表名> [ FROM <数据库名>]

SHOW INDEX FROM tb_stu_info2\G

# 删除索引
drop index index_name on table_name_
alter table table_name_ drop ...

存储过程

有时需要多条sql语句处理多个表才能完成操作。存储过程是一组为了完成特定功能的sql语句集合。使用存储过程的目的是将工作预先用 SQL 语句写好并用指定名称存储，经编译和优化后存储在数据库中。当使用时，调用“CALL存储过程名字”即可自动完成。

存储过程都存储在information_schema的Routines表下 SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=存储过程名;

# 创建存储过程
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>
BEGIN
...
END <结束符号，由 delimiter 定义>

DELIMITER //		# 定义结束符号，防止与单个语句的;弄混
CREATE PROCEDURE ShowStuScore()
BEGIN			    # 开始过程
SELECT * FROM tb_students_score;
END //				# 过程结束

DELIMITER //
CREATE PROCEDURE GetScoreByStu
(IN name VARCHAR(30))
BEGIN
SELECT student_score FROM tb_students_score
WHERE student_name=name;
END//

# 查看存储过程
SHOW PROCEDURE STATUS LIKE 存储过程名;	# 状态
SHOW CREATE PROCEDURE 存储过程名;		# 定义

SHOW PROCEDURE STATUS LIKE 'showstuscore' \G

# 修改存储过程
ALTER PROCEDURE 存储过程名 [ 特征 ... ]

ALTER PROCEDURE showstuscore MODIFIES SQL DATA SQL SECURITY INVOKER;

# 删除存储过程
DROP PROCEDURE [ IF EXISTS ] <过程名>

DROP PROCEDURE ShowStuScore;

特征指定了存储过程的特性，可能的取值有：

CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
NO SQL 表示子程序中不包含 SQL 语句。
READS SQL DATA 表示子程序中包含读数据的语句。
MODIFIES SQL DATA 表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
DEFINER 表示只有定义者自己才能够执行。
INVOKER 表示调用者可以执行。
COMMENT 'string' 表示注释信息。

触发器

通过对数据表的相关操作来触发、激活从而实现执行。比如当对 student 表进行操作（mysql中只有 INSERT，DELETE 或 UPDATE）时就会激活它执行。

SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';

# 查看触发器
SHOW TRIGGERS

# 创建触发器
CREATE TRIGGER <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> 
FOR EACH Row
BEGIN
    <触发器主体>
END

# 在insert事件中用 NEW 代替新插入的数据
# 在delete事件中用 OLD 代替旧的数据
# 在update中用 NEW 与 OLD
# 用 NEW.id OLD.name 获取数据

CREATE TRIGGER test_tt AFTER DELETE ON `test`  FOR EACH ROW
BEGIN
UPDATE `member` SET `name` = old.name WHERE id = OLD.id;
END

# 一直得到 NULL， 查找已删除数据，一定查不到
CREATE TRIGGER test_tt AFTER DELETE ON `test`  FOR EACH ROW
BEGIN
DECLARE s VARCHAR(20) DEFAULT 'hello';
SET s= (select name from test where id = old.id);
SET s = concat('a','hello');
UPDATE `member` SET `name` = s WHERE id = OLD.id;
END

CREATE TRIGGER SumOfSalary
BEFORE INSERT ON tb_emp8
FOR EACH ROW
SET @sum=@sum+NEW.salary;

CREATE TRIGGER SumOfSalary
BEFORE INSERT ON tb_emp8
FOR EACH ROW
SET @sum=@sum+NEW.salary;

CREATE TRIGGER double_salary
AFTER INSERT ON tb_emp6
FOR EACH ROW
INSERT INTO tb_emp7
VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);

# 删除触发器
DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>

事件

# 查看事件是否开启
SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
SHOW PROCESSLIST;
# 开启事件
SET GLOBAL event_scheduler = ON ; -- 也可直接修改配置文件 event_scheduler = ON

# 创建事件
CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;

CREATE EVENT IF NOT EXISTS e_test 
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO tb_eventtest(user,createtime)VALUES('MySQL',NOW());

# 查看事件
SELECT * FROM information_schema.events limit 1\G

# 修改事件
ALTER EVENT event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;

ALTER EVENT e_test ON SCHEDULE EVERY 30 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO tb_eventtest(user,createtime) VALUES('MySQL',NOW());

# 删除事件
DROP EVENT [IF EXISTS] event_name;

事务

默认开启事务自动提交，除了显式的(begin, start transaction)，每一条sql语句都被当做事务执行。

> BEGIN
> update ...
> insert ...
> COMMIT

> BEGIN
> update ...
> update ...(error)
> ROLLBACK

SHOW VARIABLES LIKE 'autocommit';	-- 查看是否默认开启事务自动提交
SET autocommit = 0|1|ON|OFF;	    -- 关闭自动提交
# 关闭自动提交后需要执行 commit 才能将数据写入数据库

# 查看事务隔离级别
show variables like '%tx_isolation%';
select @@tx_isolation;

# 修改事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

MySQL 包括的事务隔离级别如下：

读未提交（READ UNCOMITTED）
读提交（READ COMMITTED）
可重复读（REPEATABLE READ）
串行化（SERIALIZABLE）

隔离级别	脏读	不可重复读	幻读
READ UNCOMITTED	√	√	√
READ COMMITTED	×	√	√
REPEATABLE READ	×	×	√
SERIALIZABLE	×	×	×

用户管理

# 创建用户
CREATE USER <用户> 
[ IDENTIFIED BY [ PASSWORD ] 'password' ] 
[ ,用户 [ IDENTIFIED BY [ PASSWORD ] 'password' ]]

CREATE USER 'test1'@"localhost" IDENTIFIED BY 'test1';

SELECT password('test1');		-- 获得密码对应哈希值
CREATE USER 'test1'@'localhost'IDENTIFIED BY PASSWORD '*06C0BF5B64ECE2F648B5F048A71903906BA08E5C';

-- 直接插入mysql数据库的user表中
INSERT INTO mysql.user(Host, User,  authentication_string, ssl_cipher, x509_issuer, x509_subject) 
VALUES ('hostname', 'username', PASSWORD('password'), '', '', '');

# 设置权限
GRANT priv_type 
ON database.table 
TO user

GRANT SELECT 
ON*.* 
TO 'test3'@localhost IDENTIFIED BY 'test3';

# 重命名账号
RENAME USER <旧用户> TO <新用户>

# 删除用户
drop user 用户1, 用户2 ...
DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

# 查看权限
SELECT * FROM mysql.user;
SHOW GRANTS FOR 'username'@'hostname';

# 授权
GRANT priv_type [(column_list)] 
ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user[IDENTIFIED BY [PASSWORD] 'password']] ...
[WITH with_option [with_option]...]

# 取消授权
REVOKE priv_type [(column_list)]...
ON database.table			-- 不指定则是所有
FROM user [, user]...



# 使命令生效
FLUSH PRIVILEGES;

授权中 with_optin 参数

GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户；
MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行 count 次查询；
MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行 count 次更新；
MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接;
MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接。

授予数据库权限时

权限名称	对应user表中的字段	说明
SELECT	Select_priv	表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
INSERT	Insert_priv	表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
DELETE	Delete_priv	表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。
UPDATE	Update_priv	表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
REFERENCES	References_priv	表示授予用户可以创建指向特定的数据库中的表外键的权限。
CREATE	Create_priv	表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
ALTER	Alter_priv	表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
SHOW VIEW	Show_view_priv	表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
CREATE ROUTINE	Create_routine_priv	表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
ALTER ROUTINE	Alter_routine_priv	表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
INDEX	Index_priv	表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
DROP	Drop_priv	表示授予用户可以删除特定数据库中所有表和视图的权限。
CREATE TEMPORARY TABLES	Create_tmp_table_priv	表示授予用户可以在特定数据库中创建临时表的权限。
CREATE VIEW	Create_view_priv	表示授予用户可以在特定数据库中创建新的视图的权限。
EXECUTE ROUTINE	Execute_priv	表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
LOCK TABLES	Lock_tables_priv	表示授予用户可以锁定特定数据库的已有数据表的权限。
ALL 或 ALL PRIVILEGES 或 SUPER	Super_priv	表示以上所有权限/超级权限

授予表权限时

权限名称	对应user表中的字段	说明
SELECT	Select_priv	授予用户可以使用 SELECT 语句进行访问特定表的权限
INSERT	Insert_priv	授予用户可以使用 INSERT 语句向一个特定表中添加数据行的权限
DELETE	Delete_priv	授予用户可以使用 DELETE 语句从一个特定表中删除数据行的权限
DROP	Drop_priv	授予用户可以删除数据表的权限
UPDATE	Update_priv	授予用户可以使用 UPDATE 语句更新特定数据表的权限
ALTER	Alter_priv	授予用户可以使用 ALTER TABLE 语句修改数据表的权限
REFERENCES	References_priv	授予用户可以创建一个外键来参照特定数据表的权限
CREATE	Create_priv	授予用户可以使用特定的名字创建一个数据表的权限
INDEX	Index_priv	授予用户可以在表上定义索引的权限
ALL 或 ALL PRIVILEGES 或 SUPER	Super_priv	所有的权限名

修改密码

# root 修改普通用户密码
alter user 'root'@'localhost' identified by '123';

UPDATE MySQL.user 
SET authentication_string = PASSWORD("newpwd") 
WHERE User = "username" AND Host = "hostname";

GRANT USAGE 
ON *.* 
TO 'user'@’hostname’ IDENTIFIED BY 'newpwd';

# 修改 root 密码
mysqladmin -u username -h hostname -p password "newpwd"

备份与恢复

热备（Hot Backup）
- 逻辑备份
- 裸文件备份
冷备（Cold Backup）
- 关机后，直接将数据目录复制
温备（Warm Backup）

mysqldump -u username -p dbname [tbname ...]> filename.sql  -- 备份
mysql -u username -P [dbname] < filename.sql			   -- 恢复

SELECT * FROM test.person INTO OUTFILE "/opt/person.txt"
# 如果导出出错，show variables like '%secure%';，查看 secure_file_priv
LOAD DATA INFILE "/opt/person.txt"

日志

错误日志

[mysqld]
log-error=dir/{filename}

SHOW VARIABLES LIKE 'log_error'; # 查看错误日志位置

mysqladmin -uroot -p flush-logs  # 删除错误日志

二进制日志

主要用于记录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，不包含数据记录查询操作。

如果 MySQL 数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。

# 查看是否开启
SHOW VARIABLES LIKE 'log_bin';

# 配置文件中设置 log-bin=C:log\mylog

# 查看
SHOW binary logs;
SHOW master status;		-- 查看当前正在写入的二进制日志文件

mysqlbinlog filename.number	# 需要在二进制日志所在的目录下运行该命令

# 删除
RESET MASTER;
PURGE MASTER LOGS TO 'filename.number';    -- 删除指定编号之前的日志
PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss'; 

# 打开或关闭二进制日志
SET SQL_LOG_BIN=0/1;

# 通过二进制日志还原数据库
mysqlbinlog filename.number | mysql -u root -p

查询日志

记录用户的所有操作，包括启动和关闭 MySQL 服务、更新语句和查询语句等。

# 查看是否开启查询日志
SHOW VARIABLES LIKE '%general%';

# 配置文件
[mysqld]
log=dir/filename

# 删除日志
mysqladmin -uroot -p flush-logs

慢查询日志

慢查询日志用来记录在 MySQL 中执行时间超过指定时间的查询语句

# 查看
SHOW VARIABLES LIKE 'slow_query%';

# 设置
[mysqld]
log-slow-queries=dir\filename
long_query_time=10

SET GLOBAL slow_query_log=ON/OFF;
SET GLOBAL long_query_time=n;

日志输出方式

SHOW VARIABLES LIKE '%log_out%';

SET GLOBAL log_output='TABLE';

mysql 结构

+---------------------------------------+
| Tables_in_information_schema          |
|---------------------------------------|
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |# 存储用户表字段|      
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |# 存储键值|
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              | # 存储数据库的表
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            | # 存储索引
| TABLES                                | # 存储用户创建的表  
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     | # 存储主键约束、外键约束、唯一约束、check约束、非空约束
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_BUFFER_PAGE                    |
| INNODB_TRX                            |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_LOCK_WAITS                     |
| INNODB_CMPMEM                         |
| INNODB_CMP                            |
| INNODB_LOCKS                          |
| INNODB_CMPMEM_RESET                   |
| INNODB_CMP_RESET                      |
| INNODB_BUFFER_PAGE_LRU                |
+---------------------------------------+

desc schemata;
+----------------------------+--------------+------+-----+---------+-------+
| Field                      | Type         | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| CATALOG_NAME               | varchar(512) | NO   |     |         |       |
| SCHEMA_NAME                | varchar(64)  | NO   |     |         |       |
| DEFAULT_CHARACTER_SET_NAME | varchar(32)  | NO   |     |         |       |
| DEFAULT_COLLATION_NAME     | varchar(32)  | NO   |     |         |       |
| SQL_PATH                   | varchar(512) | YES  |     | NULL    |       |
+----------------------------+--------------+------+-----+---------+-------+

desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
|-----------------|---------------------|------|-----|---------|-------|
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |  
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       | # 所属的数据库名
| TABLE_NAME      | varchar(64)         | NO   |     |         |       | # 表名称
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       | 
| ENGINE          | varchar(64)         | YES  |     | NULL    |       | 
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       | 
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       | 
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |


desc columns;

| Field                    | Type                | Null | Key | Default | Extra |
|--------------------------|---------------------|------|-----|---------|-------|
| TABLE_CATALOG            | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |       | # 所属的数据库名
| TABLE_NAME               | varchar(64)         | NO   |     |         |       | # 所属的表名称
| COLUMN_NAME              | varchar(64)         | NO   |     |         |       | # 列名称
| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |       |
| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)          | NO   |     |         |       |
| DATA_TYPE                | varchar(64)         | NO   |     |         |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(32)         | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(32)         | YES  |     | NULL    |       |
| COLUMN_TYPE              | longtext            | NO   |     | NULL    |       |
| COLUMN_KEY               | varchar(3)          | NO   |     |         |       |
| EXTRA                    | varchar(27)         | NO   |     |         |       |
| PRIVILEGES               | varchar(80)         | NO   |     |         |       |
| COLUMN_COMMENT           | varchar(1024)       | NO   |     |         |       |

posted @ 2020-12-11 13:01 某某人8265 阅读(121) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部