目錄

• 資料庫介紹
• SQL
  • SQL定義
  • SQL的分類：
    • DDL（資料定義語言：操作資料庫和表）
    • DML（資料操縱語言：操作資料（增刪改））
    • DQL（查詢表中的資料記錄）（重點）
• 視圖
• 事務

資料庫介紹

• 資料庫DataBase：

用于存盤和管理資料的倉庫

• 特點：

1. 持久化存盤資料，資料庫就是一個檔案系統
2. 方便存盤和管理資料
3. 使用統一操作方式

• 常見的資料庫軟體：

1. Oracel
2. MySQL
   MySQL圖形化工具：SQLYog
3. SQL Sever（微軟中型資料庫）
4. DB2（IBM公司的）
5. SQLite（嵌入式的小型資料庫，應用在手機端）

• MySQL目錄結構：

1. 資料庫：檔案夾
2. 表：檔案
3. 資料：資料

• 關系型資料庫多表之間的關系

1. 一對一（了解）：
2. 一對多（多對一）：外鍵在多中，是一的主鍵
3. 多對多： 學生 老師 必須新建一張表 實作關系：新建一張表

SQL

SQL定義

SQL：結構化查詢語言
定義了操作所有關系型資料庫的規則

1. 以分號結尾
2. 使用空格、縮進提高可讀性
3. MySQL資料庫的SQL不區分大小寫
4. 三種注釋： 單行:-- 注釋內容或# 注釋內容 多行:/注釋/

SQL的分類：

DDL（資料定義語言：操作資料庫和表）

1. 操作資料庫：CRUD

• C（Create）：創建

	create database 名稱;
	create database if not exists 名稱;

--指定字符集為gbk：

	create database 名稱 character set gbk;

• R（Retrieve）：查詢

--查詢所有資料庫的名稱：
	show databases；
--查詢某個資料庫的字符集（查詢某個資料庫的創建陳述句）：
	show create database 資料庫名稱;

• U (Update)：修改

--修改資料庫的字符集
	alter database 資料庫名稱 character set 字符集名稱;

• D （Delete）:洗掉

--洗掉資料庫
		drop database 資料庫名稱;
--判斷資料庫是否存在，存在再洗掉
		drop database if exists 資料庫名稱;

• 使用資料庫

--查詢當前正在使用的資料庫名稱
	select database()
--使用資料庫
	use 資料庫名稱;

2. 操作表

• C（Create）：創建
  1. 語法：

create table 表名（列名1 資料型別1，
				  列名2 資料型別2，	
）;

資料庫型別：
1. int：整數型別
2. double：小數型別
3. date：日期，只包含年月日
4. datetime日期：包含年月日秒
5. timestamp：時間錯型別
6. 字串型別：varchar

• R（Retrieve）：查詢

--查詢某個資料庫中所有的表名稱
	show tables;
--查詢表結構
	desc 表名;

• U (Update)：修改

1. 修改表名

alter table 表名 rename to 新的表名;

2. 修改表的字符集

--首先學會查看 
	show create table 表名;
alter table 表名 character set 字符集名稱;

3. 添加一列

alter table 表名  add 列名 資料型別; 

4. 修改列的名稱、型別

alter table 表名 change 列名 新列名 新資料型別;
alter table 表名 modify 列名 新資料型別;

5. 洗掉列

alter table 表名 drop 列名;

• D （Delete）:洗掉

drop table 表名;
drop table if exists 表名;

• 約束：
  概念：對表中的資料限定，保證正確性、有效性、 完整性； 分類：

1. 主鍵約束：primary key

• 注意：

1. 含義：非空且唯一
   2. 一張表只能有一個欄位為主鍵
   3. 主鍵就是表中記錄的唯一標識

• 在創建表時，添加主鍵約束

create table stu(
	id int primary key,
	name varchar(20)
);

• 洗掉主鍵

alter table stu drop  primary key;

• 創建完表后，添加主鍵約束

alter table stu modify id int primary key;

• 自動增長

1. 概念：如果某一列是數值型別的，使用auto_increment可以來完成自動增長；
   2. 在創建表時，添加主鍵約束，并且完成主鍵自增長:
   3. 洗掉
   alter table stu modify ID int；

增加知識點：identity【主鍵自動增長，用戶不需要為identity修飾的主鍵賦值】 ...primary key
identity(100,5);--id從100開始，以5遞增，可以并不設定引數，為1,1；

2. 非空約束：not null

• 創建表時添加約束；
• 創建表完后，添加非空約束；
• 洗掉非空約束；

3. 唯一約束：unique，值不能重復

• 洗掉唯一約束；

alter table 表名 drop index 列名;

4. 外鍵約束：foreign key

• 創建表時，可以添加外鍵；
  語法：

create  table 表名(
...
外鍵列
constraint 外鍵名稱 foreign key （外鍵名稱） references 主表名稱（主表列名稱） 			);

• 洗掉外鍵

alter table   表名  drop foreign key   外鍵名稱  ；

• 創建表之后添加外鍵

alter table   表名  add constraint 外鍵名稱 foreign key （外鍵名稱） references 主表名稱（主表列名稱）；

DML（資料操縱語言：操作資料（增刪改））

• 添加資料
  語法：

insert into 表名（列名1，列名2...列名n） values（值1，值2...值n）;

注意事項：
1. 列名和值要一一對應；
2. 如果表名后，不定義列名，則默認給所有列添加值
3. 除了數字型別，其他型別需要使用引號引起來

• 洗掉資料
  語法:

delete from 表名 where 條件;

注意：
1. 如果不加條件，則洗掉表中所有記錄
2. truncate table 表名； -----效率更高，洗掉表，再創建一個一模一樣的空表

• 修改資料
  語法：

update 表名 set 列名1=值1，列名2=值2... where 條件;

注意事項：
如果不加任何條件，則會將表中所有記錄修改；

DQL（查詢表中的資料記錄）（重點）

--復雜查詢語法：
	select 
		欄位串列
	from
		表名串列
	where
		條件串列
	group by
		分組欄位
	having  
		分組之后的條件
	order by
		排序
	limit
		分頁限定

查詢：

1. 計算列

--查詢所有
select * from 表名;
	如：select * from banji;

--as可以起別名		
select banji_id as "班級號",banji_name as "班級名" from banji;
select banji_id,banji_name,banji_num*12 as "年薪",banji_name as "班級名" from banji;

--輸出的行數是banji表的行數，每行只有一個欄位，值是4		
select 4 from banji;         
		

2. distinct【不允許重復的】消除重復（多個NULL也可以過濾掉）

--如：
select jiaoshi_id from banji_jiaoshi_mapping;
select distinct jiaoshi_id from banji_jiaoshi_mapping;
	--若查詢兩列，則會把組合過濾，distinct放在兩列之前，不能放在中間，要不然會邏輯錯誤 

3. between

	--如：select * from banji 
	where banji_id>=1 and banji_id <=2;
--等價于
select * from banji
	where banji_id between 1 and 2;

select * from banji
	where banji_id <=1 or banji_id>2;
--等價于
select * from banji
	where banji_id not between 1 and 2;

4. in[屬于若干個孤立的值]

	如： select * from banji
	where banji_id in (2,4);
--等價于
select * from banji
	where banji_id=2 or banji_id=4;
	
	select * from banji
	where banji_id not in (2,4);
--等價于
select * from banji
	where banji_id!=2 and banji_id!=4;

5. top

	select top 2 * from banji;

select top 100 percent * from banji; --百分比

	select top 3 * from banji
	where banji_num between 1 and 60
	order by banji_num desc;  --降序

	select top 3 * from banji
	where banji_num between 1 and 60
	order by banji_num;   --不寫默認升序

6. null[空值]

select * from banji;
select * from banji where banji_num <> null;  --error
select * from banji where banji_num !=null;  --error
select * from banji where banji_num =null;   --error
--null不能參與<>   !=   =運算
--null可以參與is    not is
select * from banji where banji_num  is null;
select * from banji where banji_num  is not null;

7. order by【以某個欄位排序】默認升序asc

select * from banji order by banji_num;--默認升序排列
select * from banji order by banji_num desc;--降序

--出現多列是按一列排完排另一列，不是按組合排序
select * from banji order by banji_num,banji_id ;
select * from banji order by banji_id,banji_num ;
--asc是升序的意思，默認可以不寫，desc是降序
select * from banji order by banji_num desc,banji_id ;--desc只對banji_num產生影響
select * from banji order by banji_num ,banji_id  desc;--desc對banji_id產生影響

強烈建議為每一個欄位都制定排序的標準

8. 模糊查詢【匹配的條件必須用單引號括起來】

	/*格式：select 欄位的集合 from 表名 where 某個欄位的名字 like 匹配的條件
	通配符：
		% :表示任意0個或多個字符
	    _:[下劃線] 任意的單個字符
	    [a-f]: a到f的任意單個字符 只能是其中的任意一個字符  
	    [a,f] : a或f 
	    [^a-c]:不是a 也不是b也不是c的任意字符*/

select * 
	from banji 
	where banji_name like '二%'

select * 
	from banji 
	where banji_name like '二_'

select * 
	from banji 
	where banji_name like '[^a-c]_'

select * 
	from banji 
	where banji_name like '%\二%' escape '\' --表示\后面的字符就是普通的字符

9. 聚合函式【多行記錄回傳一個值，通常用于統計分組的資訊】

1. 函式分類：單行函式：每行回傳一個值

2. 多行函式：多行回傳一個值

3. 聚合函式是多行函式

4. 單行函式和多行函式一起用出錯

   如：聚合函式的分類：
   1.max()

   2. min()
   3. avg():平均值
   4. count():求個數 count(*)回傳表中所有記錄的個數 count(欄位名)：回傳欄位值非空的記錄個數，重復的記錄有效

可能遇到的問題： select max(banji_num),min(banji_num),count(*) from banji;

10. group by

use student;
select * from banji
alter table  banji add sal int;
update banji set sal=4000;
update banji set sal=5000 where banji_id in (2,4,6);
select dept,avg(sal)as "平均工資" from banji group by dept;

--使用了group by之后只能顯示分組后的整體資訊，不能出現組內詳細資訊

11. having【對分組后的資訊過濾】

• having使用時通常先使用group by
• 如果沒使用group by使用having意味著把所有記錄當做一組來過濾
• having子句出現的欄位必須是分組之后組的整體資訊，不是詳細資訊
• having中不能使用欄位的別名
• having和where區別： 相同：都是對資料過濾，只保留有效的資料 不同：where是對原始的記錄的過濾，having是對分組之后的記錄過濾

--如：
select dept,avg(sal)as "平均工資" from banji group by dept having avg(sal)>3000 order by dept desc;

--引數的位置不可以變

12. 連接查詢
    定義：將兩個或多個表以一定條件連接起來從中檢索出滿足條件的資料

分類：

• 內連接

1. select ... from A,B 的用法

select * from jiaoshi;
select * from banji;
update banji set banji_name='六班' where banji_id=6;

select * from banji,jiaoshi;-- 產生4*6 行臨時表，行數相乘，列數相加，笛卡爾積

2. select ... from A,B where ... 的用法

select * from banji,jiaoshi where banji_id=1;--產生4*1行
--對	select  ... from A,B where ... 產生的笛卡爾積用where中的條件進行過濾

3. select ... from A join B on ...的用法

select banji.sal from banji join jiaoshi on banji_id in(1,2);--join是連接，on是連接條件，必須是組合

4. select ... from A,B where ...與
   select ... from A join B on ...的比較

• 分別是SQL92和99標準；輸出結果是一樣的，推薦SQL99標準
  • SQL99更容易理解
  • SQL99標準中，on和where可以做不同的分工 on指定連接條件，where可以對連接后過濾

5. select、 from、 where、 join、 on、 group、 order、 top 、having的混合使用

--查詢的順序：
select top...
	from A
	join B
	on ...
	join C
	on ...
	where ...
	group by ...
	having ...
	order by ...

• 外連接【回傳滿足連接條件的記錄和不滿足條件的部分記錄】

• 內連接默認inner join 分類：
• 左外連接：回傳滿足連接條件的記錄和左表不滿足條件的部分記錄 left join 匹配則輸出，不匹配就用第幾行做匹配輸出第幾行，其中左邊為左表，右邊為null
  實際意義：回傳一個事物的相關資訊，如果該事物沒相關資訊，則輸出null；如：倉庫訂單
• 右外連接：回傳滿足連接條件的記錄和右表不滿足條件的部分記錄 right join 匹配則輸出，不匹配就用第幾行做匹配輸出第幾行，其中右邊為右表，左邊為null

• 完全連接
  • full join
    左外連接+右外連接-所有正常記錄=完全連接
    交叉連接
  • cross join結果是笛卡爾積，等價于什么也不寫
• 自連接
  一張表自己和自己連接起來查詢資料
  起兩個不同的別名
  子查詢回傳的值只有一個

13. 聯合 ：union，兩個select中使用

定義：表和表之間的資料以縱向的方式連接在一起 以前的所有的連接是以橫向的方式連接；
注意：要聯合成功，列數必須是相等的，型別是兼容的

14. 分頁：

--如：
select top 4 * from banji
	where banji_num not in (select top 6 banji_id from banji order by banji_num desc)
	order by banji_num desc;
 

1. 公式：開始的索引=（當前的頁碼-1）*每頁顯示的條數
2. 分頁操作是一個“方言”

15. 嵌套查詢

視圖

為什么需要視圖： 簡化查詢，避免了代碼的冗余，避免書寫大量重復的SQL陳述句.

什么是視圖？視圖邏輯上當做一張虛擬表看待，

視圖的格式：

create view 視圖的名字
as
	--select前面不能添加begin
	select陳述句
	--select后面不能添加end

視圖的優點：簡化查詢、增加資料的保密性
視圖的缺點：增加了資料庫維護的成本 視圖只能簡化查詢，但并不能加快查詢的速度

注意問題：1. 創建視圖的select陳述句必須為所有的計算列指定別名
2. 視圖不是物理表，是虛擬表
3. 不建議通過視圖更新視圖所依附的原始表的資料

事務

事務要么全部執行成功，要么全部執行失敗

為什么需要事務？ 事務主要用來保證資料的合理性和并發處理的能力； 如：銀行轉賬、賣票

事務研究的問題

1. 避免資料處于不合理的中間狀態
2. 怎樣避免多用戶同時訪問是資料呈現給用戶的資料是合理的 事務和執行緒的關系： 事務也是通過鎖來解決很多問題的 執行緒同步就是通過鎖來解決的 synchronized

事務和第三方插件的關系 直接使用事務庫技術難度大，所以大多使用第三方插件庫來實作
第三方插件要想完成預期的功能，一般必須借助資料庫的事務機制

T-SQL使用下列陳述句管理事務：
開始事務：begin transaction
提交事務：commit transaction
回滾（撤銷）事務：rollback transaction

TL-SQL：速度快，安全性好；難移植（也可以使用Java在編譯器去實作）

一旦事務提交或者回滾，則事務結束

事務三種運行模式：
1. 自動提交事務
2. 顯式事務 每個事務均以begin transaction開始，以commit或rollback顯式結束
3. 隱形事務 在前一個事務完成時新事務隱式啟動，但每個事務仍以commit或rollback陳述句結束

事務的四大屬性：
1. 事務是一個完整的操作，事務的各步操作不可再分
2. 當事務完成時，資料必須處于一致狀態
3. 當前事務與其他未完成的事務是隔離的
4. 事務完成后，事務日志能夠保持事務的永久性

標籤：SQL Server

上一篇：sql某個日期是當年的第幾周

下一篇：sqlserver 資料庫遷移