文化网站源码,深圳的网站建设公司,外汇反佣网站建设,百度官网首页网址我不会记录的特别详细 大体框架
基本的Select语句运算符排序与分页多表查询单行函数聚合函数子查询
第三章 基本的SELECT语句
SQL分类
这个分类有很多种#xff0c;大致了解下即可
DDL#xff08;Data Definition Languages、数据定义语言#xff09;#xff0c;定义了… 我不会记录的特别详细 大体框架
基本的Select语句运算符排序与分页多表查询单行函数聚合函数子查询
第三章 基本的SELECT语句
SQL分类
这个分类有很多种大致了解下即可
DDLData Definition Languages、数据定义语言定义了不同的数据库、表、视图、索引等数据库对象也可以用来创建、删除、修改数据库和数据表的结构。 常见的CREATE、DROP、ALTERDMLData Manipulation Language、数据操作语言用于添加、删除、更新和查询数据库记录并检查数据完整性。 主要包括INSERT、DELETE、UPDATE、SELECT等。SELECT用的最多.DCLData Control Language、数据控制语言用于定义数据库、表、字段、用户的访问权限和安全级别。 主要包括GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。
SQL的规则与规范
规则
这个必须遵守
每条命令以;或\g或\G结束关键字不能被缩写也不能分行更不能在中间插入空格如select写成sel ect错误字符串和日期时间类型的数据可以使用单引号(‘’)表示列的别名用双引号(“”),建议加上as
规范
windows下对大小写不敏感Linux下大小写敏感比如数据库名大写和小写不一样但是关键字、函数名、列名及其别名是忽略大小写的推荐的规范-SQL关键字、函数名、绑定变量都大写其余的都小写
注释
单行注释 #和–,多行注释 /**/
# 单行注释
-- 单行注释
/*多行注释
*/命名规则
这个了解即可
库名,表名不得超过30个字符变量名限制为29个字符同一个MySQL软件中库名必须唯一同一个库中表名必须唯一同一个表中字段名唯一若关键字与字段名冲突且坚持使用用着重号()把字段引起来 例子
create database a;//正确
create database a;//错误已经存在
use a;
create table order(id int);//错误order是关键字
create table order(id int);//正确数据导入指令
source sqlFile基本的Select语句
select … from TableName;
为什么不推荐使用* 因为获取所有的列会降低性能让效率变低
列的别名
就比如你要计算年工资你总不能存一个年工资和月工资把这样浪费空间还不是起一个别名 注意事项 别名使用双引号列名和别名之间加入AS若别名有空格必须使用双引号别名要见名知义 例子:
SELECT name,salary,salary * 12 AS annual salary FROM employee;DISTINCT
去重的对所在列以及后面的列去重 例如:
SELECT DISTINCT departmentId From employee;//对departmentId进行去重
SELECT DISTINCT departmentId,salary FROM employee;//对部门id和工资这个组合进行去重
/*
若有两条记录
departmentId salary
1 8000
1 7800
那么这两条记录会保留
若两条记录是
departmentId salary
1 8000
1 8000
这个是保留一个
*/NULL参与运算
只要有NULL结果必定为NULL 注意:NULL不是空他占存储空间NULL的长度为空
查询常数
这个我没有试过明天试试 就比方说我想要把公司A的所有部门都列出来格式如下:
公司A部门名称公司A部门1公司A部门2公司A部门3
可以这么写
SELECT 公司A,departmentName from deparments;显示表结构
DESC或者DESCRIBE
DESCRIBE departments;
desc departments;结果中有两列是Key和Extra只记录这两个
Key表示该列是否已编制索引。PRI表示该列是表主键的一部分(主键可以有多个列组成)UNI表示该列是UNIQUE索引的一部分(复合索引)MUL表示在列中某个给定值允许出现多次。Extra:该列的附加信息例如AUTO_INCREMENT等等
where
这个太简单了不记录了
第四章 运算符
算数运算符
加- 减-- 乘-* 除-/ 取余-%,比如 17 % 5 2 (17 -3*5)
比较运算符
这个很简单简要记录 真为1假为0其他为null
不等于:,!大于(等于):()小于(等于):()
等号运算符
若两边都是字符串比较的是ASCII码是否相等 若是整数就是比较数值大小若一个是字符串另一个是整数MySQL会把字符串转换为数字进行比较若有一个为null那么结果必定为null
安全等于运算符
他比等于多一个对null的判断,只有他是针对null的就是当两边都是null的时候返回1;一边为null一边不为null返回0
SELECT NULL NULL, 11;逻辑运算符
与或非,异或没了这里简单记录, 注意这里只要有一个参数是NULL那么结果必定为NULL优先级:NOT AND OR(XOR)
运算符规则例子NOT 或!若为0结果为1若为1结果为0SELECT NOTAND 或 同一则一有一个0就是0SELECT 1 1OR 或 ||有一个一就是一全为0结果就是0SELECT 1 OR 0XOR两个不一样就是1一样就是0SELECT 1 XOR 0
位运算符
我的理解就是把操作数转换为补码按照指定的运算符的规则进行运算再把结果转换为十进制数字 支持的运算符
运算符含义例子按位与(AND)SELECT A B|按位或(OR)SELECT A | B^按位异或(XOR)SELECT A ^ B~按位取反SELECT ~A按位左移SELECT A 1按位右移SELECT B 1
解释: 以 8 和-2 为例 8转换为补码是 0 1000 -2的原码是 1 0010 -2的反码是 1 1101 -2的补码是 1 1110
8 -2 0 1000 1 1110 —————— 0 1000 这里直接转二进制即可结果是8
8 | -2 0 1000 | 1 1110 —————— 1 1110 最高位是1是负数要求真值对1 1110取补等于1 0010 转换为二进制数为-2 但是显示的结果是18446744073709551614应该是默认情况下数字以bigint类型存储的bigint类型是8个字节他的结果解释方式应该是把二进制解释成无符号数要不然不会这么大的数字如果是按照这么来的话那1 1110需要进行符号位扩展,扩展如下 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1110 ,就是在1 1110的最高位1前面加了79个1把这一串数字转换为十进制等于18446744073709551614
8 ^ -2 0 1000 | 1 1110 —————— 1 0110 符号位扩展扩展成64位扩展结果 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 0110,把这东西转换为十进制18446744073709551606
~ -2 -2的补码(64位)等于1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1110
# 1111 1111 - 0000 0000
# 1111 1111 - 0000 0000
# 1111 1111 - 0000 0000
# 1111 1111 - 0000 0000
# 1111 1111 - 0000 0000
# 1111 1111 - 0000 0000
# 1111 1110 - 0000 0001
# 从上到下,把-后面的数字串起来为1前面63个0当成无符号数转换为十进制为1所以答案就是11 2 tips:在一定的条件下可以当作除以2的n次幂来用 右移的规则:右移指定的位数后右边低位的数值被移出并丢弃左边高位空出的位置用0补齐。 这里不做符号位扩展了简要的记录一下 1的补码是0 1,右移一位01的最低位1就没了高位补零那么直接移成了0 1 2 tips: 在一定的条件下可以当成乘以2的n次幂来用 就是乘以了2n,n就是右移的位数可能会溢出
优先级 其他运算符
IS NULL: 判空的IS NOT NULL:判非空的LEAST:返回多值中的最小值
SELECT LEAST(1,2,3,4) FROM DUAL;GREATEST:返回多值中的最大值
SELECT GREATEST(1,2,3,4) FROM DUAL;BETWEEN AND:判断值是否在指定的区间内区间是左闭右闭的
SELECT 2 between 1 and 2;ISNULL:判空IN: 判断一个值是否为列表中的任意一个值 一般是在子查询里面用的比较多
SELECT 2 NOT IN (1,2);NOT IN:判断一个值是否不是一个列表中的任意一个值
SELECT 2 NOT IN (1,2);LIKE 运算符:匹配字符串的模糊匹配 %是通配零个或多个字符的 _是只能匹配一个字符的
SELECT abcd LIKE a__d;ESCAPE:回避特殊符号的,\也可以 比方说查找前两个字符是EM第三个字符是%或者_的 :取消_,%的匹配作用 ESCAPE:指定一个字符充当\的作用这个言简意赅点, 指定字符后面紧挨着的第一个匹配符号(_,%)失效,这个不太常用
SELECT DepartmentId FROM DEPT WHERE DepartmentId like EM\%or DepartmentId like EM\_;//这个用的比较少,了解
SELECT DepartmentId FROM DEPT WHERE DepartmentId like EMa% ESCAPE aor DepartmentId like EMb_ ESCAPE b;REGEXP运算符 这里要用到点正则表达式的东西,这个用到了去网上搜 正则表达式简要了解下这东西能写出一本书出来 ‘^’:匹配以该字符后面的字符开头的字符串 ‘$’:匹配以该字符前面的字符结尾的字符串 . : 匹配任何一个但字符 “[…]”:匹配出现在方括号里面的字符如匹配所有字母:[a-zA-Z] ‘*’:匹配零个或多个在它前面的字符, 扩展 正则表达式
常应用于检索字符串提取数字验证等等 MySQL支持的
查询以特定字符或字符串开头的记录: 在name列查询以ea开头的记录
SELECT * FROM user where name REGEXP ^ea;查询以特定字符或字符串结尾的记录: 在name列查询以or结尾的记录
SELECT * FROM user where name REGEXP or$;用符号’.通配一个字符,查询name列字段值中包含ea且e和a之间只有一个字母的记录
SELECT * FROM user where name REGEXP e.a;
用’*‘和’匹配多个字符:在name列中查找以字母c开头且c后面出现字母e至少一次的记录
SELECT * FROM user where name REGEXP ^ce;匹配指定字符串,可以匹配多个
在name列中查找包含ea的记录
SELECT * FROM user WHERE name REGEXP ea;在name列中查找包含ea和or的记录
SELECT * FROM user WHERE name REGEXP ea | or;匹配指定字符中任意一个,就是方括号的用法 在name列中查找包含字母abced和 wxy的记录
SELECT * FROM user WHERE name REGEXP [a-dw-y]匹配指定字符以外的字符 [^字符集合]匹配不在指定集合中的任何字符 在name列中查找id字符串包含a-h和数字1-8以外字符的记录
SELECT * FROM user WHERE id REGEXP [^a-h1-8];使用{n,}或者{n,m}来指定字符串连续出现的次数,“字符串{n,}”表示至少匹配n次前面的字符“字符串{n,m}”表示匹配前面的字符串不少于n次不多于m次。例如or{2,}表示or连续出现至少2次也可以 大于2次ea{2,4}表示ea连续出现最少2次最多不能超过4次。
第五章 排序与分页
排序
使用order by 子句进行排序
asc(ascend):升序desc(descend):降序 一般是在select语句的结尾处 比如 查找员工的姓名工资,部门号,按部门id进行升序排序若相同按工资降序排序
SELECT last_name,department_id,salary
FROM employees
ORDER BY department_id,salary DESC;分页
就是一次性查找出来的数据很多时用户一看数据太多将不想看了降低可读性 格式 LIMIT [位置偏移量,] 行数 位置偏移量可选,若不写就是0行数就是你要显示的条数看例子
前5条记录
SELECT * FROM TableName LIMIT 0,5; --前5条记录
SELECT * FROM TableName LIMIT 5;--实现效果和上面的一样第6~15条数据
SELECT * FROM TableName LIMIT 5,10;MySQL8中可以使用LIMIT 4 OFFSET 4就是从第5条记录后面开始的4条数据 等同于 LIMIT 4,4 分页显示的公式 (当前页数-1)*每页条数,每页条数
SELECT * FROM TableName LIMIT (PageNo-1)*PageSize,PageSize;第六章 多表查询
引子
案例查询员工的姓名及其部门名称 需求:查询员工的姓名及其部门名称,你可能会这么写
SELECT last_name, department_name
FROM emploees, departments;查询结果 employee只有107条数据那么结果也应该只有107条但是结果是2889条实际上是做了一个交叉连接(不推荐使用)
笛卡尔积的理解 SQL92中笛卡尔积也称为交叉连接英文是 CROSS JOIN。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积 出现问题的原因: 忽略多个表的连接条件(或关联条件) 连接条件无效 所有表中的所有行互相连接 没有条件就加条件
#案例查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id departments.department_id;多表查询分类详解
等值连接 vs 非等值连接
等值连接
等值连接我的理解是表之间有关联字段的且连接条件是什么等于什么的
SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id,departments.location_id
FROM employees, departments
WHERE employees.department_id departments.department_id;区分重复的列名: 多表中有相同的列的时候必须在列名之前加上表名前缀 独有的也建议加上表名前缀 表的别名 可以简化查询,如果使用了别名那么在查询字段中、过滤条件中就只能使用别名进行代替 SELECT emp.employee_id, emp.last_name, emp.department_id,dep.department_id, dep.location_id
FROM employees emp , departments dep
WHERE emp.department_id dep.department_id;连接多个表 总结连接 n个表,至少需要n-1个连接条件。比如连接三个表至少需要两个连接条件。 非等值连接
查询员工姓名,工资以及对应的等级 所涉及到的表结构 employee的
字段名字段类型employee_id,intfirst_namevarchar(20)last_namevarchar(25)emailvarchar(25)phone_numbervarchar(20)hire_datedatejob_idvarchar(10)salary“double(8,2)”commission_pct“double(2,2)”manager_idintdepartment_idint
job_grades
字段名字段类型grade_levelvarchar(3)lowest_salinthighest_salint
SELECT last_name,salary,grade_level
FROM employee e,job_grades j
WHERE e.salary BETWEEN lowest_sal AND highest_sal;自连接 vs 非自连接
自连接:自己连接自己 非自连接:自己不连接自己的 WORKER表中的MANAGER_ID和MANAGER表中的EMPLOYEE_ID相等 需求: 查询employees表返回xxx works for xxx
SELECT CONCAT(worker.last_name,works for ,manager.last_name)
FROM employees worker,employees manager
WHERE worker.manager_id manager.employee_id;内连接 vs 外连接
内连接
合并具有同一列的两个以上的表的行结果集中不包含一个表与另一个表不匹配的行 我自己复述出来的:合并相同列只包含满足条件的 需求: 查询所有员工的last_name,department_name信息
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id d.department_id;内连接(INNER JOIN)实现 语法
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;- 需求 查询员工id员工姓名员工的部门id部门id部门的位置id
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e JOIN departments d
ON e.department_id d.department_id;需求:查询员工id部门名字部门所在的城市
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id e.department_id
JOIN locations l
ON d.location_id l.location_id;外连接 可以查询满足条件的记录也可以查询某一方不满足条件的记录
内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左或右表中不满足条件的 行 这种连接称为左或右 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。如果是左外连接则连接条件中左边的表也称为 主表 右边的表称为 从表 。如果是右外连接则连接条件中右边的表也称为 主表 左边的表称为 从表 。SQL92:使用()创建连接 在 SQL92 中采用代表从表所在的位置。即左或右外连接中() 表示哪个是从表。 Oracle 对 SQL92 支持较好**而 MySQL 则不支持 SQL92 的外连接。** #左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id departments.department_id();#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id() departments.department_id;
接下来记录MySQL支持的写法 SQL99语法实现多表查询 通过JOIN…ON子句来实现的下面是语法结构
SELECT table1.column, table2.column,table3.column
FROM table1JOIN table2 ON table1 和 table2 的连接条件JOIN table3 ON table2 和 table3 的连接条件
左外连接(LEFT OUTER JOIN)
语法:
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;例子:查询员工的姓名,所在的部门id所在的部门名称
SELECT e.last_name,e.department_id,d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id d.department_id;右外连接(RIGHT OUTER JOIN)
语法:
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;例子:查询员工的姓名,所在的部门id所在的部门名称
SELECT e.last_name,e.department_id,d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id d.department_id;注意 LEFT JOIN 和RIGHT JOIN 只存在于SQL99及以后的标准
满外连接(FULL OUTER JOIN) 满外连接的结果 左右表匹配的数据 左表没有匹配到的数据 右表没有匹配到的数据。
满外连接(FULL JOIN)
但是 MySQL不支持FULL JOIN我们可以通过LEFT JOIN UNION RIGHT JOIN 来实现
UNION的作用 合并查询结果,合并的时候两个表对应的列数和数据类型必须相同并且相互对应,各个SELECT语句之间使用UNION或UNION ALL关键字分割 语法格式:
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2UNION的图解
UNION ALL的图解 但是UNION ALL的效率高
举例:查询部门编号90或邮箱包含a的员工信息
# 方式1
SELECT * FROM employees WHERE email LIKE %a% OR department_id90;
# 方式2
SELECT * FROM employees WHERE email LIKE %a%
UNION
SELECT * FROM employees WHERE department_id90;举例:查询中国用户中男性的信息以及美国用户中中年男性的用户信息
SELECT id,cname FROM t_chinamale WHERE csex男
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGendermale;7种JOIN的实现方式 # 中图 内连接A ∩ B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id d.department_id;# 左上图左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id d.department_id;# 右上图:右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id d.department_id;# 左中图 A - A ∩ B (A ∩ B 的department_id必然不等于null,我们想要结果中的department_id要等于null的)
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id d.department_id
WHERE d.department_id IS NULL;# 右中图
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id d.department_id
WHERE e.department_id IS NULL# 左下图
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id d.department_id
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id d.department_id# 右下图 左下图 - 中图 左中图 ∪ 右中图
# 左中图 ∪ 右中图
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id d.department_id
WHERE e.department_id IS NULL;# 左下图 - 中图 这个我写的实现不了新特性
NATURAL JOIN 帮我们自动查询两张连接表中 所有相同的字段 然后进行等值连接 。 SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id d.department_id
AND e.manager_id d.manager_id;# 用NATUAL JOIN
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;USING 连接
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id d.department_id;
# 用 USING
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);USING只能和JOIN一起使用并且要求两个关联字段在关联表中名称一致而且只能表示关联字段值相等,这个应用比较窄
第七章 单行函数
数值函数
基本函数
函数名称函数作用ABS(x)返回x的绝对值SIGN(X)返回X的符号。正数返回1负数返回-10返回0PI()返回圆周率的值CEIL(x)CEILING(x)返回大于或等于某个值的最小整数FLOOR(x)返回小于或等于某个值的最大整数LEAST(e1,e2,e3…)返回列表中的最小值GREATEST(e1,e2,e3…)返回列表中的最大值MOD(x,y)返回X除以Y后的余数RAND()返回0~1的随机值RAND(x)返回0~1的随机值其中x的值用作种子值相同的X值会产生相同的随机数ROUND(x)返回一个对x的值进行四舍五入后最接近于X的整数ROUND(x,y)返回一个对x的值进行四舍五入后最接近X的值并保留到小数点后面Y位TRUNCATE(x,y)返回数字x截断为y位小数的结果,若y0则对整数进行操作SQRT(x)返回x的平方根。当X0时返回NULLRADIANS(x)将角度转化为弧度其中参数x为角度值DEGREES(x)将弧度转化为角度其中参数x为弧度值 弧度制与角度制的转换 r 1,一圈是2Π,一圈是360°, 1° 2Π/360° Π/180° 3.1415926…/180° 0.017453 弧度值-角度值 角度值 弧度值 * (180/Π) 举例: SELECT
ABS(-1),ABS(3),SIGN(-23),SIGN(43),PI(),CEIL(32.5),CEILING(-43.5),FLOOR(32.5)
FROM DUAL;结果
SELECT
FLOOR(-43.5),MOD(12,-5),MOD(-12,5),MOD(17,-3),RAND(),RAND(),RAND(-1),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;结果
SELECT
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;结果
SELECT RADIANS(25),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90))
FROM DUAL;三角函数
函数用法SIN(x)返回x的正弦值其中参数x为弧度值ASIN(x)返回x的反正弦值即获取正弦为x的值。如果x的值不在-1到1之间则返回NULLCOS(x)返回x的余弦值其中参数x为弧度值ACOS(x)返回x的反余弦值即获取余弦为x的值。如果x的值不在-1到1之间则返回NULLTAN(x)返回x的正切值其中参数x为弧度值ATAN(x)返回x的反正切值即返回正切值为x的值ATAN2(m,n)返回两个参数的反正切值COT(x)返回x的余切值其中X为弧度值 ATAN2(M,N)函数返回两个参数的反正切值。 与ATAN(X)函数相比ATAN2(M,N)需要两个参数例如有两个 点point(x1,y1)和point(x2,y2)使用ATAN(X)函数计算反正切值为ATAN((y2-y1)/(x2-x1))使用ATAN2(M,N)计 算反正切值则为ATAN2(y2-y1,x2-x1)。由使用方式可以看出当x2-x1等于0时ATAN(X)函数会报错而 ATAN2(M,N)函数则仍然可以计算。 就是ATAN2的应用范围要比ATAN要大 SELECT
SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1)
)
FROM DUAL;指数与对数
函数用法POW(x,y),POWER(X,Y)返回x的y次方EXP(X)返回e的X次方其中e是一个常数2.718281828459045LN(X)LOG(X)返回以e为底的X的对数当X 0 时返回的结果为NULLLOG10(X)返回以10为底的X的对数当X 0 时返回的结果为NULLLOG2(X)返回以2为底的X的对数当X 0 时返回NULL 进制转换类
函数用法BIN(x)返回x的二进制编码HEX(x)返回x的十六进制编码OCT(x)返回x的八进制编码CONV(x,f1,f2)返回f1进制数变成f2进制数 字符串函数
函数用法ASCII(S)返回字符串S中的第一个字符的ASCII码值CHAR_LENGTH(s)返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同LENGTH(s)返回字符串s的字节数和字符集有关CONCAT(s1,s2,…,sn)连接s1,s2,…,sn为一个字符串CONCAT_WS(separate,s1,s2,…,sn)同CONCAT(s1,s2,…)函数用separate来连接s1~snINSERT(str, idx, len,replacestr)将字符串str从第idx位置开始len个字符长的子串替换为字符串replacestrREPLACE(str, a, b)用字符串b替换字符串str中所有出现的字符串aUPPER(s) 或 UCASE(s)将字符串s的所有字母转成大写字母LOWER(s) 或LCASE(s)将字符串s的所有字母转成小写字母LEFT(str,n)返回字符串str最左边的n个字符RIGHT(str,n)返回字符串str最右边的n个字符LPAD(str, len, pad)用字符串pad对str最左边进行填充直到str的长度为len个字符RPAD(str ,len, pad)用字符串pad对str最右边进行填充直到str的长度为len个字符LTRIM(s)去掉字符串s左侧的空格RTRIM(s)去掉字符串s右侧的空格TRIM(s)去掉字符串s开始与结尾的空格TRIM(s1 FROM s)去掉字符串s开始与结尾的s1TRIM(LEADING s1 FROM s)去掉字符串s开始处的s1TRIM(TRAILING s1 FROM s)去掉字符串s结尾处的s1REPEAT(str, n)返回str重复n次的结果SPACE(n)返回n个空格STRCMP(s1,s2)比较字符串s1,s2的ASCII码值的大小SUBSTR(s,index,len)返回从字符串s的index位置其len个字符作用与SUBSTRING(s,n,len),MID(s,n,len)相同LOCATE(substr,str)返回字符串substr在字符串str中首次出现的位置作用于POSITION(substrIN str)、INSTR(str,substr)ELT(m,s1,s2,…,sn)返回指定位置的字符串如果m1则返回s1如果m2则返回s2如果mn则返回snFIELD(s,s1,s2,…,sn)返回字符串s在字符串列表中第一次出现的位置FIND_IN_SET(s1,s2)返回字符串s1在字符串s2中第一次出现的位置。其中字符串s2是一个以逗号分隔的字符串REVERSE(s)返回s反转后的字符串NULLIF(value1,value2)比较两个字符串如果value1与value2相等则返回NULL否则返回value1 字符串起始位置从1开始 我挑几个我不会的记录
SELECT TRIM(A FROM AABAA),TRIM(LEADING C FROM CACC),TRIM(TRAILING B FROM AEBBB)FROM dual;SELECT FIND_IN_SET(AB,AA,AB,AB),ELT(2,A,B,C),LOCATE(NOTDY,THE NOTDY WEBSITE),NULLIF(A,B)FROM dual;日期和时间函数
获取日期和时间
函数用法CURDATE() CURRENT_DATE()返回当前日期只包含年、月、日CURTIME() CURRENT_TIME()返回当前时间只包含时、分、秒NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() /LOCALTIMESTAMP()返回当前系统日期和时间UTC_DATE()返回UTC世界标准时间日期UTC_TIME()返回UTC世界标准时间时间
SELECT CURRENT_DATE(),CURRENT_TIME(),SYSDATE(),UTC_DATE(),UTC_TIME()FROM dual;日期与时间戳的转换
函数用法UNIX_TIMESTAMP()以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() -1634348884UNIX_TIMESTAMP(date)将时间date以UNIX时间戳的形式返回。FROM_UNIXTIME(timestamp)将UNIX时间戳的时间转换为普通格式的时间
SELECT UNIX_TIMESTAMP(SYSDATE()),UNIX_TIMESTAMP(CURDATE()),UNIX_TIMESTAMP(CURTIME()),UNIX_TIMESTAMP(2023-8-19 10:43:10)FROM dual;获取月份、星期、星期数、天数等函数
函数用法YEAR(date) / MONTH(date) / DAY(date)返回具体的日期值HOUR(time) / MINUTE(time) /SECOND(time)返回具体的时间值MONTHNAME(date)返回月份January…DAYNAME(date)返回星期几MONDAY TUESDAY…SUNDAYWEEKDAY(date)返回周几注意周1是0周2是1。。。周日是6QUARTER(date)返回日期对应的季度范围为14WEEK(date) WEEKOFYEAR(date)返回一年中的第几周DAYOFYEAR(date)返回日期是一年中的第几天DAYOFMONTH(date)返回日期位于所在月份的第几天DAYOFWEEK(date)返回周几注意周日是1周一是2。。。周六是7
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;SELECT MONTHNAME(2023-10-26),DAYNAME(2023-10-26),WEEKDAY(2023-10-26),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW())
FROM DUAL;SELECT DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;日期与操作函数
函数用法EXTRACT(type FROM date)返回指定日期中特定的部分type指定返回的值type的取值与含义 SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( DAY FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( DAY_SECOND FROM NOW())
FROM DUAL;时间和秒钟转换的函数
函数用法TIME_TO_SEC(time)将 time 转化为秒并返回结果值。转化的公式为 小时3600分钟60秒SEC_TO_TIME(seconds)将 seconds 描述转化为包含小时、分钟和秒的时间v
SELECT TIME_TO_SEC(SYSDATE()),SEC_TO_TIME(7878)
FROM DUAL;计算日期和时间的函数
第一组
函数用法DATE_ADD(datetime, INTERVAL expr type)ADDDATE(date,INTERVAL expr type)返回与给定日期时间相差INTERVAL时间段的日期时间DATE_SUB(date,INTERVAL expr type)SUBDATE(date,INTERVAL expr type)返回与date相差INTERVAL时间间隔的日期
type的取值 例子:
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD(2021-10-21 23:32:12,INTERVAL
1 SECOND) AS col2,
ADDDATE(2021-10-21 23:32:12,INTERVAL 1 SECOND) AS col3,
DATE_ADD(2021-10-21 23:32:12,INTERVAL 1_1 MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL 1_1 YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;SELECT DATE_SUB(2021-01-21,INTERVAL 31 DAY) AS col1,
SUBDATE(2021-01-21,INTERVAL 31 DAY) AS col2,
DATE_SUB(2021-01-21 02:01:01,INTERVAL 1 1 DAY_HOUR) AS col3
FROM DUAL;第2组:
函数用法ADDTIME(time1,time2)返回time1加上time2的时间。当time2为一个数字时代表的是秒 可以为负数SUBTIME(time1,time2)返回time1减去time2后的时间。当time2为一个数字时代表的是 秒 可以为负数DATEDIFF(date1,date2)返回date1 - date2的日期间隔天数TIMEDIFF(time1, time2)返回time1 - time2的时间间隔FROM_DAYS(N)返回从0000年1月1日起N天以后的日期TO_DAYS(date)返回日期date距离0000年1月1日的天数LAST_DAY(date)返回date所在月份的最后一天的日期MAKEDATE(year,n)针对给定年份与所在年份中的天数返回一个日期MAKETIME(hour,minute,second)将给定的小时、分钟和秒组合成时间并返回PERIOD_ADD(time,n)返回time加上n后的时间
SELECT
ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),1:1:3),DATEDIFF(NOW(),2023-8-
01),
TIMEDIFF(NOW(),2023-8-19 10:10:18)FROM DUAL;SELECTFROM_DAYS(366),TO_DAYS(0000-12-25),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12), # 这个MAKEDAET我试了感觉是在这一年的第一天的基础上加上nn就是后面的数字
MAKETIME(10,21,23),PERIOD_ADD(20200101010101,
10)FROM DUAL;日期的格式化与解析
函数用法DATE_FORMAT(date,fmt)按照字符串fmt格式化日期date值TIME_FORMAT(time,fmt)按照字符串fmt格式化时间time值GET_FORMAT(date_type,format_type)返回日期字符串的显示格式STR_TO_DATE(str, fmt)按照字符串fmt对str进行解析解析为一个日期
常用的fmt的格式符
格式符说明格式符说明%Y4位数字表示年份%y表示两位数字表示年份%M月名表示月份January,…%m两位数字表示月份01,02,03。。。%b缩写的月名Jan.Feb.…%c数字表示月份1,2,3,…%D英文后缀表示月中的天数1st,2nd,3rd,…%d两位数字表示月中的天数(01,02…)%e数字形式表示月中的天数1,2,3,4,5…%H两位数字表示小数24小时制01,02…%h和%I两位数字表示小时12小时制01,02…%k数字形式的小时24小时制(1,2,3)%l数字形式表示小时12小时制1,2,3,4…%i两位数字表示分钟00,01,02%S和%s两位数字表示秒(00,01,02…)%W一周中的星期名称Sunday…%a一周中的星期缩写Sun.Mon.,Tues.…%w以数字表示周中的天数(0Sunday,1Monday…)%j以3位数字表示年中的天数(001,002…)%U以数字表示年中的第几周1,2,3。。其中Sunday为周中第一天%u以数字表示年中的第几周1,2,3。。其中Monday为周中第一天%T24小时制%r12小时制%pAM或PM%%表示%
data_type和format_type参数取值如下:
SELECT DATE_FORMAT(NOW(),%H:%i:%s),STR_TO_DATE(09/01/2009,%m/%d/%Y),STR_TO_DATE(20190522154706,%Y%m%d%H%i%s),STR_TO_DATE(2014-04-22 15:47:06,%Y-%m-%d %H:%i:%s);SELECT GET_FORMAT(DATE, USA),DATE_FORMAT(NOW(),GET_FORMAT(DATE,USA)),STR_TO_DATE(2020-01-01 00:00:00,%Y-%m-%d);流程控制函数
函数用法IF(value,value1,value2)如果value的值为TRUE返回value1否则返回value2IFNULL(value1, value2)如果value1不为NULL返回value1否则返回value2CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END相当于Java的if…else if…else…CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END相当于Java的switch…case…
SELECT IF(2-1,TRUE,FALSE);
-- TRUE
SELECT IFNULL(null,abc);
-- abc
SELECT CASE WHEN 10 THEN 10WHEN 20 THEN 20ELSE 30 END-- 10;
SELECT employee_id,salary, CASE WHEN salary15000 THEN 高薪
WHEN salary10000 THEN 潜力股
WHEN salary8000 THEN 屌丝
ELSE 草根 END 描述
FROM employees;SELECT oid,status, CASE status WHEN 1 THEN 未付款
WHEN 2 THEN 已付款
WHEN 3 THEN 已发货
WHEN 4 THEN 确认收货
ELSE 无效订单 END
FROM t_order;SELECT CASE WHEN 1 0 THEN yes WHEN 1 0 THEN no ELSE unknown END;SELECT CASE WHEN 1 0 THEN yes WHEN 1 0 THEN no ELSE unknown END;SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;SELECT CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;SELECT employee_id,12 * salary * (1 IFNULL(commission_pct,0))
FROM employees;SELECT last_name, job_id, salary,
CASE job_id WHEN IT_PROG THEN 1.10*salary
WHEN ST_CLERK THEN 1.15*salary
WHEN SA_REP THEN 1.20*salary
ELSE salary END REVISED_SALARY
FROM employees;加密与解密函数
函数用法PASSWORD(str)返回字符串str的加密版本41位长的字符串。加密结果 不可逆 常用于用户的密码加密MD5(str)返回字符串str的md5加密后的值也是一种加密方式。若参数为NULL则会返回NULLSHA(str)从原明文密码str计算并返回加密后的密码字符串当参数为NULL时返回NULL。 SHA加密算法比MD5更加安全 。ENCODE(value,password_seed)返回使用password_seed作为加密密码加密valueDECODE(value,password_seed)返回使用password_seed作为加密密码解密value
SELECT PASSWORD(mysql), PASSWORD(NULL);
| PASSWORD(mysql) | PASSWORD(NULL) |
-----------------------------------------------------------
| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | |
-----------------------------------------------------------
SELECT md5(123);
43cca4b3de2097b9558efefd0ecc3588SELECT SHA(Tom123)
-c7c506980abc31cc390a2438c90861d0f1216d50SELECT ENCODE(mysql, mysql);
--------------------------
| ENCODE(mysql, mysql) |
--------------------------
| íg ¼ ìÉ |
--------------------------
1 row in set, 1 warning (0.01 sec)mysql SELECT DECODE(ENCODE(mysql,mysql),mysql);
-----------------------------------------
| DECODE(ENCODE(mysql,mysql),mysql) |
-----------------------------------------
| mysql |
-----------------------------------------
1 row in set, 2 warnings (0.00 sec)
MySQL信息函数
函数用法VERSION()返回当前MySQL的版本号CONNECTION_ID()返回当前MySQL服务器的连接数DATABASE()SCHEMA()返回MySQL命令行当前所在的数据库USER()CURRENT_USER()、SYSTEM_USER()SESSION_USER()返回当前连接MySQL的用户名返回结果格式为“主机名用户名”CHARSET(value)返回字符串value自变量的字符集COLLATION(value)返回字符串value的比较规则
SELECT DATABASE(),USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER(),CHARSET(ABDC),COLLATION(ABCD);其他函数
函数用法FORMAT(value,n)返回对数字value进行格式化后的结果数据。n表示 四舍五入 后保留到小数点后n位CONV(value,from,to)将value的值进行不同进制之间的转换INET_ATON(ipvalue)将以点分隔的IP地址转化为一个数字INET_NTOA(value)将数字形式的IP地址转化为以点分隔的IP地址BENCHMARK(n,expr)将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间CONVERT(value USING char_code)将value所使用的字符编码修改为char_code
# 如果n的值小于或者等于0则只保留整数部分
mysql SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2);
-------------------------------------------------------------
| FORMAT(123.123, 2) | FORMAT(123.523, 0) | FORMAT(123.123, -2) |
-------------------------------------------------------------
| 123.12 | 124 | 123 |
-------------------------------------------------------------
1 row in set (0.00 sec)
mysql SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);
------------------------------------------------------
| CONV(16, 10, 2) | CONV(8888,10,16) | CONV(NULL, 10, 2) |
------------------------------------------------------
| 10000 | 22B8 | NULL |
------------------------------------------------------
1 row in set (0.00 sec)
mysql SELECT INET_ATON(192.168.1.100);
----------------------------
| INET_ATON(192.168.1.100) |
----------------------------
| 3232235876 |
----------------------------
1 row in set (0.00 sec)
# 以“192.168.1.100”为例计算方式为192乘以256的3次方加上168乘以256的2次方加上1乘以256再加上
100。
mysql SELECT INET_NTOA(3232235876);
-----------------------
| INET_NTOA(3232235876) |
-----------------------
| 192.168.1.100 |
-----------------------
1 row in set (0.00 sec)
mysql SELECT BENCHMARK(1, MD5(mysql));
----------------------------
| BENCHMARK(1, MD5(mysql)) |
----------------------------
| 0 |
----------------------------
1 row in set (0.00 sec)
mysql SELECT BENCHMARK(1000000, MD5(mysql));
----------------------------------
| BENCHMARK(1000000, MD5(mysql)) |
----------------------------------
| 0 |
----------------------------------
1 row in set (0.10 sec)
mysql SELECT CHARSET(mysql), CHARSET(CONVERT(mysql USING utf8));
----------------------------------------------------------
| CHARSET(mysql) | CHARSET(CONVERT(mysql USING utf8)) |
----------------------------------------------------------
| utf8mb4 | utf8 |
----------------------------------------------------------
1 row in set, 1 warning (0.00 sec)第八章 聚合函数 语法 注意不能嵌套使用
常用的几个
AVG和SUM函数
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE %REP%;MIN和MAX函数
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;COUNT函数
count(*)返回表中记录总数适用于任意数据类型 count(expr)返回expr不为空的记录总数
二者谁好呢? 对于MyISAM引擎没有区别,对于Innodb引擎的表用count(*),count(1)直接读取行数复杂度是O(n)
能不能使用count(列名)替换count()? 不能,count()是SQL92定义的标准统计行数的语法,跟数据库无关跟NULL和非NULL无关 说明count(*)会统计值为 NULL 的行而 count(列名)不会统计此列为 NULL 值的行。
GROUP BY
比如要求出EMPLOYEES表中各部门的平均工资 语法:
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];WHERE 一定放在FROM后面,并且SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中,包含在GROUP BY子句中的列不必包含在SELECT列表中
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
-- department_id没有包含在组函数中但是在GROUP BY当中使用多个列进行分组
(若列1相同看列2都相同归为一组一个不同归为多组)
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;使用WITH ROLLUP
查询出的分组记录之后增加一条记录:计算查询出的所有记录的总和
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id 80
GROUP BY department_id WITH ROLLUP;HAVING 这东西必须和GROUP BY一起使用 统计部门最高工资比1000高的部门
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)10000 ;WHERE 和HAVING的对比
HAVING必须要与GROUP BY配合使用可以把分组计算的函数和分组字段作为筛选条件,而WHERE 可以把表中的字段作为筛选条件但是不能使用分组中的计算函数作为筛选条件WHERE是先筛选后连接,而HAVING是先连接后筛选
优点缺点WHERE先筛选数据再关联执行效率高不能使用分组中的计算函数进行筛选HAVING可以使用分组中的计算函数在结果中集中进行筛选效率低
SELECT执行顺序
关键字的顺序是不能颠倒的 SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT … SELECT语句执行的顺序
FROM left_table
ON join_condition
join_type JOIN right_table
WHERE where_condition
GROUP BY group_by_list
HAVING having_condition
SELECT
DISTINCT select_list
ORDER BY order_by_condition
LIMIT limit_numbersql语句如下:
SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id team.team_id # 顺序 1
WHERE height 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7每一步都产生了一个虚拟表把这个虚拟表传入下一个步骤作为输入对我们来说是不可见的
SQL的执行原理
SELECT 是先执行 FROM 这一步的。在这个阶段如果是多张表联查还会经历下面的几个步骤
首先先通过 CROSS JOIN 求笛卡尔积相当于得到虚拟表 vtvirtual table1-1通过 ON 进行筛选在虚拟表 vt1-1 的基础上进行筛选得到虚拟表 vt1-2添加外部行。如果我们使用的是左连接、右链接或者全连接就会涉及到外部行也就是在虚拟 表 vt1-2 的基础上增加外部行得到虚拟表 vt1-3。 当然如果我们操作的是两张以上的表还会重复上面的步骤直到所有表都被处理完为止。这个过程得 到是我们的原始数据。 SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT… FROM - WHERE - GROUP BY - HAVING - SELECT 的字段 - DISTINCT - ORDER BY - LIMIT SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5 FROM player JOIN team ON player.team_id team.team_id # 顺序 1 WHERE height 1.80 # 顺序 2 GROUP BY player.team_id # 顺序 3 HAVING num 2 # 顺序 4 ORDER BY num DESC # 顺序 6 LIMIT 2 # 顺序 7 当我们拿到了查询数据表的原始数据也就是最终的虚拟表 vt1 就可以在此基础上再进行 WHERE 阶 段 。在这个阶段中会根据 vt1 表的结果进行筛选过滤得到虚拟表 vt2 。 然后进入第三步和第四步也就是 GROUP 和 HAVING 阶段 。在这个阶段中实际上是在虚拟表 vt2 的 基础上进行分组和分组过滤得到中间的虚拟表 vt3 和 vt4 。 当我们完成了条件筛选部分之后就可以筛选表中提取的字段也就是进入到 SELECT 和 DISTINCT 阶段 。 首先在 SELECT 阶段会提取想要的字段然后在 DISTINCT 阶段过滤掉重复的行分别得到中间的虚拟表 vt5-1 和 vt5-2 。 当我们提取了想要的字段数据之后就可以按照指定的字段进行排序也就是 ORDER BY 阶段 得到 虚拟表 vt6 。 最后在 vt6 的基础上取出指定行的记录也就是 LIMIT 阶段 得到最终的结果对应的是虚拟表 vt7 。 当然我们在写 SELECT 语句的时候不一定存在所有的关键字相应的阶段就会省略。 同时因为 SQL 是一门类似英语的结构化查询语言所以我们在写 SELECT 语句的时候还要注意相应的 关键字顺序所谓底层运行的原理就是我们刚才讲到的执行顺序。
第九章 子查询
就是嵌套查询
基本使用
SELECT select_list
FROM table
WHERE expr operator (SELECT select_listFROM table);子查询在主查询之前一次执行完成子查询的结果被主查询(外查询)使用子查询要包含在括号内子查询要放在比较条件的右侧单行操作符-单行子查询;多行操作符-多行子查询
分类
按 返回的结果是一条数据还是多条数据来分
单行子查询
这个最简单 操作符就是哪些等于小于(等于)哪些东西,我不记录
查询工资大于149号员工工资的员工信息
// 主查询是查询员工信息的条件是工资大于149号的
// 子查询是查询149号员工的工资的
SELECT last_name
FROM employees
WHERE salary (SELECT salaryFROM employeesWHERE employee_id 143);返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资 SELECT last_name,job_id,salary
FROM employees
WHERE job_id (SELECT job_id FROM employees WHERE employee_id 141)// 找到141号员工的job_id
AND salary (SELECT salary FROM employees WHERE employee_id 143);//找到143号员工的工资返回工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary
(SELECT MIN(salary)
FROM employees);查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id manager_iddepartment_id
//方式1:
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
(SELECT manager_id
FROM employees
WHERE employee_id IN (174,141))
AND department_id IN
(SELECT department_id
FROM employees
WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);
//方式2:成对比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);HAVING中的子查询
首先执行子查询向主查询中的HAVING子句返回结果
查询最低工资大于50号部门最低工资的部门id和其最低工资
// 描述50号部门最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) (SELECT MIN(salary)FROM employeesWHERE department_id 50);CASE中的子查询
显示员工的employee_id,last_name和location。其中若员工department_id与location_id为1800 的department_id相同则location为’Canada’其余则为’USA’。
SELECT employee_id, last_name,
(CASE department_idWHEN(SELECT department_id FROM departmentsWHERE location_id 1800)THEN Canada ELSE USA END) location
FROM employees;子查询的空值问题
不返回任何行
多行子查询
就是子查询返回多行使用多行比较符
比较操作符
操作符含义IN等于列表中的任意一个ANY需要和单行比较操作符一起使用和子查询返回的某一个值比较ALL需要和单行比较操作符一起使用和子查询返回的所有值比较SOME实际上是ANY的别名作用相同一般常使用ANY
返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary ANY (SELECT salary FROM employees WHERE job_id IT_PROG)
AND job_id!IT_PROG;返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary ALL (SELECT salary FROM employees WHERE job_id IT_PROG)
AND job_id!IT_PROG;查询平均工资最低的部门id
//方式1:
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) (
SELECT MIN(avg_sal)
FROM (SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id
) dept_avg_sal;
//方式2:
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) ALL (SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id)
);按按内查询是否被执行多次来分
关联子查询
子查询依赖主查询一般是子查询中使用了主查询的表中的某些列并且对这些列做了条件关联。 执行一次外部查询子查询需要重新计算一次
查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
// 方式1:
SELECT last_name,salary,department_id
FROM employees e
WHERE salary (SELECT AVG(salary) FROM employees WHERE department_id e.department_id);
//方式2:在FROM中使用子查询
SELECT last_name,salary,e1.department_id
FROM employees e1,
(SELECT department_id,AVG(salary) dept_avg_sal FROM employees
GROUP BY department_id) e2
WHERE e1.department_id e2.department_id
AND e2.dept_avg_sal e1.salary;//也可以写成这样
SELECT last_name,salary,e1.department_id
FROM employees e1 JOIN (SELECT department_id,AVG(salary) dept_avg_sal FROM employees
GROUP BY department_id) e2
ON e1.department_id e2.department_id
AND e2.dept_avg_sal e1.salary;查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (SELECT department_nameFROM departments dWHERE e.department_id d.department_id
);若employees表中employee_id与job_history表中employee_id相同的数目不小于2输出这些相同id的员工的employee_id,last_name和其job_id
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 (SELECT COUNT(*)FROM job_historyWHERE employee_id e.employee_id);EXISTS与NOT EXISTS关键字
检查在子查询中是否存在满足条件的行 如果在子查询中不存在满足条件的行NOT EXISTS返回trueEXISTS返回false 如果在子查询中存在满足条件的行NOT EXISTS返回falseEXISTS返回true.
查询公司管理者的employee_idlast_namejob_iddepartment_id信息
// 方式1:
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *FROM employees e2WHERE e2.manager_id e1.employee_id);
//方式2:自连接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1
JOIN employees e2
WHERE e1.employee_id e2.manager_id;
//方式3:
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_idFROM employees
);查询departments表中不存在于employees表中的部门的department_id和department_name
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT XFROM employeesWHERE department_id d.department_id);不相关子查询
相关更新
在employees中增加一个department_name字段数据为员工对应的部门名称
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
# 2
UPDATE employees e
SET department_name (SELECT department_nameFROM departments dWHERE e.department_id d.department_id);相关删除
删除表employees中其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in(SELECT employee_idFROM emp_historyWHERE employee_id e.employee_id);自连接与子查询效率谁好
自连接好子查询实际上是通过未知表进行查询后的条件判断而自连接是通过已知的自身数据表 进行条件判断因此在大部分 DBMS 中都对自连接处理进行了优化。
