MYSQL-多表查询和函数

第一题讲解

# 1. 查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。
分析:
	(分析要查的表): 
	(显示的列):
	(关联条件):
	(过滤条件):
	[分组条件]:
	[排序条件]:
	[分页条件]:
	
SELECT 
	d.deptno, dname, loc, count(empno) 
FROM dept d JOIN emp e 
ON d.deptno = e.deptno
GROUP BY d.deptno;

自连接查询

create table area(
    pid int primary key auto_increment,
    name varchar(20),
    city_id int
);

insert into area values (1,'广东省',null),(2,'江西省',null),(3,'广州市',1),(4,'深圳',1),
                        (5,'东莞',1),(6,'南昌',2),(7,'赣州',2),(8,'九江',2);

# 例如，使用自连接查询命令完成：
# （1）把区域表tb_area分别理解成两个表：省表province、城市表city；
# （2）自连接查询省份编号、省名、城市名、城市编号的展示结果；
select province.pid,province.name,city.name,city.pid from area province join area city on province.pid = city.city_id;
# （3）自连接查询省的名称为广东省的所有城市信息。
select a.pid,a.name,b.name,b.pid from area a join area b on a.pid = b.city_id where a.name = '广东省';

子查询的三种情况

1.子查询当条件(单值对比)
2.子查询当条件(多值对比)
3.子查询当临时表(出现在from的后边位置)

-- todo 子查询三种情况--------------------
# 1.子查询当条件(单值对比--where 后边)
-- 查询 工资高于平均工资的员工信息
select * from emp where sal > (select avg(sal) from emp);

# 2.子查询当条件(多值对比)
-- 查询销售部和财务部所有员工信息.
select * from emp where deptno in (select deptno from dept where dname = '销售部' or dname='财务部');
select * from emp where deptno in (select deptno from dept where dname in ('销售部','财务部'));
select e.* from emp e join dept d on e.deptno = d.deptno where dname = '销售部' or dname='财务部';-- 连接查询实现

# 3.子查询当临时表(出现在from的后边位置)
-- 查询工资高于15000元的员工信息和他的部门信息
select * from emp where sal > 15000;
select d.dname,d.loc,e.* from dept d join (select * from emp where sal > 15000) e on d.deptno = e.deptno;

union连接查询

union: 纵向拼接去重
union all:纵向拼接不去重

-- 需求:查询工资大于28000或者部门是10号部门的员工信息.
select * from emp where sal > 28000
union
select * from emp where deptno = 10;

select * from emp where sal > 28000
union all
select * from emp where deptno = 10;

-- 两个表列和类型相同但是名字不同也可以拼接.
create table teacher(
    t_id int,
    t_name varchar(20),
    t_gender varchar(20)
);

create table student(
    s_id int,
    s_name varchar(20),
    s_gender varchar(20)
);

insert into teacher values (1,'张三','男'),(2,'李四','男'),(3,'王五','男'),(4,'小美','女');
insert into student values (1,'tom','男'),(2,'jerry','男'),(3,'jack','男'),(4,'rose','女');

-- 需求 查询男性的学生和老师.
select t_id id,t_name name,t_gender gender from teacher where t_gender = '男'
union
select * from student where s_gender = '男';

数学函数

-- todo mysql 数学类函数----------------
-- round -- 指定小数位(四舍五入)
select round(3.1415926); -- 3
select round(3.1415926,2); -- 3.14
select round(3.145,2); -- 3.15 -- 四舍五入

-- format 格式化数字展示便于阅读,指定小数位(四舍五入)
select format(12345.1415926,3);

-- floor -- 舍弃小数位-- 向下取整
select floor(12345.141592);-- 12345
select floor(12345.541592);-- 12345
select floor(12345.941592);-- 12345
-- ceil -- 向上取整
select ceil(12345.141592);-- 12346
select ceil(12345.541592);-- 12346
select ceil(12345.941592);-- 12346

-- mod 模运算 -- (求余数)
select mod(5,3); -- 2

-- pow(x,y) x的y 次方
select pow(2,3); -- 8

-- rand() 随机数函数
select rand();-- 0-1之间的随机数 -- 每次都是变化的.
select rand(10);-- 根据种子生成随机数.每次都是固定的.

字符串函数

-- todo 字符串相关的函数----------------------------
select upper('hello');
select lower('HELLO');
select s_id,upper(student.s_name),s_gender from student;
-- 第一个参数是要操作的字符串(列),要替换的字符,提换成xx字符.
select replace('黑马程序员','黑马','白马');

-- 把所有参数(n个)拼接成为一个大的字符串.不可拼接null
select concat('a','黑','b','白',100,false,true);

-- 按照指定字符把多个参数进行拼接.最后成为一大的字符串
select concat_ws('_','a','b','中文');-- 拼接成为二维数据--csv格式的数据.

-- 重复字符串拼接--指定次数
select repeat('我错了',3);

-- 把字符串内容倒序输出
select reverse('abc');
select reverse('你好吗');

-- substr 按照位置截取指定个数的字符串
select substr('hello',2);-- ello
select substr('hello',1,1);-- h
select substr('hello',-2,2);-- lo
select substr('hello',-5,5);-- hello

select left('hello',3);-- 从左侧截取制定个数的字符串
select right('hello',3);-- 从右侧截取制定个数的字符串

select char_length('abc');-- 求字符串长度
select char_length('你好');
select length('abc');-- 推荐这个 --求字符串长度

-- 需求 : 把学生的名字首字母转大写其它不变.
select * from student;
select concat(upper(substr(student.s_name,1,1)),substr(s_name,2)) from student;

时间日期函数

-- todo 时间日期相关函数----------------------
select now();-- 2024-09-25 15:56:31
select current_date();-- 2024-09-25
select current_time();-- 15:57:15

-- 第一个时间要大于第二个时间.求的是第一参减去第二参的时间差(单位是天)
select datediff('2023-09-18','2022-09-10');-- 8

-- 加时间
select date_add(now(),INTERVAL 1 DAY);
select date_add(now(),INTERVAL -1 DAY);-- 加负数就是减
select date_add(now(),INTERVAL 10 YEAR);

-- 时间减法
select date_sub('2000-10-11',interval 1 day);-- 2000-10-10
select date_sub(20001010,interval 1 day);-- 2000-10-09

-- 把第二个时间添加到第一个时间上并返回.
select timestamp(now(),'10:10:10');

select YEAR(now());-- 单独获取年
select MONTH(now());-- 单独获取月
select day(now());-- 单独获取日
select hour(now());-- 单独获取时

-- 使用格式化指定时间格式
select date_format(now(),'%Y年%m月%d日 %H:%i:%s');-- 2024年09月25日 16:16:35
select date_format(now(),'%Y-%m-%d %H:%i:%s');-- 2024-09-25 16:16:30
select date_format(now(),'%Y/%m/%d %H:%i:%s');-- 2024/09/25 16:16:24

-- 把年月日变成秒值.
select unix_timestamp();-- 1727252256
select unix_timestamp(now());-- 1727252256
select unix_timestamp('1970-01-01');-- 1727252256 -- 有时候数据库存储的时间就是秒值

-- 把秒值时间抓换为年月日时间
select from_unixtime(0);
select from_unixtime(1727252256);

select '2020-10-10' > '2020-10-09'; -- 底层是转秒值然后对比.

时间日期案例

-- ------sql日期案例------2020年最后一次登录------------------------------------------------------------
Create table If Not Exists Logins (user_id int, time_stamp datetime);
Truncate table Logins;
insert into Logins (user_id, time_stamp) values ('6', '2020-06-30 15:06:07');
insert into Logins (user_id, time_stamp) values ('6', '2021-04-21 14:06:06');
insert into Logins (user_id, time_stamp) values ('6', '2019-03-07 00:18:15');
insert into Logins (user_id, time_stamp) values ('8', '2020-02-01 05:10:53');
insert into Logins (user_id, time_stamp) values ('8', '2020-12-30 00:46:50');
insert into Logins (user_id, time_stamp) values ('2', '2020-01-16 02:49:50');
insert into Logins (user_id, time_stamp) values ('2', '2019-08-25 07:59:08');
insert into Logins (user_id, time_stamp) values ('14', '2019-07-14 09:00:00');
insert into Logins (user_id, time_stamp) values ('14', '2021-01-06 11:59:59');
-- select * from logins where time_stamp > '2020-01-01' and time_stamp < '2020-12-31';
select user_id,max(time_stamp) from logins where year(time_stamp) = 2020 group by user_id;