一、笛卡尔集
笛卡尔集会在下面条件下产生
①省略连接条件
② 连接条件无效
③ 所有表中的所有行互相连接
④为了避免笛卡尔集, 可以在 WHERE 加入有 效的连接条件。
二、等值/连接连接
①、使用连接在多个表中查询数据
(1) 在 WHERE 子句中写入连接条件;
(2)在表中有相同列时,在列名之前加上表名前缀。
②、区分重复的列名
(1) 在不同表中具有相同列名的列可以用表的别名加以区分。
(2)如果使用了表别名,则在select语句中需要使用表别名代替表名。
(3) 表别名最多支持32个字符长度,但建议越少越好。
③、表的别名
(1)使用别名可以简化查询。
(2) 使用表名前缀可以提高执行效率。
④、连接多个表
(1) 连接 n个表,至少需要 n-1个连接条件。 例如:连接三个表,至少需要两个连接条件。
三、连表查询
★1.内连接 inner join
语法:select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件;
特点:
①添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
④ inner join 连接和sql92语法中的等值连接效果一样的,都是查询多表的交集
2.外连接
特点:
①、外连接的查询结果为主表中的所有记录如果从表中有和它匹配的,则显示匹配的值如果从表中没有和它匹配的,则显示null外连接查询结果=内连接结果+主表中有而从表没有的记录
②、左外连接,left join左边的是主表;右外连接,right join右边的是主表
③、左外和右外交换两个表的顺序,可以实现同样的效果
④、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
案例1:
二、查询哪个城市没有部门
SELECT city
FROM t_mysql_departments d
RIGHT OUTER JOIN t_mysql_locations l
ON d.`location_id`=l.`location_id`
WHERE d.`department_id` IS NULL;
★3.左外连接 left join
关键字:left join on / left outer join on
语法:SELECT 查询列表 FROM 表1 别名1 left join 表2 别名2 ON 连接条件;
含义:
left join 是left outer join的简写,它的全称是左外连接,是外连接中的一种。 左(外)连接,左表(a_table)的记录将会全部表示出来,而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
★4.右外连接 right join
关键字:right join on / right outer join on
语法:SELECT 查询列表 FROM 表1 别名1 right join 表2 别名2 ON 连接条件;
含义:
right join是right outer join的简写,它的全称是右外连接,是外连接中的一种。与左(外)连接相反,右(外)连接,左表(a_table)只会显示符合搜索条件的记录,而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
四、常用函数
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、聚合函数
1、 SUM():求和。
2、 AVG():求平均值。
3、MAX():求最大值。
4、 MIN():求最小值。
1、2、3、4、常与GROUP BY一起使用,也可单独使用)
5、 COUNT():统计记录的条数。常与GROUP BY一起使用,也可单独使用 如果有筛选的条件加关键字having
五、合并
1、UNION:将所有的查询结果合并到一起,然后去除掉相同的记录
语法:select 'a','b','c' UNION select 'a','b','c'
2、UNION ALL:将所有的查询结果合并到一起,不会去除掉相同的记录
语法:select 'a','b','c' UNION ALL select 'a','b','c'