数据库介绍

  • 什么是数据库

    数据库(Database)就是按照数据结构来组织，存储和管理数据的仓库。
    专业的数据库是专门对数据进行创建，访问，管理，搜索等操作的软件，比起我们自己用文件读写的方式，用它对数据进行管理更加的方便，快速，安全。

  • 数据库特点及作用

    • 对数据进行持久化的保存
    • 方便数据的存储和查询，速度快，安全，方便
    • 可以处理并发访问
    • 更加安全的权限管理访问机制

  • 常见的数据库

    数据库分两大类，一类是 关系型数据库。另一类叫做 非关系型数据库。

    • 关系型数据库： MySQL，Oracle，PostgreSQL，SQLserver…
    • 非关系型数据库：Redis内存数据库，MongoDB文档数据库…

    关系型数据库与非关系型数据库对比可以看这篇文章:

    https://my.oschina.net/u/3647687/blog/1534979

‘

MySQL是最流行的关系型数据库管理系统

关于数据库的一些概念

  • 数据库 Database
  • 数据表 Tables
  • 数据字段 filed
  • 行 row ：每一行就是一条数据
  • 列 col

MySql基础操作

使用MySql的三种方式：

方式一：通过在命令行敲命令来操作

方式二：通过图型界面工具,如 Navicat 等

方式三：通过编程语言(python,php,java,go…)执行mysql命令

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

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

####SQL语句中的快捷指令

\G 格式化输出（文本式，竖立显示）

\s 查看服务器端信息

\c 结束命令输入操作

\q 退出当前sql命令行模式

\h 查看帮助

数据库操作步骤(windows命令行)

  1. 开启数据库服务：
1
net start 你电脑中mysql服务的服务名

你mysql服务的服务名一般安装时就指定好的，默认是mysql。如果不是可以从系统服务里找一下

windows中查看你的mysql服务名

  1. win+R键 打开运行，输入services.msc，确定

    image-20201107184754395

  2. 找到你mysql的服务名

    image-20201107185331055

关闭数据库服务：

1
net stop 你电脑中mysql服务的服务名
  1. 连接数据库：
1
mysql -h 服务器地址 -u 用户名 -p (密码) -P 端口

image-20201107190657607

如果时本地操作可以省略 -h 和 -P，写成这样即可

1
mysql -u 用户名 -p (密码)
  1. 操作(增删改)

后面会详细讲解

  1. 退出MySql
1
2
3
exit;
-- 或者
quit;

也可以用快捷命令\q

MySql数据类型

数据类型是定义列中可以存储什么类型的数据以及该数据实际怎样存储的基本规则

数据类型限制存储在数据列中的数据的类型。例如，数值数据类型列只能接受数值类型的的数据

在设计表时，应该特别重视所用的数据类型。使用错误的数据类型可能会严重地影响应用程序的功能和性能。

后续更改包含数据的列不是一件小事（而且这样做可能会导致数据丢失），定义之后一般不会再修改字段的数据类型

基本常用的数据类型

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

字符串数据类型

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

最常用的有三种基本的串类型，分别为定长串char、变长串varchar和变成文本text

定长串：char

  • 接受长度固定的字符串，其长度是在创建表时指定的。不允许存储多于指定长度字符的数据。

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

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

变长串 ：varchar

  • 存储可变长度的字符串

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

注意：

既然变长串数据类型这样灵活，为什么还要使用定长串数据类型？

回答：因为性能，MySQL处理定长列远比处理变长列快得多。

变长文本：text

变长文本类型存储，最大长度为64K

串类型

数值类型

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

数值类型

举例：

1
2
3
4
decimal(5, 2) 	--表示数值总共5位, 小数占2位
tinyint 		--1字节(8位) 0-255/-128-127
int 			--4字节。 -21亿-21亿/0-42亿
-- MySQL中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8, 2)或float

有符号或无符号

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

有符号数值列可以存储正或负的数值

无符号数值列只能存储正数。

默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字

日期和时间类型

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

1
datetime -- 8字节 范围：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

二进制数据类型

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

通常情况下，不会使用二进制数据类型进行存储到数据库中

二进制类型

表的字段约束

  • unsigned 无符号(给数值类型使用，表示为正数，不写可以表示正负数都可以)
  • 字段类型后面加括号限制宽度
    • char(5)、varchar(7) 在字符类型后面加限制，表示字符串的长度
    • int(4) 没有意义，默认无符号的int为int(11)，有符号的int(10)，在数字类型后面只加(x)限制宽度没有意义。
    • int(4) unsigned zerofill 只有当给int类型设置有前导零(zerofill)时，设置int的宽度才有意义(不够长度会在前面补 0)。
  • not null 不能为空，在操作数据库时如果输入该字段的数据为NULL ，就会报错
  • default 设置默认值
  • primary key 主键不能为空，且唯一。一般和自动递增一起配合使用。
  • auto_increment 定义列为自增属性，一般用于主键，数值会自动加1
  • unique 唯一索引(数据不能重复:用户名)可以增加查询速度，但是会降低插入和更新速度

MySQL的运算符

  • 算术运算符： +、 -、 *、 /或DIV、 %或MOD

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

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

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

  • like: 支持特殊符号 % 和 _

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

    详细内容可以看菜鸟编程MySQL运算符

主键

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

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

对于主键的要求

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

  • 不允许NULL

  • 不在主键列中使用可能会更改的值。（例如，如果使用一个名字作为主键以标识某个供应商，当该供应商合并或更改其名字时，就得更改这个主键。）

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

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

MySql数据库表引擎与字符集

服务器处理客户端请求

其实不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：客户端进程向服务器进程发送一段文本（MySQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。

那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？客户端可以向服务器发送增删改查各类请求，我们这里以比较复杂的查询请求为例来画个图展示一下大致的过程：

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

存储引擎

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

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

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

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

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

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

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的行锁，InnoDB的行锁是基于索引建立的，如果索引失效或者没有索引，那么，行锁失效，由行锁升级为表锁。

  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。

字符集和编码

我们知道在计算机中只能存储二进制数据，那该怎么存储字符串呢？当然是建立字符与二进制数据的映射关系了，建立这个关系最起码要搞清楚两件事儿：

  1. 你要把哪些字符映射成二进制数据？也就是界定清楚字符范围。

  2. 怎么映射？

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

人们抽象出一个字符集的概念来描述某个字符范围的编码规则，以下是一些常用字符集：

  • ASCII 字符集

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

    1
    2
    '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 。

  • GBK 字符集

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

  • Unicode 字符集

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

    1
    2
    'L' -> 01001100（十六进制：0x4C）
    '啊' -> 111001011001010110001010（十六进制：0xE5958A）

    我们常用的utf8只是Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、utf16、utf32这几种编码方案，utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个字符，utf32使用4个字节编码一个字符

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

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

更多关于字符集和编码的内容可以看下面这篇文章

http://www.ruanyifeng.com/blog/2007/10/ascii_unicode_and_utf-8.html

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 中支持的字符集可以用下边这个语句：

1
show charset;

Mysql数据库与数据表操作

库和表的概念与关系

库就像是文件夹，库中可以有很多个表，表就像是我们的excel表格文件一样，每一个表中都可以存储很多数据。

mysql中可以有很多不同的库，库中可以有很多不同的表，表中可以定义不同的列（字段），表中可以根据结构去存储很多的数据。

数据库的操作

数据库创建

1
2
3
4
5
6
# 链接mysql数据库后，进入mysql后可以操作数据
# 创建库
create database if not exists test default charset=utf8mb4;
-- 1. 数据库 test 如果不存在则创建数据库，存在则不创建
-- 2. 创建 test 数据库，并设置字符集为utf8
-- 3. 无特殊情况都要求字符集为utf8或者utf8mb4的字符编码

查看所有库

1
2
# 查看所有库
show databases;

打开库/进入库/选择库

1
2
# use 库名
use test;

删除库

删库有风险，动手需谨慎！！！

1
2
# 删除库，库中的所有数据都将在磁盘中删除。
drop database 库名

数据表的操作

创建表

语法格式：

1
create table 表名(字段名，类型，[字段约束]，…)；

实例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 以下创建一个 users 的表
create table if not exists 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;
-- 表引擎innodb 字符集utf8mb4

# 查看表结构
desc users;

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

创建表的基本原则：

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

修改表结构

语法格式：

1
alter table 表名 action (更改的选项)
添加字段

语法：

1
alter table 表名 add 添加的字段信息

实例：

1
2
3
4
5
6
7
8
-- 在 users 表中 追加 一个 num 字段 int类型 不为空 默认加在最后面
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;
删除字段
1
2
# 删除字段 alter table 表名 drop 被删除的字段名
alter table users drop aa;
修改字段

语法：

1
2
3
alter table 表名 change|modify 被修改的字段信息
# change: 可以修改字段名，
# modify: 不能修改字段名。

实例：

1
2
3
4
# 修改表中的 num 字段 类型,使用 modify 不修改字段名
alter table users modify num tinyint not null default 12;
# 修改表中的 num 字段 为 int 并且 字段名改为 mm
alter table users change num mm int;

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

修改表名

1
alter table 原表名 rename as 新表名;

更改表中自增的值

1
2
3
# 在常规情况下，auto_increment 默认从1开始继续递增
# 更改自增值让其从1000开始
alter table users auto_increment = 1000;

 注意：

更改之后，只对后面再增加的数据有影响，对已存在的数据没有影响。

修改表引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 推荐在定义表时，表引擎定义为 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 test where name = 'users'\G;
*************************** 1. row ***************************
			Name: users
		   Engine: InnoDB
		   ....

# 修改表引擎语句
alter table users engine = 'myisam';

删除表

1
drop table 表名

MySQL 数据操作——DML

数据的DML操作：添加数据，修改数据，删除数据

添加数据

格式：

1
insert into 表名[(字段列表)] values(值列表...);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 标准添加（指定所有字段，给定所有的值）
insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','lamp138');


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


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

-- 批量添加值
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');

修改数据

格式：

1
update 表名 set 字段1=值1,字段2=值2,字段n=值n... where 条件;
1
2
3
4
5
6
-- 将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);  -- 等价于上面

删除数据

格式：

1
delete from 表名 [where 条件];
1
2
3
4
5
6
7
8
9
-- 删除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;

MySQL数据查询——DQL基本语法

语法格式： sql查询语句 一定要严格按照这样的顺序，否则会产生错误

1
2
3
4
5
select 字段列表|* from 表名
[where 搜索条件]
[group by 分组字段 [having 分组条件]]
[order by 排序字段 排序规则]
[limit 分页参数]

下面以一个数据表stu为例进行讲解

SQL示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 创建表
CREATE TABLE `stu`(
	`id`  int unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(10) NOT NULL,
	`email` char(50) NOT NULL,
	`phone` char(11) NOT NULL,
	`age` tinyint(4)NOT NULL,
	`sex` char(1) DEFAULT NULL,
	`class_id` int DEFAULT NULL, 
    PRIMARY KEY(id)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

-- 添加数据
insert into stu values
(null,'张三','zhangsane@qq.com','13701184321',21,'男',1),
(null,'李四','lisi@qq.com','13701104322',22,'男',1),
(null,'王五','wangwu@qq.com','13701104323',20,'女',1),
(null,'赵六','zhaoliu@qq.com','13701104324',19,'男',1),
(null,'田七','tianqi@qq.com','13701104325',23,'女',1),
(null,'王五六','wangwuliu@qq.com','13701104326',23,'女',1),
(null,'熊大','xiongda@qq.com','13701184327',25,'男',2),
(null,'熊二','xionger@qq.com','13701104328',22,'男',2),
(null,'一一','yiyi@qq.com','13701184329',19,'女',2),
(null,'呀呀','yaya@qq.com','13701104320',22,'男',2);
 
 -- 查看表数据
select * from stu;

检索数据SELECT

检索列

1
2
3
4
5
6
7
8
# 检索单个列
select name from stu; -- 查询 stu 表中 name （字段）列的所有数据

# 检索多个列
select id, name, age, sex from stu; 

# 检索所有列
select * from stu;

如果没有明确排序查询结果，则返回的数据的顺序没有特殊意义。

返回数据的顺序可能是数据被添加到表中的顺序，也可能不是。只要返回相同数目的行，就是正常的

使用通配符 * 的说明：

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

虽然使用通配符，不用明确列出所需列，会使你自己省事，但检索不需要的列通常会降低检索和应用程序的性能。使用通配符有一个优点： 由于不明确指定列名（因为 * 检索每个列），所以能检索出名字未知的列。

检索不同的行（去重） DISTNCT

1
2
# 检索不同的行（去重） DISTNCT
select distinct class_id from stu;

DISTINCT关键字指示MySQL只返回不同的值。

DISTINCT关键字应用于所有列，而不仅是前置它的列。

如果给出SELECT DISTINCT 字段1,字段2,.... FROM xxx;除非指定的列都不同，否则所有行都将被检索出来

使用完全限定的表名

1
2
3
select name from stu; -- 无限制
select stu.name from stu; -- 限制字段名 stu.name stu表的name字段
select stu.name from test.stu;  -- 限制表名 test.stu test库的stu表

WHERE 数据检索条件过滤

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

通常只会根据特定操作或报告的需要提取表数据的子集。

只检索所需数据需要指定搜索条件（search criteria)，搜索条件也称为过滤条件（filtercondition)。

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

  • 可以在where子句中指定任何条件
  • 可以使用 and 或者 or 指定一个或多个条件
  • where条件也可以运用在update和delete语句的后面

WHERE 子句操作符

操作符 说明
= 等于
<> != 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN…AND… 指定两值之间
IS NULL 空值 
1
2
3
4
5
6
7
8
-- 查询 stu 表中 age > 22的数据
select * from stu where age > 22;

-- 查询 stu 表中 name=某个条件值 的数据
select * from stu where name = '王五';

-- 查询 stu 表中 sex 为 null 的数据
select * from stu where sex is null;

组合WHERE子句

MySQL允许给出多个WHERE子句。

这些子句可以两种方式使用：以AND子句的方式或OR子句的方式使用。也称为逻辑操作符

  • AND 检索满足所有给定条件的行
  • OR 检索满足任一给定条件的行

示例：

1
2
3
4
5
6
7
8
9
10
-- 查询 stu 表中 年龄在22到25之间的数据
select * from stu where age >= 22 and age <= 25;
select * from stu where age between 22 and 25;

-- 查询 stu 表中 年龄不在22到25之间的数据
select * from stu where age < 22 or age > 25;
select * from stu where age not between 22 and 25;

-- 查询 stu 表中 年龄在22到25之间的女生信息
select * from stu where age >= 22 and age <= 25 and sex = '女';

注意

and 和 or 在使用时要注意 sql语句优先级的问题，sql会优先处理and条件

比如下面语句：

1
select * from stu where age=22 or age = 25 and sex = '女';

假如要求查询 users 表中 年龄为22或者25 的女生信息，上面语句查询的结果就不能符合我们的要求。

上面语句查询的是 （年龄为22的所有人） 或者 （年龄为25的女生）信息：

要达到我们要求的查询，可以用小括号来提升优先级

1
select * from stu where (age=22 or age = 25) and sex = '女';

IN与NOT

1
2
3
4
-- 查询 stu 表中 id 是1或2或3的数据
select * from stu where id in (1,2,3);
-- 查询 stu 表中 id 不是1 也不是2 也不是3的数据
select * from stu where id not in (1,2,3);

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。功能与OR相当

NOT 用于否定后跟进的条件

与OR，相比，使用IN的优点

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

通配符的使用 LIKE

我们可以在where条件中使用 =，<，> 等符号进行条件的过滤，但是当想查询某个字段是否包含某个字符时如何过滤呢？例如： 查询 name字段中包含五的数据

可以使用LIKE语句进行某个字段的模糊搜索。

常用通配符：

  • 百分号(%) ：表示任意字符出现任意次数
  • 下划线(_)：用途与%类似，但下划线只匹配单个字符
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- like 语句 like某个确定的值 和。where name = '王五' 是一样
select * from stu where name like '王五';

-- 使用 % 模糊搜索。%代表任意个任意字符
	-- 查询name字段中包含五的
	select * from stu where name like '%五%';
	
	-- 查询name字段中最后一个字符 为 五的
	select * from stu where name like '%五';
   
    -- 查询name字段中第一个字符 为 王 的
	select * from stu where name like '王%';
	
-- 使用 _ 单个的下划线。表示一个任意字符，使用和%类似
	-- 查询表中 name 字段为两个字符的数据
	select * from stu where name like '__';
	
	-- 查询 name 字段最后为 五 的两个字符的数据
	select * from stu where name like '_五';

where子句中的like在使用%或者_进行模糊搜索时，所花时间更长，效率不高，使用时注意：

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

MySql中也支持正则表达式，使用 REGEXP 关键字来匹配。

1
2
-- 查询 id 为 3到8 的所有数据
select * from stu where id regexp '[3-8]';

字段的计算

存储在数据库表中的数据一般不是应用程序所需要的格式。下面举几个例子:

  • 如果想在一个字段中既显示用户名，又显示班级号，但这两个信息一般包含在不同的表列中。
  • 同时姓名、手机号和地址存储在不同的列中（应该这样）,但快递单打印程序却需要把它们作为一个恰当格式的字段检索出来。
  • 列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。
  • 物品订单表存储物品的价格和数量，但不需要存储每个物品的总价格（用价格乘以数量即可）。为打印发票需要物品的总价格。
  • 需要根据表数据进行总数、平均数计算或其他计算

计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的

拼接Concat

stu表包含用户名和手机号码信息。假如要生成一个学生报表，需要在学生的名字中按照name(phone)这样的
格式列出

解决办法是把两个列拼接起来。在MySQL的SELECT语句中，可使用concat()函数来拼接两个列

1
select concat(name ,'(', phone,')') from stu;

使用别名AS

SELECT语句拼接字段可以完成。但此新计算列的名字是什么呢？实际上它没有名字，它只是一个值。
如果仅在SQL查询工具中查看一下结果，这样没有什么不好。

但是，一个未命名的列不能用于客户机应用中，因为客户机没有办法引用它。

为了解决这个问题，SQL支持列别名。别名（alias)是一个字段或值的替换名。别名用AS关键字赋予

1
select concat(name ,'(',phone,')') as name_phone from stu;

MySql查询常用函数

与其他大多数计算机语言一样，SQL支持利用函数来处理数据。

函数一般是在数据上执行的，它给数据的转换和处理提供了方便。

文本处理函数

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html

image-20201108204526003

日期和时间处理函数

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

1
2
-- 根据出生日期计算年龄
TIMESTAMPDIFF(YEAR, @birthday, CURDATE())

数值处理函数

https://dev.mysql.com/doc/refman/5.7/en/numeric-functions.html

Mysql中的统计函数（聚合函数）

我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。

使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成

  • max() 返回某列最大值
  • min() 返回某列最小值
  • count() 返回某列的行数
  • sum() 返回某列值和
  • avg() 返回某列的平均值

在使用count时，如果指定列名，则指定列的值为空的行被忽略，但如果COUNT函数中用的是星号（*）,则不忽略。

例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
# 计算 users 表中 最大年龄，最小年龄，年龄和及平均年龄
select max(age),min(age),sum(age),avg(age) from stu;
+----------+----------+----------+----------+
| max(age) | min(age) | sum(age) | avg(age) |
+----------+----------+----------+----------+
|       25 |       19 |      216 |  21.6000 |
+----------+----------+----------+----------+
-- 上面数据中的列都是在查询时使用的函数名，不方便阅读和后期的调用，可以通过别名方式 美化
select max(age) as max_age,
min(age) as min_age,
sum(age) as sum_age,
avg(age) as avg_age from stu;
+---------+---------+---------+---------+
| max_age | min_age | sum_age | avg_age |
+---------+---------+---------+---------+
|      25 |      19 |     216 | 21.6000 |
+---------+---------+---------+---------+
-- 统计 users 表中的数据量
select count(*) from stu;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
select count(id) from stu;
+----------+
| count(id)|
+----------+
|       10 |
+----------+

-- 上面的两个统计，分别使用了 count(*) 和 count(id),结果目前都一样，有什么区别？
-- count(*) 是按照 users表中所有的列进行数据的统计，只要其中一列上有数据，就可以计算
-- count(id) 是按照指定的 id 字段进行统计，也可以使用别的字段进行统计，
-- 但是注意，如果指定的列上出现了NULL值，那么为NULL的这个数据不会被统计
-- 假设有下面这样的一张表需要统计
+------+-----------+------+--------+-----------+------+------+
| id   | name      |  age |  phone |     email |  sex | mm   |
+------+-----------+------+--------+-----------+------+------+
| 1000 | aa        | 20   | 123    | NULL      | 男   | NULL |
| 1001 | bb        | 20   | 123456 | NULL      | 男   | NULL |
| 1002 | cc        | 25   | 123    | NULL      | 女   | NULL |
| 1003 | dd        | 20   | 456    | NULL      | 女   | NULL |
| 1004 | ff        | 28   | 789    | NULL      | NULL | NULL |
+------+-----------+------+--------+-----------+------+------+
5 rows in set (0.00 sec)
-- 如果按照sex这一列进行统计，结果就是4个而不是5个，因为sex这一列中有NULL值存在
mysql> select count(sex) from stu;
+------------+
| count(sex) |
+------------+
| 4          |
+------------+

聚合函数除了以上简单的使用以外，通常情况下都是配合着分组进行数据的统计和计算

GROUP BY 分组

group by 语句根据一个或多个列对结果集进行分组

一般情况下，是用于数据的统计或计算，配合聚合函数使用

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 统计 users 表中 男女生人数,
-- 很明显按照上面的需要，可以写出两个语句进行分别统计
select count(*) from stu where sex = '女';
select count(*) from stu where sex = '男';
-- 可以使用分组进行统计，更方便
select sex,count(*) from stu group by sex;

-- 统计1班和2班的人数
select class_id,count(*) from stu group by class_id;

-- 分别统计每个班级的男女生人数
select class_id,sex,count(*) as num from stu group by class_id,sex;

注意：在使用group by分组时，一般除了聚合函数，其它在select后面出现的字段列都需要出现在grouop by 后面。

HAVING子句

having 在分组聚合计算后，对结果再一次进行过滤，类似于where，where过滤的是行数据，having过滤的是分组数据

1
2
3
4
-- 要统计班级人数
select class_id,count(*) from stu group by classid;
-- 统计班级人数，并且要人数达到5人及以上
select class_id,count(*) as num from stu group by class_id having num >=5;

ORDER BY 对检索数据进行排序

默认查询出的数据，并不是随机排序的，如果没有指定排序，数据一般将以它在底层表中出现的顺序显示，关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。

我们在mysql中使用select的语句查询的数据结果是根据数据在底层文件的结构来排序的，在需要排序时可以使用order by对返回的结果进行排序

order by asc 升序，默认

  • desc 降序
1
2
3
4
5
6
7
8
-- 按照年龄对结果进行排序，
	-- 从大到小
	select * from users order by age desc;
	-- 从小到大排序 asc 默认可以不写
	select * from users order by age;
-- 也可以按照多个字段进行排序
select * from users order by age,id; # 先按照age进行排序，age相同情况下，按照id进行排序 升序
select * from users order by age,id desc; -- 降序

限制结果 LIMIT

  • limit n 提取n条数据，
  • limit m,n 跳过m条数据，提取后面的n条数据
  • limit n offset m 跳过m条数据，提取后面的n条数据 ，等同于limit m,n
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查询stu 表中的数据，只要3条
select * from stu limit 3;

-- 跳过前3条数据，再取4条数据
select * from stu limit 3,4;
select * from stu limit 4 offset 3;
-- limit一般应用在数据分页上面
-- 例如每页显示10条数据
第一页 limit 0,10
第二页 limit 10,10
第三页 limit 20,10
第四页 limit 30,10
...
-- 查询stu 表中年龄最大的两个人的信息
select * from stu order by age desc limit 2;

image-20201108191010335

总结

mysql中的查询语句比较灵活多样，并且在使用查询语句时，一定要注意sql的正确性和顺序。

image-20201108162518097

Mysql数据库导入导出和授权

数据导出

  • 数据库数据导出：在命令行中然后输入以下命令 导出某个库中的数据
1
2
3
mysqldump -u root -p 库名 > 要保存的文件路径/xxx.sql
# 比如：
mysqldump -u root -p test > D://test.sql

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

  • 将数据库中的表导出 :在命令行中然后输入以下命令 导出某个库中指定表的数据
1
2
3
mysqldump -u root -p 库名 表名 > 要保存的文件路径/xxx.sql
# 比如：
mysqldump -u root -p test users > D://test-users.sql

数据导入

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

1
2
3
4
# 在新的数据库中 导入备份的数据，导入导出的sql文件
mysql -u root -p 新建的库名 < 路径/xxx.sql
# 把导出的表sql 导入数据库
mysql -u root -p 新建的库名 < 路径/xxx.sql

权限管理

mysql中的root用户是数据库中权限最高的用户，千万不要用在项目中。

可以给不同的用户，或者项目，创建不同的mysql用户，并适当的授权，完成数据库的相关操作，这样就在一定程度上保证了数据库的安全。

创建用户的语法格式：

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

示例：

1
2
3
4
5
6
7
8
# 在mysql中 创建一个 zhangsan 用户，授权可以对test这个库中的所有表 进行 添加和查询 的权限
grant select,insert on test.* to zhangsan@'%' identified by '123456';

# 用户 lisi，密码 123456 可以对test库中的所有表有所有操作权限
grant all on test.* to lisi@'%' identified by '123456';

# 删除用户 lisi
drop user 'lisi'@'%';