MySQL基础¶
2025-03-032025-03-04
假设现在你已经是某大型互联网公司的高级程序员，让你写一个火车票购票系统，来hold住十一期间全国的购票需求，你怎么写？
在同一时段抢票的人数如果太多，那么你的程序不可能运行在一台机器上，应该是多台机器一起分担用户的购票请求。
那么问题就来了，票务信息的数据存在哪里？存在文件里么？
如果存储在文件里，那么存储在哪一台机器上呢？是每台机器上都存储一份么？
首先，如果其中一台机器上卖出的票另外两台机器是感知不到的，其次，如果我们将数据和程序放在同一个机器上，如果程序和数据有一个出了问题都会导致整个服务不可用。最后，操作或修改文件中的内容对python代码来说是一件很麻烦的事。
基于上面这些问题，单纯的将数据存储在和程序同一台机器上的文件中是非常不明智的。
1. 基本概念¶
根据上面的例子，我们可以知道，将文件和程序存在一台机器上是很不合理的，同时，操作文件是一件很麻烦的事，所以我们可以使用数据库来存储数据。
1.1 数据¶
所谓的数据（Data），就是描述主观或客观存在的人、事、物特征的符号记录（Record），描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机。如：
1.2 记录¶
所谓的记录（Record），就是指代描述事物的一系列特征而组成的相关信息集合，在计算机中描述一个事物的记录，就相当于文件里的一行内容。
当然单纯的一行记录如果没有任何的组织结构或没有任何的说明，实际上是没有任何意义，如果我们按逗号作为分隔，依次定义各个信息代表的具体意义，相当于定义一张表格一样，如：
| name | sex | age | birthday | province | major | job |
|---|---|---|---|---|---|---|
| moluo | True | 18 | 2000 | 广东 | 计算机科学与技术 | python开发 |
这样我们就可以了解moluo，性别为男，年龄18岁，出生于2000年，来自于广东，计算机专业等相关信息。
1.3 数据表¶
数据表（Data Table，或者Table）就如同上面的表格一样，在第二行以后还可以继续写入其他人的相关信息记录。最终保存为一个文件的形式。我们可以通俗的理解这个文件就是一个数据表。
| name | sex | age | birthday | province | major | job |
|---|---|---|---|---|---|---|
| moluo | True | 18 | 2000 | 广东 | 计算机科学与技术 | python开发 |
| xiaohong | False | 17 | 2000 | 广西 | 舞蹈学 | 音乐老师 |
当然，开发中我们有时候一个程序或项目，要保存的数据会非常多，相关的数据会保存成一个数据表，那么当数据表文件特别多时，我们又要怎么管理呢？ 是不是得建一个文件夹保存这些文件啊？
1.4 数据库¶
数据库（Database） 就是一种按照特定数据结构来组织、存储和管理数据的仓库。通俗地理解，数据库就是一个存储数据表的特殊文件夹，只不过这个仓库可以将数据按照特定的数据结构进行高效压缩存储在磁盘上，同时为了方便用户组织和管理数据，数据库还会专门配套了数据库管理系统（Database Management System 简称DBMS）。用户通过操作数据库管理系统提供的功能，就可以有效的组织和管理存储在数据库中的数据。
1.4.1 数据库管理系统¶
在了解了Data与Database的概念后，如何科学地组织和存储数据，如何高效获取和维护数据成了关键，这就需要用到了一个系统软件---数据库管理系统。
所谓的数据库管理系统（DataBase Management System，简称DBMS），就是操纵和管理数据库的软件，基于这个软件，用户可以对数据库进行科学地组织和存储数据，它对数据库进行统一的管理和控制，可以保证数据库的数据的安全性和完整性。
1.4.2 数据库服务器¶
数据库管理系统本质上就是一套基于socket通信实现的Client/Server架构的软件，所以我们肯定需要一台计算机来安装并运行起来，而安装了数据库管理系统的服务端程序的计算机，我们一般就称之为数据库服务器。用户通过安装数据库管理系统客户端的计算机，我们就称之为数据库客户端。
一般而言，数据库服务器，我们都会选择安装在linux、unix或window server等系统服务器上，而数据库客户端则可以是如下三种：
- 界面化管理工具，如Navicat，pycharm，Induction
- 代码程序，java、python、php等编程语言基于socket通讯实现的，往往作为一个工具类或者模块被程序直接调用。
- 终端命令行工具，cmd/bash
1.4.3 数据库类型¶
数据库类型，也就是数据库管理系统的类型，一般基于不同的操作方式或不同的底层实现，会分2大类：关系型数据库与菲关系型数据库。
1.4.3.1 关系型数据库¶
关系型数据库（RMDBS）是建立在关系模型基础上的数据库，即数据库中表与表的数据之间存在某种关联的内在关系，因为这种内在关系，称这种数据库为关系型数据库。因这一类数据都是使用了SQL（结构查询语言）来操作数据，因此也叫“SQL数据库”。
常见的关系型数据库管理系统：
- 中小型数据库：Mysql/MariaDB、postgreSQL。（一张表可以保存千万级，勉强亿级）
- 重量级数据库：Oracle、SQLServer、DB2（一张表保存亿级，轻轻松松）
- 微型数据库： Access、SQLlite3（一张表保存几十万的数据就已经超常发挥了）
1.4.3.2 非关系型数据库¶
非关系型数据库（NOSQL，是Not Only SQL的简写）泛指除了关系型数据库以外的所有数据库。例如：Redis、MongoDB、hbase、 Hadoop、elasticsearch、图数据库。因为关系型数据库全部是通过SQL（结构化查询语言）来完成数据管理操作的，所以我们会经常把这一类的数据库称之为SQL数据库，而同样的，非关系型数据库并不使用SQL来完成数据库的操作，所以非关系型数据这一类型，叫NO SQL数据库。
1.5 SQL¶
SQL（Structured Query Language，结构化查询语言），是1974年IBM（深蓝）的开发人员Boyce和Chamberlin提出的一套专为关系型数据库而建立的操作命令集，是一种用于对数据库进行数据操作的特殊编程语言。因为它功能齐全，效率高，简单易学易维护，所以被所有的关系型数据库系统所使用的。SQL与Python这种编程语言不一样，SQL依赖于数据库管理系统而存在，也就是说一般情况下，只有数据库管理系统才能识别SQL代码，并根据SQL代码完成对数据库的操作。
上面的代码就是SQL语句的查询语句，其中大写的单词，就是SQL语句的操作关键代码，也可以叫语句或命令或关键字。它本质上就是一个底层封装了如何操作数据的数学公式的函数名，而跟着在后面的内容，则是函数的参数。
2. MySQL¶
MySQL是一个开源免费的关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一，具有成本低、速度快、体积小且开放源代码等优点。
上图来源：https://db-engines.com/en/ranking
2.1 基本安装¶
此处我们演示的是windows下安装mysql。linux的安装过程在ubuntu封装笔记里面有。
2.1.1 下载¶
打开网址，https://www.mysql.com，点击导航DOWNLOADS
mysql分2个主版本：Enterprise（企业版，收费闭源）和 Community（社区版，免费开源）。此处我们使用免费的社区版。
点击MySQL Community (GPL) Downloads »。
windows选择zip压缩包格式，mac OS选择dmg格式。
不需要注册登陆网站，直接谢谢，继续下载即可。
2.1.2 解压¶
把下载到本地的zip文件手动解压，将解压之后的文件夹放到专门保存开发软件的目录下，这个目录就是mysql的安装目录。
例如，此处我放在了C:/tool/目录下。
注意，目录路径不能出现中文，不能出现空格等特殊符号，否则会出错的！！！
2.1.3 配置¶
什么是配置？主要是为了让软件能适应当前安装的环境（网络环境、使用环境、操作系统等）而进行变量配置或者参数的修改。
2.1.3.1 配置环境变量¶
【此电脑】- 【右键】-【属性】-【高级系统设置】-【环境变量】- 【找到系统变量中的path】-【选中】-【编辑】- 【新建】-【将刚刚mysql压缩包点进去bin目录路径复制并粘贴进来】-【确定】
2.1.3.2 创建data目录¶
主要用于存放mysql数据库以及数据的。
注意：
是mysql的安装目录！！！！
2.1.3.3 创建配置文件¶
mysql在windows下的配置文件，叫 mysql.ini，默认是没有的，我们需要手动创建。
mysql.ini的配置内容，注意，basedir和datadir的路径要根据自己的路径如实填写，如下：
[mysqld]
; 设置3306端口，如果当前电脑安装了多个mysql时，需要错开端口。
port=3306
; 设置mysql的安装目录
basedir="C:/tool/mysql-8.0.28-winx64"
; 设置mysql数据库的数据的存放目录，就是前面手动创建的data目录
datadir="C:/tool/mysql-8.0.28-winx64/data"
; 允许最大连接数
max_connections=200
; 允许连接失败的次数。
max_connect_errors=10
; 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
; 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
; 默认使用“mysql_native_password”插件认证, mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
; 设置mysql网络通信的默认字符集
default-character-set=utf8mb4
[client]
; 设置mysql客户端连接服务端时默认使用的端口
port=3306
; 设置mysql客户端的默认字符集
default-character-set=utf8mb4
2.1.3.4 初始化数据库¶
重新打开一个cmd黑窗口，输入以下命令，让数据库完成初始化操作。
2.1.3.5 注册系统服务¶
把mysql注册到操作系统作为系统服务，保证将来电脑重启了就可以开机自启了。
在上面打开的黑窗口中如下以下命令：
mysql80就是自己取的服务名（服务器是唯一的），只要符合python的变量规则，不要使用中文，可以自己发挥。
确认是否安装到了系统服务，可以通过【此电脑】- 【右键】-【管理】- 【服务与应用程序】 - 【服务】- 【右边窗口】
2.1.4 启动¶
windows下安装的mysql默认是没有启动服务的。
2.1.5 登录¶
通过以下命令按回车键，接着输入上面初始化的登陆密码，就可以登陆MySQL交互终端了。
注意：mysql与linux一样，在安装成功以后默认就存在了一个上帝一般的用户，叫root。
2.1.5.1 修改root登陆密码¶
接下来的操作是在登陆了mysql终端以后的操作。
alter user 'root'@'localhost' identified by '123';
# 'root' 就是要修改密码的用户名
# 'localhost' 表示允许用户在什么地址下可以使用密码登陆到数据库服务器，localhost表示本地登陆
# '123' 就是新的密码了，注意，不要设置空密码！以后公司里面的密码一定要非常难记的才最好。
完成了上面的操作以后，mysql就安装完成了。
2.2 基本概念¶
前面的学习中我们提到，mysql是关系型数据库，所以我们要操作mysql就需要使用SQL（结构化查询语言）。
2.2.1 sql规范¶
1. 在数据库管理系统中，SQL语句关键字不区分大小写(但官方建议用大写) ，参数区分大小写。数据库名、数据表名、字段名统一小写，如数据库名、数据表名、字段名与SQL关键字同名，使用反引号` ` 圈起来，避免冲突。
2. SQL语句可单行或多行书写，默认以英文分号（;）结尾，关键词不能跨多行或简写。主要关键字的前后必须使用英文空格！！
3. 字符串跟日期类型的值都要以（单|双）引号括起来，单词之间需要使用半角的空格隔开。
4. 用空格和缩进来提高SQL语句的可读性。
2.2.1.1 注释¶
SQL语句中支持2种不同的注释，注释的作用是写给开发人员的，数据库管理系统是不识别的。
2.2.2 SQL类型¶
因为SQL语句的底层本质就是对操作数据的数学公式进行封装而得到的函数关键字，那么根据不同底层不同函数的用途，SQL语句通常分3大类型：
- 数据定义语言（Data Definition Language，DDL）
用于创建或删除数据库、数据表、字段的SQL语句，包含以下几种指令：
| SQL关键字 | 描述 |
|---|---|
| create | 创建数据库和数据表等 |
| drop | 删除数据库和数据表等 |
| alter | 修改数据库和表等对象的结构 |
- 数据操作语言（Data Manipulation Language，DML）
用于对数据表中的数据进行增删查改的。
| SQL关键字 | 描述 |
|---|---|
| SELECT | 查询表中的数据 |
| INSERT | 向表中插入新数据 |
| UPDATE | 变更表中的数据 |
| DELETE | 删除表中的数据 |
- 数据控制语言（Data Control Language，DCL）
用于对控制数据库的操作权限的，包括用户权限以及数据操作权限。
| SQL关键字 | 描述 | |
|---|---|---|
| COMMIT | 确认对数据库中的数据进行的变更 | |
| ROLLBACK | 取消对数据库中的数据进行的变更 | |
| GRANT | 赋予用户操作权限 | |
| REMOVE | 取消用户的操作权限 |
扩展：有些人也会把SQL语句主要分为6种：
DDL：数据定义语言，进行数据库、表的管理等，如create、drop
DQL：数据查询语言，用于对数据进行查询，如select
DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete
TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
DCL：数据控制语言，进行授权与权限回收，如grant、revoke
CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor
2.2.3 常用命令¶
mysql的学习官方文档：http://dev.mysql.com/
mysql8.0的中文翻译文档：http://www.deituicms.com/mysql8cn/cn/web.html
mysql 5.1的中文翻译文档：https://www.mysqlzh.com/
| 命令 | 描述 |
|---|---|
| help | 查看系统帮助 |
| status | 查看数据库管理系统的状态信息 |
| exit | 退出数据库终端连接，也可以使用quit |
| \c | 当打错命令了，想换行重新写时可以在错误命令后面跟着\c回车 |
status命令查看mysql运行状态
mysql> status
--------------
mysql Ver 8.0.28 for Win64 on x86_64 (MySQL Community Server - GPL)
Connection id: 15 # mysql是一个单进程多线程的软件，连接ID 是 20
Current database: #当前操作或使用的数据库名，空表示刚登陆mysql终端
Current user: root@localhost # 当前用户账户信息，格式： 用户名@用户登录地址
SSL: Cipher in use is TLS_AES_256_GCM_SHA384 # SSL: 是否开启安全加密的端口连接MySQL，No就是没有加密
Using delimiter: ; # 当前MySQL的断句符号[定界符]，就是要求我们写完一条SQL语句以后，必须以这个符号结尾。
Server version: 8.0.28 MySQL Community Server - GPL # mysql属于基于socket通信实现的C/S架构的系统软件，当前数据库版本
Protocol version: 10 # 端口号版本
Connection: localhost via TCP/IP # socekt连接
Server characterset: utf8mb4 # 数据库管理系统的字符集
Db characterset: utf8mb4 # 数据库的字符集
Client characterset: gbk # 客户端的字符集，windows下使用cmd连接默认会以gbk编码方式
Conn. characterset: gbk # socket通信连接的字符集
TCP port: 3306 # 当前数据库使用的端口号（mysql的默认端口就是3306）
Binary data as: Hexadecimal # 二进制数据查询显示的转换进制（Hexadecimal表示十六进制显示二进制呢绒）
Uptime: 2 days 15 hours 40 min 26 sec # 当前系统已经启动的时间
Threads: 2 Questions: 14 Slow queries: 0 Opens: 130 Flush tables: 3 Open tables: 46 Queries per second avg: 0.000
# Threads: 2 表示当前mysql进程中一共维护了多少个线程
# Questions: 14 mysql数据库启动以来， 一共执行了多少条SQL语句
# Slow queries: 0 在mysql执行所有的SQL语句中是否有慢查询SQL的出现，所谓的满查询SQL指代的就是执行时间耗时很长的性能很低的SQL语句。
# Opens: 130 mysql被打开链接了多少次
# Flush tables: 3 表示mysql启动以来，里面的所有数据表一共被修改的次数多少次
# Open tables: 46 表示mysql启动以来，里面的所有数据表一共被打开查询了多少次
# Queries per second avg: 0.000 平均每秒mysql完成多少次SQL执行，是一个负载参数，是一个很重要的性能指标，接近于0是性能最好的时候，数值越大，性能越差。
--------------
3. 常用操作¶
MySQL中针对数据的操作划分为3个等级：数据库>数据表>数据记录，而在postgreSQL等数据库中，则划分为数据库>模式>数据表>数据记录。
3.1 数据库操作¶
3.1.1 创建数据库¶
在磁盘上创建一个存储数据表的文件夹。开发中，一个软件项目往往就使用一个或多个数据库来保存数据。
注意：mysql中的编码字符集中utf-8，要换成utf8mb4。SQL语句中的中括号部分表示可选。
终端操作，创建一个名为base数据库。
执行效果：
mysql> create database base;
Query OK, 1 row affected (0.01 sec)
-- 在mysql中，每一次执行SQL语句，不管这条SQL语句用于干什么的，对于ymsql而言，都是一次查询（query）
-- Query OK, 表示语句执行过程中，没有任何语法错误，但是并不保证执行结果就是我们所要的。
-- 1 row affected 有一个数据发生了变化、
-- (0.01 sec) 表示mysql执行本次的SQL语句一共耗费了多长时间，单位：秒（sec，second）
SQL语句的执行错误效果：
mysql> create databases base;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databases base' at line 1
-- 上面就是SQL语句的语法错误提示，主要关注的就是 the right syntax to use near 后面单引号部分的内容，里面就是mysql识别到的错误代码部分。
注意：数据库相当于存储数据的一个特殊目录，所以数据名必须是唯一的。不能重复，否则报错！！！
重复数据库名会报错如下：
mysql> create database base;
ERROR 1007 (HY000): Can not create database 'base'; database exists
-- database exists表示数据库已经存在了。
因此，在不确定数据库是否存在的情况下，可以采用if not exists来判断，如果没有创建。
mysql> create database if not exists base;
Query OK, 1 row affected, 1 warning (0.00 sec)
-- Query OK， 就是SQL语句成功执行了，
-- 1 row affected，有一行数据被影响了。明明数据库没有被添加，为什么还有会1行数据记录产生？原因是mysql会在SQL语句执行过程中，记录运行日志的。所以这里是多了一行错误日志。
-- 1 warning，SQL执行过程中，mysql发现了一个运行级别的错误
3.1.2 查看数据库¶
-- 查看所有数据库
show databases;
-- 查看名字中包含base的数据库
show databases like '%base%';
-- 查看指定数据库的建库sql语句
show create database 数据库名;
终端操作：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| base |
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
6 rows in set (0.00 sec)
3.1.3 修改数据库¶
针对数据库的修改操作，mysql提供了修改字符集的操作，该操作一般不使用。所以仅作了解即可。
终端操作
mysql> alter database base character set utf8; -- 设置数据库的编码为utf8，mysql中编码utf-8必须改成utf8
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show create database base; -- 显示base数据库的建库语句
+----------+--------------------------------------------------------------------------------------------------+
| Database | Create Database
|
+----------+--------------------------------------------------------------------------------------------------+
| base | CREATE DATABASE `base` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database base character set utf8mb4; -- 设置数据库编码为utf8mb4
Query OK, 1 row affected (0.00 sec)
mysql> show create database base; -- 显示base数据库的建库语句
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database
|
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| base | CREATE DATABASE `base` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.1.4 删除数据库¶
删除数据库，会把库中所有数据表与表中数据一并删除，使用需谨慎，建议备份数据库内容之后再进行删除。
终端操作：
mysql> drop database base; -- 删除数据库语句
Query OK, 0 rows affected (0.00 sec)
mysql> drop database if exists base; -- 删除时先判断当前数据库是否存在
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop database base; -- 删除一个不存在的数据会报错
ERROR 1008 (HY000): Can not drop database 'base'; database does not exist
3.1.5 使用数据库¶
在开发中，我们创建数据库的目录就是为了保存数据，而保存数据的话，则必须在数据库中对数据表进行操作，因为不同的目录是可以保存同名的文件，同样道理，不同的数据库可以拥有同样表名的文件，那么要对哪一个数据库中的数据表进行操作呢？就需要告诉数据库管理系统，我们要使用哪一个数据库。
终端操作：
-- 因为上面的操作中，我们已经没有新建的数据库了，而剩下的几个数据库是mysql内置的，没有特殊原因，千万不要碰！！！
-- 为了接下来的学习，我们再次创建一个school数据库。也就是，被删除的数据库，可以在后面再次创建一个同名的数据库，但是并非原来的数据库了。
create database school;
use school;
执行效果：
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> use school; -- 高数mysql，我们要使用school数据库
Database changed
mysql> status -- 可以通过status查看，我们当前使用了是哪一个数据库
--------------
C:\tool\mysql-8.0.28-winx64\bin\mysql.exe Ver 8.0.28 for Win64 on x86_64 (MySQL Community Server - GPL)
Connection id: 20
Current database: school
Current user: root@localhost
注意：使用 drop database 命令时要非常谨慎，在执行该命令后，MySQL 不会给出任何提示确认信息。删除数据库后，数据库中存储的所有数据表和数据也将一同被删除，而且不能恢复。因此最好在删除数据库之前先将数据库进行备份。
3.2 数据表操作¶
数据表就相当于存储数据的特殊文件，数据表中的一条记录就相当于普通文件的一行内容。
与普通文件不同的是，数据表是二维的表格结构。
3.2.1 创建数据表¶
数据表就相当于文件，文件有文件名，自然地，数据表也要有表名。同样道理，数据表中的一条记录就相当于文件的一行内容。只是不同的是，数据表需要定义表头（上图中的首行），称为表的字段名。而且因为数据库的存储数据更加科学、严谨，所以需要创建表时要给每一个字段设置数据类型以及字段约束（完整性约束条件）。
create table [if not exists] 表名 (
字段名1 数据类型[ ( 存储空间 ) 字段约束 ],
字段名2 数据类型[ ( 存储空间 ) 字段约束 ],
字段名3 数据类型[ ( 存储空间 ) 字段约束 ],
.....
字段名n 数据类型[ ( 存储空间 ) 字段约束 ],
primary key(一个 或 多个 字段名) -- 注意，最后一段定义语句，不能有英文逗号的出现，否则报错。
) [engine = 存储引擎 character set 字符集];
注意：
- 上面SQL语句中，小括号中的定义字段语句后面必须以英文逗号结尾，而最后一个字段的定义语句不能有英文逗号出现，否则报错。
- 在同一张数据表中，字段名是不能相同，否则报错！
- 创建数据表的SQL语句中，存储空间和字段约束是选填的，而字段名和数据类型则是必须填写的。
创建班级表
-- mysql中创建数据表要以 create table `表名`
-- 表的字段信息必须写在 ( ) 小括号里面
create table classes (
-- 建议一行一个字段，id 就是字段名
-- int 表示设置字段值要以整数的格式保存到硬盘中，
-- auto_increment表示当前字段值在每次新增数据时自动+1作为值保存
-- primary key，mysql中叫主键，表示用于区分一个数据表中不同行的数据的唯一性，同时还具备加快查询速度的作用
-- 注意：auto_increment与primary key 一般是配合使用的，对应的字段名一般也叫id，而且在一个数据表中只有一个字段能使用auto_increment primary key进行设置。
id int auto_increment primary key,
-- 字段名：name
-- varchar(10) 表示当前name这一列可以存储的数据是字符串格式，并且最多只能存10个字符
name varchar(10),
-- 字段名：address
-- varchar(100) 表示adderss这一列可以存储的数据是字符串格式，并且最多只能存100个字符
address varchar(100),
-- 字段名：total
-- int 表示当前total这一列的数据只能是整数，而且一个数据表中，整数的最大范围只能是42亿
total int
);
上面的SQL语句就相当于创建了一个表格。
| id | name | address | total |
|---|---|---|---|
终端操作：
mysql> create table classes (
-> id int auto_increment primary key,
-> name varchar(10),
-> address varchar(100),
-> total int
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> show tables; -- 显示当前数据库school中所有的数据表
+------------------+
| Tables_in_school |
+------------------+
| classes |
+------------------+
1 row in set (0.00 sec)
创建学生表
create table student(
id int auto_increment, -- 字段名：id，数据类型：int整型，auto_increment整数自动增长+1
name varchar(10), -- 字段名：name， 数据类型：varchar字符串（长度限制最多10个字符）
sex int default 1, -- 字段名：sex，数据类型：int整型，默认值（default）：1 相当于True
classes int, -- 字段名：classes， 数据类型：int整型，
age int, -- 字段名：age，数据类型：int整数,
description text, -- 字段名：description，数据类型：text文本
primary key (id) -- 设置主键(id) 每个表必须都有主键，用以区分不同行的数据
);
终端操作：
mysql> create table student ( -- 建表语句
-> id int auto_increment, -- 字段名：id，数据类型：int整型，auto_increment整数自动增长+1
-> name varchar(10), -- 字段名：name， 数据类型：varchar字符串（长度限制最多10个字符）
-> sex int default 1, -- 字段名：sex，数据类型：int整型，默认值（default）：1 相当于True
-> classes int, -- 字段名：classes， 数据类型：int整型，
-> age int, -- 字段名：age，数据类型：int整数,
-> description text, -- 字段名：description，数据类型：text文本
-> primary key (id) -- 设置主键(id) 每个表必须都有主键，用以区分不同行的数据
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> show tables; -- 显示当前数据库下所有的数据表
+------------------+
| Tables_in_school |
+------------------+
| classes |
| student |
+------------------+
2 rows in set (0.00 sec) -- 出现这句话，表示创建表成功
mysql> desc student; -- 查看表结构
+-------------+-------------+------+-----+---------+----------------------------------------+
| Field | Type | Null | Key | Default | Extra |
| 字段名 | 数据类型 | 是否能填None值 | 键 | 默认值 | 额外选项 |
+-------------+-------------+------+-----+---------+-----------------------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| sex | int | YES | | 1 | |
| classes | int | YES | | NULL | |
| age | int | YES | | NULL | |
| description | text | YES | | NULL | |
+-------------+-------------+------+-----+---------+---------------------------+
6 rows in set (0.00 sec)
mysql> show create table student; -- 显示当前数据库中的student数据表的建表语句
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`sex` int DEFAULT '1',
`classes` int DEFAULT NULL,
`age` int DEFAULT NULL,
`description` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
练习：假设现在我们有一个课程表(courses)，里面需要保存课程编号（id），课程名（cource），授课老师（lecturer），教室（address）。
create table courses (
id int auto_increment primary key comment "课程编号",
course varchar(50) comment "课程名称",
lecturer int comment "讲师编号",
address int comment "教室编号"
);
终端操作：
mysql> create table courses (
-> id int auto_increment primary key comment "课程编号",
-> course varchar(50) comment "课程名称",
-> lecturer int comment "讲师编号",
-> address int comment "教室编号"
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc courses; -- 显示表结构
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| course | varchar(50) | YES | | NULL | |
| lecturer | int | YES | | NULL | |
| address | int | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show create table courses; -- 显示建表语句
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| courses | CREATE TABLE `courses` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`course` varchar(50) DEFAULT NULL COMMENT '课程名称',
`lecturer` int DEFAULT NULL COMMENT '讲师编号',
`address` int DEFAULT NULL COMMENT '教室编号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.2.2 命名规范¶
定义字段名，与mysql中数据表名、数据库名，规范基本一样，如下：
- 在64个字符以内，建议简短，如果不够清晰，可以使用下划线前缀来划分，例如：userinfo，useraddress，usercourse，teacher_info，等等，
- 不能是SQL语句的关键字或者保留字，如果不确定名称是否是关键字或保留字，则可以使用反引号来圈住，例如：
age，info- 采用变量命名方式[ 由字母、数字、下划线组成，不能以数字开头 ]
3.2.3 数据类型¶
数据库里面的数据在保存时也要通过指定数据的类型来告诉数据库管理系统，这些数据有什么用途，所以也会有对应的数据类型。数据类型是为了节约内存,提高计算速度，尽量使用存储空间少的类型。常用数据类型有数值类型、字符串类型、时间日期类型、枚举类型。mysql的单表数据表可以支持最多亿级（2000W-5000W是比较合适的）
3.2.3.1 数值类型¶
MySQL中的数值类型提供了整型、浮点型、定点数，与python类似。
对于小数的表示，MYSQL分为两种方式：浮点数（float）和定点数（Decimal）。浮点数包括float(单精度)和double(双精度), 而定点数只有decimal一种，在mysql中底层以字符串的形式存放，比浮点数更精确，适合用来表示货币等精度要求高的数据。
| 分类 | 数据类型 | 存储大小 | 有符号范围（signed） | 无符号范围(unsigned) | 使用场景 |
|---|---|---|---|---|---|
| 整型 | tinyint(m) | 1个字节 | (-128，127) | (0，255) | 年龄，分类的变好 |
| 整型 | smallint(m) | 2个字节 | (-32 768，32 767) | (0，65 535) | 商品分类编号，员工编号， |
| 整型 | mediumint(m) | 3个字节 | (-8388608~8388607) | (0，16 777 215) | 小的数据表的主键id |
| 整型 | int(m) | 4个字节 | (-2147483648~2147483647) | (0，4 294 967 295) | 一般数据表的主键id |
| 整型 | bigint(m) | 8个字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 超大表的主键id |
| 浮点型 | float(m,d) | 8位精度，4个字节 | 单精度，近似值的小数 m总个数，d小数位 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) |
0，(1.175 494 351 E-38，3.402 823 466 E+38) | 数值类型的时间戳，带小数的经纬度 |
| 浮点型 | double(m,d) | 16位精度，8个字节 | 双精度，近似值的小数 m总个数，d小数位 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) |
0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | |
| 定点数 | decimal(m,d) | 精确值，精确值的小数 m总个数，d小数位 依赖于m和d的值 |
依赖于m和d的值 | 货币，积分 | |
| 二进制位 | bit(m) | 1个字节 | 依赖于m的值 |
依赖于m的值 | 签到二进制记录，布隆过滤器。 |
整型：
-- 说明：在数据表中，建议加上主键id，但是如果有特殊要求，也可以不加
-- tinyint类型
drop table if exists test1;
create table test1 (
id int auto_increment primary key,
age tinyint unsigned,
score tinyint,
number smallint,
chinese_achievement float(4,1), -- 当前小数中所有数字的数量最多是3个数字，其中小数位最多1个数字
math_achievement double(4,1), --
english_achievement decimal(4,1)
);
-- 以下是测试代码，后面学习到
insert into test1 (age, score, number, chinese_achievement, math_achievement, english_achievement)
values (20, 100, 30000, 60.5, 93.5, 100);
终端操作：
mysql> drop table if exists test1; -- 删除表，如果表存在的话
Query OK, 0 rows affected (0.01 sec)
mysql> create table test1 ( -- 建表语句
-> id int auto_increment primary key,
-> age tinyint unsigned,
-> score tinyint,
-> number smallint,
-> chinese_achievement float(4,1), -- 当前小数中所有数字的数量最多是3个数字，其中小数位最多1个数字
-> math_achievement double(4,1), --
-> english_achievement decimal(4,1)
-> );
Query OK, 0 rows affected, 2 warnings (0.02 sec)
-- 添加一条数据到数据表test1中
mysql> insert into test1 (age, score, number, chinese_achievement, math_achievement, english_achievement)
-> values (20, 100, 30000, 60.5, 93.5, 100);
Query OK, 1 row affected (0.01 sec)
-- 查看数据表test1中的所有数据
mysql> select * from test1;
+----+------+-------+--------+---------------------+------------------+---------------------+
| id | age | score | number | chinese_achievement | math_achievement | english_achievement |
+----+------+-------+--------+---------------------+------------------+---------------------+
| 1 | 20 | 100 | 30000 | 60.5 | 93.5 | 100.0 |
+----+------+-------+--------+---------------------+------------------+---------------------+
3.2.3.2 字符串类型¶
MySQL中针对文本内容的存储类型提供了字符串与文本两种格式，其中按存储方式不同，又分为固定长度（定长）与可变长度（变长）两种，按存储格式不同，又分为普通字符格式与二进制格式两种。
SQL 语句中（单|双）引号都能表示字符串或文本。
| 数据类型(n指定存储的长度上限) | 大小 | 描述 | 应用场景 |
|---|---|---|---|
| char(n) | 0-255字符 | 定长字符串 | 姓名，验证码 |
| varchar(n) | 0-65535字符 | 变长字符串 | 账号，密码，文章标题，商品标题 |
| binary(n) | 0-255字符 | 定长二进制字符串 | |
| varbinary(n) | 0-65535字符 | 变长二进制字符串 | |
| tinytext | 0-255字符 | 可变长度文本 | |
| text | 0-65 535字符 | 可变长度文本 | 文章内容， |
| mediumtext | 0-16 777 215字符 | 可变长度文本 | |
| longtext | 0-4 294 967 295字符 | 可变长度文本 | |
| tinyblob | 0-255字符 | 可变二进制文本 | |
| blob | 0-65 535字符 | 可变二进制文本 | 小图标、二进制的认证信息 |
| mediumblob | 0-16 777 215字符 | 可变二进制文本 | |
| longblob | 0-4 294 967 295字符 | 可变二进制文本 | |
| json | 0-4 294 967 295字符 | 可变二进制json文本 (也叫bson: binary json) | 主要实现一些NoSQL数据的存储 |
char与varchar的区别：
1.char(n) 若存入字符数小于n，则以空格补于其后，SQL语句查询时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。 2.char(4)不管是存入几个字符，都将占用4个字符空间，varchar是存入的实际字符数+1个结束符号（n<=255）或2个字节(n>255)，所以varchar(4), 存入3个字符将占用4个字符空间。 3.因为char类型是固定长度存储空间的，所以在数据库查询速度要比varchar类型的快，因为varchar是动态空间分配，所以每次查找的是否要判断结束符的位置。
varchar字符串和text文本的区别：
- varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个结束符号（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
- text类型不能有默认值，注意json也不能有默认值。
- varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text。
索引：index，主要为了加快查询数据的数据的一种技术，类似书籍的目录。
create table test2 (
id int auto_increment primary key,
name char(15),
username varchar(15),
password varchar(250),
description text,
avatar blob,
info json
);
终端操作：
-- 创建表语句
mysql> create table test2 (
-> id int auto_increment primary key,
-> name char(15),
-> username varchar(15),
-> password varchar(250),
-> description text,
-> avatar blob,
-> info json
-> );
Query OK, 0 rows affected (0.02 sec)
-- 添加数据
insert into test2 (name ,username, password, description, avatar, info)
values
("李磊", "lilei202209", sha1("123456"), "很长的一段自我介绍....", "abc", '{"address": "北京市"}');
-- 查看数据
mysql> select * from test2;
+----+------+-------------+------------------------------------------+------------------------+----------------+-----------------------+
| id | name | username | password | description | avatar | info |
+----+------+-------------+------------------------------------------+------------------------+----------------+-----------------------+
| 1 | 李磊 | lilei202209 | 7c4a8d09ca3762af61e59520943dc26494f8941b | 很长的一段自我介绍.... | 0x616263 | {"address": "北京市"} |
+----+------+-------------+------------------------------------------+------------------------+----------------+-----------------------+
1 row in set (0.00 sec)
3.2.3.3 日期类型¶
表示时间值的日期和时间类型为 DATETIME、DATE、TIMESTAMP、TIME 和 YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的 MySQL 不能表示的值时使用"零"值。
| 数据类型 | 取值范围 | 日期格式 | 零值 | 使用场景 |
|---|---|---|---|---|
| year | 1901~2155 | YYYY | 0000 | 电影年份，图书年份 |
| date | 1000-01-01~9999-12-31 | YYYY-MM-DD | 0000-00-00 | 生日 |
| time | -838:59:59~838:59:59 | HH:MM:SS | 00:00:00 | 餐牌时间，会议时间 |
| datetime | 1000-01-01 00:00:00~9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 0000-00-00 00:00:00 | 添加时间，更新时间，删除时间，登陆时间 |
| timestamp | 1970-01-01 00:00:01~2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 0000-00-00 00:00:00 | 添加时间，更新时间，删除时间，登陆时间 |
create table user_info (
id int auto_increment primary key comment "主键ID",
nickname varchar(50) comment "昵称",
username varchar(32) comment "登陆账户",
password varchar(500) comment "登录密码",
birthday date comment "出生日期",
created_time timestamp default current_timestamp() comment "创建时间",
updated_time timestamp comment "更新时间",
deleted_time timestamp default null comment "虚拟删除时间"
);
-- 添加数据给数据表
insert into user_info (nickname, username, password, birthday)
values
("小灰灰", "root", sha1("123456"), "2000-10-21");
-- 查询数据
select * from user_info;
-- 更新数据
update user_info set nickname="大灰狼", updated_time=current_timestamp() where id = 1;
-- 删除数据[虚拟删除]
update user_info set deleted_time=current_timestamp() where id = 1;
-- 查看数据[加上一个查询条件，通过where指定不要查询deleted_time有值的]
select * from user_info where deleted_time = null;
mysql的空叫null，python的空叫None。
终端操作：
mysql> use school -- 使用school数据库
Database changed
mysql> show tables; -- 显示数据库下所有的数据表
+------------------+
| Tables_in_school |
+------------------+
| classes |
| courses |
| student |
| test1 |
| test2 |
+------------------+
5 rows in set (0.05 sec)
-- 建表语句
mysql> create table user_info (
-> id int auto_increment primary key comment "主键ID",
-> nickname varchar(50) comment "昵称",
-> username varchar(32) comment "登陆账户",
-> password varchar(500) comment "登录密码",
-> birthday date comment "出生日期",
-> created_time timestamp default current_timestamp() comment "创建时间",
-> updated_time timestamp comment "更新时间",
-> deleted_time timestamp default null comment "虚拟删除时间"
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> desc user_info; -- 显示表结构
+--------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | auto_increment |
| nickname | varchar(50) | YES | | NULL | |
| username | varchar(32) | YES | | NULL | |
| password | varchar(500) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| created_time | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_time | timestamp | YES | | NULL | |
| deleted_time | timestamp | YES | | NULL | |
+--------------+--------------+------+-----+-------------------+-------------------+
8 rows in set (0.02 sec)
-- 添加数据给数据表
mysql> insert into user_info (nickname, username, password, birthday)
-> values
-> ("小灰灰", "root", sha1("123456"), "2000-10-21");
Query OK, 1 row affected (0.01 sec)
-- 查看表中所有的数据内容
mysql> select * from user_info;
+----+----------+----------+------------------------------------------+------------+---------------------+--------------+--------------+
| id | nickname | username | password | birthday | created_time | updated_time | deleted_time |
+----+----------+----------+------------------------------------------+------------+---------------------+--------------+--------------+
| 1 | 小灰灰 | root | 7c4a8d09ca3762af61e59520943dc26494f8941b | 2000-10-21 | 2022-05-31 08:56:31 | NULL
| NULL |
+----+----------+----------+------------------------------------------+------------+---------------------+--------------+--------------+
1 row in set (0.00 sec)
-- 更新数据的时候，顺便更新下更新时间
mysql> update user_info set nickname="大灰狼", updated_time=current_timestamp() where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 查询更新数据后的信息
mysql> select * from user_info;
+----+----------+----------+------------------------------------------+------------+---------------------+---------------------+--------------+
| id | nickname | username | password | birthday | created_time | updated_time | deleted_time |
+----+----------+----------+------------------------------------------+------------+---------------------+---------------------+--------------+
| 1 | 大灰狼 | root | 7c4a8d09ca3762af61e59520943dc26494f8941b | 2000-10-21 | 2022-05-31 08:56:31 | 2022-05-31 08:58:57 | NULL |
+----+----------+----------+------------------------------------------+------------+---------------------+---------------------+--------------+
1 row in set (0.00 sec)
-- 删除数据[逻辑删除, 虚拟删除]
mysql> update user_info set deleted_time=current_timestamp() where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user_info;
+----+----------+----------+------------------------------------------+------------+---------------------+---------------------+---------------------+
| id | nickname | username | password | birthday | created_time | updated_time | deleted_time |
+----+----------+----------+------------------------------------------+------------+---------------------+---------------------+---------------------+
| 1 | 大灰狼 | root | 7c4a8d09ca3762af61e59520943dc26494f8941b | 2000-10-21 | 2022-05-31 08:56:31 | 2022-05-31 08:58:57 | 2022-05-31 09:00:08 |
+----+----------+----------+------------------------------------------+------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
-- 查询数据时，使用where指定不要查询哪些已经被虚拟删除的数据
mysql> select * from user_info where deleted_time = null;
Empty set (0.00 sec)
3.2.3.4 枚举与集合¶
enum中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。ENUM只允许从值集合中选取单个值，而不能一次取多个值。SET和ENUM非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。
| 类型 | 大小 (字节) | 用途 |
|---|---|---|
| enum | 对1-255个成员的枚举需要1个字节存储; 对于255-65535个成员，需要2个字节存储; 最多允许65535个成员。 |
单选：选择性别，现居地城市 |
| set | 1-8个成员的集合，占1个字节 9-16个成员的集合，占2个字节 17-24个成员的集合，占3个字节 25-32个成员的集合，占4个字节 33-64个成员的集合，占8个字节 |
多选：兴趣、爱好、标签 |
create table users (
id int auto_increment primary key comment "主键ID",
nickname varchar(50) comment "昵称",
username varchar(32) comment "登陆账户",
password varchar(500) comment "登录密码",
birthday date comment "出生日期",
education enum("小学","初中", "高中", "中专", "大专", "本科") comment "学历",
hobby set("game", "code", "shopping", "swim", "play ball"),
created_time timestamp default current_timestamp() comment "创建时间",
updated_time timestamp comment "更新时间",
deleted_time timestamp default null comment "虚拟删除时间"
);
desc users;
-- 添加数据
insert into users (nickname, username, password, birthday, education, hobby)
values
("小灰灰", "root", sha1("123456"), "2000-10-21", "小学", "game");
insert into users (nickname, username, password, birthday, education, hobby)
values
("墨落", "moluo", sha1("123456"), "2018-01-21", "初中", 'game,code,shopping');
-- 查看数据
select * from users;
终端操作：
-- 创建表
mysql> create table users (
-> id int auto_increment primary key comment "主键ID",
-> nickname varchar(50) comment "昵称",
-> username varchar(32) comment "登陆账户",
-> password varchar(500) comment "登录密码",
-> birthday date comment "出生日期",
-> education enum("小学","初中", "高中", "中专", "大专", "本科") comment "学历",
-> hobby set("game", "code", "shopping", "swim", "play ball"),
-> created_time timestamp default current_timestamp() comment "创建时间",
-> updated_time timestamp comment "更新时间",
-> deleted_time timestamp default null comment "虚拟删除时间"
-> );
Query OK, 0 rows affected (0.03 sec)
-- 查看表结构
mysql> desc users;
+--------------+--------------------------------------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------------------------------------------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | auto_increment |
| nickname | varchar(50) | YES | | NULL | |
| username | varchar(32) | YES | | NULL | |
| password | varchar(500) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| education | enum('小学','初中','高中','中专','大专','本科') | YES | | NULL | |
| hobby | set('game','code','shopping','swim','play ball') | YES | | NULL | |
| created_time | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_time | timestamp | YES | | NULL | |
| deleted_time | timestamp | YES | | NULL | |
+--------------+--------------------------------------------------+------+-----+-------------------+-------------------+
10 rows in set (0.00 sec)
-- 添加数据
mysql> insert into users (nickname, username, password, birthday, education, hobby)
-> values
-> ("小灰灰", "root", sha1("123456"), "2000-10-21", "小学", "game");
Query OK, 1 row affected (0.00 sec)
-- 查看数据
mysql> select * from users;
+----+----------+----------+------------------------------------------+------------+-----------+-------+---------------------+--------------+--------------+
| id | nickname | username | password | birthday | education | hobby | created_time | updated_time | deleted_time |
+----+----------+----------+------------------------------------------+------------+-----------+-------+---------------------+--------------+--------------+
| 1 | 小灰灰 | root | 7c4a8d09ca3762af61e59520943dc26494f8941b | 2000-10-21 | 小学 | game | 2022-05-31 09:18:05 | NULL | NULL |
+----+----------+----------+------------------------------------------+------------+-----------+-------+---------------------+--------------+--------------+
1 row in set (0.00 sec)
-- 再次添加数据
mysql> insert into users (nickname, username, password, birthday, education, hobby)
-> values
-> ("墨落", "moluo", sha1("123456"), "2018-01-21", "初中", 'game,code,shopping');
Query OK, 1 row affected (0.00 sec)
-- 再次查看数据
mysql> select * from users;
+----+----------+----------+------------------------------------------+------------+-----------+--------------------+---------------------+--------------+--------------+
| id | nickname | username | password | birthday | education | hobby | created_time | updated_time | deleted_time |
+----+----------+----------+------------------------------------------+------------+-----------+--------------------+---------------------+--------------+--------------+
| 1 | 小灰灰 | root | 7c4a8d09ca3762af61e59520943dc26494f8941b | 2000-10-21 | 小学 | game | 2022-05-31 09:18:05 | NULL
| NULL |
| 2 | 墨落 | moluo | 7c4a8d09ca3762af61e59520943dc26494f8941b | 2018-01-21 | 初中 | game,code,shopping | 2022-05-31 09:24:19 | NULL
| NULL |
+----+----------+----------+------------------------------------------+------------+-----------+--------------------+---------------------+--------------+--------------+
2 rows in set (0.00 sec)
3.2.4 字段约束¶
也叫完整性约束条件，主要是为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
| 约束类型 | SQL关键字 | 语法 | 描述 |
|---|---|---|---|
| 填充 | zerofill | 字段名 整型 zerofill | 为数据表中的整型字段设置数值左边补0 |
| 无符号 | unsigned | 字段名 数据类型 unsigned | 为数据表中的数值类型字段设置数值指定不能小于0，可以让字段值的取值范围，在正数范围内增加1倍。 |
| 默认值 | default | 字段名 数据类型 default 默认值 | 为数据表中的字段指定默认值。但blob、text与json类型不支持default。 |
| 非空 | not null | 字段名 数据类型 not null | 非空字段指字段的值不能为NULL。 |
| 唯一索引 | unique | 列级约束 字段名 数据类型 unique 表级约束 unique (字段名 1，字段名 2…) |
用于保证数据表中字段的不同行的值唯一性，即表中字段的值不能重复出现在多行。 列级约束定义在一个列上，只对该列起约束作用； 表级约束是独立于列的定义，可以应用在一个表的多个列上。 |
| 主键索引 | primary key | 列级约束 字段名 数据类型 primary key 表级约束 primary key(字段名 1,字段名2…) |
一个表中只能有一个主键。可以指定单个字段为单列主键，也可以指定多个字段为联合主键。 |
| 自动增长 | auto_increment | 字段名 数据类型 auto_increment | 一个表中只能有一个自动增长的字段，该字段类型是整数类型，一般用于设置主键。 自动增长值从1开始自增，每次加1。 |
| 索引 | index / key | index / key (字段名) | 给对应的字段的值设置添加索引（目的让当前字段的值在被删除，修改，查询时，加快执行速度） |
| 外键索引 | foreign key | constraint 外键名 foreign key 字段名 [，字段名2，…] references <主表名> 主键列1 [，主键列2，…] | 用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。 |
练习，动手创建一个商品表。
create table goods_info(
id int unsigned not null auto_increment comment "主键ID",
goods_number int zerofill unsigned not null comment "商品进货号",
title varchar(100) not null comment "商品标题",
company varchar(100) not null comment "商品厂商",
description text null comment "商品描述",
country varchar(50) default "中国" comment "产地",
unique (title, company),
index (title),
index (country),
primary key (id, goods_number)
);
-- 添加一条数据
insert into goods_info (goods_number, title, company, description)
values
(1, "小羊牌沐浴露", "小羊生活科技", "很好用的沐浴露~~~");
-- 一次性添加多条数据
insert into goods_info (goods_number, title, company, description)
values
(2, "小羊牌洗发水", "小羊生活科技", "很好用的洗发水~~~"),
(3, "小羊牌洗面奶", "小羊生活科技", "很好用的洗面奶~~~"),
(4, "大羊牌沐浴露", "大羊生活科技", "很好用的沐浴露~~~"),
(5, "大羊牌洗面奶", "大羊生活科技", "很好用的洗面奶~~~");
-- 查看数据
select * from goods_info;
终端操作：
-- 建表语句
mysql> create table `goods_info`(
-> id int unsigned not null auto_increment comment "主键ID",
-> goods_number int zerofill unsigned not null comment "商品进货号",
-> title varchar(100) not null comment "商品标题",
-> company varchar(100) not null comment "商品厂商",
-> description text null comment "商品描述",
-> country varchar(50) default "中国" comment "产地",
-> unique (title, company),
-> index (title),
-> index (country),
-> primary key (id, goods_number)
-> );
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> desc goods_info;
+--------------+---------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| goods_number | int(10) unsigned zerofill | NO | PRI | NULL | |
| title | varchar(100) | NO | MUL | NULL | |
| company | varchar(100) | NO | | NULL | |
| description | text | YES | | NULL | |
| country | varchar(50) | YES | MUL | 中国 | |
+--------------+---------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
-- 添加一条数据
mysql> insert into goods_info (goods_number, title, company, description)
-> values
-> (1, "小羊牌沐浴露", "小羊生活科技", "很好用的沐浴露~~~");
Query OK, 1 row affected (0.00 sec)
-- 一次性添加多条数据
mysql> insert into goods_info (goods_number, title, company, description)
-> values
-> (2, "小羊牌洗发水", "小羊生活科技", "很好用的洗发水~~~"),
-> (3, "小羊牌洗面奶", "小羊生活科技", "很好用的洗面奶~~~"),
-> (4, "大羊牌沐浴露", "大羊生活科技", "很好用的沐浴露~~~"),
-> (5, "大羊牌洗面奶", "大羊生活科技", "很好用的洗面奶~~~");
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
-- 查看数据
mysql> select * from goods_info;
+----+--------------+--------------+--------------+-------------------+---------+
| id | goods_number | title | company | description | country |
+----+--------------+--------------+--------------+-------------------+---------+
| 1 | 0000000001 | 小羊牌沐浴露 | 小羊生活科技 | 很好用的沐浴露~~~ | 中国 |
| 2 | 0000000002 | 小羊牌洗发水 | 小羊生活科技 | 很好用的洗发水~~~ | 中国 |
| 3 | 0000000003 | 小羊牌洗面奶 | 小羊生活科技 | 很好用的洗面奶~~~ | 中国 |
| 4 | 0000000004 | 大羊牌沐浴露 | 大羊生活科技 | 很好用的沐浴露~~~ | 中国 |
| 5 | 0000000005 | 大羊牌洗面奶 | 大羊生活科技 | 很好用的洗面奶~~~ | 中国 |
+----+--------------+--------------+--------------+-------------------+---------+
5 rows in set (0.00 sec)
3.2.5 查看数据表¶
3.2.5.1 查看所有数据表¶
列出当前数据库中所有的数据表,语法：
终端操作：
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| classes |
| courses |
| goods_info |
| student |
| test1 |
| test2 |
| user_info |
| users |
+------------------+
8 rows in set (0.00 sec)
3.2.5.2 查看表结构¶
以表格形式列出当前数据表的字段结构信息。常用的是desc，语法：
3.2.5.3 查看建表语句¶
效果：
mysql> show create table goods_info;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| goods_info | CREATE TABLE `goods_info` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`goods_number` int(10) unsigned zerofill NOT NULL COMMENT '商品进货号',
`title` varchar(100) NOT NULL COMMENT '商品标题',
`company` varchar(100) NOT NULL COMMENT '商品厂商',
`description` text COMMENT '商品描述',
`country` varchar(50) DEFAULT '中国' COMMENT '产地',
PRIMARY KEY (`id`,`goods_number`),
UNIQUE KEY `title` (`title`,`company`),
KEY `title_2` (`title`),
KEY `country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.2.6 删除数据表¶
删除表结构，并把数据一并删除，因为删除表操作没有确认么，所以使用需谨慎，强烈建议先备份后删除，或者直接改表名来代替删除操作。
终端操作：
-- 显示当前数据库下所有的数据表
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| classes |
| courses |
| goods_info |
| student |
| user_info |
| users |
+------------------+
6 rows in set (0.00 sec)
-- 删除usres数据表
mysql> drop table users;
Query OK, 0 rows affected (0.02 sec)
-- 显示所有数据表
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| classes |
| courses |
| goods_info |
| student |
| user_info |
+------------------+
5 rows in set (0.00 sec)
-- 判断表存在了，再删除
mysql> drop table if exists test2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
3.2.6.1 重置表信息¶
保留数据表结构，但是把数据表存储的数据清空以及数据表的状态清零，相当于删除原表，并新建一张一模一样的空数据表。
终端操作：
-- 一次性添加多条数据
insert into goods_info (goods_number, title, company, description)
values
(1, "小羊牌沐浴露", "小羊生活科技", "很好用的沐浴露~~~"),
(2, "小羊牌洗发水", "小羊生活科技", "很好用的洗发水~~~"),
(3, "小羊牌洗面奶", "小羊生活科技", "很好用的洗面奶~~~"),
(4, "大羊牌沐浴露", "大羊生活科技", "很好用的沐浴露~~~"),
(5, "大羊牌洗面奶", "大羊生活科技", "很好用的洗面奶~~~");
-- 查看数据
select * from goods_info;
-- 添加了数据以后的建表信息中，会附带一个AUTO_INCREMENT状态，记录自动增加的计算值
show create table goods_info;
-- 如果直接通过delete删除数据表所有数据的方式，实际上不能让表中的状态清零的
delete from goods_info;
-- 查看数据
select * from goods_info;
-- 查看表的建表语句
show create table goods_info;
-- 重置表所有信息
truncate table goods_info;
-- 查看数据
select * from goods_info;
-- 查看表的建表语句
show create table goods_info;
终端操作：
-- 一次性添加多条数据
mysql> insert into goods_info (goods_number, title, company, description)
-> values
-> (1, "小羊牌沐浴露", "小羊生活科技", "很好用的沐浴露~~~"),
-> (2, "小羊牌洗发水", "小羊生活科技", "很好用的洗发水~~~"),
-> (3, "小羊牌洗面奶", "小羊生活科技", "很好用的洗面奶~~~"),
-> (4, "大羊牌沐浴露", "大羊生活科技", "很好用的沐浴露~~~"),
-> (5, "大羊牌洗面奶", "大羊生活科技", "很好用的洗面奶~~~");
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
-- 查看数据
mysql> select * from goods_info;
+----+--------------+--------------+--------------+-------------------+---------+
| id | goods_number | title | company | description | country |
+----+--------------+--------------+--------------+-------------------+---------+
| 1 | 0000000001 | 小羊牌沐浴露 | 小羊生活科技 | 很好用的沐浴露~~~ | 中国 |
| 2 | 0000000002 | 小羊牌洗发水 | 小羊生活科技 | 很好用的洗发水~~~ | 中国 |
| 3 | 0000000003 | 小羊牌洗面奶 | 小羊生活科技 | 很好用的洗面奶~~~ | 中国 |
| 4 | 0000000004 | 大羊牌沐浴露 | 大羊生活科技 | 很好用的沐浴露~~~ | 中国 |
| 5 | 0000000005 | 大羊牌洗面奶 | 大羊生活科技 | 很好用的洗面奶~~~ | 中国 |
+----+--------------+--------------+--------------+-------------------+---------+
5 rows in set (0.00 sec)
-- 添加了数据以后的建表信息中，会附带一个AUTO_INCREMENT状态，记录自动增加的计算值
mysql> show create table goods_info;
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| goods_info | CREATE TABLE `goods_info` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`goods_number` int(10) unsigned zerofill NOT NULL COMMENT '商品进货号',
`title` varchar(100) NOT NULL COMMENT '商品标题',
`company` varchar(100) NOT NULL COMMENT '商品厂商',
`description` text COMMENT '商品描述',
`country` varchar(50) DEFAULT '中国' COMMENT '产地',
PRIMARY KEY (`id`,`goods_number`),
UNIQUE KEY `title` (`title`,`company`),
KEY `title_2` (`title`),
KEY `country` (`country`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- 如果直接通过delete删除数据表所有数据的方式，实际上不能让表中的状态清零的
mysql> delete from goods_info;
Query OK, 5 rows affected (0.00 sec)
mysql>
mysql> -- 查看数据
mysql> select * from goods_info;
Empty set (0.00 sec)
mysql>
mysql> -- 查看表的建表语句
mysql> show create table goods_info;
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| goods_info | CREATE TABLE `goods_info` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`goods_number` int(10) unsigned zerofill NOT NULL COMMENT '商品进货号',
`title` varchar(100) NOT NULL COMMENT '商品标题',
`company` varchar(100) NOT NULL COMMENT '商品厂商',
`description` text COMMENT '商品描述',
`country` varchar(50) DEFAULT '中国' COMMENT '产地',
PRIMARY KEY (`id`,`goods_number`),
UNIQUE KEY `title` (`title`,`company`),
KEY `title_2` (`title`),
KEY `country` (`country`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- 重置表所有信息
mysql> truncate table goods_info;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> -- 查看数据
mysql> select * from goods_info;
Empty set (0.00 sec)
mysql>
mysql> -- 查看表的建表语句
mysql> show create table goods_info;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| goods_info | CREATE TABLE `goods_info` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`goods_number` int(10) unsigned zerofill NOT NULL COMMENT '商品进货号',
`title` varchar(100) NOT NULL COMMENT '商品标题',
`company` varchar(100) NOT NULL COMMENT '商品厂商',
`description` text COMMENT '商品描述',
`country` varchar(50) DEFAULT '中国' COMMENT '产地',
PRIMARY KEY (`id`,`goods_number`),
UNIQUE KEY `title` (`title`,`company`),
KEY `title_2` (`title`),
KEY `country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
delete与truncate的区别：
两个命令都会把数据清空，但是delete会删除数据，保留表的历史状态，而truncate会清空数据并清楚表的历史状态。
3.2.7 修改数据表¶
针对数据表的修改操作，MySQL提供了数据表重命名，增加字段，删除字段，修改字段信息等操作。
3.2.7.1 数据表重命名¶
终端操作：
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| classes |
| courses |
| goods_info |
| student |
| user_info |
+------------------+
5 rows in set (0.00 sec)
mysql> alter table student rename student_info;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| classes |
| courses |
| goods_info |
| student_info |
| user_info |
+------------------+
5 rows in set (0.00 sec)
3.2.7.2 增加字段¶
开发中，根据一开始的需求，设计的数据表，有可能随着业务的发展，时空的变化，可能会对数据表中的字段进行增加。
-- 添加一个字段
alter table 表名
add 字段名1 数据类型 [完整性约束条件…];
-- 一次性添加多个字段
alter table 表名
add 字段名1 数据类型 [完整性约束条件…],
add 字段名2 数据类型 [完整性约束条件…],
...
add 字段名n 数据类型 [完整性约束条件…];
-- 在指定位置添加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST; -- 在第一个字段之前，新增字段（新的字段就变成了表的第一个字段）
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; -- 在指定字段之后新增字段
create table test1 (
id int unsigned NOT NULL AUTO_INCREMENT primary key COMMENT '主键ID'
);
desc test1;
-- 新增字段
alter table test1 add title varchar(100) NOT NULL COMMENT '商品标题';
desc test1;
-- 新增多个字段
alter table test1
add company varchar(100) NOT NULL COMMENT '商品厂商',
add description text COMMENT '商品描述';
-- 新增字段作为表的第一个字段存在
alter table test1 add number int(10) unsigned zerofill NOT NULL COMMENT '商品进货号' first;
-- 在指定位置之后添加字段
alter table test1 add country varchar(50) DEFAULT '中国' COMMENT '产地' after company;
终端操作：
-- 新建数据表
mysql> create table test1 (
-> id int unsigned NOT NULL AUTO_INCREMENT primary key COMMENT '主键ID'
-> );
Query OK, 0 rows affected (0.03 sec)
-- 查看表结构
mysql> desc test1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
+-------+--------------+------+-----+---------+----------------+
1 row in set (0.00 sec)
-- 新增一个字段
mysql> alter table test1 add title varchar(100) NOT NULL COMMENT '商品标题';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看表结构
mysql> desc test1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
-- 新增多个字段
mysql> alter table test1
-> add company varchar(100) NOT NULL COMMENT '商品厂商',
-> add description text COMMENT '商品描述';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 新增字段作为表的第一个字段
mysql> alter table test1 add number int(10) unsigned zerofill NOT NULL COMMENT '商品进货号' first;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 2
-- 查看表结构
mysql> desc test1;
+-------------+---------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------------+------+-----+---------+----------------+
| number | int(10) unsigned zerofill | NO | | NULL | |
| id | int unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| company | varchar(100) | NO | | NULL | |
| description | text | YES | | NULL | |
+-------------+---------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
-- 在指定字段的后面新增字段
mysql> alter table test1 add country varchar(50) DEFAULT '中国' COMMENT '产地' after company;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看表结构
mysql> desc test1;
+-------------+---------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------------+------+-----+---------+----------------+
| number | int(10) unsigned zerofill | NO | | NULL | |
| id | int unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| company | varchar(100) | NO | | NULL | |
| country | varchar(50) | YES | | 中国 | |
| description | text | YES | | NULL | |
+-------------+---------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
3.2.7.3 删除字段¶
在开发中，如果表中有数据了，一般我们强烈反对删除字段的！！！因为这个操作会导致对应字段的值也被删除，因此如果非要删除字段，则务必保证已经备份了数据表。
终端操作：
create table test2(
id int unsigned NOT NULL AUTO_INCREMENT primary key COMMENT '主键ID',
name varchar(50) comment "用户名",
age tinyint comment "年龄",
sex enum("男", "女", "保密")
);
insert into test2 (name, age, sex) values ("小明", 17, "男"), ("小白", 18, "女");
select * from test2;
alter table test2 drop sex;
select * from test2;
操作效果：
-- 新建表
mysql> create table test2(
-> id int unsigned NOT NULL AUTO_INCREMENT primary key COMMENT '主键ID',
-> name varchar(50) comment "用户名",
-> age tinyint comment "年龄",
-> sex enum("男", "女", "保密")
-> );
Query OK, 0 rows affected (0.02 sec)
-- 添加多条数据
mysql> insert into test2 (name, age, sex) values ("小明", 17, "男"), ("小白", 18, "女");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
-- 查看表数据
mysql> select * from test2;
+----+------+------+------+
| id | name | age | sex |
+----+------+------+------+
| 1 | 小明 | 17 | 男 |
| 2 | 小白 | 18 | 女 |
+----+------+------+------+
2 rows in set (0.00 sec)
-- 删除sex字段
mysql> alter table test2 drop sex;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看数据
mysql> select * from test2;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | 小明 | 17 |
| 2 | 小白 | 18 |
+----+------+------+
2 rows in set (0.00 sec)
3.2.7.4 修改字段¶
-- 方式1: 不提供的字段名修改(针对原字段直接覆盖性修改)
alter table 表名
modify 字段名 数据类型 [完整性约束条件…];
-- 方式2: 提供字段名的修改(相当于删除原字段， 新增一个字段进行修改，保留原字段的数据类型)
alter table 表名
change 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
-- 方式3：提供字段名的修改(相当于删除原字段，不保留源字段的数据类型)
alter table 表名
change 旧字段名 新字段名 新数据类型 [完整性约束条件…];
-- 方式4：修改字段排列顺序/在增加的时候指定字段位置
ALTER TABLE 表名
CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
演示操作，SQL语句：
-- 新建表
create table test3 (
id int unique,
name char(10) not null
);
-- 去掉name字段的 no null约束，改成null
alter table test3 modify name char(10) null;
-- 添加null约束
alter table test3 modify name char(10) not null;
-- 去掉字段id的unique约束
-- 注意：删除唯一索引unique或者普通索引index，都需要使用drop index 字段来删除
alter table test3 drop index id;
-- 添加unique约束
alter table test3 modify id int unique;
-- 给数据添加联合唯一约束
alter table test3 add unique index(id, name);
操作效果：
-- 创建表
mysql> create table test3 (id int unique, name char(10) not null);
Query OK, 0 rows affected (0.03 sec)
-- 查看表结构
mysql> desc test3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | char(10) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 修改name字段的no null 为 null
mysql> alter table test3 modify name char(10) null;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看表结构
mysql> desc test3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 把name字段的nul 改成 no null
mysql> alter table test3 modify name char(10) not null;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看表结构
mysql> desc test3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | char(10) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 删除id的unique索引
mysql> alter table test3 drop index id;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看表结构
mysql> desc test3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(10) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 给id添加unique 唯一索引
mysql> alter table test3 modify id int unique;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看表结构
mysql> desc test3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | char(10) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
--再次删除id的索引
mysql> alter table test3 drop index id;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 给id 与 name 添加联合唯一的索引（就是在数据表中多行的情况，id+name 的结果不能重现两个一样的）
mysql> alter table test3 add unique index(id, name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看表结构
mysql> desc test3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | char(10) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 添加数据
mysql> insert into test3 (id, name ) values (1, "小明");
Query OK, 1 row affected (0.00 sec)
-- 查看数据
mysql> select * from test3;
+------+------+
| id | name |
+------+------+
| 1 | 小明 |
+------+------+
1 row in set (0.00 sec)
-- 添加数据
mysql> insert into test3 (id, name ) values (2, "小明");
Query OK, 1 row affected (0.00 sec)
-- 查看数据
mysql> select * from test3;
+------+------+
| id | name |
+------+------+
| 1 | 小明 |
| 2 | 小明 |
+------+------+
2 rows in set (0.00 sec)
-- 添加数据
mysql> insert into test3 (id, name ) values (2, "小红");
Query OK, 1 row affected (0.00 sec)
-- 查看数据
mysql> select * from test3;
+------+------+
| id | name |
+------+------+
| 1 | 小明 |
| 2 | 小明 |
| 2 | 小红 |
+------+------+
3 rows in set (0.00 sec)
-- 如果出现id=1，name=小明，这种情况则会因为联合唯一索引的原因，会报错。
mysql> insert into test3 (id, name ) values (1, "小明");
ERROR 1062 (23000): Duplicate entry '1-小明' for key 'test3.id_2'
在实际开发中，很多时候，开发人员实际上是没有权限去操作数据库或者数据表的，因为开发人员只负责代码的编写与数据的操作，在很多中大型IT企业中，数据库的创建和数据表的设计，实际上很多时候都是技术总监、项目主管、DBA（数据库管理员）来完成的。因此针对数据表的操作，往往都是在开发人员参与项目进来之前就已经完成的了。当然在国内相对来说说，大部分的公司都是中小型IT企业，甚至有草台班子的情况，所以数据库的创建或者数据表的设计，也会有时候落实到开发人员的手上。
3.3 数据操作¶
数据操作是开发人员开发中对数据库进行最多最频繁的操作，主要有增加（insert）、查询（select）、修改（update）与删除（delete）四大语句。
对数据的增删查改操作有时候也被统称为CURD。
Create 增加数据
Update 修改数据
Read 查询数据
Delete 删除数据
3.3.1 添加数据¶
添加数据时，主键字段和有默认值的字段可以不写值，当然有需要的话，也可以填写上。
添加数据使用insert into 表名 语句来添加数据，其中into可以省略不写。
-- 指定字段添加数据（可以打乱顺序）
INSERT INTO 表名 (字段1,字段2,字段3,....) VALUES (字段1的值,字段2的值,字段3的值,....);
-- 也可以省略不写字段名，但是values后面字段值必须和表结构中的字段的排序位置与数量都保持一致（除了主键）。
INSERT INTO 表名 VALUES (字段值1,字段值2,字段值3,....);
-- 一次性添加多条记录(指定要添加数据的字段名，可以打乱顺序)
INSERT INTO 表名 (字段1,字段2,字段3,....)
VALUES
(字段值1,字段值2,字段值3,....),
(字段值1,字段值2,字段值3,....),
(字段值1,字段值2,字段值3,....)
...
(字段值1,字段值2,字段值3,....); -- 末行一定不要忘了结束符号
-- 一次性添加多条记录(不指定要添加数据的字段名，不可以打乱顺序，一定是按照表结构的字段排列顺序一一填写，除了主键ID以外)
INSERT INTO 表名
VALUES
(字段值1,字段值2,字段值3,....),
(字段值1,字段值2,字段值3,....),
(字段值1,字段值2,字段值3,....)
...
(字段值1,字段值2,字段值3,....); -- 末行一定不要忘了结束符号
-- 表数据的添加还支持表复制操作[这实际上是一种叫子查询的写法]
insert into 表名 (字段1, 字段2, 字段3, ...) select (字段1, 字段2, 字段3, ...) from 表名
操作演示：
-- 删除原来的student_info;
drop table if exists student_info;
-- 新建数据表
CREATE TABLE `student_info` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`sex` int DEFAULT '1',
`classes` int DEFAULT NULL,
`age` int DEFAULT NULL,
`description` text,
PRIMARY KEY (`id`)
);
-- 新增一条数据[可以选择指定ID]
insert into student_info (id,name,sex,classes,age, description) values (101,'刘德华',1,508,17,'给我一杯忘情水~');
-- 新增一条数据[更多时候，我们是不指定主键ID，让其自动增长，同时，具有默认值的字段也可以不填写，mysql会自动采用默认值替换]
insert into student_info (name, classes, age, description) values ('周华健', 501,17,'来也匆匆去也冲冲~恨不能相逢');
-- 上面的字段，如果是全部字段，那么字段这一块内容可以省略不写。
-- 例如，我们再次添加一个学生，如果省略了字段名，那么填写数据的数据项必须和表结构的字段数量保持一致。id字段，采用null来代替
insert into student_info values (null, '张学友', 1, 508, 17, '爱就像头饿狼~');
-- 添加多名学生
INSERT INTO student_info (name,sex,classes,age,description)
VALUES
('周润发',1,508,17,'5个A~'),
('周杰伦',1,508,17,'给我一首歌的时间~');
-- 复制表数据[可以复制来自当前表数据，也可以复制来自其他表的数据，只要字段值符合数据类型即可]
insert into student_info (name,sex,classes,age,description) select name,sex,classes,age,description from student_info;
操作效果：
-- 删除数据表
mysql> drop table if exists student_info;
Query OK, 0 rows affected (0.02 sec)
-- 新增一张数据表
mysql> CREATE TABLE `student_info` (
-> `id` int NOT NULL AUTO_INCREMENT,
-> `name` varchar(10) DEFAULT NULL,
-> `sex` int DEFAULT '1',
-> `classes` int DEFAULT NULL,
-> `age` int DEFAULT NULL,
-> `description` text,
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected (0.02 sec)
-- 查看表结构
mysql> desc student_info;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| sex | int | YES | | 1 | |
| classes | int | YES | | NULL | |
| age | int | YES | | NULL | |
| description | text | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
-- 添加一条数据（指定所有字段，包括ID字段）
mysql> insert into student_info (id,name,sex,classes,age, description) values (101,'刘德华',1,508,17,'给我一杯忘情水~');
Query OK, 1 row affected (0.01 sec)
-- 查看数据
mysql> select * from student_info;
+-----+--------+------+---------+------+-----------------+
| id | name | sex | classes | age | description |
+-----+--------+------+---------+------+-----------------+
| 101 | 刘德华 | 1 | 508 | 17 | 给我一杯忘情水~ |
+-----+--------+------+---------+------+-----------------+
1 row in set (0.00 sec)
-- 显示建表语句，可以发现当前ID计数器已经到了102了
mysql> show create table student_info;
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_info | CREATE TABLE `student_info` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`sex` int DEFAULT '1',
`classes` int DEFAULT NULL,
`age` int DEFAULT NULL,
`description` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- 添加一条数据（指定字段，不指定ID主键与有默认值的字段）
mysql> insert into student_info (name, classes, age, description) values ('周华健', 501,17,'来也匆匆去也冲冲~恨不能相逢');
Query OK, 1 row affected (0.00 sec)
-- 查看数据
mysql> select * from student_info;
+-----+--------+------+---------+------+-----------------------------+
| id | name | sex | classes | age | description |
+-----+--------+------+---------+------+-----------------------------+
| 101 | 刘德华 | 1 | 508 | 17 | 给我一杯忘情水~ |
| 102 | 周华健 | 1 | 501 | 17 | 来也匆匆去也冲冲~恨不能相逢 |
+-----+--------+------+---------+------+-----------------------------+
2 rows in set (0.00 sec)
-- 添加一条数据，不指定字段则必须一个个全部按顺序添加，其中id使用null来代替，作用是保证后续的字段的位置与表结构中的一致。
mysql> insert into student_info values (null, '张学友', 1, 508, 17, '爱就像头饿狼~');
Query OK, 1 row affected (0.00 sec)
-- 查看数据
mysql> select * from student_info;
+-----+--------+------+---------+------+-----------------------------+
| id | name | sex | classes | age | description |
+-----+--------+------+---------+------+-----------------------------+
| 101 | 刘德华 | 1 | 508 | 17 | 给我一杯忘情水~ |
| 102 | 周华健 | 1 | 501 | 17 | 来也匆匆去也冲冲~恨不能相逢 |
| 103 | 张学友 | 1 | 508 | 17 | 爱就像头饿狼~ |
+-----+--------+------+---------+------+-----------------------------+
3 rows in set (0.00 sec)
-- 一次性添加多条数据
mysql> INSERT INTO student_info (name,sex,classes,age,description)
-> VALUES
-> ('周润发',1,508,17,'5个A~'),
-> ('周杰伦',1,508,17,'给我一首歌的时间~');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
-- 查看数据
mysql> select * from student_info;
+-----+--------+------+---------+------+-----------------------------+
| id | name | sex | classes | age | description |
+-----+--------+------+---------+------+-----------------------------+
| 101 | 刘德华 | 1 | 508 | 17 | 给我一杯忘情水~ |
| 102 | 周华健 | 1 | 501 | 17 | 来也匆匆去也冲冲~恨不能相逢 |
| 103 | 张学友 | 1 | 508 | 17 | 爱就像头饿狼~ |
| 104 | 周润发 | 1 | 508 | 17 | 5个A~ |
| 105 | 周杰伦 | 1 | 508 | 17 | 给我一首歌的时间~ |
+-----+--------+------+---------+------+-----------------------------+
5 rows in set (0.00 sec)
-- 复制指定表中的数据并添加到当前表中
mysql> insert into student_info (name,sex,classes,age,description) select name,sex,classes,age,description from student_info;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
-- 复制指定表中的数据并添加到另一张表中
mysql> insert into student_info (name) select name from test3;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 查看表数据
mysql> select * from student_info;
+-----+--------+------+---------+------+-----------------------------+
| id | name | sex | classes | age | description |
+-----+--------+------+---------+------+-----------------------------+
| 101 | 刘德华 | 1 | 508 | 17 | 给我一杯忘情水~ |
| 102 | 周华健 | 1 | 501 | 17 | 来也匆匆去也冲冲~恨不能相逢 |
| 103 | 张学友 | 1 | 508 | 17 | 爱就像头饿狼~ |
| 104 | 周润发 | 1 | 508 | 17 | 5个A~ |
| 105 | 周杰伦 | 1 | 508 | 17 | 给我一首歌的时间~ |
| 106 | 刘德华 | 1 | 508 | 17 | 给我一杯忘情水~ |
| 107 | 周华健 | 1 | 501 | 17 | 来也匆匆去也冲冲~恨不能相逢 |
| 108 | 张学友 | 1 | 508 | 17 | 爱就像头饿狼~ |
| 109 | 周润发 | 1 | 508 | 17 | 5个A~ |
| 110 | 周杰伦 | 1 | 508 | 17 | 给我一首歌的时间~ |
| 113 | 小明 | 1 | NULL | NULL | NULL |
| 114 | 小明 | 1 | NULL | NULL | NULL |
| 115 | 小红 | 1 | NULL | NULL | NULL |
+-----+--------+------+---------+------+-----------------------------+
13 rows in set (0.00 sec)
添加数据的SQL语句使用中，一次性插入多个记录，效率要比多次添加一条记录的性能要更好。因为mysql是基于socket通信的单进程多线程的系统软件，所以每次执行SQL语句的，每次都会打开一个客户端与服务端的通信线程。所以同样的添加10条记录，一次添加一条记录，则会打开10个线程，而一次性添加10个记录，那么只需要打开1个线程。
3.3.2 界面化工具¶
使用界面化工具来管理数据库会更加清晰高效。常用的界面化工具：pycharm、navicat、dbeaver、phpstudy+phpmyadmin、syslog、mysql workbench（官方开发）。其中，pycharm的免费版本的数据库管理工具比较难用，navicat属于收费的。其他都是免费。
3.3.2.1 pycharm¶
使用database数据库管理工具
创建数据库连接
管理数据
创建数据表
使用database提供的console终端执行SQL语句
3.3.2.2 navicat¶
左上角可以创建数据库连接。
创建数据表
如果要创建主键以外的其他索引，则操作如下。
完成了字段设置操作以后，点击保存，设置表名即可。
3.3.3 csv数据导入¶
点选数据库连接，选择Import Data from files...
在弹出的新窗口中选择csv文件所在路径。
设置导入数据选项。因为我们使用的是windows，所以一般在windows下生成的csv数据都是gbk编码的。
完成以后的效果如下：
3.3.4 查询数据¶
查询操作是开发中四大操作中最常用（100个数据操作，其中可能存在90个操作是查询数据），也是最复杂的操作，复杂的原因是因为查询的过程中，需要通过不同的条件来查询，有时候查询会涉及到1张表，有时候多张表。
3.3.4.1 全列查询¶
在生产环境中，谨慎使用上述语句，因为返回的数据可能超大的。如果数据很大的话，就可能把服务器的网络带宽吃满，从而导致服务器无法正常工作。所以全列查询仅用于测试与学习。
操作：
3.3.4.2 指定列查询¶
指定列的顺序不需要按照定义表时的顺序查询，在查询SQL语句中，除了from关键字以外，其他关键字后面跟着的字段名，通常可以当做变量名看待。
操作：
-- 查找学生表的姓名跟年龄
SELECT name,age FROM student;
--查找课程表中的id,课程名称和教室编号
select id, cource,address from course;
3.3.4.3 表达式查询¶
SQL语句的关键字除了from等少部分关键以外，大部分关键字后面通常都可以跟着1个或多个表达式，MySQL中的表达式与Python中的表达式类似，可以是一个常量数值，也可以一个是字段名，也可以是基于运算符的表达式或基于函数调用的表达式。
-- 单个表达式
select 表达式 from 表名;
-- select 字段名 from 表名;
-- select 字段名+10 from 表名; -- 注意：此处的字段名的数据类型必须支持+号运算。
-- select 函数名(字段名) from 表名; -- 注意：此处的函数必须是MySQl内置的函数或者开发者基于MySQl语法声明的自定义函数
-- 多个表达式
select 表达式1,表达式2... from 表名;
操作：
select 1, name, age, 2022-age from student; -- 查询学生年龄
select name, if(sex=1, '男', '女') from student;
-- if在mysql中既是一个语句关键字，也是一个函数，参数1：表达式，参数2：表示结果为True的值内容，参数3：表示结果为False的值内容，
3.3.4.4 指定别名¶
SQL语句中提供了as关键字，可以给表达式、表名、字段名设置别名，在查看结果中表达式的字段名会被替换成别名。
操作：
select 1, name, age, 2022-age as born from student;
select name, if(sex=1, '男', '女') as sex, description as des from student;
3.3.4.5 去重查询¶
使用关键字 distinct可以把某列在不同行上相同的值的记录给去重。distinct必须写在所有字段名（字段列）或表达式前面。
-- 查询指定单个字段
select distinct 字段 from 表名;
-- 查询指定多个字段
select distinct 字段1, 字段2,... from 表名; -- 反正distinct必须跟在select后面，否则报错。而且只能出现一个distinct
操作：
效果：
3.3.4.6 结果排序¶
如果我们的SQL查询语句没有加上 order by，此时查询出来的结果顺序是未定的（MySQl数据库中并不会按人类的想法进行排序）。
可以在查询语句的表名后面加上 order by 要排序的列名/表达式 [asc | desc] 来进行排序
asc：表示升序（由小到大），不加具体要排序的方式，默认是 ascdesc：表示降序（由大到小），是descending的缩写排序过程中，是字符编码的位置来排序的。
-- 单列排序
select 字段 from 表名 order by 字段 desc;
select 字段1, 字段2.... from 表名 order by 字段名 asc | desc;
-- 多列排序
select 字段1, 字段2.... from 表名 order by 字段1 asc | desc, 字段2 asc | desc ....;
操作：
-- 结果排序
-- 单个字段排序
select * from student order by age desc;
-- 多个字段排序
select * from student order by classes asc, age asc; -- 每一个班最小的都在第一个位置。
select * from student order by classes asc, age desc; -- 每一个班最大的都在第一个位置。
3.3.4.7 结果限制¶
因为服务器的内存是有限的，而我们在查询一些大量数据的表时，如果一次性全部把数据读取出来，这些数据放在内存中有可能导致服务器的内存不足，严重的话还会导致程序崩溃，因此 往往我们不想一次取出所有的数据，可以对查询出的结果使用limit进行数量限制。limit 主要用于在项目开发中的分页功能实现。
limit有三种使用方式：
-- 方式1：limit后面跟着 一个参数 表示限制结果的数量
select 字段列表 from 表名 limit 结果数量;
-- 方式2： limit后面跟诊 两个参数，第一个参数表示取数据的开始下标[在表中下标从0开始]，第二个参数表示限制结果的数量。
select 字段列表 from 表名 limit 开始下标, 结果数量;
--方式3：也可以通过offset指定显示结果的开始下标
select 字段列表 from 表名 limit 结果数量 offset 开始下标;
操作：
-- 结果限制
SELECT * FROM student LIMIT 3; -- 从下标=0开始查询3条数据，相当远 limit 0,3
select * from student limit 3,3; -- 从下标=3开始查询3条数据
select * from student limit 6,3; -- 从下标=6开始查询3条数据
select * from student limit 3 offset 0; -- 从下标=0开始查询3条数据，相当远 limit 0,3
select * from student limit 3 offset 3; -- 从下标=3开始查询3条数据
select * from student limit 3 offset 6; -- 从下标=6开始查询3条数据
-- 结合排序就可以实现基本的查询
-- 成绩最好的5个学生
select sid from student_course order by achievement desc limit 5;
-- 查询年龄最大的10个学生[0-9]
select id, name,age from student order by age desc limit 0,10;
-- 从所有学生中，查询年级最大的下标从10-19的学生出来。[10-19]
select id,name,age from student order by age desc limit 10,10;
3.3.4.8 条件查询¶
条件查询需要使用条件语句，而条件语句适用很广，我们上面所学的几种查询语句的后面都可以使用，除此之外，后面所学的 update、delete 等语句也可以使用条件语句。条件语句是通过 where 条件表达式的形式，去进行数据筛选，相当于遍历数据表，针对每一个记录带入到条件中，将符合条件的记录保留下来，不符合的则淘汰。
操作：
-- 查询id为13的学生
select * from student where id=13;
-- 查询名为“吴杰”的学生
select * from student where name = '吴杰';
-- 多条件查询，查出301班的女生
select * from student where classes = 301 and sex=2;
-- 查询年龄最大的男生？
select * from student where sex=1 order by age desc limit 1;
-- 查询301班年龄最小的男生？
select * from student where classes=301 and sex = 1 order by age limit 1;
-- 以下条件等于不写条件，因为1相当于Ture
select * from student where 1;
select * from student where id = 3 or 1;
MySQL针对一条SQL查询语句的执行流程
MySQL针对一条SQL查询语句的执行，内部会使用查询优化器使用“选取-投影-连接”策略进行查询。例如
SELECT id, name FROM student WHERE sex = "女";中，SELECT查询先根据WHERE条件子句在数据表中选取符合条件，而不是将表全部查询出来以后再进行 sex 过滤。SELECT 查询先根据 id 和 name 进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。
3.3.4.9 运算符¶
MySQL中提供的运算符主要有：算术运算符、比较运算符、逻辑运算符与位运算符等。其中，常用的运算符有算术运算符、比较运算符、逻辑运算符。
3.3.4.9.1 算术运算符¶
| 运算符 | 描述 |
|---|---|
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| / 或 DIV | 除法 |
| % 或 MOD | 取余 |
操作：
select 1, name, age, 2022-age as born from student;
-- 在成绩中查询中所有学生被扣的分数
select sid, cid, achievement, 100-achievement from student_course;
-- id为偶数的学生，在mysql中判断两边的值是否相等，使用一个=来完成.
select id,name from student where id % 2 = 0;
3.3.4.9.2 比较运算符¶
比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。
| 符号 | 描述 |
|---|---|
| = | 等于 |
| <>, != | 不等于 |
| > | 大于 |
| < | 小于 |
| <= | 小于等于 |
| >= | 大于等于 |
| BETWEEN | 叫范围运算符，在两值之间，相当于 >=min &&<=max |
| NOT BETWEEN | 不在两值之间 |
| IN | 叫成员运算符，判断字段值是否在集合中 |
| NOT IN | 判断字段值是否不在集合中 |
| <=> | 严格比较两个NULL值是否相等，两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0 |
| LIKE | 模糊匹配 |
| REGEXP 或 RLIKE | 正则式匹配 |
| IS NULL | 为空 |
| IS NOT NULL | 不为空 |
-- 运算符
select 1, name, age, 2022-age as born from student;
-- 在成绩中查询中所有学生被扣的分数
select sid, cid, achievement, 100-achievement from student_course;
-- id为偶数的学生
select id,name from student where id % 2 = 0;
-- 比较运算符
-- 查询出所有年龄大于20岁的学生
select id, name, age from student where age > 20;
-- 查询出所有年龄大于20岁的女生
select id, name, age from student where age > 20 and sex=2;
-- 查询出除了301班以外的其他学生信息
select id, name, classes from student where classes != 301;
-- 范围比较查询，也叫范围查询
-- BETWEEN ... AND 查询出班级编号在301-305之间的学生
select id,name,classes from student where classes >=301 and classes <= 305;
select id,name,classes from student where classes between 301 and 305; -- 等价于上面的语句
-- NOT BETWEEN ... AND
-- 查出年齡段不在20-22之间的学生
select id,name,age from student where age < 20 or age > 22;
select id,name,age from student where age not between 20 and 22; -- 等价于上面的语句
-- IN 成员查询
-- 查询ID为1, 11, 21, 31, 41,的学生信息
select * from student where id = 1 or id = 11 or id = 21 or id = 31 or id = 41;
select * from student where id in (1, 11, 21, 31, 41); -- 等价于上面的语句
-- NOT IN 非成员查询
-- 查询出301，401，501以外的其他班级学生信息
select * from student where classes != 301 and classes != 401 and classes != 501;
select * from student where classes not in (301, 401, 501); -- 等价于上面的语句
-- 飞船运算符[主要是识别null]
-- 查询没有填写个性签名的学生信息
select * from student where description <=> null;
-- is null 与 is not null 判断值是否为空或不为空
select * from student where description is null;
select * from student where description is not null;
-- LIKE 模糊查询
-- 找出姓李的学生
select * from student where name like '李%';
-- 找出名字格式：李x的学生
select * from student where name like '李_';
-- 找出名字格式：李xx的学生
select * from student where name like '李__';
-- 找出名字是4个字的学生
select * from student where name like '____';
-- 名字包含"白"字的学生
select * from student where name like '%白%';
-- 名字以"杰"结尾的
select * from student where name like '%杰';
-- REGEXP 正则匹配
-- 找出个性签名子里面包含英文的
select * from student where student.description regexp '[a-zA-Z]+';
3.3.4.9.3 逻辑运算符¶
用于组合判断多个子条件形成一个整体的表达式，如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。
| 运算符写法1 | 运算符号写法2 | 作用 |
|---|---|---|
| ! | NOT | 逻辑非，并列，如果组合的条件都是TRUE,返回TRUE |
| && | AND | 逻辑与，或者，如果组合的条件其一是TRUE,返回TRUE |
| || | OR | 逻辑或，取反，如果条件是FALSE,返回TRUE |
操作：
-- 逻辑运算符
-- 查询年龄大于20岁小于22岁的学生
select * from student where age > 20 and age < 22;
-- 查询年龄小于20岁或者大于22岁的学生
select * from student where age < 20 or age > 22;
-- 查询年龄小于18岁 或者性别是女的学生
select * from student where age < 18 or sex = 2;
-- 查询年龄在18-22之间的女生信息(班级、姓名、年龄和性别)
select * from student where age >= 18 and age <= 22 and sex = 2;
select * from student where age between 18 and 22 and sex = 2;
-- 查询309班的所有男生信息(姓名、年龄、个性签名)
select * from student where classes = 309 and sex = 1;
-- 查询306班、305班、304班的男生信息(姓名、年龄、个性签名)
select * from student where (classes = 304 or classes = 305 or classes = 306) and sex = 1;
select * from student where classes in (304, 305, 306) and sex = 1;
-- 另一种写法的逻辑运算符
select * from student where (classes = 304 || classes = 305 || classes = 306) && sex = 1;
select * from student where classes in (304, 305, 306) && sex = 1;
3.3.4.9.4 优先级¶
- 优先级由高到低的顺序为：小括号 > not > 算术运算符 > 比较运算符 > and > or
- 使用过程中， 如果不清晰运算符之间的优先级，可以使用小括号
( )提升优先级。
3.3.4.10 聚合查询¶
聚合的意思是把多个数据放在一起计算，最终得到一个结果。聚合运算都是写在select关键字后面。
| 聚合函数 | 说明 |
|---|---|
| count | 返回查询到的数据的行的数量 |
| sum | 返回查询到的数据的值的总和，不是数字没有意义 |
| avg | 返回查询到的数据的值的 平均值，不是数字没有意义 |
| max | 返回查询到的数据的值的 最大值，不是数字没有意义 |
| min | 返回查询到的数据的值的 最小值，不是数字没有意义 |
操作：
-- 查询305班所有的学生数量
select count(1) from student where classes=305;
select count(id) as c from student where classes=305;
-- 所有学生的最小年龄
SELECT MIN(age) FROM student;
-- 所有学生的最大年龄
SELECT max(age) FROM student;
-- 查询302班中所有学生的平均年龄。
select AVG(age) from student where classes=302;
-- 查询305、304、302、301的男生平均年龄
select avg(age) from student where classes in (301, 302, 304, 305) and sex =1;
3.3.4.11 分组查询¶
GROUP BY子句， 可以对表的查询结果中的指定字段进行分组，常常与聚合函数一起使用。格式：GROUP BY 字段名语句不能写在where 条件表达式之前。
-- 方式1：按单个字段的不同行的值进行分组
group by 字段
-- 方式2：按多个字段的值的组合进行分组
group by 字段1, 字段2...
-- 不管是单个字段或多个字段分组，实际上都是在数据表中查看出现的多少个不同的字段值或字段值的组合，那么查询结果就会有多少个组。
操作：
-- 查询student表中有男女学生的数量分别是多少？
select if(sex=1,'男', '女') as sex, count(id) as total from student group by sex;
-- 查询学生表中各个年龄段的学生数量
select age, count(id) as total from student group by age;
-- 查询各个班级的人数各是多少
select classes, count(id) from student group by classes order by classes;
-- 多个分组
-- 计算每一个班中男生女生的数量
select classes, sex, count(id) from student group by classes, sex order by classes;
3.3.4.12 结果过滤¶
where条件语句的作用是针对当前数据表中的原始数据（聚合前的数据）进行筛选，而如果我们希望对聚合后的数据进行筛选，则需要使用having关键字才可以，having只能跟在group by之后使用，也叫分组结果过滤。
操作，查询301班级大于班上平均成绩的学生成绩信息(name，平均分，班级)。
3.3.4.13 连表查询¶
在前面的学习中，我们对数据查询操作全部都基于单张数据表进行操作的，但是在工作中，因为数据的存储需求不同或者用途不同，有时候并不能一张表把所有的数据都保存起来，因此一个真正项目，一般都是一个或多个数据库组成，而一个数据库下往往也会存在多张数据表。50张表以下基本都是小项目，200张表以下的基本都是属于中性项目，200张以上的基本就是大型项目。
当然，我们对于数据的查询操作，有时候单纯依靠对一张表进行查询，得到的数据可能并不完整，此时我们就需要采用连表查询了，也叫关联查询。主要是依靠关系型数据库中表与表之间在设计时存在的内在关联关系来进行联合查询的。
mysql中针对连表的方式有3种：左连（left join）、内连（inner join）与 右连（right join），在一些其他的重量级数据库管理系统中，如Oracle，实际上还支持外连（outer join）。
| 连表方式 | 描述 | 举例 |
|---|---|---|
| 左连 | from 主表 left join 从表 on 连表条件表达式 |
|
| 内连 | from 主表 inner join 从表 on 连表条件表达式 |
|
| 右连 | from 主表 right join 从表 on 连表条件表达式 |
在开发中，一般连表的前提时两张表或多张表之间存在字段的值是有映射关联的，而且一般不建议关联查询的表数量太多，建议在7张以内，太多的数据表进行关连查询，容易导致字段名重复引起冲突，此时如果冲突需要使用as 给字段起别名来避免。格式：
-- 2张表进行左连查询
select 字段列表 from 主表名 left join 从表名 on 主表名.主键名 = 从表名.外键名
-- 2张表进行右连查询
select 字段列表 from 主表名 right join 从表名 on 主表名.主键名 = 从表名.外键名
-- 2张表进行内连查询
select 字段列表 from 从表名 inner join 从表名 on 主表名.主键名 = 从表名.外键名
-- 3张表以上，连表条件表达式，只要写出来的任意一张表与当前表有关联，都可以参与连表操作，并非一定要主表的主键名
-- 下面的left join 仅仅是举例，多张表的连表查询，可以是left join，也可以right join，或者inter join
select 字段列表 from 主表名
left join 从表名1 on 任意表.主键名 = 从表名1.外键名
left join 从表名2 on 任意表.主键名 = 从表名2.外键名 -- 此处的任意表可以是 主表名，也可以是从表名1或者从表名n
...
left join 从表名n on 任意表.主键名 = 从表名n.外键名
操作：
-- 查询学生id为1的用户，成绩信息
select * from student
left join student_course on student.id = student_course.sid
where id = 1;
-- 关联三张表
select student.id, name, cid, achievement, cource from student
left join student_course on student.id = student_course.sid
left join course on student_course.cid = course.id
where student.id = 1;
-- 关联四张表
select student.id, name, cid, achievement, cource, lecturer.lecturer from student
left join student_course on student.id = student_course.sid
left join course on course.id = student_course.cid
left join lecturer on lecturer.id = course.lecturer
where student.id = 1;
3.3.4.14 查询语句的完整格式¶
select [distinct] 字段1 as A,字段2 as B....
from 表名1 as 表别名2, 表名2 as 表别名2
left join 从表1 on 表名.主键=从表1.外键
left join ....
where ....
group by ... having ...
order by ...
limit start,count
- 执行顺序为：
- from 表名[包括连表]
- where ....
- group by ...
- select distinct *
- having ...
- order by ...
- limit start,count
- 实际使用中，只是语句中某些部分的组合，而不是全部
from后面实际上可以跟着多个表名。
select * from student, course; -- 笛卡尔积的组合显示方式
select * from student, course where student.id < 3 and course.id <5; -- 笛卡尔积的组合显示方式
A表 B表
1 A
2 B
------------------------------------
最终显示结果：笛卡尔积
1 A
1 B
2 A
2 B
3.3.5 更新数据¶
更新操作会对数据造成不可逆的操作，所以更新数据时一定要注意添加where条件子句。如果没有条件或条件的判断结果一直是True，则整个表所有的记录都会被更新。
演示：
-- 修改学生的年龄
UPDATE student set age=18 where name='刘福荣';
-- 修改学生的名字与年龄
UPDATE student set name="吴小杰", age=20 where id = 65;
3.3.6 删除数据¶
删除操作也会对数据造成不可逆的操作，所以删除数据时一定要注意添加where条件子句。如果没有条件或条件的判断结果一直是True，则整个表所有的记录都会被删除。
演示：
3.4 数据备份与恢复¶
3.4.1 备份数据¶
备份数据就是把数据库中的数据保存到指定格式文件中。公司中为了保证数据安全，往往都会进行周期性的数据备份。
在cmd终端下运行mysqldump命令可以完成数据备份（注意：不需要进入MySQL的交互终端）：
3.4.2 数据恢复¶
- 连接mysql，创建新的数据库
- 退出连接，执行如下命令