mysql 高级查询语句 mysql高级语法

MySQL数据库SQL语句

• 一、MySQL数据库SQL语句

• 1、别名
• 2、子查询
• 3、EXISTS
• 4、连接查询
• 5、CREATE VIEW 视图
• 6、UNION联集
• 7、交集值
• 8、无交集值
• 9、case

一、MySQL数据库SQL语句

1、别名

语法：select “表格别名” . "栏位1”[AS] 栏位别名" FROM "表格名" [AS] "表格别名";

SELECT A.Store_ Name Store, SUM (A.Sales) "Total Sales" FROM Store_ Info A GROUP BY A. Store_ Name;

SELECT A.Store_ Name Store, SUM (A.Sales) "Total Sales" FROM Store_ Info A GROUP BY A. Store_ Name;

2、子查询

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

语法: SELECT "栏位1" FROM "表格1" WHERE "栏位2" [比较运算符]
(SELECT "栏位1" FROM "表格2" WHERE "条件");


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

SELECT SUM(Sales) FROM lirun  WHERE didian IN
    -> (select didian from mendian where zuobiao='west');

SELECT SUM(A.Sales) FROM lirun A WHERE A.didian IN
    -> (SELECT didian FROM mendian B WHERE B.didian = A.didian) ;

3、EXISTS

用来测试内查询有没有产生任何结果，类似布尔值是否为真

#如果有的话，系统就会执行外查询中的SQL语句。若是没有的话，那整个SQL语句就不会产生任何结果

语法: SELECT "栏位1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件") ;


SELECT SUM(Sales) FROM  lirun  WHERE EXISTS (SELECT *FROM mendian where zuobiao='west');

4、连接查询

• inner join(内连接):只返回两个表中联结字段相等的行
• leftjoin(左连接):返回包括左表中的所有记录和右表中联结字段相等的记录
• right join (右连接):返回包括右表中的所有记录和左表中联结字段相等的记录

SELECT * FROM mendian A INNER JOIN lirun B on A.didian = B.didian ;

SELECT * FROM lirun A RIGHT JOIN mendian B on A.didian = B.didian ;

SELECT * FROM mendian A，lirun B WHERE A.didian = B. didian;

SELECT A.didian REGION, SUM(A.Sales) SALES FROM lirun A, mendian B WHERE A.didian = B. diidian GROUP BY REGION;

5、CREATE VIEW 视图

可以被当作是虚拟表或存储查询，视图跟表格的不同是，表格中有实际储存资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料

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

语法: CREATE VIEW "视图表名" AS "SELECT 语句";

CREATE VIEW V_ REGION_ SALES AS SELECT A. Region REGION, SUM (B.Sales) SALES FROM mendian A
INNER JOIN lirun B ON A.didian= B.didian GROUP BY REGION;

SELECT * FROM  shitu1;
DROP VIEW shitu1;

6、UNION联集

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

#UNION :生成结果的资料值将没有重复，且按照字段的顺序进行排序

语法: [SELECT 语句1] UNION [SELECT 语句2];

SELECT didian FROM lirun UNION SELECT didian FROM mendian;

SELECT didian FROM lirun UNION ALL SELECT didian FROM mendian;

7、交集值

取两个SQL语句结果的交集

SELECT A.didian FROM lirun A INNER JOIN mendian B ON A.didian = B.didian;

SELECT A.didian FROM lirun A INNER JOIN mendian B USING (didian) ;

• 两表没用单独重复的行，并且确实有交集的时候用

select A.dididan from
(select  didian from lirun union all select didian  from mendian) A
group by A.didian  having  count(*) > 1;

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

SELECT A. didian FROM (SELECT B.didian FROM lirun B INNER JOIN mendian C ON B.didian =C.didian) A
GROUP BY A.didian;

SELECT DISTINCT A.didian FROM lirun A INNER JOIN mendian B USING (didian) ;

8、无交集值

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

SELECT DISTINCT didian FROM lirun WHERE (didian) NOT IN (SELECT didian FROM mendian) ;

SELECT DISTINCT A.didian FROM lirun A LEFT JOIN mendian B USING (didian) WHERE B.didian IS NULL;

9、case

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

SELECT CASE ("栏位名")
           WHEN "条件1" THEN "结果1"
           WHEN "条件2" THEN "结果2" 
           [ELSE "结果N"]
            END
FROM "表名";


#"条件"可以是一个数值或是公式。ELSE子句则并不是必须的。

mysql> select didian, case didian
    -> when 'hefei' then sales * 2
    -> when 'hangzhou' then sales * 1.5
    -> else sales
    -> end
    -> "xin sales",date
    -> from lirun;