MySQL

數(shù)據(jù)庫DataBase

執(zhí)行順序是 on > where > having
on 是對 join的兩張表進行過濾
where 是對單個表進行約束 再生成一個表
having 對單個表進行過濾而不是生成表 可以跟聚合函數(shù) 因為是對已存在表的過濾
MySQL屬于關(guān)系型數(shù)據(jù)庫
表與表之間是互相關(guān)聯(lián)的
MySQL由RDBMS和生成的數(shù)據(jù)庫文件組成
innoDB MyISAM 都是表引擎
innoDB 屬于索引組織表 事務(wù)安全(要么不做要么做完整) 外鍵 行級鎖
MyISAM 屬于堆表 不支持事務(wù) 不支持外鍵 表級鎖
在寫多讀少的應(yīng)用中InnoDB插入性能更穩(wěn)定 且高并發(fā)因為行級鎖 InnoDB更有優(yōu)勢
在讀多寫少的應(yīng)用中MyISAM讀取性能更穩(wěn)定 但高并發(fā)因為表級鎖可能不如InnoDB

數(shù)據(jù)庫的優(yōu)點
  • 持久化存儲
  • 讀寫速度快
  • 保證數(shù)據(jù)的有效性
  • 對程序的支持性好 容易擴展

數(shù)據(jù)庫的本質(zhì)是一個文件系統(tǒng)
根據(jù)特殊的算法數(shù)據(jù)庫的性能要比普通文件更有優(yōu)勢
在linux下mysql數(shù)據(jù)庫文件儲存在/var/lib/mysql
必須切換到管理員身份才能訪問/var/lib/mysql

數(shù)據(jù)庫中以表為組織單位儲存數(shù)據(jù)
表中的列稱為字段
表中的行稱為記錄
能唯一標記記錄的字段稱為主鍵

DBMS

數(shù)據(jù)庫管理系統(tǒng)
DBMS 是一個程序用來管理數(shù)據(jù)庫
數(shù)據(jù)庫又分為 關(guān)系型數(shù)據(jù)庫 非關(guān)系型數(shù)據(jù)庫
數(shù)據(jù)庫僅僅是數(shù)據(jù)庫文件需要通過DBMS來增刪改查和管理
而MySQL 屬于C/S模型 所以分為 RDBMS客戶端和RDBMS服務(wù)器
客戶端與服務(wù)器之間通過SQL語言進行控制

關(guān)系型數(shù)據(jù)庫:
  • Oracle 大型項目中使用
  • MySQL WEB中廣泛使用的關(guān)系型數(shù)據(jù)庫
  • MS SQL Server 微軟的項目中使用
  • SQLite 輕量級數(shù)據(jù)庫 用于移動平臺

SQL

結(jié)構(gòu)化查詢語言
用于RDBMS_client與RDBMS_server之間的溝通
SQL是用來操作RDBMS的語言 當前的關(guān)系型數(shù)據(jù)庫都支持SQL
所以SQL可以操作 oracle mysql ms sql server sqlite

SQL語句主要分為:
  • DQL 數(shù)據(jù)查詢語言 用于對數(shù)據(jù)進行查詢
  • SML 數(shù)據(jù)操作語言 用于對數(shù)據(jù)進行增刪改
  • TPL 事務(wù)處理語言 用于對事務(wù)進行處理
  • DCL 數(shù)據(jù)控制語言 用于進行授權(quán)與權(quán)限回收
  • DDL 數(shù)據(jù)定義語言 用于進行數(shù)據(jù)庫和表的管理等
  • CCL 指針控制語言 通過控制指針完成表的操作

SQL是一門語言 專門用來操作關(guān)系型數(shù)據(jù)庫
SQL語句不區(qū)分大小寫
SQL語句結(jié)束時要輸入;結(jié)尾

服務(wù)器端和客戶端的安裝

安裝 sudo apt-get install mysql-server
啟動 sudo service mysql start
停止 sudo service mysql stop
重啟 sudo service mysql restart

安裝 sudo apt-get install mysql-client

mysql的配置文件

/etc/mysql/mysql.cnf加載的配置文件目錄

  • /etc/mysql/conf.d/
  • /etc/mysql/mysql.conf.d/

bind-address 表示服務(wù)器綁定的IP 默認為127.0.0.1
port 表示端口 默認為3306
datadir 表示數(shù)據(jù)庫目錄 默認為/var/lib/mysql
general_log_file 表示普通日志 默認為/var/log/mysql/mysql.log/mysql/mysql
log_error 表示錯誤日志 默認為/var/log/mysql/error.log/mysql/error

數(shù)據(jù)類型

創(chuàng)建數(shù)據(jù)庫時指定編碼和排序編碼
數(shù)據(jù)表默認使用數(shù)據(jù)庫的編碼格式
使用數(shù)據(jù)類型的原則: 夠用就行 盡量使用取值范圍小的 而不用大的 節(jié)省儲存空間

常用數(shù)據(jù)類型
  • 整數(shù): int bit
  • 小數(shù): decimal
  • 字符串: varchar(N) char(N)
  • 日期時間: date time datetime
  • 枚舉類型: enum
  • 集合類型: set
特別說明的類型

int 表示整數(shù) 如int(2)表示的是顯示寬度 不是限制個數(shù) 也不是最大數(shù)
decimal 表示浮點數(shù) 如decimal(5,2)表示共存5位數(shù) 小數(shù)占2位 整數(shù)占3位
char(N) 表示固定長度的字符串必須指定長度 如char(3)三個字符個數(shù) 填充"ab"會補充空格為"ab "
varchar(N) 表示可變長度的字符串必須指定長度 如varchar(3)最大字符個數(shù) 填充"ab"是就會存儲"ab"
text表示存儲大文本 當字符大于4000時推薦使用
圖片 音頻 視頻 不儲存在數(shù)據(jù)庫而是上傳到某個服務(wù)器 在表中存儲路徑

數(shù)值類型
類型 字節(jié)大小 有符號范圍(Signed) 無符號范圍(Unsigned)
TINYINT 1 -128 ~ 127 0 ~ 255
SMALLINT 2 -32768 ~ 32767 0 ~ 65535
MEDIUMINT 3 -8388608 ~ 8388607 0 ~ 16777215
INT/INTEGER 4 -2147483648 ~ 2147483647 0 ~ 4294967295
BIGINT 8 -9223372036854775808 ~ 9223372036854775807 0 ~ 18446744073709551615

默認數(shù)值類型是有符號的 定義無符號數(shù)值在類型后加上unsigned 并且 unsigned 必須寫在類型后
int unsigned tinyint unsigned

字符串
類型 字節(jié)大小 示例
CHAR(N) 0-255 類型:char(3) 輸入 'ab', 實際存儲為'ab ', 輸入'abcd' 實際存儲為 'abc'
VARCHAR(N) 0-255 類型:varchar(3) 輸 'ab',實際存儲為'ab', 輸入'abcd',實際存儲為'abc'
BLOB 0-65536 二進制且區(qū)分大小寫
TEXT 0-65535 大文本
日期時間類型
類型 字節(jié)大小 示例
DATE 4 '2020-01-01'
TIME 3 '12:29:59'
DATETIME 8 '2020-01-01 12:29:59'
YEAR 1 '2017'
TIMESTAMP 4 '1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC

如果需要系統(tǒng)自動填入日期或時間或者年份 在設(shè)置時間類型時默認值設(shè)置為 current_timestamp() 函數(shù)

約束

通過約束保證數(shù)據(jù)的穩(wěn)定可靠

  • 主鍵 primary key 物理上存儲的順序
  • 非空 not null 此字段不允許填寫空值
  • 增長 auto_increment 此字段的值自動增長
  • 唯一 unique 此字段的值不允許重復(fù)
  • 默認 default 指定此字段每個記錄的默認值
  • 外鍵 foreign key 約束為關(guān)系字段 表中字段中的值必須在其他表中字段的值的范圍內(nèi) 這樣的字段類型稱為外鍵

當為關(guān)系字段填寫值時 先到關(guān)聯(lián)的表中查詢此值是否存在 如果存在則填寫成功 如果不存在則填寫失敗并拋出異常 所以外鍵影響性能 可以在代碼階段通過邏輯避免需要使用外鍵約束的必要

MySQL的基本用法

連接和退出

鏈接數(shù)據(jù)庫 mysql -u root -p mysql -u 用戶名 -p 密碼
退出數(shù)據(jù)庫 exit quit Ctrl+D
use 數(shù)據(jù)庫名; 使用指定數(shù)據(jù)庫

顯示

show databases; 顯示所有數(shù)據(jù)庫
show create database 數(shù)據(jù)庫名; 顯示創(chuàng)建數(shù)據(jù)庫的信息
show tables; 顯示所有數(shù)據(jù)表
show create table 表名; 顯示創(chuàng)建數(shù)據(jù)表的信息
show character set 查看可用的字符集
show collation 查看可用的排序規(guī)則

select now(); 查看當前時間
select version(); 查看數(shù)據(jù)庫版本
select database(); 查看當前使用的數(shù)據(jù)庫
desc 表名; 查看數(shù)據(jù)表的結(jié)構(gòu) 顯示為字段 類型 約束

創(chuàng)建刪除數(shù)據(jù)庫

create database 數(shù)據(jù)庫名 charset=編碼 collate=排序編碼; 創(chuàng)建數(shù)據(jù)庫指定編碼及排序規(guī)則 表默認使用數(shù)據(jù)庫的編碼格式 不指定編碼默認使用latin1
drop database 數(shù)據(jù)庫名; 刪除數(shù)據(jù)庫

create database pythondb charset=utf8 collate=utf8_general_ci;
drop database pythondb;
修改數(shù)據(jù)庫屬性

alter database 數(shù)據(jù)庫名 charset=編碼; 修改數(shù)據(jù)庫的編碼格式
alter database 數(shù)據(jù)庫名 collate=排序編碼; 修改數(shù)據(jù)庫排序規(guī)則

alter database pythondb charset=latin1;
alter database pythondb collate=utf8_general_ci;
創(chuàng)建刪除數(shù)據(jù)表

create table 表名稱(字段 類型 約束[,字段 類型 約束]); 創(chuàng)建數(shù)據(jù)表并設(shè)置多個字段
drop table 表名; 刪除數(shù)據(jù)表

create table students(id int primary key, name varchar(10));
drop table students;
修改數(shù)據(jù)表屬性

alter table 表名 add 字段 類型 約束; 添加表中的字段
alter table 表名 change 原字段 新字段 類型 約束; 修改已經(jīng)存在的字段
alter table 表名 modify 字段 類型 約束; 修改已存在字段的類型和約束
alter table 表名 drop 字段; 刪除數(shù)據(jù)表中的字段

alter table students add name varchar(4) not null;
alter table students change age birthday datetime;
alter table students modify birthday date not null;
alter table students drop birthday;
查詢

使用select查詢時 填入的查詢字段的順序 決定了顯示的順序 where 類似于判斷 用于判斷范圍
select * from 表名; 查看數(shù)據(jù)表中的所有數(shù)據(jù)
select 字段[,字段] from 表名 where 條件; 查看滿足條件的某些字段的信息
select 字段 as 別名[,字段 as 別名] from 表名 where 條件; as 設(shè)置別名方便查看

select * from students;
select name, id from students where id =9;
select id as 序號, name as 姓名 from students where id>3;
插入

insert into 表名 values(記錄1, 記錄2, 記錄3); 向表中插入記錄
插入的字段屬于自動增長時可以填充 0 null default
插入的字段屬于枚舉時可以填充枚舉的 序號
使用全列插入時即使有默認值的字段也應(yīng)該填寫

insert into students values(0, "老王", "男");  
insert into students values(null, "老王", 1);
部分插入

部分插入時NOT NULL 的部分必須填充 沒有填充的部分根據(jù)默認值填充 沒有默認值的部分為NULL
insert into 表名 (字段1, 字段2) values(記錄1, 記錄2); 向表中插入部分記錄

insert into students (id, name) values(default, "老李");
多行插入

insert into 表名 values(記錄1, 記錄2), (記錄1, 記錄2); 一次插入多行
insert into 表名 (字段1, 字段2) values(記錄1, 記錄2), (記錄1, 記錄2); 多行部分插入

insert into students values(null, "小王"), (0, "小李");
insert into students (id , name) values(default, "小王"), (null, "小李");
修改

where 類似于判斷 用于判斷范圍 set 字段 指定列 where 條件 指定行
update 表名 set 字段=值; 修改數(shù)據(jù)表中所有字段的值
update 表名 set 字段1=值1, 字段2=值2 where 條件; set指定列和值 where指定行范圍

update students set age=18;
update students set name="小李", age=18 where id=4;
刪除

物理刪除表內(nèi)容
delete from 表名; 清空數(shù)據(jù)表內(nèi)容
delete from 表名 where 條件; 刪除滿足條件的記錄

delete from students;
delete from students where id=4;

邏輯刪除表內(nèi)容
添加is_delete字段將此字段設(shè)置為bit類型用0,1來表示是否刪除
更新id_delete的值邏輯刪除記錄

alter table students add is_delete bit default 0;
update students set is_delete=1 where id=3;

MySQL 查詢

查詢

select 表名.字段, 表名.字段 from 表名; 此用法的意義在于表的別名
select 別名.字段, 別名.字段 from 表名 as 別名; 避免多個表同名沖突
select distinct 字段 from 表名; distinct 顯示不同的 也就是去重

select students.name, students.name from students;
select s.name,s.name from students as s;
select distinct gender from students;
條件

比較運算符
< 小于 > 大于 <= 小于等于 >= 大于等于 != 不等于
邏輯運算符
and 與 or 或 not 非
select * from 表名 where not 條件1 and not 條件2; 查詢不滿足條件1也不滿足條件2的結(jié)果
select * from 表名 where not (條件1 and 條件2); 查詢不是滿足條件1和條件2的結(jié)果

select * from students where not age>18 and not gender="女";
select * from students where not (age>18 and gender=2);

判斷空
null 類似Python中的none 判斷空使用 is null 而不是 == null
is null 為空 is not null 非空
select 字段 from 表名 where is null; 查詢字段為空的結(jié)果
select 字段 from 表名 where is not null; 查詢字段不為空的結(jié)果

slelect height from students where is null;
select height from students where is not null;

模糊查詢
模糊查詢效率低下因為需要匹配每一條記錄的值
like 模糊匹配 % 沒有或多個 _ 必須有一個
select 字段 from 表名 where 字段 like "小%"; 查詢以小開頭加多個任意字符的字段
select 字段 from 表名 where 字段 like "小_"; 查詢以小開頭一個任意字符的字段
select 字段 from 表名 where 字段 like "__"; 查詢必須為兩個字符的結(jié)果

select name from students where name like "小%";
select name from students where name like "小_";
select name from students where name like "__"

rlike 正則匹配
select 字段 from 表名 where 字段 rlike "^周.*"; 查詢以周開頭任意數(shù)量字符的結(jié)果
select 字段 from 表名 where 字段 rlike "杰$"; 查詢必須以杰結(jié)尾的結(jié)果

select name from students where name rlike "^周.*";
select name from students where name rlike "杰$";

范圍查詢
select 字段 from 表名 where 字段 in(1,3,5); 包含集合的結(jié)果
select 字段 from 表名 where 字段 not in(2,4,6); 不包含集合的結(jié)果
連續(xù)范圍
select 字段 from 表名 where 字段 between 開始 and 結(jié)束 在兩個值范圍的結(jié)果 包含開始和結(jié)束的值
select 字段 from 表名 where 字段 not between 開始 and 結(jié)束 不在范圍內(nèi)的結(jié)果 包含開始和結(jié)束的值

select name from students where cls in(1,3,5);
select name from students where cls not in(2,4,6);
排序

MySQL默認按主鍵從小到大排序
指定排序后有相同值的繼續(xù)按主鍵從小到大排序
asc 從小到大排序 desc 從大到小排序
select * from 表名 order by 字段 asc; 結(jié)果按照字段的值升序
select * from 表名 order by 字段 desc, 字段 desc; 當?shù)谝慌判蚝笥邢嗤禃r按第二排序繼續(xù)排序

select * from students order by age desc;
select * from students order by age desc, id desc;
聚合函數(shù)

聚合函數(shù)就是用來輸入多個數(shù)據(jù)輸出一個結(jié)果的 在select 指定字段時使用
count() 統(tǒng)計個數(shù) max() 求最大值 min() 求最小值 sum() 求和 avg() 求平均值 round(目標,位數(shù)) 四舍五入目標并指定保留小數(shù)
select count(字段) from 表名 where 條件; count()查詢所指定字段出現(xiàn)的次數(shù)值為NULL的不統(tǒng)計
select max(字段) from 表名; max()取查詢到的數(shù)據(jù)的最大值
select min(字段) from 表名; min()取查詢到的數(shù)據(jù)的最小值
select sum(字段) from 表名; sum()求查詢到的數(shù)據(jù)的總和
select avg(字段) from 表名; avg()求查詢的數(shù)據(jù)的平均值 小數(shù)默認保留4位
select sum(字段)/count(*) from 表名; 通過計算獲得平均值
select round(avg(字段),位數(shù)) from 表名; round(目標, 位數(shù)) 四舍五入 0不保留小數(shù)

select count(*) from students;
select max(age) from students;
select min(height) from students;
select sum(age) from students;
select avg(height) from students;
select sum(height)/count(*) from students;
select round(avg(age),2) from students;
分組

分組一般需要與聚合函數(shù)一起使用
group by 字段; 以此字段的記錄為依據(jù)把表中的所有記錄歸納分組生成一個中間表此表中此字段的值唯一其他字段的值可能有多個當語句中使用 group by 字段 后 select 所查詢的數(shù)據(jù)就只針對中間表內(nèi)的數(shù)據(jù)進行查詢所以 select 后只能輸入此字段因為關(guān)系表不能輸出有多個數(shù)據(jù)的記錄 并且使用聚合函數(shù)進行操作 因為聚合函數(shù)可以接收多個數(shù)據(jù) 輸出一個結(jié)果
where 指定表輸出的條件 having 指定中間表輸出的條件
count(字段) 查詢表中此字段的數(shù)據(jù)以分組形式顯示 原始表中null的不統(tǒng)計
group_concat(字段[,"char",字段]) 查詢表中此字段的數(shù)據(jù)以分組形式顯示 并且可以任意連接字符
select 字段 from 表名 group by 字段; 以字段分組 并查詢分組后字段的信息 但不能查詢其他字段的信息 因為其他字段可能有多個值
select count(字段) from 表名 group by gender; 查詢每組性別分別有多少所統(tǒng)計的數(shù)量
select group_concat(字段,"-",字段) from 表名 group by gender; 查詢每組性別中這些字段的值并以字符間隔

select gender from students group by gender;
select count(*) from students group by gender;
select gender, group_concat(name,"-",age) from students group by gender;
分頁

面對大量數(shù)據(jù)時使用分頁查看
limit 始終寫在所有語句的最后
select 字段 from 表名 limit 個數(shù); 限制查詢的結(jié)果所顯示的個數(shù)
select 字段 from 表名 limit 開始,個數(shù); 指定結(jié)果的個數(shù)并且指定開始位置
select 字段 from 表名 limit (頁碼-1)*每頁個數(shù), 每頁個數(shù); 前端實現(xiàn)分頁的原理 但SQL中不支持這種寫法

select * from students limit 2;
select * from students limit 4, 2;
連接查詢

MySQL中(僅限于MySQL)CROSS JOIN與INNER JOIN的表現(xiàn)是一樣的在INNER JOIN不指定ON條件得到的結(jié)果都是笛卡爾積 可以簡寫為 JOIN
having 指定中間表的查詢條件
select * from 表1 inner join 表2; 查詢字段以笛卡爾積輸出結(jié)果
select 表1.* 表2.字段 from 表1 inner join 表2 on 表1.字段=表2.字段; 內(nèi)連接查詢字段顯示表1字段等于表2字段的部分
select 別名1.字段, 別名2.字段 from 表1 as 別名1 left join 表2 as 別名2 on 別名1.字段=別名2.字段; 左鏈接查詢 顯示交集以左表為主
select 字段 from 表1 right join 表2 on 表1.字段=表2.字段; 右連接查詢 顯示交集 右表的數(shù)據(jù)如果左表沒有對應(yīng)的值用null填充

select * from students inner join classes on students.cls_id=classes.id;
select s.*, c.name from students as s inner join classes as c on s.cls_id=c.id;
select * from students left join classes on students.cls_id=classes.id;
select * from students left join classes on students.cls_id=classes.id having classes.name is null;
select students.name, classes_name from students right join classes on students.cls_id=classes.id;
自關(guān)聯(lián)

如果在一個連接查詢中 涉及到的兩個表都是同一個表 這種查詢就稱為自連接查詢
同一張表在FROM子句中多次出現(xiàn) 為了區(qū)別該表的每一次出現(xiàn) 需要為表定義一個別名 自連接是一種特殊的內(nèi)連接 它是指相互連接的表在物理上為同一張表 但可以在邏輯上分為兩張表
自關(guān)聯(lián)可以實現(xiàn) 在一張表內(nèi)存儲包含關(guān)系的不同數(shù)據(jù)
select * from 表名 as 別名1 inner join 表名 as 別名2 on 別名1.字段=別名2.字段 having 別名1.字段="xxx"; 查詢表中字段為xxx的所有自關(guān)聯(lián)內(nèi)容

子查詢

子查詢允許把一個查詢嵌套在另一個查詢當中 子查詢 又叫內(nèi)部查詢 相對于內(nèi)部查詢包含內(nèi)部查詢的就稱為外部查詢
子查詢中可以包含比如: select distinct group by order by limit join union 但是對應(yīng)的外部查詢必須是以下語句之一 select insert update delete set do
select * from 表 where 字段=(select 函數(shù)(字段) from 表); 通過內(nèi)部查詢的結(jié)果給定外部查詢的范圍

分組 聚合函數(shù) 自關(guān)聯(lián) 子查詢 綜合運用

查詢表中各種類中最貴商品的所有信息
select * from 表 inner join (select 字段, max(字段1) as 別名1 from 表 group by 字段) as 別名2 on 表.字段=別名2.別名1;

select * from goods as g1 inner join (select cata_name, max(price) as max_price from goods group by cate_name) as g2 on g1.price=g2.max_price;

視圖

視圖就是一條SELECT語句執(zhí)行后返回的結(jié)果集
視圖是查詢語句執(zhí)行的結(jié)果 不存儲具體的數(shù)據(jù)

定義視圖

建議以v_開頭
create view 視圖名稱 as select 語句;

create view v_goods_b_c as select g.*, gb.name as brand_name, gc.name as cates_name from goods as g left join goods_brands as gb on g.brand_id=gb.id left join goods_cates as gc on g.cate_id=gc.id;
查看視圖

show tables;查看表會列出所有視圖

使用視圖

視圖的用途只是查詢無法對視圖進行增刪改

select * from v_goods_b_c;
刪除視圖

drop view 視圖名稱;

drop view  v_goods_b_c;
視圖的作用
  1. 提高了重用性 就像一個函數(shù)
  2. 對數(shù)據(jù)庫重構(gòu) 卻不影響程序的運行
  3. 提高了安全性能 可以對不同的用戶
  4. 讓數(shù)據(jù)更加清晰

事務(wù)

事務(wù)廣泛的運用于訂單系統(tǒng) 銀行系統(tǒng)等多種場景

事務(wù)四大特性(簡稱ACID)

原子性（atomicity）

一個事務(wù)必須被視為一個不可分割的最小工作單元，整個事務(wù)中的所有操作要么全部提交成功诅挑，要么全部失敗回滾檬某。對于一個事務(wù)來說贡未，不可能只執(zhí)行其中的一部分操作疯攒，這就是事務(wù)的原子性

一致性（consistency）

數(shù)據(jù)庫總是從一個一致性的狀態(tài)轉(zhuǎn)換到另一個一致性的狀態(tài)会喝。（一致性確保了，因為事務(wù)最終沒有提交概页，所做的修改也不會保存到數(shù)據(jù)庫中籽御。）

隔離性（isolation）

一個事務(wù)所做的修改在最終提交以前，對其他事務(wù)是不可見的绰沥。

持久性（durability）

一旦事務(wù)提交篱蝇，則其所做的修改會永久保存到數(shù)據(jù)庫。

事務(wù)命令

表的引擎類型必須是innodb類型才可以使用事務(wù) 這是mysql表的默認引擎
開啟事務(wù)后執(zhí)行修改命令 變更會維護到本地緩存中 而不維護到物理表中
開啟事務(wù)
begin; 或者 start transaction;
將緩存中的數(shù)據(jù)變更維護到物理表中
commit;
放棄緩存中變更的數(shù)據(jù)
rollback;

注意

默認每條增刪改命令都會自動的觸發(fā)事務(wù) 包括insert update delete
在SQL語句中手動開啟事務(wù)的原因是：可以進行多次數(shù)據(jù)的修改徽曲，如果成功一起成功，否則一起回滾到之前的數(shù)據(jù)

索引

當數(shù)據(jù)庫中數(shù)據(jù)量很大時 查找數(shù)據(jù)會變得很慢 這時借助索引就可以讓數(shù)據(jù)的查詢變快
索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分)麸塞，它們包含著對數(shù)據(jù)表里所有記錄的引用指針秃臣。

索引原理

MySQL通過算法自動建立B-Tree 通過分叉縮小范圍加速查詢過程
最簡單的如果1000條數(shù)據(jù)，1到100分成第一段哪工，101到200分成第二段奥此，201到300分成第三段……這樣查第250條數(shù)據(jù)，只要找第三段就可以了雁比，一下子去除了90%的無效數(shù)據(jù)稚虎。

索引的使用

  • 查看索引

    • show index from 表名;

  • 創(chuàng)建索引

    • create index 索引名稱 on 表名(字段名稱(長度))
    • 如果指定字段是字符串 需要指定長度 建議長度與定義字段時的長度一致
    • 字段類型如果不是字符串 可以不填寫長度部分
create index name_index on goods(name(10));
  • 刪除索引
    • drop index 索引名稱 on 表名;
drop index name_index on goods;
注意：

要注意的是，建立太多的索引將會影響更新和插入的速度偎捎，因為它需要同樣更新每個索引文件蠢终。對于一個經(jīng)常需要更新和插入的表格，就沒有必要為一個很少使用的where字句單獨建立索引了茴她，對于比較小的表寻拂，排序的開銷不會很大，也沒有必要建立另外的索引丈牢。

建立索引會占用磁盤空間

賬戶管理

在生產(chǎn)環(huán)境下操作數(shù)據(jù)庫時祭钉，絕對不可以使用root賬戶連接，而是創(chuàng)建特定的賬戶己沛，授予這個賬戶特定的操作權(quán)限慌核，然后連接進行操作，主要的操作就是數(shù)據(jù)的crud

  • MySQL賬戶體系：

    • 服務(wù)實例級賬號：如果某用戶如root,擁有服務(wù)實例級分配的權(quán)限申尼，那么該賬號就可以刪除所有的數(shù)據(jù)庫垮卓、連同這些庫中的表
    • 數(shù)據(jù)庫級別賬號：對特定數(shù)據(jù)庫執(zhí)行增刪改查的所有操作
    • 數(shù)據(jù)表級別賬號：對特定表執(zhí)行增刪改查等所有操作
    • 字段級別的權(quán)限：對某些表的特定字段進行操作
    • 存儲程序級別的賬號：對存儲程序進行增刪改查的操作

  • 賬戶的操作主要包括創(chuàng)建賬戶、刪除賬戶晶姊、修改密碼扒接、授權(quán)權(quán)限等

1.進行賬戶操作時，需要使用root賬戶登錄，這個賬戶擁有最高的實例級權(quán)限
2.通常都使用數(shù)據(jù)庫級操作權(quán)限

數(shù)據(jù)庫設(shè)計

  • 關(guān)系型數(shù)據(jù)庫建議在E-R模型的基礎(chǔ)上钾怔，我們需要根據(jù)產(chǎn)品經(jīng)理的設(shè)計策劃碱呼，抽取出來模型與關(guān)系，制定出表結(jié)構(gòu)宗侦，這是項目開始的第一步
  • 在開發(fā)中有很多設(shè)計數(shù)據(jù)庫的軟件愚臀，常用的如power designer，db desinger等矾利，這些軟件可以直觀的看到實體及實體間的關(guān)系
  • 設(shè)計數(shù)據(jù)庫姑裂，可能是由專門的數(shù)據(jù)庫設(shè)計人員完成，也可能是由開發(fā)組成員完成男旗，一般是項目經(jīng)理帶領(lǐng)組員來完成
  • 現(xiàn)階段不需要獨立完成數(shù)據(jù)庫設(shè)計舶斧，但是要注意積累一些這方面的經(jīng)驗

三范式

  • 經(jīng)過研究和對使用中問題的總結(jié)，對于設(shè)計數(shù)據(jù)庫提出了一些規(guī)范察皇，這些規(guī)范被稱為范式(Normal Form)
  • 目前有跡可尋的共有8種范式茴厉，一般需要遵守3范式即可
  • 第一范式（1NF）：強調(diào)的是列的原子性，即列不能夠再分成其他幾列什荣。

考慮這樣一個表：【聯(lián)系人】（姓名矾缓，性別，電話） 如果在實際場景中稻爬，一個聯(lián)系人有家庭電話和公司電話嗜闻，那么這種表結(jié)構(gòu)設(shè)計就沒有達到 1NF。要符合 1NF 我們只需把列（電話）拆分桅锄，即：【聯(lián)系人】（姓名琉雳，性別，家庭電話竞滓，公司電話）咐吼。1NF 很好辨別，但是 2NF 和 3NF 就容易搞混淆商佑。

  • 第二范式（2NF）：首先是 1NF锯茄，另外包含兩部分內(nèi)容，一是表必須有一個主鍵茶没；二是沒有包含在主鍵中的列必須完全依賴于主鍵肌幽，而不能只依賴于主鍵的一部分。

考慮一個訂單明細表：【OrderDetail】（OrderID抓半，ProductID喂急，UnitPrice，Discount笛求，Quantity廊移，ProductName）糕簿。 因為我們知道在一個訂單中可以訂購多種產(chǎn)品，所以單單一個 OrderID 是不足以成為主鍵的狡孔，主鍵應(yīng)該是（OrderID懂诗，ProductID）。顯而易見 Discount（折扣）苗膝，Quantity（數(shù)量）完全依賴（取決）于主鍵（OderID殃恒，ProductID），而 UnitPrice辱揭，ProductName 只依賴于 ProductID离唐。所以 OrderDetail 表不符合 2NF。不符合 2NF 的設(shè)計容易產(chǎn)生冗余數(shù)據(jù)问窃。
可以把【OrderDetail】表拆分為【OrderDetail】（OrderID亥鬓，ProductID，Discount泡躯，Quantity）和【Product】（ProductID贮竟，UnitPrice，ProductName）來消除原訂單表中UnitPrice较剃，ProductName多次重復(fù)的情況。

  • 第三范式（3NF）：首先是 2NF技健，另外非主鍵列必須直接依賴于主鍵写穴，不能存在傳遞依賴。即不能存在：非主鍵列 A 依賴于非主鍵列 B雌贱，非主鍵列 B 依賴于主鍵的情況啊送。

考慮一個訂單表【Order】（OrderID，OrderDate欣孤，CustomerID馋没，CustomerName，CustomerAddr降传，CustomerCity）主鍵是（OrderID）篷朵。 其中 OrderDate，CustomerID婆排，CustomerName声旺，CustomerAddr，CustomerCity 等非主鍵列都完全依賴于主鍵（OrderID）段只，所以符合 2NF腮猖。不過問題是 CustomerName，CustomerAddr赞枕，CustomerCity 直接依賴的是 CustomerID（非主鍵列）澈缺，而不是直接依賴于主鍵坪创，它是通過傳遞才依賴于主鍵，所以不符合 3NF姐赡。 通過拆分【Order】為【Order】（OrderID莱预，OrderDate，CustomerID）和【Customer】（CustomerID雏吭，CustomerName锁施，CustomerAddr，CustomerCity）從而達到 3NF杖们。 *第二范式（2NF）和第三范式（3NF）的概念很容易混淆悉抵，區(qū)分它們的關(guān)鍵點在于，2NF：非主鍵列是否完全依賴于主鍵摘完，還是依賴于主鍵的一部分姥饰；3NF：非主鍵列是直接依賴于主鍵，還是直接依賴于非主鍵列孝治。

E-R模型
  • E表示entry列粪，實體，設(shè)計實體就像定義一個類一樣谈飒，指定從哪些方面描述對象岂座，一個實體轉(zhuǎn)換為數(shù)據(jù)庫中的一個表
  • R表示relationship，關(guān)系杭措，關(guān)系描述兩個實體之間的對應(yīng)規(guī)則费什，關(guān)系的類型包括包括一對一、一對多手素、多對多
  • 關(guān)系也是一種數(shù)據(jù)鸳址，需要通過一個字段存儲在表中

實體A對實體B為1對1，則在表A或表B中創(chuàng)建一個字段泉懦，存儲另一個表的主鍵值
實體A對實體B為1對多：在表B中創(chuàng)建一個字段稿黍，存儲表A的主鍵值
實體A對實體B為多對多：新建一張表C，這個表只有兩個字段崩哩，一個用于存儲A的主鍵值巡球，一個用于存儲B的主鍵值

擴展閱讀
邏輯刪除
  • 對于重要數(shù)據(jù)，并不希望物理刪除琢锋，一旦刪除辕漂，數(shù)據(jù)無法找回
  • 刪除方案：設(shè)置isDelete的列，類型為bit吴超，表示邏輯刪除钉嘹，默認值為0
  • 對于非重要數(shù)據(jù)，可以進行物理刪除
  • 數(shù)據(jù)的重要性鲸阻，要根據(jù)實際開發(fā)決定
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末跋涣，一起剝皮案震驚了整個濱河市缨睡，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌陈辱，老刑警劉巖奖年，帶你破解...
    沈念sama閱讀 206,482評論 6贊 481
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異沛贪，居然都是意外死亡陋守，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,377評論 2贊 382
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門利赋，熙熙樓的掌柜王于貴愁眉苦臉地迎上來水评，“玉大人，你說我怎么就攤上這事媚送≈性铮” “怎么了？”我有些...
    開封第一講書人閱讀 152,762評論 0贊 342
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵塘偎，是天一觀的道長疗涉。 經(jīng)常有香客問我，道長吟秩，這世上最難降的妖魔是什么咱扣？ 我笑而不...
    開封第一講書人閱讀 55,273評論 1贊 279
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮涵防，結(jié)果婚禮上偏窝，老公的妹妹穿的比我還像新娘。我一直安慰自己武学，他們只是感情好，可當我...
    茶點故事閱讀 64,289評論 5贊 373
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布伦意。 她就那樣靜靜地躺著火窒，像睡著了一般。 火紅的嫁衣襯著肌膚如雪驮肉。 梳的紋絲不亂的頭發(fā)上熏矿，一...
    開封第一講書人閱讀 49,046評論 1贊 285
  • 城市分裂傳說
    那天，我揣著相機與錄音离钝，去河邊找鬼票编。 笑死，一個胖子當著我的面吹牛卵渴，可吹牛的內(nèi)容都是我干的慧域。 我是一名探鬼主播，決...
    沈念sama閱讀 38,351評論 3贊 400
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼浪读，長吁一口氣：“原來是場噩夢啊……” “哼昔榴！你這毒婦竟也來了辛藻？” 一聲冷哼從身側(cè)響起，我...
    開封第一講書人閱讀 36,988評論 0贊 259
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤互订，失蹤者是張志新（化名）和其女友劉穎吱肌，沒想到半個月后，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體仰禽，經(jīng)...
    沈念sama閱讀 43,476評論 1贊 300
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡氮墨，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 35,948評論 2贊 324
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了吐葵。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片规揪。...
    茶點故事閱讀 38,064評論 1贊 333
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖折联，靈堂內(nèi)的尸體忽然破棺而出粒褒，到底是詐尸還是另有隱情，我是刑警寧澤诚镰，帶...
    沈念sama閱讀 33,712評論 4贊 323
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布奕坟，位于F島的核電站，受9級特大地震影響清笨，放射性物質(zhì)發(fā)生泄漏月杉。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點故事閱讀 39,261評論 3贊 307
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一抠艾、第九天 我趴在偏房一處隱蔽的房頂上張望苛萎。 院中可真熱鬧，春花似錦检号、人聲如沸腌歉。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,264評論 0贊 19
  • 一樁弒父案齐苛，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽翘盖。三九已至，卻和暖如春凹蜂，著一層夾襖步出監(jiān)牢的瞬間馍驯，已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 31,486評論 1贊 262
  • 情欲美人皮
    我被黑心中介騙來泰國打工玛痊， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留汰瘫，地道東北人。 一個月前我還...
    沈念sama閱讀 45,511評論 2贊 354
  • 代替公主和親
    正文 我出身青樓擂煞，卻偏偏與公主長得像脯倒，于是被迫代替她去往敵國和親常摧。 傳聞我的和親對象是個殘疾皇子蓖捶，可洞房花燭夜當晚...
    茶點故事閱讀 42,802評論 2贊 345

推薦閱讀更多精彩內(nèi)容

  • 一、MySQL優(yōu)化 MySQL優(yōu)化從哪些方面入手： （1）存儲層（數(shù)據(jù)） 構(gòu)建良好的數(shù)據(jù)結(jié)構(gòu)浙宜。可以大大的提升我們S...
    寵辱不驚丶歲月靜好閱讀 2,415評論 1贊 8
  • 1. 了解SQL 1.1 數(shù)據(jù)庫基礎(chǔ) ? 學(xué)習(xí)到目前這個階段蛹磺，我們就需要以某種方式與數(shù)據(jù)庫打交道粟瞬。在深入學(xué)習(xí)MyS...
    鋒享前端閱讀 1,042評論 0贊 1
  • 什么是數(shù)據(jù)庫？ 數(shù)據(jù)庫是存儲數(shù)據(jù)的集合的單獨的應(yīng)用程序萤捆。每個數(shù)據(jù)庫具有一個或多個不同的API裙品，用于創(chuàng)建，訪問俗或，管理...
    chen_000閱讀 4,030評論 0贊 19
  • 手動不易市怎，轉(zhuǎn)發(fā)請注明出處 --Trance 數(shù)據(jù)庫系統(tǒng)命令: (1).查看存儲過程狀態(tài):show pro...
    Trance_b54c閱讀 1,650評論 0贊 8
  • 1.MySQL數(shù)據(jù)庫 2.SQL語句 第一節(jié)課 ###1(MySQL數(shù)據(jù)庫)數(shù)據(jù)庫概念.avi 5...
    碼了個農(nóng)啵閱讀 1,194評論 1贊 16