MySQL进阶操作
先建两个表
表-ren
+------+--------------+-------+--------------+----------+-------+ | p_id | p_name | p_age | p_menpai | p_leader | p_sal | +------+--------------+-------+--------------+----------+-------+ | p001 | 张三丰 | 100 | 武当 | 0 | 10000 | | p003 | 岳不群 | 40 | 华山 | 0 | 6500 | | p002 | 张无忌 | 20 | 明教 | 0 | 8000 | | p004 | 东方不败 | 35 | 日月神教 | 0 | 12000 | | p005 | 令狐冲 | 21 | 华山 | p003 | 4000 | | p006 | 林平之 | 18 | 华山 | p003 | 2000 | | p007 | 左冷禅 | 50 | NULL | 0 | 10000 | | p008 | 张翠山 | 46 | 武当 | p001 | 10000 | | p009 | 张远桥 | 55 | 武当 | p001 | 6500 | +------+--------------+-------+--------------+----------+-------+
表-wei
+------+--------------+-----------+ | a_id | a_name | a_add | +------+--------------+-----------+ | a001 | 嵩山 | 河南 | | a002 | 华山 | 山西 | | a003 | 武当 | 湖北 | | a004 | 日月神教 | 黑木崖 | +------+--------------+-----------+
聚合函数-max(),min(),sum(),avg()
用于查数据 使用聚合函数查出来的结果字段名是带有聚合函数的，使用as语句可以来更名
查一个字段的最大值
-- select max(字段) from 表名 select max(p_sal) from ren -- max(p_sal) -- 12000
查一个字段的最小值
select min(字段) from 表名
查一个字段的平均值
select avg(字段) from 表名
差一个字段的和
select sum(字段) from 表名
查有一个字段有多少数据
可以通过查主键来查有多少数据，查不到null
-- select count(字段) from 表名 select count(p_menpai) from ren -- count(p_menpai) -- 8 select count(p_id) from ren -- count(p_id) -- 9
分组 - 分组后使用条件不使用where 使用having
-- select 字段1 from 表名 group by 字段一 -- 以字段一进行分组，相同值为一组，可以给某一字段去重，可以分组调用聚合函数 select avg(p_sal),p_menpai from ren group by p_menpai -- 查每组的平均工资 -- avg(p_sal) p_menpai -- 10000.0000 (null) -- 4166.6667 华山 -- 12000.0000 日月神教 -- 8000.0000 明教 -- 8833.3333 武当
group_concat()--可以查询分组后字段中的项
如有下表
mysql> select * from student; +-----+--------+----------+--------+ | sid | gender | class_id | sname | +-----+--------+----------+--------+ | 1 | 男 | 1 | 理解 | | 2 | 女 | 1 | 钢蛋 | | 3 | 男 | 1 | 张三 | | 4 | 男 | 1 | 张一 | | 5 | 女 | 1 | 张二 | | 6 | 男 | 1 | 张四 | | 7 | 女 | 2 | 铁锤 | | 8 | 男 | 2 | 李三 | | 9 | 男 | 2 | 李一 | | 10 | 女 | 2 | 李二 | | 11 | 男 | 2 | 李四 | | 12 | 女 | 3 | 如花 | | 13 | 男 | 3 | 刘三 | | 14 | 男 | 3 | 刘一 | | 15 | 女 | 3 | 刘二 | | 16 | 男 | 3 | 刘四 | +-----+--------+----------+--------+
用法
mysql> select group_concat(sname) from student group by class_id; +-------------------------------------------+ | group_concat(sname) | +-------------------------------------------+ | 理解,钢蛋,张三,张一,张二,张四 | | 铁锤,李三,李一,李二,李四 | | 如花,刘三,刘一,刘二,刘四 | +-------------------------------------------+ 3 rows in set (0.00 sec) mysql> select group_concat(sname,gender) from student group by class_id; +-------------------------------------------------------------+ | group_concat(sname,gender) | +-------------------------------------------------------------+ | 理解男,钢蛋女,张三男,张一男,张二女,张四男 | | 铁锤女,李三男,李一男,李二女,李四男 | | 如花女,刘三男,刘一男,刘二女,刘四男 | +-------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> select group_concat(sname),group_concat(gender) from student group by class_id; +-------------------------------------------+-------------------------+ | group_concat(sname) | group_concat(gender) | +-------------------------------------------+-------------------------+ | 理解,钢蛋,张三,张一,张二,张四 | 男,女,男,男,女,男 | | 铁锤,李三,李一,李二,李四 | 女,男,男,女,男 | | 如花,刘三,刘一,刘二,刘四 | 女,男,男,女,男 | +-------------------------------------------+-------------------------+
-- 删除重复内容
delete from ren where p_name in (
select * from (select p_name from ren GROUP BY p_name HAVING count(1)>1) as h) -- 根据要查删除的字段分组，加条件得到有多个项的组名，重新命名，因为不能同时查和删
-- 删除数据并且保留符合条件的
delete from ren where p_name in (
select * from (select p_name from ren GROUP BY p_name HAVING count(1)>1) as h
)
and p_id NOT IN(
select * from(select max(p_id) from ren GROUP BY p_name HAVING count(1)>1) T
) -- 前面条件是找到有重复内容的组，后面的条件找到符合条件的项
-- 代码意义为 删除p_name中重复的项，保留p_id最大的项
去重
-- select distinct 字段 from 表名 -- 可以使用多个字段，用逗号隔开，两个字段对应的值一样才会去重 select distinct p_menpai from ren -- 武当 -- 华山 -- 明教 -- 日月神教 -- (null)
分页
-- select * from 字段名 limit x,y -- x表示起始位置的索引，y表示查几条 select p_id from ren limit 2,5 -- p_id -- p002 -- p004 -- p005 -- p006 -- p007
关于limit
-- limit实际上是扫描至索引位置,查询指定条数 -- 当指定位置非常靠后时,会耗费大量的时间 -- 线上常用的方式一个是限制页数 -- 二就是在页面中记录前后记录的ID,使用where筛选出数据在使用limit
多表联合查询
多个表须有关联才能进行查询
-- select * from 表1,表2 where 表1.字段=表2.字段 and -- 一定要加条件，否则会出现笛卡尔乘积 -- 结果是符合两个表条件的表 select * from ren,wei where ren.p_menpai=wei.a_name +------+--------------+-------+--------------+----------+-------+------+--------------+-----------+ | p_id | p_name | p_age | p_menpai | p_leader | p_sal | a_id | a_name | a_add | +------+--------------+-------+--------------+----------+-------+------+--------------+-----------+ | p001 | 张三丰 | 100 | 武当 | 0 | 10000 | a003 | 武当 | 湖北 | | p003 | 岳不群 | 40 | 华山 | 0 | 6500 | a002 | 华山 | 山西 | | p004 | 东方不败 | 35 | 日月神教 | 0 | 12000 | a004 | 日月神教 | 黑木崖 | | p005 | 令狐冲 | 21 | 华山 | p003 | 4000 | a002 | 华山 | 山西 | | p006 | 林平之 | 18 | 华山 | p003 | 2000 | a002 | 华山 | 山西 | | p008 | 张翠山 | 46 | 武当 | p001 | 10000 | a003 | 武当 | 湖北 | | p009 | 张远桥 | 55 | 武当 | p001 | 6500 | a003 | 武当 | 湖北 | +------+--------------+-------+--------------+----------+-------+------+--------------+-----------+
如果是多个表进行查询，每一个表必须和其他一个或多个表有关联
左连接
select * from 表1 left join 表2 on 表1.字段=表2.字段
注意：on 表示条件 专门配置 left join 来使用
特点：左表数据全要，右表的数据与左表数据相匹配则显示，不匹配则以NULL填充
select * from ren left join wei on ren.p_menpai=wei.a_name +------+--------------+-------+--------------+----------+-------+------+--------------+-----------+ | p_id | p_name | p_age | p_menpai | p_leader | p_sal | a_id | a_name | a_add | +------+--------------+-------+--------------+----------+-------+------+--------------+-----------+ | p003 | 岳不群 | 40 | 华山 | 0 | 6500 | a002 | 华山 | 山西 | | p005 | 令狐冲 | 21 | 华山 | p003 | 4000 | a002 | 华山 | 山西 | | p006 | 林平之 | 18 | 华山 | p003 | 2000 | a002 | 华山 | 山西 | | p001 | 张三丰 | 100 | 武当 | 0 | 10000 | a003 | 武当 | 湖北 | | p008 | 张翠山 | 46 | 武当 | p001 | 10000 | a003 | 武当 | 湖北 | | p009 | 张远桥 | 55 | 武当 | p001 | 6500 | a003 | 武当 | 湖北 | | p004 | 东方不败 | 35 | 日月神教 | 0 | 12000 | a004 | 日月神教 | 黑木崖 | | p002 | 张无忌 | 20 | 明教 | 0 | 8000 | NULL | NULL | NULL | | p007 | 左冷禅 | 50 | NULL | 0 | 10000 | NULL | NULL | NULL | +------+--------------+-------+--------------+----------+-------+------+--------------+-----------+
右连接
与左连接相似
右表数据全要，左表的数据与右表数据相匹配则显示，不匹配则以NULL填充
-- select * from 表1 right join 表2 on 表1.字段=表2.字段 select * from ren RIGHT join wei on ren.p_menpai=wei.a_name +------+--------------+-------+--------------+----------+-------+------+--------------+-----------+ | p_id | p_name | p_age | p_menpai | p_leader | p_sal | a_id | a_name | a_add | +------+--------------+-------+--------------+----------+-------+------+--------------+-----------+ | p001 | 张三丰 | 100 | 武当 | 0 | 10000 | a003 | 武当 | 湖北 | | p003 | 岳不群 | 40 | 华山 | 0 | 6500 | a002 | 华山 | 山西 | | p004 | 东方不败 | 35 | 日月神教 | 0 | 12000 | a004 | 日月神教 | 黑木崖 | | p005 | 令狐冲 | 21 | 华山 | p003 | 4000 | a002 | 华山 | 山西 | | p006 | 林平之 | 18 | 华山 | p003 | 2000 | a002 | 华山 | 山西 | | p008 | 张翠山 | 46 | 武当 | p001 | 10000 | a003 | 武当 | 湖北 | | p009 | 张远桥 | 55 | 武当 | p001 | 6500 | a003 | 武当 | 湖北 | | NULL | NULL | NULL | NULL | NULL | NULL | a001 | 嵩山 | 河南 | +------+--------------+-------+--------------+----------+-------+------+--------------+-----------+
里连接 与多表联合相似
-- select * from 表1 inner join 表2 on 表1.字段=表2.字段 select * from ren inner join wei on ren.p_menpai=wei.a_name +------+--------------+-------+--------------+----------+-------+------+--------------+-----------+ | p_id | p_name | p_age | p_menpai | p_leader | p_sal | a_id | a_name | a_add | +------+--------------+-------+--------------+----------+-------+------+--------------+-----------+ | p001 | 张三丰 | 100 | 武当 | 0 | 10000 | a003 | 武当 | 湖北 | | p003 | 岳不群 | 40 | 华山 | 0 | 6500 | a002 | 华山 | 山西 | | p004 | 东方不败 | 35 | 日月神教 | 0 | 12000 | a004 | 日月神教 | 黑木崖 | | p005 | 令狐冲 | 21 | 华山 | p003 | 4000 | a002 | 华山 | 山西 | | p006 | 林平之 | 18 | 华山 | p003 | 2000 | a002 | 华山 | 山西 | | p008 | 张翠山 | 46 | 武当 | p001 | 10000 | a003 | 武当 | 湖北 | | p009 | 张远桥 | 55 | 武当 | p001 | 6500 | a003 | 武当 | 湖北 | +------+--------------+-------+--------------+----------+-------+------+--------------+-----------+
union --- unionall
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列，每条 SELECT 语句中的列的顺序必须相同，不然会混乱。
select id,name from biao1 union select id2,name2 from biao2 +----+--------+ | id | name | +----+--------+ | 1 | 向北 | | 2 | 杨枭 | | 1 | 何年 | | 2 | 何月 | +----+--------+ select id2,name2 from biao2 union select id,name from biao1; +-----+--------+ | id2 | name2 | +-----+--------+ | 1 | 何年 | | 2 | 何月 | | 1 | 向北 | | 2 | 杨枭 | +-----+--------+ select id2,name2 from biao2 union select id from biao1; -- ERROR 1222 (21000): The used SELECT statements have a different number of columns
浙公网安备 33010602011771号