SQL必知必会学习笔记

时间：2017-12-15 14:53:54 阅读：269 评论：0 收藏：0 [点我收藏+]

-----------------------------------------------------------
-- Sams Teach Yourself SQL in 10 Minutes
-- http://forta.com/books/0672336073/
-- Example table creation scripts for Microsoft SQL Server.
-----------------------------------------------------------


-------------------------
-- Create Customers table
-------------------------
CREATE TABLE Customers
(
  cust_id      char(10)  NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL 
);

--------------------------
-- Create OrderItems table
--------------------------
CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL 
);

----------------------
-- Create Orders table
----------------------
CREATE TABLE Orders
(
  order_num  int      NOT NULL ,
  order_date datetime NOT NULL ,
  cust_id    char(10) NOT NULL 
);

------------------------
-- Create Products table
------------------------
CREATE TABLE Products
(
  prod_id    char(10)      NOT NULL ,
  vend_id    char(10)      NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  varchar(1000) NULL 
);

-----------------------
-- Create Vendors table
-----------------------
CREATE TABLE Vendors
(
  vend_id      char(10) NOT NULL ,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL 
);

----------------------
-- Define primary keys
----------------------
ALTER TABLE Customers WITH NOCHECK ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (cust_id);
ALTER TABLE OrderItems WITH NOCHECK ADD CONSTRAINT PK_OrderItems PRIMARY KEY CLUSTERED (order_num, order_item);
ALTER TABLE Orders WITH NOCHECK ADD CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (order_num);
ALTER TABLE Products WITH NOCHECK ADD CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (prod_id);
ALTER TABLE Vendors WITH NOCHECK ADD CONSTRAINT PK_Vendors PRIMARY KEY CLUSTERED (vend_id);

----------------------
-- Define foreign keys
----------------------
ALTER TABLE OrderItems ADD
CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num),
CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders ADD 
CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products ADD
CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

创建表

-------------------------------------------------------------
-- Sams Teach Yourself SQL in 10 Minutes
-- http://forta.com/books/0672336073/
-- Example table population scripts for Microsoft SQL Server.
-------------------------------------------------------------


---------------------------
-- Populate Customers table
---------------------------
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(‘1000000001‘, ‘Village Toys‘, ‘200 Maple Lane‘, ‘Detroit‘, ‘MI‘, ‘44444‘, ‘USA‘, ‘John Smith‘, ‘sales@villagetoys.com‘);
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(‘1000000002‘, ‘Kids Place‘, ‘333 South Lake Drive‘, ‘Columbus‘, ‘OH‘, ‘43333‘, ‘USA‘, ‘Michelle Green‘);
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(‘1000000003‘, ‘Fun4All‘, ‘1 Sunny Place‘, ‘Muncie‘, ‘IN‘, ‘42222‘, ‘USA‘, ‘Jim Jones‘, ‘jjones@fun4all.com‘);
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(‘1000000004‘, ‘Fun4All‘, ‘829 Riverside Drive‘, ‘Phoenix‘, ‘AZ‘, ‘88888‘, ‘USA‘, ‘Denise L. Stephens‘, ‘dstephens@fun4all.com‘);
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(‘1000000005‘, ‘The Toy Store‘, ‘4545 53rd Street‘, ‘Chicago‘, ‘IL‘, ‘54545‘, ‘USA‘, ‘Kim Howard‘);

-------------------------
-- Populate Vendors table
-------------------------
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(‘BRS01‘,‘Bears R Us‘,‘123 Main Street‘,‘Bear Town‘,‘MI‘,‘44444‘, ‘USA‘);
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(‘BRE02‘,‘Bear Emporium‘,‘500 Park Street‘,‘Anytown‘,‘OH‘,‘44333‘, ‘USA‘);
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(‘DLL01‘,‘Doll House Inc.‘,‘555 High Street‘,‘Dollsville‘,‘CA‘,‘99999‘, ‘USA‘);
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(‘FRB01‘,‘Furball Inc.‘,‘1000 5th Avenue‘,‘New York‘,‘NY‘,‘11111‘, ‘USA‘);
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(‘FNG01‘,‘Fun and Games‘,‘42 Galaxy Road‘,‘London‘, NULL,‘N16 6PS‘, ‘England‘);
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(‘JTS01‘,‘Jouets et ours‘,‘1 Rue Amusement‘,‘Paris‘, NULL,‘45678‘, ‘France‘);

--------------------------
-- Populate Products table
--------------------------
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘BR01‘, ‘BRS01‘, ‘8 inch teddy bear‘, 5.99, ‘8 inch teddy bear, comes with cap and jacket‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘BR02‘, ‘BRS01‘, ‘12 inch teddy bear‘, 8.99, ‘12 inch teddy bear, comes with cap and jacket‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘BR03‘, ‘BRS01‘, ‘18 inch teddy bear‘, 11.99, ‘18 inch teddy bear, comes with cap and jacket‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘BNBG01‘, ‘DLL01‘, ‘Fish bean bag toy‘, 3.49, ‘Fish bean bag toy, complete with bean bag worms with which to feed it‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘BNBG02‘, ‘DLL01‘, ‘Bird bean bag toy‘, 3.49, ‘Bird bean bag toy, eggs are not included‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘BNBG03‘, ‘DLL01‘, ‘Rabbit bean bag toy‘, 3.49, ‘Rabbit bean bag toy, comes with bean bag carrots‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘RGAN01‘, ‘DLL01‘, ‘Raggedy Ann‘, 4.99, ‘18 inch Raggedy Ann doll‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘RYL01‘, ‘FNG01‘, ‘King doll‘, 9.49, ‘12 inch king doll with royal garments and crown‘);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES(‘RYL02‘, ‘FNG01‘, ‘Queen doll‘, 9.49, ‘12 inch queen doll with royal garments and crown‘);

------------------------
-- Populate Orders table
------------------------
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, ‘2012-05-01‘, ‘1000000001‘);
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, ‘2012-01-12‘, ‘1000000003‘);
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, ‘2012-01-30‘, ‘1000000004‘);
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, ‘2012-02-03‘, ‘1000000005‘);
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, ‘2012-02-08‘, ‘1000000001‘);

----------------------------
-- Populate OrderItems table
----------------------------
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, ‘BR01‘, 100, 5.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, ‘BR03‘, 100, 10.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, ‘BR01‘, 20, 5.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 2, ‘BR02‘, 10, 8.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 3, ‘BR03‘, 10, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, ‘BR03‘, 50, 11.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 2, ‘BNBG01‘, 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 3, ‘BNBG02‘, 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 4, ‘BNBG03‘, 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 5, ‘RGAN01‘, 50, 4.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, ‘RGAN01‘, 5, 4.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 2, ‘BR03‘, 5, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 3, ‘BNBG01‘, 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 4, ‘BNBG02‘, 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 5, ‘BNBG03‘, 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, ‘BNBG01‘, 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, ‘BNBG02‘, 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, ‘BNBG03‘, 250, 2.49);

插入数据

2.5 select

SELECT       要返回的列或表达式    是
FROM          从中检索数据的表        仅在从表选择数据时使用
WHERE        行级过滤                      否
GROUP BY 分组说明                      仅在按组计算聚集时使用
HAVING        组级过滤                      否
ORDER BY 输出排序顺序               否

SELECT DISTINCT vend_id, prod_price --会列出二列数据不相同每行不同的数据
FROM Products;

3.1排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;   --排序 先排prod_price顺序,然后在其基础上排prod_name

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 3, 2;  --先排第3列,在其基础上排第2列

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY  prod_name DESC,prod_price DESC; --DESC是反转,先排前面的,再排后面的

4.1where

排序ORDER BY应该在where之后

where的字段不是一定要在select里出现的

‘‘单引号用来限定字符串,与数值

<>	!=	不等于注:Access支持<>不支持!=
!		不(非) 不可单用,用于组合
>=		大于等于
BETWEEN		在指定的两个值之间
IS NULL		为NULL值 (NULL表示无值的意思)

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10; --在二者之间

SELECT cust_name FROM CUSTOMERS
WHERE cust_email IS NULL; --显示其列cust_email为空的 cust_name

5.1逻辑操作符

and 满足二个子句

or 满足其中一个

SELECT prod_name, prod_price FROM Products
WHERE vend_id = ‘DLL01‘ OR vend_id = ‘BRS01‘ AND prod_price >= 10;--SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符
--等价于vend_id = ‘DLL01‘ OR (vend_id = ‘BRS01‘ AND prod_price >= 10)

所以如果想先OR 应该在or二个条件加括号

IN操作符指定条件范围 (注in比or操作更快)

SELECT prod_name, prod_price FROM Products
WHERE vend_id IN ( ‘DLL01‘, ‘BRS01‘ ) ORDER BY prod_name;--显示包含了‘DLL01‘, ‘BRS01‘的

NOT操作符排除

SELECT prod_name FROM Products
WHERE NOT vend_id = ‘DLL01‘ ORDER BY prod_name;--除去‘DLL01‘ 的所有都显示出来

6.1 LIKE操作符

多个通配符% 任何字符出现任意次数 (注 Access需要使用*) 不匹配null

SELECT prod_id, prod_name FROM Products 
WHERE prod_name LIKE ‘Fish%‘;  --开头为Fish的

单个通配符_ 匹配单个字符 (注 Access需要使用?) 不匹配null 语法和%一样

[]通配符匹配一个字符,内容为方括号内的

SELECT cust_contact FROM Customers
WHERE cust_contact LIKE ‘[JM]%‘  --匹配J或M开头的cust_contact

7.2计算字段

拼接字段

SELECT vend_name + ‘ (‘ + vend_country + ‘)‘ FROM Vendors ORDER BY vend_name;--拼接vend_name与end_country字段

RTRIM()删除右边的空格 LTRIM()删除左边的空格 trim()删除左右二边的空格

SELECT RTRIM(vend_name) + ‘ (‘ + RTRIM(vend_country) + ‘)‘ FROM Vendors ORDER BY vend_name;--删除右边的空格再合并

AS 显示别名

SELECT vend_name  AS vend_title FROM Vendors --用vend_title别名显示结果

执行算术运算

SELECT prod_id, quantity, item_price, 
quantity*item_price AS expanded_price --把查询到的quantity与item_price字段的结果相乘用expanded_price显示
FROM OrderItems  WHERE order_num = 20008;

8.1函数

函数	说明		函数	说明
LEFT(字段名,2)	返回字符串左边的2个字符	相反	RIGHT(字段名,2)	返回字符串右边的2个字符
LOWER() Access使用LCASE()	将字符串转换为小写		UPPER() Access使用UCASE()	将字符串转换为大写
LTRIM()	去掉字符串左边的空格		RTRIM()	去掉字符串右边的空格
Soundex()	返回字符串的SOUNDEX值主要用于英语读法相似的(略过)		length()	返回字符串的长度

日期处理函数

SELECT order_num,order_date FROM Orders
WHERE DATEPART(yy, order_date) = 2012; --表示字段order_date的年份为2012年的
--DATEPART(M, order_date)如果是M则表示月份是5月,d为日,H为时,mm为分,ss为秒

数值处理函数

ABS()	绝对值	PI()	圆周率	SQRT()	平方根	EXP()	指数值
COS()	角度余弦	SIN()	角度正弦	TAN()	角度正切

9.1聚集函数

AVG()	平均值	COUNT()	计数(共几行)	MAX()	最大值	MIN()	最小值
SUM()	总计(和运算)

9.3组合聚集函数

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM Products;

10.2分组与过滤

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num     --这里按order_num分组
HAVING COUNT(*) >= 3   --这里给分组过滤,显示分组大于3的
ORDER BY items, order_num;   --排序方式,可以理解为对结果的排序处理

11 使用子查询

先得到子句的结果再去查询 (注意嵌套子查询只能单列(多列将返回错误))

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
                  FROM Order
                  WHERE order_num IN (SELECT order_num
                                      FROM OrderItems
                                      WHERE prod_id = ‘RGAN01‘));

先得到父句的结果再去查询子句

SELECT cust_name, 
       cust_state,
       (SELECT COUNT(*) 
        FROM Orders 
        WHERE Orders.cust_id = Customers.cust_id) AS orders --这里的子句用结果中的Customers.cust_id为条件去查询(只能返回一个)
FROM Customers 
ORDER BY cust_name;

12 联结

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;  --内部相同的联结,不相同的不显示

多表联结,则使用and

SELECT cust_name, cust_contact FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num AND prod_id = ‘RGAN01‘;

自联结

找Jim Jones的公司的所有记录

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name   --找c1表内与c2结果列名cust_name一样的的记录
 AND c2.cust_contact = ‘Jim Jones‘; --先找到jim Jones那一行,得到c2表的一行

外连接

左联接 LEFT OUTER JOIN

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders ---以左边的表为基础显示结果
 ON Customers.cust_id = Orders.cust_id;

右联接 RIGHT OUTER JOIN

SELECT Customers.cust_id, Orders.order_num
FROM Customers RIGHT OUTER JOIN Orders --右边表为基础显示结果
 ON Orders.cust_id = Customers.cust_id;

全联接 FULL OUTER JOIN

SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers --显示比二个表为基础的结果
ON Orders.cust_id = Customers.cust_id;

左连接并分组计算

SELECT Customers.cust_id,
       COUNT(Orders.order_num) AS num_ord
FROM Customers LEFT OUTER JOIN Orders
 ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

组合查询UNION (二个结果同一行会被自动取消) (合并结果,二个查询结果的字段要一致,类型能隐式转换)

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN (‘IL‘,‘IN‘,‘MI‘)
UNION -- 这里改成UNION ALL则显示二个查询的集合,不会自动取消同一行
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = ‘Fun4All‘;

15 数据插入

插入检索出来的数据

INSERT INTO Customers
(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
SELECT --从CustNew检索出的结果插入到Customers表中,插入时主要为列的顺序,非字段名
 cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country
FROM CustNew;

复制新表

SELECT * --所有列,如果只是部分,应指定列名
INTO CustCopy  --新的表名
FROM Customers;

16 更新数据

UPDATE Customers
SET cust_contact = ‘Sam Roberts‘,
    cust_email = ‘sam@toyland.com‘  --也可以直接设置为null, cust_email = NULL
WHERE cust_id = ‘1000000006‘; --更新时一定要注意过滤,不然所有行都会更新掉

删除数据注!删除前最好使用select检索结果,看where是否有错

DELETE FROM Customers
WHERE cust_id = ‘1000000006‘;

17创建表

CREATE TABLE OrderItems
(
    order_num      INTEGER          NOT NULL,  --不能为空值,不允许NULL值
    order_item     INTEGER           NULL,   --表示可以为空值
    prod_id        CHAR(10)         NOT NULL,
    quantity       INTEGER          NOT NULL      DEFAULT 1,  --默认值为1
    item_price     DECIMAL(8,2)     --省略NOT NULL时,默认可以为空l
);

GETDATE()系统时间

慎用下面命令

添加一列

ALTER TABLE Vendors
ADD vend_phone CHAR(20)

移除一列

ALTER TABLE Vendors
DROP COLUMN vend_phone;

删除表

DROP TABLE CustCopy;--可以使用关系规则防止意外删除

SQL Server用户使用sp_rename存储过程重命名表

18视图

CREATE VIEW CustomerEMailList AS  --创建视图,创建一个cust_email不为空的表,查询时和表格一样操作
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

19执行存储过程

EXECUTE AddNewProduct( ‘JTS01‘, ‘Stuffed Eiffel Tower‘,6.49,‘Plush stuffed toy with the text La Tour Eiffel in red white and blue‘ );
--执行存储过程,其参数有多个

创建存储过程

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
-- 为订单编号声明变量
DECLARE @order_num INTEGER
-- 获取当前最高订单号
SELECT @order_num=MAX(order_num)
FROM Orders
-- 确定下一个订单号
SELECT @order_num=@order_num+1
-- 插入新的秩序
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(@order_num, GETDATE(), @cust_id)
-- 返回的订单号
RETURN @order_num;

20管理事务处理

注意:书本的实例测试时,部分有问题,这里请查询网上相关资料为准

事务管理

BEGIN TRAN  --开始事务
INSERT INTO Customers(cust_id, cust_name)VALUES(‘1000000017‘, ‘Toys Emporium‘); 
INSERT INTO Customers(cust_id, cust_name)VALUES(‘1000000010‘, ‘Toys Emporium‘);
if @@ERROR <> 0 Rollback tran;  --ERROR为SQL函数,出错时ERROR不为零,意在上面的操作中有错误时回滚事务
COMMIT TRAN --这是提交事务,可以理解为,之前BEGIN TRAN之后的一系列操作还没保存!在时才会保存到源数据里
select * from Customers

事务保存点

begin Tran;  --这是TRANSACTION的缩写 在begin Tran这后总有一个ROLLBACK Tran回滚事务或commit tran执行事务,
save tran test_1; --保存点test_1
DELETE FROM CustNew where cust_id = ‘1000000002‘;
save tran test_2;  --保存点test_2
DELETE FROM CustNew where cust_id = ‘1000000003‘;
ROLLBACK Tran test_1; --回滚至保存点test_2 ,
--commit tran;  --提交事务,事务的结束,如果没设置自动结束,事务会占用资源,无法查询
select * from CustNew;

21游标

游标很少用于ASP等参考资料

DECLARE CustCursor CURSOR ----声明时未指定"local"或"global"关键字,系统默认游标是"global(全局)"的.
FOR SELECT * FROM Customers WHERE cust_email IS NULL
OPEN CustCursor  --在open时,执行查询,存储检索出的数据以供浏览和滚动.
FETCH NEXT FROM CustCursor --读取游标数据,其中的NEXT为下一行记录,详见附2,这里可以执行 单语句运行
CLOSE CustCursor; --关闭游标
Deallocate CustCursor; --释放游标
/*
附1:标准游标:Declare MyCursor Cursor For Select * From Master_Goods
    只读游标:Declare MyCusror Cursor For Select * From Master_Goods For Read Only
    可更新游标:Declare MyCusror Cursor For Select * From Master_Goods For UpDate

附2:Next表示返回结果集中当前行的下一行记录，如果第一次读取则返回第一行。默认的读取选项为Next
    Prior表示返回结果集中当前行的前一行记录，如果第一次读取则没有行返回，并且把游标置于第一行之前。
    First表示返回结果集中的第一行，并且将其作为当前行。
    Last表示返回结果集中的最后一行，并且将其作为当前行。
    Absolute　n　如果n为正数，则返回从游标头开始的第n行，并且返回行变成新的当前行。如果n为负，则返回从游标末尾开始的第n行，并且返回行为新的当前行，如果n为0，则返回当前行。
    Relative　n　如果n为正数，则返回从当前行开始的第n行，如果n为负,则返回从当前行之前的第n行，如果为0，则返回当前行。
*/

22约束

CREATE TABLE Vendors (
  vend_id         CHAR(10)       NOT NULL    PRIMARY KEY, --Primary key设置为主键
  vend_name       CHAR(50)       NOT NULL,
  )

ALTER TABLE Vendors  --修改列
ADD CONSTRAINT PRIMARY KEY (vend_id);--改为主键 使用的是Constraintd 强迫

外键

CREATE TABLE Orders
(
    order_num     INTEGER     NOT NULL PRIMARY KEY,
    order_date    DATETIME    NOT NULL,
    cust_id       CHAR(10)    NOT NULL REFERENCES Customers(cust_id) --定义外键为Customers的cust_id字段
);

检查约束

CREATE TABLE OrderItems
(
    order_num     INTEGER     NOT NULL,
    order_item    INTEGER     NOT NULL,
    prod_id       CHAR(10)    NOT NULL,
    quantity      INTEGER     NOT NULL CHECK (quantity > 0), --约束其列值必须大于0
    item_price    MONEY       NOT NULL
);

ADD CONSTRAINT CHECK (gender LIKE ‘[MF]‘) --检查列中只包含M或F

索引

CREATE INDEX prod_name_ind --建立索引
ON PRODUCTS (prod_name); --建立索引的表下的列

触发器

CREATE TRIGGER customer_state --建立索引
ON Customers --表名Customers
FOR INSERT, UPDATE  --插入与修改操作
AS
UPDATE Customers
SET cust_state = Upper(cust_state) --cust_state列
WHERE Customers.cust_id = inserted.cust_id; --插入的数据表等于源数据的cust_id列

SQL必知必会学习笔记

原文：http://www.cnblogs.com/praybb/p/7991320.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)

SQL必知必会学习笔记

2.5 select

3.1排序

4.1where

5.1逻辑操作符

and 满足二个子句

or 满足其中一个

IN操作符 指定条件范围 (注in比or操作更快)

NOT操作符 排除

6.1 LIKE操作符

7.2计算字段

8.1函数

9.1聚集函数

9.3组合聚集函数

10.2分组与过滤

11 使用子查询

先得到子句的结果再去查询 (注意嵌套子查询只能单列(多列将返回错误))

先得到父句的结果再去查询子句

12 联结

自联结

外连接

左联接 LEFT OUTER JOIN

右联接 RIGHT OUTER JOIN

全联接 FULL OUTER JOIN

组合查询UNION (二个结果同一行会被自动取消) (合并结果,二个查询结果的字段要一致,类型能隐式转换)

15 数据插入

16 更新数据

17创建表

18视图

19执行存储过程

20管理事务处理

注意:书本的实例测试时,部分有问题,这里请查询网上相关资料为准

21游标

22约束

IN操作符指定条件范围 (注in比or操作更快)

NOT操作符排除