《SQL必知必会》总结（自用）

基本概念知识

数据库基础：

1. 数据库 ：数据库是通过DBMS创建和操纵的容器；
2. 表；
3. 列；
4. 行：表中的数据是按行存储的；
5. 主键：一列或几列可以唯一标识表中的每一行；

基本语法

创建数据库和表

--创建数据库test和表stocks
--这个符号表示注释
CREATE DATABASE test;
USE  test;
CREATE TABLE stocks(TradeDate CHAR(10),
					SPY double,
                    GLD double,
					AMZN double,
                    GOOD double,
                    KPTI double,
                    GILD double,
                    MPC double);
--通过下面语句可以检查一下表stocks是否创建成功
SELECT *
FROM stocks;
--之后再使用wizard导入数据，当然也可以用wizard让workbench直接分配列和datatype
--将TradeDate中的日期从字符串改变成iso format;同样，wizard中的import 中也可以设定
UPDATE stocks SET TradeDate=str_to_date(TradeDate，‘’%m/%d/%y‘’);
--在使用直接导入datetype类型数据时，可能会出现我们不想要的时间，例如几时几分，摆脱语句如下(这里重新直接导入数据得到的表另外命名为data，只是一个例子)
 SELECT *
 FROM Date;
 ALTER TABLE Data CHANGE Date TradeDate Date;
 ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);

--定义主键
ALTER TABLE Customers ADD PRIMARY KEY (cust_id);

检索语句-SELECT

--SELECT子句语法顺序

select
from
where
group by
having
order by

--SQL逻辑查询语句执行顺序

#执行from语句
#执行on过滤
#添加外部行
#执行where过滤
#执行group by 分组
#执行having过滤
#select列表
#执行distinct子句
#执行order by子句

SELECT name 
FROM table
--限制结果为只显示5行，第一个数字是检索的行数，第二个数字是指从哪开始，本例中表示返回从第五行起的5行数据，且第一个被检索的行是第0行而不是第1行。
LIMIT 5 OFFSET 5；
--当然也可以直接写成 LIMIT 5，5（逗号之前对应offset）
--只检索特定列前可加DISTINCT

排序语句-ORDER BY

--排序直接使用ORDER BY，注意ORDER BY位于SELECT语句以及WHERE语句之后
SELECT prod_name
FROM Products
ORDER BY prod_name;
--默认为升序排序如果要使用降序排序，只需加入DESC关键字，同时DESC关键字只应用到直接位于其前面的列名
SELECT prod_name
FROM Products
ORDER BY prod_name DESC;

过滤语句-WHERE

--where提供多种过滤条件，包括组合过滤，由于WHERE优先处理AND语句，所以要在合适的地方加括号进行明确分组
SELECT prod_name,prod_price
FROM Products
WHERE (vend_id='DLL01' OR vend_id='BRS01')
AND prod_price >=10;

通配符（wildcard）-LIKE操作符
通配符搜索只能用于文本字段（字符串）
通配符%，%表示任何字符出现任意次数
通配符 _ 只匹配单个字符而不是多个字符

SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE 'Fish%'
-- %不会匹配产品名称为NULL的行

注：
1.不要过度使用通配符，如果其他操作符能够达到相同的目的，应该使用其他操作符
2.在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处，把通配符用在开始处，搜索起来是很慢的。

创建计算字段
字段（field）：
基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而字段这个术语就在计算字段这种场合下使用

1.拼接(concatenate)字段：注意不同的DBMS使用的符号不同
拼接：将值联结到一起

SELECT vend_name +'(' +vend_country +')'
FROM Vendors
ORDER BY vend_name;
--以上为SQL SERVER语句

RTRIM可以去掉右边所有的空格

SELECT RTRIM(vend_name) +'(' +RTRIM(vend_country) +')'
FROM Vendors
ORDER BY vend_name;
--以上为SQL SERVER语句
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
FROM Vendors
ORDER BY vend_name;
-- 以上为MySQL语句

大多数DBMS都支持RTRIM():去掉字符串右边的空格、LTRIM()：去掉字符串左边的空格、以及TRIM()：去掉字符串左右两边的空格

使用别名-用关键字AS就行
（别名有时称为导出列）（derived column）

使用函数处理数据：（下列为一些常见函数）
SUBSTRING(‘abcdefg’,3,2)
运行结果：cd
CONVERT()：数据类型转换
CURDATE()：取当前日期
YEAR()：取当前日期的年份
MONTH()：取当前日期的月份
注意：SQL函数是不可移植的
UPPER()：转换为大写
LOWER()：转换为小写
SOUNDEX()：相同读音检索
SQL聚集函数：
AVG()、COUNT()、MAX()、MIN()、SUM()
注意：对于COUNT()函数，如果指定列名，COUNT函数会忽略指定列为NULL的行，但如果COUNT *则不会忽略。

分组数据-GROUP BY/HAVING

GROUP BY 子句指示DBMS分组数据，然后对每个组而不是整个结果集进行聚集；
GROUP BY 子句的一些重要规定：
1、GROUP BY子句可以包含任意数目的列，因而可以对分组数据进行嵌套，更细致地进行分组；
2、除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出
GROUP BY子句和HAVING子句

SELECT cust_id,
       COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*)>=2;
-- 分组就是相当于把cust_id一样的值看成一个组进行过滤

GROUP BY子句必须放在WHERE子句之后，ORDER BY子句之前，因为WHERE过滤行，HAVING过滤分组，WHERE在数据进行分组前过滤，HAVING在数据分组后进行过滤

SELECT vend_id, count(*) as num_prods
FROM Products
WHERE prod_price>=4
GROUP BY vend_id
HAVING count(*)>=2;
--------------------------------------------------------
--step1:WHERE子句过滤所有价格大于等于4的产品
--step2:按vend_id分组数据
--step3:HAVING子句过滤计数为2或2以上的分组
--------------------------------------------------------

注意：
1.GROUP BY子句中列出的每一列都必须是检索列或有效表达式（但不能是聚集函数）
2.除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。

HAVING和WHEN：HAVING和WHERE非常类似，如果不指定GROUP BY，则大多数DBMS会同等对待它们。使用HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。

ORDER BY 与GROUP BY

ORDER BY	GROUP BY
对产生的输出排序	对行分组，但输出可能不是分组的顺序
任意列都可以使用（非选择列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式）则必须使用

注：一般在使用GROUP BY子句时，应该也要给出ORDER BY子句，这是保证数据正确排序的唯一方法，千万不要仅依赖GROUP BY排序数据。

使用子查询

SQL允许使用子查询（subquery），即嵌套在其他查询中的查询。
子查询总是从内向外处理的，虽然对于能嵌套的子查询数目没有限制，但是在实际使用时由于性能的限制，不能嵌套太多的子查询。
注：
1、作为子查询的SELECT语句只能查询单个列，企图检索多个列将返回错误。
2、虽然子查询在构造SELECT语句时极有用，但必须注意限制有歧义的列，在SELECT语句中操作多个表时可以使用完全限定列名来避免有歧义的列“表名.列名”

联结表

SQL最强大的功能之一就是能在数据查询的执行中联结（join）表。
关系表的设计就是要把信息分解成多个表，一个数据一个表。各表通过某些共同的值相互关联，关系数据库的可伸缩性远比非关系数据库要好。（可伸缩性：能够适应不断增加的工作量而不失败）

1、创建联结

select vend_name,prod_name,prod_price
from   Vendors,Products
where Vendors.vend_id = Products.vend_id
--使用where可帮助逻辑配对,当然也可以联结多个表（使用and即可）

注：笛卡尔积：由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数*第二个表中的行数。有时，返回笛卡尔积的联结也称为叉联结（cross join）

-等值联结（内联结 inner join）
FROM a
INNER JOIN b ON a.?=b.?

-外联结

select Customers.cust_id,Orders.order_num
from Customers
left outer join Orders on Customers.cust_id = Orders.cust_id

注：
1、应该总是提供联结条件，否则容易出现笛卡尔积；
2、巧妙正确使用外联结能够获得想要提取的数据。此外需要注意的是MySQL不支持full outer join。

组合查询

利用UNION操作符将多条select语句组合成一个结果集。（就是取并的一个操作）
主要有两种情况需要使用组合查询：
1、在一个查询中从不同的表返回结构数据；
2、对一个表执行多个查询，按一个查询返回数据。
注：
1、具体的DBMS对UNION能组合的最大语句数目有限制；
2、UNION必须由两条或两条以上的select语句组成，语句之间用关键词UNION分割；
3、UNION中的每个查询必须包含相同的列、表达式或聚集函数
4、列数据类型必须兼容，类型不必完全相同，但必须是DBMS可以隐含转换的类型。
5、若第一条select语句为select prod_name，第二条语句select prodname，那么查询结果返回prod_name，也就是第一个名字。
6、UNION查询结果集中会自动去除重复的行；若想保留重复的行则可以使用UNION ALL。
7、在UNION组合查询中，只能存在一条order by 子句。

插入数据

插入数据选择：
INSERT INTO 表名(需要指定列名)
VALUES(),(),…/使用select from

更新和删除数据

更新：update
删除：delete

更新

若是SQL没有撤销键（undo）要十分谨慎小心地使用update，否则再添回来很麻烦。

update Customers --要更新的表
set cust_email = ' ' --列名和它的新值
      cust_contact = ' ' --只需一条set命令
where cust_id = ; --确定要更新哪些行的过滤条件

注：
1、没有where子句，DBMS将会更新表中的所有行
2、要删除某个列的值，可以设置它为NULL（若表允许有NULL值，注意与空值的区别）

删除

delete from Customers
where cust_id = ' '

注：
1、DELETE语句从表中删除行，甚至是删除表中所有行。但是DELETE不删除表本身。
2、如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE 语句，它完成相同的工作，而速度更快（因为不记录数据的变动）

创建表和操纵表

<创建、更改、删除表的基本知识>
创建表的两种方法：
1、多数的DBMS都具有交互式创建和管理数据库表的工具（实际上也是执行SQL语句）；
2、表也可以直接用SQL语句操纵

用程序创建表，可以使用SQL的 CREATE TABLE语句。
指定默认值： DEFAULT
MySQL： DEFAULT CURRENT_DATE()

更新表：
ALTER TABLE
注：
1、使用ALTER TABLE更改表结构，必须给出下面信息：
在 alter table 之后给出要更改的表名；列出要做哪些更改

删除表：
DROP TABLE CustCopy
注：删除表是删除整个表而不是其内容

重命名表
MySQL使用 RENAME