数据库高级查询2

分组查询(group by)

分组查询:

1.查询每个部门的最高工资

select deptno,max(sal) from emp group by deptno

2.查询每个职位的平均工资

select deptno,avg(sal) from emp group by deptno

3.查询每个部门的人数

select deptno,count(*) from emp group by deptno

4.查询工资大于1000的员工,每个部门的最大工资

select deptno,max(sal) from emp where sal>1000 group by deptno

多字段分组查询:group by 字段1,字段2

1.查询每个部门下每个主管的手下人数

select deptno, mgr, count(*) from emp where mgr is not null group by deptno,mgr

2.查询emp表中每个部门的编号,人数,工资总和,最后根据人数进行升序排序,如果人数一致,根据工资总和降序排序

select deptno,count(*),sum(sal) from emp group by deptno order by count(*) asc,sum(sal) desc

3.查询工资在1000-3000之间的员工信息,每个部门的编号,平均工资,最低工资,最高工资,根据平均工资进行升序排序排列

select deptno,avg(sal),min(sal),max(sal) from emp where sal between 1000 and 3000 group by deptno order by avg(sal)

4.查询含有上级领导的员工,每个职业的人数,工资的总和,平均工资,最低工资,最后根据人数进行降序排列,如果人数一致,根据平均工资进行升序排列

select job, count(*),avg(sal),min(sal) from emp where mgr is not null group by job order by count(*) desc,avg(sal) asc

各种关键字的顺序

select * from 表名  where .... group...having... order by... limit...

    having(结合group by使用)

having一般要结合分组查询和聚合函数使用,用于给聚合函数的内容添加条件

聚合函数的条件不能写在where后面

普通字段的条件写在where后面,聚合函数的条件写在having后面

1.查询每个部门的平均工资,要求平均工资大于2000(c是别名的用法)

select deptno,avg(sal) c from emp group by deptno having c >2000

2.查询每个分类的平均单价,要求平均单价低于100

select category_id ,avg(price) a from t_item group by category_id having a<100

3.查询category_id分类为238和917的两个分类的平均单价

select category_id,avg(price) from t_item where category_id in(238,917) group by category_id

4.查询emp表中每个部门的平均工资高于2000的部门编号,部门人数,平均工资,最后根据平均工资降序排列

select deptno,count(*),avg(sal) a from emp group by deptno having a>2000 order by a desc

5.查询emp表中工资在1000-3000之间的员工,每个部门编号,工资总和,平均工资,过滤掉平均工资低于2000的部门,按照平均工资进行升序排序

select deptno,sum(sal), avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000 order by a asc

6.查询emp表中每年入职的人数

select extract(year from hiredate) year,count(*) from emp group by year

7.查询每个部门的最高平均工资

select deptno,avg(sal) from emp group by deptno order by avg(sal) limit 0,1

子查询(嵌套查询)

子查询可以写在where或having后面当做查询条件的值

写在from后面,当做一张新表(但是必须要有别名)

select ename from (select * from emp where sal>1000) newtable

写在创建表的时候

create table emp_20 as (select * from emp where deptno=20)

1.查询emp表中工资最高的员工信息

select * from emp where sal=(select max(sal) from emp)

2.查询emp表中工资大于平均工资的所有员工的信息

select * from emp where sal>(select avg(sal) from emp)

3.查询工资高于20号部门最大工资的员工信息

select * from emp where sal>(select max(sal) from emp where deptno=20)

4.查询工资高于20号部门最大工资的员工信息

select * from emp where sal>(select avg(sal) from emp)

5.查询和Jones相同工资的其他员工信息

select * from emp where job=(select job from emp where ename='jones' and ename!='jones')

6.查询工资最低的员工的同事们的信息(同事=相同job)

select * from  emp where job=(select job from emp where sal=(select min(sal) from emp)) and sal !=(select min(sal) from emp)

7.查询最晚入职的员工信息

select * from emp where hiredate=(select max(hiredate) from emp)

8.查询名字为King的部门编号和部门名称(需要用到dept表)

select deptno,dname from dept where deptno=(select deptno from emp where ename='king')

9.查询有员工的部门信息(编号和名称)

select deptno ,dname from dept where deptno in (select distinct deptno from emp)

10.查询平均工资最高的部门信息

select * from dept where deptno in (select deptno from emp group by deptno having avg(sal)=(select avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1))

关联查询

同时查询多张表的数据称为关联查询

1.查询每一个员工的名称和其对应的部门名称

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno

2.查询在new york工作的所有员工的信息

select  e.* from emp  e,dept  d  where e.deptno=d.deptno and  d.loc='new york'

笛卡尔积

如果关联查询不写关联关系则查询到的数据是两张表的乘积,这个乘积称为笛卡尔积,笛卡尔是一种错误查询方式的结果,工作切记不要出现.

    等值连接和内连接

等值连接:

select * from A,B where A.x=B.x and A.age=18

内连接:

select * from A join B on A.x=B.x where A.age=18(将关联关系写在on后面)

1.查询每个员工的名称和其对应的部门名称

select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno

外连接

使用外连接查询得到的数据层除了两张表的交集数据以外和另外一张主表的全部数据,哪个表为主表通过left/rigth控制, left以join左边表为主表 rigth以join右边表为主表

1.查询所有员工的名称和其对应的部门名称

select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno

1、首先在mysql数据库，创建一张data表，表内插入多条数据，用于测试。

2、创建一个test.php文件，在文件内，使用header()方法将页面的编码格式设置为utf-8。

3、然后在test.php文件内，连接mysql数据库，并使用mysqli_select_db选择要 *** 作的数据库。

4、在test.php文件内，创建一条查询data数据表所有数据的sql语句，再使用mysqli_query执行sql语句。

5、在test.php文件内，通过while语句，使用mysqli_fetch_assoc函数将结果集数据转换为数组形式，并通过echo输出每一条记录的username值。

6、最后在浏览器打开test.php文件，查看结果，如下图所示就完成了。

create table 图书 (总编号 Char(6),分类号 Char(8),书名 Char (16),作者 Char (6),出版单位 Char (20),单价 Numeric(6,2))

create table 读者(借书证号 Char (4),单位 Char (8),姓名 Char (6),性别 Char (2),职称 Char (6),地址 Char (20))

create table 借阅(借书证号 Char (4),总编号 Char (6),借书日期 Datetime)

1. 对图书管理数据库，将借阅表增加新字段 ：应还日期 datetime 。请对下面的SQL语句填空：

ALTER TABLE 读者 add 应还日期 datetime

2. 向图书表插入一条记录，总编号为101002，分类号为02，书名为数据库技术，作者为王大为，出版单位为电子工业出版社，单价为23.5。

INSERT INTO 图书 VALUES('101002','02','数据库技术','王大为','电子工业出版社',23.5)

3. 对于图书管理数据库，将图书表中电子工业出版社的图书的单价涨价10%。请对下面的SQL语句填空：

UPDATE 图书 SET 单价=单价*1.1 WHERE 出版单位='电子工业出版社'

4. 对于图书管理数据库，要查询所藏图书中每个出版社的图书最高单价、平均单价。

SELECT 出版单位,MAX(单价) AS 最高单价,AVG(单价) AS 平均单价 FROM 图书 GROUP BY 出版单位

5．对于图书管理数据库，求电子工业出版社出版图书的最高单价和平均单价。

SELECT 出版单位,MAX(单价) AS 最高单价,AVG(单价) AS 平均单价 FROM 图书 where 出版单位='电子工业出版社'

6．使用SQL语句从上表中查询所有姓刘的读者的信息：

SELECT * FROM 读者 WHERE 姓名 like '刘%'

7． 检索书价在10元至30元(含10元和30元)之间的图书的书名、作者、书价和分类号，结果按分类号升序排序。

SELECT 书名,作者,单价,分类号 FROM 图书

WHERE 单价 between 10 and 30

ORDER BY 分类号

8. 对于图书管理数据库，查询借阅图书超过3本的每个读者的借书证号和所借图书册数。请对下面的SQL语句填空：

SELECT 借书证号,count(*) as 借图书册数 FROM 借阅

GROUP BY 借书证号 having count(*)>3

9. 对于图书管理数据库，查询电子工业出版社的图书信息，检索结果按书价降序排列。

SELECT * FROM 图书 WHERE 出版单位='电子工业出版社' ORDER BY 单价 DESC

10. 对于图书管理数据库，查询所藏图书中，有两种及两种以上的图书出版社所出版图书的最高单价和平均价。

SELECT 出版单位,MAX(单价) AS 最高单价,AVG(单价)AS 平均价 FROM 图书 GROUP BY 出版单位 HAVING count(*) >=2

---