例表
employee
id | name | age | d_id |
---|---|---|---|
1 | wch | 20 | 1 |
3 | cy | 20 | 2 |
4 | wxh | 21 | 3 |
5 | wxxx | 19 | 1 |
6 | llr | 25 | 3 |
department
id | name |
---|---|
1 | 人事部 |
2 | 研发部 |
3 | 市场部 |
– 1了解查询
– select *from employee order by d_id ASC ——– 升序
– select *from employee order by d_id DESC ———— 降序
– 2.了解子查询&连接查询&模糊查询
– 2.1 子查询
– SELECT
– employee.*
– FROM
– employee
– WHERE
– employee.d_id = (
– SELECT
– department.id
– FROM
– department
– WHERE
– department.name
= “人事部”
– )
– 3.查出部门为人事部的员工的所有信息,并输出部门的编号(连接查询,写出两种连接方式的语句)
3.1(连接查询)
–SELECT * FROM employee,department WHERE employee.d_id =department.id and department.name=”人事部”
3.2(子查询)
SELECT
employee.*
FROM
employee
WHERE
employee.d_id = (
SELECT
department.id
FROM
department
WHERE
department.name
= “人事部”
)
– 4.查出部门为研发部的所有员工 (同上的思路)
– SELECT * FROM employee,department where employee.d_id=department.id AND department.name=”研发部”
– SELECT
– employee.*
– FROM
– employee
– WHERE
– employee.d_id = (
– SELECT
– department.id
– FROM
– department
– WHERE
– department.name
= “研发部” )
– 5.查出年龄在19到21之间的员工,并输出他们所在的部门
– SELECT *,department.name
FROM department,employee WHERE (department.id =employee.d_id) and (age BETWEEN 19 and 21)
– 6.查出名字中带有w的所有员工
– select employee.* from employee where name like “w%” —– 模糊查找 “%w%”任何位置
– 7.查出名字为3个字符的所有员工
– select employee.* FROM employee WHERE LENGTH( employee.name) = 3 – 查找字符个数 “_”
– 8.查出每个部门下的所有员工,按部门分组(group by)
– SELECT * FROM employee GROUP BY employee.d_id,employee.id – 在单独的表里应用分组
– SELECT * FROM employee,department where employee.d_id =department.id GROUP BY employee.d_id,employee.id
–(分组和聚合函数组合应用)
– SELECT d_id,GROUP_CONCAT(name) FROM employee GROUP BY d_id;
– SELECT name,d_id,count(*) FROM employee GROUP BY d_id
– 9.查出每个部门下年龄超过20的员工 (having)
– SELECT * FROM employee GROUP BY employee.d_id,employee.id HAVING age>=20 —–(having)
– SELECT * FROM employee WHERE employee.age >=20 GROUP BY employee.d_id,employee.id —-其余方法
– 10.找出员工平均年龄大于20岁的部门名 (聚合函数) 例子AVG(age)求平均
– SELECT department.name
,AVG(age) FROM department,employee WHERE department.id =employee.d_id GROUP BY employee.d_id – 我打印出来才知道符合函数的输出处理结果 哈哈哈哈
– SELECT department.name
FROM department,employee WHERE department.id =employee.d_id GROUP BY employee.d_id HAVING AVG(age)>=20 —— HAVING 是用来执行 AVG(age)函数
– 11.找出所有员工中年龄最大的前两位员工
– SELECT *,MAX(age) FROM employee —-最大
不成气候的想法 ——-我想知道怎么找出第二大年龄的所有员工(目前不会)
– where age = (select Max(age) from employee)
– SELECT MAX(select age FROM employee WHERE age<(select Max(age) from employee))
– FROM employee
成功完成题目要求“”先排序 再使用LIMIT 限制条目 ——缺陷假设年龄并列一二名太多人的时候不能都列出来来“” ——求可以解决的博友解决
select * from employee order by age DESC LIMIT 2 —- 限制输出前两个条目 LIMIT 2