进阶一:基础查询
语法:
select 查询列表 from 表名;
特点:
-
select后面跟的查询列表,可以有多个部分组成,中间用逗号隔开
例如:select 字段1,字段2,表达式 from 表; -
执行顺序
① from子句
② select子句
-
查询列表可以是:字段、表达式、常量、函数等
一、查询常量
SELECT 100 ;
二、查询表达式
SELECT 100%3;
三、查询单个字段
SELECT last_name
FROM employees
;
四、查询多个字段
SELECT last_name
,email
,employee_id
FROM employees;
五、查询所有字段
SELECT * FROM employees
;
六、查询函数(调用函数,获取返回值)
SELECT DATABASE();
SELECT VERSION();
SELECT USER();
七、起别名
方式一:使用as关键字
SELECT USER() AS 用户名;
SELECT USER() AS “用户名”;
SELECT USER() AS ‘用户名’;
SELECT last_name AS “姓 名” FROM employees;
方式二:使用空格
SELECT USER() 用户名;
SELECT USER() “用户名”;
SELECT USER() ‘用户名’;
SELECT last_name “姓 名” FROM employees;
八、+的作用
– 需求:查询 first_name 和last_name 拼接成的全名,最终起别名为:姓 名
SELECT CONCAT(first_name,last_name) AS “姓 名”
FROM employees;
九、distinct的使用
需求:查询员工涉及到的部门编号有哪些(不能重复)
SELECT DISTINCT department_id FROM employees;
十、查看表的结构
DESC employees;
SHOW COLUMNS FROM employees;
```mys
#ifnull(表达式1,表达式2)
/*
表达式1:可能为null的字段或表达式
表达式2:如果表达式1为null,则最终结果显示的值
功能:如果表达式1为null,则显示表达式2,否则显示表达式1
*/
SELECT commission_pct,IFNULL(commission_pct,'空') FROM employees;
进阶二:条件查询
语法:
select 查询列表
from 表名
where 筛选条件;执行顺序:
①from子句
②where子句
③select子句select last_name,first_name from employees where salary>20000;
特点:
1、按关系表达式筛选
关系运算符:> < >= <= = <>
补充:也可以使用!=,但不建议
2、按逻辑表达式筛选
逻辑运算符:and or not
补充:也可以使用&& || ! ,但不建议
3、模糊查询
like
in
between and
is null
一、按关系表达式筛选
#案例1:查询部门编号不是100的员工信息
SELECT *
FROM employees
WHERE department_id <> 100;
#案例2:查询工资<15000的姓名、工资
SELECT last_name,salary
FROM employees
WHERE salary<15000;
二、按逻辑表达式筛选
#案例1:查询部门编号不是 50-100之间员工姓名、部门编号、邮箱
#方式1:
SELECT last_name,department_id,email
FROM employees
WHERE department_id <50 OR department_id>100;
#方式2:
SELECT last_name,department_id,email
FROM employees
WHERE NOT(department_id>=50 AND department_id<=100);
#案例2:查询奖金率>0.03 或者 员工编号在60-110之间的员工信息
SELECT *
FROM employees
WHERE commission_pct>0.03 OR (employee_id >=60 AND employee_id<=110);
三、模糊查询
1、like
/*
功能:一般和通配符搭配使用,对字符型数据进行部分匹配查询
常见的通配符:
_ 任意单个字符
% 任意多个字符,支持0-多个
like/not like
*/
#案例1:查询姓名中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE ‘%a%’;
#案例2:查询姓名中包含最后一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE ‘%e’;
#案例3:查询姓名中包含第一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE ‘e%’;
#案例4:查询姓名中包含第三个字符为x的员工信息
SELECT *
FROM employees
WHERE last_name LIKE ‘__x%’;
#案例5:查询姓名中包含第二个字符为_的员工信息
SELECT *
FROM employees
WHERE last_name LIKE ‘__%’;
SELECT *
FROM employees
WHERE last_name LIKE ‘_KaTeX parse error: Expected group after '_' at position 1: _̲%' ESCAPE '’;
2、in
/*
功能:查询某字段的值是否属于指定的列表之内
a in(常量值1,常量值2,常量值3,…)
a not in(常量值1,常量值2,常量值3,…)
in/not in
*/
#案例1:查询部门编号是30/50/90的员工名、部门编号
#方式1:
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);
#方式2:
SELECT last_name,department_id
FROM employees
WHERE department_id = 30
OR department_id = 50
OR department_id = 90;
#案例2:查询工种编号不是SH_CLERK或IT_PROG的员工信息
#方式1:
SELECT *
FROM employees
WHERE job_id NOT IN(‘SH_CLERK’,‘IT_PROG’);
#方式2:
SELECT *
FROM employees
WHERE NOT(job_id =‘SH_CLERK’
OR job_id = ‘IT_PROG’);
3、between and
/*
功能:判断某个字段的值是否介于xx之间
between and/not between and
*/
#案例1:查询部门编号是30-90之间的部门编号、员工姓名
#方式1:
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;
#方式2:
SELECT department_id,last_name
FROM employees
WHERE department_id>=30 AND department_id<=90;
#案例2:查询年薪不是100000-200000之间的员工姓名、工资、年薪
SELECT last_name,salary,salary12(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary12(1+IFNULL(commission_pct,0))<100000 OR salary12(1+IFNULL(commission_pct,0))>200000;
SELECT last_name,salary,salary12(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary12(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;
4、is null/is not null
#案例1:查询没有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NULL;
#案例2:查询有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;
SELECT *
FROM employees
WHERE salary IS 10000;
#----------------对比------------------------------------
= 只能判断普通的内容
IS 只能判断NULL值
<=> 安全等于,既能判断普通内容,又能判断NULL值
SELECT *
FROM employees
WHERE salary <=> 10000;
SELECT *
FROM employees
WHERE commission_pct <=> NULL;
进阶3:排序查询
语法:
select 查询列表
from 表名
【where 筛选条件】
order by 排序列表
执行顺序:
①from子句
②where子句
③select子句
④order by 子句
举例:
select last_name,salary
from employees
where salary>20000
order by salary ;
select * from employees;
特点:
1、排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合
2、升序 ,通过 asc ,默认行为
降序 ,通过 desc
一、按单个字段排序
#案例1:将员工编号>120的员工信息进行工资的升序
SELECT *
FROM employees
ORDER BY salary ASC ;
#案例1:将员工编号>120的员工信息进行工资的降序
SELECT *
FROM employees
WHERE employee_id>120
ORDER BY salary DESC;
二、按表达式排序
#案例1:对有奖金的员工,按年薪降序
SELECT salary12(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary12(1+IFNULL(commission_pct,0)) DESC;
三、按别名排序
#案例1:对有奖金的员工,按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC;
四、按函数的结果排序
#案例1:按姓名的字数长度进行升序
SELECT last_name
FROM employees
ORDER BY LENGTH(last_name);
五、按多个字段排序
#案例1:查询员工的姓名、工资、部门编号,先按工资升序,再按部门编号降序
SELECT last_name,salary,department_id
FROM employees
ORDER BY salary ASC,department_id DESC;
六、按列数排序
SELECT * FROM employees
ORDER BY 2 DESC;
SELECT * FROM employees
ORDER BY first_name;