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

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

继《全程软件测试(六十八):数据库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和任何数值参与运算结果都是NULL 10、【补充】ifnull函数 功能:判断某字段或表达式是否为null,如果为null,返回指定的值,否则返回原本的值 SELECT IFNULL(字段名, 指定值) FROM 表名; 11、【补充】isnull函数 功能:判断某字段或表达式是否为null,如果是null,则返回1,否则返回0 SELECT 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 ; 注意:=只能判断普通类型的数值,而<=>不仅可以判断普通类型的数值还可以判断NULL 7、查询员工编号!=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,否则返回表达式2 2、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; 注意:返回子串第一次出现的索引,如果找不到返回0 11、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. 筛选分为两类:分组前筛选和分组后筛选

针对的表

语句位置

连接的关键字

分组前筛选

分组前的原始表

group by前

where

分组后筛选

分组后的结果集

group by后

having

  1. 分组可以按单个字段也可以按多个字段
  2. 分组可以搭配着排序使用

三、演示

1、查询每个工种的员工平均工资 SELECT AVG(salary), job_id FROM employees GROUP BY job_id ; 2、查询每个位置的员工部门个数 SELECT COUNT(*), location_id FROM departments GROUP BY location_id ; 3、查询有奖金的每个领导手下员工的平均工资 SELECT AVG(salary), manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id ; 4、查询哪个部门的员工个数>5 SELECT COUNT(*), department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 5 ; 5、查询每个工种有奖金的员工的最高工资>6000的最高工资和公众编号,按最高工资升序 SELECT MAX(salary) m, job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING m > 6000 ORDER BY m ; 6、查询每个工种每个部门的最低工资并按最低工资降序 SELECT MIN(salary), job_id, department_id FROM employees GROUP BY job_id, department_id ORDER BY MIN(salary) DESC ;

3.7、连接查询

一、含义

连接查询又称多表查询,当查询的字段来自于多个表时,就会用到连接查询

二、注意

  • 笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行

发生原因:没有有效的连接条件

如何避免:添加有效的连接条件

三、分类

1、按年代分类

  • SQL92标准:支持内连接
  • SQL99标准:支持内连接、部分外连接(左外、右外)、交叉连接

2、按功能分类

  • 内连接

等值连接

非等值连接

自连接

  • 外连接

左外连接

右外连接

全外连接

  • 交叉连接

四、SQL92标准演示

1、SQL92标准:等值连接

#查询员工名和对应的部门名 SELECT last_name, department_name FROM employees, departments WHERE employees.`department_id` = departments.`department_id` ;

2、SQL92标准:非等值连接

#查询员工的工资和工资级别 SELECT salary, grade_level FROM employees e, job_grades g WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal` ;

3、SQL92标准:自连接

#查询员工名和它对应上级的名称 SELECT e.employee_id, e.last_name, m.employee_id, m.last_name FROM employees e, employees m WHERE e.`manager_id` = m.`employee_id` ;

3.8、SQL99标准

一、语法

SELECT 查询列表 FROM 表1 别名1 【连接类型】 JOIN 表2 别名2 ON 连接条件 【where 分组前筛选条件】 【group BY 分组列表】 【having 分组后筛选条件】 【order BY 排序列表】 ;

二、连接类型

  • 1、内连接:inner
  • 2、外连接

左外连接:left 【outer】(左边的是主表)

右外连接:right 【outer】(右边的是主表)

全外连接:full 【outer】(两边都是主表,但是MySQL不支持全外连接、Oracle支持)

  • 3、交叉连接:cross(交叉连接其实是用SQL99语法实现笛卡尔乘积)

三、演示

1、内连接:等值连接

#查询员工名和对应的部门名 SELECT last_name, department_name FROM departments d INNER JOIN employees e ON e.`department_id` = d.`department_id` ;

2、内连接:非等值连接

#查询员工的工资和工资级别 SELECT salary, grade_level FROM employees e INNER JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal` ;

3、内连接:自连接

#查询员工名和它对应上级的名称 SELECT e.last_name, m.last_name FROM employees e INNER JOIN employees m ON e.`manager_id` = m.`employee_id` ;

4、外连接:左外连接

#查询哪个部门没有员工 SELECT d.`department_name` FROM departments d LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL ;

5、外连接:右外连接

#查询哪个部门没有员工 SELECT d.`department_name` FROM employees e RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL ;

6、交叉连接

#使用交叉连接进行笛卡尔乘积查询 SELECT b.*, bo.* FROM beauty b CROSS JOIN boys bo ;

3.9、子查询

一、含义

嵌套在其它语句内部的select语句称为子查询或内查询,外面的语句可以是insert、delete、update、select等,一般select作为外面语句较多,外面如果为select语句,则此语句称为外查询或主查询

二、分类

  • 1、按出现的位置划分

select后面:标量子查询

from后面:表子查询

where或having后面

1、标量子查询

2、列子查询

3、行子查询

exists后面

1、标量子查询

2、列子查询

3、行子查询

4、表子查询

  • 2、按结果集行列数划分

标量子查询(单行子查询):结果集为一行一列

列子查询(多行子查询):结果集为多行一列

行子查询:结果集为多行多列

表子查询:结果集为多行多列

三、特点

  1. 子查询放在小括号内
  2. 子查询一般放在条件的右侧
  3. 子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
  4. 标量子查询,一般搭配着单行操作符使用:>、>=、<、<=、!=、<>、=、<=>
  5. 列子查询,一般搭配着多行操作符使用:in、not in、any、some、all、exits

四、演示

1、select后面

#查询每个部门的员工个数 SELECT d.*, ( SELECT COUNT(*) FROM employees e WHERE e.department_id = d.`department_id` ) 个数 FROM departments d ;

2、from后面

#查询每个部门平均工资的工资等级 SELECT ag_dep.*, g.`grade_level` FROM ( SELECT AVG(salary) ag, department_id FROM employees GROUP BY department_id ) ag_dep INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal ;

3、where或having后面

标量子查询:查询最低工资的员工姓名和工资 SELECT last_name, salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees ) ; 列子查询: #查询所有是领导的员工姓名 SELECT last_name FROM employees WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees ) ; #返回其它工种中比如job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id以及salary SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) AND job_id <> 'IT_PROG' ; #返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ALL ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) AND job_id <> 'IT_PROG' ; 行子查询:查询员工编号最小并且工资最高的员工信息 SELECT * FROM employees WHERE (employee_id, salary) = ( SELECT MIN(employee_id), MAX(salary) FROM employees ) ;

4、exists后面

#查询有员工的部门名 SELECT department_name FROM departments d WHERE EXISTS ( SELECT * FROM employees e WHERE e.`department_id` = d.`department_id` ) ;

3.10、分页查询

一、语法

SELECT 查询列表 FROM 表1 别名1 【连接类型】 JOIN 表2 别名2 ON 连接条件 【WHERE 分组前的筛选】 【GROUP BY 分组字段】 【HAVING 分组后的筛选 】 【ORDER BY 排序字段 ASC|DESC】 LIMIT 【offset, 】size ;

二、特点

  1. limit语句放在查询语句的最后
  2. offset代表起始索引,起始索引从0开始,size代表条目个数
  3. 分页语句:select 查询列表 from 表 limit (page-1)*size,size;

三、演示

#查询前五条员工信息 SELECT * FROM employees LIMIT 0,5;

3.11、联合查询

一、语法

查询语句1 union 【all】 查询语句2 union 【all】 ...

二、特点

  1. 要查询的结果来自于多个表且多个表没有直接的连接关系,但查询的信息一致时,可以使用联合查询
  2. 要求多条查询语句的查询列数是一致的
  3. 要求多条查询语句的查询的每一列的类型和顺序最好一致
  4. union关键字默认去重,如果使用union all可以包含重复项

三、演示

#查询中国用户中男性的信息以及外国用户中年男性的用户信息 SELECT id,cname FROM t_ca WHERE csex='男' UNION ALL SELECT t_id,tname FROM t_ua WHERE tGender='male';

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页