DQL(Data Query Language):数据查询语句,用于查询数据库数据。
首先创建一个myemployees的数据库作为演示例子,下面是表的结构介绍:
一、基础查询
语法:select 查询列表 from 表名;
特点:
- 查询列表可以是:表中的字段、常量值、表达式、函数
- 查询的结果是一个虚拟的表格
(一)查询表中的单个字段
SELECT last_name FROM employees;
如果想查询的字段和MySQL关键字重名怎么办?用``将字段框起来
SELECT `name` FROM student;
(二)查询表中的多个字段
SELECT last_name,salary,email FROM employees;
(三)查询表中的所有字段
SELECT * FROM employees;
(四)查询常量值
字符型(包括字符串,MySQL中其实没有字符串的概念只有字符)和日期型的常量值都是用单引号
SELECT 100;
SELECT 'John';
(五)查询表达式
SELECT 100%98;
(六)查询函数
SELECT VERSION();
(七)起别名
①便于理解
②如果要查询的字段有重名的情况,使用别名可以区分开来
方式一:使用AS
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
方式二:使用空格
SELECT last_name 姓,first_name 名 FROM employees;
假如我想给查询的结果起个别名,但是这个别名带空格或者特殊符号怎么办?
用双引号!
SELECT salary AS "out put" FROM employees;
(八)去重
同个结果只显示一次
SELECT DISTINCT department_id FROM employees;
(九)+号的作用
Java中的+号:
①运算符,两个操作数都为数值型
②连接符,只要有一个操作数为字符串
MySQL中的+号:
①仅仅只有一个功能:运算符
SELECT 100+90;#两个操作数都数值型,则做加法运算
SELECT '123'+90;#其中一方为字符型,试图将字符型数值转换成数值型
#如果转换成功,则继续做加法运算
SELECT 'John'+90;#90 如果转换失败,则将字符型数值转换成0
SELECT null+10;#只要其中一方为null,则结果肯定为null
(十)CONCAT函数
拼接字符串
案例:查询员工名和姓连接成一个字段,并显示为姓名
#规范:关键字占一行
SELECT
CONCAT(last_name,first_name)
AS
姓名
FROM
employees;
(十一)IFNULL函数
它有两个参数,第一个参数是要做判断的那个字段,第二个参数是如果该字段为null那么要为它显示的值
案例:显示出表employees的全部列,各个列之间用逗号拼接,列头显示为out_put
#我们事先知道commission_pct有可能为null值,而任何值与null值拼接的结果都是null,因此我们要用IFNULL
SELECT
CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct,0))AS out_put
FROM
employees;
(十二)ISNULL函数
判断某字段或表达式是否为null,如果为null,返回1,如果不为null,返回0
SELECT
ISNULL(commission_pct),commission_pct
FROM
employees;
二、条件查询
语法:
SELECT
查询列表
FROM
表名
WHERE
筛选条件
分类:
- 按条件表达式筛选
简单条件运算符:
运算符 | > | < | = | <> | >= | <= |
---|---|---|---|---|---|---|
含义 | 大于 | 小于 | 等于 | 不等于(也可以用!=,不建议) | 大于等于 | 小于等于 |
- 按逻辑表达式筛选
逻辑运算符:- && || !(不建议)
- and or not(建议)
- 模糊查询
- like
- between and
- in
- is null
(一)按条件表达式筛选
案例1:查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE
salary>12000;
案例2:查询部门编号不等于90号的员工名和部门编号
SELECT
last_name,dapartment_id
FROM
employees
WHERE
department_id<>90;
(二)按逻辑表达式筛选
案例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
department_id<90 OR department_id>110 OR salary>15000;
#==============或者================
SELECT
*
FROM
employees
WHERE
NOT(department_id>=90 AND department_id<=110) OR salary>15000;
(三)模糊查询
1. LIKE
like特点:
- 一般和通配符搭配使用
通配符:
%表示任意多个字符,包含0个字符
_表示任意单个字符 - 可以判断字符型,也可以判断数值型
案例1:查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
案例2:查询员工名中第三个字符为e,第五个字符为a的员工名和工资
SELECT
last_name,salary
FROM
employees
WHERE
last_name LIKE '__e_a%';
案例3:查询员工名中第二个字符为_的员工名
如果名字和通配符一样,我们就需要用到转义符\
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_\_%';
或者我们可以用关键字ESCAPE自定义一个转义字符
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';
案例4:查询部门编号以1开头的员工信息
SELECT
*
FROM
employees
WHERE
department_id LIKE '1__';
2. BETWEEN AND
between and
①使用between and可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序
案例1:查询员工编号在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE
employee_id >= 100 AND employee_id<=120;
#============等价于================
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 120;
3. IN
in:判断某字段的值是否属于in列表中的某一项
特点:
①使用in提高语句简洁度
②in列表的值类型必须一致或兼容
③in列表不支持通配符
案例1:查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
last_name,job_id
FROM
employees
WHERE
job_id='IT_PROG' OR job_id='AD_VP' OR job_id='AD_PRES';
#==================等价于==================
SELECT
last_name,job_id
FROM
employees
WHERE
job_id IN('IT_PROG','AD_VP','AD_PRES');
4. IS NULL 和 IS NOT NULL
=或<>不能用于判断null值
is null 或 is not null 可以判断null值
案例1:查询没有奖金的员工名和奖金率
SELECT
last_name,commission_pct
FROM
employees
WHERE
commission_pct IS NULL;
不能写commission_pct=null
案例2:查询有奖金的员工名和奖金率
SELECT
last_name,commission_pct
FROM
employees
WHERE
commission_pct IS NOT NULL;
5. 安全等于<=>
is null:仅可以判断NULL值,可读性较高,建议使用
<=>:既可以判断NULL值,又可以判断普通的数值,可读性较低
案例1:查询没有奖金的员工名和奖金率
SELECT
last_name,commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
案例2:查询工资为12000的员工信息
SELECT
last_name,salary
FROM
employees
WHERE
salary <=> 12000;
练习题:查询员工号为176的员工的姓名、部门号和年薪
SELECT
last_name,dapartment_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees
WHERE
employee_id=176;
面试题:
试问select * from employees;
和select * from employees where commission_pct like'%%' and last_name like '%%';
结果是否一样?并说明原因
不一样!因为commission_pct有的为null值
如果判断的字段没有null值的那就一样
或者改成or:select * from employees where commission_pct like'%%' or last_name like '%%';
就一样
三、排序查询
语法:
SELECT 查询列表
FROM 表名
【WHERE 筛选条件】#可有可无
ORDER BY 排序列表【ASC|DESC】#可有可无 缺省默认为ASC升序
特点:
①ASC代表的是升序,DESC代表的是降序,如果不写,默认是升序
②ORDER BY子句中可以支持单个字段、多个字段、表达式、函数、别名
③ORDER BY子句一般是放在查询语句的最后面,LIMIT子句除外
(一)按单个字段排序
案例1:查询员工信息,要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
(二)按多个字段排序
案例1:查询员工信息,要求先按工资升序,再按员工编号降序
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;
(三)有筛选条件
案例1:查询部门编号>=90的员工信息,按入职时间的先后进行排序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY hiredate ASC;
案例2:选择工资不在8000到17000的员工的姓名和工资,按工资降序
补充:NOT BETWEEN AND :不在…之间
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
#----------等价于---------
SELECT last_name,salary
FROM employees
WHERE NOT(salary BETWEEN 8000 AND 17000)
ORDER BY salary DESC;
(四)按表达式排序
案例1:按年薪的高低显示员工的信息和年薪
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
(五)按别名排序
案例1:按年薪的高低显示员工的信息和年薪
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC;
(六)按函数排序
案例1:按姓名的长度显示员工的姓名和工资
补充:LENGTH()函数可以计算字节长度,一个字母占一个字节,UTF8下一个汉字占三个字节
SELECT LENGTH(last_name) 姓名长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
四、常见函数
函数类似于Java中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1、隐藏了实现细节 2、提高代码的重用性
调用方法:SELECT 函数名(实参列表)【FROM 表】;
(一)单行函数
1.字符函数
(1)LENGTH() 获取参数值的字节个数
SELECT LENGTH('mike');
SELECT LENGTH('今天天气真好');
(2)CONCAT() 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
(3)UPPER()、LOWER() 大小写控制函数
UPPER()将所有字母变为大写
LOWER()将所有字母变为小写
SELECT UPPER('john');
SELECT LOWER('JOHN');
案例1:将姓变大写,名变小写,然后拼接
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) 姓名 FROM employees;
(4)LEFT()、RIGHT() 从指定方向截取字符串
从左边截取
SELECT LEFT('hello,world',5);
从右边截取
SELECT RIGHT('hello,world',5);
(5)SUBSTR() (或者SUBSTRING())截取字符串
注意:MySQL的索引都是从1开始
案例1:截取从指定索引处后面的所有字符
SELECT SUBSTR('今天天气真好',3) out_put;
案例2:截取从指定所引处到指定字符长度的字符
SELECT SUBSTR('今天天气真好',1,2) out_put;
案例3:姓中首字符大写,其他字符小写然后用_拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put
FROM employees;
案例4:将员工的姓名按首字母排序,并写出姓名的长度
SELECT last_name,SUBSTR(last_name,1,1) 首字母,LENGTH(last_name) 长度
FROM employees
ORDER BY 首字母;
(6)INSTR() 返回子串第一次出现的索引,如果找不到返回0
案例1:
SELECT INSTR('今天天气很好天气很好','天气') AS out_put;
案例2:查询所有学生的邮箱的用户名
SELECT SUBSTR(email,1,INSTR(email,'@')-1) 用户名
FROM stuinfo;
(7)TRIM() 去掉两端的空格或指定字符
案例1:去掉两端的空格
SELECT TRIM(' 今天天气真好 ') AS out_put;
案例2:去掉两端的指定字符
SELECT TRIM('a' FROM 'aaaaa今天天气aaaaa真好aaaaaaaa') AS out_put;
(8)LPAD() 用指定的字符实现左填充至指定字符长度
案例1:
SELECT LPAD('今天天气真好',10,'*') AS out_put;
案例2:如果指定长度小于已有字符,会截取字符
SELECT LPAD('今天天气真好',2,'*') AS out_put;
(9)RPAD() 用指定的字符实现右填充至指定字符长度
案例1:
SELECT RPAD('今天天气真好',10,'*') AS out_put;
(10)REPLACE() 替换
SELECT REPLACE('今天天气真好','今天','昨天') AS out_put;
(11)REVERSE() 反转字符串
SELECT REVERSE('hello,world');
2.数学函数
(1)ROUND() 四舍五入
SELECT ROUND (-1.55);
SELECT ROUND(1.567,2);#保留两位小数
(2)CEIL() (或者CEILING())向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02);
(3)FLOOR() 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99);
(4)TRUNCATE() 截断
SELECT TRUNCATE(1.69999,2);# 2表示截断至小数点后2位
(5)MOD() 取余
MOD(a,b)=a-a/b*b
SELECT MOD(10,-3);
(6)ABS() 求绝对值
SELECT ABS(-9);#9
(7)RAND() 产生随机数
返回一个0-1之间的随机数
SELECT RAND();
3.日期函数
(1)NOW() 返回系统当前日期+时间
SELECT NOW();
(2)CURDATE() 返回系统当前系统日期,不包含时间
SELECT CURDATE();
(3)CURTIME() 返回系统当前时间,不包含日期
SELECT CURTIME();
(4)年月日小时分钟秒
SELECT YEAR(NOW()),MONTH(NOW()),MONTHNAME(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
(5)STR_TO_DATE() 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1997-7-1','%Y-%c-%d') AS out_put;
案例:查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate='1992-4-3';
SELECT * FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');
(6)DATE_FORMAT() 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
案例:查询有奖金的员工名和入职日期(xx月xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月%d日 %y年') AS 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;
(7)DATEDIFF() 返回两个日期之间的天数
格式:DATEDIFF(date1,date2)
date1-date2 前面日期减去后面日期
案例1:计算现在距离北京奥运会过去了多少天
SELECT DATEDIFF(NOW(),'2008-8-8');
案例2:查询入职日期在"1998-03-03"之后的员工名、入职时间
SELECT last_name,hiredate
FROM employees
WHERE DATEDIFF(hiredate,'1998-03-03')>0;
4.其他函数
(1)VERSION()
查看MySQL的版本号
(2)DATABASE()
查看当前使用的数据库
(3)USER()
显示当前登陆的用户名与它对应的host
(4)PASSWORD(‘字符’)
返回该字符的加密形式
(5)MD5(‘字符’)
返回该字符的MD5加密形式
5.流程控制函数
(1)IF()
if-else选择结构
语法:
IF(表达式1,表达式2,表达式3)
如果表达式1成立,则IF函数返回表达式2的值,否则返回表达式3的值。
SELECT IF(10>5,'大于','小于');
案例:查询所有员工名,如果有奖金备注“有奖金”,没奖金备注“没奖金”
SELECT last_name,IF(commission_pct IS NOT NULL,'有奖金','没奖金') AS 备注
FROM employees;
(2)CASE
这里的CASE结构作为表达式出现,后面还会讲CASE作为独立的语句出现
①CASE函数使用一:Java中switch-case的效果
Java中:
switch(变量或表达式){
case 常量1:语句1;break;
case 常量2:语句2;break;
...
default:语句n;break;
}
MySQL中:
CASE 要判断的变量、字段或表达式
WHEN 常量1 THEN 要显示的值1
WHEN 常量2 THEN 要显示的值2
...
ELSE 要显示的值n
END
案例:查询员工的工资,要求:
部门号=30,新工资为1.1倍
部门号=40,新工资为1.2倍
部门号=50,新工资为1.3倍
其他部门,新工资为原工资
SELECT last_name,department_id,salary AS 原工资,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
②CASE函数使用二:Java中多重if的效果
Java中:
if(条件1){
语句1;
}else if(条件2){
语句2;
}
...
else{
语句n;
}
MySQL中:
CASE
WHEN 条件1 THEN 要显示的值1
WHEN 条件2 THEN 要显示的值2
...
ELSE 要显示的值n
END
案例:查询员工的工资级别,要求:
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
SELECT last_name,salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
(二)分组函数
- 功能:做统计使用,又称为统计函数、聚合函数、组函数
- 分类:SUM()求和、AVG()求平均值、MAX()求最大值、MIN()求最小值、COUNT()计算个数
- 特点:
- SUM()、AVG()一般用于处理数值型,MAX()、MIN()、COUNT()可以处理任何类型
- 以上分组函数都忽略null值
- 可以和 DISTINCT 搭配实现去重的运算
- 一般使用COUNT(*)用作统计行数
- 和分组函数一同查询的字段有限制:要求是GROUP BY后的字段
SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(salary)
FROM employees;
和 DISTINCT 搭配实现去重的运算
SELECT COUNT(DISTINCT salary),COUNT(salary)
FROM employees;
COUNT() 函数
SELECT COUNT(salary),COUNT(*),COUNT(1) FROM employees;
效率:
- MYISAM存储引擎下,COUNT(*)的效率高
- INNODB存储引擎下,COUNT(*)和COUNT(1)的效率差不多,比COUNT(字段)要高一些
练习题1:查询员工表中的最大入职时间和最小入职时间的相差天数(DATEDIFF)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) diffrence
FROM employees;
练习题2:查询部门编号为90的员工个数
SELECT COUNT(*) 个数
FROM employees
WHERE department_id=90;