查询DQL——函数

这篇文章的讲解我是按照下面这个sql脚本进行的

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `Englishname` varchar(20) DEFAULT NULL COMMENT '英文名',
  `grade` smallint(6) DEFAULT NULL COMMENT '成绩',
  `sex` varchar(20) DEFAULT NULL COMMENT '性别',
  `age` tinyint(4) DEFAULT NULL COMMENT '年龄',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6906 DEFAULT CHARSET=utf8mb4;

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
INSERT INTO `students` VALUES ('1', '张三', 'zs', '95', 'male', '18', '北京');
INSERT INTO `students` VALUES ('6', '赵六', 'zl', '96', 'fmale', '18', '北京');
INSERT INTO `students` VALUES ('7', '钱七', 'qi', '85', 'male', '18', '上海');
INSERT INTO `students` VALUES ('8', '孙八', 'sb', '89', 'male', '18', '北京');
INSERT INTO `students` VALUES ('9', '周九', 'zj', '76', 'fmale', '18', '四川');
INSERT INTO `students` VALUES ('10', '吴十', 'ws', '98', 'male', '18', '北京');
INSERT INTO `students` VALUES ('11', '郑十一', 'zs', '65', 'male', '18', '北京');
INSERT INTO `students` VALUES ('12', '王十二', 'ws', '60', 'fmale', '18', '西安');
INSERT INTO `students` VALUES ('13', '李十三', 'ls', '55', 'fmale', '18', '北京');
INSERT INTO `students` VALUES ('14', '钱十四', 'qs', '50', 'male', '18', '重庆');
INSERT INTO `students` VALUES ('15', '孙十五', 'ss', '45', 'male', '18', '北京');
INSERT INTO `students` VALUES ('16', '周十六', 'zs', '40', 'fmale', '18', '北京');
INSERT INTO `students` VALUES ('17', '吴十七', 'ws', '35', 'male', '18', '北京');
INSERT INTO `students` VALUES ('18', '郑十八', 'zs', '30', 'male', '18', '天津');
INSERT INTO `students` VALUES ('19', '王十九', 'ws', '25', 'male', '18', '北京');
INSERT INTO `students` VALUES ('20', '李二十', 'ls', '20', 'fmale', '18', '江西');
INSERT INTO `students` VALUES ('21', '钱二十一', 'qs', '15', 'male', '18', '北京');
INSERT INTO `students` VALUES ('22', '孙二十二', 'ss', '10', 'male', '18', '河北');
INSERT INTO `students` VALUES ('23', '周二十三', 'zs', '5', 'fmale', '18', '北京');
INSERT INTO `students` VALUES ('24', '吴二十四', 'ws', '0', 'male', '18', '北京');
INSERT INTO `students` VALUES ('25', '郑二十五', 'zs', '-5', 'fmale', '18', '北京');
INSERT INTO `students` VALUES ('26', '王二十六', 'ws', '-10', 'male', '18', '湖南');
INSERT INTO `students` VALUES ('27', '李二十七', 'ls', '-15', 'male', '18', '北京');
INSERT INTO `students` VALUES ('28', '钱二十八', 'qs', '-20', 'fmale', '18', '杭州');
INSERT INTO `students` VALUES ('29', '孙二十九', 'ss', '-25', 'male', '18', '北京');

一，数据处理函数

1，字符相关

（1）转大小写

大写：upper(), ucase().

select upper(code) from conf_world_district where id<50;

小写：lower(), lcase(). 

select * from student where lower(name) like 'tom';

（2）截取字符串

查找英文名第一个字母是z的学生。

select * from students where substr(Englishname,1,1)='z';

（3）获取字符串长度

select * from students where length(Englishname)=2;

（4）获取字符个数

 select * from students where char_length(name)=4;

（5）字符串拼凑

select concat(Englishname,'abc') from students;

（6）去除字符串前后空白

 select concat('abc',trim('    a   b   c      '));

//去除后导的1
mysql> select trim(trailing'1' from 'xxxxxxxxxxxx1111111111');

//去除前导的x
mysql> select trim(leading'x' from 'xxxxxxxxxxxx1111111111');

//去除两边
mysql> select trim(both'x' from 'xxxxxxxxxxxx1111111111xxxxx');

2，数字相关

（1）随机数

//0到1的1随机数
select rand();

//拿到固定的随机数，加一个整数，相当于映射
select rand(10000);

（2）四舍五入

//保留整数
select round(3.14);
//保留1位
select round(3.14,1);

（3）舍去

//舍去->9.99
select truncate(9.9999,2);
//四舍五入->10.00
select round(9.9999,2);

（4）取整

//向上取整->4
select ceil(3.55);
//向下取整->3
select floor(3.55);

（5）空处理

MySQL中规定，有空（NULL）参加的运算结果都是空。

//如果这个值是NULL，就将这个值看作100
select ifnull(NULL,100)*10;

例：

select ifnull(grade,100) as finalgrade from students;

二，日期和时间相关的函数

1，获取当前日期和时间

（1）获取当前日期，时间

select now();
select sysdate();

（2）单独获取

//获取当前日期
select curdate();
select current_date();
select current_date;

//获取时间
select curtime();
select current_time();
select current_time;

//获取年月日，时分秒
select year(now());
select month(now());
select day(now());
select hour(now());
select minute(now());
select second(now());

2，添加时间

select date_add('2024-5-15',interval 3 day);
select date_sub('2024-5-15 15:31:30',interval 50 second);

3,日期格式化

select date_format(now(),'%Y-%m-%d %H:%m:%s');

4，字符串转日期

inser into student(name,birthday) values('xbm',str_to_date('10/1/1999','m%d%Y%'));

5，查看第几天

select dayofyear(now());
select dayofmonth(now());
select dayofweek(now());

6，时间差

select datediff('2024-10-1','2024-10-7');
select datediff('2024-10-1 10:10:10','2024-10-7 8:8:8');

三，条件函数

1，if函数

select name,id, if(grade>=85,'good','bad') as hehavior from students;

注意：if 语句可以嵌套。

2，case函数

SELECT *,  
       (CASE   
            WHEN grade >= 90 THEN grade + 10  
            WHEN grade < 80 THEN grade - 10  
            ELSE grade  
        END) AS finalgrade  
FROM students;

3，cast函数

select cast('2010-10-10 10:10:10' as time);
select cast('15.15' as signed);
select cast(123.123 as char(4));//-> 123. 

4，加密函数

select md5('xbm');

md5是一种加密算法，在数据库中用的少。

四，分组函数

1，基本函数

//找最大值
select max(grade) from student;
//找最小值
select min(grade) from student;
//找总数
select sum(grade) from student;
//有多少个
select count(grade) from student;
//找平均值
select avg(grade) from student;

注意：在 where 后面不能用上面这几个函数。因为在逻辑上有歧异。

2，分组查询

(1）group by

select address,avg(grade) from students group by address;
select address,avg(grade) from students group by address order by avg(grade);

(2)having

having 必须加在 group by 后面。

select address,avg(grade) from students group by address having address <> '杭州';
//等同于
select address,avg(grade) from students where address <> '杭州' group by address;

执行顺序： from -> group by -> where -> having -> order by

(3)组内排序

select address,group_concat(grade order by grade desc) as grades 
from students 
group by address;

五，连接查询

1，什么是连接查询

简单地说就是多个表的信息一起查。

2，内连接

（1）什么是内连接

就是交集

（2）等值连接

（inner) join 连接表；

on 接条件；

select e.ename, d.dname from emp e join dept d on e.deptno=d.deptno;

（3）非等值连接

select e.ename,e.salary,s.grade 
from emp s join salgrade s 
on e.sal between s.losal and s.hisal;

（4）自连接

select e0.ename 员工 , e1.ename 领导 
from emp e0 join emp e1 
on e0.mgr=e1.empno;

3，外连接

（1）什么是外连接

除了交集，其他不满足条件的数据也要列出来，并于NULL匹配。

（2）左连接

select e.*,d.* from dept d left outer join emp e on e.deptno = d.deptno;

（3）右连接

select e.*,d.* from dept d right outer join emp e on e.deptno = d.deptno;

4，全连接

MySQL不支持full join.

5，多张表连接

select 
    e.ename,d.dname,s.grade
from
    emp e
join 
    dept d
on 
    e.deptno=d.deptno
join 
    salgrade s
on 
    e.sal between s.losal and s.hisal;

六，子查询

注意：有些子查询方式在MySQL 8 中已经被删了，所以没有列举出来。

1，什么是子查询

select 嵌套

select ...(select)...
from...(select)...
where...(select)...

2，where后面使用子查询

select * from students where grade > (select avg(grade) from students);

3，from后面使用子查询

//将一个查询出的表当作一个临时表
...(select * from students where grede >=90) as temp...

4，select后面使用子查询

select
    e.ename ,(select d.dname from dept d where e.deptno=d.deptno as) as dname 
    from e;

5，exists, not exists

//...where exists(子查询)...;
select     
    * 
from 
    students s 
where 
    exists(select * from grade g where s.grade >= avg(g));

注意：exists 的效率比in 高。

七，union&union all

都是将两个查询结构进行合并。

union会去重，union all 不会。

select * from students where grade >90
union
select * from students where grade <60;

八，limit

//取前三条
select * from students limit 0,3;
selevt * from students order by grade limit 0,10;

//分页
//已知页码和每页显示的记录条数
select * from students limit (pageno - 1)*pagesize,pagesize;