第二十三章 MySQL数据库 手册4 视图、函数、存储过程、流程控制、触发器、用户和权限管理
实验四：视图、函数、存储过程、流程控制、触发器、⽤户和权限管理
⽬的
视图、函数、存储过程、流程控制、触发器、用户和权限管理，管理数据库。
前提
linux系统，已安装好数据库。
安装mariadb
7 ~]# vim /etc/yum.repos.d/mariadb-10.2.repo
# MariaDB 10.2 CentOS repository list - created 2022-05-14 05:44 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.2/centos7-amd64
gpgkey=https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
7 ~]# yum install -y mariadb-server
7 ~]# systemctl start mariadb
#查看端口
7 ~]# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
...
LISTEN 0 80 :::3306 :::*
...
安全加固，root⼝令为空。
设置数据库管理员root口令
禁止root远程登录
删除anonymous用户帐号
删除test数据库
7 ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): #回车
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] n
... skipping.
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
空⼝令，登录。
7 ~]# mysql -uroot -p
Enter password: #回车即可
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.2.43-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> exit
Bye
准备测试数据库：
下载：hellodb_innodb.sql
从windows导⼊centos：
7 ~]# rz
#查看hellodb_innodb.sql是否导入
7 ~]# [root@centos7 ~]# ls
anaconda-ks.cfg Documents hellodb_innodb.sql Music Public Videos
Desktop Downloads initial-setup-ks.cfg Pictures Templates
导⼊数据库：
7 ~]# mysql < hellodb_innodb.sql
7 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.2.43-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
测试数据库：hellodb
MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
测试表：student
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
命令介绍
1. 视图
视图：VIEW,虚表，保存有实表的查询结果
创建方法：
CREATE VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL]
CHECK OPTION]
查看视图定义：SHOW CREATE VIEW view_name
删除视图：
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；其修改操作受基表限制
【例1】建⽴视图，虚拟的表，不真实存在。内存不占，可以隐藏敏感信息。
MariaDB [hellodb]> create view view_students as select stuid,name from students;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| employee |
| scores |
| students |
| teachers |
| toc |
| users |
| view_students |
+-------------------+
10 rows in set (0.00 sec)
MariaDB [hellodb]> desc view_students;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| stuid | int(10) unsigned | NO | | 0 | |
| name | varchar(50) | NO | | NULL | |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
判断是视图还是表：
MariaDB [hellodb]> show table status like 'view_students' \G
*************************** 1. row ***************************
Name: view_students
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW #视图！
1 row in set (0.00 sec)
MariaDB [hellodb]> show table status like 'students'\G #真表！
*************************** 1. row ***************************
Name: students
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 25
Avg_row_length: 655
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 26
Create_time: 2022-05-18 14:39:20
Update_time: 2022-05-18 14:39:20
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
【例2】删除视图。
MariaDB [hellodb]> drop view view_students;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
2. 函数和存储过程
2.1、系统函数和⾃定义函数5.7【5.7为版本、可改】系统函数官方文档:https://dev.mysql.com/doc/refman/5.7/en
自定义函数 (user-defined function UDF)
保存在mysql.proc表中
创建UDF
CREATE [AGGREGATE] FUNCTION function_name(parameter_name
type,[parameter_name type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body
说明：
参数可以有多个,也可以没有参数
必须有且只有一个返回值
创建函数:示例：无参UDF
CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello
World!”;
查看函数列表：
SHOW FUNCTION STATUS;
查看函数定义
SHOW CREATE FUNCTION function_name
删除UDF:
DROP FUNCTION function_name
调用自定义函数语法:
SELECT function_name(parameter_value,...)
自定义函数中定义局部变量语法
DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]
说明：局部变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一行定义
为变量赋值语法
SET parameter_name = value[,parameter_name = value...]
SELECT INTO parameter_name
【例3】⾃定义函数
MariaDB [hellodb]> create function simplefun() returns varchar(20) return "hello world";
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show function status \G
*************************** 1. row ***************************
Db: hellodb
Name: simplefun
Type: FUNCTION
Definer: root@localhost
Modified: 2022-05-19 02:19:05
Created: 2022-05-19 02:19:05
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
测试⾃定义函数
MariaDB [hellodb]> select simplefun();
+-------------+
| simplefun() |
+-------------+
| hello world |
+-------------+
1 row in set (0.00 sec)
【例4】使⽤，⾃定义函数
MariaDB [hellodb]> DELIMITER //
MariaDB [hellodb]> CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS
-> VARCHAR(20)
-> BEGIN
-> DELETE FROM students WHERE stuid = uid;
-> RETURN (SELECT COUNT(stuid) FROM students);
-> END//
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> DELIMITER ;
删除第10个stuid
MariaDB [hellodb]> select deletebyid(10); #删除第10条记录
+----------------+
| deletebyid(10) |
+----------------+
| 24 |
+----------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from students; #发现第10条被删除了
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
24 rows in set (0.00 sec)
【例5】使⽤，⾃定义函数
定义函数：
MariaDB [hellodb]> set @var=100;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> select @var;
+------+
| @var |
+------+
| 100 |
+------+
1 row in set (0.00 sec)
学⽣年龄的平均值：
MariaDB [hellodb]> select avg(age) from students;
+----------+
| avg(age) |
+----------+
| 27.7500 |
+----------+
1 row in set (0.00 sec)
定义函数：
MariaDB [hellodb]> select avg(age) from students into @avg_age;
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select @avg_age;
+--------------+
| @avg_age |
+--------------+
| 27.750000000 |
+--------------+
1 row in set (0.00 sec)
使⽤函数：
MariaDB [hellodb]> update students set age=age+10 where age > @avg_age;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 63 | M | 2 | 16 |
| 4 | Ding Dian | 42 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 56 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 43 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 110 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
24 rows in set (0.00 sec)
【例6】局部变量。（⼀般不⽤）
MariaDB [hellodb]> DELIMITER //
MariaDB [hellodb]> CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT
-> UNSIGNED)
-> RETURNS SMALLINT
-> BEGIN
-> DECLARE a, b SMALLINT UNSIGNED;
-> SET a = x, b = y;
-> RETURN a+b;
-> END//
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> DELIMITER ;
使⽤函数：
MariaDB [hellodb]> select addtwonumber(10,20);
+---------------------+
| addtwonumber(10,20) |
+---------------------+
| 30 |
+---------------------+
1 row in set (0.00 sec)
2.2、存储过程
存储过程优势
- 存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用, 省去了编译的过程
- 提高了运行速度
- 同时降低网络数据传输量
存储过程与自定义函数的区别
- 存储过程实现的过程要复杂一些,而函数的针对性较强
- 存储过程可以有多个返回值,而自定义函数只有一个返回值
- 存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用
存储过程：存储过程保存在mysql.proc表中
创建存储过程
- CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter …]]) routime_body
- proc_parameter : [IN|OUT|INOUT] parameter_name type
- 其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；
- param_name表示参数名称；type表示参数的类型
查看存储过程列表 SHOW PROCEDURE STATUS; 查看存储过程定义 SHOW CREATE PROCEDURE sp_name
调用存储过程
- CALL sp_name ([ proc_parameter [,proc_parameter …]])
- CALL sp_name
- 说明:当无参时,可以省略"()“,当有参数时,不可省略”()”
存储过程修改
- ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改
存储过程体,所以要修改存储过程,方法就是删除重建
删除存储过程
- DROP PROCEDURE [IF EXISTS] sp_name
【例7】存储过程
MariaDB [hellodb]> select now();
+---------------------+
| now() |
+---------------------+
| 2022-05-22 01:41:38 |
+---------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show procedure status;
+-------+--------------------+-----------+----------------+---------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created nnection | Database Collation |
+-------+--------------------+-----------+----------------+---------------------+----------------------+--------------------+
| mysql | AddGeometryColumn | PROCEDURE | root@localhost | 2022-05-22 00:18:19 | 2022-05-22 0_ci | latin1_swedish_ci |
| mysql | DropGeometryColumn | PROCEDURE | root@localhost | 2022-05-22 00:18:19 | 2022-05-22 0_ci | latin1_swedish_ci |
+-------+--------------------+-----------+----------------+---------------------+----------------------+--------------------+
2 rows in set (0.01 sec)
定义存储过程:
MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> CREATE PROCEDURE showTime()
-> BEGIN
-> SELECT now();
-> END//
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> delimiter ;
调⽤存储过程：
MariaDB [hellodb]> CALL showTime;
+---------------------+
| now() |
+---------------------+
| 2022-05-22 01:51:49 |
+---------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
【例8】存储过程，查询id对应的学⽣。
MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED)
-> BEGIN
-> SELECT * FROM students WHERE stuid = uid;
-> END//
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> call selectById(2);
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
+-------+------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> call selectById(20);
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
| 20 | Diao Chan | 19 | F | 7 | NULL |
+-------+-----------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
删除存储过程：
MariaDB [hellodb]> drop procedure selectbyid;
Query OK, 0 rows affected (0.00 sec)
【例9】存储过程，循环语句，1+…+100=?。
MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> CREATE PROCEDURE dorepeat(n INT)
-> BEGIN
-> SET @i = 0;
-> SET @sum = 0;
-> REPEAT SET @sum = @sum+@i; SET @i = @i +1;
-> UNTIL @i > n END REPEAT;
-> END//
Query OK, 0 rows affected (0.15 sec)
MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> CALL dorepeat(100);
Query OK, 0 rows affected (0.07 sec)
MariaDB [hellodb]> SELECT @sum;
+------+
| @sum |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)
【例10】包含IN参数和OUT参数
MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED, OUT num
-> SMALLINT UNSIGNED)
-> BEGIN
-> DELETE FROM students WHERE stuid >= uid;
-> SELECT row_count() into num;
-> END//
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> call deleteById(2,@Line);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> SELECT @Line;
+-------+
| @Line |
+-------+
| 23 |
+-------+
1 row in set (0.00 sec)
stuid为2以前的全删了：
MariaDB [hellodb]> select * from students;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
+-------+-------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
2.3、流程控制（可能会⽤）
存储过程和函数中可以使用流程控制来控制语句的执行
流程控制：
IF：用来进行条件判断。根据是否满足条件，执行不同语句
CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断
LOOP：重复执行特定的语句，实现一个简单的循环
LEAVE：用于跳出循环控制
ITERATE：跳出本次循环，然后直接进入下一次循环
REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句
WHILE：有条件控制的循环语句
3、触发器（不推荐⽤）
触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行
创建触发器
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
说明：
trigger_name：触发器的名称
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件
tbl_name：该触发器作用在表名
查看触发器
SHOW TRIGGERS
查询系统表information_schema.triggers的方式指定查询条件，查看指定的触发器信息。
mysql> USE information_schema;
Database changed
mysql> SELECT * FROM triggers WHERE
trigger_name='trigger_student_count_insert';
删除触发器
DROP TRIGGER trigger_name;
【例11】定义触发器，学⽣数字⾃动增加或减少。
创建两张表
MariaDB [hellodb]> CREATE TABLE student_info (
-> stu_id INT(11) NOT NULL AUTO_INCREMENT,
-> stu_name VARCHAR(255) DEFAULT NULL,
-> PRIMARY KEY (stu_id)
-> );
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> CREATE TABLE student_count (
-> student_count INT(11) DEFAULT 0
-> );
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> INSERT INTO student_count VALUES(0);
Query OK, 1 row affected (0.00 sec)
两张表都是空的
MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from student_info;
Empty set (0.01 sec)
创建触发器，在向学⽣表INSERT数据时，学⽣数增加，DELETE学⽣时，学⽣数减少
#增加
MariaDB [hellodb]> CREATE TRIGGER trigger_student_count_insert
-> AFTER INSERT
-> ON student_info FOR EACH ROW
-> UPDATE student_count SET student_count=student_count+1;
Query OK, 0 rows affected (0.00 sec)
#减少
MariaDB [hellodb]> CREATE TRIGGER trigger_student_count_delete
-> AFTER DELETE
-> ON student_info FOR EACH ROW
-> UPDATE student_count SET student_count=student_count-1;
Query OK, 0 rows affected (0.00 sec)
添加a记录
MariaDB [hellodb]> insert student_info(stu_name)values('a');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from student_info;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1 | a |
+--------+----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
⼀张表的记录变化，触发了另⼀张表的变化。
MariaDB [hellodb]> insert student_info(stu_name)values('b'),('c');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> select * from student_info;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1 | a |
| 2 | b |
| 3 | c |
+--------+----------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> delete from student_info;
Query OK, 3 rows affected (0.00 sec)
MariaDB [hellodb]> select * from student_info;
Empty set (0.00 sec)
MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
查看触发器
MariaDB [hellodb]> show triggers \G
*************************** 1. row ***************************
Trigger: trigger_student_count_insert
Event: INSERT
Table: student_info
Statement: UPDATE student_count SET student_count=student_count+1
Timing: AFTER
Created: NULL
sql_mode:
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Trigger: trigger_student_count_delete
Event: DELETE
Table: student_info
Statement: UPDATE student_count SET student_count=student_count-1
Timing: AFTER
Created: NULL
sql_mode:
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
2 rows in set (0.00 sec)
删除触发器
MariaDB [hellodb]> drop trigger trigger_student_count_delete;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> drop trigger trigger_student_count_insert;
Query OK, 0 rows affected (0.00 sec)
4、用户和权限管理
元数据数据库：mysql
系统授权表：
db, host, user
columns_priv, tables_priv, procs_priv, proxies_priv
用户账号：
'USERNAME'@'HOST'
@'HOST':
主机名
IP地址或Network
通配符： % _
【例12】系统的默认账户，不安全。
MariaDB [hellodb]> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
| root | 127.0.0.1 | |
| root | ::1 | |
+------+-----------+----------+
3 rows in set (0.00 sec)
4.1、⽤户管理
创建用户：CREATE USER
CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']；
默认权限：USAGE
用户重命名：RENAME USER
RENAME USER old_user_name TO new_user_name;
删除用户：
DROP USER 'USERNAME'@'HOST‘
示例：删除默认的空用户
DROP USER ''@'localhost';
修改密码：
mysql>SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password');
mysql>UPDATE mysql.user SET password=PASSWORD('password')
WHERE clause;
此方法需要执行下面指令才能生效：
mysql> FLUSH PRIVILEGES;
#mysqladmin -u root -poldpass password ‘newpass’
忘记管理员密码的解决办法：
启动mysqld进程时，为其使用如下选项：
--skip-grant-tables --skip-networking
使用UPDATE命令修改管理员密码
关闭mysqld进程，移除上述两个选项，重启mysqld
【例13】添加⽤户，远程登录。
前提条件
主机：192.168.37.7
客户机：192.168.37.6
在主机192.168.37.7上做操作：
创建账户
MariaDB [(none)]> create user wang@'192.168.37.6' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user,host,password from mysql.user;
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | |
| wang | 192.168.37.6 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | |
| root | ::1 | |
+------+--------------+-------------------------------------------+
4 rows in set (0.00 sec)
本机是⽤匿名账户连接的。
[root@centos7 ~]# mysql -uroot -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.2.44-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> exit
Bye
在客户机192.168.37.6上做操作：
6 ~]# yum install mariadb-server -y
6 ~]# service mysqld start
6 ~]# mysql -uwang -p123456 -h192.168.37.7
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.5-10.2.44-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
查看状态：
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 16
Current database:
Current user: wang@192.168.37.6 <--通过王账户连接
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.2.44-MariaDB MariaDB Server
Protocol version: 10
Connection: 192.168.37.7 via TCP/IP <--远程机器IP:192.168.37.7
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 8 min 22 sec
Threads: 7 Questions: 20 Slow queries: 0 Opens: 19 Flush tables: 1 Open tables: 12 Queries per second avg: 0.039
--------------
在主机192.168.37.7上做操作：
删除⽤户（为了后续、先不用删除）
MariaDB [(none)]> drop user wang@'192.168.37.6';
Query OK, 0 rows affected (0.00 sec)
在客户机192.168.37.6
再次连接、发现不允许连接到此MySQL服务器
#不允许连接到此MariaDB服务器
6 ~]# mysql -uwang -p123456 -h192.168.37.7
ERROR 1130 (HY000): Host '192.168.37.6' is not allowed to connect to this MariaDB server
【例14】加密函数
MariaDB [(none)]> select password('123456');
+-------------------------------------------+
| password('123456') |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
【例15】修改密码
原密码：
MariaDB [(none)]> select user,host,password from mysql.user;
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | |
| root | 127.0.0.1 | |
| root | ::1 | |
| wang | 192.168.37.6 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+--------------+-------------------------------------------+
4 rows in set (0.00 sec)
修改密码：
MariaDB [(none)]> set password for wang@'192.168.37.6'=password('magedu');
Query OK, 0 rows affected (0.00 sec)
新密码：
MariaDB [(none)]> select user,host,password from mysql.user;
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | |
| root | 127.0.0.1 | |
| root | ::1 | |
| wang | 192.168.37.6 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
+------+--------------+-------------------------------------------+
4 rows in set (0.00 sec)
就⼝令失效，⽤新⼝令：
客户机：192.168.37.6
6 ~]# mysql -uwang -p123456 -h192.168.37.7
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.5-10.2.44-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
【例16】修改密码，直接该表
MariaDB [(none)]> select user,host,password from mysql.user;
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | |
| root | 127.0.0.1 | |
| root | ::1 | |
| wang | 192.168.37.6 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
+------+--------------+-------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> update mysql.user set password=password('123456') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [(none)]> select user,host,password from mysql.user;
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | ::1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| wang | 192.168.37.6 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
+------+--------------+-------------------------------------------+
4 rows in set (0.00 sec)
改表在新终端不能⽴即⽣效，刷新权限才能⽣效。
新终端：（原主机开一个新的窗口）
[root@CentOS7 ~]# mysql -uroot -p123456
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
原终端刷新权限：
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
新终端：
[root@centos7 ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.2.44-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
【例17】忘记密码，解决⽅案。
第⼀种：删除⽬录，启动时⽣成新的，数据丢了【工作中不推荐、慎用】。
7 ~]# rm -rf /var/lib/mysql/*
第⼆种：改配置⽂件，添加忽略授权表，谁都能连，极度危险。
7 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
7 ~]# cat /etc/my.cnf
[mysqld]
skip-grant-tables #忽略授权表。
skip-networking #禁止网络连接。
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
7 ~]# systemctl restart mariadb
7 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.44-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
旧⼝令
MariaDB [(none)]> select user,host,password from mysql.user;
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| wang | 192.168.37.6 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | ::1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+--------------+-------------------------------------------+
4 rows in set (0.00 sec)
设置新⼝令
MariaDB [(none)]> update mysql.user set password=password('magedu')where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [(none)]> select user,host,password from mysql.user;
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
| wang | 192.168.37.6 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
| root | ::1 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
+------+--------------+-------------------------------------------+
4 rows in set (0.00 sec)
配置⽂件改回去
7 ~]# vim /etc/my.cnf
[mysqld]
#skip-grant-tables
#skip-networking
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
重启服务
7 ~]# systemctl restart mariadb
7 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
新密码登录
7 ~]# mysql -uroot -pmagedu
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.44-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
4.2、⽤户和权限管理
权限类别：
管理类
CREATE TEMPORARY TABLECREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS
程序类
FUNCTION、PROCEDURE、TRIGGER
CREATE
ALTER
DROP
EXCUTE
数据库级别
库和表级别：DATABASE、TABLE
ALTER
CREATE
CREATE VIEW
DROP
INDEX
SHOW VIEW
GRANT OPTION：能将自己获得的权限转赠给其他用户
数据操作
SELECT
INSERT
DELETE
UPDATE
字段级别
SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)
所有权限
ALL PRIVILEGES 或 ALL
5、授权
GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host'
[IDENTIFIED BY 'password'] [WITH GRANT OPTION];
priv_type: ALL [PRIVILEGES]
object_type:TABLE | FUNCTION | PROCEDURE
priv_level: *(所有库) | *.* | db_name.* | db_name.tbl_name | tbl_name(当前库的表) |
db_name.routine_name(指定库的函数,存储过程,触发器)
with_option: GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
回收授权
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type]
priv_level FROM user [, user] ...
示例：REVOKE DELETE ON testdb.* FROM 'testuser'@‘172.16.0.%’;
查看指定用户获得的授权
Help SHOW GRANTS
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER[()];
注意：MariaDB服务进程启动时会读取mysql库中所有授权表至内存
(1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，使之生
效
(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表：mysql> FLUSH
PRIVILEGES;
【例18】授权，必须是管理员。
创建数据库
MariaDB [(none)]> create database wordpress;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| wordpress |
+--------------------+
4 rows in set (0.03 sec)
创建并授权⽤户账号
MariaDB [(none)]> grant all on wordpress.* to wpuser@'192.168.37.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user,host,password from mysql.user;
+--------+--------------+-------------------------------------------+
| user | host | password |
+--------+--------------+-------------------------------------------+
| root | localhost | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
| wang | 192.168.37.6 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
| root | ::1 | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
| wpuser | 192.168.37.% | *128977E278358FF80A246B5046F51043A2B1FCED |
+--------+--------------+-------------------------------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> exit
Bye
另一台机器37.6上测试一下
6 ~]# mysql -uwpuser -pcentos -h192.168.37.7
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.5-10.2.44-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| wordpress |
+--------------------+
2 rows in set (0.00 sec)
usage权限，仅能连接
MariaDB [(none)]> show grants for wang@'192.168.37.6';
+----------------------------------------------------------------------------------------------------------------+
| Grants for wang@192.168.37.6 |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang'@'192.168.37.6' IDENTIFIED BY PASSWORD '*6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
ALL PRIVILEGES具有所有权限，能⼲所有事。
MariaDB [(none)]> show grants for wpuser@'192.168.37.%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for wpuser@192.168.37.% |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wpuser'@'192.168.37.%' IDENTIFIED BY PASSWORD '*128977E278358FF80A246B5046F51043A2B1FCED' |
| GRANT ALL PRIVILEGES ON `wordpress`.* TO 'wpuser'@'192.168.37.%' |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
【例19】navicat图形界⾯，远程连接数据库
navicat图形界⾯下载:
解压缩，不用安装、执行navicat.exe
建⽴新连接
注意⽤户权限和主机地址。
6、存储引擎(maria 5.5版本、光盘直接安装yum install mariadb-server)
MyISAM引擎特点
不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5.5前默认的数据库引擎
MyISAM存储引擎适用场景
只读（或者写较少）、表较小（可以接受长时间进行修复操作）
InnoDB引擎特点
行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性更好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5.5开始为默认的数据库引擎
InnoDB数据库文件
所有InnoDB表的数据和索引放置于同一个表空间中
表空间文件：datadir定义的目录下
数据文件：ibddata1, ibddata2, ...
每个表单独使用一个表空间存储表的数据和索引
启用：innodb_file_per_table=ON
【例20】查看当前默认的存储引擎
centos7默认InnoDB，⽀持事务，更稳定。
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)
centos6默认MyISAM，性能好
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)
【例21】修改默认的存储引擎，centos6.
6 ~]$ vim /etc/my.cnf
[mysqld]
default_storage_engine=innodb #修改默认的存储引擎、centos6
重启服务：
6 ~]$ service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
登录mysql：
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)
7、MySQL中的系统数据库
mysql数据库
是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字
等mysql自己需要使用的控制和管理信息
performance_schema数据库
MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为
PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表
information_schema数据库
MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与“数据字典”，
提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方
式）
【例22】MySQL中的系统数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
| wordpress |
+--------------------+
6 rows in set (0.00 sec)
[root@centos7 ~]# ls /var/lib/mysql/
aria_log.00000001 ib_buffer_pool ibtmp1 mysql_upgrade_info wordpress
aria_log_control ibdata1 multi-master.info performance_schema
centos7.pid ib_logfile0 mysql tc.log
hellodb ib_logfile1 mysql.sock test
8、服务器配置选项，服务器系统变量和服务器状态变量
8.1、选项获取mysqld的可用选项列表：
mysqld --help --verbose
mysqld --print-defaults 获取默认设置
设置服务器选项方法：
在命令行中设置
shell> ./mysqld_safe --skip-name-resolve=1
在配置文件my.cnf中设置
skip_name_resolve=1
mysql程序不再PATH变量中，只能找到主程序后，再加选项。
7 ~]# ps aux| grep mysqld
mysql 10003 0.0 5.0 1780612 99740 ? Ssl 17:38 0:05 /usr/sbin/mysqld --basedir=/usr
root 11940 0.0 0.0 112708 976 pts/1 S+ 20:10 0:00 grep --color=auto mysqld
7 ~]# /usr/sbin/mysqld --help -v
可用选项列表，太多了。。。
7 ~]# vim /etc/my.cnf
#
[mysqld]
character-set-server=utf8mb4 # 添加
#重启mariadb
7 ~]# systemctl restart mariadb
7 ~]# mysqld --print-defaults
mysqld would have been started with the following arguments:
--character-set-server=utf8mb4
8.2、服务器系统变量
服务器系统变量：分全局和会话两种
获取系统变量
mysql> SHOW GLOBAL VARIABLES;
mysql> SHOW [SESSION] VARIABLES;
mysql> SELECT @@VARIABLES;
修改服务器变量的值：
mysql> help SET
修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效
mysql> SET GLOBAL system_var_name=value;
mysql> SET @@global.system_var_name=value;
修改会话变量：
mysql> SET [SESSION] system_var_name=value;
mysql> SET @@[session.]system_var_name=value;
【例23】变量
7 ~]# vim /etc/my.cnf
innodb_file_per_table #添加
7 ~]# systemctl restart mariadb #重启服务
查看所有变量：
MariaDB [(none)]> show variables;
查看变量，过滤：
MariaDB [(none)]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
【例24】变量，最⼤连接数
MariaDB [(none)]> show variables like 'max_con%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
最⼤连接数改为1000
MariaDB [(none)]> set global max_connections=1000;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show variables like 'max_con%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 1000 |
+--------------------+-------+
2 rows in set (0.00 sec)
8.3、服务器状态变量
服务器状态变量：不能改
分全局和会话两种
状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改
mysql> SHOW GLOBAL STATUS;
mysql> SHOW [SESSION] STATUS;
MariaDB [(none)]> show status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 580 |
+---------------+-------+
1 row in set (0.00 sec)
【例25】状态变量
线程状态
MariaDB [(none)]> show status like 'threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 1 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
查询次数
MariaDB [(none)]> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 1 | <--查询次数
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> select * from mysql.user;
MariaDB [(none)]> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 2 | <--查询次数发生改变
+---------------+-------+
1 row in set (0.00 sec)
添加次数
MariaDB [(none)]> show status like 'com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert | 0 |
+---------------+-------+
1 row in set (0.00 sec)
8.4、服务器变量SQL_MODE
SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置，参看：
https://mariadb.com/kb/en/library/sql-mode/
常见MODE:
NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户
NO_ZERO_DATE
在严格模式，不允许使用‘0000-00-00’的时间
ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的
NO_BACKSLASH_ESCAPES
反斜杠“\”作为普通字符而非转义字符
PIPES_AS_CONCAT
将"||"视为连接操作符而非“或运算符”
【例26】服务器变量SQL_MODE
创建表
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
| wordpress |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> use test;
Database changed
#创建表
MariaDB [test]> create table t1(id int,name char(3));
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(3) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
查看sql_mode变量
MariaDB [test]> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
超出了赋值的范围，报了warning，但是依然能执⾏，⾃动截选为客现实的值，不安全。
MariaDB [test]> insert t1 values(1,'1234');
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [test]> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [test]> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | 123 |
+------+------+
1 row in set (0.00 sec)
改为传统⽅式
MariaDB [test]> set sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> show variables like 'sql_mode';
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
添加超出赋值范围的，报错，不能添加。
MariaDB [test]> insert t1 values(2,'1234');
ERROR 1406 (22001): Data too long for column 'name' at row 1
MariaDB [test]> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | 123 |
+------+------+
1 row in set (0.00 sec)
说明：在其他终端上，添加依然有效。只是针对当前终端，也可以设为全局模式。退出mysql，变量设置消失，想长久保 存，必须写⼊配置⽂件。
9、缓存
查询缓存（ Query Cache ）原理
缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询
缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写
优缺点
1、不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结
果，提高查询性能
2、查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低其效率；
3、查询缓存的使用，会增加检查和清理Query Cache中记录集的开销
查询缓存相关的服务器变量
query_cache_min_res_unit：查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，但会导致
更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足
query_cache_limit：单个查询结果能缓存的最大值，默认为1M，对于查询结果过大而无法缓存的语句，建
议使用SQL_NO_CACHE
query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于
此值有警报
query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以从查询缓存中返回结果，默
认值为OFF，表示可以在表被其它会话锁定的场景中继续从缓存返回数据；ON则表示不允许
query_cache_type：是否开启缓存功能，取值为ON, OFF, DEMAND
SELECT语句的缓存控制
SQL_CACHE：显式指定存储查询结果于缓存之中
SQL_NO_CACHE：显式查询结果不予缓存
query_cache_type参数变量
query_cache_type的值为OFF或0时，查询缓存功能关闭
query_cache_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予
缓存，显式指定SQL_NO_CACHE，不予缓存，此为默认值
query_cache_type的值为DEMAND或2时，查询缓存功能按需进行，显式指定SQL_CACHE的SELECT语句才会缓
存；其它均不予缓存
查询缓存相关的状态变量：SHOW GLOBAL STATUS LIKE ‘Qcache%';
Qcache_free_blocks：处于空闲状态 Query Cache中内存 Block 数
Qcache_total_blocks：Query Cache 中总Block ，当Qcache_free_blocks相对此值较大时，可能用内
存碎片，执行FLUSH QUERY CACHE清理碎片
Qcache_free_memory：处于空闲状态的 Query Cache 内存总量
Qcache_hits：Query Cache 命中次数
Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，即没有命中的次数
Qcache_lowmem_prunes：记录因为内存不足而被移除出查询缓存的查询数
Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL以及由于
query_cache_type 设置的不会被 Cache 的 SQL语句
Qcache_queries_in_cache：在 Query Cache 中的 SQL 数量查询缓存中内存块的最小分配单位
query_cache_min_res_unit ：(query_cache_size - Qcache_free_memory) /
Qcache_queries_in_cache
查询缓存命中率 ：Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%
查询缓存内存使用率：(query_cache_size – qcache_free_memory) /query_cache_size * 100%
【例27】缓存相关的服务器变量
MariaDB [(none)]> show variables like 'query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)
每⼀条记录，默认给最少4k，最⼤1M的缓存
MariaDB [test]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> select * from students where stuid=1;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
+-------+-------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
【例27】修改缓存
临时，改变量
MariaDB [hellodb]> show variables like 'query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1024000 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)
永久，改配置⽂件
7 ~]# vim /etc/my.cnf
[mysqld]
query_cache_size=100M #修改缓存为100M
7 ~]# systemctl restart mariadb #重启服务
【例28】命中率
MariaDB [hellodb]> show global status like 'Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1006792 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)
第⼀次查看
MariaDB [hellodb]> select * from students where stuid=2;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
+-------+------------+-----+--------+---------+-----------+
1 row in set (0.01 sec)
计⼊缓存，Qcache_hits命中为0
MariaDB [hellodb]> show global status like 'Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1005256 |
| Qcache_hits | 0 | <--Qcache_hits命中为0
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+---------+
8 rows in set (0.00 sec)
第⼆次执⾏
MariaDB [hellodb]> select * from students where stuid=2;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
+-------+------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
计⼊缓存，Qcache_hits命中为1
MariaDB [hellodb]> show global status like 'Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1005256 |
| Qcache_hits | 1 | <--Qcache_hits命中为1
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+---------+
8 rows in set (0.00 sec)
226
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
