数据库操作003-优快云博客

本文链接：https://blog.youkuaiyun.com/Tlwhisper/article/details/114093624

1、排序查询

order by
升序asc 降序desc

mysql> select * from score order by scores desc;
+------+-----------+--------+
| id   | name      | scores |
+------+-----------+--------+
|    1 | yuwen     |     80 |
|    8 | yuwen     |     80 |
|    9 | yuwen     |     80 |
|    2 | shuxue    |     60 |
|    3 | yingyu    |     60 |
|    4 | gym       |     60 |
|    5 | zheng_zhi |     60 |
|    6 | lishi     |     60 |
|    7 | dili      |     60 |
|   10 | huaxue    |   NULL |
+------+-----------+--------+

按表达式排序
按表达式的别名排序

mysql> select *
    -> ,scores*12*2 年薪
    -> from score
    -> order by 年薪 desc;
+------+-----------+--------+--------+
| id   | name      | scores | 年薪   |
+------+-----------+--------+--------+
|    1 | yuwen     |     80 |   1920 |
|    8 | yuwen     |     80 |   1920 |
|    9 | yuwen     |     80 |   1920 |
|    2 | shuxue    |     60 |   1440 |
|    3 | yingyu    |     60 |   1440 |
|    4 | gym       |     60 |   1440 |
|    5 | zheng_zhi |     60 |   1440 |
|    6 | lishi     |     60 |   1440 |
|    7 | dili      |     60 |   1440 |
|   10 | huaxue    |   NULL |   NULL |
+------+-----------+--------+--------+

按函数排序

mysql> select *
    -> from score
    -> order by length(name);
+------+-----------+--------+
| id   | name      | scores |
+------+-----------+--------+
|    4 | gym       |     60 |
|    7 | dili      |     60 |
|    1 | yuwen     |     80 |
|    6 | lishi     |     60 |
|    8 | yuwen     |     80 |
|    9 | yuwen     |     80 |
|    2 | shuxue    |     60 |
|    3 | yingyu    |     60 |
|   10 | huaxue    |   NULL |
|    5 | zheng_zhi |     60 |
+------+-----------+--------+

双条件排序
先按scores升序排序，再按id降序排序，

mysql> select * from score order by scores asc,id desc;
+------+-----------+--------+
| id   | name      | scores |
+------+-----------+--------+
|   10 | huaxue    |   NULL |
|    7 | dili      |     60 |
|    6 | lishi     |     60 |
|    5 | zheng_zhi |     60 |
|    4 | gym       |     60 |
|    3 | yingyu    |     60 |
|    2 | shuxue    |     60 |
|    9 | yuwen     |     80 |
|    8 | yuwen     |     80 |
|    1 | yuwen     |     80 |
+------+-----------+--------+

2、单行函数

select 函数名【实参】【from表】
1、单行函数：
isnull()、concat()拼接字符、length()字节
字符函数
数学函数
日期函数
其他函数
流程控制函数
2、聚合函数、统计用的，组函数、分组函数
字符函数：
查看编码类型

mysql> show variables like '%char%';

utf-8一个汉字3个字节
jdk一个汉字两个字节
select upper(“haha”);
select lower(“hehehe”);

字符串截取
substr,substring

mysql> select substr("扬言梦中国北京紫禁城",6);
+--------------------------------------------+
| substr("扬言梦中国北京紫禁城",6)           |
+--------------------------------------------+
| 北京紫禁城                                 |
+--------------------------------------------+

如果后面接2个参数，则是截取多少个字符长度，而不是下标！

把name的首字母变成大写，剩下的边成小写。

mysql> select concat(upper(substr(name,1,1)),"_",
    -> lower(substr(name,2)))
    -> from score;

查找子串在母串中的第一个出现的位置
没找到就返回0

mysql> select instr("杨不悔爱上了一个殷梨亭","殷梨亭") as out_put;
+---------+
| out_put |
+---------+
|       9 |
+---------+

mysql> select trim('a' from 'aaaa张翠山aaaa')as put_put;
+-----------+
| put_put   |
+-----------+
| 张翠山    |
+-----------+

左填充
右填充

mysql> select lpad('殷素素',12,'a') as out_put;
+--------------------+
| out_put            |
+--------------------+
| aaaaaaaaa殷素素    |
+--------------------+
# 右边
mysql> select rpad('张翠山',12,'b') as out_put;
+--------------------+
| out_put            |
+--------------------+
| 张翠山bbbbbbbbb    |
+--------------------+

替换：

mysql> select replace ('张无忌爱上周芷若','周芷若','赵敏') as out_put;
+-----------------------+
| out_put               |
+-----------------------+
| 张无忌爱上赵敏        |
+-----------------------+

2.2数学函数

四舍五入

mysql> select round(-2.54);
+--------------+
| round(-2.54) |
+--------------+
|           -3 |
+--------------+

# 重载
mysql> select round(1.345,2);
+----------------+
| round(1.345,2) |
+----------------+
|           1.35 |
+----------------+

# 向上取整
select ceil(1.233);
# 向下取整
select floor(1.234);
# 截断
select truncate(1.23455,4);
# 取余
select mod(-10,-3); # 结果是：-1

取余数mod操作等价于：a-a/b*b;

2.3日期函数

mysql> select now();
mysql> select curdate();
mysql> select curtime();



mysql> select year(now());
mysql> select year('1998-12-3') 年;

mysql> select month(now());
mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| February         |
+------------------+

格式化输出

mysql> select * from employees 
    -> where hiredate=str_to_date('4-3 1992','%c-%d %Y');

把日期格式化输出

mysql> select date_format(now(),'%Y年%m月%d日') as out_put;
+-------------------+
| out_put           |
+-------------------+
| 2021年02月26日    |
+-------------------+


mysql> select last_name, date_format(hiredate,'%m月/%d日 %y年') 入职日期
    -> from employees
    -> where commission_pac is not null;

2.3其他函数

select user();
select version();
select database();

2.4流程控制函数

if,if前面的那个逗号不写就错了

mysql> select name,scores, if(scores>=80,'高分','低分') 备注 from score;
+-----------+--------+--------+
| name      | scores | 备注   |
+-----------+--------+--------+
| yuwen     |     80 | 高分   |
| shuxue    |     60 | 低分   |
| yingyu    |     60 | 低分   |
| gym       |     60 | 低分   |
| zheng_zhi |     60 | 低分   |
| lishi     |     60 | 低分   |
| dili      |     60 | 低分   |
| yuwen     |     80 | 高分   |
| yuwen     |     80 | 高分   |
| huaxue    |   NULL | 低分   |
+-----------+--------+--------+

case
when then
else
end

mysql> select name,scores 原始分数,
    -> case scores
    -> when 60 then scores * 1.2
    -> when 70 then scores * 1.3
    -> else scores
    -> end as 新分数
    -> from score;
+-----------+--------------+-----------+
| name      | 原始分数     | 新分数    |
+-----------+--------------+-----------+
| yuwen     |           80 |        80 |
| shuxue    |           60 |      72.0 |
| yingyu    |           60 |      72.0 |
| gym       |           60 |      72.0 |
| zheng_zhi |           60 |      72.0 |
| lishi     |           60 |      72.0 |
| dili      |           60 |      72.0 |
| yuwen     |           80 |        80 |
| yuwen     |           80 |        80 |
| huaxue    |           70 |      91.0 |
+-----------+--------------+-----------+

区间判断

mysql> select scores,
    -> case
    -> when scores>75 then 'A'
    -> when scores>65 then 'B'
    -> else "D"
    -> end as "分数等级" from score;
+--------+--------------+
| scores | 分数等级     |
+--------+--------------+
|     80 | A            |
|     60 | D            |
|     60 | D            |
|     60 | D            |
|     60 | D            |
|     60 | D            |
|     60 | D            |
|     80 | A            |
|     80 | A            |
|     70 | B            |
+--------+--------------+

3、多行函数

3.1基础

忽略null值

select sum( scores) from score;  # 只用在数值
select avg(scores) from score;   # 只用在数值
select max(scores) from score;
select min(scores) from score;
select count(scores) from score;

多个一起用
mysql> select round(avg(scores),2),max(scores) from score;
+----------------------+-------------+
| round(avg(scores),2) | max(scores) |
+----------------------+-------------+
|                61.91 |          80 |
+----------------------+-------------+

# 结合去重函数使用
mysql> select sum(scores),sum(distinct(scores))
    -> from score;
+-------------+-----------------------+
| sum(scores) | sum(distinct(scores)) |
+-------------+-----------------------+
|         681 |                   221 |
+-------------+-----------------------+

统计

mysql> select count(*) from score;  # 效率高
mysql> select count(1) from score;#后来innodb存储引擎
mysql> select count(scores) from score; # 最慢，因为还要判断null

和分组查询一起查询的字段是可以group by后面的字段
返回两个日期相差的天数

mysql> select datediff('2019-9-29','2019-10-1');
+-----------------------------------+
| datediff('2019-9-29','2019-10-1') |
+-----------------------------------+
|                                -2 |
+-----------------------------------+

4、分组查询

4.1简单的分组前查询

查询每个名字的个数：

mysql> select count(*),name
    -> from score
    -> group by name;
+----------+-----------+
| count(*) | name      |
+----------+-----------+
|        3 | yuwen     |
|        1 | shuxue    |
|        1 | yingyu    |
|        1 | gym       |
|        1 | zheng_zhi |
|        1 | lishi     |
|        1 | dili      |
|        1 | huaxue    |
|        1 | hanxu     |
+----------+-----------+

查询有奖金的每个领导手下员工的最高工资

mysql> select max(salary),manager_id
    -> where employees
    -> where commission_pct is not null
    -> group by manager_id;

4.2分组后筛选 having

查询那个部门的员工个数大于2

分析
分2步：
第1步:把每个部门的员工个数统计出来，
第2步：在刚才那个临时表中再次进行筛选

mysql> select count(*),department_id
    -> from employees
    -> group by department_id
    -> having count(*)> 2;

查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

第一步：查询每个工种有奖金的员工的最高工资
第二步：筛选>12000

select job_id,max(salary)
from employees
where commission_pct is not null
group by job_id

having max(salary)>12000;

eg2
查询领导编号>102的领导手下最低工资>5000的领导编号有哪些，和这个手下的工资
1、查询领导编号>102的领导手下的最低工资，领导编号和那个手下的工资
还可以先把这个>102这个条件在第一步时候晚点写！

select min(salary),manage_id
from employees
where manage_id>102
group by manage_id;

2、最低工资>5000

select min(salary),manage_id
from employees
where manage_id>102
group by manage_id

having min(salary)>5000;

4.3总结

分组查询的筛选条件在分组之前还是分组之后：

	数据源	位置	关键字
条件在分组之前	原始表	group by 之前	where
条件在分组之后	分组后的结果集	group by 之后	having

分组函数做条件放在group by 之后
能放在前面的条件就放在前面，比如group by 后面使用的字段，

4.4按表达式分组

按员工姓名长度分组，查询每组的员工个数，并给出>5的个数：
1、查询每个长度的员工个数
2、添加筛选条件

select count(*),length(last_name) num
from employees
group by length(last_name)

having count(*)>5;

mysql支持group by 和having 后面加别名，where后面不支持别名
而oraclegroup by 和having 后面不支持别名

4.5按多个字段分组

查询每个部门每个工种的平均工资

select avg(salary),department_id,job_id
from employees
group by department_id,job_id;

查询每个部门每个工种的平均工资,并且按工资降序排列

select avg(salary),department_id,job_id
from employees
group by department_id,job_id
order by avg(salary);

查询所有部门的编号，员工数量，平均工资，并按平均工资降序显示

select department_id,count(*),avg(salary)
from employees
group by department_id
order by avg(salary) desc;

5、连接查询的分类

内连接：
* 等值连接
* 非等值连接
* 自连接
外连接
* 左外连接
* 右外连接
* 全外连接
交叉连接
eg1
女神表

mysql> select * from beauty;
+------+-----------+------+--------------+
| id   | name      | sex  | boyfriend_id |
+------+-----------+------+--------------+
|    1 | 柳岩      | 女   |            8 |
|    2 | 苍老师    | 女   |           18 |
|    3 | 杨颖      | 女   |            3 |
|    4 | 热巴      | 女   |            2 |
|    5 | 周芷若    | 女   |            1 |
|    6 | 赵敏      | 女   |            1 |
|    7 | 小昭      | 女   |            1 |
|    8 | 王语嫣    | 女   |            4 |
|    9 | 岳灵珊    | 女   |            9 |
+------+-----------+------+--------------+

男神表

mysql> select * from boys;
+------+-----------+--------+
| id   | boyname   | usercp |
+------+-----------+--------+
|    1 | 张无忌    |    100 |
|    2 | 鹿晗晗    |    102 |
|    3 | 黄晓明    |    103 |
|    4 | 段誉      |    104 |
|    9 | 令狐冲    |    109 |
+------+-----------+--------+

两个表的等值连接

mysql> select name,boyname
    -> from boys,beauty
    -> where beauty.boyfriend_id=boys.id;
+-----------+-----------+
| name      | boyname   |
+-----------+-----------+
| 杨颖      | 黄晓明    |
| 热巴      | 鹿晗晗    |
| 周芷若    | 张无忌    |
| 赵敏      | 张无忌    |
| 小昭      | 张无忌    |
| 王语嫣    | 段誉      |
| 岳灵珊    | 令狐冲    |
+-----------+-----------+

为表取别名

mysql> select b.name,y.boyname from boys y,beauty b where b.boyfriend_id=y.id;
+-----------+-----------+
| name      | boyname   |
+-----------+-----------+
| 杨颖      | 黄晓明    |
| 热巴      | 鹿晗晗    |
| 周芷若    | 张无忌    |
| 赵敏      | 张无忌    |
| 小昭      | 张无忌    |
| 王语嫣    | 段誉      |
| 岳灵珊    | 令狐冲    |
+-----------+-----------+