MySQL-資料庫-有解無憂

一，資料庫

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 子查詢

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/282365.html

標籤：其他

上一篇：SQL綜合復習試題（一）

下一篇：資料庫第六章課后作業