sqlserver 基礎知識大整理

 

  sqlserver 基礎知識大整理（強烈推薦之一）

                              SQL-Structured Query Language

--(開啟SQL服務：net start mssqlserver)

--(在命令列中輸入'sqlwb'命令可開啟SQL管理器 )

--(如果要執行多條命令中的一條,滑鼠選定後再按F5執行)

create database sales                           --建立一個名為sales的資料庫
on
(
name='sales_data',
filename='d:\sales_data.mdf',
size=10,
maxsize=50,
filegrowth=5
)
log on
(
name='sales_log',
filename='d:\sales_log.ldf',
size=10,
maxsize=50,
filegrowth=5
)

drop database sales                             --刪除所建立的資料庫

sp_helpdb sales                                 --查看資料庫的相關資訊

sp_helpfile                                     --查看當前資料庫資料檔案與記錄檔的相關資訊

sp_detach_db sales                              --轉移資料庫時分離資料庫

sp_attach_db sales,@filename1='資料檔案路徑名'  --整合分離的資料庫                 
                  ,@filename2='記錄檔路徑名'

--(如何減小資料庫記錄檔的大小:  a.分離資料庫 b.轉移記錄檔 c .整合資料庫但不指定記錄檔)

--資料庫的備份

sp_addumpdevice 'disk','mydisk','d:\sales.bak'  --添加裝置。disk表示目標裝置類型,mydisk表示目標裝置邏輯名稱,d:\sales.bak表示目標裝置實體名稱

backup database sales to mydisk                 --向裝置寫入資料.其中的sales指資料庫名,mydisk為自定的裝置標示符

restore database sales from mydisk              --恢複資料庫

sp_dropdevice mydisk                            --刪除裝置

EXEC sp_dboption 'sales','read only','true'     --設資料庫為唯讀

EXEC sp_dboption 'sales',autoshrink,true        --設資料庫為自動壓縮

EXEC sp_dboption 'sales','single user'          --設資料庫為單使用者

--(以上命令中單引號可加可不加,但名字中出現空格的一定要加.大小寫不分)

DBCC shrinkdatabase (sales,10)                  --將資料庫中的檔案減小,使資料庫中有10%的可用空間

---------------------------------------------------------------------------------------------------------------

create table goods                              --建表的第一種約束文法
(
gid int primary key,
gname varchar(10) unique,
price money check(price>300),
ldate datetime default getdate()
)

insert into goods(gid,gname,price) values(105,'computer5',1222)--當表中有預設值約束時向表中輸入資料

insert into goods values(107,'computer',13434,default)         --當表中有預設值約束時向表中添加資料的另一種方法

sp_help goods                                   -- 用來查詢表的資訊

select *from goods    --用來查詢表中的內容

create table goods                              --建表的第二種約束文法
(
gid int constraint pg_id primary key,           --用constraint 給表中的列約束起名
gname varchar(10) constraint uq_name unique,
price money constraint ck_price check(price>300),
ldate datetime constraint df_date default getdate()
)

alter table goods drop 約束名                   --用來刪除約束

 create table goods                             --建表的第三種約束文法
(
gid int not null,
gname varchar(10),
price money,
ldate datetime
)

alter table goods add constraint pk_id primary key(gid)
alter table goods add constraint uq_name unique(gname)
alter table goods add constraint cj_price check(price>300 and price<1000)
alter table goods add constraint df_ldate default getdate() for ldate

create table gp                                 --建立引用goods的表gp
(
wno int identity(1001,1) primary key,           --identity為設定自動成長列標示,1001是起始數字,references為引用
      --在插入資料時不能給自動成長列賦 值，插入字元型資料與日期型資料時要用單引號
gno int constraint fk_id foreign key            --定義gno為表的外鍵
references goods(gid) 
)
drop table gp
create table gp
(
wno int identity(1001,1) primary key,
gno int
)

alter table gp add constraint fk_id foreign key(gno) references goods(gid)--效果同上，另一種寫法

alter table 表名 add 列名 資料類型               --為表加上一列

alter table 表名 drop column 列名                --刪除一列

delete from 表名 where 條件(如：gid=1001)        --刪除符合where條件的一行

insert into 表名 values (default)                --為表附預設值

insert into 表名(列名) values()                  --同上

--預設值約束不影響曆史資料！

--當為包含有自動成長列的表添加資料時不須為自動成長列附值

delete from 表名                                 --全刪表中資料

delete from 表名 where gid=1001                  --刪除符合條件（gid=1001)的資料

truncate table 表名                              --截斷表，不可帶條件，不能截斷被外鍵引用的表，不管該表中是否有資料

update 表名 set 列名=列值                        --用來更新資料

where gid=1000 or gid=1001

update 表名 set 列名=列值                        --同上

where gid in(1000,1001)

update 表名 set 列名1=列值1，列名2=列值2,......  --為多列更新值

where 條件

--事務可分為3種:1。明確交易 2。隱含交易 3。自動認可事務（系統預設為自動認可事務）    

select * from 表名                               --查詢表中資料

begin tran t1            --開始一個明確交易 

update 表名 set 列名=列值                        --更新資料

where not 條件                                   --更新條件

rollback t1                                     --復原一個事務    

commit t1                                       --提交事務（以後不能再復原了）

--隱含交易通過 SET IMPLICIT_TRANSACTIONS ON語句將隱含交易設為開,當串連以隱含交易操作時，
--將在提 交或復原後自動啟動新事務，無須描述事務的開始，只須提交或復原事務

SET IMPLICIT_TRANSACTIONS ON

select * into 新表名 from 舊錶名                 --備份現有表資料到新表中，它能複製表的結構，資料。
--還可以加上條件過濾如果只想複製到指定列，用列名代替*即可

--如果只想複製表的結構而不想複製資料，加上永不成立條件。（如where 1>3) 

--該語句自動建立新表，但原表的約束關係不能被複製，但not null與identity屬性可複製

select 列名1，列名2，列名3,......
into 新表名 from 舊錶名    --備份現有表中的部分資料到新表中

alter table gp add constraint gp_id foreign key(gno) references
goods(gid) on delete cascade on update no action --這是用來進行級連更新和刪除的文法，
      --在 on的後面可加上: 1.delete cascade 2.delete no action 3.update cascade 4.update no action

--------------------------------------------------------------------------------------------------------------------------------------------------

create table gp
(
pid int identity(100,1) primary key,
pname varchar(10),
ptel varchar(12) check(ptel like '[0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)

                                                --這是電話號碼約束的方法

select host_name()        --查看本機名

select getdate()                                --擷取目前時間

select user                                     --查看目前使用者

xp_cmdshell 'dir'                               --執行DOS命令dir，將DOS命令寫在''中間即可

xp_cmdshell 'net user EKIN 1234 /add'           --添加windows使用者，EKIN為使用者名稱，1234為密碼

xp_cmdshell 'net user EKIN /delete'             --刪除windows使用者

xp_cmdshell 'net user administrator 9527'       --修改管理員密碼

Uniqueidentifier              --這是資料類型的一種，全球唯一的標示符，用newid()函數給這個類型的資料提供值

                           
select *from gp INNER JOIN goods on gp.gno=goods.gid               --內聯結 僅顯示兩個聯結表中的匹配行的聯結
 
select *from gp LEFT OUTER JOIN goods on gp.gno=goods.gid          --左向外聯結  包括第一個命名表（“左”表，出現在JOIN子句的最左邊）中的所有行。不包括“右”表中的不匹配行   

select *from gp right OUTER JOIN goods on gp.gno=goods.gid         --右向外聯結  包括第二個命名表（“右”表，出現在JOIN子句的最右邊）中的所有行。不包括“左”表中的不匹配行  

select *from gp full OUTER JOIN goods on go.gno=goods.gid          --完整外部聯結  包括所有聯結表中的所有行，不管它們是否匹配

select *from gp CROSS JOIN goods                                   --交叉聯結  在這類聯結的結果集內，兩個表中每個可能成對的行佔一行，不論它們是否匹配

select *from goods where price between 1300 and 1800               --區間查詢。查價格在1300-1800間的貨物

select *from goods where gid in(1001,1003)                         --查詢貨物ID為1001和1003的產品，不是查1001與1003之間的貨物！in前加not指除了1001和1003以外的貨物

select *from goods where price not between 1300 and 1500           --查price不在1300與1500之間的貨物

select *from goods where price is null                             --查詢價格為空白的貨物

select *from goods where gname like ' '                            --模糊查詢。''中加 % 代表gname中的任一字元， _ 代表gname中的一個字元，[ ]代表一個區間，[^]代表不在這區間

                   --比如:select *from Renyuan where age like '2[^1-4]'

--在SQL中一個漢字與一個符號或字母都只佔一個字元，  用  nchar  可錄漢字。

select max(price) as 最高價格 from goods                           --as為取別名，max()為求最大值的函數,min()求最小值，arg()求平均值

select sum(price) from goods                                       --求price 的總和，sum()用來求總和的

--單行與彙總不能一起使用，除非依據單行進行分組。（比如：select gid, max(price）from goods) 

select gid,  max(price) as 最高價格 from goods group by gid        --按gid進行分組，就是求同類gid貨物的最高價格

--在where子句中不能出現彙總函式（比如：where max(price)>1300)

select gid,max(price) as 最高價格 from goods group by gid having max(price)>1300  --用having指定分組條件，代替where

create table info
(
 ino int,age int
)

insert into info values(12,22)
select *from info order by ino asc,age desc                        --order by指定排序條件，asc表示升序，desc表示降序.

--以上這個程式結果為編號按照升序排列，在編號相同的情況下，按age降序排列

select max(convert(int,price)) from goods                          --在goods表中查詢出價格最高的貨物，並且強制轉換price的類型  

select top 1 * from goods where price>4000      --在goods表中查詢出價格大於4000的貨物資料，並只顯示第一條記錄