mysql数据库之查询

目录

• 查询关键字
  • 查询关键字之having过滤
  • 查询关键字之distinct去重
  • 查询关键字之order by排序
  • 查询关键字之limit分页
  • 查询关键字之regexp正则
• 多表查询思路
  • 子查询
  • 连表操作
    • 四个连表关键字
• 可视化软件navicat
• 多表查询练习题
  • 查询所有的课程的名称以及对应的任课老师姓名
  • 查询平均成绩大于八十分的同学的姓名和平均成绩
  • 查询没有报朱韦老师课的学生姓名
  • 查询没有同时选修物理课程和体育课程的学生姓名
  • 查询挂科超过两门(包括两门)的学生姓名和班级

查询关键字

查询关键字之having过滤

# 区别
  where用在分组之前的筛选
  having用在分组之后的筛选
把where叫做筛选，having叫做过滤是为了方便区分

# 统计每个部门年龄在28岁以上的员工的平均薪资并且保留平均薪资大于10000的部门
# 1.先获取每个部门年龄在28岁以上的员工的平均薪资
  先筛选出28岁以上的员工数据，然后再对数据进行分组
  select position,avg(salary) from pythonschool where age>28 group by position;
# 2.在过滤出平均薪资大于9000的数据
  针对分组之后的数据第二次筛选，需要使用having过滤出平均薪资大于9000的部门
  select position,avg(salary) from pythonschool 
  	where age>28 
    group by position
    having avg(salary) > 9000
    ;
# 聚合函数起别名，方便在其他地方作为条件使用
  select position,avg(salary) as avg_salary from pythonschool 
  	where age>28 
    group by position
    having avg_salary > 9000
    ;

查询关键字之distinct去重

# 去重的前提，数据必须完全相等并且不能带有主键
select distinct department_director,group_concat('编号',id,'姓名',name) from pythonschool group by department_director;

查询关键字之order by排序

# 按照年龄高低排序
select * from pythonschool order by age;  # 默认是升序
select * from pythonschool order by age asc;  # 升序关键字asc可以省略 
select * from pythonschool order by age desc;  # 降序
# 先按照年龄降序排序,如年龄相同,再按照薪资降序排序
	select * from pythonschool order by age desc,salary desc;
# 统计各岗位年龄在25岁以上的员工平均工资,并保留平均工资大于7000的岗位并按照从小到大的顺序排序
select position,avg(salary) as avg_salary from pythonschool 
where age > 25 
group by position
having avg_salary > 7000
order by avg_salary asc;

查询关键字之limit分页

# 分页即限制展示条数
# 限制只展示八条数据
	select * from pythonschool limit 8;
# 分页
	select * from pythonschool limit 8,16;
# 查询工资最高的三人的详细信息
	select * from pythonschool order by salary desc limit 3;

查询关键字之regexp正则

select * from pythonschool where name regexp '^(D|T).*(c|m)$';

多表查询思路

子查询

# 使用子查询获取dominic所在的部门名称
# 1.先获取dominic的部门编号
  select department_id from pythonschool where name='dominic';
# 2.将结果加括号作为查询条件
  select department_name from maindepartment where id=(select department_id from pythonschool where name='dominic');

连表操作

四个连表关键字

# 连表操作有四个关键字
inner join		内连接
  select * from pythonschool inner join maindepartment on pythonschool.department_id=maindepartment.id;
'''只连接两张表中有对应关系的数据'''
left join		左连接
  select * from pythonschool left join maindepartment on pythonschool.department_id=maindepartment.id;
'''以左表为基准,展示所有的数据;没有对应项则用NULL填充'''
right join		右连接
  select * from pythonschool right join maindepartment on pythonschool.department_id=maindepartment.id;
'''以右表为基准,展示所有的数据;没有对应项则用NULL填充'''
union		        全连接
  select * from pythonschool left join maindepartment on pythonschool.department_id=maindepartment.id
union
  select * from pythonschool right join maindepartment on pythonschool.department_id=maindepartment.id;
'''左右两表数据全部展示,没有对应项则用NULL填充'''

  select maindepartment.department_name from pythonschool 
  inner join maindepartment on pythonschool.department_id=maindepartment.id
  where pythonschool.name='dominic';

可视化软件navicat

Navicat可以充当很多数据库软件的客户端，提供了图形化界面能够更加方便地操作数据库
# 下载
  官网地址:https://www.navicat.com/en/
  注意：正版仅可免费体验14天 
# 版本
  navicat有很多版本  
  针对图形化软件，版本越新越好
# 使用
  内部封装了SQL语句，用户只需使用鼠标就可以完成一系列操作
  使用navicat编写SQL，tab键自动补全语句
#  SQL语句注释语法
  按键：ctrl+?
  或者：
  # 
  --
# 运行SQL文件
可导入本地sql文件

多表查询练习题

查询所有的课程的名称以及对应的任课老师姓名

SELECT
	course.cname,
	teacher.tname 
FROM
	teacher
	INNER JOIN course 
WHERE
	teacher.tid = course.cid;

查询平均成绩大于八十分的同学的姓名和平均成绩

SELECT
	student.sname,
	t1.avg_num 
FROM
	student
	INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY score.student_id HAVING avg_num > 80 ) AS t1 
WHERE
	t1.student_id = student.sid;

查询没有报朱韦老师课的学生姓名

select student.sname from student where sid not in (select score.student_id from score inner join (select course.cid from course inner join (select tid from teacher where tname='朱韦老师') as t1 where t1.tid=course.teacher_id) as t2 where t2.cid = score.course_id);

查询没有同时选修物理课程和体育课程的学生姓名

SELECT
	sname 
FROM
	student 
WHERE
	sid IN (
	SELECT
		student_id 
	FROM
		score 
	WHERE
		course_id IN ( SELECT cid FROM course WHERE cname IN ( '自然', '体育' ) ) 
	GROUP BY
		student_id 
	HAVING
	count( course_id ) = 1 
	)

查询挂科超过两门(包括两门)的学生姓名和班级

SELECT
	student.sname,
	class.caption 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 );