剛開始學C#時候的筆記,只是些基礎的語句如有錯誤請批評指正,謝謝,(使用SqlServer2012以上) 一. 資料庫概述 SQI全稱 structrued Query Language 1、數據：能被電腦識別存儲處理的符號的集合。包括：數字、符號、圖片、聲音、視頻。 《英文全稱Data》 2、數 ...
剛開始學C#時候的筆記,只是些基礎的語句如有錯誤請批評指正,謝謝,(使用SqlServer2012以上)
一. 資料庫概述
SQI全稱 structrued Query Language
1、數據：能被電腦識別存儲處理的符號的集合。包括：數字、符號、圖片、聲音、視頻。 《英文全稱Data》
2、資料庫：長期存儲在電腦內，有組織，可共用的大量數據的集合。 英文DataBase 簡寫DB
3、資料庫管理系統 （DataBase Management System）科學地組織和存儲數據，高效地獲取和維護數據的系統軟體。
4、四大系統資料庫：master資料庫、model資料庫、msdb資料庫和tempdb資料庫。他們是不可刪除的。
1）master資料庫：資料庫稱為主資料庫，保存著放在SQL Server實體上的所有資料庫。記錄了SQLServer系統的所有系統級別信息。並且還記錄所有的登錄帳戶、系統配置設置和已連接的Server(服務)等信息。
2）Model資料庫：稱為模板資料庫，用作在系統上創建的所有資料庫的模板。
3）Msdb資料庫：供SQLServer代理程式調度、警報和作業以及記錄操作員時使用。
4）Tempdb資料庫稱為臨時資料庫。保存所有的臨時表和臨時存儲過程。 Temporary 臨時的
5、SSMS是SQL Server Management Studio的縮寫
6、有兩種身份驗證：
1）SQL Server 身份驗證 （混合登錄） 預設賬戶：sa
2）Windows身份驗證。
7、使用ssms創建、使用、刪除資料庫 刪除的時候註意不能使用本資料庫
8、使用T-SQL語言創建、使用、刪除資料庫 （重點）
1）SQL （Structured Query Language 結構化查詢語言）
2）創建資料庫 create database 資料庫名
使用資料庫 use 資料庫名
刪除資料庫 drop database 資料庫名
9、 資料庫登錄如果發生錯誤2 ，應開啟（MSSQLSERVER）服務
啟動MSSQLSERVER服務：1、選中任務欄→右鍵選中任務管理器→打開服務，找到MSSQLSERVER，啟動
2、桌面→選中電腦，右鍵【管理】→服務和應用程式→①選中服務，找到MSSQLSERVER服務，啟動②sqlserver配置管理器→
10、常見的資料庫管理系統： 微軟的 SQL Server Office ACCESS
甲骨文 Oracle
Mysql 等等
11、SQL Server 2012資料庫系統的組成部分：
Analysis Services 分析服務（性能分析）
Integration Services 集成服務
Reporting Services 報表服務
DBMS 資料庫管理
二. 資料庫操作
資料庫操作
1、使用ssms創建、使用、刪除資料庫 刪除的時候註意不能使用本資料庫
2、使用T-SQL語言創建、使用、刪除資料庫 （重點）
1）SQL （Structured Query Language 結構化查詢語言）
2）創建資料庫 create database 資料庫名
使用資料庫 use 資料庫名
刪除資料庫 drop database 資料庫名
3、資料庫的備份與還原
備份
1）使用SSMS來操作 （SQL Server Management Studio）
2）使用SQL語句來操作 backup database Class1512netA to disk='d:\Class1512netA.bak'
backup database 資料庫名 to disk='路徑\備份資料庫名.bak'
還原
1）使用SSMS來操作 （SQL Server Management Studio）
2）使用SQL語句來操作 restore database Class1512netA from disk='d:\Class1512netA.bak'
restore database 資料庫名 from disk='路徑\備份資料庫名.bak'
4、資料庫的分離和附加
分離
1）使用SSMS來操作
2）使用SQL語句來操作 Exec sp_detach_db 資料庫名
exec sp_detach_db Class1512netA
附加
1）使用SSMS來操作
2）使用SQL語句來操作 Exec sp_attach_db 資料庫名,‘路徑\db.mdf’,’路徑\db.ldf’
exec sp_attach_db Class1512netA ,'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Class1512netA.mdf','C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Class1512netA_log.ldf'
5、修改資料庫的容量
alter database Class1512netA
modify file
(
name=Class1512netA,
size=20MB
);
go
6、查看資料庫
sp_helpdb 資料庫名
三. 表操作
A、數據類型
1、整型 small int
int
big int
2、字元型
非Unicode Unicode
①定長字元 char（n） nchar（n）
②變長字元 varchar（n） nvarchar（n）
3、浮點型
①近似值 單精度 real
雙精度 float
②精確值 decimal（5，2） 第一個參數表示總長度 第二個參數表示小數部分的位數
numeric
4、日期時間類型 datetime 8位元組
date 3位元組
time 5位元組
5、貨幣類型 money
6、布爾型 bit 0 False
1 True
7、唯一標識符 uniqueidentifier
B、建數據表
1、SSMS方式建立
2、SQL方式建立
create table 表名
（
欄位名1（列名） 數據類型,
欄位名2（列名） 數據類型,
...
欄位名n（列名） 數據類型
）
create table Student
(
Name varchar(20) not null,
Age int ,
Sex char(2) not null,
Home varchar(200)
)
3、修改表結構
1）增加列
alter table 表名
add 列名1 數據類型，
列名2 數據類型
alter table Student
add Hobby varchar(20),
Addr varchar(50)
2）刪除列
alter table 表名
drop column 列名1,列名2,列名n
alter table Student
drop column tel,Addr
3)修改列的數據類型
Alter table 表名
Alter column 列名 數據類型
alter table Student
alter column Home varchar(100)
4、刪除表
drop table 表名
drop table Student
四. 約束
約束
1、主鍵約束：保證實體的唯一性 （什麼樣的欄位可以選作主鍵，必須是唯一性的欄位）
①SSMS
②SQL
primary key 主鍵
identity 自增 identity（3,2） 從3開始每次增加2 第一個參數從幾開始，第二個參數每次增加幾個
要想使用identity必須是int型
2、非空約束：保證欄位不能為空
①SSMS 把勾去掉則就是非空的
②SQL
not null
3、檢查約束 check(條件) and or not >= <= > < = <>(不等於)
between ? and ? (包含邊界)
4、預設約束 default
5、唯一約束 unique
6、外鍵約束
foreign key references 表(列名)
create table Student
(
StuID int primary key identity,--主鍵自增
StuName varchar(20) not null,
StuAge int check(StuAge between 0 and 120),
StuSex char(2) check (StuSex='男' or StuSex='女'),
StuAddr varchar(50) default ('北京'),
StuTel varchar(14) unique
)
create table Score
(
StuId int foreign key references Student(StuID),
StuFen float
)
7、規則（Rule） 對存儲的數據表的列或自定義的數據類型中的值得約束
①創建規則
Create Rule 規則名 AS @變數名錶達式
CREATE RULE numCheck --rule 規則 numCheck 自己定義的規則名
AS @num>5 --使用@定義變數 num 自己定義的變數名 >5條件表達式
②綁定規則
EXEC sys.sp_bindrule '規則名','表名.列名'
EXEC sys.sp_bindrule 'numCheck','Test.TestNum' --綁定名為numCheck的規則到Test表的TestNum列
③取消綁定
EXEC sys.sp_unbindrule '表名.列名' --取消Test表的TestNum列的規則
④刪除規則
DROP RULE numCheck --Drop Rule 規則名 正在使用的規則不能被刪除
五.
數據更新
1、添加數據
1）部分列添加
insert into 表名 （列名1，列名2，...，列名n） values(值1,值2,...,值n)
insert into Student (StuName,StuAge,StuSex,StuTel)values ('關羽',45,'男','13010001100')
2)全部添加
insert into 表名 values(值1,值2,...,值n)
insert into Student values ('劉備',45,'男',default,'13010001103')
2、更新數據
1）全部更新
update 表名 set 列名1=值1，列名2=值2，列名n=值n
update Student set StuAge=18,StuSex='女'
2）帶條件的更新
update 表名 set 列名1=值1，列名2=值2，列名n=值n where 條件
update Student set StuAge=55,StuSex='男' where StuName='關羽'
3、刪除數據
1）全部刪除 (如果兩表有外鍵關係，先刪外鍵表，後刪主鍵表)
delete from 表
delete from Student
2）帶條件的刪除
delete from 表 where 條件
delete from Student where StuName='劉玉洲' and StuAddr='河南'/*-
六 .
T-SQL語言核心
1、什麼是T-SQL？
T-SQL是微軟公司在關係型資料庫管理系統SQL Server中的SQL3標準的實現，是微軟對SQL的擴展。
2、將T-SQL語句分為4大類,分為：①數據操作語句 insert delete update select
②數據定義語句
............create database drop database create table drop table alter
③數據控制語句 grant（授權） deny（拒絕） revoke（收回許可權）
④附加的語言元素
3、SQL-Server定義了兩種標識符：①規則標識符 第一，開頭不能是數字 第二包含 字母 數字 漢字 _ @ # 第三不能是系統保留字（關鍵字）
②界定標識符 [] '' [0-9] '%張%'
4、常量
①數字常量 （整數 小數 貨幣）
②字元串常量 （‘’引起的 字母數字特殊符號 ）
③日期時間常量 ‘2017-08-07’ '170807'
④符號常量 兩種 第一分隔符 第二標識符
5、變數
①全局變數 @@全局變數名
②局部變數 @局部變數名
DECLARE @局部變數名 類型 declare @count int 定義count為int類型變數
set @count=（select count(*) from student） 將count賦值為某查詢語句的結果
go
6、瞭解批處理和腳本
批處理是同時從應用程式發送到SQL Server並得以執行一組單條或多條T-SQL語句。這些語句為了達到一個整體的目標而同時執行。
7、運算符：
算術運算符：+ - * / %
比較運算符：= > < >= <= <>
邏輯運算符：and or not all some any between exsits in like
連接運算符：+
按位運算符： & |^ ~
優先順序： 算術
比較
邏輯
8、流程式控制制語句
①BEGIN END begin end 開始結束
②IF ELSE if else 如果否則
③CASE WHEN THEN 情況 當 則
④WHILE 當（迴圈）
⑤GOTO 跳轉
⑥RETURN 返回
--迴圈結構舉例
declare @count int --定義變數xount
set @count =0 --賦初值為0
while @count<10
begin
print 'count='+Convert(varchar(8),@count)
select @count=@count+1;
end
0
print '執行總次數為'+Convert(varchar(8),@count)
---分支結構舉例
declare @sex varchar(10)
set @sex='男 '
select
case @sex
when '男' then '他是男生'
when '女' then '她是女生'
else 'error'
end
七. 查詢
第七單元：查詢（簡單查詢）
1、查詢所有列
select * from student
--查詢指定列
select StuName from student
2、查詢前n行
select top 3 * from student
select top 3 StuName from student
3、帶where的查詢
--查詢學生表中id為5的學生的信息
select * from student where stuid=5
--查詢年齡在17-19之間的女學生
select * from student where stuage between 17 and 19 and stusex='女'
--查詢山西的男生
select * from student where stuhome='山西' and stusex='男'
--查詢所有男同學的姓名、電話
select stuname,stutel from student where stusex='男'
--查詢所有女生的姓名、籍貫
select stuname,stuhome from student where stusex='女'
4、查詢結果加別名 欄位 （as） 別名 as可以省略
select stuname 姓名,stuhome 籍貫 from student where stusex='女'
5、使用distinct去掉重覆
--查詢所有學生的性別 去掉重覆
select distinct stusex from student
--查詢所有學生的年齡去重
select distinct stuage from student
6、使用order by 排序 asc升序（預設） desc （降序）
--查詢學生信息按年齡升序排列
select * from student order by stuage asc
--查詢學生信息按姓名降序排列
select * from student order by stuname desc
--練習
--查詢年齡大於18歲的男生或 所有女生的信息
select * from student where (stuage>18 and stusex='男') or stusex='女'
--查詢年齡在17-19歲之間的所有女生的信息按姓名降序排列
select * from student where stusex='女' and stuage between 17 and 19 order by stuname desc
--查詢籍貫是山西的男同學的姓名、電話按照年齡升序排序
select stuname,stutel from student where stusex='男' and stuhome='山西' order by stuage asc
7、聚合函數
1）sum求和函數
select sum(stuage) 年齡總和 from student
2）avg求平均值
select avg(stuage) from student
3)max求最大值
select max(stuage) from student
--查詢年齡最大的女生的年齡
select max(stuage) from student where stusex='女'
4）min求最小值
select min(stuage) from student
5)count()計數
select count(stuid) from student
--查詢年齡為17歲的女學生的個數
select count(*) from student where stuage=17 and stusex='女'
8、分組排序 group by 欄位
重中之重： group by後面出現的欄位 才能出現select 後面 或者聚合函數
--查詢男女生各自的平均年齡
select stusex,avg(stuage) from student group by stusex
--查詢各省學生的數量
select stuhome 省份, count(*) 人數 from student group by stuhome
--用having子句對分組結果進行篩選
--重中之重：having後面的條件只能是group by後面出現的欄位或聚合函數
--查詢各省人數大於1的省份及數量
select stuhome 省份, count(*) 人數 from student group by stuhome having count(*)>1
--查詢各省人數中山東省的人數
select stuhome 省份, count(*) 人數 from student group by stuhome having stuhome='山東'
八. 查詢2（多表查詢、嵌套查詢、子查詢）
--1、模糊查詢 like
--通配符 % 代表0到多個字元 _表示一個字元 [] 在範圍內 [^]不在範圍之內
--查詢姓李的學生的信息
select * from student where stuname like '李%'
--查詢帶李字的學生的信息
select * from student where stuname like '%李%'
--查詢李某的信息（姓名為兩個字的）
select * from student where stuname like '李_'
--查詢李某某的信息（姓名為三個字的）
select * from student where stuname like '李__'
--查詢年齡23-25範圍之內的學生信息
select * from student where stuage like '2[3-5]'
--查詢手機號帶6的學生信息
select * from student where stutel like '%6%'
--查詢年齡不在的位數不在6、8、9的學生信息 []可以是連續的可以是不連續的
select * from student where stuage like '_[^6,8,9]'
-----------------------------------------------------------------------
--2、帶in的查詢
--查詢學生年齡在16 18 19 25這幾個年齡的學生的信息
select * from student where stuage in (16,18,19,25)
--3、帶not in的查詢
--查詢學生年齡不在16 18 19 25這幾個年齡的學生的信息
select * from student where stuage not in (16,18,19,25)
-----------------------------------------------------------------------
--4、多表查詢、嵌套查詢、子查詢
--查詢年齡大於平均年齡的學生
select * from student where stuage>(select avg(stuage) from student)
--查詢學生編號大於王寶的學號的學生的信息
select * from student where stuid>(select stuid from student where stuname='王寶')
select * from Category
select * from Product
--查詢手機類的所有商品
select* from Product where CategoryId=(select CategoryId from Category where CategoryName='手機' )
--查詢手機類商品按價格排序（降序）
select* from Product where CategoryId=
(select CategoryId from Category where CategoryName='手機')
order by ProductPrice desc
--查詢價格在4000-5000的手機商品的信息
select * from Product where ProductPrice between 4000 and 5000
and CategoryId=(select CategoryId from Category where CategoryName='手機')
--查詢各種商品的單價之和
select CategoryName from Category where CategoryId in (select CategoryId from Product group by CategoryId)
select Category.CategoryName,sum(Product.ProductPrice) from Category inner join Product on Category.CategoryId=Product.CategoryId group by Category.CategoryId,CategoryName
----------------------------------------------------------------------------
--掌握次序函數ROW_NUMBER()
select * from student order by stuage desc
select row_number() over (order by stuage desc) 名次列,* from student
九.
連接查詢
--1、內連接 (inner join) 等值連接
select * from a inner join b on 連接條件
select * from a inner join b on a.id=b.id
create table a
(
id int primary key , --課程id
name varchar(20) --課程名稱
)
create table b
(
stuid int primary key, --學生id
stuname varchar(20), --學生姓名
kid int, --課程id
)
select * from a
select * from b
--採用內鏈接
select * from a inner join b on a.id=b.kid
--左連接（左外連接）
select * from a left join b on a.id=b.kid
--右連接（右外連接）
select * from a right join b on a.id=b.kid
select * from b right join a on a.id=b.kid
--全外連接（full join）
select * from a full join b on a.id=b.kid
--自連接 本表與本表連接
create table employee
(
empid int primary key identity,
empname varchar(20) null,
empupid int
)
--查詢所有員工的姓名、以及直接領導的姓名
select a.empname 員工姓名,b.empname 上級領導 from employee a inner join employee b on a.empupid=b.empid
--查詢員工表中姓名相同的員工的信息
select a.empid,a.empname from employee a inner join employee b on a.empname=b.empname and a.empid<>b.empid
--查詢NULL的值
is NULL
is not NULL
十. 函數
--A、字元串函數
--1、ASCII() :返回一個字元或字元串最左的字元的ASCII碼 0 48 A 65 a 97
select ASCII('a')
select ASCII('Zhello')
--2、Char():將指定ASCII碼轉換為字元
select char(65)
select char(92)
--3、Left() ：返回字元串從左開始數 指定數量的字元串
select LEFT('football',4)
--4、Right()：返回字元串從右開始數 指定數量的字元串
select Right('football',4)
select Right('親愛的',2)
--5、Ltrim():去左邊的空格
select Ltrim(' I love you ')
--6、Rtrim():去右邊的空格
select Rtrim(' I love you ')
select Ltrim(Rtrim(' I love you ')) --去左右空格
--7、STR() :將數值類型轉換為字元數據
select STR(11314.1134,4,2) --第一個參數 數值 第二個參數是 總的位數（包括小數點） 第三個參數：小數點後保留幾位 註意：如果指定位數小於數的實際位數會顯示*
select STR(1314.1138,9,3)
--8、Reverse 字元串逆序
select Reverse('abc')
select Reverse('張三豐')
--9、LEN ：計算字元串長度
select LEN('woaini')
select LEN('我愛你嗎')
--10、substring： 截取字元串 第一個參數：字元串本身 第二個參數從哪開始截取（從1開始）第三個參數：截取幾個
select substring('noyes',1,3)
select substring('疾病發生科技發達',1,3)
--11、Lower() 變小寫
select Lower('HELLO')
--12、Upper() 變大寫
select Upper('hello')
--13、Replace 字元串替換 第一個參數：字元串本身 第二個參數 要替換的字元串 第三個參數：替換後的字元串
select Replace('I love you','love','hate')
--B、數學函數
--1、abs() 求絕對值
select abs(-8.8) select abs(5)
--2、sqrt() 求平方根 （要求非負數）
select sqrt(2)
select sqrt(3)
--3、Rand() 返回一個浮點型的隨機數 0-1之間的
select Rand()
--4、round() 四捨五入 約等於 第一個參數 ：數值本身 第二個參數：保留有效位數
select round(3.141,2) select round(3.1415926,4)
--C、日期函數
--1、GetDate() 獲得當前系統日期
select GetDate()
--2、Day() 獲得日期的天
select day('2012-08-31')
select day(GetDate())
--3、Month() 獲得月份的值
select Month('2012-08-31')
select Month(GetDate())
--4、Year()獲得年份的值
select Year('2012-08-31')
select Year(GetDate())
--D、系統函數
--1、返回資料庫的名稱 DB_Name(數據id) 可以用 DB_ID('資料庫名')
select DB_Name( DB_ID('Class1512netA'))
select DB_Name(8)
--2、返回登錄的用戶名 suser_sname()
select suser_sname()
select host_name() 返回主機名
use Class1512netA
--3、返回指定欄位的長度值 col_length(表，欄位)
select col_length('student','stusex')
select * from student
--E、轉換函數
--1、cast(x as type)
select cast (108 as varchar(3))
--2、Convert(數據類型,要轉的值)
select convert(varchar(10),10086)
select convert(varchar(100),Getdate())
--F、生成GUID （uniqueidentifier 唯一標識符）
--1、NEWID()
create table student2
(
stuid uniqueidentifier,
stuname varchar(20)
)
insert into student2 values(newid(),'張三')
select * from student2
select newid()
十一.存儲過程
1.概念 : 存儲過程（Stored Procedure）是在大型資料庫系統中，一組為了完成特定功能的SQL 語句集，存儲在資料庫中，經過第一次編譯後調用不需要再次編譯，用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。存儲過程是資料庫中的一個重要對象。
2.種類:
1系統存儲過程
以sp_開頭,用來進行系統的各項設定.取得信息.相關管理工作。
2本地存儲過程
用戶創建的存儲過程是由用戶創建並完成某一特定功能的存儲過程，事實上一般所說的存儲過程就是指本地存儲過程。
3臨時存儲過程
分為兩種存儲過程：
一是本地臨時存儲過程，以井字型大小(#)作為其名稱的第一個字元，則該存儲過程將成為一個存放在tempdb資料庫中的本地臨時存儲過程，且只有創建它的用戶才能執行它;
二是全局臨時存儲過程，以兩個井字型大小(##)號開始，則該存儲過程將成為一個存儲在tempdb資料庫中的全局臨時存儲過程，全局臨時存儲過程一旦創建，以後連接到伺服器的任意用戶都可以執行它，而且不需要特定的許可權。
4遠程存儲過程
在SQL Server2005中，遠程存儲過程(Remote Stored Procedures)是位於遠程伺服器上的存儲過程，通常可以使用分散式查詢和EXECUTE命令執行一個遠程存儲過程。
5擴展存儲過程
擴展存儲過程(Extended Stored Procedures)是用戶可以使用外部程式語言編寫的存儲過程，而且擴展存儲過程的名稱通常以xp_開頭。
3.優點
a.存儲過程只編譯一次,在創建時編譯,提高資料庫執行速度
b.避免複雜情況一條一條語句執行多次連接資料庫而造成的資源浪費
c.存儲過程可以重覆使用
d.安全性高,可設定某些用戶有制定存儲過程的許可權
4.定義一個存儲過程
--存儲過程的關鍵字是procedure或proc
-- 創建一個簡單的存儲過程
create proc 存儲過程名 -- 註 proc是procedure的簡寫,可以寫成create procedure 存儲過程 修改 alter 刪除 drop
as
存儲過程主體
帶輸入輸出參數的存儲過程
create proc 存儲過程名稱
@title nvarchar(max) not null, -- 參數名 參數類型 約束 參數類別(預設是輸入參數)
@info varchar(max) null output, -- output輸入輸出蠶食
@out varchar(max) out --輸出參數
as
begin
sql語句
end
5. 事務
創建一個帶事務的存儲過程
create proc 存儲過程名稱
as
begin tran
begin try
--在這裡寫sql語句
sql語句1
sql語句2
sql語句3
...
end try
begin catch
--當執行存儲過程發生異常時,查詢異常信息,回滾事務
select ERROR_LINE() errorLine,
ERROR_MESSAGE(),
ERROR_NUMBER()
--判斷執行到catch時證明sql語句執行錯誤,事務回滾
if(@@TRANCOUNT>10) rollback
end catch
--當sql執行到這裡的時候證明sql語句執行完成,提交事務
if(@@TRANCOUNT>10)
commit
(未完待續...)
