SQLServer筆記

------------恢復內容開始------------

-
-----------------------------------------------------SQL語句的執行順序------------------------------------------------------------------------

1>From 表
2>where 條件
3>group by 列
4>Having 篩選條件
5>select 5-1>被分組列,聚合函數列5-2>distinct>5-3>top
6>order by 列

-----------------------------------------------------GROUP BY---------------------------------------------------------------------------------

--當使用了分組語句(group by)或者是聚合函數的時候，在select的查詢列表中不能再包含其他列名，
-- 除了該列同時也出現了group by字句中，或者該列也包含了在某個聚合函數中
分組查詢語法: SELECT 被分組列, 聚合函數列 FROM 表名 WHERE 普通列 GROUP BY 被分組列 HAVING 被分組列, 聚合函數列 ORDER BY 被分組列, 聚合函數列

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

update 表 set 列 (select 列 from 表)--子查詢
delete from 表名 where....----delete語句如果不加where條件，表示將表中所有的數據刪除，加上where條件後，會按照where條件進行刪除對應的行
insert into 表名(列1，列2，列3) values(值1，值2，值3)

------------------------------------------------建表約束----------------------------------------------------------

create table Employees
(
EmpId int identity(1,1) primary key,
EmpName varchar(50) not null unique check(len(EmpName)>2),
EmpGender char(2) default('男'),
EmpAge int check(EmpAge>0 and EmpAge<120),
EmpEmail varchar(100) unique,
EmpAddress varchar(500) not null
EmpDepId int foreign key references Department(DepId) on delete cascade--on delete cascade級聯刪除
)
--------------------------------------------------------------------------------------------------------------------------------------
Connection 負責用來連接資料庫
ResultSet 結果集
PreparedStatement 負責用來執行sql語句
要用statement類的executeQuery()方法來下達select指令以查詢資料庫
executeQuery()方法會把資料庫響應的查詢結果存放在ResultSet類對象中供我們使用

第一步：載入驅動程式 Class.forName(DRIVER);
第二步：連接資料庫 Connection conn = DriverManager.getConnection(URL, "sa", "123456");
第三步：發送sql語句(增刪改) PreparedStatement pst = conn.prepareStatement(sql);
第四步：執行查詢
ResultSet rs = st.executeQuery();
while(rs.next()){
System.out.println(rs.getString("studentName")+"|"+rs.getString("studentAge"));
}

備份資料庫
backup database 資料庫名稱 to disk='路徑'
恢複數據庫
restore detabase 資料庫名稱 from disk='備份路徑'


SQL server中的常用數據類型

1.數字數據類型
整數型
bigint =long
int =int
smallint =shotr
tinyint =byte 0-255
bit 1~0

小數型
decimal
numeric 兩個一樣

貨幣型
money

2.字元串類型
char
nchar
varchar
nvarchar
text
ntext
varchar(max)
nvarchar(max)

3.時間類型
datetime

4.二進位
binary 固定長度
vninary 可變長度

-------------------------------------------------------
帶n的和不帶n的區別
char(2) 表示可以儲存兩個位元組。ab，12，胡(英文，數字1個位元組，中文2個位元組)

nchar(2) 表示無論存儲中文還是英文，數字，每個字元都占用兩個位元組。ab，12，胡偉(英文，數字2個位元組，中文2個位元組)

不帶n的這些數據類型，長度最長可以設置為8000，
帶n的這些數據類型，長度最長可以設置為4000

char(8000)
varchar(8000)

nchar(4000)
nvarchar(4000)
-------------------------------------------------------
帶var的和不帶var的區別
nchar 不帶var表示，固定長度
varchar 帶var表示，可變長度

//固定長度，存儲1字元也是要占用10個位元組的，會自動補9個空格
char(10) 1 10位元組
1111111111 10位元組

//可變長度，會根據實際儲存數據的大小動態重新分配存儲空間，相對來說節省存儲空間
varchar(10) 1 1位元組
11111 5位元組

//10，表示最多10個位元組，如果存儲的數據超過了10個位元組，那麼無論是固定長度還是可變長度都會報錯的。
-------------------------------------------------------
text 淘汰了，等於varchar(max)
ntext 淘汰了，等於nvarchar(max)

varchar(max) max表示4G
nvarchar(max) max表示4G

-------------------------------------------------------資料庫文件
數據文件 主要數據文件 有且只有一個 .mdf(primary data file 的縮寫)
次要數據文件 0或者多個 .ndf(primary data file 的縮寫)
日誌文件 無 至少一個 .ldf(log data file 的縮寫)

1. 字元串函數:
CHARINDEX CHARINDEX('ab', 'cdab') 返回 3 返回'ab'在'cdab'中的位置，
SUBSTRING SUBSTRING('abc', 2, 2) 返回 'bc' 從第2個位置開始截取長度為2的字元串
LEN LEN('1個逗逼') 返回 4 返回字元串的長度(非位元組長度)
UPPER/LOWER UPPER('aBc')/LOWER('aBc') 返回'ABC'/'abc' 將字元串中的字母轉換為大寫/小寫
LTRIM/RTRIM LTRIM(' abc')/RTRIM('abc ') 返回'abc'/'abc' 去掉字元串左邊/右邊的空格 同時去掉兩邊空格 RTRIM(LTRIM(' abc '))
REPLACE REPLACE('abc', 'b', 'x') 返回 'axc' 將'abc'中的'b'替換為'x'
STUFF STUFF('abcd', 1, 2, '你好') 返回 '你好cd' 刪除從第1個字元開始，長度為2的字元串，並插入 你好
2. 日期函數
GETDATE GETDATE() 返回當前日期
DATEADD DATEADD(mm, -2, GETDATE()) 返回當前日期-2月
DATEDIFF DATEDIFF(dd, '1989-07-14', GETDATE()) 返回兩個日期之間的間隔
DATENAME DATENAME(DW, GETDATE()) 返回 '星期幾' 以字元串形式返回當前日期指定的部分
DATEPART DATEPART(DW, GETDATE()) 返回 一個星期的第幾天 以整數形式返回當前日期指定的補分
3. 數學函數
CEILING/FLOOR CEILING(24.1)/FLOOR(24.4) 返回 25/24 返回大於24.1的最小整數、返回小於24.1的最大整數
ROUND ROUND(748.35, 1) 返回 748.40 四捨五入到小數點後1位
4. 系統函數
CONVERT CONVERT(VARCHAR(3), 123) 返回 '123' 轉換數據類型
DATALENGTH DATALENGTH('1個逗逼') 返回 7 返回任何數據類型的位元組數，漢字2位元組

-------------------------------------------------------
--在這裡編寫SQL語句命令
--1.創建一個資料庫
create database MyDatabaseOne
--2.刪除資料庫
drop database MyDatabaseOne
--3.創建資料庫的時候設置一些參數選項
create database MyDatabaseOne
on--指定主文件的屬性
(
--配置主數據的選項
name='MyDatabaseOne',--住數據文件的邏輯名稱
filename='E:\MySQLServerDatabase.mdf',--住數據文件的實際保存路徑
size=5MB,--初始化大小
maxsize=200MB,--最大文件大小
filegrowth=10%--每次增長

)
log on--指定日誌文件的屬性
(
--配置日誌文件的選項
name='MyDatabaseOne_log',--日誌文件的邏輯名稱
filename='E:\MySQLServerDatabase.ldf',--日誌文件的實際保存路徑
size=5MB,--日誌文件的初始大小
filegrowth=10%--每次增長
)
-------------------------在資料庫中創建一個表-------------------------
--將代碼環境切換到MyDatabaseOne
USE MyDatabaseOne
create table Departments
(
Auto int identity(1,1)primary key,
Departments nvarchar(50) not null,
)
--通過代碼，刪除master資料庫下的某些表
USE master

--drop database --刪除資料庫
drop table Table_1--刪除表
drop table Table_1 where 字句--刪除表中某一條

---創建一個員工表---
--<員工表>：
USE MyDatabaseOne
GO
create table Employees
(
EmpID int identity(1,1) primary key,//主鍵
EmpIDCard varchar(18) not null,
EmpName nvarchar(50) null,
EmpGender bit not null,
EmpJoinDate datetime,
EmpAge int,
EmpAddress nvarchar(300),
EmpPhone varchar(100),
DeptId int not null,
EmpEmail varchar(100),
)
drop table Employees
use MyDatabaseOne
create table Employees
(
EmpID int identity(1,1) primary key,
EmpIDCard varchar(18) not null,
EmpName nvarchar(50) null,
EmpGender bit not null,
EmpJoinDate datetime,
EmpAge int,
EmpAddress nvarchar(300),
EmpPhone varchar(100),
DeptId int not null,
EmpEmail varchar(100),
)

實體完整性：
primary key//(主鍵)約束 唯一識別每一條記錄的標誌，可以由多列共同組成**只能有一個，不許重覆，不許為null
identity//(自增)約束 列值自增，一般使用此屬性設置的列作為主鍵identtity(1,1)
unique//(唯一)約束 可以使用unique約束確保在非主鍵列中部存在重覆值，但是值可以為null

域完整性：
check(檢查)約束//用於限制列中的值的範圍()
foreign key(外鍵)約束//一個表中的foreign key指向另一個表中的primary key
default(預設值)約束//用於向列種插入預設值default'中國'
not null(非空)約束//用於強制列不接受null值
引用完整性：
引用完整性是指兩個表的主鍵和外鍵的數據對應一致，它建立在外鍵和主鍵的關係之上，在sql server中，引用完整性的作用表現在一下3個方面
1.禁止在子表中添加主表中不存在的記錄
2.禁止修改主表的值
3.禁止刪除子表中的有對應記錄的主表記錄
用戶自定義完整性：
主要是規則rule、約束constraint和觸發器trigger
StudentNum int references Tb_Student(StudentNum) 引入例子！
-------------------------SQL語句入門-------------------------
DDL(數據定義語言，建表，建庫等語句)
DML(數據操作語言)
DCL(數據控制語言)

SQL語句中字元串用 單引號、單等號
SQL語句不區分大小寫(取決與排序規則)

-----------------------------------------------------------------------向學生表中插入一條記錄
--insert into 表名(列1，列2，列3) values(值1，值2，值3)
--1，自動編號列，預設就會自動增長，所以不需要(預設情況下也不能向自動編號列插入值)
註意：如果欄位類型為varchar或者datetime，則必須使用單引號引起來
賦值與查看例子
insert into Tb_Teacher(TeacherName,TeacherAge,TeacherSalary,TeacherTel) 插入所有值(沒省略)
values('大蛇丸',25,999999,'15271000220')
select * from Tb_Teacher
insert into Tb_Teacher(TeacherName,TeacherTel) 插入部分值
values('火神','22022022022')
select * from Tb_Teacher
insert into Tb_Teacher
values('八神',25,88888,'11011011011') 插入所有值(省略)
select * from Tb_Teacher


同時插入多行數據
insert into Tb_Student
--values('200709002','凱奇',21,'法國','2007-09-02',3000.5) //values插入值
select '200709002','凱奇',21,'法國','2007-09-02',3000.5 union //select插入值(內容不需要())
select '200709003','saha',25,'印度','2007-09-02',1000.5 union
select '200709004','張小飛',21,'中國','2007-09-02',2000.5 //最後一行不需要union


-----------------------------------------------------------------------強行插入
--啟動自動編號列插入值
--啟動某個表的“自動編號列”手動插入值得功能
set identity_insert Tb_Teacher on
insert into Tb_Teacher(TeacherID,TeacherName,TeacherTel)
values(10086,'移動SB','10086')
set identity_insert Tb_Teacher off
select * from Tb_Teacher

--在SQL語句中的直接寫的字元串中，如果包含中文，一定在字元串前面加N
（因為當排序規則不是簡體中文的時候會亂碼）
例子
values(10086,N'移動SB','102016/7/13086')

-----------------------------------------------------------------------打開和關閉查詢結果視窗:ctrl+r

-----------------------------------------------------------------------更新語句：
--update 表名 set 列=新值，列2=新值，....where 條件
--update語句如果不加where條件，那麼表示對錶中所有條件都進行修改，所以一定要加where條件
select * from Tb_Teacher
update Tb_Teacher set TeacherAge=TeacherAge+1,TeacherName = TeacherName+'(男)' where TeacherAge = 26
update Tb_Teacher set Age=30 where Name='大蛇丸' or Age<25
select * from Tb_Teacher

--刪除數據語句
--delete from 表名 where....
--delete語句如果不加where條件，表示將表中所有的數據刪除，加上where條件後，會按照where條件進行刪除
--刪除Tb_Teacher表中的所有數據，自動編號沒有回覆到預設，仍然繼續編號
delete from Tb_Teacher
select * from Tb_Teacher
insert into Tb_Teacher
values('胡偉',25,200,'15271100220')
select * from Tb_Teacher
insert into Tb_Teacher
values('楊磊',26,300,'15271200220')
insert into Tb_Teacher
values('小軍',27,400,'15271300220')
insert into Tb_Teacher
values('張衡',28,500,'15271400220')

外鍵關係-級聯，
級聯刪除將先刪除子表中的相應記錄，再刪除主表記錄


--truncate table 表名
--如果要刪除表中全部數據，那麼建議使用truncate
--truncate特點:
--1.truncate語句不能跟where條件(無法根據條件來刪除，只能全部刪除)
--2.truncate同時自動編號恢復到初始值
--3.使用truncate刪除表中的所有數據要比delete效率高的多的多。
--4.truncate 刪除數據，不觸發觸發器
------------------------------------------------------------------------------------------------------------------------
use mstanford
drop table Tb_Student
create table Tb_Student
(
StudentNo varchar(20) primary key,
StudentName nvarchar(20) not null,
StudentAge int not null check(StudentAge>=20 and StudentAge<=30),
County nvarchar(20) not null default('中國'),
StuTime datetime not null,
Tuition money not null
)
select * from Tb_Student
update Tb_Student set StuTime='2009-09-01' --更新數據

------------------------增加數據 INSERT INTO
select * from Tb_Student_Coures
insert into Tb_Student_Coures values ('200709003','.net','2009-09-09','通過') --增加數據
insert into Tb_Student_Coures values ('200709004','jap','2009-09-09','NULL')
insert into Tb_Student_Coures values ('200709002','java','2009-09-09','通過')

update Tb_Student_Coures set CouresTime='2008-08-08',Notes='通過'where StudentNo='200709003' --更具條件更新數據


------------------------簡單查詢 SELECT
select * from mstanford.dbo.Tb_Student --查詢數據
select studentno from mstanford.dbo.Tb_Student order by studentno desc --查詢並且按降序排列 ASC(縮寫ascending)表示升序 DESC(縮寫descending)表示降序
insert into mstanford.dbo.Tb_Student_Coures values('200709004','jsp','2008-08-08','取消考試')--增加數據
insert into mstanford.dbo.Tb_Student_Coures values('200709004','java',2008-01-02,'NULL')
select StudentNo,Notes from mstanford.dbo.Tb_Student_Coures--查詢多列數據
select * from mstanford.dbo.Tb_Student_Coures--查看所有數據簡寫
select SCNo,StudentNo,CouresName,CouresTime,Notes from mstanford.dbo.Tb_Student_Coures--查看所有數據完整寫
select distinct studentno from mstanford.dbo.Tb_Student_Coures--distinct查看數據並且去掉重覆
select StudentNo as 學號,SCNo as 編號,CouresName as 課程名稱,CouresTime as 課程時間,Notes as 備註 from mstanford.dbo.Tb_Student_Coures--查詢使用別名


------------------------排序查詢 ORDER BY
update Tb_Student_Coures set CouresTime='2008-01-02' where SCNo='43' --更改
select * from Tb_Student_Coures order by CouresTime DESC --查詢所有某列降序
select * from Tb_Student_Coures order by StudentNo desc,CouresTime desc --查詢所有多列降序

------------------------查詢限定行 TOP N PERCENT
select top 3 * from Tb_Student_Coures --查詢表中前3行
select top 1 percent * from Tb_Student_Coures --查詢表中1%行(percent百分比)
select top 50 percent * from Tb_Student order by StudentNo desc,Tuition asc --查詢表中1%行(percent百分比)StudentNo 降序,Tuition 降序

------------------------條件查詢 WHERE --邏輯運算符 'NOT' 'AND' 'OR' 'IS NULL'返回TRUE 'IS NOT NULL'返回FALSE
select * from mstanford.dbo.Tb_Student_Coures where Notes='通過' -------------單條件查詢(使用比較運算符)
select * from mstanford.dbo.Tb_Student_Coures where SCNo>=40 -------------單條件查詢(使用比較運算符)
select * from mstanford.dbo.Tb_Student_Coures where SCNo>40 AND notes='通過' -------------多條件查詢(使用邏輯運算符)
select * from mstanford.dbo.Tb_Student_Coures where Notes is NULL -------------查詢表中 Notes值NULL空的行
select * from mstanford.dbo.Tb_Student_Coures where Notes is not null -------------查詢表中 Notes值為NOT NULL的行

select * from mstanford.dbo.Tb_Student_Coures where Notes != '通過' -------------查詢表中 notes值不是'通過'的行(不檢查值為NULL)的行
select * from mstanford.dbo.Tb_Student_Coures where not Notes = '通過' -------------查詢表中 notes值不是'通過'的行(不檢查值為NULL)的行

select * from mstanford.dbo.Tb_Student_Coures where (StudentNo='200709002' or StudentNo='200709003') and CouresName='.net' -------------理解運算優先順序ADN > OR，()限制優先順序

------------------------SQL Server內置函數，可以與INSERT UPTATE DELETE等一起使用：1.字元串函數，2.日期函數，3.數字函數，4.系統函數

select STUFF(PName,1,0,'拳皇_') as 名稱,WeaponID,SkillID from DB_King_Fighters.dbo.player --使用stuff刪除並且插入字元
select upper(CouresName) as 課程名稱 from mstanford.dbo.Tb_Student_Coures ------------- 查詢表中課程名稱，並將小寫轉換成大寫
select * from mstanford.dbo.Tb_Student_Coures where len(CouresName)>3 -------------查詢表中課程名稱大於3的選課信息
select * from mstanford.dbo.Tb_Student_Coures where CouresTime<GETDATE() -------------查詢選課日期再當前日期之前的選課信息
select * from mstanford.dbo.Tb_Student_Coures where DATENAME(DW,CouresTime)='星期三' -------------查詢選課為星期三的選課信息

select CONVERT(int,SUBSTRING(StudentNo,1,2))+CONVERT(int,SUBSTRING(StudentNo,3,2)) as 學號 from mstanford.dbo.Tb_Student_Coures -------------理解substring,convert

--CONVERT(int,SUBSTRING(StudentNo,1,2))--SUBSTRING(實例,截取開始位,截取結束位)
select * from mstanford.dbo.Tb_Student_Coures
update mstanford.dbo.Tb_Student_Coures set CouresTime='2009-09-09',CouresName='jsp',StudentNo='200709004' where SCNo=43
delete from mstanford.dbo.Tb_Student_Coures where SCNo=39--理解刪除表中某一行
select top 2 StudentNo,Notes from mstanford.dbo.Tb_Student_Coures where Notes='通過' order by StudentNo desc--理解查表行數，查表列數，查表條件，查表排序一起運用
select * from mstanford.dbo.Tb_Student_Coures where SUBSTRING(CouresName,1,1)='j' and Notes is not null--理解is not null的用法，不能使用!= null

------------------------------------------------------------LIKE 運算符
------------------------------------------------------------通配符 % 任意0個或者多個字元
select ProductName,UnitPrice from Products where ProductName like 'c%'--通配符%表示任意字元的匹配(以C開頭)
select ProductName,UnitPrice from Products where ProductName like '%t'--(以T結尾的)
select ProductName,UnitPrice from Products where ProductName like 'c%t'--(以c開頭以t結尾)
select ProductName,UnitPrice from Products where ProductName like '%t%'--(包含t)
------------------------------------------------------------通配符 _ 任意單個字元
select ProductName,UnitPrice from Products where ProductName like 't_fu'
select ProductName,UnitPrice from Products where ProductName like '_____'--產品名稱長度是5個字元的產品

select ProductName,UnitPrice from Products where ProductName like '_e%'--查詢第二個位元組為e的產品名稱
select ProductName,UnitPrice from Products where ProductName like '_a%' and QuantityPerUnit like '%pkgs%'
------------------------------------------------------------通配符 [] 指定一系列的字元，只要滿足這些字元其中之一且出現在[]通配符的位置的字元串就滿住查詢條件
select ProductName from Products where ProductName like '%[_]%'--名稱中帶有_的數據
update Products set ProductName='abc_123' where ProductName='huwei_520' --更改數據
select ProductName,UnitPrice from Products where ProductName like '%[abfg]'--名稱最後一位是[abfg]的數據


------------------------------------------------------------IN 運算符(相對於or，IN簡捷，後面可以是SQL語句)
select ProductName,SupplierID from Products where SupplierID=1 or SupplierID=4 or SupplierID=3
select ProductName,SupplierID from Products where SupplierID in(1,3,4) --同上兩句相等


------------------------------------------------------------BETWEEN 運算符
select ProductName,UnitPrice from Products where UnitPrice between 6 and 10 order by UnitPrice desc--unitprices價格在6-10之間的產品名稱和單價數據,unitprices按降序排列,排序規則放在語句最後
select LAStName,BirthDate from Employees where BirthDate between '1952-01-01' and '1960-01-01'--查詢出生區間出生日期between

------------------------------------------------------------聚合函數 SUM MAX MIN AVG COUNT
--DATE type 求min/max按照時間的先後排列的，日期越早月小，
--CHAR type 求min/max按照搜首字母A-Z的順序排列，越後越大
--漢字 type 求min/max按照全拼拼音進行比較，若首字母形同則比下一個字元
------------------------------------------------------------SUM聚合函數
select top 10 SUM(UnitPrice) AS 前十價格之和 from Products--前十價格之和
select * from Products
select SUM(UnitPrice*Quantity) AS 所有商品價格和 from [Order Details] where OrderID='10249'--OrderID是10249所有商品價格的和

------------------------------------------------------------MAX/MIN函數
select MAX(UnitPrice) AS 最高價格產品 from Products--最高價格產品
select MIN(BirthDate) AS 年紀最大員工 from Employees--年齡最大的員工生日
------------------------------------------------------------AVG集函數
select AVG(UnitPrice) AS 商品價格平均價 from Products
------------------------------------------------------------COUNT函數
select COUNT(ProductName) AS 商品個數 from Products--商品個數
select COUNT(*) AS 記錄數 from Products--查詢所有記錄數(包括空值)

------------------------------------------------------------多聚合函數一起用
select COUNT(*) AS 總記錄數,AVG(UnitPrice) AS 平均價格, MAX(UnitPrice)AS 最高價格 from Products--多聚合函數的使用

------------------------------------------------------------分組查詢
------------------------------------------------------------GROUP BY字句
分組查詢語法: SELECT 分組列, 聚合列 FROM 表名 WHERE 普通列 GROUP BY 分組列 HAVING 分組列, 聚合列 ORDER BY 分組列, 聚合列
select EmployeeID,MIN(OrderDate) AS 每個員工最早訂單時間 from Orders group by EmployeeID order by EmployeeID ASc--聚合函數與分組查詢共用
------------------------------------------------------------HAVING字句
select EmployeeID,COUNT(*) AS 訂單數量 from Orders group by EmployeeID having COUNT(*)>100
select EmployeeID,COUNT(*) AS 訂單數量 from Orders group by EmployeeID having COUNT(*)>100 and EmployeeID>2--和下一句對比，如果不是判斷結果集，那麼可以用下麵的寫法
select EmployeeID,COUNT(*) AS 訂單數量 from Orders where EmployeeID>2 group by EmployeeID having COUNT(*)>100
select * from Orders

------------------------------------------------------------作業1
select FirstName,LAStName,HomePhone from Employees where HomePhone like '(%)_5%122' --%_用法
select count(ProductName) AS 商品數, avg(unitprice) AS 平均價格,SUM(unitprice) AS 單價和, max(unitprice) AS 最高價, min(unitprice) AS 最低價 from Products--聚合函數使用
select ProductID AS 產品編號, MAX(UnitPrice*Quantity) AS 訂單額 from [Order Details] where ProductID>70 and OrderID>11020 GROUP BY ProductID order by MAX(UnitPrice*Quantity) desc--聚合函數 group by的使用

------------------------------------------------------------上機 1模糊查詢
select ProductName,UnitPrice from Products where ProductName like 'c_[a-f][^g-z]%'
select * from Products
UPDATE Products set ProductName='[楊磊牛逼]' where ProductID=1
select ProductName,UnitPrice from Products where ProductName like '%[%]%'--包含%的產品名稱和單價
select ProductName from Products where ProductName like '%[_]%'
select ProductName from Products where ProductName like '%[[]%]%'

select * from Employees where (City='london' or City='kirkland' or City='seattle') and HomePhone like '%2'--和下麵的一樣
select * from Employees where City in ('london','kirkland','seattle') and HomePhone like '%2'

------------------------------------------------------------2聚合函數
select AVG(datediff(YY,BirthDate,GETDATE())) AS 平均年紀,
MAX(datediff(YY,BirthDate,GETDATE())) AS 最大年紀
from Employees

select COUNT(*) AS 記錄次數,COUNT(Region) AS Region欄位值的個數 from Employees--計算記錄次數和Region not is null 的次數
select * from Orders
select CustomerID,OrderID from Orders where OrderID>11011 and EmployeeID>2
select CustomerID,COUNT(*) as 訂單數量 from Orders where OrderID>11011 group by CustomerID having COUNT(*)>2

select * from Customers
select Country as 國家,COUNT(CustomerID) as 客戶數量 from Customers group by Country
select Country as 國家,COUNT(CustomerID) as 客戶數量,CompanyName as 公司名稱名稱 from Customers where CompanyName like 'b%' group by Country,CompanyName
select Country as 國家,COUNT(CustomerID) as 客戶數量,CompanyName as 客戶公司名稱,Country as 國家 from Customers
where CompanyName like 'b%' and LEN(Country) between 5 and 10 group by Country,CompanyName,Country --理解集合函數，between運算符group by的用法

------------------------------------------------------------表的基本連接
------------------------------------------------------------兩表連接
use Northwind
--查詢屬於beverages和condiments類的商品名，切商品名以'c'開頭
select Categories.CategoryID,Categories.CategoryName,--種類ID，種類名稱
Products.CategoryID,Products.ProductName--商品.種類ID，商品.商品名稱
from Categories,Products
where Categories.CategoryID=Products.CategoryID--兩張表的連接條件
and CategoryName in('beverages','condiments')--查詢類別(查詢這兩個種類)
and ProductName like 'c%'

--select TB_A.A,TB_B.C FROM TB_A,TB_B WHERE TB_A.C=TB_B.C
select * from Categories
select * from Products
select * from [Order Details]

--TB_A 表和 --TB_B都存在欄位C，所以在select語句中使用該欄位時，一定要知名其所在的表，如TB_A.C、TB_B.C，其他的充滿欄位要需要進行同樣的處理，否則資料庫系統會報錯
--使用sel server關鍵字作為表名，列名的時候，需要使用“[]”包括起來，例如create table [order]
--select語句首先執行from字句，由於定義表別名是在from字句中執行，而在其他子句中使用，所以在select語句的任何子句中都可以使用表的別名
select c.CategoryID,c.CategoryName,
p.CategoryID,p.ProductName,
o.OrderID
from Categories as c,Products as p,[Order Details] as o
where c.CategoryID=c.CategoryID
and p.ProductID=o.ProductID
and CategoryName in('beverage','condiments')
and ProductName like 'c%'
and o.OrderID>1060

------------------------------------------------------------內連接
--內連接也稱為等同連接，返回的結果是兩個表中所有相匹配的數據，捨棄不匹配的數據
select * from Products
select * from Categories
select Categories.CategoryID as 種類ID,Categories.CategoryName as 種類名稱,
Products.CategoryID as 種類ID,Products.ProductName as 種類名稱
from Categories join Products
on Categories.CategoryID=Products.CategoryID
where CategoryName in('beverages','condiments')
and ProductName like 'c%'

select * from Customers
select * from Orders

select kh.CompanyName as 客戶公司,kh.ContactName as 客戶名字,kh.Phone as 客戶電話,dd.OrderID as 訂單編號,dd.OrderDate as 訂單日期
from Customers as kh,Orders as dd
where kh.CustomerID=dd.CustomerID
order by 訂單編號

select kh.CompanyName as 客戶公司,kh.ContactName as 客戶名字,kh.Phone as 客戶電話,dd.OrderID as 訂單編號,dd.OrderDate as 訂單日期
from Customers as kh left join Orders as dd
on kh.CustomerID=dd.CustomerID
order by 訂單編號

------------------------------------------------------------外連接
select * from Customers
select * from Employees
select kh.City as 客戶所在城市,yg.FirstName+yg.LastName as 員工姓名,kh.ContactName as 客戶姓名
from Employees as yg right join Customers as kh
on kh.City=yg.City


--全外部鏈接full on...on
select * from Customers
select * from Orders
select Orders.OrderID 訂單編號,Orders.OrderDate 訂單日期,Customers.CompanyName 客戶公司
from Customers full join Orders
on Customers.CustomerID=Orders.CustomerID

-----------------------------------------------------------SQL SERVER執行順序

--SELECT...
--FROM...
--WHERE...
--GROUP BY...
--HAVING...
--ORDER BY...


--查詢供貨商的公司名稱和所供應的商品名稱
select * from Suppliers
select * from Products
select * from [Order Details]
select s.CompanyName,p.ProductName
from Suppliers s,Products p
where s.SupplierID=p.SupplierID

select s.CompanyName,p.ProductName
from Suppliers s join Products p
on s.SupplierID=p.SupplierID

select s.CompanyName,p.ProductName,o.OrderID
from Suppliers s,Products p,[Order Details] o
where s.SupplierID=p.ProductID and p.ProductID=o.ProductID

select Suppliers.CompanyName,Products.ProductName,[Order Details].OrderID
from Suppliers join Products on Suppliers.SupplierID = Products.SupplierID join [Order Details] on Products.ProductID = [Order Details].ProductID

select * from Customers
select * from Suppliers
select c.CompanyName 客戶姓名,c.[Address] 客戶地址,s.CompanyName 供貨商公司,s.ContactName 供貨商聯繫人
from Customers c left join Suppliers s
on c.City=s.City

select * from Orders
select * from Employees
select * from Customers
select Orders.OrderID as 訂單編號,Employees.FirstName+Employees.LastName as 負責人姓名,Customers.CompanyName as 下訂單公司名稱
from Employees right join Orders on Orders.EmployeeID=Employees.EmployeeID left join Customers on Orders.CustomerID=Customers.CustomerID



---------------------------------------------------------------------------第二階段
1、資料庫設計階段
需求分析：收集信息
概念設計：標識實體、標識屬性、標識關係-》E-R圖[實體(Entity)關係(Relationship)圖] 軟體vision
邏輯設計：E-R圖轉換成相應的表並通過3大範式進行審核
物理設計：選擇合適物理實現
實施：
運行和維護：

範式(Normal Formate)NF
第一範式 確保每一列的原子性，不可再拆分
第二範式 除了主鍵外，所有的列都依賴於主鍵，並且沒有一個實體是組合主鍵
不符合第二範式會有問題
1數據冗餘
2跟新異常
3插入異常
4刪除異常
第三範式 非關鍵字對非主鍵的間接函數依賴


ER模型 E-R模型圖 實體(Entity)關係(Relationship)圖 vision
PowerDesigner 概念數據模型
PhysicalDiagram 物理圖

實體：實體是現實生活中區別於其他事物，具有自己屬性的對象，同一類實體的所有實例就是構成該對象的實體集。
屬性：屬性是實體的特征
關係：實體之間存在的聯繫


關係分類
1對1關聯 1:1屬於
班級 輔導員
一個班級只有一個輔導員
一個輔導員只負責一個班級
1對多關聯 1:N擁有
班級 學生
一個班級有多個學生
多個學生屬於一個班級
多對1關聯 N:1

辦事處與員工之間是一對多的關係，反之員工與辦事處之間就是多對一的映射基礎

多對多關聯 N:M
學生 教師
一個學生有多名老師(語文數學)
一個老師教多名學生


部門中擁有很多員工（1:N）
一個員工可以管理一個部門(1:1)
辦事處中擁有很多員工(1:N)
員工可以存在很多技能(M:N)

映射基數：
1：1（員工管理部門）
1：N（部門擁有員工）
N：1（員工屬於部門）
M:N（員工和技能）

--------------------------------------------------------------------------------------------------
變數 運算符 批處理語句 流程式控制制語句

全局變數
@@ERROR 返回執行的上一個語句的錯誤號
@@IDENTITY 返回最後插入的標識值
@@ROWCOUNT 返回受上一語句影響的行數
@@SERVERNAME 返回運行 SQL Server 的本地伺服器的名稱
@@MAX_CONNECTIONS 返回允許同時進行的最大用戶連接數

print @@version

局部變數：由用戶定義的變數，其作用域在定義它的代碼塊中（指存儲過程，函數，匿名的T—SQL代碼塊），一般是在兩個GO之間
局部變數：以@開頭，由用戶定義，先聲明再賦值最後使用

聲明：
declare @變數名 數據類型
聲明並賦值：
declare @變數名 數據類型=值

給變數賦值：
set @變數名 = 值
select @變數名 = 值
select @變數名 = 列名 from 表

使用變數：
列印變數：
print @變數名
select @變數名
select @變數名 as 別名

set 將已經確定的常量賦值給局部變數
select 將從資料庫中查詢的結果賦值給局部變數

go語句的作用：
go前面的語句執行完之後，才會執行其後的代碼
作為批處理語句的結束，go之前聲明的變數，在go之後不能使用

--------------------------------------------------------------------------------使用T-SQL編程
create database Company_DB
on(
name='Company_DB',
filename='E:\MySQLServerDatabase\Company_DB.mdf'

)
log on(
name='Company_DB_log',
filename='E:\MySQLServerDatabase\Company_DB.ldf'
)
create table Office
(
OfficeCode nvarchar(20) primary key,--辦公司代號
OfficeAddress nvarchar(100) not null unique--辦公司地址
)
create table Department
(
DeptNo nvarchar(20) primary key, --部門代號
DeptName nvarchar(20) not null unique,--部門名稱
Principal nvarchar(20) --部門負責人
)
create table Employee
(
EmpNo nvarchar(20) primary key,--員工號
EmpName nvarchar(20) not null,--員工名字
EmpAddress nvarchar(100) not null,--員工地址
EmpPhone varchar(12) not null,--員工電話
Birthday datetime not null,--員工生日
HireDate datetime not null,--入職時間
DeptNo nvarchar(20) foreign key references Department(DeptNo),--外鍵
OfficeCode nvarchar(20) foreign key references Office(OfficeCode)--外鍵
)

insert into Office
values ('C01','中山北路200號')
insert into Office
values ('C02','北京中路35號')
insert into Office
values ('C03','福州路10號')

insert into Department(DeptNo,DeptName)
values ('D01','技術部')
insert into Department(DeptNo,DeptName)
values ('D02','市場部')
insert into Department(DeptNo,DeptName)
values ('D03','行政部')

insert into Employee
values ('E001','Tom','凱撒大廈501','021-45364743','1980-10-02','2004-04-06','D01','C01')
insert into Employee
values ('E002','Jack','凱撒大廈502','021-45364743','1980-01-03','2004-05-16','D01','C01')
insert into Employee
values ('E003','White','凱撒大廈503','021-45364743','1980-01-04','2003-05-06','D02','C01')
insert into Employee
values ('E004','Smith','凱撒大廈504','021-45364743','1980-05-02','2000-05-22','D02','C02')
insert into Employee
values ('E005','John','凱撒大廈505','021-45364743','1980-06-01','2004-02-13','D02','C02')
insert into Employee
values ('E006','Slider','凱撒大廈506','021-45364743','1982-08-02','2005-03-12','D03','C02')
insert into Employee
values ('E007','Buth','凱撒大廈507','021-45364743','1983-02-12','2006-05-06','D03','C02')
insert into Employee
values ('E008','Jennifer','凱撒大廈508','021-45364743','1980-11-02','2004-05-16','D01','C03')
insert into Employee
values ('E009','Kelly','凱撒大廈509','021-45364743','1980-01-23','2004-05-22','D01','C03')
insert into Employee
values ('E010','Winston','凱撒大廈510','021-45364743','1978-10-02','2002-09-06','D01','C03')
insert into Employee
values ('E011','Joy','凱撒大廈511','021-45364743','1980-11-12','2004-12-06','D01','C03')

drop table Office
drop table Department
drop table Employee

select * from Office
select * from Department
select * from Employee

use Company_DB

declare @DeptNo nvarchar(10)='D01'--聲明變數並賦值
select @DeptNo

declare @EmpNo nvarchar(10)='胡偉'--聲明變數
print @EmpNo

--------------------------------------------------------------------------------
use Company_DB
go
--查詢Dept='D10'的員工號
declare @DeptNo nvarchar(10)='D01'
select EmpNo from Employee where DeptNo=@DeptNo

--把查詢的EmpNo的查詢結果最後一條記錄值賦值給局部變數@EmpNo
declare @EmpNo nvarchar(10) --定義變數
declare @DeptNo nvarchar(10)='D01'--定義點亮並且賦值
select @EmpNo=EmpNo from Employee where DeptNo=@DeptNo
select @EmpNo

--------------------------------------------------------------------------------
--找出一部門中工齡最大的員工（按照工齡升序排列,自動取最後一條數據賦值）
declare @EmpNo nvarchar(10)
declare @DeptNo nvarchar(10)='D01'
select @EmpNo=EmpNo from Employee where DeptNo=@DeptNo group by EmpNo order by MAX(DATEDIFF(yyyy,hiredate,getdate())) asc
print @EmpNo--輸出顯示

--更新部門負責人 E010 'D01'
update Department set principal = @EmpNo where DeptNo = @DeptNo


--找出二部門中工齡最大的員工(按照工齡升序，自動取最後一條數據賦值)
set @DeptNo='D02'--為變數@DeptNo重新複製
select @EmpNo=EmpNo from Employee where DeptNo=@DeptNo group by EmpNo order by MAX(DATEDIFF(yyyy,hiredate,getdate()))
print @EmpNo
update Department set Principal=@EmpNo where DeptNo=@DeptNo

--找出三部門中工齡最大的員工(按照工齡升序，自動取最後一條數據賦值)

set @DeptNo='D03'
select @EmpNo=EmpNo from Employee where DeptNo=@DeptNo group by EmpNo order by MAX(DATEDIFF(yyyy,hiredate,getdate()))
print @EmpNo
update Department set Principal=@EmpNo where DeptNo=@DeptNo

go
select * from Department
go

print '錯誤號碼'+convert(nvarchar(225),@@error
select @@VERSION as '版本代號'

------------------------------------------------運算符-----------------------------------
declare @OptNumber int=12436
--print '轉換之前'+@OptNumber 失敗int要想和varchar格式一起列印需要把int轉換成varchar
print '轉換前：'+convert(varchar(5),@optNumber)
declare @reverse varchar(5)
--取除個位數
declare @unit int = @OptNumber%10--6
set @reverse=(CONVERT(varchar(1),@unit))--6賦值給倒序變數，賦值順序左→右
print '逆序number:'+@reverse
--去掉個位數，變數OptNumber將變成1243
set @OptNumber=@OptNumber/10--1243
set @unit=@OptNumber%10--3
set @reverse=@reverse+CONVERT(varchar(1),@unit)
print '逆序number:'+@reverse
set @OptNumber=@OptNumber/10--124
set @unit=@OptNumber%10--4
set @reverse=@reverse+CONVERT(varchar(1),@unit)
print '逆序number:'+@reverse
set @OptNumber=@OptNumber/10--12
set @unit=@OptNumber%10--2
set @reverse=@reverse+CONVERT(varchar(1),@unit)
print '逆序number:'+@reverse
set @OptNumber=@OptNumber/10--1
set @unit=@OptNumber%10--1
set @reverse=@reverse+CONVERT(varchar(1),@unit)
print '逆序number:'+@reverse

-----------------求1，2，3........100之間的奇數和
declare @tableNum table(Num int)--雖然求奇數不需要table類型變數，但是為了演示位運算符，所以定義table類型變數以便查詢
declare @loop int =1
while @loop<=100--迴圈次數
begin
insert into @tableNum values(@loop)
set @loop=@loop+1
end
select SUM(num) from @tableNum where Num & 1=1
-----練習
declare @table table(num int)
declare @i int =1
while @i<=100
begin
insert into @table values (@i)
set @i=@i+1
end
select sum(num) from @table where num % 2=1
------------------------------ALL關鍵字----------------------
--ALL：將特定值與查詢的結果集中所有的數據進行比較，若結果集中數據都滿足該條件則返回結果為true，否則結果為false
--語法：
--特定值 比較運算符 ALL(查詢語句)
create table Expertise(
SKillName nvarchar(20),
SKillLevel int,
EmpNo nvarchar(20)
)
insert into Expertise values('C','2','E010')
insert into Expertise values('C','1','E03')
insert into Expertise values('C','2','E02')
insert into Expertise values('C','3','E008')
insert into Expertise values('C','2','E005')
insert into Expertise values('C#','4','E006')
insert into Expertise values('C#','3','E007')
insert into Expertise values('DELPHI','1','E009')
insert into Expertise values('JAVA','2','E004')
SELECT * FROM Expertise

IF 2<=ALL(SELECT SKillLevel FROM Expertise)
PRINT '全部員工技能都通過了2級'
ELSE
PRINT '有員工技能不達標，還需要培訓'


-------------------------------ANY關鍵字---------------------
--ANY：將特定值與查詢的結果集中所有的數據進行比較，若結果集中任意一個數據滿足該條件則返回結果為true，否則結果為false
--語法：
--特定值 比較運算符 ANY(查詢語句)
select * from Expertise
--if 0<=ANY(SELECT SKillLevel FROM Expertise)
if 2<=ANY(SELECT SKillLevel FROM Expertise where EmpNo='E010')
PRINT '已經有員工技能超過了2級'
ELSE
PRINT '全部員工的技能不達標，需要加強練習'

-------------------------------EXISTS關鍵字---------------------
--Exists：判斷查詢的結果集中是否存在數據，若存在數據，則結果為true，否則結果為false
--註意：可以使用not exists
select * from Employee
if Exists (SELECT * FROM Employee WHERE EMPADDRESS IS NULL)
PRINT '有員工沒有填寫住址'
ELSE
PRINT '全部都填寫住址了'
----------------------------------------------------SQL流程式控制制語句-------------
--BEGIN-END
--作用：相當於JAVA,C#中{}
--註意
--當語句塊中語句多於一句時，需要使用begin-end
--degin-end之間必須只有存在一條語句
create table Salary(
Id int identity(1,1) primary key,
EmpNo NVARCHAR(20) FOREIGN KEY references Employee(EmpNo),
Salary money,
StartTime datetime
)
select * from Salary
select * from Expertise
select * from Office
select * from Department
select * from Employee

insert into Salary values ('E001','5000','2004-04-06 00:00:00')
insert into Salary values ('E002','6500','2004-05-16 00:00:00')
insert into Salary values ('E003','5500','2003-05-06 00:00:00')
insert into Salary values ('E004','7200','2000-05-22 00:00:00')
insert into Salary values ('E005','5000','2004-02-13 00:00:00')
insert into Salary values ('E006','8000','2005-03-12 00:00:00')
insert into Salary values ('E007','11000','2006-05-06 00:00:00')
insert into Salary values ('E008','4800','2004-05-16 00:00:00')
insert into Salary values ('E009','6700','2004-05-22 00:00:00')
insert into Salary values ('E010','8000','2002-09-06 00:00:00')
insert into Salary values ('E011','9500','2004-12-06 00:00:00')

--如果某個員工的技能等級已經達到4級以上且最高，給這個員工增加基本工資500
declare @emp nvarchar(10)
declare @level int
declare @salary money
--查詢最高技能等級的員工
select @emp=EmpNo from Expertise order by SKillLevel
print @emp--E006
select @level=MAX(SKillLevel) from Expertise
print @level--4
if @level>=4
begin
select @salary=Salary from Salary where EmpNo=@emp
insert into Salary values (@emp,@salary+500,GETDATE())
end
GO

-------------------------------------IF ELSE 條件語句-------------------------------------
/**
if-else
作用：進行條件判斷
工作原理：條件成立執行if語句塊，不成立else語句塊
註意：
else子句可以省略
在if，else子句均可以嵌套if-else結構
*/
declare @worktime int
declare @emp nvarchar(10)
declare @salary money
select @emp=EmpNo,@worktime=DATEDIFF(YYYY,hireDate,GETDATE()) from Employee order by DATEDIFF(YYYY,hireDate,GETDATE()) asc
print @emp
if @worktime>=4
begin
select @salary=Salary from Salary where EmpNo=@emp
insert into Salary values (@emp,@salary+1000,GETDATE())
end
select * from Salary

-------------------------------------WHILE 語句-------------------------------------
/*
while
作用：進行迴圈
工作原理：先判斷後執行
**/
select * from Expertise

declare @count int
while(1=1)--無限迴圈
begin
select @count=COUNT(*) from Expertise where SKillName like '%c%' and SKillLevel<3
if(@count>0)
begin
update Expertise set SKillLevel+=1 where SKillName like '%c%' and SKillLevel<3
end
else
break--沒有員工C語言技能低於3級後就退出迴圈
end
----------------------------------CASE 分支語句--------------------------------------------------
/*
case-end
作用：類似多重條件結構，用於進行多路分支
case 欄位名
when 值1 then 返回值1
when 值2 then 返回值2
...
else 返回值n
end
工作原理：將欄位的值逐一與when語句之後的值進行匹配，若存在匹配項，則返回then之後值，若不存在匹配項，則返回else之後返回值，其中else子句可以省略
case
when 條件1 then 返回值1
when 條件2 then 返回值2
...
else 返回值n
end
工作原理：將逐一判斷when語句之後的條件，若條件為真，則返回then之後值，若條件為假，則返回else之後返回值，其中else子句可以省略
*/
create table TimeWork(
Id int identity(1,1) primary key,
EmpNo nvarchar(10),
WorkState nvarchar(20),
WorkDate datetime
)
go
--創建表
insert into TimeWork values ('E001','病假','2008-10-11')
insert into TimeWork values ('E001','調休','2008-10-10')
insert into TimeWork values ('E001','正常上班','2008-10-08')
insert into TimeWork values ('E001','正常上班','2008-10-09')
insert into TimeWork values ('E001','正常上班','2008-10-12')
insert into TimeWork values ('E002','加班','2008-10-11')
insert into TimeWork values ('E002','休年假','2008-10-08')
insert into TimeWork values ('E002','休年假','2008-10-09')
insert into TimeWork values ('E002','正常上班','2008-10-10')
insert into TimeWork values ('E003','病假','2008-10-08')
insert into TimeWork values ('E003','正常上班','2008-10-09')
insert into TimeWork values ('E003','正常上班','2008-10-10')
insert into TimeWork values ('E003','正常上班','2008-10-11')
insert into TimeWork values ('E004','請假','2008-10-11')
insert into TimeWork values ('E004','休息','2008-10-08')
insert into TimeWork values ('E004','正常上班','2008-10-09')
insert into TimeWork values ('E004','正常上班','2008-10-10')
insert into TimeWork values ('E005','調休','2008-10-10')
insert into TimeWork values ('E005','正常上班','2008-10-08')
insert into TimeWork values ('E005','正常上班','2008-10-09')
insert into TimeWork values ('E005','正常上班','2008-10-11')
go
select * from TimeWork
drop table TimeWork

select WorkDate as 日期,
COUNT(case WorkState when '病假' then '0' end) as 病假,
COUNT(case WorkState when '調休' then '0' end) as 調休,
COUNT(case WorkState when '請假' then '0' end) as 請假,
COUNT(case WorkState when '正常上班' then '0' end) as 正常上班,
COUNT(case WorkState when '加班' then '0' end) as 加班,
COUNT(case WorkState when '休年假' then '0' end) as 休年假,
COUNT(case WorkState when '休息' then '0' end) as 休息,
COUNT(case WorkState when '其他' then '0' end) as 其他
from TimeWork group by WorkDate order by WorkDate desc

select * from TimeWork

select WorkDate,
病假=sum(case workstate when '病假' then 1 else 0 end),
調休=sum(case workstate when '調休' then 1 else 0 end),
請假=sum(case workstate when '請假' then 1 else 0 end),
正常上班=sum(case workstate when '正常上班' then 1 else 0 end),
加班=sum(case workstate when '加班' then 1 else 0 end),
休年假=sum(case workstate when '休年假' then 1 else 0 end),
其他=sum(case workstate when '其他' then 1 else 0 end)
from TimeWork
group by workdate
order by workdate desc
----------------------------------RETURN 語句--------------------------------------------------
/*
break：跳出迴圈結構
return：跳出當前的批處理，而進入下一個批處理的執行
goto：必須和label一起配合使用，跳轉到相應label標簽處
*/

declare @num int=0
while(1=1)
begin
set @num+=1
if(@num>10)
RETURN
PRINT @num
end
go
select @@CONNECTIONS as '連接數量'
go

----------------------------------GOTO 語句--------------------------------------------------
SELECT * FROM Expertise
declare @num1 int
declare @num2 int
LABEL1:
PRINT '最高的技能級別就是6級了，不能再升級了'
WHILE(0=0)
BEGIN
SELECT @num1 =COUNT(*) FROM Expertise WHERE SKillLevel<2
IF(@num1>0)
BEGIN
UPDATE Expertise SET SKillLevel=SKillLevel+1
SELECT @num2=COUNT(*) FROM Expertise WHERE SKillLevel>6--查看Expertise表中SKillLevel列有幾行大於6的數據並且把這個數據賦值給@num2
IF(@num2>0)--如果@num2>0,
BEGIN
GOTO LABEL1--跳轉到錨點LABEL1執行
END
END
END

PRINT @@IDENTITY

--------------------------------------課後操作題-----------------------------------------------
create table orders
(
OrdersID nvarchar(100),
ProductID nvarchar(100),
[Date] datetime,
Number int,
[Money] int
)

insert into orders values ('SD-90102001','HW03202','1990-10-20 03:20:00','5','340')
insert into orders values ('SD-90112001','HW03212','1990-11-12 10:22:00','10','1880')
insert into orders values ('SD-90112001','HW03205','1990-11-24 12:25:35','30','2400')
insert into orders values ('SD-90102001','HW03211','1990-10-12 05:06:23','20','500')
insert into orders values ('SD-90102002','HW03211','1990-10-15 06:38:36','10','250')
insert into orders values ('SD-90082002','HW03212','1990-08-24 11:39:09','5','950')
insert into orders values ('SD-90082003','HW03202','1990-08-26 10:21:17','5','340')
insert into orders values ('SD-90052003','HW03223','1990-05-01 11:45:18','10','240')
insert into orders values ('SD-90062003','HW03224','1990-06-01 05:40:54','20','5000')
insert into orders values ('SD-90102003','HW03223','1990-10-17 06:26:25','5','350')
insert into orders values ('SD-90012005','HW03212','1990-01-08 07:28:22','7','1300')
insert into orders values ('SD-90022005','HW03223','1990-02-02 08:05:02','10','700')
insert into orders values ('SD-90012005','HW03202','1990-01-07 11:11:08','5','340')
insert into orders values ('SD-90062005','HW03202','1990-06-22 12:17:30','5','340')

drop table orders
SELECT * FROM orders
-------------一下兩張表union all的表
select
商品編號=ProductID,銷售詳情='銷售數量',
[1月]=sum(case datepart(month,[Date])
when 1 then Number
else 0
end),
[2月]=sum(case datepart(month,[Date])
when 2 then Number
else 0
end),
[3月]=sum(case datepart(month,[Date])
when 3 then Number
else 0
end),
[4月]=sum(case datepart(month,[Date])
when 4 then Number
else 0
end),
[5月]=sum(case datepart(month,[Date])
when 5 then Number
else 0
end),
[6月]=sum(case datepart(month,[Date])
when 6 then Number
else 0
end),
[7月]=sum(case
when datepart(month,[Date])=7 then Number
else 0
end),
[8月]=sum(case
when datepart(month,[Date])=8 then Number
else 0
end),
[9月]=sum(case
when datepart(month,[Date])=9 then Number
else 0
end),
[10月]=sum(case
when datepart(month,[Date])=10 then Number
else 0
end),
[11月]=sum(case
when datepart(month,[Date])=11 then Number
else 0
end),
[12月]=sum(case
when datepart(month,[Date])=12 then Number
else 0
end)
from orders
group by ProductID
union all
select
商品編號=ProductID,銷售金額='銷售金額',
[1月]=sum(case datepart(month,[Date])
when 1 then [money]
else 0
end),
[2月]=sum(case datepart(month,[Date])
when 2 then [money]
else 0
end),
[3月]=sum(case datepart(month,[Date])
when 3 then [money]
else 0
end),
[4月]=sum(case datepart(month,[Date])
when 4 then [money]
else 0
end),
[5月]=sum(case datepart(month,[Date])
when 5 then [money]
else 0
end),
[6月]=sum(case datepart(month,[Date])
when 6 then [money]
else 0
end),
[7月]=sum(case
when datepart(month,[Date])=7 then [money]
else 0
end),
[8月]=sum(case
when datepart(month,[Date])=8 then [money]
else 0
end),
[9月]=sum(case
when datepart(month,[Date])=9 then [money]
else 0
end),
[10月]=sum(case
when datepart(month,[Date])=10 then [money]
else 0
end),
[11月]=sum(case
when datepart(month,[Date])=11 then [money]
else 0
end),
[12月]=sum(case
when datepart(month,[Date])=12 then [money]
else 0
end)
from orders
group by ProductID

----------------一下答案，參考上面容易理解c
select
商品編號=ProductID,
[1月銷清單]='數量'+convert(varchar,sum(case datepart(month,[Date])
when 1 then Number
else 0
end))+' '+'金額'+convert(varchar,sum(case datepart(month,[Date])
when 1 then [money]
else 0
end)),
[2月銷清單]='數量'+convert(varchar,sum(case datepart(month,[Date])
when 2 then Number
else 0
end))+' '+'金額'+convert(varchar,sum(case datepart(month,[Date])
when 2 then [money]
else 0
end)),
[3月銷清單]='數量'+convert(varchar,sum(case datepart(month,[Date])
when 3 then Number
else 0
end))+' '+'金額'+convert(varchar,sum(case datepart(month,[Date])
when 3 then [money]
else 0
end)),
[4月銷清單]='數量'+convert(varchar,sum(case datepart(month,[Date])
when 4 then Number
else 0
end))+' '+'金額'+convert(varchar,sum(case datepart(month,[Date])
when 4 then [money]
else 0
end)),
[5月銷清單]='數量'+convert(varchar,sum(case datepart(month,[Date])
when 5 then Number
else 0
end))+' '+'金額'+convert(varchar,sum(case datepart(month,[Date])
when 5 then [money]
else 0
end)),
[6月銷清單]='數量'+convert(varchar,sum(case datepart(month,[Date])
when 6 then Number
else 0
end))+' '+'金額'+convert(varchar,sum(case datepart(month,[Date])
when 6 then [money]
else 0
end)),
[7月銷清單]='數量'+convert(varchar,sum(case datepart(month,[Date])
when 7 then Number
else 0
end))+' '+'金額'+convert(varchar,sum(case datepart(month,[Date])
when 7 then [money]
else 0
end)),
[8月銷清單]='數量'+convert(varchar,sum(case datepart(month,[Date])
when 8 then Number
else 0
end))+' '+'金額'+convert(varchar,sum(case datepart(month,[Date])
when 8 then [money]
else 0
end)),
[9月銷清單]='數量'+convert(varchar,sum(case datepart(month,[Date])
when 9 then Number
else 0
end))+' '+'金額'+convert(varchar,sum(case datepart(month,[Date])
when 9 then [money]
else 0
end)),
[10月銷清單]='數量'+convert(varchar,sum(case datepart(month,[Date])
when 10 then Number
else 0
end))+' '+'金額'+convert(varchar,sum(case datepart(month,[Date])
when 10then [money]
else 0
end)),
[11月銷清單]='數量'+convert(varchar,sum(case datepart(month,[Date])
when 11 then Number
else 0
end))+' '+'金額'+convert(varchar,sum(case datepart(month,[Date])
when 11 then [money]
else 0
end)),
[12月銷清單]='數量'+convert(varchar,sum(case datepart(month,[Date])
when 12 then Number
else 0
end))+' '+'金額'+convert(varchar,sum(case datepart(month,[Date])
when 12 then [money]
else 0
end))
from orders
group by ProductID


--查看某個時間的月份
select datepart(month,[Date]) from orders where [date]='1990-10-20 03:20:00'

--上機階段1
---------------------------變數的定義和使用
/*
用戶狀態：待審核，已審核，被屏蔽
用戶角色：待審核會員，普通會員，VIP會員，超級管理員
用戶在剛註冊時狀態為待審核，角色是待審核會員，管理員進行確認和管理
*/
create table UserState(
Id int identity(1,1) primary key,
[State] nvarchar(100)
)
create table RoleInf(
Id int identity(1,1) primary key,
Rolename nvarchar(100),
Roledesc nvarchar(100),
Discount DECIMAL(3,2)
)
create table UserInf(
UserName nvarchar(50),
[PassWord] varchar(50),
RealName nvarchar(50),
Gender nvarchar(10),
IDCardNo varchar(20),
Email varchar(100),
Phone varchar(11),
[Address] nvarchar(200),
Balance money,
UserStateId int foreign key REFERENCES UserState(id),
UserRoleId int foreign key REFERENCES RoleInf(id),
PicUrl varchar(200)
)
drop table UserState
drop table RoleInf
drop table UserInf

select * from UserState
select * from RoleInf
select * from UserInf
--階段1
--指導部分
/*
用戶狀態：待審核、已審核、被屏蔽
用戶角色：待審核會員、普通會員、VIP會員、超級管理員
用戶在剛註冊時狀態為待審核，角色是待審核會員。管理員進行確認和管理
*/
declare @stateid int,@roleid int
insert into UserState values('待審核')
select @stateid=@@IDENTITY--使用select為局部變數賦值//當userstate用戶狀態表中添加數據以後@stateid數據會遞增改變
insert into RoleInf values('待審核會員','','1')
set @roleid=@@IDENTITY--使用set為局部變數賦值
--使用insert select union 語句多行插入
insert into userinf
select 'tom123','123456','tom','1','321032198008152919','[email protected]','','','1000',@stateid,@roleid,'d:\prt\images\tom.jpg' union all
select 'jack123','admin123','jack','1','321032198612126746','[email protected]','','','1000',@stateid,@roleid,'d:\prt\images\jack.jpg' union all
select 'Slider123','as123','Slider','1','321032198208213232','[email protected]','','','1000',@stateid,@roleid,'d:\prt\images\Slider.jpg' union all
select 'Micky123','888888','Micky','1','321032198910102