sgsheg/gist:a888017d69a44b296195

## gistfile1.md

      
    Raw
  

              gistfile1.md
            
          
    Mysql基本

========

检索数据

检索单个列数据
检索多个列数据
检索所有列数据
检索不同的值

使用 DISTINCT关键字
不能部分使用DISTINCT，关键字用于所有列，不仅仅是跟在其后的列。


限制结果

输出指定的行数 SELECT name FROM user limit 5;
输出指定的行数从哪一行开始 SELECT name FROM user limit 5 offset 5;


使用注释

在一行的开始使用#，这一整行都将作为注释。
多行注释,/**/


排序检索数据

为了明确地排序用SELECT语句检索出来的数据，可使用ORDER BY子句。

ORDER BY子句取一个或多个列的名字，据此对输出进行排序。


ORDER BY子句的位置

在指定一条ORDER BY子句的事后，应该确保它是SELECT语句的最后一条子句。如果它不是最后一条子句，将会出现错误信息。


按多个列排序

要按多个列排序，简单指定列名，列名之间使用逗号分隔。


按照列位置进行排序

除了能用列名指出排序顺序外，ORDER BY还支持按照相对位置进行排序。
例子 SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;
这一技术的主要好处是不用重新输入列名，但它也有缺点。不明确地给出列名可能会造成错用列名排序。


指定排序方向

降序排序 DESC.
升序  ASC


过滤数据

使用WHERE子句

只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件。
警告：在同时使用ORDER BY和WHERE子句，应该让ORDER BY位于WHERE之后，否则将会产生错误。


WHERE子句操作符

检查单个值
不匹配检查

例子: SELECT vend_id, prod_name FROM Products WHERE vend_id <> 'DLL01'和 SELECT vend_id, prod_name FROM Products WHERE vend_id != 'DLL01'。


范围值检测

要检查某个范围的值，可以使用BETWEEN操作符。


空值检查

确定值是否为NULL，不能简单地检查是否=NULL
例子：SELECT prod_name FROM Products WHERE prod_price IS NULL


警告：通过过滤选择不包含指定值的所有行，你可能希望返回含NULL值的行。但是这做不到，因为未知（unknown）有特殊的含义，数据库不知道他们是否匹配，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。


高级过滤数据

为了进行更强的过滤控制，SQL允许给出多个WHERE子句。这些子句有两种使用方式，即以AND和OR子句的方式使用。

操作符（operator）用来联接或改变WHERE子句中的子句的关键字，也称为逻辑运算符（logical operation）


AND操作符

要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件。
例子：SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;


OR操作符

与AND操作符相反


求值顺序

允许两者结合以进行复杂、高级的过滤。
SQL在处理OR操作符前，优先处理AND操作符。解决这个问题的方法是使用圆括号来进行明确分组。
任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序。


IN操作符

IN操作符用来指定条件范围。


NOT操作法

NOT操作符只有一格功能，否定其后所跟的任何条件。


用通配符进行 过滤

like操作符

%通配符

在搜索中，百分号表示任意字符出现任意次数
例子

找出所有词Fish起头的产品：SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%'; # 执行这条语句将检索任意以Fish起头的词。
多个匹配符：SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%began bag%'; # 搜索模式'%began bag%'表示任何位置商包含文本began bag的值，不论它之前或之后出现什么字符。
通配符出现在搜索模式的中间：SELECT prod_name FROM Products WHERE prod_name LIKE 'F%y'; # 有一种情况下把通配符放在中间是有用的，比如WHERE email LIKE b%forta.com


注意NULL：子句SELECT prod_name LIKE %不会匹配产品名称为NULL的行


下划线（_）通配符

用途和%一样，但它只匹配单个字符，而不是多个字符。
例子

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear' # 注意后面的空格


注意：_总是刚好匹配一格字符，不能多也不能少，与%匹配0个不一样。


方括号（[]）通配符

方括号（[]）通配符用来指定一格字符集，它必须匹配指定位置的一个字符。


使用通配符技巧

不要过度使用通配符
在确实使用通配符时，也尽量不要把他们用在搜索模式开始处。
仔细注意通配符的位置


创建计算字段

计算字段

字段（field）：基本上与列（column）意思相同，进程互相使用


拼接字段

拼接（concatenate）:将值联接到一起（将一个值附加到另一个值）构成单个值
例子：

SELECT vend_name + '(' + vend_country + ')' FROM Vendors ORDER BY vend_name
相同效果：SELECT vend_name || '(' || vend_contry || ')' FROM Vendors ORDER BY vend_name;


使用别名

SQL支持列别名。别名（alias）是一个字段或值的替换名。别名用AS关键字赋予。
例子

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_contry) + ')' AS vend_title FROM Vendors ORDER BY vend_name;


执行算数计算

例子：

SELECT prod_id, quantity, item_price, quantity * item_price AS expand_price FROM OrderItems WHERE order_num = 20008;


使用数据处理函数

函数

可移植（portable）:所编写的代码可以在多个系统上运行


使用函数

文本处理函数

例子：SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name;


日期和时间处理函数

应用程序一般不使用日期和时间的存储格式。
例子：

SELECT order_num FROM Orders WHERE YEAR(order_date) = 2012;


数值处理函数


汇总数据

聚集函数

聚集函数（aggregate function）对某些运行的函数，计算并返回一格值。
例子：

AVG()：返回某列的平均值
COUNT(): 计算某列的行数
MAX() ：返回某列的最大值
MIN(): 返回某列的最小值
SUM(): 返回某列值值和


AVG()函数：

AVG（）通过对表中行数计算并计算其列值之和。
例子：SELECT AVG(prod_price) AS avg_price FROM Products;
AVG（）函数忽略值为NULL的行


COUNT 函数

COUNT()函数进行计数。
COUNT()函数两种使用方式

使用COUNT(*)对表中数目进行计算，不管表列中是否包含的是空值（NULL）还是非空值
使用COUNT(column)对特定列中具有值的行进行计算，忽略NULL值


MAX函数

返回指定列中的最大值
例子：SELECT MAX(prod_price) AS max_price FROM Products;
对非数值数据使用MAX()，用于文本数据时，MAX()返回按该列排序后的最后一行。


MIN函数

MIN函数与MAX函数功能相反
例子：SELECT MIN(prod_price) AS min_price FROM Products
虽然MIN()函数一般用来找出最小的数值或日期，但在用于文本数据的时候，MIN()会返回该列排序后最前面的行。


SUM函数

SUM（）函数用来返回指定列值的和（总计）。
例子：SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 2005;
NULL值，SUM函数会自动忽略NULL的值


聚集不同值

DISTINCT不能用于COUNT(*)

如果指定列名，则DISTINCT只能用于COUNT。DISTINCT不能用于COUNT(*)。类似地，DISTINCT必须使用列名，不能用于计算或表达式。


组合聚集函数

例子

SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
MAX(prod_price) AS price_avg
FROM Products


分组数据

数据分组

例子：SELECT COUNT(*) AS num_prods FROM Products WHERE vend_id = 'DLL01'


创建分组

分组是使用SELECT和GROUP BY子句。
例子：SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id


过滤分组

HAVING非常类似于WHERE。事实上，目前所有类型的WHERE都可以用HAVING来代替。唯一的差别，WHERE过滤行，而HAVING过滤分组。
过滤分组例子：

SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;


分组和排序

GROUP BY和ORDER BY完成相同工作。
ORDER BY对产生的输出排序，任意列都可以使用，但不一定需要。
一般在使用GROUP BY子句时，应该也使用ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要依赖GROUP BY排序数据。
例子：

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3


SELECT子句顺序


使用子查询

子查询

查询（query）：任何SQL语句都是查询。


使用子查询过滤

例子

SELECT cust)id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Order
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RANGE01'));


作为计算字段使用子查询

使用子查询的另一个方法是创建计算字段。
例子

SELECT cust_name,
cust_state,
(SELECT Orders
 FROM Orders
 WHERE Orders.cust_id = Customers.cust_id) AS orders
 FROM Customers
 ORDER BY cust_name;


创建和操作表

创建表

表创建基础

使用CREATE TABLE

新表的名字
表列的名字和定义，逗号分隔
有的DBMS还要求表的位置


例子

创建Products

CREATE TABLE Products
(
prod_id         CHAR(10)         NOT NULL,
prod_vend_id    CHAR(10)         NOT NULL,
prod_name       CHAR(254)        NOT NULL,
prod_price      DECIMAL(8, 2)    NOT NULL,
prod_desc       VARCHAR(1000)    NULL
)


注意：指定的表名必须不存在，否则将会报错。


使用NULL值

允许NULL值的列也允许在插入行时不给该列的值
不允许NULL值的列不接受没有列值的行
每个表列要么是NULL列，要么是NOT NULL值。
例子

CREATE TABLE Vendors
(
vend_id          CHAR(10)   NOT NULL,
vend_name        CHAR(50)   NOT NULL,
vend_city        CHAR(50)   ,
vend_zip         CHAR(10)   
);


指定默认值

SQL允许指定默认值，在插入行时如果不给出值，将会采用默认值。默认值在CREATE TABLE语句的列定义中用关键字DEFAULT指定。
例子

CREATE TABLE OrderItems
(
order_num      INTEGER         NOT NULL,
order_item     INTEGER         NOT NULL,
prod_id        CHAR(10)        NOT NULL,
quantity       INTEGER         NOT NULL  DEFAULT 1,
item_price     DECIMAL(8,2)    NOT NULL
);


更新表

更新表定义，可以使用ALTER TABLE语句。
例子

ALTER TABLE Vendors
ADD vend_phone CHAR(20);
ALTER TABLE Vendors
DROP COLUMN vend_phone


小心使用ALTER TABLE

使用ALTER TABLE要极为小心，应该在进行该动前做完整的备份。（模式和数据的备份）。数据表的更改不能撤销，如果增加了不需要的列，业务无法删除他们。


删除表

DROP TABLE CustCopy;


重新命名表

不存在严格标准，MySQL使用RENAME语句。


插入数据

数据插入

插入完整行

例子

INSERT INTO TABLE Customer
VALUES ('1004',
'Toy Land'
);


插入部分行
插入检索出数据

INSERT一般用来给表插入具有指定列的值的行。INSERT还存在另外一种形式，可以利于它将SELECT语句的结果插入表中。这就是所谓的INSERT SELECT.
例子

INSER INTO Customers（
cust_id,
cust_contact)
SELECT cust_id,
cust_contact
FROM Custnew;


从一个表复制到另一个表

有一种数据插入不使用INSERT语句。要将表的内容复制到另一个全新的表，可以使用SELECT INTO.
例子：

SELECT *
INTO CustCopy
FROM Customer;


更新和删除数据

更新数据使用UPDATE

例子

UPDATE Customer
SET cust_email = 'sgsheg@163.com'
WHERE cust_id = '11'


删除数据使用DELETE

例子

DELETE FROM Customer
WHERE cust_id = '100';


如果要删除所有的行，请使用TRUNCATE TABLE;语句


跟新和删除的指导原则

下面是很多SQL程序员使用UPDATE和DELETE时所遵循的原则

除非确实打算更新和删除每一行，否则绝不要使用不带WHERE子句的UPDATE和DELETE
保证每个表都有主键，尽可能像WHERE那样使用它。
在UPDATE或DELETE语句使用WHERE子句之前，应该先使用SELECT进行测试，确保它过滤的是正确的记录，以防止编写的WHERE子句不正确。


视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用动态检索数据的查询。

例子

SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';


创建视图

视图用CREATE VIEW来创建视图。只能用于创建不存在的视图。删除视图使用DROP VIEW,覆盖更新视图需要先删除存在的视图。
例子


CREATE VIEW ProductCustomer AS
SELECT custom_id, custom_contact, prod_id
FROM Customer, Orders, OrderItems
WHERE Customer.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;
SELECT custom, cust_content
FROM ProductCustomer
WHERE prod_id = 'RGAN01';


利用视图简化复杂的联接

一个最常见的视图作用是隐藏复杂的SQL，这通常涉及到联结。
视图大大简化了复杂的SQL语句的使用。利用视图，可一次性编写基础的SQL，然后根据多次使用。

创建不绑定特定数据的视图是一个好方法。扩展视图的范围不仅使得它能被重用，而且可能很有用。这样做不需要创建和维护多个类似视图。


用视图重新格式化检索出的数据


联结表

联结

SQL最强大的功能之一就是在 数据查询的执行中联结（join）表。联结就是利用SQL 的SELECT能执行的最重要动作。


创建联结
联结表是SQL一个最重要、最强大的特性，有效地使用联结需要对关系数据库设计的基本了解。


创建高级联结

使用表别名

例子

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_price) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;


使用不同类型的联结
使用带聚集函数的联结
使用联结和联结条件