MySQL數據庫的基本操作實例分析

本文小編為大家詳細介紹“MySQL數據庫的基本操作實例分析”，內容詳細，步驟清晰，細節處理妥當，希望這篇“MySQL數據庫的基本操作實例分析”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學習新知識吧。

一、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_info\G;
*************************** 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數據庫的基本操作實例分析”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注億速云行業資訊頻道。