小饒學程式設計之JAVA SE第三部分——MySQL基礎

溫文爾雅的清歡渡發表於2020-11-15
程式設計JavaMySql

MySQL基礎

一、基本概念

1.1 資料庫

資料庫（database）：是按照資料結構來組織、儲存和管理資料的倉庫。

1.2 資料庫的特點

  • 資料結構化

    ​ 據庫系統實現了整體資料的結構化，這是資料庫的最主要的特徵之一。

  • 實現資料共享

    ​ 因為資料是面向整體的，所以資料可以被多個使用者、多個應用程式共享使用。

  • 資料獨立性高

    ​ 資料的獨立性包含邏輯獨立性和物理獨立性，其中，邏輯獨立性是指資料庫中資料的邏輯結構和應用程式相互獨立，物理獨立性是指資料物理結構的變化不影響資料的邏輯結構。

  • 資料統一管理與控制

    ​ 資料的統一控制包含安全控制、完整控制和併發控制。簡單來說就是防止資料丟失、確保資料的正確有效，並且在同一時間內，允許使用者對資料進行多路存取，防止使用者之間的異常互動。

1.3 資料庫管理系統

​ 資料庫管理系統(Database Management System)是一種操縱和管理資料庫的大型軟體，用於建立、使用和維護資料庫，簡稱DBMS。

1.4 資料庫分類

  • 關係型資料庫

    ​ 這種型別的資料庫是最古老的資料庫型別，關係型資料庫模型是把複雜的資料結構歸結為簡單的二元關係（即二維表格形式）。

  • 非關係型資料庫

    ​ 關係型資料庫以外的統稱為非關係型資料庫，簡稱NoSQL（not only SQL）。從儲存結構可以劃分鍵值儲存資料庫（Redis）、列儲存資料庫（HBase）、面向文件資料庫（MongoDB）、圖形資料庫（Neo4J）、搜尋引擎儲存（Elasticsearch）。

1.5 常見資料庫

  • MYSQL：開源免費的資料庫，小型的資料庫.已經被Oracle收購了。
  • Oracle：收費的大型資料庫，Oracle公司的產品。Oracle收購SUN公司，收購MYSQL。
  • DB2：IBM公司的資料庫產品，收費的。常應用在銀行系統中。
  • SQLServer: MicroSoft公司收費的中型的資料庫。C#、.net等語言常使用。
  • SQLite：嵌入式的小型資料庫，預設內建在android系統中。
  • Redis： 是完全開源免費的，是一個高效能的key-value資料庫。

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-dZiuDQB6-1605408423459)(assets/資料庫排名.jpeg)]

​

1.6 MySQL安裝與解除安裝

  • 安裝

    • 一般安裝到C盤，傻瓜式安裝。

  • 解除安裝

    • 先解除安裝MySQL軟體，然後進入C盤，開啟顯示隱藏目錄，進入programData目錄，刪除mysql目錄。

1.7 MySQL服務的啟動

  • 手動從系統的服務開啟，可以設定為自動開啟

  • 命令列開啟跟關閉服務

    • net start mysql：啟動mysql的服務
    • net stop mysql：關閉mysql服務

1.8 MySQL的登入跟退出

  • 命令列

    • 明文登入：mysql -uroot -proot,其中紅色字型的root/root分別表示使用者名稱跟密碼。

    • 密文登入：mysql -uroot -p 回車後輸入密碼即可。

    • 遠端登入：mysql -h[遠端電腦ip] -u使用者名稱 -p密碼。

    • 退出：exit 或者 quit

  • 第3方工具（Navicat ）

二.、SQL

2.1 什麼是SQL

  • 結構化查詢語言（Structured Query Language）簡稱SQL，SQL語句就是對資料庫進行操作的一種語言。

  • SQL語句可以分單行或者多行書寫，以分號結尾。

  • 可使用空格跟縮排增強語句的可讀性。

  • MySQL不區分大小寫。

  • 註釋有3種：

    • – 單行註釋，–後面緊跟的空格不能省略

    • # 單行註釋

    • /* 多行註釋 */

2.2 SQL分類

  • DDL（Data Definition Language）資料定義語言
    • 用來定義資料庫物件：資料庫，表，列等。關鍵字：create，drop，alter 等
  • DML（Data Manipulation Language）資料操作語言
    • 用來對資料庫中表的資料進行增刪改。關鍵字：insert，delete，update 等
  • DQL（Data Query Language）資料查詢語言
    • 用來查詢資料庫中表的記錄（資料）。關鍵字：select等
  • DCL（Data Control Language）資料控制語言（瞭解）
    • 用來定義資料庫的訪問許可權和安全級別，及建立使用者。關鍵字：GRANT，REVOKE等

2.3 資料庫操作

2.3.1 建立資料庫

  • 直接建立資料庫

    create database blb_db ;

  • 如果資料庫不存在則建立，存在則不建立。

    create database if not exists blb_db;

  • 建立資料庫，不存在則建立，並指定製定字符集

    create database if not exists blb_db character set utf8;

2.3.2 查詢資料庫

  • 顯示所有資料庫

    show databases;

  • 檢視某個資料庫的建立以及字符集

    show create database blb_db;

2.3.3 修改資料庫

  • 修改資料庫字符集

    alter database blb_db character set utf8 ;

2.3.4 使用資料庫

  • 查詢當前正在使用的資料庫

    select database();

  • 使用資料庫

    use blb_db;

2.4 表結構操作

2.4.1 建立表

  • 語法

    create table 表名 (
	列名  型別  [約束]  [COMMENT '備註1'] ,
	列名  型別  [約束]  [COMMENT '備註2'] ,
	列名  型別  [約束]  [COMMENT '備註3'] ,
	......
	列名  型別  [約束]  [COMMENT '備註N'] 
)

  • 一個栗子

    create table student(
	stu_id  varchar(10)  ,
	stu_name varchar(10) ,
	stu_sex varchar(2),
	stu_address varchar(100),
	stu_age int ,
	stu_date datetime 
);

2.4.2 查詢表

  • 查詢表的資料

    SELECT * from student ;

  • 詢某個資料中所有的表

  • show tables;

  • 查詢表結構

    desc student;

  • 查詢表的建立SQL

    show create table student ;

2.4.3 修改表

  • 修改表名

    • alter table 表名 rename to 新的表名;

      alter table student rename to s1;

  • 修改表的字符集

    • alter table 表名 character set 字符集的名稱;

      alter table student character set utf8

  • 新增一列

    • alter table 表名 add column 列名 列型別 [約束];

    • alter table 表名 add (列名 列型別 [約束] , 列名 列型別 [約束] , ...... );

      alter table student add  column stu_height int  not null 
alter table student add (stu_weight int);

    • alter table 表名 add column 列名 列型別 [約束] [FIRST]/[AFTER 列名];

    • alter table s1 add  column stu_height int  not null  FIRST 
alter table s1 add  column stu_height int  not null  AFTER stu_age

  • 修改列名稱跟型別

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

      alter table student change  stu_weight  stu_weight1  double

  • 刪除列

    • alter table 表名 drop 列名;

      alter table student drop column stu_weight

2.4.4 刪除表

  • drop table 表名;

  • drop table if exists 表名;

    drop table student;
drop table if exists student;

2.5 表資料操作（重點）

2.5.1 新增資料

  • 語法

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

    insert into student values ('101' ,'zhangsan','男','asdfa',18, str_to_date('08.09.2008 08:09:33','%d.%m.%Y %h:%i:%s')  );
-- 插入多個值的話可以一次性插入
insert into student values
('101' ,'zhangsan','男','asdfa',18, '2020-07-15 12:11:12' ),
('102' ,'lisi','女','123',18, '2020-06-15 11:11:12' ),
('103' ,'wangwu','男','456',18, '2020-04-15 10:11:12' );
    • 列名跟值需要一一對應。
    • 如果表名後沒有跟指定欄位則必須將全部所有欄位值給出。

2.5.2 修改資料

  • 語法

    update 表名 set 欄位名1=新欄位值1, 欄位名2=新欄位值2 ... [ where 條件]

    update  student  set  stu_age = 21,stu_sex='女'  where stu_name = 'zhangsan';
    • 如果不加任何條件，則會將表中所有記錄全部修改。

2.5.3 刪除資料

  • 語法

    delete from 表名 [where 條件];

    delete from student  where stu_name = 'zhangsan';
    • 如果不加條件，則刪除表中所有記錄。
    • 當需要刪除所有資料（表結構保留）的時候也可以使用TRUNCATE TABLE 表名。
    • delete from屬於DML，所以操作可以回滾。而TRUNCATE TABLE 表名屬於DDL，所以操作不能回滾。
    • 從效率上講TRUNCATE TABLE 表名高於delete from

2.6 查詢資料（重點）

  • 語法

    select 欄位列表 from 表名列表

    [where 條件列表]

    [group by 分組欄位列表]

    [having 分組後的條件列表]

    [order by 排序欄位]

    [limit 分頁限定]

2.6.1 基礎查詢

  1. 多個欄位的查詢

    • select 欄位名1,欄位名2... from 表名;

    • 如果查詢表中的所有欄位可以使用*號select * from 表名

  2. 去重

    • select distinct 欄位列表 from 表名

  3. 列計算

    • SELECT stu_age+1 from student ;

    • 如果有null參與的運算，計算結果都會為null，可以使用IFNULL函式解決

      -- IFNULL：如果第一個引數為null的時候按照第2個引數值代替處理。
SELECT IFNULL(stu_age,0)+1  from student  ;

  4. 取別名

    • select 欄位名1 as 別名1 ,欄位名2 as 別名2 ... from 表名;

      select stu_id as Stuid,stu_name  as "Stu Name" from student2 ;

    • 如果別名欄位有空格需要用引號

    • as可以省略

2.6.2 條件查詢

  • > < >= <= <> !=

    select * from student where stu_age > 18 ;

  • BETWEEN AND

    -- 18跟28都包括在內
select * from student where stu_age between 18 and 28 ;

  • is null、is not null

    • 對錶中的欄位進行空或者非空判斷

      select * from student where stu_sex is null ;
select * from student where stu_sex is not null ;

  • in、not in

    select * from student where stu_age in (18,28,38);
select * from student where stu_age not in (18,28,38);
-- 使用not in的時候如果表中有null的值的話，則查詢無結果，需要先把null值過濾掉，這是MySQL的bug
select * from student where stu_age is not null and  stu_age not in (18,28,38);
    • in 、not in後面的集合可以是具體值也可以是個子查詢。
    • not in在mysql中是有bug的，需要把null值給排除掉。

  • and 、or、not

    select * from student where stu_name is not null and stu_age > 18 ;
select * from student where stu_age > 18 or stu_sex = '男';
select * from student where not stu_age>18 ;
    • and效果同&&、or效果同||、not效果同!

  • like

    • 用來進行模糊查詢

    • 佔位符_表示任意一個字元

    • 佔位符%表示任意N個字元

      -- 查詢姓名第2個字元是h的學生資訊
select * from student where stu_name like '_h%'

所有null值不會參與運算，如果需要參與可以使用IFNULL函式。

 -- 查詢所有年齡小於18歲的學生資訊，如果年齡欄位為null按照0處理。
 SELECT * from student where IFNULL(stu_age,0) < 18 ;

2.6.3 排序查詢

  • 語法order by 排序欄位1 排序方式1，排序欄位2 排序方式2...

  • ASC升序（預設），DESC降序

  • 多欄位排序時，只有前面欄位值相同時才把相同欄位值的資料按照後面的排序欄位跟方式排序。

    -- 先按照年齡進行升序排序，年齡相同的再按照id降序排序
select * from student order by stu_age asc ,stu_id desc ;

2.6.4 聚合函式

​ 將一列資料作為一個整體，進行縱向的計算。

  1. count

    計算個數，一般選擇非空的列，比如主鍵。計算表資料總條數一般使用count(*)

    -- 計算學生表中資料總條數
select count(*) as total_num from student ;

  2. max、min、avg、sum

    分別表示計算列的最大值、最小值、平均值、總和。

    select max(stu_age) as max_age from student ;
select min(stu_age) as min_age from student ;
select avg(stu_age) as avg_age from student ;
select sum(stu_age) as sum_age from student ;
-- 查詢student表中最大年齡、最小年齡、平均年齡、總年齡
select  max(stu_age) max_age ,min(stu_age),avg(stu_age),sum(stu_age)  from student ;
    • 使用了聚合函式後不能查詢一般欄位，如果使用了分組可以使用分組欄位。
    • where後面不能跟聚合函式

2.6.5 分組查詢

  • 語法：group by 分組欄位 [having 條件]

    -- 按照性別進行分組
select  stu_sex from  student2 group by stu_sex    ;
-- 按照性別分組，分組後查詢平均年齡>18的分組資訊
select  avg(stu_age), stu_sex from student2 group by stu_sex having avg(stu_age) > 18
    • 分組後查詢欄位只能是分組欄位跟聚合函式，非分組共有欄位則無意義。
    • where跟having的區別？
      • where跟having都表示條件過濾，where是先過濾在分組，having是先分組在過濾。
      • where後面不能跟聚合函式，having後可以跟聚合函式。

3.2.6 分頁查詢

  • limit X , Y ;

    X為起始位置的索引，Y為取多少條資料。

    -- 從索引為0的位置開始，取5條資料
select * from student limit 0 , 5 ;

  • 假設每頁資料為pageSize，當前頁數為pageNum，則取出當頁資料的SQL公式為

    select * from student limit (pageNum-1)*pageSize , pageSize

  • 只有MySQL可以通過limit分頁，其它資料庫不能使用這種方式。

三、資料型別

​ MySQL中定義資料欄位的型別對你資料庫的優化是非常重要的。MySQL支援多種型別，大致可以分為三類：數值（整型/小數）、日期/時間和字串(字元)型別。

3.1 整型

類 型大 小範圍（有符號）範圍（無符號）
TINYINT(m)1byte(-128，127)(0，255)
SMALLINT(m)2byte(-32 768，32 767)(0，65 535)
MEDIUMINT(m)3byte(-8 388 608，8 388 607)(0，16 777 215)
INT(m)或INTEGER(m)4byte(-2 147 483 648，2 147 483 647)(0，4 294 967 295)
BIGINT(m)8byte(-9223372036854775808，9223372036854 775 807)(0，18446744073709551 615)

  • 引數m表示的顯示寬度

    -- 這裡引數5指的是顯示的寬度而已，當數字超過則不受此限制
create table t (col int(5) ZEROFILL);
insert into t values(1),(12),(123),(1234),(12345),(123456) ;

    • 引數m只是當位數小於m時候顯示的寬度，對數字大小沒有限制，意義不大。

    • ZEROFILL表示顯示長度不夠的用0佔位。

    • 需要在命令列中輸出。

    • 如果實際插入的值超過了型別的範圍限制則報錯，無法插入。

    • 如果引數m不給出會有預設值，每種型別的預設值不同。

    • 預設是有符號的，如果要定義無符號的需要加上關鍵字UNSIGNED

    • 定義了ZEROFILL則直接是有無符號的，ZEROFILL跟有符號衝突，只能選擇1個。

      create table t (col int  );
create table t (col integer(4) UNSIGNED );
create table t (col tinyint UNSIGNED ZEROFILL  );

3.2 浮點

類 型大 小含 義
float(m,d)4byte單精度浮點型，m總個數，d小數位
double(m,d)8byte雙精度浮點型，m總個數，d小數位

  • m表示整個小數的總長度，d表示小數位的長度

    create table t (col FLOAT(6,3));

insert into t values(123.45678);  -- 123.457
insert into t values(1234.56); -- 報錯，整數部分超出範圍
insert into t values(12.3456789); -- 12.346
insert into t values(12.3); -- 12.300，後面的0需在命令列顯示
    • 也可以不給（m,d）值，直接使用float跟double，則預設會按照實際的精度（由實際的硬體跟作業系統決定）來顯示。
    • （m,d）會採取四捨五入，因此會出現精度丟失的問題。
    • （m,d）的用法是非標準用法，如果要用於資料庫的遷移，則最好不要這麼使用。

3.3 定點數

​ 浮點型在資料庫中存放的是近似值，而定點型別在資料庫中存放的是精確值。

  • ​ decimal(P,D)
    • P表示有效數字的精度，P的範圍為[1，65]
    • D是表示小數點後的位數，D的範圍是[0,30]，MySQL要求D<=P
    • DECIMAL(6,2) 表示此列最多可以儲存6位數字，小數位數為2位; 因此，此列的範圍是從-9999.99到9999.99
    • DECIMAL(P)相當於DECIMAL(P,0)
    • DECIMAL在不指定精度時，預設整數為10，小數為0

3.4 日期和時間型別

類 型大 小含 義
DATE3表示日期，包含年月日
TIME3表示時間，包含時分秒
YEAR1表示年份
DATETIME8表示日期，包含年月日，時分秒
TIMESTAMP4表示日期，包含年月日，時分秒，自動更新
-- 定義col列，datetime型別，預設值'2018-07-14 10:11:09'
create table t(id int ,col DATETIME DEFAULT '2018-07-14 10:11:09' );

-- 定義col列，timestamp型別，預設為當前系統時間
create table t(id int ,col TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

-- 定義col列，timestamp型別，當本行資料有改動則自動更新col列的值為系統最新時間，下面2個SQL等效
create table t(id int ,col TIMESTAMP );
create table t(id int ,col TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- v5.5需要此設定

-- 插入資料，時間欄位為當前系統時間
insert into t (col) values(CURRENT_TIMESTAMP);
insert into t (col) values(CURRENT_TIMESTAMP());
insert into t (col) values(SYSDATE());
insert into t (col) values(NOW());

-- 插入指定時間
insert into t (col) values('2020-07-14 14:05:06');
insert into t (col) values(STR_TO_DATE('2019-08-07 14:05:06','%Y-%m-%d %H:%i:%s') );

-- 查詢日期時間欄位
select DATE_FORMAT(col,'%Y-%m-%d %H:%i:%s ') from t ;
  • %Y表示年，%m表示月，%d表示天，%H表示小時，%i表示分，%s表示秒。
  • YEAR、TIME、DATE用法類似

3.5 字元

類 型含 義
char(n)固定長度，最多255個字元
varchar(n)固定長度，最多65535個字元
tinytext可變長度，最多255個字元
text可變長度，最多65535個字元
mediumtext可變長度，最多2的24次方-1個字元
longtext可變長度，最多2的32次方-1個字元
BLOB二進位制形式的長文字資料
MEDIUMBLOB二進位制形式的中等長度文字資料
LONGBLOB二進位制形式的極大文字資料
  • char(n)若存入字元數小於n，則以空格補於其後，查詢之時再將空格去掉。所以char型別儲存的字串末尾不能有空格，varchar不限於此。定長，浪費空間，速度快。
  • varchar(n)是存入的實際字元數+1個位元組（n<=255）或2個位元組(n>255)，所以varchar(4)存入3個字元將佔用4個位元組。不定長，節省空間。
  • text不用指定長度n，text是實際字元數+2個位元組。text型別不能有預設值。
  • BLOB都是儲存二進位制資料，儲存的資料只能整體讀出。

3.6 列舉

​ 實現將所有可能出現的結果都設計好，實際上儲存的資料必須是規定好的資料中的一個。

  • 語法：enum（可能出現的元素列表）

    -- 定義sex列，型別為列舉，值只能是列表中的某一個值，可以為null
create table t(sex enum('男','女') );
 
-- 插入資料，值如果非null則必須為列舉列表中的一個
insert into t(sex) values ("男");

-- 插入資料，列舉值也可以根據索引來引用，從1開始。
insert into t(sex) values (2);

四、約束

​ 對錶中的資料進行限定，保證資料的正確性、有效性和完整性。

分類：

  • 主鍵約束：primary key
  • 非空約束 : not null
  • 唯一約束 : unique
  • 外來鍵約束 : foreign key

4.1 主鍵約束（重點）

​ 主鍵，又稱主碼，是表中一列或多列的組合。主鍵要求主鍵列的資料唯一，並且不允許為空，主鍵能夠唯一地表識表中的一條記錄。一個表只能有一個主鍵，但是可以是多列（複合主鍵）。

  • 使用

    -- 方式1: 直接在主鍵欄位後標識
create table t(
	id int primary key ,
	name varchar(10)
);

-- 方式2： 後面專門定義主鍵約束，如果是複合主鍵（多列）只能使用這種方式
create table t(
	id int,
	name varchar(10) ,
	primary key(id)
);

-- 方式3：在已存在的表新增主鍵約束
alter table t modify id int primary key;
alter table t add constraint id_pri primary key (id)


-- 刪除主鍵約束
alter table t drop primary key;

4.2 非空約束

​ 定義表的時候限定為非空約束後則此欄位不能有null值。

  • 使用

    -- 建立表的時候給指定列新增非空約束
create table t(	 
	stu_name varchar(10) not null
);

-- 在表已經存在的基礎上新增非空約束
alter table t modify stu_name varchar(10) not null;

-- 刪除非空約束
alter table t modify stu_name varchar(10);

4.3 唯一約束

​ 限定列資料不能有重複的值，null可以重複。

  • 使用unique

    -- 建立表的時候給指定列加上唯一約束
create table t(	 
	stu_name varchar(10) unique 
);

-- 在表存在的前提下新增唯一約束
alter table t modify stu_name varchar(10) unique;
alter table t add constraint name_unique unique(stu_name);


-- 刪除列上的唯一約束
alter table t drop index stu_name
-- 通過約束名刪除列上的唯一約束
alter table t drop index name_unique;

4.4 預設約束

​ 限制某列資料當沒有給定值的時候使用指定的預設值，關鍵字是default。

-- 建立表的時候給指定列加上預設約束
create table t(	 
	stu_age int default 18  
);

-- 在表存在的前提下新增預設約束
alter table t modify stu_age int DEFAULT 18;

-- 刪除預設約束
alter table t modify stu_age int

4.5 自動增長

​ 當某列是數值時，我們可以給這列設定，讓它自動增長。一般跟數值型別的主鍵一起使用。關鍵字auto_increment。

  • 使用

    -- 建立表的時候直接定義自增長
create table student(
	s_id int primary key auto_increment,
	s_name varchar(10) 
) auto_increment=10000 -- 設定自動增長從10000開始

-- 設定自動增長從10000開始
alter table student  auto_increment=10000;

-- 給已經存在的表欄位新增自增長
alter table student s_id int auto_increment;

-- 刪除自增長
alter table student modify s_id int ;
    • 新增自動增長以後，也同樣可以對欄位進行主動賦值。
    • 如果手動賦值跳躍性，則後面會直接根據最大值++。

4.6 外來鍵約束

​ 讓表與表產生關係，從而保證資料的正確性。

  • 使用foreign key

    -- 先建立學生表，父表
create table student(
s_id int primary key ,
	s_name varchar(10) 
);

-- 再建立子表
create table score (
	s_id int ,
	s_name VARCHAR(10),
	s_socre int ,
	user_id int ,
    -- 指定欄位user_id是外來鍵，指向父表student表中的s_id欄位，外來鍵必須指向父表的主鍵
	-- constraint s_foreign 這裡可以加上，給這個外來鍵取個名字  
    FOREIGN KEY(user_id) references student(s_id)
 ); 
 
-- 建立表後再新增外來鍵約束，score_foreign為給這個外來鍵約束取的名字
 alter table score add constraint score_foreign foreign key (user_id) references student(s_id)  
 
 --  刪除外來鍵約束，score_foreign為外來鍵約束取的名字，如果沒有手動取名則改為使用預設生成的約束即可。
 alter table score drop foreign key score_foreign;
    • 建立完父表與字表的外來鍵約束關係後，則子表的外來鍵欄位值如果不為null的話，則必須是主表中的存在值
    • 建立好外來鍵約束關係後，如果子表外來鍵值使用了主表的一個值，則預設情況下主表的這個值不能刪除或者改成別的值。如果不要這個預設操作需要在定義外來鍵的時候設定。

  • 級聯刪除、級聯更新

    • 當刪除主表的資料時，子表的行為可以設定級聯刪除跟級聯更新，也可以設定null刪除，設定null更新。

      create table score (
	s_id int ,
	s_name VARCHAR(10),
	s_socre int ,
	user_id int ,
    -- 設定外來鍵時，級聯刪除跟級聯更新
	FOREIGN KEY(user_id) references student(s_id) on delete cascade on update cascade 
 );
 
 -- 設定外來鍵時，級聯刪除跟級聯更新
 alter table score add constraint score_foreign foreign key (user_id) references student(s_id) on update cascade on delete cascade 
 
 create table score (
	s_id int ,
	s_name VARCHAR(10),
	s_socre int ,
	user_id int ,
    -- 設定外來鍵時，set null刪除跟set null更新
	FOREIGN KEY(user_id) references student(s_id) on delete set null on update set null );
 
  -- 設定外來鍵時，set null刪除跟set null更新
 alter table score add constraint score_foreign foreign key (user_id) references student(s_id) on update set null on delete set null

五、多表查詢

  • 如果有表A,B那麼查詢出來的數量等於A*B，這個現象被稱為笛卡爾積

    select * from student,score ;

  • 笛卡爾積：

    • 有兩個集合A,B，取這兩個集合的所有組成情況。

    • 要完成多表查詢，需要消除無用的資料。

5.1 內連線

  • 隱式內連線: 通過where消除無用資料。

    select * from student,score where student.s_id = score.user_id ;

  • 顯式內連線INNER JOIN on

    select * from student INNER JOIN score on student.s_id = score.user_id ;

5.2 左（外）連線

  • 顯示左連線left JOIN on

    select * from student left join score on student.s_id = score.user_id ;

5.3 右（外）連線

  • 顯示左連線right JOIN on

    select * from student right join score on student.s_id = score.user_id ;

5.4 子查詢

​ 查詢中巢狀查詢，稱巢狀查詢為子查詢。

六、事務

6.1 事務的概念

​ 如果一個包含多個SQL步驟的業務操作，把這些操作放入一個事務中，這些操作要麼同時成功，要麼同時失敗。

  • 操作

    -- 開啟事務,這2種方式等效
start transaction;
begin;

-- 提交事務，這2種方式等效
commit;
commit work;

-- 回滾事務，這2種方式等效
rollback;
rollback work;
    • MySQL中預設自動提交事務。
    • 可以通過SELECT @@autocommit;檢視事務的預設提交方式，0為手動提交，1為自動提交。
    • 修改預設提交方式： set @@autocommit = 1;

6.2 事務的特性

​ 事務有4大特性，簡稱ACID。

  • 原子性(Atomicity)：是不可分割的最小操作單位，要麼同時成功，要麼同時失敗。

  • 一致性(Consistency)：事務操作前後，資料總量不變。

  • 隔離性(Isolation)：多個事務之間。相互獨立。

  • 永續性(Durability)：當事務提交或回滾後，資料庫會持久化的儲存資料。

6.3 事務的併發問題

  • 髒讀：事務A讀取了事務B更新的資料，然後B回滾操作，那麼A讀取到的資料是髒資料。

  • 不可重複讀：事務 A 多次讀取同一資料，事務 B 在事務A多次讀取的過程中，對資料作了更新並提交，導致事務A多次讀取同一資料時，結果不一致。

  • 幻讀：事務 A 多次讀取同一資料，事務 B 在事務A多次讀取的過程中，對資料作了修改或者刪除操作並提交，導致事務A多次讀取同一資料時，莫名的多出了一些之前不存在資料，或者莫名的丟了一些資料。像發生了幻覺一樣。

    • 不可重複讀的和幻讀很容易混淆，不可重複讀側重於修改，幻讀側重於新增或刪除。
    • 解決不可重複讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表。

6.4 事務的隔離級別

事務隔離級別髒讀不可重複讀幻讀
讀未提交（read-uncommitted）是是是
不可重複讀（read-committed）否是是
可重複讀（repeatable-read）否否是
序列化（serializable）否否否
  • MySQL預設隔離級別為repeatable-read
  • Oracle預設隔離級別是read-committed
  • Serializable強制的進行排序，在每個讀讀資料行上新增共享鎖。會導致大量超時現象和鎖競爭。
  • 隔離界別從小到大安全性越來越高，但是效率越來越低。
  • 查詢資料庫隔離級別select @@tx_isolation;
  • 設定資料庫隔離級別set global transaction isolation level 級別字串;

七、其它

7.1 新增使用者

  • CREATE USER '使用者名稱'@'主機名' IDENTIFIED BY '密碼'

    CREATE USER 'hello'@'localhost' IDENTIFIED BY '123';

7.2 刪除使用者

  • DROP USER '使用者名稱'@'主機名'

    -- 刪除本機上的hello使用者
DROP USER 'hello'@'localhost'

7.3 修改密碼

  • SET PASSWORD FOR '使用者名稱'@'主機名' = PASSWORD('新密碼');

    SET PASSWORD FOR 'hello'@'localhost' = PASSWORD('789');

7.4 修改root密碼

  • 停止mysql服務:net stop mysql

  • 無驗證方式啟動mysql: mysqld --skip-grant-tables

  • 開啟新的cmd視窗,直接輸入mysql命令，敲回車。就可以登入成功

  • 接著執行use mysql;跟update user set password = password('你的新密碼') where user = 'root';

  • 關閉2個CMD視窗

  • 開啟工作管理員，手動結束mysqld.exe 的程式或者重啟

  • 啟動mysql服務

  • 使用新密碼登入。

7.5 授予許可權

  • grant 許可權列表 on 資料庫名.表名 to '使用者名稱'@'主機名' identified by '密碼' ;

    -- 給hello使用者所有的許可權，操作blb_db資料庫下的student表  
GRANT ALL ON blb_db.student TO 'hello'@'localhost' identified by '123';
    • 許可權列表代表14種許可權，分別為：select,insert,update,delete,create,drop,index,alter,grant等，ALL代表所有的。
    • 使用者地址可以是localhost，可以使ip地址、機器名字、域名。也可以用’%'表示任何地址連線。

7.6 檢視許可權

  • SHOW GRANTS FOR '使用者名稱'@'主機名';

    SHOW GRANTS FOR 'hello'@'localhost';

7.6 撤銷許可權

  • revoke 許可權列表 on 資料庫名.表名 from '使用者名稱'@'主機名';

    revoke all on blb_db.student from 'hello'@'localhost';

7.7 資料備份

  • 方式1：mysqldump -u使用者名稱 -p密碼 資料庫的名稱 > 儲存的路徑

    • mysqldump -uroot -proot blb_db>d://a.sql

  • 方式2：圖形工具

7.8 資料還原

  • 命令列方式

    • 登入資料庫

      [外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-R2HwlUPG-1605408423476)(assets/f.png)]

    • 建立並使用資料庫

    • source 檔案路徑

  • 方式2：圖形工具

八、總結

主要學習內容是表的增刪改查和表資料的增刪改查，其次是資料型別和約束在建表的時候會用到。
最重要的是表資料的查詢。

相關文章