SQL实战笔记，第六篇

！51. 查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。

解题比较有技巧，原来的字符串长度减去把要计数的字符去掉后的字符串的长度就是要计数的字符个数

select (length('10,A,B')-length(replace('10,A,B',',','')))as cnt
--字符串长度length()
--SQL server 里的函数是len()

52. 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

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`));

注意：从字符串中取字符的函数
Oracle，SQLite：substr(string,start,length)
MySQL：substr()或substring()
SQL Server：substring()

--select first_name from employees
--order by right(first_name,2) asc-- MySQL,server可以这里不可以
--考察substr(,,)，MySQL有这个函数
--SQL server 叫 substring(,,)函数
select first_name from employees order by substr(first_name,-2)

！SQL server substring 用法：
SQL Server SUBSTRING()函数简介

----截取字符串的后两位，（字符串，开始位置，截取长度）
select substring('harry',len('harry')-1,2)

！53. 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

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`));

注意：考察group_concat()函数

select dept_no,group_concat(emp_no) as employees from dept_emp
group by dept_no
--group_concat函数

54. 查找排除当前最大、最小salary之后的员工的平均工资avg_salary。

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 avg(salary) as avg_salary from salaries 
where to_date='9999-01-01'
and salary <> (select max(salary)from salaries)
and salary <> (select min(salary)from salaries)

！55. 分页查询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`));

注意：与SQL server 复杂的分页查询不太一样

select * from employees limit 5,5-- LIMIT 5 OFFSET 5
-- 不要order by 这里

56. 获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received ，没有分配具体的员工不显示

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`));
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);

考察表的连接，left join ，小表连大表

select a.emp_no,a.dept_no,b.btype,b.recevied
from dept_emp as a 
left join emp_bonus as b
on a.emp_no=b.emp_no

！57. 使用含有关键字exists查找未分配具体部门的员工的所有信息。

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`));

考察…where not exists(…)语法

select * from employees
where not exists
(select emp_no from dept_emp where emp_no=employees.emp_no)
--这题注意与not in的语法区别
--WHERE emp_no NOT IN

58. 存在如下的视图：
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关键字。

简单，理解视图就知道

select * from employees where emp_no>10005
--select * from emp_v--也可以

！59. 获取有奖金的员工相关信息。

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 关键字来选择设定bonus

select a.emp_no,a.first_name,a.last_name,b.btype,c.salary,
case b.btype when 1 then 0.1*c.salary
             when 2 then 0.2*c.salary 
             else 0.3*c.salary end as bonus
from employees as a
inner join emp_bonus as b on a.emp_no=b.emp_no
inner join salaries as c on b.emp_no=c.emp_no
where c.to_date='9999-01-01'
-- case关键字的使用

-》判等语法：
case 列名或表达式
when 值1 then …
when 值2 then …
when 值3 then …
else …
end
-》判不等语法：
case
when 条件1 then …
when 条件2 then …
when 条件3 then …
else …
end

！60. 按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。

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`));

注意：考察表的复用，sum()函数，条件 b.emp_no<=a.emp_no，是找出b表中在a.emp_no前面的人，sum后就是累加

select a.emp_no,a.salary,sum(b.salary) as running_total
from salaries a, salaries b 
where b.emp_no<=a.emp_no
and a.to_date='9999-01-01'
and b.to_date='9999-01-01'
group by a.emp_no --必须要group by 
order by a.emp_no

！61. 对于employees表中，给出奇数行的first_name

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`));

注意：奇数行肯定有%2=1运算，但如何考虑条件值得学习，表的复用，好像类似自己定义了rownumber()

select e1.first_name from
(select e2.first_name,
(select count(*) from employees e3 where e3.first_name<=e2.first_name) as rowid
from employees as e2) as e1--子查询虚表
where e1.rowid%2=1
--即在给定e2.first_name的情况下，不大于 e2.first_name的 e3.first_name的个数有多少，
--该个数刚好与 e2.first_name的排名标号匹配，且将该值命名为rowid