联结(join)表
- 相同的数据在一张表内出现多次不是一件好事,此因素是关系型数据库设计的基础。关系表的设计就是要保证把信息分解成多个表。
- FROM···WHERE···可换为FROM···INNER JOIN···ON···
- 联结多个表,考虑的仅仅是几张表是通过哪些键进行联结的,写出联结后,便可直接取所有表里想要的信息了。
SELECT vend_name,prod_name,prod_price
FROM Vendors,Products
WHERE Vendors.vend_id = Products.vend_id; //限定性语句
FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;
SELECT cust_name,cust_contact
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Oders.order_num
AND prod_id = 'RGAN01' //此语句目的在于取订购了RGAN01产品的用户信息
自联结、自然联结、外部联结
- 自联结就是对同一张表进行联结。
- 自然联结。排除返回列的多次出现的情况,使每个列只返回一次。
- 外部联结用于联结那些在相关表中没有关联行的行。比如列出客户订单数,包括没下订单的客户。FROM···LEFT OUTER JOIN···ON···(LEFT指的是从OUTER JOIN左边的表选择所有行);也可用*= 表示左联结。
//自联结
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 Customers.cust_id,Orders.order_num
FROM Customers,Orders // FROM Customers RIGHT OUTER JOIN Orders ON Orders.cust_id=Orders.cust_id
WHERE Customers.cust_id *= Orders.order_num
组合查询
- UNION,可以用于连接两条SELECT语句,给出所有筛选出的数据项。
- 使用UNION中,每个查询必须包含相同的列、表达式或聚合函数。
- UNION默认会自动去除重复行。也可使用UNION ALL 来返回所有。
- 可以使用ORDER BY进行排序,不过只能使用一次,并且应将其放置在最后一个SELECT后。
插入数据INSERT
- 可以插入完整的行、行的一部分、以及某些查询的结果
- INSERT (···) SELECT (···) FROM ···。此语句可以先用SELECT筛选出另一张表里的数据,再插入到现表中。
- SELECT INTO,此语句是导入一张新表
INSERT IN TO Customers
VALUES ('1006','Toy Land',···,NULL)
//基本语法,插入完整的行。更可靠的做法是在Customers表名后,列出插入的项名,可以不按表的实际顺序。
SELECT * INTO CustCopy FROM Customers //创建出了一个名为CustCopy的新表
CREATE TABLE CustCopy AS SELECT * FROM Customers //MySQL、Oracle专用
更新及删除数据
- 更新数据: UPDATE 。不要省略WHERE语句,不然更新整张表。允许一次更新多个值,用逗号隔开,最后一行不用逗号。
- 删除数据: DELETE 。同样不要省略WHERE。后果自负。注意,DELETE不删除表本身。
- 通常习惯是,在使用UPDATE或者DELETE使用WHERE子句之前,应该先用SELECT进行测试,保证它过滤的是正确的记录,以防止WHERE子句不正确。
UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com' //若要清空,可设置为NULL
WHERE cust_id = '100006';
DELETE FROM Customers
WHERE cust_id = '100006';
表的创建及操纵
- CREATE TABLE。使用CREATE TABLE来创建,创建时,要保证指定的表名不存在。
- 创建时,每列取值可以给出 NULL 或者 NOT NULL 。后者就是指,在插入或者更新新行时,该列必须有值,否则返回错误或者插入失败。
- 注意,NULL是默认值,也就是不指定的话,默认就当做NULL,即可以插入空值。
- ALTER TABLE。此语句可以更新表的结构,比如给列改名、增加列等。
- DROP TABLE。可以删除表。
CREATE TABLE Orders
{
order_num INTEGER NOT NULL,
order_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL,
cust_city CHAR(50) NULL,
quantity INTEGER , DEFAULT 1,
};
ALTER TABLE Vendors
ADD vend_phone CHAR(20); //ADD可以换成其他,如DROP COLUMN
视图
- 视图不包含数据,它包含的是一个查询条件。它仅仅是用来查看存储在别处的数据的一种设施。
- 如果视图里的规则过于复杂,则检索性能可能会下降的很厉害。
- CREATE VIEW,可以用来简化语句,可供多个地方调用,更简单。
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;
//此语句创建一个名为ProductCustomers的视图,它联结三个表,以返回已订购了任意产品的所有客户的列表
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01'; //使用视图进行检索
存储过程
- 它实质就是为以后的重复使用而保存的一条或者多条SQL语句的集合,可视为批处理文件,但其作用不仅限于批处理。
- EXCUTE,接受存储过程名以及需要传递给它的任何参数。
CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
DECLARE @order_num INTEGER
SELECT @order_num=MAX(order_num)
FROM Orders
SELECT @order_num=@order_num+1
INSERT INTO Orders(order_num,order_date,cust_id)
VALUES(@order_num,GETDATE(),@cust_id)
RETURN @order_num; //创建一个存储过程,它只用接收一个参数,即cust_id