#进阶1:基础查询
/*
语法:
select 查询列表 from 表名1、查询列表可以是表中的字段、常量值、表达式、函数
2、查询结果是一个虚拟的表格
类似于:
*/USE myemployees;
#1.查询表中的单个字段
SELECT last_name FROM employees;#2.查询表中的多个字段
SELECT last_name,salary,email FROM employees;#3.查询表中的所有字段
SELECT
`employee_id`,
`first_name`,
`last_name`,
`email`,
`phone_number`,
`job_id`,
`salary`,
`commission_pct`,
`manager_id`,
`department_id`,
`hiredate`
FROM
employees ;SELECT * FROM employees;
#4.查询常量值
SELECT 100;
SELECT 'john';#5.查询表达式
SELECT 100*98;#6.查询函数
SELECT VERSION();#7.起别名
#方式一
SELECT 100*98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式二
SELECT last_name 姓,first_name 名 FROM employees;
#案例:查询salary,显示结果out put
SELECT salary AS "out put" FROM employees;#8.去重
#案例:查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;#9. + 的作用
#案例:查询员工名和姓连接成一个字段,并显示为姓名
/*
只有一个功能:运算符select 100+90 :两个操作数都为数值型,加法运算
select '123'+90:其中一个为字符型,试图将字符型数值转换成数值型,
若转换成功则继续做加法运算
失败则将字符转换为0继续运算
select 'john'+90
select null+0: 只要其中一方为null,则结果肯定为null*/
SELECT CONCAT('a','b','c') AS 结果;SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
SELECT last_name,first_name FROM employees;
SELECT
last_name + first_name AS 姓名
FROM
employees ;#练习
SELECT employee_id,last_name,
salary*12 'annual salary'
FROM employees;#显示表结构
DESC departments;#显示表employees的全部列,各列之间用逗号链接,列头显示成out_put
SELECT
CONCAT('first_name', ',', 'last_name', ',',`job_id`,',',IFNULL(`commission_pct`,0)) AS out_put
FROM
employees ;SELECT
IFNULL(`commission_pct`, 0) AS 奖金率,
`commission_pct`
FROM
employees ;
#进阶2:条件查询
/*
语法:
select
查询列表
from
表名
where
筛选条件
顺序:w→f→s分类:
1、按条件表达式筛选 < > = !=或<> <= >=
2、按逻辑表达式筛选 && || ! (与或非)
and or not
3、模糊查询 like
between and
in
is null
*/
USE myemployees;
#1、按条件表达式筛选
#案例1:查询工资>12000员工
SELECT
*
FROM
employees
WHERE salary > 12000 ;#案例2:部门编号不等于90号的员工名和部门编号
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id <> 90;#2、按逻辑表达式筛选
#案例1:工资在10000到20000之间的员工名、工资、奖金
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
salary>=10000 AND salary<=20000;
#案例2:部门编号不在90-110之间的或工资高于15000的员工信息
SELECT
*
FROM
employees
WHERE
NOT(department_id>=90 AND department_id<=110) OR salary>15000#3、模糊查询
/*
like
between and
in
is null | is not null
*/
#1.like模糊匹配
/*
一般和通配符使用
% 任意多个字符(包含0个)
_ 任意单个字符*/
#like
#案例1:员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
#案例2:员工名中第三个字符为n,第五个字符为l的员工名和工资
SELECT
last_name,salary
FROM
employees
WHERE
last_name LIKE '__n_l%';
#案例3:员工名中第二个字符为_的员工名
#way1
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_\_%';
#way2
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';
#between and 包含临界值,临界值不可调换顺序#案例1:查询员工编号100-120之间的所有员工信息
#way1
SELECT
*
FROM
employees
WHERE
employee_id>=100 AND employee_id<=120;
#way2
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 120;
#in 是否属于in列表中的某一项/*
值类型必须一致或兼容
不支持通配符`myemployees`
*/
#案例:查询员工的工种编号是IT_PROG,AD_VP,AD_PRES中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN('IT_PROG' ,'AD_VP', 'AD_PRES')#is null
#案例1:查询没有奖金的员工名和奖金率
#way1
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NOT NULL;
#安全等于<=>:可判断null值
#way2
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
#案例2:查询员工号为176的员工姓名、部门号和年薪
SELECT
last_name,
department_id,
salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees;#homework
SELECT
CONCAT(last_name,first_name) AS 姓名,
salary
FROM
employees
WHERE
salary>12000;
SELECT
CONCAT(last_name, first_name) AS 姓名,
employee_id,
department_id,
salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
WHERE employee_id = 176 ;SELECT
CONCAT(last_name, first_name) AS 姓名,
salary
FROM
employees
WHERE
salary<5000 OR salary>12000;
SELECT
CONCAT(last_name, first_name) AS 姓名,
job_id
FROM
employees
WHERE
manager_id IS NULL;
SELECT
CONCAT(last_name, first_name) AS 姓名
FROM
employees
WHERE
last_name LIKE '__a%';
SELECT
CONCAT(last_name, first_name) AS 姓名,
salary,
commission_pct
FROM
employees
WHERE
commission_pct IS NOT NULL
SELECT
*
FROM
employees
WHERE
job_id <> 'IT';
DESC departments;SELECT
DISTINCT location_id
FROM
departments;
SELECT
*
FROM
employees
WHERE
commission_pct LIKE '%%';
#%%不包含null
#进阶3:排序查询
/*语法:
select 查询列表
from 表
where 筛选条件
order by 排序列表(asc、desc)默认升序order by 一般放最后,limit除外
*/#案例1:查询员工信息,工资高到低(降序,desc)
SELECT * FROM employees ORDER BY salary DESC ;
SELECT * FROM employees ORDER BY salary ASC ;#案例2:查询部门编号大于等于90的员工信息,按入职时间先后
SELECT
*
FROM
employees
WHERE department_id >= 90
ORDER BY hiredate ASC ;#案例3:按年薪高低显示员工的信息和年薪(表达式)
SELECT
*,
salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY salary * 12 *(1+ IFNULL(commission_pct, 0)) DESC;#案例4:按年薪高低显示员工的信息和年薪(别名)
SELECT
*,
salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY 年薪 ASC;#案例5:按姓名长度显示员工姓名和工资(按函数)
SELECT
LENGTH(last_name) 字节长度,last_name,salary
FROM
employees
ORDER BY LENGTH(last_name) DESC;#案例6:查询员工信息,先按工资排序,再按员工编号排序(按多个字段排序)
SELECT
*
FROM
employees
ORDER BY
salary ASC,employee_id DESC;
#工资不在8000到17000的员工给姓名和工资,工资降序
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
#进阶4:常见函数
/*
调用:
select 函数名() 【from 表】;
分