Mysql多表条件查询如何优化 mysql多表多条件函数查询

目录

一、统计函数

二、字符串相关函数

三、数学函数

四、日期函数

五、加密和系统函数

六、流程控制函数

七、多表查询

1.多表笛卡尔集

2.自连接

3.子查询

①子查询做条件

②子查询做临时表

4.any 和 all

5.表复制和去重

6.合并查询

7.左、右外连接

---

一、统计函数

1.count

返回查询结果一共多少行

注意：count(*) 统计满足条件的所有行数

count(列) 统计满足条件的所有行数，但是会排除为NULL的。

2.sum

sum函数返回满足where条件的行的和，一般用在数值列

-- 求某一列平均值
select sum(chinese)/count(*) from student;

3.max/min

返回最大/最小值

4.avg

返回平均值

5.group by

-- 统计每个部门的平均工资和最高工资
SELECT AVG(sal),MAX(sal),deptno from emp group by deptno;


-- 统计每个部门,每种岗位的平均工资和最高工资
SELECT AVG(sal),MAX(sal),deptno，job from emp group by deptno,job;

-- 求平均工资低于2000的部门号和他的平均工资
SELECT AVG(sal),deptno from emp group by deptno having AVG(sal) < 2000;

-- 使用别名
SELECT AVG(sal) as avg_sal,deptno from emp group by deptno having avg_sal < 2000;

二、字符串相关函数

-- concat拼接字符串
slelect concat(ename '工作是' job) from emp;

-- 转大写
select ucase(ename) from emp;

-- 转小写
select lcase(ename) from emp;

-- 从ename左边取2个字符
select left(ename,2) from emp;

-- 从ename右边取2个字符
select right(ename,2) from emp;

-- 返回长度(返回的是字节数)
select length(ename) from emp;

-- 替换
select ename,replace(job,'MANAGER','经理') from emp;

-- 截取字符串(从1位置开始)
select substring(ename,1,3) from emp;

-- 去除空格(trim:所有的，ltrim:左边的，rtrim:右边的)
select trim(ename) from emp;

练习：将ename字段首字母转为小写再输出完整名字

-- 将名字以首字母小写的方式输出
select concat(lcase(left(ename,1)),substring(ename,2)) from emp;

三、数学函数

--取绝对值，结果10
SELECT ABS(-10) from DUAL;

--十进制转二进制，结果1010
select BIN(10) from DUAL;

--向上取整，结果5
select ceiling(4.5) from dual;
--向上取整，结果-4
select ceiling(-4.5) from dual;


-- 向下取整4
select floor(4.5) from dual;
-- 向下取整-5
select floor(-4.5) from dual;

-- 进制转换，含义：8是十进制的8，转换成2进制，结果1000
select conv(8,10,2) from dual;
--进制转换，含义：16是十六进制的16，转换成2进制，结果10110
select conv(16,16,2) from dual;

-- 保留小数位数（四舍五入），结果58.14
select format(58.1356451,2) from dual;
-- 保留小数位数（四舍五入），结果58.13
select format(58.13266516,2) from dual;

-- 最小值，结果-5
select least(1,10,3,-5) from dual;

-- 求余,结果1
select mod(10,3) from dual;

-- 随机数，范围：0<= v <= 1.0
select rand() from dual;
-- 随机数，结果1~100之间
select ceiling(rand() * 100) from dual;

-- 返回一个固定的随机数，参数可以任意
select rand(0) from dual;

四、日期函数

-- 当前日期，如2022-06-13
select CURRENT_DATE from dual;

-- 当前时间22:36:44
select CURRENT_TIME from dual;

-- 当前时间戳，如2022-06-13 22:37:04
select CURRENT_TIMESTAMP from dual;

-- 返回日期部分，如2022-06-13
select date('2022-06-13 22:30:10') from dual;

-- 当前时间戳，如2022-06-13 22:37:27
select now() from dual;

-- 建表
create table msg(
	id int,
	content varchar(256),
	send_time datetime
);

insert into msg values(1,'北京新闻',CURRENT_TIMESTAMP)
insert into msg values(2,'上海新闻',CURRENT_TIMESTAMP)
insert into msg values(3,'深圳新闻',CURRENT_TIMESTAMP)

-- 显示所有新闻，发布日期只显示日期不显示时间
select date(send_time),content from msg;


-- 查询10分钟内发布的新闻
-- 方式1：DATE_ADD：加上10分钟
select * from msg where DATE_ADD(send_time,INTERVAL 10 MINUTE) >= NOW();
-- 方式2：DATE_SUB：减去10分钟
select * from msg where send_time >= DATE_SUB(now(),INTERVAL 10 MINUTE);


-- DATEDIFF:取相差多少天
-- 7984
select DATEDIFF('2011-11-11','1990-1-1') from dual;

-- 求你多大年龄了：27
select floor(DATEDIFF(now(),'1994-12-12')/365) from dual;

-- 算一下假如你能活到80岁，到现在还有多少天
select DATEDIFF(DATE_ADD('1994-12-12',INTERVAL 80 YEAR),now()) FROM DUAL;

-- 获取年月日时分秒
select YEAR(now()) from dual;
select MONTH(now()) from dual;
select DAY(now()) from dual;
select HOUR(now()) from dual;
select MINUTE(now()) from dual;
select SECOND(now()) from dual;
select MONTH('2022-06-12') from dual;

-- 返回1970-1-1到现在的秒数，结果1655135526
select UNIX_TIMESTAMP() from dual;

-- 返回1970-1-1到现在的年，结果52.4840
select UNIX_TIMESTAMP()/(365*24*60*60) from dual;

-- 把UNIX_TIMESTAMP秒数（时间戳）转换成指定格式的日期
-- 意义：在实际可开发中，可以存一个整数，然后表示时间，通过FROM_UNIXTIME转换
select FROM_UNIXTIME('1655135321','%Y-%m-%d') from dual;
select FROM_UNIXTIME('1655135321','%Y-%m-%d %H:%i:%s') from dual;

tips:

五、加密和系统函数

--加密和系统函数
--查看使用的用户名和ip
select user() from dual;

--查看数据库名
select database() from dual;

--得到一个字符串的md5值
select MD5('abcd1234') from dual;

--加密密码
select password('abcd1234') from dual;

六、流程控制函数

CREATE TABLE emp(
  id INT,
  name VARCHAR(32),
  sex CHAR(1),
  birthday DATE,
  entry_date DATETIME,
  job VARCHAR(32),
  salary DOUBLE,
  comm DOUBLE,
	resume TEXT) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
  
INSERT INTO emp VALUES(10,'红孩儿','男','2002-10-01','2002-10-01 11:11:11','放火的',3000,NULL,'家里有背景，要吃唐僧肉')
INSERT INTO emp VALUES(10,'Bob','男','2002-10-01','2002-10-01 11:11:11','CLERK',3000,NULL,'家里有背景，要吃唐僧肉')
INSERT INTO emp VALUES(10,'Smith','男','2002-10-01','2002-10-01 11:11:11','MANAGER',3000,'200.00','家里有背景，要吃唐僧肉')


select if(TRUE,'beijing','shanghai') from dual;
select IFNULL(null,'程序员飞扬') from dual;
select case
				when true then 'jack'
				when false then 'tom'
				else 'mary' end

--案例
--查询emp表，如果comm为空则显示0.0
select name,if(comm is null,0.0,comm) as comm from emp;

select name,ifnull(comm,0.0) as comm from emp;

--根据不同的工作返回不同的角色
select name,(select case
										when job='CLERK' then '职员'
										when job='MANAGER' then '领导'
										else '其他人员' end) as job from emp;

七、多表查询

1.多表笛卡尔集

2.自连接

--自连接
--查询员工姓名以及他的老板
select worker.name,boss.name from emp worker,emp boss where worker.boss=boss.id

3.子查询

①子查询做条件

--子查询
--查询和Smith同部门的员工
select * from emp where deptno = (select deptno from emp where name = 'Smith')

②子查询做临时表

4.any 和 all

--any 和 all
--查询所有员工的信息，比10号部门所有员工工资都高的人。
select * from emp where salary > all(select salary from emp where deptno=10)


--查询所有员工的信息，比10号部门其中一个员工工资都高的人。
select * from emp where salary > any(select salary from emp where deptno=10)

5.子查询案例

CREATE TABLE dept(
	deptno INT,
	dname VARCHAR(32),
	loc VARCHAR(32)
	) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
select * from dept;

CREATE TABLE salgrade(
	grade INT,
	losal double,
	hisal double
	) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;


--查询加强
CREATE TABLE emp(
  id INT,
  name VARCHAR(32),
  sex CHAR(1),
  birthday DATE,
  entry_date DATETIME,
  job VARCHAR(32),
  salary DOUBLE,
	resume TEXT) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
  

--查询部门和工作都和Smith相同的人
select * from emp where (deptno,job) = (select deptno,job from emp where name='Smith')
--查询部门和工作都和Smith相同的人,并且不包含Smith
select * from emp where (deptno,job) = (select deptno,job from emp where name='Smith') and name !='Smith'


--子查询案例
--查询比本部门平均工资高的人
select deptno,avg(salary) from emp group by deptno;

SELECT name,salary,temp.avg_salary,emp.deptno FROM emp,( SELECT deptno, avg( salary ) AS avg_salary FROM emp GROUP BY deptno ) temp 
WHERE
	emp.deptno = temp.deptno 
	AND emp.salary > temp.avg_salary;
	
	
	--查询每个部门工资最高的人
	SELECT name,salary,emp.deptno FROM emp,( SELECT deptno, max( salary ) AS max_salary FROM emp GROUP BY deptno ) temp 
WHERE
	emp.deptno = temp.deptno 
	AND emp.salary = temp.max_salary;
	
--查询部门编号，名称，地址以及本部门总人数
select count(*),deptno from emp group by deptno

select dept.deptno,dname,loc,temp.per_num from dept,(select count(*) as per_num,deptno from emp group by deptno) temp where dept.deptno=temp.deptno

5.表复制和去重

--表复制和去重
--表复制（蠕虫复制）
insert into emp select * from emp;

--去重
create table my_tb02 like emp;
desc my_tb02;
insert into my_tb02 select * from emp;
select * from my_tb02;

--①复制一个表my_temp，②删除原表数据③将临时表去重数据插入原表
create table my_temp like my_tb02;
insert into my_temp select * from my_tb02
delete from my_tb02;
insert into my_tb02 select distinct * from my_temp

6.合并查询

--合并查询
select * from emp
--union 会去重
select name,job,salary from emp where salary>2000
union
select name,job,salary from emp where job = 'MANAGER'

--union all 不会去重
select name,job,salary from emp where salary>2000
union all

7.左、右外连接

--左右外连接
create table stu(
	id int,
	name varchar(50)
)
insert into stu value (1,'alice'),(2,'jack'),(3,'tom'),(4,'allen');

create table exam(
	id int,
	grade double
)
insert into exam value (1,65.5),(2,78.5),(3,98),(10,56);

--查出学生的成绩，没有成绩的也要显示他的信息(左连接)
select stu.id,name,grade from stu
left join exam on stu.id=exam.id

--显示所有成绩，没有匹配的人就显示空（右连接）
select stu.id,name,grade from stu
right join exam on stu.id=exam.id