為什麼學習資料庫
1、崗位技能需求
2、現在的世界,得資料者得天下
3、存儲資料的方法
4、程式,網站中,大量資料如何長久儲存?
5、資料庫是幾乎軟體體系中最核心的一個存在。
什麼是資料庫
資料庫 ( DataBase , 簡稱DB )
概念 : 長期存放在計算機内,有組織,可共享的大量資料的集合,是一個資料 "倉庫"
作用 : 儲存,并能安全管理資料(如:增删改查等),減少備援...
資料庫總覽 :
- 關系型資料庫 ( SQL )
-
- MySQL , Oracle , SQL Server , SQLite , DB2 , ...
- 關系型資料庫通過外鍵關聯來建立表與表之間的關系
- 非關系型資料庫 ( NOSQL )
-
- Redis , MongoDB , ...
- 非關系型資料庫通常指資料以對象的形式存儲在資料庫中，而對象之間的關系通過每個對象自身的屬性來決定
什麼是DBMS
資料庫管理系統 ( DataBase Management System )
資料庫管理軟體 , 科學組織和存儲資料 , 高效地擷取和維護資料
為什麼要說這個呢?
因為我們要學習的MySQL應該算是一個資料庫管理系統.
MySQL簡介
概念 : 是現在流行的開源的,免費的 關系型資料庫
曆史 : 由瑞典MySQL AB 公司開發，目前屬于 Oracle 旗下産品。
特點 :
- 免費 , 開源資料庫
- 小巧 , 功能齊全
- 使用便捷
- 可運作于Windows或Linux作業系統
- 可适用于中小型甚至大型網站應用
官網 :
安裝MySQL
這裡建議大家使用壓縮版,安裝快,友善.不複雜.
軟體下載下傳
mysql5.7 64位下載下傳位址:
電腦是64位的就下載下傳使用64位版本的！
安裝步驟
1、下載下傳後得到zip壓縮包.
2、解壓到自己想要安裝到的目錄，本人解壓到的是D:\Environment\.19
3、添加環境變量：我的電腦->屬性->進階->環境變量
選擇PATH,在其後面添加: 你的mysql 安裝檔案下面的bin檔案夾
4、編輯 檔案 ,注意替換路徑位置
[mysqld]basedir=D:\Program Files\\datadir=D:\Program Files\\data\port=3306skip-grant-tables
5、啟動管理者模式下的CMD，并将路徑切換至mysql下的bin目錄，然後輸入mysqld –install (安裝mysql)
6、再輸入 mysqld --initialize-insecure --user=mysql 初始化資料檔案
7、然後再次啟動mysql 然後用指令 mysql –u root –p 進入mysql管理界面(密碼可為空)
8、進入界面後更改root密碼
update mysql.user set authentication_string=password('123456') where user='root'and Host = 'localhost';
9、重新整理權限
flush privileges;
10、修改 檔案删除最後一句skip-grant-tables
11、重新開機mysql即可正常使用
net stop mysqlnet start mysql
12、連接配接上測試出現以下結果就安裝好了
一步步去做 , 理論上是沒有任何問題的 .
如果您以前裝過,現在需要重裝,一定要将環境清理幹淨 .
好了,到這裡大家都裝好了,因為剛接觸,是以我們先不學習指令.
這裡給大家推薦一個工具 : SQLyog .
即便有了可視化工具,可是基本的DOS命名大家還是要記住!
SQLyog
可手動操作,管理MySQL資料庫的軟體工具
特點 : 簡潔 , 易用 , 圖形化
使用SQLyog管理工具自己完成以下操作 :
- 連接配接本地MySQL資料庫
- 建立MySchool資料庫
-
- 字段
- GradeID : int(11) , Primary Key (pk)
- GradeName : varchar(50)
- 資料庫名稱MySchool
- 建立資料庫表(grade)
在曆史記錄中可以看到相對應的資料庫操作的語句 .
連接配接資料庫
打開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? 指令關鍵詞 : 尋求幫助-- 表示注釋
結構化查詢語句分類
資料庫操作
指令行操作資料庫
建立資料庫 : create database [if not exists] 資料庫名;
删除資料庫 : drop database [if exists] 資料庫名;
檢視資料庫 : show databases;
使用資料庫 : use 資料庫名;
對比工具操作資料庫
學習方法：
- 對照SQLyog工具自動生成的語句學習
- 固定文法中的單詞需要記憶
-
mysql 大量資料 更改索引_MySql——來自狂神說Java - 賢賢賢sir
建立資料表
屬于DDL的一種，文法 :
create table [if not exists] `表名`( '字段名1' 列類型 [屬性][索引][注釋], '字段名2' 列類型 [屬性][索引][注釋], #... '字段名n' 列類型 [屬性][索引][注釋])[表類型][表字元集][注釋];
說明 : 反引号用于差別MySQL保留字與普通字元而引入的 (鍵盤esc下面的鍵).
資料值和列類型
列類型 : 規定資料庫中該列存放的資料類型
數值類型
字元串類型
日期和時間型數值類型
NULL值
- 了解為 "沒有值" 或 "未知值"
- 不要用NULL進行算術運算 , 結果仍為NULL
資料字段屬性
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
- 預設的
- 用于設定預設值
- 例如,性别字段,預設為"男" , 否則為 "女" ; 若無指定該列的值 , 則預設值為"男"的值
-- 目标 : 建立一個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 表名(
-- 省略一些代碼
-- Mysql注釋
-- 1. # 單行注釋
-- 2. /*...*/ 多行注釋
)ENGINE = MyISAM (or InnoDB)
-- 檢視mysql所支援的引擎類型 (表類型)
SHOW ENGINES;
MySQL的資料表的類型 : MyISAM , InnoDB , HEAP , BOB , CSV等...
常見的 MyISAM 與 InnoDB 類型：
經驗 ( 适用場合 ) :
- 适用 MyISAM : 節約空間及相應速度
- 适用 InnoDB : 安全性 , 事務處理及多使用者操作資料表
資料表的存儲位置
- MySQL資料表以檔案方式存放在磁盤中
-
- 包括表檔案 , 資料檔案 , 以及資料庫的選項檔案
- 位置 : Mysql安裝目錄\data\下存放資料表 . 目錄名對應資料庫名 , 該目錄下檔案名對應資料表 .
- 注意 :
-
- * . frm -- 表結構定義檔案
- * . MYD -- 資料檔案 ( data )
- * . MYI -- 索引檔案 ( index )
- InnoDB類型資料表隻有一個 *.frm檔案 , 以及上一級目錄的ibdata1檔案
- MyISAM類型資料表對應三個檔案 :
mysql 大量資料 更改索引_MySql——來自狂神說Java - 賢賢賢sir
設定資料表字元集
我們可為資料庫,資料表,資料列設定不同的字元集，設定方法 :
- 建立時通過指令來設定 , 如 : CREATE TABLE 表名()CHARSET = utf8;
- 如無設定 , 則根據MySQL資料庫配置檔案 中的參數設定
修改資料庫
修改表 ( ALTER TABLE )
修改表名 :ALTER TABLE 舊表名 RENAME AS 新表名
添加字段 : ALTER TABLE 表名 ADD字段名 列屬性[屬性]
修改字段 :
- ALTER TABLE 表名 MODIFY 字段名 列類型[屬性]
- ALTER TABLE 表名 CHANGE 舊字段名 新字段名 列屬性[屬性]
删除字段 : ALTER TABLE 表名 DROP 字段名
删除資料表
文法：DROP TABLE [IF EXISTS] 表名
- IF EXISTS為可選 , 判斷是否存在該資料表
- 如删除不存在的資料表會抛出錯誤
其他
1. 可用反引号(`)為辨別符(庫名、表名、字段名、索引、别名)包裹，以避免與關鍵字重名！中文也可以作為辨別符！
2. 每個庫目錄存在一個儲存目前資料庫的選項檔案。
3. 注釋：
單行注釋 # 注釋内容
多行注釋 /* 注釋内容 */
單行注釋 -- 注釋内容 (标準SQL注釋風格，要求雙破折号後加一空格符(空格、TAB、換行等))
4. 模式通配符：
_ 任意單個字元
% 任意多個字元，甚至包括零字元
單引号需要進行轉義 \'
5. CMD指令行内的語句結束符可以為 ";", "\G", "\g"，僅影響顯示結果。其他地方還是用分号結束。delimiter 可修改目前對話的語句結束符。
6. SQL對大小寫不敏感 (關鍵字)
7. 清除已有語句：\c
外鍵
外鍵概念
如果公共關鍵字在一個關系中是主關鍵字，那麼這個公共關鍵字被稱為另一個關系的外鍵。由此可見，外鍵表示了兩個關系之間的相關聯系。以另一個關系的外鍵作主關鍵字的表被稱為主表，具有此外鍵的表被稱為主表的從表。
在實際操作中，将一個表的值放入第二個表來表示關聯，所使用的值是第一個表的主鍵值(在必要時可包括複合主鍵值)。此時，第二個表中儲存這些值的屬性稱為外鍵(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;
DML語言
資料庫意義 ： 資料存儲、資料管理
管理資料庫資料方法：
- 通過SQLyog等管理工具管理資料庫資料
- 通過DML語句管理資料庫資料
DML語言 ：資料操作語言
- 用于操作資料庫對象中所包含的資料
- 包括 :
-
- INSERT (添加資料語句)
- UPDATE (更新資料語句)
- DELETE (删除資料語句)
添加資料
INSERT指令
文法：
INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值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...'該部分可省略 , 但添加的值務必與表結構,資料列,順序相對應,且數量一緻.
-- 一次插入多條資料
INSERT INTO grade(gradename) VALUES ('大三'),('大四');
練習題目
自己使用INSERT語句為課程表subject添加資料 . 使用到外鍵.
修改資料
update指令
文法：
UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHEREcondition];
注意 :
- column_name 為要更改的資料列
- value 為修改後的資料 , 可以為變量 , 具體指 , 表達式或者嵌套的SELECT結果
- condition 為篩選條件 , 如不指定則修改該表的所有列資料
where條件子句
可以簡單的了解為 : 有條件地從表中篩選資料
測試：
-- 修改年級資訊
UPDATE grade SET gradename = '高中' WHERE gradeid = 1;
删除資料
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 : 自增列依然從上一個自增資料基礎上開始 (存在檔案中,不會丢失)
DQL語言
DQL( Data Query Language 資料查詢語言 )
- 查詢資料庫資料 , 如SELECT語句
- 簡單的單表查詢或多表的複雜查詢和嵌套查詢
- 是資料庫語言中最核心,最重要的語句
- 使用頻率最高的語句
SELECT文法
SELECT [ALL | DISTINCT]
{* | table.* | [[as alias1][,[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 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傳回結果中包含 ' . ' , ' * ' 和括号等幹擾開發語言程式.
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;
連接配接查詢
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
自連接配接
/*
自連接配接
資料表與自身進行連接配接
需求:從一個包含欄目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'
排序和分頁
測試
/*============== 排序 ================
文法 : 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
子查詢
/*============== 子查詢 ================
什麼是子查詢?
在查詢語句中的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名學生的成績資訊(學号,姓名,分數)
使用子查詢,查詢郭靖同學所在的年級名稱
*/
常用函數
資料函數
SELECT ABS(-8); /*絕對值*/
SELECT CEILING(); /*向上取整*/
SELECT FLOOR(); /*向下取整*/
SELECT RAND(); /*随機數,傳回一個0-1之間的随機數*/
SELECT SIGN(0); /*符号函數: 負數傳回-1,正數傳回1,0傳回0*/
字元串函數
SELECT CHAR_LENGTH('狂神說堅持就能成功'); /*傳回字元串包含的字元數*/
SELECT CONCAT('我','愛','程式'); /*合并字元串,參數可以有多個*/
SELECT INSERT('我愛程式設計helloworld',1,2,'超級熱愛'); /*替換字元串,從某個位置開始替換某個長度*/
SELECT LOWER('KuangShen'); /*小寫*/
SELECT UPPER('KuangShen'); /*大寫*/
SELECT LEFT('hello,world',5); /*從左邊截取*/
SELECT RIGHT('hello,world',5); /*從右邊截取*/
SELECT REPLACE('狂神說堅持就能成功','堅持','努力'); /*替換字元串*/
SELECT SUBSTR('狂神說堅持就能成功',4,6); /*截取字元串,開始和長度*/
SELECT REVERSE('狂神說堅持就能成功'); /*反轉
-- 查詢姓周的同學,改成鄒
SELECT REPLACE(studentname,'周','鄒') AS 新名字
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 VERSION(); /*版本*/
SELECT USER(); /*使用者*/
聚合函數
| 函數名稱 | 描述 |
|---|---|
| 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;
題目：
-- 查詢不同課程的平均分,最高分,最低分
-- 前提:根據不同的課程進行分組
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是從資料表中的>字段直接進行的篩選的
*/
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,'kuangshen','123456'),(2,'qinjiang','456789')
如果我們要對pwd這一列資料進行加密，文法是：
update testmd5 set pwd = md5(pwd);
如果單獨對某個使用者(如kuangshen)的密碼加密：
INSERT INTO testmd5 VALUES(3,'kuangshen2','123456')
update testmd5 set pwd = md5(pwd) where name = 'kuangshen2';
插入新的資料自動加密
INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));
查詢登入使用者資訊(md5對比使用，檢視使用者輸入加密後的密碼進行比對)
SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');
小結
-- ================ 内置函數 ================
-- 數值函數
abs(x) -- 絕對值 abs() = 10
format(x, d) -- 格式化千分位數值 format(1234567.456, 2) = 1,234,567.46
ceil(x) -- 向上取整 ceil() = 11
floor(x) -- 向下取整 floor () = 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();
事務
什麼是事務
- 事務就是将一組SQL語句放在同一批次内去執行
- 如果一個SQL語句出錯,則該批次内的所有SQL都将被取消執行
- MySQL事務處理隻支援InnoDB和BDB資料表類型
事務的ACID原則 百度 ACID
原子性(Atomic)
- 整個事務中的所有操作，要麼全部完成，要麼全部不完成，不可能停滞在中間某個環節。事務在執行過程中發生錯誤，會被復原(ROLLBACK)到事務開始前的狀态，就像這個事務從來沒有執行過一樣。
一緻性(Consist)
- 一個事務可以封裝狀态改變(除非它是一個隻讀的)。事務必須始終保持系統處于一緻的狀态，不管在任何給定的時間并發事務有多少。也就是說：如果事務是并發多個，系統也必須如同串行事務一樣操作。其主要特征是保護性和不變性(Preserving an Invariant)，以轉賬案例為例，假設有五個賬戶，每個賬戶餘額是100元，那麼五個賬戶總額是500元，如果在這個5個賬戶之間同時發生多個轉賬，無論并發多少個，比如在A與B賬戶之間轉賬5元，在C與D賬戶之間轉賬10元，在B與E之間轉賬15元，五個賬戶總額也應該還是500元，這就是保護性和不變性。
隔離性(Isolated)
- 隔離狀态執行事務，使它們好像是系統在給定時間内執行的唯一操作。如果有兩個事務，運作在相同的時間内，執行相同的功能，事務的隔離性将確定每一事務在系統中認為隻有該事務在使用系統。這種屬性有時稱為串行化，為了防止事務操作間的混淆，必須串行化或序列化請求，使得在同一時間僅有一個請求用于同一資料。
持久性(Durable)
- 在事務完成以後，該事務對資料庫所作的更改便持久的儲存在資料庫之中，并不會被復原。
基本文法
-- 使用set語句來改變自動送出模式
SET autocommit = 0; /*關閉*/
SET autocommit = 1; /*開啟*/
-- 注意:
--- 1.MySQL中預設是自動送出
--- 2.使用事務時應先關閉自動送出
-- 開始一個事務,标記事務的起始點
START TRANSACTION
-- 送出一個事務給資料庫
COMMIT
-- 将事務復原,資料回到本次事務的初始狀态
ROLLBACK
-- 還原MySQL資料庫的自動送出
SET autocommit =1;
-- 儲存點
SAVEPOINT 儲存點名稱 -- 設定一個事務儲存點
ROLLBACK TO SAVEPOINT 儲存點名稱 -- 復原到儲存點
RELEASE SAVEPOINT 儲存點名稱 -- 删除儲存點
測試
/*
課堂測試題目
A線上買一款價格為500元商品,網上銀行轉賬.
A的銀行卡餘額為2000,然後給商家B支付500.
商家B一開始的銀行卡餘額為10000
建立資料庫shop和建立表account并插入2條資料
*/
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
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
INSERT INTO account (`name`,`cash`)
VALUES('A',),('B',)
-- 轉賬實作
SET autocommit = 0; -- 關閉自動送出
START TRANSACTION; -- 開始一個事務,标記事務的起始點
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 送出事務
# rollback;
SET autocommit = 1; -- 恢複自動送出
索引
索引的作用
- 提高查詢速度
- 確定資料的唯一性
- 可以加速表和表之間的連接配接 , 實作表與表之間的參照完整性
- 使用分組和排序子句進行資料檢索時 , 可以顯著減少分組和排序的時間
- 全文檢索字段進行搜尋優化.
分類
- 主鍵索引 (Primary Key)
- 唯一索引 (Unique)
- 正常索引 (Index)
- 全文索引 (FullText)
主鍵索引
主鍵 : 某一個屬性組能唯一辨別一條記錄
特點 :
- 最常見的索引類型
- 確定資料記錄的唯一性
- 确定特定資料記錄在資料庫中的位置
唯一索引
作用 : 避免同一個表中某資料列中的值重複
與主鍵索引的差別
CREATE TABLE `Grade`(
`GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
-- 或 UNIQUE KEY `GradeID` (`GradeID`)
)
正常索引
作用 : 快速定位特定資料
注意 :
- index 和 key 關鍵字都可以設定正常索引
- 應加在查詢找條件的字段
- 不宜添加太多正常索引,影響資料的插入,删除和修改操作
CREATE TABLE `result`(
-- 省略一些代碼
INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 建立表時添加
)
-- 建立後添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);
全文索引
百度搜尋：全文索引
作用 : 快速定位特定資料
注意 :
- 隻能用于MyISAM類型的資料表
- 隻能用于CHAR , VARCHAR , TEXT資料列類型
- 适合大型資料集
/*
#方法一：建立表時
CREATE TABLE 表名 (
字段名1 資料類型 [完整性限制條件…],
字段名2 資料類型 [完整性限制條件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(長度)] [ASC |DESC])
);
#方法二：CREATE在已存在的表上建立索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(長度)] [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上建立索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(長度)] [ASC |DESC]) ;
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主鍵索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#顯示索引資訊: SHOW INDEX FROM student;
*/
/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
/*EXPLAIN : 分析SQL語句執行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
/*使用全文索引*/
-- 全文搜尋通過 MATCH() 函數完成。
-- 搜尋字元串作為 against() 的參數被給定。搜尋以忽略字母大小寫的方式執行。對于表中的每個記錄行，MATCH() 傳回一個相關性值。即，在搜尋字元串與記錄行在 MATCH() 清單中指定的列的文本之間的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
/*
開始之前，先說一下全文索引的版本、存儲引擎、資料類型的支援情況
MySQL 5.6 以前的版本，隻有 MyISAM 存儲引擎支援全文索引；
MySQL 5.6 及以後的版本，MyISAM 和 InnoDB 存儲引擎均支援全文索引;
隻有字段的資料類型為 char、varchar、text 及其系列才可以建全文索引。
測試或使用全文索引時，要先看一下自己的 MySQL 版本、存儲引擎和資料類型是否支援全文索引。
*/
拓展：測試索引
建表app_user：
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 UPDATECURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app使用者表'
批量插入資料：100w
DROP FUNCTION IF EXISTS mock_data;
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@', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();
索引效率測試
無索引
SELECT * FROM app_user WHERE name = '使用者9999'; -- 檢視耗時
SELECT * FROM app_user WHERE name = '使用者9999';
SELECT * FROM app_user WHERE name = '使用者9999';
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '使用者9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 992759
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
建立索引
CREATE INDEX idx_app_user_name ON app_user(name);
測試普通索引
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '使用者9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_app_user_name
key: idx_app_user_name
key_len: 203
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '使用者9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '使用者9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '使用者9999';
1 row in set (0.00 sec)
索引準則
- 索引不是越多越好
- 不要對經常變動的資料加索引
- 小資料量的表建議不要加索引
- 索引一般應加在查找條件的字段
索引的資料結構
-- 我們可以在建立上述索引的時候，為其指定索引類型，分兩類
hash類型的索引：查詢單條快，範圍查詢慢
btree類型的索引：b+樹，層數越多，資料量指數級增長(我們就用它，因為innodb預設支援它)
-- 不同的存儲引擎支援的索引類型也不一樣
InnoDB 支援事務，支援行級别鎖定，支援 B-tree、Full-text 等索引，不支援 Hash 索引；
MyISAM 不支援事務，支援表級别鎖定，支援 B-tree、Full-text 等索引，不支援 Hash 索引；
Memory 不支援事務，支援表級别鎖定，支援 B-tree、Hash 等索引，不支援 Full-text 索引；
NDB 支援事務，支援行級别鎖定，支援 Hash 索引，不支援 B-tree、Full-text 等索引；
Archive 不支援事務，支援表級别鎖定，不支援 B-tree、Hash、Full-text 等索引；
使用者管理
使用SQLyog 建立使用者，并授予權限示範
基本指令
/* 使用者和權限管理 */ ------------------
使用者資訊表：mysql.user
-- 重新整理權限
FLUSH PRIVILEGES
-- 增加使用者 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 使用者名 IDENTIFIED BY [PASSWORD] 密碼(字元串)
- 必須擁有mysql資料庫的全局CREATE USER權限，或擁有INSERT權限。
- 隻能建立使用者，不能賦予權限。
- 使用者名，注意引号：如 'user_name'@''
- 密碼也需引号，純數字密碼也要加引号
- 要在純文字中指定密碼，需忽略PASSWORD關鍵詞。要把密碼指定為由PASSWORD()函數傳回的混編值，需包含關鍵字PASSWORD
-- 重命名使用者 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
-- 設定密碼
SET PASSWORD = PASSWORD('密碼') -- 為目前使用者設定密碼
SET PASSWORD FOR 使用者名 = PASSWORD('密碼') -- 為指定使用者設定密碼
-- 删除使用者 DROP USER kuangshen2
DROP USER 使用者名
-- 配置設定權限/添加使用者
GRANT 權限清單 ON 表名 TO 使用者名 [IDENTIFIED BY [PASSWORD] 'password']
- all privileges 表示所有權限
- *.* 表示所有庫的所有表
- 庫名.表名 表示某庫下面的某表
-- 檢視權限 SHOW GRANTS FOR [email protected];
SHOW GRANTS FOR 使用者名
-- 檢視目前使用者權限
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
-- 撤消權限
REVOKE 權限清單 ON 表名 FROM 使用者名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 使用者名 -- 撤銷所有權限
權限解釋
-- 權限清單
ALL [PRIVILEGES] -- 設定除GRANT OPTION之外的所有簡單權限
ALTER -- 允許使用ALTER TABLE
ALTER ROUTINE -- 更改或取消已存儲的子程式
CREATE -- 允許使用CREATE TABLE
CREATE ROUTINE -- 建立已存儲的子程式
CREATE TEMPORARY TABLES -- 允許使用CREATE TEMPORARY TABLE
CREATE USER -- 允許使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW -- 允許使用CREATE VIEW
DELETE -- 允許使用DELETE
DROP -- 允許使用DROP TABLE
EXECUTE -- 允許使用者運作已存儲的子程式
FILE -- 允許使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX -- 允許使用CREATE INDEX和DROP INDEX
INSERT -- 允許使用INSERT
LOCK TABLES -- 允許對您擁有SELECT權限的表使用LOCK TABLES
PROCESS -- 允許使用SHOW FULL PROCESSLIST
REFERENCES -- 未被實施
RELOAD -- 允許使用FLUSH
REPLICATION CLIENT -- 允許使用者詢問從屬伺服器或主伺服器的位址
REPLICATION SLAVE -- 用于複制型從屬伺服器(從主伺服器中讀取二進制日志事件)
SELECT -- 允許使用SELECT
SHOW DATABASES -- 顯示所有資料庫
SHOW VIEW -- 允許使用SHOW CREATE VIEW
SHUTDOWN -- 允許使用mysqladmin shutdown
SUPER -- 允許使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL語句，mysqladmin debug指令；允許您連接配接(一次)，即使已達到max_connections。
UPDATE -- 允許使用UPDATE
USAGE -- “無權限”的同義詞
GRANT OPTION -- 允許授予權限
/* 表維護 */
-- 分析和存儲表的關鍵字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 檢查一個或多個表是否有錯誤
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理資料檔案的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
MySQL備份
資料庫備份必要性
MySQL資料庫備份方法
- mysqldump備份工具
- 資料庫管理工具,如SQLyog
- 直接拷貝資料庫檔案和相關配置檔案
mysqldump用戶端
作用 :
- 轉儲資料庫
- 搜集資料庫進行備份
- 将資料轉移到另一個SQL伺服器,不一定是MySQL伺服器
-- 導出
1. 導出一張表 -- mysqldump -uroot -p123456 school student >D:/
mysqldump -u使用者名 -p密碼 庫名 表名 > 檔案名(D:/)
2. 導出多張表 -- mysqldump -uroot -p123456 school student result >D:/
mysqldump -u使用者名 -p密碼 庫名 表1 表2 表3 > 檔案名(D:/)
3. 導出所有表 -- mysqldump -uroot -p123456 school >D:/
mysqldump -u使用者名 -p密碼 庫名 > 檔案名(D:/)
4. 導出一個庫 -- mysqldump -uroot -p123456 -B school >D:/
mysqldump -u使用者名 -p密碼 -B 庫名 > 檔案名(D:/)
可以-w攜帶備份條件
-- 導入
1. 在登入mysql的情況下：-- source D:/
source 備份檔案
2. 在不登入的情況下
mysql -u使用者名 -p密碼 庫名 < 備份檔案
規範化資料庫設計
為什麼需要資料庫設計
當資料庫比較複雜時我們需要設計資料庫
糟糕的資料庫設計 :
- 資料備援,存儲空間浪費
- 資料更新和插入的異常
- 程式性能差
良好的資料庫設計 :
- 節省資料的存儲空間
- 能夠保證資料的完整性
- 友善進行資料庫應用系統的開發
軟體項目開發周期中資料庫設計 :
- 需求分析階段: 分析客戶的業務和資料處理需求
- 概要設計階段:設計資料庫的E-R模型圖 , 确認需求資訊的正确和完整.
設計資料庫步驟
- 收集資訊
-
- 與該系統有關人員進行交流 , 座談 , 充分了解使用者需求 , 了解資料庫需要完成的任務.
- 辨別實體[Entity]
- 辨別每個實體需要存儲的詳細資訊[Attribute]
- 辨別實體之間的關系[Relationship]
三大範式
問題 : 為什麼需要資料規範化?
不合規範的表設計會導緻的問題：
- 資訊重複
- 更新異常
- 插入異常
- 删除異常
三大範式
第一範式 (1st NF)
第一範式的目标是確定每列的原子性,如果每列都是不可再分的最小資料單元,則滿足第一範式
第二範式(2nd NF)
第二範式(2NF)是在第一範式(1NF)的基礎上建立起來的，即滿足第二範式(2NF)必須先滿足第一範式(1NF)。
第二範式要求每個表隻描述一件事情
第三範式(3rd NF)
如果一個關系滿足第二範式,并且除了主鍵以外的其他列都不傳遞依賴于主鍵列,則滿足第三範式.
第三範式需要確定資料表中的每一列資料都和主鍵直接相關，而不能間接相關。
規範化和性能的關系
為滿足某種商業目标 , 資料庫性能比規範化資料庫更重要
在資料規範化的同時 , 要綜合考慮資料庫的性能
通過在給定的表中添加額外的字段,以大量減少需要從中搜尋資訊所需的時間
通過在給定的表中插入計算列,以友善查詢
![mysql 設定 0、1 用什麼資料類型_什麼是MySQL資料庫？看這一篇幹貨文章就夠了！...[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)