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 (關聯式資料庫管理系統)應用軟體。

SQL語言是最常用的標準化語言，被用來存取MySQL資料庫。 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資料庫的基本操作實例分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！