小白MySQL学习-day04

最新推荐文章于 2024-10-02 23:04:10 发布

liulemon6

最新推荐文章于 2024-10-02 23:04:10 发布

阅读量107

点赞数

文章标签：学习

本文链接：https://blog.csdn.net/qq_74593128/article/details/131837371

版权

SQL语句

其中用到了聚合函数：主要有max()返回最大值,min()返回最小值,count()返回个数，sum()返回和，avg()返回平均数，聚合函数返回值的接收用having然后比大小 "having count（1）> 2"

条件：

where

算术关系：> = < !=（between and）

逻辑关系：and or （in (列表)） exists not

表示列的方法：直接id 或者加作用域？ info.id

select * from info where age > 30;

select * from info where id between 2 and 4; -- id大于等于2、且小于等于4

select * from info where name = '武沛齐' and age = 19;

select * from info where (name = '李杰' or email="pyyu@live.com") and age=49;

select * from info where id not in (1,4,6);
select * from info where id in (select id from depart);

select * from info where not exists (select * from depart where id=5);

通配符:

用于模糊搜索

主要是%和_

select * from info where email like "%@live.com"; --%可以占位多个字符

select * from info where email like "_upeiqi@live.com"; --_一个下划线占位1个字符

映射:

就是as

select id, name as NM, 123 from info;

select
   id,
   name,
   666 as num,   --别名num
   ( select max(id) from depart ) as mid, -- max/min/sum
   ( select min(id) from depart) as nid, -- max/min/sum
   age
from info;

#一般不用，效率低

select
   id,
   name,
   ( select title from depart where depart.id=info.depart_id) as x1
from info;

case..when用法

then 类似于别名 end是列的名称（别名）

select
   id,
   name,
   case depart_id when 1 then "第1部门" end v1
from info;

select
   id,
   name,
   case depart_id when 1 then "第1部门" end v1,
   case depart_id when 1 then "第1部门" else "其他" end v2,
   case depart_id when 1 then "第1部门" when 2 then "第2部门" else "其他" end v3,
   case when age<18 then "少年" end v4,
   case when age<18 then "少年" else "油腻男" end v5,
   case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;

排序：

升序asc 降序desc

order by

select * from info order by age desc; -- 倒序
select * from info order by age asc; -- 顺序

select * from info order by id desc;
select * from info order by id asc;
select * from info order by age asc,id desc; -- 优先按照age从小到大；如果age相同则按照id从大到小。

取部分：

limit （多少个）offset （起始位置）

select * from info order by id desc limit 3;                       -- 先排序，再获取前3条数据
select * from info where id > 4 order by id desc limit 3;           -- 先排序，再获取前3条数据

select * from info limit 3 offset 2;   -- 从位置2开始，向后获取前3数据

分组：

group by （条件）

select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;

select depart_id,count(id) from info group by depart_id having count(id) > 2;

select age,count(id) from info where id > 4 group by age having count(id) > 2; -- 聚合条件放在having后面

左右连表:

left/right inner/outer join...on (条件)

关键是认清主从表

外接：展现出主表的所有数据（不论左接还是右接）

主表 left outer join 从表 on 主表.x = 从表.id

在两表有未联系的数据时出现不同

select * from info left outer join depart on info.depart_id = depart.id;

select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;

内接：展现出两个表有关联的数据

主表 left inner join 从表 on 主表.x = 从表.id

联合（上下连表）:

少用

union (all)

select id,title from depart
union
select email,name from info;
-- 列数需相同

select id from depart
union
select id from info;

-- 自动去重

select id from depart
union all
select id from info;

-- 保留所有