Sql

数据库

数据库是一个以某种有组织的方式存储的数据集合(通常是一个文件或一组文件)

表

表是一种结构化的文件，可以用来存储某种特定类型的数据，表在数据库中是唯一的

模式

表具有一些特性，这些特性定义了表在数据库中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等，描述表的这组信息就是模式(scheme).

列

表中的一个字段，所有表都是由一个或多个列组成的

数据类型

每个表列都有相应的数据类型，它限制该列中存储的数据

行

表中的一个记录有时候也被称为一条记录

主键

表中的每一行都应该有一列（或几列）可以唯一标识自己，每个表都应该有主键，方便更新和删除表中特定行

需要满足以下要求中的表中任何列都可以作为主键:

任意两行不具有相同的主键值
每一行必须具有一个主键值
主键列中的值不允许修改和更新
主键值不能重用(如果某行从表中删除，它的主键不能赋值给新增的行)

sql

Strctured Query Language 结构化查询语言，是一门和数据库沟通的语言。不同于一般的程序语言，sql中只有很少的关键字

sql 优点:

sql不是某个特定数据库供应商专有的语言
sql简单易学
灵活使用sql可以进行非常复杂和高级的数据操作

玩具经销商使用的订单录入系统

vendors表

vendors表存储销售产品的供应商

DROP TABLE IF EXISTS Vendors;
CREATE TABLE Vendors
(
  vend_id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  vend_name VARCHAR(255) NOT NULL,
  vend_country VARCHAR(255) NOT NULL ,
  vend_provice VARCHAR(255) NOT NULL ,
  vend_city VARCHAR(255) NOT NULL ,
  vend_address VARCHAR(255) NOT NULL ,
  vend_zip VARCHAR(255) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Products表

Products表中包含所有产品，每行代表一个

DROP TABLE IF EXISTS Products;
CREATE TABLE Products
(
prod_id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT,
vend_id INT(12) NOT NULL ,
prod_name VARCHAR(255) NOT NULL ,
prod_price VARCHAR(255) NOT NULL ,
prod_desc VARCHAR(255) NOT NULL,
FOREIGN KEY (vend_id)
      REFERENCES Vendors(vend_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Customers表

Customers表中存储所有顾客信息

DROP TABLE IF EXISTS Customers;
CREATE TABLE Customers
(
cust_id INT(12) NOT NULL  PRIMARY KEY AUTO_INCREMENT,
cust_name VARCHAR(255) NOT NULL ,
cust_address VARCHAR(255) NOT NULL ,
cust_city VARCHAR(255) NOT NULL ,
cust_provice VARCHAR(255) NOT NULL ,
cust_zip VARCHAR(255) NOT NULL ,
cust_county VARCHAR(255) NOT NULL ,
cust_contact VARCHAR(255) NOT NULL ,
cust_email VARCHAR(255) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Orders表

Orders 表存储顾客订单

DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders
(
order_num INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
cust_id INT(12) NOT NULL,
FOREIGN KEY (cust_id)
      REFERENCES Customers(cust_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

OrderItems表

OrderItems表中存储每个订单的实际物品

DROP TABLE IF EXISTS OrderItems;
CREATE TABLE OrderItems
(
order_item INT(12) NOT NULL PRIMARY KEY  AUTO_INCREMENT,
order_num INT(12) NOT NULL REFERENCES Orders(order_num),
prod_id INT(12) NOT NULL REFERENCES Products(prod_id),
quantity INT(12) NOT NULL ,
item_price FLOAT(12) NOT NULL ,
FOREIGN KEY (order_num)
  REFERENCES Orders(order_num),
FOREIGN KEY (prod_id)
  REFERENCES Products(prod_id)
);

检索数据

使用换行书写的方式更易于调试
以分号结束每条语句
不区分大小写，建议大写关键字

SELECT prod_name , prod_id, prod_city
FROM Products;

不显示重复值, DISTINCT关键字作用于所有列

SELECT DISTINCT vend_id
FROM Products;

排列数据

SELECT prod_name
FROM Products
ORDER BY prod_name DESC, prod_price
LIMIT 5 OFFSET 5 ;
-- 从第5行起的5行数据
-- 先按照名字排序，然后 *相同*(严格相同) 名字中再按价格排序
-- 对输出进行排序  ( ORDER BY 子句要保障是最后一条子句 )
-- DESC    -- 默认按照升序排列，指定DESC按照降序排列
-- 注意先后顺序

如果想在多个列降序，必须对每一列指定DESC关键字 DESC 是 DESCENDING 的缩写

过滤数据

SELECT prod_name,prod_price
FROM Products
WHERE prod_desc IS NULL;
-- WHERE prod_price = 3.49;
-- WHERE prod_price BETWEEN 5 AND 10;
-- WHERE prod_name = 'apple iphone5'
-- 如果将值与字符串类型的列进行比较，就需要加上引号

操作符	说明
=	等于
< >	不等于
!=	不等于
<	小于
<=	小于等于
!<	不小于
>	大于
>=	大于等于
!>	不大于
BETWEEN	在指定的值之间
IS NULL	为NULL值

高级过滤数据

组合where子句

SELECT prod_name,prod_price
FROM products
WHERE prod_name = 'iphone'
AND prod_price < 120;

组合时需要注意优先级，AND比OR拥有更高的优先级,可以使用括号限制优先级

SELECT prod_name,prod_price
FROM products
WHERE
( prod_name = 'iphone' OR prod_desc =  'DELL' )
AND prod_price < 120;

SELECT prod_name,prod_price
FROM products
WHERE vend_name IN ('dell01','brs01');

使用IN的优点

合法选项多时，IN操作符更清楚直观
与其他AND和OR操作符组合使用IN时，求值顺序容易管理
IN比一组OR执行更快
IN可以包含其他SELECT语句

SELECT prod_name,prod_price
FROM products
WHERE NOT vend_name IN ('dell01','brs01');

用通配符进行过滤

SELECT  *
FROM products
WHERE prod_name
LIKE '%Fish%';
-- %代表Fish之后的任意字符
-- LIKE 'Fish%';
-- 包含Fish
-- LIKE 'b%@qq.com'
--  找特定格式的电子邮件地址
-- LIKE '_____@qq.com'
--  _代表一个字符

不要过度使用通配符
确实需要使用通配符的场景，不要把它用在搜索模式的开始处
注意通配符的位置

计算字段

存储在数据库表中的数据不一定是应用程序所需要的格式

需要公司名和公司地址，但这两各信息在不同的表中
城市省和邮政编码存储在不同的列中，但前台程序需要一个合起来的格式
列数据是大小写混合的，但前台需要大写数据
订单表存储物品的价格和数量，但没有总价
需要根据表数据计算总数，平均数

sql语句内完成的许多转换工作和格式化工作可以直接在客户端完成，不过在服务器上会更快

-- AS关键字可以创建一个别名 有时候也叫导出列
SELECT  vend_name + ' (' + vend_country + ')' AS vend_title
FROM Vendors
ORDER BY vend_name

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

使用函数处理数据

sql中的函数面临兼容性问题,如果使用函数，要做详细的注释

sql中的函数类型

处理文本字符串
处理数值数据
处理日期和时间
系统函数

-- 常用字符处理函数  UPPER LEFT LENGTH LEN LOWER LTRIM RTRIM SOUNDEX
SELECT vend_name , UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name

SOUNDEX 函数为一个字符串生成语音格式的字符串,例子如下

SELECT cust_name,cust_contact
FROM Customers
WHERE cust_contact = 'Michael Green';

数据库中存储的是发音相似但拼写错误的字符串 ‘Michealle Green’,使用SOUNDEX可以查找到

SELECT cust_name,cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

SELECT order_num
FROM Orders
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= order_date;
-- 查询30天以内的订单

Mysql常用数值处理函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

以上5各函数都可以搭配DISTINCT参数

SELECT AVG(prod_price) AS avg_price
FROM Products

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

SELECT COUNT(*) AS num_counts
FROM Products;

SELECT MIN(prod_price) AS min_price
FROM Products;

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;

可以组合

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

分组数据

计算每一种商品各有多少个

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id

GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，进行更细致的分组
如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总
GROUP BY子句中列出每一列都必须是检索列或有效的表达式（不能是聚集函数），如果在SELECT中使用表达式，则必须在GROUP BY 子句中指定相同的表达式。不能使用别名
除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUPBY 子句中给出
GROUP BY 子句必须出现在 WHERE 子句之前，ORDER BY 子句之后

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

WHERE 在数据分组前进行过滤, HAVING 在数据分组后进行过滤。

过去12个月内具有两个以上订单的用户

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

一般在使用GROUP BY子句时，应该也给出ORDER BY子句。保障数据正确排序

SELECT 子句顺序

子句	说明	是否必须
SELECT	要返回的列或表达式	是
FROM	要检索的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否

使用子查询

SELECT cust_id
FROM Orders
WHERE order_num IN (
  SELECT order_num
  FROM OrderItems
  WHERE prod_id = 'rgan01'
);

作为子查询的SELECT语句只能查询单个列.
注意子查询的性能

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

联结表

同一供应商生产多种物品供应商名，地址，联系方法和商品会分开存储

理由：

对每个产品重复供应商信息浪费时间空间
供应商信息发生变化，只需修改一次
不要让相同的数据出现多次

关系型数据库的可伸缩性远比非关系型数据库好

把数据分开存储之后检索需要使用联结

SELECT vend_name,prod_name,prod_price
FROM Vendors,Products
WHERE Vendors.vend_id = Products.vend_id;


SELECT vend_name,prod_name,prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

创建高级联结

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

以上使用为内联结或等值联结接下来我们来看其他三种联结自联结自然联结和外联结

使用自联结替换一次子查询

SELECT c1.cust_id,c1.cust_name,c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

标准的联结返回所有数据，相同的列甚至出现多次。自然联结排除列多次出现的问题

SELECT C.* , O.order_num
FROM Customers AS C , Orders AS O
WHERE C.cust_id = O.cust_di

许多联结将一个表中的行和另一个表中的行相关联，但是有时候需要包含没有关联行的那些行，这个时候需要使用外联结

SELECT Customers.cust_id,Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id

带聚集函数的联结，比如检索所有的顾客及每个顾客所下的订单数

SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id

组合查询

多数SQL查询只包含从一个或多个表中返回数据的单条SELECT语句。但是，SQL允许执行多个查询，并将结果作为一个结果集返回。这些组合查询通常称为并（union）或复合查询（compound query)

应用场景

在一个查询中从不同的表返回数据结构
对一个表执行多个查询，按一个查询返回数据

SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name,cust_contact;

UNION 必须由两条或两条以上的SELECT语句组成
UNION 中每个查询必须包含相同的列，表达式或聚集函数
列数据类型必须兼容
UNION 会自动去掉重复的行

对于特别复杂的使用场景，使用union能精简查询

插入数据

INSERT INTO Customers VALUES(
  '1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL
);

编写依赖于特定列次序的SQL语句是很不安全的

INSERT INTO Customers( cust_ id, cust_ contact ) VALUES( '10', '123456789');

可以省略列

如果该列定义为允许NULL值
如果定义中给出默认值

插入检索出的数据

INSERT INTO Customers(cust_id,cust_contact,cust_email)
SELECT cust_id,cust_contact,cust_email
FROM CustNew;

更新和删除数据

使用更新和删除前，应该保证自己有足够的安全权限

UPDATE Customers
SET cust_email = 'kim@qq.com', cust_contact = 'Sam rose'
WHERE cust_id = '100000005';

DELETE FROM Customers
WHERE cust_id = '100000006'

DELETE 删除整行，要删除指定的列，请使用UPDATE语句

创建和操纵表

创建一张表

CREATE TABLE Products
(
  prod_id char(10) not null,
  vend_id char(10) not null,
  prod_name char(255) not null,
  prod_price decimal(8,2) not null,
  prod_desc text(1000) null
);

创建带默认值的表

CREATE TABLE OrderItems
(
  order_num integer not null,
  order_item integer not null,
  prod_id char(10) not null,
  quantity interger not null  default 1,
  ---------
  ctime time current_date(),
  ---------
)

更新表结构

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

ALTER TABLE Vendors
DROP COLUMN vend_phone;

删除表

DROP TABLE CustCopy

使用视图

我们可以把一些需要复杂的sql联结查询才能得到的数据集创建成一张虚拟的表，这种行为在sql中被称为创建视图.

使用视图的好处：

重用sql
简化复杂的sql操作
使用表的一部分而不是整个表
保护数据
更改数据的格式和表示

视图的限制

视图名必须唯一
可以创建的视图数目没有限制

CREATE VIEW ProductCustomers AS
SELECT cust_name,cust_contact,prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;


SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

使用存储过程

为了处理订单，必须核对以保证库存中有相应的物品
如果物品中有库存，需要预定，不再出售给别的人，并且减少物品数据以反映正确的库存量
库存中没有的物品需要订购，这需要与供应商进行某种交互
关于哪些物品入库和哪些物品退订，需要通知相应的顾客

存储过程就是为以后使用而保存的一条或多条SQL语句。可将其视为批处理文件

通过把处理封装在一个易用的单元中，可以简化复杂的操作
由于不要求反复建立一系列处理步骤，可以保证数据的一致性，如果所有开发人员和应用程序都使用同一存储过程，则使用的代码都是相同的
简化对变动的管理
存储过程通常以编译过的形式存储，性能更高

mysql中的存储过程

事务

使用事务处理（transaction processing) ,通过确保成批的sql操作要么完全执行，要么完全不执行，来维护数据库的完整性。

给系统添加订单的过程如下：

检查数据库中是否存在相应的顾客，如果不存在，添加他；
检索顾客的ID；
在Orders表中添加一行，它与顾客ID相关联
检索Orders表中赋予新订单的 ID
为订购的每个物品在OrderItems表中添加一行，通过检索出来的ID把它与Orders表相关联

假设某种数据库故障，这个过程无法实现。

如果故障发生在添加顾客之后，添加Orders表之前，则不会有什么问题。某些顾客没有订单是完全合法的。重新执行此过程，所插入的顾客记录将被检索和使用。可以有效的从出故障的地方开始执行此过程。

但是，如果故障发生在插入Orders行之后，添加OrderItems行之前，结果就是数据库中存在不完整的订单。而我们都不知道

解决这种问题需要使用事务

事务(transaction) 指一组SQL语句
回退(rollback)指撤销指定SQL语句的过程
提交(commit) 指将未存储的sql语句结果写入数据库表
保留点(savepoint)指事务处理中设置的临时占位符（placeholder),可以对它发布回退（与回退整个事务处理不同）

事务处理用来管理INSERT UPDATE DELETE 语句。不能回退SELECT语句也不能回退 CREATE DROP

START TRANSACTION

DELETE FROM Orders;
ROLLBACK;

START TRANSACTION
DELETE OrderItems WHERE order_num = 12345;
DELETE Orders WHERE order_num = 12345;
COMMIT TRANSACTION

-- 保留点
SAVEPOINT delete1;

BEGIN TRANSACTION
INSERT INTO Customers(cust_id,cust_name)
VALUES('10000','Toys Emporium');
SAVE TRANSACTION StartOrder;
INSERT INTO Orders(order_num,order_date,cust_id)
VALUES (20100,'2011/12/1','1000000010');
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION

使用游标

有时候需要在检索出的行中前进或者后退一行或多行，这就是游标的用途。游标是一个存储在服务器上数据库查询，它不是一条select语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

高级sql特性

约束

关系数据库存储分解为多个表的数据，每个表存储相应的数据。利用键来建立从一个表到另一个表的引用（由此产生术语引用完整性）。正确的进行关系数据库设计，需要一种方法保证只在表中插入合法数据。例如，如果Orders表存储订单信息，OrdersItems表存储订单详细内容，应该保证OrderItems中引用的任何订单ID都存在于Orders中，类似的，在Orders表中引用的任意顾客必须存在于Customs表中。

虽然可以在插入新行时进行检查，但是最好不要这样做，原因如下：

如果在客户端层面上实施数据完整性规则，则每个客户端都要被迫实施这些规则，一定会有一些客户端不实施这些规则
在执行update和delete操作时，也必须实施这些规则。
执行客户端检查非常耗时。

约束管理如何插入或处理数据库数据规则

添加一个主键

CREATE TABLE Vendors
(
  vend_id CHAR(10) NOT NULL PRIMARY KEY,
  vend_name CHAR(50) NOT NULL
)

ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY(vend_id);

外键

外键是表中的一列，其值必须列在另一个表的主键中，外键是保证引用完整性的及其重要的部分。

CREATE TABLE Orders
(
  order_num INTEGER NOT NULL PRIMARY KEY,
  order_date DATETIME NOT NULL ,
  cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)
)

以上示例中 cust_id的值必须是Customers表中cust_id的值。

ALTER TABLE Orders
ADD CONSTRAINT 
FOREIGN KEY (cust_id) REFERENCES Customers(cust_id);

外键有助于防止意外删除

唯一约束

唯一约束用来保证一列中的数据是唯一的。他们类似于主键，但存在以下重要区别

表可以包含多个唯一约束，但每个表只允许一个主键
唯一约束列可包含NULL值
唯一约束列可修改或更新
唯一约束列可重复使用
与主键不一样，唯一约束不能用来定义外键

唯一约束可以使用 UNIQUE关键字在表定义中定义

CREATE TABLE  user
{
  u_id interger not null primary key,
  card_num interger not null unique
}

检查约束

检查约束用来保证一列中的数据满足一组指定的条件。检查约束的常见用途

检查最大最小值。例如，防止0个物品的订单（即使0是合法的数）
指定范围。例如，保证发货日期大于等于今天的日期，但不超过今天起一年后的日期
只允许特定的值。例如，在性别字段中只允许M或F

CREATE TABLE OrderItems
(
  order_num Interger not null,
  order_item interger not null,
  prod_id char(10) not null,
  quantity interger  not null check(quantity > 0),
  item_price money nto null
);
ADD CONSTAINT CHECK (gender LIKE '[MF]');

索引

索引用来排序数据以加快搜索和排序操作的速度。

主键不用定义索引

可以在一个或多个列上定义索引，使数据库保存其内容的一个排过序的列表。在定义了索引之后，数据库以使用书的索引类似的方法使用它。数据库搜索排过序的索引，找出匹配的位置，然后检索这些行。

索引改善检索操作的性能，但降级了数据插入，修改和删除的性能。在执行这些操作时，数据库必须动态的更新索引
索引数据可能要占用大量的存储空间
并非所有数据都合适做索引。取值不多的数据不如具有更多可能值的数据（如姓或者名），能通过索引得到那么多的好处
索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引

CREATE INDEX prod_name_ind
ON Products (prod_name);

索引必须唯一命名。

触发器

触发器可以与特定表上的Insert update delete操作相关联