首页 >数据库 >mysql教程 > 正文

MySQL数据库的基本操作实例分析

转载2023-05-26 23:27:541093

一、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中文网其它相关文章！

微信

分享

本站声明：本文转载于：亿速云，如有侵犯，请联系admin@php.cn删除

相关标签：MySQL

• 上一篇：Linux怎样查看mysql密码
• 下一篇：mysql内连接查询实例分析

相关文章

相关视频

---

• MySQL中怎么用Union优化Like语句
• MySQL中DAYOFYEAR函数怎么用
• Centos7怎么配置Mysql多主一从
• php+mysql如何使用存储过程
• MySQL数据库的基本操作实例分析

视频教程分类

• php视频教程
• html视频教程
• css视频教程
• JS视频教程
• jQuery视频教程
• mysql视频教程
• Linux视频教程
• Python视频教程
• Laravel视频教程
• Vue视频教程

专题推荐

• 独孤九贱-php全栈开发教程

  全栈 170W+

  主讲：Peter-Zhu 轻松幽默、简短易学，非常适合PHP学习入门

• 玉女心经-web前端开发教程

  入门 80W+

  主讲：灭绝师太 由浅入深、明快简洁，非常适合前端学习入门

• 天龙八部-实战开发教程

  实战 120W+

  主讲：西门大官人 思路清晰、严谨规范，适合有一定web编程基础学习

作者信息

WBOY

长风破浪会有时，直挂云帆济沧海。

微信小程序中PHP开发的实时监控和统计分析工具介绍192

PHP如何实现微信小程序中的图片合成和处理功能642

MySQL中CONCAT()函数拼接出现NULL问题如何解决981

• 最新文章
• 热门排行

• php中文网为您推荐几个好用的源码
• mysql查询结果怎么实现多列拼接查询
• mysql数据类型实例分析
• MySQL中CONCAT()函数拼接出现NULL问题如何解决
• php怎么将数组转换为MySQL查询语句
• CentOS怎么编译安装Nginx1.10.1+MySQL5.7.14+PHP7.0.11
• MySQL回表指的是什么
• 如何使用Docker部署MySQL5.7&8.0主从集群
• mysql字符串存储过程是什么

• mysql怎么卸载干净？
• sql是什么意思
• sql增删改查语句是什么？
• mysql和oracle的区别有哪些
• 数据库是什么
• 数据库sql server怎么完全卸载？
• 一文带你深入了解MySQL安装和配置（图文教程）
• 什么是最大公约数和最小公倍数？

推荐视频教程

PHP直播课

• 相关教程
• 热门推荐
• 最新课程

• 

  独孤九贱(3)_JavaScript视频教程

  312845次学习

  收藏
• 

  独孤九贱(6)_jQuery视频教程

  310847次学习

  收藏
• 

  php8，我来也

  342528次学习

  收藏
• 

  最懂你的大前端课：HTML5/CSS3/ES6/NPM/Vue/...【原创】

  66556次学习

  收藏
• 

  Thinkphp6.0正式版视频教程

  366536次学习

  收藏

• 

  phpStudy极速入门视频教程

  505085次学习

  收藏
• 

  独孤九贱(4)_PHP视频教程

  1148874次学习

  收藏
• 

  PHP实战天龙八部之仿爱奇艺电影网站

  710370次学习

  收藏
• 

  独孤九贱(1)_HTML5视频教程

  578925次学习

  收藏
• 

  独孤九贱(5)_ThinkPHP5视频教程

  1149420次学习

  收藏

• 

  go语言零基础开发内容管理系统

  984次学习

  收藏
• 

  运维自动化平台项目

  2010次学习

  收藏
• 

  光速学会docker容器

  1295次学习

  收藏
• 

  PHP入门到实战消息队列RabbitMQ

  829次学习

  收藏
• 

  TypeScript-45分钟入门

  737次学习

  收藏