MySQL 必知必会

MySQL是一种DBMS，即是一种数据库软件

1 查询数据

show databases;

use db_name;

show tables;

describe table_name;
desc table_name;
show columns from table_name;

show create database db_name;

show create table table_name;

help show;

2 通配符 like

百分号% - 任意字符出现任意次数（0/1/多次）

下划线_ - 匹配任意一个字符

3 正则表达式 regexp

// 无需数据表进行正则表达式测试，匹配返回1，不匹配返回0
select 'hello' regexp '[A-Z]{5}';

4 插入数据

insert into table_name values(NULL, xx, yy);

// 应该总是使用带列名的列表
insert into table_name(column1, column2, column3) values(val1, val2, val3);

// 一次插入多行数据
insert into table_name(col1, col2, col3) values(v1, v2, v3), (v4, v5, v6);

// 插入查询数据
insert into table_one(col1, col2, col3) select col4, col5, col6 from table_two where ...;

5 更新数据

update table_name set column1=val1, column2=val2 where column3=val3;

// 多行更新发生错误时，忽略错误继续更新
update ignore table_name set column=val where ...;

6 删除数据

delete from table_name where column=val;

// 删除整个表的全部数据
truncate table table_name;

7 创建和操纵表

// 创建数据表
create table if not exists table_name
(
	id int not null auto_increment,
    column data_type constraint,
    primary key (id)
) engine=InnoDB;

// 新增一列
alter table table_name add new_column data_type;

// 删除一列
alter table table_name drop column column_name;

// 添加外键
alter table table_name1 
add constraint foreign_key_name 
foreign key (column1_in_table_name1) 
references table_name2 (column2_in_table_name2);

// 删除表
drop table table_name;

// 重命名表
rename table old_table_name to new_table_name;

rename table 
old_table_name1 to new_table_name1,
old_table_name2 to new_table_name2,
old_table_name3 to new_table_name3;

8 使用视图

视图是虚拟的表，其中不包含真实数据，只包含根据需要而进行查询的SQL语句。

// 创建视图
create view view_name as
select * from table_name where column=val ...;

// 使用视图，视图定义中的where子句将会和使用视图时的where子句自动组合
select * from view_name where ...;

// 查看创建视图语句
show create view view_name;

// 删除视图
drop view view_name;

9 使用存储过程

为了以后的使用，而保存的一条或多条MySQL语句的集合，可视为MySQL的批处理文件。

// 定义存储过程
delimiter //
create procedure procedure_name()
begin
	select avg(price) as average_price from product 
	where is_valid=1;
end //
delimiter ;

// 调用存储过程
call procedure_name();
call procedure_name(params1, params2, params3);

// 删除存储过程
drop procedure if exists procedure_name;

// 使用带变量的存储过程，in-输入参数，out-输出参数
delimiter //
create procedure product_price(
	in prod_id int,
    out pmin decimal(8, 2),
    out pmax decimal(8, 2),
    out pavg decimal(8, 2)
)
begin
	select min(price) from product where product_id = prod_id into pmin;
	select max(price) from product where product_id = prod_id into pmax;
	select avg(price) from product where product_id = prod_id into pavg;
end //
delimiter ;

// 所有MySQL变量都以@开始
call product_price(
	2022,
    @min_price,
    @max_price,
    @avg_price
);

select @min_price, @max_price, @avg_price;

// 检查存储过程，显示创建语句
show create procedure procedure_name;

// 检查存储过程，显示创建信息，创建时间创建人等
show procedure status like "procedure_name";

10 游标

游标是一个储存在数据库服务器上的查询结果集，它不是一个SQL语句，而是该语句查询出来的结果集。通过游标，应用程序可以滚动的浏览其中的数据。

主要步骤：

1. 声明游标，定义需要使用的MySQL语句，此时不会检索数据；
2. 打开游标，此时会执行MySQL语句，检索得到数据集；
3. 对于填有数据的游标，根据需要使用其中的数据；
4. 关闭游标，结束使用游标。

// 声明游标
declare cursor_name cursor for
select * from table_name where column=val ...;

// 打开游标
open cursor_name;

// 使用游标数据
fetch ...;

// 关闭游标
close cursor_name;

11 触发器

MySQL在响应insert，update，delete操作时自动执行预定义的SQL语句，这就是触发器的功能。

// 创建触发器，对于insert，update，delete三种操作，分别有before和after两类，共计6种组合
create trigger trigger_name after insert on table_name 
for each row select ...;

// 删除触发器，不支持修改触发器，只能先删除后重新创建
drop trigger trigger_name;

insert触发器

在insert语句执行之前或之后执行，可以引用一个NEW虚拟表，访问被插入的数据

create trigger trigger_name after insert on table_name 
for each row select NEW.column, ...;

delete触发器

在delete语句执行之前或之后执行，可以引用一个OLD虚拟表，访问被删除的数据，OLD虚拟表中的数据都是只读，不能更新。

create trigger trigger_name before delete on table_name 
for each row
begin
	insert into archive_table(column1, column2, ...) 
	values(OLD.val1, OLD.val2, ...);
end;

update触发器

在update语句执行之前或之后执行，可以引用一个OLD虚拟表，访问update之前的数据，OLD虚拟表中的数据都是只读，不能更新。可以引用一个NEW虚拟表，访问update之后的数据。

create trigger trigger_name before update on table_name 
for each row set NEW.column = upper(NEW.column);

12 事务处理

事物是必须完整执行的MySQL语句块，其中的语句要么都成功执行，要么都不执行。

start transaction;

rollback;

// 设置保存点
savepoint sp1;

// 回退至保存点
rollback to sp1;

commit;

13 字符集和校对顺序

字符集和校对顺序可以在服务器、数据库、表、列等各个级别进行设置。

// 查看支持的字符集
show character set;

// 查看支持的校对顺序，_cs区分大小写，_ci不区分大小写
show collation;

// 查看默认字符集
show variables like 'character%';

// 查看默认校对顺序
show variables like 'collation%';

14 安全与权限管理

// 查看用户账号信息
use mysql;
select user from user;
select host, user from user;

// 创建用户
create user soloman identified by 'password';

// 修改密码
set password for soloman = password('my_pwd');

// 重命名用户
rename user soloman to solomon;

// 删除用户
drop user solomon;

// 查看用户权限
show grants for root;

// 分配权限，授予用户soloman在db_name数据库的所有表的读写权限。
grant select, insert on db_name.* to soloman;

// 撤销权限，撤销用户soloman在db_name数据库的所有表的读权限。
revoke select on db_name.* from soloman;

15 数据库维护

// 检查表键正确性
analyze table table_name;

// 检查表
check table table_name1, table_name2;