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;
、