SQL学习笔记5 组合查询 插入/更新/删除数据 创建和操纵表

组合查询 UNION

SQL允许执行多个查询，并将结果作为一个查询结果集返回，这些组合查询通常称为并（union）或复合查询（compound query）

SELECT cust_name, cust_contact
FROM customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact
FROM customers
WHERE cust_name = 'ZJW'
ORDER BY cust_name, cust_contact

• UNION操作符来组合数条SQL查询，其必须由两条或两条以上的SELECT语句组成，语句之间之间用UNION分隔
• UNION中每个查询必须包含相同的列、表达式或聚集函数，但次序可以不同
• 每个查询对应列的数据类型必须兼容
• UNION ALL操作符与UNION类似，区别在于其返回所有匹配的行，包括重复的行，其可以完成WHERE子句完不成的工作
• 若要对查询结果集进行排序，只能有一个ORDER BY语句，且必须位于最后一条SELECT语句之后

插入数据 INSERT INTO

INSERT用来将行插入到数据表中，其可以插入完整的行、部分行或某些查询结果

INSERT INTO customers
VALUES('001',
        'ZJW',
        'NEW YORK'，
        'USA'，
        NULL，
        NULL
    )；

• INSERT INTO后面接表名，VALUES子句用于指定每一列的值
• 若某列没有值，应使用NULL（假定允许）
• 上述语句省略了列名，根据给值的顺序来赋予对应列插入值，这种方法具有极大的依赖性，一旦表中的列顺序改变，就可能出现错误，因此不建议这样使用

INSERT INTO customers(cust_id，
                      cust_name,
                      cust_city
                      cust_country,
                      cust_contact,
                      cust_email)
VALUES('001',
        'ZJW',
        'NEW YORK'，
        'USA'，
        NULL，
        NULL
    )；

• 不省略列名，使用完整的插入语句，更能避免错误
• 如果表的定义允许，可以在INSERT操作中省略某些列，省略的列必须满足以下条件之一：允许NULL值或具有默认值，不满足条件会出现错误

插入检索的数据

INSERT INTO customers(cust_id，
                      cust_name,
                      cust_city
                      cust_country,
                      cust_contact,
                      cust_email)
SELECT cust_id，
       cust_name,
       cust_city
       cust_country,
       cust_contact,
       cust_email
FROM custnew;

• SELECT中列出的每一列对应于customers表明后跟的每一列，根据顺序进行匹配，与列名无关
• 通常INSERT只插入一行，要插入多行需要执行多个INSERT语句，但INSERT SELECT是个例外

将一个表复制到另一个表
SELECT INTO语句将数据复制到一个新表（导出数据）

SELECT *
INTO custcopy
FROM customers；

• DB2不支持SELECT INTO

MariaDB、MySQL、Oracle、PostgreSQL、SQLite语法稍有不同

CREATE TABLE custcopy AS
SELECT *
FROM customers;

• 任何SELECT选项和子句都可以使用，包括WHERE和GROUP BY
• 不管从多少个表检索数据，数据都只能插入到一个表中

更新数据 UPDATE

UPDATE customers
SET cust_email = '123456@qq.com'，
    cust_contact = '123456789'
WHERE cust_id = '001';

• UPDATE后面接表名，SET命令用来将新值赋给被更新的列，可以更新多个列，之间用逗号分隔
• UPDATE语句中可以使用子查询，使用SELECT语句检索出的数据更新列数据
• 要删除某列，可设置它的值为NULL
• 若没有WHERE语句，则表示对表中所有行进行更新

删除数据 DELETE

DELETE FROM customers
WHERE cust_id = '001'；

• 若没有WHERE语句，则表示对表中所有行进行删除
• 若想删除表中所有行，更快的方式是TRUNCATE TABLE
• DELETE删除行而不是列，删除列请用UPDATE

多表删除

DELETE employee, employeeSkills
FROM employee, employeeSkills, department
WHERE employee.employeeID = employeeSkills.employeeID
AND employee.departmentID = department.departmentID
AND department.name='Finance'; 

使用别名

DELETE 别名 FROM 表名称 AS 别名 WHERE 列名称 = 值

创建/操纵表

创建表

CREATE TABLE products
(
    prod_id    CHAR(10)      NOT NULL,
    vend_id    CHAR(10)      NOT NULL,
    prod_price DECIMAL(8,2)  NOT NULL,
    quantity   INTEGER       NOT NULL   DEFAULT 1,
    prod_desc  VARCHAR(1000) NULL
);

• 新表的名字在CREATE TABLE后面给出
• 表列的名字和定义，用逗号分隔
• 若不允许该列为空值，则需指定NOT NULL，省略或NULL都表明允许该列为空值
• DEFAULT关键字指定默认值

更新表

ALTER TABLE vendors
ADD vend_phone CHAR(20);

• 许多DBMS允许增加列，但不允许删除或更改表中的列
• 许多DBMS**允许重命名**表中的列
• 不同DBMS对表修改的命令语法可能不同

删除表

DROP TABLE custcopy

• 删除整个表没有确认，谨慎操作
• 若该表是某个关系的组成部分时，删除可能不会成功

重命名表

RENAME TABLE old_table_name TO new_table_name;

• 不同的DBMS重命名命令可能不同