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)

)