DQL

1）基本查询

语法：select 查询列表 from 表名;

特点：
 1.查询结果是一张虚拟表， 不是真实存在
 2.查询列表，可以是常量、字段名、表达式、函数
    
    

代码
#语法：select 查询列表 from 表名;

#显示employees中所有字段信息
select * from employees;

#只显示employees中salary字段
select salary from employees;

#显示employees中部分字段
select first_name,last_name,salary,hiredate from employees;

#对查询产生的结果--虚拟表 给字段起别名
select first_name as 姓氏 ,last_name as 名字,salary as 工资 ,hiredate as 入职时间 
from employees;


select first_name as 姓氏 ,last_name as 名字,salary as 工资 ,hiredate as 入职时间,
salary+10000 as 涨薪一万后 
from employees;

select 99;

# 获取当前系统时间 new Date() 
select now();

2）条件查询

语法：select 查询列表 from 表名 where 条件;

特点：
    1.条件表达式：>  <  >=  <=  =  != <>
    2.逻辑表达式： and  or  not
    3.模糊条件: like  / between...and  /in/ not null

1.条件表达式:

案例1：查询员工薪资>10000的员工信息
select * from employees where salary > 10000;

案例2：查询姓氏不是 john 的员工的薪资和姓名
select salary,first_name,last_name from employees where first_name <> 'John';

2.逻辑表达式

案例3：查询员工的薪资大于10000并且小于20000的员工姓名和薪资
select last_name,salary from employees where salary > 10000 and salary < 20000;

案例4：查询员工的薪资不在10000到20000之间的员工信息
select * from employees where salary < 10000 or salary > 20000;
select * from employees where salary not between 10000 and 20000;

3.模糊条件

案例5：查询员工名字中第一个字符是e的员工信息
select last_name from employees where last_name like 'e%';

案例6：查询员工名字中第二个字符是e第五个字符是a的员工的名字和薪资
select last_name,salary from employees where last_name like '_e__a%';

案例7：查询员工的姓名中第二个字符是_的员工信息

select * from employees where last_name like '_\_%';

案例8: 查询员工的年薪在10w到20w之间的员工名字和薪资

select last_name as 姓名, salary as 月薪,salary*12 as 年薪
from employees where salary*12 between 100000 and 200000;

案例9:查询员工的编号为(1,102,104,105,109)的员工信息

select * from employees where employee_id = 1 or employee_id = 102 
or employee_id = 104 or employee_id = 105 or employee_id = 109;

select * from employees where employee_id in(1,102,104,105,109);

案例10：查询有奖金的员工信息
select * from employees where commission_pct is null;
select * from employees where commission_pct is not null;

、