MySQL高级
order by 对查询结果继续排序
对结果进行排序
order by【查询的结果进行排序】
asc： 升序【默认可以不写】
desc： 降序【如果使用必须书写】
格式：
select 列名 from 表名 where 条件 order by 列名
当前语法的SQL语句执行顺序
1. from --》 2.where --》 3. select --》 4. order by
*/
#查询：商品ID，商品名称，分类编号，零售价格并按照零售价格进行升序和降序【单列的排序】
#升序 【可有在列名的后面添加 ASC 或不写 默认都是升序】
SELECT id,productName,dir_id,salePrice from product ORDER BY salePrice ;
#降序
SELECT id,productName,dir_id,salePrice from product ORDER BY salePrice DESC;
#支持别名
SELECT id,productName,dir_id,salePrice as sp from product ORDER BY sp DESC;
#查询：商品ID，商品名称，分类编号，零售价格，先按照编号降序，然后按照零售价格升序
SELECT id,productName,dir_id,salePrice from product ORDER BY dir_id desc,salePrice asc;
#配合限制条件，限制条件的结果进行排序
#查询分类编号为2的批发价格进行排序【降序】
select id,productName,dir_id,salePrice * cutoff as 批发价格 from product where dir_id = 2 ORDER BY 批发价格 desc;
limit关键字
/*
分页查询【真是含义 限制提交】
limit 关键字 作用是限制当前查询结果返回的个数
PS：因为在web阶段我们可以用limit做到页面展示数据的个数的限制已到达分页的效果
分页逻辑有两种：
1.假分页/逻辑分页/内存分页
优点:翻页数据速度快，缺点：如果数据量过大，造成内存溢出
一次性查询出所有数据结果集，然后存储在内存中【集合】，每次翻页的时候，都从内存中取出指定条数进行页面展示
2.真分页/物理分页/数据库分页【推荐】
每次翻页的时候都从数据库中截取指定条件的内容进行返回，并展示到前端页面
优点：不会出现内存溢出，缺点：慢
*/
#limit正常用法【限制页面展示提交】
select * from product LIMIT 3;
#limit 分页效果
#select 列名 from 表名 limit m,n;
#例如：查询第1条到第3条数据
select * from product LIMIT 0,3;
#当前这个语句就是说，展示的第一页中数据是 1-3条数据;
#查询到底4条到6条的数据
select * from product LIMIT 3,3;
# n 就是页面需要展示的结果的条数【展示多少条数据】
# m 起始值 相当于重 m~n之间的一个范围计算 例如 3,3 --》就像相当于从3条开始往后查找3个数据
select * from product LIMIT 6,3;
#如果以分页来看m 会被看做时start，这个start并不是m真正值 这个值会进行和一个计算statr相当于是页码
# m值 = （start - 1）*pageSize
#n值会看做时pageSize
MySQL函数
时间函数
语法： select 时间函数(参数)
返回一个虚拟表，这个表中存在函数计算结果【一行一列】
| 时间函数 | 描述 |
|---|---|
| SYSDATE() | 当前系统时间（日、月、年、时、分、秒） |
| CURDATE() | 获取当前日期 |
| CURTIME() | 获取当前时间 |
| WEEK(DATE) | 获取指定日期为一年中的第几周 |
| YEAR(DATE) | 获取指定日期的年份 |
| HOUR(TIME) | 获取指定时间的小时值 |
| MINUTE(TIME) | 获取时间的分钟值 |
| DATEDIFF(DATE1,DATE2) | 获取DATE1 和 DATE2 之间相隔的天数 |
| ADDDATE(DATE,N) | 计算DATE 加上 N 天后的日期 |
| now() | 获取当前时间(date+time) |
时间函数的应用
#1.获取当前时间得到的是【日期+时间】2020-08-04 15:07:42
SELECT NOW();
#2.时间值是可以做计算的 得到的结果是 20200804150621
SELECT NOW()+6;
#3.精确时间小数2020-08-04 15:07:19.534378
SELECT NOW(6);
#4.和当前now类似的时间函数 sysdate --> 2020-08-04 15:08:32
SELECT SYSDATE();
#出现的问题时间的变化
SELECT NOW(),SLEEP(3),NOW();
SELECT SYSDATE(),SLEEP(3),SYSDATE();
#实际使用中我们多使用NOW(),now在执行开始时候就得到值了，sysdate在函数执行时
#5.curdate 获得当前日期2020-08-04
SELECT curdate();
#6.curtime 获取当前时间15:16:09
SELECT CURTIME();
#获取当前时间 ：周数【一年的第几周】、 年、小时、分钟
SELECT WEEK(NOW());
SELECT YEAR(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
#计算两个时间值的相隔天数 【前大，后小】得到是正数 【前小，后大】得到是负数
SELECT DATEDIFF(CURDATE(),CURDATE()+3);
#在当前日期上加上N天后的日期
SELECT ADDDATE(NOW(),3);
#这里是为了演示结果，所以使用select，实际在查询中可以使用函数进行对列值
扩展：时间函数用法
选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒（常用）
SELECT now(),date(now()); -- 日期
SELECT now(),time(now()); -- 时间
SELECT now(),year(now()); -- 年
SELECT now(),quarter(now()); -- 季度
SELECT now(),month(now()); -- 月
SELECT now(),week(now()); -- 周
SELECT now(),day(now()); -- 日
SELECT now(),hour(now()); -- 小时
SELECT now(),minute(now()); -- 分钟
SELECT now(),second(now()); -- 秒
SELECT now(),microsecond(now()); -- 微秒
EXTRACT(unit FROM date)：从日期中抽取出某个单独的部分或组合
SELECT now(),extract(YEAR FROM now()); -- 年
SELECT now(),extract(QUARTER FROM now()); -- 季度
SELECT now(),extract(MONTH FROM now()); -- 月
SELECT now(),extract(WEEK FROM now()); -- 周
SELECT now(),extract(DAY FROM now()); -- 日
SELECT now(),extract(HOUR FROM now()); -- 小时
SELECT now(),extract(MINUTE FROM now()); -- 分钟
SELECT now(),extract(SECOND FROM now()); -- 秒
SELECT now(),extract(YEAR_MONTH FROM now()); -- 年月
SELECT now(),extract(HOUR_MINUTE FROM now()); -- 时分
分别返回日期在一周、一月、一年中是第几天
SELECT now(),dayofweek(now());
SELECT now(),dayofmonth(now());
select now(),dayofyear(now());
分别返回日期的星期和月份名称
名称是中文or英文的由系统变量lc_time_names控制(默认值是'en_US')
show variables like 'lc_time_names'; 查看系统变量
select dayname(now()),monthname(now());
set lc_time_names='zh_CN'; 可以设置修改
select dayname(now()),monthname(now());
MySQL关于根据日期查询数据的sql语句
查询在某段日期之间的数据:
select * from 数据表 where 时间字段名 BETWEEN '2016-02-01' AND '2016-02-05'
查询往前3个月的数据:【
DATE_SUB(date参数是合法的日期表达式， INTERVAL expr type
参数是您希望添加的时间间隔。
) 函数从日期减去指定的时间间隔。
INTERVAL 间隔 SECOND MINUTE HOUR DAY WEEK MONTH YEAR】
select * from 数据表 where 时间字段名 BETWEEN DATE_SUB(NOW(),INTERVAL 3 MONTH) AND NOW()
查询往前一年的数据:
select * from 数据表 where 时间字段名 BETWEEN DATE_SUB(NOW(),INTERVAL 1 YEAR) AND NOW()
查询本月的数据:【DATE_FORMAT（date 参数是合法的日期。format 规定日期/时间的输出格式。）
格式：%H(24小时)%h(12小时) %i(分钟)%s(秒)
%Y年%m月%d日
】
select * from 数据表 where DATE_FORMAT(时间字段名,'%Y-%m')=DATE_FORMAT(NOW(),'%Y-%m')
查询上月的数据:
select * from 数据表 where DATE_FORMAT(时间字段名,'%Y-%m')=DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m')
查询本周的数据:
select * from 数据表 where YEARWEEK(DATE_FORMAT(时间字段名,'%Y-%m-%d')) = YEARWEEK(NOW())
查询上周数据:
select * from 数据表 where YEARWEEK(DATE_FORMAT(CREATE_TIME,'%Y-%m-%d')) = YEARWEEK(NOW())-1
查询往前7天的数据:
select * from 数据表 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= 你要判断的时间字段名
查询往前30天的数据:
select * from 数据表 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= 你要判断的时间字段名
根据国人习惯 周一为一周的第一天习惯算一周的数据
SELECT * FROM t_browse_statistics WHERE YEARWEEK(date_format(brows_date,'%Y-%m-%d'),1) = YEARWEEK(now())+1
ORDER BY brows_date DESC
字符串函数
| 字符串函数 | 说明 |
|---|---|
| CONCAT(str1,str2,str…) | 将 多个字符串连接 |
| INSERT(str,pos,len,newStr) | 将str 中指定 pos 位置开始 len 长度的内容替换为 newStr |
| LOWER(str) | 将指定字符串转换为小写 |
| UPPER(str) | 将指定字符串转换为大写 |
| SUBSTRING(str,num,len) | 将str 字符串指定num位置开始截取 len 个内容 |
#字符串函数【在MySQL中如果要使用函数 多用于在 select语句范围内】
select concat('1900-1-1','~','2020-1-1') as 日期范围;
#字符串替换
select insert("这是一个数据库",3,2,'MySQL');
#字符串截取
select SUBSTRING('JavaMySQLOracle',5,5);
统计【聚合】函数
统计函数作用于**【一组数据】**，并对一组数据返回一个值
| 聚合函数 | 说明 |
|---|---|
| SUM() | 求所有行中单列结果的总和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| COUNT() | 求总行数 |
简单使用【多用于单纯的计算】
#查询所有商品的平均零售价格
select avg(salePrice) from product; #avg就相当于是获取了所有表中的数据的总条数，然后将所有的零售价格相加 ，然后进行相除得到结果
#统计表中有多少条记录
SELECT count(id) from product;
#思考题： 为什么这样写可以 * 和 1 的区别是什么？
SELECT count(*) from product;
select count(1) from product;
#查询商品最小零售价格，最高零售价格以及商品零售价格总和
select MIN(salePrice),MAX(salePrice),SUM(salePrice) from product;
#统计某个商品的个数 分类编号【分类编号为4的商品总数】
select count(id) from product WHERE dir_id = 4;
分组查询和Having语句
group by 关键字
需要先将emp和dept表到导入到数据库中
emp【员工表】
员工编号 员工名字 员工工作 领导编号 入职日期 薪水 奖金 部门编号
dept【部门表】
部门编号 部门名称 部门所在位置
#分组查询
/*
关键字 group by
这个语句可以根据一个或多个列对结果进行分组，在分组的列上我们可以使用聚合函数进行计算
语法：
select 列或集合函数 from 表名 where 条件 group by 要分组的类
SQL语句执行顺序：
1.from --》 where --》 group by --》 select --》 order by --》limit
ps：一般使用group by的时候都会使用聚合函数 【分组之后做统计】
*/
#查询每个部门的部门编号和每个部门的工资和
select deptno,sum(sal) from emp GROUP BY deptno;
#查询每个部门的部门编号以及每个部门的总人数
select deptno,count(*) from emp GROUP BY deptno;
#查询每个部门编号以及每个部门工资大于1500的总人数
select deptno,count(*) from emp where sal > 1500 GROUP BY deptno;
#SQL语句书写顺序 select ---> from ---> where ---> group by ---> order by ---> limit
#group by这个分组的条件限制有两种
#一种是在分组之前where子句 一种就是在分组之后 Having子句
#SQL语句执行顺序：
#1.from --》 where --》 group by --》having --》 select --》 order by --》limit
#SQL语句书写顺序
#select ---> from ---> where ---> group by --> having ---> order by ---> limit
/*
having和where子句的区别？
1.having子句是在分组之后对数据进行过滤
2.where子句是在分组之前对数据进行过滤
3.having子句的后面是可以使用聚合函数，但是where是不可以使用聚合函数
4.where子句是对分组之前数据进行条件过滤，没有满足where子句条件的语句不会参与到分组中，而having是对分组之后数据的约束
5.where子句使用场景比较访问基本上可以和任意查询语句进行搭配而having只适合使用在group by 的后面
*/
#查询工资总和大于9000的部门编号以及工资总和
select deptno, SUM(sal) from emp GROUP BY deptno HAVING SUM(sal) > 9000;
总结：
SQL语句书写
select * from 表名 where 条件 group by 分组列 having 分组之后的条件限制
order by 列的排序【asc | desc】 limit 其实位置，展示的数据条数
SQL语句的执行顺序
1.from 指定数据来源于那张表
2.where 对查询数据做第一个过滤
3.group by 对数据进行分组
4.having 对分组之后的数据进行二次过滤
5.select 查询过滤分组之后数据结果集
6.order by 对这个查询之后结果进行排序【asc 升序 | desc 降序】
7.limit 限制最终输出结果的个数
多表联合查询
1.单表查询：从一张表中查询数据
2.多表查询：从多张表中联合查存储数据
为什么要多表查询？
扫一扫
1107
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
