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

一、MySQL简介1、数据库管理软件分类

主要分为关系型和非关系型。

可以简单的理解为,关系型数据库需要有表结构,非关系型数据库是key-value存储的,没有表结构。

关系型:如sqllite,db2,oracle,access,sql server,MySQL,注意:sql语句通用。

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

非关系型: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 &
hellip;
from &
hellip;
[where &
hellip;
]

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是一款出色的关系型数据库,是Web开发的事实标准之一。本文将介绍MySQL数据库的基本操作。如果你对MySQL不熟悉或需要一些刷新,那么这篇文章就是对你最好的服务。
一、MySQL数据库的基本操作
本节将简要介绍如何在MySQL数据库中建立一个新的数据库,创建一个新的数据表,并向该表添加数据。
首先,让我们通过以下命令登录MySQL服务器:
mysql -u root -p
这将询问您的MySQL管理员密码,输入密码后按回车。
接下来,我们将创建一个名为\"mydb\"的新数据库,输入以下命令:
CREATE DATABASE mydb;
MySQL数据库现在应该包含一个新的数据库名为“mydb”。
接下来,我们将创建一个名为“mytable”的新的数据表。 Table具有名称,“ ID”(整数类型),“ NAME”(字符类型),和“ ADDRESS”(字符类型)字段。
CREATE TABLE mytable ( ID INT NOT NULL AUTO_INCREMENT, NAME VARCHAR(30) NOT NULL, ADDRESS VARCHAR(50), PRIMARY KEY (ID));
通过这个命令,我们不仅 creating表,我们还定义了ID列作为主键。此时,该表应处于数据表管理平台的“tables”列表中。我们可以通过简单的查询来验证这一点:
SHOW TABLES;
我们现在可以向“mytable”数据表中添加一些数据。为此,我们使用“ INSERT INTO”命令,它将添加一些新行到数据表中。以下将添加“ ID”等于1,“ NAME”等于“ Alice”,并且“ ADDRESS”等于“ New York”(注意,数据表中的字段采用单引号)的一行:
INSERT INTO mytable (ID, NAME, ADDRESS) VALUES (1, 'Alice', 'New York');
我们重复此操作,将ID值增加1,以便添加更多行数据。例如:
INSERT INTO mytable (ID, NAME, ADDRESS) VALUES (2, 'Bob', 'Los Angeles');
这些简单的示例足以开始操作MySQL数据库,但是还有很多内容需要掌握,包括更多复杂的查询和所有的MySQL指令。
二、MySQL查询操作
查询是我们需要MySQL的主要原因之一,因此在这篇文章中的第二个部分,我们将探讨如何执行MySQL查询操作。
查询需要使用SELECT语句,允许按不同的方式查看结果。
例如,以下查询将返回mytable的所有列和所有行:
SELECT * FROM mytable;
这是最简单的查询,您也可以按道理限制列和行。
例如,以下查询仅返回“ NAME”列和匹配“ New York”行:
SELECT NAME FROM mytable WHERE ADDRESS='New York';
这利用了“ WHERE”子句,它是一个选择语句。您可以根据其它要求,筛选所需的查询信息。
最后,SQL支持 ORDER BY 子句,这使我们可以对结果集进行排序。例如,以下查询将按照“ NAME”列的升序排序返回所有内容:
SELECT * FROM mytable ORDER BY NAME ASC;
加法,MySQL支持许多特定类型查询的特殊语句。
三、MySQL查询操作
在这篇文章的最后一部分,我们将介绍如何使用MySQL管理数据库。
管理员可以使用MySQL来删除数据库,数据表或者表中的行。
例如,对于删除数据库,我们使用以下命令:
DROP DATABASE mydb;
对于删除数据表,我们使用以下命令:
DROP TABLE mytable;
最后,对于删除表中的一行,我们使用以下命令:
DELETE FROM mytable WHERE ID=2;
这应该很清楚了,我们将“ID等于2”的行从“mytable”数据表中删除。
MySQL既是一项强大的工具,又是网站和Web应用程序的必备组件。本文旨在为初学者提供相关的示例,以便更好地熟悉MySQL。。
三、总结
本文已经涵盖了MySQL数据库的基本操作。我们已经看到了如何创建新的数据库和数据表,如何向数据表中添加数据,如何执行简单和复杂的SELECT查询,并了解了一些MySQL的常用管理技巧。花费一些时间熟练掌握这些内容,您便可以成为一名MySQL专家。