SQLSERVER SQL编程集合 (part 2)

//////////////////////////////////授权与撤销权利

 

--受予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