23-數據庫 - tw511教學網

一，數據庫

1.1 概念

數據庫就是儲存數據的倉庫

數據庫可以實現數據的持久化儲存

數據庫的本質是檔案系統

1.2 DBMS

Database Management System：數據庫管理系統

DBMA：數據庫管理員

常見的數據庫管理系統：

MySQL：Oracle公司的中小型數據庫，從6版本開始收費

Oracle：Oracle公司的大型數據庫，收費的

DB2：IBM公司的大型數據庫

SQLServer：Mircosoft公司的大型數據庫

SQLite：行動端的嵌入式的小型數據庫

Java中主要使用的是MySQL和Oracle

1.3 數據庫的結構

一個DBMS中可以維護管理多個數據庫

一個數據庫由若幹張表組成

一張表包含若幹條記錄

一條記錄包含若幹欄位資訊

數據庫中的表相當於程式碼中的實體類

例如：要研究醫生這個類，就應該建立一張醫生表

數據庫表中的一列（欄位）相當於實體類中的屬性

例如：汽車有品牌屬性，那麼在汽車表中就應該有品牌欄位

根據類建立出的物件就相當於數據庫表中的一行（一條記錄）

例如：當我們獲取寵物物件時，我們就可以從該物件上獲取它所具備的所有屬性；同樣的，當我們查詢到一條寵物記錄時我們就可以從表中明確該寵物的所有欄位資訊。

1.4 SQL語言

結構化查詢語言：Structured Query Language

分類：

DDL：數據定義語言（Data Definition Language）

對數據庫、表進行建立、修改、刪除等操作

關鍵字：create、alter、drop等

DML：數據操作語言（Data Manipulation Language）

對錶中記錄、欄位進行增、刪、改等操作

關鍵字：insert、delete、update等

DQL：數據查詢語言（Data Query Language）

對數據庫、表、記錄、欄位進行查詢的

關鍵字：select、from、where、in、and、or、between、having、group by、order by、limit等

DCL：數據控制語言（Data Control Language）

對數據庫的安全級別和存取許可權進行管理的

關鍵字：revoke、grant、commit、roll back等

1.5 mysql軟體

注：

安裝的目錄必須是純英文路徑的

刪除步驟

在控制面板中解除安裝軟體

刪除mysql安裝路徑下的mysql資料夾

刪除C槽下ProgramData下的mysql資料夾

登錄mysql

方式一：

開啓 cmd

輸入 mysql -uroot -p密碼

方法二：

開啓 cmd

輸入 mysql -uroot -p

再輸入密碼

方式三：

找到安裝mysql後的 Command Line Client

輸入密碼

二，SQL

C (create) R (read) U (update) D(delete)

2.1 庫的操作

建庫：
create database 庫名;
判斷數據庫是否存在，不存在才建立：
create database if not exists 庫名;
建立數據庫並指明編碼格式：
create database 庫名 character set 編碼格式;
查詢數據庫的建立語句以及編碼格式：
show create database 庫名;
檢視已有的數據庫：
show databases;
刪庫：
drop database 庫名;
判斷數據庫是否存在，存在才刪除：
drop database if exists 庫名;
修改數據庫的編碼格式：
alter database 庫名 character set 編碼格式;
指定使用的數據庫：
use 庫名;

2.2 表的操作

建表：

create table 表名 (欄位名 欄位型別 [約束], 欄位名 欄位型別 [約束], 欄位名 欄位型別 [約束], ...);

判斷表是否存在，不存在才建立：

create table if not exists 表名 (欄位名 欄位型別 [約束], 欄位名 欄位型別 [約束], 欄位名 欄位型別 [約束], ...);

查詢所有表：

show tables;

檢視錶結構：

desc 表名;

刪表：

drop table 表名;

判斷表是否存在，存在才刪除：

drop table if exists 表名;

複製表結構：

create table 新表名 like 被複制的表名;

修改表名：

alter table 舊錶名 rename to 新表名;

修改表結構：

新增新欄位

alter table 表名 add 新欄位 欄位型別 [約束];

刪除欄位
```
alter table 表名 drop 欄位;
```

修改欄位型別

alter table 表名 modify 欄位 欄位型別 [約束];

修改欄位名

alter table 表名 change 舊欄位名 新欄位名 欄位型別 [約束];

2.3 記錄、欄位的操作

查詢表中的所有記錄：

select * from 表名;
* 表示所有欄位（列）
等價於 select 欄位1,欄位2,欄位3,... from 表名;

新增記錄：

向所有欄位新增數據

insert into 表名 values (值1,值2,值3,...);

向指定欄位新增數據

insert into 表名 (欄位1,欄位2,欄位3,...) values (值1,值2,值3,...);

批次新增（所有欄位）

insert into 表名 values (值1,值2,值3,...), (值1,值2,值3,...),...;

批次新增（指定欄位）

insert into 表名 (欄位1,欄位2,欄位3,...) values (值1,值2,值3,...), (值1,值2,值3,...),...;

刪除記錄：
刪除所有記錄
delete from 表名;
根據條件刪除指定記錄：
delete from 表名 where 條件;
刪除所有記錄
truncate table 表名;

修改記錄：
修改所有記錄
update 表名 set 欄位名 = 值,欄位名 = 值,欄位名 = 值...;
根據條件修改指定記錄
update 表名 set 欄位名 = 值,欄位名 = 值,欄位名 = 值,... where 條件; 
注：

新增記錄時，值的數量必須與列的數量一致

新增記錄和修改記錄時，值的型別必須與欄位型別一致

除了數值型別，其他型別都必須用單/雙引號括起來

delete from 表名；刪除表中所有記錄，它的刪除方式是逐行刪除，表中有多少條記錄，刪除語句就會執行多少次；truncate table 表名；刪除表中記錄，它的刪除方式是將整張表刪除，然後構建一張與原來表結構相同的空表。

2.4 數據型別

2.4.1 數值型別

int：整數型別

double：小數型別

特殊的：salary double(n，m)

n：表示整數位和小數位一共最多有n位

m：表示小數位最多保留m位

例如：salary double(6，2) 的最大值是9999.99

2.4.2 日期和時間型別

datetime：時間戳，包含年月日時分秒，格式：yyyy-MM-dd HH:mm:ss

timestamp：時間戳，包含年月日時分秒，格式：yyyy-MM-dd HH:mm:ss

注：

如果某個欄位的是型別是timestamp，在我們給它賦值爲null或者沒有對它賦值時，系統會給它一個預設值，預設值是新增這條語句的時間或者是對它賦值爲null時的時間。

timestamp型別欄位值不可能是空值

2.4.3 字串型別

varchar：

例如：name varchar(20)：表示姓名欄位的值最大20個字元

2.5 查詢

2.5.1 基礎查詢

查詢所有：
select * from 表名;
查詢所有記錄的指定欄位：
select 欄位1,欄位2,欄位3,... from 表名;
去重查詢
select distinct 欄位名 from 表名;

2.5.2 條件查詢

在 where 關鍵字後加上條件，查詢時會根據條件進行記錄的篩選
邏輯運算子

&&、and

||、or

！

關係運算符

" > "

" < "

" >= "

" <= "

" = "

" != 「、」 <> "
指定範圍內

between … and …
例如：查詢成績在80~100之間的學生
select * from student where score between 80 and 100;
注：含頭含尾
在指定列表中

in(值1，值2，值3，…)

不在指定列表中

not in(值1，值2，值3，…)

空和非空

判斷爲空：is null

判斷非空：is not null

模糊查詢

關鍵字：like

佔位符：

_：單個任意字元

%：任意個任意字元

2.5.3 排序查詢

關鍵字：order by

排序方法：
升序：預設，asc
降序：desc
// 降序排列員工的銷售額，銷售額相同時，根據工資升序排列
select * from emp order by sale desc,salary asc;
注：如果多個欄位進行排序，只有第一欄位的值相同時，纔會執行第二欄位的排序

格式：order by 欄位1 排序方式，欄位2 排序方式，…；

2.5.4 聚合函數

概念：將一列數據作爲一個整體，然後進行縱向的計算

avg()：計算平均值

sum()：求和

max()：求最大值

min()：求最小值

count()：求數量

注：

count() 的參數一般使用非空欄位或者*

聚合函數不計算 null 值

2.5.5 分組查詢

關鍵字：group by

注：

分組後查詢的欄位只能是：分組欄位、聚合函數

where 和 having 的區別：

where 用在分組前進行條件篩選，如果不滿足 where 的條件則不進入分組；having 用在分組後進行條件篩選，如果不滿足 having 的條件則不被查詢到

where 後不能跟聚合函數，having 後可以使用聚合函數進行分組後的條件篩選

2.5.6 分頁查詢

關鍵字：limit

語法：limit 開始索引，每頁查詢的記錄數

注：索引從0開始

公式：開始索引 = （當前頁碼 - 1） * 每頁查詢的記錄數

2.5.7 查詢語句的結構

select

欄位列表

from

表名

where

條件列表

group by

分組欄位

having

分組後的條件

order by

排序欄位排序方式

limit

索引，記錄數

2.5.8 起別名

可以爲欄位、表起別名

起別名的作用是能夠區分多張表中的同名欄位，並且簡化了書寫

格式：
as 別名
注：as 可以省略

2.6 約束

概念：對錶中數據進行限定

分類：

非空約束：not null

唯一約束：unique

主鍵約束：primary key

外來鍵約束：foreign key

2.6.1 非空約束：not null

限定欄位值不能爲空

建立表時，新增非空約束
create table 表名(欄位名 欄位型別 not null,欄位名 欄位型別 [約束],欄位名 欄位類 [約束]...);
建立表後，新增非空約束
alter table 表名 modify 欄位名 欄位型別 not null;
注：已存在空值的欄位不能被設定成非空

刪除非空約束（設定欄位允許有空值）
alter table 表名 modify 欄位名 欄位型別;

2.6.2 唯一約束：unique

限定欄位值不能有重複

建立表時，新增唯一約束
create table 表名(欄位名 欄位型別 unique,欄位名 欄位型別 [約束],欄位名 欄位類 [約束]...);
建立表後，新增唯一約束
alter table 表名 modify 欄位名 欄位型別 unique;
刪除唯一約束
alter table 表名 drop index 欄位名;
注：

唯一約束的值可以是空值，並且允許存在多個 null 值

已經存在重複值的欄位不能被設定成唯一的

一個欄位可以同時被設定成非空且唯一的，並且非空和唯一的關鍵字的順序是任意的

2.6.3 主鍵約束：primary key

一般主鍵不使用具有特殊含義的欄位

特點：

非空且唯一

一張表中只能有一個主鍵欄位

主鍵是表中記錄的唯一標識

建立表時，新增主鍵約束

create table 表名(欄位名 欄位型別 primary key,欄位名 欄位型別 [約束],欄位名 欄位類 [約束]...);
create table 表名(欄位名 欄位型別 [約束],欄位名 欄位型別 [約束],..., primary key(主鍵欄位));

建立表後，新增主鍵約束

alter table 表名 modify 欄位名 欄位型別 primary key;

刪除主鍵約束

alter table 表名 drop primary key;

注：刪除主鍵約束後，主鍵仍有非空約束

主鍵自增長：

概念：如果某個欄位是數值型別的主鍵欄位，可以使用 auto_increment 來實現主鍵自增長

建立表時，新增主鍵自增長
create table 表名(欄位名 欄位型別 primary key auto_increment,欄位名 欄位型別 [約束],欄位名 欄位類 [約束]...);
建立表後，新增主鍵自增長
alter table 表名 modify 主鍵欄位 欄位型別 auto_increment;
刪除主鍵自增長
alter table 表名 modify 主鍵欄位 欄位型別;
建立表後，新增主鍵約束的同時新增自增長
alter table 表名 modify 欄位 欄位型別 primary key auto_increment;
注：

能新增自增長功能的必須是主鍵欄位

新增主鍵自增長的主鍵欄位必須是數值型別的

第一條自增長的欄位值是 1

從存在過的最大值開始 +1

如果存在過的最大值是小數，那麼自增長的值是大於它的最小整數

2.6.4 外來鍵約束：foreign key

外來鍵讓表與表之間產生了聯繫，保證了數據的完整性

注：從表外來鍵的型別必須與主表主鍵的型別一致

建立表時，新增外來鍵約束
create table 表名(欄位名 欄位型別 [約束], 欄位名 欄位型別 [約束], ...,  [constraint 外來鍵名] foreign key(外來鍵欄位) references 主表(主表的主鍵欄位));
刪除外來鍵
alter table 表明 drop foreign key 外來鍵名;
建立表後，新增外來鍵約束
alter table 表名 add [constraint 外來鍵名] foreign key(外來鍵欄位) references 主表(主表的主鍵欄位);
建立表後，新建外來鍵欄位的同時，新增外來鍵約束
alter table 表名 add 外來鍵欄位 欄位型別, add [constraint 外來鍵名] foreign key(外來鍵欄位) references 主表(主表的主鍵欄位);

級聯操作：對主表中的記錄進行更新和修改的同時，能直接影響從表中的記錄

建立從表時，新增級聯操作

create table 表名(欄位名 欄位型別 [約束], 欄位名 欄位型別 [約束], ...,  [constraint 外來鍵名] foreign key(外來鍵欄位) references 主表(主表的主鍵欄位) on delete cascade on update cascade);

建立從表後，新增級聯操作

alter table 表名 add [constraint 外來鍵名] foreign key(外來鍵欄位) references 主表(主表的主鍵欄位) on delete cascade on update cascade;

建立從表後，新建外來鍵欄位，新增外來鍵約束，並新增外來鍵的級聯操作

alter table 表名 add 外來鍵欄位 欄位型別, add [constraint 外來鍵名] foreign key(外來鍵欄位) references 主表(主表的主鍵欄位) on delete cascade on update cascade;

2.7 多表

2.7.1 表與表之間的關係

一對一

例如：人和身份證、公司與註冊地址、汽車與車牌號

一對多（多對一）

例如：部門和員工、商品和分類

多對多

例如：學生和課程、商品和訂單

2.7.2 多表建立連線

一對一

在任意一方新增外來鍵指向另一方的主鍵，外來鍵要保證唯一

一對多

在多的一方新增外來鍵指向一的一方的主鍵
多對多

需要藉助第三張中間表。中間表至少包含兩個欄位，這兩個欄位作爲中間表的外來鍵，分別指向兩張主表的主鍵。爲了保證中間表不會出現重複值，需要將這兩個外來鍵欄位形成聯合主鍵。
create table tb_user_hobby(
	uid int,
	hid int,
	primary key(uid,hid), /* 聯合主鍵 */ 
	foreign key(uid) references user(uid),
	foreign key(hid) references hobby(hid)
);

2.8 多表查詢

2.8.1 交叉查詢

語法：
select 欄位列表 from 表1, 表2;
交叉查詢的結果是笛卡爾積，是多張表所有記錄的組成情況

我們需要使用條件來去除無效的數據

2.8.2 內連線查詢

在交叉查詢的基礎上使用 where 條件去除無效的數據

語法：

隱式內連線
select 欄位列表 from 表1, 表2 where 條件;
顯式內連線
select 欄位列表 from 表1 inner join 表2 on/where 條件;
注：inner 可以省略

內連線查詢的結果是兩表的交集

2.8.3 外連線

語法：

左外連線
select 欄位列表 from 表1 left outer join 表2 on 條件;
右外連線
select 欄位列表 from 表1 right outer join 表2 on 條件;
注：outer 可以省略

左外連線查詢的結果是左表的全部和兩表的交集

右外連線查詢的結果是右表的全部和兩表的交集

2.9 子查詢

概念：將一條查詢語句的結果作爲另一條查詢語句的表、記錄、欄位、條件