sql练习

#DISTINCT 去重，放在字段前，DESCRIBE 查看表结构
#1.查询员工12个月的工资总和，并起别名ANNUAL SALARY
SELECT * FROM employees;
SELECT employee_id,last_name,salary * (1 + IFNULL(commission_pct,0)) * 12 "ANNUAL SALARY" FROM employees;
#2.查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id FROM employees;
#3.查询工资大于12000的员工姓名和工资
SELECT last_name,salary FROM employees WHERE salary > 12000;
#4.查询员工号为176的员工姓名和部门号
SELECT last_name,job_id FROM employees WHERE employee_id = 176;
#5.显示表 departments 的结构，并查询其中全部数据
DESCRIBE departments;
SELECT * FROM departments;

#算术运算符练习：查询员工id为偶数的员工信息
#1.MOD 取模  
#2.FROM DUAL 伪表 该表主要目的是为了保证在使用SELECT语句中的语句的完整性而提供的 一般用于验证函数
SELECT * FROM employees WHERE employee_id MOD 2 = 0;

#比较运算符：比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL
#查询时进行比较，只要结果不是1则过滤掉数据，只会显示比较结果为1的数据
#1.字符串存在隐式转换，转换不成功，则看作0 
SELECT 1 = '1',1 = 'a',0 = 'a' FROM DUAL;
#2.两边都是字符串按照ANSI比较规则进行比较
SELECT 'a' = 'b','a' = 'a' FROM DUAL;
#3.只要有NULL参与判断，结果为NULL
SELECT 1 = NULL , NULL = NULL FROM DUAL;

#安全等于运算符 <=>
#在两个操作数均为NULL时，其返回值为1,当一个操作数为NULL时，其返回值为0
SELECT 1 <=> NULL,NULL <=> NULL;
SELECT * FROM employees	WHERE commission_pct = NULL;
SELECT * FROM employees	WHERE commission_pct <=>NULL;

SELECT * FROM employees	WHERE commission_pct IS NULL;
SELECT * FROM employees	WHERE commission_pct IS NOT NULL;

SELECT * FROM employees;
SELECT last_name,salary FROM employees WHERE salary < 5000 OR salary > 12000;
SELECT department_id,last_name FROM employees WHERE 
#department_id = 20 or department_id = 50;
department_id IN (20,50);
SELECT last_name,job_id FROM employees WHERE manager_id <=> NULL;#IS NULL
SELECT last_name,salary,commission_pct FROM employees WHERE NOT commission_pct <=> NULL;
SELECT last_name FROM employees WHERE last_name LIKE '__a%';
SELECT * FROM employees WHERE first_name LIKE '%e';
SELECT last_name,job_id,department_id FROM employees WHERE 
#错误写法department_id > 80 and department_id < 100;
department_id BETWEEN 80 AND 100;
SELECT last_name,salary,manager_id FROM employees WHERE manager_id IN (100,101,110);

#排序与分页
#查询工资最高的员工信息
SELECT * FROM employees ORDER BY salary DESC LIMIT 1;

#查询员工姓名，部门号，年薪，年薪降序，姓名升序
SELECT last_name,department_id,salary * (1 + IFNULL(commission_pct,0)) * 12 yearSal 
FROM employees
ORDER BY yearSal DESC,last_name;

#选择薪资不在8000 - 17000的员工的姓名和薪资，工资降序，显示21-40的数据
SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC LIMIT 20,20;

#查询邮箱中包含e的员工信息，并按照邮箱的字节数降序，再按部门编号升序
SELECT * FROM employees WHERE email REGEXP '[e]' ORDER BY LENGTH(email) DESC,department_id;
#email like '%e%'