MySQL數(shù)據(jù)庫簡介與基本操作
更新時間：2022年05月30日 15:06:21 作者：springsnow
這篇文章介紹了MySQL數(shù)據(jù)庫與其基本操作，文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下
一、MySQL簡介
1、數(shù)據(jù)庫管理軟件分類
主要分為關(guān)系型和非關(guān)系型。
可以簡單的理解為，關(guān)系型數(shù)據(jù)庫需要有表結(jié)構(gòu)，非關(guān)系型數(shù)據(jù)庫是key-value存儲的，沒有表結(jié)構(gòu)。
關(guān)系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql語句通用。
非關(guān)系型：mongodb，redis，memcache
2、MySQL
MySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng) ，由瑞典MySQL AB 公司開發(fā)，目前屬于 Oracle 旗下產(chǎn)品。
MySQL是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一，在 WEB 應(yīng)用方面，MySQL是最好的RDBMS (關(guān)系數(shù)據(jù)庫管理系統(tǒng))應(yīng)用軟件。
MySQL所使用的 SQL 語言是用于訪問數(shù)據(jù)庫的最常用標(biāo)準(zhǔn)化語言。MySQL 軟件采用了雙授權(quán)政策，分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點(diǎn)，一般中小型網(wǎng)站的開發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫。
MySQL為我們提供開源的安裝在各個操作系統(tǒng)上的安裝包，包括mac，linux，windows。
二、存儲引擎(也稱作表類型)
MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲在文件（或者內(nèi)存）中。每一種技術(shù)都使用不同的存儲機(jī)制、索引技巧、鎖定水平并且最終提供廣泛的不同的功能和能力。這些不同的技術(shù)以及配套的相關(guān)功能在MySQL中被稱作存儲引擎(也稱作表類型)。
MySQL默認(rèn)配置了許多不同的存儲引擎，可以預(yù)先設(shè)置或者在MySQL服務(wù)器中啟用。
1、常用存儲引擎及適用場景
- InnoDB：用于事務(wù)處理應(yīng)用程序，支持外鍵和行級鎖。如果應(yīng)用對事物的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作除了插入和查詢之外，還包括很多更新和刪除操作，那么InnoDB存儲引擎是比較合適的。
InnoDB除了有效的降低由刪除和更新導(dǎo)致的鎖定，還可以確保事務(wù)的完整提交和回滾，對于類似計(jì)費(fèi)系統(tǒng)或者財(cái)務(wù)系統(tǒng)等對數(shù)據(jù)準(zhǔn)確要求性比較高的系統(tǒng)都是合適的選擇。 - MyISAM：如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務(wù)的完整性、并發(fā)性要求不高，那么可以選擇這個存儲引擎。
- Memory：將所有的數(shù)據(jù)保存在內(nèi)存中，在需要快速定位記錄和其他類似數(shù)據(jù)的環(huán)境下，可以提供極快的訪問。
Memory的缺陷是對表的大小有限制，雖然數(shù)據(jù)庫因?yàn)楫惓＝K止的話數(shù)據(jù)可以正常恢復(fù)，但是一旦數(shù)據(jù)庫關(guān)閉，存儲在內(nèi)存中的數(shù)據(jù)都會丟失。
mysql支持的存儲引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、 NDB、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。
其中NDB和InnoDB提供事務(wù)安全表，其他存儲引擎都是非事務(wù)安全表。
2、存儲引擎在mysql中的使用
# 查看當(dāng)前的默認(rèn)存儲引擎: mysql> show variables like "default_storage_engine"; # 查詢當(dāng)前數(shù)據(jù)庫支持的存儲引擎 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語句，修改一個已經(jīng)存在的表的存儲引擎。 mysql> alter table ai engine = innodb;
2 、在配置文件中指定存儲引擎
# my.ini文件 [mysqld] default-storage-engine=INNODB
三、MySQL表操作
1、查看表結(jié)構(gòu)
查看表結(jié)構(gòu)有兩種方式：
- desc[tablename]和describe [tablename]：這兩種方法和效果相同，可以查看當(dāng)前的表結(jié)構(gòu)。
- show create table [tablename]：除了可以看到表定義之外，還可以看到engine（存儲引擎）和charset（字符集）等信息。（\G選項(xiàng)的含義是是的記錄能夠豎向排列，以便更好的顯示內(nèi)容較長的記錄。）
舉例：
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 specified2、自動增長列
約束字段為自動增長，被約束的字段必須同時被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刪除后，再插入值，該字段仍按照刪除前的位置繼續(xù)增長
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 |
+----+------+------+
--應(yīng)該用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)
--在創(chuàng)建完表后，修改自增字段的起始值
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
--也可以創(chuàng)建表時指定auto_increment的初始值，注意初始值的設(shè)置為表選項(xiàng)，應(yīng)該放到括號外
mysql> create table student(id int primary key auto_increment, name varchar(20),sex enum('male','female') default 'male'
)auto_increment=3 ;四 、MySQL支持的數(shù)據(jù)類型
1、ENUM和SET類型
- ENUM中文名稱叫枚舉類型，它的值范圍需要在創(chuàng)建表時通過枚舉方式顯示。
ENUM只允許從值集合中選取單個值，而不能一次取多個值。用途：單選：選擇性別
ENUM：
對1-255個成員的枚舉需要1個字節(jié)存儲;
對于255-65535個成員，需要2個字節(jié)存儲;
最多允許65535個成員。
- SET和ENUM非常相似，也是一個字符串對象，里面可以包含0-64個成員。根據(jù)成員的不同，存儲上也有所不同。
set類型可以允許值集合中任意選擇1或多個元素進(jìn)行組合。對超出范圍的內(nèi)容將不允許注入，而對重復(fù)的值將進(jìn)行自動去重。用途：多選：興趣愛好性別
SET：
1-8個成員的集合，占1個字節(jié)
9-16個成員的集合，占2個字節(jié)
17-24個成員的集合，占3個字節(jié)
25-32個成員的集合，占4個字節(jié)
33-64個成員的集合，占8個字節(jié)
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')中的一項(xiàng)作為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('抽煙','喝酒','燙頭','翻車')中的項(xiàng)，并自帶去重功能
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('抽煙','喝酒','燙頭','翻車')中的項(xiàng)，
mysql> insert into t11 values ('jason','燙頭,翻車,看妹子');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1五、MySQL表查詢
1、限制查詢的記錄數(shù)（limit）
示例：
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; --默認(rèn)初始位置為0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0 , 5 ; --從第0開始，即先出第一條，然后包含這一條在內(nèi)往后查5條
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5 , 5 ; --從第5開始，即先出第6條，然后包含這一條在內(nèi)往后查5條2、使用正則表達(dá)式查詢
小結(jié)：對字符串匹配的方式
- 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}';六、數(shù)據(jù)備份(命令行)
1、 數(shù)據(jù)庫的邏輯備份
--語法： mysqldump -h 服務(wù)器 -u用戶名 -p密碼 數(shù)據(jù)庫名 > 備份文件.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、 數(shù)據(jù)恢復(fù)
--方法一： [root@nick backup]-- mysql -uroot -p123 < /backup/all.sql --方法二： mysql> use db1; mysql> SET SQL_LOG_BIN=0; --關(guān)閉二進(jìn)制日志，只對當(dāng)前session生效 mysql> source /root/db1.sql
七、事務(wù)和鎖（SQL）
begin; -- 開啟事務(wù)
select * from emp where id = 1 for update; -- 查詢id值，for update添加行鎖；
update emp set salary=10000 where id = 1; -- 完成更新
commit; -- 提交事務(wù)八、執(zhí)行計(jì)劃Explain
執(zhí)行計(jì)劃：讓mysql預(yù)估執(zhí)行操作(一般正確)
Explain語法：
explain select … from … [where …]
Explain命令在解決數(shù)據(jù)庫性能上是第一推薦使用命令，大部分的性能問題可以通過此命令來簡單的解決，Explain可以用來查看SQL語句的執(zhí)行效果，可以幫助選擇更好的索引和優(yōu)化查詢語句，寫出更好的優(yōu)化語句。
具體用法和字段含義可以參考官網(wǎng)explain-output ，這里需要強(qiáng)調(diào)rows是核心指標(biāo)，絕大部分rows小的語句執(zhí)行一定很快（rows：顯示MySQL認(rèn)為它執(zhí)行查詢時必須檢查的行數(shù)。）。所以優(yōu)化語句基本上都是在優(yōu)化rows。
例如：
explain select * from news;
輸出：
+--+-----------+-----+----+-------------+---+-------+---+----+-----+? |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|? +--+-----------+-----+----+-------------+---+-------+---+----+----—+
到此這篇關(guān)于MySQL數(shù)據(jù)庫簡介與基本操作的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
Mysql數(shù)據(jù)庫百萬級數(shù)據(jù)測試索引效果
這篇文章主要為大家介紹了Mysql數(shù)據(jù)庫百萬數(shù)據(jù)測試索引效果，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2022-06-06
這篇文章主要介紹了mysql查看索引的方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-09-09
這篇文章主要介紹了MySQL常用慢查詢分析工具詳解，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-08-08
這篇文章主要為大家詳細(xì)介紹了Mac下MySQL5.7忘記root密碼的解決方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-01-01
將 Ghost 從 SQLite3 數(shù)據(jù)庫遷移到 MySQL 數(shù)據(jù)庫
如果網(wǎng)站流量小，直接使用 Ghost 默認(rèn)的 SQLite 數(shù)據(jù)庫還是很方便的，能夠省去安裝、配置數(shù)據(jù)庫的繁瑣步驟。但是，隨著網(wǎng)站流量的增加， SQLite 就慢慢頂不住了，這時最好的選擇就是使用 MySQL 數(shù)據(jù)庫。2014-07-07
解決數(shù)據(jù)庫有數(shù)據(jù)但查詢出來的值為Null問題
這篇文章主要介紹了解決數(shù)據(jù)庫有數(shù)據(jù)但查詢出來的值為Null問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-10-10
MySQL修改表結(jié)構(gòu)操作命令總結(jié)
這篇文章主要介紹了MySQL修改表結(jié)構(gòu)操作命令總結(jié),包含如刪除列、添加列、修改列、添加主鍵、刪除主鍵、添加唯一索引、添加普通索引等內(nèi)容,需要的朋友可以參考下2014-12-12
CentOS下php使用127.0.0.1不能連接mysql的解決方法
這篇文章主要介紹了CentOS下php使用127.0.0.1不能連接mysql的解決方法,本文原因是SELINUX導(dǎo)致的連接失敗,需要的朋友可以參考下2015-01-01
Sql查詢MySql數(shù)據(jù)庫中的表名和描述表中字段(列)信息
這篇文章主要介紹了Sql查詢獲取MySql數(shù)據(jù)庫中的表名和描述表中列名數(shù)據(jù)類型,長度,精度,是否可以為null,默認(rèn)值,是否自增,是否是主鍵,列描述等列信息2017-12-12
MySQL分割字符串一行轉(zhuǎn)多行的實(shí)現(xiàn)方法
這篇文章主要介紹了MySQL分割字符串一行轉(zhuǎn)多行,本文通過實(shí)例代碼給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-06-06