查询
基础查询
一、语法
select 查询列表(查询的东西
from 表名;
二、特点
1、查询列表可以是字段、常量、表达式、函数,也可以是多个
2、查询结果是一个虚拟表
三、示例
1、查询单个字段
select 字段名 from 表名;
2、查询多个字段
select 字段名,字段名 from 表名;
3、查询所有字段
select * from 表名
4、查询常量
select 常量值;
如select 100;
select ‘willam’
select 10*8
注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要
5、查询函数
select 函数名(实参列表);
6、查询表达式
select 100/1234;
7、起别名
①as
select 100*98 as 结果
SELECT last_name AS 姓,first_name AS 名 FROM employees;
②空格
select last_name 姓,first_name 名 from employees;
8、去重
select distinct 字段名 from 表名;
SELECT DISTINCT department_id FROM employees;
9、+
仅一个作用:做加法运算
select 数值+数值; 直接运算
select 字符+数值;先试图将字符转换成数值,如果转换成功,则继续运算;否则转换成0,再做运算
select null+值;结果都为null
10 补充函数
concat函数 拼接字符
功能:拼接字符
select concat(字符1,字符2,字符3,…);
SELECT CONCAT('ben ','and ','wiliam ') AS 兄弟;
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
ifnull函数 判断null
功能:判断某字段或表达式是否为null,如果为null 返回指定的值,否则返回原本的值
select ifnull(commission_pct,0) from employees;
SELECT
IFNULL(commission_pct,0) AS 奖金率,
commission_pct
FROM
employees;
isnull函数 判断null
功能:判断某字段或表达式是否为null,如果是,则返回1,否则返回0
0.【快捷键】
F12 刷格式
课后题
(1) 显示表departments 的结构,并查询其中的全部数据
DESC departments;
SELECT * FROM departments;
(2) 显示出表 employees 中的全部 job_id(不能重复)
SELECT DISTINCT job_id FROM jobs;
(3)显示出表 employees 的全部列,各个列之间用逗号连接,列头显示成 OUT_PUT
wrong:
#`commission_pct`有些为空,那他和其他concat拼接,结果就都成了null。所以要分情况讨论,引入ifnull函数。
-- select
-- concat(`first_name`,',',`last_name`,',',`email`,',',`phone_number`,',',`job_id`,',',`salary`,',',`commission_pct`,',',`manager_id`,',',`department_id`,',',`hiredate`) as out_put
-- from
-- employees;
true:
SELECT
CONCAT(`first_name`,',',`last_name`,',',`email`,',',`phone_number`,',',`job_id`,',',`salary`,',',IFNULL(commission_pct,0) ,',',`manager_id`,',',`department_id`,',',`hiredate`) AS out_put
FROM
employees;
条件查询
一、语法
select 查询列表
from 表名
where 筛选条件
二、筛选条件的分类
1、简单条件运算符
大于 >
小于 <
等于 =
不等于 <>(建议用这个) 和 !=
大于等于 >=
小于等于**<=**
安全等于 <=> 可以判断null
2、逻辑运算符
作用:用于连接条件表达式
&& and (同1为1)
|| or (有1为1 )
! not(非1=0)
3、模糊查询(搭配通配符)
-
like :一般搭配通配符使用,可以判断字符型或数值型
-
通配符:% 任意多个字符,_ 任意单个字符
-
between and
-
in :in列表内容必须统一或兼容(‘123’可转换成123)
【 in不支持通配符】 -
is null /is not null:用于判断null值,因为‘’=‘’号不能判断null
练习
- 查询工资大于10000的员工信息
SELECT
*
FROM
employees
WHERE
salary>10000;
- 查询部门编号不等于90的员工名和部门编号
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id <>90;
- 查询工资在10000-20000的员工名,工资及奖金
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE salary BETWEEN 10000 AND 20000 ;
- 查询部门编号不是在90—110之间,或者工资高于15000的员工信息
SELECT *
FROM
employees
WHERE department_id < 90
OR department_id > 110
OR salary > 15000 ;
- 查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE last_name LIKE '%a%' ;
- 查询员工名中第三个字符为e,第五个字符为a的员工名和工资
SELECT
last_name,
salary
FROM
employees
WHERE last_name LIKE '__e_a%' ;
- 查询员工名中第二个字符为_的员工名
SELECT last_name
FROM employees
WHERE last_name LIKE '_d_%' ESCAPE 'd';
- 查询员工编号在100-120之间员工的信息
SELECT *
FROM employees
WHERE employee_id BETWEEN 100 AND 120;
- 查询员工工种编号是IT_PROG、AD_VP、AP_PRES的一个的员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE jOb_id IN ('IT_PROG', 'AD_VP', 'AD_PRES') ;
- 没有奖金的员工名和奖金率
SELECT
last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;
is null PK <=>
------- 普通类型的数值 null值 可读性
is null × √ √
<=> √ √ ×
- 查询有奖金的员工名和奖金率
SELECT
last_name,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
课后练习:
1. 查询工资大于 12000 的员工姓名和工资
SELECT
last_name,salary
FROM
employees
WHERE salary>12000;
2. 查询员工号为 176 的员工的姓名和部门号和年薪
SELECT
last_name,
department_id,
salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
WHERE employee_id = 176 ;
3. 选择工资不在 5000 到 12000 的员工的姓名和工资
SELECT
last_name,
salary
FROM
employees
WHERE salary < 5000
OR salary > 12000 ;
4. 选择在 20 或 50 号部门工作的员工姓名和部门号
SELECT
last_name AS 姓名,
department_id AS 部门号
FROM
employees
WHERE department_id = 20
OR department_id = 50 ;
5. 选择公司中没有管理者的员工姓名及 job_id
SELECT
last_name,
job_id
FROM
employees
WHERE manager_id IS NULL ;
6. 选择公司中有奖金的员工姓名,工资和奖金级别
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE commission_pct IS NOT NULL ;
7. 选择员工姓名的第三个字母是 a 的员工姓名
SELECT
last_name
FROM
employees
WHERE last_name LIKE '__a%' ;
8. 选择姓名中有字母 a 和 e 的员工姓名
SELECT
last_name
FROM employees
WHERE last_name LIKE '%a%e%';
9. 显示出表 employees 表中 first_name 以 'e' 结尾的员工信息
SELECT
*
FROM
employees
WHERE first_name LIKE '%e' ;
10. 显示出表 employees 部门编号在 80-100 之间的姓名、职位
SELECT
last_name,
job_id
FROM
employees
WHERE department_id BETWEEN 80
AND 100 ;
11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
SELECT
last_name,
job_id
FROM
employees
WHERE manager_id = 100
OR manager_id = 101
OR manager_id = 110 ;
经典面试题:
试问:SELECT * FROM employees; 和 SELECT * FROM employees WHERE
commission_pct LIKE ‘%%’ AND last_name LIKE ‘%%’; 结果是否一样?说明原因
答:不一样!如果判断的字段有NULL 值不一样。
换成:SELECT * FROM employees WHERE commission_pct LIKE ‘%%’ OR last_name LIKE ‘%%’;
这样才一样。
排序查询
一、语法
select 查询列表
from 表
where 筛选条件
order by 排序列表 【asc升序 | desc降序 】
二、特点
1、asc :升序,如果不写默认升序
desc:降序
2、排序列表 支持 单个字段、多个字段、函数、表达式、别名
3、order by的位置一般放在查询语句的最后(除limit语句之外)
练习
# 1.按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT
*,
salary * 12 *(1+ IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY salary * 12 *(1+ IFNULL(commission_pct, 0)) DESC ;
# 2. 按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT
*,
salary * 12 *(1+ IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY 年薪 DESC ;
# 3.按姓名长度显示员工的姓名和工资【按函数排序】
SELECT
LENGTH(last_name),
last_name,
salary
FROM
employees
ORDER BY LENGTH(last_name) DESC ;
# 4.查询员工信息,要求先按工资排序,再按员工编号排序【按多个字段排序】
SELECT
*
FROM
employees
ORDER BY salary ASC,
employee_id DESC ;
# 5. 查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序
SELECT
last_name,
department_id,
salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY 年薪 DESC,
last_name ASC ;
#6.选择工资不在8000到17000的员工的姓名和工资,按工资升序
SELECT last_name,salary
FROM employees
ORDER BY salary ;
#7.查询邮箱中包含e的员工信息。先按邮箱的字节数降序,再按部门号升序
SELECT
*
FROM
employees
ORDER BY LENGTH(email) DESC,
department_id ASC ;
转义字符:
- \ 反斜杠
- ‘a%’ escape ‘a’ 【a可为任意字符,表示是通配符】