MySQL多表查询和函数使用及其例题

最新推荐文章于 2024-05-19 03:18:57 发布

帥哥正澤爺

最新推荐文章于 2024-05-19 03:18:57 发布

阅读量240

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_44824827/article/details/132014903

版权

多表查询

笛卡尔乘积现象

表查询中的笛卡尔乘积现象：多行表在查询时，如果定义了无效连接或者漏写了连接条件，就会产生笛卡尔乘积现象，所谓的笛卡尔乘积即是每个表的每一行都和其他表的每一行组合。

###

SELECT * FROM DEPT,EMP;

等值连接查询

常是在存在主键外键关联关系的表之间的连接进行，使用"="连接相关的表
注：n个表进行等值连接查询，最少需要n-1个等值条件来约束

查询每个部门的所有员工
select d.dname,e.ename from emp e ,dept d where d.deptno = e.deptno;

自连接查询

表表查询不仅可以在多个表之间进行查询，也可以在一个表之中进行多表查询

查询当前公司员工和所属上级员工的信息
 select e1.empno as 员工编号,e1.ename as 员工姓名,e2.empno as 领导编号,e2.ename as 领导姓名 from emp as e1,emp as e2 where e1.mgr = e2.empNo;

内连接查询

内连接查询使用inner join关键字实现，inner可以省略。内连接查询时，条件用 on连接，多个条件使用()将其括起来.

查询每个部门的所有员工
select dept.name,emp.name from emp inner join dept on emp.deptno = dept.deptno;

外连接

外连接分为左外连接（left outer join）和右外连接（right outer join）其值outer可以省略。外连接查询时，条件用 on连接，多个条件使用()将其括起来.
左外连接表示以左表为主表，右外连接表示以右表为主表。查询时将主表信息在从表中进行匹配

查询每个员工所属的部门
select dept.name,emp.name from emp right join dept on emp.deptno = dept.deptno;
查询每个部门的所有员工
select dept.name,emp.name from emp left join dept on emp.deptno = dept.deptno;

子查询

单行子查询

 查询软件部门下的所有员工
 select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = '软件部门';

单行多列

查询公司中和员工相同薪水和奖金的员工

select * from emp e1 where (e1.sal,e1.comm) = (select e2.sal,e2.comm from emp e2 
where e2.ename = 'scott');

多行子查询

如果子查询返回了多行记录，则称这样的嵌套查询为多行子查询，多行子查询就需要用到多行记录的操作符

如： in , all , any(some) in 子查询中所有的记录

any 表示大于子查询中的任意一个值，即大于最小值

all 表示大于子查询中的所有值，即大于最大的值

问题

在使用了 GROUP BY 的 SQL 中， ORDER BY 可以使用聚合函数吗? 可以使用未在 GROU P BY 中出现的列吗？

order by 可以使用聚合函数，不可以使用未在group by 中出现的列

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno ORDER BY count(*) DESC, empno 

ASC; -- 可以执行吗？
答：不可以执行

可以使用子查询，有限制，mysql的功能问题
mysql不支持对同一个表查询后做修改(update、delete)操作

DELETE FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT'); 
是否可以
答：不可以，同表查询应该设置别名

子查询[应用]

单列子查询

查看 emp 表中与 SMITH 岗位相同的员工信息
 select  * from emp e where job=(select job from emp e1 where ename='smith');

单行子查询

多行子查询

查询 与 SMITH 在同一个部门且岗位相同的员工的信息
 select * from emp e where (e.deptno,e.job)=(select e1.deptno,e1.job from emp e1 where ename='smith');
查询emp表中与20部门员工岗位相同的员工信息

单行函数

注：这里的字符串是从1开始的（不是0）

练习

将 emp 表中雇员姓名首字母大写其余字母小写. 如 SCOTT => Scott

select concat( upper(substr(ename,1,1)), lower(substr(ename,2))) from emp;

给每个雇员的 JOB 前加上 KFM- 前缀
```
 select concat('kfm-',job) from emp;
```

给每个雇员的 JOB 后加上部门名称

 select concat('job-',d.dname) from emp e join dept d on d.deptno=e.deptno ;

数学函数

日期函数

--type值可以是:
MICROSECOND 微秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 周
MONTH 月
QUARTER 季度
YEAR 年
MINUTE_SECOND 分钟:秒
HOUR_SECOND 小时:分钟 : 秒
HOUR_MINUTE 小时:分钟
DAY_SECOND 天 小时:分钟:秒
DAY_MINUTE 天 小时:分钟
DAY_HOUR 天 小时
YEAR_MONTH 年-月
--可以被用在format字符串:
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天

练习

计算公司员工入职的天数

select ename, concat((datediff(now(),hiredate)),'天') as 入职天数 from emp;

计算公司员工入职的月数

 select ename, concat((TIMESTAMPDIFF(month,hiredate,now())),'月') as 入职月份 from emp;

计算公司员工入职的年份

 select ename, concat((TIMESTAMPDIFF(year,hiredate,now())),'年') as 入职月份 from emp;

入职41年以上的员工工资涨 2000

 update emp set salary=salary+2000 where TIMESTAMPDIFF(year,hiredate,now()) > 41;

显示在 2 月份入职的员工信息

 select * from emp where month(hiredate)= 2;

显示在星期三入职的员工信息

select * from emp where weekday(hiredate)= 3;

假设 SCOTT 入职时 24 岁，现在多大了？

 select ename,(TIMESTAMPDIFF(year,hiredate,now())+24) as 年龄 from emp where ename='scott';

员工试用期为 3 个月，显示他们转正日期

 select emp.*, date_add(hiredate,interval 3 month) as 转正日期 from emp;

所用到日期函数

data_add('日期',interval 日期类型) 、 timestampdiff(返回的日期类型,'日期','日期')、month('日期')、weekday('日期')

设计数据库步骤

软件开发的步骤大致上可以分为：需求分析，概要设计，详细设计，代码编写，运行测试，部署发行数据库是在代码编写前完成的数据库设计可分为这几个过程需求分析，概念模型，逻辑模型，物理模型，运行验证

创建概念模型

P74~p84

数据库设计范式

数据库的设计有五大设计范式。常用的有三大设计范式，称之为第一范式( 1NF )，第二范式( 2NF )，第三范式( 3NF )，他们是逐步为严格的，满足第二范式，就必须满足先满足第一范式。满足第三范式时就必须首先满足第二范式
第一范式(1NF)
第二范式(2NF)
第三范式(3NF)
巴斯-科德范式(BCNF)
第四范式(4NF)