关闭

SQLSERVER SQL编程集合 (part 2)

612人阅读 评论(0) 收藏 举报
//////////////////////////////////授权与撤销权利

 

--受予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(1036500)

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(35NOT 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

 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:68118次
    • 积分:864
    • 等级:
    • 排名:千里之外
    • 原创:9篇
    • 转载:17篇
    • 译文:1篇
    • 评论:16条
    文章分类
    最新评论