MySQL高级SQL语句

创建两个表

use kgc;
create table location(Region char(20),Store_name char(20));
insert into location values('East','Boston');
insert into location values('East','New York');
insert into location values('West','Los Angeles');
insert into location values('West','Houston');

create table store_info(store_name char(20),sales int(10),date char(10));
insert into store_info values('Los Angeles',15001,'2022-05-30');
insert into store_info values('Houston',2501,'2022-05-31');
insert into store_info values('Los Angeles',3001,'2022-06-01');
insert into store_info values('Boston',7001,'2022-06-02');

SELECT

显示表格中的一个或者多个字段中所有的信息

kk语法：SELECT 字段 FROM 表名;
select store_name from store_info;

DISTINCT

去重

语法：SELECT DISTINCT 字段 FROM 表名;
select distinct store_name from store_info;

WHERE

有条件查询

语法：SELECT 字段1,字段2,... FROM 表名 WHERE 条件;

select store_name,sales from store_info where sales >1000;

AND OR

且或

语法：SELECT 字段 FROM 表名 WHERE 条件1 {[ANDIOR]"条件2"1+;
select store_name,sales from store_info where sales > 5000 or(sales < 10000 and sales >3000);

IN

显示已知的值的数据记录

语法：SELECT 字段 FROM 表名 WHERE 字段 IN ('值1','值2',...);
select * from store_info where store_name in('Los Angeles','Houston');
 select * from store_info where store_name not in('Los Angeles','Houston') ;
 #not表示取反的意思

BETWEEN

显示两个值范围内的数据记录

语法：SELECT 字段 FROM 表名 WHERE 字段 BETWEEN '值1'AND,'值2';
select store_name,sales from store_info where sales between 3000 and 10000;

通配符

通常通配符都是跟LIKE一起使用的

%	百分号表示零个、一个或多个字符
_	下划线表示单个字符
A_Z	所有以'A'起头，另一个任何值的字符，且'z'为结尾的字符串。例如，'ABZ'
ABC%	所有以ABC开头的字符串如：'ABCD'、'ABCABC'
%CBA	所有以CBA结尾的字符串如：'WCBA'、'CBACBA'
%AN%	所有包含AN的字符串例如，'LOSANGELES'和'SAN FRANCISCO'都符合这个模式。
_AN%	所有第二个字母为 A 第三个字母为N 的字符串例如，'SAN FRANCISCO'、符合这个模式，而'LOSANGELES'则不符合这个模式。

LIKE

匹配一个模式来找出我们要的数据记录

语法：SELECT 字段 FROM 表名 WHERE 字段 LIKE{模式};
select * from store_info where store_name like '%os%'; 
#显示表内指定字段包含os的数据记录
select * from store_info where store_name like 'l%s';
#显示表内指定字段已l开头s结尾的数据记录
select * from store_info where store_name like '_o%n';
#显示表内指定字段第二个字母为o且以n结尾的数据记录

ORDER BY

按关键字排序

语法：SELECT 字段 TROM 表名 [WHERE 条件] ORDER BY 字段 [ASC,DESC];
#ASC是按照升序进行排序的，是默认的排序方式。
#DESC是按降序方式进行排序。
select store_name,sales from store_info order by sales desc;  #降序排序
select store_name,sales from store_info order by sales asc; #升序排序
select store_name,sales from store_info order by store_name asc; #字符串排序

函数

数学函数

abs（x）	返回x的绝对值
rand()	返回0到1的随机数
mod（x，y）	返回x除以y以后的余数
power（x，y）	返回x的y次方
round（x）	返回离x最近的整数
round（x，y）	保留x的y位小数四舍五入后的值
sqrt（x）	返回x的平方根
truncate（x，y）	返回数字x截断为y位小数的值
ceil（x）	返回大于或等于x的最小整数
floor（x）	返回小于或等于x的最大整数
qreatest（x1，x2...）	返回集合中最大的值
least（x1，x2...）	返回集合中最小的值

示例：
select abs(-1),rand(),mod(5,3),power(2,3),round(1.89);

select round(1.8937,3),truncate(1.235,2),ceil(5.2),floor(2.1),least(1.8,3,6,1,2.1);

聚合函数

函数	含义
avg()	返回指定列的平均值
count()	返回指定列中非 NULL 值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum(x)	返回指定列的所有值之和

示例：
select avg(sales) from store_info;  #返回指定字段列的平均值
select count(name) from city;  #返回指定表内字段列中非空值的个数 
select count(*) from city;    #返回指定表内字段列中包括非空值的个数
#注：innodb执行count（*）会执行全表扫描
select count(distinct store_name) from store_info;  #去重返回指定列中非 NULL 值的个数
select max(sales) from store_info;
select min(sales) from store_info; 
select sum(sales) from store_info;

字符串函数

trim()	返回去除指定格式的值
concat(x,y)	将提供的参数 x 和 y 拼接成一个字符串
substr(x,y)	获取从字符串 x 中的第 y 个位置开始的字符串，跟substring（）函数作用相同
substr(x,y,z)	获取从字符串 x 中的第 y 个位置开始长度为z 的字符串
length(x)	返回字符串 x 的长度
replace(x,y,z)	将字符串 z 替代字符串 x 中的字符串 y
upper(x)	将字符串 x 的所有字母变成大写字母
lower(x)	将字符串 x 的所有字母变成小写字母
left(x,y)	返回字符串 x 的前 y 个字符
right(x,y)	返回字符串 x 的后 y 个字符
repeat(x,y)	将字符串 x 重复 y 次
space(x)	返回 x 个空格
strcmp(x,y)	比较 x 和 y，返回的值可以为-1,0,1
reverse(x)	将字符串 x 反转

返回去除指定格式的值

select trim(leading 'Los' from 'Los Angeles');
#[位置]：的值可以为LEADING（起头），TRAILING（结尾），BOTH（起头及结尾）。
#[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。

将指定表内的字段值拼接指定数据记录

select concat(region,store_name) from location where store_name='boston';
#如sql_mode开启了PIPES_AS_CONCAT，"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数concat相类似，这和oracle数据库使用方法一样的
select region || '' || store_name from location;

获取从字符串 x 中的第 y 个位置开始的字符串

select substr(region || '' || store_name,5) from location;
select substr(store_name,3) from location where store_name='los angeles';

获取从字符串 x 中的第 y 个位置开始长度为z 的字符串

返回字符串 x 的长度

select region,length(store_name) from location;

将字符串 z 替代字符串 x 中的字符串 y

select replace(region,'ast','stern') from location;

GROUP BY

查询结果进行汇总分组

对GROUEBY后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的
GROUP BY有一个原则，凡是在GROUP BY后面出现的字段，必须在SELECT后面出现；
凡是在SELECT后面出现的、且未在聚合函数中出现的字段，必须出现在GROUPBY后面

语法：SELECT "字段1",SUM("字段2") FROM "表名" GROUPBY "字段1";
select store_name,sum(sales) from store_info group by store_name asc order by sum(sales) asc;; #汇总求值排序

HAVING

用来过滤由GROUP BY语句返回的记录集(结果)，通常与GROUP BY语句联合使用HAVING 语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

语法：SELECT"字段1"，SUM("字段2”") FROM "表格名" GROUP BY "字段1" HAVIN(函数条件);
select store_name,sum(sales) from store_info group by store_name having sum(sales) >15000;

别名

字段别名表格别名

语法：SELECT "表格别名" "字段1" [AS] "字段别名" FROM "表格名" [AS] "表格别名";
select store_name,sum(sales) as "A(sales)" from store_info A group by A.store_name;

子查询

连接表格，在WHERE子句或HAVING子句中插入另一个sQL语句

语法：SELECT "字段1" FROM "表格1" WHERE "字段2"[比较运算符]     #外查询
(SELECT "字段1" FROM "表格2" WHERE "条件");   #内查询

可以是符号的运算符，例如=、>、<、>=、<=；也可以是文字的运算符，例如LIKE、IN、BETWEEN

select sum(sales) from store_info where store_name in (select store_name from location where region='west');

select sum(A.sales) from store_info A where A.store_name in(select store_name from location B where B.store_name = A.store_name);

EXISTS

用来测试内查询有没有产生任何结果，类似布尔值是否为真
如果有的话，系统就会执行外查询中的sQL语句。若是没有的话，那整个sQL语句就不会产生任何结果。

语法：SELECT "字段1" FROM "表格1" WHERE EXISTS(SELECT * FROM"表格2" WHERE "条件" );
select sum(sales) from store_info where exists(select * from location where region = 'west');

表连接的方式

inner join（内连接）：只返回两个表中联结字段相等的行
left join（左连接）：返回包括左表中的所有记录和右表中联结字段相等的记录
right join（右连接）：返回包括右表中的所有记录和左表中联结字段相等的记录

select * from location A left join store_info B on A.store_name=B.store_name;   #左连接

select * from location A right join store_info B on A.store_name=B.store_name;   #右连接

返回包括左表中的所有记录和右表中联结字段相等的记录

内连接一：
select * from location A inner join store_info B on A.store_name=B.store_name;  #连接两个表内字段数据记录相等的数据记录
内连接二：
select * from location A,store_info B where A.store_name=B.store_name;   #连接两个表内字段数据记录相等的数据记录

select A.region region,sum(B.sales) sales from location A,store_info B where A.store_name = B.store_name group by region;      #连接两个表内字段数据记录相等的数据记录对region字段进行汇总分组并求和

表的自我连接

select A.name, A.score, count(B.score) from ky18 AS A ,ky18 AS B where A.score <= B.score group by A.name, A.score;
###定义别名A、B表将A表与B表进行比较条件A表score字段数据记录大于等于B表score字段数据进行汇总记录
select A.name, A.score, count(B.score)  from ky18 AS A ,ky18 AS B where A.score <= B.score group by A.name, A.score order by count(B.score) ;
###将汇总结果排序

###假设表内有同样的scoer数据记录进行排序
select A.name, A.score, count(B.score)  from ky18 AS A ,ky18 AS B where A.score < B.score or(A.score = B.score and A.name = B.name) group by A.name, A.score order by count(B.score) ;
###如果非得分出第4名示例
select A.name, A.score, count(B.score)  from ky18 AS A ,ky18 AS B where A.score < B.score or(A.score = B.score and A.name <= B.name) group by A.name, A.score order by count(B.score) ;

CREATE VIEW

视图，可以被当作是虚拟表或存储查询。
视图跟表格的不同是，表格中有实际储存数据记录，而视图是建立在表格之上的一个架构，它本身并不实际储存数据记录。
临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。
视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写sQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。

语法：CREATE VIEW "视图表名" AS "SELECT 语句";
create view v_region_sales as select A.region region,sum(B.sales) sales from location A inner join store_info B on A.store_name = B.store_name group by region;
select * from v_region_sales;   #查看视图表
drop view v_region_sales；  #删除视图表

UNION

联集，将两个sQL语句的结果合并起来，两个sQL语句所产生的字段需要是同样的数据记录种类
UNION：生成结果的数据记录值将没有重复，且按照字段的顺序进行排序
UNION ALL：将生成结果的数据记录值都列出来，无论有无重复

语法：[SELECT语句1] UNION [SELECT语句2];
语法：[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;   ##将生成结果的数据记录值都列出来，无论有无重复

交集值

取两个soL语句结果的交集

select A.store_name from location A inner join store_info B on A.store_name = B.store_name;   #内连接只返回两个表中联结字段相等的行

select A.store_name from location A inner join store_info B using(store_name);    #suing指定字段进行内查询比较 内连接只返回两个表中联结字段相等的行
select A.store_name from location A inner join store_info B where A.store_name = B.store_name;

取两个sQL语句结果的交集，且没有重复

select distinct A.store_name from location A inner join store_info B using(store_name);

select distinct store_name from location where (store_name) in (select store_name from store_info);

select distinct A.store_name from location A left join store_info B using(store_name) where B.store_name is not null;

select A.store_name from (select B.store_name from location B inner join store_info C on B.store_name = C.store_name) A group by A.store_name;

select A.store_name from (select distinct store_name from location  union all select distinct store_name from store_info) A group by A.store_name having count(*)>1;

无交集值

显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，且没有重复

 select distinct store_name from location where(store_name) not in (select store_name from store_info);

select distinct A.store_name from location A left join store_info B using(store_name) where B.store_name is null;

select A.store_name from (select distinct store_name from location  union all select distinct store_name from store_info) A group by A.store_name having count(*)=1;

CASE

是 SQL 用来做为 IF-THEN-ELSE 之类逻辑的关键字

语法：
  SELECT CASE ("字段名")
  WHEN "条件1" THEN "结果1"
  WHEN "条件2" THEN "结果2"
  ...
  [ELSE "结果N"]
  END
FROM "表名";

"条件" 可以是一个数值或是公式。 ELSE 子句则并不是必须的。
    select store_name,case store_name
    when 'los angeles' then sales * 2
    when 'boston' then 2000
    else sales
    end
    "new sales",date     #"New Sales" 是用于 CASE 那个字段的字段名。
    from store_info;

空值

（NULL）和无值（）的区别
1.无值的长度为0，不占用空间的；而NULL，值的长度是NULL，是占用空间的。
2.IS NULL或者IS NOTNUL，是用来判断字段是不是为NULL或者不是NULL，不能查出是不是无值的。
3.无值的判断使用=，或者<>1，来处理。<>代表不等于。
4.在通过count（）指定字段统计有多少行数时，如果遇到NULL值会自动忽略掉，遇到无值会加入到记录中进行计算。

示例：

SELECT length(NULL), length(''), length('1');

select * from city where name is NULL;
select * from city where name is not NULL;

SELECT * FROM City WHERE name = '';
SELECT * FROM City WHERE name <> '';

select count(*) from city;
select count(name) from city;

正则表达式

一般配合regexp使用

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

语法：SELECT "字段" FROM "表名" WHERE "字段" REGEXP {模式};
SELECT * FROM Store_Info WHERE Store_Name REGEXP 'os';
SELECT * FROM Store_Info WHERE Store_Name REGEXP '^[A-G]';
SELECT * FROM Store_Info WHERE Store_Name REGEXP 'Ho|Bo';

存储过程

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

存储过程的优点：

1、执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
2、sQL语句加上控制语句的集合，灵活性高
3、在服务器端存储，客户端调用时，降低网络负载
4、可多次重复被调用，可随时修改，不影响客户端调用
5、可完成所有的数据库操作，也可控制数据库的信息访问权限

创建存储过程

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

##调用存储过程##
call gb;

查看存储过程

SHOW CREATE PROCEDURE [数据库.]存储过程名;		#查看某个存储过程的具体信息
SHOW CREATE PROCEDURE gb\G;
SHOW PROCEDURE STATUS [LIKE '%Proc%']\G;

存储过程的参数

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

DELIMITER $$				
CREATE PROCEDURE Proc1(IN inname CHAR(20))  #数据类型一定要与下面的where语句后字段的数据类型相同
-> BEGIN					
-> SELECT * FROM Store_Info WHERE Store_Name = inname;
-> END $$					
DELIMITER ;					

CALL Proc1('Boston');

删除存储过程

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。

DROP PROCEDURE IF EXISTS PrOC;
#仅当存在时删除，不添加IFEXISTS时，如果指定的过程不存在，则产生一个错误

存储过程的控制语句

条件语句if-then-else……end if

 elimiter $$                                   #修改默认结束符为$$ 
 create procedure proc2(in pro int)            #创建存储过程proc2，参数为pro，类型为int 
    -> begin                                   #过程体以关键字begin开始 
    -> declare var int;                        #定义变量var为int类型 
    -> set var=pro*2;                          #设置变量var等于传入参数的3倍 
    -> if var>=10 then                         #如果变量var大于等于10，则执行修改表字段数据自加1
    -> update t set id=id+1;     
    -> else                                    #其他则减1
    -> update t set id=id-1;                    
    -> end  if;                                #结束if语句 
    -> end $$      
 elimiter ；                                   #重新修改默认结束符为原始的; 
call proc2（6）；                               #调用proc2存储过程，并传入参数6

循环语句while……end whilg DELIMITER $$

DELIMITER $$                               #修改默认结束符为$$
create procedure proc3()                   #创建存储过程proc3
    -> begin                               #过程体以关键字begin开始
    -> declare var int (10);               #定义变量i为int类型（长度最大为10）
    -> set var=0;                          #起始值 = 0;
    -> while var<6 do             #使用while循环 var小于6满足条件则进行添加数据，内容为变量var的值
    -> insert into t values(var);
    -> set var=var+1;                      #变量var每次循环后自加1 
    -> end while;                          #结束while循环
    -> end $$                              #创建存储过程结束

DELIMITER ;                                #重新修改默认结束符为原始的;
call proc3;                                #调用yxp存储过程

posted @ 2022-06-02 14:06 GENGBO 阅读(96) 评论(0) 编辑收藏举报

刷新页面返回顶部

GENG BO

MySQL高级SQL语句

MySQL高级SQL语句

创建两个表

SELECT

DISTINCT

WHERE

AND OR

IN

BETWEEN

通配符

LIKE

ORDER BY

函数

数学函数

聚合函数

字符串函数

GROUP BY

HAVING

别名

子查询

EXISTS

表连接的方式

表的自我连接

CREATE VIEW

UNION

交集值

无交集值

CASE

空值

正则表达式

存储过程

存储过程的优点：

创建存储过程

查看存储过程

存储过程的参数

删除存储过程

存储过程的控制语句

循环语句while……end whilg DELIMITER $$

公告