1、初識MySQL#
JavaEE：企業級Java開發 Web
前端（頁面：展示，資料！）
後臺（連線點：連線資料庫JDBC，連線前端（控制，控制檢視跳轉，給前端傳遞資料））
資料庫（存資料，Txt，Excle，word）
學好資料庫：
作業系統，資料結構與演算法。
離散數學，數位電路，體系結構，編譯原理。
1.1 什麼是資料庫#
資料庫（DB,DataBase）
概念：資料倉儲，軟體，安裝在作業系統（window，Linux，mac）之上
作用：儲存資料，管理資料
1.2 資料庫分類#
關係型資料庫： （SQL）
- MySQL，Oracle，SqlServer，DB2，SQLlite
- 透過表和表之間，行和列之間的關係進行資料的儲存
非關係型資料庫： （NoSQL） Not Only
- Redis，MongDB
- 非關係型資料庫，以物件儲存，透過物件自身的屬性來決定
DBMS（資料庫管理系統）
- 資料庫的管理軟體，科學有效的管理我們的資料，維護和獲取資料
- MySQL，本質：資料庫管理系統
1.3 MySQL安裝#
1.下載後得到zip壓縮包.
2.解壓到自己想要安裝到的目錄，本人解壓到的是D:\Environment\mysq-5.7.19
3.添環境變數
1.我的電腦>屬性>高階>環境變數
2.選擇PATH,在其後面新增：你的mysql安裝檔案下面的bin資料夾
3.在D:\Environment\mysql--5.7.19下新建my.ini檔案
4.編輯my.ini檔案，注意替換路徑位置[mysqld] basedir=E:\Environment\mysql-8.0.27\ datadir=E:\Environment\mysql-8.0.27\data\ port=3306 skip-grant-tables5.啟動管理員模式下的CMD,並將路徑切換至mysql下的bin目錄，然後輸入mysqld -install(安裝mysql)
6、初始化資料庫檔案
mysqld --initialize-insecure --user=mysql
7.然後啟動mysql,進入mysql管理介面(密碼為空)net start mysql 啟動MySQL mysql -u root -p 連線MySQL8.進入介面後更改root密碼
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';(最後輸入flush privileges;重新整理許可權)
9.修改my.ini檔案,註釋最後一句 #skip-grant-tables
10.重啟mysql即可正常使用net stop mysql net start mysql11.連線上測試出現以下結果就安裝好了
1.4 在安裝MySQL過程中的一些錯誤
sc delete mysql，清空服務
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
當修改密碼出現報錯時可使用下面兩句任意一句話來進行修改
SET PASSWORD = '123456'
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
1.5 SQLyog#
Ultimate版本
註冊名：kuangshen
註冊碼：8d8120df-a5c3-4989-8f47-5afc79c56e7c
1、新建一個資料庫
連線資料庫時的一些錯誤：
錯誤：1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client.
再次安裝MySQL後，使用 Navicat 或者 MySQL yog都會出現下面這個異常。
連線資料庫之後把下面的兩條指令逐條複製貼上到控制檯就可以了 ( *'123456'為你的資料庫登入密碼。* )。
alter user 'root'@'localhost' identified with mysql_native_password by '123456';
flush privileges;
1.6 連線資料庫#
命令列連線
ctrl + c 強行終止
mysql -uroot -p123456 -- 連線資料庫
update mysq1.user set authentication_string=password('123456') where user='root' and Host ='loca1host'; -- 修改使用者密碼
flush privileges; -- 重新整理許可權
--------------------------------------------
--所有的語句都使用;結尾
show databases;-- 檢視所有的資料庫
mysql> use schoo1 -- 切換資料庫use 資料庫名
Database changed
show tables; -- 檢視資料庫中所有的表
describe student; -- 顯示資料庫中所有的表的資訊
create database westos; -- 建立一個資料庫
exit; -- 退出連線
-- 單行註釋(sQL的本來的註釋)
/*(sq1的多行註釋)
he1lo
world
*/
資料庫語言：CRUD 增刪改查
DDL 定義
DML 操作
DQL 查詢
DCL 控制
2、運算元據庫#
運算元據庫 > 運算元據庫中的表 > 運算元據庫表中的資料
mysql關鍵字不區分大小寫
2.1 運算元據庫#
1、建立資料庫
CREATE DATABASE [IF NOT EXISTS] westos;
2、刪除資料庫
DROP DATABASE [IF EXISTS] westos
3、使用資料庫
-- tab 鍵的上面，如果你的表名或者欄位名是一個特殊字元，就需要帶``
USE`school`
4、檢視資料庫
SHOW DATABASE -- 檢視所有的資料庫，記得+"s"
學習思路：
- 對照sqlyog視覺化歷史記錄檢視sql
- 固定的語法或關鍵字必須要強行記住
2.2 資料庫的資料（列）型別#
數值
-
tinyint 1個位元組
-
smallint 2個位元組
-
mediumint 3個位元組
-
int 4個位元組
-
bigint 8個位元組
-
float 4個位元組
-
double 8個位元組（存在精度問題）
-
decimal 字串形式的浮點數（用於金融計算等，不存在精度問題）
字串
- char 固定大小的字串 0~255
- varchar 可變字元 0~65535 常用的變數 String
- tinytext 微型文字 2^8-1
- text 文字串 2^16-1 儲存大文字
時間日期
- date YYYY-MM-DD，日期格式
- time HH : mm : ss 時間格式
- datetime YYYY-MM-DD HH : mm : ss 最常用的時間格式
- timestamp 時間戳，1970.1.1到現在的毫秒數
- year 年份表示
null
- 沒有值，未知
- 注意，不要使用null進行運算，結果為null
2.3 資料庫的欄位屬性（重點）#
unsigned：
- 無符號的整數
- 宣告該列不能宣告為負數
zerofill：
-
0填充的
-
不足的位數用0來填充，int（3），5 , 005
自增：
- 通常理解為自增，自動在上一條記錄的基礎上 +1（預設）
- 通常用來設計唯一的主鍵--index，必須是整數型別
- 可以自定義設計主鍵自增的起始值和步長
非空：
- 假設設定為 not null ，如果不給它賦值，就會報錯
- null，如果不填寫值，預設就是null
預設：
- 設定預設的值
- sex，預設值為男，如果不指定該列的值，則會有預設的值
int（11）也就是int（M），M指示最大顯示寬度，不是說限制你只能插入規定長度的資料，最大有效顯示寬度是255，顯示寬度與儲存大小或型別包含的值的範圍是無關的
如int（1），你可以插1，也可以插10,100
但是varchar（20）就不一樣，輸入的字元必須在20個字元內
2.4 建立資料庫#
-- 目標:建立一個schoo1資料庫
-- 建立學生表(列,欄位)使用sQL建立
-- 學號int 登入密碼varchar(20)姓名,性別varchar(2),出生日期(datatime),家庭住址, emai1
--注意點，使用英文()，表的名稱和欄位儘量使用``括起來
-- 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 -- 顯示錶的結構
2.5 資料表的型別#
-- 關於資料庫引擎
/*
INNODB 預設使用~
MYISAM 早些年使用的
*/
| MYISAM | INNODB | |
|---|---|---|
| 事務支援 | 不支援 | 支援 |
| 資料行鎖定 | 不支援（表鎖定） | 支援 |
| 外來鍵索引 | 不支援 | 支援 |
| 全文索引 | 支援 | 不支援 |
| 表空間的大小 | 較小 | 較大，約為2倍 |
常規使用操作：
- MYISAM 節約空間，速度較快
- INNODB 安全性高，事務的處理，多表多使用者操作
在物理空間存在的位置
所有的資料庫檔案都存在data目錄下，一個資料夾就對應一個資料庫
本質還是檔案的儲存
MySQL引擎在物理檔案上的區別
- INNODB 在資料庫表中只有一個*.frm檔案，以及上級目錄下的ibdata1檔案
- MYISAM對應檔案
- *.frm 表結構的定義檔案
- *.MYD 資料檔案（data）
- *.MYI 索引檔案（index）
設定資料庫表的字符集編碼
CHARSET=utf8
MySQL的預設編碼是Latin1，不支援中文
可以在my.ini中配置預設的編碼
character-set-server = utf8
2.6 修改刪除表#
修改
-- 修改表名: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
最終結論:
change用來欄位重新命名，不能修改欄位型別和約束。
modify不用來欄位重新命名，只能修改欄位型別和約束。
刪除
-- 刪除表（表存在）
DROP TABLE IF EXISTS teacher1
所有的建立和刪除操作儘量加上判斷，以免報錯
注意點：
- 欄位名使用 `` 包裹
- 註釋使用 -- 或者 /**/
- sql關鍵字不敏感，建議小寫
- 符號用英文
3、MySQL資料管理#
3.1、外來鍵#
在建立表的時候，增加約束（複雜）
CREATE TABLE grade(
`gradeid` INT(5) AUTO_INCREMENT NOT NULL COMMENT'年級id',
`gradename` VARCHAR(10) NOT NULL COMMENT'年級姓名',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 學生表的外來鍵要去引用年級表的gradeid
-- 定義外來鍵
-- 給這個外來鍵新增約束（執行引用），reference 引用
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
建立表成功後，新增外來鍵約束
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
-- ALTER TABLE 表 ADD CONSTRAINT 約束名 FOREIGN KEY(作為外來鍵的列) REFERENCES 哪個表（哪個欄位）
以上操作都是物理外來鍵，都是資料庫級別的外來鍵，不建議使用
最佳實踐
- 資料庫就是單純的表，只用來存資料，只有行和列
- 想使用外來鍵時，用程式去實現
3.2、DML語言#
資料庫意義：資料儲存資料管理
DML語言：資料操作語言
- insert
- update
- delete
3.3、新增#
語法：insert into 表名 (欄位1，欄位2，...) values（值1），(值2,...)
-- 插入單行
INSERT INTO `student` (`name`,pwd,sex) VALUES ('趙','xyz','男')
-- 插入多行
INSERT INTO `student`(`name`,pwd,sex) VALUES('錢','asd','男'),('孫','qwe','男')
-- 省略欄位
INSERT INTO `student` VALUES (4,'李','zxc','男','2000-01-01','北京','email')
注意事項：
- 欄位和欄位之間使用英文逗號隔開
- 欄位是可以省略的，但是後面的值必須要一一對應，不能少
- 可以同時插入多條資料，values 後面的值使用英文逗號隔開即可
3.4、修改#
語法：update 表 set 列 = 值 where 條件
UPDATE `student` SET `name` = '李四' WHERE pwd = 'ls'
-- 不指定條件的情況下
UPDATE `student` SET `name` = '張三'
-- 修改多個屬性
UPDATE `student` SET `name` = '李四', birthday = '2002-02-04', address = '上海' WHERE `pwd` = 'ls'
條件：where子句 運算子 id等於某個值，大於某個值，在某個區間內修改
運算子會返回布林值
| 運算子 | 含義 | 範圍 | 結果 |
|---|---|---|---|
| = | 等於 | ||
| <>或 ！= | 不等於 | ||
| > | |||
| < | |||
| <= | |||
| >= | |||
| between...and... | 在某個範圍內 | [2,5] | |
| and | 5>1 and 1>2 | false | |
| or | 5>1 or 1>2 | true |
注意：
- colnum_name 是資料庫的列，儘量帶上``
- 條件，篩選的條件，如果沒有指定，則會修改所有的列
- value，是一個具體的值，也可以是一個變數
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` = '孫' and sex = '女'
3.5、刪除#
delete 命令
語法：delete from 表名 where 條件
-- 刪除資料（會全刪，一般不這麼寫）
DELETE FROM `student`
-- 刪除指定資料
DELETE FROM `student` WHERE id = 4
TRUNCATE 命令
作用：完全清空一個資料庫表，表的結構和索引約束不會變
-- 清空student表
TRUNCATE `student`
delete 和 TRUNCATE 的區別
-
相同點：
-
都能刪除資料，都不會刪除表結構
不同點：
- TRUNCATE 重新設定自增列，計數器會歸零
- TRUNCATE 不會影響事務
delete刪除的問題，重啟資料庫，現象
- INNODB 自增列會從1開始（存在記憶體中，斷電即失）
- MyISAM 繼續從上一個自增量開始（存在檔案中，不會丟失）
4、DQL查詢資料#
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}];
-- 指定查詢的記錄從哪條至哪條
4.1查詢指定欄位#
-- 查詢全部學生
SELECT * FROM `student`
-- 查詢指定欄位
SELECT `studentno`,`studentname` FROM `student`
-- 使用 AS 可以給欄位起別名
SELECT `studentno` AS 學號,`studentname` AS 學生姓名 FROM `student`
-- CONCAT(a,b)
SELECT CONCAT('姓名：',studentname) AS 名字 FROM `student`
去重 distinct
作用：去除SELECT查詢出來的結果中重複的資料，重複的資料只顯示一條
SELECT DISTINCT `studentno` AS '學號' FROM `result`
資料庫的列（表示式）
SELECT VERSION()
-- 學員考試成績 + 1 分檢視
SELECT `studentresult` +1 AS '提分後' FROM `result`
資料庫中的表示式：文字值，列，NULL，函式，計算表示式，系統變數
select 表示式 from 表
4.2、where條件子句#
作用：檢索資料中符合條件的值
邏輯運算子
| 運算子 | 語法 |
|---|---|
| and && | a and b |
| or | |
| Not ！ | not a ！a |
儘量使用英文字母
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=80 AND `studentresult`<=95
-- 模糊查詢
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`BETWEEN 80 AND 95
SELECT `studentno`,`studentresult` FROM `result`
WHERE NOT `studentno` = 1001
模糊查詢：比較運算子
| 運算子 | 語法 | 描述 |
|---|---|---|
| IS NULL | a is null | 如果a為null，結果為真 |
| IS NOT NULL | a is not null | a不為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 '%達%'
-- 查詢1001,1002號學員
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentno` IN (1001,1002)
4.4、聯表查詢#
JOIN 對比
-- join（連線的表） on （判斷的條件） 連線查詢
-- where 等值查詢
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
INNER JOIN `result` r
ON 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
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一個匹配，就返回行 |
| left join | 會從左表中返回所有的值，即使右表中沒有匹配 |
| right join | 會從右表中返回所有的值，即使左表中沒有匹配 |
-- 思考題(查詢了參加考試的同學資訊:學號，學生姓名，科目名，分數)
/*思路
1．分析需求，分析查詢的欄位來自哪些表,student.result. subject（連線查詢)
2．確定使用哪種連線查詢？ 7種
確定交叉點（這兩個表中哪個資料是相同的)
判斷的條件:學生表的中 studentNo.=成績表studentNo */
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
自連線
自己的表和自己的表連線，核心：一張表拆為兩張一樣的表即可
SELECT a.`categoryname` AS '父欄目',b.`categoryname` AS '子欄目'
FROM category a, category b
WHERE a.`categoryid` = b.`pid`
4.5、分頁和排序#
排序
-- 排序：升序 ASC ，降序 DESC
-- ORDER BY 透過哪個欄位排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `subject` sub
ON s.`gradeid` = sub.`gradeid`
INNER JOIN result r
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等數學-2'
ORDER BY `studentresult` DESC
分頁
-- 語法：limit 起始值，頁的大小
-- 第N頁 （n-1）* pagesize，pagesize
-- pagesize ：頁面大小
-- （n-1）* pagesize：起始值
-- n：當前頁
-- 資料總數/頁面大小 = 總頁數
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `subject` sub
ON s.`gradeid` = sub.`gradeid`
INNER JOIN result r
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等數學-2'
ORDER BY `studentresult` DESC
LIMIT 0,5
4.6、子查詢#
-- 查詢科目為高等數學-3且分數> 80分的學生的學號和姓名
SELECT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等數學-3'
AND `studentresult` >80
-- 子查詢
SELECT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` >80 AND `subjectno` = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '高等數學-3'
)
4.7、分組和過濾#
-- 查詢不同課程的平均分，最高分，最低分，平均分大於80
-- 核心：（根據不同的課程分組）
SELECT `subjectname`, AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno`
HAVING AVG(`studentresult`) >80
ANY_VALUE()函式說明
MySQL有any_value(field)函式，它主要的作用就是抑制ONLY_FULL_GROUP_BY值被拒絕。
這樣sql語句不管是在ONLY_FULL_GROUP_BY模式關閉狀態還是在開啟模式都可以正常執行，不被mysql拒絕。
any_value()會選擇被分到同一組的資料裡第一條資料的指定列值作為返回資料。
4.8、select小結#
5、MySQL函式#
https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html
5.1、常用函式#
-- 數學運算
SELECT ABS(-8) -- 絕對值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一個0~1之間的隨機數
SELECT SIGN(10) -- 判斷一個數的符號 0-0 負數返回-1 正數返回1
-- 時間和目期函式(記住)
SELECT CURRENT_DATE()--獲取當前日期
SELECT CURDATE() --獲取當前日期
SELECT NOW ()--獲取當前的時間
SELECT LOCALTIME()--本地時間
SELECT SYSDATE() --系統時間
SELECTYEAR(NOW ())
SELECT MONTH(NOW())SELECT DAY(NOW ())
SELECT HOUR (NOW () )
SELECT MINUTE (NOW () )
SELECT SECOND (NOW ())
--系統
SELECT SYSTEMUSER()
SELECT USER()
SELECT VERSION()
5.2、聚合函式#
| 函式名稱 | 描述 |
|---|---|
| COUNT() | 計數 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| ... |
count(1)：統計所有的記錄（包括null）。
count(*)：統計所有的記錄（包括null）。
count(欄位)：統計該"欄位"不為null的記錄。
count(distinct 欄位)：統計該"欄位"去重且不為null的記錄。
count(1)中的1並不是表示第一個欄位，而是表示一個固定值。其實就可以想成表中有這麼一個欄位，這個欄位就是固定值1，count(1)，
就是計算一共有多少個1。count(*)，執行時會把星號翻譯成欄位的具體名字，效果也是一樣的，不過多了一個翻譯的動作，比固定值的方式效率稍微低一些。
-- 都能夠統計表中的資料(想查詢一個表中有多少個記錄，就使用這個count( ))
SELECT COUNT(`BornDate`) FROM student; -- Count(欄位)，會忽略所有的null值
SELECT COUNT(*） FROM student; -- count(*)，不會忽略 null值，本質計算行數
SELECT COUNT(1)FROM result; -- Count(1)，不會忽略null值,本質計算行數
-- 當要統計的數量比較大時，發現count(*)花費的時間比較多，相對來說count(1)花費的時間比較少。
-- 如果你的資料表沒有主鍵，那麼count(1)比count(*)快 ；如果有主鍵的話，那主鍵（聯合主鍵）作為count的條件也比count(*)要快 。
-- 2、如果你的表只有一個欄位的話那count(*)就是最快的。
-- 3、如果count(1)是聚索引,id,那肯定是count(1)快,但是差的很小的。因為count(*),自動會最佳化指定到那一個欄位。
所以沒必要去count(1)，用count(*),sql會幫你完成最佳化。此時count(1)和count(*)基本沒有區別!
SELECT SUM(`studentResult`) AS 總和 FROM result
SELECT AVG(`studentResult`) AS 平均分 FROM result
SELECT MAX(`studentResult`) AS 最高分 FROM result
SELECT MIN( `studentResult `) AS 最低分 FROM result
5.3、資料庫級別的MD5加密#
MD5不可逆，具體的值的MD5是一樣的
-- 明文密碼
INSERT INTO `grade` VALUES(6,'123456')
-- 插入時加密
INSERT INTO `grade` VALUES(7,MD5('123456'))
-- 插入後加密
UPDATE `grade` SET `gradename` = MD5(gradename) WHERE `gradeid` = 6
UPDATE `grade` SET `gradename` = MD5(gradename) -- 全部加密
WHERE `gradeid` = 6
-- 校驗：將使用者傳入過來的密碼，進行md5加密，比對加密後的值
SELECT * FROM `grade` WHERE `gradeid` = 6 AND `gradename` = MD5('123456')
6、事務#
6.1、什麼是事務#
事務：要麼都成功，要麼都失敗
——————
1、SQL執行 A 給 B 轉賬
2、SQL執行 B 收到 A 的錢
——————
將一組SQL放在一個批次中去執行
事務原則：ACID 原子性 一致性 隔離性 永續性
原子性（Atomicity）：
要麼都成功，要麼都失敗
一致性（Consistency）：
事務前後資料完整性要保持一致
永續性（Durability）:
事務一旦提交則不可逆，被持久化到資料庫中
隔離性（Isolation）：
事務的隔離性是多個使用者併發訪問資料庫時，資料庫為每一個使用者開啟的事務，不能被其它事務的運算元據所干擾，事務之間要相互隔離
隔離所導致的一些問題
髒讀：
指一個事務讀取了另一個事務未提交的資料
不可重複讀：
在另一個事務內讀取表中的某一行資料，多次讀取結果不同（這個不一定是錯誤，只是某些場合不對）
虛讀（幻讀）：
是指在一個事務內讀取到了別的事務插入的資料，導致前後讀取不一致
執行事務
-- MySQL預設開啟事務自動提交
SET autocommit = 0 -- 關閉
SET autocommit = 1 -- 開啟
-- 手動處理事務
SET autocommit = 0
-- 事務開啟
START TRANSACTION -- 標記一個事務的開始，從這個之後的sql都在一個事務內
UPDATE XXX
UPDATE XXX
-- 提交，持久化（成功）
COMMIT
-- 回滾（失敗）
ROLLBACK
-- 事務結束
SET autocommit = 1 -- 開啟自動提交
SAVEPOINT 儲存點名 -- 設定一個事務的儲存點
7、索引#
索引是幫助MySQL高效獲取資料的資料結構
7.1、索引的分類#
- 主鍵索引（PRIMARY KEY）
- 唯一的標識，主鍵不可重複，只能有一個列作為主鍵
- 唯一索引（UNIQUE KEY）
- 避免重複的列出現，可以重複
- 常規索引（KEY/INDEX）
- 預設的，index，key關鍵字來設定
- 全文索引（FullText）
- 在特定的資料庫引擎下才有
-- 顯示全部索引
SHOW INDEX FROM `student`
-- 建立一個索引可以用
-- CREATE INDEX 索引名 on 表（欄位）
-- 新增一個全文索引
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)
-- EXPLAIN 分析sql執行的情況
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('周')
索引在小資料量的時候，用處不大，但是在大資料量的時候，區別十分明顯
7.2、索引原則#
- 索引不是越多越好
- 不要對經常變動的資料加索引
- 小資料量的表不需要加索引
- 索引一般加在常用來查詢的欄位上
索引的資料結構
Hash 型別的索引
Btree：InnoDB的預設資料結構
8、許可權管理和備份#
8.1、使用者管理#
SQL yog視覺化管理
SQL 命令操作
使用者表：mysql.user
本質：讀這張表進行增刪改查
-- 建立使用者
CREATE USER xy IDENTIFIED BY '111111'
DROP USER xy -- 刪除
-- 修改密碼
SET PASSWORD = PASSWORD('123456') -- 當前使用者
SET PASSWORD FOR xy = PASSWORD('123123')
-- 重新命名
RENAME USER xy TO xy1
-- 使用者授權 ALL PRIVILEGES 全部的許可權，庫.表
-- ALL PRIVILEGES 除了能給別的使用者授權，其它都能幹
GRANT ALL PRIVILEGES ON *.* TO xy
-- 撤銷許可權
REVOKE ALL PRIVILEGES ON *.* FROM xy
8.2、MySQL備份#
MySQL資料庫備份的方式
- 直接複製物理檔案
- 在SQLyog 這種視覺化工具中手動匯出
- 使用命令列匯出 mysqldump 命令列使用
- 匯出：mysqldump -h 主機 -u使用者名稱 -p 密碼 資料庫 表名 >物理磁碟位置/檔名
- 匯入：登入的情況下，切換到指定的資料庫，source 備份檔案
#mysq1dump -h主機 -u使用者名稱 -p密碼 資料庫 表名 > 物理磁碟位置/檔名
mysqldump -hlocalhost -uroot -p123456 school student > D:/a.sql
#mysq1dump -h主機 -u使用者名稱 -p密碼 資料庫 表1 表2 表3 > 物理磁碟位置/檔名
mysqldump -hlocalhost -uroot -p123456 school student > D:/b.sql
#mysq1dump -h主機 u使用者名稱 -p密碼 資料庫 > 物理磁碟位置/檔名
mysqldump -hlocalhost -uroot -p123456 school > D:/c.sql
#登入的情況下，匯入
mysql -uroot -p123456
use school
#source備份檔案
source d:/a.sql
#不登入的情況下，匯入
mysq1 -u使用者名稱 -p密碼 庫名<備份檔案
9、規範資料庫設計#
9.1概述#
槽糕的資料庫設計：
- 資料冗餘，浪費空間
- 資料庫的插入和刪除麻煩，異常（遮蔽使用物理外來鍵）
- 程式效能差
良好的資料庫設計：
- 節省記憶體空間
- 保證資料庫的完整性
- 方便開發系統
資料庫的設計：
- 分析需求，分析業務和需要處理的資料庫的需求
- 概要設計：設計關係圖E-R圖
9.2、三大正規化#
第一正規化（1NF）
原子性：保證每一列不可再分
第二正規化（2NF）
前提：滿足第一正規化
每張表只描述一件事情
第三正規化（3NF）
前提：滿足第一正規化和第二正規化
第三正規化需要確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關
規範性 和 效能問題
關聯查詢的表不得超過三張表
- 考慮商業化的需求和目標，（成本，使用者體驗）資料庫的效能更加重要
- 在規範效能的問題的時候，需要適當的考慮一下規範性
- 故意給某些表增加一些冗餘的欄位（從多表查詢變為單表查詢）
- 故意增加一些計算列（從大資料量降低為小資料量的查詢：索引）
目前關聯式資料庫有六種正規化：第一正規化（1NF）、第二正規化（2NF）、第三正規化（3NF）、巴斯-科德正規化（BCNF）、第四正規化(4NF）和第五正規化（5NF，又稱完美正規化）。
而通常我們用的最多的就是第一正規化（1NF）、第二正規化（2NF）、第三正規化（3NF），也就是本文要講的“三大正規化”。
第一正規化（1NF）：要求資料庫表的每一列都是不可分割的原子資料項。
舉例說明：
在上面的表中，“家庭資訊”和“學校資訊”列均不滿足原子性的要求，故不滿足第一正規化，調整如下：
可見，調整後的每一列都是不可再分的，因此滿足第一正規化（1NF）；
第二正規化（2NF）：在1NF的基礎上，非碼屬性必須完全依賴於候選碼（在1NF基礎上消除非主屬性對主碼的部分函式依賴）
第二正規化需要確保資料庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯合主鍵而言）。
舉例說明：
在上圖所示的情況中，同一個訂單中可能包含不同的產品，因此主鍵必須是“訂單號”和“產品號”聯合組成，
但可以發現，產品數量、產品折扣、產品價格與“訂單號”和“產品號”都相關，但是訂單金額和訂單時間僅與“訂單號”相關，與“產品號”無關，
這樣就不滿足第二正規化的要求，調整如下，需分成兩個表：
第三正規化（3NF）：在2NF基礎上，任何非主屬性不依賴於其它非主屬性（在2NF基礎上消除傳遞依賴）
第三正規化需要確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關。
舉例說明：
上表中，所有屬性都完全依賴於學號，所以滿足第二正規化，但是“班主任性別”和“班主任年齡”直接依賴的是“班主任姓名”，
而不是主鍵“學號”，所以需做如下調整：
這樣以來，就滿足了第三正規化的要求。
ps:如果把上表中的班主任姓名改成班主任教工號可能更確切，更符合實際情況，不過只要能理解就行。
10、JDBC（重點）#
java.sql
javax.sql
還需要匯入一個資料庫驅動包 mysql-connector-java-版本.jar
10.1、第一個JDBC程式#
建立測試資料庫
CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `jdbcStudy`;
CREATE TABLE `users`(
`id` INT PRIMARY KEY,
`NAME` VARCHAR(40),
`PASSWORD` VARCHAR(40),
`email` VARCHAR(60),
birthday DATE
);
INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
1、建立專案
2、匯入資料庫驅動
3、編寫測試程式碼
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 載入驅動
Class.forName("com.mysql.jdbc.Driver");
// 使用者資訊和url
// useUnicode = true 支援中文字符集
// character=utf8 設定字符集編碼
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8";
String username = "root";
String password = "123456";
// 連線資料庫物件 Connection 代表資料庫
Connection connection = DriverManager.getConnection(url,username,password);
// 建立執行sql的物件 Statement
Statement statement = connection.createStatement();
// 執行sql，可能存在結果，檢視返回的結果集
String sql = "select * from users";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.println("id = " + resultSet.getObject("id"));
System.out.println("naem = " + resultSet.getObject("NAME"));
System.out.println("password = " + resultSet.getObject("PASSWORD"));
System.out.println("email = " + resultSet.getObject("email"));
System.out.println("birth = " + resultSet.getObject("birthday"));
System.out.println("============================");
}
// 釋放連線
resultSet.close();
statement.close();
connection.close();
}
步驟總結：
1、載入驅動
2、連線資料庫DriverManager
3、獲得執行sql的物件 Statement
4、獲得返回的結果集
5、釋放連線
DriverManager
Class.forName("com.mysql.jdbc.Driver"); //固定寫法，載入驅動
Connection connection = DriverManager.getConnection(url,username,password);
// Connection 代表資料庫
// 可以設定自動提交，事務提交，事務回滾
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8";
// 協議://主機地址:埠號/資料庫名?
//oralce --1521
//jdbc:oracle:thin:@localhost:1521:sid
Statement
//Statement 執行sql的物件
statement.executeQuery();//查詢操作返回 ResultSet
statement.execute(;//執行任何sQL
statement.executeupdate();//更新、插入、刪除。都是用這個，返回一個受影響的行數
ResultSet
Resultset查詢的結果集，封裝了所有的查詢結果
可以獲得指定的資料型別
resultSet.getObject() //在不知道列的具體型別時使用
// 如果知道就使用指定的型別
遍歷，指標
resultSet.beforeFirst();//移動到最前面
resultSet.afterLast();//移動到最後面
resultSet.next();//移動到下一個資料
resultSet.previous();//移動到前一行
resultSet.absolute(row);//移動到指定行
10.2、statement物件#
jdbc中的statement物件用於向資料庫傳送SQL語句，想完成對資料庫的增刪改查，只需要透過這個物件向資料庫傳送增刪改查語句即可
Statement物件的executeUpdate方法，用於向資料庫傳送增、刪、改的sql語句，executeUpdate執行完後，將會返回一個整數（即增刪改語句導致了資料庫幾行資料發生了變化)。
Statement.executeQuery方法用於向資料庫傳送查詢語句，executeQuery方法返回代表查詢結果的ResultSet物件。
編寫工具類（利用反射機制），建立配置檔案（db.properties裡面存放Driver和URL等資訊）
工具類：
package lesson01;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class jdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static{
try {
InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//獲取連線
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//釋放連線
public static void release(Connection coon , Statement st, ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(coon!=null){
try {
coon.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
程式碼：
package lesson01;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ST_insert {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = jdbcUtils.getConnection();
st = conn.createStatement();
String sql = "insert into users(id,NAME,PASSWORD,email,birthday) values(4,'xy','4654656','2568798778@qq.com','2020-01-01')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
SQL隱碼攻擊問題
sql存在漏洞，會被攻擊導致資料洩露，SQL會被拼接 or
//登入業務
//當使用者輸入的使用者名稱和密碼為下述情況時，會返回資料庫中所有資料，導致資料洩露
Login("'or'1=1","'or'1=1")
10.3、PreparedStatement物件#
PreparedStatement可以防止SQL隱碼攻擊，效率更高
防注入的本質：把傳遞進來的引數當做字元，假設其中存在跳脫字元，就直接忽略，比如說 ‘ 會被直接轉義
package lesson01;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class PST_insert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = jdbcUtils.getConnection();
//區別：使用佔位符代替引數
String sql = "insert into users(id,NAME,PASSWORD,email,birthday) values(?,?,?,?,?)";
st = conn.prepareStatement(sql); //預編譯sql，先寫sql
//手動給引數賦值
st.setInt(1,4);
st.setString(2,"xy");
st.setString(3,"536365");
st.setString(4,"467981234@qq.com");
//注意點： sql.Date 資料庫
// java.util.Date new Date().getTime()獲得時間戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
//執行
int i = st.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
jdbcUtils.release(conn,st,null);
}
}
}
10.4、事務#
程式碼實現
1、開啟事務：conn.setAutoCommit(flase);
2、一組業務執行完畢，提交事務
3、可以在catch語句中顯示的定義回滾語句，但預設失敗就會回滾
10.5、資料連線池#
資料庫連線---執行完畢---釋放
連線 -- 釋放 十分浪費資源
池化技術：準備一些預先的資源，過來就連線預先準備好的
最小連線數（一般就是常用連線數）
最大連線數（業務承載能力上限）
等待超時（超出業務承載能力上限後，排隊等待的最長時間）
編寫連線池，實現一個介面 DateSource
開源資料來源實現
DBCP
C3P0
Druid：阿里巴巴
使用了這些資料庫連線池後，我們在專案開發中就不需要編寫連線資料庫的程式碼了
DBCP
需要用到的jar包
commons-dbcp-1.4、 commons-pool-1.6
C3P0
需要用到的jar包
c3p0-0.9.5.5、 mchange-common-java-0.2.19
結論
無論使用什麼資料來源，本質還是一樣的，DataSource介面不會變，方法就不會變