mysql 高阶查询

目录

基础用法

表连接

求交集

内连接

左/右连接

多表查询

子查询

分组统计

求左表无交集

求右表无交集

求两个表无交集

面试题：视图表的数据是否能修改？

无值'' ?和 空值null 的区别？

存储过程（数据库脚本）

创建存储过程：

查看存储过程

存储过程参数（只能用于字段）

存储过程控制语句

---

基础用法

select 字段 from 表 where 字段 = 值 [and|or 字段 = 值];
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?!= <> > >= < <=
?? ??? ??? ??? ??? ? ? ? ? ? ? [not] in (值1, 值2, ...);
?? ??? ??? ??? ??? ??? ? ? ? ? between 值1 and 值2;
?? ??? ??? ??? ??? ??? ? ? ? ? like '通配符表达式'; ? ? ? ? % ? ?_
?? ??? ??? ??? ??? ??? ??? ? ? regexp '正则表达式'; ? ? ? ^ ? $ ? ?. ? * ? ?{} ? ?[] ? ?|
?? ??? ??? ??? ??? ??? ??? ? ??
select 字段 from 表 order by 字段 ASC|DESC; ? ? ? ? ? ? ? ? ? ?#排序
select 字段,聚合函数(字段) from 表 group by 字段; ? ? ? ? ? ? ?#分组，通常会结合聚合函数使用
select 字段,聚合函数(字段) from 表 group by 字段 having 条件表达式; ? ? ?#having的作用：根据group by分组后的结果再进行条件过滤

数学函数 ?round(x,y) ?truncate(x,y) ?rand() ? greatest(值1, 值2, ....) ? ? least(值1, 值2, ....)
聚合函数 ?sum(字段) ? avg(字段) ? max(字段) ? min(字段) ? count(字段) ? count(*)
字符串函数 ? concat(字段1, 字段2, ....) ? ?substr(x,y,z) ? ? ? ?replace(x,y,z) ? ? length(字段)
? ? ? ? ? ? ? 字段1 || 字段2 ? ? ? ? ? ? ? substring(x,y,z)

select 字段 AS 字段别名 ?from ?表 AS 表别名;

select 字段 from 表1 where 字段 运算符 (select 字段 from 表2 where 字段);
外查询 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 内查询 ? （内查询的结果会作为外查询的条件来使用）

表连接

inner join ? ?内连接，只返回两个表的字段相等的行记录
left join ? ? 左连接，返回左表所有的行记录和右表字段相等的行记录，不相等的行返回null
right join ? ?右连接，返回右表所有的行记录和左表字段相等的行记录，不相等的行返回null
union ? ? ? ? 联集，将两个select查询语句的结果合并，并去重
union all ? ? 联集，将两个select查询语句的结果合并，不去重

full outer join ? 全外连接，返回左表和右表中所有的行记录，MySQL不支持

求交集

内连接

select A.字段 from 左表 as A inner join 右表 as B ?on ?A.字段 = B.字段;
select A.字段 from 左表 as A inner join 右表 as B ?using(同名字段);

左/右连接

select A.字段 from 左表 as A left join 右表 as B ?on ?A.字段 = B.字段 where B.字段 is not null;
select B.字段 from 左表 as A right join 右表 as B ?on ?A.字段 = B.字段 where A.字段 is not null;

多表查询

select A.字段 from 左表 A, 右表 B where A.字段 = B.字段;

子查询

select A.字段 from 左表 A where A.字段 in (select B.字段 from 右表 B);

分组统计

select A.字段,count(字段) from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) A group by A.字段 having count(字段) > 1;

求左表无交集

select A.字段 from 左表 as A left join 右表 as B ?on ?A.字段 = B.字段 where B.字段 is null;

select A.字段 from 左表 A where A.字段 not in (select B.字段 from 右表 B);

求右表无交集

select B.字段 from 左表 as A right join 右表 as B ?on ?A.字段 = B.字段 where A.字段 is null;

select B.字段 from 右表 B where B.字段 in (select A.字段 from 左表 A);

求两个表无交集

select A.字段 from 左表 as A left join 右表 as B ?on ?A.字段 = B.字段 where B.字段 is null?
union?
select B.字段 from 左表 as A right join 右表 as B ?on ?A.字段 = B.字段 where A.字段 is null;

select A.字段 from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) A group by A.字段 having count(A.字段) = 1;

create view ?视图表名 ?as select distinct 字段 from 左表 union all select distinct 字段 from 右表;
select 字段 from 视图表名 group by 字段 having count(字段) = 1;

create view ?视图表名 ?as ?select语句;

面试题：视图表的数据是否能修改？

视图表保存的是select查询语句的定义。如果select语句查询的字段是没有被处理过的源表字段，则可以通过视图表修改源表数据。
?如果select语句查询的字段被 group by 或 函数 等处理过的字段，则不可以直接修改视图表的数据

select case '字段' when 值1或表达式 then 值或表达式 ? ? ? ? ? ? ?#根据一个字段的条件设置一个新的字段的值
when 值2或表达式 then 值或表达式
....
else 值或表达式 end AS "字段别名"?
from 表;

无值'' ?和 空值null 的区别？

空值null 的长度为 NULL ，占用空间；可以通过 字段 is null 或 字段 is not null 判断是否为null行；count(字段) 会忽略 null 行
无值'' 的长度为 0 ，不占用空间；可以通过 字段 = '' 或 字段 <> '' 判断是否为无值的行；count(字段) 不会忽略 无值 的行
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?!= ''

存储过程（数据库脚本）

创建存储过程：

1）修改SQL语句结束符 ? ? delimiter $$
2）创建存储过程 ? ?use 库名 ? ? ? create procedure 存储过程名() begin ?SQL语句序列; ?end$$
3）把结束符改回分号 ? ? ?delimiter ;
4）调用存储过程 ? ? ? ? ?call 存储过程名;

查看存储过程

show create procedure 存储过程名;

show procedure status like '存储过程名'\G
? ?

存储过程参数（只能用于字段）

in ? ? ?传入参数
out ? ? 传出参数
inout ? 传入传出参数

create procedure 存储过程名(in 传入参数名 参数数据类型, out 传出参数名 参数数据类型)
begin
? ? select 字段 into 传出参数 from 表 where 字段 = 传入参数;
end$$

call ?存储过程名(参数值|@变量名; @变量名); ? ? ?#传入参数指定的值可以是纯量值，也可以是变量名；传出参数指定的值只能是变量名

create procedure 存储过程名(inout 参数名 参数数据类型)
begin
? ? select 字段 into 参数名 from 表 where 字段 = 参数名;
end$$

set @变量名 = 传入的值; ? ? ?#设置传入的值
call 存储过程名(@变量名); ? ?#传入传出参数指定的值只能是变量名
select @变量名; ? ? ? ? ? ? ?#查看传出的值

存储过程控制语句

条件语句：
if 条件表达式 then
? ? SQL语句序列1;
else
? ? SQL语句序列2;
end if;

循环语句：
while 条件表达式
do
? ? SQL语句序列;
?? ?set 条件迭代;
end while;