mysql查询案例

1.查询last_name中第三个字母是a的员工工号，姓，名

分析:第三个字母是a,因此需要使用匹配单个字符的通配符(_)来匹配前两个字符,另外还需要使用匹配任意个字符的统配符(%)来匹配其他字符.

SELECT id , last_name , first name
FROM s_emp
WHERE lower(last_name) LIKE '__a%'
-- 注意,有些数据库(例如Oracle)是严格区分大小写的,因此需要使用大小写转换函数.若后面用小写,前面就用lower().若后面大写,就用upper() 

2.统计每个部门的最高工资,最低工资,工资总和,平均工资,保留到整位数.

分析：因为是每个部门，部门有很多种，所以要用到GROUP BY来进行分组查询。

SELECT dept_id 部门号，
       round(max(salary)) 最高工资，
       round(min(salary)) 最低工资，
       round(sum(salary)) 工资总和，
       round(avg(salary)) 平均工资，
FROM s_emp
GROUP BY dept_id;
-- 注意在SELECT 后面出现的非组函数一定要在GROUP BY之后一定要出现。

3.统计平均工资高于2000的部门

列出部门编号、部门名称、以及部门平均工资（保留到整数）

SELECT d.id 部门号,
       d.name 部门名称,
       round(avg(e.salary)) 平均工资
FROM s_dept d JOIN s_emp e ON e.dept_id = d.id
GROUP BY d.id
HAVING avg(e.salary) > 2000 ;

-- SQL查询语句的写法：
1.SELECT...     确定结果集中包含那些列
2.FROM...       确定查询那张表
3.JOIN...AN...  确定与那张表连接以及连接的条件是什么
4.WHERE...      对数据进行过滤（有查询连接是表示连接后的结果）
5.GROUP BY...   确定数据分组条件
6.HAVING...     对分组后的数据进行筛选
7.ORDER BY...    多数据进行排序
--SQL的执行顺序
2.FROM
3.JOIN...ON...
4.WHERE...
5.GROUP BY ...
6.HAVING...
1.SELECT...
7.ORDER BY...

4.统计员工工号、姓名及其直接上司的工号、姓名

员工的上司也是员工，也在员工表中， 可以将s_emp表当两张表使用，给表取两个别名

SELECT e.id 员工工号, concat(e.first_name , e.last_name) 员工姓名 ,
       m.id 员工工号, concat(m.first_name , m.last_name) 老板姓名 ,
FROM s_emp e JOIN s_emp m ON e.manager_id = m.id;
-- s_emp e 是把s_emp表当作员工表来用，取别名为e
-- s_emp m 是把s_emp表当作上司表来用，取别名为m

5.统计每个员工的工号、姓名、部门编号、部门名称、部门所在地区

设计三张表的链接

SELECT e.id , e.first_name ,d.id , d.name , r.id , r,name
FROM s_emp e
JOIN s_dept d ON e.dept_id=d.id
JOIN s_region r ON d.region_id = r.id ;
-- 把第一次JOIN ON 的结果当场一张表，再次有第三张表去JOIN ON .就算有多种表，也可以如此实现。

6.查询每个学生每门课程的成绩，列出学生姓名，课程名称，考试时间，考试成绩

在做这种查询的时候，先分析表，了解到从哪个表可以连接到那个表，找出主表，从表，子表的关系。

SELECT s.name , c.name , sc.exam_time , sc.score
FROM t_student s
JOIN t_scores sc ON sc.sid = s.id
JOIN t_courses c ON sc.cid = c.id;
-- 表，列的命名要简单使用易懂。

7.统计所有男生每门课的平均成绩，列出课程名，平均成绩

先查询到所有男生，然后根据课程来分组。

SELECT c,name , avg ( sc.score )
FROM t_students s
JOIN t_scores sc ON sc.sid = s.id
JOIN t_courses c ON sc.cid = c.id
WHERE s.gender = '男'
GROUP BY c.name;

8.查询没有学生选秀的课程信息，列出编号。名称，学分

• 首先找出成绩表中存在的课程编号列表

  distinct 的作用是去重

  SELECT DISTINCT cid FROM t_scores;

• 再找出课程表中不存在于之前列表中的课程

• SELECT * FROM t_courses WHERE id NOT IN (1601,1602,1603,1604);

• 将NOT IN之后的列表更换为子查询

  SELECT * FROM t_courses
  WHERE id NOT IN （SELECT DISTINCT cid FROM t_scores ）;