SQLSERVER SQL编程集合 (part 3)

查询，表达式，总计函数

USE  sqlstudy

GO

/**/ /*先进行分组再进行聚合函数的计算*/

 

SELECT  last_name, trainer,  COUNT ( * )  AS  num_trainers,  SUM (sales_amt)  AS  gross_sales,  AVG (sales_amt)    

FROM  employees, sales

WHERE  sales  >   0   AND  emp_id  =  sold_by

GROUP   BY  last_name, trainer

HAVING   AVG (sales_amt)  >   6000

 

/**/ /*不能将AS 后的名字用在group by 中，但是可以用在order by 中 如upSale*/

SELECT  emp_id, sales, cust_id, sale升值后  =   ' 升值后= ' ,  ' ckcs ' , (sales_amt  *   10 )  AS  upSale, (sales_amt  -   10   *   0.5 )  AS  testSale

FROM  sales, employees

GROUP   BY  emp_id, sales, cust_id, sales_amt

ORDER   BY  emp_id  DESC , upSale

 

SELECT  emp_id

FROM  employees

WHERE  (emp_id <   5   AND   NOT  emp_id  =   ' 2 ' )  OR  last_name  =   ' Green '   /**/ /* not <> != */

 

 

SELECT  emp_id, last_name, trainer, (sales  *   10 ) 

FROM  employees

ORDER   BY   1   ASC ,  3   DESC ,  4   DESC    /**/ /*当某列不在表中的列（无名）的列*/

/**/ /*ORDER BY emp_id ASC, trainer DESC*/

 

USE  sqlstudy

GO

SELECT  last_name,  CASE  emp_id

                        WHEN   1   THEN   ' good '

                        WHEN   2   THEN   ' so good '

                        WHEN   3   THEN   ' bad '  

                   END   AS   ' check '

FROM  employees 

WHERE  sales  >   0

 

SELECT  last_name,  CASE   WHEN  sales  =   2   THEN   ' good '

                        WHEN  emp_id  =  ( SELECT  emp_id  FROM  employees  WHERE  emp_id  =   4 )  THEN   ' bad '

                        WHEN  trainer  =   ' Greg '   THEN   ' so good '

                   END    AS   ' check sales '

FROM  employees

WHERE  emp_id  <>   5

 

USE  sqlstudy

GO

UPDATE  employees

SET  sales  =   CASE   WHEN  emp_id  =   1   THEN  sales  *   1.5

                  WHEN  emp_id  =   2   THEN  sales  *   2.5

                  WHEN  emp_id  =   3   THEN  sales  *   3.5

                  WHEN  emp_id  =   4   THEN  sales  *   4.5

                  ELSE  sales  *   5.5

         END

WHERE  emp_id  <>   5

 

SELECT  trainer,  CASE   WHEN  sales  <>   0   THEN   100 / sales

                      ELSE   ' 0 '  

                 END   AS   ' test sales '

FROM  employees

 

SELECT  trainer,  CASE   WHEN  sales  >   20   THEN  sales  -   20

                      ELSE   ' 20 '

                 END   AS   ' test sales '

FROM  employ

 

USE  sqlstudy

Go

/**/ /*nullif 在sales的值为6时返回null，否则返回sales的原值*/

SELECT  last_name , NULLIF (sales,  6 )  AS   ' test sales '

FROM  employees     

/**/ /*isnull 在sales的值为null是返回6，否则返回sales的原值*/

SELECT  last_name , ISNULL (sales,  6 )  AS   ' test sales '

FROM  employees

 

 

 

USE  sqlstudy

GO     -- coalesce(a, b, c, ..。)返回第一个不为null的值，当全为null的时候返回null值。

SELECT  last_name,  coalesce (sales, ( SELECT   MIN (sales)  FROM  employees),  0 )  AS   ' test sales '

FROM  employees

WHERE  emp_id  <>   4

 

ALTER   TABLE  employees  ADD  first_name  char ( 10 )

 

ALTER   TABLE  employees  DROP   COLUMN  total_emp 

 

UPDATE  employees  SET  total_emp  =  ( SELECT   COUNT ( DISTINCT  sales)  FROM  employees)  /**/ /*distinct 不可以和* 一起连用*/

 

SELECT   COUNT ( ' 1 ' )  FROM  employees

 

SELECT   COUNT (first_name),  COUNT (last_name),

        COUNT ( DISTINCT  (first_name  +  last_name))

FROM  employees

 

USE  sqlstudy

GO

SELECT  emp_id, trainer,  SUM (sales)  AS   ' totalsales '

FROM  employees

GROUP   BY  emp_id, trainer

WITH  ROLLUP

ORDER   BY  emp_id

 

SELECT  emp_id, trainer,  SUM (sales)  AS   ' totalsales '

FROM  employees

GROUP   BY  emp_id, trainer

WITH  CUBE

ORDER   BY  emp_id

 

SELECT   SUM (sales),  SUM ( DISTINCT  sales) 

FROM  employees

 

SELECT   AVG (sales)  AS   ' testsales ' ,  AVG ( DISTINCT  sales)  AS   ' unique testsales '

FROM  employees

WHERE  emp_id  <>   ' 10 '

 

SELECT  sales,  COUNT ( * )

FROM  employees

GROUP   BY  sales

 

USE  sqlstudy

GO

SELECT  last_name  FROM  employees  WHERE  sales  IS   NULL    /**/ /*不能通过比较运算符找到null值*/

 

SELECT  last_name  FROM  employees  WHERE  sales  =  ( SELECT  sales  FROM  employees  WHERE  last_name  =   ' ckcs ' )

 

SELECT  last_name  FROM  employees  WHERE  sales  <=  (. 5   *   4 )

 

SELECT  last_name  FROM  employees  WHERE  sales  BETWEEN   0   AND   2   /**/ /*等价于sales >=0 AND sales <=2*/

 

 

/// 多表查询创建sql视图

 

USE  sqlstudy

GO

CREATE   VIEW  vw_employee_salary  /**/ /*纵向的视图*/

(employee_id, employee_salary)   /**/ /*给view中的列去个别名*/

AS

SELECT  id, salary

FROM  employee

 

CREATE   VIEW  vw_employee_choose_name   /**/ /*横向的视图*/

AS

SELECT   *

FROM  employee

WHERE  age = 25

 

CREATE   VIEW  vw_choose_name_salary

AS

SELECT  id, name, salary

FROM  employee

WHERE  age  <>   25

 

CREATE   VIEW  vw_employee_name_sold_id

AS

SELECT  name, cust_id

FROM  employee, sales

WHERE  employee.id  =  sales.cust_id

USE  sqlstudy

GO

CREATE   VIEW  vw_sales_employee

AS

SELECT  cust_id, salary, name

FROM  employee, sales

 

UPDATE  vw_sales_employee

SET  name  =   ' text '

WHERE  cust_id  =   ' 2 '    /**/ /*会将employee中的所有行都改变？？？/*/

 

USE  sqlstudy

GO

CREATE   VIEW  vw_no_check

AS

SELECT  id, name, salary, department

FROM  employee

WHERE  department  =   ' temp '

 

 

INSERT   INTO  vw_no_check  /**/ /*通过view向基表插入行*/

VALUES ( 101 ,  ' JINLP ' ,  563 )

 

UPDATE  vw_no_check

SET  department =   ' daxue '

WHERE  id  =   ' 88 '

 

 

CREATE   VIEW  vw_test_check

AS

SELECT  id, name, salary, department

FROM  employee

WHERE  department  =   ' temp '

WITH   CHECK   OPTION

 

INSERT   INTO  vw_test_check

VALUES ( 111 ,  ' JINLP ' ,  563 ,  ' temp ' )   /**/ /*在check的约束下只能department = 'temp'下进行插入*/

 

UPDATE  vw_test_check

SET  department =   ' daxie '

WHERE  id  =   ' 111 '    /**/ /*在check的约束下不能改变department de 'temp'值下进行插入*/

 

SELECT  cust_id,  avg (sales_amp)

FROM  sales

WHERE  cust_id  =   ' 1 '

GROUP   BY  cust_id

 

USE  sqlstudy

GO

INSERT   INTO  employee_3

SELECT   *   FROM  employee_1

 

 

SELECT   *   FROM  employee_1

UNION  

SELECT   *   FROM  employee_2   /**/ /*消除重复的行(MSSqlserver 以第一个表的列作为列名)*/

 

SELECT   *   FROM  employee_1

UNION   ALL

SELECT   *   FROM  employee_2

ORDER   BY  salary ( ORDER   BY   3 ："3为结果表中的第3列")

 

SELECT   ' ckcs '   AS   ' ckcs '  ,id ,name  FROM  employee_1

UNION  

SELECT  name  +  emp_id ,emp_id ,name  FROM  employee_2

 

SELECT   *   FROM  employee_1   /**/ /*使用挂号来告诉系统执行的顺序*/

UNION  

( SELECT   *   FROM  employee_2

UNION    ALL

SELECT   *   FROM  employee_3)

 

CREATE   TABLE  use_mul_unique   /**/ /*多列的unique约束*/

(

employee_id   int ,

office        char ( 20 ),

emp_name      char ( 10 ),

SSAN          char ( 30 )  UNIQUE ,

CONSTRAINT  unique_by_test  UNIQUE (employee_id, office)

)