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| 
+--+-----------+-----+----+-------------+---+-------+---+----+----—+