sql查询练习（3）

create table employees(
    employee_id int primary key,
    first_name varchar(20) not null,
    last_name varchar(20) not null,
    job_id varchar(20) not null,
    phone_number varchar(20) not null,
    salary int,
    manager_id varchar(20), 
    department_id varchar(20) not null
);

创建employees表，列名为employees_id（员工id）first_name（姓）last_name（名）job_id（工号）phone_number（手机号）salary（月薪）manager_id（所属经理工号）department_id（所属部门id）

给表插入数据：

insert into employees values(1,'李','刚刚','2010','18688264493',4000,'2001','002');
insert into employees values(2,'陈','晨','2004','18287244415',4000,'2001','002');
insert into employees values(3,'刘','哲','3001','18868258732',10000,null,'003');
insert into employees values(5,'季','如风','3012','19674827442',6000,'3001','003');
insert into employees values(4,'刘','娜','3005','18864825264',8000,'3001','003');
insert into employees values(6,'张','慧琳','5008','17888449663',3000,'5001','005');
insert into employees values(7,'明','艾静','5001','19377883456',10000,null,'005');
insert into employees values(8,'姚','汝能','5023','18902897328',6000,'5001','005');
insert into employees values(9,'郝','爱佳','4008','17896943587',5000,'4001','004');
insert into employees values(10,'李','雯雯','4013','18688264493',8000,'4001','004');
insert into employees values(11,'张','文广','4015','13888569018',4000,'4001','004');

select employee_id from employees where first_name like '%张';
select employee_id from employees where last_name like '%静%';
select job_id from employees where salary between 5000 and 10000; ----包含临界值
select job_id from employees where manager_id is not null;
select job_id from employees where manager_id not in ('3001','5001'); ---不能检查出null，输出结果不包括manager_id为null的值
select job_id from employees where manager_id is null;
select job_id from employees where manager_id <=> null; ----PostgreSQL中无法使用
select job_id,12*salary from employees order by 12*salary desc;
select job_id,12*salary as "年薪" from employees order by "年薪" desc;
select last_name,first_name from employees order by length(last_name) desc;
select * from employees order by salary desc,employee_id asc;

关于concat函数：

将多个字符连接成一个字符

concat(str1,str2,str3,....) 返回字符串，如果存在null值，则返回值为null

concat(str1,seperator,str2,seperator,....)

返回结果为连接参数产生的字符串并且有分隔符，如果有任何一个参数为null，返回值为null

concat_ws()是concat()的特殊形式，第一个参数是分隔符，表示所有连接的字符串之间都有分隔符，而concat(str1,seperator,str2,seperator,....) 两个字符串之间没有seperator时，连接后的字符串之间就没有分隔符。concat_ws()不会因为null值而返回null值。

group_concat()不是很明白所以就不写了。

select concat(first_name,last_name) "姓名" from employees;
select concat(employee_id,first_name,last_name) "姓名" from employees;---int 类型与str类型可以混合连接
select concat(employee_id,',',first_name,',',last_name) "姓名" from employees;
select concat(employee_id,',',first_name,last_name) "姓名" from employees;
select concat_ws(',',employee_id,first_name,last_name) "姓名" from employees;

insert into employees values(12,'liu','Nino','5002','18678969979',7000,'5001','005');
insert into employees values(13,'xia','Brain','4018','17544236943',6000,'4001','004');
insert into employees values(14,'liu','nuoyi','6022','18878122645',9000,'6001','006');
insert into employees values(15,'li','SaSha','6001','13566752806',10000,null,'006');

下面是一些随机小练习

select length('john');
select length('张三丰hahaha');
select upper('john'); --JOHN
select lower('joHn'); --john
select concat(upper(first_name),' ', lower(last_name)) as "姓名" from employees;
----substr/substring(索引从1开始)
select substr('李莫愁爱上了陆展元',7) out_put;
select substr('李莫愁爱上了陆展元',1,3) out_put;
select substr('李莫愁爱上了陆展元',7); ---也可以输出结果
select concat(upper(substr(last_name,1,1)),lower(subsrt(last_name,2))) out_put from employees; ---postgreSQL无法实现
--instr返回子串中第一次出现的索引，找不到返回0
select instr('杨不殷六侠悔爱上了殷六侠','殷六侠');---MySQL实现
select position('殷六侠' in '杨不殷六侠悔爱上了殷六侠');  ---postgreSQL中功能同instr
select trim('  张翠山   ') out_put;-------trim 函数可以移除字符串的首尾信息，最常见的用法为移除字符首尾空格
select length(trim('  张翠山   ')) out_put;
select trim('aa' from 'aaaa张aaaa翠a山aaa') out_put; ---如何只去头不去尾？
select length(ltrim('   张aaaa翠a山aaa')) out_put; --去左端
select length(rtrim('   张aaaa翠a山aaa   ')) out_put; ---去右端
select trim(leading 'a' from 'aaaa张aaaa翠a山aaa') out_put;  --左端
select trim(trailing 'a' from 'aaaa张aaaa翠a山aaa') out_put; ---右端（与a的个数无关，不管尾部多少个都会去掉）
select lpad('周芷若',5,'*') as out_put; --指定字符串长度，剩余用其他字符填充（左填充）
select lpad('周芷若',2,'*') as out_put; --从左端计数为长度2
select rpad('张无忌',2,'!') as 输出结果; --从左端计数为长度2
select rpad('张无忌',12,'!@') as 输出结果; --右填充为长度为12的字符串
select replace('张无忌爱周芷若爱周芷若','周芷若','赵敏'); --全部替换
select round(-1.85) out_put;  ---先四舍五入再加符号
select round(3.23) out_put;
select ceil(-1.01) out_put;
select ceil(2.32) out_put;
select floor(-0.82) out_put;
select floor(2.45) out_put;
select round(1.23375,3); ---保留3位小数，与截断不同
select truncate(1.678,2); ---postgreSQL无法使用（截断）
select mod(10,-3);  ---取余
select 10%3;---取余
select 10/3;
select now(); ---当前日期与时间
select curdate(); ---当前日期
select curtime(); ---当前时间
select current_date; ---PostgreSQL用法
select current_time;
select year(now()) out_put; --MySQL
select extract(year from now()) out_put;--PostgreSQL
select extract(month from now()) out_put;
select extract(day from current_date) out_put;
select str_to_date('1998-3-2','%Y-%c-%d') out_put; ---PostgreSQL不能用
select to_date('1998-3-2','%Y-%c-%d') out_put;

select version();
select user;

关于case...when...的用法：

select salary 原始工资 ,department_id, case department_id
when '002' then salary*1.1
when '003' then salary*1.5
when '004' then salary*1.3
when '005' then salary*1.2
else salary
end as 新工资
from employees;

下面这道练习题是同学给的，适合小白进阶练习：

对employees表中的数据按salary分组：<6000，5001-6000，6001-8000，>8000，并统计每组人数。

select 
case 
when salary > 8000 then 'A'
when salary > 6000 then 'B'
when salary > 5000 then 'C'
else 'D'
end as 分组, count(salary)
from employees group by 分组;

group by 字段列必须在前面的select 内容中出现，要先分组才能进行计数，count()函数功能是计算表某一列的行数或整张表的行数，因此count(salary)可以替换为count(*)。

小拓展：

去掉组人数小于3的组，并按照组人数从大到小排列。

select 
case 
when salary > 8000 then 'A'
when salary > 6000 then 'B'
when salary > 5000 then 'C'
else 'D'
end as 分组, count(salary)
from employees group by 分组 having count(salary) >3 order by count(salary) desc;

group by 后对数据进行筛选是针对的是分组后的数据，连接条件的关键字是having不是where，where应用于整张表。

下面又是一些小练习：

select avg(salary),department_id from employees group by department_id;
select count(*), department_id from employees group by department_id;
select max(salary),department_id from employees group by department_id;
select count(*), department_id from employees group by department_id having count(*) > 2;
select max(salary) 最高工资 ,min(salary) 最低工资, avg(salary) 平均工资,sum(salary) 总工资,department_id from employees group by department_id order by department_id;
select max(salary)-min(salary) from employees;
select min(salary) , department_id from employees where manager_id is not null group by department_id having min(salary) >= 6000 ; -----******------
select department_id,count(*),avg(salary) from employees group by department_id order by avg(salary) desc;
select department_id,count(*) 员工人数 from employees group by department_id;
select sum(salary) from employees;
select avg(salary) from employees;
select max(salary) from employees;
select min(salary) from employees;
select count(salary) from employees;
select sum(salary) 总薪水, round(avg(salary),2) 平均薪水, max(salary) 最高薪水, min(salary) 最低薪水, count(salary) 员工数目 from employees;
select sum(last_name) from employees;
select max(last_nanme) from employees;
select count(manager_id) from employees; ----没有计算null值
select count(distinct salary),count(salary) from employees;
select count(*) from employees where department_id='003';

小练习后续更新中。。。。