Mysql进阶

必备sql语句

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

select * from info where id in (1,4,6); -- 查询id为1或4或6的数据

select * from info where id in (select id from depart); -- 查询info表中 id为在depart表中id列的数据（条件搜索只能写一列）

-- 当exists后面的条件成立就执行前面的查询，如果不成立则不查询
select * from info where exists (select * from depart where id=5)

-- 当exists后面的条件不成立就执行前面的查询，如果成立则不查询
select * from info where not exists (select * from depart where id=5)


-- 先将info中id>5的查询出来当作一个新的表T，然后再查询表T中age>10的数据
select * from (select * from info where id>5) as T where age>10;

-- 同时查两个表 条件是infro中的id为10和name中的name为jxsm
select * from infro,name where infro.id=10 and name.name="jxsm";

通配符

• 不适合大数据量的搜索

分别有:  '%' 和 '_'  其中'%'表示n个字符  '_'表示一个字符
-- 查询name表中 names列为"%张%"的数据
select * from name where names like "%张%"
-- 查询name表中 names列为"_张"的数据
select * from name where names like "_张"

映射

-- 查询info表中id和name，返回的时候表头name会被改成NM，还会新曾一列123（填充的数据也都是123）并修改表头为age
select id,name as NM,123 as age from info;


-- 查询info表中的id和name，并新曾123和在dep中id列中的最大值别名为mid列和在dep中id列中的最小值别名为nid(其中子查询只能返回一条数据)
select
	id,
	name,
	123 as num,
	(select max(id) from dep) as mid,
	(select min(id) from dep) as nid,
	age
from info;


-- 在info中查询id和name，并再新曾一行(查询depart中的title，id为info中相对应的depart_id)别名为x1
####效率比较低
select
	id,
	name,
	(select title from depart where depart.id=info.depart_id) as 	x1
from info;

case/when

select
	id,
	name,
	case depart_id when 1 then "第一" end v1,
	case depart_id when 1 then "第一" else "其他" end v2,
	case depart_id when 1 then "第一" when 2 then "第二" else "其他" end v3
from info;

-- case depart_id when 1 then "第一" end v1
-- 查询info中depart_id列，当值为1的时候返回第一否则为null别名为v1

-- case when age<18 the "少年" end v4;
-- 当age<18的时候返回少年，别名为v4

排序

select * from info order by age desc; -- 基于age进行倒叙排列大到小
select * from info order by age asc; -- 基于age进行顺序排列从小到大


select  * from info order by age asc,id desc; -- 先将结果基于age进行顺序排序，如果age相等了再基于id从大到小排列

• 优先级:

  在sql语句中，会先执行条件命令再执行排序命令

取部分数据

select * from info limit 5; -- 获取前5条数据
select * from info order by age desc limit 3; -- 先排序再获取前3条数据


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

分组

-- 更具age列分组(合并)然后查询info表中的age列 [先分组再合并]

select age from info group by age;
-- 更具age进行分组，相同组只保留id最大的哪个值

select age,max(id) from info group by age;
-- count的作用是计数有几个数据被合并到了一起

select age,count(id) from info group by age;
-- sun的作用是通过age合并起来后这几列的id之和

select age,sum(id) from info group by age;
-- avg的作用是取平均值

select age,avg(id) from info group by age;

-- having count(id)>2;的作用是筛选出分组后id一样的总数>2的数据
select age,count(id) from info group by id having count(id)>2;

连表

左右连表

.... from 主表 left outer join 从表 on 主表.x = 从表.x
.... from 从表 right outer join 主表 on 主表.x = 从表.x

-- 更具infro表中的depart_id和deaprt表中的id之间的对应关系，在info表的右边拼接一deaprt表并且只传info中的name和deaprt表中的title
select info.name,deaprt.title from info left outer join depart on infro.depart_id = deaprt.id;

• 左右连表会依主表的数据为主，若从表没有依然会展示只不过数据会填充成NOLL

• 简写：select * from info left join

内连接

-- from 表 inner join 表 on 条件
-- 互相匹配，只有关联上的数据才会显示，未关联上d
select * from info inner join depart pn info.depart = depart.title

• sql优先级

join -- 连表
on -- 连表的条件
where -- 条件
group by -- 分组
having -- 聚合条件
order by -- 排序
limit -- 取值范围

上下连表

-- 取出depart表中的id和title和info中的id,name然后上下拼接起来表中的id和title和info中的id,name然后上下拼接起来(表头为上面哪个表)(即使数据类型不一致也可以连接起来)[会自动去除重复]

select id,title from depart
union
select id,name from info;

-- union all 进行去重
select id,title from depart
union all
select id,name from info;

表关系

外键约束

• 在创建表的时候写（被约束的是主表）

create table info(
    id int not null auto_increment primary key,
    constraint fk_主表名_关联表 foreign key (当前info表中的列名) references 关联表名(关联表中的列名)
)

-- 例子 此时info表中的depart_id的范围就是depart表中id列中存在的数据
create table info(
    id int not null auto_increment primary key,
    constraint fk_info_depart foreign key (depart_id) references depart(id)
)

• 后面再添加（被约束的是主表）

  alter table 表名 add constraint fk_主表名_关联表 foreign key 主表名(列名) references 副表名(列明)

• 删除外键

alter table 主表名 drop foreign key 外键名;