MySQL之连表查询和常用函数

一，回顾MySQL之CRUD

博客链接：

https://blog.csdn.net/weixin_53436351/article/details/120142791

二，笛卡尔集

select  name ,boyName  from  beauty,boys;

笛卡尔集的产生条件

1，省略连接条件

2，连接条件无效

3，所有表中的所有行相互连接

笛卡尔乘积现象：表1   n行，表2   m行，查询结果为n*m行

为了避免笛卡尔集，可以在    where   后加入有效的连接条件

三，等值连接

使用连接在多个表中查询数据

1，在where子句中写入连接条件

2，在表中有相同列时，在列名之前加上表名前缀

①多表等值连接结果为多表的交集部分

②n表连接，至少需要n-1个连接条件

③多表的顺序没有要求

④一般要为表起别名

⑤可以搭配子句使用如：排序，分组，筛选

select name ,boyName from boys ,beauty ,where beauty.boyfriend_id= boys.id;

 区分重复的列名

1，在不同表中具有相同列名的列可以用表的别名加以区分

2，如果使用了表别名，则在select语句中需要使用表别名代替表名

3.表别名最多支持32个字符长度，但建议越少越好

表的别名

1，使用别名可以简化查询,提高语句的简洁度

2，使用表名前缀可以提高查询效率，区分多个重名的字段

 表的顺序可以替换：查询员工名，工种号，工种名

select e.last_name,e.job_id,j.job_title from t_mysql_jobs j,t_mysql_employees e where e.job_id=j.job_id;

可以加以筛选：查询有奖金的员工名，部门名

select last_name,separtment_name,commission_pct from t_mysql_employees e,t_mysql_departments d where e.department_id=d.department_id and e.commission_pct  is not null;

 可以加分组：

查询每个城市的部门个数

select count(*) 个数 ,city from t_mysql_departments d,t_mysql_locations l where d.location_id=l.location_id group by city; 

 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

select department_name,d.manager_id,min(salary）from t_mysql_departments d,t_mysql_employees e where d.department_id=e.department_id and commission_pctis not null group by department_name,d.manager_id;

可以加排序：查询每个工种的工种名和员工的个数，并且按员工个数降序

select job_title,count(*) from t_mysql_employees e,t_mysql_jobs j where e.job_id=j.job_id group by job_title order by count(*) desc;

连接多个表

1，连接n个表，至少需要n-1个连接条件

2，连表查询按功能分类：

内连接

等值连接   √√√

非等值连接

自连接

外连接

左外链接        √√√

右外连接        √√√

全外连接

交叉连接

四，join连接

分类：

内连接   inner  join on

外连接

左外连接   left outer  join on

右外连接  right  outer   join on

全外连接 full outer

交叉连接 cross

使用on子句创建连接：

自然连接中是以具有相同名字的列为连接条件的

可以使用on子句指定额外的连接条件

这个连接条件是与其他条件分开的

on子句使语句具有更高的可读性

select 查询列表 from 表1 别名【连接类型】join 表2 别名

on 连接条件【where筛选条件】【group by 分组】【having 筛选条件】【order by 排序列表】

特点：

①外连接的查询结果为主表中的所有记录，如果有与它匹配的，则显示匹配的值，没有匹配的则显示null ，外连接查询结果=内连接结果+主表中有而从表中没有的记录

②左外连接，left  join 左边的是主表

右外连接，  left  join 右边的是主表

③左外和右外交换两个表的顺序，可以实现同样的效果

④全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

五，常见函数

 1.日期函数

   1) NOW()：获取当前日期和时间
   2) DAY()：获取日期中的天
   3) MONTH()：获取日期中的月
   4) YEAR()：获取日期中的年
   5) 日期转换函数DATE_FORMAT()/STR_TO_DATE()

   DATE_FORMAT()：将日期转换成字符串
   STR_TO_DATE()：将字符串转换成日期

 2.字符串函数

   1) UPPER()/LOWER()：大小写互转
   2) REPLACE()：搜索并替换字符串中的子字符串
   3) SUBSTRING()：从具有特定长度的位置开始的最一个子字符串  substr
   4) TRIM()：去除前后空格
   5) LENGTH()：获取字符串长度

   3.数字函数
   1）FLOOR：向下取整
   2）CEIL： 向上取整
   3）ROUND：四舍五入

六、聚合函数(SUM/COUNT/AVG/MAX/MIN)

   1) SUM()：求和。常与GROUP BY一起使用，也可单独使用,
   2) AVG()：求平均值。常与GROUP BY一起使用，也可单独使用
   3) MAX()：求最大值。常与GROUP BY一起使用，也可单独使用
   4) MIN()：求最小值。常与GROUP BY一起使用，也可单独使用
   5) COUNT()：统计记录的条数。常与GROUP BY一起使用，也可单独使用
    如果有筛选的条件加关键字having

七、合并(union)

   1) UNION：将所有的查询结果合并到一起，然后去除掉相同的记录
   2) UNION ALL：将所有的查询结果合并到一起，不会去除掉相同的记录

       前提条件：结果集列数个数相同，列的类型还要相同或是兼容
       使用场景：在项目统计报表模块，用来合并数据