Mysql 数据库的基本操作

一、Mysql体系结构

mysql结构总共分为4个层次，连接层、服务层、引擎层、存储层。

• 连接层
  • 最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限
• 服务层
  • 第二层架构主要完成大多数的MySQL的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
• 引擎层
  • 存储引擎真正负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能。这样我们可以根据自己的需要。来选取合适的存储引擎
• 存储层
  • 主要是将数据存储在文件系统之上，并完成存储引擎的交互。

二、存储引擎

1、存储引擎介绍

• MySQL的存储引擎指MySQL中管理数据的方式。
• 存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
• 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。
• 不同的存储引擎提供不同的存储机制、索引、锁定等功能，使用不同的存储引擎，还可以 获得特定的功能。
• 现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL数据库的核心就是存储引擎。

MySQL引擎：可以理解为 MySQL的“文件系统”，只不过功能更加强大。

MySQL引擎功能： 除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。

2、存储引擎的分类

（1）InnoDB存储引擎：默认引擎，最常用的。

InnoDB是事务型数据库的首选引擎，支持事务（ACID），支持行锁定和外键；InnoDB是默认的MySQL引擎

InnoDB特点：

支持事务处理、支持外键、支持崩溃修复和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如12306售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为它支持事务的提交（commit）和回滚（rollback）。（支持事务：要么成功，要么失败。比如：银行转账。保障数据的完整性）

#外键(foreign key)：链接了两个表，确保数据的关系一致性。外键约束规定一个表中的数据必须匹配另一个表中的数据。

#行锁定(Row locking)：行锁定允许多个事务同时访问表中的不同行，只有访问同一行时才需要排队。这可以在更新数据时降低锁定的范围，提高并发效率。

#事务：所有操作要么全部完成，要么全部不完成。

（2）MyISAM存储引擎

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

MyISAM特点：

插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用程序对数据的完整性、并发性要求比较低，也可以使用。

（3）MEMORY内存型引擎

MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供了快速访问

MEMORY特点：

所有的数据都在内存中，数据的处理速度快，但是安全性不高，因为数据极其容易发生丢失，服务器重启后，数据就丢失了。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。但是它对表的大小有要求，不能建立太大的表。所以，这类数据库引擎只使用在相对较小的数据库表中。

常见的应用场景有：需要高速缓存的表、经常被读但不被写的临时表、数据较小的表。

3、如何选择引擎

具体情况具体分析，主要看业务场景，每个引擎适用不同的业务场景

• 如果要提供提交、回滚、并要求实现并发控制，InnoDB是一个好的选择；
• 如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率；
• 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎。

三、增删改查的sql语句

1、SQL介绍

SQL（Structured Query Language 即结构化查询语言）

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发。

DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER

(create 、 drop 、alter)

DML语句 数据库操纵/操作语言（对记录的操作）： 插入数据INSERT、删除数据DELETE、更新数据UPDATE

（insert 、delete 、update）

DCL语句 数据库控制语言（和权限有关）： 例如控制用户的访问权限GRANT、REVOKE（grant、revoke）

DQL语句 数据库查询语言： 查询数据SELECT （select ）

2、创建并查看数据库

#创建数据库 ：mysql> create database 库名;

#查看数据库：mysql> show databases;

#进入数据库：mysql> use 库名

#查看当前所在的库：mysql> select database();

3、创建表

#语法:

create table 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件]

)[存储引擎 字符集];

#案例：

create table users(

id int primary key, #id字段名，int数据类型，整数 primary key主键

name varchar(30), #name字段名，varchar(30)可变长字符串，限制30字符

age int #age是字段名 数据类型是int整数，

)ENGINE=InnoDB DEFAULT CHARSET=utf8; #指定存储引擎，设置默认的字符集

#参数解释

表名：需要创建的表名

字段：表中包含的各个字段名和类型

类型：字段的类型，如int、varchar等

宽度：某些类型的长度限制，如varchar(50)

约束条件：给字段添加的约束，如primary key、 null 等

存储引擎：指定表使用的存储引擎，如InnoDB、MyISAM等

字符集：指定表使用的字符集，如utf8等

#注意：

#在同一张表中，字段名是不能相同

#宽度和约束条件是可选的

#字段名和类型是必须的

4、sql语言应用案例

（1）创建表：

create table t1(id int,name varchar(20),age int);

# 字段 类型 字段 类型(长度)，字段 类型

mysql> create table t2(id int,name varchar(50),sex enum('m','f'),age int); #m:male f:female 女士

（2）查看有哪些表

mysql> show tables;

（3）查看表结构：

mysql> desc t1;

（4）查看表里面的所有记录:

#语法: select 内容 from 表名；

mysql> select * from t1;

#*代表所有内容

（5）查看表里面的指定字段的数据

#语法：select 字段,字段 from 表名；

mysql> select name,sex from t1;

（6）查看表的状态

mysql> show table status like '表名'\G #每条SQL语句会以分号结尾，想看的清楚一些以\G结尾，一条记录一条记录显示。（把表90度向左反转，第一列显示字段，第二列显示记录）使用的\G就不用添加分号了

（7）修改表名称

#方式一、语法：rename table 旧表名 to 新表名;

mysql> rename table t1 to t2;

#方式二、语法：alter table 旧表名 rename 新表名;

mysql> alter table t2 rename t3;

（8）使用edit(\e)编辑

mysql> \e #可以写新的语句，调用的vim编辑器，在里面结尾的时候不加分号，保存退出之后在加“;”

create table users2(

id int primary key,

name varchar(30),

age int(3)

)ENGINE=InnoDB DEFAULT CHARSET=utf8；

（9）删除表：慎用！

mysql> drop table 表名;

（10）删除库：慎用！

mysql> drop database 库名;

四、数据库类型

在MySQL数据库管理系统中，可以通过存储引擎来决定表的类型。

MySQL数据库管理系统也提供了数据类型决定表存储数据的类型。

常见的数据类型：

1、整型

作用：用于存储用户的年龄、游戏的Level、经验值等。

分类：tinyint smallint mediumint int bigint

#常用的是int

2、字符串类型

#作用：用于存储用户的姓名、爱好、电话，邮箱地址，发布的文章等

#字符类型 char varchar #存字符串

- char表示【定长字符串】，长度是固定的；如果插入数据的长度【小于】char的固定长度时，则用空格填充；因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间。

- varchar表示【可变长字符串】，长度是可变的；插入的数据是多长，就按照多长来存储；varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间。

- 结合性能角度（char更快），节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。

char(10) 根据10来限制，一定会占10个，小于10个用空格补齐。列的长度是固定在为创建表时，声明的长度： 0 ~ 255

varchar(10) 根据实际字符串长度占空间，最多10个。列中的值为可变长字符串长度为： 0 ~ 65535。

#总结：

① 经常变化的字段用varchar

② 知道固定长度的用char

③ 超过255字符的只能用varchar或者text

④ 能用varchar的地方不用text

text：文本格式

3、枚举类型 enum （enumeration）

#ENUM 是一个字符串对象，值为表创建时 列规定中 枚举的一列值。其语法格式如下：

# ENUM( '值1', '值1', …, '值n' )

例：create table t101(name enum('tom','jim'));

#只能从tom,jim两个里面2选其1

4、日期类型

#时间和日期类型测试：year、date、time、datetime、timestamp

#作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等

注意事项:

#插入年份时，尽量使用4位值

#插入两位年份时，，以20开头，比如65, 结果2065

>=70，以19开头，比如82，结果1982