mysql基礎(chǔ)

數(shù)據(jù)庫基本概念

MariaDB or MySQL官網(wǎng)：https://db-engines.com/en/ranking

磁盤文件--> 層次模型 --> 網(wǎng)狀模型 --> (Codd) 關(guān)系模型

DBMS是管理數(shù)據(jù)庫的系統(tǒng)軟件琅催，它實現(xiàn)數(shù)據(jù)庫系統(tǒng)的各種功 能晃跺。是數(shù)據(jù)庫系統(tǒng)的核心

DBA：負責數(shù)據(jù)庫的規(guī)劃焙矛、設(shè)計垛吗、協(xié)調(diào)、維護和管理等工作

關(guān)系 ：關(guān)系就是二維表叠艳。并滿足如下性質(zhì)： 表中的行奶陈、列次序并不重要

行row：表中的每一行，又稱為一條記錄

列column：表中的每一列虑绵，稱為屬性尿瞭，字段

主鍵（Primary key）：用于惟一確定一個記錄的字段；復(fù)合主鍵：多個字段組合成一個主鍵(NOT NULL)翅睛；

惟一鍵：一個或多個字段的組合，填入的數(shù)據(jù)必須能在本表 中唯一標識本行黑竞；允許為NULL捕发，一個表可以存在多個

約束：

　　主鍵約束：惟一、非空很魂；一張表只能有一個扎酷；

　　惟一鍵約束：惟一，可以存在多個遏匆；

　　外鍵約束：參考性約束法挨；

　　檢查性約束：check谁榜；

三層模型：

　　物理層 --> SA (決定數(shù)據(jù)的存儲格式，即RDBMS在磁盤上如何組織文件)

　　邏輯層 --> DBA(描述存儲什么數(shù)據(jù)凡纳，以及數(shù)據(jù)間存在什么樣的關(guān)系)

　　視圖層 --> Coder(描述DB中的部分數(shù)據(jù))

域domain：屬性的取值范圍窃植，如，性別只能是‘男’和‘女’ 兩個值

表：row, column荐糜；

關(guān)系運算：選擇巷怜、投影

數(shù)據(jù)庫：表、索引暴氏、視圖（虛表）延塑、SQL、存儲過程procedure答渔，過程無返回值关带、存儲函數(shù)function，過程有返回值沼撕、觸發(fā)器trigger豫缨、事件調(diào)度器event scheduler，任務(wù)計劃端朵；

事務(wù)transaction：多個操作被當作一個整體對待 ACID:?原子性（Atomicity）好芭、一致性（Consistency）、隔離性（Isolation冲呢，又稱獨立性）舍败、持久性（Durability）。

原子性：整個事務(wù)中的所有操作敬拓，要么全部完成邻薯，要么全部不完成，不可能停滯在中間某個環(huán)節(jié)乘凸。事務(wù)在執(zhí)行過程中發(fā)生錯誤厕诡，會被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個事務(wù)從來沒有執(zhí)行過一樣营勤。

一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后灵嫌，數(shù)據(jù)庫的完整性約束沒有被破壞。

隔離性：兩個事務(wù)的執(zhí)行是互不干擾的葛作，一個事務(wù)不可能看到其他事務(wù)運行時寿羞，中間某一時刻的數(shù)據(jù)。

持久性：在事務(wù)完成以后赂蠢，該事務(wù)所對數(shù)據(jù)庫所作的更改便持久的保存在數(shù)據(jù)庫之中绪穆，并不會被回滾。

數(shù)據(jù)的操作：? 數(shù)據(jù)提取：在數(shù)據(jù)集合中提取感興趣的內(nèi)容玖院。（SELECT）? 數(shù)據(jù)更新：變更數(shù)據(jù)庫中的數(shù)據(jù)菠红。（INSERT、DELETE难菌、 UPDATE）

應(yīng)用程序指以數(shù)據(jù)庫為基礎(chǔ)的應(yīng)用程序：DBMS --> RDBMS（關(guān)系型數(shù)據(jù)庫管理系統(tǒng)）

設(shè)計關(guān)系數(shù)據(jù)庫時试溯，遵從不同的規(guī)范要求，設(shè)計出合理的 關(guān)系型數(shù)據(jù)庫扔傅，這些不同的規(guī)范要求被稱為不同的范式耍共，各種范 式呈遞次規(guī)范，越高的范式數(shù)據(jù)庫冗余越小猎塞。

目前關(guān)系數(shù)據(jù)庫有六種范式：第一范式（1NF）试读、第二范式 （2NF）、第三范式（3NF）荠耽、巴德斯科范式（BCNF）钩骇、第 四范式(4NF）和第五范式（5NF，又稱完美范式）铝量。滿足最 低要求的范式是第一范式（1NF）倘屹。在第一范式的基礎(chǔ)上進 一步滿足更多規(guī)范要求的稱為第二范式（2NF），其余范式 以次類推慢叨。一般說來纽匙，數(shù)據(jù)庫只需滿足第三范式(3NF）即可

RDBMS：

范式：第一范式、第二范式拍谐、第三范式烛缔；

1NF：無重復(fù)的列，每一列都是不可分割的基本數(shù)據(jù)項轩拨，同 一列中不能有多個值

2NF：屬性完全依賴于主鍵践瓷，第二范式必須先滿足第一范式 ，要求表中的每個行必須可以被唯一地區(qū)分亡蓉。

3NF：屬性不依賴于其它非主屬性晕翠，滿足第三范式必須先滿 足第二范式。第三范式要求一個數(shù)據(jù)庫表中不包含已在其它表中已包含的非主關(guān)鍵字信息砍濒，非PK的字段間不能有從屬關(guān)系

SQL: Structure Query Language：結(jié)構(gòu)化查詢語言--- SQL解釋器---數(shù)據(jù)存儲協(xié)議：應(yīng)sq用層協(xié)議淋肾，C/S

olacle(tcp:1521),mysql(tcp:3306),sql ?server(tcp:1433)

mysql基礎(chǔ)和應(yīng)用結(jié)構(gòu)

mysql特性：插件式存儲引擎、單進程多線程

mysql體系結(jié)構(gòu)：

安裝：服務(wù)端mysql-server和客戶端mysql梯影，yum安裝巫员，二進制安裝，源碼編譯安裝

提高安全性 ：運行mysql_secure_installation

服務(wù)端程序：

mysqld, mysqld_safe, mysqld_multi

客戶端程序：

mysql, mysqldump, mysqlbinlog, mysqladmin, ...

非客戶端類管理程序：

myisamchk, myisampack, ...

配置文件： /etc/my.cnf 和 /etc /my.cnf.d/ *.cnf

mysql命令選項：

-uUSERNAME: 用戶名甲棍；默認為root

-hHOST: 服務(wù)器主機; 默認為localhost

-pPASSWORD：用戶的密碼;建議使用-p,默認為空密碼

mysql用戶賬號由兩部分組成： 'USERNAME'@'HOST' HOST用于限制此用戶可通過哪些遠程主機連接mysql服務(wù)

支持使用通配符： % 匹配任意長度的任意字符 172.16.64.0/16 或 172.16.%.% _ 匹配任意單個字符

運行mysql前通常修改的參數(shù)

在配置文件的[mysqld]中添加兩個選項：?

innodb_file_per_table = on ?啟用innodb存儲引擎

skip_name_resolve = on 禁止主機名解析

MYSQL語言基礎(chǔ)

在數(shù)據(jù)庫系統(tǒng)中，SQL語句不區(qū)分大小寫(建議用大寫) ，但字符串常量區(qū)分大小寫

SQL語句可單行或多行書寫感猛，以“;”結(jié)尾七扰，關(guān)鍵詞不能跨多行或簡寫

用空格和縮進來提高語句的可讀性 ，句通常位于獨立行陪白，便于編輯颈走，提高可讀性

注釋： SQL標準： /*注釋內(nèi)容*/ 多行注釋 ? ? ?-- 注釋內(nèi)容 單行注釋，注意有空格

MySQL注釋： #

數(shù)據(jù)庫對象的命名規(guī)則：1.必須以字母開頭 咱士；2.可包括數(shù)字和三個特殊字符（# _ $）立由；3.不要使用MySQL的保留字 ；4.同一Schema下的對象不能同名

MySQL的數(shù)據(jù)類型：字符型序厉、數(shù)值型锐膜、日期時間型、內(nèi)建類型

字符型：CHAR(#)弛房， BINARY(#)：定長型道盏；CHAR不區(qū)分字符大小寫，而BINARY區(qū)分文捶；

? ? ? ? ? ? ? ?VARCHAR(#)荷逞， VARBINARY(#)：變長型TEXT：TINYTEXT，TEXT粹排，MEDIUMTEXT种远，LONGTEXT

? ? ? ? ? ? ? ?BLOB：TINYBLOB，BLOB顽耳，MEDIUMBLOB坠敷， LONGBLOB

數(shù)值型：

浮點型：近似FLOAT、DOUBLE斧抱、REAL常拓、BIT

整型：精確：INTEGER：TINYINT，SMALLINT辉浦，MEDIUMINT弄抬，INT，BIGINT宪郊；DECIMAL

日期時間型：日期：DATE掂恕；時間：TIME；日期j時間：DATETIME弛槐；間戳：TIMESTAMP懊亡；年份：YEAR(2), YEAR(4)

內(nèi)建：ENUM：枚舉：ENUM('Sun','Mon','Tue','Wed')；SET：集合

類型修飾符：

　　　　字符型：NOT NULL乎串，NULL店枣，DEFALUT ‘STRING’，CHARACET SET ‘CHARSET’，COLLATION ‘collocation'

　　　　整型：NOT NULL鸯两， NULL闷旧， DEFALUT value, AUTO_INCREMENT, UNSIGNED（使用unsigned會使數(shù)據(jù)可變長度增長一倍）

　　　　日期時間型：NOT NULL， NULL钧唐， DEFAULT

DML:

INSERT/REPLACE（增）, DELETE（刪）, SELECT（查）, UPDATE（改）忙灼，ALTER。

創(chuàng)建數(shù)據(jù)庫：CREATE ? ?DATABASE ?|SCHEMA ? [IF NOT EXISTS] ? 'DB_NAME';?

刪除數(shù)據(jù)庫：DROP ? ?DATABASE ? | ? SCHEMA ? [IF EXISTS] ? 'DB_NAME';

查看支持所有字符集：SHOW CHARACTER SET; ?

查看支持所有排序規(guī)則：SHOW COLLATION;

查看數(shù)據(jù)庫列表：SHOW DATABASES; ?

創(chuàng)建表：CREATE ?TABLE ?'DB_TABLE_NAME' ;

刪除表：DROP TABLE [IF EXISTS] tb_name;

查看表： USE ? 'DB_NAME' ?; ?SHOW ?TABLES ;

查看支持的engine類型：SHOW ENGINES;

查看表結(jié)構(gòu)：DESC [db_name . ]tb_name;

查看表狀態(tài)：SHOW TABLE STATUS LIKE 'tbl_name'\G

實例：

CREATE TABLE students ? (id ? int ? UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED);

DESC students;

CREATE TABLE students2 (id ? int ? UNSIGNED NOT NULL ,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));

ALTER ? TABLE ? students ? RENAME s1; （重命名）

ALTER TABLE ?s1 ? ADD ?phone ?varchar(11) ? AFTER name;

ALTER TABLE ? s1 ?MODIFY ?phone int;

ALTER TABLE ? s1 CHANGE ?COLUMN ?phone ?mobile char(11);

ALTER TABLE ? s1 ?DROP ?COLUMN mobile;

ALTER TABLE students ADD gender ENUM('m','f') ? ?（枚舉钝侠，gender的域值為m和f）

ALETR TABLE students ?CHANGE id ? sid ?int ?UNSIGNED NOT NULL PRIMARY KEY;

ALTER TABLE students ADD UNIQUE KEY(name); ?（唯一鍵）

ALTER TABLE students ADD INDEX(age); ?（添加age的索引）

SHOW INDEXES FROM students;

ALTER TABLE students DROP age;

索引：定義在查找時作為查找條件的字段 ----優(yōu)點：提高查詢速度 该园；缺點：占用額外空間，影響插入速度 ?（創(chuàng)建索引必須要有索引名稱）

創(chuàng)建索引： CREATE INDEX ? index_name ? ON tbl_name (index_col_name,...);

刪除索引： DROP INDEX index_name ON tbl_name;

查看索引： SHOW INDEXES FROM [db_name.]tbl_name;

INSERT：（插入帅韧，增）

INSERT ? ?[INTO] ? ?tbl_name ? ?[(col_name,...)] ? {VALUES | VALUE} ? ? (val1,...),(...),...

UPDATE：（改）

UPDATE ? tbl_name ?SET col1=val1, col2=val2, ... [WHERE clause] ? ?[ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];

DELETE:：（刪）

DELETE ? FROM ? ?tbl_name ? [WHERE clause] ? [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n]; 可先排序再指定刪除的行數(shù)

SELECT col1,col2,... FROM tbl_name [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n]; ?Limit m,n 跳過m個里初，要n個

SELECT字段表示法： ?*： 所有字段 ? ? ?as：字段別名， col1 AS alias1 ?例：

WHERE clause:

操作符： >, <, >=, <=, ==, != , BETWEEN ... AND ...??

LIKE: ?%：任意長度的任意字符 ; ?_：任意單個字符弱匪；

RLIKE：正則表達式模式匹配

IS NULL 青瀑，IS NOT NULL IN （val1,val2,…）

條件邏輯操作： and，or萧诫，not

示例：

DESC students;

INSERT INTO students VALUES(1,'tom'斥难，'m'),(2,'alice','f');

INSERT INTO students ?(id,name) VALUES(3,'jack'),(4,'allen');

SELECT * FROM students WHERE id < 3;

SELECT * FROM students WHERE gender='m';

SELECT * FROM students WHERE gender IS NULL;

SELECT * FROM students WHERE gender IS NOT NULL;

SELECT * FROM students ORDER BY name DESC LIMIT 2; ?（符合條件的前兩個）

SELECT * FROM students ORDER BY name DESC LIMIT 1,2;

SELECT * FROM students ?WHERE id >=2 and id <=4;