数据库分析
1.DDL?（Data Definition Language?）數(shù)據(jù)庫定義語言?statements are used to define the database structure or schema.
DDL是SQL語言的四大功能之一。
DDL不需要commit.
CREATE
ALTER
DROP
2.DML?（Data Manipulation Language）數(shù)據(jù)操縱語言?statements are used for managing data within schema objects.
由DBMS提供，用于讓用戶或程序員使用，實(shí)現(xiàn)對(duì)數(shù)據(jù)庫中數(shù)據(jù)的操作。
DML分成交互型DML和嵌入型DML兩類。
依據(jù)語言的級(jí)別，DML又可分成過程性DML和非過程性DML兩種。
需要commit.
SELECT
INSERT
UPDATE
DELETE
LOCK TABLE
3、DCL（Data Control Language）數(shù)據(jù)庫控制語言? 授權(quán)，角色控制等
GRANT 授權(quán)
REVOKE 取消授權(quán)
?
4.TCL（Transaction Control Language）事務(wù)控制語言?
?
sql中any和all的區(qū)別
這兩個(gè)都是用于子查詢的
?
any 是任意一個(gè)
all 是所有
select * from student where 班級(jí)=01' and age > any(select age from student where 班級(jí)='02');
以上就是說，查詢出01班中，年齡大于 02班所有人 的 同學(xué)
相當(dāng)于：select * from student where 班級(jí)='01' and age > (select max(age) from student where 班級(jí)='02');
而：select * from student where 班級(jí)='01' and age > any (select age from student where 班級(jí)='02');?就是說，查詢出01班中，年齡大于 02班任意一個(gè) 的 同學(xué)
相當(dāng)于
select * from student where 班級(jí)='01' and age > (select min(age) from student where 班級(jí)='02');
select into from 和 insert into select 的用法和區(qū)別
select into from 和 insert into select都是用來復(fù)制表，兩者的主要區(qū)別為： select into from 要求目標(biāo)表不存在，因?yàn)樵诓迦霑r(shí)會(huì)自動(dòng)創(chuàng)建。insert into select from 要求目標(biāo)表存在
如果兩個(gè)表結(jié)構(gòu)一樣：
insert into table_name_new?select * from?table_name_old
如果兩個(gè)表結(jié)構(gòu)不一樣：
insert into table_name_new(column1,column2...)?select?column1,column2... from?table_name_old
select into?from的用法:
select?a.BookId,?a.BookName,a.BookPrice,a.ChuMemo,a.FileName?into?table_name_new?from?BuyCard.dbo.Book?a
?
union和union?all
UNION 操作符用于合并兩個(gè)或多個(gè) SELECT 語句的結(jié)果集。
請(qǐng)注意，UNION 內(nèi)部的 SELECT 語句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時(shí)，每條 SELECT 語句中的列的順序必須相同。
SQL UNION 語法
SELECT column_name FROM table_name1
UNION
SELECT column_name FROM table_name2
注釋：默認(rèn)地，UNION 操作符選取不同的值。如果允許重復(fù)的值，請(qǐng)使用 UNION ALL。
SQL UNION ALL 語法
SELECT column_name FROM table_name1
UNION ALL
SELECT column_name FROM table_name2
SQL查找重復(fù)數(shù)據(jù)
SELECT?RoleId
FROM?RoleEmployee
where?RoleId?in?(select?RoleId?from?RoleEmployee?group?by?RoleId?having?COUNT(RoleId)>1)
?
SQL中常用的函數(shù):
--系統(tǒng)函數(shù)
SELECT?COL_NAME(OBJECT_ID('Role'),1)--返回表中第1個(gè)字段的名稱；
SELECT?HOST_ID();???--使用該函數(shù)返回服務(wù)器端計(jì)算機(jī)的標(biāo)識(shí)號(hào)
SELECT?HOST_NAME();--返回服務(wù)器端計(jì)算機(jī)的名稱；
--數(shù)學(xué)函數(shù)
--1、ABS(X)絕對(duì)值函數(shù)；
SELECT?ABS(-2.0),ABS(2);
?
--2、PE()返回圓周率的函數(shù)；
SELECT?PI();
?
--3、SQRT(X)平方根函數(shù)；
SELECT?SQRT(9),SQRT(11);
?
SELECT?RAND()?----生成的隨機(jī)數(shù)；
--8、FLOOR(X)函數(shù)：返回比X小的最大整數(shù)；
SELECT?FLOOR(33.333),FLOOR(-33.333);
?
SELECT?SQUARE(0),SQUARE(3),SQUARE(-3),SQUARE(3.3);--SQUARE(X)函數(shù)：返回x的平方；
SQL日期和字符串函數(shù)
?
?
sql server常用字符串函數(shù)
--返回字符表達(dá)式最左側(cè)指定數(shù)目的字符串
select?LEFT('abcdefg',0)--''
select?LEFT('abcdefg',1)--'a'
select?LEFT('abcdefg',2)--'ab'
--返回字符表達(dá)式最右側(cè)指定數(shù)目的字符串select?RIGHT('abcdefg',0)--''
?
select?RIGHT('abcdefg',1)--'g'
select?RIGHT('abcdefg',2)--'gf'
--SUBSTRING（被截取字符串,開始位置,長度）
SELECT?SUBSTRING('abcd',1,1)--a
SELECT?SUBSTRING('abcd',2,2)--bc
?
--返回轉(zhuǎn)換為小寫的字符串select?LOWER('abcdefg')--'abcdefg'
--返回轉(zhuǎn)換為大寫的字符串select?UPPER('abcdefg')--ABCDEFG
--返回去左空格的字符串select?LTRIM(' ?abcdefg')--'abcdefg'
--返回去右空格的字符串select?RTRIM('abcdefg ???')--'abcdefg'
--replace（被搜索字符串,要被替換的字符串，替換的字符串）
select?REPLACE('abcdefg','cd','a')--abaefg
select?REPLACE('abcdefg','cd','')--abefg
--返回指定次數(shù)重復(fù)的表達(dá)式select?REPLICATE('a',4)--aaaa
select?REPLICATE('abc|',4)--abc|abc|abc|abc|
--返回反轉(zhuǎn)后的字符串select?REVERSE('ABC')--CBA
--返回字符串的長度select?LEN('abcdefg')—7
?
?
SQL自定義函數(shù)：
一、標(biāo)量值函數(shù)（返回一個(gè)值）
CREATE??FUNCTION?MySTR(@strs?VARCHAR(50))
RETURNS?VARCHAR(50)?
AS
BEGIN
?DECLARE?@str2?VARCHAR(30)
?SET?@str2=@strs
?RETURN?@str2
END
?
?
?--執(zhí)行函數(shù)
SELECT?dbo.MySTR('aa')?AS?result
?
--在sql 語句也可以這樣用：
DECLARE?@str3?VARCHAR(30)
?SET?@str3=(select?name?from?userinfo?where?huji=dbo.MySTR('邯鄲')?and?id=23 )
select?@str3
?
二、表格值函數(shù)（返回多列或者一張表）
CREATE??FUNCTION???tabcmess(@title?VARCHAR(10))
RETURNS??TABLE?
AS?
return(select?a.GoodsId,a.GoodsName?from?GoodsDB.dbo.Goods?a?where?a.GoodsName?like?'%'+@title+'%')
?
--執(zhí)行
?
SELECT?*?FROM?tabcmess('韭菜炒雞蛋')
?
Sql創(chuàng)建臨時(shí)表
create?table?#table1（局部臨時(shí)表）
(
???id?int,
???name?varchar(50)
)
?
create?table?##table2（全局臨時(shí)表）
(
???id?int,
???name?varchar(50)
)
select?a.GoodsId,a.GoodsName,a.GoodsNum?into?#table3?from?GoodsDB.dbo.Goods?a
?
SQL條件判斷和循環(huán)語句
if語句使用示例：
???????declare?@a?int
???????set?@a=12
???????if?@a>100
??????????begin
??????????????print?@a
??????????end????
?????else
?????????begin
????????????print?'no'
?????????end
?
?2、while語句使用示例：
???????declare?@i?int
???????set?@i=1
???????while?@i<30
?????????????begin
?????????????insert?into?test (userid) values(@i)
????????????set?@i=@i+1
?????????end
?SQL類型轉(zhuǎn)換函數(shù)
select?CAST('123.4'?as?decimal(9,2))??-- 123.40
select?CONVERT(decimal(9,2),?'123.4')?-- 123.40
SQL視圖
什么是視圖？
視圖是一個(gè)虛擬的表，是一個(gè)表中的數(shù)據(jù)經(jīng)過某種篩選后的顯示方式，視圖由一個(gè)預(yù)定義的查詢select語句組成。
視圖的特點(diǎn)。
視圖中的數(shù)據(jù)并不屬于視圖本身，而是屬于基本的表，對(duì)視圖可以像表一樣進(jìn)行insert,update,delete操作。
視圖不能被修改，表修改或者刪除后應(yīng)該刪除視圖再重建。
視圖的數(shù)量沒有限制，但是命名不能和視圖以及表重復(fù)，具有唯一性。
視圖可以被嵌套，一個(gè)視圖中可以嵌套另一個(gè)視圖。
視圖不能索引，不能有相關(guān)聯(lián)的觸發(fā)器和默認(rèn)值，sql server不能在視圖后使用order by排序。
?
視圖的優(yōu)點(diǎn)：
簡單性。視圖不僅可以簡化用戶對(duì)數(shù)據(jù)的理解，也可以簡化他們的操作。那些被經(jīng)常使用的查詢可以被定義為視圖，從而使用戶不必為以后的操作每次都指定全部的條件。
?安全性。通過視圖用戶只能查詢和修改他們所能見到的數(shù)據(jù)。數(shù)據(jù)庫中的其他數(shù)據(jù)則既看不見也取不到。數(shù)據(jù)庫授權(quán)命令可以使每個(gè)用戶對(duì)數(shù)據(jù)庫的檢索限制到特定的數(shù)據(jù)庫對(duì)象上，但不能授權(quán)到數(shù)據(jù)庫特定行和特定的列上。通過視圖，用戶可以被限制在數(shù)據(jù)的不同子集上。
視圖的缺點(diǎn)
即使是視圖的一個(gè)簡單查詢，SQL Server也把它變成一個(gè)復(fù)雜的結(jié)合體，需要花費(fèi)一定的時(shí)間。
在后期的維護(hù)上 數(shù)據(jù)庫的表結(jié)構(gòu)變了 也會(huì)影響視圖的改變，維護(hù)繁瑣。
所以在定義數(shù)據(jù)庫的時(shí)候，要合理的使用視圖。
?
?
SQL索引：
拿漢語字典的目錄頁（索引）打比方：正如漢語字典中的漢字按頁存放一樣，SQL Server中的數(shù)據(jù)記錄也是按頁存放的，每頁容量一般為4K?。為了加快查找的速度，漢語字（詞）典一般都有按拼音、筆畫、偏旁部首等排序的目錄（索引），我們可以選擇按拼音或筆畫查找方式，快速查找到需要的字（詞）。
同理，SQL Server允許用戶在表中創(chuàng)建索引，指定按某列預(yù)先排序，從而大大提高查詢速度。
???????????SQL Server中的數(shù)據(jù)也是按頁（?4KB?）存放
???????????索引：是SQL Server編排數(shù)據(jù)的內(nèi)部方法。它為SQL Server提供一種方法來編排查詢數(shù)據(jù)?。
???????????索引頁：數(shù)據(jù)庫中存儲(chǔ)索引的數(shù)據(jù)頁；索引頁類似于漢語字（詞）典中按拼音或筆畫排序的目錄頁。
???????????索引的作用：通過使用索引，可以大大提高數(shù)據(jù)庫的檢索速度，改善數(shù)據(jù)庫性能。
USE?GoodsDB
Go
?
IF?EXISTS?(SELECT?name?FROM?sysindexes
WHERE?name?=?'IX_writtenExam')
begin
DROP?INDEX?Employee.IX_writtenExam--根據(jù)索引名進(jìn)行刪除
end
else
begin
CREATE?clustered?index?IX_writtenExam--創(chuàng)建聚集索引
ON?Employee(Phone)
end
create?nonclustered?index?NONCLU_ABC?on?abc(A)--創(chuàng)建非聚集索引
/*-----指定按索引?IX_writtenExam?查詢----*/
SELECT * FROM stuMarks??(INDEX=IX_writtenExam)
????WHERE writtenExam BETWEEN 60 AND 90
索引的優(yōu)缺點(diǎn)
???????????優(yōu)點(diǎn)
–?????????加快訪問速度
–?????????加強(qiáng)行的唯一性
???????????缺點(diǎn)
–?????????帶索引的表在數(shù)據(jù)庫中需要更多的存儲(chǔ)空間
–?????????操縱數(shù)據(jù)的命令需要更長的處理時(shí)間，因?yàn)樗鼈冃枰獙?duì)索引進(jìn)行更新
?
創(chuàng)建索引的指導(dǎo)原則
???????????請(qǐng)按照下列標(biāo)準(zhǔn)選擇建立索引的列。
–?????????該列用于頻繁搜索
–?????????該列用于對(duì)數(shù)據(jù)進(jìn)行排序
???????????請(qǐng)不要使用下面的列創(chuàng)建索引：
–?????????列中僅包含幾個(gè)不同的值。（如果該列存在大量重復(fù)的數(shù)據(jù)，則不能在該列上創(chuàng)建索引，否則會(huì)降低性能）
–?????????表中僅包含幾行。為小型表創(chuàng)建索引可能不太劃算，因?yàn)?/span>SQL Server在索引中搜索數(shù)據(jù)所花的時(shí)間比在表中逐行搜索所花的時(shí)間更長
?
?SQL觸發(fā)器：
???觸發(fā)器是一種特殊類型的存儲(chǔ)過程，它不同于之前的我們介紹的存儲(chǔ)過程。觸發(fā)器主要是通過事件(增、刪、改)進(jìn)行觸發(fā)被自動(dòng)調(diào)用執(zhí)行的。而存儲(chǔ)過程可以通過存儲(chǔ)過程的名稱被調(diào)用。
觸發(fā)器是在對(duì)表進(jìn)行增、刪、改時(shí)，自動(dòng)執(zhí)行的存儲(chǔ)過程。觸發(fā)器常用于強(qiáng)制業(yè)務(wù)規(guī)則，它是一種高級(jí)約束，通過事件進(jìn)行觸發(fā)而被執(zhí)行。
二、觸發(fā)器分類
SQL Server 包括兩種常規(guī)類型的觸發(fā)器：事前觸發(fā)器，事后觸發(fā)器
1、觸發(fā)器分為：
（1） after觸發(fā)器（事后觸發(fā)）
????a、 insert觸發(fā)器
????b、 update觸發(fā)器
????c、 delete觸發(fā)器
（2） instead of 觸發(fā)器 （事前觸發(fā)）
注：after觸發(fā)器要求只有執(zhí)行某一操作**insert、update、delete之后觸發(fā)器才被觸發(fā)，且只能定義在表上。而**instead of觸發(fā)器表示并不執(zhí)行其定義的操作（insert、update、delete）而僅是執(zhí)行觸發(fā)器本身。
create?trigger?InsertName
on?Goodsindent--觸發(fā)器創(chuàng)建在哪張表上
with?encryption--給觸發(fā)器加密
for?insert--創(chuàng)建添加類型觸發(fā)器
as
declare?@goodsname?varchar(50),@qty?int
select?@goodsname=GoodsName,@qty=qty?from?inserted
update?Goods?set?GoodsNum=GoodsNum-@qty?where?GoodsName=@goodsname
SQL游標(biāo)：
游標(biāo)（cursor）
游標(biāo)是系統(tǒng)為用戶開設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果
在數(shù)據(jù)庫中，游標(biāo)是一個(gè)十分重要的概念。游標(biāo)提供了一種對(duì)從表中檢索出的數(shù)據(jù)進(jìn)行操作的靈活手段，就本質(zhì)而言，游標(biāo)實(shí)際上是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制
游標(biāo)的一個(gè)常見用途就是保存查詢結(jié)果，以便以后使用。游標(biāo)的結(jié)果集是由SELECT語句產(chǎn)生，如果處理過程需要重復(fù)使用一個(gè)記錄集，那么創(chuàng)建一次游標(biāo)而重復(fù)使用若干次，比重復(fù)查詢數(shù)據(jù)庫要快的多。如果我們做的數(shù)據(jù)量大，而且系統(tǒng)上跑的不只我們一個(gè)業(yè)務(wù)。所以，我們都要求盡量避免使用游標(biāo)，游標(biāo)使用時(shí)會(huì)對(duì)行加鎖，可能會(huì)影響其他業(yè)務(wù)的正常進(jìn)行。而且，數(shù)據(jù)量大時(shí)其效率也較低效。另外，內(nèi)存也是其中一個(gè)限制。
因?yàn)橛螛?biāo)其實(shí)是相當(dāng)于把磁盤數(shù)據(jù)整體放入了內(nèi)存中，如果游標(biāo)數(shù)據(jù)量大則會(huì)造成內(nèi)存不足，內(nèi)存不足帶來的影響大家都知道了。所以，在數(shù)據(jù)量小時(shí)才使用游標(biāo)
存儲(chǔ)過程:
? 1.存儲(chǔ)過程只在創(chuàng)造時(shí)進(jìn)行編譯，以后每次執(zhí)行存儲(chǔ)過程都不需再重新編譯，而一般 SQL 語句每執(zhí)行一次就編譯一次,所以使用存儲(chǔ)過程可提高數(shù)據(jù)庫執(zhí)行速度。
? 2.當(dāng)對(duì)數(shù)據(jù)庫進(jìn)行復(fù)雜操作時(shí)(如對(duì)多個(gè)表進(jìn)行 Update,Insert,Query,Delete 時(shí)），可將此復(fù)雜操作用存儲(chǔ)過程封裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。這些操作，如果用程序來完成，就變成了一條條的 SQL 語句，可能要多次連接數(shù)據(jù)庫。而換成存儲(chǔ)，只需要連接一次數(shù)據(jù)庫就可以了。
? 3.存儲(chǔ)過程可以重復(fù)使用,可減少數(shù)據(jù)庫開發(fā)人員的工作量。
? 4.安全性高,可設(shè)定只有某此用戶才具有對(duì)指定存儲(chǔ)過程的使用權(quán)。
轉(zhuǎn)載于:https://www.cnblogs.com/MNCnblogs/p/10437441.html
總結(jié)
- 上一篇： springboot配置cxf
- 下一篇： UVA572