（第二十三天）

上午

在同步时，对删除和修改都比较慎重（监控同步时）

mysql 主从搭建 前提软件 libaio ， rsync

1 、主

2 、从

3 、同步

4 、测试

注意：先执行从服务器的脚本，再执行主服务器脚本

master-mysql 配置脚本

先要在主服务器上配置免密登录

ssh-keygen

ssh-copy-id root@10.0.0.12

vim mysql.sh

#!/bin/bash

echo " 安装 master-mysql"

sleep 3

# 下载依赖软件包

yum list installed | grep libaio &> /dev/null

if [ $? -ne 0 ];then

yum -y install libaio &> /dev/null

fi

echo "libaio 已经安装 "

yum list installed | grep rsync &> /dev/null

if [ $? -ne 0 ];then

yum -y install rsync &> /dev/null

fi

echo "rsync 已经安装 "

yum list installed | grep ntpdate.x86_64 &> /dev/null

if [ $? -ne 0 ];then

yum -y install ntpdate.x86_64 &> /dev/null

fi

echo "ntpdate.x86_64 已经安装 "

ntpdate cn.ntp.org.cn

sleep 3

# 解压软件包

tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar

sleep 3

tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

echo " 软件包已经解压 "

# 转移到指定工作目录

cp -r mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql

cd /usr/local/mysql/

# 创建工作文件目录

mkdir mysql-files

# 创建 mysql 系统用户

id mysql

if [ $? -ne 0 ];then

useradd -r -s /sbin/nologin mysql

fi

echo "mysql 用户已经创建 "

# 修改工作文件目录的权限和所属主，所属组

chown mysql:mysql ./mysql-files/

chmod 750 ./mysql-files/

# 删除 /etc/my.cnf 文件

rm -rf /etc/my.cnf

# 初始化 mysql

/usr/local/mysql/bin/mysqld --initialize --user = mysql --

basedir = /usr/local/mysql/

sleep 3

# 加密

/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir = /usr/local/mysql/data

echo " 加密成功 "

# 设置 service 服务

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

# 开启 mysql

service mysql8 start

if [ $? -ne 0 ];then

echo "mysql 开启失败 "

else

echo "mysql 开启成功 "

fi

sleep 3

# 编辑配置文件 my.cnf

cat >> /usr/local/mysql/my.cnf << EOF

[mysqld]

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

socket = /tmp/mysql.sock

activate_all_roles_on_login = on

port = 3306

log-error = /usr/local/mysql/data/db01-master.err

log-bin = /usr/local/mysql/data/binlog

server-id = 10

character_set_server = utf8mb4

EOF

# 重启 mysql

service mysql8 restart

if [ $? -ne 0 ];then

echo "mysql 重启失败 "

else

echo "mysql 重启成功 "

fi

sleep 3

# 数据同步

echo " 开始配置数据同步，如果不配置请在 3 秒内按 ctrl+c 进行打断 "

sleep 3

service mysql8 stop

echo "mysql 服务已经停止 "

rm -rf /usr/local/mysql/data/auto.cnf

echo "auto.cnf 文件已经删除 "

yum -y install rsync &> /dev/null

if [ $? -eq 0 ];then

echo "rsync 软件已经安装成功 "

else

echo "rsync 软件安装失败 "

fi

rsync -av /usr/local/mysql/data root@10.0.0.12:/usr/local/mysql/ &>

/etc/null

if [ $? -eq 0 ];then

echo "data 目录同步成功 "

else

echo "data 目录同步失败 "

fi

sleep 3

# 开启 mysql

service mysql8 start

if [ $? -ne 0 ];then

echo "mysql 开启失败 "

else

echo "mysql 开启成功 "

fi

# 修改环境变量

sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile

# 激活配置

source /etc/profile

if [ $? -ne 0 ];then

echo "profile 文件激活失败 "

fi

echo " 完成 master-mysql 配置 "

source mysql.sh

# source /etc/profile // 激活 profile 文件

mysql -p 'hz),bpIhb2x+' // 密码为上面初始化之后自动生成的

slave-mysql 配置脚本

#!/bin/bash

echo " 安装 slave-mysql"

sleep 3

# 下载依赖软件包

yum list installed | grep libaio &> /dev/null

if [ $? -ne 0 ];then

yum -y install libaio &> /dev/null

fi

echo "libaio 已经安装 "

yum list installed | grep rsync &> /dev/null

if [ $? -ne 0 ];then

yum -y install rsync &> /dev/null

fi

echo "rsync 已经安装 "

yum list installed | grep ntpdate.x86_64 &> /dev/null

if [ $? -ne 0 ];then

yum -y install ntpdate.x86_64 &> /dev/null

fi

echo "ntpdate.x86_64 已经安装 "

ntpdate cn.ntp.org.cn

sleep 3

# 解压软件包

tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar

sleep 3

tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

echo " 软件包已经解压 "

# 转移到指定工作目录

cp -r mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql

cd /usr/local/mysql/

# 创建工作文件目录

mkdir mysql-files

# 创建 mysql 系统用户

id mysql

if [ $? -ne 0 ];then

useradd -r -s /sbin/nologin mysql

fi

echo "mysql 用户已经创建 "

# 修改工作文件目录的权限和所属主，所属组

chown mysql:mysql ./mysql-files/

chmod 750 ./mysql-files/

# 删除 /etc/my.cnf 文件

rm -rf /etc/my.cnf

# 设置 service 服务

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

# 编辑配置文件 my.cnf

cat >> /usr/local/mysql/my.cnf << EOF

[mysqld]

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

socket = /tmp/mysql.sock

activate_all_roles_on_login = on

port = 3310

log-error = /usr/local/mysql/data/db01-slave.err

relay-log = /usr/local/mysql/data/relaylog

server-id = 11

character_set_server = utf8mb4

EOF

# 数据同步

echo " 开始配置数据同步，如果不配置请在 3 秒内按 ctrl+c 进行打断 "

sleep 3

yum -y install rsync &> /dev/null

if [ $? -eq 0 ];then

echo "rsync 软件已经安装成功 "

else

echo "rsync 软件安装失败 "

fi

# 修改环境变量

sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile

# 激活配置

source /etc/profile

if [ $? -ne 0 ];then

echo "profile 文件激活失败 "

fi

echo " 完成 slave-mysql 配置 , 等待 master-mysql 同步 data"

echo " 注意该数据库端口号为 3310"

source mysql.sh

两个脚本都运行完之后

主服务器配置

systemctl stop firewalld // 关闭防火墙

mysql> alter user 'root' @ 'localhost' identified by '123' ;

Query OK, 0 rows affected (0.15 sec) // 修改 root 密码

mysql> create user 'slave' @ '%' identified by '123' ;

Query OK, 0 rows affected (0.03 sec) // 创建 slave 用户

mysql> grant replication slave on *.* to 'slave' @ '%' ;

Query OK, 0 rows affected (0.02 sec) // 授予 replication slave 权限

mysql> flush privileges; // 重新加载权限表

Query OK, 0 rows affected (0.00 sec)

mysql> flush tables with read lock; // 刷新所有表并施加一个全局读锁

Query OK, 0 rows affected (0.01 sec)

mysql> show master status; // 查看主服务器当前二进制日志的状态信息

+ --------------- + ---------- + -------------- + ------------------ + ---------------

---- +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

Executed_Gtid_Set |

+ --------------- + ---------- + -------------- + ------------------ + ---------------

---- +

| binlog.000003 | 1178 | | |

|

+ --------------- + ---------- + -------------- + ------------------ + ---------------

---- +

1 row in set (0.00 sec)

从服务器配置

service mysql8 start

source /etc/profile // 激活 profile 文件

mysql -uslave -p123 -h10 .0.0.13 -P3306 --get-server-public-key // 获取

服务器的公共密钥

mysql> quit

Bye

mysql -P3310 -p 'hz),bpIhb2x+' // 密码为主服务器原先生成的密码

mysql> alter user 'root' @ 'localhost' identified by '123' ;

Query OK, 0 rows affected (0.08 sec) // 修改 root 密码

mysql> change master to

- > master_host = '10.0.0.13' ,

- > master_user = 'slave' ,

- > master_password = '123' ,

- > master_port = 3306 ,

- > master_log_file = 'binlog.000003' ,

- > master_log_pos = 1178 ;

Query OK, 0 rows affected, 9 warnings (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show slave status\G

*************************** 1 . row ***************************

Slave_IO_State: Waiting for source to send event

Master_Host: 10 .0.0.13

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000003

Read_Master_Log_Pos: 1178

Relay_Log_File: relaylog.000002

Relay_Log_Pos: 323

Relay_Master_Log_File: binlog.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

主服务器配置

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

到此配置全部完成

下午

1 、新增

insert into 库名称 . 表名 (id ， username ， password) values(1,"abc","123");

insert into 表名称 values(1,"name","word");

insert into 表名称 select * from 其他表 ;

inster into 表名称 values(),();

2 、删除

delete from 表名 ;

delete from tablename where id=3;

delete from tablename where age>8;

delect from tablename where name on("a","b","c");

3 、修改

update mysql.user set host='%' where name='root'

update user set password='abc' where username="zhangsan"

4 、查询

单表查询： select 字段名列表 from 表名，索引 ;

一、 MySQL 函 数介绍

1 、聚合函数

只有 select ⼦句和 having ⼦句、 order by ⼦句中能使⽤聚合函数， where ⼦句不能使⽤聚合函

数。当使⽤聚合查询以后，不能使⽤ where 条件，如果要添加条件，就使⽤ having 。

2 、其他常⽤函数

（ 1 ）⽇期函数

（ 2 ）数字函数

（ 3 ）字符串函数

二、 MySQL 分组查询

语法：

select 聚合函数 ( 表头名 1), 表头名 2 from 数据表名 group by 表 头名 ;

select 聚合函数 ( 表头名 ) 临时表头名 , 真实表头名 from 数据表名 group by 真实表头名 ;

若⽤ group by 分组查询语句，必须加⼊聚合函数，否则报错 （踩坑）

mysql> create table 学 ⽣ 表 ( 序号 int, 姓名 char(16), 年龄 int, 班级 int);

Query OK, 0 rows affected (0.01 sec)

# 新建 ⼀ 个学 ⽣ 表，表头有：序号，数字形式；姓名， ⽂ 本字符串形式；年龄，数字形式；班级，数字形

式。

mysql> desc 学 ⽣ 表 ;

+ -------- + ---------- + ------ + ----- + --------- + ------- +

| Field | Type | Null | Key | Default | Extra

|

+ -------- + ---------- + ------ + ----- + --------- + ------- +

| 序号 | int(11) | YES | | NULL |

|

| 姓名 | char(16) | YES | | NULL |

|

| 年龄 | int(11) | YES | | NULL |

|

| 班级 | int(11) | YES | | NULL |

|

+ -------- + ---------- + ------ + ----- + --------- + ------- +

4 rows in set (0.00 sec)

# 展示学 ⽣ 表表头信息

insert into 学 ⽣ 表 values(1, 'tom' ,15,101);

insert into 学 ⽣ 表 values(2, 'mike' ,16,102);

insert into 学 ⽣ 表 values(3, 'john' ,14,103);

insert into 学 ⽣ 表 values(4, 'lili' ,15,102);

insert into 学 ⽣ 表 values(5, 'jack' ,15,101);

insert into 学 ⽣ 表 values(6, 'xiaoming' ,17,103);

# 向学 ⽣ 表内插 ⼊ 学 ⽣ 信息

mysql> select * from 学 ⽣ 表 ;

+ -------- + ---------- + -------- + -------- +

| 序号 | 姓名 | 年龄 | 班级 |

+ -------- + ---------- + -------- + -------- +

| 1 | tom | 15 | 101 |

| 2 | mike | 16 | 102 |

| 3 | john | 14 | 103 |

| 4 | lili | 15 | 102 |

| 5 | jack | 15 | 101 |

| 6 | xiaoming | 17 | 103 |

+ -------- + ---------- + -------- + -------- +

6 rows in set (0.00 sec)

# 查看表内学 ⽣ 信息

mysql> select avg( 年龄 ) from 学 ⽣ 表 ;

+ ------------- +

| avg( 年龄 ) |

+ ------------- +

| 15 .3333 |

+ ------------- +

1 row in set (0.00 sec)

# 计算所有学 ⽣ 平均年龄， avg ：计算平均值，计算 ⾮ 数字，结果为 0

mysql> select avg( 年龄 ) from 学 ⽣ 表 group by 班级 ;

+ ------------- +

| avg( 年龄 ) |

+ ------------- +

| 15 .0000 |

| 15 .5000 |

| 15 .5000 |

+ ------------- +

3 rows in set (0.00 sec)

# 通过 group by 语法计算每个班的学 ⽣ 平均年龄

mysql> select avg( 年龄 ) 平均年龄 , 班级 from 学 ⽣ 表 group by 班级 ;

+ -------------- + -------- +

| 平均年龄 | 班级 |

+ -------------- + -------- +

| 15 .0000 | 101 |

| 15 .5000 | 102 |

| 15 .5000 | 103 |

+ -------------- + -------- +

3 rows in set (0.00 sec)

#“ 平均年龄 ” ：这个是 ⾃⼰ 临时命名的表头名，也可以不写，那么就是下 ⾯ 的效果

mysql> select avg( 年龄 ), 班级 from 学 ⽣ 表 group by 班级 ;

+ ------------- + -------- +

| avg( 年龄 ) | 班级 |

+ ------------- + -------- +

| 15 .0000 | 101 |

| 15 .5000 | 102 |

| 15 .5000 | 103 |

+ ------------- + -------- +

3 rows in set (0.00 sec)

mysql> select min( 年龄 ) 最 ⼩ 年龄 , 班级 from 学 ⽣ 表 group by 班级 ;

+ -------------- + -------- +

| 最 ⼩ 年龄 | 班级 |

+ -------------- + -------- +

| 15 | 101 |

| 15 | 102 |

| 14 | 103 |

+ -------------- + -------- +

3 rows in set (0.00 sec)

#avg 也可以改成 min 最 ⼩ 值、 max 最 ⼤ 值、 sum 加法函数等运算 ⽅ 式

mysql> select count(*) 总 ⼈ 数 , 班级 from 学 ⽣ 表 group by 班级 ;

+ ----------- + -------- +

| 总 ⼈ 数 | 班级 |

+ ----------- + -------- +

| 2 | 101 |

| 2 | 102 |

| 2 | 103 |

+ ----------- + -------- +

3 rows in set (0.00 sec)

# 也可以 ⽤ count 统计函数计算每个班的总 ⼈ 数

# 这些 count 、 avg 、 max 、 min 、 sum 就是聚合函数，分组查询必须有这些函数的其 ⼀

三、 MySQL ⼦语句查询

⼦查询是指⼀个查询语句嵌套在另⼀个查询语句内部的查询 ;

该查询语句可以嵌套在⼀个 SELECT 、 SELECT...INTO 、 INSERT...INTO 等语句中。

在执⾏查询时，⾸先会执⾏⼦查询中的语句 , 再将返回的结果作为外层查询的过滤条件。

在⼦查询中通常可以使⽤⽐较运算符和 IN 、 EXISTS 、 ANY 、 ALL 等关键字。

例如： select * from class where cid=(select classid from student where sname=' 张三 ');

1 、⽐较运算符的⼦查询

也就是⽤ “=” 、 “<” 、 “>” 这类⽐较运算符

mysql> create table class(cid int(4) not null primary key,cname varchar(20));

Query OK, 0 rows affected (0.05 sec)

# 创建班级表

mysql> create table student (sid int(4) not null primary key, sname varchar

(20), sage int (2), classid int (4) not null);

Query OK, 0 rows affected (0.03 sec)

# 创建学 ⽣ 表

insert into class values(1001, 'Java' );

insert into class values(1002, 'C++' );

insert into class values(1003, 'Python' );

insert into class values(1004, 'PHP' );

insert into class values(1005, 'Android' );

# 向班级表插 ⼊ 数据

insert into student values(1, ' 张三 ' ,20,1001);

insert into student values(2, ' 李四 ' ,21,1002);

insert into student values(3, ' 王五 ' ,24,1003);

insert into student values(4, ' 赵六 ' ,23,1004);

insert into student values(5, ' ⼩ 明 ' ,21,1001);

insert into student values(6, ' ⼩ 红 ' ,26,1001);

insert into student values(7, ' ⼩ 亮 ' ,27,1002);

# 向学 ⽣ 表插 ⼊ 数据

mysql> select * from class where cid = (select classid from student where

sname = ' 张三 ' );

+ ------ + ------- +

| cid | cname |

+ ------ + ------- +

| 1001 | Java |

+ ------ + ------- +

1 row in set (0.00 sec)

# 查询张三同学所在班级的信息

mysql> select * from class where cid>(select classid from student where

sname = ' 张三 ' );

+ ------ + --------- +

| cid | cname |

+ ------ + --------- +

| 1002 | C ++ |

| 1003 | Python |

| 1004 | PHP |

| 1005 | Android |

+ ------ + --------- +

4 rows in set (0.03 sec)

# 查询 ⽐ 张三同学所在班级编号还 ⼤ 的班级的信息

2 、 exists 关键字的⼦查询

exists 关键字后⾯的参数可以是任意⼀个⼦查询， 它不产⽣任何数据，只返回 true 或 false 。⽽当

返回值为 true 时外层查询才会执⾏。

相当于内层句⼦是⼀个判断句式。

mysql> select * from class where exists (select * from student where

sname = ' 王五 ' );

+ ------ + --------- +

| cid | cname |

+ ------ + --------- +

| 1001 | Java |

| 1002 | C ++ |

| 1003 | Python |

| 1004 | PHP |

| 1005 | Android |

+ ------ + --------- +

5 rows in set (0.00 sec)

# 假如王五同学在学 ⽣ 表中则从班级表查询所有班级信息

四、 MySQL 多表联合查询

1 、交叉连接查询

交叉连接返回的结果是被连接的两个表中所有数据⾏的笛卡尔积 ;

⽐如：集合 A={a,b} ，集合 B={0,1,2} ，则集合 A 和 B 的笛卡尔积为 {(a,0),(a,1),(a,2),(b,0),(b,1),(b,2) 。所

以，交叉连接也被称为笛卡尔连接。

语法： select * from 表 1 cross join 表 2;

mysql> select * from student cross join class;

+ ----- + -------- + ------ + --------- + ------ + --------- +

| sid | sname | sage | classid | cid | cname |

+ ----- + -------- + ------ + --------- + ------ + --------- +

| 1 | 张三 | 20 | 1001 | 1001 | Java |

| 1 | 张三 | 20 | 1001 | 1002 | C ++ |

| 1 | 张三 | 20 | 1001 | 1003 | Python |

| 1 | 张三 | 20 | 1001 | 1004 | PHP |

| 1 | 张三 | 20 | 1001 | 1005 | Android |

| 2 | 李四 | 21 | 1002 | 1001 | Java |

| 2 | 李四 | 21 | 1002 | 1002 | C ++ |

| 2 | 李四 | 21 | 1002 | 1003 | Python |

| 2 | 李四 | 21 | 1002 | 1004 | PHP |

| 2 | 李四 | 21 | 1002 | 1005 | Android |

| 3 | 王五 | 24 | 1003 | 1001 | Java |

| 3 | 王五 | 24 | 1003 | 1002 | C ++ |

| 3 | 王五 | 24 | 1003 | 1003 | Python |

| 3 | 王五 | 24 | 1003 | 1004 | PHP |

| 3 | 王五 | 24 | 1003 | 1005 | Android |

| 4 | 赵六 | 23 | 1004 | 1001 | Java |

| 4 | 赵六 | 23 | 1004 | 1002 | C ++ |

| 4 | 赵六 | 23 | 1004 | 1003 | Python |

| 4 | 赵六 | 23 | 1004 | 1004 | PHP |

| 4 | 赵六 | 23 | 1004 | 1005 | Android |

| 5 | ⼩ 明 | 21 | 1001 | 1001 | Java |

| 5 | ⼩ 明 | 21 | 1001 | 1002 | C ++ |

| 5 | ⼩ 明 | 21 | 1001 | 1003 | Python |

| 5 | ⼩ 明 | 21 | 1001 | 1004 | PHP |

| 5 | ⼩ 明 | 21 | 1001 | 1005 | Android |

| 6 | ⼩ 红 | 26 | 1001 | 1001 | Java |

| 6 | ⼩ 红 | 26 | 1001 | 1002 | C ++ |

| 6 | ⼩ 红 | 26 | 1001 | 1003 | Python |

| 6 | ⼩ 红 | 26 | 1001 | 1004 | PHP |

| 6 | ⼩ 红 | 26 | 1001 | 1005 | Android |

| 7 | ⼩ 亮 | 27 | 1002 | 1001 | Java |

| 7 | ⼩ 亮 | 27 | 1002 | 1002 | C ++ |

| 7 | ⼩ 亮 | 27 | 1002 | 1003 | Python |

| 7 | ⼩ 亮 | 27 | 1002 | 1004 | PHP |

| 7 | ⼩ 亮 | 27 | 1002 | 1005 | Android |

+ ----- + -------- + ------ + --------- + ------ + --------- +

35 rows in set (0.00 sec)

# 两个表交叉连接， class 表的 cid 和 student 表的 classid 相乘

2 、多表联合查询

语法： select 查询字段 1, 查询字段 2, ... from 表 1 join 表 2 join ... on 表 1. 关系字段 = 表 2. 关系字段

=... ；

join ：联合

on ：条件

mysql> select sname,cname from student inner join class on

student .classid = class.cid;

+ -------- + -------- +

| sname | cname |

+ -------- + -------- +

| 张三 | Java |

| 李四 | C ++ |

| 王五 | Python |

| 赵六 | PHP |

| ⼩ 明 | Java |

| ⼩ 红 | Java |

| ⼩ 亮 | C ++ |

+ -------- + -------- +

7 rows in set (0.00 sec)

# 查询学 ⽣ 姓名及其所学习的学科

五、 MySQL 授权

1 、 MySQL 基础权限

2 、权限语法

grant all on test.user

客户机地址可以有以下表现形式：

% ：表示所有地址

192.168.33.% ：表示 33 ⽹段下所有主机，⼀般为你想为哪个⽹段授权，或为⾃⼰⽹段下的主机授权

mysql> grant all on *.* to haha@ '192.168.100.%' identified by '123456' ;

# 为 haha ⽤ 户进 ⾏ 授权

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges; # 刷新权限

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for haha@ '192.168.100.%' ; # 查看 haha 的权限

+ ------------------------------------------------------- +

| Grants for haha@192.168.100.% |

+ ------------------------------------------------------- +

| GRANT ALL PRIVILEGES ON *.* TO 'haha' @ '192.168.100.%' |

+ ------------------------------------------------------- +

1 row in set (0.00 sec)

mysql> drop user haha@ '192.168.100.%' ; # 删除 ⽤ 户

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for haha@ '192.168.100.%' ;

ERROR 1141 (42000): There is no such grant defined for user 'haha' on host

'192.168.100.%'

六、 MySQL 触发器

1 、概念

触发器是⼀种特殊的存储过程，它在插⼊，删除或修改特定表中的数据时触发执⾏，它⽐数据库本

身标准的功能有更精细和更复杂的数据控制能⼒。

1 、例如在某⼀个时间触发什么事情

2 、例如不允许股票价格的升幅⼀次超过 %10

3 、审计功能，某⼀个⼈登录会记录所有的操作

2 、触发器语法

create trigger 触发器名称 触发的时机 触发的动作 on 表名 for each row 触发器状态

1 、语法的红字为固定格式

2 、触发器名称：⾃定义

3 、触发的时机： before( 之前 ) 或 after( 之后 ) ，在执⾏动作之前还是之后

4 、触发的动作：指的激发触发程序的语句类型（插⼊、更新、删除表或数据等）

5 、 each row ：操作的每⼀⾏都会被监控

6 、触发器状态：在触发的动作之前或之后做什么事情，⽐如当我删 了表 1 的某条数据后，⾃动清空表 2 。

3 、触发器创建的四要素

（ 1 ）监视地点： table （表）

（ 2 ）监视事件： insert （插⼊）、 update （更新）、 delete （删除） 等动作

（ 3 ）触发时间： before （之前）、 after （之后）

（ 4 ）触发事件：在监视事件之前或之后，对当前表或其他表的插 ⼊、更新、删除等动作

4 、触发器实例

# 查看数据库内的所有表

mysql> show tables;

+ -------------- +

| Tables_in_yh |

+ -------------- +

| class |

| it |

| student |

+ -------------- +

3 rows in set (0.00 sec)

# 创建触发器规则

# 命令 ⼤ 意：创建名为 deltable 的触发器，在执 ⾏ 每 ⼀ 条的删除 class 表命令之后，删除 student 表

mysql> create trigger deltable after delete on class for each row delete from

student;

Query OK, 0 rows affected (0.00 sec)

# 查看触发器

mysql> show triggers\G;

*************************** 1 . row ***************************

Trigger: deltable

Event: DELETE

Table: class

Statement: delete from student

Timing: AFTER

Created: 2023 -09-26 20 :46:24.53

sql_mode:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_

DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AU

TO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Definer: root@localhost

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

ERROR:

No query specified

# 查看当前数据库下的所有数据表

mysql> show tables;

+ -------------- +

| Tables_in_yh |

+ -------------- +

| class |

| it |

| student |

+ -------------- +

3 rows in set (0.00 sec)

# 查看 ⼀ 下 class 表的内容，因为要 ⽤ 该表做示范

mysql> select * from class;

+ ------ + --------- +

| cid | cname |

+ ------ + --------- +

| 1001 | Java |

| 1002 | C ++ |

| 1003 | Python |

| 1004 | PHP |

| 1005 | Android |

+ ------ + --------- +

5 rows in set (0.01 sec)

# 查看 ⼀ 下 student 表的内容，因为要 ⽤ 该表做示范

mysql> select * from student;

+ ----- + ---------- + ------ + --------- +

| sid | sname | sage | classid |

+ ----- + ---------- + ------ + --------- +

| 1 | mike | 17 | 1001 |

| 2 | john | 18 | 1002 |

| 3 | lili | 19 | 1003 |

| 4 | zhangsan | 20 | 1004 |

| 5 | tom | 21 | 1005 |

+ ----- + ---------- + ------ + --------- +

5 rows in set (0.00 sec)

# 删除 class 表中 cid 为 1005 的整 ⾏ 数据，也就是执 ⾏ 之前设置的触发动作

mysql> delete from class where cid = 1005 ;

Query OK, 1 row affected (0.00 sec)

# 因为触发器执 ⾏ ，所以 student 表的内容在 class 表的删除动作后被清空

mysql> select * from student;

Empty set (0.00 sec)

# 删除触发器

mysql> drop trigger deltable;

Query OK, 0 rows affected (0.00 sec)

七、 MySQL 基本优化操作

1 、忘记 MySQL 密码

[root@localhost ~] # vim /etc/my.cnf

[mysqld]

skip-name-resolve # 添加该 ⾏ ，表示本机跳过 MySQL 密码验证

skip-grant-tables # 添加该 ⾏ ，表示登录时，忽略所有的 ⽤ 户认证信息，包括 ⽤ 户名、密码和权

限。

:wq

[root@localhost ~] # systemctl restart mysqld.service

[root@localhost ~] # mysql # 免密时，直接使 ⽤ MySQL 命令登录

mysql> use mysql;

mysql> update user set authentication_string = password( '123' ) where

user = "root" ;

Query OK, 1 row affected, 1 warning (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 1

# 更新 MySQL ⽤ 户 “root” 的密码为 “123” 等。如果 ⽤ 户名不叫 “root” ，那么最后的等号后 ⾯ 写你想要改

的 ⽤ 户名

mysql> select Host,user,authentication_string from user;

# 这条命令意为：算法对账户明 ⽂ 密码加密后的字符串，不 ⽤ 看，看不懂，没什么 ⽤ 。

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@localhost ~] # mysql -uroot -p123

2 、 MySQL 存储引擎

（ 1 ）查看数据库使⽤的存储引擎

mysql> use yh; # 查看存储引擎时，必须要切换到某个数据库

Database changed

mysql> show tables; # 查看当前 yh 数据库下的所有数据表

+ -------------- +

| Tables_in_yh |

+ -------------- +

| class |

| it |

| student |

+ -------------- +

3 rows in set (0.00 sec)

mysql> show table status \G; # 查看所有数据表的存储引擎

*************************** 1 . row ***************************

Name: class

Engine: InnoDB # 存储引擎，常 ⽤ 的还有 MyISAM

Create_time: 2023 -09-25 09 :27:28 # 数据表创建时间

Update_time: 2023 -09-26 20 :57:20 # 数据表更新时间

*************************** 2 . row ***************************

Name: it

Engine: InnoDB # 存储引擎

Create_time: 2023 -09-25 17 :55:38 # 数据表创建时间

*************************** 3 . row ***************************

Name: student

Engine: InnoDB

Create_time: 2023 -09-25 10 :16:48 # 数据表创建时间

Update_time: 2023 -09-26 20 :57:20

mysql> show table status where name = 'class' \G;

# 也可查看单个数据表的存储引擎

*************************** 1 . row ***************************

Name: class

Engine: InnoDB # 存储引擎

Version: 10

Row_format: Dynamic

Rows: 4

Avg_row_length: 4096

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2023 -09-25 09 :27:28 # 创建时间

Update_time: 2023 -09-26 20 :57:20 # 更新时间

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

ERROR:

No query specified

（ 2 ）创建表并指定存储引擎

mysql> create table haha(id int) engine = MyISAM;

# 创建表 haha ，并添加数字形式的表头 id ，指定 haha 表的存储引擎为 MyISAM

Query OK, 0 rows affected (0.00 sec)

mysql> show table status where name = 'haha' \G;

# 查看 haha 表的存储引擎及其他状态信息

*************************** 1 . row ***************************

Name: haha

Engine: MyISAM # 存储引擎

（ 3 ）修改已存在数据表的存储引擎

mysql> alter table haha engine = InnoDB; # 修改已存在的数据表 haha 的存储引擎

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show table status where name = 'haha' \G;

# 查看 haha 表状态及存储引擎

*************************** 1 . row ***************************

Name: haha

Engine: InnoDB # 存储引擎

（ 4 ）修改 MySQL 服务的默认存储引擎

[root@localhost ~] # vim /etc/my.cnf

[mysqld]

default-storage-engine = InnoDB # 添加此 ⾏ ，当以后再创建表时，存储引擎将改为 InnoDB

练习

远程连接数据库的要素

1 、 username

2 、 password

3 、 url mysql ip| 域名 数据库名称 端口

别名

select id as 编号 ,name as 姓名 ,gender as 性别 from student;

数据分析的基础

1 、排序

max

min

2 、汇总

count

sum

avg

3 、数学进制

二进制

八进制

十进制

十六进制

select max(price) from product;

select * from student order by gender;

select * from student order by gender desc;

select * from student order by gender asc;

当我们对数据进行聚合查询后，不能使用 where 条件，如果要添加条件就使用 having

select gender as 性别， count(gender) as 人数 from student group by gender;

create table product(id int primary key auto_increment,name varchar(45) not

null,price float not null,qty int not null);

desc product;

insert into product (name,price,qty) values(" 香蕉 ",8.5,200),(" 苹果 ",12.5,400),

(" 菠萝 ",12.4,70),(" 哈密瓜 ",18.3,400);

select * from product;

select * from product order by qty;

select * from product order by price;

select * from (select * from product order by qty) as a order by a.price;

select max(price) from product;

select min(price) from product;

select sum(price) from product;

select avg(price) from product;

select *,price*qty as tt from product;

select sum(tt) from (select *,price*qty as tt from product) as a;

select year('1985-7-6') as birth;

select month('1985-7-6') as mon;

select now();

select year(now());

select second(now());

insert into product (name,price,qty)values(now(),7.8,90);

select * from product;

create table p(createtime defult now())

select trim("a b");

select floor(3.14);

select floor(-3.14);

select ceiling(9.8);

select ceiling(9.3);

select round(9.5);