目录
IS NULL / IS NOT NULL / ISNULL( )
左外连接LEFT-OUTE 和 右外连接RIGHT-OUTER
SELECT-FROM结构
SELECT-FROM语句简单使用
#格式:SELECT 字段1,字段2... FROM 表名;
#SELECT 简单使用
SELECT 1 + 1 ; #这种情况隐式使用了伪表
SELECT 1 + 1 FROM DUAL #DUAL:伪表 显示使用伪表
SELECT sname,sex
FROM stu; #查询stu表的sname,sex字段
别名,去重和空值参与运算
给字段设置别名
设置别名:就是查询出来的字段名显示为我们设置的别名
#给字段设置别名的三种方式
#方式一:直接加
SELECT sname name FROM stu #name就是设置的别名
#使用AS关键字
SELECT sname AS name FROM stu
#使用双引号
SELECT sname "name 1" FROM stu
去重操作
使用关键字:DISTINCT
#去重 DISTINCT
SELECT DISTINCT *
FROM employees;
空值参与运算
所有运算符或列值遇到null值,结果都为空值。
SELECT 1 + null FROM DUAL; //null
着重号的使用和查询常数
当表名与关键字重名时,可以使用一对` ` 着重号引起来
SELECT * FROM `order`;
查询常数
添加常数可以在每一条记录都可以显示这个常数。
SELECT '常数',name FROM t_test;
显示表结构DESC和过滤数据WHERE
显示表结构
使用关键字:DESCRIBE或它的简写DESC
DESC t_test;
DESCRIBE t_test;
过滤数据:WHERE
语法说明:
过滤条件:此条件将不满足的记录排除掉。
SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件
SELECT *
FROM t_test
WHERE id < 5;
运算符
算数运算符
算数运算符主要用于数学运算,其可以连接运算符前后的两个数值或表达式,对数值或表达式进行以下运算:
在sql中,+号只有相加的意思,由此:1 + ‘1’ =2 (隐式转换),并不存在连接的意思。
比较运算符
比较运算符用来对表达式左边的操作数和右边的操作数进行比较,比较结果为真返回1,反之返回0,其他情况返回null。
等号运算符
- 等号运算符(=)判断等号两边的值、字符串或表达式是否相等,如果相等则返回1,不相等则返回0。
- 在使用等号运算符时,遵循如下规则:
- 如果等号两边的值,字符串或表达式都为字符串,则MySQL会按照字符串进行比较,其比较的是每个字符串中字符的ANSI编码是否相等。
- 如果等号两边的值都是函数,则MySQL会按照整数来比较两个值的大小。
- 如果等号两边的值一个是整数,另一个是字符串,则MySQL会将字符串转化为数字进行比较。
- 如果等号两边值,字符串或表达式中有一个为null,则比较结果为null
安全等于
安全等于运算符(<=>) 与等于运算符(=)的作用是相识的,唯一的区别是:<=>可以对NULL进行判断。在两个操作数均为NULL时,其返回值为1,而不为NULL;当一个操作数为NULL时,其返回值为0,不为NULL。
其他运算符
IS NULL / IS NOT NULL / ISNULL( )
一组关于是否为空的过滤条件
#IS NULL 过滤为空
SELECT *
FROM t_test
WHERE salary IS NULL; #返回一条记录
#IS NOT NULL 过滤不为空
SELECT *
FROM t_test
WHERE salary IS NOT NULL;#返回除了上面那条记录
#ISNULL(expr)过滤为空
SELECT *
FROM t_test
WHERE ISNULL(salary);#返回一条记录
LEAST / GREATEST
-- LEAST / GREATEST
SELECT LEAST('a','b','c');#找出最小 a
SELECT GREATEST('a','b','c');#找出最大 c
BETWEEN AND
SELECT `name`,salary
FROM emp
WHERE salary BETWEEN 6000 AND 8000;
#过滤6000<=salary<=8000
#需要注意的是:写法范围:左边小,右边大,不然报错。
例如错误写法:BETWEEN 8000 AND 6000
IN /NOT IN
SELECT id,`name`,salary
FROM emp
WHERE id IN (100,101,102);
#确定id的选项为100 101 102
LIKE 模糊查询
#需求:名称包含字符h的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE '%h%';# %符号表示任意一个字符,或多个字符
#需求:查询名称以a开头的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE 'a%';
#需求:查询名称以a结尾的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE '%a';
#需求:查询名称中包含字符a,并且包含h的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE '%a%' AND `name` LIKE '%h%';
#需求:查询名称第二个字符为a的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE '_a%';# _符号表示任意一个字符
#需求:查询名称以_开头的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE '_\_a%'; # \转义字符
关键字ESCAPE:自定义转义符
#需求:查询名称以_开头的员工id
SELECT id,`name`
FROM emp
WHERE `name` LIKE '_$_a%' ESCAPE '$'; # 自定义转义字符
逻辑运算符
逻辑运算符主要用来判断表达式的真假,在MySQL中,逻辑运算符的返回结果为1,0或者NULL。MySQL支持如下逻辑运算符:
排序和分页
排序
排序规则
使用ORDER BY子句排序
ASC(ascend)升序
DESC(descend)降序
ORDER BY子句在SELECT语句的结尾
关于别名的说明
在设置别名时,order by中可以设置,但是在where中不能设置。
一级排序和二级排序
-- 一级排序
#需求:按照工资升序排序
SELECT salary
FROM emp
ORDER BY salary ASC
#需求:按照工资降序排序
SELECT salary
FROM emp
ORDER BY salary DESC
-- 二级排序
#需求:按工资降序再按照id升序
SELECT id,salary
FROM emp
ORDER BY salary DESC,id ASC
分页 LIMIT
将查询出来的结果分页呈现。
#显示前5条数据
SELECT *
FROM emp
LIMIT 0,5;#0为偏移量 5为查询记录条数
#显示第6-10条数据
SELECT *
FROM emp
LIMIT 5,5;
MySQL8新特性 LIMIT OFFSET
#需求 查询第20-21条记录
SELECT *
FROM emp
LIMIT 2 OFFSET 19; #2为条数,19为偏移量
排序求最值
#需求:查询salary最高的记录
SELECT id,salary
FROM employees
ORDER BY salary DESC
LIMIT 0,1
#或者
-- LIMIT 1 OFFSET 0;
多表查询
多表查询,也成为关联查询,指两个或多个表一起完成查询操作。
前提条件:这些一起查询得表之间是有关系得(一对一,一对多)。
SELECT t_emp.id,t_dep.dep_name
FROM t_emp,t_dep #给两个表设置别名
#建立两个表之间的连接:两个表都有dep_id字段
WHERE t_emp.dep_id = t_dep.dep_id
#写法优化:设置别名
#需求:查询t_emp表中的id,和t_dep表中的dep_name
SELECT e.id,d.dep_name
FROM t_emp e,t_dep d #给两个表设置别名
#建立两个表之间的连接:两个表都有dep_id字段
WHERE e.dep_id = d.dep_id
#需要注意的是:设置了别名后,必须使用别名,不能使用表明
多表查询的分类
等值连接和非等值连接
两者最大区别:关系是否为等量关系,例如上述案例中关系即为等量的。
案例:
#等值连接
SELECT e.id,d.dep_name
FROM t_emp e,t_dep d
#连接条件
WHERE e.dep_id = d.dep_id
#非等值连接
SELECT e.`name`,g.grade
FROM t_emp e , t_salgrade g
#连接条件
WHERE e.salary BETWEEN g.minsalary AND g.maxsalary
自连接和非自连接
两者区别
自连接:一张表自己连接自己,也就是一张表复制一份出来,在进行连接。
非自连接:连接非自身就是非自连接。
#自连接 自我引用
#利用employees表查询employee_id 对应的manager_id
#再利用manager_id 查询出对应的manager名称
SELECT e.last_name,e.employee_id,m.employee_id,m.last_name
FROM employees e,employees m;
WHERE e.`manager_id` = m.`employee_id`;
内连接和外连接
- 内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行。以上案例均为内连接。
- 外连接:两个表在连接过程中除了返回满足连接条件以外还返回左(或右)表中不满足条件的行,没有匹配行时,结果为空(NULL)。
- 如果是左外连接,则连接条件中左边的表称为主表,右边的表称为从表。
- 如果是右外连接,则连接条件中右边的表称为主表,左边的表称为从表。
外连接的分类
左外连接,右外连接,满外连接。
左外连接:两个表在连接过程中除了返回满足连接条件的行以外,还返回左表中不满足条件的行。
右外连接:两个表在连接过程中除了返回满足连接条件的行以外,还返回右表中不满足条件的行。
满外连接:
SQL99语法
实现多表查询内连接 JOIN ON
SELECT e.id ,d.dep_name
FROM t_emp e JOIN t_dep d
#on后跟连接条件
ON e.dep_id = d.dep_id
#语法结构:
# 表1 JOIN 表2
ON 表1 表2 连接条件
JOIN 表3
ON 表3 和表1或表2的连接条件
JOIN....
ON...
左外连接LEFT-OUTE 和 右外连接RIGHT-OUTER
#左外连接
SELECT e.id ,d.dep_name
FROM t_emp e LEFT OUTER JOIN t_dep d
ON e.dep_id = d.dep_id
#右外连接
SELECT e.id ,d.dep_name
FROM t_emp e RIGHT OUTER JOIN t_dep d
ON e.dep_id = d.dep_id
(OUTER 和INNER 可以省略)
满外连接FULL-OUTER
#满外连接
SELECT e.id ,d.dep_name
FROM t_emp e FULL OUTER JOIN t_dep d
ON e.dep_id = d.dep_id
7种SQL的JOINS连接方式
如上图,我们只能得到左外,右外和满外连接,但是其他情况我们无法直接得到,但是有时候又有需求,这时候我们就需要使用关键字:UNION。
UNION和UNION ALL 关键字使用
合并查询结果
利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集,合并时,两个表对应的列数和数据类型必须相同,并且相互对应,各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
语法:
SELECT colimn... FROM table1
UNION [ 可选 ]
SELECT column... FROM table2
...
UNION和UNION ALL区别
UNION:返回两个查询结果的并集,去除重复记录。
UNION ALL:返回两个结果的并集,对于两个结果集重复部分,不去重。
使用须知:执行UNION ALL时所需要的资源比UNION少,因为UNION ALL不需要执行去重操作,如果已知合并后结果无重复数据或不需要去重的,选择UNION ALL更为合适。节约资源,提高效率。
SQL函数
单行函数/基本函数
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行交换
- 每行返回一个结果
- 可以嵌套
- 参数可以是一列或一个值
SQL中的常用单行函数
进制间转换相关的函数
操作字符串相关的函数
需要注意的是:SQL的索引是从1开始的,而并非0开始。
时间和日期相关的函数
获取日期时间
日期和时间戳的转换
获取月份,星期,星期数,天数相关的函数
时间和秒钟的函数
计算日期和时间的函数
信息函数
聚合函数
聚合函数用于一组数据,并对一组数据返回一个值。例如求最大值。
常见的聚合函数
- AVG()
- SUM()
- MAX()
- MIN()
- COUNT()
COUNT()函数
#聚合函数COUNT
SELECT COUNT(id),COUNT(salary),COUNT(salary*2),COUNT(1),COUNT(*)
FROM emp;
** 一般查询表中存在记录数量:使用count(*)
**count()函数在计数时,自动过滤空值,null不计算在内。所以使用某字段计算表中存在的记录数量是不准确的。
COUNT(*)和COUNT(列名)
- 用count(*)和count(列名)谁更好?
对于MyISAM引擎的表是没有区别的,这种引擎内部有一计数器在维护者行数。
Innodb引擎的表用count(*)直接读取行数,复杂度是O(n)因为innodb真的要去数一遍
但是好于具体的count(列名)。 - 能不能使用count(列名)替换count(*)?
不要使用count(列名)来代替count(*)是SQL92定义的标准统计行数的语法,跟数据库无关,跟NULL和非NULL无关。
说明:count(*)会统计值为NULL的行,而count(列名)不会统计此列为NULL的行。
AVG()函数
特殊情况:当使用AVG()函数计算平均值的时候,存在有null的情况:
#commission字段中存在多行为null
#错误写法:
SELECT AVG(commission_pct)
FROM emp_all; #输出 0.222857
#正确写法1:
SELECT SUM(commission_pct)/COUNT(IFNULL(commission_pct,0))
FROM emp_all #输出 0.072897
#正确写法2:
SELECT AVG(IFNULL(commission_pct,0))
FROM emp_all; #输出 0.072897
#IFNULL:条件函数 IFNULL(x,y)当x不为NULL时,输出x,否则输出y
GROUP BY使用
该函数用于分组,可以使用GROUP BY子句将表中数据分为若干组。
#需求:求各部门的平均成绩(将部门作为条件分组)
SELECT AVG(salary)
FROM emp_all
GROUP BY department_id #12条记录
GROUP BY中使用WITH ROLLUP
使用WITH ROLLUP关键字之后,在所有查询出的分组记录之后添加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量。
#需求:求各部门的平均成绩(将部门作为条件分组)
SELECT AVG(salary)
FROM emp_all
GROUP BY department_id WITH ROLLUP #13条记录
使用WITH ROLLUP时,不能同时使用ORDER BY,两者互斥。
HAVING的使用(用来过滤数据)
要求1:如果过滤条件存在聚合函数,则必须使用HAVING
要求2:HAVING要在GROUP BY 后面
开发中使用HAVING的前提是SQL中使用了GROUP BY
#需求:查询部门id为10,20,30的部门中最高工资比10000高的部门id
SELECT department_id,MAX(salary)
FROM emp_all
WHERE department_id IN (10,20,30)
GROUP BY department_id
HAVING MAX(salary) > 10000;
WHERE和HAVING对比
区别1:WHERE可以直接使用表中的字段作为筛选条件,但是不能使用分组中的计算函数作为筛选条件;HAVING必须要与GROUP BY配合使用,可以把分组计算的函数和分组字段作为筛选条件。例如上述例子:HAVING MAX(salary)> 10000 。
区别2:如果需要通过连接从关联表中获取数据,WHERE是先筛选后连接,而HAVING是先连接后筛选。则在相关查询中,WHERE更高效。
SQL执行过程
关键字声明顺序
SELECT - - FROM - - WHERE - - GROUP BY - - HAVING - - ORDER BY - - LIMIT...
SQL语句执行顺序
FROM - - WHERE - - GROUP BY - - HAVING - - SELECT - - DISTINCT - - ORDER BY - -LIMIT
SQL子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询。
SQL中子查询的使用大大增强了SELECT查询能力,因为很多时候查询需要从结果集中获取数据,或者需要从同一个表中先计算出一个结果,然后与这个数据结果进行比较。
使用要点
1.子查询的结果被主查询使用。
2.子查询要放到括号内。
3.将查询结果放到比较条件的右侧。
案例:
#1.查询和员工Zlotkey相同部门的员工姓名和工资
#方式1:子查询
SELECT last_name,salary,department_id
FROM employees
WHERE department_id =(SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
);
子查询的分类
分类方式1:
将查询的结果返回一条还是多条记录作为区分,分为:单行子查询,多行子查询。
分类方式2:
按照子查询是否被执行多次,将子查询分为相关子查询和不相关子查询。
单行子查询
单行操作符
**在内查询出现空值的情况下,不会报错。
多行子查询
多行比较操作符
利用子查询解决聚合函数不能嵌套的问题
#需求:查询平均工资最低的部门id
#错误写法:聚合函数不可嵌套
SELECT MIN(AVG(salary))
#正确写法:将查询的结果作为一张表
SELECT MIN(avg_salary)
FROM (
SELECT AVG(salary) as avg_salary
FROM emp_all
GROUP BY department_id
)t_empall_avgsalary #为表设置名字
多行子查询例子
#需求:查询平均工资最低的部门信息
#方式1:单行子查询
SELECT d.department_id,d.department_name
FROM departments d JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id
HAVING AVG(salary) = (SELECT MIN(avg_salary)
FROM(
SELECT AVG(salary) avg_salary
FROM employees
GROUP BY department_id
)t_avg_salary);
#方式2:多行子查询
SELECT d.department_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary) #查询出来的结果不为单个
FROM employees
GROUP BY department_id
);