1.基础查询
语法:select 查询列表 from 表名;
特点:
-
查询的结果集是一个虚拟表
-
select后面的查询列表,可以有多个部分组成,中间用逗号隔开
-
select 字段1,字段2,表达式 from 表;
-
-
查询列表可以是:字段,表达式,常量,函数等
-
#一、查询常量 SELECT 100; #二、查询表达式 SELECT 100%3; #三、查询单个字段 SELECT last_name FROM employees; #四、查询多个字段 SELECT last_name ,email FROM employees; #五、查询所有字段 SELECT * FROM employees; #六、查询函数(调用函数,获取返回值) SELECT DATABASE(); SELECT VERSION(); #七、起别名 #方式一:使用as关键字 SELECT USER() AS 用户名; SELECT USER() AS "用户名"; SELECT USER() AS '用户名'; #像以下这种起别名,为了避免语法错误,应该给别名添加单/双引号 SELECT last_name AS "姓 名" FROM employees; #方式二:使用空格 SELECT USER() 用户名; SELECT USER() "用户名"; SELECT USER() '用户名'; #八、需求:查询last_name和first_name拼接成的全名,最终起别名为:姓 名 /* mysql中+的作用: 1、加法运算 ①两个操作数都是数值型 100+11, 输出111 ②其中一个操作数为字符型,将字符型数据强转为数值型,如果无法转换,则直接当做0处理 '梁晓芳'+100,输出100 ③其中一个操作数为null null+null,输出null null+100,输出null */ #使用concat拼接函数 SELECT CONCAT(last_name,first_name) AS "姓 名" FROM employees; #九、distinct的使用 #需求:查询员工涉及到的部门编号有哪些,去除相同的部门编号 SELECT DISTINCT department_id FROM employees; #十、查看表的结构 DESC employees; SHOW COLUMNS FROM employees; #ifnull(表达式1,表达式2) /* 表达式1:可能为null的字段或表达式 表达式2:如果表达式1为null,则最终结果显示的值 功能:如果表达式1为null,则显示表达式2,否则显示表达式1 */
-
2.条件查询
语法:select 查询列表 from 表名 where 筛选条件;
执行顺序:
①from子句
②where子句
③select子句
SELECT last_name,first_name FROM employees WHERE salary>20000
特点:
-
按关系表达式筛选
-
关系运算符:> < >= <= = <>不等于 也可以使用!=,不推荐
-
-
按逻辑表达式筛选
-
逻辑运算符:and or not
-
-
模糊查询
-
like
-
in
-
between and
-
is null
-
#一、按关系表达式筛选
#案例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(last_name,first_name),department_id,email FROM employees WHERE department_id > 100 OR department_id < 50;
#方式二:
SELECT CONCAT(last_name,first_name),department_id,email FROM employees WHERE NOT(department_id >=50 AND department_id <=100);
#案例2:查询奖金率>0.03或者员工编号在60-100之间的与员工信息
SELECT * FROM employees WHERE commission_pct>0.03 OR (employee_id >=60 AND employee_id <=110);
#三、模糊查询
#1、like
/*
功能:一般和通配符搭配使用,对字符型数据进行部分匹配查询
常见的通配符:
_ 任意单个字符
% 任意多个字符,支持0到多个
*/
#案例1:查询姓中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
#案例2:查询姓中最后一个字符为e的员工信息
SELECT * FROM employees WHERE last_name LIKE '%e';
#案例3:查询姓中第三个字符为x的员工信息
SELECT * FROM employees WHERE last_name LIKE '__x%';
#案例4:查询姓中包含第二个字符为_的员工信息
#设置$为转义符
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
#2、in
/*
功能:查询某字段的值是否属于指定的列表之内
a in (常量值1、常量值2、常量值3...)
a not in (常量值1、常量值2、常量值3...)
*/
#案例1:查询部门编号是30/50/90的员工名、部门编号
SELECT CONCAT(last_name,first_name),department_id FROM employees WHERE department_id IN(30,50,90);
#案例2:查询工种编号不是SH_CLERK或IT_PROG的员工信息
SELECT * FROM employees WHERE job_id NOT IN('SH_CLERK','IT_PROG');
#3、between and
/*
功能:判断某个字段的值是否介于XX之间
*/
#案例1:查询部门编号是30-90之间的部门编号、员工姓名
SELECT department_id,CONCAT(last_name,first_name) FROM employees WHERE department_id BETWEEN 30 AND 90;
#案例2:查询年薪不是100000-200000之间的员工姓名、工资、年薪
SELECT CONCAT(last_name,first_name) AS '姓名',salary,salary*12*(1+IFNULL(commission_pct,0)) AS '年薪' FROM employees
WHERE salary*12*(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值
<=> 安全等于,既能判断普通内容,又能判断NULL值,语义性差
*/
SELECT * FROM employees WHERE commission_pct <=> NULL;
SELECT * FROM employees WHERE salary <=> 10000;
3.排序查询
语法:
select 查询列表 from 表名 [where 筛选条件] order by 排序列表
执行顺序:
-
from子句
-
where子句
-
select子句
-
order by子句
特点:
-
排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合
-
升序: 关键字asc(默认) 降序:关键字desc
#一、按单个字段排序
#案例1:将员工编号>120的员工信息进行工资的升序
SELECT * FROM employees WHERE employee_id>120 ORDER BY salary;
#二、按表达式排序
#案例1:对有奖金的员工,按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS '年薪' FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC ;
#三、按别名排序
#案例1:对有奖金的员工,按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS '年薪' FROM employees WHERE commission_pct IS NOT NULL ORDER BY 年薪 DESC ;
#四、按函数的结果排序
#案例1:按姓的字数长度进行升序排序
SELECT last_name FROM employees ORDER BY LENGTH(last_name);
#五、按多个字段进行排序
#案例1:查询员工的姓名、工资、部门编号,先按工资升序,再按部门编号降序
SELECT CONCAT(last_name,first_name),salary,department_id FROM employees ORDER BY salary ASC,department_id DESC;
#六、按列数排序
SELECT * FROM employees ORDER BY 2;
4.常见函数
常见函数:
-
字符函数
-
数学函数
-
日期函数
-
流程控制函数
4.1字符函数
#1.CONCAT() 拼接字符
SELECT CONCAT('hello','lyc');
#2.LENGTH() 获取字节长度 utf-8中汉字占三个字节
#输出12
SELECT LENGTH('hello,张三');
#3.CHAR_LENGTH() 获取字符个数
#输出8
SELECT CHAR_LENGTH('hello,张三');
#4.SUBSTR() 截取子串
#SUBSTR(str,起始索引,截取的字符长度) mysql索引从1开始
SELECT SUBSTR('武汉加油,中国加油',1,2);
#5.INSTR() 获取字符第一次出现的索引
#INSTR('主串','获取的子串')
SELECT INSTR('武汉加油,武汉加油,中国加油,武汉加油,中国加油','中国加油');
#6.TRIM() 去前后指定的字符 默认是空格
#去掉前后空格
SELECT TRIM(' 武汉 加油 ') AS a;
#去掉前后指定的字符'.'
SELECT TRIM('.' FROM '...武汉...加油.....') AS a;
#7.LPAD() 左填充 RPAD() 右填充
SELECT LPAD('Tom','10','x');
SELECT RPAD('Tom','10','x');
4.2数学函数
#1.ABS() 绝对值
SELECT ABS(-9.4);
#2.CEIL() 向上取整 返回大于等于该参数的最小整数
SELECT CEIL(1.09);
#3.FLOR() 向下取整 返回小于等于该参数的最大整数
SELECT FLOOR(-1.09);
#4.ROUND() 四舍五入
SELECT ROUND(1.87);
SELECT ROUND(1.873466,2);
#5.TRUNCATE() 截断,保留小数点位数
SELECT TRUNCATE(1.874366,2);
#6.MOD() 取余
SELECT MOD(-10,3);
4.3日期函数
#1.NOW() 获取当前时间(包含年月日时分秒)
SELECT NOW();
#2.CURDATE() 获取当前日期
SELECT CURDATE();
#3.CURTIME() 获取当前时间(包含时分秒)
SELECT CURTIME();
#4.DATEDIFF() 获取两个日期相差的天数
SELECT DATEDIFF('2020-4-23','1998-7-16');
#5.DATE_FORMAT() 对指定日期进行格式化
SELECT DATE_FORMAT('2020-4-23','%Y年%m月%d日 %H小时%i分钟%s秒');
#6.STR_TO_DATE() 按指定格式解析字符串为日期类型
SELECT STR_TO_DATE('4/23 2020','%m/%d %Y');
4.4流程控制语句
#1.IF()
#需求:如果有奖金,则显示最终奖金,如果没有,则显示0
SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct) AS 奖金,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 salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END AS 'newSalary'
FROM employees;
/*情况2:类似于多重if语句,实现区间判断
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
...
ELSE 结果n
END
*/
#案例:如果工资>20000,显示级别A;如果工资>15000,显示级别B;如果工资>10000,显示级别B;否则,显示级别D
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS '等级'FROM employees;
4.5分组函数(聚合函数)
说明:往往用于将一组数据进行统计计算,最终得到一个值
#1.SUM(字段名): 求和
#案例1:查询员工信息表中,所有员工的工资和,工资平均值,最低工资,最高工资,有工资的个数
SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM employees;
#案例2:查询emp表中的记录数
SELECT COUNT(employee_id) FROM employees;
#案例3:查询emp表中月薪大于2500的人数
SELECT COUNT(salary) FROM employees WHERE salary>2500;
#COUNT的补充介绍
#1.查询结果集的行数,推荐使用COUNT(*)
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;
#2.搭配distinct实现去重的统计
#需求:查询有员工的部门个数
#去掉重复的
SELECT COUNT(DISTINCT department_id) FROM employees;
#2.AVG(字段名): 求平均数
#3.max(字段名): 求最大值
#4.min(字段名): 求最小值
#5.count(字段名): 计算非空字段值的个数
5.分组查询
语法:select 查询列表 from 表名 where 筛选条件 group by 分组列表 having 分组后筛选 order by 排序列表;
执行顺序:
-
from子句
-
where子句
-
group by子句
-
having子句
-
select子句
-
order by子句
特点:
-
查询列表往往是 分组函数和被分组的字段 ☆
-
分组查询中的筛选分为两类
-
筛选的基表
使用的关键词
位置
分组前筛选
原始表
WHERE
GROUP BY的前面
分组后筛选
分组后的结果集
HAVING
GROUP BY的后面
-
where--group by-- having
分组函数做条件只能放在having的后面
#1.求每个部门的总工资,平均工资
SELECT SUM(salary),AVG(salary),department_id FROM employees GROUP BY department_id;
#2.查询每个工种的员工平均工资
SELECT AVG(salary),job_id FROM employees GROUP BY job_id;
#3.查询每个领导的手下人数
SELECT COUNT(*),manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id;
#实现分组前的查询
#4.查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary) AS 最高工资,department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;
#5.查询每个领导手下有奖金的员工的工资
SELECT AVG(salary) AS 平均工资,manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;
#实现分组后的查询
#6.查询哪个部门的员工个数大于5
SELECT COUNT(*) AS '员工个数',department_id FROM employees GROUP BY department_id HAVING COUNT(*)>5;
#7.每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary) FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000;
#8.领导编号>102的每个领导手下的最低工资大于5000的最低工资
SELECT MIN(salary) 最低工资,manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;
#实现排序
#9.每个工种没有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT job_id,MAX(salary) FROM employees WHERE commission_pct IS NULL GROUP BY job_id HAVING MAX(salary)>6000 ORDER BY MAX(salary);
#按多个字段分组
#10.查询每个工种每个部门的最低工资,并按最低工资降序
#提示:工种和部门一样,才是一组
SELECT MIN(salary) 最低工资,job_id,department_id FROM employees GROUP BY job_id,department_id ORDER BY MIN(salary) DESC;
6.连接查询
含义:又称多表查询,当查询的字段来自多个表时,就会用到连接查询
笛卡尔乘积现象:表1 有m行,表2 有n行 ,结果=m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
分类:
-
按年代分类:
-
sql92标准 仅仅支持内连接
-
sql99标准[推荐] 支持内连接+外连接(左外连接和右外连接)+交叉连接
-
-
按功能分类
-
内连接
-
等值连接
语法:select 查询列表 from 表名1 别名1,表名2 别名2... where 等值连接的连接条件
特点:
-
为了解决多表中的字段名重复问题,往往为表起别名,提高语义性;
-
表的顺序无要求
-
-
非等值连接
-
自连接
-
-
外连接
-
左外连接
-
右外连接
-
全外连接
-
-
交叉连接
-