Mysql从入门到放弃（九）

一、 聚合函数

1. count 统计个数
2. max 最大值
3. min 最小值
4. sun 求和
5. avg 求平均数

select count(*) as count,max(age),min(age),avg(age),sum(age) from user;

二、 group by 分组

主体结构

select count(字段) from 表名 group by 字段

统计 男生和女生分别有多少人

select sex,count(*) from user group by sex;

统计每班有多少人

select class,count(*) from user group by class;

统计 每班的男生和女生分别有多少人

select class,sex,count(*) as count from user group by class,sex;

having 分组的条件的使用 相当于 where

查询人数大于2人的班级

select class,count(*) as count from user group by class having count>2;

查询班级为 3班和4班的人数

select class,count(*) as count from user group by class having class in('onlin3','onlin4');

查询班级为3班和4班的人数 并且人数大于2人

select class,count(*) as count from user group by class having class in('onlin3','onlin4') and count >2;

三、多表联查

表结构：

user表

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

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

|+----------+-------------+------+-----+-----------------------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| sex | tinyint(4) | NO | | 1 | |

| username | varchar(20) | YES | | NULL | |

| age | tinyint(4) | NO | | 18 | |

| userinfo | varchar(50) | NO | | 我是帅气的lucky老师啊 | |

| class | varchar(20) | NO | | onlin4 |

|+----------+-------------+------+-----+-----------------------+----------------+

adddress表

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

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

|+---------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| uid | int(11) | NO | | NULL | |

| address | varchar(255) | NO | | NULL | |

| code | varchar(255) | NO | | NULL |

|+---------+------------------+------+-----+---------+----------------+

主体结构：

select * from 表名1,表名2... where 表名1.字段名=表名2.字段名 and ...

查询id为1的用户名和地址

SELECT
  *
FROM
  USER,
  address
WHERE
  `user`.id = address.uid
AND `user`.id = 1

获取某个字段的值和起别名

SELECT
  u.username,a.address
FROM
  USER u,
  address a
WHERE
  u.id = a.uid
AND u.id = 1

(2) 显示内连接 inner join

主体结构：

select * from 表1 inner join 表2 on 条件;

查询id为1的用户名和地址

实例：

SELECT
  u.username,a.address
FROM
  USER u inner join
  address a
on
  u.id = a.uid
AND u.id = 1

(3) 左连接 left join

主体结构：

select * from 表1 left join 表2 on 条件;

查询id为1的用户名和地址

实例：

SELECT
  u.username,a.address
FROM
  USER u left join
  address a
on
  u.id = a.uid

(4) 右连接 right join

主体结构：

select * from 表1 left join 表2 on 条件;

查询id为1的用户名和地址

实例：

SELECT
  u.username,a.address
FROM
  USER u right join
  address a
on
  u.id = a.uid

注意：

1. 显示内连接和隐示内连接是相同的 会将俩端匹配的数据查询出来
2. 左连接 会以左表为主表 右表为辅表 将主表的数据全部查询出来 辅表的数据没有的用null来占位
3. 右连接 会以右表为主表 左表为辅表 将主表的数据全部查询出来 辅表的数据没有的用null来占位

四、其它操作

(1) 修改密码

set password for 用户名@localhost=password('用户名');

(2) 创建其它用户分配权限

1. 使用MySQL库
2. use mysql
3. 查看当前库下有哪些用户
4. select user from user;
5. 创建用户
6. create user 用户名 identified by '密码' create user lucky identified by '123456';
7. 赋予权限 grant all on lucky.* to 用户名 all权限代表增删查 grant insert,select on lucky.* to lucky;
8. 回收权限 revoke all on lucky.* from lucky;
9. 修改用户名 rename user lucky to zhangsan;
10. 删除用户 drop user zhangsan;
11. 刷新 flush privileges

五、触发器

概述：

触发器：它是一个特殊的存储过程 他是MySQL在 insert update delete 的时候执行 自动执行 不能直接调用

它包含四个要素

1. 监事地点(table)
2. 监事时间(insert/update/delete)
3. 触发时间(after/before)
4. 触发时间(insert/update/delete)

主体结构：

create trigger trigger_name
after/before insert/update/delete on table_name
for each row
begin
sql 语句(触发的语句一句或多句)
end;

创建俩张表 商品表goods和订单表order 来说明触发器的使用实例

表结构：

goods表

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

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

| goods_id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| goods_name | varchar(255) | NO | | NULL | |

| goods_num | int(11) | NO | | NULL | |

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

order表

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

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

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

| order_id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| goods_id | int(11) | YES | | NULL | |

| order_num | int(11) | YES | | NULL | |

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

将goods表添加四条数据

insert into goods(goods_name,goods_num) values('手机',20),('电脑',30),('单反',20);

(1) 实现购买任意商品 对应的商品数量响应的减少

分析：

监事地点：order表

监事事件: insert 操作

触发时间：insert之后

触发事件：update操作

实例：

create trigger t1
after insert on `order`
for each row
begin
update goods set goods_num=goods_num-new.order_num where goods_id=new.goods_id;
end;

注意：

new代表新增的一行的数据 行中每一列的值用new.列名来表示

(2) 购买5个手机

insert into order(goods_id,order_num) values(1,5);

再去查询会发现商品的数量发生了更改 自动减少

(3) 撤销订单

分析：

监事地点：order表

监视事件：delete操作

出发时间 : 在delete 操作之后

触发事件:update 操作

drop trigger if exists t1;
create trigger t1
after delete on `order`
for each row
begin
update goods set goods_num=goods_num+old.order_num where goods_id=old.goods_id;
end;

取消刚才买的5个手机的订单

delete from order where order_id=1;

当删除以后会发现 商品的数量将删除的订单的数量进行累加