mysql数据类型精讲全程软件测试六十九

导读继《全程软件测试（六十八）：数据库MySQL从零开始入门—读书笔记》文章，此篇接续第三章——DQL语言第三章DQL语言3.1、基础查询一、语法SELECT查....

继《全程软件测试（六十八）：数据库MySQL从零开始入门—读书笔记》文章，此篇接续第三章——DQL语言

第三章 DQL语言

3.1、基础查询

一、语法

SELECT 查询列表 FROM 表名;

二、特点

1. 查询列表可以是字段、常量、函数、表达式
2. 查询结果是一个虚拟表

三、示例

1、查询单个字段SELECT 字段名 FROM 表名;2、查询多个字段SELECT 字段名,字段名 FROM 表名;3、查询所有字段SELECT * FROM 表名;4、查询常量SELECT 常量值;注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要5、查询函数SELECT 函数名(实参列表);6、查询表达式SELECT 100/25;7、起别名 SELECT 字段名 AS "别名" FROM 表名;注意：别名可以使用单引号、双引号引起来，当只有一个单词时，可以省略引号，当有多个单词且有空格或特殊符号时，不能省略，AS可以省略；8、去重复SELECT DISTINCT 字段名 FROM 表名;9、做加法SELECT 数值 数值; 直接运算SELECT 字符 数值; 首先先将字符转换为整数，如果转换成功，则继续运算，如果转换失败，则默认为0，然后继续运算SELECT NULL 数值; NULL和任何数值参与运算结果都是NULL10、【补充】ifnull函数功能：判断某字段或表达式是否为null，如果为null，返回指定的值，否则返回原本的值SELECT IFNULL(字段名, 指定值) FROM 表名;11、【补充】isnull函数功能：判断某字段或表达式是否为null，如果是null，则返回1，否则返回0SELECT ISNULL(字段名) FROM 表名;

3.2、条件查询

一、语法

SELECT 查询列表 FROM 表名 WHERE 筛选条件;

二、分类

1. 条件运算符：>、>=、<、<=、=、<=>、!=、<>
2. 逻辑运算符：and、or、not
3. 模糊运算符：

• like：%任意多个字符、_任意单个字符，如果有特殊字符，需要使用escape转义
• between and
• not between and
• in
• is null
• is not null

三、演示

1、查询工资>12000的员工信息SELECT * FROM employees WHERE salary > 12000 ;2、查询工资>=14000的员工信息SELECT * FROM employees WHERE salary >= 14000 ;3、查询工资<12000的员工信息SELECT * FROM employees WHERE salary < 12000 ;4、查询工资<=14000的员工信息SELECT * FROM employees WHERE salary <= 14000 ;5、查询员工编号=100的员工信息SELECT * FROM employees WHERE employee_id = 100 ;6、查询员工编号<=>100的员工信息SELECT * FROM employees WHERE employee_id <=> 100 ;注意：=只能判断普通类型的数值，而<=>不仅可以判断普通类型的数值还可以判断NULL7、查询员工编号!=100的员工信息SELECT * FROM employees WHERE employee_id != 100 ;8、查询员工编号<>100的员工信息SELECT * FROM employees WHERE employee_id <> 100 ;注意：!=和<>都是判断不等于的意思，但是MySQL推荐使用<> 9、查询工资>12000&&工资<18000的员工信息SELECT * FROM employees WHERE salary > 12000 AND salary < 18000 ;10、查询工资<=12000||工资>=18000的员工信息SELECT * FROM employees WHERE salary <= 12000 OR salary >= 18000 ;11、查询工资<=12000||工资>=18000的员工信息SELECT * FROM employees WHERE NOT (salary > 12000 AND salary < 18000) ;12、查询员工名中第一个字符为B、第四个字符为d的员工信息SELECT *FROM employees WHERE last_name LIKE B__d% ;注意：last_name指的是员工名13、查询员工编号在100到120之间的员工信息SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120 ;14、查询员工编号不在100到120之间的员工信息SELECT * FROM employees WHERE employee_id NOT BETWEEN 100 AND 120 ;15、查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号SELECT last_name, job_id FROM employees WHERE job_id IN (IT_PROT, AD_VP, AD_PRES) ;注意：in列表的值类型必须一致或兼容，in列表中不支持通配符%和_16、查询没有奖金的员工名和奖金率SELECT last_name, commission_pct FROM employees WHERE commission_pct is null ;17、查询有奖金的员工名和奖金率SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NOT NULL ;注意：=、!=不能用来判断NULL、而<=>、is null 、 is not null可以用来判断NULL，但注意<=>也可以判断普通类型的数值；

3.3、排序查询

一、语法

SELECT 查询列表 FROM 表 【WHERE 筛选条件】order by 排序列表 【asc | desc】 ;

二、注意

• 排序列表可以是单个字段、多个字段、别名、函数、表达式
• asc代表升序，desc代表降序，如果不写，默认是asc
• order by的位置一般放在查询语句的最后（除limit语句之外）

三、示例

1、按单个字段排序：查询员工信息，要求按工资降序SELECT * FROM employees ORDER BY salary DESC ;2、按多个字段查询：查询员工信息，要求先按工资降序，再按员工编号升序SELECT * FROM employees ORDER BY salary DESC, employee_id ASC ;3、按别名排序查询：查询员工信息，要求按员工年薪升序SELECT *, salary * 12 * (1 IFNULL(commission_pct, 0)) 年薪 FROM employees ORDER BY 年薪 ASC ;4、按函数排序查询：查询员工信息，要求按员工名字的长度降序SELECT LENGTH(last_name), last_name FROM employees ORDER BY LENGTH(last_name) DESC ;5、按表达式排序：查询员工信息，要求按员工年薪升序SELECT *, salary * 12 * (1 IFNULL(commission_pct, 0)) FROM employees ORDER BY salary * 12 * (1 IFNULL(commission_pct, 0)) ASC ;

3.4、单行函数

一、语法

SELECT 函数名(实参列表) 【FROM 表】;

二、分类

1、字符函数

• concat：连接字符
• substr：截取子串
• replace：替换字符
• upper：变大写
• lower：变小写
• lpad：左填充
• rpad：右填充
• length：获取字节长度
• trim：去除前后空格
• instr：获取子串第一次出现的索引

注意：MySQL中的索引是从1开始的

2、数学函数

• round：四舍五入
• ceil：向上取整
• floor：向下取整
• mod：取模运算（a-a/b*b）
• truncate：保留小数的位数，不进行四舍五入
• rand：获取随机数，返回0-1之间的小数

3、日期函数

• now：返回当前日期 时间
• curdate：返回当前日期
• curtime：返回当前时间
• year：返回年
• month：返回月
• day：返回日
• hour：小时
• minute：分钟
• second：秒
• monthname：以英文形式返回月
• datediff：返回两个日期相差的天数
• date_format：将日期转换成字符
• str_to_date：将字符转换成日期

格式符：%Y：四位的年份%y：二位的年份%m：二位的月份（01，02，...，12）%c：一位的月份（1，2，...，12）%d：日（01，02，...，31）%H：小时（24小时制）%h：小时（12小时制）%i：分钟（00，01，02，...，59）%s：秒（00，01，02，...，59）

4、控制函数

• if：判断函数
• case：分支函数

1、IF(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式22、case的格式一： CASE 变量或字段或表达式 WHEN 常量1 THEN 值1 WHEN 常量2 THEN 值2 ... ELSE 值n END ;3、case的格式二： CASE WHEN 条件1 THEN 值1 WHEN 条件2 THEN 值2 ... ELSE 值n END

5、其它函数

• version：当前数据库的版本
• database：当前打开的数据库
• user：当前登录的用户
• password(‘字符’)：返回该字符的密码形式
• md5(‘字符’)：返回该字符的md5加密形式

三、演示

1、concat——连接字符SELECT CONCAT(Hello, ,World) AS out_put;2、substr——截取子串#截取从指定索引处后面所有字符SELECT SUBSTR(李莫愁爱上了陆展元,7) AS out_put;#截取从指定索引处指定字符长度的字符SELECT SUBSTR(李莫愁爱上了陆展元,1,3) AS out_put;3、replace——替换字符SELECT REPLACE(张无忌爱上了周芷若,周芷若,赵敏) AS out_put;4、upper——变大写SELECT UPPER(john) AS out_put;5、lower——变小写SELECT LOWER(john) AS out_put;6、lpad——左填充SELECT LPAD(殷素素,10,*) AS out_put;7、rpad——右填充SELECT RPAD(殷素素,10,*) AS out_put;8、length——获取字节长度SELECT LENGTH(john) AS out_put;9、trim——去除前后空格#删除指定字符的左右空格SELECT LENGTH(TRIM( 张翠山 )) AS out_put;#删除指定字符的指定字符SELECT TRIM(aaa FROM aaaaaaaaa张翠山aaaaaaaaa) AS out_put;10、instr——获取子串第一次出现的索引SELECT INSTR(杨不悔爱上了殷六侠,殷六侠) AS out_put;注意：返回子串第一次出现的索引，如果找不到返回011、round——四舍五入#默认四舍五入SELECT ROUND(-1.55) AS out_put;#指定小数位数SELECT ROUND(1.567,2) AS out_put;注意：四舍五入和符号无关12、ceil——向上取整SELECT CEIL(-1.02) AS out_put;注意：向上取整，返回>=该参数的最小整数13、floor——向下取整SELECT FLOOR(-9.99) AS out_put;注意：向下取整，返回<=该参数的最大整数14、mod——取模运算（a-a/b*b）SELECT MOD(10,3) AS out_put;15、truncate——保留小数的位数，不进行四舍五入SELECT TRUNCATE(1.69999,1) AS out_put;16、rand——获取随机数，返回0-1之间的小数SELECT RAND() AS out_put;17、now——返回当前日期 时间SELECT NOW() AS out_put;18、curdate——返回当前日期SELECT CURDATE() AS out_put;19、curtime——返回当前时间SELECT CURTIME() AS out_put;20、year——返回年SELECT YEAR(NOW()) 年;21、month——返回月SELECT MONTH(NOW()) 月;22、day——返回日SELECT DAY(NOW()) 日;23、hour——返回时SELECT HOUR(NOW()) 时;24、minute——返回分SELECT MINUTE(NOW()) 分;25、second——返回秒SELECT SECOND(NOW()) 秒;26、monthname——以英文形式返回月SELECT MONTHNAME(NOW()) 月名;27、datediff——返回两个日期相差的天数SELECT DATEDIFF(1995-2-7,1995-2-6) AS out_put;28、date_format——将日期转换成字符SELECT DATE_FORMAT(NOW(),%Y年%m月%d日) AS out_put;29、str_to_date——将字符转换成日期SELECT STR_TO_DATE(1998-3-2,%Y-%c-%d) AS out_put;30、if——判断函数SELECT IF(10 < 5, 大, 小) AS out_put;31、case的格式——分支函数（一）/*案例：查询员工的工资，要求部门号=30，显示的工资为1.1倍部门号=40，显示的工资为1.2倍部门号=50，显示的工资为1.3倍其它部门，显示的工资为原工资*/SELECT salary 原始工资, department_id, 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 ;32、case的格式——分支函数（二）/*案例：查询员工的工资情况 如果工资>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 工资级别 FROM employees ;33、version——当前数据库的版本SELECT VERSION();34、database——当前打开的数据库SELECT DATABASE();35、user——当前登录的用户SELECT USER();36、password(‘字符’)——返回该字符的密码形式SELECT PASSWORD(123456);37、md5(‘字符’)——返回该字符的md5加密形式SELECT MD5(123456);

3.5、分组函数

一、语法

SELECT 函数名(实参列表) 【FROM 表】;

二、分类

• sum：求和
• avg：平均值
• max：最大值
• min：最小值
• count：计算个数

1、sum、avg一般用于处理数值型，max、min、count可以处理任何类型2、以上分组函数都忽略null值3、可以和distinct搭配实现去重的运算：select sum(distinct 字段) from 表;4、一般使用count(*)用作统计行数5、和分组函数一同查询的字段要求是group by后的字段

三、演示

1、sum——求和SELECT SUM(salary) FROM employees;2、avg——平均值SELECT AVG(salary) FROM employees;3、max——最大值SELECT MAX(salary) FROM employees;4、min——最小值SELECT MIN(salary) FROM employees;5、count——计算个数SELECT COUNT(salary) FROM employees;

3.6、分组查询

一、语法

SELECT 查询列表 FROM 表【where 筛选条件】 GROUP BY 分组的字段 【having 分组后的筛选】【order BY 排序的字段】 ;

二、特点

1. 和分组函数一同查询的字段必须是group by后出现的字段
2. 筛选分为两类：分组前筛选和分组后筛选