一、当前学习MySQL,所查询语句基于myemployees数据库,库中包含四张表'employees'、'departments'、'jobs'、'locations',每张表包含字段如下所示:
①'employees'表包含字段:employees_id、first_name、last_name、email、phone_number、job_id、salary、commission_pct、manager_id、department_id、hiredate;
②'departments'表包含字段:department_id、department_name、manager_id、location_id;
③‘jobs’表包含字段:job_id、job_title、min_salary、max_salary;
④location_id、street_address、postal_code、city、state_province、country_id.
1、like模糊查询
# 查询姓中有'a'的人员信息;
select * from employees where first_name like '%a%';
查询结果:(截图为部分记录)
②like结合转义字符使用,直接用’\‘或使用'escape';
select last_name from employees where last_name like '_\_%';
select last_name from employees where last_name like '_$_%' ESCAPE '$';
2、between and ①包含临界值;②提高语句简洁度;③两个范围位置不能颠倒,小的在前;
select * from employees where employee_id>=100 and employee_id<=120;
select * from employees where employee_id between 100 and 120;
3、'IN' 用法:①用于判断某字段的值是否属于in列表中的某一项
特点:①提高语句简洁度,in列表的值类型必须一致或兼容
#查询员工工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号;
select last_name,job_id from employees where job_id='IT_PROG' OR job_id='AD_VP' OR job_id='AD_PRES';
select last_name,job_id from employees WHERE job_id IN('IT_PROG','AD_VP','AD_PRES');
4、IS NULL:is null 和 IS NOT NULL可判断字段是否为空;
#查询没有奖金的员工名和奖金率;
select last_name,commission_pct from employees WHERE commission_pct is NULL;
5、<=>:安全等于
'is null' pk '<=>' 区别:①<=>既可以判断null值,又能判断普通数值;②is null仅仅可以判断null值;
#查询工资大于12000的员工姓名:
#查询员工编号为176号的员工姓名、部门编号、年薪
select last_name,commission_pct from employees WHERE commission_pct<=>null;
select last_name,salary from employees WHERE salary <=>12000;
select last_name,department_id,salary*12 from employees where employee_id<=>'176';
6、IFNULL(判断值,为空输出值):如果为空,处理成指定值。
select last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) as
年薪 from employees where employee_id<=>'176';
select salary,last_name from employees WHERE commission_pct is null and salary<18000;
7、<>不包含
#查询工种号不包含'IT'但薪水等于12000的员工信息
select * from employees WHERE job_id<>'IT' or salary<=>12000;
8、DESC查询表结构、distinct去重
#查询departments表中涉及到哪些位置为空;#查询departments不重复的本地位置
DESC departments;
SELECT DISTINCT location_id from departments;
注:由于篇幅原因,查询结果不截图,只记录如何使用,持续补充中。