MySQL

⼀、MySQL 函数 数据分析的基础

1. 排序

1. max 2. min
2. 汇总 1. count 2. sum
3. avg
4. 数制

1. ⼆进制 1. 0 0 2. 1 1 3. 2 10 4. 3 11 5. 4 100 6.
2. ⼋进制
3. ⼗进制
4. ⼗六进制
5. AA 2

1. 聚合函数

只有 select ⼦句和 having ⼦句、order by ⼦句中能使⽤聚合函 数，where ⼦句不能使⽤聚合函数。当使⽤聚合查询以后，不能 使⽤where条件，如果要添加条件，就使⽤having

1. 其他常⽤函数

1. ⽇期函数
2. 数字函数
3. 字符串函数

⼆、MySQL 分组查询

语法： select 聚合函数(表头名 1),表头名 2 from 数据表名 group by 表 头名; select 聚合函数(表头名) 临时表头名,真实表头名 from 数据表名 group by 真实表头名; 若⽤ group by 分组查询语句，必须加⼊聚合函数，否则报错 （踩坑）

下⾯实例：

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

mysql> desc 学⽣表;

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 学⽣表;

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

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

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

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

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

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));

# 创建班级表

mysql> create table student (sid int(4) not null primary key, sname varchar (20), sage int (2), classid int (4) not null);

# 创建学⽣表

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='张三');

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

mysql> select * from class where cid>(select classid from student where sname='张三');

1. 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;

mysql> select * from student cross join class;

1. 多表联合查询 语法：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 test.user

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

%：表示所有地址

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

mysql> grant all on *.* to haha@'192.168.100.%' identified by '123456'; # 为haha⽤户进⾏授权

mysql> flush privileges;

# 刷新权限

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

# 查看 haha的权限

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

• MySQL 触发器

概念 触发器是⼀种特殊的存储过程，它在插⼊，删除或修改特定表中 的数据时触发执⾏，它⽐数据库本身标准的功能有更精细和更复 杂的数据控制能⼒ 1. 例如在某⼀个时间触发什么事情

1. 例如不允许股票价格的升幅⼀次超过%10 3. 审计功能，某⼀个⼈登录会记录所有的操作 2、触发器语法 create trigger 触发器名称 触发的时机 触发的动作 on 表名 for each row 触发器状态 1. 语法的红字为固定格式 2. 触发器名称：⾃定义
2. 触发的时机：before(之前) 或 after(之后)，在执⾏动作之前还是 之后
3. 触发的动作：指的激发触发程序的语句类型（插⼊、更新、删除表或数据等） 5. each row：操作的每⼀⾏都会被监控 6. 触发器状态：在触发的动作之前或之后做什么事情，⽐如当我删 了表 1 的某条数据后，⾃动清空表 2。

1. 触发器创建的四要素 （1）监视地点：table（表） （2）监视事件：insert（插⼊）、update（更新）、delete（删除） 等动作 （3）触发时间：before（之前）、after（之后） （4）触发事件：在监视事件之前或之后，对当前表或其他表的插 ⼊、更新、删除等动作
2. 触发器实例

#查看数据库内的所有表

mysql> show tables;

• 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> select Host,user,authentication_string from user; #这条命令意为：算法对账户明⽂密码加密后的字符串，不⽤看，看 不懂，没什么⽤。

1. MySQL 存储引擎

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

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

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

1. 创建表并指定存储引擎

mysql> create table haha(id int) engine=MyISAM; #创建表haha，并添加数字形式的表头id，指定haha表的存储引擎 为MyISAM

1. 修改已存在数据表的存储引擎

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

1. 修改 MySQL 服务的默认存储引擎

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

[mysqld]

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