数据库查询运用

1、数据的备份还原

• 导出：先对数据库进行备份，然后提取备份文件中的sql语句

• 导入：

  • 方法1：创建和原始数据库同名的数据，然后执行从备份文件中导出的sql语句【此方法不需要修改导出sql语句】

  • 方法2：在数据库中直接运行导出的sql语句【此方法往往需要先修改数据库文件】

测试用的数据库：hellodb

• students：学生表

• classes：班级表

• courses：课程表

• socres：成绩表

• teachers：老师表

• coc：班级和开设的课程的对照关系表

2、简单查询

mysql的查询操作

• 单表查询：也就是从一个表中找数据，也叫做 简单查询

• 多表查询：联合多个表查询数据，也叫做 链接查询

• 联合查询

查询的格式

# 格式1：查看表中的全部的数据
select  *  from  表名 where 条件；
​
# 格式2：查看表中的全部的数据
select  字段名...  from  表名 where 条件；

条件的写法

>   大于
<   小于
<>  不等于
!=  不等于
>=  大于等于
<=  小于等于
is null       是空
is not null   不是空
like          模糊查询
in            判断某个字段是否在给定的集合中
between ... and ...  判断范围

添加写法举例

# age字段大于20
where age > 20
​
# id字段小于等于3
where id <=3
​
# age字段不是15
where age != 15
​
# age字段是15
where age = 15
​
# name字段为空
where name is null
​
# name字段不为空
where name is not null
​
# name字段是以q为开头
where name like "q%"
​
# name字段中包含q
where name like "%q%"
​
# name字段以q为结尾
where name like "%q"
​
# age是3 或者5 或7
where age in (3,5,7)
where age = 3 or age = 5 or age = 7;
​
# age大于等于3且小于等于5
where age>=3 and age<=5
where age between 3 and 5

组合条件的方法

• and 多个条件同时满足

• or 多个条件满足任意一个即可

排序：

• order by 字段 ： 根据指定的字段进行排序

• asc：升序【默认是】

• desc：降序

指定输出几条记录

• 默认输出所有符合条件的记录

• limit 数字

案例：

#1 查询students表中的全部数据
select * from students
​
#查询所有学员的姓名、性别、年龄
select name,age,gender from students;
​
#2 查询students表中的数据，但是仅仅显示姓名和年龄和id
select age,stuid,name from students;
​
#3 查询年龄小于20的用户
select * from students where age<20;
​
#4 查询年龄在20-30之间的用户
select * from students where age>=20 and age<=30;
select * from students where age between 20 and 30;
​
#5 查询年龄是100或者20或者25的用户
select * from students where age=20 or age=100 or age=25;
select * from students where age in (20,100,25);
​
#6 查看姓名是以r为开头的用户
select * from students where name like "r%";
​
#7 将所有的用户按照年龄进行排序
select * from students order by age;
​
#8 将所有的用户按照年龄进行排序，而且用降序排列
select * from students order by age desc;
​
#9 将所有的用户按照id的降序排序
select * from students order by stuid desc;
​
#10 将所有的男性学生按照降序排序
# 第一步：首先找到所有的男性
select * from students where gender='m';
# 第二步：将找到男性按照age进行排序
select * from students where gender='m' order by age;
# 第三步：按照降序排序
select * from students where gender='m' order by age desc;
​
#11 查询姓名是以r为开头或者年龄大于50的用户，结构按照id降序排序
# 第一步：找到符合条件的数据
select * from students where name like "r%" or age>50;
# 第二步：按照id排序
select * from students where name like "r%" or age>50 order by stuid;
# 第三步：实现降序排序
select * from students where name like "r%" or age>50 order by stuid desc;
​
#12 查看年龄最大的用户的姓名
# 第一步：查询所有的用户
select * from students;
# 第二步：让用户按照年龄排序
select * from students order by age;
# 第三步：实现逆序排序【将年龄最大的用户放到第一个】
select * from students order by age desc;
# 第四步：输出第一条记录【此时年龄最大的用户就是第一条记录】
select * from students order by age desc limit 1;
# 第五步：修改语句，指定只显示姓名字段
select name from students order by age desc limit 1;
​
#13 查询年龄最小的用户的姓名
# 第一步：查询所有的用户
select * from students;
# 第二步：让用户按照年龄排序【将年龄最小的用户放到第一个】
select * from students order by age;
# 第三步：输出第一条记录【此时年龄最小的用户就是第一条记录】
select * from students order by age limit 1;
# 第五步：修改语句，指定只显示姓名字段
select name from students order by age limit 1;
​
#14 查询姓名是以r为开头或者年龄大于50的用户中id最大的用户的姓名
#第一步：查询符合条件的用户
select * from students where name like "r%" or age>50;
#第二步：按照id排序
select * from students where name like "r%" or age>50 order by stuid;
#第三步：改为降序
select * from students where name like "r%" or age>50 order by stuid desc;
#第四步：输出第一条记录
select * from students where name like "r%" or age>50 order by stuid desc limit 1;
#第五步：只显示姓名字段
select name from students where name like "r%" or age>50 order by stuid desc limit 1;
​
#15 查询姓名是以r为开头或者年龄大于50的用户中id最大的用户的性别
select gender from students where name like "r%" or age>50 order by stuid desc limit 1;
​
#16 查询姓名是以r为开头或者年龄大于50的用户中id最大的用户的姓名和性别
select name,gender from students where name like "r%" or age>50 order by stuid desc limit 1;

聚合函数

• sum() 求和

• avg() 求平均值

• max() 求最大值

• min() 求最小值

• count() 统计记录条数

#1 计算所有学生的平均年龄
select avg(age) from students;
​
#2 查询显示最大的年龄
select max(age) from students;
# 也可以用如下方法
select age from students order by age desc limit 1;
​
#3 查询显示最小的年龄
select min(age) from students;
# 也可以用如下方法
select age from students order by age limit 1;
​
#4 计算所有学生的年龄之和
select sum(age) from students;
​
#5 计算一共有多少条记录【一条记录就是一个用户】
select count(*) from students;
​
#6 计算年龄大于50的用户个数
select count(*) from students where age>50;
​
#7 计算年龄大于50的用户的年龄和
select sum(*) from students where age>50;
​
#8 计算年龄大于50的用户的平均年龄
select avg(*) from students where age>50;

简单查询的格式

select ... from ... where ... order by limit ...

补充：在update和delete使用条件

#1 将年龄大于50的用户的年龄修改120
# 格式：update  表名 set 字段 = 值 where 条件
update students set age=120 where age>50;
​
#2 将id大于23的用户删除
# 格式：delete from 表名 where 条件;
delete from students where id>23;

去重：去除重复的字段

• distinct

# 显示所有用户的年龄
select age from students
​
# 去除重复的年龄【相同的年龄仅仅显示1个】
select distinct age from students
​
# 将年龄小于50的用户，按照年龄排序，并且去除重复的年龄
select distinct age from students where age<50 order by age;

分组

• group by 字段

分组后加条件

• having 条件

补充内容：建议掌握住

#1 按照性别进行分组，分别统计m 和 f有多少个学员
select count(gender),gender from students group by gender;
​
#2 分别计算男性和女性的平均年龄
select avg(age),gender from students group by gender;
​
#3 统计年龄大于20的学员中，男性和女性分别有多少人
select gender,count(gender) from students where age>20 group by gender;
​
#4 统计各个班级中，分别有多少个学员
select count(classid),classid from students group by classid;
​
#5 统计各个班级中，分别有多少个学员，要求只显示班级人数大于3的班级
select classid,count(classid) from students group by classid having count(classid)>3;

完整的查询格式

select ... from ... where ... order by ... limit ... where ... group by ... having ...