MySQL數據庫簡介與基本操作

Posted on 2022-05-30 by WalkonNet

一、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數據庫簡介與基本操作的文章就介紹到這瞭。希望對大傢的學習有所幫助，也希望大傢多多支持WalkonNet。

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簡介

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、數據恢復

發佈留言取消回覆