MySQL数据库与基本操作(增删改查)

一、数据库的基本概念

数据库要学习的四个基本概念，主要是：数据、数据库系统、数据库、数据管理系统。数据（Date）是描述事物的记录，数据库系统（DBS），数据库管理系统（DBMS）相当于仓库管理员，数据库（DB）。他们之间的关系是数据库系统包括数据库管理系统，数据库，数据。

使用数据库可以高效且条理分明的存储数据，使人们能够更加迅速、方便的管理数据。数据库具有以下特点：

可以结构化存储大量的数据信息，方便用户进行有效的检索和访问
可以有效的保持数据信息的一致性、完整性，降低数据冗余
可以满足应用的共享和安全方面的要求

1.1 数据库的组成

数据（Date）

描述事物的符号记录
包括数字，文字、图形，图像、声音、档案记录等
以“记录”形式按统一的格式进行储存
相同格式和类型的数据统一存放在一起，而不会把"人”和"书"混在一起存储。这样，数据的存储就能够井然有序。

数据如何保存：最外层是mysql服务—》mysql 数据库—》数据表—》记录为行，字段为列—>数据保存在一行行记录中

表

将不同的记录组织在一起
用来储存具体数据
记录：行
字段(属性)：列
以行+列的形式就组成了表(数据存储在表中)

关系数据库的表由记录组成，记录由字段组成，字段由字符或数字组成。它可以供各种用户共享，具有最小冗余度和较高的数据独立性，它是统一管理的相关数据的集合。

数据库

表的集合，是存储数据的仓库
以一定的组织方式存储的相互有关的数据集合

1.2 数据库管理系统（DBMS）

是实现对数据库资源有效组织、管理和存取的系统软件，在操作系统的支持下，支持用户对数据库的各项操作

DBMS主要包括以下功能：

数据库的建立和维护功能：包括建立数据库的结构和数据的录入与转换、数据库的转储与恢复、数据库的重组与性能监视等功能。
数据定义功能：包括定义全局数据结构、局部逻辑数据结构、存储结构、保密模式及信息格式等功能。保证存储在数据库中的数据正确、有效和相容，以防止不合语义的错误数据被输入或输出。
数据操纵功能：包括数据查询统计和数据更新两个方面。
数据库的运行管理功能:这是数据库管理系统的核心部分，包括并发控制、存取控制、数据库内部维护等功能。
通信功能：DBMS 与其他软件系统之间的通信,如Access能与其他office组件进行数据交换。

1.3 数据库系统（DBS）

是一个人机系统，由硬件、OS、数据库、DBMS、应用软件和数据库用户组成
用户可以通过DBMS或应用程序操作数据库

1.4 DBMS的工作模式

接受应用程序的数据请求和处理请求
将用户的数据请求（高级指令）转换为复杂机器代码（底层指令）
实现对数据库的操作
从数据库的操作中接受查询结果
对查询结果进行处理(格式转换)
将处理结果返回给用户

二、数据库的发展史

第一代数据库：20世纪60年代起，第一代数据库系统问世，是层次模型和网状模型的数据库系统。

第二代数据库：20世纪70年代初，第二代数据库——关系型数据库开始出现；20世纪80年代初，IBM公司的关系数据库系统DB2问世，开始逐步取代层次与网状模型的数据库，成为行业主流。

第三代数据库：20世纪80年代开始，适应不同领域的新型数据库系统不断涌现，面对对象的数据库系统，实用性强、适应面广。20世纪90年代后期，形成了多种数据库系统共同支撑应用的局面。一些新的元素被添加进主流数据库系统中，如：SQL+NOSQL组合使用。

三、关系型数据库与非关系型数据库

3.1 关系型数据库

关系数据库系统是基于关系模型的数据库系统；关系模型的数据结构使用简单易懂的二维数据表

每一行称为一条记录，用来描述一个对象的信息
每一列称为一个字段，用来描述对象的一个属性

关系模型可用简单的"实体-关系-属性"来表示

实体：也称为实例，对应现实世界中可区别于其他对象的“事件”或“事物”
如银行客户、银行账户等。

属性：实体所具有的某一特性，一个实体可以有多个属性；如“银行客户”实体集中的每个实体均具有姓名、住址、电话等属性。

联系：实体集之间的对应关系称为联系，也称为关系；如银行客户和银行账户之间存在“储蓄”的关系。

所有实体及实体之间联系的集合构成一个关系数据库

在二维表中，每一行称为一条记录，用来描述一个对象的信息；每一列成为一个字段，用来描述对象的一个属性。

3.2 非关系数据库

非关系数据库也被称作NoSQL (Not Only SQL)
存储数据不以关系模型为依据,不需要固定的表格式

优点：

数据库可高并发读写
对海量数据高效率存储与访问
数据库具有高扩展性与高可用性
常用的非关系数据库：Redis、mongoDB等

四、关系型数据库和非关系型数据库的区别

4.1 关系型数据库

关系数据库结构是二维数据库表，二维表当中每个字段（列)用来描述对象的一个属性，每个记录（行）用来描述一个对象的信息（完整信息），关系数据库写到哪里也就是存储在硬盘当中读写系统就会受到的Io限制或者瓶颈。

关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织。

优点：

易于维护：都是使用表结构，格式一致;
使用方便：SQL语言通用，可用于复杂查询;
复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。

缺点：

读写性能比较差，尤其是海量数据的高效率读写;
固定的表结构，灵活度稍欠;
高并发读写需求，传统关系型数据库来说，硬盘I/o是一个很大的瓶颈。

4.2 非关系型数据库

非关系数据库也被称作NoSQL（Not Only SQL）

存储数据不以关系模型为依据，不需要固定的表格式

MongoDB 、 Redis（内存数据库/缓存数据库）K-V键值对、与之类似的Memcache，K-V键值对
redis-memecache对比：

相同点：存储高热数据(在内存中高速运行）

不同点：redis可以做持久化保存，可以存储对象

非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。

优点：

格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
高扩展性；
成本低：nosql数据库部署简单，基本都是开源软件。

缺点：

不提供sql支持，学习和使用成本较高；
无事务处理；
数据结构相对复杂，复杂查询方面稍欠。

五、MySQL存储引擎

5.1 InnoDB存储引擎（快速读取，不支持事务）

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：

1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合

2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的

3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上

4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键

5、InnoDB被用在众多需要高性能的大型数据库站点上

InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

5.2 MyISAM存储引擎（更注重写，支持事务）

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：

1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持

2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成

3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16

4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上

5、BLOB和TEXT列可以被索引

6、NULL被允许在索引的列中，这个值占每个键的0~1个字节

7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩

8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快

9、可以把数据文件和索引文件放在不同目录

10、每个字符列可以有不同的字符集

11、有VARCHAR的表可以固定或动态记录长度

12、VARCHAR和CHAR列可以多达64KB

使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

六、数据库的基本操作

6.1 登录数据库

mysql -uroot -p

6.2 创建数据库并进入数据库

create database school;
show databases;

七、查看数据库结构

7.1 查看数据库信息

show databases;
show databases\G

7.2 查看数据库中包含的表及表结构

use school;
create table student (id int NOT NULL,Stuname char(10) NOT NULL,score decimal(5,2),passwd char(48) DEFAULT'', PRIMARY KEY (id));
desc student;
show tables;

注：主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

7.3、常用的数据库类型

int	整型
float	单精度浮点 4字节32位
double	双精度浮点 8字节64位
char	固定长度的字符类型
varchar	可变长度的字符类型
text	文本
image	图片
decimal(5,2)	5个有效长度数字，小数点后面有2位

八、Mysql数据文件

主键是唯一的，但主键可以由多个字段构成

MySQL数据库的数据文件存放在**/usr/local/mysql/data目录下**，每个数据库对应一个子目录，用于存储数据表文件。每个数据表对应为三个文件，扩展名分别为“.frm”、“.MYD”和“.MYI”。

8.1 MYD文件

MYD”文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个“.MYD”文件与之对应，同样存放于所属数据库的文件夹下，和“.frm”文件在一起。

8.2 MYI文件

“.MYI”文件也是专属于 MyISAM 存储引擎的，主要存放 MyISAM 表的索引相关信息。对于 MyISAM 存储来说，可以被 cache 的内容主要就是来源于“.MYI”文件中。每一个MyISAM 表对应一个“.MYI”文件，存放于位置和“.frm”以及“.MYD”一样。

8.3 MyISAM存储引擎

MyISAM 存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件
（frm,myd,myi）。每个表都有且仅有这样三个文件做为 MyISAM 存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个.MYI 文件中。

另外还有“.ibd”和 ibdata 文件，这两种文件都是用来存放 Innodb 数据的，之所以有两种文件来存放 Innodb 的数据（包括索引），是因为Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件，文件存放在和 MyISAM 数据相同的位置。如果选用共享存储表空间来存放数据，则会使用 ibdata 文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata 文件。

九、SQL 语句

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

SQL语言分类：

DDL：数据定义语言，用于创建数据库对象，如库、表、索引等
DML：数据操纵语言，用于对表中的数据进行管理,用来插入、删除和修改数据库中的数据
DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
DCL：数据控制语言，用于设置或者更改数据库用户或角色权限（数据控制语句，用于控制不通数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别,如COMMIT、ROLLBACK、GRANT、REVOKE）

9.1、 DDL数据定义语言

用于创建数据库对象，如库、表、索引等

creat
drop
alter

9.1.1 删除指定的表

use school;
drop tables student;

9.1.2 删除指定数据库

drop database school;

9.2、DML数据操控语言

数据操纵语言，用于对表中的数据进行管理，用来插入、删除和修改数据库中的数据。

insert
update
delete

9.2.1 向数据表中插入新的内容

格式：

INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...);

示例：

INSERT INTO student  (id,name,score,passwd) values(1,'zhangsan',70.5,PASSWORD('123456'));
#PASSWORD('123456'):查询数据记录时，密码字串以加密形式显示:若不使用PASSWORD()，查询时以明文显示。

9.2.2 修改、更新数据表中的数据记录

格式:

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

示例:

UPDATE student SET passwd=PASSWORD('') WHERE name='zhangsan';
UPDATE student SET name='wangxiaoer',passwd='' WHERE id=3;

9.2.3 查询数据表中的内容

SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];例：select * from student;
select name,score from student  where id=1;

9.2.4 在数据表中删除指定的数据记录

DELETE FROM 表名 [WHERE 条件表达式];例：delete from student where id=1;
select * from student;

9.3、DQL数据查询语言

select name from student\G         #以列表方式竖向显示
select * from student limit 2;      #只显示头2行
select * from student limit 2,3;    #显示第2行后的前3行

9.4、DCL数据控制语言

9.4.1 修改表名

ALTER TABLE 旧表名 RENAME 新表名;例：alter table student rename test;
show tables;
select * from test;

9.4.2 扩展表结构（增加字段）

ALTER TABLE 表名 ADD address varchar(50) default '地址不详';#default ‘地址不详’：表示此字段设置默认值 地址不详；可与 NOT NULL 配合使用例：alter table test add address varchar(50) default '地址不详';

9.4.3 修改字段（列）名，添加唯一键

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];例：alter table test change name student varchar(20) unique key;
select * from school;例：
insert into test (id,student,score,passwd) values(1,'lcdb',250,123456);
select * from test;
insert into test (id,user_name,score,passwd) values(6,'zhangbin',250,123456);

每个表中只能由一个主键，但是许多内容都需要唯一性，这就是唯一键的作用

9.4.4 删除字段

ALTER TABLE 表名 DROP 字段名;例：alter table test drop score;

9.4.5 拓展案例

if not exists 表示检测要创建的表是否已存在，如果不存在就继续创建
int(4) zerofill 表示若数值不满4位数，则前面用“0”填充，例0001
auto_increment 表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
unique key 表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键
not null 表示此字段不允许为NULL

例：use school;
create table if not exists school (id int(4) zerofill primary key auto_increment,student_name varchar(20) not null,cardid varchar(18) not null unique key,hobby varchar(50));desc school;