MySQL | SQL基础、数据表的基本CURD操作详解
Ubuntu 17.04及之前版本的MySQL数据库的安装过程见下文：
《Ubuntu 18.04 下MySQL数据库的安装、基本配置和完全卸载》
Ubuntu 18.04版本的MySQL数据库的安装过程见下文：
《Ubuntu18.04 解决MySQL不能输入中文的问题》
本文及之后的MySQL相关博文中均使用下述环境：
- Ubuntu 18.04 64位
- MySQL 8.0.16
文章目录
SQL基础：语句结构、数据类型与运算符
结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
SQL可以非常高效的进行数据库的增删改查操作，SQL+索引更是可以实现带各种附加条件的高效率查询操作。
语句结构
结构化查询语言包含6个部分：
1、数据查询语言（DQL:Data Query Language）
其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。
保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。
2、数据操作语言（DML：Data Manipulation Language）
其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。
3、事务处理语言（TPL）
它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。
4、数据控制语言（DCL）
它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
5、数据定义语言（DDL）
其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREATE TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
6、指针控制语言（CCL）
它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。
数据类型
使用MySQL数据库存储数据时，不同的数据类型决定了MySQL存储数据方式的不同。类型规定了数据的大小，因此使用的时候选择合适的类型，不仅会降低表占用的磁盘空间，间接减少了磁盘I/O的次数，提高了表的访问效率，而且索引的效率也和数据的类型息息相关。为此，MySQL数据库提供了多种数据类型，其中包括
- 整数类型
- 浮点数类型
- 定点数类型
- 日期和时间类型
- 字符串类型
- 二进制类型。
整数类型
浮点数类型和定点数类型
日期与时间类型
注意TIMESTAMP会自动更新时间，非常适合那些需要记录最新更新时间的场景，而DATETIME需要手动更新。
字符串和二进制类型
TEXT类型用于表示大文本数据。例如：文章内容、评论等，TEXT类型分为四种，如下表所示：
BLOB类型是一种特殊的二进制类型，它用于表示很大的二进制数据。例如：图片、PDF文档等。BLOB类型分为四种，如下表所示：
MySQL运算符
接下来我们主要介绍 MySQL 的运算符及运算符的优先级。 MySQL 主要有以下几种运算符：
- 算术运算符
- 比较运算符
- 逻辑运算符
- 位运算符
算术运算符
MySQL 支持的算术运算符包括:
在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。
比较运算符
SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。
逻辑运算符
逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。
位运算符
位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。
数据库的基本操作
创建和查看数据库
MySQL安装完成后，要想将数据存储到数据库的表中，首先要创建一个数据库。创建数据库就是在数据库系统中划分一块存储数据的空间。在MySQL中，创建数据库的基本语法格式如下：
CREATE DATABASE 数据库名称;
注意：数据库名是唯一的，不可重复出现。
接下来创建一个名为 itcast 的数据库：
CREATE DATABASE itcast;
创建成功后，可以使用下述语句来查看数据库：
SHOW DATABASES;
创建好数据库后，若想查看某个已经创建的数据库信息，可以通过下述语句查看：
SHOW CREATE DATABASE 数据库名称;
我们接下来查看之前已经创建好的数据库itcast：
SHOW CREATE DATABASE itcast;
修改数据库
MySQL数据库一旦安装成功，创建数据库的编码也就确定了。但如果想修改数据库的编码，可以使用ALTER DATABASE实现。修改数据库编码的基本语法格式如下所示：
ALTER DATABASE 数据库名称 DEFAULT CHARACTER
SET 编码方式 COLLATE 编码方式_bin;
接下来我们修改itcast数据库的编码格式为gbk：
ALTER DATABASE itcast DEFAULT CHARACTER
SET gbk COLLATE gbk_bin;
删除数据库
删除数据库是将数据库系统中已经存在的数据库删除。成功删除数据库后，数据库中的所有数据都将被清除，原来分配的空间也将被回收。在MySQL中，删除数据库的基本语法格式如下：
DROP DATABASE 数据库名称;
需要注意的是，如果要删除的数据库不存在，那么将会删除失败。
那么接下来我们删除名称为itcast的数据库：
DROP DATABASE itcast;
从上述执行结果可以看出，数据库系统中已经不存在名称为itcast的数据库了，说明itcast数据库被成功删除了。
数据表的基本操作
创建数据表
CREATE TABLE 表名(字段名1 数据类型[完整性约束条件]，
···
字段名n 数据类型[完整性约束条件]);
接下来我们创建一个用于存储学生信息的表tb_grade。
首先创建数据库:
CREATE DATABASE itcast;
选择使用的数据库：
USE itcast;
创建数据表：
CREATE TABLE tb_grade(
id int,
name VARCHAR(20),
grade FLOAT);
查看数据表是否创建成功：
SHOW TABLES;
查看数据表的创建语句、表的字符编码(使用\G使得显示结果整齐美观)
SHOW CREATE TABLE 表名\G
使用DESCRIBE语句查看数据表：
DESCRIBE 表名;
-- 简写为
DESC 表名;
修改数据表
修改表名
alter table 旧表名 rename to 新表名;
接下来我们将数据库itcast中的 tb_grade 表名修改为 grade 表：
alter table tb_grade rename to grade;
从上述结果可以看出，数据库itcast中的tb_grade已被成功修改为grade了。
修改字段名(注意新数据类型不能为空)
alter table 表名 change 旧字段名 新字段名 新数据类型;
接下来我们将数据表grade中的name字段修改为username，数据类型保持不变：
alter table grade change name username VARCHAR(20);
从上述结果可以看出，数据表 grade 中的name字段名称已被成功修改为username了。
修改字段的数据类型
alter table 表名 modify 字段名 数据类型;
接下来我们将数据表grade中 id 字段的数据类型由 int(11) 修改为 int(20) :
alter table grade modify id int(20);
从上述结果可以看出，数据表 grade 中的 id 字段的数据类型已被成功修改为 int(20) 了。
添加字段
alter table 表名 add 新字段名 数据类型
[约束条件] [first|after 已存在字段名]
first参数为可选参数，用与将新添加的字段设置为表的第一个字段，after也为可选参数，用于添加新字段到指定的"已存在的字段名"后面。
接下来我们在数据表grade中的 username 字段后面添加一个没有约束条件的INT类型的字段age :
alter table grade add age int(10) after username;
从上述结果可以看出，数据表 grade 中的 username 字段后面成功添加了age字段。
删除字段
alter table 表名 drop 字段名
接下来我们删除grade表中的age字段：
alter table grade drop age;
从上述结果可以看出，数据表 grade 中的 age字段后面成功添加了age字段。
修改字段的排列位置
alter table 表名 modify 字符段1 数据类型 first|after 字段2
first参数为可选参数，指的是将字段1修改为表的第一个字段，after也为可选参数，用于将字段1插入到字段2的后面。
接下来我们将数据表grade中的username字段修改为表的第一个字段：
alter table grade modify username VARCHAR(20) first;
接下来我们将数据表grade中的id字段插入到grade字段的后面：
alter table grade modify id int(20) after grade;
删除数据表
删除数据表是指删除数据库中已存在的表。在删除数据表的同时，数据表中的存储的数据都将被删除。需要注意的是，创建数据表时，表和表之间可能会存在关联，要删除这些被其他表关联的表比较复杂，之后会讲解。下面是删除没有关联的表：
drop table 表名;
表的约束
为了防止数据表中插入错误的数据，MySQL中定义了一些维护数据库完整性的规则，即表的约束。下面列举了常见的表的约束。
- PRIMARY KEY：主键约束，用于唯一标识对应的记录
- FOREIGN KEY：外键约束
- NOT NULL：非空约束
- UNIQUE：唯一性约束
- DEFAULT：默认值约束
主键约束
在MySQL中，为了快速查找表中的某条信息，可以通过设置主键来实现。主键约束是通过 PRIMARY KEY 定义的，它可以唯一的标识表中的记录。在MySQL中，主键约束分为以下两种：
- 单字段主键
- 多字段主键
单字段主键
单字段主键指的是由一个字段构成的主键，基本语法格式如下：
字段名 数据类型 primary key
示例：创建一个数据表example，并设置 id 作为主键。
create table example
(id INT primary key,
name VARCHAR(20),
grade FLOAT);
多字段主键(联合主键)
多字段主键指的是多个字段组合而成的主键，基本语法格式如下：
primary key(字段名1, 字段名2, 字段名n)
示例：创建一个数据表example，在表中将 stu_id 和 course_id 两个字段共同作为主键。
create table example
(stu_id INT,
course_id INT,
grade FLOAT
primary key(stu_id, course_id)
);
注意：每个数据表中最多只能有一个主键约束，定义为primary key的字段不能有重复值且不能为NULL值。
非空约束
非空约束指的是字段的值不能为NULL，在MySQL中，非空约束是通过NOT NULL定义的，其基本的语法格式如下所示：
字段名 数据类型 NOT NULL;
示例：创建一个数据表example，为表中的name字段设置非空约束。
create table example
(id INT primary key,
name VARCHAR(20) NOT NULL,
grade FLOAT);
需要注意的是，在同一个数据表中可以定义多个非空字段。
唯一约束
唯一约束用于保证数据表中字段值的唯一性，即表中字段的值不能重复出现。其基本语法格式如下：
字段名 数据类型 unique;
示例：创建一个数据表example，为表中的 stu_id 字段设置唯一约束。
create table example
(id INT primary key,
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL);
默认约束
默认约束用于给数据表中的字段指定默认值，即当在表中插入一条新记录时，如果没有给这个字段赋值，那么数据库系统会自动为这个字段插入默认值，基本语法格式如下：
字段名 数据类型 default 默认值;
示例：创建一个数据表example，为表中的 grade 字段设置唯一约束。
create table example
(id INT primary key,
stu_id INT UNIQUE,
grade FLOAT DEFAULT 0);
设置表的字段值自动增加
在数据表中，若想为表中插入的新纪录自动生成唯一的ID，可以使用AUTO_INCREMENT约束来实现。
AUTO_INCREMENT约束的字段可以是任何整数类型。默认情况下，该字段的值是从1开始自增的。其基本语法格式如下：
字段名 数据类型 auto_increment
create table example
(id INT primary key AUTO_INCREMENT,
stu_id INT UNIQUE,
grade FLOAT DEFAULT 0);
添加、更新和删除数据
要操作数据表中的数据，必须通过MySQL提供了数据库操作语言实现，包括插入数据的 INSERT 语句，更新数据的 UPDATE 语句以及删除数据的 DELETE 语句。
添加数据
要操作数据表中的数据，首先要保证数据表中存在数据，MySQL使用 INSERT 向数据表中添加数据，并且根据添加方式的不同分为三种，分别是为表的所有字段添加数据、为表的指定字段添加数据、同时添加多条记录。接下来进行详细的讲解。
为表中所有字段添加数据
为表中所有字段添加数据的INSERTE语句有两种：
- INSERT语句中指定所有字段名
- INSERT语句中不指定字段名
INSERTE语句中指定所有字段名
基本语法格式如下：
INSERT INTO 表名(字段名1, 字段名2, ··)
VALUES(值1, 值2, ···);
-- 示例
INSERT INTO student(id,name,grade)
VALUES(1, ’zhangsan‘, 98.5);
INSERTE语句中不指定字段名
基本语法格式如下：
INSERT INTO 表名 VALUES(值1, 值2, ···);
-- 示例
INSERT INTO student
VALUES(3, ’wangwu‘,61.5);
为表的指定字段添加数据
基本语法格式如下：
INSERT INTO 表名(字段名1, 字段名2, ···)
VALUES(值1, 值2, ···);
-- 示例
INSERT INTO student(id,name)
VALUES(4, ’zhaoyun‘);
注意：如果某个字段在定义时添加了非空约束，但是没有添加default默认约束，那么插入新纪录时就必须为该字段赋值，否则数据库系统会提示错误。
同时添加多条记录
有时候，需要一次向表中添加多条记录，当然可以使用上述两种方式将记录逐条添加，但这样做需要添加多条INSERT语句，比较麻烦。
更重要的是INSERT逐条添加的效率问题，当我们使用MySQL数据库时，便与MySQL服务器建立了一条TCP连接，每次发送的SQL语句都是一次完整的通信过程，但我们发送多条SQL语句时，便是进行了多次通信，而我们使用INSERT语句可以实现一次添加多条记录，这样不但完成了业务工作，也减少了通信次数，提高了使用效率。
基本语法格式如下：
INSERT INTO 表名(字段名1, 字段名2, ···)
VALUES(值1, 值2, ···),
(值1, 值2, ··`),
···
(值1, 值2, ···); /*每条记录直接使用逗号隔开*/
-- 示例
INSERT INTO student VALUES
(6, ’liubei‘, 99),
(7, ’guanyu‘, 100),
(8, ’zhangfei‘, 40.5);
更新数据
更新数据是指对表中存在的记录进行修改，这是数据库常见的操作，比如某个学生改了名字，就需要对其记录的name字段值进行修改，MySQL中使用UPDATE语句来更新表中的记录，其基本的语法格式如下所示：
UPDATE 表名
SET 字段名1=值1[,字段名2=值2，···]
[WHERE 条件表达式]
UPDATE对表中数据的更新可以实现两种操作：
- UPDATE 更新部分数据
- UPDATE 更新全部数据
UPDATE 更新部分数据
更新部分数据是指根据指定的条件更新表中的某一条或几条记录，需要使用WHERE子句来指定更新记录的条件。
-- 示例
UPDATE student
SET name='caocao',score=50
WHERE id=1;
UPDATE student
SET grade='合格'
WHERE score > 60;
UPDATE 更新全部数据
如果UPDATE语句中没有使用WHERE子句，那么表中所有记录的指定字段都会被更新。
-- 示例
UPDATE student
SET grade=80;
删除数据
删除数据是对表中已存在的记录进行删除，这是数据库的常见操作，比如一个学生转学了，就需要在student表中将其信息记录删除，MySQL中使用DELETE语句来删除表中的记录，其基本语法格式如下：
DELETE FROM 表名 [WHERE 条件表达式]
同样的，DELETE对表中数据的删除可以实现两种操作：
- DELETE 删除部分数据
- DELETE 删除全部数据
DELETE 删除部分数据
删除部分数据是指根据指定的条件删除表中的某一条或几条记录，需要使用WHERE子句来指定删除记录的条件。
-- 示例
DELETE FROM student
WHERE id=11;
DELETE FROM student
WHERE id>5;
DELETE 删除全部数据
如果DELETE语句中没有使用WHERE子句，那么表中所有记录都会被删除。
-- 示例
DELETE FROM student;
使用关键字TRUNCATE 删除表中数据
在MySQL数据库中，还有一种方式可以删除表中的所有记录，这种方式需要用到一个关键字TRUNCATE，其语法格式如下:
TRUNCATE [TABLE] 表名
-- 示例
TRUNCATE TABLE student;
TRUNCATE语句和DELETE语句都可以实现删除表中所有数据的功能，但两者也存在一定的区别。
- DELETE语句是DML语言，TRUNCATE语句通常被认为是DDL语言。
- DELETE语句后面可以跟WHERE子句删除表中部分记录，而TRUNCATE语句只能用于删除表中的全部记录。
- 使用TRUNCATE语句删除表中数据后，再次向表中添加记录时，若表中存在有自动增加属性的字段，那么它值将重新从1开始；而DELETE语句删除表中数据后，再次向表中添加记录时，自动增加属性的字段的值为删除时该字段的最大值加一。
- 使用DELETE语句时，每删除一条记录都会在日志中记录，而使用TRUNCATE语句时，不会在日志中记录删除的内容，因此TRUNCATE语句的执行效率比DELETE语句要高。