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`='24736743@qq.com' 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','text123@qq.com','123456198001011234'),
(1001,'123456','趙強',1,3,'13800002222','廣東深圳','1990-1-1','text111@qq.com','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),'24736743@qq.com',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
