SQL note5

两种情况使用组合查询：
在一个查询中从不同的表返回结构数据
对一个表执行多个查询，按一个查询返回数据

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’;

SELECT cust_name,cust_contact,cust_email
-> FROM Customers
-> WHERE cust_state IN (‘IL’,’IN’,’MI’)
-> OR cust_name = ‘Fun4All’;

UNION ALL

=======================================================
INSERT INTO Customers
-> VALUES(‘1000000006’,’Toy Land’,’123 Any Street’,’New York’,’NY’,’1111’,’USA’,NULL,NULL);

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

INSERT INTO Customers
-> (cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
-> VALUES
-> (‘1000000007’,’toy land’,’123 any street’,’new york’,’ny’,’1111’,’usa’);

INSERT通常只插入一行。
INSERT SELECT 是个例外，它可以用一条INSERT插入多行。

INSERT INTO Customers
-> (cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
-> SELECT
-> cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country
-> FROM
-> CustNew;

复制表
CREATE TABLE CustCopy AS
-> SELECT * FROM Customers;

==========================================================

-> UPDATE Customers
-> SET cust_contact = 'Sam Roberts',
->     cust_email = 'sam@toyland.com'
-> WHERE cust_id = '1000000006';


-> DELETE FROM Customers
-> WHERE cust_id = '1000000006';

->TRUNCATE TABLE CustCopy;

=========================================================
DROP TABLE CustCopy;

-> CREATE TABLE TableTest 
-> (
->     num INTEGER NOT NULL,
->     id  CHAR(10)NOT NULL,
->     qua INTEGER NOT NULL DEFAULT 1
-> );

-> ALTER TABLE TableTest
-> ADD addtest CHAR(20);