20190412 T-SQL语言二

Use xsxk;
WITH c_count(id,xb,rs)
AS (SELECT 班级,性别,count(*)
FROM XS GROUP BY 班级,性别 )

SELECT * FROM c_count

-- WITH 的意义

SELECT * INTO #Tem
FROM XS

SELECT *
FROM #Tem

SELECT *
FROM #Tem T
WHERE T.出生日期 BETWEEN '1995-02-09' AND '1995-02-19' -- OK

SELECT TT.学号, CASE WHEN 总学分>60 THEN '优秀' ElSE '不太好' END AS 级别
FROM #Tem TT

 

 

SELECT *
FROM [dbo].[Test_2019_t]

SELECT * INTO #Test
FROM [dbo].[xs]

SELECT *
FROM #Test

-- 1. Between…And

SELECT *
FROM XS
WHERE 出生日期
between '1995-02-09' and '1995-07-02'
order by 出生日期 desc

SELECT *
FROM XS
WHERE 总学分
--between 10 and 20
IN (20,21)
order by 总学分 asc

-- 2. IS NULL

SELECT *
FROM XS
WHERE 备注 IS NULL;

-- 3. TOP
SELECT TOP 10 *
FROM XS

-- 4. UNION 去重的作用 -- UNION ALL

SELECT 姓名,学号 -- 表1
FROM xs
UNION
SELECT 姓名,学号 -- 表2 链接起来-- 表三的感觉,
FROM xs3
ORDER BY 学号 ASC
-- UNION --
--表3

 

SELECT 姓名,学号 -- 表1
FROM xs
WHERE 学号='14311001'

INSERT INTO xs3(姓名,学号)
VALUES('杨天','14311001')

-- 嵌套查询

SELECT 学号 -- 表1
FROM xs3
WHERE 学号='14311001'

SELECT *
FROM XS
WHERE 学号 IN ( SELECT 学号 -- 表1
FROM xs3 ) --('','')

-- inner join
-- left join
-- right join

SELECT *
FROM XS t1
INNER JOIN XS3 t2
ON t1.学号=t2.学号

 

SELECT *
FROM XS t1
RIGHT JOIN XS3 t2
ON t1.学号=t2.学号

-- 表跟表之间的一个关联关系
use xsxk
select *
from XS3

--

 

-- CASE WHEN THEN ELSE

SELECT 学号,CASE WHEN 总学分>19 THEN '优秀' ELSE '良' END AS 成绩
FROM XS

-- UPATE

SELECT *
FROM XS3

UPDATE XS3
SET 姓名='xx' ,出生日期='1991-04-05'
WHERE 学号='14341001'

-- 更新

-- 删除

DELETE FROM XS3
WHERE 学号='14311001' -- IN () ，IN

 

-- INSERT

-- 创建视图 --命令创建

CREATE VIEW view_xs1
AS SELECT 学号,姓名 FROM XS -- 块
-- 1
--2
--3

GO

SELECT *
FROM view_xs1

-- 修改 视图 ALTER 修改

 

-- DROP

DROP VIEW [dbo].[View_SS] --修改

-- View Insert

SELECT * FROM xs

INSERT INTO view_xs1
VALUES('10000007','王二'); -- 思考,如果我的视图内部有两个以上的表,3,4 ,
-- Insert 思考

SELECT *
FROM view_xs1

UPDATE view_xs1 SET 姓名='ts'
--SELECT * FROM view_xs1
WHERE 学号 = '10000007' --保证条件正确性
SELECT * FROM view_xs1

DELETE view_xs1
WHERE 学号 = '10000007'
SELECT COUNT(*) FROM xs

 

-- 存储过程
CREATE PROCEDURE QuerT
AS
SELECT * FROM XS

CREATE PROCEDURE Quer @xh char(10)
AS
SELECT * FROM xs WHERE 学号=@xh

-- 如何使用

EXEC [dbo].[QuerT]

EXEC [dbo].[Quer] '14311001' -- 传参数

EXEC sp_helptext Quer

-- 存储过程的查看

-- sp_rename Quer,Quer_NEW --重命名

--
DROP PROCEDURE QuerT