一、笛卡尔集:
1、笛卡尔集会在下面条件下产生:
– 省略连接条件
– 连接条件无效
– 所有表中的所有行互相连接
• 为了避免笛卡尔集, 可以在 WHERE 加入有 效的连接条件。
二、等值/连接连接 :
1、使用连接在多个表中查询数据 :
• 在 WHERE 子句中写入连接条件。
• 在表中有相同列时,在列名之前加上表名前缀
2、区分重复的列名:
• 在不同表中具有相同列名的列可以用表的别名
加以区分。
• 如果使用了表别名,则在select语句中需要使
用表别名代替表名
• 表别名最多支持32个字符长度,但建议越少越
好
3、表的别名:
• 使用别名可以简化查询。
• 使用表名前缀可以提高执行效率。
4、连接多个表 :
• 连接 n个表,至少需要 n-1个连接条件。 例如:连接
三个表,至少需要两个连接条件。
5、连接查询案例 :
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
分类:
按年代分类:
sql92标准:仅仅支持内连接
sql99标准【推荐】:支持内连接+外连接(左外和右外)+交叉连接
按功能分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉文件
5.1、等值连接:
① 多表等值连接的结果为多表的交集部分
②n表连接,至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用,比如排序、分组、筛选
#案例1:查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;
5.2、非等值连接:
#案例1:查询员工的工资和工资级别
SELECT salary,grade_level
FROM t_mysql_employees e,t_mysql_job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';
5.3、自连接:
#案例:查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM t_mysql_employees e,t_mysql_employees m
WHERE e.`manager_id`=m.`employee_id`;
三、join连接:
内连接 [inner] join on
外连接
左外连接 left [outer] join on
右外连接 right [outer] join on
1、使用ON 子句创建连接 :
自然连接中是以具有相同名字的列为连接条件的。
可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
ON 子句使语句具有更高的易读性。
2、join案例 :
2.1语法:
select 查询列表 from 表1 别名 【连接类型】 join 表2 别名 on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
2.2分类:
内连接(★):inner
外连接
左外(★):left 【outer】
右外(★):right 【outer】
全外:full【outer】
交叉连接:cross
2.2.1内连接
语法:
select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件;
分类:等值 非等值 自连接
特点:
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的,都是查询多表的交集
2.2.2外连接:
特点:
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的,则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接,left join左边的是主表
右外连接,right join右边的是主表
3、左外和右外交换两个表的顺序,可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
2.2.2.1左外连接:
SELECT b.*,bo.* FROM t_mysql_boys bo LEFT OUTER JOIN t_mysql_beauty b ON b.`boyfriend_id` = bo.`id' WHERE b.`id` IS NULL;
2.2.2.2右外连接:
SELECT d.*,e.employee_id FROM t_mysql_employees e RIGHT OUTER JOIN t_mysql_departments d ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL;
2.2.2.3全连接:
USE girls; SELECT b.*,bo.* FROM t_mysql_beauty b FULL OUTER JOIN t_mysql_boys bo ON b.`boyfriend_id` = bo.id;
2.2.2.4交叉连接:
SELECT b.*,bo.* FROM t_mysql_beauty b CROSS JOIN boys bo;
四、常用函数:
1、字符函数:
作用 | 函数 | 结果 |
---|---|---|
转小写 | LOWER('SQL Course') | sql course |
转大写 | UPPER('SQL Course') | SQL COURSE |
拼接 | CONCAT('Hello', 'World') | HelloWorld |
截取 | SUBSTR('HelloWorld',1,5) | Hello |
长度 | LENGTH('HelloWorld') | 10 |
字符出现索引值 | INSTR('HelloWorld', 'W') | 6 |
字符截取后半段 | TRIM('H' FROM 'HelloWorld') | elloWorld |
字符替换 | REPLACE('abcd','b','m') | amcd |
2、数字函数:
作用 | 函数 | 结果 |
---|---|---|
四舍五入 | ROUND(45.926, 2) | 45.93 |
截断 | TRUNC(45.926, 2) | 45.92 |
求余 | MOD(1600, 300) | 100 |
3、日期函数 :
作用 | 函数 | 结果 |
---|---|---|
获取当前日期 | now() | |
将日期格式的字符转换成指定格式的日期 | STR_TO_DATE('9-13-1999','%m-%d-%Y') | 1999-09-13 |
将日期转换成字符 | DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’) | 2018年06月06日 |
4、其他函数 :
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
5、其他函数 :
1.if函数: if else 的效果
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金,呵呵','有奖金,嘻嘻') 备注
FROM t_mysql_employees;
2.case函数的使用一: switch case 的效果
java中
switch(变量或表达式){
case 常量1:语句1;break;
...
default:语句n;break;
}mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
*/
*案例:查询员工的工资,要求
部门号=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 t_mysql_employees;
6、流程控制函数 :
• 在 SQL 语句中使用IF-THEN-ELSE 逻辑
• 使用方法: – CASE 表达式
五、子查询:
含义:出现在其他语句中的select语句,称为子查询或内查询外部的查询语句,称为主查询或外查询
分类:
按子查询出现的位置:
1、select后面:仅仅支持标量子查询
案例:查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM t_mysql_employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM t_mysql_departments d;
2、from后面:支持表子查询
将子查询结果充当一张表,要求必须起别名
*/
#案例:查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id
3、where或having后面:★
标量子查询(单行)
①查询Abel的工资
SELECT salary
FROM t_mysql_employees
WHERE last_name = 'Abel'
列子查询 (多行) √
案例1:返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM t_mysql_departments
WHERE location_id IN(1400,1700)
行子查询
案例:查询员工编号最小并且工资最高的员工信息
SELECT *
FROM t_mysql_employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM t_mysql_employees
);
4、exists后面(相关子查询)
表子查询
按结果集的行列数不同:标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
案例1:查询有员工的部门名
#in
SELECT department_name
FROM t_mysql_departments d
WHERE d.`department_id` IN(
SELECT department_id
FROM t_mysql_employees
)
六、分页查询:
应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求
语法:
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
offset要显示条目的起始索引(起始索引从0开始)
size 要显示的条目个数
特点:
①limit语句放在查询语句的最后
②公式
要显示的页数 page,每页的条目数sizeselect 查询列表
from 表
limit (page-1)*size,size;
案例:有奖金的员工信息,并且工资较高的前10名显示出来
SELECT
*
FROM
t_mysql_employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10 ;