创建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';小练习后续更新中。。。。
