MySQL_04-函数&多表查询

已于 2022-06-20 02:23:13 修改
阅读量247 收藏
点赞数
分类专栏： MySQL 文章标签： sql 数据库 database mysql
于 2022-06-14 08:03:01 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lu_xin5056/article/details/125270987
版权

目录

一、统计函数

二、字符串相关函数

三、数学函数

四、日期函数

五、加密和系统函数

六、流程控制函数

七、多表查询

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;

四、日期函数

d2c65bd6d175c7a7f2174a823839040f.png

-- 当前日期，如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.多表笛卡尔集

f2a427e36b8c9a839151edefbd171c95.png

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

程序员飞扬
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
mysql自定义函数多参数_MySQL（10）---自定义函数
weixin_39819138的博客
01-18 3494
MySQL(10)---自定义函数之前讲过存储过程，存储过程和自定义函数还是非常相似的，其它的可以认为和存储过程是一样的，比如含义，优点都可以按存储过程的优点来理解。存储过程相关博客：它们唯一不不同点在于存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新函数 ：有且仅有1 个返回，适合做处理数据后返回一个结果。一、语法1、创建函数CREATE FUNCTION 函数名...
mysql多表查询、子查询、函数详解
m0_48811221的博客
02-25 422
多表查询 交叉查询 ##########交叉查询(没用)或者笛卡尔积查询:多个表的数据的乘积 #问题:大量数据的冗余 , 在多表查询中 多表查询建立在笛卡尔积的基础上 ##语法:select * from A , B, C,D..... SELECT * FROM dept , emp; SELECT COUNT(*) FROM dept , emp; 内连接 ###########显示内连接 #语法: select * from A inner join B on 连接的条件 #
MySQL之单表查询、多表查询、常用函数
每天一个前端小知识~
09-29 507
一、单表查询 1.基本语法（关键字） select 列数据 from 表的“结果集” where 分组前条件 group by 分组 having 分组后条件 order by 排序 limit 分页 2.基础查询 多个字段的查询 select 字段名1，字段名2... from 表名； 如果查询所有字段，则可以使用*来替代字段列表。 去除重复： * distinct 起别名：
MySQL的常用函数以及多表联查
qq_44895342的博客
10-12 751
MySQL多表联查与函数
【MySQL】多表查询的使用
patrick的博客
03-25 469
1. 熟悉常见的表 DESC employees; DESC departments; DESC locations; 引入：查询一个员工(ABEL)在哪个城市工作 SELECT * FROM employees WHERE last_name = 'Abel'; SELECT * FROM departments WHERE department_id = 80; SELECT * FROM locations WHERE location_id = 2500; 但是这样进行了三次查询,会比较
MySQL查询函数---为表和字段取别名
匿名攻城狮
08-29 9370
(1)为表取别名 (2)为字段取别名 (1)为表取别名 当表名字很长或者执行一些特殊查询时，为了方便操作或者需要多次使用相同的表时，可以为表指定别名，用这个别名代替原来的名称。为表取别名的基本语法格式为： 表名 [as] 表别名 "表名"为数据库中存储的数据表的名称，“表别名为查询时指定的表的新名称,as关键字为可选参数” 【例1】为orders表取别名o，查询30001订单的下单日期，SQL...
MySQL-mysql_real_connect()函数介绍
weixin_43580319的博客
08-17 7812
头文件 #include<mysql.h> 函数原型描述:   MYSQL *mysql_real_connect (MYSQL *mysql,   const char *host,   const char *user,   const char *passwd,   const char *db,   unsigned int port,   const char *unix_socket,   unsigned long client_flag) 上面描述了五个参数的主要取值，   MY
mysql-mysql学习详记四&&mysql多表查询&&关键字执行顺序&&mysql内置函数&&其它注意事项
weixin_47055922的博客
09-09 4894
一、mysql多表查询 1、多表查询 案例：首先创建表 并插入数据 create table dept( id int primary key auto_increment, name varchar(20) ); insert into dept values(null, '财务部'); insert into dept values(null, '人事部'); insert into dept values(null, '科技部');
Mysql接口API相关函数详细使用说明——mysql_init,mysql_real_connect,mysql_query,mysql_close等相关
JMW1407的博客
07-27 7152
MySql接口API相关函数MySql接口API相关函数1、部分API函数总览1.1、部分API函数表1.2、部分MySql结构体说明1.3、API函数使用步骤2、mysql_init()——MYSQL对象初始化2.1、函数原型及参数说明2.2、简单使用3、mysql_real_connect()——数据库引擎建立连接3.1、函数原型及参数说明3.2、简单使用4、mysql_query()——查询数据库某表内容4.1、函数原型及参数说明4.2、简单使用5、mysql_store_result()——向客户端
mysql_query()函数的返回值详解
木牛的博客
10-25 6218
由上可以知道，mysql_query（）执行sql语句时，并不是什么时候都要执行释放结果集，而是当某些操作产生了结果集，才调用mysql_free_result（）释放；看手册时，一眼看上去，觉得mysql_query（）函数返回的本来就是资源类型，可是为什么现在又是bool值了呢？我在操作mysql，插入数据时，关闭资源，PHP提示了一个warning。本篇文章主要介绍mysql_query()函数的返回值详解，感兴趣的朋友参考下，希望对大家有所帮助。千万级数据并发如何处理？
浅谈mysql_query()函数的返回值问题
12-18
看手册时，一眼看上去，觉得mysql_query（）函数返回的本来就是资源类型，可是为什么现在又是bool值了呢？好吧，耐心看完手册，才发现，原理是这样的，如下图片： 总结：由上可以知道，mysql_query（）执行sql语句...
Mysql函数手册.rar_MySQL函数手册_VZI_mysql 函数手册
09-15
MySQL数据库函数手册，私家珍藏，经验积累
mysql_generate_series:mysql_generate_series是PostgreSQL generate_series函数MySQL复制品
05-27
mysql_generate_series是PostgreSQL函数MySQL版本。 该版本（原始版本）经过（大量）改编，并试图简化方法调用，并在可能的情况下使MySQL版本参数遵循PostgreSQL版本。 它提供了一个单一的方法generate_series...
MySQL使用聚合函数进行单表查询
09-08
主要介绍了MySQL使用聚合函数进行单表查询，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
mysql-udf-regexp:实现REGEXP _...（）函数MySQL用户定义函数模块
05-01
该程序包将正则表达式函数用作MySQL用户定义函数（UDF）。 该软件包实现的功能是： REGEXP_LIKE(text, pattern [, mode]) REGEXP_SUBSTR(text, pattern [,position [,occurence [,mode]]]) REGEXP_INSTR?(text, ...
基础—SQL—DQL（数据查询语言）聚合函数
最新发布
m0_74363339的博客
05-30 375
这篇博客主要讲到了SQL分类中的DQL（数据查询语言）—聚合函数的讲解，聚合函数比较简单，但它主要配合着分组查询进行数据查询的操作，注意NULL的字段值不参与聚合函数的运算......
SQL Server定期收缩日志文件详细步骤——基于SQL Server 2012
lxcw_sir的博客
05-29 364
SQL Server定期收缩日志文件详细步骤——基于SQL Server 2012
PostgreSQL常用插件
PGCCC的博客
05-29 530
1.提供 PostgreSQL 实例的操作系统级别的资源使用情况，包括缓存命中率和磁盘 IO 等。通过使用这些插件，您可以大大增强 PostgreSQL 的功能，满足各种复杂的应用需求。1.提供三元组（trigram）相似度匹配和索引功能，常用于模糊搜索和文本相似度匹配。1.增加对地理空间数据的支持，使 PostgreSQL 成为一个功能强大的空间数据库。1.PostgreSQL 的过程语言，允许用户编写存储过程和触发器。1.基于逻辑复制的插件，提供更细粒度的数据复制控制和双向复制能力。
postgressql——Tuple学习（2）
Re_view的博客
05-27 280
postgressql——Tuple学习（2）
golang mysql时间戳_mysql-无效的复制时间戳
05-31
如果支持，你可以使用 UNIX_TIMESTAMP 函数将时间戳转换为可读日期格式。如果还是出现无效的复制时间戳错误，可能是你的复制配置有问题，你需要检查你的配置文件是否正确。另外，你也可以尝试使用其他的 MySQL 驱动...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程序员飞扬

赠人玫瑰，手有余香，感谢支持！

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值