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‘;

数据类型

1. 整数

   • tinyint 1字节

   • SMALLINT 2字节

   • MEDIUMINT 3字节

   • INT 4字节

   • BIGINT 8字节

2. 浮点数

   • FLOAT 4字节

   • DOUBLE 8字节

3. 定点数

   • DECIMAL(M, D), DEC 取值范围与double相同，以字符串形式存放

4. 日期 时间

   类型名称	日期格式	日期范围	存储需求
   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 个字节

5. 字符串

   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个成员）

6. 二进制

   类型名称	说明	存储需求
   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;

常用运算符

算数

1. +

2. -

3. *

4. /

逻辑

1. NOT ！

2. AND &&

3. OR ||

4. XOR

比较运算符

位运算符

操作表中数据

查询

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;

正则

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;

索引

1. 普通索引 使用 index, key CREATE INDEX index_id ON tb_student(id);

2. 唯一索引 使用 unique

3. 主键索引 使用 primary key

4. 空间索引 使用 spatial CREATE SPATIAL INDEX index_line ON tb_student(line);

索引设计原则

1. 选择唯一性索引

2. 为经常需要排序、分组和联合操作的字段建立索引

3. 为常作为查询条件的字段建立索引

4. 限制索引的数目

5. 尽量使用数据量少的索引

6. 数据量小的表最好不要使用索引

7. 尽量使用前缀来索引

8. 删除不再使用或者很少使用的索引

# 创建索引
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 <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>
?
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）

用户管理

# 创建用户
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 [IDENTIFIED BY [PASSWORD] ‘password‘]
?
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 个连接。

修改密码

# 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"
?

日志

1. 错误日志

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

SHOW VARIABLES LIKE ‘log_error‘; # 查看错误日志位置
?
mysqladmin -uroot -p flush-logs  # 删除错误日志

2. 二进制日志

主要用于记录数据库的变化情况，即 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
?
?

3. 查询日志

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

# 查看是否开启查询日志
SHOW VARIABLES LIKE ‘%general%‘;
?
# 配置文件
[mysqld]
log=dir/filename
?
# 删除日志
mysqladmin -uroot -p flush-logs
?
?

4. 慢查询日志

慢查询日志用来记录在 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基础