基于Mysql下SQL语言的入门应用例子（数据库学习记录1）

例表

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

学习记录内容贡献：

sql指导老师 薄峰 博主主页：>http://blog.csdn.net/wch873

sql语言语法参考：>http://blog.csdn.net/a88055517/article/details/6736284