MySQL末节

1，聚合函数

(1)sum() 求和

avg() 求平均值

max() 求最大值

min() 求最小值

count() 统计记录条数

案例：

#计算所有学生男生的年龄和
select sum(age) from students where gender="F"
#查询年龄最小的学生信息
select min(age) from students
select * from students order by age limit 1
#统计女生有多少人
select count(stuid) from students where gender="M"
#计算年龄大于50的用户个数
select count(stuid) from students where age>50

(2)去重：去除重复的字段

distinct

案例

select distinct age from students

(3)分组：

group by

案例：
select count(*),classid from students group by classid
按照班级分组，并且统计各个班级有多少人

分组后加条件

having

格式：select 字段 from 表名 group by 字段 having 条件

2,查询进阶

链接查询

将两张或多张表联合起来进行查询，这这种查询叫链接查询

交叉链接

表中每一行，分别和其他表中的每一行，组成一个新的行

新表的行数是两个表的行数相乘，列数是俩张表的列数相加

---

select *from students，classes

---

表中存在着大量的无用数据

自然链接

将多个表中，符合条件的进行链接

在交叉链接的基础上，通过加条件，可以得到自然链接

---

select *from students,classes where students.classid=classes=classid

---

字段和表别名

格式：

select 字段名 as 新名字 from 表名 ...

案例
select name as name1 from students where name="zhao he"

select 字段名 from 表名 as 新名字 ...

案例
select name, c.classid from students as s,classes as c where s.classid=c.classid

使用自然链接存在的问题：

会导致数据缺失

解决方法：用左外连接、右外连接

左表：前面的

右表：后面的

左外链接

显示结果以左表为准

左表中的数据会全部出现，右表中的数据，有就显示，没有的显示为空

格式：

select *from 左表 left join 右表 on 左表.条件=右表.条件

右外链接

显示结果以右表为准

右表中的数据会全部出现，左表中的数据，有就显示，没有的显示为空

格式：

select *from 左表 right join 右表 on 左表.条件=右表.条件

子查询

在查询中，使用另外一个语句的查询结果

案例：查询所有年龄大于平均年龄的学生

select name,age from students where age>(select avg(age) from students )

3,数据库设计方法

ER 模型：表和表的关联关系

1. 主键：是用于在表中唯一的标识一个字段
2. 外键：外键是另外一个表主键，通过使用外键，可以将两个表关联起来   

关系的类型别：

• 一对一
• 一对多
• 多对多

案例：

create table tb2( 
classid int primary key, 
name char(30)) 

create table tb1( 
id int primary key, 
name char(30), 
classid int, 
foreign key(classid) references tb2(classid)) 

视图：

视图： view

• 视图可以认为是一张表
• 但是这表表存在内存中的虚表，断电后会消失

作用：

通过视图，可以让用户只能访问数据库中一部分数据（给用户授权）

格式：

create view 视图名 as select 语句

案例：

#创建视图
create view new1 as select * from students where stuid<10

#授权给用户指定的数据
grant all on hellodb.new1 to "jerry"@"localhost" identified by "123456"

存储过程：

作用：快速向数据库写入大量的测试数据

定义存储过程格式：

delimiter // 
create procedure 存储过程名()
begin
sql语句
end //

调用 / 使用存储过程

call 存储过程名()

案例：

delimiter // 
create procedure m1() 
begin 
declare i int;
set i = 0; 
while i<1000 do 
insert into tb1(name) values(i);

set i = i + 1; 
end while; 
end // 
delimiter;
call ()

事物：

• 将多个sql语句组合成一个整体，这个整体就是一个事物
• 事物中的所有语句要么都执行，要么都不执行
• 事务中的语句，如果只有部分执行成功，那么就需要将这个事物进行回滚操作

回滚：将数据恢复到执行之前的状态

通过使用事物，可以保证数据的一致性

事物的操作：

提交事物

回滚事物

提交事物：

让事物中的 sql 语句，真正的生效

如果使用了事物，那么在事物提交之前，其他用户是看不到事务中的操作的

操作的命令格式：

begin #启动事务
commit #提交事务，提交之后，事务自动结束
rollback #回滚事务，回滚之后，事务自动结束