⼀、MySQL 函数
数据分析的基础
1. 排序
1. max
2. min
2. 汇总
1. count
2. sum
3. avg
3. 数制
1. ⼆进制
1. 0
0
2. 1 1
3. 2 10
4. 3 11
5. 4 100
6.
2. ⼋进制
3. ⼗进制
4. ⼗六进制
5. AA 27
1、聚合函数
只有 select ⼦句和 having ⼦句、order by ⼦句中能使⽤聚合函数,where ⼦句不能使⽤聚合函数。当使⽤聚合查询以后,不能使⽤where条件,如果要添加条件,就使⽤having
![](https://i-blog.csdnimg.cn/direct/f3000e2cf4ee4cb6a510f593a6eb246d.png)
2、其他常⽤函数
(1)日期函数
![](https://i-blog.csdnimg.cn/direct/0677ef28d7024c35aca8fee6f6876d00.png)
![](https://i-blog.csdnimg.cn/direct/2715e2572d814b0a9aae84e108d91789.png)
(2)数字函数
![](https://i-blog.csdnimg.cn/direct/324fa99d58a34ff5af1e3f90c143ae64.png)
(3)字符串函数
![](https://i-blog.csdnimg.cn/direct/c2e7edbb17cc41f6a0b19ce81141deb8.png)
⼆、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 fromstudent 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;
ysql> 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 基础权限
![](https://i-blog.csdnimg.cn/direct/1ac00e7d4b90499b94be449228237de7.png)
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. 触发的动作:指的激发触发程序的语句类型<insert ,update,delete>(插⼊、更新、删除表或数据等)
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命令登录
Welcome to the MySQL monitor. Commands end with ;
or \g.
Your MySQL connection id is 3
Server version: 5.7.18 MySQL Community Server
(GPL)
Copyright (c) 2000, 2017, Oracle and/or its
affiliates. All rights reserve
d.
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 statem
ent.
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
2、MySQL 存储引擎
![](https://i-blog.csdnimg.cn/direct/c199e8ce5a914631908d4fb9b93bcae7.png)
(1)查看数据库使⽤的存储引擎
mysql> use yh; #查看存储引擎时,必须要切换到某个数据库
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
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
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:
*************************** 2. row
***************************
Name: it
Engine: InnoDB #存储引擎
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2023-09-25 17:55:38 #数据表创建
时间
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
*************************** 3. row
***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2023-09-25 10:16:48 #数据表创建
时间
Update_time: 2023-09-26 20:57:20
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
3 rows in set (0.00 sec)
ERROR:
No query specified
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 #存储引擎
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1970324836974591
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2023-09-26 22:28:09 #创建时间
Update_time: 2023-09-26 22:28:09 #更新时间
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
(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 #存储引擎
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2023-09-26 22:30:52 #创建时间
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
(4)修改 MySQL 服务的默认存储引擎
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
default-storage-engine=InnoDB #添加此⾏,当以后再创
建表时,存储引擎将改为InnoDB