MySQL Crash Course

发布于 2017-06-18 · 本文总共 27122 字 · 阅读大约需要 78 分钟

SQL

数据库基础

数据库database：保存有组织的数据的容器

表table：某种特定类型数据的结构化清单；

模式schema：关于数据库和表的布局特性的信息；

列column：表中的一个字段，所有表都由一个或者多个列组成；

数据类型datatype：所容许的数据的类型，每个表列都有相应的数据类型，限制该列中存储的数据；

行row：表中的一个记录

主键primary key：能够唯一区分表中每一行的一列（或一组列）

什么是SQL（Structured Query Language）

一种专门用来与数据库通信的语言

优点：

1.SQL不是一种专利语言；

2.简单易学

3.强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作；

MySQL简介

DBMS（Database Management Software）–数据库管理软件

MySQL得到广泛使用：

1.成本–MySQL是开放源码，可以免费使用；

2.性能–MySQL执行很快；

3.可信赖–某些非常重要和声望很高的公司、站点使用MySQL；

4.简单–MySQL容易安装和使用；

MySQL工具

mysql命令行实用工具

mysql>
命令用;或\g结束；
帮助信息：help或者\h
退出命令：quit、exit

MySQL Administor

http://dev.mysql.com/downloads/

MySQL Query Browser

图形交互客户机，用来编写和执行MySQL命令；

http://dev.mysql.com/downloads/

使用MySQL

连接

mysql -uroot -p -h 127.0.0.1

选择数据库

use database；

查看数据库和表

show databases;

mysql> show databases;
+-----------------------+
| Database              |
+-----------------------+
| blog                  |
| blog_100              |
| information_schema    |
| mysql                 |
| performance_schema    |
+-----------------------+
5 rows in set (0.00 sec)

show tables;

mysql> show tables;
+--------------------------+
| Tables_in_qwq_test    |
+--------------------------+
| t                        |
| t1                       |
| t2                       |
+--------------------------+
3 rows in set (0.00 sec)

show columns from t1; 或者 desc t1;

mysql> show columns from t1;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name             | varchar(64)  | NO   | UNI |         |                |
| alias            | varchar(64)  | NO   |     |         |                |
| description      | varchar(256) | YES  |     | NULL    |                |
| created_at       | datetime     | NO   |     | NULL    |                |
| updated_at       | datetime     | YES  |     | NULL    |                |
| creator          | varchar(32)  | NO   |     |         |                |
| modifier         | varchar(32)  | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
11 rows in set (0.01 sec)

show create database qwq_test; 查看创建特定数据库的语句

mysql> show create database qwq_test;
+----------+-------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                 |
+----------+-------------------------------------------------------------------------------------------------+
| qwq_test | CREATE DATABASE `qwq_test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ |
+----------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

show create table t1; 查看创建特定数据表的语句

show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

show errors;

show warnings; 显示服务器错误或警告消息

mysql> show errors;
Empty set (0.00 sec)

mysql> 
mysql> show warnings;
Empty set (0.00 sec)

show grants; 查看授予用户的安全权限

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                  |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

show status; 查看服务器状态信息；

help show

mysql> help show
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:
    LIKE 'pattern'
  | WHERE expr

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/8.0/en/extended-show.html.

URL: http://dev.mysql.com/doc/refman/8.0/en/show.html

检索数据

select语句

检索单个列

select name from project;

检索多个列

select id,name from project;

检索所有列

select * from project;

检索不同的行

select distinct name from project;

限制结果

select * from project limit 10;

select * from project limit 10, 5; 第一个数为开始位置，第二个数为要检索的行；

limit 4 offset 3 == limit 3，4从行3开始取4行；

使用完全限定的表名

select qwq_test.project.id from qwq_test.project;

排序检索数据

排序数据

select * from project order by name;

按多个列排序

select * from project order by name, id;

指定排序方向

默认升序

select * from project irder by name desc;

过滤数据

where子句

检查单个值

select * from project where name=”test”;

不匹配检查

select * from project where name <> “test”;

范围查询

select * from project where id between 5 and 10;

空值检查

select * from project where name is null;

null与不匹配：

null具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或者不匹配过滤时不返回null的行；

OR操作符

select * from project where id=1 or id=2;

AND操作符

select * from project where id=1 and id=2;

IN操作符

select * from project where id in (1,2);

NOT操作符

select * from project where id not in (1,2);

使用通配符进行过滤

like操作符

使用%

select * from project where name like “%qwq”;

使用_

select * from project where name like “_qwq”;

mysql> select id, name  from project where name like "_est_project";
+----------+--------------+
| id       | name         |
+----------+--------------+
| 10000000 | test_project |
+----------+--------------+
1 row in set (0.00 sec)

注意

使用通配符搜索的处理一般要比其他搜索花费更长的时间

1.不要过度使用通配符

2.最好不要放在开头，不能利用索引，会很慢

使用正则表达式搜索

基本字符匹配

select * from project where name REGEXP ‘test’;

mysql> select name, id from project where name REGEXP 'test';
+---------------------+----------+
| name                | id       |
+---------------------+----------+
| public_autotest_new | 10000001 |
| test_project        | 10000000 |
+---------------------+----------+
2 rows in set (0.00 sec)

OR匹配

select * from project where name REGEXP ‘test｜qwq’;

匹配几个字符之一

select * from project where name REGEXP ‘[abc]test’;

select * from project where name REGEXP ‘a｜b｜c test’;

匹配范围

select * from project where name REGEXP ‘[1-5]test’;

匹配特殊字符

select * from project where name REGEXP ‘\.test’;

匹配字符类

类	说明
[:alnum:]	任意字母和数字
[:alpha:]	任意字符
[:blank:]	空格和制表
[:cntrl:]	ASCII控制字符
[:digit:]	任意数字
[:graph:]	与print相同，但不包括空格
[:lower:]	任意小写字母
[:print:]	任意可打印字符
[:punct:]	即不在alnum也不在cntrl中的任意字符
[:space:]	包括空格在内的任意空白字符
[:upper:]	任意大写字母
[:xdigit:]	任意十六进制数字

匹配多个实例

select * from project where name REGEXP ‘$[0-9] test?$’;

定位符

^:文本开始 $:文本结束 [[:<:]]:词的开始 [[:>:]]:词的结尾

select * from project where name REGEXP ‘^test’;

创建计算字段

拼接字段

Concat

select Concat(“(“, name, “)”) as new_name from project;

mysql> select Concat("(", name, ")") as new_name from project;
+-----------------------+
| new_name              |
+-----------------------+
| (public_autotest_new) |
| (test_project)        |
+-----------------------+
2 rows in set (0.00 sec)

Trim():去空格

RTrim()：去右空格

LTrim()：去左空格

执行算术计算

select id*id from project;

使用数据处理函数

文本处理函数

函数	说明
Left	返回串左边的字符
Right	返回串左边的字符
Length	返回串左边的字符
Locate	返回串左边的字符
Lower	返回串左边的字符
Upper	返回串左边的字符
LTrim	返回串左边的字符
RTrim	返回串左边的字符
Soundex	返回串左边的字符
SubString	返回串左边的字符

日期和时间处理函数

函数	说明
AddDate

数值处理函数

函数	说明
Abs
Cos
Sin
Tan
Exp
Mod
Pi
Rand
Sqrt

汇总数据

聚集函数aggregate function：运行在组上，计算和返回单个值的函数

函数	说明
AVG	返回某列的平均值
COUNT	行数
MAX	最大值
MIN	最小值
SUM	某列之和

AVG()

COUNT()

count(*) count(column)忽略null值

MAX()

忽略null值

MIN()

忽略null值

SUM()

忽略null值

分组数据

创建分组

mysql> select group_id,count(*) as num_projects from project group by group_id;
+----------+--------------+
| group_id | num_projects |
+----------+--------------+
|        7 |            1 |
+----------+--------------+
1 row in set (0.01 sec)

注意：

1.group by子句必须出现在where子句之后，order by子句之前；

2.如果列中null值，则null将作为一个分组返回，如果列中有多行null值，它们分为一组；

使用rollup：

过滤分组

having过滤分组，where过滤行； having支持所有where操作符；

分组排序

mysql> select group_id,count(*) as num_projects from project group by group_id having group_id > 0 order by num_projects;
+----------+--------------+
| group_id | num_projects |
+----------+--------------+
|        7 |            1 |
|        1 |            2 |
+----------+--------------+
2 rows in set (0.00 sec)

使用子查询

利用子查询进行过滤

作为计算字段使用子查询

联结表

where子句

笛卡尔积：由没有联结条件的表关系返回的结果为笛卡尔积；检索出的行的数目将是第一个表中的行数乘以第二个表中的行数；

mysql> select project_id, group_id from env, project;
+------------+----------+
| project_id | group_id |
+------------+----------+
|   10000000 |    10000 |
|   10000000 |    10001 |
|   10000000 |    10002 |
|   10000000 |    10000 |
|   10000000 |    10001 |
|   10000000 |    10002 |
|   10000000 |    10000 |
|   10000000 |    10001 |
|   10000000 |    10002 |
|   10000000 |    10000 |
|   10000000 |    10001 |
|   10000000 |    10002 |
+------------+----------+
12 rows in set (0.00 sec)

内部联结

mysql> select project_id, group_id from env inner join  project on env.project_id=project.id;
+------------+----------+
| project_id | group_id |
+------------+----------+
|   10000000 |    10000 |
|   10000000 |    10000 |
|   10000000 |    10000 |
|   10000000 |    10000 |
+------------+----------+
4 rows in set (0.01 sec)

联结多个表

mysql> select project_id, group_id from env,project where  env.project_id=project.id;
+------------+----------+
| project_id | group_id |
+------------+----------+
|   10000000 |    10000 |
|   10000000 |    10000 |
|   10000000 |    10000 |
|   10000000 |    10000 |
+------------+----------+
4 rows in set (0.00 sec)

创建高级联结

outer join

组合查询

创建组合查询

select name, id from project where group_id=10001 union select name, id from project where name like “%qwq%”;

mysql> select name, id from project where group_id=10001 union select name, id from project where name like "%qwq%";
+----------+----------+
| name     | id       |
+----------+----------+
| test     | 10000001 |
| test_qwq | 10000002 |
+----------+----------+
2 rows in set (0.00 sec)

union:

1.union必须由两条或者两条以上的select语句组成，语句之间用union分隔；

2.union中的每个查询必须包含相同的列、表达式或聚集函数，次序不一定相同；

3.列数据类型必须兼容；

union all：包含重复的行

mysql> select name, id from project where group_id=10001 union select name, id from project where name like "%test%";
+------------------------+----------+
| name                   | id       |
+------------------------+----------+
| test                   | 10000001 |
| public_autotest_new123 | 10000000 |
| test_qwq               | 10000002 |
+------------------------+----------+
3 rows in set (0.00 sec)

mysql> select name, id from project where group_id=10001 union all select name, id from project where name like "%test%";
+------------------------+----------+
| name                   | id       |
+------------------------+----------+
| test                   | 10000001 |
| public_autotest_new123 | 10000000 |
| test                   | 10000001 |
| test_qwq               | 10000002 |
+------------------------+----------+
4 rows in set (0.00 sec)

全文搜索

MyISAM支持全文索引，InnoDB不支持；

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断的重新索引；在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引；

在索引之后，select可与match()和against()一起使用以实际执行搜索；

插入数据

插入完整的行

insert into project values(x,xxx,xxxx);

insert into project(id, name) values(1001, “test”);

插入多行

insert into project(id, name) values(1001, “test”),(1002, “test1”);

插入检索出的数据

insert into project(id, name) select id, name from project;

mysql> insert into project(id, name,created_at) select id+3, Concat(name, "qwq"), created_at from project;
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select id, name from project;
+----------+---------------------------+
| id       | name                      |
+----------+---------------------------+
| 10000000 | public_autotest_new123    |
| 10000003 | public_autotest_new123qwq |
| 10000001 | test                      |
| 10000004 | testqwq                   |
| 10000002 | test_qwq                  |
| 10000005 | test_qwqqwq               |
+----------+---------------------------+
6 rows in set (0.00 sec)

更新和删除数据

更新

update project set name=”test_update” where id=10000004;

mysql> update project set name="test_update" where id=10000004;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

删除

delete from project where id=10000004;

mysql> delete from project  where id=10000004;
Query OK, 1 row affected (0.06 sec)

如果要删除所有行，使用truncate table比较快，truncate会删除原来的表重新创建一个；

原则

1.更新和删除一定要带where

2.保证每个表都有主键；

3.先select确认再删除；

4.使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据行；

创建和操纵表

创建表

create table project ( id int not null auto_increment, name char(64) not null, description char(64) null, item_price decimal(8,2) not null, primary key (id) ) engine=InnoDB;

更改表

添加列：

alter table project add group_id int(11);

删除列：

alter table project drop column group_i_d;

删除表

drop table project;

重命名表

rename table project to project1;

使用视图

视图

视图是虚拟的表；与包含数据的表不一样，视图只包含使用时动态检索数据的查询；

为什么使用视图

1.重用SQL语句；

2.简化复杂的SQL操作；编写查询之后，可以方便的重用它而不必知道它的基本查询细节；

3.使用表的组成部分而不是整个表；

4.保护数据；

5.更改数据格式和表示；视图可返回与底层表的表示和格式不同的数据；

视图的规则和限制

1.视图必须唯一命名；

2.可以创建的视图数目没有限制

3.创建视图需要足够访问权限；

4.视图可以嵌套；可以利用从其他视图中检索数据的查询来构造一个视图；

5.order by可以用在视图中；

6.视图不能索引，也不能有关联的触发器或默认值；

7.视图可以和表一起使用；

使用视图

1.创建视图：create view

2.查看创建视图的语句：show create view view_name；

3.删除视图：drop view view_name;

4.更新视图：先drop再create；或者用create or replace view …

mysql> create view project_view as select name, id from project where name is not null;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from project_view;
+---------------------------+----------+
| name                      | id       |
+---------------------------+----------+
| public_autotest_new123    | 10000000 |
| public_autotest_new123qwq | 10000003 |
| test                      | 10000001 |
| test_qwq                  | 10000002 |
| test_qwqqwq               | 10000005 |
+---------------------------+----------+
5 rows in set (0.01 sec)

更新视图

通常，视图是可以更新的；更新一个视图将更新其基表；如果对视图增加或者删除行，实际上是对其基表增加或删除行；

并非所有视图都是可以更新的；如果MySQL不能正确的确定被更新的基数据，则不允许更新：如果视图定义中有以下操作，则不能更新： 1.分组；group by或者having；

2.联结；

3.子查询；

4.并；

5.聚集函数(Min(),Count(),Sum()等)；

6.distinct；

7.导出列；

使用存储过程

简单、安全、高性能；

通过把处理封装在容易使用的单元中，简化复杂的操作；

不需要反复建立一系列处理步骤，保证了数据的完整性；防止错误；需要执行的步骤越多，出错的可能性越大；

简化对变动的管理；如果表名、列名或者业务逻辑有变化，只需要更改存储过程的代码；

提高安全性；

提高性能；

编写功能更强更灵活的代码；

创建存储过程

delimiter // create procedure select_project() begin select * from project; end //

delimiter;

call select_project();

mysql> call select_project();
+----+----------+------+------+
| id | name     | c1   | time |
+----+----------+------+------+
|  1 | qwq      |    1 | NULL |
| 55 | TEST     |   10 | NULL |
+----+----------+------+------+
9 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

删除存储过程

drop procedure select_project;

使用参数

delimiter // create procedure select_max_count_project1( IN name_p char(64), OUT max_c decimal(8,2) ) begin select max(c1) from project where name=name_p into max_c; end //

delimiter ;

call select_max_count_project(@max_c); select @max_c;

mysql> delimiter //
mysql> create procedure select_max_count_project1(
    ->   IN name_p char(64),
    ->   OUT max_c decimal(8,2)
    -> )
    -> begin 
    ->   select max(c1) from project where name=name_p into max_c;
    -> end //
Query OK, 0 rows affected (0.07 sec)
mysql> delimiter ;
mysql> end //
mysql> call select_max_count_project1("test", @max_c1);
Query OK, 1 row affected (0.00 sec)
mysql> select @max_c1;
+---------+
| @max_c1 |
+---------+
|   10.00 |
+---------+
1 row in set (0.00 sec)

检查存储过程

show create procedure select_max_count_project;

show procedure status;

使用游标

有时需要在检索出来的行中前进或后退一行或多行，这就是使用游标的原因；

游标(cursor)，是一个存储在MySQL服务器上的数据库查询，游标不是一条SELECT语句，而是被该语句检索出来的结果集；

可以看做是指向查询结果集的指针；在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据；

通过cursor，可以一次一行的从结果集中把行拿出来处理。

注意：MySQL游标只能用于存储过程和函数。

在使用游标前，必须先声明它；这个过程实际上没有检索数据，只是定义要使用的SELECT语句；

一旦声明后，必须打开游标以供使用；

对于填有数据的游标，根据需要取出各行；

在结束游标使用时，必须关闭游标；

创建游标

游标的处理过程：

声明游标declare：没有检索数据，只是定义要使用的select语句； DECLARE cursor_name CURSOR FOR select_statement;

打开游标open：打开游标以供使用，用上一步定义的select语句把数据实际检索出来； OPEN cursor_name;

检索游标fetch：对于填有数据的游标，根据需要取出(检索)各行； FETCH cursor_name INTO var_name [, var_name] …

关闭游标close：在结束游标使用时，必须关闭游标； CLOSE cursor_name;

create procedure p_name() begin declare done boolean default 0; declare o int;

declare project_c cursor for select id from project;

declare continue handler for sqlstate ‘02000’ set done=1;

create table if not exists tmp_table(id int);

open project_c;

repeat fetch project_c into o; insert into tmp_table(id) values(o);

until done end repeat;

close project_c; end;

mysql> delimiter $$
mysql> create procedure p_name()
    -> begin
    ->   declare done boolean default 0;
    ->   declare o int;
    -> 
    ->   declare project_c cursor for select id from project;
    -> 
    ->   declare continue handler for sqlstate '02000' set done=1;
    -> 
    ->   create table if not exists tmp_table(id int);
    -> 
    ->   open project_c;
    -> 
    ->   repeat
    ->     fetch project_c into o;
    ->     insert into tmp_table(id) values(o);
    -> 
    ->   until done end repeat;
    -> 
    ->   close project_c;
    -> end$$
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter ;
mysql> call p_name();
Query OK, 1 row affected (0.07 sec)
mysql> select * from tmp_table;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|   50 |
|   51 |
|   53 |
|   54 |
|   55 |
|   55 |
+------+
10 rows in set (0.00 sec)

使用触发器

触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句：
DELETE；
INSERT；
UPDATE；

创建触发器

create trigger new_project after insert on project for each row insert time VALUES(NOW());

mysql> insert into project(id, name, c1) values(50, 'qwq_test',2);
Query OK, 1 row affected (0.09 sec)
mysql> insert into project(id, name, c1) values(51, 'qwq_test',2);
Query OK, 1 row affected (0.03 sec)
mysql> select * from time;
+---------------------+
| time                |
+---------------------+
| 2020-07-11 20:52:45 |
| 2020-07-11 20:53:08 |
+---------------------+
2 rows in set (0.00 sec)
mysql> create table time (time datetime);
Query OK, 0 rows affected (0.09 sec)

删除触发器

drop trigger new_project;

mysql> SHOW TRIGGERS\G;
*************************** 1. row ***************************
             Trigger: new_project
               Event: INSERT
               Table: project
           Statement: INSERT INTO time VALUES(NOW())
              Timing: AFTER
             Created: 2020-07-11 20:45:28.92
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> drop trigger new_project;
Query OK, 0 rows affected (0.07 sec)

使用触发器

insert触发器

create trigger project_new after insert on project for each row select New.id;

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
       FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.01 sec)

mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';

delete触发器

delete触发器中，可以引用OLD虚拟表，访问被删除的行； delimiter |

create trigger delete_project before delete on project for each row begin insert into archive_project(name, id) values(OLD.name, OLD.id); end; |

delimiter ;

mysql一遇到分号，它就要自动执行。有时候，不希望MySQL这么做。可能输入较多的语句，且语句中包含有分号。需要事先把delimiter换成其它符号，如//或$$。

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  b4 INT DEFAULT 0
);

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW
  BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

delimiter ;

INSERT INTO test3 (a3) VALUES
  (NULL), (NULL), (NULL), (NULL), (NULL),
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

update触发器

create trigger update_project before update on project for each row set New.state=Upper(New.state);

mysql> create trigger update_project before update on project for each row set New.name=Upper(New.name);
Query OK, 0 rows affected (0.08 sec)

mysql> update project set name="test" where id=55;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from project;
+----+----------+------+------+
| id | name     | c1   | time |
+----+----------+------+------+
|  1 | qwq      |    1 | NULL |
| 55 | TEST     |   10 | NULL |
+----+----------+------+------+
9 rows in set (0.00 sec)

参考:https://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html

事务处理

事务(transaction)：一组SQL语句

回退(rollback)：撤销指定SQL语句的过程；

提交(commit)：将未存储的SQL语句结果写入数据库

保留点(savepoint)：事务处理中设置的临时占位符，可以对它发布回退

控制事务处理

标识事务的开始：start transaction

使用rollback

start transaction；

delete from resources where project_id=10001;

delete from project where id=10001；

rollback;

使用commit

start transaction；

delete from resources where project_id=10001;

delete from project where id=10001；

commit;

使用保留点

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符；这样，如果需要回退，可以回退到某个占位符；

savepoint delete1;

rollback to delete1;

释放保留点：release savepoint;

更改默认的提交行为

默认的MySQL行为是自动提交所有更改；即更改立即失效；

指示MySQL不自动提交更改：

set autocommit=0;

全球化和本地化

字符集和校对顺序

字符集：字母和符号的集合
编码：某个字符集成员的内部表示
校对：规定字符如何比较的指令

使用字符集和校对顺序

查看支持的字符集完整列表：show character set;

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.04 sec)

查看所支持校对的完整列表：show collation;

 show collation;
+----------------------------+----------+-----+---------+----------+---------+---------------+
| Collation                  | Charset  | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+----------+-----+---------+----------+---------+---------------+
| armscii8_bin               | armscii8 |  64 |         | Yes      |       1 | PAD SPACE     |
| armscii8_general_ci        | armscii8 |  32 | Yes     | Yes      |       1 | PAD SPACE     |
| latin7_general_ci          | latin7   |  41 | Yes     | Yes      |       1 | PAD SPACE     |
| latin7_general_cs          | latin7   |  42 |         | Yes      |       1 | PAD SPACE     |
| utf8_vietnamese_ci         | utf8     | 215 |         | Yes      |       8 | PAD SPACE     |
+----------------------------+----------+-----+---------+----------+---------+---------------+
270 rows in set (0.02 sec)

确定所用的字符集和校对：

show variables like ‘character%’;

show variables like ‘collation%’;

mysql> show variables like 'character%';
+--------------------------+-----------------------------------------------------------+
| Variable_name            | Value                                                     |
+--------------------------+-----------------------------------------------------------+
| character_set_client     | utf8mb4                                                   |
| character_set_connection | utf8mb4                                                   |
| character_set_database   | utf8                                                      |
| character_set_filesystem | binary                                                    |
| character_set_results    | utf8mb4                                                   |
| character_set_server     | utf8mb4                                                   |
| character_set_system     | utf8                                                      |
| character_sets_dir       | /usr/local/mysql-8.0.11-macos10.13-x86_64/share/charsets/ |
+--------------------------+-----------------------------------------------------------+
8 rows in set (0.03 sec)

mysql> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8_general_ci    |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

给表指定字符集和校对：

create table mytable ( column1 INT, column2 VARCHAR(10) )default character set hebrew collate hebrew_general_ci;

对每个列设置设置字符集和校对：

create table mytable ( column1 INT, column2 VARCHAR(10) character set latin1 collate latin1_general_ci )default character set hebrew collate hebrew_general_ci;

order by时指定校对：

select * from project order by name collate latin1_general_cs;

_cs:区分大小写

_ci:不区分大小写

安全管理

访问控制

MySQL服务器的安全基础是：用户对他们需要的数据具有适当的访问权，既不能多也不能少；

多数用户只需对表进行读和写，少数用户能创建和删除表；
某些用户需要读表，但可能不需要更新表；
允许某些用户添加数据，但不允许删除数据；
管理员可能需要处理用户账号的权限，但多数用户不需要；
允许某些用户通过存储过程访问数据，但不允许直接访问数据
根据用户登录地点限制对某些功能的访问；

管理用户

MySQL用户账号和信息存储在名为mysql的数据库中；

use mysql; select user from user;

mysql> select user from user;
+------------------+
| user             |
+------------------+
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
4 rows in set (0.00 sec)

创建用户账号

create user qwq identified by ‘password’;

mysql> create user qwq identified by 'qwq';
Query OK, 0 rows affected (0.03 sec)

用户账号重命名：

rename user qwq to qwq_new;

mysql> rename user test to qwq1;
Query OK, 0 rows affected (0.03 sec)

删除用户

drop user test;

设置访问权限

show grants for qwq;

grant select on qwq_table.* to qwq;

mysql> grant select on qwq_test.* to qwq;
Query OK, 0 rows affected (0.08 sec)

show grants

mysql> show grants for qwq;
+-------------------------------------------+
| Grants for qwq@%                          |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `qwq`@`%`           |
| GRANT SELECT ON `qwq_test`.* TO `qwq`@`%` |
+-------------------------------------------+
2 rows in set (0.01 sec)

grant的反操作是revoke：

revoke select on qwq_table.* from qwq;

mysql> revoke select on qwq_test.* from qwq;
Query OK, 0 rows affected (0.07 sec)

mysql> show grants for qwq;
+---------------------------------+
| Grants for qwq@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `qwq`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

grant和revoke可以在几个层次上控制访问权限：

整个服务器：grant all和revoke all；
整个数据库：on database.*;
特定的表：on database.table;
特定的列；
特定的存储过程；

更改密码

set password for qwq@’%’ = ‘123456’;

SET PASSWORD = ‘auth_string’;

mysql> set password for qwq@'%' = '123456';
Query OK, 0 rows affected (0.03 sec)

SET PASSWORD FOR ‘jeffrey’@’localhost’ = ‘auth_string’;

With no FOR user clause, the statement sets the password for the current user:
SET PASSWORD = ‘auth_string’;

Note Rather than using SET PASSWORD to assign passwords, ALTER USER is the preferred statement for account alterations, including assigning passwords. For example:

ALTER USER user IDENTIFIED BY ‘auth_string’;

参考：https://dev.mysql.com/doc/refman/8.0/en/set-password.html

数据库维护

进行数据库维护

检查表键是否正确：analyze table

mysql> analyze table project;
+------------------+---------+----------+----------+
| Table            | Op      | Msg_type | Msg_text |
+------------------+---------+----------+----------+
| qwq_test.project | analyze | status   | OK       |
+------------------+---------+----------+----------+
1 row in set (0.03 sec)

检查表：check table

mysql> check table project;
+------------------+-------+----------+----------+
| Table            | Op    | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| qwq_test.project | check | status   | OK       |
+------------------+-------+----------+----------+
1 row in set (0.00 sec)

如果MyISAM表访问产生不正确和不一致的结果；修复相应的表：repair table

如果从一个表中删除大量数据，可以使用optimize table来收回所用的空间，从而优化表的性能；

诊断启动问题

几个重要的mysqld命令行选项：

–help
–safe-mode装载减去某些最佳配置的服务器；
–verbose显示全文本消息
–version显示版本信息

查看日志文件

错误日志
查询日志
binlog
慢查询日志

性能优化

MySQL具有特定的硬件建议
一般来说，关键的生产DBMS应该运行在专用服务器上
可能需要调整内存分配、缓冲区大小等配置；查看配置show variables；show status；
MySQL是一个多用户线程的DBMS；show processlist；
编写同一条select语句总是有多种方法；应该试验联结、并、子查询等，找出最佳方法；
使用explain；
一般来说，存储过程执行比一条一条执行其中的各条语句要快；
使用正确的数据类型；
不要使用select * …
有的操作支持delayed关键字，如果使用它，将把控制立即返回给调用程序；
导入数据时，应该关闭自动提交；删除索引然后在导入数据完成后再重建；
索引数据库表以改善数据检索性能；
通过union代替or语句；
like很慢，一般来说，最好使用fulltext；
数据库是不断变化的实体；

refs

《MySQL Crash Course》Ben Forta https://dev.mysql.com/doc/refman/8.0/en