一、Mysql介紹
1.1 概述
資料庫存儲資料的倉庫
本質上是一個檔案系統，還是以檔案的方式存在伺服器的電腦上的。
所有的關系型資料庫都可以使用通用的SQL語句進行管理
1.2 基本操作MySQL服務啟動net start mysql : 啟動mysql的服務
net stop mysql:關閉mysql服務手動。
cmd--> services.msc 打開服務的視窗
使用管理者打開cmdMySQL登入mysql -uroot -p密碼
mysql -hip -uroot -p連接配接目标的密碼
mysql --host=ip --user=root --password=連接配接目标的密碼MySQL退出exit
quit
二、SQL介紹
2.1 SQL概述
Structured Query Language：結構化查詢語言 定義了操作所有關系型資料庫的規則。每一種資料庫操作的方式存在不一樣的地方，稱為“方言”。
SQL通用文法SQL 語句可以單行或多行書寫，以分号結尾。
可使用空格和縮進來增強語句的可讀性。
MySQL 資料庫的 SQL 語句不區分大小寫，關鍵字建議使用大寫。
包括3種注釋單行注釋: -- 注釋内容 或 # 注釋内容(mysql 特有)
多行注釋:
SQL分類DDL(Data Definition Language)資料定義語言 用來定義資料庫對象：資料庫，表，列等。關鍵字：create, drop,alter 等
DML(Data Manipulation Language)資料操作語言 用來對資料庫中表的資料進行增删改。關鍵字：insert, delete, update 等
DQL(Data Query Language)資料查詢語言 用來查詢資料庫中表的記錄(資料)。關鍵字：select, where 等
DCL(Data Control Language)資料控制語言(了解) 用來定義資料庫的通路權限和安全級别，及建立使用者。關鍵字：GRANT， REVOKE 等
2.2 DDL
操作資料庫
C(Create):建立#建立資料庫：
create database 資料庫名稱;
#建立資料庫，判斷不存在，再建立：
create database if not exists 資料庫名稱;
#建立資料庫，并指定字元集
create database 資料庫名稱 character set 字元集名;
R(Retrieve)：查詢#查詢所有資料庫的名稱:
show databases;
#查詢某個資料庫的字元集:查詢某個資料庫的建立語句
show create database 資料庫名稱;
U(Update):修改#修改資料庫的字元集
alter database 資料庫名稱 character set 字元集名稱;
D(Delete):删除#删除資料庫
drop database 資料庫名稱;
#判斷資料庫存在，存在再删除
drop database if exists 資料庫名稱;
使用資料庫#查詢目前正在使用的資料庫名稱
select database();
#使用資料庫
use 資料庫名稱;
操作表
C(Create):建立
文法create table 表名(
列名1 資料類型1,
列名2 資料類型2,
....
列名n 資料類型n
);
最後一列，不需要加逗号(,)
資料類型int：整數類型
double:小數類型
date:日期，隻包含年月日，yyyy-MM-dd
datetime:日期，包含年月日時分秒 yyyy-MM-dd HH:mm:ss
timestamp:時間錯類型 包含年月日時分秒 yyyy-MM-dd HH:mm:ss如果不給這個字段指派，或指派為null，則預設使用目前的系統時間，來自動指派
varchar：字元串#建立表
create table student(
id int,
name varchar(32),
age int ,
score double(4,1),
birthday date,
insert_time timestamp
);
# 複制表：
create table 表名 like 被複制的表名;
R(Retrieve)：查詢#查詢某個資料庫中所有的表名稱
show tables;
#查詢表結構
desc 表名;
U(Update):修改#修改表名
alter table 表名 rename to 新的表名;
#修改表的字元集
alter table 表名 character set 字元集名稱;
#添加一列
alter table 表名 add 列名 資料類型;
#修改列名稱 類型
alter table 表名 change 列名 新列别 新資料類型;
alter table 表名 modify 列名 新資料類型;
#删除列
alter table 表名 drop 列名;
D(Delete):删除drop table 表名;
drop table if exists 表名 ;
2.3 DML
添加資料
文法insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
注意列名和值要一一對應
如果表名後，不定義列名，則預設給所有列添加值insert into 表名 values(值1,值2,...值n);
除了數字類型，其他類型需要使用引号(單雙都可以)引起來
删除資料
文法delete from 表名 [where 條件]
注意如果不加條件，則删除表中所有記錄
如果要删除所有記錄delete from 表名; -- 不推薦使用。有多少條記錄就會執行多少次删除操作
TRUNCATE TABLE 表名; -- 推薦使用，效率更高 先删除表，然後再建立一張一樣的表。
修改資料
文法update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 條件];
注意如果不加任何條件，則修改表中所有記錄
2.4 DQL
文法select
字段清單
from
表名清單
where
條件清單
group by
分組字段
having
分組之後的條件
order by
排序
limit
分頁限定
基礎查詢
多字段的查詢select 字段名1，字段名2... from 表名；
去除重複distinct
計算列一般可以使用四則運算計算一些列的值。(一般隻會進行數值型的計算)
ifnull(表達式1,表達式2)：null參與的運算，計算結果都為null表達式1：哪個字段需要判斷是否為null
如果該字段為null後的替換值。
起别名as #as可以省略
條件查詢where子句後跟條件
運算符占位符:
_:單個任意字元
%：多個任意字元
> < <= >= = <>
BETWEEN...AND
IN( 集合)
LIKE：模糊查詢
IS NULL
AND 或 &&
OR 或 ||
NOT或 !
排序查詢
文法order by 子句
#order by 排序字段1 排序方式1 ， 排序字段2 排序方式2...
排序方式ASC：升序，預設的
DESC：降序如果有多個排序條件，則目前邊的條件值一樣時，才會判斷第二條件
聚合函數
含義
将一列資料作為一個整體，進行縱向的計算
常用函數#計算個數
count
#一般選擇非空的列：主鍵
#count(*)
#計算最大值
max
#計算最小值
min
#計算和
sum
#計算平均值
avg
注意
聚合函數的計算，注意排除null值
解決方法選擇不包含非空的列進行計算
IFNULL函數
分組查詢
文法group by 分組字段；
注意分組之後查詢的字段：分組字段、聚合函數-
where 和 having 的差別where在分組之前進行限定，如果不滿足條件，則不參與分組。having在分組之後進行限定，如果不滿足結果，則不會被查詢出來
where後不可以跟聚合函數，having可以進行聚合函數的判斷。
分頁查詢
文法limit 開始的索引,每頁查詢的條數;
公式
開始的索引 = (目前的頁碼 - 1) * 每頁顯示的條數
注意limit 是一個MySQL"方言"
2.5 DCL
管理使用者#添加使用者：
CREATE USER '使用者名'@'主機名' IDENTIFIED BY '密碼';
#删除使用者：
DROP USER '使用者名'@'主機名';
#修改使用者密碼：
UPDATE USER SET PASSWORD = PASSWORD('新密碼') WHERE USER = '使用者名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
SET PASSWORD FOR '使用者名'@'主機名' = PASSWORD('新密碼');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
#查詢使用者
#1. 切換到mysql資料庫
USE myql;
#2. 查詢user表
SELECT * FROM USER;
權限管理#查詢權限
SHOW GRANTS FOR '使用者名'@'主機名';
SHOW GRANTS FOR 'lisi'@'%';
#授予權限
grant 權限清單 on 資料庫名.表名 to '使用者名'@'主機名';
-- 給張三使用者授予所有權限，在任意資料庫任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
#撤銷權限
revoke 權限清單 on 資料庫名.表名 from '使用者名'@'主機名';
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
三、限制
作用
對表中的資料進行限定，保證資料的正确性、有效性和完整性
3.1 主鍵限制
作用
主鍵限制(primary key)，能夠唯一确定表中的一條記錄。我們通過給某個字段添加該限制，就可以使得該字段不重複且不為空。
文法#在建立表時，添加主鍵限制
create table stu(
id INT primary key,-- 給id添加主鍵限制
name VARCHAR(20)
);
#聯合主鍵
CREATE TABLE stu (
id INT,
name VARCHAR(20),
PRIMARY KEY(id, name) -- 此處字段id和name一同作為主鍵，聯合主鍵要求每個字段加起來不同即可(無需每個字段都不同)
);
#删除主鍵
ALTER TABLE stu DROP PRIMARY KEY;
#建立完表後，添加主鍵
ALERT TABLE stu ADD PRIMARY KEY (id);
#或
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
注意主鍵非空且唯一
主鍵就是表中記錄的唯一辨別
3.2 自增限制
作用
如果某一列是數值類型的，使用 auto_increment 可以來完成值得自動增長
文法#在建立表時，添加主鍵限制，并且完成主鍵自增長
create table stu(
id int primary key auto_increment,-- 給id添加主鍵限制,自增限制
name varchar(20)
);
#删除自動增長
ALTER TABLE stu MODIFY id INT;
#添加自動增長
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
注意
自增限制一般與主鍵搭配使用
3.3 唯一限制
作用
唯一限制修飾的字段不可以重複
文法#在建立表時，添加主鍵限制，并且完成主鍵自增長,唯一限制
create table stu(
id int primary key auto_increment UNIQUE,-- 給id添加主鍵限制,自增限制，唯一限制
name varchar(20)
);
#或
CREATE TABLE stu (
id int primary key auto_increment，
name VARCHAR(20),
UNIQUE(id, name)
);
#删除唯一限制
ALTER TABLE stu DROP INDEX phone_number;
#在建立表後，添加唯一限制
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
注意mysql中，唯一限制限定的列的值可以有多個null
3.4 非空限制
作用
非空限制(not null)限制的字段，值不能為null
文法#建立表時添加限制
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name為非空
);
#删除name的非空限制
ALTER TABLE stu MODIFY NAME VARCHAR(20);
#建立表完後，添加非空限制
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
3.5 預設限制
作用
我們插入字段時沒有傳值，就會使用預設值
文法#建立表時添加限制
CREATE TABLE stu (
id INT DEFAULT 10,
name VARCHAR(20),
);
3.6 外鍵限制
作用
外鍵限制(foreign key)讓表于表産生關系，進而保證資料的正确性。
文法#在建立表時，可以添加外鍵
create table 表名(
....
外鍵列
constraint 外鍵名稱 foreign key (外鍵列名稱) references 主表名稱(主表列名稱)
);
#删除外鍵
ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;
#建立表之後，添加外鍵
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名稱) REFERENCES 主表名稱(主表列名稱);
注意主表中沒有的資料，在副表中是不可以使用的
主表中的資料被副表引用時，是不可以删除的
3.7 級聯操作
文法#添加級聯操作
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱
FOREIGN KEY (外鍵字段名稱) REFERENCES 主表名稱(主表列名稱) ON UPDATE CASCADE ON DELETE CASCADE ;
#分類：
#級聯更新
ON UPDATE CASCADE
#級聯删除
ON DELETE CASCADE
四、範式
概念
設計關系資料庫時，遵從不同的規範要求，設計出合理的關系型資料庫，這些不同的規範要求被稱為不同的範式，各種範式呈遞次規範，越高的範式資料庫備援越小。目前關系資料庫有六種範式：第一範式(1NF)、第二範式(2NF)、第三範式(3NF)、巴斯-科德範式(BCNF)、第四範式(4NF)和第五範式(5NF，又稱完美範式)
4.1 第一範式
第一範式(1NF)：每一列都是不可分割的原子資料項。即，字段還可以拆分的，就不滿足第一範式
4.2 第二範式
第二範式(2NF)：在1NF的基礎上，非碼屬性必須完全依賴于碼(在1NF基礎上消除非主屬性對主碼的部分函數依賴)。即，除主鍵外的每一列，都必須完全依賴于主鍵，如果出現不完全依賴，則隻可能發生在聯合主鍵的情況下不滿足第二範式的例子CREATE TABLE order(
product_id INT,
customer_id INT,
product_name VARCHAR(20),
customer_name VARCHAR(20),
PRIMARY KEY(product_id, customer_id)
);
滿足第二範式的例子CREATE TABLE order(
id INT PRIMARY KEY,
product_id INT,
customer_id INT
);
CREATE TABLE product(
id INT PRIMARY KEY,
name VARCHAR(20)
);
CREATE TABLE customer(
id INT PRIMARY KEY,
name VARCHAR(20)
);
幾個概念函數依賴：A-->B,如果通過A屬性(屬性組)的值，可以确定唯一B屬性的值。則稱B依賴于A 例如：學号-->姓名。 (學号，課程名稱) --> 分數
完全函數依賴：A-->B， 如果A是一個屬性組，則B屬性值得确定需要依賴于A屬性組中所有的屬性值。 例如：(學号，課程名稱) --> 分數
部分函數依賴：A-->B， 如果A是一個屬性組，則B屬性值得确定隻需要依賴于A屬性組中某一些值即可。 例如：(學号，課程名稱) -- > 姓名
傳遞函數依賴：A-->B, B -- >C . 如果通過A屬性(屬性組)的值，可以确定唯一B屬性的值，在通過B屬性(屬性組)的值可以确定唯一C屬性的值，則稱 C 傳遞函數依賴于A 例如：學号-->系名，系名-->系主任
碼：如果在一張表中，一個屬性或屬性組，被其他所有屬性所完全依賴，則稱這個屬性(屬性組)為該表的碼 例如：該表中碼為：(學号，課程名稱)主屬性：碼屬性組中的所有屬性
非主屬性：除過碼屬性組的屬性
4.3 第三範式
第三範式(3NF)：在2NF基礎上，任何非主屬性不依賴于其它非主屬性(在2NF基礎上消除傳遞依賴),即除主鍵外的其他列之間不能有傳遞依賴關系不滿足第三範式的例子CREATE TABLE order(
id INT PRIMARY KEY,
product_id INT,
customer_id INT,
customer_phone INT
);
#此處customer_phone又依賴于customer_id，存在傳遞依賴關系，不滿足第三範式
滿足第三範式的例子CREATE TABLE order(
id INT PRIMARY KEY,
product_id INT,
customer_id INT
);
CREATE TABLE customer(
id INT PRIMARY KEY,
name VARCHAR(20),
phone INT
);
五、多表查詢
5.1 概覽
最下面兩種語句MySQL中不适用，改為分别左右查詢之後進行union操作
5.2 實際操作
建表語句CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, empno INT NOT NULL, PRIMARY KEY (`id`), KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_dept(deptName,address) VALUES('華山','華山');
INSERT INTO t_dept(deptName,address) VALUES('丐幫','洛陽');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武當','武當山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明頂');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('風清揚',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('嶽不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐沖',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('喬峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('滅絕師太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('張三豐',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('張無忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韋小寶',18,NULL,100010);
JOIN查詢
笛卡爾積SELECT * FROM t_dept, t_emp;
t_dept共10條記錄，t_emp共6條記錄。兩表共同查詢後共60條記錄
内連接配接SELECT * FROM t_emp a INNER JOIN t_dept b ON a.deptId = b.id;
隐式内連接配接：使用where條件消除無用資料SELECT * FROM t_emp a , t_dept b WHERE a.deptId = b.id;
左外連接配接SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId = b.id;
右外連接配接SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id;
左外連接配接取左表的獨有部分(即，查詢沒有門派的人)SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId = b.id WHERE a.deptId IS NULL;
右外連接配接取右表的獨有部分(即，查詢沒有人的門派)SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id WHERE a.deptId IS NULL;
注意：判斷字段是否為NULL時，不能使用’=’
因為= NULL的結果不會報錯，但是結果永遠為false。是以必須使用IS NULL來進行判空
全外連接配接MySQL不支援全外連接配接，要查詢兩個表的全集，需要合并兩個查詢結果，是以要使用 UNION 關鍵字
查詢兩表獨有
5.3 子查詢
概念
查詢中嵌套查詢，稱嵌套查詢為子查詢。
子查詢不同情況子查詢的結果是單行單列的查詢可以作為條件，使用運算符去判斷。 運算符： > >= < <= =#查詢員工工資小于平均工資的人
SELECT * FROM emp WHERE emp.salary
子查詢的結果是多行單列的子查詢可以作為條件，使用運算符in來判斷#查詢'财務部'和'市場部'所有的員工資訊
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财務部' OR NAME = '市場部');
子查詢的結果是多行多列的子查詢可以作為一張虛拟表參與查詢#查詢員工入職日期是2011-11-11日之後的員工資訊和部門資訊
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
六、事務
概念
事務是一個最小的不可分割的單元，事務能夠保證一個業務的完整性
多條sql語句要麼同時成功，要麼同時失敗，這時就要用到事務
6.1 操作#開啟事務
start transaction;
#復原
rollback;
#送出
commit;
注意MySQL資料庫中事務預設自動送出， Oracle資料庫預設是手動送出事務
事務送出的兩種方式自動送出，一條DML(增删改)語句會自動送出一次事務
手動送出，需要先開啟事務，再送出
修改事務的預設送出方式檢視事務的預設送出方式：SELECT @@autocommit; -- 1 代表自動送出 0 代表手動送出
修改預設送出方式： set @@autocommit = 0;
6.2 事務的特征原子性(atomicity)：事務是最小操作的機關，不可再分，同一事務中的sql語句要麼同時成功，要麼同時失敗
一緻性(consistency)：事務操作前後，資料總量不變。
隔離性(isolation)：多個事務之間。互相獨立。
持久性(durability)：事物一旦結束(commit)，就不可傳回(rollback)，即持久化
6.2 事務的隔離級别
概念：多個事務之間隔離的，互相獨立的。但是如果多個事務操作同一批資料，則會引發一些問題，設定不同的隔離級别就可以解決這些問題
事務的并發問題髒讀：：一個事務，讀取到另一個事務中沒有送出的資料事務A讀取到了事務B沒有Commit的資料，然後B復原操作，那麼A讀取到的資料是髒資料
不可重複讀：在同一個事務中，兩次讀取到的資料不一樣。事務 A 多次讀取同一資料，事務B在事務A多次讀取的過程中，對資料作了更新并送出，導緻事務A多次讀取同一資料時，結果不一緻
幻讀：一個事務操作(DML)資料表中所有記錄，另一個事務添加了一條資料，第一個事務查詢批量處理之後，查詢到了另一個事務新增的資料。系統管理者A将資料庫中所有學生的成績從具體分數改為ABCDE等級，但是系統管理者B就在這個時候插入了一條具體分數的記錄，當系統管理者A改結束後發現還有一條記錄沒有改過來，就好像發生了幻覺一樣注：不可重複讀側重于修改，幻讀側重于新增或删除
事務的隔離級别讀未送出 read uncommitted
讀已送出 read committed
可以重複讀 repeatable read
串行化 serializable事務隔離級别髒讀不可重複讀幻讀讀未送出(read uncommitted)未解決未解決未解決
讀已送出 (read committed)解決未解決未解決
可以重複讀 (repeatable read)解決解決未解決
串行化 (serializable)解決解決解決注：隔離級别從小到大安全性越來越高，但是效率越來越低
解決不可重複讀的問題隻需鎖住滿足條件的行，解決幻讀需要鎖表
操作#資料庫查詢隔離級别,版本 5.x
select @@tx_isolation;
#資料庫查詢隔離級别,版本 8.0
SELECT @@global.transaction_isolation;
#資料庫設定隔離級别：
set global transaction isolation level 級别字元串;