1、第一節(jié)

1.1什么是數(shù)據(jù)庫(kù)

? 用來(lái)存儲(chǔ)數(shù)據(jù)的倉(cāng)庫(kù) ，就像存放文件的保險(xiǎn)柜。

1.2如何創(chuàng)建數(shù)據(jù)庫(kù)

拿SQL server舉例

兩種方式：

? 1) 右鍵數(shù)據(jù)庫(kù)-》創(chuàng)建數(shù)據(jù)庫(kù)

2）SQL 語(yǔ)句創(chuàng)建

新建查詢鏈接-》輸入以下語(yǔ)句-》選中執(zhí)行

--CREATE DATABASE [數(shù)據(jù)庫(kù)名] --例：創(chuàng)建學(xué)校數(shù)據(jù)庫(kù) CREATE DATABASE SCHOOL;

1.3如何創(chuàng)建表

1）選擇數(shù)據(jù)庫(kù)-》右鍵創(chuàng)建表

設(shè)置主鍵-》右鍵設(shè)計(jì)-》右鍵所選對(duì)象（例如：id）-》設(shè)置主鍵

2）語(yǔ)句創(chuàng)建，新建查詢-》輸入語(yǔ)句

CREATE TABLE [表名] ([字段] int PRIMARY KEY,--設(shè)置主鍵[字段] varchar(20) NOT NULL,--不為空[字段] int ,[字段] int DEFAULT [默認(rèn)值],--設(shè)置默認(rèn)值 );CREATE TABLE Student (id int PRIMARY KEY,name varchar(20) NOT NULL,age int ,sex int DEFAULT 1,phone int );

2、第二節(jié)(查詢)

2.1檢索（SELECT）

查找單列數(shù)據(jù)

SELECT name FROM Student;

查找多列數(shù)據(jù)

SELECT id,name,sex FROM Student;

查找全部數(shù)據(jù)

SELECT * FROM Student

2.2檢索數(shù)據(jù)排序

默認(rèn)排序是根據(jù)數(shù)據(jù)插入順序輸出

排序 ORDER BY(默認(rèn)升序)

? ASC升序

? DESC降序

單個(gè)列排序

--根據(jù)id升序排序 SELECT name FROM Student ORDER BY id;

多個(gè)列排序

根據(jù)需求進(jìn)行排序

SELECT id,name,age FROM Student ORDER BY name,age;

按列位置進(jìn)行排序

根據(jù)第二列第三列排序（輸出結(jié)果同上）

SELECT id,name,age FROM Student ORDER BY 2,3;

指定方向排序

--根據(jù)id降序排列 SELECT name FROM Student ORDER BY id DESC;--根據(jù)id升序排列 SELECT name FROM Student ORDER BY id ASC;

2.3過(guò)濾查詢

使用WHERE語(yǔ)句查詢

--查找姓名為張三的信息 SELECT name,age,sex FROM Student WHERE name = '張三';

操作符

檢查單個(gè)值

--查找年齡大于等于18的信息 SELECT name,age,sex FROM Student WHERE age>=18;

不匹配查詢

--查找性別不是男(1)的信息【查找所有女生(0)】 SELECT name,age,sex FROM Student WHERE sex!=1;

范圍值查詢

--查找年齡在6-18的信息 SELECT name,age,sex FROM Student WHERE age BETWEEN 6 AND 18;

空值查詢

--查找手機(jī)號(hào)為空的信息 SELECT name,age,sex FROM Student WHERE phone IS NULL;

2.4高級(jí)過(guò)濾查詢

1、組合查詢

AND

--查找年齡大于十八的男生 SELECT name,age,sex FROM Student WHERE age >18 AND sex = 1;

OR

--查找年齡大于十八或者是男生的信息 SELECT name,age,sex FROM Student WHERE age >18 OR sex = 1;

計(jì)算次序

AND 、OR進(jìn)行組合

--年齡大于18的id大于等于1或者是男生的信息 SELECT name,age,sex FROM Student WHERE (id>=1 OR sex = 1) AND age >18

2、IN操作符

用來(lái)指定范圍

--查找年齡在6-18的信息并根據(jù)age升序排序 SELECT name,age,sex FROM Student WHERE age IN (6,18) ORDER BY age;

3、NOT操作符

--查找女生并按年齡升序排序 SELECT name,age,sex FROM Student WHERE NOT sex=1 ORDER BY age;

2.5通配符進(jìn)行過(guò)濾查詢

1、LIKE操作符

通配符：用來(lái)匹配值的一部分的特殊字符

搜索模式：有字面值、通配符或兩者進(jìn)行組合構(gòu)成的搜索條件

1.1百分號(hào)(%)通配符

%表示任意字符出現(xiàn)的任意次數(shù)

--查找姓氏為李的所有信息 SELECT * FROM Student WHERE name LIKE '李%';

1.2下劃線(_)通配符

_ 通配符匹配任意單個(gè)字符

--查找姓氏為李姓名為兩位的所有信息 SELECT * FROM Student WHERE name LIKE '李_';

1.3方括號(hào)([ ])通配符

[]通配符用來(lái)指定一個(gè)字符集，必須匹配指定的位置

--查找姓氏為李和王的所有信息 SELECT * FROM Student WHERE name LIKE '[李王]%';

^ 脫字節(jié)

用來(lái)否定條件

--查找姓氏不為李和王的所有信息 SELECT * FROM Student WHERE name LIKE '[^李王]%';

3、第三節(jié)(計(jì)算字段)

3.1計(jì)算字段

例如查找公司名稱、同時(shí)顯示公司名稱和公司地址，但是這兩個(gè)信息存放在兩列中，我們需要的字段表中并沒(méi)有直接顯示，因此我們需要直接從數(shù)據(jù)庫(kù)中檢索出轉(zhuǎn)換、計(jì)算或格式化過(guò)的數(shù)據(jù)，這樣的數(shù)據(jù)被稱為計(jì)算字段

3.2拼接字段

將兩個(gè)值進(jìn)行拼接成單個(gè)值

--查找姓名年齡 并將其拼接成一列 SELECT name + '('+age+')' FROM Student

格式化數(shù)據(jù)時(shí)可使用函數(shù)(例：去除串尾空格，使用函數(shù) RTRIM() )

--查找姓名年齡 并將其拼接成一列 SELECT RTRIM(name) + '('+RTRIM(age)+')' FROM Student

使用別名

--查找姓名年齡 并將其拼接成一列 SELECT RTRIM(name) + '('+RTRIM(age)+')' AS NAME FROM Student

3.3執(zhí)行算術(shù)計(jì)算

--查找信息并添加sum列（男生年齡+1，女生年齡+0） SELECT name,age,sex,age+sex AS SUM FROM Student

4、第四節(jié)(函數(shù))

4.1文本處理函數(shù)

UPPER()函數(shù) 文本轉(zhuǎn)大寫(xiě)

--查找姓名并轉(zhuǎn)大寫(xiě) SELECT UPPER(name) FROM STUDENT

用法同上

SOUNDEX()函數(shù)

soundex是一個(gè)將任何文本串轉(zhuǎn)換為描述其語(yǔ)音表示的字母數(shù)字模式的算法。soundex考慮了類似的發(fā)音字符和音節(jié)，使得對(duì)字符串進(jìn)行發(fā)音比較而不是字母比較。

例如由于輸入錯(cuò)誤name列中有sam，但是實(shí)際是san，直接查找會(huì)顯示為空，使用SOUNDEX()則可以避免這樣的情況

SELECT * FROM STUDENT WHERE SOUNDEX(name) = SOUNDEX('san');

4.2日期和時(shí)間處理函數(shù)

--查找入學(xué)時(shí)間是2022的學(xué)生姓名 SELECT name FROM STUDENT WHERE DATEPART(yyyy,date) = 2022; DATEPART(datapart,date)

date 參數(shù)是合法的日期表達(dá)式。datepart 參數(shù)可以是下列的值：

datepart縮寫(xiě)

年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小時(shí)	hh
分鐘	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
納秒	ns

4.3數(shù)值處理函數(shù)

5、第五節(jié)(聚集函數(shù))

5.1 sql中的聚集函數(shù)

5.2 AVG()函數(shù)

平均值

--查找平均年齡 SELECT AVG(age) AS AVGAGE FROM STUDENT; --查找男生的平均年齡 SELECT AVG(age) AS AVGAGE FROM STUDENT WHERE sex = 1;

5.3 COUNT()函數(shù)

計(jì)算個(gè)數(shù)

--查找人數(shù) SELECT COUNT(name) AS COU FROM STUDENT; --查找男生人數(shù) SELECT COUNT(name) AS COU FROM STUDENT WHERE sex = 1;

5.4 MAX() \ MIN()函數(shù)

MAX()最大值

--查找最大年齡 SELECT MAX(age) AS AGE FROM STUDENT;

MIN()最小值

--查找最小年齡 SELECT MIN(age) AS AGE FROM STUDENT;

5.5 SUM()函數(shù)

求和

--查找年齡和 SELECT SUM(age) AS AGESUM FROM STUDENT;

5.6 聚集不同值

ALL DISTINCT

默認(rèn)是ALL 全部數(shù)據(jù)

DISTINCT 對(duì)查詢結(jié)果去重

--查找不同年齡段的個(gè)數(shù) SELECT COUNT(DISTINCT age) AS COUN FROM STUDENT;

5.7 組合聚集函數(shù)

--數(shù)據(jù)個(gè)數(shù)、最大年齡、最小年齡、平均年齡 SELECT COUNT(*) AS NUM,MAX(AGE) AS MAX,MIN(AGE) AS MIN,AVG(AGE) AS AVG FROM STUDENT;

6、第六節(jié)(分組)

GROUP BY

6.1 創(chuàng)建分組

--根據(jù)姓名分組 SELECT NAME , COUNT(*) AS NUM FROM STUDENT GROUP BY NAME;

6.2 過(guò)濾分組

通過(guò)HAVING 進(jìn)行過(guò)濾分組

HAVING 支持 WHERE 所有的通配符

--根據(jù)姓名分組并過(guò)濾出出現(xiàn)次數(shù)大于等于二的姓名和出現(xiàn)次數(shù) SELECT NAME , COUNT(*) AS NUM FROM STUDENT GROUP BY NAME HAVING COUNT(*)>=2;

6.3 分組和排序

--分組并根據(jù)NAME排序 SELECT NAME , COUNT(*) AS NUM FROM STUDENT GROUP BY NAME HAVING COUNT(*)>=2 ORDER BY ID,AGE;

7、第七節(jié)(子查詢)

7.1 利用子查詢進(jìn)行過(guò)濾

--查找成績(jī)大于等于90分的學(xué)生 SELECT NAME FROM STUDENT WHERE ID IN ( SELECT studentidFROM GradeWHERE SCORE >=90);

也可嵌套多個(gè)子查詢，但在實(shí)際開(kāi)發(fā)中考慮性能，并不會(huì)使用過(guò)多的嵌套

7.2作為計(jì)算字段使用子查詢

--查找學(xué)生姓名、年齡、總成績(jī) SELECT NAME, AGE,(SELECT SUM(SCORE)FROM GRADEWHERE grade.studentid = student.id) AS SCORE FROM STUDENT;

8、第八節(jié)(表聯(lián)結(jié))

8.1創(chuàng)建聯(lián)結(jié)

兩個(gè)表連接

--查找姓名 年齡 成績(jī) SELECT NAME,AGE,SCORE FROM STUDENT,GRADE WHERE STUDENT.ID = GRADE.STUDENTID;

8.2笛卡爾積

檢索出的行的數(shù)目 = 第一個(gè)表行數(shù)*第二個(gè)表的行數(shù)

SELECT NAME,AGE,SCORE FROM STUDENT,GRADE WHERE STUDENT.ID = GRADE.STUDENTID;

表1

idnameage

1	張三	18
2	李四	19

表2

idstudentidsubjectidscore

1	1	1	88
2	1	2	69
3	2	1	59
4	2	2	80

檢索結(jié)果

nameagescore

張三	18	88
張三	18	69
李四	19	59
李四	19	80

8.3內(nèi)連接

INNER JOIN

內(nèi)連接只匹配兩者的公共部分

--查找兩個(gè)學(xué)生表中重復(fù)的學(xué)生 SELECT * FROM STUDENT_1 A INNER JOIN STUDENT_2 B ON A.ID = B.ID;

8.4自聯(lián)結(jié)

--例：想要給張老師所帶班級(jí)的所有代課老師授予優(yōu)秀教師，我們需要先找到張老師所帶的班級(jí)，然后找到該班級(jí)的代課老師SELECT TEA_ID,TEA_NAME,TEA_PHONE FROM TEACHERS WHERE CLASS_ID = (SELECT CLASS_IDFROM TEACHERSWHERE TEA_NAME = '張老師');

8.5外連接

左連接

LEFT JOIN \ LEFT OUTER JOIN(兩者沒(méi)有區(qū)別，前者是后者簡(jiǎn)寫(xiě))

以左邊信息為主，只查詢左表有的信息。

--所有學(xué)生包含沒(méi)有考試的學(xué)生 SELECT * FROM STUDENT A LEFT JOIN GRADE B ON A.ID = B.STUDENTID;

右聯(lián)結(jié)

RIGHT JOIN\RIGHT OUTER JOIN

只匹配右表存在的信息

--查詢參加考試的學(xué)生的信息 SELECT * FROM STUDENT A RIGHT JOIN GRADE B ON A.ID = B.STUDENTID;

全聯(lián)結(jié)

FULL JOIN \ FULL OUTER JOIN

--查找所有信息 SELECT * FROM STUDENT A FULL JOIN GRADE B ON A.ID = B.STUDENTID;

8.6使用帶有聚集函數(shù)的聯(lián)結(jié)

--查找參加考試信息并統(tǒng)計(jì)人數(shù) SELECT NAME ,COUNT(NAME) AS NUM FROM STUDENT A RIGHT JOIN GRADE B ON A.ID = B.STUDENTID GROUP BY A.NAME;

8.7組合查詢

使用UNION

-- 查詢 年齡大于18的男生 SELECT ID,NAME,AGE FROM STUDENT WHERE AGE>18 UNION SELECT ID,NAME,AGE FROM STUDENT WHERE SEX = 1;

UNION會(huì)自動(dòng)去除重復(fù)的行

若要想要包含重復(fù)的行則使用UNION ALL

-- 查詢 年齡大于18的男生 SELECT ID,NAME,AGE FROM STUDENT WHERE AGE>18 UNION ALL SELECT ID,NAME,AGE FROM STUDENT WHERE SEX = 1;

對(duì)組合查詢進(jìn)行排序

-- 查詢 年齡大于18的男生,并根據(jù)id,NAME,AGE排序 SELECT ID,NAME,AGE FROM STUDENT WHERE AGE>18 UNION SELECT ID,NAME,AGE FROM STUDENT WHERE SEX = 1 GROUP BY ID,NAME,AGE;

9、第九節(jié)(插入)

9.1數(shù)據(jù)插入(INSERT)

1、插入完整行

--插入整行信息 INSERT INTO STUDENT VALUES ('6','JACK','23','1','1234567'); -- 或 -- 這樣更加安全，但相對(duì)繁瑣 INSERT INTO STUDENT(ID,NAME,AGE,SEX,PHONE ) VALUES ('6','JACK','23','1','1234567');

2、插入部分行

-- 插入id，姓名，年齡 INSERT INTO STUDENT(ID,NAME,AGE ) VALUES ('6','JACK','23');

3、插入檢索出的數(shù)據(jù)

-- 查找STUDENT插入到STUDENT_S中 INSERT INTO STUDENT_S(ID,NAME,AGE,SEX,PHONE) SELECT ID,NAME,AGE,SEX,PHONE FORM STUDENT;

9.2 從一個(gè)表復(fù)制到另一個(gè)表

SELECT * INTO STUDENT_S FROM Student;

10、更新和刪除數(shù)據(jù)

10.1 更新數(shù)據(jù)

UPDATE

--修改姓名，年齡 UPDATE STUDENT SET NAME = 'MC趙四'，AGE = '55' WHERE ID = '5';

10.2 刪除數(shù)據(jù)

DELETE

--從表中刪除一行數(shù)據(jù) DELETE FROM STUDENT WHERE ID = '8';

ALTER

--刪除列 ALTER TABLE TEACHER DROP COLUMN TEACHERID;

11、創(chuàng)建和操作表

11.1 創(chuàng)建表基礎(chǔ)

CREATE TABLE STUDENTS (S_ID INT PRIMARY KEY, --主鍵(唯一標(biāo)識(shí))NAME VARCHAR(20) NOT NULL, --(不為空)AGE INT NULL, --為空(可省略，默認(rèn)不填為空)SEX VARCAHR(10) DEFAULT '男' --設(shè)置默認(rèn)值 )

11.2約束

主鍵 PRIMARY KEY

主鍵是一種特殊的約束，用來(lái)保證一個(gè)列中的值時(shí)唯一的，并且永遠(yuǎn)不改動(dòng)。

-- 創(chuàng)建表時(shí)聲明自增主鍵 CREATE TABLE TTT (ID INT NOT NULL PRIMARY KEY IDENTITY(1001,1),-- IDENTITY(初始值，自增量)NAME VARCHAR(20) NOT NULL,AGE INT,SEX VARCHAR(10) ) -- 修改主鍵 ALTER TABLE TTT ADD PRIMARY KEY (ID);

外鍵 FOREIGN KEY

外鍵時(shí)表中的一個(gè)列，必須與另一個(gè)表的主鍵對(duì)應(yīng)。通常用于連接兩個(gè)表

-- 創(chuàng)建表時(shí)聲明外鍵 CREATE TABLE TTT (ID INT NOT NULL PRIMARY KEY IDENTITY(1001,1),-- IDENTITY(初始值，自增量)NAME VARCHAR(20) NOT NULL,AGE INT CHECK(AGE>=0 AND AGE<200),SEX VARCHAR(10) DEFAULT '男',PHONE INT NOT NULL UNIQUE,CLASSID INT REFERENCES CLASS(ID) ); -- 修改外鍵 ALTER TABLE TTT ADD CLASSID INT FOREIGN KEY(CLASSID) REFERENCES CLASS(ID);

非空 NOT NULL

使數(shù)據(jù)不能為空

NAME VARCHAR(20) NOT NULL;

默認(rèn) DEFAULT

給數(shù)據(jù)設(shè)置默認(rèn)值

SEX VARCHAR(10) DEFAULT '男';

檢查 CHECK

檢查約束用來(lái)保證這一列數(shù)據(jù)滿足一組指定的條件。

-- 年齡滿足大于等于0小于200 AGE INT CHECK(AGE>=0 AND AGE<200)

唯一 UNIQUE

約束一組數(shù)據(jù)中該列數(shù)據(jù)沒(méi)有重復(fù)、唯一。

-- 手機(jī)號(hào)唯一 PHONE INT NOT NULL UNIQUE,

11.3 更新表

ALERT

添加時(shí)可以添加約束

--添加列 ALTER TABLE STUDENT ADD CLASSID INT ; --添加列并設(shè)成外鍵 ALTER TABLE STUDENT ADD CLASSID INT FOREIGN KEY(CLASSID) REFERENCES CLASS(ID); --刪除列 ALTER TABLE TEACHER DROP COLUMN TEACHERID; --添加約束-- --添加外鍵 ALTER TABLE STUDENT ADD CONSTRAINT CLASSID FOREIGN KEY(CLASSID) REFERENCES CLASS(ID); --添加主鍵 ALTER TABLE STUDENT ADD CONSTRAINT ID PRIMARY KEY(ID); --添加默認(rèn)約束 ALTER TABLE STUDENT ADD CONSTRAINT AGE DEFAULT 1 FOR AGE;

11.4 刪除表

DROP

DROP TABLE STUDENT

11.5 重命名表

SP_RENAME 'STUDENT_S','STUDENT_S1';

12、第十二節(jié)(視圖)

12.1 什么是視圖

? 視圖是一個(gè)虛擬的表，并不存在具體的列和行。本質(zhì)上就是查詢。

例，我們想要查找某一學(xué)科及格的同學(xué)。

--查找某一學(xué)科及格的同學(xué) SELECT STUDENT.NAME,STUDENT.LASSID FROM STUDENT,GRADE,SUBJECT WHERE STUDENT.ID = GRADE.STUDENTIDAND GRADE.SUBJECTID = SUBJECT.IDAND SUBJECT.NAME = '語(yǔ)文'AND GRADE.SCORE >= 60;

任何需要這個(gè)數(shù)據(jù)的人都必須要了解相關(guān)表的結(jié)構(gòu)，并知道如何創(chuàng)建查詢和對(duì)表進(jìn)行聯(lián)結(jié)。

為了檢索別的學(xué)科及格的學(xué)生數(shù)據(jù)，必須要修改where語(yǔ)句，因此非常麻煩。

如果我們把這個(gè)查詢封裝成一個(gè)名為 sub_pass的虛擬表。這樣就可一比較簡(jiǎn)單的實(shí)現(xiàn)不同科目的查詢。

--SUB_PASS表 SELECT STUDENT.NAME,STUDENT.CLASSID,SUBJECT.name as subna FROM STUDENT,GRADE,SUBJECT WHERE STUDENT.ID = GRADE.STUDENTIDAND GRADE.SUBJECTID = SUBJECT.IDAND GRADE.SCORE >= 60;

例：

--查詢數(shù)學(xué) SELECT NAME,CLASSID FROM SUB_PASS WHERE subna = '數(shù)學(xué)';

12.2 為什么使用視圖

重用SQL語(yǔ)句

簡(jiǎn)化復(fù)雜sql操作，在編寫(xiě)查詢后，可以方便的重用它而不必知道他的基本結(jié)構(gòu)

使用表的組成部分而不是整個(gè)表

保護(hù)數(shù)據(jù)，可以給用戶授予表的特定部分的訪問(wèn)權(quán)限而不是整個(gè)表的訪問(wèn)權(quán)限

能夠更改數(shù)據(jù)的格式和表示，視圖可以返回和底層表的表示很格式不同的數(shù)據(jù)。

12.3 視圖的規(guī)則和限制

• 視圖必須唯一命名(與表一樣)

• 對(duì)視圖的創(chuàng)建數(shù)目沒(méi)有限制

• 為了創(chuàng)建視圖，必須具有足夠的訪問(wèn)權(quán)限，通常由數(shù)據(jù)庫(kù)管理人員授權(quán)

• 視圖可以嵌套

• 視圖不能使用索引

12.4 創(chuàng)建視圖

CREATE VIEW 創(chuàng)建視圖

DROP VIEW 刪除視圖

--創(chuàng)建sub_pass視圖 CREATE VIEW sub_pass AS SELECT STUDENT.NAME,STUDENT.CLASSID,SUBJECT.name as subna FROM STUDENT,GRADE,SUBJECT WHERE STUDENT.ID = GRADE.STUDENTIDAND GRADE.SUBJECTID = SUBJECT.IDAND GRADE.SCORE >= 60; --查詢 SELECT NAME FROM SUB_PASS WHERE SUBNA = '數(shù)學(xué)';

用視圖重新格式化數(shù)據(jù)

-- 查看學(xué)生姓名及班級(jí) SELECT RTRIM(NAME)+'是'+RTRIM(CLASSID)+'班' AS CLASS FROM student;

使用視圖格式化數(shù)據(jù)

-- 創(chuàng)建視圖 CREATE VIEW STUCLASS AS SELECT RTRIM(NAME)+'是'+RTRIM(CLASSID)+'班' AS CLASS FROM STUDENT; --使用視圖查詢 SELECT CLASS FROM STUCLASS;

使用試圖過(guò)濾掉不想要的數(shù)據(jù)

-- 過(guò)濾掉未填寫(xiě)班級(jí)的學(xué)生 CREATE VIEW STUCLASS AS SELECT RTRIM(NAME)+'是'+RTRIM(CLASSID)+'班' AS CLASS FROM STUDENT WHERE CALSSID IS NOT NULL;

使用計(jì)算字段

-- 使用計(jì)算字段查詢學(xué)生信息及教材費(fèi) CREATE VIEW BOOK_S AS SELECT ID,NAME,BOOK_NUM,BOOK_PRICE,BOOK_NUM*BOOK_PRICE AS PRICE FROM BOOK;

13、第十三節(jié)(存儲(chǔ)過(guò)程)

13.1 什么是存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程是大型數(shù)據(jù)庫(kù)系統(tǒng)中，一組為了完成特定功能的sql 語(yǔ)句集，存儲(chǔ)在數(shù)據(jù)庫(kù)中，經(jīng)過(guò)一次編譯后再次調(diào)用后不需要再次編譯，用戶通過(guò)指定存儲(chǔ)過(guò)程并給出參數(shù) (如果該存儲(chǔ)過(guò)程有參數(shù)) 來(lái)調(diào)用存儲(chǔ)過(guò)程

人話：專門(mén)干一件事的sql語(yǔ)句

可以由數(shù)據(jù)庫(kù)調(diào)用，也可由程序調(diào)用

13.2 為什么使用存儲(chǔ)過(guò)程

• 效率高

  • 編譯一次后會(huì)存放到數(shù)據(jù)庫(kù)，每次調(diào)用時(shí)直接執(zhí)行。普通sql語(yǔ)句保存時(shí)，會(huì)保存到其他地方，需要先分析編譯在執(zhí)行

• 降低網(wǎng)絡(luò)流量

  • 在遠(yuǎn)程調(diào)用時(shí)，不會(huì)傳輸大量的字符串類型sql語(yǔ)句

• 復(fù)用性

  • 存儲(chǔ)過(guò)程往往只針對(duì)一個(gè)特定的功能編寫(xiě)，需要某個(gè)功能時(shí)，可以再次調(diào)用

• 可維護(hù)性高

  • 當(dāng)功能需求發(fā)生小變動(dòng)時(shí)，修改存儲(chǔ)過(guò)程比較容易，花費(fèi)精力小

• 安全性高

  • 某個(gè)特定功能的存儲(chǔ)過(guò)程一般只有特定的用戶可以使用，有身份限制，相對(duì)安全性高

13.3 創(chuàng)建存儲(chǔ)過(guò)程

-- 創(chuàng)建無(wú)參 -- 查詢學(xué)生全部信息 CREATE PROCEDURE stuInfo AS SELECT * FROM STUDENT;--調(diào)用，執(zhí)行存儲(chǔ)過(guò)程 EXEC stuInfo; -- 創(chuàng)建有參 -- 根據(jù)傳參（學(xué)生ID）來(lái)查詢學(xué)生信息 CREATE PROCEDURE stuInfo(@STUID INT) AS SELECT * FROM STUDENT WHERE ID = @STUID;--調(diào)用，執(zhí)行存儲(chǔ)過(guò)程 -- 查找1號(hào)學(xué)生全部信息 EXEC stuInfo 1;

13.4 修改、刪除、重命名操作

修改

-- 修改為只查找學(xué)生姓名 ALTER PROCEDURE stuInfo AS SELECT NAME FROM STUDENT;

刪除

DROP PROCEDURE stuInfo;

重命名

SP_RENAME stu_only, stuInfo;

13.5 有參存儲(chǔ)過(guò)程

-- 一個(gè)參數(shù) ----------------------- CREATE PROCEDURE stuInfo(@STUID INT) AS SELECT * FROM STUDENT WHERE ID = @STUID;-- 兩個(gè)參數(shù) ----------------------- CREATE PROCEDURE stuInfo(@STUID INT,@STUAGE INT) AS SELECT * FROM STUDENT WHERE ID = @STUID AND AGE = @STUAGE; --調(diào)用 EXEC stuInfo 1,11;-- 創(chuàng)建有返回值的存儲(chǔ)過(guò)程 ------------ CREATE PROCEDURE stuName(@STUID INT,@s_NAME varCHAR(20) OUTPUT) AS SELECT @s_NAME = NAME FROM STUDENT WHERE ID = @STUID; -- 調(diào)用,執(zhí)行存儲(chǔ)過(guò)程，根據(jù)學(xué)號(hào)輸出姓名 DECLARE @NAME VARCHAR(20) EXEC STUNAME 1,@NAME OUTPUT PRINT @NAME;-- 創(chuàng)建帶通配符的存儲(chǔ)過(guò)程 ------------ CREATE PROC STU_NAME(@S_NAME VARCHAR(20) = '%') AS SELECT * FROM STUDENT WHERE NAME LIKE @S_NAME; -- 調(diào)用 EXEC STU_NAME '張%'; -- 或 EXEC STU_NAME '%三';-- 創(chuàng)建加密存儲(chǔ)過(guò)程 ----------------- CREATE PROC STU_INFO WITH ENCRYPTION -- WITH ENCRYPTION子句對(duì)用戶隱藏存儲(chǔ)過(guò)程的文本 AS SELECT * FROM STUDENT; -- 調(diào)用 EXEC STU_INFO; EXEC SP_HELPTEXT 'STU_INFO'; -- 對(duì)象 'STU_INFO' 的文本已加密。-- 不緩存存儲(chǔ)過(guò)程 ------------------ CREATE PROC INFO_S WITH RECOMPILE ASSELECT * FROM STUDENT GO EXEC INFO_S;EXEC SP_HELPTEXT INFO_S;-- 創(chuàng)建帶有游標(biāo)的存儲(chǔ)過(guò)程 ----------- CREATE PROC STU_USER@S_CUR CURSOR VARYING OUTPUT ASSET @S_CUR = CURSOR FORWORD ONLY STATIC FORSELECT ID,NAME,AGEFROM STUDENTOPEN @S_NAME; GO DECLARE @CUR CURSOR,@S_ID INT,@S_NAME VARCHAR(20),@S-AGE INT EXEC STU_USER @S_CUR = @CUR OUTPUT; FETCH NEXT FROM @CUR INTO @S_ID,@S_NAME,@S-AGE; WHILE(@@FETCH_STATUS=0) BEGINFETCH NEXT FROM @CUR INTO @S_ID,@S_NAME,@S-AGE;PRINT 'S_ID:'+CONVERT(VARCHAR,@S_ID)+',S_NAME:'+@S_NAME+',S_AGE'+@S_AGE; END CLOSE @CUR; --關(guān)閉游標(biāo) DEALLOCATE @CUR; --釋放游標(biāo)

創(chuàng)建插入數(shù)據(jù)的存儲(chǔ)過(guò)程

CREATE PROC S_INSERT@S_ID INT ,@S_NAME VARCHAR(20) AS DECLARE @S_NUM INT SELECT @S_ID = COUNT(*)+1 FROM STUDENT SELECT @S_NUM = MAX(AGE)+1 FROM STUDENT INSERT INTO STUDENT(ID,AGE,NAME) VALUES(@S_ID,@S_NUM,@S_NAME); go exec S_INSERT 2,'魏老板'; SELECT * FROM STUDENT;

14、第十四節(jié)(事務(wù))

14.1 事務(wù)處理

什么是事務(wù)

事務(wù)處理可以用來(lái)維護(hù)數(shù)據(jù)庫(kù)的完整性，他保證成批的SQL操作要么完全執(zhí)行，要么完全不執(zhí)行。

事務(wù)的四大特性

• 原子性

  • 要么全部完成，要么全部取消。如果事務(wù)崩潰，狀態(tài)回到事務(wù)之前(事務(wù)回滾)

• 隔離性

  • 隔離性是當(dāng)多個(gè)用戶并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，比如操作同一張表，數(shù)據(jù)庫(kù)為每一位用戶開(kāi)啟的用戶，不能被其他事務(wù)干擾，多個(gè)并發(fā)事務(wù)之間相互隔離
  • 例：T1和T2兩個(gè)并發(fā)事務(wù)，在T1看來(lái)，T2發(fā)生在T1之后或之前，并不是同時(shí)進(jìn)行。在T2看來(lái)是在T1之前或之后。

• 持久性

  • 事務(wù)發(fā)生后對(duì)數(shù)據(jù)的改變是永久的

• 一致性

  • 數(shù)據(jù)與表一致，只有合法的數(shù)據(jù)才能寫(xiě)入數(shù)據(jù)庫(kù)

事務(wù)處理

事務(wù)：指一組AQL語(yǔ)句

回退(回滾)：指撤銷指定SQL語(yǔ)句的過(guò)程

提交：指將未存儲(chǔ)的SQL語(yǔ)句存儲(chǔ)到數(shù)據(jù)庫(kù)

保留點(diǎn)：指事務(wù)處理中設(shè)置的臨時(shí)占位符，你可以對(duì)它發(fā)布回退

14.2 控制事務(wù)處理

BEGIN 開(kāi)啟一個(gè)事務(wù)

ROLLBACK 事務(wù)回滾

COMMIT 確認(rèn)事務(wù)

-- STUDENT表插入一行信息 BEGIN TRANSACTION --簡(jiǎn)寫(xiě)TRAN INSERT INTO STUDENT (ID,NAME,AGE,SEX,PHONE) VALUES(14,'腦',22,1,11122233344 ) COMMIT TRANSACTION

ROLLBACK 事務(wù)回滾

-- 插入數(shù)據(jù)，如果插入失敗回滾輸出1，成功輸出0 BEGIN TRAN ADDINFO DECLARE @ERROR_S INT; SET @ERROR_S = 0; BEGIN TRYINSERT INTO STUDENT VALUES(9,'魏老板',23,1,12112,201);INSERT INTO STUDENT VALUES(10,'馬大師',23,1,12112,201);INSERT INTO STUDENT VALUES(11,'腦師傅',23,1,12112,201); END TRY BEGIN CATCHSET @ERROR_S = @ERROR_S+1; END CATCH IF(@ERROR_S>0) BEGINROLLBACK TRAN ADDINFO;PRINT @ERROr_S; END ELSE BEGINCOMMIT TRAN ADDINFO;PRINT @ERROR_S; END

SAVE 保留點(diǎn)

對(duì)于簡(jiǎn)單的事務(wù)可以使用ROLLBACK、COMMIT進(jìn)行整體回退提交。而復(fù)雜的事務(wù)可能需要部分提交和回退。

為了支持部分回退事務(wù)處理，必須能在事務(wù)處理塊中合適的位置放置占位符。而這些占位符就是保留字。

-- 依次插入數(shù)據(jù),第一條數(shù)據(jù)插入成功后會(huì)設(shè)置一個(gè)保留點(diǎn)，第二條數(shù)據(jù)插入時(shí)，如果失敗則回退到保留點(diǎn)。往后依次。 BEGIN TRAN ADD_UI INSERT INTO STUDENT VALUES(21,'王富貴',23,0,12121,201); IF @@ERROR <> 0 ROLLBACK TRAN; SAVE TRAN SUCCESS1; INSERT INTO STUDENT VALUES(22,'那個(gè)人',23,0,12121,201); IF @@ERROR <> 0 ROLLBACK TRAN SUCCESS1; SAVE TRAN SUCCESS2; INSERT INTO STUDENT VALUES(23,'那個(gè)村',23,0,12121,201); IF @@ERROR <> 0 ROLLBACK TRAN SUCCESS2; SAVE TRAN SUCCESS3; INSERT INTO STUDENT VALUES(14,'那個(gè)鎮(zhèn)',23,0,12121,201); IF @@ERROR <> 0 ROLLBACK TRAN SUCCESS3; COMMIT TRAN;

15、第十五節(jié)(游標(biāo))

15.1 游標(biāo)

游標(biāo)（cursor）是系統(tǒng)為用戶開(kāi)設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語(yǔ)句的執(zhí)行結(jié)果。每個(gè)游標(biāo)區(qū)都有一個(gè)名字,用戶可以用SQL語(yǔ)句逐一從游標(biāo)中獲取記錄，并賦給主變量，交由主語(yǔ)言進(jìn)一步處理。

作用

遍歷數(shù)據(jù)行；

保存查詢結(jié)果，方便下文調(diào)用；

15.2 創(chuàng)建游標(biāo)

DECLARE CURSOR_NAME(游標(biāo)名稱) [insensitive] [scroll] cursor for select [xxxx] -- 查詢語(yǔ)句-- 創(chuàng)建游標(biāo) DECLARE STU_NAME CURSOR FOR SELECT * FROM STUDENT; -- 打開(kāi)游標(biāo) OPEN S_NAME;-- 使用FETCH訪問(wèn)游標(biāo)數(shù)據(jù) -- 聲明參數(shù) DECLARE @ID INT,@NAME VARCHAR(20),@AGE INT,@SEX INT,@PHONE INT,@CLASSID INT; FETCH NEXT FROM STU_NAME INTO @ID,@NAME,@AGE,@SEX,@PHONE,@CLASSID; -- 打印信息 PRINT cast(@ID as varchar)+@NAME+cast(@AGE as varchar)+cast(@SEX as varchar)+cast(@PHONE as varchar)+cast(@CLASSID as varchar); -- 打印全部學(xué)生信息 WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM STU_NAME INTO @ID,@NAME,@AGE,@SEX,@PHONE,@CLASSID;PRINT cast(@ID as varchar)+@NAME+cast(@AGE as varchar)+cast(@SEX as varchar)+cast(@PHONE as varchar)+cast(@CLASSID as varchar); END --關(guān)閉游標(biāo) CLOSE STU_NAME;

next

返回結(jié)果集當(dāng)前行的下一行，首次提取返回第一行。

frior

返回結(jié)果集的上一行，首次提取無(wú)數(shù)據(jù)返回。

first

返回結(jié)果集第一行。

last

返回結(jié)果集最后一行。

absolute

移動(dòng)到結(jié)果集的第n行。如果n為正數(shù)，從結(jié)果集的第一行（包含第一行）起移到第n行；如果n為負(fù)數(shù)，則從結(jié)果集的最后一行起移到第n行。

relative

從游標(biāo)指針的當(dāng)前位置移動(dòng)n行。如果n為正數(shù)，則讀取游標(biāo)當(dāng)前位置起向后的第n行數(shù)據(jù)；如果n為負(fù)數(shù)，則讀取游標(biāo)當(dāng)前位置起向前的第n行數(shù)據(jù)。

15.3 基于游標(biāo)定位UPDATE語(yǔ)句和定位DELETE語(yǔ)句

-- UPDATE UPDATE [表名] SET [列名] WHERE CURRENT [游標(biāo)名]; -- 修改性別 OPEN STU_NAME; DECLARE @ID INT,@NAME VARCHAR(20),@AGE INT,@SEX INT,@PHONE INT,@CLASSID INT FETCH NEXT FROM STU_NAME INTO @ID,@NAME,@AGE,@SEX,@PHONE,@CLASSID; UPDATE STUDENT SET SEX = 1 WHERE CURRENT OF STU_NAME; CLOSE STU_NAME;-- DELETE DELETE FROM [表名] WHERE CURRENT OF [游標(biāo)名]-- 刪除一個(gè)學(xué)生數(shù)據(jù) OPEN STU_NAME; DECLARE @ID INT,@NAME VARCHAR(20),@AGE INT,@SEX INT,@PHONE INT,@CLASSID INT FETCH NEXT FROM STU_NAME INTO @ID,@NAME,@AGE,@SEX,@PHONE,@CLASSID; DELETE FROM STUDENT WHERE CURRENT OF STU_NAME

15.4 刪除游標(biāo)

CLOSE 關(guān)閉游標(biāo)并不會(huì)釋放其占用的數(shù)據(jù)結(jié)構(gòu)

若想刪除需要用DEALLOCATE進(jìn)行刪除

DEALLOCATE STU_NAME;

16、第十六節(jié)(索引、觸發(fā)器)

16.1 索引

索引是用來(lái)排序數(shù)據(jù)一加快搜索和排序操作的速度。

單列索引

CREATE INDEX NAME ON STUDENT(NAME)

多列suoyin

CREATE INDEX NAME ON STUDENT(NAME,AGE)

唯一索引

CREATE UNIQUE INDEX NAME ON STUDENT(NAME)

隱式索引

-- 由數(shù)據(jù)庫(kù)服務(wù)器在創(chuàng)建某些對(duì)象的時(shí)候自動(dòng)生成。例如主鍵約束和唯一約束。

修改索引

use database_nameexec sp_rename ‘table_name.old_name’ ‘new_name’

刪除索引

DROP INDEX STUDENT.NAME;

查看索引

use [數(shù)據(jù)庫(kù)名] exec sp_helpindex [表名]

16.2 觸發(fā)器

觸發(fā)器時(shí)特定的存儲(chǔ)過(guò)程，在特定的數(shù)據(jù)庫(kù)活動(dòng)發(fā)生時(shí)自動(dòng)執(zhí)行。

觸發(fā)器具有以下數(shù)據(jù)的訪問(wèn)權(quán)：

• INSERT 操作的所有數(shù)據(jù)；

• UPDATE操作的所有新、舊數(shù)據(jù)；

• DELETE操作中刪除的數(shù)據(jù)；

觸發(fā)器的作用

• 保證數(shù)據(jù)一致。例：在INSERT或UPDATE中轉(zhuǎn)化所有姓名大寫(xiě)
• 基于某個(gè)表的變動(dòng)在其他表上執(zhí)行活動(dòng)。例：每當(dāng)更新或刪除一行時(shí)將審計(jì)跟蹤記錄寫(xiě)入某個(gè)日志表
• 進(jìn)行額外的驗(yàn)證并根據(jù)需求回退數(shù)據(jù)。例：保證某個(gè)學(xué)生的額外加分不超限定，如果超出，則阻塞插入
• 計(jì)算計(jì)算列的值或更新時(shí)間戳。

創(chuàng)建一個(gè)觸發(fā)器

-- 插入、更新數(shù)據(jù)時(shí)會(huì)把姓名修改為大寫(xiě) CREATE TRIGGER NAME_UPER ON STUDENT FOR INSERT,UPDATE AS UPDATE STUDENT SET NAME = UPPER(NAME);

總結(jié)

以上是生活随笔為你收集整理的sql语句(SQL SERVER)的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。