數據庫基礎
一、數據模型
數據庫模型圖
1、層次模型、
2、網狀模型、
3、關系模型
二維關系：
-
表：就是一個關系及屬性的描述，如：學生（學好，姓名，性別，班級）
行：row, entity 列：colume, attribution
-
索引：數據結構，輔助完成數據查找；
-
視圖：偽表；是一種外模式，是建立在基礎表之上的數據查詢
SQL接口：
用來連接到數據庫系統上，用來操作的一個shell，也提供編程功能，使之能操作數據；Structured Query language（結構化的查詢語言） ANSI：SQL-標準，SQL-86, SQL-89, SQL-92, SQL-99, SQL-03, ...
SQL代碼：
存儲例程：storage routing 存儲過程：storage procedure 存儲函數：storage function 觸發器：trigger 事件調度器：event scheduler 授權系統：把有線多個資源讓有限多個用戶去使用 用戶和權限： 用戶：用戶名和密碼；在mysql中用戶名是用 name@訪問Ip 來定義的。 權限：管理類（負責系統的啟動和停止）、數據庫（可以操作數據庫上的所有組件）、表級別的權限、字段級別的權限
DMBS：DataBase Management System（數據庫）
RDBMS：Relational DBMS（關系型數據庫）
Mysql：單進程，多線程模型 用戶連接：通過線程來實現； 線程池：用來定義mysql的線程響應個數的
數據庫模型圖
事務機制：一個由單個或多個SQL語句組成的不可分割原子操作，Transaction；要么全執行要么全不執行
可以通過“回滾”操作 判斷一個存儲系統是否支持事務，測試標準 ACID: A: 原子性； C：一致性；這個加上了那個就得減去，總體不變 I：隔離性；事務于事務之間是隔離的（操作同一個事務要各自獨立） D：持久性；操作的結果必須要持久保存
對于數據庫必須具有三個組件
-
邏輯組件：所能看到并進行操作的組件
-
中間層（映射層）：使操作的邏輯組件轉換成為物理組件 DBA 管理的組件
-
物理組件：數據庫系統存儲到硬盤上所需要的組件
二、RDMBS基礎概念
1、數據庫的設計要遵循限定：
補充材料：RDMBS設計范式基礎概念
設計關系數據庫時，遵從不同的規范要求，設計出合理的關系型數據庫，這些不同的規范要求被稱為不同的范式，各種范式呈遞次規范，越高的范式數據庫冗余越小。 目前關系數據庫有六種范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又稱完美范式）。滿足最低要求的范式是第一范式（1NF）。在第一范式的基礎上進一步滿足更多規范要求的稱為第二范式（2NF），其余范式以次類推。一般說來，數據庫只需滿足第三范式(3NF）就行了。 (1) 第一范式（1NF） 所謂第一范式（1NF）是指在關系模型中，對域添加的一個規范要求，所有的域都應該是原子性的，即數據庫表的每一列都是不可分割的原子數據項，而不能是集合，數組，記錄等非原子數據項。即實體中的某個屬性有多個值時，必須拆分為不同的屬性。在符合第一范式（1NF）表中的每個域值只能是實體的一個屬性或一個屬性的一部分。簡而言之，第一范式就是無重復的域。 說明：在任何一個關系數據庫中，第一范式（1NF）是對關系模式的設計基本要求，一般設計中都必須滿足第一范式（1NF）。不過有些關系模型中突破了1NF的限制，這種稱為非1NF的關系模型。換句話說，是否必須滿足1NF的最低要求，主要依賴于所使用的關系模型。 (2) 第二范式(2NF) 第二范式（2NF）是在第一范式（1NF）的基礎上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。第二范式（2NF）要求數據庫表中的每個實例或記錄必須可以被唯一地區分。選取一個能區分每個實體的屬性或屬性組，作為實體的唯一標識。 第二范式（2NF）要求實體的屬性完全依賴于主關鍵字。所謂完全依賴是指不能存在僅依賴主關鍵字一部分的屬性，如果存在，那么這個屬性和主關鍵字的這一部分應該分離出來形成一個新的實體，新實體與原實體之間是一對多的關系。為實現區分通常需要為表加上一個列，以存儲各個實例的唯一標識。簡而言之，第二范式就是在第一范式的基礎上屬性完全依賴于主鍵。 (3) 第三范式（3NF） 第三范式（3NF）是第二范式（2NF）的一個子集，即滿足第三范式（3NF）必須滿足第二范式（2NF）。簡而言之，第三范式（3NF）要求一個關系中不包含已在其它關系已包含的非主關鍵字信息。簡而言之，第三范式就是屬性不依賴于其它非主屬性，也就是在滿足2NF的基礎上，任何非主屬性不得傳遞依賴于主屬性。
一共有5種范式：一般滿足第三個范式就好了，后面的
太完美反而不好
（1）表
為了滿足范式設計要求，將一個數據拆分為多個
（2）數據約束：
約束：constraint，向數據表提供的數據要遵守的限制；
-
主鍵：一個或多個字段的組合，填入的數據必須能在本表中唯一標識本行；必須提供數據，即NOT NULL;
一個表只能存在一個
-
惟一鍵：一個或多個字段的組合，填入的數據必須能在本表中唯一標識本行；允許為NULL；
一個表可以存在多個
-
外鍵：一個表中的某字段可填入數據取決于另一個表的主鍵已有的數據；
-
檢查性約束：取決于表達式的要求；
（3）索引：
將表中的一個或多個字段中的數據復制一份另存，并且此些需要按特定次序排序存儲；存在的目的是為了加速查詢操作，但會減慢寫入操作
索引類型
-
樹狀結構
-
hash結構
（4）關系運算：
選擇：挑選出符合條件的行（部分）； 投影：挑選出需要的字段；選出列 連接：
（5）數據抽象：
物理層：決定數據的存儲格式，即RDBMS在磁盤上如何組織文件； 邏輯層：描述DB存儲什么數據，以及數據間存在什么樣的關系； 視圖層：描述DB中的部分數據；
（6）關系模型的分類：
關系模型 實體-關系模型 基于對象的關系模型 半結構化的關系模型
三、mariadb
1.MariaDB的特性：
插件式存儲引擎：存儲管理器有多種實現版本，功能和特性可能均略有差別；用戶可根據需要靈活選擇； 存儲引擎也稱之為“表類型”； (1) 更多的存儲的存儲引擎 MyISAM --> Aria, InnoDB --> XtraDB (2) 諸多擴展和新特性 (3) 提供了較多測試組件 (4) truly open source MySQL is a multi-user, multi-threaded SQL database server. C/S： Server： mysqld, mysqld_safe, mysqld_multi Client：mysql
MySQL:的發行機制
Community Edtion 社區版 Enterprise Edtion 企業版
2、mariadb的安裝及配置相關
MySQL
-
安裝
CentOS 6: mysql-server, mysql
-
額外添加的配置項：
[mysqld] ... skip_name_resolve innodb_file_per_table=ON
注意這里在 skip_name 這一項不能等于on 他不支持這里是 mysql5.x版本
MariaDB：
-
安裝
CentOS 7: mariadb-server, mariadb
配置文件：/etc/my.cnf, /etc/my.cnf.d/*.cnf
-
額外添加的配置項：
[mysqld] skip_name_resolve = ON #表示跳過主機名解析 innodb_file_per_table = ON #每表單獨的表空間
Mariadb編譯安裝：
-
1、目前只支持Centos 7.x 因為其依賴的 glibc 包要求版本大于2.14
-
2、要求壓縮包要展開至 /usr/local 目錄并且叫 mysql
-
3、展開后我們不建議把這個目錄進行改名而是創建連接叫 mysql
步驟1：
1、從官方下載壓縮包 并解壓到/usr/local目錄下 2、ln -sv mariadb*（這個是展開的目錄） mysql 對此目錄做軟連接 3、創建一個mysql用戶（組）、確保是系統用戶 groupadd -r -g 306 mysql 4、把 mysql里面的所有文件屬主屬組改為root：mysql 5、準備數據存儲目錄 （這里只做演示 就指定 /mydata/data），并且改屬主屬組為 mysql 日志文件為 6、創建/etc/mysql 并把當前 support-files/my-large.cnf.sh 復制到其內部更名為 my.cnf當配置文件樣本 7、改配置，注意是在 [mysqld] 段中加 [mysqld] datadir = /mydata/data skip_name_resolve = ON innodb_file_per_table=ON
8、centos 6復制 support-files/mysql.server.sh到 /etc/ic.d/init.d/ 改名叫mysqld 加上執行權限就好了 7也行。 9.添加 mysqld 到啟動項 chkconfig --add mysqld 10、執行scripts下的腳本生成元數據文件 ./mysql_install_db.sh --user=mysql --datadir=/mydata/data
2.簡化版步驟
安裝MariaDB： 預制的包管理器格式的程序包： rpm包： os vendor：mariadb-devel MariaDB官方 通用二進制格式的程序包： 源碼包編譯：項目構建工具為cmake, 而非流行的make； 通用二進制格式包的安裝配置步驟： # useradd -r mysql # tar xf mariadb-VERSION-linux-x86_64.tar.gz -C /usr/local/ # cd /usr/local/ # ln -sv mariadb-VERSION-linux-x86_64 mysql # cd mysql # chown -R root:mysql ./* # mkdir -pv /mydata/data # chown -R mysql.mysql /mydata/data # scripts/mysql_install_db --user=mysql --datadir=/mydata/data --skip_name_resolve # cp support-files/my-large.cnf /etc/my.cnf
配置文件查找次序：
—->/etc/my.cnf –> /etc/mysql/my.cnf –> –default-extra-file=/PATH/TO/CONF_FILE –> ~/.my.cnf
3、注意：
默認的管理員用戶為：root，密碼為空；首次安裝后建議使用 mysql_secure_installation 命令進行安全設定；
mariadb的基礎應用
數據類型：
作用：存儲格式、數據范圍、所能參與的運算、排序方式
-
字符型：
定長字符型：CHAR(#)不區分字符大小寫；#表示儲存多少個，BIARNY(#) 變長字符型：VARCHAR(#)不區分字符大小寫，VARBINARY(#) 對象存儲：（基于指針的存儲，用來存儲大數據） TEXT BLOB 內建類型：ENUM（集合），SET（枚舉）
-
數值型：
精確數值型： INT：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT 近似數值型： FLOAT DOUBLE
-
日期時間型：
DATE TIME DATETIME TIMESTAMP YEAR
-
修飾符：
-
NOT NULL ：非空
-
DEFAULT ：默認值
-
類型符：
-
UNSIGNED：無符號
MariaDB程序組成：
-
C：Client
mysql mysqladmin mysqldump mysqlbinlog ...
-
S：Server
mysqld mysqld_safe 建議運行的程序，默認就是運行這個程序，線程安全方式 mysqld_mult 多實例；啟動多個mysql
-
管理工具程序：
myisampack myisamchk ...
三類套接字地址：
IPv4:PORT IPv6:PORT Unix_Sock：/var/lib/mysql/mysql.sock, /tmp/mysql.sock（源碼安裝）
一、命令行客戶端程序mysql
mysql [OPTIONS] [database] 常用選項： -uUSERNAME 默認為root -hHOST ：遠程主句（即mysql服務器）地址，默認為localhost -pPASSWORD 注意：mysql的用戶賬號由兩部分組成：’USERNAME‘@'HOST';其中HOST用于限制此用戶可以通過那些遠程主連接當前mysql服務； HOST的表示方式，支持使用通配符： %表示任意字符 _：匹配任意單個字符 -Ddb_name：連接到服務器端之后，設定其處指明的數據庫為默認數據庫，也可以進入庫后用 use 指明 -e 'STATEMENT；' 表示不再進入交互式模式，直接輸入一條命令后運行并退出 -S sock_file_path -P port
在mysql命令提示符下的命令：
-
客戶端命令：在客戶端執行，本地執行。
mysql> help \u db_name:設定那個庫為默認庫 \?：獲取可用的命令幫助； \q：退出客戶端程序； \d CHAR：自定義語句結束符； \g：語句結束標記；這個是指定結束標記 \G：語句結束標記，豎排顯示結果； \! SHELL_CMD：運行shell命令； \s：當前連接及服務器相關的狀態信息； \. \c 表示這條語句不想運行 相當于shell ctrl+c
二 、服務端命令：
SQL語句，發往服務端運行，并取回結果；需要顯式的語句結束符 ‘；’
-
DDL：數據定義語言，主要用于數據庫組件，例如數據庫、表、索引、視圖、觸發器、事件調度器、存儲過程、存儲函數；
CREATE， ALTER， DROP
-
DML：數據操縱語言，CRUD操作，主要用于操作表中的數據；
INSERT，DELETE，UPDATE，SELECT
-
DCL：數據控制語言
GRANT, REVOKE
-
授權命令：
GRANT ALL [PRIVILEGES] ON db.tbl TO 'username'@'host' IDENTIFIED BY 'password'; db：數據庫的名字，可以使用*通配； tbl：表的名稱，可以使用*通配； mydb.*, *.*, mydb.tbl1 FLUSH PRIVILEGES
獲取幫助：
mysql> help KEYWORD mysql> help contents
(一)、數據庫管理（數據庫的名字區分字符大小寫）：
-
創建
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS（不存在時創建）] db_name
[DEFAULT] CHARACTER SET [=] charset_name 設定默認的字符集 [DEFAULT] COLLATE [=] collation_name （設定默認的排序規則）
-
修改
ALTER {DATABASE | SCHEMA} [db_name]
CHARACTER SET [=] charset_name COLLATE [=] collation_name ‘
-
刪除
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
相關命令：
SHOW CHARACTER SET SHOW COLLATION SHOW CREATE DATABASE db_name
（二）、表管理
引用方式：
tbl_name db_name.tbl_name 上一種必須先要用 use 先切換到那個數據庫中才能用
1、表創建：之 一
CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,…) [table_options]
create_definition:由逗號分隔的列表
字段定義：
column_name column_defination
約束定義：
PRIMARY KEY(col1[,col2, ....]) 主鍵
UNIQUE KEY ：惟一鍵
FOREIGN KEY ：外鍵約束
CHECK(expr) ：檢查約束
索引定義：
{INDEX|KEY}
{FULLTEXT|SPATIAL}
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
table_option:
ENGINE [=] engine_name
-
查看數據庫支持的存儲引擎種類：
SHOW ENGINES;
-
查看表狀態信息：
SHOW TABLE STATUS [WHERE CLAUSE] [LIKE CLAUSE]
第二種表創建方式：復制表結構
第三種表創建方式：復制表數據
2、表修改：
ALTER TABLE tbl_name [alter_specification [, alter_specification] …]
alter_specification:
-
(1) 表選項
ENGINE=engine_name 改引擎 ...
-
(a) 字段
-
添加：
ALTER TABLE tbl_name ADD [COLUMN] col_name data_type [FIRST | AFTER col_name ]
-
刪除：
ALTER TABLE tbl_name DROP [COLUMN] col_name
-
修改：
CHANGE（改名和定義）：CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] MODIFY（只改定義）：MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
-
(b) 鍵
-
添加
ADD {PRIMARY|UNIQUE|FOREIGN} key (col1, col2, ...) -
刪除
DROP {PRIMARY|UNIQUE|FOREIGN} KEY key_name；
-
c、索引
-
添加
ADD INDEX(col1, col2, ...);
-
刪除
DROP INDEX index_name；
查看表上的索引信息：
SHOW INDEXES FROM tbl_name；
查看表創建語句：
SHOW CREATE TABLE tbl_name
2、表刪除：
DROP TABLE [IF EXISTS] tbl_name [, tbl_name] …
（三）索引管理：
索引是特殊的數據結構
單獨管理索引時，要有索引名稱！
創建：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (字段1上、字段2上、…)
默認的索引類型是btree索引，也有HASH但不能用
index_col_name: col_name [(length)] [ASC | DESC]
刪除：
DROP INDEX index_name ON tbl_name
查看：
SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
DML語句：
INSERT，SELECT，DELETE，UPDATE
INSERT：
INSERT [INTO] tbl_name [(col_name,…)] {VALUES | VALUE} ({expr | DEFAULT},…),(…),…
注意：字符型數據要用引號 數值型數據不能用引號
查找所有的表中內容，生產工作中盡量不要用，相當于對庫進行了一次攻擊
–
–
–
–
SELECT：
-
(1) SELECT * FROM tbl_name; 查看表中的所有數據
-
(2) SELECT col1, col2, … FROM tbl_name;
字段可以顯示為別名：col1 AS ALIAS
-
(3) SELECT col1, col2, … FROM tbl_name WHERE CLUASE;
WHERE expr：布爾表達式； col_name OPERATOR value|col_name; 操作符： >, <, <=, >=, =, != BETWEEN ... AND ... LIKE 'PATTERN'： 通配符： _：匹配任意單個字符； %：任意長度的任意字符； RLIKE 'PATTERN':可以使用正則表達式模式匹配 IN(list) 組合條件： and, or, not IS NULL IS NOT NULL
-
(4)查詢以后做排序
SELECT col1, … FROM tbl1_name [WEHRE CLAUSE] ORDER BY col1, col2, … [ASC|DESC]
ASC：升序
DESC：降序
DELETE：刪除
DELETE FROM tbl_name [WHERE where_condition] [ORDER BY] [LIMIT row_count]
（1）、DELETE FROM tbl_name [WHERE where_condition]
（2）、DELETE FROM tbl_name [ORDER BY] [LIMIT row_count]
UPDATE：更新數據
UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] … [WHERE where_condition]
—
圖形管理工具：
phpMyAdmin Navicat mysql-front SQLyog ToadForMySQL
用戶賬號及權限管理;
用戶賬號：'username'@'host'
host:此用戶訪問當前mysql服務時，允許通過那些主機遠程創建連接
表示方式：IP，網絡地址、主機名、通配符（%和_）;
-
禁止檢查主機名：my.cnf
[mysqld]
skip_name_resolve=ON
創建用戶賬號：
CREATE USER 'username'@'host' [IDENTIFIED BY 'passwd'];
再更新下數據庫
FLUSH PRIVILEGES;
注意這樣創建的用戶沒有寫入權限，只有讀取權限，無法查看元數據庫
刪除用戶賬號
DPOP USER 'username'@'host' [,'username'@'host']
授權：
級別：管理權限、數據庫、表、字段、存儲歷程。
GRANT priv_type ON [object_type] db_name.tbl_name TO 'username'@'host' [IDENTIFIED BY 'passwd'];
priv_type: ALL [PRIVILEGES] db_name.tb_name: *.*: 所有庫的所表； db_name.*: 指定庫的所有表； db_name.tb_name: 指定庫的指定表； db_name.routine_name：指定庫的存儲例程； object_type: TABLE | FUNCTION | PROCEDURE
-
查看指定用戶獲得的授權：
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER;
查看當前用戶的權限
-
回收授權：
REVOKE priv_type, … ON db_name.tb_name FROM 'user'@'host';
注意：MariaDB服務進程啟動時會讀取mysql庫中的所有授權表至內存中； (1) GRANT或REVOKE等執行權限操作會保存于表中，MariaDB的服務進程會自動重讀授權表； (2) 對于不能夠或不能及時重讀授權表的命令，可手動讓MariaDB的服務進程重讀授權表： mysql> FLUSH PRIVILEGES;
mysql剛裝上后要用 mysql_secure_installation 設定一下安全權限
**重新初始化數據庫命令 ：mysql_install_db –user=mysql –database=/path/to/newpath
**
在修改了數據庫數據保存默認位置時使用
編譯安裝mariadb
安裝MariaDB：
預制的包管理器格式的程序包： rpm包： os vendor：mariadb-devel MariaDB官方 通用二進制格式的程序包： 源碼包編譯：項目構建工具為cmake, 而非流行的make； 通用二進制格式包的安裝配置步驟： # useradd -r mysql # tar xf mariadb-VERSION-linux-x86_64.tar.gz -C /usr/local/ # cd /usr/local/ # ln -sv mariadb-VERSION-linux-x86_64 mysql # cd mysql # chown -R root:mysql ./* # mkdir -pv /mydata/data # chown -R mysql.mysql /mydata/data # scripts/mysql_install_db --user=mysql --datadir=/mydata/data --skip_name_resolve # cp support-files/my-large.cnf /etc/my.cnf [mysqld] ....... datadir = /mydata/data skip_name_resolve = ON innodb_file_per_table = ON # cp support-files/mysql.server /etc/rc.d/init.d/mysqld # chkconfig --add mysqld
原創文章，作者：qzx，如若轉載，請注明出處：http://www.www58058.com/52836
