MySQL高级SQL语句

manny
408 阅读8分钟

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;

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

\

avatar
文章
阅读
粉丝