常用的SQL语句小结

只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称 

1.创建一张表

CREATE TABLE <表名>
（ <列名1> <数据类型> <该列所需约束>，
<列名2> <数据类型> <该列所需约束>，
<列名3> <数据类型> <该列所需约束>，
<列名4> <数据类型> <该列所需约束>，
<该表的约束1>， <该表的约束2>，……）；

案例：

(1)

CREATE TABLE Product
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));

(2)

CREATE TABLE ProductIns
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER DEFAULT 0,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));

2.删除一张表

DROP TABLE <表名>；  案例：DROP TABLE Product;

3.表定义更新（添加字段)

ALTER TABLE <表名> ADD COLUMN <列的定义>；

特定的SQL
Oracle和SQL Server中不用写COLUMN。
ALTER TABLE <表名> ADD <列名> ；
另外，在Oracle中同时添加多列的时候，可以像下面这样使用括号。
ALTER TABLE <表名> ADD （ <列名>， <列名>，……）；

案例：添加一列可以存储100位的可变长字符串的product_name_pinyin列

DB2 PostgreSQL MySQL

ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);
Oracle
ALTER TABLE Product ADD (product_name_pinyin VARCHAR2(100));
SQL Server
ALTER TABLE Product ADD product_name_pinyin VARCHAR(100);

4.表定义更新（删除字段）

 ALTER TABLE <表名> DROP COLUMN <列名>；

特定的SQL
Oracle中不用写COLUMN。
ALTER TABLE <表名> DROP <列名> ；
另外，在Oracle中同时删除多列的时候，可以像下面这样使用括号来实现。
ALTER TABLE <表名> DROP （ <列名>， <列名>，……）；

案例：删除product_name_pinyin列
SQL Server DB2 PostgreSQL MySQL
ALTER TABLE Product DROP COLUMN product_name_pinyin;
Oracle
ALTER TABLE Product DROP (product_name_pinyin); 

5.修改表的名称Poduct 变成Product（如果没有数据建议直接删除然后重建）

Oracle PostgreSQL
ALTER TABLE Poduct RENAME TO Product;
DB2
RENAME TABLE Poduct TO Product;
SQL Server
sp_rename 'Poduct', 'Product';
MySQL
RENAME TABLE Poduct to Product;

6.向Product表中插入数据 

（1）一次插入一行

INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);
案例：

INSERT INTO ProductIns (product_id, product_name, product_type,sale_price, purchase_price, regist_date)
VALUES ('0001', 'T恤衫','衣服', 1000, 500, '2009-09-20');

（2）一次插入多行（Oracle以外)

INSERT INTO ProductIns (product_id, product_name, product_type,sale_price, purchase_price, regist_date)
VALUES('0002', '打孔器','办公用品', 500, 320, '2009-09-11'),
('0003', '运动T恤','衣服', 4000, 2800, '2009-09-12'),
('0004', '菜刀','厨房用具', 3000, 2800, '2009-09-20');

特定的SQL（没用过Oracle，此处做个标记)

Oracle使用如下语法来巧妙地完成多行INSERT操作。

-- Oracle中的多行INSERT

INSERT ALL INTO ProductIns VALUES ('0002', '打孔器','办公用品',500,320, '2009-09-11')
INTO ProductIns VALUES ('0003', '运动T恤','衣服', 4000, 2800, NULL)
INTO ProductIns VALUES ('0004', '菜刀','厨房用具', 3000, 2800, '2009-09-20')
SELECT * FROM DUAL;

DUAL是Oracle特有（安装时的必选项）的一种临时表A。因此“SELECT *
FROM DUAL”部分也只是临时性的，并没有实际意义

（3）插入默认值（之前工作过一些公司不允许使用数据库表默认值，必须在业务代码里设置并注释，也不允许使用存储过程和触发器，主要是为了统一标准让代码方便维护，当然也看具体公司具体业务来定)

上面1.创建表ProductIns已经使用默认值

案例1：

INSERT INTO ProductIns (product_id, product_name, product_type,sale_price, purchase_price, regist_date)
VALUES ('0007','擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');

案例2:（不使用DEFAULT关键字，直接省略设置了默认值的字段）

INSERT INTO ProductIns (product_id, product_name, product_type, purchase_price, regist_date)
VALUES ('0007', '擦菜板', '厨房用具', 790, '2009-04-28');

 （4）从其他表复制数据到新表

-- 用来插入数据的商品复制表
CREATE TABLE ProductCopy
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));

-- 将商品表中的数据复制到商品复制表中
INSERT INTO ProductCopy(product_id, product_name, product_type,sale_price, purchase_price, regist_date)
SELECT product_id,product_name,product_type,sale_price,purchase_price, regist_date FROM Product;

上面INSERT语句的SELECT语句中，可以使用WHERE子句或者GROUP BY子句等任
何SQL语法（但使用ORDER BY子句并不会产生任何效果）。

7.删除表数据

（1）删除全部数据
DELETE FROM <表名>;  案例：DELETE FROM Product;

（2）按照条件删除部分数据

DELETE FROM Product WHERE sale_price >= 4000;

（3）删除全部数据（非标准SQL语句，但很多数据库都支持，速度比DELETE快，如果设置了主键自增的话，用TRUNCATE可以重置主键值从头开始，而（1）则保留最后值下次插入新数据+1）

TRUNCATE <表名>; 案例：TRUNCATE productins

8.更新表数据

 （1）基本语法，将登记日期全部更新为“2009-10-10”
UPDATE <表名> SET <列名> = <表达式>;  案例：UPDATE Product SET regist_date = '2009-10-10';

 （2）指定条件的UPDATE语句
UPDATE Product SET sale_price = sale_price * 10 WHERE product_type = '厨房用具';

 （3）使用NULL值来更新（只有未设置NOT NULL约束和主键约束的才能使用)

UPDATE Product SET regist_date = NULL WHERE product_id = '0008';

（4）多列更新

UPDATE Product SET sale_price = sale_price * 10,purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';

9.事务

事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务，可以对数据库中的数据更新处理的提交和取消进行管理。
● 事务处理的终止指令包括COMMIT（提交处理）和ROLLBACK（取消处理）两种。
● DBMS的事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）四种特性。通常将这四种特性的首字母结合起来，统称为ACID特性。

（1）事务的普通用法
开头语法

SQL Server、PostgreSQL
BEGIN TRANSACTION
MySQL
START TRANSACTION
Oracle、DB2
无

提交案例：

BEGIN TRANSACTION;

-- 将运动T恤的销售单价降低1000日元
UPDATE Product SET sale_price = sale_price - 1000 WHERE product_name = '运动T恤';
-- 将T恤衫的销售单价上浮1000日元
UPDATE Product SET sale_price = sale_price + 1000 WHERE product_name = 'T恤衫';
COMMIT;

回滚案例：

BEGIN TRANSACTION;
-- 将运动T恤的销售单价降低1000日元
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = '运动T恤';
-- 将T恤衫的销售单价上浮1000日元
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T恤衫';
ROLLBACK;

（2）事务处理何时开始
之前我们说过，事务并没有标准的开始指令存在，而是根据 DBMS 的不同而
不同。
实际上，几乎所有的数据库产品的事务都无需开始指令。这是因为大部分情况
下，事务在数据库连接建立时就已经悄悄开始了，并不需要用户再明确发出开始指
令。例如，使用 Oracle 时，数据库连接建立之后，第一条 SQL 语句执行的同时，
事务就已经悄悄开始了。
像这样不使用指令而悄悄开始事务的情况下，应该如何区分各个事务呢？通常
会有如下两种情况。
A 每条SQL语句就是一个事务（ 自动提交模式）
B 直到用户执行COMMIT或者ROLLBACK为止算作一个事务
通常的 DBMS 都可以选择其中任意一种模式。默认使用自动提交模式的
DBMS 有 SQL Server、 PostgreSQL 和 MySQL 等 A。该模式下的 DML 语句如下
所示，每一条语句都括在事务的开始语句和结束语句之中。
BEGIN TRANSACTION;
-- 将运动T恤的销售单价降低1000日元
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = '运动T恤';
COMMIT;
BEGIN TRANSACTION;
-- 将T恤衫的销售单价上浮1000日元
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T恤衫';
COMMIT;
在默认使用 B 模式的 Oracle 中，事务都是直到用户自己执行提交或者回滚指
令才会结束。
自动提交的情况需要特别注意的是 DELETE 语句。如果不是自动提交，即使
使用 DELETE 语句删除了数据表，也可以通过 ROLLBACK 命令取消该事务的处
理，恢复表中的数据。但这仅限于明示开始事务，或者关闭自动提交的情况。如果
不小心在自动提交模式下执行了 DELETE 操作，即使再回滚也无济于事了。这是
一个很严重的问题

所以设计数据库的时候，需仔细考虑下是否设置一个字段IsDelete，0代表未删除，1代表已经被删除，删除的逻辑就是更新IsDelete字段为1

（3）事务的ACID特性

原子性（Atomicity）
原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。例如，在之前的例子中，在事务结束时，绝对不可能出现运动 T 恤的价格下降了，而 T 恤衫的价格却没有上涨的情况。该事务的结束状态，要么是两者都执行了（COMMIT），要么是两者都未执行（ROLLBACK）。从事务中途停止的角度去考虑，就能比较容易理解原子性的重要性了。由于用户在一个事务中定义了两条 UPDATE 语句， DBMS 肯定不会只执行其中一条，否则就会对业务处理造成影响。

一致性（Consistency）
一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等。例如，设置了 NOT NULL 约束的列是不能更新为 NULL 的，试图插入违反主键约束的记录就会出错，无法执行。对事务来说，这些不合法的 SQL 会被回滚。也就是说，这些 SQL 处理会被取消，不会执行。

隔离性（Isolation）
隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。此外，在某个事务中进行的更改，在该事务结束之前，对其他事务而言是不可见的。因此，即使某个事务向表中添加了记录，在没有提交之前，其他事务也是看不到新添加的记录的。

持久性（Durability）
持久性也可以称为耐久性，指的是在事务（不论是提交还是回滚）结束后， DBMS 能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复。如果不能保证持久性，即使是正常提交结束的事务，一旦发生了系统故障，也会导致数据丢失，一切都需要从头再来。保证持久性的方法根据实现的不同而不同，其中最常见的就是将事务的执行记录保存到硬盘等存储介质中（该执行记录称为日志）。当发生故障时，可以通过日志恢复到故障发生前的状态。