SQL总结

本文作为SQL语句快速复习之用

SQL基础

1. 表具有一些特性，这些特性定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何重命名等信息，描述表的这组信息的就是所谓的模式
2. SQL中，即使不一定需要，加上分号也没有坏处
3. SQL不区分大小写，但是表名，列名和值可能有所不同

SELECT

samples:

1. • SELECT TOP 5 prop_name FROM product;（SQL Server）
   • SELECT prop_name FROM product LIMIT 5;(MySQL)
   • SELECT prop_name FROM product limit 5 OFFSET 5;

2. • SELECT prop_name FROM products; --这是一条注释

注释*/
SELECT * FROM products;```

3.  	SELECT cust_name,cust_contract
    	FROM customers
    	WHERE cust_id IN(
    		SELECT cust_id
    		FROM orders
    		WHERE order_num IN(
    			SELECT order_num
    			FROM order_items
    			WHERE prop_id=`RGAN01`
    							)
    					);
   

4. • SELECT prod_name,prod_price FROM products WHERE prop_price BETWEEN 5 AND 10;

   • SELECT custumers.cust_id,orders.order_num FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id;

     SQL查询连接有：

     • INNER JOIN内连接：只保留两表中完全匹配的结果集
     • LEFT JOIN左连接：返回左表中所有的行，即使右表中无匹配记录（左表中匹配到和没有匹配到的都返回）
     • RIGHT JOIN右连接：返回右表中所有的行，即使左表中无匹配记录
     • FULL JOIN全连接：返回左右表中匹配和未匹配的行

INSERT UPDATE DELETE

samples:

• INSERT INTO table VALUES(...);

• INSERT INTO table(c1,c2) VALUES(...);

• UPDATE customers SET cust_email='wintercolor@tech.com' WHERE cust_id='1';

• DELETE FROM customers WHERE cust_id='1';

CREATE TABLE

​```DROP TABLE IF EXISTS order_items;
CREATE TABLE order_items (
id int(10) NOT NULL AUTO_INCREMENT,
cust_name varchar(50) NOT NULL,
user_info varchar(200) DEFAULT NULL,
order_time DATETIME DEFAULT NOW(),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

### ALTER TABLE

`ALTER TABLE vendors ADD vend_phone VARCHAR(20);`

### DROP TABLE

`DROP TABLE cust_copy;`

### 视图

使用视图的目标：

- 重用SQL
- 简化复杂的SQL操作
- 保护数据，给予用户特殊权限

```     CREATE VIEW product_customers AS
 SELECT cust_name,cust_contact,prop_id
 FROM customers,orders,order_items
 WHERE customers.cust_id=order.cust_id
 AND order_items.order_name=order.order_name;

• 覆盖（或更新）视图，必须先删除之，然后再创建它。删除视图可以使用drop语句，其语法为：DROP VIEW viewname
• 视图为虚拟的表，它们包含的不是数据而是根据需要检索数据的查询

存储过程

创建：

CREATE proc | procedure procedure_name
	[{@参数数据类型} [=默认值] [output],
	 {@参数数据类型} [=默认值] [output],
	 ....
	]
as
	SQL_statements
go

• sample

  创建：

--查询存储过程
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_COUNT', N'P') IS NOT NULL
	DROP procedure PROC_SELECT_STUDENTS_COUNT;
GO
CREATE procedure PROC_SELECT_STUDENTS_COUNT
AS 
	SELECT COUNT(ID) FROM Students
GO

​ 执行：EXEC PROC_SELECT_STUDENTS_COUNT

事务处理

使用事务处理(transaction processing)确保成批SQL操作要么完全执行，要么完全不执行

1. 事务transaction:指一组SQL
2. 回退rollback:撤销指定SQL
3. 提交commit:将未存储的SQL结果写入表中
4. 保留点checkpoint:事务处理中设计的临时占位符，可以对其发布回退

注意：事务管理只对INSERT,UPDATE,DELETE

1. 创建事务，提交事务

   BEGIN TRANSACTION tran
   INSERT INTO cust_prod  values(100,'陈浩','男',19)
   COMMIT TRANSACTION tran
   

2. 回滚事务

   ```/*启动一个事务向student表中删除一个记录，然后回滚该事务*/
   BEGIN TRANSACTION tran
   DELETE cust_prod  WHERE cust_id=100
   ROLLBACK TRANSACTION tran```
   

3. 设置保存点

   BEGIN TRANSACTION mytran         --启动事务
   SELECT * FROM cust_prod
   SAVE TRANSACTION s1      --设置保存点。
   INSERT INTO cust_prod VALUE(200,'王洪','男',22)   --插入另一个学生的记录
   ROLLBACK TRANSACTION s1   --事务回滚到保存点s1
   COMMIT TRANSACTION
   GO 
   SELECT * FROM cust_prod     --陈浩插入到表中而王洪没有插入到表中
   

高级特性

• 结果集result set: SQL查询所检索出来的结果

• 游标cursor:是一个存储在DBMS上的数据库查询检索出的数据集

• 约束

  ​ vend_id CHAR(10) NOT NULL PRIMARY KEY-- <-主键

  ​ cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)-- <-外键

• 索引：排序数据，加快搜索和排序操作的速度

  CREATE INDEX prod_name_id ON products(prod_name)

• 触发器

      CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
      ON { table }   
      [ WITH <dml_trigger_option> [ ,...n ] ]  
      { FOR | AFTER }   
      { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
      AS { sql_statement  [ ; ] [ ,...n ] }  
  
      <dml_trigger_option> ::=  
    	[ NATIVE_COMPILATION ]  
    	[ SCHEMABINDING ]  
    	[ EXECUTE AS Clause ]
  

---

SQL必知必会

存储过程

事务处理

---