資料庫（SQL Server Management Studio）

1.SQL（結構化查詢）語言

定義：結構化查詢語言（Structured Query Language）簡稱SQL，是一種編程語言，一種資料庫查詢和程式設計語言，用于存取資料以及查詢、更新和管理關系資料庫系統，
特點：（1）一體化（兩方面）
（2）高度非程序化語言
（3）面向集合的操作方式（一次一集合）
（4）互動式和嵌入式兩種使用方式，統一的語法結構
（5）支持三級模式結構
視圖 <——>外模式
基本表（的集合）<——>模式
存盤檔案和索引 <——>內模式
功能：（1）、SQL資料定義功能：能夠定義資料庫的三級模式結構，
（2）、SQL資料操縱功能：包括對基本表和視圖的資料插入、洗掉和修改，特別是具有很強的資料查詢功能，
（3）、SQL的資料控制功能：主要是對用戶的訪問權限加以控制，以保證系統的安全性，
陳述句結構：
1、資料查詢（DQL）： SELECT
2、資料定義(DDL)： CREATE（添加），DROP（洗掉），ALTER（修改）
3、資料操縱(DML)： INSERT（添加），DELETE（洗掉），UPDATE（修改）
4、資料控制(DCL)： GRANT，REVOKE
5、事務控制語言（TCL）：COMMIT（提交）命令，SAVEPOINT（保存點）命令，ROLLBACK（回滾）命令
6、指標控制語言（CCL）：DECLARE CURSOR，FETCH INTO，UPDATE WHERE CURRENT
注：資料定義中的添加，修改和洗掉是對資料庫以及資料表進行操作；而資料操縱中的是對表中的欄位進行操作，

2.使用T-SQL陳述句創建資料檔案

2.1用SQL陳述句創建資料庫

create database db_Student
on(name=db_Student_data,filename='D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db_Student_data.mdf',size=3MB,maxsize=100MB,filegrowth=1MB)
log on(name=db_Student_data,filename='D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db_Student_data.ldf',size=3MB,maxsize=100MB,filegrowth=1MB)

name：資料庫名
Filename=‘D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db_Student_data.mdf’：資料庫檔案名
size=3MB 初始大小(后面單位沒寫默認：MB)
maxsize=100MB：最大大小（不受限制：unlimited）
filegrowth=1MB：增長量級

mdf：主資料檔案后綴名

ndf：輔資料檔案后綴名

ldf：日志檔案后綴名

輔資料檔案只需在主資料檔案括號后用逗號隔開加括號在括號中編輯五個屬性即可

2.1.1、使用T—SQL創建資料庫、查看資料庫

查看指定資料庫或所有的資料庫資訊

陳述句格式： Execute sp_helpdb 資料庫名

（1）查看當前資料庫服務器中所有資料庫的資訊

? Execute sp_helpdb

（2）查看當前資料庫服務器中Student資料庫的資訊

? Execute sp_helpdb Student

2.1.2、使用T—SQL陳述句更改資料庫名稱

陳述句格式：

? sp_renamedb 當前資料庫名稱,資料庫新名稱

2.2、分離、附加、洗掉資料庫

分離：Sp_detach_db 資料庫名

附加：Sp_attach_db 資料庫名,檔案名 (檔案名：附加到的檔案的物理地址)

洗掉資料庫：drop database 資料庫名 (資料庫一旦被洗掉，就永久的被洗掉)

2.3、修改資料庫

修改資料庫：ALTER DATABASE 資料庫名

1、添加次要資料庫檔案(由于添加輔資料庫檔案導致資料庫結構改動)

ALTER  DATABASE  db_HX
add file(name=db_HX,name=db_HX,filename='D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db_HX.ldf',size=3MB,maxsize=100MB,filegrowth=1MB)

2、更改指定檔案

ALTER  DATABASE  db_HX
modify  file(name=db_HX,size=20mb)

3、洗掉指定檔案

ALTER  DATABASE  db_HX
remove  file db_HX

4、添加檔案組

ALTER  DATABASE  db_HX
add filegroup group1

5、將db_HX_data檔案添加到檔案組group中

ALTER  DATABASE  db_HX
add file(name=db_HX_data,filename='物理檔案名')
to filegroup group

2.4縮減資料庫空間

1、縮減資料庫空間的方法：
（1）縮減已有資料庫檔案大小
（2）洗掉沒有資料或清空的資料庫檔案
2、縮減已有資料庫檔案大小
（1）收縮資料庫
dbcc shrinkdatabase(db_HX,10)
（2）收縮特定資料庫檔案
dbcc shrinkfile(db_HX _dat,5)

3.使用T-SQL陳述句創建資料表

3.1資料表

概念：
（1）資料表是資料庫中最重要的物件,資料庫的全部資料都存盤在不同的資料表中;
（2）包含資料庫中所有資料的資料庫物件，表定義為列的集合，資料在表中是按行和列組織排列的，每行代表惟一的一條記錄，每列代表記錄中的一個域，
SQL常用的資料型別
（1）精確數字型別 包括：

整數型別：
- Bigint(長整型) 8位元組
- int(基本整型) 4位元組
- smallint(短整型) 2位元組
- tinyint() 1位元組
Bit（位型別）：
- 取值：0和1
- 在輸入0以外的其他值時，系統均把它們當1
Decimal和Numeric（數值型別）：
- 帶固定精度和小數位數的數值資料型別;
  Decimal(p[,s])和numeric(p[,s])
  P:精度,取值在1到38之間,默認18;
  S:小數位數,0<=s<=p,默認為0;
  eg:decimal（7，3），表示共有7位數，其中整數4位，小數3位，
Money和SmallMoney（貨幣型別）
- Money：基本貨幣型,占8個位元組長度
- Smallmoney：短貨幣型,占4個位元組長度
  （2 ）近似數字型別:
Real：近似實型浮點數，最大可有7位精確位數，占用4個位元組存盤空間，
Float：可變精度實型浮點數,可精確到第15位小數，不指定Float 資料型別的長度，占用8個位元組存盤空間，
（3） 日期和時間型別:
Datetime：(占用8個位元組),精確到毫秒，
Smalldatetime：(占用4個位元組)，精確到分，
（4） 字符資料型別:
Char（n）：定長字符型，若輸入資料的字串長度小于n，則系統自動在其后添加空格來填滿設定好的空間；若輸入的資料過長，將會截掉其超出部分，
Varchar(n)：變長字符型,每一列的長度達到規定位元組之前系統不會在其后添加空格來填滿設定好的空間
Text：文本型別
（5）Unicode 字符資料型別：
Nchar(n)：定長統一字符型，Nchar資料型別n的取值為1~4000，
Nvarchar(n)：變長統一字符型，n的取值范圍為1~4000，
Ntext：與Text資料型別類似
（6）二進制字符資料型別：
Binary(n)：定長二進制，最大長度（即n的最大值）為8000，
Varbinary(n)：變長二進制，
Image：用于存盤照片、目錄圖片(包括.GIF,BNP,JPEG)等
（7）其他資料型別
（8）用戶自定義資料型別

3.2使用T-SQL陳述句創建資料表

陳述句格式：

CREATE TABLE  
[資料庫名.DBO.] 創建資料表的表名
（ 列名1  資料型別    [NULL|NOT NULL],
    列名2   資料型別   [NULL|NOT NULL], 
     ………
    列名n   資料型別  [NULL|NOT NULL] 
  ）

注意：
每個欄位之間用半角狀態下的逗號“,”分隔,最后一個欄位沒有逗號，

3.3創建約束

1、分類：

Primary Key主鍵約束
null空值約束
Unique惟一性約束
Check檢查約束
Default默認值約束
Foreign Key外鍵約束
2、完整性約束的基本語法格式：

[CONSTRAINT constraint_name（約束名）]<約束型別>
   約束不指定名稱時，系統會給定一個名稱，

(1)主鍵（primary key）
陳述句格式：

constraint  約束名  primary  key（列名）
--eg:
create  table  tb_score
(  
  sno  char(10)  not null,
  cno char(10)  not null,
  score  float,
  constraint PK_tb_score  primary key(sno,cno)
)

注意：
命名規范：PK_[表名稱]
(2) 惟一性約束 (unique)

使用惟一性約束的欄位允許為空值；
一個表中可以允許有多個惟一性約束；
可以把惟一性約束定義在多個欄位上.
陳述句格式：

constraint  約束名  unique （列名)
--eg:
create  table tb_student
( sno  nchar(10)  not null,
  sn   nvarchar(30)   not null,
  ……
  constraint pk_tb_student primary key(sno),
  constraint  uq_tb_student unique(sn)
)

主鍵約束與惟一約束的區別:
①在一個基本表中只能定義一個primary key約束，但可定義多個unique約束；
②對于指定為primary key的一個列或多個列的組合，其中任何一個列都不能出現空值，而對于unique所約束的惟一鍵，則允許為空，
注意：不能為同一個列或一組列既定義unique約束，又定義primary key約束，
(3)檢查約束(check)

一個表中可以定義多個檢查約束；
每個create table陳述句中每個欄位只能定義一個檢查約束；
當執行insert陳述句或者update陳述句時，檢查約束將驗證資料，

constraint  約束名  check （約束運算式)
--eg:
create  table  tb_score
 (  sno char(5),
    ……
    constraint ck_tb_score check (score>=0 and score<=100)
) 
--注:  score  between 0 and 100也可以

(4)默認約束 (default)

每個欄位只能定義一個默認約束；
如果定義的默認值長于其對應欄位的允許長度，那么輸入到表中的默認值將被截斷，
語法格式:

default  默認值
--eg:
create table tb_student
(
   sno char(10),
   dept varchar(50) default ‘軟體學院',   
   ……
)

(5) 空值約束(null)

null值不是0也不是空白，更不是填入字串的“null”字串，而是表示“不知道”、“ 不確定”或“沒有資料”的意思，

(6) 外鍵約束 (foreign key)

外鍵從句中的欄位數目和每個欄位指定的資料型別都必須和references從句中的欄位相匹配；
一個表中最多可以有31個外鍵約束；
在臨時表中，不能使用外鍵約束；
主鍵和外鍵的資料型別必須嚴格匹配，
語法格式：

constraint   約束名  foreign key  （列名）references  主鍵表名 （主鍵表列名）
--eg:創建tb_score表，sno和cno為外鍵，
create table tb_score
(	sno char(10),
	……
	constraint FK_tb_score_tb_student foreign key(sno) references tb_student(sno),
	constraint FK_tb_score_tb_course foreign key(cno) references tb_course(cno)
)

3.4 修改、洗掉資料表

--eg: :在表tb_student中增加一個remark欄位，型別為varchar，長度為200，洗掉表中的polity欄位，并且修改sn欄位的資料型別為char，長度為20，
  alter  table  tb_student
  add  remark varchar(200)  null
  
  alter  table  tb_student
  drop  column  polity
   
  alter  table  tb_student  
  alter  column  sn  char(20)  null

洗掉表

SQL Server管理平臺創建主鍵約束
利用DROP TABLE陳述句洗掉表
語法形式:

drop table 表名

注意：

drop table陳述句可以洗掉一個表和表中的資料及其與表有關的所有索引、觸發器、約束、許可物件，
在洗掉一個表之前要先洗掉與此表相關聯的表中的外鍵約束，

3.5 增加、修改和洗掉約束

--eg:：在tb_student表中添加完整性約束定義，使birthday為系統當前時間，
alter table tb_student
add constraint def_tb_student default getdate() for birthday

--eg:：在tb_score表中洗掉完整性約束定義，使score取任意值，
alter table tb_score
drop constraint ck_tb_score

3.6 SQL資料查詢

3.6.1 select陳述句

SELECT陳述句格式：

SELECT   select_list    [  INTO  new_table_name ]  FROM   talbe_list
    [ WHERE     行條件運算式  ]
    [  GROUP BY   分組運算式  [  HAVING    組條件運算式 ] ]
    [  ORDER BY  order_list]  [ ASC|DESC ]

select基本陳述句格式：
select 查詢欄位1,欄位2 ,…欄位n from 表名

--從db_student資料庫的tb_student表中檢索所有學生的全部欄位內容，
      select  *  from tb_student
 -- 注意：當顯示全部列時可用*代替

3.6.2

1、top關鍵字
使用top關鍵字可以回傳查詢結果中的前n行，如果指定percent關鍵字，將從結果集中回傳前n%行，

-- 回傳tb_student的前3條記錄，
       select  top  3  *  from  tb_student

-- 回傳tb_student的前20%的記錄，
       select  top  20  percent  *  from tb_student

2、列別名
對查詢資料列標題進行修改，或為沒有標題的列增加臨時標題,列別名表示方式如下：
列別名 = 列名
列名as 列別名

-- tb_student 表中查詢所有學生的學號，姓名，出生日期欄位內容，要求用別名顯示，
select  sno  as  學號,姓名=sn,birthday=出生日期 from tb_student

3、組合列
除了只是顯示基礎欄位的資訊列之外，查詢還可以使用運算或函式來任意組合得到列，

-- 查詢學生的學號姓名，要求如下效果201401010101 (陳廣錄)，
select  sno+  '('  +  sn  +  ') '  as '學號姓名'  from  tb_student

-- 顯示每名同學的學號，姓名和年齡，
   select   sno   as  學號, sn  as  姓名, year(getdate()) - year(birthday)  as  年齡 from  tb_student

3.6.3 where子句的基本使用

基本的where子句格式：

where  篩選列名  運算子  值

查詢條件	謂詞
比較（比較運算子）	=,>,<,>=,<=,!=,<>,!>,!<
確定范圍	BETWEEN a AND b,NOT BETWEEN a AND b
確定集合	IN,NOT IN
字符匹配	LIKE,NOT LIKE
空值	IS NULL,IS NOT NULL
多重條件（邏輯謂詞）	AND,OR

(1)where條件使用算術運算式：

注意：不同資料型別值前后界定符號不同

-- 在tb_student表中找到姓名為陳君然的同學，
select * from  tb_student where sn = '陳君然'
-- 查詢tb_student表中姓“張”的同學資訊，
select * from tb_student   where left(sn, 1) = '張'
/*注意本例中left函式的使用
     left 函式：回傳從字串左邊開始指定個數的字符
     eg：left  (欄位名, 2) 結果回傳該欄位資料的前2位
*/

(2)where條件使用邏輯運算式

運算式　or 運算式
運算式　and 　運算式

-- 列出tb_score表中的所有低于30分或高于90分的同學，
select * from tb_score where (score< 30)  or  (score > 90)
-- 列出tb_student表中所有男團員的學生，
 select * from tb_student where (sex= '男')  and  (polity = '團員')
 -- 列出tb_score表中c01成績中大于60分或c02成績中大于60分的同學，
 select * from tb_score where (cno = 'c01') and (score > 60)  or (cno= 'c02') and (score> 60)
--注意：使用or邏輯關系時，只要滿足任意一個運算式即可，而and需要同時滿足兩個條件，

(3)where條件使用三種特殊運算，

between…and 允許你指定值的范圍
in 允許您指定一組值
like 允許您使用通配符來模糊查詢

-- 從表tb_student表中查詢1995~1996年出生的同學，
select *  from tb_student where   year(birthday) between 1995 and 1996
--  查詢tb_student表中所有黨員和團員的資訊，
select * from tb_student where  polity  in  ('黨員', '團員') 
-- 查詢tb_student表中所有姓“張”和姓“李”的同學，
select * from tb_student where  left(sn, 1) in ('張', '李')

通配符	含義	示例
-	任意單個字符	where sn like’王_'匹配姓王的兩個字的
%	由零個或多個字符組成的字串	where sn like’%劉%'匹配含劉該字的所有字串
[]	某個范圍或集合內的任意指定的字符	where sn like '張[1-3]'匹配"張1"或"張2"或"張3"但不匹配"張4"
[^]	不在某個范圍或集合內的任意指定的字	Where sn like '張[^3]'匹配處張3以外的所有字串

-- 在tb_student表中找到姓“李X”的同學，
select * from tb_student where  sn like '李_'
-- 在tb_student表中找到所有姓名中帶佳字的同學，
select * from tb_student where  sn like '%佳%'
--   在tb_student表中找到所有姓王名 (陽/洋/楊)的同學，
select * from tb_student where  sn like  '王[陽洋楊]' 
 -- 在tb_student表中找到所有不姓張的同學，
 select * from tb_student where  sn like '[^張]%'

(4)where條件使用null
NULL（空值）表示列的資料值未知或不可用
匹配方式: 列名 is NULL
列名 is not NULL

-- 在tb_student表中查詢政治面貌為空的學生學號、姓名、院系， 
select sno,sn,dept from tb_student where polity is null

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/277399.html

標籤：其他

上一篇：mysql 資料庫學習筆記

下一篇：web安全之SQL注入的基礎入門（1）

資料庫基礎知識(上)