下载掌阅APP，畅读海量书库
立即打开
· MySQL数据库操作
· MySQL数据表操作
· MySQL数据查询
· 在MySQL中插入数据
· MySQL中的where、update、delete语句
· MySQL中的常见函数、分组等操作
本章将讲解MySQL创建数据库、创建数据表、数据查询、常用函数和分组以及一些常用的操作语句，并且提供大量实例供读者参考和学习。
数据库常见的操作有：创建数据库、删除数据库和修改数据库。
创建数据库的语法如下：
create database [if not exists] db_name
其中，db_name代表的是需要创建的数据库的名称；[if not exists]是可选的，表示如果此名称的数据库不存在，就创建一个此名称的数据库，防止因创建现存同名的数据库而报错。
例4.1 两次创建名为mydb的数据库，示例SQL语句及其执行结果如下：
如上所示，在第一次成功创建mydb数据库之后，第二次创建同名的数据库时，由于没有加if not exists条件而报错，说明此名称的数据库已经存在。
删除数据库的语法如下：
drop database [if exists] db_name;
其中，db_name代表的是需要删除的数据库的名称；[if exists]是可选的，表示如果此名称的数据库存在，就删除此名称的数据库，防止因删除不存在的数据库而报错。
例4.2 两次删除名为mydb的数据库，示例SQL语句及其执行结果如下：
如上所示，在第一次成功删除mydb数据库后，第二次删除mydb数据库时，由于没有加if exists条件而报错，说明已不存在mydb数据库。
修改数据库的语法如下：
alter database db_name
[ default character set charset_name]
[ default collate collation_name]
其中db_name代表的是需要修改的数据库的名称；charset_name（字符集名）表示数据库默认使用的字符集编码；collation_name（排序规则）表示数据库默认使用的排序规则。这条语句就是用于修改db_name数据库的默认字符集和默认排序规则。
例4.3 修改数据库的默认字符集编码，示例SQL语句及其执行结果如下：
mysql> create database mydb;
Query OK, 1 row affected (0.01 sec)
mysql> alter database mydb character set=utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)
在创建数据库时，如果想修改数据库的默认字符集（即字符集编码），则可以通过上面的命令进行修改。
数据表常见的操作有创建数据表、修改数据表和删除数据表。
创建数据表的语法如下：
其中，tbl_name表示数据表的名称；[if not exists]是可选的，表示当不存在此名称的数据表时才去创建，防止因创建已经存在的数据表而报错。
例4.4 创建数据表，示例SQL语句及其执行结果如下：
其中，use_id字段使用了auto_increment（自动增加），表示此字段的值默认从1开始，每次自动加1。下面列举创建表时的其他组成部分。
主键（Primary Key）是一行数据的唯一标识，而且此键的列必须定义为not null。如果它们没有显式地声明为not null，则MySQL会隐式地声明为not null。一张数据表只能有一个主键。
例4.5 创建一张student表，其中studentno字段为此数据表的主键，示例SQL语句如下：
例4.6 创建一张包含id字段的数据表test，给表和字段分别添加注释，示例SQL语句如下：
create table test (
`id` int(11) unsigned comment ‘this is num’
)comment='test table ’;
语法如下：
create table [if not exists] 表名（
# 省略代码
）charset = 字符集名;
语法如下：
create table 表名(
# 省略代码
)engine=存储引擎;
为了更好地理解上述语法，下面用一个实例来说明。
例4.7 根据表4-1的说明，创建一个数据表结构。
表4-1 创建数据表结构
示例SQL语句如下：
除了创建数据表之外，还可以修改和删除数据表。
下面是修改数据表的一些常见语法。
（1）修改表名的语法如下：
alter table 旧表名 rename [to] 新表名;
（2）添加字段的语法如下：
alter table 表名 add 字段名 数据类型 [属性];
（3）修改字段的语法如下：
alter table 表名 change 原字段名 新字段名 数据类型 [属性];
（4）删除字段的语法如下：
alter table 表名 drop 字段名;
例4.8 结合修改数据表的常见语法进行测试，示例SQL语句如下：
删除MySQL数据表的通用语法如下：
drop table [if exists] table_name ;
其中，table_name代表的是需要删除的数据表的名称；[if exists]是可选的，表示如果此名称的数据表存在，就删除此名称的数据表，防止因删除不存在的数据表而报错。
在MySQL中删除表时要小心，因为执行删除命令后所有数据都会消失。
约束（Constraint）是MySQL提供的自动保持数据库完整性的一种方法，定义了可输入表或表的单个列中的数据的限制条件。在MySQL中有5种约束，具体如下：
（1）主键约束（Primary Key Constraint）：要求主键的列数据唯一，并且不允许为空。
（2）唯一约束（Unique Constraint）：要求该列的数据唯一，允许为空，但只能出现一个空值。
（3）检查约束（Check Constraint）：某列取值的范围限制、格式限制等。
（4）默认约束（Default Constraint）：某列的默认值。
（5）外键约束（Foreign Key Constraint）：用于在两个数据表之间建立关系，需要指定引用主表的哪一列。
可以在创建表时规定约束（通过create table语句），或者在表创建之后规定约束（通过alter table语句）。
创建约束的语法如下：
alter table 表名 add constraint 约束名
创建约束时需要注意约束名的取名规则，推荐采用下面的命名方式：
· 主键约束：如pk_stuno。
· 唯一约束：如uq_stuid。
· 默认约束：如df_stuaddress。
· 检查约束：如ck_stuborndate。
· 外键约束：如fk_stuno。
下面我们通过一个实例来加深对创建约束的理解。
例4.9 在表student中添加下面的约束：
（1）主键约束：学号。
（2）唯一约束：身份证号码。
（3）默认约束：address列的默认值是“地址不详”。
（4）检查约束：出生日期的值为1980年1月1日后。
（5）外键约束：设置gradeid列为外键，建立grade表与student表的引用关系。
创建约束的示例SQL语句及其执行结果如下：
# --主键约束
alter table student
add constraint pk_stuno primary key (studentno);
# --唯一约束（身份证号码唯一）
alter table student add
constraint uq_stuid unique (identitycard);
# --默认约束（地址不详）
alter table studentadd
constraint df_stuaddress default ('地址不详') for address;
# --检查约束（出生日期是自1980年1月1日以后）
alter table student
add constraint ck_stuborndate check(borndate>='1980-1-1');
# --添加外键约束，需要先在grade表中给gradeid创建主键，然后在student表中给gradeid创建外
键
alter table grade
add constraint pk_gradeid primary key(gradeid);
alter table student
add constraint fk_grade
foreign key(gradeid) references grade(gradeid);
需要特别强调的是，创建数据表与创建数据库等语句都属于数据定义语言（Data Definition Language，DDL），MySQL 8支持原子操作数据定义语句。原子DDL语句将与操作DDL关联的数据字典更新、存储引擎操作和二进制日志写入合并为一个原子操作，即使服务器在操作期间停止，该操作也会被提交（更改将保留到数据字典中）或被回滚。在早期的MySQL版本中，元数据存储在元数据文件、非事务表和特定于存储引擎的字典中，是需要中间提交的。而MySQL 8的数据字典提供的集中式事务元数据存储解决了这些问题，让DDL语句操作重构为原子操作（之前的版本不属于原子操作）。
MySQL数据库使用select语句来查询数据，其语法如下：
select column_name,column_name
from table_name[where clause][limit n][ offset m]
在查询之前需要掌握下面几个知识点：
（1）select语句中可以使用一个或者多个数据表，表之间使用逗号（,）分隔，并使用where语句来设定查询条件。
（2）select命令可以读取一条或者多条记录。
（3）可以使用星号（*）来代替其他字段，select语句会返回表的所有字段数据。
（4）可以使用where语句来包含任何条件。
（5）可以使用limit属性来设置返回的记录数。
（6）可以通过offset指定select语句开始查询的数据偏移量。默认情况下数据偏移量为0。
例4.10 使用select语句进行查询，示例SQL语句及其执行结果如下：
使用insert into语句在MySQL数据表中插入数据，其语法如下：
需要注意的是，如果数据是字符串型，则必须使用单引号或者双引号，如"value"，而且插入的数据值和字段类型必须对应，并且数据表中的列数必须与要插入的列数相匹配。
例4.11 向数据表userinfo中插入两条数据，示例SQL语句及其执行结果如下：
在这个示例中，我们并没有提供id的数据，因为该字段在创建表时已经设置为auto_increment（自动增加），所以该字段会自动递增，而不需要我们去设置数据值。
如果需要在一行中插入多个数据，可以使用如下语法：
通常情况下，在MySQL数据表中可以使用SQL select语句来读取数据。如果需要有条件地从表中选取数据，那么可以将where子句添加到select语句中，其语法如下：
select field1, field2,...fieldn from table_name1, table_name2...
[where condition1 [and [or]] condition2...
在使用此语法时，需要了解如下几个知识点：
（1）查询语句中可以使用一张或者多张数据表，表之间使用逗号（,）分隔，并使用where语句来设置查询条件。
（2）可以在where子句中指定任何条件。
（3）可以使用and或者or指定一个或多个条件。
（4）where子句也可以运用于SQL的delete或者update命令中。
（5）where子句类似于程序语言中的if条件，根据MySQL表中的字段值来读取指定的数据。
另外，where子句可以使用如表4-2所示的运算符。
表4-2 where子句可以使用的运算符
例4.12 使用where子句在MySQL表中查询指定数据，示例SQL语句及其执行结果如下：
如果想在MySQL数据表中读取指定的数据，那么where子句是非常有用的。如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。
and和or运算符基于一个以上的条件对记录进行筛选。and为“与”运算符，只有满足所有条件的数据才会被筛选出来。or为“或”运算符，满足其中一个条件的数据就会被筛选出来。
例4.13 使用and和or运算符查询数据，示例SQL语句及其执行结果如下：
between运算符用于筛选出介于两个值之间（数据范围内）的值。
例4.14 使用between运算符查询数据，示例SQL语句及其执行结果如下：
在select语句中，可以使用distinct关键字指示MySQL消除重复的记录值。
例4.15 使用与不使用distinct显示查询结果，示例SQL语句及其执行结果如下：
update命令用于修改或更新MySQL数据表中的数据。使用update命令修改MySQL数据表中数据的通用语法如下：
update table_name set field1=new-value1, field2=new-value2[where clause]
在使用update命令时需要知道下面几个知识点：
（1）update同时更新一个或多个字段。
（2）update可以在where子句中指定任何条件。
（3）update可以在单个数据表中同时更新数据。
例4.16 使用update更新数据，示例SQL语句及其执行结果如下：
除此之外，如果update语句包含order by（排序）子句，则各行会按照子句指定的顺序更新。
例4.17 使用order by按指定的列排序，示例SQL语句及其执行结果如下：
上面示例中数据表userinfo的主键是id，由于第一个update更新时没有指定排序，因此在更新时出现错误（主键重复），重新指定排序之后可以正常进行修改。
MySQL数据库使用delete命令来删除MySQL数据表中的记录，通用语法如下：
delete from table_name [where clause]
在使用delete命令时需要知道下面几个知识点：
（1）如果没有指定where子句，则MySQL表中的所有记录都将被删除。
（2）可以在where子句中指定任何条件。
（3）可以在单张表中一次性删除所有数据。
例4.18 使用delete命令删除数据，示例SQL语句及其执行结果如下：
在MySQL中使用select命令来读取数据，同时可以在select语句中使用where子句来获取指定的记录。where子句中可以使用等号（=）来设置筛选数据的条件，如"title='www.baidu.com'"。有时需要筛选出title字段中含有"com"字符的所有记录，这时就需要在where子句中使用like子句。
like子句中使用百分号（%）字符来表示任意字符，如果没有使用百分号，那么like子句与等号（=）的效果是一样的。以下是select语句使用like子句从数据表中读取数据的通用语法：
select field1, field2,...fieldn
from table_name
where field1 [not] like condition1 [and [or]] filed2 = 'somevalue'
在使用like子句时需要知道下面几个知识点：
（1）可以在where子句中指定任何条件。
（2）可以在where子句中使用like子句。
（3）可以使用like子句代替等号（=）。
（4）like通常与%一起使用，类似于一个元字符的搜索。
（5）可以使用and或者or运算符指定一个或多个条件。
（6）可以在delete或update命令中使用where…like子句来指定条件。
例4.19～例4.22是进行like操作的示例。
例4.19 在数据表students中查找所有以字母“H”开头的学生姓名，示例SQL语句及其执行结果如下：
可以看到，查询结果中只返回了以字母“H”开头的学生姓名。
例4.20 在数据表students中查找所有不以字母“F”开头的学生姓名，示例SQL语句及其执行结果如下：
可以看到，查询结果中返回了不以字母“F”开头的学生姓名。
例4.21 在数据表students中查找所有包含字母“e”的学生姓名，示例SQL语句及其执行结果如下：
可以看到，查询结果中返回了所有包含字母“e”的学生姓名。
“_”通配符只能代表单个字符，字符的长度不能为0。例如，a_b可以代表acb、adb、aub等字符串。
例4.22 在数据表students中，查找所有以字母“y”结尾且“y”前面只有4个字母的学生姓名，示例SQL语句及其执行结果如下：
可以看到，查询结果中返回了以字母“y”结尾且“y”前面只有4个字母的学生姓名。
使用select语句从MySQL数据表中读取数据后，如果需要对读取的数据进行排序，就可以使用order by子句来设置按照哪个字段、哪种方式进行排序，再返回搜索结果，通用的语法如下：
select field1, field2,...fieldn from table_name1, table_name2...
order by field1 [asc [desc][默认 asc]], [field2...] [asc [desc][默认 asc]]
在使用order by子句之前，需要了解下面的几个知识点：
（1）可以使用任何字段作为排序的条件，从而返回排序后的查询结果。
（2）可以设置多个字段来排序。
（3）可以使用asc或desc关键字来设置查询结果按升序或降序排列。默认情况下，按升序排列（asc）。
（4）可以添加where…like子句来设置条件。
例4.23 通过order by对数据进行升序和降序，示例SQL语句及其执行结果如下：
group by语句根据一个或多个列对结果集进行分组。在分组的列上可以使用count、sum、avg等函数来统计结果。分组的语法如下：
select column_name, function(column_name)
from table_name
where column_name operator value
group by column_name;
例4.24 使用group by语句将数据表按名字进行分组，示例SQL语句及其执行结果如下：
如果想在分组统计数据的基础上再进行相同的统计（比如sum、avg、count等），那么需要使用with rollup来实现，其语法如下：
select column_name, function(column_name)
from table_name
where column_name operator value
group by column_name with rollup;
例4.25 将例4.24中的数据表按名字进行分组，再统计每个人登录的次数，示例SQL语句及其执行结果如下：
其中记录null表示所有人的登录次数。使用coalesce设置一个可以取代null的名称，示例SQL语句及其运行结果如下：
由于where关键字无法与聚合函数一起使用，因此MySQL中增加了having子句，让我们可以筛选出分组后的各组数据。having子句的语法如下：
select column_name, aggregate_function(column_name)
from table_name where column_name operator value
group by column_name
having aggregate_function(column_name) operator value;
例4.26 根据name分组求出登录总次数，然后在计算结果的基础上统计出总次数大于2次的结果，示例SQL语句及其运行结果如下：
union运算符将两个以上的select语句的结果连接组合到一个结果集合中，遇到多个select语句时会删除重复的数据。union的语法如下：
select expression1, expression2, ... ,expression_n
from tables
[where conditions]
union [all | distinct]
select expression1, expression2, ... ,expression_n
from tables
[where conditions];
参数说明如下：
· expression1, expression2, …, expression_n：要检索的列。
· tables：要检索的数据表。
· where conditions：可选，检索条件。
· distinct：可选，删除结果集合中重复的数据。默认情况下，union运算符已经删除了重复数据，所以distinct修饰符对结果没什么影响。
· all：可选，返回所有结果集，包含重复数据。
下面用示例来演示union和union all的使用。首先准备数据：
例4.27 使用union从数据表userinfo和student中选取所有不同的age（只有不同的值），示例SQL语句及其执行结果如下：
由此可知，使用union进行组合查询可以得到不重复的值。
例4.28 使用union all从数据表userinfo和student中选取所有的age（有重复的值），示例SQL语句及其执行结果如下：
由此可知，使用union all进行组合查询得到的值没有去重。
例4.29 使用带有where的union all语句从数据表userinfo和student中选取所有大于或等于18的age，示例SQL语句及其执行结果如下：
建立一个图书馆管理系统的数据库用来存放图书馆的相关信息，包括图书的基本信息、图书的借阅信息和读者的信息。根据下表信息使用SQL语句创建相应的表结构。
图书信息表Book
读者信息表Reader
图书借阅表Borrow
罚款记录表
1．根据上述表格中的说明列，为每个表的相关列添加约束。
2．在图书信息表中添加BTotal列，数据类型是int，用于保存每种图书的馆藏总量。
3．向各表中插入至少两条测试数据，并查询测试。