Day 19
一、Mysql语句
1、新增
insert into库名称.表名
Inser into
2、删除
3、修改
4、查询：查看数据库、数据表及字段
# 创建普通用户进行远程连接，提供用户名和密码
mysql> create user 'tp'@'%' identified by 'tp';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to 'tp'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| abc |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.01 sec)
mysql> use test;
# 查看student表
mysql> select * from student;
+----+--------+--------+
| id | name | gender |
+----+--------+--------+
| 1 | 花卷 | 女 |
| 2 | 张三 | 男 |
| 3 | 莎莎 | 女 |
+----+--------+--------+
3 rows in set (0.01 sec)
# 只查看student表的部分字段
mysql> select name from student;
+--------+
| name |
+--------+
| 花卷 |
| 张三 |
| 莎莎 |
+--------+
3 rows in set (0.00 sec)
mysql> select id,gender from student;
+----+--------+
| id | gender |
+----+--------+
| 1 | 女 |
| 2 | 男 |
| 3 | 女 |
+----+--------+
3 rows in set (0.00 sec)
# 设置别名
mysql> select id as 编号,name as 名字,gender as 性别from student;
+--------+--------+--------+
| 编号 | 名字 | 性别 |
+--------+--------+--------+
| 1 | 花卷 | 女 |
| 2 | 张三 | 男 |
| 3 | 莎莎 | 女 |
+--------+--------+--------+
3 rows in set (0.00 sec)
mysql> select gender as 性别,count(*) as 人数 from student group by gender;
+--------+--------+
| 性别 | 人数 |
+--------+--------+
| 女 | 3 |
| 男 | 2 |
+--------+--------+
2 rows in set (0.00 sec)
# 任意两张表组成一个字段
mysql> select a.*,b.* from student as a,student as b;
+----+--------+--------+----+--------+--------+
| id | name | gender | id | name | gender |
+----+--------+--------+----+--------+--------+
| 3 | 莎莎 | 女 | 1 | 花卷 | 女 |
| 2 | 张三 | 男 | 1 | 花卷 | 女 |
| 1 | 花卷 | 女 | 1 | 花卷 | 女 |
| 3 | 莎莎 | 女 | 2 | 张三 | 男 |
| 2 | 张三 | 男 | 2 | 张三 | 男 |
| 1 | 花卷 | 女 | 2 | 张三 | 男 |
| 3 | 莎莎 | 女 | 3 | 莎莎 | 女 |
| 2 | 张三 | 男 | 3 | 莎莎 | 女 |
| 1 | 花卷 | 女 | 3 | 莎莎 | 女 |
+----+--------+--------+----+--------+--------+
9 rows in set (0.00 sec)
二、Mysql函数：
1、排序：asc(升序)、desc(降序)
mysql> select * from student order by gender desc;
+----+--------+--------+
| id | name | gender |
+----+--------+--------+
| 2 | 张三 | 男 |
| 5 | 孙八 | 男 |
| 1 | 花卷 | 女 |
| 3 | 莎莎 | 女 |
| 4 | 赵六 | 女 |
+----+--------+--------+
5 rows in set (0.00 sec)
mysql> select * from student order by gender asc;
+----+--------+--------+
| id | name | gender |
+----+--------+--------+
| 1 | 花卷 | 女 |
| 3 | 莎莎 | 女 |
| 4 | 赵六 | 女 |
| 2 | 张三 | 男 |
| 5 | 孙八 | 男 |
+----+--------+--------+
5 rows in set (0.00 sec)
# 按数量升序排列
mysql> select * from product order by qty;
+----+-----------+-------+-----+
| id | name | price | qty |
+----+-----------+-------+-----+
| 1 | 香蕉 | 8.5 | 200 |
| 2 | 苹果 | 12.5 | 400 |
| 3 | 菠萝 | 12.4 | 600 |
| 4 | 哈密瓜 | 18.2 | 700 |
+----+-----------+-------+-----+
4 rows in set (0.00 sec)
# 按价格降序排列
mysql> select * from (select * from product order by qty) as a order by a.price desc;
+----+-----------+-------+-----+
| id | name | price | qty |
+----+-----------+-------+-----+
| 4 | 哈密瓜 | 18.2 | 700 |
| 2 | 苹果 | 12.5 | 400 |
| 3 | 菠萝 | 12.4 | 600 |
| 1 | 香蕉 | 8.5 | 200 |
+----+-----------+-------+-----+
4 rows in set (0.00 sec)
2、汇总：count、sum、avg
3、数制：二进制、八进制、十六进制
4、聚合函数
注意！！！：只有 select ⼦句和 having ⼦句、order by ⼦句中能使⽤聚合函数，where ⼦句不能使⽤聚合函数。当使⽤聚合查询以后，不能使⽤where条件，如果要添加条件，就使⽤having。
========举例============
# 创建product表
mysql> create table product(
-> id int primary key auto_increment,
-> name varchar (45) not null,
-> pirce float not null,
-> qty int not null);
Query OK, 0 rows affected (0.02 sec)
# 查看表
mysql> desc product;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(45) | NO | | NULL | |
| price | float | NO | | NULL | |
| qty | int | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
# 插入数据
mysql> insert into product (name, price,qty) values("香蕉", 8.5 , 200);
Query OK, 1 row affected (0.00 sec)
mysql> insert into product (name, price,qty) values("苹果", 12.5, 400);
Query OK, 1 row affected (0.00 sec)
mysql> insert into product (name, price,qty) values("菠萝", 12.4, 600);
Query OK, 1 row affected (0.00 sec)
mysql> insert into product (name, price,qty) values("哈密瓜", 18.2, 700);
Query OK, 1 row affected (0.00 sec)
# 查看表的详细信息
mysql> select * from product;
+----+-----------+-------+-----+
| id | name | price | qty |
+----+-----------+-------+-----+
| 1 | 香蕉 | 8.5 | 200 |
| 2 | 苹果 | 12.5 | 400 |
| 3 | 菠萝 | 12.4 | 600 |
| 4 | 哈密瓜 | 18.2 | 700 |
+----+-----------+-------+-----+
# 新增列
mysql> select *,price*qty as zj from product;
+----+-----------+-------+-----+--------------------+
| id | name | price | qty | zj |
+----+-----------+-------+-----+--------------------+
| 1 | 香蕉 | 8.5 | 200 | 1700 |
| 2 | 苹果 | 12.5 | 400 | 5000 |
| 3 | 菠萝 | 12.4 | 600 | 7439.999771118164 |
| 4 | 哈密瓜 | 18.2 | 700 | 12740.000534057617 |
+----+-----------+-------+-----+--------------------+
4 rows in set (0.00 sec)
4 rows in set (0.00 sec)
1)常用聚合函数
# 统计表中数据的行数或者统计指定列不为空值的数据行个数
select count(*或表头名) from 表名；
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.06 sec)
mysql> select count(1) from student;
+----------+
| count(1) |
+----------+
| 3 |
+----------+
1 row in set (0.06 sec)
mysql> select count(gender) from student;
+---------------+
| count(gender) |
+---------------+
| 3 |
+---------------+
1 row in set (0.05 sec)
# 计算指定列的最大值，如果指定列是字符串，类型(文字类型)则使用字符首拼排序
select max(表头名) from 表名;
mysql> select max(price) from product;
+------------+
| max(price) |
+------------+
| 18.2 |
+------------+
1 row in set (0.00 sec)
# 计算指定列的最⼩值，如果指定列是字符串，类型(文字类型)则使用字符首拼排序
select min(表头名) from 表名;
mysql> select min(price) from product;
+------------+
| min(price) |
+------------+
| 8.5 |
+------------+
1 row in set (0.00 sec)
# 计算指定列的数值和，如果指定列不是数值，类型则计算结果为 0
select sum(表头名) from 表名;
mysql> select sum(price) from product;
+-------------------+
| sum(price) |
+-------------------+
| 51.60000038146973 |
+-------------------+
1 row in set (0.00 sec)
# 计算指定列的平均值，如果指定列不是数值，类型则计算结果为 0
select avg(表头名) from 表名;
mysql> select avg(price) from product;
+--------------------+
| avg(price) |
+--------------------+
| 12.900000095367432 |
+--------------------+
1 row in set (0.00 sec)
5、其他常用函数
=======日期时间函数=====
mysql> select month('2003-09-01') as birth;
+-------+
| birth |
+-------+
| 9 |
+-------+
1 row in set (0.00 sec)
mysql> select day('2003-09-01') as birth;
+-------+
| birth |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
# 查看当前年份
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2024 |
+-------------+
1 row in set (0.00 sec)
# 查看当前秒
mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
| 53 |
+---------------+
1 row in set (0.00 sec)
# 在表中插入时间戳，记录每次修改的时间
mysql> insert into product (name, price,qty) values(now(),7.8,90);
Query OK, 1 row affected (0.00 sec)
| 3 | 菠萝 | 12.4 | 600 |
| 4 | 哈密瓜 | 18.2 | 700 |
| 5 | 2024-08-07 15:33:09 | 7.8 | 90 |
+----+---------------------+-------+-----+
5 rows in set (0.00 sec)
=========数字函数=======
mysql> select floor(3.14);
+-------------+
| floor(3.14) |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
# 向上取整
mysql> select ceiling(9.5);
+--------------+
| ceiling(9.5) |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)
# 四舍五入
mysql> select round(8.1);
+------------+
| round(8.1) |
+------------+
| 8 |
+------------+
1 row in set (0.00 sec)
=========字符串函数=======
mysql> select trim("a b");
+-------------+
| trim("a b") |
+-------------+
| a b |
+-------------+
1 row in set (0.00 sec)
三、Mysql分组查询：
1、语法：
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)
#计算所有学生平均年龄，avg：计算平均值，计算⾮数字，结果为0
mysql> select avg(年龄) from 学生表;
+-------------+
| avg(年龄) |
+-------------+
| 15.3333 |
+-------------+
1 row in set (0.00 sec)
#通过group by语法计算每个班的学生平均年龄
mysql> select avg(年龄) from 学生表 group by 班级;
+-------------+
| avg(年龄) |
+-------------+
| 15.0000 |
| 15.5000 |
| 15.5000 |
+-------------+
3 rows in set (0.00 sec)
#“平均年龄”：这个是⾃⼰临时命名的表头名，也可以不写，那么就是下⾯的效果
mysql> select avg(年龄) 平均年龄,班级 from 学生表
group by 班级;
+--------------+--------+
| 平均年龄 | 班级 |
+--------------+--------+
| 15.0000 | 101 |
| 15.5000 | 102 |
| 15.5000 | 103 |
+--------------+--------+
3 rows in set (0.00 sec)
#avg也可以改成min最⼩值、max最⼤值、sum加法函数等运算⽅式
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)
#也可以⽤count统计函数计算每个班的总⼈数，这些count、avg、max、min、sum就是聚合函数，分组查询必须有这些函数的其⼀。
mysql> select count(*) 总⼈数,班级 from 学⽣表 group
by 班级;
+-----------+--------+
| 总⼈数 | 班级 |
+-----------+--------+
| 2 | 101 |
| 2 | 102 |
| 2 | 103 |
+-----------+--------+
3 rows in set (0.00 sec)
四、MySQL子语句查询
1、⼦查询是指⼀个查询语句嵌套在另⼀个查询语句内部的查询;该查询语句可以嵌套在⼀个 SELECT、SELECT...INTO、INSERT...INTO 等语句中。
2、在执⾏查询时，⾸先会执⾏⼦查询中的语句,再将返回的结果作为外层查询的过滤条件。
3、在⼦查询中通常可以使⽤⽐较运算符和 IN、EXISTS、ANY、ALL 等关键字。
例如：select * from class where cid=(select classid from
student where sname='张三');
1)⽐较运算符的⼦查询：也就是⽤“=”、“<”、“>”这类⽐较运算符
mysql> select sum(zj) from (select *,price*qty as zj from producct) as b;
+-------------------+
| sum(zj) |
+-------------------+
| 26880.00030517578 |
+-------------------+
1 row in set (0.00 sec)
======举例=======
# 创建班级表
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;
#两个表交叉连接，class表的cid和student表的classid相乘
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)
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
# grant all on 库.* to ⽤户@客户机地址 identified by '密码';all：表示所有权限，可替换为其他权限库.*：表示某个数据库下所有数据表⽤户@客户机地址：表示希望给予哪个用户权限，前⾯的⽤户是临时⾃建⽤户，当客户机登录时，⽤该⽤户登录'密码'：临时⾃建⽤户的密码，也是临时⾃写的。
#查看给予客户机的权限
show grants for ⽤户@客户机地址
# 取消权限
revoke 权限列表 on 库.* from ⽤户@客户机地址；
# 刷新权限
flush privileges;（重新加载权限表）
# 删除临时用户
drop user '⽤户名'@'客户机地址';
========举例==========
# 为haha⽤户进⾏授权
mysql> grant all on *.* to haha@'192.168.100.%'
identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges; # 刷新权限
Query OK, 0 rows affected (0.00 sec)
# 查看haha的权限
mysql> show grants for haha@'192.168.100.%';
+-------------------------------------------------
------+
| 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、概念：触发器是⼀种特殊的存储过程，它在插⼊，删除或修改特定表中的数据时触发执⾏，它⽐数据库本身标准的功能有更精细和更复杂的数据控制能⼒。例如在某⼀个时间触发什么事情 、不允许股票价格的升幅⼀次超过%10 、审计功能，某⼀个⼈登录会记录所有的操作。
2、触发器语法：
create trigger 触发器名称 触发的时机 触发的动作 on 表名 for each row 触发器状态。 其中触发器名称自定义；
触发的时机：before(之前) 或 after(之后)，在执⾏动作之前还是之后；
触发的动作：指的激发触发程序的语句类型<insert,update,delete>（插⼊、更新、删除表或数据等）；
each row：操作的每⼀⾏都会被监控；
触发器状态：在触发的动作之前或之后做什么事情，⽐如当我删了表 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;
#这条命令意为：算法对账户明文密码加密后的字符串，不用看，看不懂，没什么用。
+-----------+-----------+-------------------------
------------------+
| Host | user | authentication_string
|
+-----------+-----------+-------------------------
------------------+
| localhost | root |
*C42CF059802456312318BB928C3334F1A6133AB4 |
| localhost | mysql.sys |
*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------
------------------+
2 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p123
4231
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
