MySQL葵花宝典
一.數據庫基礎
1.數據庫的操作
1.1顯示當前的數據庫
show databases；1.2創建數據庫
create database [if not exists] db_name;1.3使用數據庫
use db_name;1.4刪除數據庫
drop database [if exists] db_name;數據庫刪除后，內部看不到對應的數據庫，里面的表和數據全被刪除
2.常用數據類型
2.1
| BIT[(M)] | M指定位數，默認為1 |
| TINYINT | 1字節 |
| SMALLINT | 2字節 |
| INT | 4字節 |
| BIGINT | 8字節 |
| FLOAT(M,D) | 4字節，單精度，M指定長度，D指定小數位數。會發生精度丟失 |
| DOUBLE(M,D) | 8字節 |
| DECIMAL(M,D) | M/D最大值+2，雙精度，M指定長度，D指定小數位數。精確數值 |
| NUMERIC(M,D) | M/D最大值+2，和DECIMAL一樣 |
數值類型可以指定為無符號（unsigned），表示不取負數。(盡量不適用unsigned)
1字節（bytes）=8bit
2.2字符串類型
| varchar(size) | 可變長度字符串 |
| text | 長文本數據 |
| mediumtext | 中等長度文本數據 |
| BOLB | 二進制形式的長文本數據 |
2.3日期類型
| datetime | 8字節，1000-9999年，不會進行時區的檢索及轉換 |
| timestamp | (時間戳)4字節，1970-2038，自動檢索當前時區并進行轉換 |
3.表的操作
需要操作數據庫中的表時，先要使用該數據庫
use db_name;3.1查看表
desc tb_name; //查看表名 show create table tb_name; //查看表結構+表編碼 show tables；//查看當前數據庫一共有多少表3.2創建表
create table tb_name (field1 datatype,field2 datatype,.......... );可以使用comment增加字段說明
示例：
create table stu_test (id int,name varchar(20) comment '姓名',password varchar(50) comment '密碼',age int,sex varchar(1),birthday timestamp,amout decimal(13,2),resume text );3.3刪除表
drop table [if exists] tb_name;3.4修改表內容
alter table tb_name add+新列名稱+類型;//新增一列 alter table tb_name change+原列名+新列名+新列類型;//修改某一列 alter table tb_name 舊表名 rename 新表名;//修改表名稱 alter table tb_name convert to character set 新編碼; //修改表編碼二.MySQL表的CRUD
CURD即增加、查詢、更新、刪除
1、新增（create）
insert [into] tb_name (屬性名) value (屬性值);案例：
-- 創建一張學生表 DROP TABLE IF EXISTS student; CREATE TABLE student (id INT,sn INT comment '學號',name VARCHAR(20) comment '姓名',qq_mail VARCHAR(20) comment 'QQ郵箱' );1.1單行數據插入+全列插入
-- 插入兩條記錄，value_list 數量必須和定義表的列的數量及順序一致 INSERT INTO student VALUES (100, 10000, '唐三藏', NULL); INSERT INTO student VALUES (101, 10001, '孫悟空', '11111');1.2 多行數據+指定列插入
-- 插入兩條記錄，value_list 數量必須和指定列數量及順序一致 INSERT INTO student (id, sn, name) VALUES(102, 20001, '曹孟德'),(103, 20002, '孫仲謀');2.查詢（retrieve）
語法：
SELECT[DISTINCT] {* | {column [, column] ...} [FROM table_name][WHERE ...][ORDER BY column [ASC | DESC], ...]LIMIT ...其中distinct為去重，asc為升序，desc為降序
案例：
-- 創建考試成績表 DROP TABLE IF EXISTS exam_result; CREATE TABLE exam_result (id INT,name VARCHAR(20),chinese DECIMAL(3,1),math DECIMAL(3,1),english DECIMAL(3,1) ); -- 插入測試數據 INSERT INTO exam_result (id,name, chinese, math, english) VALUES(1,'唐三藏', 67, 98, 56),(2,'孫悟空', 87.5, 78, 77),(3,'豬悟能', 88, 98.5, 90),(4,'曹孟德', 82, 84, 67),(5,'劉玄德', 55.5, 85, 45),(6,'孫權', 70, 73, 78.5),(7,'宋公明', 75, 65, 30);（1）全行全列查詢
select * from tb_name;（2）查找指定列
-- 指定列的順序不需要按定義表的順序來 SELECT id, name, english from exam_result;（3）查詢字段為表達式
-- 表達式不包含字段 SELECT id, name, 10 FROM exam_result; -- 表達式包含一個字段 SELECT id, name, english + 10 FROM exam_result; -- 表達式包含多個字段 SELECT id, name, chinese + math + english FROM exam_result;（4）別名
SELECT column [AS] alias_name [...] FROM table_name; -- 結果集中，表頭的列名=別名 SELECT id, name, chinese + math + english 總分 FROM exam_result;as 可以省略，列名與別名中間用空格省略
（5）去重查詢：DISTINCT
98分重復了
（6）排序：order by
select 列名 from tb_name order by 列名 [asc/desc];//默認升序若結果中包含null值，這個值null認為是最小值，比負數還小
針對多個列進行排序：
查看所有學生的姓名，語文成績，數學成績，其中按照語文成績升序，數學成績降序排序
優先按照語文成績升序排序，只有兩個語文成績相同時才按照數學成績降序排序
（7）分頁查詢 limit
select 屬性名稱 from tb_name limit n; //查詢結果按照前n行輸出 select 屬性名稱 from tb_name limit n offset s;//查詢結果按照前n行輸出，從第s行輸出開始（8）條件查詢：where （不支持別名）
3.修改表數據 update
update tb_name set 列名 = （表達式）;//根據表達式確定修改的行（1）修改一行一列
update exam_result set math = math + 30 where name = '張三';//把name屬性為‘張三’的數學成績+30（2）修改一行多列
update exam_result set math = math + 30,english = english + 20 where name = '張三';（3）修改多行數據（where過濾出來的結果是多行）
//成績表中所有名中帶三的加30分 update exam_result set math = math + 30 where name like '%三%';（4）全列修改
update 不帶where條件;//(重置)4.刪除（delete）
delete from 表名 where 條件;刪除全表數據
delete from exam_result;//行行delete，慢，可加條件，可恢復 truncate table exam_result;//相當于在os中直接把數據變成0，快，不可恢復，不能加條件三.MySQL進階
1.數據庫約束
1.1約束類型
- NOT NULL - 指示某列不能存儲 NULL 值。
- UNIQUE - 保證某列的每行必須有唯一的值。
- DEFAULT - 規定沒有給列賦值時的默認值。
- PRIMARY KEY - NOT NULL 和 UNIQUE 的結合。確保某列（或兩個列多個列的結合）有唯一標識，有助于更容易更快速地找到表中的一個特定的記錄。
- FOREIGN KEY - 保證一個表中的數據匹配另一個表中的值的參照完整性。
- CHECK - 保證列中的值符合指定的條件。對于MySQL數據庫，對CHECK子句進行分析，但是忽略CHECK子句。
示例：
-- 重新設置學生表結構 DROP TABLE IF EXISTS student; CREATE TABLE student ( id INT NOT NULL PRIMARY KEY, //id屬性不為空（設置為主鍵時可省略not null） sn INT UNIQUE,//指定sn屬性為唯一、不可重復的 name VARCHAR(20) DEFAULT 'unkown',//插入name列為空時，默認值為‘unknown’ qq_mail VARCHAR(20) );對于整數類型的主鍵，常常搭配自增長auto_increment來使用。插入數據對應字段不給值時，使用最大值+1
1.2外鍵用于關聯其他表的主鍵或唯一鍵
foreign key (字段名) references 主表（列）2.表的設計
三大范式
2.1 一對一
2.2一對多
2.3多對多
3.新增
插入查詢結果
insert into tb_name（字段1，字段2，...) select ....案例：創建一張用戶表，設計有name姓名、email郵箱、sex性別、mobile手機號字段。需要把已有的
學生數據復制進來，可以復制的字段為name、qq_mail
4.查詢
4.1聚合查詢
（1）聚合函數
| count() | 返回查詢到的數據數量 |
| sum() | 返回查詢數據的總和 |
| avg() | 返回查詢數據的平均值 |
| max() | 返回查詢到的數據的最大值 |
| min() | 返回查詢到的數據的最小值 |
count（任意值）效果相同，相當于在臨時表中創建了一列屬性，值都為設置的任意值，統計當前有多少行，速度快
（2）GROUP BY 子句
SELECT 中使用 GROUP BY 子句可以對指定列進行分組查詢。需要滿足：使用 GROUP BY 進行分組查詢時，SELECT 指定的字段必須是“分組依據字段”，其他字段若想出現在SELECT 中則必須包含在聚合函數
中。
(3)HAVING
GROUP BY 子句進行分組以后，需要對分組結果再進行條件過濾時，不能使用 WHERE 語句，而需要用HAVING
4.2聯合查詢
//初始化測試數據： drop table if exists classes; drop table if exists student; drop table if exists course; drop table if exists score;create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));create table student (id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20) ,classes_id int);create table course(id int primary key auto_increment, name varchar(20));create table score(score decimal(3, 1), student_id int, course_id int);insert into classes(name, `desc`) values ('計算機系2019級1班', '學習了計算機原理、C和Java語言、數據結構和算法'), ('中文系2019級3班','學習了中國傳統文學'), ('自動化2019級5班','學習了機械自動化');insert into student(sn, name, qq_mail, classes_id) values ('09982','黑旋風李逵','xuanfeng@qq.com',1), ('00835','菩提老祖',null,1), ('00391','白素貞',null,1), ('00031','許仙','xuxian@qq.com',1), ('00054','不想畢業',null,1), ('51234','好好說話','say@qq.com',2), ('83223','tellme',null,2), ('09527','老外學中文','foreigner@qq.com',2);insert into course(name) values ('Java'),('中國傳統文化'),('計算機原理'),('語文'),('高階數學'),('英文');insert into score(score, student_id, course_id) values -- 黑旋風李逵 (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6), -- 菩提老祖 (60, 2, 1),(59.5, 2, 5), -- 白素貞 (33, 3, 1),(68, 3, 3),(99, 3, 5), -- 許仙 (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6), -- 不想畢業 (81, 5, 1),(37, 5, 5), -- 好好說話 (56, 6, 2),(43, 6, 4),(79, 6, 6), -- tellme (80, 7, 2),(92, 7, 6);classes表
course表
score表
student表
(1)內連接
select 字段 from 表1 別名1 [inner] join 表2 別名2 on 連接條件 and 其他條件; select 字段 from 表1 別名1,表2 別名2 where 連接條件 and 其他條件;
（2）外連接
外連接分為左外連接和右外連接。如果聯合查詢，左側的表完全顯示我們就說是左外連接；右側的表完全顯示我們就說是右外連接。
案例：查詢所有同學的成績，及同學的個人信息，如果該同學沒有成績，也需要顯示
-- “老外學中文”同學 沒有考試成績，也顯示出來了 select * from student stu left join score sco on stu.id=sco.student_id; -- 對應的右外連接為： select * from score sco right join student stu on stu.id=sco.student_id; -- 學生表、成績表、課程表3張表關聯查詢 SELECT stu.id, stu.sn, stu.NAME, stu.qq_mail, sco.score, sco.course_id, cou.NAME FROM student stu LEFT JOIN score sco ON stu.id = sco.student_id LEFT JOIN course cou ON sco.course_id = cou.id ORDER BY stu.id;（3）自連接
自連接是指在同一張表連接自身進行查詢。
案例：
顯示所有“計算機原理”成績比“Java”成績高的成績信息
以上查詢只顯示了成績信息，并且是分布執行的。要顯示學生及成績信息，并在一條語句顯示：
SELECT stu.*, s1.score Java, s2.score 計算機原理 FROM score s1 JOIN score s2 ON s1.student_id = s2.student_id JOIN student stu ON s1.student_id = stu.id JOIN course c1 ON s1.course_id = c1.id JOIN course c2 ON s2.course_id = c2.id AND s1.score < s2.score AND c1.NAME = 'Java' AND c2.NAME = '計算機原理';（4）子查詢
子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢
- 單行子查詢：返回一行記錄的子查詢
查詢與“不想畢業” 同學的同班同學：
- 多行子查詢：返回多行記錄的子查詢
案例：查詢“語文”或“英文”課程的成績信息 - [NOT] IN關鍵字：
可以使用多列包含：
-- 插入重復的分數：score, student_id, course_id列重復 insert into score(score, student_id, course_id) values -- 黑旋風李逵 (70.5, 1, 1),(98.5, 1, 3), -- 菩提老祖 (60, 2, 1); -- 查詢重復的分數 SELECT * FROM score WHERE ( score, student_id, course_id ) IN ( SELECT score, student_id, course_id FROM score GROUP BY score, student_id, course_id HAVING count( 0 ) > 1 );- 在from子句中使用子查詢：子查詢語句出現在from子句中。這里要用到數據查詢的技巧，把一個
子查詢當做一個臨時表使用。
查詢所有比“中文系2019級3班”平均分高的成績信息：
查詢成績表中，比以上臨時表平均分高的成績
SELECT * FROM score sco, ( SELECT avg( sco.score ) score FROM score sco JOIN student stu ON sco.student_id = stu.id JOIN classes cls ON stu.classes_id = cls.id WHERE cls.NAME = '中文系2019級3班' ) tmp WHERE sco.score > tmp.score;（5）合并查詢
在實際應用中，為了合并多個select的執行結果，可以使用集合操作符 union，union all。使用UNION
和UNION ALL時，前后查詢的結果集中，字段需要一致。
- union
該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中的重復行。
案例：查詢id小于3，或者名字為“英文”的課程：
- union all
該操作符用于取得兩個結果集的并集。當使用該操作符時，不會去掉結果集中的重復行。
案例：查詢id小于3，或者名字為“Java”的課程
四.MySQL索引和事物
1.索引
1.1概念
索引是一種特殊的文件，包含著對數據表里所有記錄的引用指針。可以對表中的一列或多列創建索引，并指定索引的類型，各類索引有各自的數據結構實現。（具體細節在后續的數據庫原理課程講解）
1.2作用
- 數據庫中的表、數據、索引之間的關系，類似于書架上的圖書、書籍內容和書籍目錄的關系。
- 索引所起的作用類似書籍目錄，可用于快速定位、檢索數據。
- 索引對于提高數據庫的性能有很大的幫助。
1.3使用場景
- 數據量較大，且經常對這些列進行條件查詢。
- 該數據庫表的插入操作，及對這些列的修改操作頻率較低。
- 索引會占用額外的磁盤空間。
滿足以上條件時，考慮對表中的這些字段創建索引，以提高查詢效率。
反之，如果非條件查詢列，或經常做插入、修改操作，或磁盤空間不足時，不考慮創建索引。
1.4使用
創建主鍵約束（PRIMARY KEY）、唯一約束（UNIQUE）、外鍵約束（FOREIGN KEY）時，會自動創建對應列的索引。
- 查看索引
- 創建索引
對于非主鍵、非唯一約束、非外鍵的字段，可以創建普通索引
create index 索引名 on 表名(字段名);- 刪除索引
2.事務
2.1為什么要使用事務
準備測試表：
drop table if exists accout; create table accout(id int primary key auto_increment,name varchar(20) comment '賬戶名稱',money decimal(11,2) comment '金額' ); insert into accout(name, money) values ('阿里巴巴', 5000), ('四十大盜', 1000);比如說，四十大盜把從阿里巴巴的賬戶上偷盜了2000元
-- 阿里巴巴賬戶減少2000 update accout set money=money-2000 where name = '阿里巴巴'; -- 四十大盜賬戶增加2000 update accout set money=money+2000 where name = '四十大盜';假如在執行以上第一句SQL時，出現網絡錯誤，或是數據庫掛掉了，阿里巴巴的賬戶會減少2000，但是四十大盜的賬戶上就沒有了增加的金額。
解決方案：使用事務來控制，保證以上兩句SQL要么全部執行成功，要么全部執行失敗。
2.2 事務的概念
事務指邏輯上的一組操作，組成這組操作的各個單元，要么全部成功，要么全部失敗。
在不同的環境中，都可以有事務。對應在數據庫中，就是數據庫事務。
2.3事務的特性
2.4 使用
（1）開啟事務：start transaction;
（2）執行多條SQL語句
（3）回滾或提交：rollback/commit;
說明：rollback即是全部失敗，commit即是全部成功。
start transaction; -- 阿里巴巴賬戶減少2000 update accout set money=money-2000 where name = '阿里巴巴'; -- 四十大盜賬戶增加2000 update accout set money=money+2000 where name = '四十大盜'; commit;五.JDBC
1.獲取數據源
MysqlDataSource dataSource = new MysqlDataSoure();2.配置數據源屬性
dataSource.setURL("jdbc:mysql://127.0.0.1:3306/庫名？characterEncoding = utf8&useSSL=false"); dataSource.setUser("root"); dataSource.setPassword("數據庫的密碼");3.獲取數據庫的連接
需要導入java.sql.Connection
Connection connection = dataSource.getConnection();4.寫sql語句
String sql = "insert into user(username,password) values (?,?)";5.獲取sql對象
獲取執行sql的Statement對象，mysql中每一個sql的執行就對應著一個Statement對象
PreparedStatement statement = connection.prepareStatement(sql); statement.setString(1,"張三"); statement.setString(2,"111");statement:用于執行不帶參數的簡單sql語句
PrepareStatement: 1.用于執行帶或不帶參數的sql語句 2.sql語句會預編譯在數據庫系統 3.執行速度快于statement
6.執行這個sql,增刪改查調用executeUpdate，返回受影響的行數
int rows = statement.executeUpdate(); System.out.println("插入成功，插入" + rows + "行");7.關閉資源
connection.close();8.具體CURD的實現
總結
- 上一篇： 插入排序及其性能分析（动图演示）
- 下一篇： 学会老板思维，用数据诊断优化业务！