MySQL資料庫入門

2022 年 1 月 10 日
筆記
MySQL, MySQL資料庫

初始MySQL

JavaEE：企業級Java開發 Web

前端（頁面：展示，數據！）

後台（連接點：連接資料庫JDBC，鏈接前端（控制，控制視圖跳轉，和給前端傳遞數據））

資料庫（存數據，Txt，Excel，word）

資料庫是所有軟體體系中最核心的存在 DBA

資料庫概念：數據倉庫，軟體，安裝在作業系統之上。作用：存儲數據，管理數據

資料庫分類

關係型資料庫：（SQL）

MySQL，Oracle，Sql Server，DB2，SQLlite
通過表和表之間，行和列之間的關係進行數據的存儲，學員資訊表，考勤表，……

非關係型資料庫：（NoSQL） Not Only

Redis，MongDB
非關係型資料庫，對象存儲，通過對象的自身的屬性來決定

DBMS（資料庫管理系統）

資料庫的管理軟體，科學有效的管理我們的數據，維護和獲取數據；
MySQL，資料庫管理系統；
MySQL是一個關係型資料庫管理系統

安裝MySQL詳細說明

解壓
把這個包放到自己的電腦環境目錄下
配置環境變數

#新建my.ini配置文件
[mysqld]
basedir=D:\environment\mysql-5.7.19\
datadir=D:\environment\mysql-5.7.19\data\
port=3306
#skip-grant-tables

啟動管理員模式下的CMD，運行所有的命令
安裝mysql服務
初始化資料庫文件
啟動mysql，進去修改密碼~
進入mysql通過命令行（-p後面不要加空格），修改密碼（sql語句後面一定要加分號！）
注掉ini中的跳過密碼
重啟mysql。連接測試，如果連接成功就OK了
啟動畫面

Sqlyog軟體安裝與使用

無腦安裝
註冊
打開連接資料庫
新建一個資料庫 school

每一個sqlyog的執行操作，本質就是對應了一個sql，可以在軟體的歷史紀錄中查看

新建一張表 student

id, name, age

查看錶
自己嘗試添加多條記錄

連接資料庫

命令行連接！

mysql -uroot -p123456 --連接資料庫

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; --修改用戶密碼

flush privileges; --刷新許可權

-------------------------------------------------------所有的語句都使用；結尾
show databases; --查看所有的資料庫

mysql> use school; --切換資料庫 use 資料庫名
Database changed

show tables; --查看資料庫中所有的表

describe student; --顯示資料庫中所有的表中的資訊

create database westos; --創建一個資料庫

exit; --退出連接

-- 單行注釋（SQL的本來的注釋）
/* （sql的多行注釋）
hello
asdas
*/

資料庫xxx語言 CRUD增刪改查！ CV程式設計師 API程式設計師 CRUD程式設計師（業務！）

DDL 定義

DML 操作

DQL 查詢

DCL 控制

操作資料庫

操作資料庫>操作資料庫中的表>操作資料庫中表的數據

MySQL的關鍵字不區分大小寫

操作資料庫（了解）

創建資料庫
```
CREATE DATABASE [IF NOT EXISTS] westos
```
刪除資料庫
```
DROP DATABASE [IF EXISTS] westos
```

使用資料庫

-- tab 鍵的上面，如果你的表明或者欄位名是一個特殊字元，就需要帶``
USE `school`

查看資料庫

SHOW DATABASES -- 查看所有的資料庫

對比：SQLyog的可視化操作

學習思路：

對照sqlyog可視化歷史記錄查看sql
固定的語法或關鍵字必須要強行記住！

資料庫的列類型

數值

tinyint 十分小的數據一個位元組
smallint 較小的數據兩個位元組
mediumint 中等大小的數據三個位元組
int 標準的整數四個位元組常用的
bigint 較大的數據 8個位元組
float 浮點數 4個位元組
double 浮點數 8個位元組
decimal 字元串形式的浮點數金融計算的時候一般是使用decimal

字元串

char 字元串固定大小的 0~255
varchar 可變字元串 0~65535 常用的變數 String
tinytext 微型文本 2^8-1
text 文本串 2^16-1 保存大文本

時間日期

java.util.Date

date YYYY-MM-DD，日期格式
time HH：mm：ss 時間格式
datetime YYYY-MM-DD HH：mm：ss 最常用的時間格式
timestamp 時間戳，1970.1.1 到現在的毫秒數！ 也較為常用！
year 年份表示

null

沒有值，未知
注意，不要使用NULL進行運算，結果為NULL

資料庫的欄位屬性（重點）

Unsigned：

無符號的整數
聲明了該列不能聲明為負數

zerofill：

0填充的
不足的位數，使用0來填充， int（3）， 5—005

自增：

通常理解為自增，自動在上一條記錄的基礎上+1（默認）
通常用來設計唯一的主鍵~ index，必須是整數類型
可以自定義設計主鍵自增的起始值和步長

非空 NULL not null

假設設置為not null，如果不給他賦值，就會報錯
null，如果不填寫值，默認就是null

默認：

設置默認的值
sex，默認值為男，如果不指定該列的值，則會有默認的值！

拓展：

/* 每一個表，都必須存在以下五個欄位！未來做項目用的，表示一個記錄存在的意義！
id 主鍵
`version` 樂觀鎖
is_delete 偽刪除
gmt_create 創建時間
gmt_update 修改時間
*/

創建資料庫表（重點）

-- 目標：創建一個school資料庫
-- 創建學生表（列，欄位） 使用SQL創建
-- 學號int 登陸密碼varchar（20）姓名，性別varchar（2），出生日期（datetime），家庭住址，email

-- 注意點，使用英文()，表的名稱和欄位盡量使用``括起來
-- AUTO_INCREMENT 自增
-- 字元串使用單引號括起來！
-- 所有的語句後面加，（英文的），最後一個不用加
-- PRIMARY KEY 主鍵，一般一個表只有一個唯一的主鍵！

CREATE TABLE IF NOT EXISTS `student`(	
   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '學號',
   `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
   `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密碼',
   `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性別',
   `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
   `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
   `email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
   PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
	`欄位名` 列類型 [屬性] [索引] [注釋],
    `欄位名` 列類型 [屬性] [索引] [注釋],
    ......
    `欄位名` 列類型 [屬性] [索引] [注釋]
)[表類型][字符集設置][注釋]

常用命令

SHOW CREATE DATABASE school -- 查看創建資料庫的語句
SHOW CREATE TABLE student -- 查看student數據表的定義語句
DESC student -- 顯示錶的結構

數據表的類型

-- 關於資料庫引擎
/*
INNODB 默認使用
MYISAM 早些年使用的
*/

	MYISAM	INNODB
事務支援	不支援	支援
數據行鎖定	不支援	支援
外鍵約束	不支援	支援
全文索引	支援	不支援
表空間的大小	較小	較大，約為2倍

常規使用操作：

MYISAM 節約空間，速度較快
INNODB 安全性高，事務的處理，多表多用戶操作

在物理空間存在的位置

所有的資料庫文件都存在data目錄下

本質還是文件的存儲！

MySQL引擎在物理文件上的區別

InnoDB在資料庫表中只有一個*.frm文件，以及上級目錄下的 ibdata1文件
MYISAM對應文件
- *.frm 表結構的定義文件
- *.MYD 數據文件（data）
- *.MYI 索引文件（index）

設置資料庫表的字符集編碼

CHARSET=utf8

不設置的話，會是mysql默認的字符集編碼– （不支援中文！）

MySQL的默認編碼是Latin1，不支援中文

在my.ini中配置默認的編碼

character-set-server=utf8

修改刪除表

修改

-- 修改表 ALTER TABLE 舊錶名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的欄位 ALTER TABLE 表名 ADD 欄位名 列屬性
ALTER TABLE teacher1 ADD age INT(11)
-- 修改表的欄位（重命名，修改約束！）
-- ALTER TABLE 表名 MODIFY 欄位名 列屬性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改約束
-- ALTER TABLE 表名 CHANGE 舊名字 新名字 列屬性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 欄位重命名
-- 刪除表的欄位：ALTER TABLE 表名 DROP 欄位名
ALTER TABLE teacher1 DROP age1

刪除

-- 刪除表（如果表存在再刪除）
DROP TABLE IF EXISTS teacher1

所有的創建和刪除操作盡量加上判斷，以免報錯

注意點：

“欄位名，使用這個包裹！
注釋 — /**/
sql 關鍵字大小寫不敏感，建議大家寫小寫
所有的符號全部用英文

MySQL的數據管理

外鍵（了解即可）

方式一、在創建表的時候，增加約束（麻煩，比較複雜）

CREATE TABLE IF NOT EXISTS `grade`(
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年級id',
   `gradename` VARCHAR(50) NOT NULL COMMENT '年級名稱',
   PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 學生表的 gradeid欄位 要去引用年級表的gradeid
-- 定義外鍵key
-- 給這個外鍵添加約束（執行引用） references 引用

CREATE TABLE IF NOT EXISTS `student`(
   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '學號',
   `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
   `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密碼',
   `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性別',
   `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
   `gradeid` INT(10) NOT NULL COMMENT '學生的年級',   
   `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
   `email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
   PRIMARY KEY(`id`),
   KEY `FK_gradeid` (`gradeid`),
   CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

刪除有外鍵關係的表的時候，必須要先刪除引用別人的表（從表），再刪除被引用的表（主表）

方式二：創建表成功後，添加外鍵約束

CREATE TABLE IF NOT EXISTS `grade`(
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年級id',
   `gradename` VARCHAR(50) NOT NULL COMMENT '年級名稱',
   PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 學生表的 gradeid欄位 要去引用年級表的gradeid
-- 定義外鍵key
-- 給這個外鍵添加約束（執行引用） references 引用

CREATE TABLE IF NOT EXISTS `student`(
   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '學號',
   `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
   `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密碼',
   `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性別',
   `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
   `gradeid` INT(10) NOT NULL COMMENT '學生的年級',   
   `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
   `email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 創建表的時候沒有外鍵關係
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

-- ALTER TABLE 表 ADD CONSTRAINT 約束名 FOREIGN KEY (作為外鍵的列) REFERENCES 那個表(哪個欄位)

以上的操作都是物理外鍵，資料庫級別的外鍵，我們不建議使用！（避免資料庫過多造成困擾，這裡了解即可）

最佳實踐

資料庫就是單純的表，只用來存數據，只有行（數據）和列（欄位）
我們想使用多張表的數據，想使用外鍵（程式去實現）

DML語言（全部記住）

資料庫意義：數據存儲，數據管理

DML語言：數據操作語言

Insert
update
delete

添加

insert

-- 插入語句（添加）
-- insert into 表名([欄位名1,欄位2,欄位3]) values('值1'),('值2'),('值3'), ....
INSERT INTO `grade`(`gradename`) VALUES('大四')

-- 由於主鍵自增我們可以省略（如果不寫表的欄位，他就會一一匹配）
INSERT INTO `grade` VALUES('大三')
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES('大三',NULL)

-- 一般寫插入語句，我們一定要數據和欄位一一對應

-- 插入多個欄位
INSERT INTO `grade` (`gradename`) VALUES('大二'),('大一')

INSERT INTO `student` (`name`) VALUES ('張三')

INSERT INTO `student` (`name`,`pwd`,`sex`) VALUES ('張三','aaaa','男')

INSERT INTO `student` (`name`,`pwd`,`sex`) 
VALUES ('張三','aaaa','男'),('王五','aaaa','男')

INSERT INTO `student` 
VALUES (5,'李四','aaaaaa','男','2000-01-01',1,'西安','email')

語法：insert into 表名([欄位名1,欄位2,欄位3]) values(‘值1’),(‘值2’),(‘值3’), ….

注意事項：

欄位和欄位之間使用英文逗號隔開
欄位是可以省略的，但是後面的值必須要一一對應，不能少
可以同時插入多條數據，VALUES後面的值，需要使用逗號隔開即可 VALUES(),()……

修改

update 修改誰（條件） set原來的值=新值

-- 修改學員名字
UPDATE `student` SET `name`='狂神' WHERE id = 1

-- 不指定條件的情況下，會改動所有表！
UPDATE `student` SET `name`='長江7號'

-- 修改多個屬性
UPDATE  `student` SET `name`='狂神',`email`='[email protected]' WHERE id = 1

-- 語法：
-- UPDATE 表名 SET column_name = value,[column_name = value,......] where [條件]

條件：where子句運算符 id等於某個值，大於某個值，在某個區間內修改…

操作符	含義	範圍	結果
=	等於	5=6	false
<>或!=	不等於	5<>6	true
>
<
<=
>=
BETWEEN … and …	在某個範圍內	[2,5]
AND	我和你&&	5>1 and 1>2	false
OR	我或你\|\|	5>1 or 1>2	true

-- 通過多個條件定位數據
UPDATE `student` SET `name`='狂神123' WHERE `name` = '狂神' AND `sex` = '女'

語法：UPDATE 表名 SET column_name = value,[column_name = value,……] where [條件]

注意：

column_name 是資料庫的列，盡量帶上“
條件，篩選的條件，如果沒有指定，則會修改所有的列
value，是一個具體的值，也可以是一個變數
多個設置的屬性之間，使用英文逗號隔開

UPDATE `student` SET `birthday`= CURRENT_TIME WHERE `name` = '狂神' AND `sex` = '女'

刪除

delete 命令

語法：delete from 表名 [where 條件]

-- 刪除數據（避免這樣寫）
DELETE FROM `student`

-- 刪除指定數據
DELETE FROM `student` WHERE id = 1

TRUNCATE 命令

作用：完全清空一個資料庫表，表的結構和索引約束不會變！

-- 清空 student 表
TRUNCATE `student`

delete 和 TRUNCATE 的區別

相同點：都能刪除數據，都不會刪除表結構
不同：
- TRUNCATE 重新設置自增列計數器會歸零
- TRUNCATE 不會影響事務

-- 測試delete 和 TRUNCATE 區別
CREATE TABLE `test` (
   `id` INT(4) NOT NULL AUTO_INCREMENT,
   `coll` VARCHAR(20) NOT NULL,
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`coll`) VALUES ('1'),('2'),('3')

DELETE FROM `test` -- 不會影響自增

TRUNCATE TABLE `test` -- 自增回歸零

了解即可：DELETE刪除的問題，重啟資料庫，現象

InnoDB 自增列會從1開始（存在記憶體當中的，斷電即失）
MyISAM 繼續從上一個自增量開始（存在文件中的，不會丟失）

DQL查詢數據（最重點）

DQL

（Data Query LANGUAGE：數據查詢語言）

所有的查詢操作都用它 Select
簡單的查詢，複雜的查詢它都能做
資料庫中最核心的語言，最重要的語句
使用頻率最高的語句

Select完整的語法：

SELECT語法

SELECT [ALL|DISTINCT]
{*|table.*|[table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
	[left | right | inner join table_name2]  -- 聯合查詢
	[WHERE ...]  -- 指定結果需滿足的條件
	[GROUP BY ...]  -- 指定結果按照哪幾個欄位來分組
	[HAVING]  -- 過濾分組的記錄必須滿足的次要條件
	[ORDER BY ...]  -- 指定查詢記錄按一個或多個條件排序
	[LIMIT {[offset,]row_count | row_countOFFSET offset}];  -- 指定查詢的記錄從哪條至哪條

注意：[]括弧代表可選的，{}括弧代表必選的

指定查詢欄位

-- 查詢全部的學生    SELECT 欄位 FROM 表
SELECT * FROM student

-- 查詢指定欄位
SELECT `StudentNo`,`StudentName` FROM student

-- 別名，給結果起一個名字    AS    可以給欄位起別名，也可以給表起別名
SELECT `StudentNo` AS '學號',`StudentName` AS '學生姓名' FROM `student` AS `s`

-- 函數 Concat(a,b)

SELECT CONCAT('姓名：',`StudentName`) AS '新名字' FROM `student`

語法：SELECT 欄位,… FROM 表

有的時候，列名字不是那麼的見名知意，我們起別名 AS 欄位名 as 別名表名 as 別名

去重 distinct

作用：去除SELECT查詢出來的結果中重複的數據，重複的數據只查詢一條

-- 查詢一下有哪些同學參加了考試，成績
SELECT * FROM `result`  -- 查詢全部的考試成績
-- 查詢有哪些同學參加了考試
SELECT `StudentNo` FROM `result`
-- 發現重複數據，去重
SELECT DISTINCT `StudentNo` FROM `result`

資料庫的列（表達式）

SELECT VERSION() -- 查詢系統版本
SELECT 100*3-1 AS `計算結果`  -- 用來計算
SELECT @@auto_increment_increment  -- 查詢自增的步長（變數）

-- 學院考試成績 + 1分查看
SELECT `StudentNo`,`StudentResult`+1 AS '提分後' FROM `result`

資料庫中的表達式：文本值，列，Null，函數，計算表達式，系統變數

SELECT 表達式 FROM 表

where條件子句

作用：檢索數據中符合條件的值

搜索的條件由一個或者多個表達式組成！結果布爾值

邏輯運算符

運算符	語法	描述
and &&	a and b a&&b	邏輯與，兩個都為真，結果為真
or \|\|	a or b a\|\|b	邏輯或，其中一個為真，則結果為真
Not !	not a !a	邏輯非，真為假，假為真！

盡量使用英文字母

-- ==============================================  where  =======================================
SELECT `StudentNo`,`StudentResult` FROM `result`

-- 查詢考試成績在 95-100 分之間
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult`>=95 AND `StudentResult`<=100

-- and    &&
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult`>=95 && `StudentResult`<=100

-- 模糊查詢（區間）
SELECT `StudentNo`,`StudentResult` FROM result 
WHERE `StudentResult` BETWEEN 95 AND 100

-- 除了900號學生之外的同學的成績
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNo`!=900

-- !=    not
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo` = 900

模糊查詢：比較運算符

運算符	語法	描述
IS NULL	a is null	如果操作符為NULL，結果為真
IS NOT NULL	a is not null	如果操作符為not null，結果為真
BETWEEN	a between b and c	若a在b和c之間，則結果為真
Like	a like b	SQL匹配，如果a匹配b，則結果為真
In	a in(a1,a2,a3…)	假設a在a1，或者a2…其中的某一個值中，結果為真

-- =====================  模糊查詢  ============================
-- 查詢姓劉的同學
-- like結合    %（代表0到任意個字元）    _（一個字元）
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '劉%'

-- 查詢姓劉的同學，名字後面只有一個字的
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '劉_'

-- 查詢姓劉的同學，名字後面只有兩個字的
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '劉__'

-- 查詢名字中間有嘉字的同學
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%嘉%'

-- ======================  in（具體的一個或者多個值）  ====================
-- 查詢 1001，1002，1003號學員
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003)

-- 查詢在北京的學生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN ('安徽','河南洛陽')

-- ============== null    not null =======================
-- 查詢地址為空的學生 null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address`='' OR `Address` IS NULL

-- 查詢有出生日期的同學  不為空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL

-- 查詢沒有出生日期的同學  為空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NULL

聯表查詢

JOIN 對比

操作	描述
inner join	如果表中至少有一個匹配，就返回行
left join	會從左表中返回所有的值，即使右表中沒有匹配
right join	會從右表中返回所有的值，即使左表中沒有匹配

-- ==================  聯表查詢  ==========================
-- 查詢參加了考試的同學（學號，姓名，科目編號，分數）
SELECT * FROM student
SELECT * FROM result

/* 思路
1. 分析需求，分析查詢的欄位來自那些類，（連接查詢）
2. 確定使用哪種連接查詢？ 7種
確定交叉點（這兩個表中那個數據是相同的）
判斷的條件：學生表中的 StudentNo = 成績表中的
StudentNo
*/

-- join（連接的表） on（判斷的條件） 連接查詢
-- where 等值查詢

SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` 
FROM student AS s INNER JOIN result AS r 
WHERE s.StudentNo = r.StudentNo

-- Right Join
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` 
FROM student s RIGHT JOIN result r ON s.StudentNo = r.StudentNo

-- Left Join
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` 
FROM student s LEFT JOIN result r ON s.StudentNo = r.StudentNo

-- 查詢缺考的同學
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` 
FROM student s LEFT JOIN result r ON s.StudentNo = r.StudentNo
WHERE `Studentresult` IS NULL

-- 思考題（查詢參加了考試的同學資訊：學號，學生姓名，科目名，分數）
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult` 
FROM student s 
LEFT JOIN result r 
ON s.StudentNo = r.StudentNo
RIGHT JOIN `subject` su 
ON r.SubjectNo = su.SubjectNo

-- 我要查詢哪些數據 select ...
-- 從哪幾個表中查 FROM 表  XXX Join 連接的表  on  交叉條件
-- 假設存在一種多張表查詢，慢慢來，先查詢兩張表然後再慢慢增加

-- From a left join b
-- From a right join b

自連接（了解）

自己的表和自己的表連接，核心：一張表拆為兩張一樣的表即可

父類

categoryid	categoryName
2	資訊技術
3	軟體開發
5	美術設計

子類

pid	categoryid	categoryName
3	4	資料庫
2	8	辦公資訊
3	6	web開發
5	7	ps技術

操作：查詢父類對應的子類關係

父類	子類
資訊技術	辦公資訊
軟體開發	資料庫
軟體開發	web開發
美術設計	ps技術

-- ==================  自連接  =============================

CREATE TABLE `category` (
   `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主題id',
   `pid` INT(10) NOT NULL COMMENT '父id',
   `categoryName` VARCHAR(50) NOT NULL COMMENT '主題名字',
   PRIMARY KEY (`categoryid`)
)ENGINE = INNODB AUTO_INCREMENT = 9 DEFAULT CHARSET = utf8

INSERT INTO `category` (`categoryid`,`pid`,`categoryName`)
VALUES ('2','1','資訊技術'),
('3','1','軟體開發'),
('4','3','資料庫'),
('5','1','美術設計'),
('6','3','web開發'),
('7','5','ps技術'),
('8','2','辦公資訊');


-- 查詢父子資訊：把一張表看為兩個一模一樣的表

SELECT a.`categoryName` AS '父欄目',b.`categoryName` AS '子欄目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`

-- 查詢學員所屬的年級（學號，學生的姓名，年級名稱）
SELECT StudentNo,StudentName,GradeName 
FROM student s INNER JOIN grade g
ON s.GradeId = g.GradeId

-- 查詢參加了資料庫結構-1考試的同學資訊：學號，學生姓名，科目名，分數
SELECT s.`StudentNo`,s.`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s INNER JOIN `subject` sub 
ON s.GradeId = sub.GradeId
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
WHERE SubjectName = '資料庫結構-1'

分頁和排序

排序

-- ==========================  分頁  limit  和排序  order by  =====================================

-- 排序：升序ASC，降序DESC
-- ORDER BY  通過哪個欄位排序，怎麼排
-- 查詢的結果根據  成績降序  排序
SELECT s.`StudentNo`,s.`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s INNER JOIN `subject` sub 
ON s.GradeId = sub.GradeId
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
WHERE SubjectName = '資料庫結構-1'
ORDER BY StudentResult ASC

分頁

-- 100萬
-- 為什麼分頁
-- 緩解資料庫壓力，給人的體驗更好，瀑布流
-- 網頁應用：
-- 分頁，每頁只顯示五條數據
-- 語法：limit 起始值，頁面的大小
-- LIMIT 0,5    1~5
-- LIMIT 1,5    2~6
-- LIMIT 6,5    
SELECT s.`StudentNo`,s.`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s INNER JOIN `subject` sub 
ON s.GradeId = sub.GradeId
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
WHERE SubjectName = '資料庫結構-1'
ORDER BY StudentResult ASC
LIMIT 0,5

-- 第一頁  limit 0,5		(1-1)*5
-- 第二頁  limit 5,5		(2-1)*5
-- 第三頁  limit 10,5		(3-1)*5
-- 第N頁   limit 0,5		(n-1)*pageSize，pageSize
-- 【pageSize：頁面大小，n 當前頁】
-- 【(n-1)*pageSize起始值】
-- 【n：當前頁】
-- 【數據總數/頁面大小 = 總頁數】

語法：limit (查詢起始下標，pageSize)

子查詢

where（這個值是計算出來的）

本質：在where語句中嵌套一個子查詢語句

where（select*from）

CREATE DATABASE IF NOT EXISTS `school`;
-- 創建一個school資料庫
USE `school`;

-- 創建年級表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
   `GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年級編號',
   `GradeName` VARCHAR(50) NOT NULL COMMENT '年級名稱',
PRIMARY KEY (`GradeID`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;

-- 插入年級數據
INSERT INTO `grade` (`GradeID`,`GradeName`) VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'預科班');

-- 創建成績表
DROP TABLE IF EXISTS `result`;

CREATE TABLE `result`(
   `StudentNo` INT(4) NOT NULL COMMENT '學號',
   `SubjectNo` INT(4) NOT NULL COMMENT '課程編號',
   `ExamDate` DATETIME NOT NULL COMMENT '考試日期',
   `StudentResult` INT(4) NOT NULL COMMENT '考試成績',
KEY `SubjectNo` (`SubjectNo`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

-- 插入成績數據 這裡僅插入了一組，其餘自行添加
INSERT INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 創建學生表
DROP TABLE IF EXISTS `student`;

CREATE TABLE `student`(
   `StudentNo` INT(4) NOT NULL COMMENT '學號',
   `LoginPwd` VARCHAR(20) DEFAULT NULL,
   `StudentName` VARCHAR(20) DEFAULT NULL COMMENT '學生姓名',
   `Sex` TINYINT(1) DEFAULT NULL COMMENT '性別，0或1',
   `GradeId` INT(11) DEFAULT NULL COMMENT '年級編號',
   `Phone` VARCHAR(50) NOT NULL COMMENT '聯繫電話，允許為空',
   `Address` VARCHAR(255) NOT NULL COMMENT '地址，允許為空',
   `BornDate` DATETIME DEFAULT NULL COMMENT '出生時間',
   `Email` VARCHAR (50) NOT NULL COMMENT '郵箱帳號允許為空',
   `IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份證號',
   PRIMARY KEY (`StudentNo`),
   UNIQUE KEY `IdentityCard`(`IdentityCard`),
   KEY `Email` (`Email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

-- 插入學生數據 其餘自行添加 這裡只添加了2行
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`)
VALUES
(1000,'123456','張偉',0,2,'13800001234','北京朝陽','1980-1-1','[email protected]','123456198001011234'),
(1001,'123456','趙強',1,3,'13800002222','廣東深圳','1990-1-1','[email protected]','123456199001011233');

-- 創建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
   `SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '課程編號',
   `SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '課程名稱',
   `ClassHour` INT(4) DEFAULT NULL COMMENT '學時',
   `GradeID` INT(4) DEFAULT NULL COMMENT '年級編號',
   PRIMARY KEY (`SubjectNo`)
)ENGINE = INNODB AUTO_INCREMENT = 18 DEFAULT CHARSET = utf8;

-- 插入科目數據
INSERT INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) VALUES
(1,'高等數學-1',110,1),
(2,'高等數學-2',110,2),
(3,'高等數學-3',100,3),
(4,'高等數學-4',130,4),
(5,'C語言-1',110,1),
(6,'C語言-2',110,2),
(7,'C語言-3',100,3),
(8,'C語言-4',130,4),
(9,'Java程式設計-1',110,1),
(10,'Java程式設計-2',110,2),
(11,'Java程式設計-3',100,3),
(12,'Java程式設計-4',130,4),
(13,'資料庫結構-1',110,1),
(14,'資料庫結構-2',110,2),
(15,'資料庫結構-3',100,3),
(16,'資料庫結構-4',130,4),
(17,'C基礎',130,1)

-- =====================  where  ============================

-- 1、查詢資料庫結構-1的所有考試結果（學號，科目編號，成績），降序排列
-- 方式一：使用連接查詢
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE `SubjectName` = '資料庫結構-1'

-- 方式二：使用子查詢（由里及外）
-- 查詢所有資料庫結構-1的學生學號
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM result
WHERE StudentNo = (
    SELECT SubjectNo FROM `subject` 
    WHERE SubjectName = '資料庫結構-1'
)
ORDER BY StudentResult DESC

-- 查詢課程為 高等數學-2 且分數不小於80的同學的學號和姓名
SELECT r.StudentNo,StudentName
FROM student s
INNER JOIN result r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo`=r.`SubjectNo`
WHERE `SubjectName`='高等數學-2' AND `StudentResult`>=80


-- 分數不小於80分的學生的學號和姓名
SELECT DISTINCT r.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.`StudentNo` = s.`StudentNo`
WHERE `StudentResult`>=80

-- 在這個基礎上增加一個科目，高等數學-2    
-- 查詢 高等數學-2 的編號
SELECT DISTINCT r.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.`StudentNo` = s.`StudentNo`
WHERE `StudentResult`>=80 AND `SubjectNo` = (
    SELECT SubjectNo FROM `subject` 
    WHERE `SubjectName` = '高等數學-2'
)

-- 再改造（由里及外）
SELECT `StudentNo`,`StudentName`FROM `student`WHERE `StudentNo` IN (
    SELECT `StudentNo` FROM result WHERE `StudentResult`>=80 AND `SubjectNo` = (
        SELECT SubjectNo FROM `subject` WHERE `SubjectName` = '高等數學-2'
    )
)

-- 練習：查詢C語言-1前5名同學的成績的資訊（學號，姓名，分數）
-- 使用子查詢~

MySQL函數

官網：//dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html

常用函數

-- ===================  常用函數  ============================

-- 數學運算
SELECT ABS(-8)  -- 絕對值
SELECT CEILING(9.4)  -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND()   -- 返回一個0~1之間的隨機數
SELECT SIGN(10)    -- 判斷一個數的符號  0-0  負數返回-1  正數返回1

-- 字元串函數
SELECT CHAR_LENGTH('即使再小的帆也能遠航')  -- 字元串長度
SELECT CONCAT('我','愛','你們')  -- 拼接字元串
SELECT INSERT('我愛編程helloworld',1,2,'超級熱愛')  -- 插入，從某個位置開始替換某個長度
SELECT LOWER('KuangShen')  -- 小寫字母
SELECT UPPER('KuangShen')  -- 大寫字母
SELECT INSTR('kuangshen','h')  -- 返回第一次出現的子串的索引
SELECT REPLACE('狂神說堅持就能成功','堅持','努力')  -- 替換出現的指定字元串
SELECT SUBSTR('狂神說堅持就能成功',4,6)  -- 返回指定的子字元串（源字元串，截取的位置，截取的長度）
SELECT REVERSE('清晨我上馬')  -- 反轉

-- 查詢姓 周的同學，名字 鄒
SELECT REPLACE(studentname,'周','鄒') FROM student
WHERE studentname LIKE '周%'

-- 時間和日期函數（記住）
SELECT CURRENT_DATE()  -- 獲取當前日期
SELECT CURDATE()  -- 獲取當前日期
SELECT NOW()  -- 獲取當前的時間
SELECT LOCALTIME()  -- 本地時間
SELECT SYSDATE()  -- 系統時間

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系統
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

聚合函數（常用）

函數名稱	描述
COUNT()	計數
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
…	…

-- =============================  聚合函數  =========================
-- 都能夠統計表中的數據（想查詢一個表中有多少個記錄，就是用這個count()）
SELECT COUNT(studentname) FROM student;  -- COUNT(指定列)，會忽略null值
SELECT COUNT(*) FROM student;  -- COUNT(*)，不會忽略null值  本質  計算行數
SELECT COUNT(1) FROM result;  -- COUNT(1)，不會忽略null值  本質  計算行數

SELECT SUM(`studentresult`) AS 總和 FROM result
SELECT AVG(`studentresult`) AS 平均分 FROM result
SELECT MAX(`studentresult`) AS 最高分 FROM result
SELECT MIN(`studentresult`) AS 最低分 FROM result

-- 查詢不同課程的平均分，最高分，最低分
-- 核心：（根據不同的課程分組）
SELECT SubjectName, AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`  -- 通過什麼欄位來分組
GROUP BY r.`SubjectNo`
HAVING 平均分>80

資料庫級別的MD5加密（拓展）

什麼是MD5？

主要增強演算法複雜度和不可逆性

MD5不可逆，具體的值的md5是一樣的

MD5破解網站的原理，背後有一個字典，MD5加密後的值加密的值

-- =============================  測試MD5 加密  =================================

CREATE TABLE `testmd5`(
   `id` INT(4) NOT NULL,
   `name` VARCHAR(20) NOT NULL,
   `pwd` VARCHAR(50) NOT NULL,
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 明文密碼
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1
UPDATE testmd5 SET pwd=MD5(pwd)  -- 加密全部的密碼

-- 插入的時候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))

-- 如何校驗：將用戶傳遞進來的密碼，進行md5加密，然後比對加密後的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

事務

什麼是事務

要麼都成功，要麼都失敗

————————————————

SQL執行 A給B轉賬 A 1000 –>200 B 200
SQL執行 B收到A的錢 A 800 –> B 400

————————————————

將一組SQL放在一個批次中去執行~

事務原則：ACID原則原子性，一致性，隔離性，持久性（臟讀，幻讀…）

原子性（Atomicity）

要麼都成功，要麼都失敗

一致性（Consistency）

事務前後的數據完整性要保證一致

持久性（Durability）

事務一旦提交則不可逆，被持久化到資料庫中

隔離性（Isolation）

事務的隔離性是多個用戶並發訪問資料庫時，資料庫為每一個用戶開啟的事務，不能被其他事務的操作數據所干擾，事務之間要相互隔離

隔離所導致的一些問題

臟讀：

指一個事務讀取了另外一個事務未提交的數據

不可重複讀：

在一個事務內讀取表中的某一行數據，多次讀取結果不同。（這個不一定是錯誤，只是某些場合不對）

虛讀（幻讀）：

是指再一個事務內讀取到了別的事務插入的數據，導致前後讀取不一致

執行事務

-- ============================  事務  ===============================

-- mysql 是默認開啟事務自動提交的
SET autocommit = 0  /*  關閉  */
SET autocommit = 1  /*  開啟（默認的）  */

-- 手動處理事務

-- 事務開啟
START TRANSACTION  -- 標記一個事務的開始，從這個之後的sql都在同一個事務內

INSERT xx
INSERT xx

-- 提交：持久化（成功！）
COMMIT
-- 回滾：回到原來的樣子（失敗！）
ROLLBACK

-- 事務結束
SET autcommit = 1  -- 開啟自動提交

-- 了解
SAVEPOINT  保存點名  -- 設置一個事務的保存點
ROLLBACK TO SAVEPOINT  保存點名  -- 回滾到保存點
RELEASE SAVEPOINT  保存點名  -- 撤消保存點

模擬場景

-- 轉賬
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE `account`(
   `id` INT(3) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(30) NOT NULL,
   `money` DECIMAL(9,2) NOT NULL,
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account(`name`,`money`)
VALUES ('A',2000.00),('B',10000.00)

-- 模擬轉賬：事務
SET autocommit=0  -- 關閉自動提交
START TRANSACTION  -- 開啟一個事務

UPDATE account SET money=money-500 WHERE `name`='A'  -- A減500
UPDATE account SET money=money+500 WHERE `name`='B'  -- B加500

COMMIT  -- 提交事務
ROLLBACK  -- 回滾

SET autocommit=1  -- 恢復默認值

索引

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數據的數據結構。

提取句子主幹，就可以得到索引的本質：索引就是數據結構

索引的分類

在一個表中，主鍵索引只能有一個，唯一索引可以有多個

主鍵索引（PRIMARY KEY）
- 唯一的標識，主鍵不可重複，只能有一個列作為主鍵
唯一索引（UNIQUE KEY）
- 避免重複的列出現，唯一索引可以重複，多個列都可以標識為唯一索引
常規索引（KEY/INDEX）
- 默認的，index/key關鍵字來設置
全文索引(FullText)
- 在特定的資料庫引擎下才有，MyISAM
- 快速定位數據

基礎語法

-- 索引的使用
-- 1、在創建表的時候給欄位增加索引
-- 2、創建完畢後，增加索引

-- 顯示所有的索引資訊
USE school

-- 增加一個索引 (索引名)  列名
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentname`(`studentname`)

-- EXPLAIN 分析sql執行的狀況
EXPLAIN SELECT * FROM student  -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('劉')

測試索引

CREATE TABLE `app_user`(
   `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(50) DEFAULT '' COMMENT '用戶昵稱',
   `email` VARCHAR(50) NOT NULL COMMENT '用戶郵箱',
   `phone` VARCHAR(20) DEFAULT '' COMMENT '手機號',
   `gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性別（0:男；1:女）',
   `password` VARCHAR(100) NOT NULL COMMENT '密碼',
   `age` TINYINT(4) DEFAULT '0' COMMENT '年齡',
   `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
   `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用戶表'


-- 插入100萬條數據
DELIMITER $$  -- 寫函數之前必須要寫，標誌
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
   DECLARE num INT DEFAULT 1000000;
   DECLARE i INT DEFAULT 0;
   WHILE i<num DO
       INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
       VALUES(CONCAT('用戶',i),'[email protected]',CONCAT('18',FLOOR(RAND()*999999999)),FLOOR(RAND()+1),UUID(),FLOOR(RAND()*100));
       SET i = i+1;
   END WHILE;
   RETURN i;
END;
SELECT mock_data();

SELECT * FROM app_user WHERE `name` = '用戶9999';  -- 0.362 sec
SELECT * FROM app_user WHERE `name` = '用戶9999';  -- 0.341 sec

EXPLAIN SELECT * FROM app_user WHERE `name` = '用戶9999';  -- rows:993158

SELECT * FROM student

-- id_表名_欄位名
-- CREATE INDEX 索引名 on 表(欄位)
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE `name` = '用戶9999';   -- 0 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用戶9999';  -- rows:1

索引在小數據量的時候，用處不大，但是在大數據的時候，區別十分明顯

索引原則

索引不是越多越好
不要對經常變動的數據加索引
小數據量的表不需要加索引
索引一般加在常用來查詢的欄位上

索引的數據結構

Hash類型的索引

Btree：InnoDB的默認數據類型

閱讀：//blog.codinglabs.org/articles/theory-of-mysql-index.html

許可權管理和備份

用戶管理

SQL yog可視化管理

SQL命令操作

用戶表：mysql.user

本質：對這張表進行增刪改查

-- 創建用戶  CREATE USER 用戶名  IDENTIFIED BY '密碼'
CREATE USER kuangshen IDENTIFIED BY '123456'

-- 修改密碼（修改當前用戶密碼）
SET PASSWORD = PASSWORD('123456')

-- 修改密碼（修改指定用戶密碼）
SET PASSWORD FOR kuangshen = PASSWORD('123456')


-- 重命名  RENAME USER 原來名字 TO 新的名字
RENAME USER kuangshen TO kuangshen2

-- 用戶授權 ALL PRIVILEGES 全部的許可權， 庫.表
-- ALL PRIVILEGES 除了給別人授權，其他都能幹
GRANT ALL PRIVILEGES ON *.* TO kuangshen2

-- 查詢許可權
SHOW GRANTS FOR kuangshen2  -- 查看指定用戶的許可權
SHOW GRANTS FOR root@localhost

-- root用戶的許可權：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

-- 撤銷許可權 REVOKE 哪些許可權，在哪個庫撤銷，給誰撤銷
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2

-- 刪除用戶
DROP USER kuangshen

MySQL備份

為什麼要備份：

保證重要的數據不丟失
數據轉移

MySQL資料庫備份的方式

直接拷貝物理文件
在Sqlyog這種可視化工具中手動導出
- 在想要導出的表或者庫中，右鍵，選擇備份或導出

使用命令行導出 mysqldump 命令行使用

# mysqldump -h主機 -u用戶名 -p密碼 資料庫 表名 > 物理磁碟位置/文件名
C:\Users\wangyudong>mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

# mysqldump -h主機 -u用戶名 -p密碼 資料庫 表1 表2 表3 > 物理磁碟位置/文件名
C:\Users\wangyudong>mysqldump -hlocalhost -uroot -p123456 school student result >D:/b.sql

# mysqldump -h主機 -u用戶名 -p密碼 資料庫> 物理磁碟位置/文件名
C:\Users\wangyudong>mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql

# 導入
# 登陸的情況下，（切換到指定的資料庫）
# source 備份文件
source d:/a.sql

mysql -u用戶名 -p密碼 庫名< 備份文件

假設你要備份資料庫，防止數據丟失。

把資料庫給朋友！sql文件給別人即可！

規範資料庫設計

為什麼需要設計

當資料庫比較複雜的時候，我們就需要設計了

糟糕的資料庫設計：

數據冗餘，浪費空間
資料庫插入和刪除都會麻煩、異常【屏蔽使用物理外鍵】
程式的性能差

良好的資料庫設計：

節省記憶體空間
保證資料庫的完整性
方便我們開發系統

軟體開發中，關於資料庫的設計

分析需求：分析業務和需要處理的資料庫需求
概要設計：設計關係圖E-R圖

設計資料庫的步驟：（個人部落格）

收集資訊，分析需求
- 用戶表（用戶登錄註銷，用戶的個人資訊，寫部落格，創建分類）
- 分類表（文章分類，誰創建的）
- 文章表（文章的資訊）
- 評論表
- 友鏈表（友鏈資訊）
- 自定義表（系統資訊，某個關鍵的字，或者一些主題） key：value
- 說說表（發表心情 .. id…content…create_time）
標識實體（把需求落地到每個欄位）
標識實體之間的關係
- 寫部落格：user –> blog
- 創建分類：user –> category
- 關註：user –> user
- 友鏈：links
- 評論：user-user-blog

三大範式

為什麼需要數據規範化？

資訊重複
更新異常
插入異常
- 無法正常顯示資訊
刪除異常
- 丟失有效的資訊

三大範式（了解）

第一範式（1NF）

原子性：保證每一列不可再分

第二範式（2NF）

前提：滿足第一範式

每張表只描述一件事情

第三範式（3NF）

前提：滿足第一範式和第二範式

第三範式需要確保數據表中的每一列數據都和主鍵直接相關，而不能間接相關

（規範資料庫的設計）

規範性和性能的問題

關聯查詢的表不得超過三張表

考慮商業化的需求和目標，（成本，用戶體驗！）資料庫的性能更加重要
在規範性能的問題的時候，需要適當的考慮一下規範性！
故意給某些表增加一些冗餘的欄位。（從多表查詢中變為單表查詢）
故意增加一些計算列（從大數據量降低為小數據量的查詢：索引）

資料庫驅動與JDBC

驅動：音效卡，顯示卡，資料庫

我們的程式會通過資料庫驅動，和資料庫打交道！

SUN公司為了簡化開發人員的（對資料庫的統一）操作，提供了一個（Java操作資料庫的）規範，俗稱JDBC

這些規範的實現由具體的廠商去做~

對於開發人員來說，我們只需要掌握JDBC介面的操作即可！

java.sql

javax.sql

還需要導入一個資料庫驅動包 mysql-connector-java-5.1.47.jar

Tags: MySQL MySQL資料庫