// 授权与撤销权利 -- 受予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