mysql 查询数据 程序_Mysql――数据查询

关键字：

1、简单查询select,from

2、条件查询where,limit

3、排序查询order by desc | asc

4、分组查询group by, having

5、嵌套查询select from (select ...)

6、多表查询inner join, outer join,...

――――――――――――――― ――――――――――――――― ―――――――――

1、简单查询

起别名：列别名，表别名

select first_name as name ,salary from employees as e;

select * from employees e;

――――――――――――――― ――――――――――――――― ―――――――――

2、条件查询：

select first_name, salary hire_date from hr.employees where salary > 10000 limit 10;

limit num 限制数量，排好序

select first_name, salary hire_date 执行顺序：4

from hr.employees 执行顺序：1

where salary > 10000 执行顺序：2

limit 10; 执行顺序：3

where salary between 10000 and 20000;

where salary > 10000 and salary < 20000;

and 可以使用符号 “&&”替换

where salary = 10000 or salary = 20000;

where salary = 10000 || salary = 20000;

where salary in (10000,20000);

or 可以使用符号 “||”替换

select first_name, salary from hr.employees order by salary desc limit 5; 前5条数据

select first_name, salary from hr.employees order by salary desc limit 4, 10;第5到往后10条数据

select first_name, salary from hr.employees order by salary desc limit 2 offset 4;第4条后2条

limit 5 offset 0; 等于 limit 5 等于 limit 0,5;

limit子句通常用于分页查询

取当前页数据：

select * from tb_name

limit (当前页-1)*每页最多显示的据路数，每页最多显示的记录数

假如有limit子句，一定记得，放在所有语句的最后面。最后执行

――――――――――――――― ――――――――――――――― ―――――――――

3、排序查询：

select first_name ,salary from hr.employees order by salary desc;

select first_name ,salary from hr.employees order by 2 desc; 其中select列表中的第2列

select first_name ,salary sal from hr.employees order by sal desc; 其中sal是定义的别名

按工资降序排序，若工资相同，按入职日期升序排序。

select first_name, salary, hire_date from hr.employees order by salary desc, hire_date asc;

――――――――――――――― ――――――――――――――― ―――――――――

4、分组查询：group by

一半放在from子句后面，where后面

假如有having，则一定有group by

说明：子查询一般会应用在where子句，from子句，having子句的后面，

假如是from子句后，需要给这个子查询起一个别名

求每个部门的平均薪水，只显示平均薪水大于5000的

select department_id, avg(salary)

from hr.employees

group by department_id

having avg(salary) > 5000;

求每个部门的平均薪水，只显示平均薪水大于5000的，部门id为空的除外

select department_id, avg(salary)

from hr.employees

where department_id is not null

group by department_id

having avg(salary) > 5000;

select department_id ,count(*), max(salary), min(salary),sum(salary)

from hr.employees

where department_id is not null

group by department_id

order by count(*) desc;

练习:

查询每个工种job_id的平均薪水，只显示平均薪水大于5000的？

select job_id, avg(salary)

from employees

group by job_id

having avg(salary) > 5000;

求每年入职的人数，年份按照降序排序？

select year(hire_date),count(*) 每年入职总人数

from employees

group by year(hire_date)

order by year(hire_date) desc;

求每个部门薪水的最大值，降序排序，取前三名

select department_id, max(salary)

from employees

group by department_id

order by max(salary) desc

limit 3;

求每个部门中薪水最高的那个人的名字？

select department_id,first_name,max(salary)

from employees

group by department_id;

错误，这里的first_name 应该是组内第一个遇到的名字

方法一：不是所有数据库都支持

select department_id, first_name, salary from employees

where (department_id, salary) in (

select department_id,max(salary) from employees

where department_id is not null

group by department_id

);

求人数最多的那个部门的部门名称；

方法一：

select department_name from departments where department_id = (

select department_id from employees group by department_id order by count(*) desc limit 1

);

方法二：

select department_name

from departments

where department_id = (

select department_id

from employees

group by department_id

having count(*)=(

select max(ct)

from (

select count(*) ct

from employees

where department_id is not null

group by department_id)e));

――――――――――――――― ――――――――――――――― ―――――――――

5、嵌套查询：

求比部门编号80平均薪水还要高的部门id以及平均薪水，部门id不为null;

select department_id, avg(salary) from employees

where department_id is not null

group by department_id

having avg(salary) > (

select avg(salary) from employees

where department_id = 80

group by department_id

);

group分组后的限制条件用having

有having，则必有group by。

――――――――――――――― ――――――――――――――― ―――――――――

6、多表查询：

1)、多表查询的应用

当需要的数据来自于多张表时，考虑使用多表查询

select表内存在共有列时，应该在列名前加上表名进行标识区别。

求雇员编号201的员工所在部门的名称？(嵌套)

select department_name from departments where department_id = (

select department_id from employees where employee_id = 201

);

select first_name, e.department_id, department_name

from employees e, departments d

where e.department_id = d.department_id

and e.employee_id = 201;

select first_name, e.department_id, department_name

from employees e inner join departments d

on e.department_id = d.department_id

where e.employee_id = 201;

2)、左连接&右链接

求每个雇员及这个雇员所在的部门信息？

select employee_id, first_name, department_name

from employees e join departments d

on e.department_id = d.department_id;

错误：但是那个department_id是null的员工被遗漏了

select employee_id, first_name, department_name

from employees e left outer join departments d

on e.department_id = d.department_id;

其中”left outer“为左外链接，outer可以省略，

左外链接指的是左边表所有记录都显示，右边表只显示满足条件的。

求每个部门及各个部门中的雇员信息？

select department_name, employee_id, first_name

from employees e right outer join departments d

on e.department_id = d.department_id;

其中”left outer“为右外链接，outer可以省略，

右外链接指的是右边表所有记录都显示，左边表只显示满足条件的。

左外链接 与 右外链接 只要把表的左右相对位置调换其实是一样的。

3)、笛卡尔积(多表查询没写链接条件)

select department_name, employee_id, first_name

from employees e, departments d;

作用：通常借助笛卡尔积的结果作为测试数据用。快速建表

create table temp

as

select department_name, employee_id, first_name

from employees e, departments d;

数据表temp的记录和结构douban来自于as语句后的查询

――――――――――――――― ――――――――――――――― ―――――――――

7、视图(view)

什么是视图？

视图是存储在数据库服务端的一张虚拟的表，视图只有结构，没有数据

所有的数据都是存储在表(table)中

视图的作用？

1、屏蔽外界直接对基表的访问，提高系统安全

2、简化sql语句在程序中的编写过程。

视图的创建？

create view myview1

as

select department_id, max(salary)

from employees

where department_id is not null

group by department_id;

一般不建议修改视图

视图的删除

drop view myview1;

――――――――――――――― ――――――――――――――― ―――――――――

7、索引(Index)数据结构树的理解。

1)、索引是什么？书的大纲

2)、索引的作用 提高查询的效率

数据量比较大，而且经常性的对某些字段进行查询，此时建议建立索引

查询的时候，某些字段出现了大量的null值，建议建立索引

3)、索引的创建

create index index01 on employees (first_name);

索引创建好以后，会在查询时自动生效。(无须显式使用)

使索引失效

alter table tb_name disable key index01;

4)、索引的删除

drop index index01 on employees(first_name);

练习：

1 求薪水和最多的那个部门的部门名称？

嵌套查询：

select department_name from departments where department_id = (

select department_id from employees group by department_id order by sum(salary) desc limit 1

);

方案二：

select department_name

from departments

select department_id

where department_id = (

from employees

group by department_id

having sum(salary) = (

select max(sm)

from (

select sum(salary) sm

from employees

group by department_id

)e));

方案三：

select department_name

from departments d1 join (

select department_id, sum(salary) sm

from employees

group by department_id

having sum(salary) = (

select max(sm)

from (

select sum(salary) sm

from employees

group by department_id

)e))d2

on d1.department_id = d2.department_id;

多表查询：

select department_name

from employees e  right join departments d

on e.department_id = d.department_id

group by d.department_id

order by sum(salary) desc

limit 1;

2 求每个部门中薪水最高的那个人的名字？

select first_name

from employees

where (department_id, salary) in (

select department_id, max(salary)

from employees

group by department_id

);

方案二：

select e1.department_id, first_name, salary

from employees e1 join (

select department_id, max(salary) max_sal

from employees

group by department_id

) e2

on e1.department_id = e2.department_id

where e1.salary = e2.max_sal;

3 求每个部门中薪水最高的前两名雇员的名字？

select department_id, first_name, salary

from employees e1

where department_id is not null

and 2 > (

select count(*)

from employees e2

where e1.department_id = e2.department_id

and e1.salary < e2.salary

);