- 浏览: 898208 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (537)
- Java SE (114)
- Struts (18)
- Hibernate (25)
- Spring (3)
- Page_Tech (41)
- Others (87)
- Database (29)
- Server (24)
- OpenSource_Tools (15)
- IDE_Tool (22)
- Algorithm (28)
- Interview (22)
- Test (28)
- Hardware (1)
- Mainframe (25)
- Web application (4)
- Linux (3)
- PHP (17)
- Android (1)
- Perl (6)
- ubuntu (1)
- Java EE (9)
- Web Analysis (5)
- Node.js (2)
- javascript (2)
最新评论
-
一键注册:
request.getRequestURL()和request.getRequestURI() -
SuperCustomer:
...
SED的暂存空间和模式空间 -
juyo_ch:
讲得挺好理解的,学习了
java 死锁及解决 -
chinaalex:
最后一题答案正确,但是分析有误.按照如下过程,上一行为瓶,下一 ...
zz智力题 -
liaowuxukong:
多谢博主啦,弱弱的了解了一点。
C++/Java 实现多态的方法(C++)
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
);
发表评论
-
zz 如何安装两个MYSQL在同一机器
2012-08-01 23:04 1032说白了就一句话:一个自动安装服务,另一个手动安装服务! ... -
zz delete和truncate的区别
2012-03-08 16:18 682delete from aatruncate table aa ... -
【原创】MYSQL 安装时候的1045错误的解决方法
2012-03-04 21:02 771在安装的时候,出现这个问题网上的很多方法是卸载后重新 ... -
SQL删除重复记录,并只剩一条
2012-02-24 14:59 1315在几千条记录里,存在着些相同的记录,如何能用SQL语句,删 ... -
数据库驱动的载入有三种方法!
2012-01-11 15:58 9111. import java.sql.DriverM ... -
JDBC 完全总结
2012-01-11 14:46 885一、JDBC 简介 SUN 公司为了简化、统一数 ... -
oracle基础书籍推荐
2012-01-10 10:00 2696很多朋友要我帮忙推荐一下Oracle的入门书籍,能够了解Ora ... -
实例分析JDBC连接数据库代码
2011-11-15 10:37 826一、jdbc连接数据库代码速查表下面罗列了各种jdbc连接 ... -
mysql 5.0存储过程学习总结
2010-11-21 20:11 765mysql存储过程的创建,删除,调用及其他常用命令 ... -
MySQL的事务管理
2010-11-21 19:59 38671,MySQL的事务支持 MySQL的事务支持不是绑定在MyS ... -
同事今天下午的MYSQL性能优化分享
2010-10-18 23:22 768下午的分享,同事讲了 ... -
视图——ALGORITHM
2010-10-18 15:22 1335修改视图需要create view ... -
MySQL视图的应用
2010-10-18 15:09 1505MySQL视图的应用 视图也称虚表,包括执行某个查询返回的一 ... -
Mysql中IFNULL和IF函数
2010-10-15 17:01 7971. IFNULL(expr1,expr2) 2. 如果e ... -
数据库水平切分的实现原理解析---分库,分表,主从,集群,负载均衡器
2010-10-09 15:39 805第1章 引言 随着互联 ... -
SQL 日期函数
2010-10-08 17:44 1028SYSDATE与CURRENT_DATE区别 ... -
Oracle truncate table 与 delete tabel的区别
2010-09-03 17:05 833一、 1.delete产生rollback,如 ... -
JDBC的连接方式和驱动类型
2010-08-24 13:49 9681.连接数据库的方式:ODBC , JDBC ODBC:Op ... -
数据库原理—— 视图
2010-08-06 10:10 2371视图是一个虚拟表,其 ... -
内连接,外连接,交叉连接
2010-08-06 10:04 990探究内连接,外连接,交叉连接 (一)内连接 内连接查询操作列 ...
相关推荐
SQL-Server-SQL语句大全
mysql/sql2000/sql2005/oracle数据库驱动包,包括数据库连接语句.
base zz zz zz zz zz base zz zz zz zz zz base zz zz zz zz zz base zz zz zz zz zz
数学建模数据集全国银行名称及所在地数据mysql/sql提取方式是百度网盘分享地址
eclipse+纯Servlet+mysql开发JSP物流管理系统 带.sql文件
主要介绍了MySQL复制出错 Last_SQL_Errno:1146的解决方法,需要的朋友可以参考下
mysql 垃圾分类数据表 sql文件
MySQL 使用 SQL 语言进行操作。MySQL一直是面试中的热点问题,也难道了很多的面试者。其实MySQL没那么难,只是大家没有系统化、实战性的过去学习、总结。同时很多开发者在实际的开发过程中也很少去接触一些偏向底层...
Office数据库升迁 Access 导入 SQL Server,相信大家都知道 SQL-Server 数据库导入导出功能吧,但当你装了精简版的SQL-Server这个功能就会没有了,也就是无法实现数据库导入导出,那么怎么办 写SQL语句? Insert ...
2.新建一个mysql数据库,将数据库db_campus_market.sql脚本导入 3.并在配置文件application-prd.properties和application-dev.properties里修改数据库配置信息,将数据库链接信息,账号密码全改成自己的 4.注意mysql...
ZZ561401.CAB ZZ561401.CAB ZZ561401.CAB
wincc SIMATIC WinCC是第一个使用最新的32位技术的过程监视系统,具有良好的开放性和灵活性。 从面市伊始,用户就对SIMATIC WinCC印象深刻。
在CAD中想要快速测量长度,在CAD工具栏找到加载应用程序,再点击加载 加载成功后在输入栏输入“zz”(不分大小写)在选择你需要测量的线段即可。
这个是我找了2个小时,浪费了150积分才找到的,为了让我们这些java爱好者,少走弯路,我把他分享出来,https://blog.csdn.net/weixin_39845780/article/details/116642786 或者你也可以直接在这里下载
我们研究了四轻子最终状态ℓ+ℓ-ℓ+ℓ-的产生,这些状态主要由一对弱电Z玻色子ZZ产生。 使用LoopSim方法,我们合并ZZ和ZZ + jet的NLO QCD结果,并获得ZZ产生的近似NNLO预测。 还包括对ZZ过程的精确胶子融合环平方的...
查询表字段列信息SELECT t.* FROM SYSCAT.COLUMNS tWHERE TABSCHEMA = 'Schemas名' AND TABNAME
练习if / else语句 对于本课程,请确保将整个项目文件夹一起压缩并上传 创建一个循环,打印从1-255的所有值 创建一个新的循环,该循环打印从1到100的所有可被3或5整除的值,但不能同时将两者 修改上一个循环,将“ ...
sql,3SQL经典面试题
2019最新省份城市县级三级联查用数据表 mysql2019最新省份城市县级三级联查用数据表 mysql2019最新省份城市县级三级联查用数据表 mysql
,主图指标,顶底信号,突破,转折信号,都很明显