数据库基础

MySQL基础操作

使用方法：
方法一：通过命令行来操作（有助于命令的掌握）
方法二：通过图形界面工具，如 Navicat等（在熟练掌握后再使用）
方法三：通过编程语言（python,php,java,go...）执行mysql命令

SQL（Structure query language）结构化查询语言

SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）

SQL语句中的快捷键

\G格式化输出（文本式、竖立显示）
\s查看服务器端信息
\c结束命令输入操作
\q退出当前sql命令行模式
\h查看帮助

操作数据库的步骤

连接，打开库，操作，关闭退出

1.通过命令行连接MySQL

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-blpf5Bf8-1603345677130)(E:\KaiKeBa\kaikeba\0825-王焕章-数据库基础_1004\imgs\import.png)]

数据库语法的特点

1)SQL语句可以换行，要以分号结尾

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kMO7DAJk-1603345677132)(E:\KaiKeBa\kaikeba\0825-王焕章-数据库基础_1004\imgs\import1.png)]

2)命令不区分大小写，关键字和函数建议用大写

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HpoPiOe4-1603345677133)(E:\KaiKeBa\kaikeba\0825-王焕章-数据库基础_1004\imgs\import2.png)]

3)关于提示符

如果提示符为’>‘那么需要输入一个’回车’

同理如果提示符为’>那么同样需要输入一个’

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VavOFcMV-1603345677135)(E:\KaiKeBa\kaikeba\0825-王焕章-数据库基础_1004\imgs\import3.png)]

4）命令打错并换行后不能修改上一行的命令，可以用 \c取消

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-u8ZOTEAu-1603345677137)(E:\KaiKeBa\kaikeba\0825-王焕章-数据库基础_1004\imgs\import4.png)]

2.数据库操作

查看数据库 show databases;

创建数据库 create database 库名 default charset=utf8mb4;

删除数据库 drop database 库名;

删库有风险，操作需谨慎

打开数据库 use 库名

3.数据表操作

数据库管理系统中，可以有很多库，每个数据库中可以包括多张数据表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-05xoG4Sk-1603345677138)(E:\KaiKeBa\kaikeba\0825-王焕章-数据库基础_1004\imgs\import5.png)]

查看表：

show tables;

创建表：

creat table '表名'(字段1 类型，字段2 类型) engine=innodb default charset=utf8mb4;

创建表：如果表不存在则创建，如果存在就不执行这条命令

create table if not exists 表名(字段1 类型，字段2 类型);

create table if not exists users(
    id int not null primary key auto_increment,
    name varchar(4) not null,
    age tinyint,
    sex enum('男','女')
)engine=innodb default charset=utf8mb4;

删除表：drop table 表名；

表结构：desc 表名；

查看建表语句：show create table 表名;

4.数据操作 增删改查

插入

insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);

insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3),(b值1,b值2,b值3);

查询

select * from 表名;

select 字段1,字段2,字段3 from 表名;

select * from 表名 where 字段=某个值;

修改

update 表名 set 字段=某个值 where 条件;

update 表名 set 字段1=值1,字段2=值2 where 条件;

update 表名 set 字段=字段+值 where 条件；

删除

delete from 表名 where 字段=某个值;

四.退出MySQL

exit; 或者 quit;

一、MySQL的数据类型

数据类型是定义列中可以存储什么类型的数据以及该数据实际怎样存储的基本规则
数据类型限制存储在数据列，列中的数据。例如，数值数据类型列只能接受数值类型的数据
在设计表时，应该特别重视所用的数据类型。使用错误的数据类型可能会严重的影响应用程序的功能和性能。
更改包含数据的列不是一件小事(而且这样做可能导致数据丢失)。

基本常用的数据类型

数据类型：整型、浮点型、字符串、日期等

1、字符串数据类型

最常用的数据类型是串数据类型。它们存储串，如名字、地址、电话号码、邮政编码等。

不管使用何种形式的串数据类型，串值都必须包括在引号内。

有两种基本的串类型，分别为定长串和变长串。

• 定长串：cahr

  1.接受长度固定的字符串，其长度是在创建表时指定的。

  定长列不允许存储多于指定长度字符的数据。

  2.指定长度后，就会分配固定的存储空间用于存放数据。

  char(7) 不管实际插入多少字符，它都会占用7个字符的位置
  

• 变长串：varchar

  存褚可变长度的字符串

  carchar(7) 如果实际插入4个字符，那么它只占4个字符位置，当然插入的数据长度不能超过7个字符。

注意

既然变长串数据类型这样灵活为什么还要使用定长数据类型？
回答：因为性能，MySQL处理定长串远比处理变长串快的多

• Text 变长文本类型存储

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jWmlndhy-1603345677139)(E:\KaiKeBa\kaikeba\0825-王焕章-数据库基础_1004\imgs\数据类型2.png)]

2.数值类型

数值数据类型存储数值，MySQL支持多种数值数据类型，每种存储的数值具有不同的取值范围。支持的取值范围越大，所需存储空间越多。

与字符串不一样，数值不应该括在引号内。

decimal(5,2)表示数值总共5位，小数占两位
float. 
tinyint 1字节(8位) 0-255,-128,127
int	4字节,	-21亿,  21亿。0-42亿

MySQL 中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8,2)

有符号或无符号

所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号

• 有符号数值列可以存储正或负的数值
• 无符号数值列只能存储正数。
• 默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字

注意

如果将邮政编码类似于01234存储为数值类型，则保存的将是数值1234，此时需要使用字符串类型手机号应该用什么进行存储呢？

3、日期和时间类型

MySQL使用专门的数据类型来存储日期和时间值

datetime 8字节 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

4、二进制数据类型

二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等

通常情况下，不会使用二进制数据类型进行，多没有数据的存储

[

二、表的字段约束

• unsigned 无符号(给数值类型使用，表示为正数，不写可以表示负数都可以)

• 字段类型后面加括号限制宽度

  • char(5),varchar(7) 在字符类型后面加限制 表示 字符串的长度

  • int(4) 没有意义，默认无符号的int为int(11),有符号的int(10)

    在数字类型后面加（5），对取值的范围值没有影响的。

  • int(4) unsigned zerofill 只有当给int类型设置有前导0时，设置int的宽度才有意义。

• not null 不能为空，在操作数据库时如果输入该字段的数据为NULL，就会报错

• default 这是默认值

• primary key 主键不能为空，且唯一，一般和自动递增一起配合使用。

• auto_increment 定义列为自增属性，一般用于主键，数值会自动加1

• unique 唯一索引（数据不能重复：用户名）可以增加查询速度，但是会降低插入和更新速度

三、MySQL的运算符

• 算数运算符：+、-、*、/、%

• 比较运算符：=、>、<、>=、<=、!=

• 数据库特有的比较：in、not in、is、null、is not null、like、between、and

• 逻辑运算符：and、or、not

• like：支持特殊符号%和_；

  其中%表示任意数量的任意字符，_表示任意一位字符
  

四、主键

1、表中的每一行都应该有可以唯一表示自己的一列，用于记录两条记录不能重复，任意两行都不具有相同的主键值。

2、应该总是定义主键，所任并不是总是都需要主键，但大多数数据库设计人员都应保证他们创建的每个表具有一个主键，以便于以后的数据操纵和管理。

要求

• 记录一旦插入到表中，主键最好不要在修改

• 不允许NULL

• 不在主键列中使用可能会更改的值。

  （例如，如果使用一个名字作为主键以标识某个供应商，当该供应商合并和更改名字时，必须要更改这个主键。）
  

• 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键

• 可以使用多个列作为联合主键，但联合主键并不常用，使用多列作为主键是，所有列值的组合必须是唯一的

MySQL数据库与数据表操作

• 数据库的操作
  • 数据库创建
  • 数据库删除
• 数据表的操作
  • 数据表的创建
  • 数据表的修改（表结构）
  • 数据表的删除

数据库的操作

1.数据库的创建

# 链接mysql数据库后，进入mysql可以操作数据

# 1. 创建库
create database if not exists chuange default charset=utf8mb4;
-- 1. 数据库 chuange 如果不存在则创建数据库，存在则不创建
-- 2. 创建 chuange 数据库，并设置字符集为utf8mb4
-- 3. 无特殊情况都要求字符集为 utf8 或者 utf8mb4 的字符编码

2.查看所有库

# 1. 查看所有库
show databases;

3.打开库/进入库/选择库

# use 库名
use chuange

4.删除库

删库有风险，动手需谨慎

# 删除库，那么库中的所有数据都在磁盘中删除
drop database 库名

数据表的操作

1.创建表

语法格式：

create table 表名(字段名，类型，【字段约束】…);

实例

# 一下创建一个 users 为表名的表
create table users(
    -- 创建ID字段，为正整数，不允许为空，主键，自动递增
    id int unsigned not null primary key auto_increment,
    -- 创建 存储 名字的字段，为字符穿类型，最大长度为5个字符，不允许为空
    username varchar(5) not null,
    -- 创建存储 密码 的字段，固定长度 32 字符，不允许为空
    password char(32) not null,
    -- 创建 年龄 字段，不允许为空，默认值为 20
    age tinyint not null default 20
)engine=innodb default charset=utf8mb4;

# 查看表结构
desc users;

# 查看建表语句
show create table users;

创建表的基本原则：

• 表名和字段名尽可能的符合命名规范，并且最好能够见名知意
• 表中数据必须有唯一表示，即主键定义，无特殊情况，主键都为数字并且自增即可
• 表中字段所对应的类型设置合理，并限制合理长度
• 表引擎推荐使用 innodb ,并无特殊情况都要求为 *utf8mb4 的字符编码

2.修改表结构

语法格式：alter table 表名 action (更改的选项)

添加字段

# 语法：alter table 表名 add 添加的字段信息
-- 在 users 表中 追加 一个 num 字段
alter table users add num int not null;

-- 在指定字段后面追加字段 在 users 表中 age 字段后面 添加一个 email 字段
alter table users add email varchar(50) after age;

-- 在指定字段后面追加字段，在 users 表中 age字段后面 添加一个 phone
alter table users add phone char(11) not null after age;

-- 在表的最前面添加一个字段
alter table users add aa int first;

删除字段

# 删除字段 alter table 表名 drop 被删除的字段名
alter table users drop aa;

修改字段

语法格式：alter table 表名 change|modify 被修改的字段信息
change：可以修改字段名。
modify：不能修改字段名。

# 修改表中的 num 字段 类型， 使用  modify 不修改字段名
alter table users modify num tinyint not null default 12;

# 修改表中的 num 字段 为 int并且字段名为 nn
alter table users change num mm int;

# 注意：一般情况下，无特殊要求，不要轻易修改表结构

3.修改表名

# 语法：alter table 原表名 rename as 新表名

4.更改表中的自增的值

# 在常规情况下，auto_increment 默认从1开始继续递增
alter table users auto_increment = 1000;

5.修改表引擎

# 推荐在定义表时，表引擎为 innodb。
# 通过查看建表语句获取当前的表引擎
mysql> show create table users\G;
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
# 直接查看当前表状态信息
mysql> show table status from tlxy where name = 'users'\G;
*************************** 1. row ***************************
Name: users
Engine: InnoDB
# 修改表引擎语句
alter table users engine = 'myisam';

6.删除表

drop table 表名

Mysql数据库表引擎与字符集

1.服务器处理客户端请求

其实不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：**客户端进程向服务器进程发送 一段文本（MySQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。**那服务器进程对客户 端进程发送的请求做了什么处理，才能产生最后的处理结果呢？客户端可以向服务器发送增删改查各类请求，我们 这里以比较复杂的查询请求为例来画个图展示一下大致的过程：

虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销，比如每次都要去查询缓 存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。从MySQL 5.7.20开始，不 推荐使用查询缓存，并在MySQL 8.0中删除。

2.存储引擎

MySQL服务器把数据的存储和提取操作都封装到了一个叫 存储引擎的模块里。我们知道 表 是由一行一行的记录 组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存 储器上，这都是 存储引擎 负责的事情。为了实现不同的功能， MySQL 提供了各式各样的 存储引擎 ，不同 存储引 擎 管理的表具体的存储结构可能不同，采用的存取算法也可能不同。

存储引擎以前叫做 表处理器 ，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操 作。

为了管理方便，人们把 连接管理 、 查询缓存 、 语法解析 、 查询优化 这些并不涉及真实数据存储的功能划分为 MySQL server 的功能，把真实存取数据的功能划分为 存储引擎 的功能。各种不同的存储引擎向上边的 MySQL server 层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像"读取索引第一条内容"、“读取 索引下一条内容”、"插入记录"等等。

所以在 MySQL server 完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返 回给客户端就好了。

MySQL 支持非常多种存储引擎：

存储引擎	描述
ARCHIVE	用于数据存档（行被插入后不能再修改）
BLACKHOLE	丢弃写操作，读操作会返回空内容
CSV	在存储数据时，以逗号分隔各个数据项
FEDERATED	用来访问远程表
InnoDB	具备外键支持功能的事务存储引擎
MEMORY	置于内存的表
MERGE	用来管理多个MyISAM表构成的表集合
MyISAM	主要的非事务处理存储引擎
NDB	MySQL集群专用存储引擎

3,MyISAM和InnoDB表引擎的区别

1) 事务支持

MyISAM不支持事务，而InnoDB支持。

事物：访问并更新数据库中数据的执行单元。事物操作中，要么都执行要么都不执行

2) 存储结构

MyISAM：每个MyISAM在磁盘上存储成三个文件。

• .frm文件存储表结构。
• .MYD文件存储数据。
• .MYI文件存储索引。

InnoDB：主要分为两种文件进行存储

• .frm 存储表结构
• .ibd 存储数据和索引 （也可能是多个.ibd文件，或者是独立的表空间文件）

3) 表锁差异

MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如 果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。**InnoDB：支持事务和行级锁，是 innodb的最大特色。**行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有 效的，非主键的WHERE都会锁全表的。

4) 表主键

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。 InnoDB：如果没有设定主键或者非空唯 一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。 InnoDB的主键范围更大，最大是MyISAM的2倍。

5) 表的具体行数

MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。 InnoDB：没有保存表的总行数 (只能遍历)，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后， myisam和innodb处理的方式都一样。

6) CURD操作

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。 InnoDB：如果你的数据执行大量的INSERT或 UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table 时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用 truncate table这个命令。

7) 外键

MyISAM：不支持 InnoDB：支持

8) 查询效率

MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。 推荐考虑使用InnoDB来替代MyISAM引擎，原因是InnoDB自身很多良好的特点，比如事务支持、存储 过程、视 图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。 另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优 势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟酌。

9）MyISAM和InnoDB两者的应用场景：

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那 么MyISAM是更好的选择。 InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要 执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。现在默认使用 InnoDB。