前言:给大家讲解MySQL连表查询,常用函数,聚合函数
码字不易,点个关注
转载请说明!
开发工具:MySQL
一、连表查询
1、笛卡尔集
笛卡尔集会在下面条件下产生
– 省略连接条件 – 连接条件无效 – 所有表中的所有行互相连接 • 为了避免笛卡尔集, 可以在 WHERE 加入有 效的连接条件。
2、等值/连接连接
使用连接在多个表中查询数据
• 在 WHERE 子句中写入连接条件。 • 在表中有相同列时,在列名之前加上表名前缀
区别重复的列名
• 在不同表中具有相同列名的列可以用表的别名加以区分。 • 如果使用了表别名,则在select语句中需要使用表别名代替表名 • 表别名最多支持32个字符长度,但建议越少越好
表的别名
• 使用别名可以简化查询。 • 使用表名前缀可以提高执行效率。
连接多个表
• 连接 n个表,至少需要 n-1个连接条件。 例如:连接三个表,至少需要两个连接条件。
连接查询
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询 笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行 发生原因:没有有效的连接条件 如何避免:添加有效的连接条件 分类: 按年代分类: sql92标准:仅仅支持内连接 sql99标准【推荐】:支持内连接+外连接(左外和右外)+交叉连接 按功能分类: 内连接: 等值连接 非等值连接 自连接 外连接: 左外连接 右外连接 全外连接 交叉连接
案例:
等值连接 ① 多表等值连接的结果为多表的交集部分 ② n表连接,至少需要n-1个连接条件 ③ 多表的顺序没有要求 ④ 一般需要为表起别名 ⑤ 可以搭配前面介绍的所有子句使用,比如排序、分组、筛选 SELECT * FROM beauty; SELECT * FROM boys; 案例:查询女生名和对应的男生名 SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id= boys.id; 为表起别名 ①提高语句的简洁度 ②区分多个重名的字段 注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定 案例:查询员工名、工种号、工种名 SELECT e.last_name,e.job_id,j.job_title FROM t_mysql_employees e,jobs j WHERE e.`job_id`=j.`job_id`;
3.join连接
分类
– 内连接 [inner] join on – 外连接 • 左外连接 left [outer] join on • 右外连接 right [outer] join on
使用ON 子句创建连接
• 自然连接中是以具有相同名字的列为连接条件的。 • 可以使用 ON 子句指定额外的连接条件。 • 这个连接条件是与其它条件分开的。 • ON 子句使语句具有更高的易读性。
语法
select 查询列表 from 表1 别名 【连接类型】 join 表2 别名 on 连接条件 【where 筛选条件】 【group by 分组】 【having 筛选条件】 【order by 排序列表】
分类:
内连接(★):inner 外连接 左外(★):left 【outer】 右外(★):right 【outer】 全外:full【outer】 交叉连接:cross
[内连接,左外连接,右外连接常用]
二、常见函数
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日 |
案例:
一、字符函数 1.length 获取参数值的字节个数 SELECT LENGTH('john'); SELECT LENGTH('张三丰hahaha'); 2.concat 拼接字符串 SELECT CONCAT(last_name,'_',first_name) 姓名 t_mysql_FROM employees; 3.upper、lower SELECT UPPER('john'); SELECT LOWER('joHn'); 将姓变大写,名变小写,然后拼接 SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM t_mysql_employees; 二、数字函数 round 四舍五入 SELECT ROUND(-1.55); SELECT ROUND(1.567,2); ceil 向上取整,返回>=该参数的最小整数 SELECT CEIL(-1.02); floor 向下取整,返回<=该参数的最大整数 SELECT FLOOR(-9.99); truncate 截断 SELECT TRUNCATE(1.69999,1); mod取余 SELECT MOD(10,-3); SELECT 10%3; 三、日期函数 now 返回当前系统日期+时间 SELECT NOW(); curdate 返回当前系统日期,不包含时间 SELECT CURDATE(); curtime 返回当前时间,不包含日期 SELECT CURTIME(); 可以获取指定的部分,年、月、日、小时、分钟、秒 SELECT YEAR(NOW()) 年; SELECT YEAR('1998-1-1') 年; SELECT YEAR(hiredate) 年 FROM t_mysql_employees; SELECT MONTH(NOW()) 月; SELECT MONTHNAME(NOW()) 月; str_to_date 将字符通过指定的格式转换成日期 SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put; 查询入职日期为1992--4-3的员工信息 SELECT * FROM employees WHERE hiredate = '1992-4-3'; SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y'); date_format 将日期转换成字符 SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put; 查询有奖金的员工名和入职日期(xx月/xx日 xx年) SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期 FROM t_mysql_employees WHERE commission_pct IS NOT NULL
三、聚合函数
sum 求和
avg 平均值
min 最小值
max 最大值
count(1)计数
到这里就结束了,我依旧是那个学IT的小学生
欢迎大佬指点