MySQL入門看這一篇就夠了

y浴血發表於2020-12-09
MySql

MySQL

JavaEE：企業級Java開發 web階段

分為1.前端（頁面，展示資料庫中的資料）

2.後臺（連線點：連結資料庫JDBC、Mybatis，連結前端（控制檢視跳轉，給前端傳遞資料））

3.資料庫（存資料）

1、初始MySQL

1.1、為什麼學習資料庫

大資料時代，Java需要資料庫

被迫需求：存資料

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

1.2、什麼是資料庫

資料庫（DB，database）

概念：資料倉儲，用來儲存資料，是一個軟體安裝在作業系統上！SQL可以儲存大量的資料大概500萬之內的資料查詢很快

作用：儲存資料和管理資料

1.3、資料庫分類

關係型資料庫（類似Excel）：有行和列

  • MySQL，Oracle，DB2等
  • 通過表和表之間，行和列之間的關係進行資料的儲存。學員資訊表，考勤表等
  • SQL

非關係型資料庫：一般是key和value

  • Redis，MongoDB
  • 非關係型資料庫儲存的是物件，通過物件自身的屬性來決定
  • NoSQL->Not Only SQL

DBMS：資料庫管理系統

  • 是資料庫的管理軟體，科學有效地管理我們的資料，有效地維護和獲取資料
  • MySQL本質就是一個資料庫管理系統

1.4、MySQL簡介

MySQL是一個關係型資料庫管理系統，

前世：由瑞典MySQL AB 公司開發

今生：屬於 Oracle 旗下產品。

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

開源的資料庫軟體

體積小、速度快、總體擁有成本低

官網：https://www.mysql.com

5.7版本穩定

安裝建議：

1、儘量不要使用exe，因為有登錄檔，解除安裝非常麻煩

2、儘可能使用壓縮包來安裝

1.5、安裝MySQL

1.6、安裝Navicat

1.7、連線資料庫

開啟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
? 命令關鍵詞 : 尋求幫助
-- 表示註釋（SQL當中原本的註釋）
/*
多行註釋
*/

資料庫xxx語言

DDL 定義

DML 操作

DQL 查詢

DCL 控制

2、運算元據庫

運算元據庫->運算元據庫中的表->運算元據庫中表的資料

MySQL裡面不區分大小寫

2.1、運算元據庫

  1. 建立資料庫

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

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

  2. 刪除資料庫

    drop database [if exists] 資料庫名;

  3. 使用資料庫

    use 資料庫名;

    如果你的表名或者欄位名是一個特殊字元，就需要加`

  4. 檢視資料庫

    show databases; -- 檢視所有的資料庫

開啟表就是查詢，對照檢視SQL語句

固定語法中的單詞需要記憶

2.2、資料庫的資料型別

數值

  • tinyint 十分小的資料 1個位元組
  • smallint 較小的資料 2個位元組
  • mediumint 中等大小的資料 3個位元組
  • int 標準的整數 4個位元組 常用
  • 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

2.3、資料庫的欄位屬性

UnSigned

  • 無符號的
  • 宣告該資料列不允許負數 .

ZEROFILL

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

Auto_InCrement

  • 自動增長的 , 每新增一條資料 , 自動在上一個記錄數上加 1(預設)

  • 通常用於設定主鍵 , 且為整數型別

  • 可定義起始值和步長

    • 當前表設定步長(AUTO_INCREMENT=100) : 隻影響當前表
    • SET @@auto_increment_increment=5 ; 影響所有使用自增的表(全域性)

NULL 和 NOT NULL

  • 預設為NULL , 即沒有插入該列的數值
  • 如果設定為NOT NULL , 則該列必須有值，如果不寫值會報錯

DEFAULT

  • 預設的
  • 用於設定預設值
  • 例如,性別欄位,預設為"男" , 否則為 "女" ; 若無指定該列的值 , 則預設值為"男"的值

要求必須存在的東西

/*		每一個表，都必須存在以下五個欄位
id					主鍵
`version`		樂觀鎖
is_delete		偽刪除
gmt_create	建立時間
gmt_update	修改時間
*/

2.4、建立資料庫表

注意點：使用英文()，表名稱 和 欄位 儘量使用``括起來

字串使用 單引號括起來

所有的語句後面加，(英文逗號)，最後一個不用加

primary key是主鍵，一般一個表只有一個唯一的主鍵

-- 目標 : 建立一個school資料庫
-- 建立學生表(列,欄位)
-- 學號int 登入密碼varchar(20) 姓名,性別varchar(2),出生日期(datatime),家庭住址,email
-- 建立表之前 , 一定要先選擇資料庫

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

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

格式：

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

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

2.5、資料表的型別

InnoDB 預設使用

MyISAM 早些年使用的

MyISAM InnoDB
事務支援 不支援 支援
資料行鎖定 不支援 支援
外來鍵 不支援 支援
全文索引 支援 不支援
表空間的大小 較小 較大，約為MyISAM的2倍

常規使用：

  • MyISAM 節約空間，速度較快
  • InnoDB 安全性高，事務的處理，多表多使用者操作

在物理空間中存在的位置

所有的資料庫檔案都存在data目錄下，一個資料夾就對應著一個資料庫

本質還是檔案的儲存！

MySQL引擎在物理檔案上的區別

  • MySQL資料表以檔案方式存放在磁碟中

    • 包括表檔案 , 資料檔案 , 以及資料庫的選項檔案
    • 位置 : Mysql安裝目錄\data\下存放資料表，目錄名對應資料庫名 , 該目錄下檔名對應資料表

  • 注意 :

    • * . frm -- 表結構定義檔案
    • * . MYD -- 資料檔案 ( data )
    • * . MYI -- 索引檔案 ( index )
    • InnoDB型別資料表只有一個 *.frm檔案 , 以及上一級目錄的ibdata1檔案
    • MyISAM型別資料表對應以上三個檔案

設定資料表字符集

我們可為資料庫,資料表,資料列設定不同的字符集，設定方法 :

  • 建立時通過命令來設定 , 如 : CREATE TABLE 表名()CHARSET = utf8;
  • 如無設定 , 則根據MySQL資料庫配置檔案 my.ini 中的引數設定
  • 不設定的話，會是MySQL預設的字符集編碼Latin1（不支援中文）

2.6、修改刪除表

修改表 ( ALTER TABLE )

修改表名 :ALTER TABLE 舊錶名 RENAME AS 新表名

新增欄位 : ALTER TABLE 表名 ADD 欄位名 列屬性[屬性]

修改欄位 :

  • ALTER TABLE 表名 MODIFY 欄位名 列型別[屬性]（修改約束）
  • ALTER TABLE 表名 CHANGE 舊欄位名 新欄位名 列屬性[屬性]（修改表的欄位名）

結論：change用來欄位重新命名，不能修改欄位型別和約束

​ modify不用來欄位重新命名，只能修改欄位型別和約束

刪除欄位 : ALTER TABLE 表名 DROP 欄位名

刪除資料表

語法：DROP TABLE [IF EXISTS] 表名

  • IF EXISTS為可選 , 判斷是否存在該資料表
  • 如刪除不存在的資料表會丟擲錯誤

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

其他

1. 可用反引號（`）為識別符號（庫名、表名、欄位名、索引、別名）包裹，以避免與關鍵字重名！中文也可以作為識別符號！

2. 每個庫目錄存在一個儲存當前資料庫的選項檔案db.opt。

3. 註釋：
  單行註釋 # 註釋內容
  多行註釋 /* 註釋內容 */
  單行註釋 -- 註釋內容       (標準SQL註釋風格，要求雙破折號後加一空格符（空格、TAB、換行等）)
   
4. 模式萬用字元：
  _   任意單個字元
  %   任意多個字元，甚至包括零字元
  單引號需要進行轉義 \'
   
5. CMD命令列內的語句結束符可以為 ";", "\G", "\g"，僅影響顯示結果。其他地方還是用分號結束。delimiter 可修改當前對話的語句結束符。

6. SQL對大小寫不敏感 （關鍵字）

7. 清除已有語句：\c

3、MySQL資料管理

3.1、外來鍵

外來鍵概念

如果公共關鍵字在一個關係中是主關鍵字，那麼這個公共關鍵字被稱為另一個關係的外來鍵。由此可見，外來鍵表示了兩個關係之間的相關聯絡。以另一個關係的外來鍵作主關鍵字的表被稱為主表，具有此外來鍵的表被稱為主表的從表。

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

外來鍵作用

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

建立外來鍵

建表時指定外來鍵約束

-- 建立外來鍵的方式一 : 建立子表同時建立外來鍵

-- 年級表 (id\年級名稱)
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

-- 學生資訊表 (學號,姓名,性別,年級,手機,地址,出生日期,郵箱,身份證號)
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT '學號',
`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性別',
`gradeid` INT(10) DEFAULT NULL COMMENT '年級',
`phoneNum` VARCHAR(50) NOT NULL COMMENT '手機',
`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
`borndate` DATETIME DEFAULT NULL COMMENT '生日',
`email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份證號',
PRIMARY KEY (`studentno`),
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`);

刪除外來鍵

操作：刪除 grade 表，發現報錯

注意 : 刪除具有主外來鍵關係的表時 , 要先刪子表 , 後刪主表

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

以上操作都是物理外來鍵，資料庫級別的外來鍵不建議使用（避免資料庫過多造成困擾）

最佳方法：

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

  • 我們想使用多張表的資料當中的外來鍵的時候，利用程式去實現

3.2、DML語言

資料庫意義 ： 資料儲存、資料管理

管理資料庫資料方法：

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

DML語言 ：資料操作語言

  • 用於運算元據庫物件中所包含的資料

  • 包括 :

    • INSERT (新增資料語句)
    • UPDATE (更新資料語句)
    • DELETE (刪除資料語句)

3.3、新增資料

INSERT命令

語法：

INSERT INTO 表名[(欄位1,欄位2,欄位3,...)] VALUES('值1','值2','值3'，...),('值1','值2','值3'，...)

[]是可選的，根據需求寫

注意 :

  • 欄位或值之間用英文逗號隔開
  • ' 欄位1,欄位2...' 該部分可省略，但新增的值務必與表結構,資料列**，順序相對應，且數量一致 **
  • 可同時插入多條資料 ，values 後用英文逗號隔開
-- 使用語句如何增加語句?
-- 語法 : INSERT INTO 表名[(欄位1,欄位2,欄位3,...)] VALUES('值1','值2','值3')
INSERT INTO grade(gradename) VALUES ('大一');

-- 主鍵自增,那能否省略呢?
INSERT INTO grade VALUES ('大二');

-- 查詢:INSERT INTO grade VALUE ('大二')錯誤程式碼：1136
Column count doesn`t match value count at row 1

-- 結論:'欄位1,欄位2...'該部分可省略 , 但新增的值務必與表結構,資料列,順序相對應,且數量一致.

-- 一次插入多條資料，values後面的值需要使用，隔開即可
INSERT INTO grade(gradename) VALUES ('大三'),('大四');

3.4、修改資料

update命令

語法：

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

注意 :

  • column_name 為要更改的資料列，儘量帶上``
  • value 為修改後的資料 , 可以為變數 , 具體值 , 表示式或者巢狀的SELECT結果
  • condition 為篩選條件 , 如不指定則修改該表的所有列資料
  • 不指定條件的情況下，會改動所有的列，所以儘量不要這樣做

where條件子句

可以簡單的理解為 : 有條件地從表中篩選資料

測試：

-- 修改年級資訊
UPDATE grade SET gradename = '高中' WHERE gradeid = 1;

3.5、刪除資料

DELETE命令

語法：

DELETE FROM 表名 [WHERE condition];

注意：condition為篩選條件 , 如不指定則刪除該表的所有列資料

-- 刪除最後一個資料
DELETE FROM grade WHERE gradeid = 5

TRUNCATE命令

作用：用於完全清空表資料 , 但表結構 , 索引 , 約束等不變 ;

語法：

TRUNCATE [TABLE] table_name;

-- 清空年級表
TRUNCATE grade

注意：區別於DELETE命令

  • 相同 : 都能刪除資料 , 不刪除表結構 , 但TRUNCATE速度更快

  • 不同 :

    • 使用TRUNCATE TABLE 重新設定AUTO_INCREMENT計數器
    • 使用TRUNCATE TABLE不會對事務有影響

測試：

-- 建立一個測試表
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('row1'),('row2'),('row3');

-- 刪除表資料(不帶where條件的delete)
DELETE FROM test;
-- 結論:如不指定Where則刪除該表的所有列資料,自增當前值依然從原來基礎上進行,會記錄日誌.

-- 刪除表資料(truncate)
TRUNCATE TABLE test;
-- 結論:truncate刪除資料,自增當前值會恢復到初始值重新開始;不會記錄日誌.

-- 同樣使用DELETE清空不同引擎的資料庫表資料.重啟資料庫服務後
-- InnoDB : 自增列從初始值重新開始 (因為是儲存在記憶體中,斷電即失)
-- MyISAM : 自增列依然從上一個自增資料基礎上開始 (存在檔案中,不會丟失)

4、DQL語言

DQL( Data Query Language 資料查詢語言 )

  • 查詢資料庫資料 , 如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}];
   -- 指定查詢的記錄從哪條至哪條

注意 : [ ] 括號代表可選的 , { }括號代表必選得

4.1、指定查詢欄位

-- 查詢表中所有的資料列結果 , 採用 **" \* "** 符號; 但是效率低，不推薦 .

-- 查詢所有學生資訊
SELECT * FROM student;

-- 查詢指定列(學號 , 姓名)
SELECT studentno,studentname FROM student;

AS 子句作為別名

作用：

  • 可給資料列取一個新別名
  • 可給表取一個新別名
  • 可把經計算或總結的結果用另一個新名稱來代替
-- 這裡是為列取別名(當然as關鍵詞可以省略)
SELECT studentno AS 學號,studentname AS 姓名 FROM student;

-- 使用as也可以為表取別名
SELECT studentno AS 學號,studentname AS 姓名 FROM student AS s;

-- 使用as,為查詢結果取一個新名字
-- CONCAT()函式拼接字串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

DISTINCT關鍵字的使用

作用 : 去掉SELECT查詢返回的記錄結果中重複的記錄 ( 返回所有列的值都相同 ) , 只返回一條

-- # 檢視哪些同學參加了考試(學號) 去除重複項
SELECT * FROM result; -- 檢視考試成績
SELECT studentno FROM result; -- 檢視哪些同學參加了考試
SELECT DISTINCT studentno FROM result; -- 瞭解:DISTINCT 去除重複項 , (預設是ALL)

使用表示式的列

資料庫中的表示式 : 一般由文字值 , 列值 , NULL , 函式和操作符等組成

應用場景 :

  • SELECT語句返回結果列中使用

  • SELECT語句中的ORDER BY , HAVING等子句中使用

  • DML語句中的 where 條件語句中使用表示式

    -- selcet查詢中可以使用表示式
SELECT @@auto_increment_increment; -- 查詢自增步長
SELECT VERSION(); -- 查詢版本號
SELECT 100*3-1 AS 計算結果; -- 表示式

-- 學員考試成績集體提分一分檢視
SELECT studentno,StudentResult+1 AS '提分後' FROM result;

  • 避免SQL返回結果中包含 ' . ' , ' * ' 和括號等干擾開發語言程式.

4.2、where條件語句

作用：用於檢索資料表中 符合條件 的記錄

搜尋條件可由一個或多個邏輯表示式組成 , 結果一般為真或假.

邏輯操作符

測試

-- 滿足條件的查詢(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;

-- 除了1000號同學,要其他同學的成績
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;

-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

模糊查詢 ：比較操作符

注意：

  • 數值資料型別的記錄之間才能進行算術運算 ;
  • 相同資料型別的資料之間才能進行比較 ;

測試：

-- 模糊查詢 between and \ like \ in \ null

-- =============================================
-- LIKE
-- =============================================
-- 查詢姓劉的同學的學號及姓名
-- 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 '%嘉%';

-- 查詢姓名中含有特殊字元的需要使用轉義符號 '\'
-- 自定義轉義符關鍵字: ESCAPE ':'

-- =============================================
-- IN
-- =============================================
-- 查詢學號為1000,1001,1002的學生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

-- 查詢地址在北京,南京,河南洛陽的學生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛陽');

-- =============================================
-- NULL 空
-- =============================================
-- 查詢出生日期沒有填寫的同學
-- 不能直接寫=NULL , 這是代表錯誤的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;

-- 查詢出生日期填寫的同學
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;

-- 查詢沒有寫家庭住址的同學(空字串不等於null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

4.3、連線查詢

JOIN 對比

七種Join：

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 會從右表中返回所有的值，即使左表中沒有匹配

  • left join是以A表的記錄為基礎的,A可以看成左表,B可以看成右表,left join是以左表為準的.換句話說,左表(A)的記錄將會全部表示出來,而右表(B)只會顯示符合搜尋條件的記錄B表記錄不足的地方均為NULL.

  • right join和left join的結果剛好相反,是以右表(B)為基礎的,A表不足的地方用NULL填充.

  • inner join並不以誰為基礎,它只顯示符合條件的記錄

sql的left join 、right join 、inner join之間的區別

　　left join(左聯接) 返回包括左表中的所有記錄和右表中聯結欄位相等的記錄
　　right join(右聯接) 返回包括右表中的所有記錄和左表中聯結欄位相等的記錄
　　inner join(等值連線) 只返回兩個表中聯結欄位相等的行

join on（判斷條件）是一個連線查詢

where是一個等值查詢

自連線

/*
自連線
   資料表與自身進行連線

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

-- 建立一個表
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'

4.4、排序和分頁

測試

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

-- 查詢 資料庫結構-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

/*============== 分頁 ================
語法 : 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='資料庫結構-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,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第一學年'
ORDER BY StudentResult DESC
LIMIT 0,10

4.5、子查詢

/*============== 子查詢 ================
什麼是子查詢?
   在查詢語句中的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.6、分組和過濾

group by 和 having

題目：

 -- 查詢不同課程的平均分,最高分,最低分
 -- 前提:根據不同的課程進行分組
 
 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..
 因為having是從前面篩選的欄位再篩選，而where是從資料表中的>欄位直接進行的篩選的
 */

5.MySQL當中常用函式

5.1、資料函式

 SELECT ABS(-8);  /*絕對值*/
 SELECT CEILING(9.4); /*向上取整*/
 SELECT FLOOR(9.4);   /*向下取整*/
 SELECT RAND();  /*隨機數,返回一個0-1之間的隨機數*/
 SELECT SIGN(0); /*符號函式: 負數返回-1,正數返回1,0返回0*/

5.2、字串函式

 SELECT CHAR_LENGTH('堅持就能成功'); /*返回字串包含的字元數*/
 SELECT CONCAT('我','愛','程式');  /*合併字串,引數可以有多個*/
 SELECT INSERT('我愛程式設計helloworld',1,2,'超級熱愛');  /*替換字串,從某個位置開始替換某個長度*/
 SELECT LOWER('YuXue'); /*小寫*/
 SELECT UPPER('YuXue'); /*大寫*/
 SELECT LEFT('hello,world',5);   /*從左邊擷取*/
 SELECT RIGHT('hello,world',5);  /*從右邊擷取*/
 SELECT REPLACE('堅持就能成功','堅持','努力');  /*替換字串*/
 SELECT SUBSTR('堅持就能成功',4,2); /*擷取字串,開始和長度*/
 SELECT REVERSE('堅持就能成功'); /*反轉
 
 -- 查詢姓周的同學,改成鄒
 SELECT REPLACE(studentname,'周','鄒') AS 新名字
 FROM student WHERE studentname LIKE '周%';

5.3、日期和時間函式

 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();     /*使用者*/

5.4、聚合函式

函式名稱 描述
COUNT() 返回滿足Select條件的記錄總和數，如 select count(*) 【不建議使用 *，效率低】
SUM() 返回數字欄位或表示式列作統計，返回一列的總和。
AVG() 通常為數值欄位或表達列作統計，返回一列的平均值
MAX() 可以為數值欄位，字元欄位或表示式列作統計，返回最大的值。
MIN() 可以為數值欄位，字元欄位或表示式列作統計，返回最小的值。 
 -- 聚合函式
 /*COUNT:非空的*/
 SELECT COUNT(studentname) FROM student;
 SELECT COUNT(*) FROM student;
 SELECT COUNT(1) FROM student;  /*推薦*/
 
 -- 從含義上講，count(1) 與 count(*) 都表示對全部資料行的查詢。
 -- count(欄位) 會統計該欄位在表中出現的次數，忽略欄位為null 的情況。即不統計欄位為null 的記錄。
 -- count(*) 包括了所有的列，相當於行數，在統計結果的時候，包含欄位為null 的記錄；
 -- count(1) 用1代表程式碼行，在統計結果的時候，包含欄位為null 的記錄 。
 /*
 很多人認為count(1)執行的效率會比count(*)高，原因是count(*)會存在全表掃描，而count(1)可以針對一個欄位進行查詢。其實不然，count(1)和count(*)都會對全表進行掃描，統計所有記錄的條數，包括那些為null的記錄，因此，它們的效率可以說是相差無幾。而count(欄位)則與前兩者不同，它會統計該欄位不為null的記錄條數。
 
 下面它們之間的一些對比：
 
 1）在表沒有主鍵時，count(1)比count(*)快
 2）有主鍵時，主鍵作為計算條件，count(主鍵)效率最高；
 3）若表格只有一個欄位，則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;

MD5 加密

一、MD5簡介

MD5即Message-Digest Algorithm 5（資訊-摘要演算法5），用於確保資訊傳輸完整一致。是計算機廣泛使用的雜湊演算法之一（又譯摘要演算法、雜湊演算法），主流程式語言普遍已有MD5實現。將資料（如漢字）運算為另一固定長度值，是雜湊演算法的基礎原理，MD5的前身有MD2、MD3和MD4。

二、實現資料加密

新建一個表 testmd5

 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,'yuxue','123456'),(2,'hejk','456789')

如果我們要對pwd這一列資料進行加密，語法是：

 update testmd5 set pwd = md5(pwd);

如果單獨對某個使用者(如kuangshen)的密碼加密：

 INSERT INTO testmd5 VALUES(3,'yuxue2','123456')
 update testmd5 set pwd = md5(pwd) where name = 'hejk2';

插入新的資料自動加密

 INSERT INTO testmd5 VALUES(4,'yuxue3',md5('123456'));

查詢登入使用者資訊（md5對比使用，檢視使用者輸入加密後的密碼進行比對）

 SELECT * FROM testmd5 WHERE `name`='hejk' AND pwd=MD5('123456');

小結

 -- ================ 內建函式 ================
 -- 數值函式
 abs(x)            -- 絕對值 abs(-10.9) = 10
 format(x, d)    -- 格式化千分位數值 format(1234567.456, 2) = 1,234,567.46
 ceil(x)            -- 向上取整 ceil(10.1) = 11
 floor(x)        -- 向下取整 floor (10.1) = 10
 round(x)        -- 四捨五入去整
 mod(m, n)        -- m%n m mod n 求餘 10%3=1
 pi()            -- 獲得圓周率
 pow(m, n)        -- m^n
 sqrt(x)            -- 算術平方根
 rand()            -- 隨機數
 truncate(x, d)    -- 擷取d位小數
 
 -- 時間日期函式
 now(), current_timestamp();     -- 當前日期時間
 current_date();                    -- 當前日期
 current_time();                    -- 當前時間
 date('yyyy-mm-dd hh:ii:ss');    -- 獲取日期部分
 time('yyyy-mm-dd hh:ii:ss');    -- 獲取時間部分
 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');    -- 格式化時間
 unix_timestamp();                -- 獲得unix時間戳
 from_unixtime();                -- 從時間戳獲得時間
 
 -- 字串函式
 length(string)            -- string長度，位元組
 char_length(string)        -- string的字元個數
 substring(str, position [,length])        -- 從str的position開始,取length個字元
 replace(str ,search_str ,replace_str)    -- 在str中用replace_str替換search_str
 instr(string ,substring)    -- 返回substring首次在string中出現的位置
 concat(string [,...])    -- 連線字串
 charset(str)            -- 返回字串字符集
 lcase(string)            -- 轉換成小寫
 left(string, length)    -- 從string2中的左邊起取length個字元
 load_file(file_name)    -- 從檔案讀取內容
 locate(substring, string [,start_position])    -- 同instr,但可指定開始位置
 lpad(string, length, pad)    -- 重複用pad加在string開頭,直到字串長度為length
 ltrim(string)            -- 去除前端空格
 repeat(string, count)    -- 重複count次
 rpad(string, length, pad)    --在str後用pad補充,直到長度為length
 rtrim(string)            -- 去除後端空格
 strcmp(string1 ,string2)    -- 逐字元比較兩字串大小
 
 -- 聚合函式
 count()
 sum();
 max();
 min();
 avg();
 group_concat()
 
 -- 其他常用函式
 md5();
 default();

相關文章