MySQL课程笔记

MySQL课程笔记

数据库基础知识

概念

  • 数据管理技术的发展阶段：人工管理阶段-〉文件系统阶段 -〉 数据库管理阶段
  • 所谓数据库(DataBase, DB)，是将数据按一定的数据模型组织、描述和存储，具有较小的冗余度，较高的数据独立性和易扩展性，并可为各种用户共享的数据集合。
  • 数据库系统(DataBase System, DBS)一般由数据库、数据库管理系统(及其开发工具)、应用系统、数据库管理员和用户结构组成
  • 数据库管理系统（DataBase Management System, DBMS）对收集到的大量数据进行整理、加工、归并、分类、计算、存储等处理，产生新的数据，以便反映事物或现象的本质和特征及其内在联系。
  • 数据库应用系统(DataBase Application System, DBAS) 数据库应用系统是由数据库系统、应用程序系统、用户组成的，具体包括：数据库、数据库管理系统、数据库管理员、硬件平台、软件平台、应用软件、应用界面。

结构化查询语言SQL

SQL概念

  1. 结构化查询语言SQL（Structured Query Language）,SQL语言是用于关系数据库查询的结构化语言，最早由Boyce和Chambedin在1974年提出，称为SEQUEL语言。1976年，IBM公司的San Jose研究所在研制关系数据库管理系统System R时修改为SEQUEL2，即目前的SQL语言。
  2. SQL-关系型数据库管理系统（RDBMS）：
    • 不同的角色（开发者，用户，数据库管理员）使用相同的语言。
    • 不同的RDBMS使用统一标准的语言。
    • SQL使用一种高级的非结构化查询语言。
    • 坚持 ACID 准则 (原子性，一致性，隔离性，持久性)
  3. NoSQL- 非关系性数据库
    • 采用Key-value方式存储数据。采取最终一致性原则，更加适合互联网数据，但这也可能导致数据丢失。
  4. NewSQL-结合SQL和NoSQL
    • 将SQL的ACID保证与NoSQL的可扩展性和高性能相结合，目前大多数NewSQL数据库都是专有软件或仅适用于特定场景，这显然限制了新技术的普及和应用。

主要特点包括：

（1）综合统一

（2）高度非过程化

（3）面向集合的操作方式

（4）以同一种语法结构提供两种使用方式

（5）语言简洁，易学易用

数据库关系图

SQL分类

SQL 语句，根据其功能，主要分为四类:DDL、DML、DQL、DCL 。

分类 全称 说明
DDL Data Definition Language 数据定义语言，用来定义数据库对象（数据库，表，字段)
DML Data Manipulation Language 数据操作语言，用来对数据库表中的数据进行增删改
DQL Data Query Language 数据查询语言，用来查询数据库中表的记录
DCL Data Control Language 数据控制语言，用来创建数据库用户、控制数据库的访问权限

SQL通用语法

在学习具体的SQL语句之前，先来了解一下SQL语言的通用语法。

  1. SQL语句可以单行或多行书写,以分号结尾。
  2. SQL语句可以使用空格/缩进来增强语句的可读性。
  3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
  4. 注释:
    单行注释:-- 注释内容 或 # 注释内容
    多行注释:/** 注释内容* */

注：MySQL下载安装教程详见MySQL的安装和使用。

图形化软件

本笔记所采用的图形化软件为 Navicat 。

“Navicat”是一套可创建多个连接的数据库管理工具，用以方便管理 MySQL、Redis、Oracle、PostgreSQL、SQLite、[SQLServer](https://baike.baidu.com/item/SQL Server/245994?fromModule=lemma_inlink)、MariaDB 和 MongoDB 等不同类型的数据库，它与 GaussDB、OceanBase、PolarDB 以及 阿里云、腾讯云、华为云、Amazon RDS、Amazon Aurora、Amazon Redshift、Microsoft Azure、Oracle Cloud 和 MongoDB Atlas等云数据库兼容。它用于创建、管理和维护数据库。Navicat 的功能足以满足专业开发人员的所有需求，但是对数据库服务器初学者来说又简单易操作。Navicat 的用户界面 (GUI) 设计良好，以安全且简单的方法创建、组织、访问和共享信息。

数据库设计

数据库设计就是将数据库中的数据对象以及这些数据对象之间关系进行规划和结构化的过程，当数据库比较复杂时我们需要设计数据库

关系数据库设计

数据的加工

  • 数据是描述事物的符号记录，模型是现实世界的抽象。
  • 数据模型是数据特征的抽象，包括数据的结构部分，操作部分和约束条件。
  • 每个事物的无穷特征如何数据化？事物之间错综复杂的关系如何数据化？现实世界直接数据化是不可行的，数据加工是一个逐步转换的过程，会经历现实世界、信息世界和数据世界三个不同层面。

1.现实世界

现实世界是存在于人们头脑之外的客观世界。现实世界存在各种事物，事物与事物之间存在联系，这种联系是由事物本身的性质决定的。

例如，学校中有教师、学生、课程，教师为学生授课，学生选修课程并取得成绩；图书馆中有图书、管理员和读者，读者借阅图书，管理员对图书和读者进行管理等。

2.信息世界

  • 信息世界是现实世界在人们头脑中的反映，人们把它用文字或符号记载下来，然后用规范化的数据库定义语言来定义描述而构成的一个抽象世界。信息世界实际上是对现实世界的一种抽象化描述。
  • 信息世界不是简单的对现实世界进行符号化，而是要通过筛选、归纳、总结、命名等抽象化过程形成概念模型，用以表示对现实世界的抽象和描述。

3.数据世界

数据世界又称机器世界，它是将信息世界的内容数据化后的产物，即将信息世界中的概念模型，进一步转换成数据模型所形成的便于计算机处理的数据表现形式。

模型

  1. 概念模型:把现实世界转换为信息世界的模型,E-R模型。
  2. 数据模型:把信息世界转化为数据世界使用的模型,关系模型。

设计数据库关系模型

规范化设计

按照规范化设计的方法，考虑数据库及其应用系统开发的全过程，将数据库的设计分为以下6个设计阶段：

  1. 需求分析、

  2. 概念设计、

  3. 逻辑设计、

  4. 物理设计、

  5. 数据库实施、

  6. 数据库运行和维护。

数据库设计步骤

数据库设计步骤

需求分析阶段

需求分析就是根据用户的需求收集数据，是设计数据库的起点。需求分析的结果是否准确反映用户的实际需求，将直接影响到后面各个阶段的设计，并影响到设计结果是否合理和实用。

需求分析的任务就是收集数据，要尽可能多地收集关于数据库要存储的数据以及将来如何使用这些数据的信息，确保收集到数据库需要存储的全部信息。

需求分析的目标可以参考以下问题：

（1）有多少数据，数据的来源在哪里，是否有已存在的数据资源？

（2）必须保存哪些数据，数据是字符、数字或日期型？

（3）谁使用数据，如何使用？

（4）数据是否经常修改，如何修改和什么时候修改？

（5）某个数据是否依赖于另一个数据或被其他数据引用？

（6）某个信息是否要唯一？

（7）哪些数据是组织内部的和哪些是外部数据？

（8）哪些业务活动与数据有关，数据如何支持业务活动？

（9）数据访问的频度和增长的幅度如何？

（10）谁可以访问数据，如何保护数据。

需求分析的方法，常用的调查方法：

（1）跟班作业。通过亲身参加业务工作来了解业务活动的情况。通过这种方法可以比较准确地了解用户的需求，但比较耗费时间。

（2）开调查会。通过与用户座谈来了解业务活动情况及用户需求。座谈时，参加者和用户之间可以相互启发。

（3）请专人介绍。

（4）询问。对某些调查中的问题，可以找专人询问。

（5）问卷调查。设计调查表请用户填写。如果调查表设计得合理，这种方法是很有效的，也易于为用户所接受。

（6）查阅记录。查阅与原系统有关的数据记录。

概念设计阶段

概念设计是整个数据库设计的关键，它通过对用户的需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型。

设计人员完成了数据库设计的第一步，收集到了现实世界中的数据，下一步的工作是将收集到的数据进行分析，找出它们之间的联系，并用E-R图来表示。

概念模型的相关概念：

  1. 实体（Entity）：客观存在并且可以相互区别的事物称为实体。实体可以是具体的事物，也可以是抽象的事件。

  2. 属性（Attribute）：描述实体的特性。一个实体可以用若干个属性来描述。

  3. 主码（Key）：唯一标识实体的属性或属性的组合。

  4. 域（Domain）：属性的取值范围称为该属性的域。

  5. 实体集（Entity Set）：具有相同特征和性质的同一类实体的集合称为实体集。例如全体学生、一批图书等。

  6. 联系（Relationship）：

    在现实世界中，事物内部以及事物之间是有联系的，这些联系在信息世界中反映为实体（型）内部的联系和实体（型）之间的联系。

    实体内部的联系通常是指组成实体的各属性之间的联系；

    实体之间的联系通常是指不同实体集之间的联系。

两个实体之间的联系可以分为 3类。

（1）一对一(1:1)联系（One-to-One Relationship）。

例如，如果一个人只能有一个身份证，一个身份证只能对应一个人，则人和身份证之间具有一对一的联系。

（2）一对多(1:n)联系（One-to-Many Relationship）。

例如，一个人可以有多个移动电话号码，但一个电话号码只能卖给一个人。人与移动电话号码之间的联系就是一对多的联系。

（3）多对多(m:n)联系（Many-to-Many Relationship）。

例如，一门课程同时可以由若干学生选修，而一个学生同时也可以选修若干门课程，课程与学生之间的联系是多对多的联系。

概念模型是对信息世界的建模，它应当能够全面、准确地描述信息世界，是信息世界的基本概念。

概念模型用于建立信息世界的模型，它是独立于计算机系统的模型，强调其语义表达功能，要求概念简单、清晰，易于用户理解，它是现实世界的第1层抽象，是用户和数据库设计人员之间进行交流的工具。

概念模型的表示方法很多，其中最为著名和使用最为广泛的是P.P.Chen于1976年提出的E-R（Entity-Relationship）模型。

建模

E-R图的组成要素及其画法

E-R图的组成要素及其画法

概念结构设计的方法：

（1）自顶向下。首先定义全局概念结构的框架，然后逐步细化。

（2）自底向上。首先定义各局部应用的概念结构，然后将它们集成起来，得到全局概念结构。

（3）逐步扩张。首先定义最重要的核心概念结构，然后向外扩充，以滚雪球的方式逐步生成其他概念结构，直至总体概念结构。

（4）混合策略。将自顶向下和自底向上的方法相结合，用自顶向下策略设计一个全局概念结构的框架，以它为框架自底向上设计各局部概念结构。

最常采用的是混合策略，即自顶向下进行需求分析，然后自底向上设计概念结构。

混合策略

概念结构设计的步骤

（1）进行数据抽象，设计局部E-R模型。
（2）集成各局部E-R模型，形成全局E-R模型。

概念结构设计的步骤

E-R图设计步骤 ：

对于复杂的系统，E-R图设计通常都应经过以下两个阶段：

（1）针对每一用户画出该用户信息的局部E—R图，确定该用户视图的实体、属性和联系。需注意的是：能作为属性的就不要作为实体，这有利于E—R图的简化。

（2）综合局部E—R图，生成总体E—R图。在综合过程中，同名实体只能出现一次，还要去掉不必要的联系，以便消除冗余。一般来说，从总体E—R图必须能导出原来的所有局部视图，包括实体、属性和联系。

逻辑设计阶段

概念设计阶段完成后，下一步进入逻辑设计阶段，即将概念模型转换成某个DBMS所支持的数据模型，并对其进行优化。

数据模型

数据模型：它是直接面向数据库的逻辑结构，是现实世界的第2层抽象。
数据模型涉及计算机系统和数据库管理系统。数据模型有严格的形式化定义，以便于在计算机系统中实现。

常见数据模型：

1.层次模型：用“树”结构来表示数据之间的关系

优点：
简单，只需很少几条命令就能操纵数据库。
性能优于关系模型和网状模型。
提供良好的完整性支持。
缺点：
不支持多对多联系，只能通过冗余数据（易产生数据不一致性）或引入虚拟结点来解决。

层次模型

2.网状模型：用“图”结构来表示数据之间的关系

优点：

​ 能更直接地反映现实世界，效率高。

缺点：

​ 结构比较复杂，DDL和DML语言复杂。

网状模型

3.关系模型：用“表”结构（或称关系）来表示数据之间的关系。

优点：

​ 建立在严格的数学概念的基础上，概念单一，数据结构简单、清晰，用户易懂易用。

​ 实体和各类联系都用关系来表示，对数据的检索结果也是关系。

​ 关系模型的存取路径对用户透明，具有更高的数据独立性，更好的安全保密性。

​ 简化了程序员的工作和数据库开发建立的工作，关系模型中的数据联系是靠数据冗余实现的。

缺点：

​ 存取路径对用户透明导致查询效率往往不如非关系数据模型。

​ 为提高性能，必须对用户的查询请求进行优化从而增加了开发数据库管理系统的难度。

关系模型

E—R模型到关系模型的转换

把E-R图转换为关系模型可遵循如下原则 ：

1.对于E—R图中每个实体集，都应转换为一个关系，该关系应包括对应实体的全部属性，并应根据关系所表达的语义确定哪个属性或哪几个属性组作为“主关键字”，主关键字用来标识实体。

2.对于E—R图中的联系，情况比较复杂，要根据实体联系方式的不同，采取不同的手段加以实现。

转换规则：（必须牢记！！）

规则1.1 实体类型的转换：将每个实体类型转换成一个关系模式，实体的属性即为关系的属性，实体的标识符即为关系模式的码。

规则1.2 联系类型的转换：根据不同的联系类型做不同的处理。

规则1.2.1 若实体间联系是1∶1，可以在两个实体类型转换成的两个关系模式中任意一个关系模式中加入另一个关系模式的码和联系类型的属性。

【例】将下图所示的含有1∶1联系的E-R图根据上述规则转换为关系模式。

方案1：“负责”与“职工”两关系模式合并。

职工（职工号，姓名，年龄，产品号）

产品（产品号，产品名，价格）

​ 或者：

方案2：“负责”与“产品”两关系模式合并。

职工（职工号，姓名，年龄）

产品（产品号，产品名，价格，职工号）

1:1

规则1.2.2 若实体间的联系是1∶n，则在n端实体类型转换成的关系模式中加入1端实体类型的码和联系类型的属性。

【例】将下图所示的含有1∶n联系的E-R图根据上述规则转换为关系模式。

仓库（仓库号，地点，面积）

产品（产品号，产品名，价格，仓库号，数量）

1:n

规则1.2.3 若实体间联系是m∶n，则将联系类型也转换成关系模式，其属性为两端实体类型的码加上联系类型的属性，而码为两端实体码的组合。

【例】将下图所示的含有m∶n联系的E-R图根据上述规则转换为关系模式。

商店（店号，店名，店址，店经理）

商品（商品号，商品名，单价，产地）

经营（店号，商品号，月销售量）

m:n

规则1.2.4 3个或3个以上的实体间的一个多元联系，不管联系类型是何种方法，总是将多元联系类型转换成一个关系模式，其属性为与该联系相连的各实体的码及联系本身的属性，其码为各实体码的组合。

【例】将下图所示的多实体集间含有m∶n联系的E-R图根据上述规则转换为关系模式。

供应商（供应商号，供应商名，地址）

零件（零件号，零件名，单价）

产品（产品号，产品名，型号）

供应（供应商号，零件号，产品号，数量）

3个及以上

数据库设计规范化

仅有好的RDBMS并不足以避免数据冗余，必须在数据库的设计中创建好的表结构

关系数据库范式理论是在数据库设计过程中将要依据的准则，数据库结构必须要满足这些准则，才能确保数据的准确性和可靠性。这些准则则被称为规范化形式，即范式。

Dr E.F.codd 最初定义了规范化的三个级别，范式是具有最小冗余的表结构。这些范式是：

第一范式(1st NF －First Normal Fromate)

第二范式(2nd NF－Second Normal Fromate)

第三范式(3rd NF－ Third Normal Fromate)

函数依赖

实体之间，实体内部的各个特征之间的相互依赖和制约关系——自然反映到关系数据库中的各关系和关系内部的各属性之间。

关系是由属性构成，函数依赖就是讲属性之间的依赖。

定义1函数依赖：

设有关系模式R(U )，X和Y均为U={A1，A2，…，An}的子集，若对于元素中X上的每个值都有Y上的一个惟一值与之相对应，则称X函数决定Y，或称Y函数依赖于X，记作X→Y，其中X叫做决定因素（Determinant），Y叫做依赖因素（Dependent）。

练习：指出学生关系S中存在的函数依赖关系

S（学号，姓名，班号，班级，课程号，教师，成绩）

学号 →姓名（每个学号只能有一个学生姓名）

学号 →班级（每个学号只能有一个班级）

（学号，课程号）→成绩（每个学生每门课只有一个成绩）

定义2：部分函数依赖 、完全函数依赖

在关系模式R(U )中，X和Y为属性集U上的子集，若X→Y，并且对于X的任何一个真子集X¢，都有X¢ → Y，则称X部分函数决定Y或Y部分函数依赖X，记作X Y。

否则，若不存X的任何一个真子集X¢，使得X¢→Y，则称X完全函数决定Y或 Y完全函数依赖于X， 记作X Y。

练习：指出学生关系S中存在的函数依赖关系是部分依赖还是完全依赖

S（学号，姓名，班号，班级，课程号，教师，成绩）

学号 →姓名（完全函数依赖）

学号 →班级（完全函数依赖）

（学号，课程号）→成绩（完全函数依赖）

（学号，课程号）→姓名（部分函数依赖）

定义3：传递函数依赖

在关系模式R(U )中，X、Y、Z是属性集U上的子集，如果X→Y ，**Y→Z，但Y不包含于X，Y不能函数决定X,则存在X → Z。

练习：指出学生关系S中存在的函数依赖关系是否存在传递函数依赖

S（学号，姓名，班号，班级，课程号，教师，成绩）

学号 →班号 班号 →班级 （班号不属于学号，班号不决定学号）

学号→班级

所以班级传递依赖于学号

属性间联系决定函数依赖

① 1∶1联系：如果两属性集X、Y之间是1∶1联系，则存在函数依赖X Y。

② 1∶n联系：如果两属性集X、Y之间是n∶1联系，则存在函数依赖X→Y，即多方决定一方。

③ m∶n联系：如果两属性集X、Y之间是m∶n联系，则不存在函数依赖。

定义4：候选码

在关系模式R(U )中，X是属性集U上的子集，如果X能够函数决定U中的每个属性*，*并且X的任何真子集都不能函数决定U中的每个属性，则称X为关系R上的一个候选码。

在一个关系中可以有多个候选码，可能是某几个属性，也可能是某几个属性的组合。

练习：指出学生关系S的候选码

S（学号，姓名，班号，班级，课程号，教师，成绩）

学号 →姓名，学号 →班号，班号→班级

（学号，课程号）→成绩

所以学生关系S的候选码是（学号，课程号）

定义5：主码/主键 主属性

一个关系包含多个候选码，可选取一个作为主码/主键。

定义6：主属性

主属性：包含在任一候选码中的属性称为主属性，其他为非主属性。

关系数据库范式理论

（1）第一范式：若关系模式R(U)的 每个分量都是不可分割的数据项，则R(U)∈1NF

第一范式

（2）第二范式：若R(U)∈1NF，且R(U)中的每一个非主属性都完全函数依赖于R(U)的任一候选码，则R(U)∈2NF

学号 姓名 班号 地址 课程号 课程名 教师 成绩
01001 王丽 01 河北省石家庄市裕华路 C001 C语言 刘峰 80
01002 张勇 01 河北省保定市裕华路 C001 C语言 刘峰 75
01003 李江 01 河北省邢台市新华路 C001 C语言 刘峰 86

候选码：学号，课程号

非主属性：姓名，班号，地址，课程名，教师，成绩

函数依赖（学号，课程号）→姓名 是部分函数依赖

学号 姓名 班号 地址 教师
01001 王丽 01 河北省石家庄市裕华路 刘峰
01002 张勇 01 河北省保定市裕华路 刘峰
01003 李江 01 河北省邢台市新华路 刘峰

第二范式还是存在数据冗余，操作异常情况

（3）第三范式：如果关系R(U)中的每一个非主属性都不传递函数依赖于R(U)的任一候选码，则R(U)∈3NF

学号 姓名 班号 班级 教师 成绩
01001 王丽 01 21级计算机网络技术 刘峰 80
01002 张勇 01 21级计算机网络技术 刘峰 75
01003 李江 01 21级计算机网络技术 刘峰 86

候选码：学号

非主属性：姓名，班号，班号，教师，成绩

学号→班号，班号→班级 所以 学号→班级 是传递函数依赖

数据库规范化实例

规范化理论

​ 利用规范化力量来改造关系模式，通过分解关系模式来消除其中不合适的数据依赖，以解决插入异常、删除异常、更新异常和数据冗余问题。

解决之道：分解、分解、再分解！

让一个关系模式尽量只描述一个概念、一个实体或一种实体之间的联系，概念单一！

例如：

学号 姓名 性别 年龄 班级编号 班级名称 课程编号 课程名 成绩
00101 王丽 女 20 001 22级计算机班 C001 C语言 80
00201 张勇 男 18 002 22级会计班 C010 经济法 75
00102 李江 男 19 001 22级计算机班 C002 数据结构 86

候选码：（学号，课程编号）

（学号，课程号）→姓名 是部分函数依赖

学号→班级编号 班级编号→班级名称是传递函数依赖

数据库规范化实例——关系分解

1NF→2NF：去掉部分依赖分解过程

学号 姓名 性别 年龄 班级编号 班级名称 课程编号 课程名 成绩
00101 王丽 女 20 001 22级计算机班 C001 C语言 80
00201 张勇 男 18 002 22级会计班 C010 经济法 75
00102 李江 男 19 001 22级计算机班 C002 数据结构 86

分解方法：候选码属性集合的每个子集，用它作为主键，将依赖于此主键的属性放置到此关系模式中，构成一个新关系模式

候选码：（学号，课程编号）

子集：学号，课程编号，（学号，课程编号）

学生（学号，姓名，性别，年龄，班级编号，班级名称）

课程（课程编号，课程名称）

选课（学号，课程编号，成绩）

1NF➡️2NF

学生（学号，姓名，性别，年龄，班级编号，班级名称）

候选码：学号

学号→班级编号 班级编号→班级名称是传递函数依赖

分解方法：对于不是候选码的每个决定因子（班级编号），新建一个关系模式，将决定因子作为新关系模式的主键，在新关系模式中包含在原关系模式中所有依赖于该决定因子的属性（班级名称），从原关系模式中删去依赖于它的所有属性（班级名称）。

学号→班级编号 班级编号→班级名称是传递函数依赖

学生（学号，姓名，性别，年龄，班级编号，班级名称）

学生（学号，姓名，性别，年龄）

班级（班级编号，班级名称）

2NF→3NF：去掉传递依赖分解过程

2NF→3NF

学生（学号，姓名，性别，年龄）

班级（班级编号，班级名称）

课程（课程编号，课程名称）

选课（学号，课程编号，成绩）

规范化和性能的关系

为满足某种商业目标，数据库性能比规范化数据库更重要

  • 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间
  • 通过在给定的表中插入计算列（如成绩总分），以方便查询

进行规范化的同时，还需要综合考虑数据库的性能。

总结

在需求分析阶段，设计数据库的一般步骤为：

​ 收集信息

​ 标识对象

​ 标识每个对象的属性

​ 标识对象之间的关系

在概要设计阶段和详细设计阶段，设计数据库的步骤为：

​ 绘制E-R图

​ 将E-R图转换为表格

​ 应用三大范式规范化表格

为了设计结构良好的数据库，需要遵守一些专门的规则，称为数据库的设计范式。

​ 第一范式（1NF）的目标：确保每列的原子性。

​ 第二范式（2NF）的目标：确保表中的每列，都和主键相关 。

​ 第三范式（3NF）的目标：确保每列都和主键列直接相关，而不是间接相关 。

数据定义语言—DDL

MySQL数据库文件介绍

数据库管理的核心任务包括创建、操作和支持数据库。在MySQL中，每个数据库都对应存放在一个与数据库同名的文件夹中。

MySQL数据库文件有“.frm”、“.MYD”和“.MYI”3种文件，其中“.frm”是描述表结构的文件，“.MYD”是表的数据文件，“.MYI”是表数据文件中的索引文件。它们都存放在与数据库同名的文件夹中。

MySQL数据库模型

  • 在数据库服务器中可以存储多个数据库文件，所以建立数据库时要设定数据库的文件名，每个数据库有惟一的数据库文件名作为与其它数据库区别的标识。
  • 数据库文件： 数据库是由相关数据表组成，一个数据库包括多个数据表。数据库文件用于记录数据库中数据表构成的信息。
  • 数据库只能由得到授权的用户访问，这样保证了数据库的安全。
MySQL数据库模型

数据库和表

数据库可以看成是一个存储数据对象的容器，这些数据对象包括表、视图、触发器、存储过程等，其中，表是最基本的数据对象，用以存放数据库的数据。

必须首先创建数据库，然后才能创建数据库的数据对象。

MySQL可以采用两种方式：

创建、操作数据库和数据对象

（1）命令行方式

（2）图形界面工具方式

数据库

MySQL自动建立的数据库介绍

​ MySQL安装完成之后，将会在其data目录下自动创建information_schema、mysql、performance_schema、sys四个系统数据库。

​ 可以使用SHOW DATABASES命令来查看当前所有存在的数据库。

创建数据库

MySQL安装后，系统自动地创建information_scema和MySQL数据库，MySQL把有关数据库的信息存储在这两个数据库中。如果删除了这些数据库，MySQL就不能正常工作。

对于用户的数据，需要创建新的数据库来存放。

1
2
3
4
5
6
7
8
9
-- 语法格式：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] 数据库名
        [ [DEFAULT] CHARACTER SET 字符集名 | [DEFAULT] COLLATE 校对规则名]

-- 说明：语句中“[ ]”内为可选项。
-- {  |  }表示二选一。
-- 例如：
Create database bookstore;

在SQL语言创建数据库命令create database中，如果省略语句中“[ ]”中的所有可选项，其结构形式如下：

1
CREATE DATABASE 数据库名;

MySQL数据库命名规则：

  • 不能与其他数据库重名
  • 名称可以由任意阿拉伯数字、字母、下划线和“$”组成，可以使用以上任意字符头，但不能使用单独的数字，否则会与其他数字混淆
  • 名称最长64个字符
  • 不能使用MySQL关键字
  • 默认情况下，Windows对数据库大小写不敏感，而在Linux下对大小写是敏感的，为了数据库在不同平台下移植，建议使用小写数据库名。
1
2
-- 【例3.1】 创建一个名为Bookstore的数据库。
CREATE DATABASE Bookstore;

显示数据库命令

显示服务器中已建立的数据库，使用SHOW DATABASES;命令。

1
2
3
-- 语法格式：
SHOW DATABASES;

此命令没有用户变量，执行“SHOW DATABASES;” 。

注意：在MySQL中，每一条SQL语句都以“;”作为结束标志。

打开数据库

因为MySQL服务器中有多个数据库，可以使用USE命令可指定当前数据库。

1
2
-- 语法格式：
USE 数据库名;

说明：这个语句也可以用来从一个数据库“跳转”到另一个数据库，在用CREATE DATABASE语句创建了数据库之后，该数据库不会自动成为当前数据库，需要用这条USE语句来指定。

1
2
-- 【例】将数据库test1设置为当前数据库。
Use test1;

修改数据库

数据库创建后，如果需要修改数据库的参数，可以使用ALTER DATABASE;命令。

1
2
3
4
5
6
7
8
9
-- 语法格式：
ALTER {DATABASE | SCHEMA} [数据库名]
               [[DEFAULT] CHARACTER SET 字符集名 | [DEFAULT] COLLATE 校对规则名]

-- 【例3.3】 修改数据库Pet的默认字符集为latin1，校对规则为latin1_swedish_ci。
ALTER DATABASE Pet
DEFAULT CHARACTER SET latin1
DEFAULT COLLATE  latin1_swedish_ci;

删除数据库

已经创建的数据库需要删除，使用DROP DATABASE命令。

1
2
3
4
5
-- 语法格式：
DROP DATABASE  [IF EXISTS] 数据库名;

-- 例如，数据库test2命令如下：
DROP DATABASE  test2;

MySQL数据类型

  • 确定表中每列的数据类型是设计表的重要步骤。
  • 列的数据类型就是定义该列所能存放的数据的值的类型。
字符串类型 数值类型 时间类型 大数据类型
Char(n) int date blob
Varchar(n) Float(M,D) time text
datetime

MySQL常用数据类型：

  • 整数类型
  • 浮点数类型和定点数类型
  • 字符串类型
  • 日期时间类型
  • 复合类型

整数类型

在int类型里，MySQL支持tinyint、smallint、mediumint、int、bigint 5种类型，这些类型都是用来存放整形数据的，只不过存储的值的大小范围不一样。

整数类型

浮点数类型和定点数类型

MySQL支持float、double、decimal 3种类型，float是单精度浮点数值、double是双精度浮点数值、decimal是定点数值，如果确定数值总长度以及小数点位数，就可以用decimal定点数值来表示。

浮点数类型和定点数类型

**提示：**存储数值范围越小，精度越高；存储数值范围越大，精度就越不准确。对于既要求精度又固定小数点位数的数值存储，采用decimal（numeric），其优点在于可以自定义小数点位数，精度高。某些特殊情况，如数值范围巨大则只能用float（real）类型了，此类型一般不提倡使用。

字符串类型

MySQL数据库提供了几种基本字符串类型，它们用来存放一个字符、一个字符串、巨大的文本或者二进制字符串数据。

字符串类型

日期时间类型

MySQL数据库提供了5种不同的日期时间类型，用来存储和日期时间相关的数据，每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。

日期时间类型

**提示：**MySQL数据库用date日期类型和datetime日期时间类型来存储日期值，使用time时间类型来存储时间值，使用year来存放年份值，date 类型的值应该用连字号“-”分隔开，而 time 类型的值应该用冒号“:”分隔开。

如何选取数据类型

数据类型的选择会影响存储空间的开销和数据查询性能，所以在设计数据库的时候，应该为存储的数据选择正确的存储数据类型。

可以遵循以下原则来选取数据类型：

（1）大小合适就是最好的，如存放姓名的字段类型，设置合适长度的字符串就可以，而不应该设置得非常大（65 535），因为这样可以用更少的磁盘容量、CPU缓存，大大减少了I/O开销。

（2）简单存放就好，如存放一个简单而又短小的字符串，可以选取varchar类型，而不应该选取text或者blob类型，简单的数据类型操作通常需要更少的CPU周期。

（3）尽量避免使用NULL，NULL是列默认的属性，通常我们要指定为NOT NULL。有NULL的列值会使得索引、索引统计和值比较更加复杂。

（4）数据类型的选择还应该考虑数据操作和应用处理的要求，如某些类型更适合于加快数据操作而不是其他方面。

（5）如果想用不同的标准对一列中所有的值进行排序，那么我们需要选择一种能够有效执行这些功能并且带有最大限度灵活性的类型。对相应字段添加索引，能够加快检索速度。需要保证使用的数据类型支持比较运算，并且相互兼容。

char和varchar

​ char是固定长度的，查询速度比varchar快得多。char的缺点是浪费存储空间。检索char列时，返回的结果会删除尾部空格，所以程序需要对空格进行处理。

​ 如果列中要存储的数据的长度差不多是一致的，则应该考虑使用char，否则应该考虑使用varchar；如果列中最大数据的长度小于50Byte，则一般也考虑使用char(当然，如果这个列很少用，则基于节省空间和减少I/O的考虑，还是可以使用varchar)；一般不宜定义大于50Byte的char类型列。

text和blob

​ 在保存大文本时，通常选择text或者blob。二者的差别是blob可以保存二进制数据，如照片。text和blob又包括text、mediumtext、longtext和blob、mediumblob、longblob，它们之间的区别是存储文本长度不同和存储字节不同。在不必要的时候避免检索大型的blob或text值。把blob或text列分离到单独的表中。

浮点数和定点数

​ 浮点数存在误差问题，因此对于货币等对精度敏感的数据，应该用定点数表示或存储。在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；要注意一些特殊值的处理。

​ decimal用于存储精确数据，而float只能用于存储非精确的数据，故精确数据最好使用decimal类型；由于float的存储空间的开销一般比decimal小（精确到7位小数只需要4个字节，而精确到15位小数只需要8个字节），故非精确数据类型建议使用float。

日期类型的选择

​ 根据实际需要选择能够满足应用的最小存储日期类型。如果记录年、月、日、时、分、秒，并且记录年份比较久远，则最好使用datetime，不要使用timestamp；如果记录的日期需要让不同时区的用户使用，则最好使用timestamp，因为日期类型中只有它能够和实际时区相对应。

创建数据表

为数据库创建数据表，可以使用SQL命令CREATE TABLE来完成

1
2
3
4
5
6
-- 语法格式：
CREATE TABLE [IF NOT EXISTS] tbl_name
      （列名1  数据类型1  [NOT NULL | NULL] [DEFAULT 列默认值]…，
         列名2  数据类型 2 [NOT NULL | NULL] [DEFAULT 列默认值]…
       ）
       ENGINE = 存储引擎；
1
-- 【例】命令行SQL语句方式创建数据库表book
字段名 字段类型 宽度 是否为空 默认值 说明
图书编号 char 10 x 主键，采用10位字符编号
类别 char 8 x 计算机 2-4个汉字
书名 varchar 40 x 不超过40个字符
出版时间 date x
单价 Float 5,2 √ 元为单位，精确到分
数量 int √ 
1
2
3
4
5
6
7
8
9
10
-- 命令如下：
CREATE TABLE  book
 (
       图书编号  char(10)   NOT NULL,
       类别  varchar(8)  NOT NULL  DEFAULT  '计算机',
       书名       varchar(40)  NOT  NULL ,
       出版时间   date        NOT  NULL ,
       单价      float(5,2)    NULL ,
       数量      int   
);

显示数据表文件名

1
2
3
4
5
6
-- 语法格式： 
SHOW TABLES;

-- 【例】显示Bookstore 数据库建立的数据表文件:
USE Bookstore;  
SHOW TABLES;

显示数据表结构

1
2
3
4
5
6
7
8
9
-- 语法格式(DESC是DESCRIBE的简写，二者用法相同。)：
{DESCRIBE | DESC} 表名 [列名 | 通配符 ]

-- 【例】 用DESCRIBE语句查看 Book 表的列的信息。
USE Bookstore;
DESCRIBE Book;
-- 【例】 查看 Book 表图书编号列的信息。
USE Bookstore;
DESC Book 图书编号;

修改表

**ALTER TABLE**用于更改原有表的结构。例如，可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表的评注和表的类型。

1
2
3
4
5
6
7
8
9
-- 语法格式：
ALTER TABLE 表名
  ADD [COLUMN] 列定义  [FIRST | AFTER列名]                	 /*添加列*/	
  | ALTER [COLUMN] 列名 {SET DEFAULT 默认值| DROP DEFAULT} 	 /*修改默认值*/
  | CHANGE [COLUMN] 旧列名 列定义    [FIRST|AFTER列名]		  /*对列重命名*/             
  | MODIFY [COLUMN] 列定义 [FIRST | AFTER 列名]		 	    /*修改列类型*/
  | DROP [COLUMN] 列名									/*删除列*/
  | RENAME [TO] 新表名	     							    /*重命名该表*/

增加列

1
2
3
4
5
6
7
8
-- 语法格式
ALTER TABLE  表名   ADD  列定义  [FIRST | AFTER  列名];

-- 【例】在表book中增加一列“浏览次数”。
ALTER TABLE book  ADD 浏览次数 int NULL;
-- 【扩展】在表book中增加一列“浏览次数”，位置在单价列的后面。
ALTER TABLE book  ADD 浏览次数 int NULL after 单价;

删除列

1
2
3
4
5
6
7
-- 语法格式
ALTER TABLE  表名  DROP 列名

-- 【例】删除book表中“浏览次数”列。
ALTER TABLE book DROP 浏览次数;
-- 【扩展】在表book中增加“浏览次数”列，删除“封面图片”列。
ALTER TABLE book  ADD 浏览次数 int NULL, DROP 封面图片;

更改列名

1
2
3
4
5
6
-- 语法格式
ALTER TABLE  表名  CHANGE  旧列名  列定义 

-- 【例】将book表中“出版时间”列改为“出版日期”列。
ALTER TABLE book  CHANGE 出版时间 出版日期 date not null;

更改列类型

1
2
3
4
5
6
-- 语法格式
ALTER TABLE  表名  MODIFY 列定义

-- 【例】将book表中“出版日期”列类型改为日期时间类型。
ALTER TABLE book MODIFY 出版日期 datetime not null;

总结

ALTER TABLE用于更改原有表的结构。可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表完整性约束和表的类型等。

1
2
3
4
增加列：ALTER TABLE  表名  ADD  列定义
删除列：ALTER TABLE  表名  DROP 列名
更改列名：ALTER TABLE  表名  CHANGE  旧列名  列定义 
更改列类型：ALTER TABLE  表名  MODIFY 列定义

复制表

  • 使用LIKE关键字创建一个与参照表相同结构的新表，但内容不会复制，因此创建的新表是一个空表。
  • 使用AS关键字可以复制表的内容，但索引和完整性约束是不会复制的。
1
2
3
4
5
6
-- 语法格式如下：
CREATE TABLE [IF NOT EXISTS] 新表名
     [ LIKE 参照表名 ] | [AS (select语句)]	

-- 【例】 假设数据库BookStore中有一个表Book，创建Book表的一个名为book_copy1的拷贝。
CREATE TABLE book_copy1 LIKE Book;

删除表

这个命令将表的描述、表的完整性约束、索引及和表相关的权限等都全部删除。

1
2
3
4
5
6
-- 语法格式如下：
DROP  TABLE [IF EXISTS] 表名1 [,表名2 ] ...

-- 【例】 删除表test。
USE XSCJ  DROP  TABLE  IF  EXISTS  test ;

数据完整性

数据完整性有以下几个方面：

  • 实体完整性
  • 域完整性
  • 参照完整性
  • 用户定义的完整性
完整性类型 约束类型 描 述 约束对象
列完整性 Default 当使用INSERT语句插入数据时，若已定义默认值的列没有提供指定值，则将该默认值插入记录中 列
列完整性 Check 指定某一列可接受的值 列
实体完整性 Primary Key 每行记录的惟一标识符，确保用户不能输入重复值，并自动创建索引，提高性能，该列不允许使用空值 行
实体完整性 Unique 在列集内强制执行值的惟一性，防止出现重复值，表中不允许有两行的同一列包含相同的非空值 行
参考完整性 Foreign Key 定义一列或几列，其值于本表或其他表的主键或UNIQUE列相匹配 表与表之间

提示：

使用SQL语句给数据表添加约束有两种方式：

  • 一是在创建数据表时，将约束语句添加在相应字段或数据表中。
  • 二是在已有的数据表中，使用ADD 语句将约束添加在相应的字段或数据表中。

主键约束——primary key

  • 主键就是表中的一列或多个列的一组，其值能唯一地标志表中的每一行。
  • 通过定义PRIMARY KEY约束来创建主键，而且PRIMARY KEY约束中的列不能取空值。
  • 如果 PRIMARY KEY 约束是由多列组合定义的，则某一列的值可以重复，但 PRIMARY KEY 约束定义中所有列的组合值必须唯一。
  • 可以用两种方式定义主键：作为列或表的完整性约束。作为列的完整性约束时，只需在列定义的时候加上关键字PRIMARY KEY。作为表的完整性约束时，需要在语句最后加上一条PRIMARY KEY（col_name，…）语句。

语法格式

1
2
3
4
5
6
7
8
9
10
11
-- 1、创建数据表添加主键约束的语法格式为：
CREATE  TABLE  tb_name    (
 列名  数据类型  NOT NULL PRIMARY KEY/*列级*/
| PRIMARY KEY(列名1[,列名2]); /*表级*/

-- 2、在已有数据表添加主键约束的语法格式为：
ALTER  TABLE  tb_name ADD PRIMARY KEY(列名1[,列名2]);

-- 3、删除主键约束的语法格式为：
ALTER  TABLE  tb_name DROP PRIMARY KEY;

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- （1）列的完整性约束：

-- 【例】 创建表book2，将编号定义为主键。
CREATE TABLE book2
    (  编号 char(6)  NOT NULL PRIMARY KEY,
       书名 char(10)  NOT  NULL,
       数量 int );


-- （2）表的完整性约束：

-- 【例】在已有的cj表，将学号和课程号定义为复合主键。ALTER   TABLE cj
ADD PRIMARY  KEY (学号, 课程号);

原则上，任何列或者列的组合都可以充当一个主键。但是主键列必须遵守一些规则。

（1）每个表只能定义一个主键。

（2）关系模型理论要求必须为每个表定义一个主键。

（3）唯一性原则：表中的两个不同的行在主键上不能具有相同的值。

（4）最小化规则：如果从一个复合主键中删除一列后，剩下的列构成主键仍然满足唯一性原则，那么，这个复合 主键是不正确的，这条规则称为最小化规则。

MySQL自动地为主键创建一个索引。通常，这个索引名为PRIMARY。然而，可以重新给这个索引起名。

1
2
3
-- 【例】 删除Book表上的主键约束。
ALTER TABLE Book;	
DROP PRIMARY KEY;

唯一约束（替代键约束）——unique

语法格式

1
2
3
4
5
6
7
8
9
-- 1、创建数据表添加唯一约束的语法格式为：
CREATE  TABLE  tb_name (列名  数据类型  [NULL|NOT NULL ] UNIQUE,……);

-- 2、在已有数据表添加唯一约束的语法格式为：
ALTER  TABLE  tb_name ADD UNIQUE(列名);

-- 3、删除唯一约束的语法格式为：
ALTER  TABLE  tb_name DROP INDEX 约束名;

在关系模型中，唯一约束像主键一样，是表的一列或一组列，它们的值在任何时候都是唯一的。替代键是没有被选做主键的候选键。定义替代键的关键字是UNIQUE。

1
2
3
4
5
6
7
8
9
10
11
-- 【例】 创建表book_copy1中将图书编号作为主键，书名列定义为唯一约束。
CREATE TABLE book_copy1
   (  图书编号 varchar(20) NOT NULL primary key,
    书名     varchar(20) NOT NULL UNIQUE,
    出版日期 date NULL  );

-- 【例】假设已有Book表中主键未设定，给图书编号添加主键约束,给书名添加唯一性约束。
ALTER TABLE Book	
ADD PRIMARY KEY(图书编号), 
ADD UNIQUE u_idx (书名) ;

  • 这个例外键中，既包括主键约束, 也包括唯一性约束，说明MySQL可以同时创建多个约束。记住，使用PRIMARY KEY的列，必须是一个具有NOT NULL属性的列。
  • 如果想要查看表中创建的约束的情况，可以使用语句，
  • 例如：SHOW INDEX FROM book;
1
2
3
-- 【例】 删除Book表上的唯一约束。
ALTER TABLE Book DROP index u_idx ;

在MySQL中替代键和主键的区别主要有以下几点。

  1. 一个数据表只能创建一个主键。但一个表可以有若干个UNIQUE键，并且它们甚至可以重合，例如，在C1和C2列上定义了一个替代键，并且在C2和C3上定义了另一个替代键，这两个替代键在C2列上重合了，而MySQL允许这样。
  2. 主键字段的值不允许为NULL，而UNIQUE字段的值可取NULL，但是必须使用NULL或NOT NULL声明。
  3. 一般创建PRIMARY KEY约束时，系统会自动产生PRIMARY KEY索引。创建UNIQUE约束时，系统自动产生UNIQUE索引。

外键约束——foreign key

语法格式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 1、创建表的同时创建外键
CREATE TABLE 表名( 列名, ... 
       FOREIGN KEY (列名)  REFERENCES 表名 [(列名 [(长度)] [ASC | DESC],...)]
       [ON DELETE  {RESTRICT | CASCADE | SET NULL | NO ACTION}]
       [ON UPDATE  {RESTRICT | CASCADE | SET NULL | NO ACTION}] ）;

-- 2、对已有表创建外键                
ALTER TABLE 表名  
ADD FOREIGN KEY (列名)  REFERENCES 表名 [(列名 [(长度)] [ASC | DESC],...)]
       [ON DELETE  {RESTRICT | CASCADE | SET NULL | NO ACTION}]
       [ON UPDATE  {RESTRICT | CASCADE | SET NULL | NO ACTION}]

-- 3、删除外键约束                
ALTER  TABLE  tb_name DROP FOREIGN KEY 约束名;


参数解释：

  • RESTRICT：当要删除或更新主键中被参照列上在外键中出现的值时， 拒绝对主键的删除或更新操作。如：要当要删除xs表中081102记录时，因为cj中还有081102记录，拒绝对xs表的删除操作。
  • CASCADE：从主键删除或更新行时自动删除或更新外键表中匹配的行。如：从xs表更新081102学号为801102时自动更新cj表中学号为081102行为801102 。
  • SET NULL：当从主键表删除或更新行时，设置外键表中与之对应的外键列为NULL。如：当从xs表删除081103行时，设置cj表中081103项为NULL。
  • NO ACTION：NO ACTION意味着不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新主键表中主要键值的企图不被允许，和RESTRICT一样。
  • SET DEFAULT：作用和SET NULL一样，只不过SET DEFAULT是指定外键表中的外键列为默认值。
  • 如果没有指定动作，两个参照动作就会默认地使用RESTRICT。

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 【例】 创建带有参照动作CASCADE的book_ref1表。
CREATE TABLE book_ref1   (  
     图书编号 　 varchar(20) NULL,
     书名　　  　varchar(20) NOT NULL,
     出版日期 　 date NULL,
     PRIMARY KEY (书名),
     FOREIGN KEY (图书编号)
     REFERENCES Book (图书编号)
     ON  UPDATE  CASCADE
    )ENGINE=INNODB;

-- 这个参照动作的作用是在主表更新时，外键表产生连锁更新动作，有些人称它为“级联”操作。举例说，如果book表中有一个图书编号为“TP.2525”的值修改为“TP.2525-1”，则book_ref1表中的“图书编号”列中的值“TP.2525”相应地改为“TP.2525-1”。

-- 通过修改添加
-- 【例】  在网络图书销售系统中，只有会员才能下订单。因此Sell表中的所有用户号也必须出现在Members表的用户号列中。定义参照完整性约束来实现这种约束。
 ALTER TABLE sell 
 ADD FOREIGN KEY (用户号) 
 REFERENCES members (用户号) 
 ON DELETE CASCADE 
 ON UPDATE CASCADE;


检查约束——check

主键、替代键、外键都是常见的完整性约束的实例。但是，每个数据库都还有一些专用的完整性约束。

例如，KC表中星期数要在1～7之间，XS表中出生日期必须大于1986年1月1日。这样的规则可以使用CHECK完整性约束来指定。

CHECK完整性约束在创建表的时候定义。可以定义为列完整性约束，也可以定义为表完整性约束。

语法格式

1
2
3
4
5
6
7
8
9
10
11
-- 1、创建数据表添加检查约束的语法格式为：
CREATE  TABLE  tb_name    (
 列名  数据类型  [NULL |NOT NULL]…… CHECK(expr),列级| CHECK(expr) );表级

-- 2、在已有数据表添加检查约束的语法格式为：
ALTER  TABLE  tb_name ADD CHECK(expr);

-- 3、删除检查约束的语法格式为：
ALTER  TABLE  tb_name DROP CHECK 约束名;


说明：expr是一个表达式，指定需要检查的条件，在更新表数据的时候，MySQL会检查更新后的数据行是否满足CHECK的条件。

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 【例】 创建表student，只考虑学号和性别两列，性别只能包含男或女。
CREATE  TABLE  student    (
    学号 char(6) NOT NULL,
    性别 char(2) NOT NULL CHECK(性别 IN ('男', '女'))
     );
-- 这里CHECK完整性约束指定了性别允许哪个值，由于CHECK包含在列自身的定义中，所以CHECK完整性约束被定义为列完整性约束。


-- 【例】创建student3表，有学号，最好成绩，平均成绩3列，要求最好成绩必须大于平均成绩。
 CREATE  TABLE  student3    (
    	学号 char(6)    NOT NULL,
    	最好成绩 int NOT NULL,
    	平均成绩 int NOT NULL,
    	check(最好成绩>平均成绩); 
-- 如果指定的完整性约束中要相互比较一个表或者两个或者多个列，那么该列的完整性约束必须定义为表完整性约束。

-- CHECK约束可以同时定义多个，中间用逗号隔开。
-- 【例】 修改book表，单价必须大于等于0，折扣在0.1-1之间。
alter table book
	add check(单价>0),
	add check(折扣>=0.1 and 折扣<=1);

默认约束——default

默认约束的作用是给列设置一个默认值，在缺省录入的情况下，会自动填充默认值。

语法格式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 1、创建数据表添加默认约束的语法格式为：
CREATE  TABLE  tb_name    (
 列名  数据类型  [NULL |NOT NULL]  default  ‘列默认值’,……);

-- 2、在已有数据表添加和修改默认约束的语法格式为：
（1）添加默认约束
alter table 表名 modify 列名 数据类型 default ‘默认值’;
（2）修改默认值
alter table 表名 alter column 列名 set default ‘默认值’;

-- 3、删除默认约束的语法格式为：
alter table 表名 alter column 列名 drop default；


案例

1
2
3
4
5
6
7
8
9
10
11
12
-- 【例】book表中将折扣设置默认值为0.8折
alter table book
modify 折扣 decimal(2,1) default 0.8;

-- 【例】book表中将折扣默认值修改为0.7折
alter table book
alter column 折扣 set  default 0.7;

-- 【例】删除book表折扣列的默认约束
alter table book
alter column 折扣 drop default;

查看表相关信息

格式

1
show create table 表名;

这个命令可以查看表的所有信息，包括一些字段类型，字段的约束，外键，主键，索引，字符编码等等

删除完整性约束

如果使用一条DROP TABLE语句删除一个表，所有的完整性约束都自动被删除了,被参照表的所有外键也都被删除了。

使用ALTER TABLE语句，完整性可以独立地被删除，而不用去删除表本身。

案例

1
2
3
4
5
-- 【例】 删除表book的主键，删除sell表的外键，删除book表的单价check约束。
ALTER TABLE book DROP PRIMARY KEY;
ALTER TABLE sell DROP FOREIGN KEY sell_ibfk_1;
ALTER TABLE book DROP check book_chk_1;

归纳总结

归纳总结：使用ALTER TABLE管理约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 1、显示表中的约束
show keys from 表名;

-- 2、主键约束
（1）添加主键约束
alter table 表名add primary key (列名)；
（2）删除主键约束
alter table  表名 drop primary key

-- 3、唯一约束
（1）添加唯一约束
alter table  表名 add unique (列名)；
（2）删除唯一约束
alter table  表名 drop index 约束名；

-- 4、外键约束
（1）添加外键约束
alter table 外键表 add foreign key (外键) 
references 主键表(主键);
（2）删除外键约束
alter table 外键表  drop foreign key  约束名; 
          
-- 5、默认约束
（1）添加默认约束
alter table 表名 modify 列名 数据类型 default ‘默认值’;
（2）修改默认值
alter table 表名 alter column 列名 set default ‘默认值’;
（3）删除默认约束
alter table 表名 alter column 列名 drop default；

-- 6、检查约束
（1）添加检查约束
alter table 表名 add check(约束表达式);
（2）删除检查 约束
alter table 表名 drop check 约束名

练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- 实训3-3 SQL语句修改数据表实训
-- 使用SQL语句实现下列操作(P84)
-- 1、创建schooldb数据库
create database schooldb;

-- 2、创建schooldb各表
CREATE TABLE class  (
  班级编号 char(6) NOT NULL PRIMARY KEY,
  班级名称 varchar(20) NOT NULL,
  院系 varchar(30)  NOT NULL,
  年级 int NULL ,
  人数 int NULL
) ;
CREATE TABLE course  (
  课程号 char(6) NOT NULL PRIMARY KEY,
  课程名 varchar(20) NOT NULL,
  学分 int NOT NULL,
  学时 int NOT NULL,
  学期 char(2)  NULL ,
  前置课 char(6) NULL 
 );
CREATE TABLE score  (
  学号 char(10)  NOT NULL,
  课程号 char(6)  NOT NULL,
  成绩 float(5,2) NULL,
  PRIMARY KEY (学号, 课程号) 
);
CREATE TABLE student  (
  学号 char(10) NOT NULL PRIMARY KEY ,
  姓名 varchar(20) NOT NULL,
  性别 char(2)  NOT NULL,
  出生日期 date NULL,
  地区 varchar(20) NULL ,
  民族 varchar(10)  NULL DEFAULT '汉',
  班级编号 char(6)  NULL 
);

-- 3、查看schooldb中的表文件
show tables;

-- 4、查看course表结构
desc course;

-- 5、在score表中增加一列备注 char（50）
alter table score add 备注 char(50);

-- 6、将score表中的备注修改为varchar（50）
alter table score modify 备注 varchar(50);

-- 7、将备注名称修改为BZ 
alter table score change 备注 BZ varchar(50);

-- 8、删除在score表BZ列
alter table score drop BZ;

-- 9、复制class表命名为classcopy，只复制表结构
create table classcopy like class;

-- 10、将classcopy表重命名为classcopy1
rename table classcopy to classcopy1;

-- 11、删除classcopy1表
drop table classcopy1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 实训3-4 数据完整性实训
-- /**/在各表中添加相应约束
-- （1）class表班级编号设置为主键。
alter table class add primary key(班级编号);

/** （2）student表学号设置为主键；
       姓名设置唯一约束
       班级编号设置为外键；
       民族设置默认约束，默认为‘汉’；
       性别设置默认约束，默认为‘男’，设置检查约束只能输入‘男’或‘女’。**/
alter table student add primary key(学号);
alter table student add foreign key(班级编号) references class(班级编号);
alter table student modify 民族 char(10) default '汉';
alter table student modify 性别 char(2) default '男',add check(性别 in('男','女'));

-- （3）course表课程号设置为主键；
--          前置课设置为外键。
alter table course add primary key(课程号), ADD FOREIGN KEY (前置课) REFERENCES course (课程号);
alter table course ADD FOREIGN KEY (前置课) REFERENCES course (课程号);

-- （4）score表（学号，课程号）设置为主键；
--          学号，课程号分别设置为外键；
--         成绩设置检查约束有效成绩在0--100分。
alter table score add primary key(学号,课程号);
alter table score add foreign key(学号) references student(学号),add foreign key(课程号) references course(课程号);
alter table score add check(成绩 between 0 and 100);

数据操作语言—DML

DML是指数据操作语言，英文全称是Data Manipulation Language，用来对数据库中表的数据记录进行更新（增删改）。

关键字：

  • 插入insert
  • 删除delete
  • 更新update

数据插入

一旦创建了数据库和表，下一步就是向表里插入数据。通过INSERT或REPLACE语句可以向表中插入一行或多行数据。

语法格式

1
2
3
INSERT [IGNORE] [INTO] 表名[(列名,...)]
             VALUES ({表达式| DEFAULT},..),(...),...
            | SET列名={表达式| DEFAULT}, ...

如果要给全部列插入数据，列名可以省略。如果只给表的部分列插入数据，需要指定这些列。对于没有指出的列，它们的值根据列默认值或有关属性来确定，MySQL处理的原则是：

（1）具有IDENTITY属性的列，系统生成序号值来唯一标志列。

（2）具有默认值的列，其值为默认值。

（3）没有默认值的列，若允许为空值，则其值为空值；若不允许为空值，则出错。

（4）类型为timestamp的列，系统自动赋值。

VALUES子句：包含各列需要插入的数据清单，数据的顺序要与列的顺序相对应。若tb1_name后不给出列名，则在VALUES子句中要给出每一列（除IDENTITY和timestamp类型的列）的值，如果列值为空，则值必须置为NULL，否则会出错。VALUES子句中的值：

（1）表达式：可以是一个常量、变量或一个表达式，也可以是空值NULL，其值的数据类型要与列的数据类型一致。例如，列的数据类型为int，插入的数据是‘aaa’就会出错。当数据为字符型时要用单引号括起。

（2）DEFAULT：指定为该列的默认值。前提是该列原先已经指定了默认值。

如果列清单和VALUES清单都为空，则INSERT会创建一行，每个列都设置成默认值。

1
2
3
-- 插入语句最常用格式：
INSERT INTO  表名(列名,...) VALUES (表达式, ...)

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 【例】 向Bookstore数据库中的表Book中插入如下的一行数据：
USE Bookstore;
INSERT INTO book VALUES ('TP.9501', '计算机', 'Dreamwearer', '鲍里嘉','高等教育出版社','2020-08-16', 33.25,50,0.8, NULL );

-- 【例】 若表Book中图书类别的默认值为“计算机”，数量默认值为NULL，插入上例那行数据。
INSERT INTO book (图书编号,书名,作者,出版社,出版时间,单价,数量,折扣 )
       VALUES ( 'TP.9501',  'Dreamwearer',  '鲍里嘉', '高等教育出版社', '2020-08-16', 33.25, 50, 0.8);

-- 与上列命令效果相同：
 INSERT INTO book 
           SET 图书编号='TP.9501', 书名='Dreamwearer', 
           图书类别=DEFAULT, 作者= '鲍里嘉', 
           出版社='高等教育出版社', 
           出版时间= '2020-08-16', 单价=33.25, 
           数量=50, 折扣=0.8;

如果例4.1正确执行，记录已经插入了，再执行例4.1的SQL代码，系统提示1062错误，这是因为两条记录的图书编号相同，而图书编号是Book表的主键，要求唯一。当插入第二条相同编号的记录时，系统提示错误，意为表中已有图书编号为6625的记录，第二条记录不能插入。

可以使用REPLACE语句，用第二条记录替换第一条记录：

1
2
3
4
5
REPLACE INTO book 
    VALUES ( 'TP.9501', '计算机', 'PHP网站制作’, 
          '林小红', '高等教育出版社', '2020-10-16’, 
          23.5, 30, 0.8, NULL);

在一个单独的INSERT语句中使用多个VALUES（）子句一次插入多条记录。

1
2
3
4
5
INSERT INTO members  VALUES 
   ('D0138', '李小华','女','123456','13822551234' ,'2020-8-23'),
   ('D0139', '张明','男', '123456','13822555432' ,'2020-9-23');


从上面的SQL代码可以看出，当一次插入多条记录时，每条记录的数据要用（）括起来，记录与记录之间用逗号分开。

数据修改(更新)

要修改表中的一行数据，可以使用UPDATE语句，UPDATE可以用来修改一个表，也可以修改多个表。

格式

1
2
3
4
5
6
7
8
-- 修改单个表，语法格式：
UPDATE [IGNORE]  表名  SET 列名1=表达式1 [,列名2=表达式2 ...][WHERE 条件];

-- 修改多个表，语法格式：
 UPDATE [IGNORE] 表名列表
             SET 列名1=表达式1 [,列名2=表达式2 ...]
             [WHERE 条件];

SET子句：根据WHERE子句中指定的条件对符合条件的数据行进行修改。若语句中不设定WHERE子句，则更新所有行。

可以同时修改所在数据行的多个列值，中间用逗号隔开。

单表案例

1
2
3
4
5
6
7
8
9
-- 【例】 将Bookstore数据库中Book表的所有书籍数量都增加10。将Members表中姓名为“张三”的员工的联系电话改为“13802551234”，密码改为“111111”。
UPDATE Book
    SET 数量 = 数量+10;

UPDATE Members
    SET联系电话 ='13802551234' , 密码 ='111111' 
 WHERE 姓名 = '张三';


多表案例

1
2
3
4
5
-- 【例】订单号为6的客户因某种原因退回2本图书，请在sell表中修改订购册数，同时书退回后，book表中该图书的数量增加4。
UPDATE  sell ,book
                SET  sell.订购册数=订购册数-2 , book.数量=数量+2
                WHERE sell.图书编号=book.图书编号 and sell.订单号='6';

数据删除

使用DELETE语句删除数据

格式
1
2
-- 从单个表中删除，语法格式：
 DELETE [IGNORE] FROM 表名 [WHERE条件];

如果省略WHERE子句则删除该表的所有行。

案例
1
2
3
4
5
6
7
8
-- 【例】 将Bookstore数据库的Members表中姓名为“张三”的员工的记录删除。
USE Bookstore;
DELETE FROM Members  WHERE 姓名='张三';

-- 【例】 将Book表中数量小于5的所有行删除。
USE Bookstore;
DELETE FROM Book  WHERE 数量<5;

使用TRUNCATE TABLE语句删除表数据

使用TRUNCATE TABLE语句将删除指定表中的所有数据，因此也称其为清除表数据语句。

格式
1
2
-- 语法格式：
TRUNCATE TABLE 表名;

说明：由于TRUNCATE TABLE语句将删除表中的所有数据，且无法恢复，因此使用时必须十分小心。

TRUNCATE TABLE 在功能上与不带 WHERE子句的DELETE语句（如DELETE FROM XS）相同，二者均删除表中的全部行。但 TRUNCATE TABLE比DELETE速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。而TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。使用 TRUNCATE TABLE，AUTO_INCREMENT计数器被重新设置为该列的初始值。

练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

/** 数据操作实训：
知识点提示：关系型数据库插入数据时以行为单位提交自动保存，有一个单元格不符合要求则整行记录都不能提交。
插入记录要符合数据类型、长度、约束的要求，尤其是添加约束的表格要考虑数据的插入顺序，删除时也要考虑相应问题。**/

-- 1.在schooldb各表中插入记录 
-- （使用SQL语句在各表中至少插入一行记录，其他记录插入方法不限）
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('AC1301', '会计13-1班', '会计学院', 2013, 35);
INSERT INTO `class` VALUES ('AC1302', '会计13-2班', '会计学院', 2013, 35);
INSERT INTO `class` VALUES ('CS1401', '计算机14-1班', '计算机学院', 2014, 35);
INSERT INTO `class` VALUES ('IS1301', '信息系统13-1班', '信息学院', 2013, NULL);
INSERT INTO `class` VALUES ('IS1401', '信息系统14-1班', '信息学院', NULL, 30);
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('11003', '管理学', 2, 32, '2', NULL);
INSERT INTO `course` VALUES ('11005', '会计学', 3, 48, '2', NULL);
INSERT INTO `course` VALUES ('21001', '计算机基础', 3, 48, '1', NULL);
INSERT INTO `course` VALUES ('21002', 'OFFICE高级应用', 3, 48, '2', '21001');
INSERT INTO `course` VALUES ('21004', '程序设计', 4, 64, '2', '21001');
INSERT INTO `course` VALUES ('21005', '数据库', 4, 64, '4', '21004');
INSERT INTO `course` VALUES ('21006', '操作系统', 4, 64, '5', '21001');
INSERT INTO `course` VALUES ('31001', '管理信息系统', 3, 48, '3', '21004');
INSERT INTO `course` VALUES ('31002', '信息系统_分析与设计', 2, 32, '4', '31001');
INSERT INTO `course` VALUES ('31005', '项目管理', 3, 48, '5', '31001');
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('2013110101', '11003', 90.00);
INSERT INTO `score` VALUES ('2013110101', '21001', 86.00);
INSERT INTO `score` VALUES ('2013110103', '11003', 89.00);
INSERT INTO `score` VALUES ('2013110103', '21001', 86.00);
INSERT INTO `score` VALUES ('2013110201', '11003', 78.00);
INSERT INTO `score` VALUES ('2013110201', '21001', 92.00);
INSERT INTO `score` VALUES ('2013110202', '11003', 82.00);
INSERT INTO `score` VALUES ('2013110202', '21001', 85.00);
INSERT INTO `score` VALUES ('2013310101', '21004', 83.00);
INSERT INTO `score` VALUES ('2013310101', '31002', 68.00);
INSERT INTO `score` VALUES ('2013310103', '21004', 80.00);
INSERT INTO `score` VALUES ('2013310103', '31002', 76.00);
INSERT INTO `score` VALUES ('2014210101', '21002', 93.00);
INSERT INTO `score` VALUES ('2014210101', '21004', 89.00);
INSERT INTO `score` VALUES ('2014210102', '21002', 95.00);
INSERT INTO `score` VALUES ('2014210102', '21004', 88.00);
INSERT INTO `score` VALUES ('2014310101', '21001', 79.00);
INSERT INTO `score` VALUES ('2014310101', '21004', 80.00);
INSERT INTO `score` VALUES ('2014310102', '21001', 91.00);
INSERT INTO `score` VALUES ('2014310102', '21004', 87.00);
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('2013110101', '张晓勇', '男', '1997-12-11', '山西', '汉', 'AC1301');
INSERT INTO `student` VALUES ('2013110103', '王一敏', '女', '0000-00-00', '河北', '汉', 'AC1301');
INSERT INTO `student` VALUES ('2013110201', '江山', '女', '1996-09-17', '内蒙古', '锡伯', 'AC1302');
INSERT INTO `student` VALUES ('2013110202', '李明', '男', '1996-01-14', '广西', '壮', 'AC1302');
INSERT INTO `student` VALUES ('2013310101', '黄菊', '女', '1995-09-30', '北京', '汉', 'IS1301');
INSERT INTO `student` VALUES ('2013310103', '吴昊', '男', '1995-11-18', '河北', '汉', 'IS1301');
INSERT INTO `student` VALUES ('2014210101', '刘涛', '男', '1997-04-03', '湖南', '侗', 'CS1401');
INSERT INTO `student` VALUES ('2014210102', '郭志坚', '男', '1997-02-21', '上海', '汉', 'CS1401');
INSERT INTO `student` VALUES ('2014310101', '王林', '男', '1996-10-09', '河南', '汉', 'IS1401');
INSERT INTO `student` VALUES ('2014310102', '李怡然', '女', '1996-12-31', '辽宁', '汉', 'IS1401');

-- /*2.*/写出完成以下操作的SQL语句
-- /*(1).*/向student表中添加数据“502001, 王晓林, 男, 2000-02-10, 广东, 汉,IS2020”
 insert into student values('502001', '王晓林', '男', '2000-02-10', '广东' , '汉','IS2020');

-- /*(2).*/向student表插入一行数据“500102，林丽，女”
insert into student(学号,姓名,性别) values('500102','林丽','女');

-- /*(3)*/将student表中所有学生的地区字段内容在原来的数据后加上“省或市”3个字
update student set 地区=CONCAT(地区,'（省或市）');

-- /*(4）*/将“王一敏”同学的出生日期改为“1998-02-10”，班级编号改为“AC1302”
update student set 出生日期='1998-02-10',班级编号='AC1302' where 姓名='王一敏';

-- /*(5) */将student表中1997年以前出生的学生记录删除
delete from student where year(出生日期)<1997;

数据查询语言—DQL

单表查询

使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织输出，通过SQL语句的查询可以从表或视图中迅速方便地检索数据。

SELECT语句格式

1
2
3
4
5
6
7
8
9
SELECT [ALL | DISTINCT]   输出列表达式, ...
      [FROM  表名1 [ , 表名2] …]	           	 /*FROM子句*/
      [WHERE 条件]                                    /*WHERE子句*/
      [GROUP BY {列名 | 表达式 | 列编号}
                 [ASC | DESC], ...	          	 /* GROUP BY  子句*/
      [HAVING 条件]  		          	       /* HAVING 子句*/
      [ORDER BY {列名 | 表达式 | 列编号}
                 [ASC | DESC] , ...]                   /*ORDER BY子句*/
       [LIMIT {[偏移量,] 行数|行数OFFSET偏移量}]	   /*LIMIT子句*/

顺序严格地排序。例如，一个HAVING子句必须位于GROUP BY子句之后，并位于ORDER BY子句之前。

选择指定的列

格式
1
2
3
-- 使用SELECT语句选择一个表中的某些列的语法格式如下：
SELECT 字段列表 FROM 表名;

各列名之间要以逗号分隔。

案例
1
2
3
4
5
-- 【例】 查询Members表中各用户的姓名、联系电话和注册时间。
USE Bookstore;
SELECT 姓名,联系电话,注册时间 
FROM Members;

说明：当在SELECT语句指定列的位置上使用*号时，表示选择表的所有列，如要显示Members表中所有列，不必将所有字段名一一列出，使用 SELECT * FROM Members;

定义列别名

当希望查询结果中的某些列或所有列显示时且使用自己选择的列标题时，可以在列名之后使用AS子句来更改查询结果的列别名。

格式
1
2
语法格式为： 
SELECT 字段列表 [AS] 别名;
案例
1
2
3
4
5
6
7
8
-- 【例】 查询Book表中图书类别为计算机的图书书名、作者和出版社，结果中各列的标题分别指定为name、auther和publisher。
SELECT 书名 AS name, 作者 AS auther, 出版社 AS publisher FROM Book WHERE 图书类别= '计算机';

-- 注意：当自定义的列标题中含有空格时，必须使用引号将标题括起来。
SELECT 书名 AS ' Name of  Book', 
作者 AS 'Name  of  Auther', 出版社 AS Publisher
FROM book   WHERE 图书类别= '计算机';

替换查询结果中的数据

在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据。例如查询XS表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。

要替换查询结果中的数据，则要使用查询中的CASE表达式。

格式
1
2
3
4
5
6
7
8
-- 语法格式
CASE
     WHEN 条件1 THEN 表达式1
     WHEN 条件2 THEN 表达式2
     ……
     ELSE 表达式
END

案例
1
2
3
4
5
6
7
8
9
-- 【例】 查询Book表中图书编号、书名和数量，对其库存数量按以下规则进行替换：若数量为空值，替换为“尚未进货”；若数量小于5，替换为“需进货”；若数量在5-50之间，替换为“库存正常”；若总学分大于50，替换为“库存积压”。列标题更改为“库存”。
SELECT 图书编号, 书名,
        CASE 
	WHEN 数量 IS NULL THEN  '尚未进货'
        	WHEN 数量 < 5 THEN  '需进货'
        	WHEN 数量 >=5 and 数量<=50 THEN  '库存正常'
       	ELSE  '库存积压'
         END  AS  库存
FROM Book;

计算列值

使用SELECT对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为结果。

格式
1
2
-- 语法格式
SELECT 表达式1[ , 表达式2… ];
案例
1
2
3
4
5
-- 【例】 对Sell表已发货的记录计算订购金额（订购金额=订购册数*订购单价），并显示图书编号和订购金额。
SELECT  图书编号, 订购册数*订购单价  AS 订购金额
 FROM   Sell   
 WHERE 是否发货= '已发货';

消除结果集中的重复行

对表只选择其某些列时，可能会出现重复行。例如，若对XSCJ数据库的XS表只选择专业名和总学分，则出现多行重复的情况。可以使用DISTINCT或DISTINCTROW关键字消除结果集中的重复行。

格式
1
SELECT DISTINCT 字段列表;

其含义是对结果集中的重复行只选择一个，保证行的唯一性。

案例
1
2
3
-- 【例】 对Book表只选择图书类别和出版社，消除结果集中的重复行。
SELECT DISTINCT 图书类别, 出版社  FROM Book;

运算符

操 作 符
操 作 符 描 述
= 等于
<> 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式

WHERE子句

WHERE子句必须紧跟FROM子句之后，在WHERE子句中，使用一个条件从FROM子句的中间结果中选取行。

格式
1
WHERE 列名 运算符 值;
  • WHERE子句会根据条件对FROM子句的中间结果中的行一行一行地进行判断，当条件为TRUE的时候，一行就被包含到WHERE子句的中间结果中。
  • 在SQL中，返回逻辑值（TRUE或FALSE）的运算符或关键字都可称为谓词。
  • 判定运算包括比较运算、模式匹配、范围比较、空值比较。

比较运算

比较运算符用于比较（除TEXT和BLOB类型外) 两个表达式值，MySQL支持的比较运算符有：=（等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）、<=>（相等或都等于空）、<>（不等于）、!=（不等于）。

当两个表达式值均不为空值（NULL）时，除了“<=>”运算符，其他比较运算返回逻辑值TRUE（真）或FALSE（假）；而当两个表达式值中有一个为空值或都为空值时，将返回UNKNOWN。

1
2
3
4
5
6
-- 【例】 查询Bookstore数据库Book表中书名为“网页程序设计”的记录。
SELECT  书名,单价   FROM  Book WHERE 书名='网页程序设计'; 

-- 【例】 查询Book表中单价大于30的图书情况。
SELECT *   FROM  Book WHERE 单价>30;

逻辑运算

通过逻辑运算符（AND、OR、XOR和NOT）组成更为复杂的查询条件。逻辑运算操作的结果是“1”或“0”，分别表示“true”或“false”。

类型
符号1 符号2 说明 示例 说明
not ! 非运算 !x 如果x是"true"，那么示例的结果是"false"； 如果x是"false"，那么示例的结果是"true"。
or || 或运算 x || y 如果x或y任一是"true"，那么示例的结果是"true"，否则示例的结果是"false"。
and && 与运算 x && y 如果x和y都是"True"，那么示例结果是"true"，否则示例的结果是"false"。
xor ^ 异或运算 x ^y 如果x和y不相同，那么示例结果是"true"，否则示例的结果是"false"。 
1
2
3
4
5
非： select not 0, not 1, not null;
或： select (1 or 0), (0 or 0), (1 or null), (1 or 1), (null or null);
与： select (1 and 1), (0 and 1), (3 and 1), (1 and null);
异或： select (1 xor 1), (0 xor 0), (1 xor 0), (0 xor 1), (null xor 1);

案例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 【例】 查询Sell表中已收货且已结清的订单情况。
SELECT 订单号,是否收货,是否结清 
FROM Sell
WHERE  是否收货='已收货'  AND 是否结清='已结清'; 

-- 【例】查询Book表中清华大学出版社和北京大学出版社出版的价格大于25元的图书。
SELECT 书名,出版社,单价 FROM  Book
WHERE  (出版社='清华大学出版社' OR 出版社='北京大学出版社' )
      AND 单价>=25;
或： 
SELECT书名,出版社,单价   FROM  Book
WHERE  (出版社='清华大学出版社'  AND 单价>=25) 
      OR  (出版社='北京大学出版社'  AND 单价>=25);

模式匹配（LIKE运算符）

LIKE运算符用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、varchar、text、datetime等类型的数据，返回逻辑值TRUE或FALSE。

使用LIKE进行模式匹配时，常使用特殊符号_和%，可进行模糊查询。“%”代表0个或多个字符，“_”代表单个字符。由于MySQL默认不区分大小写，要区分大小写时需要更换字符集的校对规则。

案例
1
2
3
4
5
6
7
8
9
10
11
12
-- 【例】 查询Members表中姓“张”的会员的身份证号、姓名及注册时间。
SELECT 用户号,会员姓名, 注册时间    FROM Members 
 WHERE  会员姓名  LIKE  '张%'; 

-- 【例】查询book表中图书编号倒数第二位为6的图书的图书编号和书名。
SELECT 图书编号, 书名   FROM  book 
 WHERE 图书编号 LIKE  '%6_';

-- 【例】查询Book表中书名中包含下画线的图书。
SELECT 图书编号,书名   FROM   Book
 WHERE 书名 LIKE '%#_%'  ESCAPE  '#';

范围比较

用于范围比较的关键字有两个：BETWEEN和IN。

当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围。

格式
1
表达式 [ NOT ] BETWEEN  表达式1   AND  表达式2;
  • 当不使用NOT时，若表达式的值在表达式表达式1与表达式2之间（包括这两个值），则返回TRUE，否则返回FALSE；使用NOT时，返回值刚好相反。
  • 注意：表达式1的值不能大于表达式2的值。
案例
1
2
3
4
5
-- 【例】 查询Book表中2020年出版的图书的情况。
SELECT  *    FROM Book   
 WHERE 出版时间  BETWEEN '2020-1-1'  AND '2020-12-31';


使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。

1
2
-- 使用IN关键字指定值表的格式为：
表达式 IN (表达式1 [,…n])
1
2
3
-- 【例】查询Book表中“高等教育出版社”、“北京大学出版社”和“人民邮电出版社”出版的图书的情况。
SELECT *  FROM Book  
 WHERE  出版社  IN ( '高等教育出版社', '北京大学出版社', '人民邮电出版社');

空值比较

当需要判定一个表达式的值是否为空值时，使用 IS NULL关键字。格式为：

1
表达式 IS [ NOT ] NULL;

当不使用NOT时，若表达式expression的值为空值，返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。

1
2
3
4
5
6
-- 【例】 查询Sell表中尚未发货的订单记录。
SELECT *   FROM   Sell   WHERE 是否发货 IS NULL;
-- MySQL有一个特殊的等于运算符“<=>”，当两个表达式彼此相等或都等于空值时，它的值为TRUE，其中有一个空值或都是非空值但不相等，这个条件就是FALSE。

-- 上面的语句也可以换成：
SELECT * FROM  Sell   WHERE 是否收货<=>NULL;

分类汇总与排序—聚合函数

SELECT子句的表达式中还可以包含所谓的聚合函数。聚合函数常常用于对一组值进行计算，然后返回单个值。

函 数 名 说 明
COUNT 求组中项数，返回int类型整数
MAX 求最大值
MIN 求最小值
SUM 返回表达式中所有值的和
AVG 求组中值的平均值

COUNT函数

聚合函数中最经常使用的是COUNT()函数，用于统计组中满足条件的行数或总行数，返回SELECT语句检索到的行中非NULL值的数目，若找不到匹配的行，则返回0。

格式
1
2
-- 语法格式为
COUNT ( { [ ALL | DISTINCT ]表达式 } | * )

其中，表达式的数据类型是除BLOB或TEXT之外的任何类型。ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认为ALL。使用COUNT(*)时将返回检索行的总数目，不论其是否包含 NULL值。

案例
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 【例】求会员总人数。
SELECT COUNT(*) AS  '会员数'  FROM Members; 

-- 【例】 统计已结清的订单数。
SELECT COUNT(是否结清)  AS  '已结清的订单数'  FROM Sell;
-- 注意：这里COUNT(是否结清)只统计是否结清列中不为NULL的行。

-- 【例】 统计订购册数在5以上的订单数。
 SELECT COUNT(订购册数)  AS  '订购册数在5以上的订单数'
 FROM Sell
 WHERE 订购册数>5;


MAX和MIN

MAX和MIN分别用于求表达式中所有值项的最大值与最小值。

格式
1
2
-- 语法格式为：
MAX / MIN ( [ ALL | DISTINCT ]表达式 )

其中，表达式是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期 类型。

案例
1
2
3
4
5
-- 【例】 求订购了图书编号为TP.3035的订单的最高订购册数和最低订购册数。
SELECT  MAX(订购册数), MIN(订购册数)
             FROM Sell
             WHERE  图书编号 = ' TP.3035 ';

注意：当给定列上只有空值或检索出的中间结果为空时，MAX和MIN函数的值也为空。

SUM函数和AVG函数

SUM和AVG分别用于求表达式中所有值项的总和与平均值。

格式
1
2
-- 语法格式为：
SUM / AVG ( [ ALL | DISTINCT ]表达式 )

其中，表达式是常量、列、函数或表达式，其数据类型只能是数值型。

案例
1
2
3
4
5
6
-- 【例】 求订购了图书编号为Ts.3035图书的订购总册数。
SELECT SUM(订购册数)  AS  '订购总册数'  FROM Sell  WHERE  图书编号 = 'Ts.3035 '; 

-- 【例】 求订购图书编号为Ts.3035图书的订单平均册数。
SELECT AVG(订购册数)  AS  '每笔订单平均册数'  FROM Sell  WHERE  图书编号 = 'Ts.3035 ';

分类汇总与排序—GROUP BY子句

GROUP BY子句主要用于根据字段对行分组。例如，根据学生所学的专业对XS表中的所有行分组，结果是每个专业的学生成为一组。

格式

1
2
GROUP BY {列名 |表达式} [ASC | DESC], ...  
              [WITH ROLLUP]

GROUP BY子句后通常包含列名或表达式。MySQL对GROUP BY子句进行了扩展，可以在列的后面指定ASC（升序）或DESC（降序）。GROUP BY可以根据一个或多个列进行分组，也可以根据表达式进行分组，经常和聚合函数一起使用。

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 【例】 输出Book表中图书类别名。
SELECT 图书类别   FROM Book  GROUP BY 图书类别; 

-- 【例】 按图书类别统计Book表中各类图书的库存数。
SELECT 图书类别,COUNT(*)  AS  '库存数'
            FROM Book
            GROUP BY 图书类别; 

-- 【例】 按图书编号分类统计其订单数和订单的平均订购册数。
SELECT 图书编号, AVG(订购册数) AS '订购册数' ,
             COUNT(订单号) AS '订单数'
             FROM Sell 
             GROUP BY 图书编号;

带ROLLUP的GROUP BY子句

使用带ROLLUP操作符的GROUP BY子句，可指定在结果集内不仅包含由 GROUP BY 提供的正常行，还包含汇总行。

1
2
3
4
5
6
-- 【例】按图书类别、出版社分类统计Book表中各类图书的库存数。
SELECT 图书类别, 出版社, Sum(数量) AS '库存数'
            FROM Book
            GROUP BY 图书类别, 出版社   
            WITH  ROLLUP;

HAVING子句

使用HAVING子句的目的与WHERE子句类似，不同的是WHERE子句是用来在FROM子句之后选择行，而HAVING子句用来在GROUP BY子句后选择行。

格式
1
GROUP BY {列名 |表达式}  HAVING 条件
  • 其中，条件的定义和WHERE子句中的条件类似，不过HAVING子句中的条件可以包含聚合函数，而WHERE子句中则不可以。
  • SQL标准要求HAVING必须引用GROUP BY子句中的列或用于聚合函数中的列。不过，MySQL支持对此工作性质的扩展，并允许HAVING引用SELECT清单中的列和外部子查询中的列。
案例
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 【例】 查找Sell表中每个会员平均订购册数在10本以上的会员的用户号和平均订购册数。
SELECT 用户号, AVG(订购册数) AS '平均订购册数'
            FROM Sell
            GROUP BY 用户号
            HAVING AVG(订购册数) >10; 
            
-- 【例】 查找Sell表中会员订单数在2笔以上且每笔订购册数都在5本以上的会员。
SELECT 用户号, AVG(订购册数) 
            FROM Sell
            WHERE 订购册数 >5
            GROUP BY 用户号
            HAVING COUNT(*) > 2;

分类汇总与排序—ORDER BY子句

在一条SELECT语句中，如果不使用ORDER BY子句，结果中行的顺序是不可预料的。使用ORDER BY子句后可以保证结果中的行按一定顺序排列。

格式

1
ORDER BY {列名 | 表达式 | 列编号} [ASC | DESC] , ...
  • 说明：ORDER BY子句后可以是一个列、一个表达式或一个正整数。正整数表示按结果表中该位置上的列排序。例如，使用ORDER BY 3表示对SELECT的列清单上的第3列进行排序。
  • 关键字ASC表示升序排列，DESC表示降序排列，系统默认值为ASC。

案例

1
2
3
4
5
6
-- 【例】 将Book表中记录按出版时间先后排序。
SELECT * FROM Book ORDER BY 出版时间; 

-- 【例】 将Sell表中记录按订购册数从高到低排列。
SELECT * FROM Sell ORDER BY 订购册数 DESC;

分类汇总与排序—LIMIT子句

LIMIT子句是SELECT语句的最后一个子句，主要用于限制被SELECT语句返回的行数。

格式

1
LIMIT {[偏移量,] 行数 |行数  OFFSET 偏移量}

语法格式中的偏移量和行数都必须是非负的整数常数，偏移量指定返回的第一行的偏移量，行数是返回的行数。例如，“LIMIT 5”表示返回SELECT语句的结果集中最前面5行，而“LIMIT 3，5”则表示从第4行开始返回5行。值得注意的是初始行的偏移量为0而不是1。

案例

1
2
3
4
5
6
-- 【例】 查找Members表中注册时间最靠前的5位会员的信息。
SELECT  *  FROM Members ORDER BY 注册时间 LIMIT 5; 

-- 【例】 查找Book表中从第4条记录开始的5条记录。
SELECT  *  FROM Book ORDER BY 学号 LIMIT 3, 5;

为了与PostgreSQL兼容，MySQL也支持LIMIT row_count OFFSET offset语法。所以将上面例子中的LIMIT子句换成“LIMIT 5 OFFSET 3”，结果一样。

练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
-- DQL——数据查询操作——单表查询

-- /*（1）*/查询全体学生的姓名和年龄，要求分别用name和age表示列名。
select  姓名 name,year(now())-year(出生日期) age from student;

-- /*（2）*/查询成绩表，成绩列用优（>=90）、良（75~90）、及格（60~75)、不及格（<60)表示
select *,
	case 
		when 成绩>=90 then '优'
		when 成绩>=75 and 成绩<90 then '良'
		when 成绩>=60 and 成绩<75 then '及格'
		else '不及格'
	end as 成绩档 from score;

-- /*（3）*/查询学时大于等于48学时的课程名和学分
select 课程名,学分,学时 from course where 学时>=48;

-- /*（4）*/查询前置课为空的课程名和学期
select 课程名,学期 from course where 前置课 is null;

-- /*（5）*/查询姓王的且名字为3个字的学生记录
select * from student where 姓名 like '王__';

-- /*（6）*/ 查询生源地不是山西省的学生信息。
select * from student where 地区 != '山西（省或市）';

-- /*（7）*/查询‘数据库’的课程号和前置课
select 课程号,前置课 from course;

-- /*（8）*/查询成绩为79分、89分或99分的记录。
select * from score where 成绩 in (79,89,99);

-- /*（9）*/查询每个班级人数增加80后的班级名称和新人数。
select 班级名称,人数+80 新人数 from class;

-- /*（10）*/查询不是1995年出生的所有女生信息。
select * from student where year(出生日期) != 1995 and 性别='女';
-- 
-- ---------------分隔线----------------
-- 
-- 数据查询实训二：分组与排序

-- /*（1）*/按性别统计学生人数
select 性别,count(*) 人数 from student group by 性别;

-- /*（2）*/统计每个学生的选课门数，平均分和最高分
select 学号,count(课程号),avg(成绩),max(成绩) from score group by 学号;
SELECT 
    学号,
    COUNT(课程号) AS 选课门数,
    AVG(成绩) AS 平均分,
    MAX(成绩) AS 最高分
FROM
    score
GROUP BY
    学号;

-- /*（3）*/查询平均分在80分以上的每个学生的选课门数，总分和最低分
select avg(成绩) 平均分,count(课程号) 选课门数,sum(成绩) 总分,min(成绩) 最低分 from score group by 学号 having 平均分>80;

-- /*（4）*/先按性别再按民族统计学生人数，并按人数从多到少排列
select 性别,民族,count(*) as 人数 from student group by 性别,民族 order by 人数 desc;
SELECT 性别,民族,COUNT(*) AS 人数 FROM student GROUP BY 性别,民族 ORDER BY 人数 DESC;


-- /*（5）*/查询选修两门课程的学生学号。
select 学号,count(*) 选课数 from score group by 学号 having 选课数=2;


-- /*（6）*/ 统计每个学期的学分总数
select *,sum(学分) 总学分 from course group by 学期;


-- /*（7）*/统计至少有2个男生的班级编号
select 班级编号 from student where 性别='男' group by 班级编号 having count(*) >= 2;


-- /*（8）*/返回学生表的从第3行开始的4行记录。
select * from student limit 2,4;


-- /*（9）*/统计2014级各系学生人数。
select left(班级编号,2) 系别,count(*) 人数 from student where left(学号,4) = 2014 group by left(班级编号,2);

-- /*（10）*/按照性别、民族统计人数，并显示分类小计。
select 性别,民族,count(*) 人数 from student group by 性别,民族 WITH ROLLUP;

多表查询

前面介绍了使用SELECT子句选择列，本小节讨论SELECT查询的对象（即数据源）的构成形式。SELECT的查询对象由FROM子句指定。

语法格式

1
2
FROM  表名1 [ [AS] 别名1 ] [ , 表名2[ [AS] 别名2]] …      /*查询表*/
            | JOIN子句			                      /*连接表*/

引用一个表

可以用两种方式引用一个表，第一种方式是使用USE语句让一个数据库成为当前数据库，在这种情况下，如果在FROM子句中指定表名，则该表应该属于当前数据库。第二种方式是指定的时候在表名前带上表所属数据库的名字。例如，假设当前数据库是db1，现在要显示数据库db2里的表tb的内容，使用如下语句：

1
SELECT  *  FROM  db2.tb;

当然，在SELECT关键字后指定列名的时候也可以在列名前带上所属数据库和表的名字，但是一般来说，如果选择的字段在各表中是唯一的，就没有必要去特别指定。

1
2
3
-- 【例】 从Members表中检索出所有客户的信息，并使用表别名Users。
SELECT * FROM  Members AS Users;

引用多个表

如果要在不同表中查询数据，则必须在FROM子句中指定多个表。指定多个表时就要使用到连接。当不同列的数据组合到一个表中叫做表的连接。

1
2
3
4
5
-- 【例】 查找Bookstore数据库中客户订购的图书书名，订购册数和订购时间。
 SELECT  Book.书名, Sell.订购册数, Sell.订购时间
        FROM  Book, Sell 
            WHERE Book.图书编号=Sell.图书编号;

多表连接—全连接（交叉连接）

概念

当数据查询涉及到多张表格时，要将多张表格的数据连接起来组成一张表格，连接的方式有多种。

交叉连接产生的新表是每个表的每行都与其他表中的每行交叉以产生所有可能的组合，列包含了所有表中出现的列，也就是笛卡儿积。全连接可能得到的行数为每个表中行数之积。

如表A有3行，表B有2行，表A和B全连接后得到6行（3x2=6)的表。

交叉连接的连接过程：

例如，有一个学生1表和一个单科成绩表，结果集是两个表记录的交叉乘积，列是两个表列的集合。

交叉连接的连接过程

交叉连接结果

案例

1
2
3
4
-- 【例】 交叉连接book表和sell表。
   (1)SELECT     *     FROM  Book, Sell
或
   (2)SELECT     *     FROM  Book  join   Sell

内连接（等值连接、自然连接）

概念

FROM子句各个表用逗号分隔，这样就指定了全连接。全连接潜在地产生数量非常大的行，因为可能得到的行数为每个表中行数之积。在这样的情形下，通常要使用WHERE子句设定条件来将结果集减少为易于管理的大小，这样的连接即为等值连接。

若表A和B进行等值连接（T1=T3 ），则如下表所示，只有两行。

T1 T2 T3 T4 T5
1 A 1 3 M
2 B 2 0 N

使用JOIN关键字的连接，语法格式如下：

1
表名1 INNER JOIN 表名2 ON 条件 | USING (列名)

案例

1
2
3
4
5
6
7
-- 【例】 查找Bookstore数据库中客户订购的图书书名，订购册数和订购时间。
（1）SELECT  Book.书名, Sell.订购册数, Sell.订购时间
         FROM  Book, Sell 
         WHERE Book.图书编号=Sell.图书编号; 
（2）SELECT  Book.书名, Sell.订购册数, Sell.订购时间
        FROM  Book [inner] join Sell 
        ON Book.图书编号=Sell.图书编号;

ㅤㅤㅤㅤ

  • 内连接是系统默认的，可以省略INNER关键字。
  • 使用内连接后，FROM子句中ON条件主要用来连接表，其他并不属于连接表的条件可以使用WHERE子句来指定。
1
2
3
4
5
6
7
8
9
10
11
12
-- 【例 】 用JOIN关键字表达下列查询：查找购买了“网页程序设计” 图书信息。
SELECT 书名,订购册数
        FROM Book JOIN Sell
           ON Book.图书编号 = Sell. 图书编号
             WHERE 书名 = '网页程序设计'; 

-- 【例】 用JOIN关键字表达下列查询：查找订购数量大于5本图书和会员姓名和订购册数。
SELECT 会员姓名,订购册数
    FROM  Sell  JOIN   Members   
         ON  Sell.用户号 = Members.用户号
                  WHERE 订购册数>5 ;

ㅤㅤㅤㅤ

内连接还可以用于多个表的连接。

1
2
3
4
5
6
-- 【例】 用JOIN关键字表达下列查询：查找购买了“网页程序设计”且订购数量大于5本的图书和会员姓名和订购册数。
SELECT Book.图书编号, 会员姓名, 书名, 订购册数
    FROM  Sell  JOIN  Book  ON  Book. 图书编号= Sell.图书编号
          JOIN   Members   ON  Sell.身份证号 = Members.身份证号
                  WHERE 书名 = '网页程序设计'  AND 订购册数>5 ;

ㅤㅤㅤㅤ

自连接

作为特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。

1
2
3
4
5
6
-- 【例】 查找BookStore数据库中订单不同、图书编号相同的图书的订单号、图书编号和订购册数。
SELECT  a.订单号,a.图书编号,a.订购册数
              FROM  Sell  AS  a  JOIN  Sell  AS  b 
              ON  a. 图书编号=b. 图书编号
              where a. 订单号!=b. 订单号;

Using子句

如果要连接的表中有列名相同，并且连接的条件就是列名相等，那么ON条件也可以换成USING子句。USING（列名）子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。其中列名为两表中相同的列名。

1
2
3
-- 【例】 查找Members表中所有订购过图书的用户的姓名。
 SELECT  Distinct 姓名 FROM Members  JOIN  Sell USING (用户号);

外连接

指定了OUTER关键字的连接为外连接。外连接包括：

  • 左外连接（LEFT OUTER JOIN）：

    结果表中除了匹配行外，还包括左表有的但右表中不匹配的行，对于这样的行，从右表被选择的列设置为NULL。

  • 右外连接（RIGHT OUTER JOIN）：

    结果表中除了匹配行外，还包括右表有的但左表中不匹配的行，对于这样的行，从左表被选择的列设置为NULL。

1
2
3
4
5
6
7
8
9
10
-- 【例】 查找所有图书的图书编号、数量及订购了图书的会员身份证号，若从未订购过，也要包括其情况。
SELECT Book.图书编号,Book.数量
FROM Book LEFT OUTER JOIN Sell
ON Book.图书编号= Sell.图书编号;

-- 【例】 查找订购了图书的会员的订单号、图书编号和订购册数以及所有会员的会员姓名。 
SELECT 订单号,图书编号,订购册数, Members.会员姓名
FROM Sell RIGHT JOIN Members 
ON Members.用户号= Sell.用户号;

各个连接

练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- 多表查询实训

-- /*（1）*/查询所有学生的学号、姓名、课程号和成绩
select a.学号,姓名,课程号,成绩 from student a join score b using(学号);

-- /*（2）*/查询会计学院全体同学的学号、姓名和班级名称
select a.学号,姓名,班级名称 from student a join class b using(班级编号) where 院系='会计学院';


-- /*（3）*/查询成绩在90分以上的学生的学号、姓名和成绩
select 学号,姓名,成绩 from student a join score b using(学号) where 成绩>90;


-- /*（4）*/使用左外连接查询所有课程的课程号、课程名称和选修了该课程的学生的学号、姓名和成绩，没有学生选修的课程也要包括。
select a.课程号,课程名,b.学号,姓名,成绩 from course a left join score b using(课程号) left join student c using(学号);

-- /*（5）*/查询“计算机14-1班”所有学生的学号和姓名
select 学号,姓名 from class a join student b using(班级编号) where 班级名称='计算机14-1班';

-- /*（6）*/查询“张晓勇”同学所选课程的课程名称和成绩。
select 课程名,成绩 from course a join score b using(课程号) join student c using(学号) where 姓名='张晓勇';

-- /*（7）*/查询至少选修2门课程的女学生姓名。
select 姓名 from score a join student b using(学号) where 性别='女' group by 学号 having count(*)>=2;

-- /*（8）*/查询姓“王”的学生所学的课程名称。
select 姓名,课程名 from student a join score b using(学号) join course c using(课程号) where 姓名 like '王%';

-- /*（9）*/查询选修“管理学”课程且成绩在80～90分之间的学生学号姓名及成绩。
select 学号,姓名,成绩 from course a join score b using(课程号) join student c using(学号) where 课程名='管理学' and 成绩 between 80 and 90;

-- /*（10）*/查询选修“计算机基础”课程的学生的平均年龄。
select 课程名,avg(year(now())-year(出生日期)) 平均年龄 from course a join score b using(课程号) join student c using(学号) where 课程名='计算机基础';
-- 
-- ---------------分隔线----------------
-- 

-- 子查询实训

-- 1、查询“计算机14-1班”所有学生的学号和姓名
select 学号,姓名 from student where 班级编号 in (select 班级编号 from class where 班级名称='计算机14-1班');

-- 2、查询与“张晓勇”同一个系的同学姓名。
select 班级编号,姓名 from student where left(班级编号,2) in (select left(班级编号,2) from student where 姓名='张晓勇');

-- 3、查询成绩比该课程平均成绩高的学生的学号及成绩。
select 学号,成绩 from score where 成绩 > (select avg(成绩) from score);

-- 4、查询没有选修“11003”课程的学生学号及姓名。
select 学号,姓名 from student where 学号 in (select 学号 from score where 课程号 != '11003');

-- 5、查询年龄高于平均年龄的学生的学号，姓名和出生日期
select 学号,姓名,出生日期 from student where year(now())-year(出生日期) > (select avg(year(now())-year(出生日期)) from student);

-- 6、查询选修了“计算机基础”课程的学生学号、姓名及班级编号。
select 学号,姓名,班级编号 from student where 学号 in (select 学号 from score where 课程号 in (select 课程号 from course where 课程名='计算机基础'));
select 学号,姓名,班级编号 from student a join score b using(学号) where 课程号 in (select 课程号 from course where 课程名='计算机基础');
-- 7、查询出生日期小于所有男同学出生日期的女同学的姓名及班级编号。
select 姓名,班级编号 from student where 性别='女' and 出生日期 > all(select 出生日期 from student where 性别='男');

-- 8、 查询学号比“李明”同学大，而出生日期比他小的学生姓名。
select 姓名 from student where 学号 > (select 学号 from student where 姓名='李明') and year(出生日期) > some(select year(出生日期) from student);

综合小测实训

小测

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 综合小测实训

-- 1、  查询雇员表中雇佣年限超过十年的员工雇员ID、姓名、雇用年限和薪水
select 雇员ID,姓名,year(now())-year(雇佣日期) 雇用年限,薪水 from 雇员表 where year(now())-year(雇佣日期) > 10;

-- 2、  查询雇员表中所有特长为“计算机”、“钢琴”、“书法”的雇员的姓名、特长。
select 姓名,特长 from 雇员表 where 特长 in('计算机','钢琴','书法');

-- 3、查询雇员表中所有姓名中第二个字为“丽”字的雇员的姓名和出生日期。
select 姓名,出生年月 from 雇员表 where 姓名 like '_丽%';

-- 4、在订单表p_order中，查询产品ID、数量和订货日期，并按订货数量降序给产品排序。
select 产品ID,数量,订货日期 from 订单表 order by 数量 desc;

-- 5、在产品表中查询平均价格超过10元的产品的种类(多种方法)
select 类别名 from 类别表 where 类别ID in (select 类别ID  from 产品表 group by 类别ID having avg(单价)>10);
select 类别名 from 类别表 a join 产品表 b using(类别ID) group by 类别ID having avg(单价)>10;

-- 6、查询所有订购了“鼠标”产品的公司的公司名称和联系方式。(多种方法)
select 公司名称,联系方式 from 客户表 where 客户ID in (select 客户ID from 订单表 where 产品ID in (select 产品ID from 产品表 where 产品名='鼠标'));
select 公司名称,联系方式 from 客户表 a join 订单表 b using(客户ID) where 产品ID in (select 产品ID from 产品表 where 产品名='鼠标');
select 公司名称,联系方式 from 客户表 a join 订单表 b using(客户ID) join 产品表 c using(产品ID) where 产品名='鼠标';
SELECT 公司名称,联系方式 from `产品表` a,`订单表` b, `客户表` c where a.产品ID = b.产品ID and b.客户ID=c.客户ID and a.产品名 = '鼠标';

-- 7、查询已订购了产品的公司的公司名称、联系人姓名和所订产品的产品ID和数量(多种方法)
select 公司名称,联系人姓名,产品ID,数量 from 客户表 a join 订单表 b using(客户ID);
SELECT 公司名称,联系人姓名,产品ID,数量 from `客户表` a, `订单表` b where a.`客户ID`=b.`客户ID`;

-- 8、使用左外连接查询产品表 表中凡是有类别信息的所有产品信息。
SELECT * from 产品表 a left JOIN `类别表` b on a.`类别ID`=b.`类别ID` where a.类别ID is not null;

数据视图

练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- 数据视图实训

-- 1、在schooldb数据库上创建视图v_score，包括所有男同学的学号,姓名，民族以及其选修的课程名和成绩
create view v_score as
select 学号,姓名,民族,课程名,成绩 from student a join score b using(学号) join course c using(课程号) where 性别='男';

-- 2、在视图v_score中查找少数民族学生的学号，姓名以及选修的课程名和成绩
select 学号,姓名,课程名,成绩 from v_score where 民族 != '汉';

-- 3、创建视图v_avg，包括学号（在视图中列名为name）和平均成绩（在视图中列名为score_avg）
create view v_avg(name,score_avg) as
select 学号,avg(成绩) from score group by 学号;

-- 4、使用视图v_avg，查找平均成绩在80分以上的学生的学号和平均成绩
select name,score_avg from v_avg where score_avg > 80;

-- 5、创建视图v_student视图中包含所有汉族学生的学生信息，并向v_student视图中插入一条记录“2020410001,李牧，男，1998-10-21，广东，汉，null”
create view v_student as
select * from student where 民族='汉' with check option;
insert into v_student values('2020410001','李牧','男','1998-10-21','广东','汉',null);

-- 6、删除v_student中女同学的记录
delete from v_student where 性别='女';

-- 7、创建视图v_term4查询第4学期所有课程信息,并要求添加with check option 子句（注意添加该子句的作用）
create view v_term4 as
select * from course where 学期 = 4 with check option;

-- 8、向v_term4视图插入一门新课，具体课程信息自定
insert into v_term4 values('21007','Linux操作系统',4,64,4,'21001');

-- 9、通过视图v_term4创建v_term4new视图，查询第4学期课程数和总课时数。
create view v_term4new(课程数,总课时数) as
select count(课程号),sum(学时) from v_term4;

-- 10、删除v_term4new视图
drop view v_term4new;

MySQL索引

练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- /*MySQL索引习题*/

-- /*二、*/
-- /*1. */对schooldb数据库course表“课程名”列上的前3个字符建立一个升序的索引“I_kc”
create index I_kc 
       on course(课程名(3) ASC);
			 
-- /*2.*/在class表的院系列和年级列上建立一个复合索引I_CX
alter table class
add index I_cx(院系,年级);

-- /*3. */在student表的姓名列上创建一个唯一索引
alter table student
     add unique (姓名);
		 
-- /*4.*/为course表创建主键索引（假设course表中主键未设定）
alter table class
add primary key (课程号);

-- /*5.*/创建score表学号和课程号的联合主键（假设score表中主键未设定）,并在成绩列创建普通索引
alter table score
    add primary key(学号,课程号),
    add index (成绩);

-- /*6.*/删除course表的主键
alter table course drop primary key ;

数据控制语言—DCL

练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
/*数据库管理习题*/

/*二、*/
-- 1.创建用户king1,king2，密码分别为ken1，ken2”
CREATE USER
king1@localhost IDENTIFIED BY 'ken1', 
king2@localhost IDENTIFIED BY 'ken2';

-- 2.授予用户king1在schooldb数据库student表上的select权限 
 GRANT SELECT
    ON  schooldb.student 
        TO king1@localhost;
				
-- 3.授予用户king2在schooldb数据库class表上的select，update权限 
 GRANT SELECT,update
    ON  schooldb.class 
        TO king2@localhost;
				
-- 4.授予用户king1在schooldb数据库所有表上的select权限 
 GRANT SELECT
    ON  schooldb.* 
        TO king1@localhost;
				
-- 5.授予用户king2在schooldb数据库所有表上的所有权限 
 GRANT ALL
    ON  schooldb.* 
        TO king2@localhost;
				
-- 6.收回king2在schooldb数据库class表上的select权限
 REVOKE  select
    ON  schooldb.class
        FROM  king2@localhost;

文章作者: [Blue Eagle]

文章链接: [https://yjh021.github.io/2024/12/08/MySQL课程笔记/]

版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 [Blue Eagle]

≡