【重温SQL基础】

day1 一个简单的表操作

　 聚合函数（也叫统计函数），SUM，COUNT()，MAX，MIN，AVG 中，只有COUNT(*)计算时是不忽略NULL值的，因为其对表中行数进行计数

 　不管是否有NULL，倘若COUNT(Name) 这种针对特定字段进行统计时肯定就不会计入NULL值了，那样没有意义。

下面是一些执行记录：

use testdb
CREATE table books(
`bookId` int(5) not null AUTO_INCREMENT COMMENT '书ID',
`bookName` VARCHAR(20) not null COMMENT '书名',
`numbers` int(10) not null comment '数量',
`details` VARCHAR(20) not null comment'简介',
key `bookId`(`bookId`)
)ENGINE = INNODB auto_increment = 1 DEFAULT CHARSET = utf8;

/*此处由于bookId已经设置自增，如果仍然为bookId字段插入value则可能会：1.雷同时报错，不雷同时则正常插入，然后下一个空id会从这个值开始自增*/
INSERT into books(`bookName`,`numbers`,`details`) values
('白雪公主与7个大灰狼',23,'安徒生童话'),
('测试1',12,'只是个测试'),
('测试2',16,'只是个测试'),
('测试3',72,'只是个测试'),
('测试4',2,'只是个测试'),
('测试5',62,'只是个测试'),
('测试6',233,'只是个测试');

ALTER TABLE books CHANGE bookName bookName VARCHAR(23) /*修改字段长度*/

 day 2  用一个完整的表 练习相关操作

整体数据库如下：

 countries 表

 departments表

 employees表

job_history 表

 jobs表

 locations和regions没用到就懒得贴了

然后嘞，贴一张用的最频繁的employees表数据内容：

一些基础练习：

1. 显示员工代号、员工姓名及薪水* 1.5倍后资料
2. 显示主管(mgr)为Null的员工资料
3. 透过emp表格找出所有可能的部门代号(不可重复)
4. 依照员工薪水资料，由大至小排列
5. 找出员工姓名为JOHN的员工数据
6. 找出薪水小于$3000的员工数据
7. 显示工作类别非'PRESIDENT'及'MANAGER'的员工数据
8. 找出员工名字为S开头的员工
9. 将员工姓名及员工代码结合为新字段，重新命名为NAME_NO
10. 找出部门代号为20且薪水介于2000及4000间(包含2000及4000)，并依照资深至资浅排序

有一些比较基础，就略过了

7.
SELECT e.* , j.JOB_TITLE from employees e ,jobs j where  e.JOB_ID = j.JOB_ID and (j.job_title not like '%president%')and j.job_title not like '%manager%';
9.
ALTER TABLE employees ADD NAME_NO varchar(30);
UPDATE EMPLOYEES SET NAME_NO = CONCAT(employee_id,first_name,last_name);
/*这个算是添加字段了，还可以用select 查询时合并字段，然后起个别名，当然，不会改变表内数据，*/
10.
select e.* from employees e,job_history jh
where e.department_id = 20 and e.salary BETWEEN 2000 and 4000 order by (jh.END_DATE-jh.START_DATE) desc; 

 day 3

 多表连接，聚合函数，子查询，视图，索引等等

1. 显示工作名称字段(转换为小写格式)
2. 显示员工名字(转换为第一个字大写其余为小写格式)
3. 显示员工名字及工作名称结合的新字符串，并找出工作名称在新字符串的开始位置
4. 列出年资大于15年的员工数据
5. 显示员工薪水数据(无条件舍去到整数字数的千位数)
6. 显示员工名字、薪水及薪水代号(薪水大于3000显示'1'，小于3000显示'-1'，与3000相等时显示0，可使用Sign函数)
7. 显示当月的天数，试写出两种以上方法
8. 计算2010年1月1日与目前日期差异天数
9. 若当该名员工无主管时，显示'NO MGR'
10. 若员工工作名称为'PRESIDENT'、 'MANAGER'及'SALESMAN'则显示'A'，其他则显示'B'  

1.
select lower(job_title) from jobs;/*注意此时job_title不要加单引号*/
2.
select concat(UPPER(left(last_name,1)),substring(last_name,2,(length(last_name)-1)))
 from employees;
3.
SELECT concat(e.LAST_NAME,j.JOB_TITLE) as c, INSTR(concat(e.LAST_NAME,j.JOB_TITLE),job_title)from employees e,jobs j
where e.JOB_ID = j.JOB_ID;

4.

select * from employees where hire_date<subdate(now(),interval 15 year);

5.
select first_name, SALARY,TRUNCATE(salary,-3) from employees;
/*round可以截断但是会有四舍五入的问题，而truncate没有这个问题，也就是无条件舍去*/
6.
select first_name ,salary,SIGN(salary-3000) from employees;
7.
第一种方法：
select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual
第二种方法：
SELECT  TIMESTAMPDIFF(day,CURDATE(),(DATE_add(CURDATE(),INTERVAL 1 month)));
以及

　　Select day(last_day(now()));

/*时间戳更精准*/
8.
SELECT  TIMESTAMPDIFF(day,'2010-01-01',(DATE_add(CURDATE(),INTERVAL 1 month)));
9.
SELECT IFNULL(e.MANAGER_ID,'NO MGR') from employees e;
10.
SELECT j.job_title, IF(j.job_title like '%president%' or j.job_title like'%manager%' or j.job_title like'%salesman%', 'A', 'B') from jobs j;

JOIN

1. 列出emp表格及dept表格，结合后所有可能排列组合
2. 显示'SMITH'及其所属部门资料
3. 显示'SMITH'员工及其所属部门数据(使用表格别名的方式，表格emp别名为a、表格dept别名为b)
4. 显示dept表格中，所在地为'NEW YORKToronto'的员工数据
5. 显示主管'KING'，直属员工资料
6. 列出员工名字、职称及薪水及其管理者名字、职称及薪水数据(需包含KING数据)
7. 同习题六范例，并依照所属部门代码及员工薪水由大至小排列
8. 找出雇用日期早于其管理者雇用日期的员工数据，显示字段包含员工姓名、雇用日期及其管理者姓名、雇用日期

 前几条略，

这里面有一个地方我想了比较久，就是第6个，后来想起来每个表都可以搞很多个对象，然后注意:这里有表jobs , 创建对象的格式应该是jobs j1,jobs j2  ，而不是jobs j1,j2     然后表名与表名间用逗号隔开，我总是忘-=。=，

/*4.显示dept表格中，所在地为'NEW YORK'的员工数据*/

select d.* from locations l 
right join departments d
on l.location_id = d.LOCATION_ID
where l.CITY = 'New York';

/*5.显示主管'KING'，直属员工资料*/
Explain select e.* from employees e
where e.DEPARTMENT_ID = 90;

select e.* from employees e
where e.DEPARTMENT_ID = 90 and e.LAST_NAME !='king';

/*6.列出员工名字、职称及薪水及其管理者名字、职称及薪水数据(需包含KING数据)*/

select  e1.FIRST_NAME ,e1.last_name  , j1.job_title, e1.salary, e2.last_name as '管理者名字',j2.job_title as '管理者职称',e2.salary as '老大薪水'
from employees e1,employees e2,  jobs j1,jobs j2 
where e2.employee_id = e1.manager_id  and  e1.JOB_ID = j1.JOB_ID and e2.JOB_ID = j2.JOB_ID;
/*7.同习题六范例，并依照所属部门代码及员工薪水由大至小排列*/
select  e1.FIRST_NAME ,e1.last_name  , j1.job_title, e1.salary, e2.last_name as '管理者名字',j2.job_title as '管理者职称',e2.salary as '老大薪水'
from employees e1,employees e2,  jobs j1,jobs j2 
where e2.employee_id = e1.manager_id  and  e1.JOB_ID = j1.JOB_ID and e2.JOB_ID = j2.JOB_ID
group by e1.department_id having count(*)>=1 
ORDER BY e1.salary desc;
/*8.找出雇用日期早于其管理者雇用日期的员工数据，显示字段包含员工姓名、雇用日期及其管理者姓名、雇用日期*/

select e1.LAST_NAME,e1.HIRE_DATE, e2.LAST_NAME as '老大名字',e2.HIRE_DATE as '老大入职日期' from employees e1,employees e2 
where e2.employee_id = e1.manager_id  and e1.HIRE_DATE < e2.HIRE_DATE ;

聚合函数

1. 计算所有员工总数、薪水最低、薪水最高、薪水总和、平均薪水等数据
2. 计算各部门员工总数、薪水最低、薪水最高、薪水总和、平均薪水
3. 同范例二，但不含部门20及管理者'KING'数据
4. 同范例二，依照部门人数由大至小排列
5. 计算各部门员工薪水最大与最小差额
6. 找出部门员工总数大于等于5，且平均薪水大于$2000的员工数据，显示部门代码、部门员工数、部门平均薪水数据
7. 同范例6，但部门代号换为部门名称
8. 计算员工数据表格中，各工作部门代号、工作类别薪水总和
9. 同上例，且依照阶层关系找出各部门薪水小计及总计
10. 同范例8，当部门小计时JOB字段显示’ALL JOB’，当总计时detpno字段显示’ALL DEPT’ 及JOB字段显示’ALL JOB’

8,9,10后续再更。。

/* 聚合函数 1.计算所有员工总数、薪水最低、薪水最高、薪水总和、平均薪水等数据*/
select count('EMPLOYEE_ID') , MIN(salary),MAX(salary),SUM(salary),AVG(salary) from employees;

/*2.计算各部门员工总数、薪水最低、薪水最高、薪水总和、平均薪水*/
select COUNT('e.DEPARTMENT_ID') ,MIN(salary),MAX(salary),SUM(salary),AVG(salary) from employees e
GROUP BY e.DEPARTMENT_ID ;
/*3.同范例二，但不含部门20及管理者'KING'数据*/
/*注意where在group by 前面*/
select COUNT('e.DEPARTMENT_ID') ,MIN(salary),MAX(salary),SUM(salary),AVG(salary) from employees e
where e.DEPARTMENT_ID != '20' and e.LAST_NAME != 'king'
GROUP BY e.DEPARTMENT_ID ;

/*4.同范例二，依照部门人数由大至小排列*/
select COUNT('e.DEPARTMENT_ID') ,MIN(salary),MAX(salary),SUM(salary),AVG(salary) from employees e
group by e.department_id having count(*)>=1 
order BY COUNT(e.DEPARTMENT_ID) desc;
/*5.计算各部门员工薪水最大与最小差额*/
select MAX(SALARY) - MIN(SALARY)  from employees e 
group by e.DEPARTMENT_ID;

/*6.找出部门员工总数大于等于5，且平均薪水大于$2000的员工数据，显示部门代码、部门员工数、部门平均薪水数据*/
select e.DEPARTMENT_ID , COUNT('e.DEPARTMENT_ID') ,AVG(salary) from employees e
group by e.department_id  HAVING count(*) >5  and avg(salary)>2000;
/*这里面不可以在where字句中用count等聚合函数进行计算 ，要么不分组，要么在group by 后面用having做限制*/


/*7.同范例6，但部门代号换为部门名称*/
select d.DEPARTMENT_NAME , COUNT('e.DEPARTMENT_ID') ,AVG(salary) from employees e , departments d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
group by d.DEPARTMENT_NAME  HAVING count(e.DEPARTMENT_ID) >5  and avg(salary)>2000;


/*count(1)与count(*)
count(*）统计所有项数，不忽略空值；
count（字段) 忽略空值。  */
select count(*) from jobs;
select count(1) from jobs;
select * from jobs;

/*8.计算员工数据表格中，各工作部门代号、工作类别薪水总和*/
select  e.DEPARTMENT_ID , e.JOB_ID , SUM(salary)  from employees e
GROUP BY e.JOB_ID, e.DEPARTMENT_ID;

/*9*/

SELECT d.MANAGER_ID AS '部门主管编号',e.MANAGER_ID AS'工作主管编号',sum(e.SALARY)
FROM employees e LEFT JOIN departments d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
GROUP BY d.MANAGER_ID,e.MANAGER_ID WITH ROLLUP/*主要是rollup函数*/

/*10*/

select coalesce(department_id,'ALL DEPT')department_id,coalesce(job_id,'ALL JOB')job_id,sum(salary)
from employees
group by job_id,department_id
with rollup;