MySql数据库基本操作

作者: LittlePy | 来源:发表于2017-09-09 12:05 被阅读26次

计算机诞生后，数据开始在计算机中存储并计算，并设计出了数据库系统，数据库系统解决的问题：持久化存储，优化读写，保证数据的有效性。

当前使用的数据库，主要分为两类

1、文档型，如sqlite，就是一个文件，通过对文件的复制完成数据库的复制；

2、服务型，如mysql、oracle、postgre，数据存储在一个物理文件中，但是需要使用终端以tcp/ip协议连接，进行数据库的读写操作。

E-R模型

当前物理的数据库都是按照E-R模型进行设计的，E表示entity，实体，R表示relationship，关系

ER模型是构建现实世界和程序数据世界的桥梁，通过ER图可以理清楚程序的业务关系，在工程中只有先把业务关系搞清楚了，才好设计数据库，才好进行相应的编码工作。其实在一个大的工程中，设计数据库和理清业务关系甚至要比实际的编码时间都要长。

关系描述两个实体之间的对应规则，包括

1.一对一

合法的情况下：一个男人娶一个女人，一个女人嫁一个男人

2.一对多

目前教室里，一个老师教多个学生，一个学生被一个老师教

一个教室里有很多学生，一个学生只能一个教室

3.多对多

在大学中，一个学生选很多课程，一个课程被学生选

在大学里，一个学生可以有很多老师，一个老师可以教很多学生

关系转换为数据库表中的一个列在关系型数据库中一行就是一个对象

三范式

经过研究和对使用中问题的总结，对于设计数据库提出了一些规范，这些规范被称为范式

1.第一范式（1NF)：列不可拆分

2.第二范式（2NF)：唯一标识

3.第三范式（3NF)：引用主键

简而言之：

说明：后一个范式，都是在前一个范式的基础上建立的。

数据类型

MySQL支持所有标准的SQL数据类型，主要分3类：数值类型、字符串类型、时间日期类型。

1、数值类型

1.1、整数类型

下面一张表就能解释清楚：

1.2、定点数

DECIMAL和NUMERIC类型在MySQL中视为相同的类型。它们用于保存必须为确切精度的值。

我们看到上面这个例子中有两个参数，即DECIMAL(M,D)，其中M表示十进制数字总的个数，D表示小数点后面数字的位数，上例中的取值范围为-999.99~999.99。

如果存储时，整数部分超出了范围（如上面的例子中，添加数值为1000.01），MySql就会报错，不允许存这样的值。

如果存储时，小数点部分若超出范围，就分以下情况：

若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存。如999.994实际被保存为999.99。

若四舍五入后，整数部分超出范围，则MySql报错，并拒绝处理。如999.995和-999.995都会报错。

M的默认取值为10，D默认取值为0。如果创建表时，某字段定义为decimal类型不带任何参数，等同于decimal(10,0)。带一个参数时，D取默认值。

M的取值范围为1~65，取0时会被设为默认值，超出范围会报错。

D的取值范围为0~30，而且必须<=M，超出范围会报错。

所以，很显然，当M=65，D=0时，可以取得最大和最小值。

1.3、浮点数

浮点数是用来表示实数的一种方法，相对于定点数来说，在长度一定的情况下，具有表示数据范围大的特点。但同时也存在误差问题，如果希望保证值比较准确，推荐使用定点数数据类型。

MySql中的浮点类型有float，double和real。他们定义方式为：FLOAT(M,D) 、 REAL(M,D) 、 DOUBLE PRECISION(M,D)。

REAL就是DOUBLE ，如果SQL服务器模式包括REAL_AS_FLOAT选项，REAL是FLOAT的同义词而不是DOUBLE的同义词。

“(M,D)”表示该值一共显示M位整数，其中D位位于小数点后面。例如，定义为FLOAT(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入，因此如果在FLOAT(7,4)列内插入999.00009，近似结果是999.0001。

FLOAT和DOUBLE中的M和D的取值默认都为0，即除了最大最小值，不限制位数。

M取值范围为0~255。FLOAT只保证6位有效数字的准确性，所以FLOAT(M,D)中，M<=6时，数字通常是准确的。如果M和D都有明确定义，其超出范围后的处理同decimal。

D取值范围为0~30，同时必须<=M。double只保证16位有效数字的准确性，所以DOUBLE(M,D)中，M<=16时，数字通常是准确的。如果M和D都有明确定义，其超出范围后的处理同decimal。

FLOAT和DOUBLE中，若M的定义分别超出7和17，则多出的有效数字部分，取值是不定的，通常数值上会发生错误。因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。

内存中，FLOAT占4-byte（1位符号位 8位表示指数 23位表示尾数），DOUBLE占8-byte（1位符号位 11位表示指数 52位表示尾数）。

1.4、bit(1)

BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1~64(即64位二进制数)，默认为1，BIT其实就是存入二进制的值，类似010110。

如果存入一个BIT类型的值，位数少于M值，则左补0.

如果存入一个BIT类型的值，位数多于M值，MySQL的操作取决于此时有效的SQL模式：

如果模式未设置，MySQL将值裁剪到范围的相应端点，并保存裁减好的值。

如果模式设置为traditional(“严格模式”)，超出范围的值将被拒绝并提示错误，并且根据SQL标准插入会失败。

看下面官方给出的例子：

2、字符串类型

2.1、CHAR和VARCHAR类型

CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如，CHAR(30)可以占用30个字符。默认长度都为255。

CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉，所以，我们在存储时字符串右边不能有空格，即使有，查询出来后也会被删除。在存储或检索过程中不进行大小写转换。

所以当char类型的字段为唯一值时，添加的值是否已经存在以不包含末尾空格（可能有多个空格）的值确定，比较时会在末尾补满空格后与现已存在的值比较。

VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值（实际可指定的最大长度与编码和其他字段有关，比如，本人MySql使用utf-8编码格式，大小为标准格式大小的2倍，仅有一个varchar字段时实测最大值仅21844，如果添加一个char(3)，则最大取值减少3。整体最大长度是65,532字节）。

同CHAR对比，VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。

VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。

如果分配给CHAR或VARCHAR列的值超过列的最大长度，则对值进行裁剪以使其适合。如果被裁掉的字符是空格，则会产生一条警告。如果裁剪非空格字符，则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。

下面显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果：

表中最后一行的值只适用在不使用严格模式时；如果MySQL运行使用严格模式，超过列长度的值不保存，并且会出现错误。因为空格的原因，相同的值存入到长度都足够的varvhar和char中，取出可能会不同，比如"a"和"a "。

2.2、BINARY和VARBINARY类型（2）

BINARY和VARBINARY类型类似于CHAR和VARCHAR类型，但是不同的是，它们存储的不是字符字符串，而是二进制串。所以它们没有字符集，并且排序和比较基于列值字节的数值值。

当保存BINARY值时，在它们右边填充0x00(零字节)值以达到指定长度。取值时不删除尾部的字节。比较时所有字节很重要（因为空格和0x00是不同的，0x00<空格），包括ORDER BY和DISTINCT操作。比如插入'a '会变成'a \0'。

对于VARBINARY，插入时不填充字符，选择时不裁剪字节。比较时所有字节很重要。

当类型为BINARY的字段为主键时，应考虑上面介绍的存储方式。

2.3、BLOB和TEXT类型

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

BLOB列被视为二进制字符串。TEXT列被视为字符字符串，类似CHAR和BINARY。

在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。

在大多数方面，可以将BLOB列视为能够足够大的VARBINARY列。同样，可以将TEXT列视为VARCHAR列。

BLOB和TEXT在以下几个方面不同于VARBINARY和VARCHAR：

1、当保存或检索BLOB和TEXT列的值时不删除尾部空格。(这与VARBINARY和VARCHAR列相同）。

2、比较时将用空格对TEXT进行扩充以适合比较的对象，正如CHAR和VARCHAR。

3、对于BLOB和TEXT列的索引，必须指定索引前缀的长度。对于CHAR和VARCHAR，前缀长度是可选的。

4、BLOB和TEXT列不能有默认值。

MySQL Connector/ODBC将BLOB值定义为LONGVARBINARY，将TEXT值定义为LONGVARCHAR。

BLOB或TEXT对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端程序。

3、时间日期类型

3.1、DATE, DATETIME, 和TIMESTAMP类型

这三者其实是关联的，都用来表示日期或时间。

当你需要同时包含日期和时间信息的值时则使用DATETIME类型。MySQL以'YYYY-MM-DD HH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

当你只需要日期值而不需要时间部分时应使用DATE类型。MySQL用'YYYY-MM-DD'格式检索和显示DATE值。支持的范围是'1000-01-01'到 '9999-12-31'。

TIMESTAMP类型同样包含日期和时间，范围从'1970-01-01 00:00:01' UTC 到'2038-01-19 03:14:07' UTC。

3.2、TIME类型

MySQL以'HH:MM:SS'格式检索和显示TIME值(或对于大的小时值采用'HHH:MM:SS'格式)。

TIME值的范围可以从'-838:59:59'到'838:59:59'。小时部分会因此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。

对于指定为包括时间部分间割符的字符串的TIME值，如果时、分或者秒值小于10，则不需要指定两位数。'8:3:2'与'08:03:02'相同。

为TIME列分配简写值时应注意。没有冒号，MySQL解释值时假定最右边的两位表示秒。(MySQL解释TIME值为过去的时间而不是当天的时间）。例如，你可能认为'1112'和1112表示'11:12:00'(11点过12分)，但MySQL将它们解释为'00:11:12'(11分，12 秒)。同样，'12'和12 被解释为 '00:00:12'。相反，TIME值中使用冒号则肯定被看作当天的时间。也就是说，'11:12'表示'11:12:00'，而不是'00:11:12'。

3.3、YEAR类型

YEAR类型是一个单字节类型用于表示年。MySQL以YYYY格式检索和显示YEAR值。范围是1901到2155。

可以指定各种格式的YEAR值：

1、四位字符串，范围为'1901'到'2155'。

2、四位数字，范围为1901到2155。

3、两位字符串，范围为'00'到'99'。'00'到'69'和'70'到'99'范围的值被转换为2000到2069和1970到1999范围的YEAR值。

4、两位整数，范围为1到99。1到69和70到99范围的值被转换为2001到2069和1970到1999范围的YEAR值。请注意两位整数范围与两位字符串范围稍有不同，因为你不能直接将零指定为数字并将它解释为2000。你必须将它指定为一个字符串'0'或'00'或它被解释为0000。

非法YEAR值被转换为0000。

约束

1.主键primary key

2.非空not null

3.惟一unique

4.默认default

5.外键foreign key

基本操作

·创建数据库：create database 数据库名 charset=utf8;

·删除数据库：drop database数据库名;

·切换数据库：use数据库名;

·查看当前选择的数据库：select database();

·查看当前数据库中所有表：show tables;

·创建表：create table表名(列及类型);

主键本身是用来唯一标识这一行，没有业务逻辑意义，所以是什么值不重要，只要唯一就行，所以如果是主键不需要修改，auto_increment表示自动增长,只能是数字类型。

如：

create table students(

id int auto_increment primary key,

sname varchar(10) not null

);

·修改表：alter table表名add|modify|drop列名类型;

如：alter table students add birthday datetime;

·删除表：drop table表名;

·查看表结构：desc表名;

·更改表名称：rename table原表名to新表名;

·查看表的创建语句：show create table '表名';

·查询：select * from表名

·增加

全行插入：insert into表名values(...)

缺省插入：insert into表名(列1,...) values(值1,...)

同时插入多条数据：insert into表名values(...),(...)...;

或insert into表名(列1,...) values(值1,...),(值1,...)...;

·主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准

·修改：update 表名 set 列1=值1,... where条件

·删除：delete from 表名 where 条件

·逻辑删除，本质就是修改操作update

alter table students add isdelete bit default 0;

如果需要删除则

update students isdelete=1 where ...;

备份与恢复

数据备份

·进入超级管理员:sudo -s

·运行mysqldump命令:mysqldump–uroot –p数据库名> ~/备份文件.sql;

按提示输入mysql的密码

数据恢复

·连接mysql，创建数据库

·退出连接，执行命令:mysql -uroot -p数据库名< ~/备份文件.sql

根据提示输入mysql密码

查询：

聚合：

分组：分组前面查询的内容只能是聚合函数和分组

排序：

分页：

模糊查询：like(%表示任意多个任意字符, _表示一个任意字符)

查询范围：·in表示在一个非连续的范围内，between ... and ...表示在一个连续的范围内。

非空判断：is null和is not null

去重复：distinct

多表直接查询其实做的是一个笛卡尔积，可以看到结果并不是我们想要的：

多表查询：

1、内连接：join或inner join

以上两种写法等效，可以看到在DEPT表中，有deptno为40的记录，但在EMP表中，并没有员工属于40这个deptno,在进行内敛查询时，因为neptno为40无法匹配，所以就自动将其删除了，所以内连接不会保留无法匹配的记录。

内连接同样可以进行一些筛选，有两种方式的筛选，其效果相同：

2、外联接：left/right outer join或 left/right join

right表示join右边的是主表，左边的是次表，进行外联接时，主表无法匹配的记录不会忽略，而会保留，次表中记录的相应内容会用NULL填充。

自关联：

举一个简单的例子来说明自关联，假如现在有两张表结构如下：

观察两张表发现，citys表比provinces表多一个列proid，其它列的类型都是一样的，存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大。因此我们可以把这两张表合并为一张表。

因为省没有所属的省份，所以可以填写为null

城市所属的省份pid，填写省所对应的编号id

这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id，在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息。

子查询案例：

内置函数：

1、字符串函数：

查看字符的ascii码

查看ascii码对应的字符

字符串拼接

utf8编码一个汉字占三个字节

从左截取字符串

从右截取字符串

截取指定为止的字符串

删除左侧空格

删除右侧空格

返回由是个空格组成的字符串

字符串替换

大小写转换

2、数学函数

3、时间日期函数

视图

为了引入视图先看下面一个简单的查询操作的例子：

这是一个简单的查询命令，相对而言还是稍微有点长的，在工作中，我们遇到的数据库操作可能比这样的SQL语句冗长的多，每次重新再写相同的命令会显得十分繁琐，那么能不能像python中的函数那样把SQL语句封装起来，每次只需要调用就可以进行多次重复的操作而实现代码复用呢？视图就是实现一种类似的功能。视图本质就是对查询的一个封装，虚拟的表，一旦封装的内容改变了，视图的内容也随着用，视图就是用来进行进行查询操作的。通过创建视图，我们可以看到下面的代码实现了和上述代码相同的功能，且提高了代码的复用性。