MySQL資料庫基礎操作

SQL分類：

• DQL：資料查詢語言
  • 代表關鍵字：select
• DML：資料操縱語言
  • 代表關鍵字：insert，delete，update
• DDL：資料定義語言
  • 代表關鍵字：create，drop，alert
• DCL：資料控制語言
  • 代表關鍵字：grant，revoke
• TCL：事務控制語言
  • 代表關鍵字：commit，rollback

一、常用命令

（一）啟動MySQL資料庫

net start mysql

G:\MySQL\mysql-5.7.28-winx64\mysql-5.7.28\bin>net start mysql
MySQL 服務正在啟動 .
MySQL 服務已經啟動成功，

（二）關閉MySQL資料庫

net stop mysql

G:\MySQL\mysql-5.7.28-winx64\mysql-5.7.28\bin>net stop mysql
MySQL 服務正在停止.
MySQL 服務已成功停止，

（三）登錄用戶

G:\MySQL\mysql-5.7.28-winx64\mysql-5.7.28\bin>mysql -u root -p
Enter password: **************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

（四）查看資料庫

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.16 sec)

mysql有內置的四個資料庫

創建資料庫

//語法格式：
create database 資料庫名;
//以 英文逗號 ; 結尾

選擇資料庫并使用

use 資料庫名;

終止一條陳述句：ctrl+c

退出mysql:

1. \q
2. exit
3. quit

二、簡單查詢

（一）查詢一個欄位

語法格式：

select 欄位名 from 表名;

表：資料庫中的一種檔案結構

（二）查詢多個欄位

語法格式：

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

（三）查詢全部欄位

語法格式：

select * from 表名;

（四）查詢后的結果重命名

語法格式：

select 欄位名 as 新欄位名 from 表名;

三、條件查詢

條件查詢必須用到where陳述句，where陳述句必須放到from陳述句后面

運算子	說明
=	等號
！=或者<>	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between…and…	兩值之間，相當于>=and<=
is null	為null（is not null不為null）
and	并且
or	或者
in	包含
not	取非
like	模糊查詢，支持%或者下劃線匹配，%匹配任意個字符，一個下劃線只能匹配一個字符

四、排序操作

資料排序使用order by陳述句

默認升序排序

多個排序欄位之間使用逗號隔開

若有where陳述句，則order by陳述句必須放在where陳述句后面

語法格式：

select * from 表名 where .. order by 欄位名;

手動指定排序

從小到大：（asc）

select * from 表名 where .. order by 欄位名 asc;

從大到小：（desc）

select * from 表名 where .. order by 欄位名 desc;

多個欄位排序（用逗號隔開）

select * from 表名 where .. order by 欄位名1 desc,欄位名2 desc..;

五、函式

（一）單行處理函式

函式名	說明	用法
lower	轉小寫	eg：select lower（欄位名） from 表名;
upper	轉大寫	eg：select upper（欄位名） from 表名;
substr	取子串	substr（被截取的字串，起始下標，截取的長度）
length	取長度	… length（欄位名）;
trim	去首尾空格	… trim（欄位名）;
str_to_date	將字串轉換成日期（嚴格標準按照輸出）	1、與資料庫格式匹配2、將字串轉換成date型別
date_format	格式化日期	按照指定日期格式輸出
format	設定千分位	eg：select format(欄位名，數字) from 表名;
round	四舍五入	select round（小數）
rand（）	生成亂數	select rand（）
ifnull	將值為null的轉換為一個具體的數	eg：select ifnull（欄位名，指定的數值） from 表名;
now()	獲取當前時間

日期格式說明：

%Y：代表4位的年份

%y：代表2位的年份

%m：代表月，格式（01 … 12）

%c：代表月，格式（1…12）

%H：代表小時，格式（00…23）

%h：代表小時，格式（01…12）

%i：代表分鐘，格式（00…59）

%r：代表 時間，格式位12小時（hh:mm:ss）

%T：代表 時間，格式為24小時（hh:mm:ss）

%S：代表秒，格式（00…59）

%s：代表秒，格式（00…59）

（二）多行處理函式

函式名	說明	用法
count	取得記錄數	eg：select count（欄位名）from 表名;
sum	求和	eg：select sum（欄位名）from 表名;
avg	取平均	eg：select avg（欄位名）from 表名;
max	取最大數	eg：select max（欄位名）from 表名;
min	取最小數	eg：select min（欄位名）from 表名;

六、分組查詢

使用group by 和 having 陳述句

group by

語法格式：

select 欄位名 from 表名 group by 欄位名;

having

對已分組的資料再次進行過濾篩選

語法格式：

select 欄位名 from 表名 group by 欄位名 having ...;

select 總結

完整的select陳述句格式：

select 欄位
from 表名
where ...
group by ...
having ...
order by ..

執行順序：

1. 首先執行where陳述句過濾原始資料
2. 執行group by對資料進行分組
3. 執行having對分組的資料再次過濾
4. 執行select選出資料
5. 執行order by對資料排序

七、連接查詢

（一）內連接

將一張表看做兩張表

語法格式：

表1 inner join 表2 on 關聯條件

關聯條件必寫

inner可省略

select
	A.a1,A.a2,B.b 
from
	A欄位 A
inner join
	B欄位 B
on
	...
where
	...
;

（二）外連接

（1）左外連接

將左邊的表當做主表來比較，與左表不相等的會被顯示

表1 left outer join 表2 on 關聯條件

關聯條件必寫

outer可省略

select
	A.a1,A.a2,B.b 
from
	A欄位 A
left outer join
	B欄位 B
on
	...
where
	...
;

（2）右外連接

將右邊的表當做主表，與右表不相等的會被顯示

表1 rigth outer join 表2 on 關聯條件

關聯條件必寫

outer可省略

select
	A.a1,A.a2,B.b 
from
	A欄位 A
right outer join
	B欄位 B
on
	...
where
	...
;

（三）子查詢

嵌套的select陳述句

可使用子查詢的陳述句包括select、where、from等

（四）union

合并集合

語法格式：

select 欄位名 from 表名 where...
union
select 欄位名 from 表名 where...;

（五）limit

提取前幾潭訓中間幾條資料

語法格式：

select 欄位名 from 表名 limit m,n;

m：記錄開始的index，從0開始，表示第一條記錄，省略m默認從0開始

n：從第m+1條開始，提取n條

八、MySQL常用資料型別

型別	說明
char（長度）	定長字串，存盤空間大小固定，適合作為主鍵或者外鍵
varchar（長度）	變長字串，存盤空間等于實際資料空間
double（有效數字位數，小數位）	數值型
float（有效數字位數，小數位）	數值型
int（長度）	整型
bigint（長度）	長整型
Date	日期型
BLOB	二進制大物件
CLOB	字符大物件
其他…	…

九、表

（一）建表

語法格式：

create table 表名(
	欄位名 資料型別,
	...
);

（二）查看表

語法格式：

desc 表名;

（三）向表中加入資料

語法格式：

insert into 表名(要插入資料的欄位) values(對應欄位資料型別的資料);

同時插入多個可用逗號隔開

可以省略欄位插入

插入日期：

1. 插入日期格式和顯示格式一致
2. 采用str_to_date
3. 添加系統日期now()

（四）添加欄位

語法格式：

alter table 表名 add 欄位名 資料型別;

（五）修改欄位

語法格式：

alter table 表名 modify 欄位名 資料型別;

（六）洗掉欄位

語法格式：

alter table 表名 drop 欄位名;

（七）表復制

語法格式：

create table 新表名 as select 欄位名 from 表名;

（八）update

語法格式：

update 表名 set 欄位名1 = 需要修改的值1,欄位名2 = 需要修改的值2 where ...

（九）delete

語法格式：

delete from 表名 where...;

（十）約束

常見約束：

• 非空約束：not null
  • 某個欄位設定其值不能為空
• 唯一約束：unique
  • 某個欄位的值不能重復
• 主鍵約束：primary key
  • 每個表都一個具有主鍵
  • 標識記錄的唯一性
  • 分為單一主鍵和復合（聯合）主鍵
    • 單一主鍵：由一個欄位構成
    • 復合（聯合）主鍵：由多個欄位組成
• 外鍵約束：forgin key
  • 保證表的完整性
  • 若表中的某個欄位是外鍵欄位，則該欄位的值必須來源于參照的表的主鍵

（1）添加約束

添加主鍵約束

alter 
	table 表名 
add constraint 
	約束名稱 
primary key 
	表名(主鍵欄位);

添加外鍵約束

alter 
	table 從表表名 
add constraint 
	約束名稱 
forign key 
	從表表名(外鍵欄位) 
references 
	主表表名(主鍵欄位);

添加唯一性約束

alter 
	table 表名 
add constraint 
	約束名稱 
unique 
	表名(欄位);

（2）洗掉約束

洗掉主鍵約束

alter 
	table 表名 
drop 
	primary key;

洗掉外鍵約束

alter 
	table 表名 
drop forign key 
	外鍵(區分大小寫);

洗掉約束約束

alter 
	table 表名 
drop key 
	約束名稱;

（3）修改約束

即修改欄位

alter table 表名 modify 表名 資料型別 約束名稱;

十、事務

保證多個操作原子性，要么全成功，要么全失敗

特征ACID：

1. 原子性（Atomicity）
   • 整個事務中的所有操作，必須作為一個單元全部完成（或者全部取消）
2. 一致性（Consistency）
   • 在事務開始之前與結束之后，資料庫都保持一致
3. 隔離性（Isolation）
   • 一個事務不會影響另一個事務的運行
4. 持久性（Durability）
   • 在事務完成之后，該事務對資料庫所作的更改會持久保存在資料庫中，不會被回滾

開啟事務

start transaction;

回滾事務

rollback;

事務提交

commit;

十一、索引

（一）創建索引

create unique index 索引名 on 表名(列名);
或
alter table 表名 add unique index 索引名(列名);

（二）查看索引

show index from 表名;

（三）使用索引

explain select 欄位名 from 表名 where ... ;

（四）洗掉索引

drop index 索引名 on 表名;
或
alter table 表名 drop index 索引名;
或
alter table 表名 drop primary key;//一個表只有一個primary key索引

十二、視圖（虛擬表）

根據查詢定義的資料庫物件，用于獲取想要看到和使用的區域資料

作用：

1. 提高檢索效率
2. 隱藏表的實作細節（面向視圖檢索）

（一）創建視圖

create 
	view 視圖名 
as 
select 
	欄位名... 
from 
	表名... 
where 
	... 
;

MySQL不支持子查詢創建視圖

（二）修改視圖

alter 
	view 視圖名 
as 
select 
	欄位名... 
from 
	表名... 
where 
	... 
	;

（三）洗掉視圖

drop view if exists 視圖名;

if exists 判斷是否存在

十三、資料庫的匯入和匯出

（一）資料庫匯入

source 絕對路徑

（二）資料庫匯出

mysql dump 要匯出的資料庫/表 匯出位置的絕對路徑 -u root -p資料庫登錄密碼

十四、資料庫設計的三種范式

（一）第一范式

資料庫表中不能出現重復記錄，每個欄位是原子性的不能在分

每一行必須唯一，每個表必須要有主鍵，可使用數值型或者定長字串表示，不可分割

（二）第二范式

建立在第一范式的基礎上的，另外要求所有非主鍵欄位完全依賴主鍵，不能產生部分依賴

（三）第三范式

建立在第二范式的基礎上的，非主鍵欄位不能傳遞依賴于主鍵欄位（不能產生傳遞依賴）

（四）表的設計

一對多，兩張表，多的表加外鍵

多對多，三張表，關系表兩個外鍵

一對一，外鍵唯一，或主鍵共享