数据库基础知识

MySQL基础操作

SQL(Structure query language) 结构化查询语言

SQL语言分为四个部分：DDL(定义)，DML(操作)，DQL(查询)，DCL(控制)

SQL语言中的快捷键

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

操作数据库的步骤

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

1.通过命令行连接MySQL

mysql -h localhost -u root -p -P 3306

-h 服务器地址
-u 登录的账户名
-p 密码
-P 端口号

数据库语法的特点

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

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

3.关于提示符

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

​ 如果提示符为’’>那么与要输入一个’’

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

2.数据库操作

查看数据库

​ show databases;

创建数据库

​ create databases 库名 default charset=utf8mb4;

删除数据库

​ drop database 库名;

打垮数据库

​ use 库名;

3.数据表操作

查看表

​ show tables;

创建表

​ create table 表名（字段名1 类型，字段名2 类型）engine=innodb default charset=utf8mb4;

创建表（如果表不存在就创建，存在就不执行命令）

​ create table if not exists 表名（字段1 类型，字段2 类型）;

删除表

​ drop table 表名;

表结构

​ desc 表名;

查看建表语句

​ show create table users;

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 字段1,字段2,字段3 from 表名;

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

修改

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

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

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

删除

delete from 表名 where 字段=某值;

5.退出

exit; quit;

MySQL中的数据类型

基本常用的数据类型：整形、浮点型、字符串、日期等

1.字符串

最常用的数据类型，如名字、地址、电话

两种基本的类型：定长串和变长串

定长串：char

​ 1.接收长度固定规定字符串，其长度在创建表时指定

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

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

​ 如char(7)固定占用7个字符位置

​ 最大长度为255

变长串varchar

​ 储存可变长度的字符串

​ varchar(7)如果实际插入4个字符则只占4个字符位置，总长度不能超过7

变长文本存储Text

MySQL处理定长的速度比变长快的多

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-L27fbwf2-1615723669401)(C:\Users\Silver\AppData\Roaming\Typora\typora-user-images\image-20210226010211472.png)]

2.数值类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zCfm9Yjl-1615723669403)(C:\Users\Silver\AppData\Roaming\Typora\typora-user-images\image-20210226010446525.png)]

3.日期和时间类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-grtKQIY7-1615723669404)(C:\Users\Silver\AppData\Roaming\Typora\typora-user-images\image-20210226010830949.png)]

4.二进制数据类型

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

MySQL中的字段约束

unsigned 无符号

字段后面加括号限制宽度

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

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

​ int(4) unsigned zerofill 只有当int类型设置有前导零时，设置int的宽度才有意义

not 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.创建表

​ 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或者utf8mb4mb4的字符编码

2.修改表结构

​ alter table 表名 action(更改的选项)

添加字段

#语法：alter table 表名 action(更改的选项)
#在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 nn int;

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

修改表名

alter table 原表名 rename as 新表名;

修改表中自增的值

#auto_increment默认为1
alter table users auto_increment = 1000; 

修改表引擎

#推荐在定义表时，表引擎为innodb
#推过查看建表语句获取当前的表引擎
show create table users\G;
#直接查看当前表状态信息
show table status from ... where name = ' '\G;

#修改表引擎
alter table users engine='...';

删除表

drop table 表名;

MySQL数据库的表引擎

MylSAM, InnoDB, Memory

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。

MySQL的字符集UTF8

将一个字符映射成一个二进制数据的过程叫做编码，将一个二进制数据映射到一个字符的过程叫做解码

人们抽象出一个字符集的概念来描述某个字符范围的编码规则

ASCII 字符集

共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码，我们看一些字符的编码方式：

'L' -> 01001100（十六进制：0x4C，十进制：76） 
'M' -> 01001101（十六进制：0x4D，十进制：77）

ISO 8859-1 字符集

共收录256个字符，是在 ASCII 字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名 latin1 。

GB2312 字符集

收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容 ASCII 字符集，所以在编码方式上显得有些奇怪：

​ 如果该字符在 ASCII 字符集中，则采用1字节编码。

​ 否则采用2字节编码。

这种表示一个字符需要的字节数可能不同的编码方式称为 变长编码方式 。比方说字符串 ‘爱u’ ，其中 ‘爱’ 需要用2个字节进行编码，编码后的十六进制表示为 0xCED2 ， ‘u’ 需要用1个字节进行编码，编码后 的十六进制表示为 0x75 ，所以拼合起来就是 0xCED275 。

小贴士： 我们怎么区分某个字节代表一个单独的字符还是代表某个字符的一部分呢？别忘了 ASCII 字符集只收录128个字符，使用0～127就可以表示全部字符，所以如果某个字节是在0～127之内的，就意味着一个字节代表一个单独的字符，否则就是两个字节代表一个单独的字符。 

GBK 字符集

GBK 字符集只是在收录字符范围上对 GB2312 字符集作了扩充，编码方式上兼容 GB2312 。

unicode 字符集

收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容 ASCII 字符集，采用变长编码方式，编码一个字符需要使用1～4个字节，比方说这样：

'L' -> 01001100（十六进制：0x4C） 

'啊' -> 111001011001010110001010（十六进制：0xE5958A）

小贴士： 其实准确的说，utf8只是Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、utf16、utf32这几种编码方案，utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个 字符，utf32使用4个字节编码一个字符。更详细的Unicode和其编码方案的知识不是本书的重点，大家 上网查查哈～ MySQL中并不区分字符集和编码方案的概念，所以后边唠叨的时候把utf8、utf16、utf32都当作一种字符集对待。

对于同一个字符，不同字符集也可能有不同的编码方式。比如对于汉字 ‘我’ 来说， ASCII 字符集中根本没有收录这个字符， utf8 和 gb2312 字符集对汉字 我 的编码方式如下：

utf8编码：111001101000100010010001 (3个字节，十六进制表示是：0xE68891) 

gb2312编码：1100111011010010 (2个字节，十六进制表示是：0xCED2) 

5.MySQL中的utf8和utf8mb4

我们上边说 utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示 了。而在 MySQL 中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以设计 MySQL的大叔偷偷的定义了两个概念：

utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。

utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

有一点需要大家十分的注意，在 MySQL 中 utf8 是 utf8mb3 的别名，所以之后在 MySQL 中提到 utf8 就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，那请使用 utf8mb4 。

字符集的查看

MySQL 支持好多好多种字符集，查看当前 MySQL 中支持的字符集可以用下边这个语句：

show charset;

MySQL数据操作DML

数据的增删改

添加数据

#标准添加（指定所有字段，给定所有的值）
insert into stu(id,name,age,sex,classid) values(null,'张三',20,'m','lamp138');

#指定部分字段添加值
insert into stu(name, classid) value('李四','lamp138');

#不指定字段添加值
insert into stu value(null,'王五',21,'w','lamp138');

#批量添加值
mysql> insert into stu values
-> (null,'zhaoliu',25,'w','lamp94'), 
-> (null,'uu01',26,'m','lamp94'), 
-> (null,'uu02',28,'w','lamp92'), 
-> (null,'qq02',24,'m','lamp92'), 
-> (null,'uu03',32,'m','lamp138'), 
-> (null,'qq03',23,'w','lamp94'), 
-> (null,'aa',19,'m','lamp138');

修改数据

#格式：update 表名 set 字段=值... where 条件

#将id为11的age改为35，sex改为m值
update stu set age=35,sex='m' where id=11;

#将id为12和14的数据值sex改为m，classid改为lamp92
update stu set sex='m',classid='lamp92' where id=12 or id=14;
#等价于
update stu set sex='m',classid='lamp92' where id in(12,14);

删除数据

#格式：delete from 表名 [where 条件]

#删除stu表中id值为100的数据
delete from stu where id=100;

#删除stu表中id为20到30的数据
delete from stu where id>=20 and id<=30;
#等价于
delete from stu where id between 20 and 30;

#删除stu表中id大于200的数据
delete from stu where id>200;

从select开始的SQL查询语句

检索数据select

检索单个列

select name from stu

​ 返回数据的顺序可能是数据被添加到表中的顺序，也可能不是

检索多个列

select id,name,age,sex from stu

检索所有列

select * from stu

​ 除非确实需要表中的每个列，否则最好别使用*通配符

检索不同的列DISTINCT

select distinct classid from stu

此关键词指示MySQL只返回不同的值

限制结果LIMIT

select * from stu limit 3,4

limit 3,4指的是跳过前3行，取4行

代替语法：limit 4 offset 3

使用完全限定的表名

select name from stu;
select stu.name from stu;
select stu.name from X.stu;

对检索的数据进行排序 ORDER BY

排序数据 ORDER BY

select * from stu order by age;

按多个列排序

select * from stu order by classid,age;
#在需要多列数据排序时，使用逗号分割列名，会按照前后顺序一次对比排序
#order by的排序默认为升序，可以使用desc设值降序
select * from stu order by classid,age desc;

#order by子句应保证位于from子句之后
#如果使用limit，它必须位于order by之后

数据检索条件过滤where

数据表一般包含大量的数据，很少需要检索所有的行

通常只会根据特定操作或需求提取表的子集

只检索所需数据需要指定搜索条件，也称为过滤条件

在select语句中，数据根据where子句中指定的搜索条件进行过滤

select name from stu where age = 22;

同时使用order by和where时，应该让order by位于where之后，否则会产生错误

where子句操作符

操作符	说明
=	等于
<> !=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN…and…	指定两个值之间
IS NULL	空值

组合where子句

where … and … 与

where … or … 或

and优先级高于or，使用括号保证实现

IN与NOT IN

#IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配
select name from stu where class_id in (1,2);

#在使用长的合法选项清单时，IN操作符的语法更清楚且更直观
#在使用IN时，计算的次序更容易管理（使用的操作符更少）
#IN操作符一般比OR操作符清单执行更快
#IN的最大优点是可以包含其他SELECT语句，是的能够更动态地建立WHERE语句

select name from stu where class_id not in(18,19);

#使用NOT原因：
#对于简单的WHERE子句，使用NOT并没有什么优势
#但是在更加复杂的子句中，NOT有优势
#例如在于IN联合使用时，NOT使找出与条件列表不匹配的行非常简单

通配符的使用

之前的操作符都是针对已知值进行过滤的

但是这种过滤方法有些情况不适合

例如搜索产品中包含某段文本的产品，必须使用通配符

在搜索子句中使用通配符，必须使用LIKE操作符

LIKE指示MySQL，之后跟的搜索模式利用通配符匹配而不是直接相等匹配

#百分号（%）通配符在搜索串中，%表示任何字符出现任意次数
select name from stu where name like 'a%'; #以a开头
select name from stu where name like '%a'; #以a结尾
select name from stu where name like '%a%'; #包含a

#下划线（_）通配符用途与%一样，但下划线只匹配单个字符而不是多个字符

使用通配符的技巧

通配符搜索的处理一般要比其他搜索所花时间更长

1.不要过度使用通配符，如果其他操作符能达到相同的目的，应该使用其他操作符
2.在确实需要使用操作符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处
3.把通配符至于搜索模式的开始处，搜索起来是最慢的
4.仔细注意通配符的位置，如果放错地方，可能不会返回想要的数据

字段的计算

函数的使用

字符串函数

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
SubString()	返回子串的字符
Upper()	将串转换为大写
Concat()	拼接字符串

聚集函数与分组Group By

聚集函数的使用

确定表中行数（或者满足某个条件或包含某个特定值的行数）

获取表中行组的和

找出表列（或所有行或某些特定的行）的最大值、最小值和平均值

上述例子都需要对表中数据（而不是实际数据本身）进行汇总
因此，返回实际表数据是对时间和处理资源的一种浪费

函数	说明
COUNT()	返回某列行数
MAX()	返回某列最大值
MIN()	返回某列最小值
SUM()	返回某列值之和
AVG()	返回某列平均值

注意：在使用count时，如果指定列名，则指定列的值为空的行被忽略，但如果COUNT()函数中用的是 * ，则不忽略

数据分组GROUP BY与HAVING

GROUP BY

#获取某个班级的人数
select count(*) as nums from stu where class_id=2;

#返回每个班级的人数 需要用到分组
select class_id,count(*) as nums from stu group by class_id;

HAVING

MySQL还允许过滤分组

事实上，目前学过的所有类型的where子句都可以用having来代替

唯一的差别是where过滤行，having过滤分组

使用group by进行分组时的注意点

***在使用group by进行分组时，按照标准的SQL模式，需要把select查询中的所有列（除了聚集函数外）全部都列在group by后面

MySQL数据导入导出

数据导出

1.数据库导出

#不进入MySQL 
mysqldump -u root -p database名 > /Desktop/code/....sql

导出一个库中所有数据，会形成一个建表和添加语句的sql文件

之后可以用这个sql文件到别的库，或者本机中创建或回复这些数据

2.将数据库中的表导出

#不进入MySQL
mysqldump -u root -p database名 tts > /Desktop/code/...-tts.sql

数据导入

把导出的sql文件数据导入到mysql数据库中

#在新的数据库中导入备份的数据
mysql -u root -p 库名 < xxx.sql

#把导出的表sql导入数据库
mysql -u root -p 库名 < xxx.sql

MySQL权限管理

mysql中的root是数据库中权限最高的用户，不可以用在项目中

可以给不同的用户或者项目，创建不同的mysql用户，并适当的授权，来完成数据库的相关操作

创建用户的语法格式

grant 授权的操作 on 授权的库.授权的表 to 账户@登录地址 identified by ‘密码’;

# 在mysql中创建一个zhangsan用户，授权可以对chuange这个库中的所有表 进行添加和查询的权限 
grant select,insert on change.* to zhangsan@'%' identified by '123456';
# 用户lisi。密码 123456 可以对tlxy库中的所有表有所有操作权限
grant all on tlxy.* to lisi@'%' identified by '123456';
# 删除用户 
drop user 'lisi'@'%';

/Desktop/code/…sql

导出一个库中所有数据，会形成一个建表和添加语句的sql文件

之后可以用这个sql文件到别的库，或者本机中创建或回复这些数据



2.将数据库中的表导出

```mysql
#不进入MySQL
mysqldump -u root -p database名 tts > /Desktop/code/...-tts.sql

数据导入

把导出的sql文件数据导入到mysql数据库中

#在新的数据库中导入备份的数据
mysql -u root -p 库名 < xxx.sql

#把导出的表sql导入数据库
mysql -u root -p 库名 < xxx.sql

MySQL权限管理

mysql中的root是数据库中权限最高的用户，不可以用在项目中

可以给不同的用户或者项目，创建不同的mysql用户，并适当的授权，来完成数据库的相关操作

创建用户的语法格式

grant 授权的操作 on 授权的库.授权的表 to 账户@登录地址 identified by ‘密码’;

# 在mysql中创建一个zhangsan用户，授权可以对chuange这个库中的所有表 进行添加和查询的权限 
grant select,insert on change.* to zhangsan@'%' identified by '123456';
# 用户lisi。密码 123456 可以对tlxy库中的所有表有所有操作权限
grant all on tlxy.* to lisi@'%' identified by '123456';
# 删除用户 
drop user 'lisi'@'%';