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