MySQL資料庫簡介與基本操作

2022-05-30 22:04:56

一、MySQL簡介

1、資料庫管理軟體分類

主要分為關係型和非關係型。

可以簡單的理解為，關係型資料庫需要有表結構，非關係型資料庫是key-value儲存的，沒有表結構。

關係型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql語句通用。

非關係型：mongodb，redis，memcache

2、MySQL

MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB 公司開發，目前屬於 Oracle 旗下產品。

MySQL是最流行的關係型資料庫管理系統之一，在 WEB 應用方面，MySQL是最好的RDBMS (關聯式資料庫管理系統)應用軟體。

MySQL所使用的 SQL 語言是用於存取資料庫的最常用標準化語言。MySQL 軟體採用了雙授權政策，分為社群版和商業版，由於其體積小、速度快、總體擁有成本低，尤其是開放原始碼這一特點，一般中小型網站的開發都選擇 MySQL 作為網站資料庫。

MySQL為我們提供開源的安裝在各個作業系統上的安裝包，包括mac，linux，windows。

二、儲存引擎(也稱作表型別)

MySQL中的資料用各種不同的技術儲存在檔案（或者記憶體）中。每一種技術都使用不同的儲存機制、索引技巧、鎖定水平並且最終提供廣泛的不同的功能和能力。這些不同的技術以及配套的相關功能在MySQL中被稱作儲存引擎(也稱作表型別)。

MySQL預設設定了許多不同的儲存引擎，可以預先設定或者在MySQL伺服器中啟用。

1、常用儲存引擎及適用場景

InnoDB：用於事務處理應用程式，支援外來鍵和行級鎖。如果應用對事物的完整性有比較高的要求，在並行條件下要求資料的一致性，資料操作除了插入和查詢之外，還包括很多更新和刪除操作，那麼InnoDB儲存引擎是比較合適的。
InnoDB除了有效的降低由刪除和更新導致的鎖定，還可以確保事務的完整提交和回滾，對於類似計費系統或者財務系統等對資料準確要求性比較高的系統都是合適的選擇。
MyISAM：如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完整性、並行性要求不高，那麼可以選擇這個儲存引擎。
Memory：將所有的資料儲存在記憶體中，在需要快速定位記錄和其他類似資料的環境下，可以提供極快的存取。
Memory的缺陷是對錶的大小有限制，雖然資料庫因為異常終止的話資料可以正常恢復，但是一旦資料庫關閉，儲存在記憶體中的資料都會丟失。

mysql支援的儲存引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、 NDB、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。
其中NDB和InnoDB提供事務安全表，其他儲存引擎都是非事務安全表。

2、儲存引擎在mysql中的使用

# 檢視當前的預設儲存引擎:
mysql> show variables like "default_storage_engine";

# 查詢當前資料庫支援的儲存引擎
mysql> show engines G;

1、在建表時指定儲存引擎

mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; 
mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;

# 也可以使用alter table語句，修改一個已經存在的表的儲存引擎。
mysql> alter table ai engine = innodb;

2 、在組態檔中指定儲存引擎

# my.ini檔案
[mysqld]
default-storage-engine=INNODB

三、MySQL表操作

1、檢視表結構

檢視表結構有兩種方式：

desc[tablename]和describe [tablename]：這兩種方法和效果相同，可以檢視當前的表結構。
show create table [tablename]：除了可以看到表定義之外，還可以看到engine（儲存引擎）和charset（字元集）等資訊。（G選項的含義是是的記錄能夠豎向排列，以便更好的顯示內容較長的記錄。）

舉例：

mysql> desc staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
rows in set (0.00 sec)

mysql> show create table staff_infoG;
*************************** 1. row ***************************
       Table: staff_info
Create Table: CREATE TABLE `staff_info` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  `phone` bigint(11) DEFAULT NULL,
  `job` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.01 sec)

ERROR: 
No query specified

2、自動增長列

約束欄位為自動增長，被約束的欄位必須同時被key主鍵約束

--不指定id，則自動增長
create table student(id int primary key auto_increment,name varchar(20),sex enum('male','female') default 'male');

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | 
auto_increment  |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values ('nick'),('tank') ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | nick | male |
|  2 | tank | male |
+----+------+------+


--也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | nick | male   |
|  2 | tank | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+


--對於自增的欄位，在用delete刪除後，再插入值，該欄位仍按照刪除前的位置繼續增長
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+

--應該用truncate清空表，比起delete一條一條地刪除記錄，truncate是直接清空表，在刪除大表時用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('nick');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | nick | male |
+----+------+------+
row in set (0.00 sec)

--在建立完表後，修改自增欄位的起始值
mysql> create table student(id int primary key auto_increment, name varchar(20),sex enum('male','female') default 'male');
mysql> alter table student auto_increment=3 ;
mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

--也可以建立表時指定auto_increment的初始值，注意初始值的設定為表選項，應該放到括號外
mysql> create table student(id int primary key auto_increment, name varchar(20),sex enum('male','female') default 'male' 
                           )auto_increment=3 ;

四、MySQL支援的資料型別

1、ENUM和SET型別

ENUM中文名稱叫列舉型別，它的值範圍需要在建立表時通過列舉方式顯示。
ENUM只允許從值集合中選取單個值，而不能一次取多個值。用途：單選：選擇性別

ENUM：
對1-255個成員的列舉需要1個位元組儲存;
對於255-65535個成員，需要2個位元組儲存;
最多允許65535個成員。

SET和ENUM非常相似，也是一個字串物件，裡面可以包含0-64個成員。根據成員的不同，儲存上也有所不同。
set型別可以允許值集合中任意選擇1或多個元素進行組合。對超出範圍的內容將不允許注入，而對重複的值將進行自動去重。用途：多選：興趣愛好性別

SET：
1-8個成員的集合，佔1個位元組
9-16個成員的集合，佔2個位元組
17-24個成員的集合，佔3個位元組
25-32個成員的集合，佔4個位元組
33-64個成員的集合，佔8個位元組

2、set/enum範例

mysql> create table t10 (name char(20),gender enum('female','male') );
Query OK, 0 rows affected (0.01 sec)

-- 選擇enum('female','male')中的一項作為gender的值，可以正常插入
mysql> insert into t10 values ('nick','male');
Query OK, 1 row affected (0.00 sec)

-- 不能同時插入'male,female'兩個值，也不能插入不屬於'male,female'的值
mysql> insert into t10 values ('nick','male,female');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

mysql> create table t11 (name char(20),hobby set('抽菸','喝酒','燙頭','翻車') );
Query OK, 0 rows affected (0.01 sec)

-- 可以任意選擇set('抽菸','喝酒','燙頭','翻車')中的項，並自帶去重功能
mysql> insert into t11 values ('tank','燙頭,喝酒,燙頭');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t11;
+------+---------------+
| name | hobby        |
+------+---------------+
| tank | 喝酒,燙頭     |
+------+---------------+
row in set (0.00 sec)

-- 不能選擇不屬於set('抽菸','喝酒','燙頭','翻車')中的項，
mysql> insert into t11 values ('jason','燙頭,翻車,看妹子');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

五、MySQL表查詢

1、限制查詢的記錄數（limit）

範例：

SELECT * FROM employee ORDER BY salary DESC 
    LIMIT 3;    --預設初始位置為0 

SELECT * FROM employee ORDER BY salary DESC
    LIMIT 0 , 5 ; --從第0開始，即先出第一條，然後包含這一條在內往後查5條

SELECT * FROM employee ORDER BY salary DESC
    LIMIT 5 , 5 ; --從第5開始，即先出第6條，然後包含這一條在內往後查5條

2、使用正規表示式查詢

小結：對字串匹配的方式

WHERE emp_name = 'nick';
WHERE emp_name LIKE 'sea%';
WHERE emp_name REGEXP 'on$';

SELECT * FROM employee WHERE emp_name REGEXP '^jas';
SELECT * FROM employee WHERE emp_name REGEXP 'on$';
SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';

六、資料備份(命令列)

1、資料庫的邏輯備份

--語法：
mysqldump -h 伺服器 -u使用者名稱 -p密碼 資料庫名 > 備份檔案.sql

--範例：
--單庫備份
mysqldump -uroot –p123  mysql > c:db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

--多庫備份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

--備份所有庫
mysqldump -uroot -p123 --all-databases > all.sql

2、資料恢復

--方法一：
[root@nick backup]-- mysql -uroot -p123 < /backup/all.sql

--方法二：
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;   --關閉二進位制紀錄檔，只對當前session生效
mysql> source /root/db1.sql

七、事務和鎖（SQL）

begin;  -- 開啟事務
   select * from emp where id = 1 for update;  -- 查詢id值，for update新增行鎖；
    update emp set salary=10000 where id = 1; -- 完成更新
commit; -- 提交事務

八、執行計劃Explain

執行計劃：讓mysql預估執行操作(一般正確)

Explain語法：

explain select … from … [where …]

Explain命令在解決資料庫效能上是第一推薦使用命令，大部分的效能問題可以通過此命令來簡單的解決，Explain可以用來檢視SQL語句的執行效果，可以幫助選擇更好的索引和優化查詢語句，寫出更好的優化語句。

具體用法和欄位含義可以參考官網explain-output ，這裡需要強調rows是核心指標，絕大部分rows小的語句執行一定很快（rows：顯示MySQL認為它執行查詢時必須檢查的行數。）。所以優化語句基本上都是在優化rows。

例如：

explain select * from news;

輸出：

+--+-----------+-----+----+-------------+---+-------+---+----+-----+ 
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| 
+--+-----------+-----+----+-------------+---+-------+---+----+----—+

到此這篇關於MySQL資料庫簡介與基本操作的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。

MySQL資料庫簡介與基本操作

目錄

一、MySQL簡介

1、資料庫管理軟體分類

2、MySQL

二、儲存引擎(也稱作表型別)

1、常用儲存引擎及適用場景

2、儲存引擎在mysql中的使用

1、在建表時指定儲存引擎

2 、在組態檔中指定儲存引擎

三、MySQL表操作

1、檢視表結構

2、自動增長列

四、MySQL支援的資料型別

1、ENUM和SET型別

2、set/enum範例

五、MySQL表查詢

1、限制查詢的記錄數（limit）

2、使用正規表示式查詢

六、資料備份(命令列)

1、資料庫的邏輯備份

2、資料恢復

七、事務和鎖（SQL）

八、執行計劃Explain

熱門文章

MySQL資料庫簡介與基本操作

目錄

一、MySQL簡介

1、資料庫管理軟體分類

2、MySQL

二、儲存引擎(也稱作表型別)

1、常用儲存引擎及適用場景

2、儲存引擎在mysql中的使用

1、 在建表時指定儲存引擎

2 、在組態檔中指定儲存引擎

三、MySQL表操作

1、檢視表結構

2、自動增長列

四 、MySQL支援的資料型別

1、ENUM和SET型別

2、set/enum範例

五、MySQL表查詢

1、限制查詢的記錄數（limit）

2、使用正規表示式查詢

六、資料備份(命令列)

1、 資料庫的邏輯備份

2、 資料恢復

七、事務和鎖（SQL）

八、執行計劃Explain

熱門文章

1、在建表時指定儲存引擎

四、MySQL支援的資料型別

1、資料庫的邏輯備份

2、資料恢復