MySQL的使用--函数的使用

不想秃头的小杨

已于 2022-05-03 20:01:06 修改

阅读量1.6k

点赞数

分类专栏： MySQL 文章标签：数据库开发 sql 数据库

于 2022-04-27 10:50:54 首次发布

本文链接：https://blog.csdn.net/Ysuhang/article/details/124444146

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

1.SQL语句的回顾

1. DML:
     -- 增加 insert --- 保证你的列名和值匹配 (个数类型)。
     insert into 表名(列名,列名....) values(值,值....)
     -- 删除
     delete from 表名 where 条件
     -- 修改
     update 表名 set 列名=值,列名=值,.... where 条件

2. DDL:
     creata database 数据库名;--- 创建数据库的命令
     use 数据库名;---- 切换数据库
     drop database 数据库名;---- 删除数据库.
     show databases;   ----显示所有的数据库名称

     create table 表名(
         列名数据类型,
         列名数据类型.....
     );
     数据类型: 数字类型(int double tintyint decimal)
              字符串类型（varchar char）
              时间类型(date, datetime)

     修改表结构:
       -- 增加一列
       alter table 表名 add column 列名数据类型；
       -- 删除一列
       alter table 表名 drop column 列名;
       -- 修改列的数据类型
       alter table 表名 modify column 列名数据类型

3. 条件查询。
   1. 判断条件 >,>=,<,<=,!=
   2. 多个条件 and or
   3. 范围查询 between and, in
   4. 去除重复 distinct
   5. 排序: order by 列名 desc; order by 列名 asc;
   6. 模糊查询: like 匹配通配符: % 统配n个字符 _:统配一个字符。



4. 分页查询。 limit (m-1)*n,n; m:表示当前页码   n:表示每页显示得条数。
5. 聚合函数:
      max(列名): 求出某列得最大值。
      min(列名): 求出某列得最小值
      sum(列名): 求出某列得和
      count(*|列名): 求出该表中得记录数.
      avg(列名): 求出某列得平均值

6. 分组查询 group by 列名,列名.. 一般和聚合函数配合使用, having 分组后得条件。

7. 再列上可以进行运算。

8. sql执行得优先级:
   from > where > group by > 聚合函数 > select > distinct > order by > limit

2.正文

1. 其他得函数
2. 表得约束
3. 连表查询。

3.其他的函数

sql也是一种编程语言，它里面也有函数，分为内置得函数和自定义得函数（高级）。
内置函数:
    1.聚合函数。
    2.字符串函数。---操作字符串
    3.时间函数--->操作得时间
    4.算术函数--->数学操作。
    5.其他函数

3.1字符串函数。 --操作字符串

常用函数:

函数	功能
CONCAT(s1, s2, ..., sn)	字符串拼接，将s1, s2, ..., sn拼接成一个字符串
LOWER(str)	将字符串全部转为小写
UPPER(str)	将字符串全部转为大写
LPAD(str, n, pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str, n, pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str, start, len)	返回从字符串str从start位置起的len个长度的字符串

3.1.1字符串拼接函数

select name from t_student;

select concat('我的名字叫:',name,",年龄:",age) as n from t_student;

3.1.2字符串转换为大写和小写

select name,upper(name),LOWER(name) from t_student;

3.1.3去除前后空格

select address,trim(address) from t_student;

3.1.4找到姓李的学生字符串的截取

select * from t_student where name like '李%';

select * from t_student where substr(name,1,1)='李'

3.2数字函数

常见函数:

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x, y)	返回x/y的模
abs(x)	求某个数得绝对值

3.2.1向上取整

select ceil(5,1) from t_student;

3.2.2 向下取整

select floor(5.9) from t_student;

3.2.3返回x/y的模

select mod(10,3) from t_student;

3.2.4 求某个数的绝对值

select abs(-5.6) from t_student;

3.2.5 -- 要求如果status为0变为1 为1变为0 update

update aaa set status=abs(status-1);

3.3时间函数

常用函数:

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1, date2)	返回起始时间date1和结束时间date2之间的天数

3.3.1CURDATE()返回当前日期

select curdate();--curdate 返回当前日期 后面可以不用跟表名

-- 也可以用再where
select * from t_emp where curdate() - entrydate=1; -- 选出入职时间等于1天的人

3.3.2CURTIME()返回当前时间

select curtime() --curtime 返回当前时间

3.3.3 NOW()返回当前日期和时间

select now() ;

3.3.4YEAR(date)获取指定date的年份

select year('2022-04-25') from t_student;


select * from t_student where year(entrydate)=2022; -- 选出入职年份在2022年的员工

3.3.5 DATE_ADD(date,INTERVAL expr type)返回一个日期/时间值加上一个时间间隔expr的时间值

select date_add('2022-04-25',interval 3 year);

3.3.6DATEDIFF(date1, date2)返回起始时间date1和结束时间date2之间的天数

select datediff('2020-05-25','2020-04-25');


-- 选出入职时间大于30天的员工
select * from tb_emp where datediff(now(),entrydate)>30;

3.4流程函数

常用函数:

函数	功能
IF(value, t, f)	如果value为true，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [ res1 ] ... ELSE [ default ] END	如果val1为true，返回res1，... 否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] ... ELSE [ default ] END	如果expr的值等于val1，返回res1，... 否则返回default默认值

3.4.1 IF(value.t.f) 如果value为true，则返回true，负责返回f

-- 如果年龄>30妇女  否则少女
select age,if(age>30,'妇女','少女') from tb_emp;

3.4.2 -- IFNULL(value1,value2) 如果value1不为空，返回value1，否则返回value2

-- ifnull 如果为null，则返回工作待定
select job,ifnull(job,'工作待定') from tb_emp;

4.约束

什么是约束? 就是在表中为某些列添加约束，使该列得值必须符合这个约束。

分类:

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束（8.0.14版本后）	保证字段值满足某一个条件	CHECK
外键约束	用来让两张图的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

约束是作用于表中==字段==上的，可以再==创建表==/==修改表==的时候添加约束。

4.1主键约束，唯一约束，非空约束，默认约束

create table t_teacher(
     id int primary key auto_increment,
		 -- 要求改列的值必须唯一且不能空 
		 
		 tname varchar(20) unique,
		 -- unique 唯一约束 表示改列的值必须唯一，但是可以运行多个null
		 
		 age int not null,
		 -- not null 非空约束 表示改列的值不能为空
		 
		 sex char(20) default '男'
		 -- default 默认约束 表示如果改列没有给定的值 则默认为男
)

insert into t_tea (id,tname,age,sex) values (null,'李四',15,'女');
insert into t_tea (id,tname,age,sex) values (null,'李四',18,'男');
-- Duplicate entry '李四' for key 'tname' 由于tname具有唯一约束 表中有名字为李四 所以不能再添加李四

insert into t_tea(id,tname,age,sex) values(null,'张三',null,'男');
-- Column 'age' cannot be null 因为age设置了非空约束所以 添加数据时年龄必须有值。

insert into t_tea(id,tname,age) values(null,'王五',20);
-- 因为sex有默认约束 所以在添加数据时可以不为sex设置值，则采用默认值。

4.2外键约束

外键约束用来让两张图的数据之间建立连接，保证数据的一致性和完整性。

-- 外键

create table t_class(
   cid int primary key auto_increment,
   cname varchar(20)
)

create table t_stu(
   sid int primary key auto_increment,
   sname varchar(20),
   classid int  -- 表示外键列
);



-- add contraint 约束得名称  foregin key 外键得列名 REFERENCES 主表名(主键)  

-- alter table + 从表名 + add contraint + 约束名随便起 + foreign key + 外键列名 + references +  主表(主键)
alter table t_stu add CONSTRAINT fk_stu_class FOREIGN key (classid) REFERENCES t_class(cid);


insert into t_class values(null,'qy666'),(null,'qy456');

insert into t_stu values(null,'张三',1);
insert into t_stu values(null,'李四',2);

insert into t_stu values(null,'王五',3); -- 因为classid=3 这个值 在我们得班级表中不存在。所以不能添加

注意: 外键列得数据类型必须和主表中主键得数据类型一致

5.多表查询

概述

项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在各种联系,基本费为三种:

一对多(多对一)

多对多

5.1连表查询

查询所有学生信息以及学生所在得班级信息。

5.2内连接

内连接演示---结果都是一样，只是语法不同。看个人习惯用哪个?
1．查询每一个员工的姓名，及关联的部门的名称〔隐式内连接实现)

2．查询每一个员工的姓名，及关联的部门的名称〔显式内连接实现)

-- 隐式查询 select 列名.... from 表1,表2 where 连表得条件。
-- 连表查询时，如果不使用连表条件则出现笛卡尔集。
-- 所谓笛卡尔集 就是A表中每一条记录关联B中中得每条记录
1．查询每一个员工的姓名，及关联的部门的名称〔隐式内连接实现)
select * from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;
 -- 如果表的名字很长 可以为表起别名
select * from tb_emp e, tb_dept d where e.dept_id=d.id;
 

-- 显示连接: A表 inner join B表 on 连表条件。 
2．查询每一个员工的姓名，及关联的部门的名称〔显式内连接实现)
select * from tb_emp  inner join tb_dept on tb_emp.dept_id=tb_dept.id;
select * from tb_emp e inner join tb_dept d on e.dept_id=d.id;

-- 上面的 inner可以省略。
select * from tb_emp e  join tb_dept d on e.dept_id=d.id;

5.3外连接

外连接演示
--1.查询emp表的所有数据, 和对应的部门信息(左外连接)

--2．查询dept表的所有数据，和对应的员工信息(右外连接)

-- 语法: select 查询列集 from A表 left join B表 on 连表条件
-- 1.查询emp表的所有数据, 和对应的部门信息(左外连接)
select * from tb_emp e left outer join tb_dept d on e.dept_id = d.id;
select * from tb_emp e left join tb_dept d on e.dept_id=d.id;

-- 2．查询dept表的所有数据，和对应的员工信息(右外连接)
select * from tb_emp e right join tb_dept d on e.dept_id=d.id;

5.4自连查询

自己和自己相连接查询。

-- 1．查询员工及其所属领导的名字。你要查询的结果再一张表中，但是还不能使用单表查询得到结果。
select a.name,b.name from tb_emp a join tb_emp b on a.managerid=b.id;
 
-- 2．查询所有员工 emp及其领导的名字emp ,如果员工没有领导，也需要查询出来
select a.name,b.name from tb_emp a left join tb_emp b on a.managerid=b.id;

6.子查询---嵌套查询

一个查询的结果 作为另一个查询的条件 或者 临时表。
-- 查询市场部的员工信息-----
-- 子查询返回的结果一列一条记录。 这个时候可以用=
select * from tb_emp where dept_id=(select id from tb_dept where name='市场部')

-- 查询市场部和研发部员工的信息。in 
 -- -- 查询市场部和研发部员工的信息。
   -- a) 查询市场部和研发部的编号
	 select id from tb_dept where name in('市场部','研发部')

   -- b) 再员工表中根据部门编号查询员工信息
	 select * from tb_emp where dept_id in (select id from tb_dept where name in('市场部','研发部'))


-- 查询在“方东白”入职之后的员工信息
select * from tb_emp where entrydate>(select entrydate from tb_emp where name='方东白')

-- -- 查询比财务部所有人工资都高的员工信息。
  a)求出财务部中最高的工资。
	select max(salary) from tb_emp e join tb_dept d on e.dept_id=d.id where d.name='财务部'
	
	b) 根据财务部最高工资查询其他员工信息
	select * from tb_emp where salary>(	select max(salary) from tb_emp e join tb_dept d on e.dept_id=d.id where d.name='财务部')

7.组合查询

多个查询的结果组合到一起。
sql union sql --->把这两条sql查询的结果组合到一起。如果有重复记录则合并成一条。
sql union all sql--->把这两条sql查询的结果组合到一起。如果有重复记录，不合并。

注意: 这两条sql返回的字段必须一样。
多个查询的结果 组合到一起。
sql union sql --->把这两条sql查询的结果组合到一起。如果有重复记录则合并成一条。
sql union all sql--->把这两条sql查询的结果组合到一起。如果有重复记录，不合并。 

注意: 这两条sql返回的字段必须一样。

	
	select name from tb_emp where salary>8000
	UNION 
	select * from tb_emp where age>40;