Mysql資料庫基礎知識_ZenDei技術網路在線

E-R模型（實體關係模型）當前物理的資料庫都是按照E-R模型進行設計的 E表示entry，實體 R表示relationship，關係一個實體轉換為資料庫中的一個表關係描述兩個實體之間的對應規則，包括一對一一對多多對多關係轉換為資料庫表中的一個列 *在關係型資料庫中一行就是一個對象數據... ...

E-R模型（實體關係模型）

當前物理的資料庫都是按照E-R模型進行設計的
E表示entry，實體
R表示relationship，關係
一個實體轉換為資料庫中的一個表
關係描述兩個實體之間的對應規則，包括
- 一對一
- 一對多
- 多對多
關係轉換為資料庫表中的一個列 *在關係型資料庫中一行就是一個對象

數據引擎

MyISAM和InnoDB的區別

MySQL預設採用的是MyISAM。
InnoDB支持事務，而MyISAM不支持。InnoDB的AUTOCOMMIT預設是打開的，即每條SQL語句會預設被封裝成一個事務，自動提交，這樣會影響速度，所以最好是把多條SQL語句顯示放在begin和commit之間，組成一個事務去提交。
InnoDB支持數據行鎖定，MyISAM不支持行鎖定，只支持鎖定整個表。即MyISAM同一個表上的讀鎖和寫鎖是互斥的，MyISAM併發讀寫時如果等待隊列中既有讀請求又有寫請求，預設寫請求的優先順序高，即使讀請求先到，所以MyISAM不適合於有大量查詢和修改並存的情況，那樣查詢進程會長時間阻塞。因為MyISAM是鎖表，所以某項讀操作比較耗時會使其他寫進程餓死。
InnoDB支持外鍵，MyISAM不支持。
InnoDB的主鍵範圍更大，最大是MyISAM的2倍。
InnoDB不支持全文索引，而MyISAM支持。全文索引是指對char、varchar和text中的每個詞（停用詞除外）建立倒排序索引。MyISAM的全文索引其實沒啥用，因為它不支持中文分詞，必須由使用者分詞後加入空格再寫到數據表裡，而且少於4個漢字的詞會和停用詞一樣被忽略掉。
MyISAM支持GIS數據，InnoDB不支持。即MyISAM支持以下空間數據對象：Point,Line,Polygon,Surface等。
沒有where的count(*)使用MyISAM要比InnoDB快得多。因為MyISAM內置了一個計數器，count(*)時它直接從計數器中讀，而InnoDB必須掃描全表。所以在InnoDB上執行count(*)時一般要伴隨where，且where中要包含主鍵以外的索引列。為什麼這裡特別強調"主鍵以外"？因為InnoDB中primary index是和raw data存放在一起的，而secondary index則是單獨存放，然後有個指針指向primary key。所以只是count(*)的話使用secondary index掃描更快，而primary key則主要在掃描索引同時要返回raw data時的作用較大。

三範式

經過研究和對使用中問題的總結，對於設計資料庫提出了一些規範，這些規範被稱為範式
第一範式（1NF)：列不可拆分
第二範式（2NF)：唯一標識
第三範式（3NF)：引用主鍵
說明：後一個範式，都是在前一個範式的基礎上建立的

欄位類型

在mysql中包含的數據類型很多，這裡主要列出來常用的幾種
數字：int,decimal
字元串：
- char(8):不可變，不夠8個在右邊補空格,
- varchar(8)：可變，不夠8個的話，該是幾個就幾個,
- text
日期：datetime
布爾：
- bit(1): 0,1
- bit(2): 00, 01, 10, 11
- bit(3): 001, 010, 100,......

約束

主鍵primary key
非空not null
惟一unique
預設default
外鍵foreign key

遠程連接

一般在公司開發中，可能會將資料庫統一搭建在一臺伺服器上，所有開發人員共用一個資料庫，而不是在自己的電腦中配置一個資料庫
運行命令
mysql -hip地址 -uroot -p

資料庫

創建資料庫
- create database test character utf8 collate utf8_general_ci;
刪除資料庫
- drop database 資料庫名;
切換資料庫
- use 資料庫名;

數據表

創建數據表
- create table create_test(
- id int(11) auto_increment primary key not null,
- name varchar(20) not null,
- birthday datetime,
- gender bit default 0,
- isDelete bit default 0
- ) engine=InnoDB default charset=utf8;

修改表
alter table 表名 add|change|drop 列名類型;
如：
alter table students add birthday datetime;
刪除表
drop table 表名;
查看表結構
desc 表名;
更改表名稱
rename table 原表名 to 新表名;
查看表的創建語句
show create table '表名';

數據操作

查詢
select * from 表名
增加
全列插入：insert into 表名 values(...)
預設插入：insert into 表名(列1,...) values(值1,...)
同時插入多條數據：insert into 表名 values(...),(...)...;
或insert into 表名(列1,...) values(值1,...),(值1,...)...;
主鍵列是自動增長，但是在全列插入時需要占位，通常使用0，插入成功後以實際數據為準
修改
update 表名 set 列1=值1,... where 條件
刪除
delete from 表名 where 條件
邏輯刪除，本質就是修改操作update
alter table students add isdelete bit default 0;
如果需要刪除則
update students isdelete=1 where ...;

清空表

-- 清空全部數據，不寫日誌，不可恢復，速度極快

truncate table 表名;

-- 清空全部數據，寫日誌，數據可恢復，速度慢

delete from 表名

查看字元集

show variables like '%char%';

數據備份

進入超級管理員
sudo -s
進入mysql庫目錄
cd /var/lib/mysql
運行mysqldump命令
mysqldump –uroot –p 資料庫名 > ~/Desktop/備份文件.sql;
按提示輸入mysql的密碼

數據恢復

連接mysqk，創建資料庫
退出連接，執行如下命令
mysql -uroot –p 資料庫名 < ~/Desktop/備份文件.sql
根據提示輸入mysql密碼

數據查詢（where中的優先順序）

小括弧，not，比較運算符，邏輯運算符
and比or先運算，如果同時出現並希望先算or，需要結合()使用

數據查詢（where和having 的區別）

where是對from後面指定的表進行數據篩選，屬於對原始數據的篩選
having是對group by的結果進行篩選，屬於對分組數據的篩選

數據聚合操作

select count(*) from students;
select max(id) from students where gender=0;
select min(id) from students where isdelete=0;
select sum(id) from students where gender=1;
select avg(id) from students where isdelete=0 and gender=0;

數據分組

select gender as 性別,count(*) from students group by gender having gender=1;

排序

select * from 表名 order by 列1 asc|desc,列2 asc|desc,...

去除重覆行

select distinct * from ...

外鍵的設置

在創建表時可以直接創建約束
create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id) on delete cascade,
foreign key(subid) references subjects(id) on delete cascade
);
也可以在創建好數據表後單獨創建外鍵約束
alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;
級聯操作的類型包括：
- restrict（限制）：預設值，拋異常
- cascade（級聯）：如果主表的記錄刪掉，則從表中相關聯的記錄都將被刪除
- set null：將外鍵設置為空
- no action：什麼都不做
企業中一般會在主表中創建邏輯刪除欄位（isDelete）.

連表查詢

select students.sname,subjects.stitle,scores.score
from scores inner join students on scores.stuid=students.id;

註：inner join ...on...左右的表明無所謂順序，讀取兩個表的全部欄位

left join...on... 和 right join...on... 查詢其中一個的全部數據欄位，另一個沒有也無所有，null填充