MySQL資料庫學習

2020 年 6 月 27 日
筆記

1.初識MySQL

1.1資料庫的定義

資料庫(DataBase,簡稱DB)，是一個按數據結構來存儲和管理數據的電腦軟體系統。每個資料庫都有一個或多個不同的 API 用於創建，訪問，管理，搜索和複製所保存的數據。

概念 : 長期存放在電腦內,有組織,可共享的大量數據的集合,是一個數據”倉庫”

作用 : 保存,並能安全管理數據(如:增刪改查等),減少冗餘…

1.2MySQL簡介

MySQL 是最流行的關係型資料庫管理系統，在 WEB 應用方面 MySQL 是最好的 RDBMS(Relational Database Management System：關係資料庫管理系統)應用軟體之一。

概念 : 是現在流行的開源的,免費的 關係型資料庫

歷史 : 由瑞典MySQL AB 公司開發，目前屬於 Oracle 旗下產品。

特點 :

免費 , 開源資料庫
小巧 , 功能齊全
使用便捷
可運行於Windows或Linux作業系統
可適用於中小型甚至大型網站應用

官網 : //www.mysql.com/

RDBMS 即關係資料庫管理系統(Relational Database Management System)的特點：

1.數據以表格的形式出現
2.每行為各種記錄名稱
3.每列為記錄名稱所對應的數據域
4.許多的行和列組成一張表單
5.若干的表單組成database

1.3關係型資料庫

(SQL)

MySQL , Oracle , SQL Server , SQLite , DB2 , …
關係型資料庫通過外鍵關聯來建立表與表之間的關係

1.4非關係型資料庫

(NOSQL)

Redis , MongoDB , …
非關係型資料庫通常指數據以對象的形式存儲在資料庫中，而對象之間的關係通過每個對象自身的屬性來決定

1.5連接資料庫

打開MySQL命令窗口

在DOS命令行窗口
在窗口中進入

安裝目錄\mysql\bin
- 可設置環境變數

連接資料庫語句 : mysql -h 伺服器主機地址 -u 用戶名 -p 用戶密碼

注意 : -p後面不能加空格,否則會被當做密碼的內容,導致登錄失敗 !

update user set password=password('123456')where user='root'; 修改密碼
flush privileges;  刷新資料庫
show databases; 顯示所有資料庫
use dbname； 打開某個資料庫
show tables; 顯示資料庫mysql中所有的表
describe user; 顯示錶mysql資料庫中user表的列資訊
create database name; 創建資料庫
use databasename; 選擇資料庫
exit; 退出Mysql
? 命令關鍵詞 : 尋求幫助
# 表示注釋

（1）結構化查詢語句SQL

結構化查詢語句分類 :

名稱	解釋	命令
DDL 數據定義語言	定義和管理數據對象，如資料庫，數據表等	CREATE、DROP ALTER
DML 數據操作語言	用於操作對象中所包含的數據	INSERT UPDATE DELETE
DQL 數據查詢語言	用於查詢數據表結構	SELECT
DCL 數據控制語言	用來管理資料庫的語言，包括管理許可權及數據更改	GRANT COMMIT ROLLBACK

（2）命令行操作資料庫

創建資料庫 : create database [if not exists] 資料庫名;

刪除資料庫 : drop database [if exists] 資料庫名;

查看資料庫 : show databases;

使用資料庫 : use 資料庫名;

CREATE DATABASE java;
DROP DATABASE IF EXISTS java;
CREATE DATABASE IF NOT EXISTS class01;
DROP DATABASE IF EXISTS	class01;
USE `class01` ;
SHOW DATABASES -- 查看所有的數據表

1.6創建數據表

屬於DDL的一種

語法 :

create table [if not exists] `表名`(
    '欄位名1' 列類型 [屬性][索引][注釋],
    '欄位名2' 列類型 [屬性][索引][注釋],
    #...
    '欄位名n' 列類型 [屬性][索引][注釋]
)[表類型][表字符集][注釋];

說明 : 反引號用於區別MySQL保留字與普通字元而引入的 (鍵盤esc下面的鍵).

（1）數據值和列類型

列類型 : 規定資料庫中該列存放的數據類型

數值類型
字元串類型
日期和時間型
NULL值

理解為”沒有值”或”未知值”
不要用NULL進行算術運算 , 結果仍為NULL

（2）數據欄位屬性

UnSigned :

無符號的整數
聲明該數據列不允許負數 .

ZEROFILL :

0填充的
不足位數的用0來填充 , 如int(3),5則為005

Auto_InCrement :

自動增長的 , 每添加一條數據 , 自動在上一個記錄數上加 1(默認)
通常用於設置主鍵 index,必須為整數類型。
可定義起始值和步長
- 當前表設置步長(AUTO_INCREMENT=100) : 隻影響當前表
- SET @@auto_increment_increment=5 ; 影響所有使用自增的表(全局)

NULL 和 NOT NULL :

默認為NULL , 即沒有插入該列的數值
如果設置為NOT NULL , 則該列必須有值

DEFAULT :

默認的
用於設置默認值
- 例如,性別欄位,默認為”男” , 否則為 “女” ; 若無指定該列的值 , 則默認值為”男”的值

# 創建學生表(列,欄位)
# 學號int 登錄密碼varchar(20) 姓名,性別varchar(2),出生日期(datatime),家庭住址,email
# 創建表之前 , 一定要先選擇資料庫

CREATE TABLE IF NOT EXISTS `student02` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '學號',
  `name` VARCHAR(30) NOT NULL DEFAULT '同學' 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

# 查看資料庫的定義
SHOW CREATE DATABASE school;
# 查看數據表的定義
SHOW CREATE TABLE student;
# 顯示錶結構
DESC student;
# 設置嚴格檢查模式(不能容錯了)
SET sql_mode='STRICT_TRANS_TABLES';

MySQL的數據表的類型 : MyISAM , InnoDB , HEAP , BOB , CSV等…

常見的 MyISAM 與 InnoDB 類型

名稱	MyISAM	InnoDB
事務處理	不支援	支援
數據行鎖定	不支援	支援
外鍵約束	不支援	支援
全文索引	支援	不支援
表空間大小	較小	較大，約2倍

適用MyISAM : 節約空間及相應速度
適用InnoDB : 安全性 , 事務處理及多用戶操作數據表

1.7數據表的存儲位置

MySQL數據表以文件方式存放在磁碟中
- 包括表文件 , 數據文件 , 以及資料庫的選項文件
- 位置 : Mysql安裝目錄\data\下存放數據表 . 目錄名對應資料庫名 , 該目錄下文件名對應數據表 .
注意 :
- InnoDB類型數據表只有一個 *.frm文件 , 以及上一級目錄的.ibd文件，
- MyISAM類型數據表對應三個文件 :
  - * . frm — 表結構定義文件
  - * . MYD — 數據文件(data)
  - * . MYI — 索引文件(index)

1.8修改資料庫

(1)修改表( ALTER TABLE )

修改表名 :
- ALTER TABLE 舊錶名 RENAME AS 新表名
添加欄位 :
- ALTER TABLE 表名 ADD 欄位名列屬性[屬性]
修改欄位 :
- ALTER TABLE 表名 MODIFY 欄位名列類型[屬性]
- ALTER TABLE 表名 CHANGE 舊欄位名新欄位名列屬性[屬性]
刪除欄位 :
- ALTER TABLE 表名 DROP 欄位名

-- 修改表名 `ALTER TABLE 舊錶名 RENAME AS 新表名`

ALTER TABLE student03 RENAME AS 03student;

-- 增加欄位 ALTER TABLE 表名 ADD欄位名 列屬性[屬性]

ALTER TABLE 03student ADD age INT(2);

-- 修改表的欄位01 ALTER TABLE 表名 MODIFY 欄位名 列類型[屬性]
ALTER TABLE 03student MODIFY age VARCHAR(4);-- 修改約束

-- 修改表的欄位02 ALTER TABLE 表名 CHANGE 舊欄位名 新欄位名 列屬性[屬性]
ALTER TABLE 03student CHANGE age setage INT(2); -- 欄位重命名

-- 刪除欄位 ALTER TABLE 表名 DROP 欄位名
ALTER TABLE 03student DROP setage;

(2)刪除數據表

語法 :

DROP TABLE [IF EXISTS] 表名
- IF EXISTS為可選 , 判斷是否存在該數據表
- 如刪除不存在的數據表會拋出錯誤

小結：

1. 可用反引號（`）為標識符（庫名、表名、欄位名、索引、別名）包裹，以避免與關鍵字重名！中文也可以作為標識符！
2. 每個庫目錄存在一個保存當前資料庫的選項文件db.opt。
3. 注釋：
    單行注釋 # 注釋內容
    多行注釋 /* 注釋內容 */
    單行注釋 -- 注釋內容        (標準SQL注釋風格，要求雙破折號後加一空格符（空格、TAB、換行等）)
4. 模式通配符：
    _    任意單個字元
    %    任意多個字元，甚至包括零字元
    單引號需要進行轉義 \'
5. CMD命令行內的語句結束符可以為 ";", "\G", "\g"，僅影響顯示結果。其他地方還是用分號結束。delimiter 可修改當前對話的語句結束符。
6. SQL對大小寫不敏感
7. 清除已有語句：\c

2.MySQL數據管理

2.1 外鍵管理（物理外鍵）

外鍵概念

　　如果公共關鍵字在一個關係中是主關鍵字，那麼這個公共關鍵字被稱為另一個關係的外鍵。由此可見，外鍵表示了兩個關係之間的相關聯繫。以另一個關係的外鍵作主關鍵字的表被稱為主表，具有此外鍵的表被稱為主表的從表。(引用別人的表：從表 ;被引用的表：主表)

　　在實際操作中，將一個表的值放入第二個表來表示關聯，所使用的值是第一個表的主鍵值(在必要時可包括複合主鍵值)。此時，第二個表中保存這些值的屬性稱為外鍵(foreign key)。

外鍵作用

　　保持數據一致性，完整性，主要目的是控制存儲在外鍵表中的數據,約束。使兩張表形成關聯，外鍵只能引用外表中的列的值或使用空值。

創建外鍵

方式一：在創建表的時候添加外鍵

-- 創建年級表
CREATE TABLE `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
-- 給這個外鍵添加約束（執行引用）

-- 創建學生表
CREATE TABLE `person` (
  `studentid` INT(4) NOT NULL COMMENT '學號', -- comment 注釋，備註
  `studentname` VARCHAR(20) NOT NULL DEFAULT '學生姓名' COMMENT '姓名',
  `sex` TINYINT(1) DEFAULT '1' COMMENT '性別', -- 微整型
  `gradeid` INT(10) NOT NULL COMMENT '學生的年級',
  PRIMARY KEY (`studentid`), -- 設置該表主鍵
  KEY `FK_gradeid` (`gradeid`), -- 添加外鍵，`FK_gradeid`（FOREIGN KEY） 連接兩個表之間的關係
  -- constraint 添加約束條件
  -- FOREIGN 外來的 ， FOREIGN KEY（欄位）
  -- REFERENCES 引用，REFERENCES `表名`（欄位名）
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) 
) ENGINE=INNODB DEFAULT CHARSET=utf8

方式二：創建表時沒有外鍵，通過修改表添加外鍵

ALTER TABLE `person`
ADD CONSTRAINT `FK_gradeid`FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`) 
-- 程式碼過長可用回車換行，單行執行可不在句尾添加;

ALTER TABLE 表名 
ADD CONSTRAINT 約束名 ADD CONSTRAINT（作為外鍵的列）REFERENCES 被引用的表名（欄位名）;

備註：被引用的表不能直接刪除，要先刪除引用它的表才能正確刪除

引用別人的表：從表

被引用的表：主表

# 刪除外鍵
ALTER TABLE person DROP FOREIGN KEY FK_gradeid;
# 發現執行完上面的,索引還在,所以還要刪除索引
# 注:這個索引是建立外鍵的時候默認生成的
ALTER TABLE person DROP INDEX FK_gradeid;

以上兩種方式都是物理外鍵，資料庫級別的外鍵，一般不建議使用，避免資料庫過多造成困擾

最佳實現：

 資料庫就是單純的表，只用來存數據，只有行（數據）和列（欄位）

我們想使用多張表的數據，想使用外鍵（通過程式去實現）

2.2 資料庫數據管理

資料庫意義

數據存儲
數據管理

管理資料庫數據方法

通過SQLyog等管理工具管理資料庫數據
通過DML語句管理資料庫數據

DML語言

DML(數據操作語言)
用於操作資料庫對象中所包含的數據
包括 :
- INSERT (添加數據語句)
- UPDATE (更新數據語句)
- DELETE (刪除數據語句)

添加數據

INSERT命令

語法 :

  INSERT INTO 表名[(欄位1,欄位2,欄位3,...)] VALUES(（'值1'）,（'值2'）,（'值3'）,...)
/*
注意：
- 欄位或值之間用英文逗號隔開.
- '欄位1,欄位2...'該部分可省略 , 但添加的值務必與表結構,數據列,順序相對應,且數量一致.
- 可同時插入多條數據 , values 後用英文逗號隔開.
*/

創建一個表：

-- DML(數據操作語言) 練習
-- 創建課程表
CREATE TABLE `subject` (
  `subjectid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '課程式號',
  `subjectname` VARCHAR(50) NOT NULL COMMENT '課程名稱',
  PRIMARY KEY (`subjectid`) -- 設置主鍵
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入一個欄位
INSERT INTO `subject`(subjectname) VALUES ('java');

-- 一個欄位插入多個值
INSERT INTO `subject`(subjectname) VALUES ('c語言'),('HTML5');

-- 為多個列同時插入欄位
INSERT INTO `subject`(`subjectname`,`subjectrank`,`subjectstar`) VALUES('java','hard','8')

-- 為多個列插入多個欄位
INSERT INTO `subject`(`subjectname`,`subjectrank`,`subjectstar`) 
VALUES('java','hard','8'),('C++','just','6'),('HTML5','easy','4')

錯誤收藏欄：

-- 由於主鍵自增，我們可以省略（如果不寫表的欄位，它就會一一匹配）
INSERT INTO `subject` VALUES ('c++');

1 queries executed, 0 success, 1 errors, 0 warnings

查詢：INSERT INTO `subject` VALUES ('c++')

錯誤程式碼： 1136
Column count doesn't match value count at row 1

執行耗時   : 0 sec
傳送時間   : 0 sec
總耗時      : 0 sec

修改數據

UPDATE命令

語法 :

  UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

注意 :

column_name 為要更改的數據列
value 為修改後的數據 , 可以為變數 , 具體指 , 表達式或者嵌套的SELECT結果
condition 為篩選條件 , 如不指定則修改該表的所有列數據

update 修改誰 (條件) set 原來的值=新值

-- 修改`subjectname`
UPDATE `subject` SET `subjectname`='計科課表'WHERE `subjectid`=1;

-- 不指定條件下會改變所有表
UPDATE `subject` SET `subjectname`='新思路'

-- 修改多個屬性，用逗號,隔開
UPDATE `subject` SET `subjectname`='java',`subjectrank`='hard',`subjectstar`='8'WHERE `subjectid`=2;

-- 通過多個條件定位數據
UPDATE `subject` SET `subjectname`='java'WHERE `subjectrank`='hard'AND `subjectstar`='8';

修改數據：

-- DML(數據操作語言) 練習
-- 創建課程表
CREATE TABLE `subject` (
  `subjectid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '課程式號',
  `subjectname` VARCHAR(50) NOT NULL COMMENT '課程名稱',
  `subjectrank`VARCHAR(50) NOT NULL COMMENT '課程難度',
  `subjectstar`INT(10) NOT NULL COMMENT '課程評分',
  PRIMARY KEY (`subjectid`) -- 設置主鍵
) ENGINE=INNODB DEFAULT CHARSET=utf8
UPDATE `subject` SET `subjectname`='計科課表'WHERE `subjectid`=1;
-- 不指定條件下會改變所有表
UPDATE `subject` SET `subjectname`='新思路'
-- 修改多個屬性，用逗號,隔開
UPDATE `subject` SET `subjectname`='java',`subjectrank`='hard',`subjectstar`='8'WHERE `subjectid`=2;
-- 通過多個條件定位數據
UPDATE `subject` SET `subjectname`='java'WHERE `subjectrank`='hard'AND `subjectstar`='8';

刪除數據

DELETE命令

語法
```
DELETE FROM 表名 [WHERE condition];
```

注意 : condition為篩選條件 , 如不指定則刪除該表的所有列數據

TRUNCATE命令

用於完全清空表數據 , 但表結構 , 索引 , 約束等不變 ;
語法 : TRUNCATE [TABLE] table_name;

注意 : 區別於DELETE命令

相同 : 都能刪除數據 , 不刪除表結構 , 但TRUNCATE速度更快
不同 :
- 使用TRUNCATE TABLE 重新設置AUTO_INCREMENT計數器
- 使用TRUNCATE TABLE不會對事務有影響

-- 1.刪除全部數據
DELETE FROM `subject` --不會影響自增
-- 2.刪除指定數據
DELETE FROM `subject`WHERE `subjectid`=1;

-- TRUNCATE
截斷表;截除;刪除記錄;語句
-- 清空 `subject` 表
TRUNCATE `subject` --自增會歸零

3.Mysql：使用DQL查詢數據

3.1DQL語言

DQL( Data Query Language 數據查詢語言 )

查詢資料庫數據 , 如SELECT語句
簡單的單表查詢或多表的複雜查詢和嵌套查詢
是資料庫語言中最核心,最重要的語句
使用頻率最高的語句

-- 創建學生資訊表
DROP TABLE IF EXISTS `student_table`;
CREATE TABLE `student_table` (
`id`
BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
`number`
VARCHAR(20) DEFAULT NULL COMMENT '學生編號',
`name`
VARCHAR(20) DEFAULT NULL COMMENT '學生姓名',
`birthday` VARCHAR(20) DEFAULT NULL COMMENT '出生年月日',
`sex`
INT(1) DEFAULT NULL COMMENT '性別(0 男 1 女 2 未知)',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='學生資訊表';

INSERT INTO student_table(number,NAME,birthday,sex) VALUES('B190901','小王
','1995-01-01',0);
INSERT INTO student_table(number,NAME,birthday,sex) VALUES('B190902','小張
','1994-03-06',1);
INSERT INTO student_table(number,NAME,birthday,sex) VALUES('B190903','小李
','1995-02-07',1);
INSERT INTO student_table(number,NAME,birthday,sex) VALUES('B190904','小紅
','1997-08-22',0);

-- 創建課程資訊表
DROP TABLE IF EXISTS `course_table`;
CREATE TABLE `course_table` (
`id` BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
`number` VARCHAR(20) DEFAULT NULL COMMENT '課程編號',
`name` VARCHAR(20) DEFAULT NULL COMMENT '課程名稱',
`teacherNumber` VARCHAR(20) DEFAULT NULL COMMENT '教師編號',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='課程資訊表';

INSERT INTO course_table(number,NAME,teacherNumber) VALUES('100','英語','T001');
INSERT INTO course_table(number,NAME,teacherNumber) VALUES('101','高數','T002');
INSERT INTO course_table(number,NAME,teacherNumber) VALUES('102','思修','T003');

-- 創建成績資訊表
DROP TABLE IF EXISTS `score_table`;
CREATE TABLE `score_table` (
`id`
BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
`st_number` VARCHAR(20) DEFAULT NULL COMMENT '學生編號',
`co_number` VARCHAR(20) DEFAULT NULL COMMENT '課程編號',
`score`
INT(20) DEFAULT NULL COMMENT '課程分數',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='成績資訊表';
INSERT INTO score_table(st_number,co_number,score) VALUES('B190901','100',80);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190901','101',90);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190902','100',95);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190902','101',60);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190902','102',70);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190903','100',90);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190903','101',79);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190903','102',85);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190904','100',60);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190904','101',70);

-- 創建教師資訊表
DROP TABLE IF EXISTS `teacher_table`;
CREATE TABLE `teacher_table` (
`id` BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
`number` VARCHAR(20) DEFAULT NULL COMMENT '教師編號',
`name` VARCHAR(20) DEFAULT NULL COMMENT '教師姓名',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='教師資訊表';

INSERT INTO teacher_table(number,NAME) VALUES('T001','張三');
INSERT INTO teacher_table(number,NAME) VALUES('T002','張四');
INSERT INTO teacher_table(number,NAME) VALUES('T003','王五');

指定查詢欄位

查詢表結果時 , 可指定查詢結果的數據列

查詢表中所有的數據列結果 , 採用 ” * “符號;
- 如 : SELECT * FROM student;
- 但是效率低 , 不推薦。

-- 指定查詢欄位
-- 查詢全部的學生 
SELECT * FROM `student_table`

-- 查詢指定欄位
SELECT `number`,`name`,`birthday` FROM `student_table`

AS子句作為別名

AS子句作用 :

可給數據列取一個新別名
可給表去一個新別名
可把經計算或總結的結果用另一個新名稱來代替

-- 給結果起別名 AS 可以給欄位起別名，也可以給表起別名
SELECT `number` AS '學生學號' ,`name` AS 學生姓名,`birthday`AS 出生日期 FROM `student_table`AS 學生資訊表

經我驗證，發現 AS 別名可以加單引號，也可以不加.

-- 函數 Concat(a,b)
-- 單個拼接
SELECT CONCAT('姓名: ',`name`)AS 學生姓名 FROM `student_table`
-- 多個拼接
SELECT CONCAT('姓名: ',`name`)AS 學生姓名,CONCAT('學號: ',`number`) AS 學生資訊 FROM `student_table`

SQL練習：

查詢出每門課都大於等於 80 分的學生學號和姓名？

-- 查詢出每門課都大於等於 80 分的學生學號和姓名
SELECT a.number,a.name
FROM student_table a
LEFT JOIN score_table AS b ON b.st_number=a.number
GROUP BY b.st_number HAVING MIN(b.score)>=80;

查詢課程英語的成績比課程高數的成績高的所有學生的學號？

-- 查詢課程英語的成績比課程高數的成績高的所有學生的學號
SELECT a.st_number FROM(SELECT st_number,score FROM score_table WHERE co_number='100') a,
(SELECT st_number,score FROM score_table WHERE co_number='101') b
WHERE a.score>b.score AND a.st_number=b.st_number;

查詢平均成績大於 60 分的同學的學號和平均成績？

-- 查詢平均成績大於 60 分的同學的學號和平均成績
SELECT a.st_number AS '學號',AVG(a.score) AS '平均成績'
FROM (SELECT st_number,score FROM score_table) a
GROUP BY a.st_number HAVING AVG(a.score)>60;

查詢所有同學的學號、姓名、選課數、總成績？

-- 查詢所有同學的學號、姓名、選課數、總成績
SELECT a.st_number AS 學號, b.name AS 姓名,
COUNT(a.co_number) AS 選課數, SUM(a.score) AS 總成績
FROM score_table a,student_table b
WHERE a.st_number = b.number
GROUP BY a.st_number, b.name

查詢姓「張」的老師的個數？

-- 查詢姓「張」的老師的個數
SELECT COUNT(*) FROM teacher_table WHERE NAME LIKE '張%';

查詢同時學過英語課程和思修課程的同學的學號、姓名？

-- 查詢同時學過英語課程和思修課程的同學的學號、姓名
SELECT number, NAME FROM student_table
WHERE number IN (SELECT st_number FROM score_table sc WHERE sc.co_number =100)
AND number IN (SELECT st_number FROM score_table sc WHERE sc.co_number =102);

查詢學過」王五「老師所教課程的所有同學的學號、姓名？

-- 查詢學過」王五「老師所教課程的所有同學的學號、姓名
SELECT a.number,a.name FROM student_table a,score_table b
WHERE a.number = b.st_number AND b.co_number
IN(SELECT c.number FROM course_table c,teacher_table d WHERE c.teacherNumber = d.number AND
d.name ='王五');

查詢各科成績最高分和最低分？

-- 查詢各科成績最高分和最低分
SELECT ct.name AS 課程, MAX(st.score) AS 最高分,MIN(st.score) 最低分
FROM score_table st
LEFT JOIN course_table ct ON ct.number=st.co_number
GROUP BY st.co_number;

去重distinct

--distinct:去除SELECT查詢出來的結果中重複的數據，重複的數據只顯示一條
-- 查詢全部的學生 
SELECT * FROM `student_table`
-- 查詢全部的成績
SELECT *FROM `score_table`
-- 查詢有哪些同學參加了考試
SELECT `st_number` FROM `score_table`
-- 去掉重複項
SELECT DISTINCT `st_number` FROM `score_table`

資料庫中的列（表達式）

-- 資料庫的列（表達式）
SELECT VERSION() -- 查詢系統版本（函數） -- 5.7.19
SELECT 100*3-1 AS 計算結果 -- 用來計算（表達式） -- 299
SELECT @@auto_increment_increment -- 查詢自增的步長（變數）-- 1
-- 學生考試成績+1分查看
SELECT `st_number`,`score` +1 AS '學生提分後成績' FROM `score_table`
-- 資料庫中的表達式：文本值，列，null，函數，計算表達式，系統變數...
-- select 表達式 from 表

3.2where條件語句

用於檢索數據表中 符合條件 的記錄
搜索條件可由一個或多個邏輯表達式組成 , 結果一般為真或假.
搜索條件的組成
- 邏輯操作符
- 比較操作符
- 用於模糊查詢 .
注意 :
- 數值數據類型的記錄之間才能進行算術運算;
- 相同數據類型的數據之間才能進行比較;

  
  -- 查詢學生成績
  SELECT `st_number`,`score`FROM `score_table`
  -- 查詢成績在60-80的學生
  SELECT `st_number`,`score`FROM `score_table`
  WHERE `score`>=60 AND `score`<=80
  -- 等價
  SELECT `st_number`,`score`FROM `score_table`
  WHERE `score`>=60 && `score`<=80
  -- 模糊查詢（區間）
  SELECT `st_number`,`score`FROM `score_table`
  WHERE `score` BETWEEN 75 AND 100
  
  -- 除了`st_number`= B190903 號學生之外的同學的成績
  SELECT `st_number`,`score`FROM `score_table`
  WHERE NOT `st_number`= 'B190903'

模糊查詢

-- 模糊查詢 

-- 查詢姓新的同學
SELECT `number`,`name`FROM`student_table`
WHERE `name` LIKE '新%'
-- like結合使用的通配符：%（代表0~任意個字元） _(代表一個字元)
-- 查詢姓新的同學後面只有一個字的
SELECT `number`,`name`FROM`student_table`
WHERE `name` LIKE '新_'
-- 查詢姓新的同學後面只有兩個字的
SELECT `number`,`name`FROM`student_table`
WHERE `name` LIKE '新__'
-- 查詢名字中含有新字的
SELECT `number`,`name`FROM`student_table`
WHERE `name` LIKE '%新%'
-- 查詢學號為 B190903，B190904，B190905的學生姓名
SELECT `number`,`name`FROM`student_table`
WHERE `number`IN('B190903','B190904','B190905')
-- 查詢未知性別的同學
SELECT `number`,`name`,`sex`FROM`student_table`
WHERE `sex`IN('2')
-- 查詢生日日期沒有填寫的同學
SELECT `name`FROM `student_table`
WHERE `birthday` IS NULL

-- 查詢生日日期沒有填寫的同學
SELECT `name`FROM `student_table`
WHERE `birthday`=''OR`birthday`IS NULL

-- 查詢生日日期已經填寫的同學
SELECT `name`FROM `student_table`
WHERE `birthday` IS NOT NULL

連接查詢

join 對比

聯表查詢

連接查詢
    如需要多張數據表的數據進行查詢,則可通過連接運算符實現多個查詢
內連接 inner join
    查詢兩個表中的結果集中的交集
外連接 outer join
    左外連接 left join
        (以左表作為基準,右邊表來一一匹配,匹配不上的,返回左表的記錄,右表以NULL填充)
    右外連接 right join
        (以右表作為基準,左邊表來一一匹配,匹配不上的,返回右表的記錄,左表以NULL填充)
        
等值連接和非等值連接

#查詢參加了考試的同學資訊(學號,學生姓名,科目編號,分數)
SELECT * FROM student;
SELECT * FROM result;

/*思路:
(1):分析需求,確定查詢的列來源於兩個類,student  result,連接查詢
(2):確定使用哪種連接查詢?(內連接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

# 右連接(也可實現)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

# 等值連接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

# 左連接 (查詢了所有同學,不考試的也會查出來)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

#查一下缺考的同學(左連接應用場景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL

#思考題:查詢參加了考試的同學資訊(學號,學生姓名,科目名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

來自網路截圖:

操作符名稱	功能描述
INNER JOIN
LEFT JOIN
RIGHT JOIN

Column 'StudentNo' in field list is ambiguous
欄位列表中的「StudentNo」列不明確

-- 聯表查詢練習
-- 查詢參加了考試的同學（姓名，學號，科目編號，成績）

SELECT *FROM`student`
SELECT *FROM `result`
/*
1.分析需求，分析查詢的欄位來自哪些表
2.確定使用哪種連接查詢（7種）
3.確定交集（數據相同）
4.判斷的條件 學生表中的 `StudentNo`= 成績表中的 `StudentNo`
*/
-- join 連接的表 on (判斷的條件) 連接查詢
-- where 等值查詢
SELECT s.`StudentNo`, `StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s		-- 兩張表都有`StudentNo`,為避免模稜兩可，取別名加以區分
INNER JOIN `result` AS r
-- 添加判斷條件，通過共有的`StudentNo`建立連接關係
WHERE s.`StudentNo`=r.`StudentNo`

-- right join=============
SELECT s.`StudentNo`, `StudentName`,`SubjectNo`,`StudentResult`
FROM `student` s    -- 取別名AS可以省略，空格分隔
RIGHT JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`

-- lift join=============
SELECT s.`StudentNo`, `StudentName`,`SubjectNo`,`StudentResult`
FROM `student` s    -- 取別名AS可以省略，空格分隔
LEFT JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`

自連接

categoryid	pid	categoryName
2	1	資訊技術
3	1	軟體開發
4	3	資料庫
5	1	美術設計
6	3	web開發
7	5	ps技術
8	2	辦公資訊

父類

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

子類

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

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

# 編寫SQL語句,將欄目的父子關係呈現出來 (父欄目名稱,子欄目名稱)
# 核心思想:把一張表看成兩張一模一樣的表,然後將這兩張表連接查詢(自連接)

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

/*
自連接
    數據表與自身進行連接

需求:從一個包含欄目ID , 欄目名稱和父欄目ID的表中 
     查詢父欄目名稱和其他子欄目名稱
*/

# 創建一個表
-- categoryid 類別
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','辦公資訊');

# 編寫SQL語句,將欄目的父子關係呈現出來 (父欄目名稱,子欄目名稱)
# 核心思想:把一張表看成兩張一模一樣的表,然後將這兩張表連接查詢(自連接)

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

#思考題:查詢參加了考試的同學資訊(學號,學生姓名,科目名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

# 查詢學員及所屬的年級(學號,學生姓名,年級名)
SELECT studentno AS 學號,studentname AS 學生姓名,gradename AS 年級名稱
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

# 查詢科目及所屬的年級(科目名稱,年級名稱)
SELECT subjectname AS 科目名稱,gradename AS 年級名稱
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

# 查詢 資料庫結構-1 的所有考試結果(學號 學生姓名 科目名稱 成績)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'

排序和分頁

/*============== 排序 ================
語法 : ORDER BY --以...排序
    ORDER BY 語句用於根據指定的列對結果集進行排序。
    ORDER BY 語句默認按照ASC升序對記錄進行排序。  -- 升序 ascending order
    如果您希望按照降序對記錄進行排序，可以使用 DESC 關鍵字。 --降序 descending order 
*/

# 查詢 資料庫結構-1 的所有考試結果(學號 學生姓名 科目名稱 成績)
# 按成績降序排序
# 按成績升序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'
#ORDER BY StudentResult DESC , studentno
ORDER BY StudentResult ASC , studentno
/*============== 分頁 ================
語法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好處 : (用戶體驗,網路傳輸,查詢壓力)
推導: 
    第一頁 : limit 0,5
    第二頁 : limit 5,5
    第三頁 : limit 10,5
    ......
    第N頁 : limit (pageNo-1)*pageSzie,pageSzie
    [pageNo:頁碼,pageSize:單頁面顯示條數]
    
*/

# 每頁顯示5條數據
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='C語言-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5
-- LIMIT 5,5

#查詢 JAVA第一學年 課程成績前10名並且分數大於80的學生資訊(學號,姓名,課程名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一學年'AND`StudentResult`>80 -- and `StudentResult`<=100
ORDER BY StudentResult DESC
LIMIT 0,10

子查詢

/*============== 子查詢 ================
什麼是子查詢?
    在查詢語句中的WHERE條件子句中,又嵌套了另一個查詢語句
    嵌套查詢可由多個子查詢組成,求解的方式是由里及外;
    子查詢返回的結果一般都是集合,故而建議使用IN關鍵字;
*/

# 查詢 資料庫結構-1 的所有考試結果(學號,科目編號,成績),並且成績降序排列

#方法一:使用連接查詢
SELECT studentno,r.subjectno,StudentResult
FROM result r 
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '資料庫結構-1'
ORDER BY studentresult DESC;

#方法二:使用子查詢(執行順序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
    SELECT subjectno FROM `subject` 
    WHERE subjectname = '資料庫結構-1'
)
ORDER BY studentresult DESC;

#查詢課程為 高等數學-2 且分數不小於80分的學生的學號和姓名

#方法一:使用連接查詢
SELECT s.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 r.studentno,studentname
FROM student s
INNER JOIN result r 
ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

#在上面SQL基礎上,添加需求:課程為 高等數學-2
SELECT r.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 = '高等數學-2'
)
#分步寫簡單sql語句,然後將其嵌套起來
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名學生的成績資訊(學號,姓名,分數)
    使用子查詢,查詢郭靖同學所在的年級名稱
*/

4.Mysql：MySQL函數

官方文檔 : //dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

常用函數

分類: 數學函數 , 字元串函數 , 日期和時間函數 , 系統資訊函數

# 數學函數 (這裡只列出一些常用的)

SELECT ABS(-8);  /*絕對值*/
-- absolute value
SELECT CEILING(9.4);  /*向上取整*/   
-- 天花板 上限 上升限度
SELECT FLOOR(9.4);  /*向下取整*/
-- 地板 樓層 自由體操
SELECT RAND();  /*隨機數,返回一個0-1之間的隨機數*/
-- 產生均勻分布隨機數
SELECT SIGN(0); /*符號函數: 負數返回-1,正數返回1,0返回0*/
-- 符號
SELECT SIGN(-4); 
SELECT SIGN(8); 

#字元串函數

SELECT CHAR_LENGTH('新思路團隊都是大佬'); /*返回字元串包含的字元數*/

SELECT CONCAT('我','相信','自己');  /*合併字元串,參數可以有多個*/

SELECT INSERT('我想進新思路',2,1,'一定努力');  /*替換字元串,從某個位置開始替換某個長度*/

SELECT LOWER('WANWAN'); /*小寫*/

SELECT UPPER('wanwan'); /*大寫*/

SELECT LEFT('hello,world',5);  /*從左邊截取*/

SELECT RIGHT('hello,world',5);  /*從右邊截取*/

SELECT REPLACE('在新思路能學習到很多東西','很多東西','新知識');  /*替換字元串*/

SELECT SUBSTR('新思路是一個很棒的團隊',4,8); /*截取字元串,開始和長度*/

SELECT REVERSE('Java EE'); /*反轉，逆向*/


#日期和時間函數

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 VERSION();  /*版本*/
SELECT USER();  /*用戶*/


# 查詢姓李的同學,改成立
SELECT REPLACE(studentname,'李','立') AS 新名字
FROM student WHERE studentname LIKE '李%';

聚合函數

函數名稱	描述
count()	計數
sum()	求和
vag()	求平均值
max()	求最大值
min()	求最小值

#聚合函數

/*COUNT:非空的*/
SELECT COUNT(studentname) FROM student;
-- 返回記錄的總數
SELECT COUNT(*) FROM student;
-- 效率低，不建議使用
SELECT COUNT(1) FROM student; 
-- 推薦
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 AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno
HAVING 平均分>80;

/*
where寫在group by前面.
要是放在分組後面的篩選
要使用HAVING..
*/

count分析

執行效果：
1.  count(1) and count(*)
當表的數據量大些時，對錶作分析之後，使用count(1)還要比使用count(*)用時多了！ 
從執行計劃來看，count(1)和count(*)的效果是一樣的。 但是在表做過分析之後，count(1)會比count(*)的用時少些（1w以內數據量），不過差不了多少。 
 
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。 
因為count(*),自動會優化指定到那一個欄位。所以沒必要去count(1)，用count(*)，sql會幫你完成優化的 因此：count(1)和count(*)基本沒有差別！ 
 
2. count(1) and count(欄位)
兩者的主要區別是
（1） count(1) 會統計表中的所有的記錄數，包含欄位為null 的記錄。
（2） count(欄位) 會統計該欄位在表中出現的次數，忽略欄位為null 的情況。即不統計欄位為null 的記錄。 

3. count(*) 和 count(1)和count(列名)區別  
執行效果上：  
count(*)包括了所有的列，相當於行數，在統計結果的時候，不會忽略列值為NULL  
count(1)包括了忽略所有列，用1代表程式碼行，在統計結果的時候，不會忽略列值為NULL  
count(列名)只包括列名那一列，在統計結果的時候，會忽略列值為空（這裡的空不是只空字元串或者0，而是表示null）的計數，即某個欄位值為NULL時，不統計。
執行效率上：  
列名為主鍵，count(列名)會比count(1)快  
列名不為主鍵，count(1)會比count(列名)快  
如果表多個列並且沒有主鍵，則 count（1） 的執行效率優於 count（*）  
如果有主鍵，則 select count（主鍵）的執行效率是最優的  
如果表只有一個欄位，則 select count（*）最優。

MD5 加密

MD5 百度百科://baike.baidu.com/item/MD5/212708?fr=aladdin

簡介：

MD5資訊摘要演算法（英語：MD5 Message-Digest Algorithm），一種被廣泛使用的密碼散列函數，可以產生出一個128位（16位元組）的散列值（hash value），用於確保資訊傳輸完整一致。

原理：

MD5演算法的原理可簡要的敘述為：MD5碼以512位分組來處理輸入的資訊，且每一分組又被劃分為16個32位子分組，經過了一系列的處理後，演算法的輸出由四個32位分組組成，將這四個32位分組級聯後將生成一個128位散列值。

總體流程如下圖所示，每次的運算都由前一輪的128位結果值和當前的512bit值進行運算

sql:MD5測試

CREATE TABLE testMD5(
`id` INT (6)NOT NULL AUTO_INCREMENT  COMMENT '序號',
`name` VARCHAR (4) NOT NULL DEFAULT '姓名'COMMENT'姓名測試',
`age` INT(3) DEFAULT NULL,
`birthday` VARCHAR (60)NOT NULL DEFAULT '時間'COMMENT'出生日期',
`password`VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)

)ENGINE = INNODB DEFAULT CHARSET =utf8

DROP TABLE`testmd5`

INSERT INTO `testmd5` VALUE(1,'張三','21','1999-04-23','123456')
INSERT INTO `testmd5` VALUE(2,'李四','22','1998-03-13','321431')
INSERT INTO `testmd5` VALUE(3,'王五','23','1997-06-18','425633')
INSERT INTO `testmd5` VALUE(4,'小張','20','2000-09-13','327268')
INSERT INTO `testmd5` VALUE(5,'小李','20','2001-12-13','526825')
INSERT INTO `testmd5` VALUE(6,'小王','20','2001-12-13',MD5('424145'))
-- 加密`password`
UPDATE `testmd5`SET `password`=MD5(`password`)WHERE `id`=1
UPDATE `testmd5`SET `password`=MD5(`password`)
-- 加密全部`birthday`
UPDATE `testmd5`SET `birthday`=MD5(`birthday`)
UPDATE `testmd5`SET `birthday`=MD5(`birthday`)WHERE `name`='小張'
-- 
-- UPDATE `testmd5`SET `testmd5`=MD5(`testmd5`)
-- > Unknown column 'testmd5' in 'field list'-->「欄位列表」中的未知列「testmd5」

-- 如何校正？ 將用戶傳來的資訊進行MD5加密，然後比對加密後的值
SELECT * FROM `testmd5`WHERE `name`='小張'AND`age`='20'AND`birthday`='2000-09-13'AND`password`='327268'

SELECT * FROM `testmd5`WHERE `name`='張三'AND`birthday`='1999-04-23'

SELECT * FROM `testmd5`WHERE `name`='李四'AND`birthday`='1999-04-23'

mysql之自定義函數

什麼是函數：

函數存儲著一系列SQL語句，調用函數就是一次性執行這些語句。所以函數可以降低語句重複。
但注意的是函數注重返回值，不注重執行過程，所以一些語句無法執行。所以函數並不是單純的SQL語句集合。

函數與存儲過程的區別：函數只會返回一個值，不允許返回一個結果集。函數強調返回值，所以函數不允許返回多個值的情況，即使是查詢語句。

5.事務ACID理解

參考部落格：//blog.csdn.net/dengjili/article/details/82468576

CSDN://blog.csdn.net/weixin_42181824/article/details/82261988

MySQL事務

事務就是將一組SQL語句放在同一批次內去執行
如果一個SQL語句出錯,則該批次內的所有SQL都將被取消執行
MySQL事務處理只支援InnoDB和BDB數據表類型

事務的ACID原則

原子性（Atomicity）

原子性是指事務是一個不可分割的工作單位，事務中的操作要麼都發生，要麼都不發生。

一致性（Consistency）
事務前後數據的完整性必須保持一致。

持久性（Durability）
持久性是指一個事務一旦被提交，它對資料庫中數據的改變就是永久性的，接下來即使資料庫發生故障也不應該對其有任何影響

隔離性（Isolation）
事務的隔離性是多個用戶並發訪問資料庫時，資料庫為每一個用戶開啟的事務，不能被其他事務的操作數據所干擾，多個並發事務之間要相互隔離。（如果隔離失敗，則會失敗，導致產生臟讀，不可重複讀，幻讀等）

事務的理解：模擬轉賬

-- =========事務的理解========

-- 1.創建一個資料庫
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci; 
USE `shop`; 
-- 2.創建一個`account`表
CREATE TABLE `account` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `cash` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 3. 添加數據
INSERT INTO `account`(`name`,`cash`)
VALUES ('A',800),('B',200)
-- 4.模擬轉賬事務
SET autocommit = 0  -- 關閉自動提交
START TRANSACTION -- 開啟一組事務
-- 5.開始轉賬 A 轉賬200給B
UPDATE `account` SET `cash`=`cash`-200 WHERE `name`='A'
UPDATE `account` SET `cash`=`cash`+200 WHERE `name`='B'
-- 6. 提交與回滾
COMMIT; -- 提交事務
ROLLBACK; -- 回滾
-- 7.恢復默認值
SET autocommit = 1 ;-- 恢復默認值

補充說明

-- =========事務的理解========

-- mysql 是默認開啟事務提交的
SET autocommit = 0 -- 關閉
SET autocommit = 1 -- 開啟
-- 手動處理事務
SET autocommit = 0 -- 關閉自動提交
-- 開啟事務
START TRANSACTION -- 標記這個事務的開始，從這以後的sql語句都在同一個事務中
INSERT **
INSERT **
-- 提交，持久化（成功）
COMMIT;
-- 回滾，回到原來的樣子（失敗）
ROLLBACK;
-- 事務結束
SET autocommit = 1 -- 開啟自動提交

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

資料庫索引

推薦博文://www.cnblogs.com/chenshishuo/p/5030029.html

作用 :

提高查詢速度
確保數據的唯一性
可以加速表和表之間的連接 , 實現表與表之間的參照完整性
使用分組和排序子句進行數據檢索時 , 可以顯著減少分組和排序的時間
全文檢索欄位進行搜索優化.

分類 :

主鍵索引 (Primary Key)

唯一標識，不可重複，只能有一個列作為主鍵

唯一索引 (Unique)

避免出現重複列，唯一索引可以重複，多個列都可以標識唯一索引

常規索引 (Index/key)

默認的是index，key關鍵字可以設置

全文索引 (FullText)

在特定的資料庫引擎下才有，可實現快速定位數據

作用 : 快速定位特定數據
注意 :
只能用於MyISAM類型的數據表
只能用於CHAR , VARCHAR , TEXT數據列類型
適合大型數據集

索引簡介

-- ========索引練習=========
-- 1.在創建表的時候使用
-- 2.創建完表後增加索引
-- 3.創建完表後修改索引

-- 顯示所有的索引資訊
SHOW INDEX FROM `student_table`
-- 增加一個全文索引 （索引名）列名
ALTER TABLE `indextest`.`student_table`ADD FULLTEXT INDEX `name`(`name`);
-- EXPLAIN 分析sql執行得情況
EXPLAIN SELECT * FROM `student_table`; -- 全文索引
EXPLAIN SELECT * FROM `student_table` WHERE MATCH (`name`)AGAINST('李')

索引準則

索引不是越多越好
不要對經常變動的數據加索引
小數據量的表建議不要加索引
索引一般應加在查找條件的欄位

用戶管理

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

CREATE USER ZhengWanfu IDENTIFIED BY '123456'

-- 修改密碼(修改當前用戶密碼)->root

SET PASSWORD = '654321'

-- 修改密碼（修改指定用戶密碼）

SET PASSWORD FOR ZhengWanfu =PASSWORD('654321')

-- 重命名 

RENAME USER ZhengWanfu TO ZhengWanfu2

-- 用戶授權 ALL PRIVILEGES 全部的許可權 除了GRANT（給別人授權）

GRANT ALL PRIVILEGES ON *.* TO ZhengWanfu2

-- 查詢許可權
SHOW GRANTS FOR ZhengWanfu2 -- 查看指定用戶許可權

SHOW GRANTS FOR [email protected] 
-- There is no such grant defined for user 'root' on host '127.0.0.1'
-- 主機「127.0.0.1」上沒有為用戶「root」定義此類授予

-- 撤銷許可權
REVOKE ALL PRIVILEGES ON *.* FROM ZhengWanfu2

-- 刪除用戶
DROP USER ZhengWanfu2

MySQL備份

資料庫備份必要性
- 保證重要數據不丟失
- 數據轉移
MySQL資料庫備份方法
- mysqldump備份工具
- 資料庫管理工具,如SQLyog
- 直接拷貝資料庫文件和相關配置文件

6.資料庫的設計

規範化資料庫設計

為什麼需要設計資料庫 ?

答:當資料庫比較複雜時我們需要設計資料庫

糟糕的資料庫設計 :

數據冗餘,存儲空間浪費
數據更新和插入的異常
程式性能差

良好的資料庫設計 :

節省數據的存儲空間
能夠保證數據的完整性
方便進行資料庫應用系統的開發

軟體項目開發周期中資料庫設計 :

需求分析階段: 分析客戶的業務和數據處理需求
概要設計階段:設計資料庫的E-R模型圖 , 確認需求資訊的正確和完整.

設計資料庫步驟

收集資訊
- 與該系統有關人員進行交流 , 座談 , 充分了解用戶需求 , 理解資料庫需要完成的任務.
標識實體[Entity]
- 標識資料庫要管理的關鍵對象或實體,實體一般是名詞
標識每個實體需要存儲的詳細資訊[Attribute]
標識實體之間的關係[Relationship]

繪製E-R圖和資料庫模型圖

E-R圖即資料庫設計的圖形化表達方式 , 也稱為實體-關係圖

關係模式 : 用二維表的形式表示實體和實體間聯繫的數據模型即關係模式R(U)

繪製資料庫模型圖的步驟 :

新建資料庫模型圖
添加實體
添加數據列及相應的屬性
添加實體之間的映射關係

三大範式

問題 : 為什麼需要數據規範化?

不合規範的表設計

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

第一範式 (1st NF)

第一範式的目標是確保每列的原子性,如果每列都是不可再分的最小數據單元,則滿足第一範式

第二範式(2nd NF)

第二範式要求每個表只描述一件事情

第三範式(3rd NF)

如果一個關係滿足第二範式,並且除了主鍵以外的其他列都不傳遞依賴於主鍵列,則滿足第三範式.

規範化和性能的關係

為滿足某種商業目標 , 資料庫性能比規範化資料庫更重要

在數據規範化的同時 , 要綜合考慮資料庫的性能

通過在給定的表中添加額外的欄位,以大量減少需要從中搜索資訊所需的時間

通過在給定的表中插入計算列,以方便查詢

7.JDBC(重點)

資料庫驅動

驅動包下載地址：//mvnrepository.com/artifact/mysql/mysql-connector-java