//
授权与撤销权利
-- 受予lily对friend的查询权限,同时授予lily授予他人查询friend表的权限
GRANT SELECT ON friend TO lily WITH GRANT OPTION
/*撤销lily的grant权利并撤销由lily用grant授予其他人的select权利,但保留lily对friend的select权利 */
REVOKE GRANT OPTION FOR select ON friend FROM lily CASCADE
事务
BEGIN TRANSACTION
CREATE TABLE trans_table
(
row_number SMALLINT ,
description_info char ( 35 )
)
DELETE FROM trans_table WHERE row_number = 2
INSERT INTO trans_table VALUES ( 4 , ' Instret Row 4 ' )
SAVE TRANSACTION save_point_1
DELETE FROM trans_table WHERE row_number = 20
UPDATE trans_table SET description_info = ' All Rows Updated '
ROLLBACK TRANSACTION save_point_1
UPDATE trans_table SET description_info = ' Row 1 After ROLLBACK to 2 ' WHERE row_number = 1
COMMIT TRANSACTION
CREATE PROCEDURE SP_Insert_Row
@row_number SMALLINT ,
@decription_info CHAR ( 35 )
AS
BEGIN TRANSACTION trans_1
INSERT INTO trans_table VALUES ( @row_number , @decription_info )
COMMIT TRANSACTION trans_1
/ 约束
USE sqlstudy
GO
/*NOCHECK 取消某个约束*/
ALTER TABLE sales NOCHECK CONSTRAINT fk_se
INSERT INTO sales VALUES ( 10 , 3 , 6500 )
ALTER TABLE sales CHECK CONSTRAINT fk_se
CREATE TABLE test_employee
(
employee_num valid_empnums,
sale_num int ,
first_name char ( 20 ),
last_name char ( 20 )
CONSTRAINT pk_em PRIMART KEY (employee_num)
CONSTRAINT fk_es FOREIGN KEY (sale_num) REFERENCES test_sales(sale_id)
)
数据库定义语言ddl
CREATE DEFAULT ud_number AS 0
EXECUTE sp_bindefault @defname = ud_number, @objname = ' orders.[order_number] '
USE sqlstudy
GO
CREATE DEFAULT ud_intem_number AS 111
EXECUTE sp_bindefault @defname = ud_intem_number, @objname = ' item_master.[intem_number] '
DROP DEFAULT testdefault
DROP DEFAULT ud_intem_number
CREATE TABLE item_master
(
intem_number INTEGER ,
description VARCHAR ( 35 ) NOT NULL ,
PRIMARY KEY (intem_number)
)
CREATE TABLE orders
(
order_number INTEGER UNIQUE NOT NULL ,
item_number INTEGER NOT NULL ,
quantity SMALLINT DEFAULT 1 ,
item_cost DECIMAL ( 5 , 2 ),
customer_number INTEGER
CONSTRAINT pk_or_item PRIMARY KEY (order_number, item_number)
CONSTRAINT fk_item_di FOREIGN KEY (item_number) REFERENCES vote(id)
)
ALTER TABLE orders ADD order_owner INTEGER NOT NULL DEFAULT ' 22 ' /*IDENTITY 不能和default共用*/
ALTER TABLE orders DROP CONSTRAINT PK_orders
ALTER TABLE item_master DROP CONSTRAINT FK_test
ALTER TABLE orders DROP COLUMN intem_number
ALTER TABLE orders ALTER COLUMN item_cost FLOAT
ALTER TABLE customer ADD CONSTRAINT pk_customer PRIMARY KEY (item_number)
ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (order_owner)
DELETE FROM orders WHERE item_cost = ' 0 '
ALTER TABLE orders WITH NOCHECK /*在创建外键时WITH NOCHECK 使外键检查只对新插入的行起作用*/
ADD CONSTRAINT fk_orders FOREIGN KEY (item_number) REFERENCES customer(item_number)
EXEC sp_help customer /*查看orders的相关信息*/
CREATE TABLE employee
(
employee_id INTEGER CONSTRAINT pk_eid PRIMARY KEY ,
division SMALLINT ,
first_name VARCHAR ( 20 )
)
CREATE TABLE ##employee
(
employee_id INTEGER ,
division INTEGER ,
first_name VARCHAR ( 20 ),
CONSTRAINT pk_eid PRIMARY KEY (employee_id, division)
)
CREATE TABLE #customer
(
item_number INTEGER ,
employee_id INTEGER ,
division INTEGER ,
age INTEGER ,
address VARCHAR ( 50 ),
CONSTRAINT pk_in PRIMARY KEY (item_number),
CONSTRAINT fk_ed FOREIGN KEY (employee_id, division) REFERENCES employee(employee_id, division)
)
SELECT * FROM #customer
SELECT * FROM ##employee
ALTER TABLE customer ADD CONSTRAINT fk_employee_id FOREIGN KEY (employee_id, division) REFERENCES employee(employee_id, division)
CREATE VIEW vw_customer
AS SELECT * FROM customer
CREATE VIEW vw_customer2
AS SELECT * FROM vw_customer
DROP VIEW vw_customer2
DROP VIEW vw_customer
/ 数据库操纵语言dml
EXEC sp_dboption sqlstudy, ' SELECT INTO/BULKCOPY ' , FALSE /*TRUE 时减少日志开销*/
UPDATE customer
SET item_number = 10
WHERE age = 2
UPDATE customer
SET item_number = (item_number * 2 )
WHERE item_number = 10
UPDATE customer
SET item_number = ' 111 '
WHERE age = ( SELECT col4 FROM temptable WHERE col3 = ' 2 ' )
UPDATE empoyees SET status = ' Key Account Manager '
WHERE employee_id IN
( SELECT saleperson_id FROM customers
WHERE customer_number IN
( SELECT customer_number FROM orders
GROUP BY customer_id
HAVING SUM (ORDER_total) > 1000000 ))
UPDATE vw_customer SET age = ' 25 ' /*通过视图跟新表时,所用视图只能是针对单一表*/
WHERE employee_id = ' 5 '
SELECT * FROM customer WHERE NULL = NULL
INSERT INTO customer(item_number, employee_id, divIsion, age, address)
VALUES ( 1 , 2 , 3 , 4 , 5 )
CREATE VIEW vw_customer AS
SELECT item_number, employee_id, age
FROM customer
INSERT INTO vw_customer(item_number, age)
VALUES ( ' 7 ' , ' 3 ' )
USE sqlstudy
GO
INSERT INTO temptable(col1, col2, col3, col4, col5)
SELECT * FROM customer
SELECT * FROM customer
WHERE age = ( SELECT MAX (col4) FROM temptable)
/// 使用比较判别和组合查询
USE pubs
GO
SELECT * FROM employee
WHERE (total_sales - 25000 )
BETWEEN ( SELECT AVG (total_sales) FROM employee) -- BETWEEN a AND b (x >= a 同时 x <= b )
AND ( SELECT AVG (total_sales) * 1.2 FROM employee)
UPDATE invoices SET sales_tax = invoice_total * 0.07
WHERE ship_to_state IN ( ' nv ' , ' ca ' , ' ut ' , ' tx ' )
UPDATE invoices SET sales_tax = invoice_total * 0.07
WHERE ship_to_state NOT IN ( ' nv ' , ' ca ' , ' ut ' , ' tx ' )
SELECT first_name, last_name FROM faculty
WHERE first_name LIKE ' Jin% ' AND last_name LIKE ' %in_ '
-- 使用转义字符(任意),表示E后面的%为实际的字符%
SELECT product_code, descriptions FROM customers
WHERE discount LIKE ' %E% ' ESCAPE ' E '
SELECT product_code, descriptions FROM customers
WHERE discount LIKE ' %S_ ' ESCAPE ' S '
SELECT product_code, descriptions FROM customers
WHERE discount NOT LIKE ' %S_ ' ESCAPE ' S '
SELECT * FROM employees
WHERE bage LIKE ' [1-9][^a-zA-Z][a-zA-Z] ' -- " ^ " 符号表示不为...
USE sqlstudy
GO
-- 当子查询为一列或返回空时UNOQUE语句返回true
SELECT emp_id, first_name
FROM employee
WHERE UNIQUE ( SELECT salesperson FROM invoices
WHERE invoice_date >= ' 9/1/2000 ' AND invoice_date <= ' 9/30/2000 ' )
-- 等价下面语句
SELECT emp_id, first_name
FROM employee
WHERE ( SELECT count (salesperson) FROM invoices
WHERE invoice_date >= ' 9/1/2000 ' AND invoice_date <= ' 9/30/2000 ' ) <= 1
USE pubs
GO
SELECT type, price, COUNT (advance) AS ' Total advance '
FROM titles
WHERE pubdate <= ( GETDATE () - 365 )
GROUP BY type, price
ORDER BY ' Total advance ' , price
-- compute 只能用于总计函数sum() avg() min() max() count()
SELECT * FROM titles WHERE pubdate <= ( GETDATE () - 365 )
COMPUTE SUM (price), AVG (advance)
-- compute by 能够返回,多级的统计
SELECT type, advance
FROM titles
WHERE pubdate <= ( GETDATE () - 365 )
ORDER BY type, advance
COMPUTE SUM (advance) BY type, advance
COMPUTE SUM (advance) -- 最终统计
-- having附加条件语句
USE pubs
GO
SELECT type, price, COUNT (advance) AS ' Total advance '
FROM titles
WHERE pubdate <= ( GETDATE () - 365 )
GROUP BY type, price
HAVING price < 15
ORDER BY ' Total advance ' , price
-- NULL值得处理
USE pubs
GO
SELECT type, price, SUM (advance) AS ' Total advance '
FROM titles
WHERE pubdate <= ( GETDATE () - 365 )
GROUP BY type, price
HAVING SUM (advance) < 5000 OR SUM (advance) IS NULL
ORDER BY ' Total advance ' , price
-- 受予lily对friend的查询权限,同时授予lily授予他人查询friend表的权限
GRANT SELECT ON friend TO lily WITH GRANT OPTION
/*撤销lily的grant权利并撤销由lily用grant授予其他人的select权利,但保留lily对friend的select权利 */
REVOKE GRANT OPTION FOR select ON friend FROM lily CASCADE
事务
BEGIN TRANSACTION
CREATE TABLE trans_table
(
row_number SMALLINT ,
description_info char ( 35 )
)
DELETE FROM trans_table WHERE row_number = 2
INSERT INTO trans_table VALUES ( 4 , ' Instret Row 4 ' )
SAVE TRANSACTION save_point_1
DELETE FROM trans_table WHERE row_number = 20
UPDATE trans_table SET description_info = ' All Rows Updated '
ROLLBACK TRANSACTION save_point_1
UPDATE trans_table SET description_info = ' Row 1 After ROLLBACK to 2 ' WHERE row_number = 1
COMMIT TRANSACTION
CREATE PROCEDURE SP_Insert_Row
@row_number SMALLINT ,
@decription_info CHAR ( 35 )
AS
BEGIN TRANSACTION trans_1
INSERT INTO trans_table VALUES ( @row_number , @decription_info )
COMMIT TRANSACTION trans_1
/ 约束
USE sqlstudy
GO
/*NOCHECK 取消某个约束*/
ALTER TABLE sales NOCHECK CONSTRAINT fk_se
INSERT INTO sales VALUES ( 10 , 3 , 6500 )
ALTER TABLE sales CHECK CONSTRAINT fk_se
CREATE TABLE test_employee
(
employee_num valid_empnums,
sale_num int ,
first_name char ( 20 ),
last_name char ( 20 )
CONSTRAINT pk_em PRIMART KEY (employee_num)
CONSTRAINT fk_es FOREIGN KEY (sale_num) REFERENCES test_sales(sale_id)
)
数据库定义语言ddl
CREATE DEFAULT ud_number AS 0
EXECUTE sp_bindefault @defname = ud_number, @objname = ' orders.[order_number] '
USE sqlstudy
GO
CREATE DEFAULT ud_intem_number AS 111
EXECUTE sp_bindefault @defname = ud_intem_number, @objname = ' item_master.[intem_number] '
DROP DEFAULT testdefault
DROP DEFAULT ud_intem_number
CREATE TABLE item_master
(
intem_number INTEGER ,
description VARCHAR ( 35 ) NOT NULL ,
PRIMARY KEY (intem_number)
)
CREATE TABLE orders
(
order_number INTEGER UNIQUE NOT NULL ,
item_number INTEGER NOT NULL ,
quantity SMALLINT DEFAULT 1 ,
item_cost DECIMAL ( 5 , 2 ),
customer_number INTEGER
CONSTRAINT pk_or_item PRIMARY KEY (order_number, item_number)
CONSTRAINT fk_item_di FOREIGN KEY (item_number) REFERENCES vote(id)
)
ALTER TABLE orders ADD order_owner INTEGER NOT NULL DEFAULT ' 22 ' /*IDENTITY 不能和default共用*/
ALTER TABLE orders DROP CONSTRAINT PK_orders
ALTER TABLE item_master DROP CONSTRAINT FK_test
ALTER TABLE orders DROP COLUMN intem_number
ALTER TABLE orders ALTER COLUMN item_cost FLOAT
ALTER TABLE customer ADD CONSTRAINT pk_customer PRIMARY KEY (item_number)
ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (order_owner)
DELETE FROM orders WHERE item_cost = ' 0 '
ALTER TABLE orders WITH NOCHECK /*在创建外键时WITH NOCHECK 使外键检查只对新插入的行起作用*/
ADD CONSTRAINT fk_orders FOREIGN KEY (item_number) REFERENCES customer(item_number)
EXEC sp_help customer /*查看orders的相关信息*/
CREATE TABLE employee
(
employee_id INTEGER CONSTRAINT pk_eid PRIMARY KEY ,
division SMALLINT ,
first_name VARCHAR ( 20 )
)
CREATE TABLE ##employee
(
employee_id INTEGER ,
division INTEGER ,
first_name VARCHAR ( 20 ),
CONSTRAINT pk_eid PRIMARY KEY (employee_id, division)
)
CREATE TABLE #customer
(
item_number INTEGER ,
employee_id INTEGER ,
division INTEGER ,
age INTEGER ,
address VARCHAR ( 50 ),
CONSTRAINT pk_in PRIMARY KEY (item_number),
CONSTRAINT fk_ed FOREIGN KEY (employee_id, division) REFERENCES employee(employee_id, division)
)
SELECT * FROM #customer
SELECT * FROM ##employee
ALTER TABLE customer ADD CONSTRAINT fk_employee_id FOREIGN KEY (employee_id, division) REFERENCES employee(employee_id, division)
CREATE VIEW vw_customer
AS SELECT * FROM customer
CREATE VIEW vw_customer2
AS SELECT * FROM vw_customer
DROP VIEW vw_customer2
DROP VIEW vw_customer
/ 数据库操纵语言dml
EXEC sp_dboption sqlstudy, ' SELECT INTO/BULKCOPY ' , FALSE /*TRUE 时减少日志开销*/
UPDATE customer
SET item_number = 10
WHERE age = 2
UPDATE customer
SET item_number = (item_number * 2 )
WHERE item_number = 10
UPDATE customer
SET item_number = ' 111 '
WHERE age = ( SELECT col4 FROM temptable WHERE col3 = ' 2 ' )
UPDATE empoyees SET status = ' Key Account Manager '
WHERE employee_id IN
( SELECT saleperson_id FROM customers
WHERE customer_number IN
( SELECT customer_number FROM orders
GROUP BY customer_id
HAVING SUM (ORDER_total) > 1000000 ))
UPDATE vw_customer SET age = ' 25 ' /*通过视图跟新表时,所用视图只能是针对单一表*/
WHERE employee_id = ' 5 '
SELECT * FROM customer WHERE NULL = NULL
INSERT INTO customer(item_number, employee_id, divIsion, age, address)
VALUES ( 1 , 2 , 3 , 4 , 5 )
CREATE VIEW vw_customer AS
SELECT item_number, employee_id, age
FROM customer
INSERT INTO vw_customer(item_number, age)
VALUES ( ' 7 ' , ' 3 ' )
USE sqlstudy
GO
INSERT INTO temptable(col1, col2, col3, col4, col5)
SELECT * FROM customer
SELECT * FROM customer
WHERE age = ( SELECT MAX (col4) FROM temptable)
/// 使用比较判别和组合查询
USE pubs
GO
SELECT * FROM employee
WHERE (total_sales - 25000 )
BETWEEN ( SELECT AVG (total_sales) FROM employee) -- BETWEEN a AND b (x >= a 同时 x <= b )
AND ( SELECT AVG (total_sales) * 1.2 FROM employee)
UPDATE invoices SET sales_tax = invoice_total * 0.07
WHERE ship_to_state IN ( ' nv ' , ' ca ' , ' ut ' , ' tx ' )
UPDATE invoices SET sales_tax = invoice_total * 0.07
WHERE ship_to_state NOT IN ( ' nv ' , ' ca ' , ' ut ' , ' tx ' )
SELECT first_name, last_name FROM faculty
WHERE first_name LIKE ' Jin% ' AND last_name LIKE ' %in_ '
-- 使用转义字符(任意),表示E后面的%为实际的字符%
SELECT product_code, descriptions FROM customers
WHERE discount LIKE ' %E% ' ESCAPE ' E '
SELECT product_code, descriptions FROM customers
WHERE discount LIKE ' %S_ ' ESCAPE ' S '
SELECT product_code, descriptions FROM customers
WHERE discount NOT LIKE ' %S_ ' ESCAPE ' S '
SELECT * FROM employees
WHERE bage LIKE ' [1-9][^a-zA-Z][a-zA-Z] ' -- " ^ " 符号表示不为...
USE sqlstudy
GO
-- 当子查询为一列或返回空时UNOQUE语句返回true
SELECT emp_id, first_name
FROM employee
WHERE UNIQUE ( SELECT salesperson FROM invoices
WHERE invoice_date >= ' 9/1/2000 ' AND invoice_date <= ' 9/30/2000 ' )
-- 等价下面语句
SELECT emp_id, first_name
FROM employee
WHERE ( SELECT count (salesperson) FROM invoices
WHERE invoice_date >= ' 9/1/2000 ' AND invoice_date <= ' 9/30/2000 ' ) <= 1
USE pubs
GO
SELECT type, price, COUNT (advance) AS ' Total advance '
FROM titles
WHERE pubdate <= ( GETDATE () - 365 )
GROUP BY type, price
ORDER BY ' Total advance ' , price
-- compute 只能用于总计函数sum() avg() min() max() count()
SELECT * FROM titles WHERE pubdate <= ( GETDATE () - 365 )
COMPUTE SUM (price), AVG (advance)
-- compute by 能够返回,多级的统计
SELECT type, advance
FROM titles
WHERE pubdate <= ( GETDATE () - 365 )
ORDER BY type, advance
COMPUTE SUM (advance) BY type, advance
COMPUTE SUM (advance) -- 最终统计
-- having附加条件语句
USE pubs
GO
SELECT type, price, COUNT (advance) AS ' Total advance '
FROM titles
WHERE pubdate <= ( GETDATE () - 365 )
GROUP BY type, price
HAVING price < 15
ORDER BY ' Total advance ' , price
-- NULL值得处理
USE pubs
GO
SELECT type, price, SUM (advance) AS ' Total advance '
FROM titles
WHERE pubdate <= ( GETDATE () - 365 )
GROUP BY type, price
HAVING SUM (advance) < 5000 OR SUM (advance) IS NULL
ORDER BY ' Total advance ' , price