MySQL一些常用高级SQL语句详解(2)-木庄网络博客

当前第2页返回上一页

-------------------别名----------------------
别名包括字段别名和表的别名，当一张表的名字或者表中的某一个字段名过于冗长时，可以使用别名将之代替，从而降低查询的失误率。
语法格式：select 字段 [AS] 字段别名 from 表名 [AS] 表格别名；
select Store_name NAME,sales SALE from store_info;

------------------子查询---------------------
子查询通常用于连接表格，在where子句或者having子句中插入另一个SQL语句。
语法格式：select 字段1 from 表格1 where 字段2 [比较运算符] (select 字段2 from 表格2 where 条件语句);括号里的select语句是内查询，括号外的select语句是外查询
select Region from location where Store_name=(select Store_name from store_info where sales=300); #比较运算符，可以是=、>、<或者>=、<=

select sales from store_info where Store_name in (select Store_name from location); #也可以是in、between...and、like等

------------------exists---------------------
exists用来测试内查询有没有产生任何结果，类似布尔值
是否为真。如果内查询产生了结果，则将结果作为外查询
的条件继续执行，如果没有结果，那么整条语句都不会产
生结果。
语法格式：select 字段1 from 表1 where exists (select 字段2 from 表2 where 条件语句);
select sum(sales) from store_info where exists (select * from location where Region='North'); #
#先执行内查询语句，即在location表中查询Region为North的所有数据，如果存在，则执行外查询语句；如果不存在，就不执行外查询
------------------连接查询-----------------------
inner join(内连接)：内连接只返回两个表中字段的值相等的数据，即两表的交集
left join(左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录
select * from location left join store_info on location.Store_name=store_info.Store_name; #左连接

select * from location right join store_info on location.Store_name=store_info.Store_name; #右连接

select * from location inner join store_info on location.Store_name=store_info.Store_name; #内连接法1

SELECT * FROM location A, store_info B WHERE A.Store_Name = B.Store_Name; #内连接法2

SELECT Region REGION, SUM(B.Sales) SALES FROM location A, store_info B WHERE A.Store_Name = B.Store_Name GROUP BY Region; #查询两表中name值相等的Region的值和sales的和，并按照Region字段进行分组，REGION是字段Region的别名，SALES是sum(sales)的别名，A是表location的别名，B是info表的别名

---------------------view-----------------------
view，视图，视图是一张虚拟的表，通常用于保存多表联合查询出来数据，这样可以极大的减轻SQL语句的复杂度，在执行n张表的联合查询时，视图可以起到很大的便利作用。视图跟表格的不同是，表格中有实际储存数据记录，而视图是建立在表格之上的一个架构，它本身并不实际储存数据记录。而视图不会因为退出数据库而消失。
语法格式1：create view 视图名 as 查询语句; #创建视图
语法格式2：drop view 视图名; #删除视图
show tables from 库名; #该命令不仅可以查看库所包含的表，也可以查看有哪些视图

------------------------union-------------------------
union，联集，其作用是将两个SQL语句的结果合并起来，两个SQL语句所产生的字段需要是同样的数据记录种类
union ：生成结果的数据记录值将没有重复，且按照字段的顺序进行排序
union all ：将生成结果的数据记录值都列出来，无论有无重复
语法格式：[select 语句 1] UNION [all] [SELECT 语句 2];
select Store_name from location union select Store_name from store_info;

select Store_name from location union all select Store_name from store_info;

-------------------求交集的几种方法------------------
存在重复数据：
1.select A.Store_Name FROM location A inner join store_info B on A.Store_Name = B.Store_Name;
2.select A.Store_name from location A,store_info B where A.Store_name=B.Store_name;
3.select A.Store_name from location A inner join store_info B using (Store_name);

无重复数据：
1.想要得到无重复数据其实很简单，在重复数据的查询语句select后加上distinct去重即可。
select distinct A.Store_Name FROM location A inner join store_info B on A.Store_Name = B.Store_Name;

2.使用子查询，在内查询中查询info中的name字段，并将之作为外查询的条件，这样，外查询的查询语句的范围就只能在内查询查出的数据中进行。（子查询实际上就是变相的内查询）
select distinct Store_name from location where Store_name in (select Store_name from store_info);

3.使用左查询，将location和info表进行左查询，会查询出location表中的所有name字段的值以及info表中与location表中name的值相等的数据，再使用distinct进行去重
select distinct A.Store_name from location A left join store_info B using(Store_name) where B.Store_name is not null;

4.使用级联查询，union all会将两张表的所有数据都连接到一起，这时只需要通过count()函数将大于1的数值统计出来，即可实现查询两表的共同数值。
select Store_name,count(A.Store_name) from (select Store_name from location union all select Store_name from store_info) A group by A.Store_name having count(A.Store_name)>1 ;

---------------------无交集---------------------
既然我们可以查询出有交集的数据，那么取反就可以实现无交集的查询了
select distinct Store_name from location where Store_name not in (select Store_name from store_info);

---------------------case---------------------
case是SQL语句用来做为when-then-else（当...就...否则...）之类逻辑的关键字
语法格式：
select case 字段名
when 条件1 then 结果1
when 条件2 then 条件2
.....
[else 结果n]
end
from 表名;

--------空值(NULL) 和无值('') 的区别----------
1．无值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。
2．IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是无值的。
3．无值的判断使用=''或者<>''来处理。<> 代表不等于。
4．在通过 count()指定字段统计有多少行数时，如果遇到 NULL 值会自动忽略掉，遇到无值会加入到记录中进行计算。
create table city(name char(10)); #新建city表
insert into city values ('beijing'); #插入三个值
insert into city values ('nanjing');
insert into city values ('xuzhou');
insert into city values (''); #插入两个无值
insert into city values ('');
insert into city values (null); #插入两个空值
insert into city values (null);
select * from city; #查询city表的所有值
select length(name) from city; #查询name字段值的长度
select count(name) from city; #统计name字段的值，空值会被忽略
select length('111'),length(null),length(''); #比较有值、空值、无值的长度
select * from city where name is null; #查询name为空的值
select * from city where name is not null; #查询name字段不为空的值
select * from city where name = ''; #查询name值为无值的数据
select * from city where name <> ''; #查询name字段不为无值的数据，空值会被忽略

--------------------正则表达式-------------------------
^:匹配文本的开始字符
$:匹配文本的结束字符
.:匹配任何一个字符
*:匹配零个或多个在它前面的字符
+:匹配前面的字符1次或多次
字符串:匹配包含指定的字符串
p1|p2:匹配p1或p2
[...]:匹配字符集合中的任意一个字符
[^...]:匹配不在括号中的任何字符
{n}:匹配前面的字符串 n 次
{n,m}:匹配前面的字符串至少n次，至多m次
语法格式：select 字段 from 表名 where 字段 REGEXP {模式};
select * from city where name regexp 'zhou'; #匹配数据中带zhou的
select * from city where name regexp 'nan|bei'; #匹配数据中有nan或bei的
select * from city where name regexp '^[xnb]'; #匹配以xnb任一字符开头的数据

三、MySQL存储过程

存储过程是一组为了完成特定功能的SQL语句集合。

存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。

存储过程的优点：

1、执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率

2、SQL语句加上控制语句的集合，灵活性高

3、在服务器端存储，客户端调用时，降低网络负载

4、可多次重复被调用，可随时修改，不影响客户端调用

5、可完成所有的数据库操作，也可控制数据库的信息访问权限

------------------创建存储过程-----------------------
delimiter ！！ #将语句的结束符号从分号;临时改为两个！！(可以是自定义)
create procedure Proc() #创建存储过程，过程名为Proc，不带参数
-> begin #过程体以关键字 BEGIN 开始
-> select * from Store_Info; #过程体语句
-> end $$ #过程体以关键字 END 结束
delimiter ; #将语句的结束符号恢复为分号

------------------调用存储过程-----------------------------
call Proc;
------------------查看存储过程----------------------------
show create procrdure [数据库.]存储过程名; #查看某个存储过程的具体信息
show create procedure proc;
show procedure status [LIKE '%Proc%'] \G

----------------存储过程的参数--------------------
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

实例：
delimiter !
create procedure proc1(in inname char(10))
-> begin
-> insert into city values(inname);
-> select * from city;
-> end !
delimiter ;
call proc1('hangzhou');

------------------删除存储过程----------------------
存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。
drop procedure if exists 存储过程名; #仅当存在时删除，不添加if exists时，如果指定的过程不存在，则产生一个错误

##存储过程的控制语句##
create table num (id int);
insert into num values(10);
（1）条件语句if...then...else ...end if
delimiter ！！
create procedure proc2(in nid int)
-> begin
-> declare var int; #declare 变量名数据类型来创建一个变量
-> set var=nid*3; #设置变量的参数
-> if var>=10 then #if-else判断
-> update num set id=id+1;
-> else
-> update num set id=id-1;
-> end if; #结束判断
-> end ！！
delimiter ;
call proc2(6);

（2）循环语句while...do...end while #用法和if一样，while的话必须要给定义的变量自增的过程，否则会死循环，可以参考shell中的while语句
delimiter ！！
create procedure proc3()
-> begin
-> declare var int(10);
-> set var=0;
-> while var<6 do
-> insert into num values(var);
-> set var=var+1;
-> end while;
-> end ！！
delimiter ;
call proc3;