sqlServer_基礎概念

SQL server的管理工具

SQL server聯機叢書

開始菜單à Microsoft SQL Server 2008 à 文檔和教程 à SQL Server聯機叢書

SQL Server 配置管理器

用於啟動和管理SQL server資料庫的服務端，以及其他相關功能。

當我們啟動SQL Server 配置管理器之後，可以在左側目錄中看到“SQL Server服務”，在“SQL Server服務”里，我們就可以對SQL Server的服務端，也就是核心數據引擎進行管理。

其中“SQL Server (MSSQLSERVER)” 和 “SQL Server (SQLEXPRESS)”就是代表我們所安裝的具體的服務端，前者是正式版，後者是體驗版。

打開Server配置管理器的另一種方法：

“我的電腦”à右鍵菜單à管理à”服務和應用”àSQL Server配置管理器

SQL server profiler

當我們的數據服務端出現問題和故障的時候，它可以給我們提供實時的跟蹤工具，和性能監控的作用。

SQL Server Management Studio

它就是SQL server的圖形化的管理界面，也就是客戶端。

啟動Management Studio

在登陸界面輸入相關的信息：

伺服器類型：資料庫引擎

伺服器名稱：我們可以輸入IP地址， 電腦名稱。如果是訪問本機的SQL server服務並且沒有改變預設埠號的話，只需要輸入一個點 ” . ”，它就代表本機的SQL Server正式版的服務端。（體驗版是.\SQLEXPRESS）

身份驗證：SQL Server身份驗證

用戶名：sa

密碼：sa

當SQL Server身份驗證無法登陸時

1、 用windows身份驗證（也就是用本機管理員來登陸，不需要輸入用戶名密碼的）。

2、 展開左側目錄中的 SQL Server à 安全性 à 登陸名 à 雙擊sa à 打開sa 用戶的屬性視窗。

3、 修改密碼

4、 取消”強制實施密碼策略”

5、 在“狀態”選項卡中，對“是否允許連接到數據引擎”和“登陸”分別選擇“授予”和“啟動”。

6、 點擊確定關閉sa 用戶的屬性視窗

7、 右鍵點擊伺服器根節點，選擇屬性打開“伺服器屬性”彈窗。

8、 選擇“安全性”選項卡，設置“伺服器身份驗證”為“SQL Server和Windows身份驗證”。

9、 確定並關閉“伺服器屬性”彈窗，然後在SQL server配置管理器中重啟數據服務端，再用sql server 帳戶密碼來登陸即可。

SQL Management Studio的界面操作

左側目錄中，我們可以創建資料庫數據表。

左上角的新建查詢按鈕，可以打開一個輸入與執行SQL語句的視窗。在此視窗中我們可以通過按F5鍵或點擊“執行”來運行SQL語句。 在輸入多條SQL語句的情況下，可以選中需要執行的代碼，然後按F5來只執行被選中的部分。

sqlcmd命令行管理工具

通過純指令的方式來管理SQL server資料庫服務端。

開始菜單à 運行à輸入cmd打開命令行視窗à輸入sqlcmd /? 查看sqlcmd命令的幫助信息。

在該命令行下我們可以通過sql語句來操作資料庫。

比如：

sqlcmd

use test

select * from student

go

最後，必須輸入go才會開始執行SQL語句。exit退出sqlcmd命令行狀態。

修改數據表結構

很多時候我們需要修改數據表欄位結構，比如添加欄位、修改欄位類型和欄位名，但是SQL server預設情況下會阻止我們對數據表結構的修改。所以我們需要更改SQL Server的設置參數。

工具菜單à 選項à 打開”選項”彈窗中的”Designers”選項卡à取消”阻止保存要求重新創建表的更改”前面的選中狀態。

T-SQL基本語法

select語句

語法：

SELECT 欄位列表 FROM 表名

where子句

where運算符

group by子句

將指定欄位中的相同的值進行分組。值相同的只顯示一行。

示例1：

SELECT age,COUNT(name) from student group by age

示例2：

在sql server 中所顯示的欄位列表中，不能使用group by後面沒有出現過的欄位名，除非使用聚合函數。

SELECT age,address,COUNT(name) from student group by age,address

order by子句

比如倒序排序

SELECT * from studentorder by id DESC

top子句

Having子句

用來給分組設置條件

示例：

SELECT age,name from student group by age,name having name = '小李'

DISTINCT子句

清除並返回結果中重覆的值。

SELECT DISTINCT age from student

insert into插入數據

一次插入一行數據

insert into student (name,age,sex,address,phone) values('小寶',13,1,'城革大本營',12345678)

一次插入多行數據

insert into student (name,age,sex,address,phone) values
('大寶',28,1,'城革大本營',12345678),
('小寶',13,1,'城革大本營',12345678),
('老寶',82,1,'城革大本營',12345678);

省略欄位名按表的欄位順序來插入數據

insert into student values('小白樓',60,1,'沙坪壩',12345678)

註意：這種方式必須按照表的欄位順序(除了主鍵ID)來排列語句中的欄位值，並且所有欄位都必須填寫值

聚合函數

AVG() 求平均值

SUM() 求合

MIN()/MAX() 求最大最小值

COUNT() 統計行數

UPDATE語句

update dbo.student set name='小白龍' where id = 14

DELETE語句

delete dbo.student where id=14

練習

創建一張學生數據表，包含欄位id、name、age、sex、address、phone、classNum

1、 一次性插入5條學生數據，並且不寫欄位名。

2、 用select語句查詢ID為2到ID為4之間的記錄，(用BETWEEN關鍵字)。

3、 查詢出所有姓王的同學(用LIKE模糊查詢)。

4、 查詢出班上年齡為(16、17、23、24)的同學

5、 統計各班分別有多少名學生

6、 分別統計男生與女生的年齡總合。

7、 找到年齡最大的女生。

8、 修改id為3的學生姓名為”李小蟲”

9、 刪除id為3的學生。

連接查詢

同時查詢多張數據表並將這些數據表以一定的邏輯關係進行連接，讓它們顯示的結果類似於一張數據表。

與連接有關的關鍵字：

INNER JOIN 、OUTER JOIN ( LEFT和RIGHT)、FULL JOIN、CROSS JOIN

內部連接

它根據一個或幾個相同的欄位將記錄匹配在一起，將這兩張表中的數據一起查詢出來。

內部連接的特點是，只顯示有關聯的數據，但是沒有關係的數據是不會被顯示出來的。

語法：

SELECT <欄位列表> FROM <第一張表> <連接類型> <第二張表> <ON 連接條件>

二表連接，示例：

select * from student INNER JOIN class ON student.cid = class.id

多表連接，示例：

select student.name,classInfo.className,teacher.name from student
INNER JOIN 
classInfo  ON student.cid = classInfo.cid
INNER JOIN 
teacher ON classInfo.teacher= teacher.tid

多表連接的使用別名，省略as

select s.name,c.className,t.name from student as s
INNER JOIN 
classInfo as c  ON s.cid = c.cid
INNER JOIN 
teacher as t ON c.teacher= t.tid

我們可以通過as關鍵字來給數據表定義一個別名，而且通過這個別名調用表中的欄位。

註意：只要定義了別名，就必須使用別名，原表的名字就不能再用了。

而且as關鍵字是可以省略的：

select s.name,c.className,t.name from student  s
INNER JOIN classInfo  c  ON s.cid = c.cid
INNER JOIN teacher  t ON c.teacher= t.tid

補充：內部連接的INNER JOIN可以簡化為JOIN ，效果是一樣的。

外部連接

內部連接有一定的排他性，第二張表是對第一張表的補充，如果第一張表不需要第二張表中的某些數據，那麼第二張表中不被需要的數據就不會被顯示出來。

語法：

SELECT <欄位列表> FROM <左表><LEFT | RIGHT > [OUTER] JOIN <右表> ON <連接條件>

如果使用LEFT就是顯示左表中的所有數據，如果使用Right就是顯示右表中的所有數據

示例：

select *from student as s RIGHT JOIN Class Info as c  ON s.cid = c.cid

多部外部連接示例：

select * from student  s RIGHT JOIN classInfo  c  ON s.cid = c.cid
LEFT JOINteacher t ON c.teacher=t.tid

完全連接

完全連接( FULL JOIN 或 FULL OUTER JOIN )

用於顯示所連接的所有表的所有數據，即使這條數據沒有任何關聯關係。

select *from student  s FULL JOIN classInfo  c  ON s.cid = c.cid

練習:

1、 先重做上課時講的例子。

2a、 假設現在製作一個超市購物系統，產品信息表(product)(id、name、price)、用戶表(customer)(id、name)、購物清單表(saleList)(id、產品編號pid、用戶編號cid)

2b、 用一條select語句查詢某個用戶的購清單上的所有產品。

2c、 用一條select語句查詢得到某個用戶的購清單上的所有產品的總價。

3a、假設現在製作一個電影院的數據查詢系統，坐位表(site)(id、row、col)、客戶表(customer)(id、name、phoneNum)、電影票(ticket)(id、cid、sid、mid)、電影表(movie)(id、name、mtime)

3b、查詢某一場電影的所有坐位上的客戶的信息。

3c、查詢某一場電影的所有坐位上的客戶的信息，並且顯示空坐位。

（如何判斷一個欄位的值為NULL值：

select * from movie where name is null）
select s.id,c.name from dbo.ticket t
join dbo.customer c on t.cid=c.id
join dbo.movie m on t.mid=m.id
right join dbo.site s on t.sid = s.id
where m.id=1
union
select id,'無座' as name  from site where id not in
(select site.id from ticket
join dbo.customer on ticket.cid=customer.id
join dbo.movie on ticket.mid=movie.id
right join dbo.site on ticket.sid = site.id
where movie.id=1)

3d、查詢某一個客戶看過的所有電影的名稱。

子查詢

它是指一個select查詢語句，並不是直接從數據表中來得到數據，而是從另外一個查詢語句的結果集中來進行查詢。

示例：

select s.name,s.age,s.sex from (

select * from student where sex = 0

) as s

where age >20

其中，在from關鍵字的後面，並不是數據表而是select語句。

交叉連接

交叉連接在本質上，也可以看做是一種內連接。只顯示有一關聯的數據。

示例

--內連接寫法

select * from classInfo

inner join teacher

on classInfo.teacher=teacher.tid

--交叉連接寫法

select * from classInfo,teacher

where classInfo.teacher=teacher.tid

兩者的結果是一樣的

聯合UNION

使用兩個或兩個以上查詢合併後只返回一個結果集

比如：

得到班上年齡大於20和所有男生的合集

select * from student where age>20

union

select * from student where sex = 1

前提每條select語句返回的欄位列表的個數和順序必須是一致的。

聯合後返回重覆的數據

union聯合後的結果自動去除掉多個select結果中的重覆數據，如果需要重覆顯示這些重覆數據，我們可以使用union all關鍵字：

select * from student where age>20

union all

select * from student where sex = 1

創建與修改資料庫、表

SQL Server中的對象名

多數情況下我們使用的是數據表或資料庫的簡寫形式，實際上SQL server中的數據表有4層命名約定。

[數據伺服器名.[資料庫名.[模式名.]]] 對象名

.test.dbo.student

資料庫伺服器名：預設是指當前已登陸的這個數據伺服器。

資料庫名：預設是指在客戶端左上角的下拉列表中已選擇的資料庫名，或用use 指令指定資料庫。

use test select * from student where sex = 1

模式名

SQL server對象可以擁有兩種模式名。

第一種模式：該對象擁有的許可權的用戶。

第二種模式：預設dbo，允許多個登陸用戶共用的一種訪問模式。

模式所代表的就是訪問許可權，通常我們使用預設的dbo模式。

CREATE語句

它用來創建資料庫對象

語法：

CREATE <對象類型> <對象名稱>

CREATE DATABASE news

CREATE TABLE newContext( id int )

CREATE DATABASE創建資料庫

新創建的資料庫，除了創建者、系統管理員、資料庫所有者以外，其他人都無法訪問。

CREATE DATABASE 的完整語法

CREATE DATABASE
[
[ON | PRIMARY ]
(
[NAME = ‘實例名’ ,]
[FILENAME = ‘文件名’ ,]
[SIZE = 文件大小 ,]
[MAXSIZE = 文件最大容量]
)
]
[
[ON | PRIMARY ]
(
[NAME = ‘實例名’ ,]
[FILENAME = ‘文件名’ ,]
[SIZE = 文件大小 ,]
[MAXSIZE = 文件最大容量]
)
]
 [COLLATE <核對名稱>]
[FOR ATTACH [ WITH <server broker> ||FROM ATTACH_REBUILD_LOG ||WITH DB_CHAINING ON|OFF|TRUSTWORTHY ON|OFF  ]]
[AS SNAPSHOT OF<源資料庫名>]
ON

用在兩個地方：一是定義資料庫文件的位置。二是定義數據日誌庫文件的位置。

PRIMARY 關鍵字用於指定多個資料庫文件中的主文件。

NAME 指定文件的實例名稱。也就是在資料庫的邏輯名（非物理文件名）

FILENAME 就是指數據文件的物理位置和文件名，mdf(資料庫)  ldf(日誌文件)

SIZE 資料庫大小,可以在數字後面用KB或GB表示資料庫的大小。

MAXSIZE 最大小容量。

COLLATE

用於處理排序和字母大小寫等問題

FOR ATTACH

將已存在的一些資料庫文件附加到當前伺服器上。當前，這個文件必須是資料庫的一部分。

WITH DB_CHAINING

跨越資料庫所有權

TRUSTWORTHY

為sql server資料庫文件添加安全層

創建資料庫示例：

CREATE DATABASE TESE22BB
ON
(
NAME =TEST22BB,
FILENAME = 'e:\test22bb.mdf',
SIZE =30MB,
MAXSIZE = 50MB
)
LOG ON
(
NAME = 'TEST22BBLOG',
FILENAME='e:\test22bb.ldf',
SIZE = 10MB,
MAXSIZE = 20MB
)
GO

 

用這種方式，我們可以在指定的硬碟或U盤路徑之下創建資料庫。

註意：如果需要對資料庫文件進行複製、剪切或刪除操作。

查看資料庫信息

EXEC sp_helpdb ‘test’

以類似查詢語句的結果集的方式返回資料庫的大小、擁有者、創建日期、文件路徑等信息。

CREATE TABLE創建數據表

CREATE TABLE 數據表名

創建表之前確定是否已經選擇當前資料庫

完整語法

CREATE TABLE [資料庫.[資料庫所有者]] 數據表名
(
<欄位名><欄位的數據類型>
[DEFAULT <預設值表達式>]
|
[IDENTITY [seed,increment][NOT FOR REPLICATION] ]
[ROWGUIDCOL]
[COLLATE<COLLATION NAME>]
[PRIMARY KEY]
[NULL | NOT NULL]
[<column constraint 欄位約束>]
|
[table_constraint 表約束]
|
[欄位名 as 計算列表達式]
)
[ON (<文件組>)|DEFAULT]
[TEXTIMAGE_ON(<文件組>)|DEFAULT]
 

DEFAULT 預設值

指該欄位在沒有輸入值的情況下預設使用的值。

IDENTITY標識、自增量

預設情況下，每條記錄自動增加1

NOT FOR REPLICATION

就是指對這個表進行複製的時候，ID主鍵的值是重新排列，還是延用之前的ID

ROWGUIDCOL

是指將一個表中的數據複製到另一個表中時，如果產生ID重覆情況下，應用如何處理。

COLLATE

用於處理排序和字母大小寫等問題。

PRIMARY KEY

設置該欄位為主鍵

NULL/NOT NULL

是否允許為空

欄位約束

對欄位中輸入的數據進行規則的限制。

計算列

可以創建一個本身沒有任何數據的列，這個列的值由其他列來動態的生成。

比如：

PCount AS price*num

這裡我們就定義了一個計算列，總價=單價*數量

註意：

1、不能計算主鍵、外鍵、唯一鍵

2、只能引用當前數據表中的欄位

表約束

對插入表的數據進行限制

ON

如果資料庫由多個部分組成，我們可以指定數據表存儲在哪個部分。

TEXTIMAGE_ON

與ON的作用類似，但是它只有在表中有Text或Image類型的欄位時才有效。

創建數據表的示例：

use testStudent2;

CREATE TABLE student(

sid int IDENTITY PRIMARY KEY NOT NULL,

sName nvarchar(50) NOT NULL,

sAge int,

sSex bit  DEFAULT 0 NOT NULL,

sYW float DEFAULT 0 NOT NULL,

sSX float DEFAULT 0 NOT NULL,

sCount AS sYW+sSX

)

練習：

創建一個產品銷售表，欄位如下：pid、pname（產品名稱）、pPrice(產品價格)、pNum(產品銷售數量)、pCount(產品銷售總價= pPrice* pNum)，用CREATE語句創建這個數據表。

ALTER修改語句

ALTER <數據對象類型><數據對象名稱>

ALTER DATABASE 修改資料庫

修改資料庫名

ALTER DATABASE test MODIFY NAME = test22

將資料庫test改名為test22

修改資料庫大小

ALTER DATABASE test MODIFY FILE (SIZE = 500MB)

註意：不能變小，只能增大它的容量。

ALTER TABLE 修改數據表

最常見的操作就是修改數據表名和表中的欄位。

添加欄位

ALTER TABLE dbo.student

ADD --這個關鍵字代表添加

phoneNum char(20) DEFAULT '00000000',

sAddress nvarchar(100) ,

createTime DateTime DEFAULT GETDATE()

--GETDATE()代表獲取系統當前時間

修改欄位名

EXEC sp_rename ‘表名.原欄位名’ , ’新欄位名’ , ’COLUMN’

示例：

EXEC sp_rename 'student.createTime','regTime','COLUMN'

修改欄位類型

ALTER TABLE 表名 ALTER COLUMN 欄位名 類型

示例：

ALTER TABLE dbo.student

ALTER COLUMN sAge nvarchar(30)

刪除欄位

ALTER TABLE 表名 DROP COLUMN 欄位名

示例：

ALTER TABLE dbo.student

DROP COLUMN sAddress

欄位的值會被一起刪除

修改表名

EXEC sp_rename ‘原表名’,’新表名’

示例：

EXEC sp_rename 'student','studentInfo'

DROP語句

刪除資料庫對象，比如：刪除數據表、視圖、存儲過程、觸發器

語法：

DROP <數據對象> <數據對象名>

DROP語句可以同時刪除多張數據表

DROP TABLE 表1，表2，….

示例：

drop table table1,table2,table3

DROP