1、 基础查询
2、 条件查询
3、 排序查询
4、 常见函数
**
**
进阶一:基础查询
**
**
/*
语法:
select 查询列表 from 表名;
特点:
1、查询的结果集 是一个虚拟表
2、select类似列表System.out.println(打印内容);
select后面跟的查询列表,可以有多个部分组成,中间用逗号隔开
例如:select 字段1,字段2,表达式 from 表;
3、执行顺序
select first_name from employees;
先判断form 表是否存在,然后判断select first_name存在否
4、查询列表可以是:字段、表达式、常量、函数等。
*/
#一、查询常量
SELECT 100;
#二、查询表达式
SELECT 100%3;
#三、查询单个字段
SELECT last_name
FROM employees
;
#四、查询多个字段
SELECT last_name
,email
,salary
FROM employees;
#五、查询所有字段
SELECT * FROM employees;
#F12:对齐格式
SELECT
first_name
,
last_name
,
email
,
phone_number
,
job_id
,
salary
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拼接成的全名,最终为起别名:姓 名
#使用concat拼接函数
SELECT CONCAT(first_name," ",last_name) AS “姓 名” FROM employees;
#九、distinct的使用
#需求:查询员工涉及到的部门编号有哪些
SELECT DISTINCT department_id FROM employees; #删除了重复项
#十、查看表的结构
DESC employees;
#进阶二:条件查询
/*
语法:
select 查询列表
from 表名
where 筛选条件;
执行顺序:
1 from子句
2 where子句
3 select子句
特点:
1、按关系表达式筛选
关系运算符:> < >= <= = <>是不等于 也可以使用!= 但不建议
2、按逻辑表达式筛选
逻辑运算符:and,or,not
3、模糊查询
like
in
between and
is null
*/
SELECT
CONCAT(last_name, " ", first_name) AS “姓名”,
salary AS “薪水”
FROM
employees
WHERE salary > 15000 ;
#案例1;查询部门编号不是100的员工信息
SELECT *
FROM employees
WHERE department_id
= 100;
#案例2:查询工资<15000的姓名、工资
SELECT
CONCAT(last_name, " ", first_name) AS “姓名”,
salary
FROM
employees
WHERE salary
< 15000 ;
#二 、按逻辑表达式筛选
#案例1;查询部门编号不是50-100之间员工姓名、部门编号、邮箱
SELECT CONCAT(first_name
,’ ',last_name
) AS ‘姓名’ , department_id
,email
FROM employees
WHERE 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
/*一般和通配符搭配使用,对字符型数据进行部分匹配查询
常见的通配符:
_ 任意单个字符
% 任意多个字符
*/
#案例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 ‘__%’;
#2、in
/*
功能:查询某字段的值是否属于指定的列表之内
a in(常量值1,常量值2,常量值3)
in/not in
*/
#案例1:查询部门编号30/50/90的员工名,部门编号
SELECT last_name ,department_id
FROM employees
WHERE department_id IN(30,50,90);
#案例2:查询工种编号不是ST_CLERK或IT_PROG的员工信息
SELECT *
FROM employees
WHERE job_id NOT IN (‘ST_CLERK’,‘IT_PROG’); #数值型不需要单引号 ,字符型需要单引号
#3、between and
/*
功能:判断某个字段的值是否介于xx之间
between 关键字1 and 关键字2 /not between and
*/
#案例1;查询部门编号是30-90之间的部门编号、员工姓名
SELECT department_id ,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;
#案例2:查询年薪不是100000-200000之间的员工姓名、工资、年薪
SELECT last_name ,salary,salary12(1+IFNULL(commission_pct
,0)) AS ‘年薪’
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;
/*
= 只能判断普通的内容
is 只能判断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 ;
#案例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 ,salary12*(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;
**
**
进阶4:常见函数
**
**
/*
函数:类似于java中学过的“方法”,
为了解决某个问题,将编写的一系列的命令集合封装在一起,对外仅仅暴露方法名,供外部调用
1、自定义方法(函数)
2、调用方法(函数)★
叫什么 :函数名
干什么 :函数功能
常见函数:
字符函数
concat
substr
length
char_length
upper
lower
trim
left
right
lpad
rpad
instr
strcmp
数学函数
abs
ceil
floor
round
truncate
mod
日期函数
now
curtime
curdate
datediff
date_format
str_to_date
流程控制函数
if
case
*/
#一、字符函数
1、CONCAT 拼接字符
SELECT CONCAT(‘hello,’,first_name,last_name) 备注 FROM employees;
2、LENGTH 获取字节长度
SELECT LENGTH(‘hello,郭襄’);
3、CHAR_LENGTH 获取字符个数
SELECT CHAR_LENGTH(‘hello,郭襄’);
4、SUBSTRING 截取子串
/*
注意:起始索引从1开始!!!
substr(str,起始索引,截取的字符长度)
substr(str,起始索引)
*/
SELECT SUBSTR(‘张三丰爱上了郭襄’,1,3);
SELECT SUBSTR(‘张三丰爱上了郭襄’,7);
5、INSTR获取字符第一次出现的索引
SELECT INSTR(‘三打白骨精aaa白骨精bb白骨精’,‘白骨精’);
6、TRIM去前后指定的字符,默认是去空格
SELECT TRIM(’ 虚 竹 ') AS a;
SELECT TRIM(‘x’ FROM ‘xxxxxx虚xxx竹xxxxxxxxxxxxxxxxxx’) AS a;
7、LPAD/RPAD 左填充/右填充
SELECT LPAD(‘木婉清’,10,‘a’);
SELECT RPAD(‘木婉清’,10,‘a’);
8、UPPER/LOWER 变大写/变小写
#案例:查询员工表的姓名,要求格式:姓首字符大写,其他字符小写,名所有字符大写,且姓和名之间用_分割,最后起别名“OUTPUT”
SELECT UPPER(SUBSTR(first_name,1,1)),first_name FROM employees;
SELECT LOWER(SUBSTR(first_name,2)),first_name FROM employees;
SELECT UPPER(last_name) FROM employees;
SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),’_’,UPPER(last_name)) “OUTPUT”
FROM employees;
9、STRCMP 比较两个字符大小
SELECT STRCMP(‘aec’,‘aec’);
10、LEFT/RIGHT 截取子串
SELECT LEFT(‘鸠摩智’,1);
SELECT RIGHT(‘鸠摩智’,1);
#二、数学函数
1、ABS 绝对值
SELECT ABS(-2.4);
2、CEIL 向上取整 返回>=该参数的最小整数
SELECT CEIL(-1.09);
SELECT CEIL(0.09);
SELECT CEIL(1.00);
3、FLOOR 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-1.09);
SELECT FLOOR(0.09);
SELECT FLOOR(1.00);
4、ROUND 四舍五入
SELECT ROUND(1.8712345);
SELECT ROUND(1.8712345,2);
5、TRUNCATE 截断
SELECT TRUNCATE(1.8712345,1);
6、MOD 取余
SELECT MOD(-10,3);
a%b = a-(INT)a/bb
-10%3 = -10 - (-10)/33 = -1
SELECT -10%3;
SELECT 10%3;
SELECT -10%-3;
SELECT 10%-3;
#三、日期函数
1、NOW
SELECT NOW();
2、CURDATE
SELECT CURDATE(); #获取当前日期
3、CURTIME
SELECT CURTIME(); #获取当前时间
4、DATEDIFF
SELECT DATEDIFF(‘1998-10-29’,‘2020-12-4’);
5、DATE_FORMAT
SELECT DATE_FORMAT(‘1998-7-16’,’%Y年%M月%d日 %H小时%i分钟%s秒’) 出生日期;
SELECT DATE_FORMAT(hiredate,’%Y年%M月%d日 %H小时%i分钟%s秒’)入职日期
FROM employees;
6、STR_TO_DATE 按指定格式解析字符串为日期类型
SELECT * FROM employees
WHERE hiredate<STR_TO_DATE(‘3/15 1998’,’%m/%d %Y’);
#四、流程控制函数
1、IF函数
SELECT IF(100>9,‘好’,‘坏’);
#需求:如果有奖金,则显示最终奖金,如果没有,则显示0
SELECT IF(commission_pct IS NULL,0,salary12commission_pct) 奖金,commission_pct
FROM employees;
2、CASE函数
①情况1 :类似于switch语句,可以实现等值判断
CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
…
ELSE 结果n
END
案例:
部门编号是30,工资显示为2倍
部门编号是50,工资显示为3倍
部门编号是60,工资显示为4倍
否则不变
显示 部门编号,新工资,旧工资
SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary2
WHEN 50 THEN salary3
WHEN 60 THEN salary*4
ELSE salary
END newSalary
FROM employees;
②情况2:类似于多重IF语句,实现区间判断
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
…
ELSE 结果n
END
案例:如果工资>20000,显示级别A
工资>15000,显示级别B
工资>10000,显示级别C
否则,显示D
SELECT salary,
CASE
WHEN salary>20000 THEN ‘A’
WHEN salary>15000 THEN ‘B’
WHEN salary>10000 THEN ‘C’
ELSE ‘D’
END
AS a
FROM employees;