SQL总结

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

SQL基础

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

SELECT

samples:

    • 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;
    • SELECT prop_name FROM products; --这是一条注释

    • /*这是一个多行 注释*/ SELECT * FROM products;

  1. /*返回订购产品RGAN01的顾客列表*/
    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` ) );

    • 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操作
  • 保护数据,给予用户特殊权限
    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. 创建事务,提交事务

    /*启动一个事务向student表中插入一个记录*/ 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必知必会

存储过程

事务处理