一、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;