數據庫筆記

2020 年 5 月 6 日
筆記

數據庫介紹
SQL
- SQL定義
- SQL的分類：
視圖
事務

數據庫介紹

數據庫DataBase：

用於存儲和管理數據的倉庫

特點：

持久化存儲數據，數據庫就是一個文件系統

方便存儲和管理數據

使用統一操作方式

常見的數據庫軟件：

Oracel

MySQL
MySQL圖形化工具：SQLYog

SQL Sever（微軟中型數據庫）

DB2（IBM公司的）

SQLite（嵌入式的小型數據庫，應用在手機端）

數據庫：文件夾

表：文件

數據：數據

關係型數據庫多表之間的關係

一對一（了解）：

一對多（多對一）：外鍵在多中，是一的主鍵

多對多：學生老師必須新建一張表實現關係：新建一張表

SQL

SQL定義

SQL：結構化查詢語言
定義了操作所有關係型數據庫的規則

以分號結尾

使用空格、縮進提高可讀性

MySQL數據庫的SQL不區分大小寫

三種注釋：單行:– 注釋內容或# 注釋內容多行:/注釋/

SQL的分類：

DDL（數據定義語言：操作數據庫和表）

操作數據庫：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 數據庫名稱;

操作表

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)：修改

修改表名

alter table 表名 rename to 新的表名;

修改表的字符集

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

添加一列

alter table 表名  add 列名 數據類型;

修改列的名稱、類型

alter table 表名 change 列名 新列名 新數據類型;
alter table 表名 modify 列名 新數據類型;

刪除列

alter table 表名 drop 列名;

D （Delete）:刪除

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

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

主鍵約束：primary key

注意：

含義：非空且唯一
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;

自動增長

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

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

非空約束：not null

創建表時添加約束；

創建表完後，添加非空約束；

刪除非空約束；

唯一約束：unique，值不能重複

刪除唯一約束；

alter table 表名 drop index 列名;

外鍵約束：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
		分頁限定

查詢：

計算列

--查詢所有
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;

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

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

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;

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;

	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;   --不寫默認升序

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;

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產生影響

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

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

	/*格式：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 '\' --表示\後面的字符就是普通的字符

聚合函數【多行記錄返回一個值，通常用於統計分組的信息】

函數分類：單行函數：每行返回一個值

多行函數：多行返回一個值

聚合函數是多行函數

單行函數和多行函數一起用出錯

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

min()

avg():平均值

count():求個數 count(*)返回表中所有記錄的個數 count(字段名)：返回字段值非空的記錄個數，重複的記錄有效

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

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之後只能顯示分組後的整體信息，不能出現組內詳細信息

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;

--參數的位置不可以變

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

分類：

內連接

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 行臨時表，行數相乘，列數相加，笛卡爾積

select … from A,B where … 的用法

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

select … from A join B on …的用法

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

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

分別是SQL92和99標準；輸出結果是一樣的，推薦SQL99標準

SQL99更容易理解

SQL99標準中，on和where可以做不同的分工 on指定連接條件，where可以對連接後過濾

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結果是笛卡爾積，等價於什麼也不寫
自連接
一張表自己和自己連接起來查詢數據
起兩個不同的別名
子查詢返回的值只有一個

聯合：union，兩個select中使用

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

分頁：

--如：
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）*每頁顯示的條數

分頁操作是一個「方言」

嵌套查詢

視圖

為什麼需要視圖：簡化查詢，避免了代碼的冗餘，避免書寫大量重複的SQL語句.

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

視圖的格式：

create view 視圖的名字
as
	--select前面不能添加begin
	select語句
	--select後面不能添加end

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

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

事務

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

為什麼需要事務？事務主要用來保證數據的合理性和並發處理的能力；如：銀行轉賬、賣票

事務研究的問題

避免數據處於不合理的中間狀態

怎樣避免多用戶同時訪問是數據呈現給用戶的數據是合理的事務和線程的關係：事務也是通過鎖來解決很多問題的線程同步就是通過鎖來解決的 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

SQL定義

SQL的分類：

DDL（數據定義語言：操作數據庫和表）

DML（數據操縱語言：操作數據（增刪改））

DQL（查詢表中的數據記錄）（重點）

視圖

事務

VirMach 便宜 VPS

QNews

數據庫筆記

數據庫介紹

SQL

SQL定義

SQL的分類：

DDL（數據定義語言：操作數據庫和表）

DML（數據操縱語言：操作數據（增刪改））

DQL（查詢表中的數據記錄）（重點）

視圖

事務

分享此文：

Related Posts

分享Github上10個比較優秀的開源項目給大家收藏下！！！

嘔心搜集總結的15個「swoole」常見問題（一）

最短路徑：Dijstra（迪傑斯特拉）算法

豐田全新SUV諜照曝光：基於卡羅拉平台打造 更注重空間表現

VirMach 便宜 VPS

QNews

熱門搜尋

豐田全新SUV諜照曝光：基於卡羅拉平台打造更注重空間表現