MySQL进阶

一、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 database test default character set=utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed

新建⼀个学生表，表头有：序号，数字形式；姓名，文本字符串形式；年龄，数字形式；班级，数字形式。
mysql> create table 学生表(序号 int,姓名 char(16),年龄 int,班级 int);
Query OK, 0 rows affected (0.02 sec)

 向学生表插入信息 

mysql> insert into 学生表 values(1,'tom',15,101);
Query OK, 1 row affected (0.02 sec)

mysql> insert into 学生表 values(2,'mike',16,102);
Query OK, 1 row affected (0.00 sec)

mysql> insert into 学生表 values(3,'john',16,102);
Query OK, 1 row affected (0.00 sec)

mysql> insert into 学生表 values(4,'lili',15,103);
Query OK, 1 row affected (0.00 sec)

mysql> insert into 学生表 values(5,'jack',15,103);
Query OK, 1 row affected (0.01 sec)

mysql> insert into 学生表 values(6,'xiaoming',16,101);
Query OK, 1 row affected (0.00 sec)

查看表内学生信息 

 

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

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

 

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

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

“平均年龄”：这个是⾃⼰临时命名的表头名，也可以不写 

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

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

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

也可以⽤count统计函数计算每个班的总⼈数 #这些count、avg、max、min、sum就是聚合函数，分组查询必须 有这些函数的其⼀。

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

 三、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, 1 warning (0.01 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, 3 warnings (0.02 sec)

向班级表插入数据 

mysql> insert into class values(1001, 'Java');
Query OK, 1 row affected (0.00 sec)

mysql> insert into class values(1002, 'C++');
Query OK, 1 row affected (0.00 sec)

mysql> insert into class values(1003, 'Python');
Query OK, 1 row affected (0.01 sec)

mysql> insert into class values(1004, 'PHP');
Query OK, 1 row affected (0.00 sec)

mysql> insert into class values(1005, 'Android');
Query OK, 1 row affected (0.00 sec)

 向学生表插入数据

mysql> insert into student values(1, '张三',20,1001);
Query OK, 1 row affected (0.00 sec)
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);

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

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

2.exists 关键字的子查询 

 exists 关键字后面的参数可以是任意一个子查询， 它不产生任何数据只返回 true 或 false。⽽当返回值为 true 时外层查询才会执行。相当于内层句子是一个判断句式。

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

mysql> select * from class where exists (select * from student where sname='王五');

 四、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相乘 

两个表交叉连接，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;

五、MySQL 授权

1.MySQL基础权限

2.权限语法 

为客户机授权：

grant all on 库.* to 用户@客户机地址 identified by '密 码';all：表示所有权限，可替换为其他权限库.*：表示某个数 据库下所有数据表用户@客户机地址：表示希望给予哪个用户权限，前面的用户是临时自建用户，当客户机登录时，用该用户登录'密码'：临时自建用户的密码，也是临时自写的

客户机地址可以有以下表现形式： %：表示所有地址 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)