學習筆記【MySQL基礎操作-第一節：MySQL基本操作】

資料庫：DataBase；簡稱DB。用於儲存和管理資料的倉庫。

資料庫的特點：

• 持久化儲存資料的。其實DB就是一個檔案系統。
• 方便儲存和管理資料。
• 使用了統一的方式運算元據庫–SQL。

---

資料庫的種類：

• MySQL：開源免費的資料庫，小型的資料庫，已經被Oracle收購了MySQL6.x開始收費。
• Oracle：收費的大型資料庫。
• DB2：IBM公司的資料庫產品，收費的。長應用在銀行系統中。
• SQLServer：MicroSoft公司收費的中興的資料庫。C#、net等語言常使用。
• 等等…

---

MySQL

啟動：

• 手動
• cmd -> services.msc 開啟服務的視窗
• 使用管理員開啟cmd： net start mysql 啟動mysql的服務；net stop mysql 關閉mysql的服務。

登入：

• mysql -uroot -p密碼
• mysql -h127.0.0.1(連線目標ip) -uroot -p密碼
• –host-127.0.0.1 --user-root --password-密碼

退出：

• exit
• quit

目錄結構：

• MySQL安裝目錄（basedir）：配置檔案my.ini
• MySQL資料目錄(datadir)：資料庫：資料夾；表：檔案；資料：資料。

---

SQL

SQL結構化查詢語言（Structured Query Language）

其實就是定義了操作所有關係型資料庫的規則。每一種資料庫操作的方式存在不一樣的地方，稱為“方言”。

SQL通用語法：

• SQL語句可以單行或多行書寫，以分號結尾。
• 可以使用空格和縮排來增強語句的可讀性。
• MySQL資料庫的SQL語句不區分大小寫，關鍵字建議使用大寫。
• 三種註釋：①單行註釋：-- 註釋內容 （要帶空格）或 # 註釋內容（mysql特有）②多行註釋 / *註釋 * /。

SQL分類

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

---

DDL 運算元據庫、表

1.運算元據庫：CRUD

C（create）建立：

• ①create database 資料庫名稱；
• ②先判斷有無再建立：create database if not exists 資料庫名稱
• ③先判斷再指定字符集：create database if not exists 資料庫名稱 character set gbk；

R（Retrieve）查詢：

• ①查詢所有資料庫的名稱show databases；
• ②查詢某個資料庫的字符集（查詢某個資料庫的建立語句）show create database 資料庫名稱。

U（Update）修改：

• 修改字符集：alter database 資料庫名稱 character set utf8；

D（Delete）刪除：

• ①drop database 資料庫名稱；
• ②先判斷有無再刪除：drop database if exists 資料庫名稱；

使用資料庫：

• ①查詢當前正在使用的資料庫名稱：select database();
• ②使用資料庫：use 資料庫名稱。

2.操作表：CRUD

C（create）建立：

• 建立表：create table 表名稱(列名1 資料型別1，列名2 資料型別2，…，列名n 資料型別n);

R（Retrieve）查詢：

• ①查詢某個資料庫中所有表的名稱：show tables;
• ②查詢表結構：desc 表名稱。

U（Update）修改：

• ①修改表名：alter table 表名 rename to 新名;
• ②修改表的字符集：alter table 表名 character set 字符集名稱（如utf8）;
• ③新增一列：alter table 表名 add 列名 資料型別；
• ④修改列名稱、型別：alter table 表名 change 列名 新列名 新資料型別; 或 alter table 表名 modify 列名 新資料型別;
• ⑤刪除列：alter table 表名 drop 列名;

D（Delete）刪除：

• ①drop table 表名；
• ②drop table if exists 表名；

常用資料型別：

• int整數型別，如age int,
• double小數型別，如score double(5,2)(共5位，保留2位，最大999.99)
• date日期，只包含年月日，yyyy-MM-dd
• datetime日期，包含年月日時分秒 yyyy-MM-dd HH:mm:ss
• timestamp日期，包含年月日時分秒 yyyy-MM-dd HH:mm:ss，若將來不給這個欄位賦值，或賦值為null，則預設使用當前的系統時間，來自動賦值
• varchar，字串，如name varchar(20);姓名對打20個字元，zhangsan8個字元，張三2個字元

---

DML 增刪改表中的資料

增加資料

• insert into 表名（列名1，列名2，…列名n） values(值1，值2，…值n);
• 列名和值需要一一對應。
• 如果表明後，不定義列名，則預設給所有列新增值：insert into 表名 values（值1，值2，…值n）;
• 除了數字型別，其他型別都需要用引號引用。

刪除資料

• delete from 表名 [where 條件]
• 如果不加條件，則刪除表中所有記錄。
• truncate table 表名；刪除表，然後建立一個一模一樣的空表。
• 如果刪除所有記錄：①不推薦使用delete from 表名；因為有多少條記錄就會被執行多少次刪除操作；②推薦使用 truncate table 表名；效率高。

修改資料

• update 表名 set 列名1=值1，列名2=值2，…列名n=值n [where 條件]；
• 如果不加任何條件則會把表中所有記錄全部修改。

---

DQL 查詢語句

select * from 表名

語法

select 欄位列表 from 表明列表 where 條件列表 group by 分組欄位 having 分組後的條件 order by 排序 limit 分頁限定

基礎查詢

• 如查詢姓名及年齡：SELECT NAME, age FROM students;
• 去除重複的結果集（SELECT DISTINCT），如：SELECT DISTINCT id FROM students;若有兩個，則兩個完全一樣才能去除，如：SELECT DISTINCT id,NAME FROM students;。
• 如果有math 和english分數，且要算它們之和：SELECT name,math,english,math+english FROM students;
• 但有null的計算結果都為null，上面業務不合理，可用IFNULL，上面可改為：IFNULL(math,0)+IFNULL(english,0)
• 後面可以起別名：IFNULL(math,0)+IFNULL(english,0) AS 總分（AS可省略）

條件查詢

• where子句後跟條件
• 運算子>,<,>=,<=,=,!=,<>(也是不等於)
• 查詢年齡大於等於20、小於等於30：① WHERE age>=20&&age<=30; ②WHERE age>=20 AND age<=30; ③WHERE age BETWEEN 20 AND 30（包括20和30）;
• 查詢年齡22歲，19歲，25歲的資訊：①WHERE age=22 OR age=19 OR age=25; ②WHERE age IN (22,18,25);
• NULL只能用IS NULL或IS NOT NULL判斷；
• LIKE 模糊查詢：① _ :單個任意字元，如查詢姓馬的：WHERE NAME LIKE " 馬%"； ②%:多個任意字元，如查詢第二個字為化的WHERE NAME LIKE " _ 化% " ;查詢名字三個字的人：WHERE NAME LIKE " _ _ _ "; 查詢名字中包含馬的人WHERE NAME LIKE " %馬% ";

排序查詢

• order by 子句：order by 排序欄位1 排序方式1，排序欄位2 排序方式2…
• 例：SELECT * FROM students ORDER BY score;
• ASC：升序，預設的。
• DESC：降序，如SELECT * FROM students ORDER BY score DESC;
• 先按照score降序，一樣的再按id升序：SELECT * FROM students ORDER BY score DESC,id ASC;

聚合函式

• 將一列資料作為一個整體，進行縱向的計算。
• count：計算個數
• max：計算最大值
• min：計算最小值
• sum：計算和
• avg：計算平均值
• 如：SELECT COUNT(NAME) FROM students;
• 聚合函式的計算，排除null值。解決方法：①選擇不包含非空的列進行計算；②IFNULL函式。

分組查詢

• group by 分組欄位；
• 分組之後查詢的欄位：分組欄位、聚合函式。
• 如：SELECT sex,AVG(score),COUNT(NAME) FROM students GROUP BY sex;
• 加條件，要大於60分才參與分組：SELECT sex,AVG(score),COUNT(NAME) FROM students WHERE score>60 GROUP BY sex;
• 再加條件，分組後人數需大於2的：SELECT sex,AVG(score),COUNT(NAME) FROM students WHERE score>60 GROUP BY sex HAVING COUNT(id)>2;
• where和having的區別：① where再分組前進行限定，不符合則不參與分組；having在分組後限定，不符合條件則不會被查詢出來。② where後不可以跟聚合函式，having可跟聚合函式。聚合函式後可以跟個別名。

分頁查詢

• limit 開始的索引，每頁查詢的條數
• 如每頁顯示2條，顯示第一頁的記錄：SELECT * FROM students LIMIT 0,2;
• 公式：開始的索引=（當前的頁碼-1）*每頁顯示的條數。
• limit是一個MySQL的“方言”。