SQL实战(六)
一、
题目描述
CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
1、三个条件,to-date max(salary) min(salary)
select avg(salary) as avg_salary from salaries where
(salary not in (select max(salary) from salaries )
and salary not in (select min(salary) from salaries)
and to_date='9999-01-01')
2、
select (sum(salary)-max(salary)-min(salary))/(length(salary)-2) as avg_salary from salaries
where to_date='9999-01-01'
这种方法通过不了,不知道为什么。
二、
分页查询employees表,每5行一页,返回第2页的数据
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
select * from employees
limit 5 offset 5
select * from employees
limit 5,5
三、
题目描述
CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
1、左连接
select de.emp_no as dept_no,de.dept_no as dept_no,em.btype ,em.recevied from dept_emp as de
left join emp_bonus as em
on de.emp_no=em.emp_no
四、
题目描述
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
select * from employees
where(emp_no not in (select emp_no from dept_emp))
2、使用exists
select * from employees as em
where not exists (select emp_no from dept_emp as de where de.emp_no=em.emp_no)
比较exists 和 in
这是同一个表的两次比较。date=2011-7-1时,7-2和7-3大于它,所以存在满足的条件,not exist 就会使不输出。同理7-2也是,但是7-3时,没有满足的条件,not exists 翻转下后,就有 的输出了。
distinct作用于所有列。
group by与聚合函数共同使用。
五、
题目描述
create view emp_v as select * from employees where emp_no >10005;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
获取employees中的行数据,且这些行也存在于emp_v中。注意不能使用intersect关键字。
1、
SELECT em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no
2、
SELECT * FROM emp_v
六、
获取有奖金的员工相关信息。
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`));
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%,
btype为2其奖金为薪水的20%,其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'
case的应用
select em.emp_no,em.first_name,em.last_name,bon.btype,s.salary,
( case bon.btype
when 1 then s.salary*0.1
when 2 then s.salary*0.2
else s.salary*0.3
end )as bonus
from employees as em join emp_bonus as bon
on em.emp_no=bon.emp_no
join salaries as s
on s.to_date='9999-01-01' and s.emp_no=em.emp_no
七、
题目描述
CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
select s1.emp_no,s1.salary,
(select sum(s2.salary) from salaries as s2
where s2.emp_no<=s1.emp_no and s2.to_date='9999-01-01') as running_total
from salaries as s1 where s1.to_date='9999-01-01'
一表两用的比较方法。
八、
题目描述
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
select e1.first_name from
(select e2.first_name,(select count(*) from employees as e3 where e2.first_name<=e3.first_name)as row_num from employees as e2)as e1
where e1.row_num%2=1
1、first_name排序而非emp_no 排序,所以不是1357这样式的。
2、一定要注意嵌套里的名称对应关系。
黄色部分是统计的按名称的排序
绿色部分是将名称和对应的排序(添加新的名字 row_num)连接。
赋予新的名字,然后判断输出。
select B.new_name from (select A.neme as new_name from A) as B 双层嵌套的格式,一定要注意名字。
最新文章
- 配置 Sublime Text 用 Node.js 执行 JavaScript 程序
- 【转】UnityVS(Visual Studio Tools For Unity)的安装与使用
- 进程控制的一些api
- Java单例模式--------懒汉式和饿汉式
- (转载)php如何判断IP为有效IP地址
- Android 3D emulation 架构理解
- POJ2739 - Sum of Consecutive Prime Numbers(素数问题)
- Github进行项目管理的常用命令总结
- 胜利大逃亡(续)(状态压缩bfs)
- VPN连接在遇到飞鱼星设备时可能出现的疑难问题
- 利用ScktSrvr打造多功能Socket服务器
- STM32F407IG开启FPU,做开方运算
- Maven 设置Maven源/镜像
- (一) Keras 一元线性回归
- 双网卡单IP实现网卡冗余与负载均衡
- MySql TIMEDIFF做计算之后,后台报Illegal hour value &#39;24&#39; for java.sql.Time type 问题
- [译]如何在.NET Core中使用System.Drawing?
- jQueryUI中Datepicker(日历)插件使用
- ceph PG数量调整/PG的状态说明
- $\rm{NOIP}$前的模拟题整理&#183;菜鸡互啄篇
热门文章
- JZOJ 1349. 最大公约数 (Standard IO)
- 前端基础面试题(js部分)
- centos下配置Nginx
- java-3个例子(新手)
- angular 项目中遇到rxjs error TS1005:&#39;;&#39;
- 2. Plugin execution not covered by lifecycle configuration
- iOS开发:判断iPhone是否是刘海屏iPhoneX、iPhoneXR、iPhoneXs、iPhoneXs Max等
- Python3 整数
- windows10 64位 安装mysql服务端 并使用navicat客户端链接 掉的一堆坑
- 【Unity游戏开发】跟着马三一起魔改LitJson