MySQL数据库SQL语句（二）

一、连接查询

1. inner join(等值相连)

• 只显示两个表中联结字段相等的行

select * from 表1名 表1别名 inner join 表2名 表2别名 on 表1别名.字段=表2别名.字段;
select * from test1 A inner join test2 B on A.name=B.name;

2. left join(左联接)

select * from 表1名 表1别名 left join 表2名 表2别名 on 表1别名.字段=表2别名.字段;
select * from test1 A left join test2 B on A.name = B.name;

3. right join(右联接)

select * from 表1名 表1别名 right join 表2名 表2别名 on 表1别名.字段=表2别名.字段;
select * from test1 A right join test2 B on A.name = B.name;

二、create view 视图

• 视图跟表格的不同是，表格中有实际储存资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料，可以简化复杂的查询
• 临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失

create view new_test as select A.id id,A.name name,A.price,B.status status from test1 A inner join test2 B on A.name=B.name;

三、MySQL之联集、交集值、无交集值、case

1. 联集

select name from test1 union select name from test2;         去重，显示两个表中name列内容

select name from test1 union all select name from test2;     不去重，显示两个表中name列内容

2. 交集值

• 取两个SQL语句结果的交集

select * from 表名 表1名 表1别名 inner join 表2名 表2别名 on 表1别名.字段=表2别名.字段;
select A.name from test1 A inner join test2 B on A.name=B.name;

3. 无交集值

• 显示第一个SQL语句与第二个SQL语句没有交集的结果

select name,price from test1 where (name) not in (select name from test2);

4. case

语法：
select case ("列名")
when "条件1" then "结果1"
WHEN "条件2" then "结果2"
...
[else "结果N"]
end
from "表名";
"条件" 可以是一个数值或是公式。 else子句则并不是必须的。
例：
select name,case name
when 'b' then price+1000
when 'c' then price-2000
else price
end
"new price" 
from test1;

5. 空值：NULL和无值的区别

• 无值的长度为0，不占用空间；而空值NULL的长度是null，是占用空间的
• is null或者is not null，是用来判断字段是不是NULL或者不是NULL，是不能查出是不是无值的
• 无值的判断使用=’‘或者<>’'来处理。<>代表不等于
• 在通过count()指定字段统计又多少行数时，如果遇到NULL值会自动忽略掉，遇到空值会自动加入记录中进行计算

四、排序

1. 算排名

select a1.name,a1.score,count(a2.score) rank from test1 a1,test1 a2
where a1.score < a2.score or (a1.score = a2.score and a1.name = a2.name)
group by a1.name,a1.score order by a1.score desc;

2. 算中位数

select score middle from (select a1.name, a1.score,count(a2.score) rank from test1 a1,test1 a2 
where a1.score < a2.score or (a1.score = a2.score and a1.name <= a2.name) 
group by a1.name,a1.score order by a1.score desc)
a3 where a3.rank = (select (count(*)+1) div 2 from test1);

五、正则表达式

语法：
select 字段 from 表名 where 字段 regexp 匹配模式

匹配模式	描述	实例
^	匹配文本的开始字符	‘^bd’ 匹配以 bd 开头的字符串
$	匹配文本的结束字符	‘qn$’ 匹配以 qn 结尾的字符串
.	匹配任何单个字符	‘s.t’ 匹配任何 s 和 t 之间有一个字符的字符串
*	匹配零个或多个在它前面的字符	‘fo*t’ 匹配 t 前面有任意个 o
+	匹配前面的字符 1 次或多次	‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串
字符串	匹配包含指定的字符串	‘clo’ 匹配含有 clo 的字符串
p1	p2	匹配 p1 或 p2
[…]	匹配字符集合中的任意一个字符	‘[abc]’ 匹配 a 或者 b 或者 c
[^…]	匹配不在括号中的任何字符	‘[^ab]’ 匹配不包含 a 或者 b 的字符串
{n}	匹配前面的字符串 n 次	‘g{2}’ 匹配含有 2 个 g 的字符串
{n,m}	匹配前面的字符串至少 n 次，至多m 次	‘f{1,3}’ 匹配 f 最少 1 次，最多 3 次

六、存储过程

语法：
delimiter !!    				
delimiter !!									#将语句的结束符号从分号；临时修改，以防出问题，可以自定义
create procedure xxx()  						#创建存储过程，过程名自定义,（）可带参数
 	begin   		    						#过程体以关键字begin开始
 	select * from xxx;		  					#过程体语句
 	end!!   	 								#过程体以关键字end结尾
delimiter ; 			    					#将语句的结束符号恢复为分号

call xxx;		  								#调用存储过程

查看存储过程：
show create procedure [数据库.]储存过程名; 	  	#查看某个储存过程的具体信息
show create procedure xxx;
show procedure status [like '%xxx%'] \G