29.MySQL高级SQL语句

2022/6/20 2:20:43

本文主要是介绍29.MySQL高级SQL语句,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

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 $$

创建两个表

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;

img

DISTINCT

去重

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

img

WHERE

有条件查询

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

select store_name,sales from store_info where sales >1000;

img

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);

img

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表示取反的意思

img

BETWEEN

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

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

img

通配符

通常通配符都是跟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结尾的数据记录

img

img

img

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; #字符串排序

img

img

函数

数学函数

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);

img

img

聚合函数

函数 含义
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;

img

img

img

img

img

字符串函数

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(起头及结尾)。
#[要移除的字符串]:从字串的起头、结尾,或起头及结尾移除的字符串。缺省时为空格。

img

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

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

img

img

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

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

img

img

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

img

返回字符串 x 的长度

select region,length(store_name) from location;

img

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

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

img

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;; #汇总求值排序

img

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;

img

别名

字段别名表格别名

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

img

子查询

  • 连接表格,在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);

img

img

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');

img

表连接的方式

  • 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;   #右连接

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

img

img

内连接一:
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字段进行汇总分组并求和

img

img

img

表的自我连接

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) ;

img

img

img

img

img

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;  #删除视图表

img

img

img

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;   ##将生成结果的数据记录值都列出来,无论有无重复

img

img

交集值

取两个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;

img

img

img

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

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

img

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

img

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

img

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;

img

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;

img

无交集值

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

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

img

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

img

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;

img

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;

img

空值

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

示例:

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

img

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

img

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

img

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

img

正则表达式

一般配合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} 匹配前面的字符串n次 'g{2}'匹配含有2个g的字符串
{n,m} 匹配前而的字符串至少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';

img

img

存储过程

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

存储过程的优点:

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

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

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

img

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

img

存储过程的参数

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');

img

删除存储过程

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

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

img

存储过程的控制语句

条件语句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

img

img

循环语句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存储过程

img



这篇关于29.MySQL高级SQL语句的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程