`

[zz] MySQL SQL语句练习

 
阅读更多

MySQL  SQL语句练习:


1.建库建表

--create database company

create database company;

use company;

--create tables

--部门表
create table dept
(
deptno int(3) primary key,
dname varchar(14),
loc varchar(13)
);

--雇员表
create table emp
(
empno int(4) not null primary key,
ename varchar(10),
job varchar(10),
mgr int(4),
hiredate datetime,
sal double,
comm double,
deptno int(3),
foreign key(deptno) references dept(deptno)
);

--工资级别表
create table salgrade
(
grade int(3) primary key,
losal int(3),
hisal int(3)
); 

----------------------------------------------------------------------------------------------------------------------------------------

2.插入数据(进行初始化)

use company;

--往部门表中查数据
insert into dept values(10,'Accounting','New York');
insert into dept values(20,'Research','Dallas');
insert into dept values(30,'Sales','Chicago');
insert into dept values(40,'Operations','Boston');
insert into dept values(50,'Admin','Washing');

--往雇员表中插数据
insert into emp values(7369,'Smith','Clerk',7902,'1980-12-17',800,0,20);
insert into emp values(7499,'Allen','Salesman',7698,'1981-2-20',1600,300,30);
insert into emp values(7844,'Turner','Salesman',7499,'1981-9-8',1500,0,30);
insert into emp values(7698,'Tom','Manager',0,'1981-9-8',6100,600,40);
insert into emp values(7876,'Adams','Clerk',7900,'1987-5-23',1100,0,20);
insert into emp values(7900,'James','Clerk',7698,'1981-12-3',2400,0,30);
insert into emp values(7902,'Ford','Analyst',7698,'1981-12-3',3000,null,20);
insert into emp values(7901,'Kik','Clerk',7900,'1981-12-3',1900,0,30);

--往工资级别表中插数据
insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,5000);
insert into salgrade values(6,5001,10000);

------------------------------------------------------------------------------------------------------------------------------------

3.必会的5个组函数:max,min,avg,sum,count 要牢牢记住

-----------------------------------------------------------------------------------------------------------------------------------

4.练习:

①查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。
select empno,ename,sal,deptno from emp
where sal = (select max(sal) from emp);

②单条查询语句综合练习题:
薪水大于1200的雇员,按照部门编号进行分组,分组后的平均薪水必须大于1500,查询各分组的平均工资,按照工资的倒序进行排列。
select avg(sal) avg_sal, deptno
from emp
where sal > 1200
group by deptno
having avg_sal > 1500
order by avg_sal desc;
说明:此句基本上包含了SQL语句的子语句和排列顺序:select(要查询的字段)->from(从哪一张或哪几张表或视图)->where(过滤条件)->group by(having)(分组及条件)->order by(按哪个或哪几个字段进行升序或降序排列)。
注意:SqlServer4.1中可能不支持在order语句中使用组函数avg,报错说:invalid use of group function(错误提示和现象有点对不上)
解决办法:给avg(sal)起个别名avg_sal,这样在order语句中就直接使用这个别名

③等值连接:
查询每个雇员和其所在的部门名
select ename,dname from emp,dept where (emp.deptno = dept.deptno);
或者(推荐)(on中就写连接条件,where中就写过滤条件,各司其职)
select ename,dname from emp join dept on(emp.deptno = dept.deptno);

④非等值连接:
查询每个雇员姓名及其工资所在的等级
select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);

⑤查询雇员名第2个字母不是a的雇员的姓名、所在的组名、工资所在的等级。
三张表的连接查询(先连接,再加上where语句进行过滤)
select ename,dname,grade
from emp e join dept d on(e.deptno = d.deptno)
join salgrade s on(e.sal between s.losal and s.hisal)
where ename not like '_a%';

⑥查询每个雇员和其经理的姓名
自连接:(事实上只有一张表,但把它当成两张表来用,使用别名来进行区分)
select e1.ename,e2.ename from emp e1,emp e2 where (e1.mgr = e2.empno);
或者:(推荐用join语句)
select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno);

⑦查询每个雇员和其经理的姓名(包括公司老板本身(他上面没有经理))
左外连接(会把左表中不符合连接条件的记录也显示出来):
select e1.ename,e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno);

⑧查询每个雇员的姓名及其所在部门的部门名(包括没有雇员的部门)
右外连接(会把右表中不符合连接条件的记录也显示出来):
select ename,dname from emp e right join dept d on(e.deptno = d.deptno);

⑨子查询1:查询每个部门中工资最高的人的姓名、薪水和部门编号
先求出每个部门中的最高工资:
select max(sal) max_sal,deptno from emp group by deptno
在使用连接查询:
select ename,sal,e.deptno
from emp e join
(select max(sal) max_sal,deptno from emp group by deptno) t
on(e.sal = t.max_sal and e.deptno = t.deptno);

⑩子查询2:查询每个部门平均工资所在的等级
select deptno,avg_sal,grade from salgrade
join(select deptno,avg(sal) avg_sal from emp group by deptno) t
on(t.avg_sal between salgrade.losal and salgrade.hisal);
或者:
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal);

⑪子查询3:查询每个部门内平均的薪水等级
先求每个人的薪水等级
select ename,deptno,grade from emp join salgrade s 
on(emp.sal between s.losal and s.hisal);
再按组进行分组求平均
select deptno,avg(grade) from
(select ename,deptno,grade from emp join salgrade s 
on(emp.sal between s.losal and s.hisal)) t
group by deptno;

⑫子查询4:查询雇员中有哪些人是经理人:
select ename from emp where empno in(select distinct mgr from emp);
或者:
select ename from emp join
(select distinct mgr from emp) t
on(emp.empno=t.mgr);

⑬子查询5:不准用库函数,求雇员表中薪水的最高值。
思路:两张完全相同的雇员表,左边一张,右边一张。拿左表中的薪水和右表中的薪水进行比较,左表中的最高薪水值必定不可能小于右表中的某一薪水值。
先求出emp表中最高薪水以下的所有薪水值
select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal);
不在此列的薪水值即为最高薪水值
select distinct sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal));



⑭子查询6:平均薪水最高的部门的部门编号
 ①:先求出每个部门的平均薪水和部门号(把这个看成一张表)
select avg(sal) avg_sal,deptno from emp group by deptno;
 ②:再求出平均薪水最高值(把这个看成一个值)
select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t;

 ③:对①表使用②条件进行查询即可
select avg_sal,deptno from
 (select avg(sal) avg_sal,deptno from emp group by deptno) t1
where avg_sal=
 (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t2);

说明:在Oracle中可以使用组函数嵌套来稍微简化SQL语句的复杂程度(最多是两层嵌套):
select max(avg(sal)) from emp group by deptno;来求出平均薪水最高值
注意:这种组函数嵌套在MySQL中不可以用


⑮子查询7:求平均薪水最高的部门的部门名称(在子查询6的基础之上)
说明:在实际应用中,达到这种程度的SQL语句已经算比较复杂了。
select dname from dept where deptno = 
( select deptno from
     (select avg(sal) avg_sal,deptno from emp group by deptno) t1
  where avg_sal=
     (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t2)
);



-------------------------------------------------------------------------------------------------------------------------------------------------

⑯子查询8:查询平均薪水的等级最低的部门名称
说明:在实际应用中,很少使用到如此复杂的SQL语句。但要掌握一步步的思路,本例是为了锻炼逻辑能力。

①求平均薪水
select avg(sal) avg_sal,deptno from emp group by deptno;

②求平均薪水的等级
select avg_sal,deptno,grade from
  (select avg(sal) avg_sal,deptno from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and hisal);

③求平均薪水的等级最低的那个值
select min(grade) from
(  select avg_sal,deptno,grade from
      (select avg(sal) avg_sal,deptno from emp group by deptno) t
   join salgrade s on(t.avg_sal between s.losal and hisal)
) t3;

④平均薪水的等级最低的部门的部门编号(将②和③组合起来,将②看成要查询的表,将③看成查询条件)
select deptno from
(   select avg_sal,deptno,grade from
       (select avg(sal) avg_sal,deptno from emp group by deptno) t
    join salgrade s on(t.avg_sal between s.losal and hisal)
) t2
where grade=
(   select min(grade) from
    (   select avg_sal,deptno,grade from
           (select avg(sal) avg_sal,deptno from emp group by deptno) t
            join salgrade s on(t.avg_sal between s.losal and hisal)
    ) t3
);

⑤平均薪水的等级最低的部门名称
select dname from dept where deptno=
(  select deptno from
     (   select avg_sal,deptno,grade from
             (select avg(sal) avg_sal,deptno from emp group by deptno) t
         join salgrade s on(t.avg_sal between s.losal and hisal)
     ) t2
   where grade=
      (   select min(grade) from
         (   select avg_sal,deptno,grade from
                    (select avg(sal) avg_sal,deptno from emp group by deptno) t
                 join salgrade s on(t.avg_sal between s.losal and hisal)
      ) t3
      )
);


或者:还有种思路,使用join连接多张表(效果同上面的④和⑤,但比上面的稍微简单些)
④查询平均薪水的等级最低的部门的部门名、部门编号、平均工资和等级
(将②和③组合起来,将②和dept表join起来,将③看成查询条件)
select dname,t2.deptno,avg_sal,grade from
(   select avg_sal,deptno,grade from
      (select avg(sal) avg_sal,deptno from emp group by deptno) t
    join salgrade s on(t.avg_sal between s.losal and hisal)
) t2
join dept on(t2.deptno=dept.deptno)
where t2.grade=
(   select min(grade) from
    (   select avg_sal,deptno,grade from
           (select avg(sal) avg_sal,deptno from emp group by deptno) t
            join salgrade s on(t.avg_sal between s.losal and hisal)
    ) t3
);

说明:本例中有个待改进的地方,就是有重复的语句段(出现了两次):
select avg_sal,deptno,grade from
   (select avg(sal) avg_sal,deptno from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and hisal)


改进1:
在Oracle中使用创建视图,可以简化上面的SQL语句
(视图也是一张表,不过是虚表(实际数据仍然存在表emp和表salgrade中)、子查询):
create view v$_dept_avg_sal_info as
select avg_sal,deptno,grade from
   (select avg(sal) avg_sal,deptno from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and hisal);
将上例简化如下:
select dname,t2.deptno,avg_sal,grade from
v$_dept_avg_sal_info t2
join dept on(t2.deptno=dept.deptno)
where t2.grade=
(   select min(grade) from v$_dept_avg_sal_info
);

改进2:
MySQL5.0中创建视图(MySQL5新特点(视图)):
待运行验证:
create view v_dept_avg_sal_info as
select avg_sal,deptno,grade from
   (select avg(sal) avg_sal,deptno from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and hisal);
将上例简化为:
select dname,v2.deptno,avg_sal,grade from
v_dept_avg_sal_info v2
join dept on(v2.deptno=dept.deptno)
where v2.grade=
(   select min(grade) from v_dept_avg_sal_info
);

分享到:
评论

相关推荐

    SQL语句中UPDATE的三种用法.docx

    ### SQL语句中UPDATE的三种用法 #### 一、环境 本文档主要基于以下环境进行测试和示例编写: - **数据库版本**:MySQL 5.0.41 for Win32 - **操作系统**:Windows XP Professional #### 二、测试环境搭建 在开始...

    mysql/sql2000/sql2005/oracle数据库驱动包

    标题中的“mysql/sql2000/sql2005/oracle数据库驱动包”指的是不同数据库系统的驱动程序集合,这些驱动程序允许应用程序与相应的数据库系统进行通信。这些数据库系统分别是MySQL、SQL Server 2000、SQL Server 2005...

    Dubbo demo小项目 实现了和 Mysql 连接 以及增删改查

    3. **编写SQL语句**:为增、删、改、查操作编写对应的SQL语句。使用JdbcTemplate或者MyBatis这样的ORM框架可以简化SQL操作。 4. **执行数据库操作**:在服务提供者或消费者中,通过调用预编译的SQL语句,实现对...

    MySQL5.6版本 Linux安装包

    这些都需要使用SQL语句或者MySQL提供的客户端工具。 10. **性能改进**:MySQL 5.6版本引入了一些性能优化特性,如InnoDB存储引擎的改进,支持更多索引类型,以及查询优化器的升级,使得处理大数据集时更高效。 在...

    MySQL/PHP数据库应用开发指南(PDG)

    MySQL的CREATE DATABASE和CREATE TABLE语句用于构建数据库结构,INSERT INTO用于添加数据,SELECT用于检索数据,UPDATE用于修改现有数据,而DELETE则用于移除数据。此外,还要熟悉JOIN操作,用于合并来自多个表的...

    MYSQL培训经典教程

    MySQL使用SQL(结构化查询语言)进行数据操作,SQL是用于管理和处理关系数据库的标准语言。 教程的早期部分将从安装MySQL开始,讲解如何在不同的操作系统(如Windows、Linux和Mac OS)上设置和配置MySQL服务器。...

    JDBC 连接mysql的一种方法,使用JAVA语言

    4. **执行SQL**:使用`Statement`对象的`executeQuery()`或`executeUpdate()`方法执行SQL语句。例如,执行一个SELECT查询: ```java ResultSet rs = stmt.executeQuery("SELECT * FROM tablename"); ``` 5. **处理...

    SSM 客户关系管理系统 ,内含sql脚本,可直接部署

    3. **Mybatis框架**:Mybatis是一个持久层框架,它简化了JDBC操作,允许开发者通过XML或注解来定义SQL语句。在SSM系统中,Mybatis作为数据访问层,将业务对象与数据库表进行映射,实现了SQL的动态执行,提高了开发...

    简单了解标准SQL的update语句三种用法

    本文将重点讨论SQL的`UPDATE`语句,这是一种用于修改现有数据表中记录的命令。我们将通过三个具体的示例来详细介绍`UPDATE`语句的三种基本用法。 首先,我们需要一个测试环境。这里使用的是MySQL 5.0.41-win32版本...

    JAVA基于Swing和MySql的考试系统

    同时,合理的SQL语句设计和索引的使用可以提高查询速度,提升用户体验。 总的来说,这个基于JAVA Swing和MySQL的考试系统展示了Java GUI编程和数据库管理的基本原理,为实际应用场景提供了有价值的参考。开发者需要...

    oracle+mysql+db2 驱动包Jar

    这个驱动实现了JDBC接口,使开发者可以使用标准的SQL语句来操作MySQL数据库。MySQL的JDBC驱动jar文件通常命名为"mysql-connector-java-xx.yy.zz.jar",这里的xx.yy.zz表示驱动的版本。安装驱动后,开发者可以通过...

    JavaEE源码仓库管理源码数据库sql文档

    SSM框架中的MyBatis正是与MySQL进行交互的重要工具之一,通过MyBatis可以方便地执行SQL语句,实现对MySQL数据库的操作。 ### 四、源码、SQL脚本、文档和视频资源的重要性 #### 1. **源码** - **学习价值**:对于...

    2022年最新PHP技术笔试题.doc

    6. SQL 优化:对于 SELECT \* FROM tb1 这样 SQL 查询语句,怎样才能减少从数据库返回的数据量?答案:BC. 用品体字段名来替代“\*”和加一种“where”语句 知识点:SQL 优化, 使用具体的字段名和添加 WHERE 语句...

    Jsp Mysql 中文 Servlet错误捕捉 集成演示2.rar

    3. 数据库连接与操作:展示如何使用JDBC建立与MySQL的连接,执行SQL语句,以及关闭数据库连接。 4. 错误处理:通过示例说明如何在JSP和Servlet中捕获并处理异常,提供友好的错误提示。 5. 整合示例:将以上元素整合...

    JavaEE源码java新闻发布管理源码数据库sql文档

    - **SQL语句**:通过分析SQL语句可以了解数据的增删改查操作是如何实现的。例如,插入新闻记录、查询特定条件下的新闻列表等。 ### 5. 学习资源 - **文档**:文档通常包含系统的设计理念、架构图、安装部署指南等...

    JavaEE源码大学生就业管理源码数据库sql文档

    通过上述分析,我们可以看到“JavaEE源码3.1javaEE_ssh_jsp_MySql大学生就业信息管理”项目涉及到了JavaEE技术栈、MySQL数据库以及相关的文档编写等多个方面,这是一次全面而深入的学习机会。希望通过对这些知识点的...

    JavaEE源码网络考试系统源码数据库sql文档

    - **SQL语句执行**:利用PreparedStatement等类来执行SQL查询或更新操作,确保代码的安全性和效率。 ### 三、网络考试系统的功能设计 #### 用户管理模块 - **注册登录**:用户可以通过注册账号并登录系统来进行...

    PHP实例开发源码-zZ笨笨 php留言板程序.zip

    例如,通过预处理语句或者参数化查询来防止SQL注入,对用户输入进行过滤或转义以避免XSS攻击。 在压缩包中,“使用须知.txt”文件可能包含了关于如何部署和运行这个留言板程序的指导,包括服务器环境要求(PHP版本...

    JavaEE银行柜员业务绩效考核源码数据库sql文档

    熟练掌握SQL语句对于数据库设计和操作非常重要。在本项目中,开发者需要能够编写复杂的查询语句,如联表查询、子查询等,以满足业务需求。 ### 五、系统部署与测试 #### 1. 系统部署 系统部署是指将开发好的应用...

    JavaEE源码企业财务系统源码数据库sql文档

    优化SQL语句减少执行时间等。 4. **安全性保障措施**:由于涉及到敏感的财务数据,必须采取严格的安全措施防止信息泄露。例如,采用HTTPS协议传输数据;定期备份数据库以防意外丢失;实施细粒度权限控制限制用户...

Global site tag (gtag.js) - Google Analytics