首页 > 数据库 >MySQL高级语句（二）

MySQL高级语句（二）

时间：2023-03-29 14:22:09浏览次数：42  
标签：语句 FARE 高级 site A1 score MySQL A2 select

 

一、Mysql高阶语句

1、别名

字段别名、表格别名
语法：SELECT “表格別名”.“字段1” [AS] “字段1別名” FROM “表格名” [AS] “表格別名”;

1 2 3 select RE.region  AS reg, count(site) from REGION AS RE group by reg;   select FA.site AS si,sum(money),count(money),date AS da from FARE AS FA group by si;

2、子查询

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

1 2 SELECT 字段1 FROM 表格1 WHERE 字段2 [比较运算符]      #外查询 (SELECT 字段1 FROM 表格2 WHERE 条件)                #内查询
  • 可以是符号的运算符
    例:=、>、<、>=、<=
  • 也可以是文字的运算符
    例:LIKE、IN、BETWEEN
1 2 3 4 5 select A.site,region from REGION AS A where A.site in (select B.site from FARE AS B where money<2000);   select B.site,money,date from FARE AS B where site in (select A.site from REGION AS A where region = 'north');

3、EXISTS

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

语法：

1 SELECT 字段1 FROM 表1 WHERE EXISTS (SELECT * FROM 表2 WHERE 条件);

例：

1 select region from REGION where exists (select * from FARE where money = '700');

4、连接查询
MysQL的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。
首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。
使用较多的连接查询包括：内连接、左连接和右连接（inner join、left join、right join）

inner join（等值相连）

只返回两个表中联接字段相等的行

1 语法：SELECT 字段 FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;

left join（左联接）　　

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

1 语法：SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;

right join（右联接）

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

1 语法：SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

例:

1 2 3 4 5 update FARE set site='xingjiang' where money='2200';   select * from REGION AS A inner join FARE AS B on A.site = B.site; select * from REGION AS A left join FARE AS B on A.site = B.site; select * from REGION AS A right join FARE AS B on A.site = B.site;

5、CREATE VIEW 视图
视图：可被当作虚拟表或存储查询。

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

1 语法：CREATE VIEW 视图表名 AS SELECT语句;

例:

1 2 3 4 select A.region,SUM(B.money) from REGION AS A inner join FARE AS B on A.site = B.site GROUP BY region; create view C AS select A.region,SUM(B.money) from REGION AS A inner join FARE AS B on A.site = B.site GROUP BY region; show tables; select * from C;

6、UNION 联集

将两个SQL语句的结果合并起来，两个SQL语句产生的字段需要是同样的资料种类

  • UNION:生成结果的资料值将没有重复，且按照字段的顺序进行排序。
1 语法：SELECT语句1 UNION SELECT语句2;
  • UNION ALL：将生成结果的资料值都列出来，无论有无重复
1 语法：SELECT语句1 UNION ALL SELECT语句2;

例：

1 2 3 select region from REGION union select money from FARE; select site from REGION union select site from FARE; select site from REGION union all select site from FARE;

7、交集值

取两个SQL语句结果的交集

例:

复制代码 
 1 select * from FARE;
 2 insert into FARE values ('xingjiang',2200,'2021-02-05');
 3 select * from FARE;
 4 
 5 select A.site from FARE AS A inner join REGION AS B on A.site = B.site;
 6 select A.site from FARE AS A inner join REGION AS B  using(site);
 7 
 8 select site from (select site from FARE union all select site from REGION) A group by A.site HAVING count(*) > 1;
 9 
10 select site from (select A.site from FARE A inner join REGION B on A.site = B.site) C group by C.site;
11 
12 select site from FARE where site in (select site from REGION);
复制代码

8、无交集值

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

1 select distinct site from FARE where (site) not in (select site from REGION);

例：

1 2 3 select A.*,B.* from FARE A left join REGION B using(site); select site from FARE A left join REGION B using(site) where B.site is null; select distinct site from FARE A left join REGION B using(site) where B.site is null;

9、CASE

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

语法：

复制代码 
1 SELECT CASE （字段名）
2     WHEN 条件1 THEN  结果1
3     WHEN 条件2 THEN 结果2
4     ……
5     ELSE 结果N
6     END
7 FROM 表名
8 
9 #条件可以是一个数值或是公式。ELSE子句不是必须的。
复制代码

例:

复制代码 
1 select * from FARE;
2 
3 select case site
4    when 'changsha' then money + 500
5    when 'tianjin' then money - 500
6    else money * 2
7    end
8    A,site
9 from FARE;
复制代码

10、算排名

表格自我连接（self join），然后将结果依序列出，算出每一行之前（包括那一行本身）有多少行数

例：

复制代码 
1 create table SCORE (name char(10),score int(4));
2 
3 insert into SCORE values('zhangsan',95);
4 insert into SCORE values('lisi',96);
5 insert into SCORE values('wangwu',75);
6 insert into SCORE values('zhaoliu',80);
7 insert into SCORE values('qianqi',90);
8 select * from SCORE;
复制代码
1 select A1.name,A1.score,count(A2.score) rank from   SCORE A1,SCORE A2 where A1.score < A2.score OR (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc;

解释：

当A1的score字段值小于A2的score字段值、或者两表score字段值相等并且name字段值相等时，从A1和A2表中 查询A1的name字段值、A1的score字段值、和A2的score字段的非空值 rank是别名 ，并为A1的name字段分组，A1的score字段降序排序

1 A1.score为75时，A2.score可为75、80、90、95、96 则 count(A2.score)为5
2 A1.score为80时，A2.score可为80、90、95、96 则 count(A2.score)为4
3 A1.score为90时，A2.score可为90、95、96 则 count(A2.score)为3
4 A1.score为95时，A2.score可为95、96 则 count(A2.score)为2
5 A1.score为96时，A2.score可为96 则 count(A2.score)为1
复制代码 
 1 这样再显示A1的name字段分组，A1的score字段降序排序 ，rank字段
 2 则为下表所示
 3 
 4 +----------+-------+------+
 5 | name     | score | rank |
 6 +----------+-------+------+
 7 | lisi     |    96 |    1 |
 8 | zhangsan |    95 |    2 |
 9 | qianqi   |    90 |    3 |
10 | zhaoliu  |    80 |    4 |
11 | wangwu   |    75 |    5 |
12 +----------+-------+------+
13 
14  统计Sales栏位的值是比自己本身的值笑得以及Sales栏位得Name栏位都相同的数量，比如zhangsan为6+1=7
复制代码

11、算中位数

例：

1 2 3 select * from (select A1.name,A1.score,count(A2.score) rank from SCORE A1,SCORE A2 where A1.score < A2.score OR (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc) A3 <br>where A3.rank = (select (count(*)+1) DIV 2 from SCORE);   select score mid from (select A1.name,A1.score,count(A2.score) rank from SCORE A1,SCORE A2 where A1.score < A2.score OR (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc) A3 <br>where A3.rank = (select (count(*)+1) DIV 2 from SCORE);

- 每个派生表必须有自己的别名，所以别名A3必须要有
- DIV是在MySQL中算出商的方式

12、算累积总计

表格自我连结（Self Join），然后将结果依序列出，算出每一行之前（包含哪一行本身）的总合
例：

1 select A1.*,sum(A2.score) sum_socore from SCORE A1,SCORE A2 where A1.score < A2.score or(A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc;

13、算总合百分比

例：

1 select A1.*,A1.score/(select sum(score) from SCORE) z_sum from SCORE A1,SCORE A2 where A1.score < A2.score or (A1.score=A2.score and A1.name=A2.name) group by A1.name;

select sum(socre) from SCORE 是为了算出字段值总合，然后每一行一一除以总合，算出每行的总合百分比。

14、算累计总合百分比

例:

1 2 3 select A1.name,A1.score,sum(A2.score),sum(A2.score)/(select sum(score) from SCORE) Z from SCORE A1,SCORE A2 where A1.score < A2.score or (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc;   select A1.name,A1.score,sum(A2.score),TRUNCATE(sum(A2.score)/(select sum(score) from SCORE),2) ||'%' Z from SCORE A1,SCORE A2<br>where A1.score < A2.score or (A1.score=A2.score and A11.name=A2.name) group by A1.name order by A1.score desc;

15、空值（null）和无值（’’）的区别

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

例：

复制代码 
1 create table SITE(site varchar(20));
2 insert into SITE values('nanjing');
3 insert into SITE values('beijing');
4 insert into SITE values('');
5 insert into SITE values('taijin');
6 insert into SITE values();
7 insert into SITE values('');
8 select * from SITE;
复制代码 
1 select length(site) from SITE;
2 select * from SITE where site is NULL;
3 select * from SITE where site is not NULL;
4 select * from SITE where site ='';
5 select * from SITE where site <> '';

16、正则表达式(与Shell部分一样)

 

 语法：SELECT 字段 FROM 表名 WHERE 字段 REGEXP 匹配模式

例：

1 2 3 select * from FARE where site regexp '^[n]'; select * from FARE where site regexp '[n]'; select * from FARE where site regexp 'jin|jing';

17、存储过程（与Shell函数差不多,代码的复用）
存储过程是一组为了完成特定功能的SQL语句集合

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

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

复制代码 
 1 DELIMITER !!                    #将语句的结束符号从分号；临时修改，以防出问题，可以自定义
 2 CREATE PROCEDURE XXX()      #创建存储过程，过程名自定义,（）可带参数
 3      BEGIN                   #过程体以关键字BEGIN开始
 4      select * from xxx;      #过程体语句
 5      END!!                    #过程体以关键字END结尾
 6 DELIMITER ;                     #将语句的结束符号恢复为分号
 7 
 8 call XXX;                      #调用存储过程
 9 
10 ====查看存储过程====
11 show create procedure [数据库.]储存过程名;       #查看某个储存过程的具体信息
12 show create procedure XXX;
13 show procedure status [like '%XXX%'] \G
复制代码

例：

复制代码 
1 DELIMITER !!
2 CREATE PROCEDURE KIND1()
3 BEGIN
4     select * from FARE;
5 END!!
6 
7 DELIMITER ;
8 CALL KIND1;
复制代码
1 2 show create procedure train_ticket.KIND1\G show procedure status like '%KIND1%'\G

存储过程的参数

 

 例：

复制代码 
1 DELIMITER !!
2 CREATE PROCEDURE KIND2(IN place char(20))
3 BEGIN
4     select * from FARE where site=place;                                                    
5 END!!         
6 
7 DELIMITER ;
8 CALL KIND2('beijing');
复制代码

(1)存储过程的条件语句

例：

复制代码 
 1 DELIMITER !!
 2 CREATE PROCEDURE KIND3(IN num int(10))
 3 BEGIN
 4 declare var int;
 5 set var=num*2;
 6 if var>=10 then
 7     update FARE set money=money+1;
 8 else 
 9     update FARE set money=money-1;
10 end if;
11 END!!
12 DELIMITER ;
复制代码
1 2 CALL KIND3(5); CALL KIND3(4);

(2)循环语句while

例：

复制代码 
 1 create table akg(id int);
 2 
 3 DELIMITER !!
 4 CREATE PROCEDURE KIND4()
 5 BEGIN
 6 declare var int;
 7 set var=0;
 8 while var<5 do
 9     insert into akg values(var);
10     set var=var+1;
11 end while;
12 END!!
13 
14 DELIMITER ;
15 CALL KIND4;
16 select * from akg;
复制代码

 

　　

　　

标签：语句,FARE,高级,site,A1,score,MySQL,A2,select
From： https://www.cnblogs.com/sj5426/p/17268771.html

相关文章

  • Mysql主从同步
    一、主从复制的重要性   日常系统业务流量的增长，一台MySQL数据库服务器已经满足不了需求了，会负载过重，容易出现查询效率慢、宕机的情况，导致用户体验差、数据的丢失。......
  • MySQL查询数据时间戳和日期的转换
    在数据库的使用中，经常需要按指定日期来查询记录，以便于统计，而在数据库中，有很多存储的是时间戳，也有的直接存日期，查询的时候可能不是那么好弄。mysql提供了两个函数：from_un......
  • Mysql问题处理分享：SQLSTATE[HY000]: General error: 126 Incorrect
    今天网站访问内容页突然报错：SQLSTATE[HY000]:Generalerror:126Incorrectkeyfilefortable'.\数据库名\qb_cms_content1.MYI';trytorepairit当mysql的临时......
  • 软考高级: 信息系统项目管理师 All In One
    软考高级:信息系统项目管理师AllInOne信息系统项目管理师(......
  • java中的循环语句
    有些程序段在某些条件下重复执行多次，称为循环结构程序。Java提供了3种循环语句实现循环结构，包括while语句、do-while语句、for语句。它们的共同点是根据给定条件来判断是否......
  • MySQL字段去除空格的简单方法
    //替换所有空格 td_water_zoning_str为表名，device_name为字段名，注意，这里只要是空格自动替换为无。  UPDATEtd_water_zoning_strSETdevice_name=replace(dev......
  • MySqL Invalid GIS data provided to function st_geometryfromtext. 解决方法
     最近需要通过geoserver显示多边形，首先需要先将数据库的数据转换为地理数据存储类型，但由于原本的信息是按text存储的，将text类型转换为geometry就需要用到ST_GeomFromText......
  • 彻底卸载MySQL
    1、添加或删除程序->卸载mysql相关应用2、删除C:\ProgramFiles与C:\ProgramFiles(x86)下的MySql文件夹，没有就不用管2、删除C:\ProgramData\MySQL文件夹（ProgramData......
  • MySQL安装 starting the server解决办法
    https://blog.csdn.net/qq_52183856/article/details/123792012主要原因：......
  • SELECT INTO 和 INSERT INTO SELECT 两种表复制语句
    Insert是T-sql中常用语句，InsertINTOtable(field1,field2,...)values(value1,value2,...)这种形式的在应用程序开发中必不可少。但我们在开发、测试过程中，经常会遇到需要......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99