MySQL_00000_00000_mysql课件

数据库概述

查看数据库排名网址：https://db-engines.com

为什么要使用数据库？

• 持久化（persistence）：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，而持久化的实现过程大多通过各种关系数据库来完成。
• 持久化的主要作用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。
  

数据库与数据库管理系统

数据库的相关概念

• DB：数据库（Database）
  即存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据。
• DBMS：数据库管理系统（Database Management System）
  是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。
• SQL：结构化查询语言（Structured Query Language）
  专门用来与数据库通信的语言。

数据库与数据库管理系统的关系

数据库管理系统（DBMS）可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。

数据库管理系统、数据库和表的关系如图所示：

MySQL介绍

关系型数据库（RDBMS）

• 这种类型的数据库是最古老的数据库类型，关系型数据库模型是把复杂的数据结构归结为简单的二元关系 （即二维表格形式）。
• 关系型数据库以行(row)和列(column) 的形式存储数据，以便于用户理解。这一系列的行和列被称为表(table) ，一组表组成了一个库(database)。
• 表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系型数据库，就是建立在关系模型基础上的数据库。
• SQL 就是关系型数据库的查询语言。

优势：

• 复杂查询： 可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
• 事务支持：使得对于安全性能很高的数据访问要求得以实现。

非关系型数据库（非RDBMS）

介绍

非关系型数据库，可看成传统关系型数据库的功能阉割版本 ，基于键值对存储数据，不需要经过SQL层的解析， 性能非常高 。同时，通过减少不常用的功能，进一步提高性能。
目前基本上大部分主流的非关系型数据库都是免费的。

有哪些非关系型数据库？
相比于 SQL，NoSQL 泛指非关系型数据库，包括了榜单上的键值型数据库、文档型数据库、搜索引擎和列存储等，除此以外还包括图形数据库。也只有用 NoSQL 一词才能将这些技术囊括进来。

键值型数据库

键值型数据库通过Key-Value键值的方式来存储数据，其中 Key 和 Value 可以是简单的对象，也可以是复杂的对象。Key 作为唯一的标识符，优点是查找速度快，在这方面明显优于关系型数据库，缺点是无法像关系型数据库一样使用条件过滤（比如where），如果你不知道去哪里找数据，就要遍历所有的键，这就会消耗大量的计算。

键值型数据库典型的使用场景是作为内存缓存 。 Redis是最流行的键值型数据库。

文档型数据库

此类数据库可存放并获取文档，可以是XML、JSON等格式。在数据库中文档作为处理信息的基本单位，一个文档就相当于一条记录。文档数据库所存放的文档，就相当于键值数据库所存放的“值”。MongoDB是最流行的文档型数据库。此外，还有CouchDB等。

搜索引擎数据库

虽然关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。搜索引擎数据库是应用在搜索引擎领域的数据存储形式，由于搜索引擎会爬取大量的数据，并以特定的格式进行存储，这样在检索的时候才能保证性能最优。核心原理是“倒排索引”。
典型产品：Solr、Elasticsearch、Splunk 等。

列式数据库

列式数据库是相对于行式存储的数据库，Oracle、MySQL、SQL Server 等数据库都是采用的行式存储（Row-based），而列式数据库是将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的I/O，适合于分布式文件系统，不足在于功能相对有限。典型产品：HBase等。

图形数据库

图形数据库，利用了图这种数据结构存储了实体（对象）之间的关系。图形数据库最典型的例子就是社交网络中人与人之间的关系，数据模型主要是以节点和边（关系）来实现，特点在于能高效地解决复杂的关系问题。

图形数据库顾名思义，就是一种存储图形关系的数据库。它利用了图这种数据结构存储了实体（对象）之间的关系。关系型数据用于存储明确关系的数据，但对于复杂关系的数据存储却有些力不从心。如社交网络中人物之间的关系，如果用关系型数据库则非常复杂，用图形数据库将非常简单。典型产品：Neo4J、InfoGrid等。

关系型数据库设计规则

• 关系型数据库的典型数据结构就是数据表 ，这些数据表的组成都是结构化的（Structured）。
• 将数据放到表中，表再放到库中。
• 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
• 表具有一些特性，这些特性定义了数据在表中如何存储，类似Java和Python中 “类”的设计。

表、记录、字段

• E-R（entity-relationship，实体-联系）模型中有三个主要概念是： 实体集 、 属性 、 联系集 。
  
  一个实体集（class）对应于数据库中的一个表（table），一个实体（instance）则对应于数据库表中的一行（row），也称为一条记录（record）。一个属性（attribute）对应于数据库表中的一列（column），也称为一个字段（field）。
  
  ORM思想 (Object Relational Mapping)体现：
  数据库中的一个表 <-> Java中的一个类
  表中的一条数据 <-> 类中的一个对象（或实体）
  表中的一个列 <-> 类中的一个字段、属性(field)

表的关联关系

表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。

四种：一对一关联、一对多关联、多对多关联、自我引用。

一对一关联（one-to-one）

在实际的开发中应用不多，因为一对一可以创建成一张表。

举例：设计学生表 ：学号、姓名、手机号码、班级、系别、身份证号码、家庭住址、籍贯、紧急联系人、…

拆为两个表：两个表的记录是一一对应关系。

基础信息表 （常用信息）：学号、姓名、手机号码、班级、系别
档案信息表 （不常用信息）：学号、身份证号码、家庭住址、籍贯、紧急联系人、…

两种建表原则：
外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一。
外键是主键：主表的主键和从表的主键，形成主外键关系。

一对多关系（one-to-many）

常见实例场景： 客户表和订单表，分类表和商品表，部门表和员工表 。

举例：
员工表：编号、姓名、…、所属部门
部门表：编号、名称、简介

一对多建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键。

多对多（many-to-many）

要表示多对多关系，必须创建第三个表，该表通常称为联接表 ，它将多对多关系划分为两个一对多关系。将这两个表的主键都插入到第三个表中。

举例1：学生-课程

学生信息表 ：一行代表一个学生的信息（学号、姓名、手机号码、班级、系别…）
课程信息表 ：一行代表一个课程的信息（课程编号、授课老师、简介…）
选课信息表 ：一个学生可以选多门课，一门课可以被多个学生选择

学号   课程编号 
1    1001
2    1001
1    1002
1
2
3
4

举例2：产品-订单
“订单”表和“产品”表有一种多对多的关系，这种关系是通过与“订单明细”表建立两个一对多关系来定义的。一个订单可以有多个产品，每个产品可以出现在多个订单中。

产品表 ：“产品”表中的每条记录表示一个产品。
订单表 ：“订单”表中的每条记录表示一个订单。
订单明细表 ：每个产品可以与“订单”表中的多条记录对应，即出现在多个订单中。一个订单可以与“产品”表中的多条记录对应，即包含多个产品。

举例3：用户-角色
多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

自我引用（Self reference）

MySQL 环境搭建

MySQL 的卸载

步骤一：停止MySQL服务。
在卸载之前，先停止MySQL8.0的服务。

打开“服务”对话框的方式：
1、在“运行”对话框中输入“services.msc”。
2、按键盘上的“Ctrl + Alt + Delete”组合键，打开“任务管理器”对话框，选择“服务”列表。
3、此电脑->管理->服务和应用程序->服务。

在“服务”对话框中找到“MySQL8.0”的服务，如果是“正在运行”状态，可以右键单击服务，选择“停止”选项停止MySQL8.0的服务。

步骤二：软件的卸载。
方式1：通过控制面板方式
卸载MySQL8.0的程序可以和其他桌面应用程序一样直接在“控制面板”选择“卸载程序”，并在程序列表中找到MySQL8.0服务器程序，直接双击卸载即可，如图所示。这种方式删除，数据目录下的数据不会跟着删除。

方式2：通过安装包提供的卸载功能卸载
也可以通过安装向导程序进行MySQL8.0服务器程序的卸载。
① 再次双击下载的mysql-installer-community-8.0.26.0.msi文件，打开安装向导。安装向导会自动检测已安装的MySQL服务器程序。
② 选择要卸载的MySQL服务器程序，单击“Remove”（移除），即可进行卸载。

③ 单击“Next”（下一步）按钮，确认卸载。

④ 弹出是否同时移除数据目录选择窗口。如果想要同时删除MySQL服务器中的数据，则勾选“Remove the data directory”，如图所示。

⑤ 执行卸载。单击“Execute”（执行）按钮进行卸载。

⑥ 完成卸载。单击“Finish”（完成）按钮即可。如果想要同时卸载MySQL8.0的安装向导程序，勾选“Yes，uninstall MySQL Installer”即可，如图所示。

方式2：通过第三方软件
比如：360软件管家等软件。

步骤三：残余文件的清理。
如果再次安装不成功，可以卸载后对残余文件进行清理后再安装。
1、服务目录：mysql服务的安装目录。
2、数据目录：默认在C:\ProgramData\MySQL。如果自己单独指定过数据目录，就找到自己的数据目录进行删除即可。

注意：
请在卸载前做好数据备份
在操作完以后，需要重启计算机，然后进行安装即可。如果仍然安装失败，需要继续操作如下步骤4。

步骤四：清理注册表（选做）。
如果前几步做了，再次安装还是失败，那么可以清理注册表。
如何打开注册表编辑器：在系统的搜索框中输入 regedit

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL服务 目录删除
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MySQL服务 目录删除
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL服务 目录删除
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\MySQL服务 目录删除
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL服务目录删除
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL服务删除

注册表中的ControlSet001,ControlSet002,不一定是001和002,可能是ControlSet005、006之类

步骤五：删除环境变量配置。
找到path环境变量，将其中关于mysql的环境变量删除，切记不要全部删除。
例如：删除 D:\develop_tools\mysql\MySQLServer8.0.26\bin; 这个部分。

MySQL的下载、安装、配置

• MySQL Community Server 社区版本，开源免费，自由下载，但不提供官方技术支持，适用于大多数普通用户。
• MySQL Enterprise Edition 企业版本，需付费，不能在线下载，可以试用30天。提供了更多的功能和更完备的技术支持，更适合于对数据库的功能和可靠性要求较高的企业客户。
• MySQL Cluster 集群版，开源免费。用于架设集群服务器，可将几个MySQL Server封装成一个Server。需要在社区版或企业版的基础上使用。
• MySQL Cluster CGE 高级集群版，需付费。

此外，官方还提供了MySQL Workbench（GUITOOL），一款专为MySQL设计的图形界面管理工具。MySQLWorkbench又分为两个版本，分别是社区版（MySQL Workbench OSS）、 商用版（MySQLWorkbenchSE）。

下载

下载地址：
MySQL官网_英文：https://www.mysql.com

步骤：
1、进入官网首页。

2、选择“DOWNLOADS”。

3、点击“MySQL Community (GPL) Downloads »”下载社区版。

4、点击“MySQL Community Server”
在General Availability(GA) Releases中选择适合的版本。

Windows平台下提供两种安装文件：
（1）MySQL二进制分发版（.msi安装文件）
（2）免安装版（.zip压缩文件）。

一般来讲，应当使用二进制分发版，因为该版本提供了图形化的安装向导过程，比其他的分发版使用起来要简单，不再需要其他工具启动就可以运行MySQL。
这里在Windows系统下推荐下载 MSI安装程序 ；点击 Go to Download Page 进行下载即可。

5、点击“Go to Download Page”进入如下界面

Windows下的MySQL8.0安装有两种安装程序
（1）mysql-installer-web-community-8.0.26.0.msi，安装时需要联网安装组件。
（2）mysql-installer-community-8.0.26.0.msi，安装时离线安装即可。推荐。

如果安装MySQL5.7版本的话，选择Archives ，接着选择MySQL5.7的相应版本即可。

6、点击“Download”进入如下界面。

7、点击“No thanks,just start my download”,进入如下界面。

安装

MySQL下载完成后，找到下载文件，双击进行安装，具体操作步骤如下。
步骤一：双击下载的mysql-installer-community-8.0.26.0.msi文件，打开安装向导。
步骤二：打开“Choosing a Setup Type”（选择安装类型）窗口，在其中列出了5种安装类型，分别是1、Developer Default（默认安装类型）
2、Server only（仅作为服务器）
3、Client only（仅作为客户端）
4、Full（完全安装）
5、Custom（自定义安装）。

这里选择“Custom（自定义安装）”类型按钮，单击“Next(下一步)”按钮。

步骤三：打开“Select Products” （选择产品）窗口，可以定制需要安装的产品清单。例如，选择“MySQL Server 8.0.26-X64”后，单击“→”按钮，即可选择安装MySQL服务器，如图所示。采用通用的方法，可以添加其他你需要安装的产品。

此时如果直接“Next”（下一步），则产品的安装路径是默认的。如果想要自定义安装目录，则可以选中对应的产品，然后在下面会出现“Advanced Options”（高级选项）的超链接。

单击“Advanced Options”（高级选项）则会弹出安装目录的选择窗口，如图所示，此时你可以分别设置MySQL的服务程序安装目录和数据存储目录。如果不设置，默认分别在C盘的Program Files目录和ProgramData目录（这是一个隐藏目录）。如果自定义安装目录，请避免“中文”目录。另外，建议服务目录和数据目录分开存放。

步骤4：在上一步选择好要安装的产品之后，单击“Next”（下一步）进入确认窗口，如图所示。单击“Execute”（执行）按钮开始安装。

步骤五：安装完成后在“Status”（状态）列表下将显示“Complete”（安装完成），如图所示。

配置

MySQL安装之后，需要对服务器进行配置。具体的配置步骤如下。
步骤一：单击“Next”（下一步）按钮，就可以进入产品配置窗口。

步骤二：单击“Next”（下一步）按钮，进入MySQL服务器类型配置窗口，如图所示。端口号一般选择默认
端口号3306。

其中，“Config Type”选项用于设置服务器的类型。单击该选项右侧的下三角按钮，即可查看3个选项，如图所示。

Development Machine（开发机器）：该选项代表典型个人用桌面工作站。此时机器上需要运行多个应用程序，那么MySQL服务器将占用最少的系统资源。
Server Machine（服务器）：该选项代表服务器，MySQL服务器可以同其他服务器应用程序一起运行，例如Web服务器等。MySQL服务器配置成适当比例的系统资源。
Dedicated Machine（专用服务器）：该选项代表只运行MySQL服务的服务器。MySQL服务器配置成使用所有可用系统资源。

步骤三：单击“Next”（下一步）按钮，打开设置授权方式窗口。其中，上面的选项是MySQL8.0提供的新的授权方式，采用SHA256基础的密码加密方法；下面的选项是传统授权方法（保留5.x版本兼容性）。

步骤四：单击“Next”（下一步）按钮，打开设置服务器root超级管理员的密码窗口，如图所示，需要输入两次同样的登录密码。也可以通过“Add User”添加其他用户，添加其他用户时，需要指定用户名、允许该用户名在哪台/哪些主机上登录，还可以指定用户角色等。此处暂不添加用户。

步骤五：单击“Next”（下一步）按钮，打开设置服务器名称窗口，如图所示。该服务名会出现在Windows服务列表中，也可以在命令行窗口中使用该服务名进行启动和停止服务。此处将服务名设置为“MySQL80”。如果希望开机自启动服务，也可以勾选“Start the MySQL Server at System Startup”选项（推荐）。
下面是选择以什么方式运行服务？可以选择“Standard System Account”(标准系统用户)或者“Custom User”(自定义用户)中的一个。这里推荐前者。

步骤六：单击“Next”（下一步）按钮，打开确认设置服务器窗口，单击“Execute”（执行）按钮。

步骤七：完成配置，如图所示。单击“Finish”（完成）按钮，即可完成服务器的配置。

步骤八：如果还有其他产品需要配置，可以选择其他产品，然后继续配置。如果没有，直接选择“Next”（下一步），直接完成整个安装和配置过程。

步骤九：结束安装和配置。

设置环境变量

如果不配置MySQL环境变量，就不能在命令行直接输入MySQL登录命令。

配置MySQL的环境变量的步骤：
步骤一：在桌面上右击【此电脑】图标，在弹出的快捷菜单中选择【属性】菜单命令。
步骤二：打开【系统】窗口，单击【高级系统设置】链接。
步骤三：打开【系统属性】对话框，选择【高级】选项卡，然后单击【环境变量】按钮。
步骤四：打开【环境变量】对话框，在系统变量列表中选择path变量。
步骤五：单击【编辑】按钮，在【编辑环境变量】对话框中，将MySQL应用程序的bin目录（C:\Program Files\MySQL\MySQL Server 8.0\bin）添加到变量值中。
步骤六：添加完成之后，单击【确定】按钮，这样就完成了配置path变量的操作，然后就可以直接输入MySQL命令来登录数据库了。

安装失败解决方案

问题一：无法打开MySQL8.0软件安装包或者安装过程中失败
在运行MySQL8.0软件安装包之前，用户需要确保系统中已经安装了.Net Framework相关软件，如果缺少此软件，将不能正常地安装MySQL8.0软件。

解决方案：到这个地址https://www.microsoft.com/en-us/download/details.aspx?id=42642下载Microsoft .NET Framework 4.5并安装后，再去安装MySQL。

另外，还要确保Windows Installer正常安装。Windows上安装MySQL8.0需要操作系统提前已安装好Microsoft Visual C++ 2015-2019。


解决方案同样是，提前到微软官网https://docs.microsoft.com/en-US/cpp/windows/latest-supported-vc-redist?view=msvc-170下载相应的环境。

问题二：卸载重装MySQL失败。
该问题通常是因为MySQL卸载时，没有完全清除相关信息导致的。
解决办法是，把以前的安装目录删除。如果之前安装并未单独指定过服务安装目录，则默认安装目录是“C:\Program Files\MySQL”，彻底删除该目录。同时删除MySQL的Data目录，如果之前安装并未单独指定过数据目录，则默认安装目录是“C:\ProgramData\MySQL”，该目录一般为隐藏目录。删除后，重新安装即可。

问题三：如何在Windows系统删除之前未卸载干净的MySQL服务列表？
操作方法：在系统“搜索框”中输入“cmd”，按“Enter”（回车）键确认，弹出命令提示符界面。然后输入“sc delete MySQL服务名”,按“Enter”（回车）键，就能彻底删除残余的MySQL服务了。

MySQL的登录

服务的启动与停止

MySQL安装完毕之后，需要启动服务器进程，不然客户端无法连接数据库。
在前面的配置过程中，已经将MySQL安装为Windows服务，并且勾选当Windows启动、停止时，MySQL也自动启动、停止。

方式一：使用图形界面工具
步骤1：打开Windows服务。

• 方式（1）：计算机（点击鼠标右键）→ 管理（点击）→ 服务和应用程序（点击）→ 服务（点击）
• 方式（2）：控制面板（点击）→ 系统和安全（点击）→ 管理工具（点击）→ 服务（点击）
• 方式（3）：任务栏（点击鼠标右键）→ 启动任务管理器（点击）→ 服务（点击）
• 方式（4）：单击【开始】菜单，在搜索框中输入“services.msc”，按Enter键确认。

步骤2：找到MySQL80（点击鼠标右键）→ 启动或停止（点击）

方式二：使用命令行工具。
启动MySQL服务命令：net start MySQL服务名
停止MySQL服务命令：net stop MySQL服务名

说明：
1、start和stop后面的服务名应与之前配置时指定的服务名一致。
2、如果当你输入命令后，提示“拒绝服务”，请以系统管理员身份打开命令提示符界面重新尝试。

自带客户端的登录与退出

当MySQL服务启动完成后，便可以通过客户端来登录MySQL数据库。注意：确认服务是开启的。

登录方式1：MySQL自带客户端。
开始菜单 → 所有程序 → MySQL → MySQL 8.0 Command Line Client

说明：仅限于root用户。

登录方式2：Windows命令行。

登陆命令：
假设MySQL的用户名为：root，密码为：root

格式：mysql -h 主机名 -P 端口号 -u 用户名 -p密码

举例：
mysql -uroot -proot
mysql -h localhost -P 3306 -u root -proot

注意：
1、-p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格。如：

mysql -hlocalhost -P3306 -uroot -proot

2、密码建议在下一行输入，保证安全

mysql -h localhost -P 3306 -u root -p
Enter password:***

3、客户端和服务器在同一台机器上，所以输入localhost或者IP地址127.0.0.1。同时，因为是连接本机： -hlocalhost就可以省略，如果端口号没有修改：-P3306也可以省略简写成：

mysql -u root -p
Enter password:****

连接成功后，有关于MySQL Server服务版本的信息，还有第几次连接的id标识。
也可以在命令行通过以下方式获取MySQL Server服务版本的信息：

mysql -V
mysql --version

登录后，通过以下方式查看当前版本信息：

mysql>select version();

退出登录

exit或quit或ctrl+c强制退出。

MySQL的使用

使用演示

一、查看所有的数据库

show databases;

“information_schema”是 MySQL 系统自带的数据库，主要保存 MySQL 数据库服务器的系统信息，比如数据库的名称、数据表的名称、字段名称、存取权限、数据文件所在的文件夹和系统使用的文件夹，等等。
“performance_schema”是 MySQL 系统自带的数据库，可以用来监控 MySQL 的各类性能指标。
“sys”数据库是 MySQL 系统自带的数据库，主要作用是以一种更容易被理解的方式展示 MySQL 数据库服务器的各类性能指标，帮助系统管理员和开发人员监控 MySQL 的技术性能。
“mysql”数据库保存了 MySQL 数据库服务器运行时需要的系统信息，比如数据文件夹、当前使用的字符集、约束检查信息，等等。

字符编码

MySQL8.x及以上版本默认编码是utf-8，而MySQL5.x默认编码不是utf-8。所以如果安装MySQL5.x版本则需要修改编码。

查看所有字符集：SHOW VARIABLES LIKE ‘character_set_%’;
设置当前连接的客户端字符集 “SET NAMES GBK;”

修改数据库和表的字符编码：
1、停止服务。
2、修改my.ini文件。
3、重新启动服务。

说明：
如果是在修改my.ini之前建的库和表，那么库和表的编码还是原来的Latin1，此时要么删了重建，要么使用alter语句修改编码。
create database 0728db charset Latin1;
use 0728db;
show create table student\G;
alter table student charset utf8; #修改表字符编码为utf-8。
show create table student\G;
alter table student modify name varchar(20) charset utf8; #修改字段字符编码为utf-8。
show create table student\G;
show create database 0728db;
alter database 0728db charset utf8; #修改数据库的字符编码为utf-8。

mysql> create database 0728db charset Latin1;
Query OK, 1 row affected (0.00 sec)
mysql> use 0728db;
Database changed
北京宏福校区：010-56253825  深圳西部硅谷校区：0755-23060254  上海大江商厦校区：021-57652717
mysql> create table student (id int , name varchar(20)) charset Latin1;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table student\G
*************************** 1. row ***************************
   Table: student
Create Table: CREATE TABLE `student` (
 `id` int(11) NOT NULL,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table student charset utf8; #修改表字符编码为UTF8
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0  Warnings: 0
mysql> show create table student\G
*************************** 1. row ***************************
   Table: student
Create Table: CREATE TABLE `student` (
 `id` int(11) NOT NULL,
 `name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,  #字段仍然是latin1编码
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table student modify name varchar(20) charset utf8; #修改字段字符编码为UTF8
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0  Warnings: 0
mysql> show create table student\G
*************************** 1. row ***************************
   Table: student
Create Table: CREATE TABLE `student` (
 `id` int(11) NOT NULL,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create database 0728db;;
+--------+-----------------------------------------------------------------+
|Database| Create Database                         |
+------+-------------------------------------------------------------------+
|0728db| CREATE DATABASE `0728db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database 0728db charset utf8; #修改数据库的字符编码为utf8
Query OK, 1 row affected (0.00 sec)
mysql> show create database 0728db;
+--------+-----------------------------------------------------------------+
|Database| Create Database                         |
+--------+-----------------------------------------------------------------+
| 0728db | CREATE DATABASE `0728db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
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

步骤1、查看编码。
命令：
show variables like ‘character_%’;
show variables like ‘collation_%’;

步骤2、修改mysql的数据目录下的my.ini配置文件。
[mysql] #大概在63行左右，在其下添加
…
default-character-set=utf8 #默认字符集
[mysqld] # 大概在76行左右，在其下添加
…
character-set-server=utf8
collation-server=utf8_general_ci
步骤3、重启服务。
步骤4、查看编码。
命令：
show variables like ‘character_%’;
show variables like ‘collation_%’;

MySQL5.x在配置完以后，如何修改配置文件？
为什么要修改my.ini文件？ 默认的数据库使用的字符集是latin1，需要修改为：utf8
修改哪些信息？

[mysql] #大概在63行左右，在其下添加
...
default-character-set=utf8 #默认字符集
[mysqld] # 大概在76行左右，在其下添加
...
character-set-server=utf8
collation-server=utf8_general_ci
1
2
3
4
5
6
7

修改完以后，需要重启服务。
net stop mysql服务名;
net start mysql服务名;

MySQL源码

进入MySQL下载界面，在“Select Operating System”选项选择“Source Code”。下载Windows版本选择“Windows (Architecture Independent), ZIP Archive”。

密码忘记问题

root用户密码忘记，重置的操作如下：
1、通过任务管理器或者服务管理，关掉mysqld(服务进程)
2、通过命令行+特殊参数开启mysqld mysqld – defaults-file=“D:\ProgramFiles\mysql\MySQLServer5.7Data\my.ini” --skip-grant-tables
3、此时，mysqld服务进程已经打开。并且不需要权限检查
4、mysql -uroot 无密码登陆服务器。另启动一个客户端进行
5、修改权限表
（1） use mysql;
（2）update user set authentication_string=password(‘新密码’) where user=‘root’ and Host=‘localhost’;
（3）flush privileges;
6、通过任务管理器，关掉mysqld服务进程。
7、再次通过服务管理，打开mysql服务。
8、即可用修改后的新密码登陆。

SQL

SQL分类

• DDL（Data Definition Languages：数据定义语言）：这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
  主要的语句关键字包括：create、drop、alter、rename、truncate等。
• DML（Data Manipulation Language：数据操作语言）：用于添加、删除、更新、查询数据库记录，并检查数据的完整性。
  主要的语句关键字包括：insert、update、delete、select等。
• DCL（Data Control Language：数据控制语言）：用于定义数据库、表、字段、用户的访问权限和安全级别。
  主要的语句关键字包括：grant、roolback、commit、revoke、savepoint等。

因为查询语句使用的非常频繁，所以很多人把查询语句单拎出来一类：
DQL（数据查询语言）。
还有单独将 commit、 rollback取出来称为
TCL（Transaction Control Language：事务控制语言）。

CRUD：创建（create）、更新（update）、读取（retrieve）和删除（delete）

SQL规范

每条命令以;或\g或\G结束。

SQL大小写规范：
1、MySQL在Windows下是大小写不敏感的。
2、MySQL在Linux环境下是大小写敏感的。
数据库名、表名、表的别名、变量名是严格区分大小写的
关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。

SQL注释

1、单行注释：
#注释文字(MySQL特有的方式)。
– 注释文字(–后面必须包含一个空格。)。
2、多行注释：/* 注释文字 */

表结构

表设计

字段命名规则

• 必须保证字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来。
• 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。

字段类型

字符

• char
  长度固定，不足使用空格填充；
  查询速度快，浪费空间；
  char(11)存储’abc’，占11位；
  最多容纳2000个字符。
• varchar
  长度不固定；
  查询速度慢，节省空间；
  varchar(11)存储’abc’，只占3位；
  最多容纳4000个字符。
  oracle中为varchar2。

面试题：char和varchar有什么区别？
char为定长字符串，char(n)，n最大为255
varchar为不定长字符串，varchar(n)，n最大长度为65535
比如：char(10)和varchar(10)存储abc，
char保存10个字符，abc占三个，其它会用空格填充；而varchar只用abc三个位置。

数字

• int int(3) oracle不支持，其使用number替代

• tinyint tinyint(1)

• long

• numeric(5,2) 3位整数，2位小数

• decimal和numeric日常理解为等效;decimal(8, 2)

  word:numeric [njuː’merɪk] adj.数字的；数值的 n.数；数字
  decimal ['desɪml] adj.小数的；十进位的 n.小数

日期

• date 包含年、月、日
• datetime 包含年、月、日和时、分、秒
• timestamp 时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

面试题：datetime和timestamp有什么区别？
数据库字段提供对日期类型的支持有date（日期）、time（时间）、datetime（日期+时间）、timestamp（时间戳）
datetime：日期+时间，存储和显示是一样的。
timestamp：时间戳，存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数。

图片

blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计，但目前主流都不会直接存储这样的数据，而只存储其访问路径，文件放在磁盘上。

word:blob [blɒb] n.小圆块；小斑点；一滴 v.弄脏

其他

• text
• float
• double

约束（constraint）

主键约束

主键是一条记录的唯一标识，具有唯一性，不能重复。

drop table tb_user;

create table tb_user(
    id int,
    name varchar(30),
    primary key(id)
);

insert into tb_user (id, name) values (1, ‘jack’);

唯一约束

name字段创建了唯一约束，插入数据时数据库会进行检查，如果插入的值相同，就会检查报错。

drop table tb_user;

create table tb_user(
    id int,
    name varchar(30) unique not null,
    phone varchar(20) unique not null,
    email varchar(30) unique not null,
    primary key(id)
);

desc tb_user;

insert into tb_user (id, name) values (1, ‘jack’);
insert into tb_user (id, name) values (2, ‘jack’);

执行上面语句出错：

Query : INSERT INTO tb_user (id,NAME) VALUES(2,‘jack’)
Error Code : 1062
Duplicate entry ‘jack’ for key ‘name’

展示表结构:

desc tb_user;

非空约束

drop table tb_user;

create table tb_user(
    id int auto_increment,
    name varchar(30) unique not null,
    age int,
    phone varchar(20) unique not null,
    email varchar(30) unique not null,
    primary key (id)
);

desc tb_user;

id为自增主键，插入数据时，指定id为null值无效，数据库会自动用下一个id值替代。
age字段类型因为设置为null，所以插入数据时可以为null。

insert into tb_user (id, age) values(null, null);

默认约束

drop table if exists stu; #如果表存在则删除，慎用,会丢失数据。

create table stu(
    id int primary key not null auto_increment, #自增主键
    name varchar(50) not null unique, #非空，唯一索引
    sex char(2) default ‘男’, #默认值
    phone char(18),
    age int,
    createdTime date default now()
);

desc stu;

主键、外键、唯一索引的区别

• Primary Key 主键约束，自动创建唯一索引
• Foreign Key 外键约束，外键字段的内容是引用另一张表的字段内容。
• Unique Index 唯一索引，唯一值但不是主键，

对于约束的好处是，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。

表操作

创建表

学生表

create table student(
    id int(4), #学号
    name varchar(20), #姓名
    sex char(2), #性别
    birthday date, #出生日期
    salary numeric(7, 2) #奖学金
);

班级表

create table class(
    classid int, #班级编号
    name varchar(30) #班级名称
);

查看表结构

desc student;
desc class;

注意：SQL不区分大、小写，MySQL中习惯表名、字段名全部大写，oracle中则习惯小写。

修改表

添加字段

alter table student add(classid int);

修改字段长度
注意长度不能小于已有的数据长度，否则会被永久破坏，不可修复。

alter table student modify column name varchar(20);

删除字段

alter table student drop column classid;

修改表名

rename table student to tb_student;

删除表

删除表中数据（清空）

delete from tb_student;
delete from class;

删除表；数据都被删除，数据将永久性丢失。

drop table tb_student;
drop table class;

drop、delete和truncate之间的区别

drop删除库或者表。数据和结构定义都会被删除。
delete和truncate只是删除表的数据。
delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录。
对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除，然后重建表的定义，所以自增主键会重新开始计数。

delete from student; #删除表中的所有数据，主键自增值不会重新计数。
truncate student; #删除表中的所有数据，主键自增值会从1开始计数。

drop table student; #删除表，包括表结构和表中的所有数据。
1
2
3
4

数据操作（CRUD）

新增

不声明字段，顺序必须和表的一致。

insert into student values (1, ‘宋江’, ‘男’, ‘1974-10-11’, 3000);

声明全部字段，顺序可以不一致，推荐方式。

insert into student (id, name, sex, salary, birthday) values (2, ‘卢俊义’, ‘女’, 30000, ‘2020-02-02’);

声明部分字段，没有的字段默认值null。

insert into student (id, name) values (3, ‘宋江’);

修改

修改一个字段，必须设置where条件

update student set sex=‘女’ where id=1;

修改多个字段，必须设置where条件。

update student set name=‘吴用’, birthday=‘2020-02-05’ where id=2;

修改某个字段；设置为null。

update student set name=null where id=3;

修改

update student set name=‘吴用’;

删除

删除一条记录，必须有where条件。

delete from student where id=3;

删除所有数据

delete from student;

查询

别名

起别名时，as都可以省略。
如果字段别名中没有空格，那么可以省略"“。
如果字段别名中有空格，那么不能省略”"。

数据导入指令

在命令行客户端登录MySQL，使用source指令导入，命令：

source d:\one_database.sql

空值（null）

在MySQL里面，空值不等于空字符串。一个空字符串的长度是0，而一个空值的长度是空。而且，在MySQL里面，空值是占用空间的。

select employee_id “员工编号”, salary “月薪”, commission_pct “奖金率”, salary*12*(1 + ifnull(commission_pct, 0)) “年薪” from employees;

伪表

select 1+1, 2*3;
select 1+1, 2*3 from dual; #dual伪表。

着重号

需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对``（着重号）引起来。

select * from `order`;

查询常数

select ‘宋江’ as corporation, last_name from employees;

查看表结构

describe employees;
desc employees;

别名

select employee_id 员工编号, last_name as 员工姓名, department_id “部门编号”, salary * 12 as “annual sal”
from employees;

distinct

select distinct department_id
from employees;

where

select *
from employees
where last_name=‘King’;

select employee_id, last_name, job_id, department_id
from employees
where department_id=90 ;

查询所有数据

select * from student;

查询id为1的记录

select * from student where id=1;

查询姓名为呼延灼的记录

select * from student where name = ‘呼延灼’;

查询姓呼的记录

select * from student where name like ‘呼%’;

查询姓名中含有呼的记录

select * from student where name like ‘%呼%’;

日期字段的年月日

select year(birthday) as 年, year(birthday) 年, month(birthday) 月, day(birthday) 日 from student;

某年的记录

select * from student where year(birthday) = 1988;

某个日期段的记录

select * from student
where year(birthday)>=1988 and year(birthday)<=2020;

select * from student
where year(birthday) between 1988 and 2020;

相关null

select * from student where sex = null;
select * from student where sex is null;
select * from student where sex is not null;

非，不包括null

select * from student where not sex=‘男’;
select * from t_user where not username=“宋江”;

排序，默认正序，desc倒序

select * from student
    where salary
    is not null
    order by salary;

select * from student
    where salary
    is not null
    order by salary asc;

select * from student
    where salary
    is not null
    order by salary desc;

保存点（savepoint）

可以看到删除数据是非常危险的，那如果我们直接在企业的生产环境操作这样的语句怎么防止误操作呢？
先设置手动提交，默认是自动提交

set @@autocommit = 0; 默认是1，手动提交
savepoint chen;
delete from student;
rollback to chen;
release savepoint chen;
先设置存储点，如果发现发现误操作删了数据，还可以回滚
set @@autocommit=0;
begin;
……语句
rollback;

事务（transaction）

事务的四个条件：ACID

事务必须满足4个条件（ACID）：

• 原子性（Atomicity，或称不可分割性） atomicity [ˌætəmˈɪsɪti] n. 原子数; 原子价; 原子化合 力; 原子力;
• 一致性（Consistency） consistency [kənˈsɪstənsi] n.连贯性，一致性; 稠度;
• 隔离性（Isolation，又称独立性） isolation [ˌaɪsəˈleɪʃn] n.隔离; 隔离状态; 孤独; 孤立状态;
• 持久性（Durability） durability [ˌdjʊərə’bɪlətɪ] n.耐久性; 持久性;

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的特性

答案：事务的四大特性为原子性，一致性，隔离性，持久性
⑴原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
⑵一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
⑶隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
⑷持久性（Durability）
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

下面举例说明，首先我们创造一张表；一列是用户，一列是金额。

(1) 原子性（Atomicity）
比如我们现在有一个任务要让A账户向B账户转100元，那么我们就需要执行两句
第一个是A账户-100
第二个是B账户+100
　　
原子性就是保证这两条数据要么都成功要么都不成功，否则就会出现总数多出100或者少100这样就会造成顾客损失或者公司损失，所以出现不成功或者成功一半就要回滚
⑵ 一致性（Consistency）
拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加总数还得是5000，这就是事务的一致性。
⑶ 隔离性（Isolation）
比如一个人A在发出转账请求时，B去同时查看两个账户余额，B要么看见的都是转账前的状态 都是1000块，要么看到的都是转账后的状态，一个900，一个1100，而不会看到一个900，一个1000或者一个1000，一个1100这种中间状态。
关于事务的隔离性数据库提供了多种隔离级别。
⑷ 持久性（Durability）
例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

数据库隔离级别

（1）读未提交（Read Uncommited）
指的是一个事务读取到另外一个事务还没有提交的内容。这种情况是必须要避免的。因为其他事务未提交的数据，是随时有可能进行回滚的，所以，任何时候，都不应该允许程序读取到某个事务还未提交的数据。如果读取到了别的事务未提交的数据，这种情况称为脏读。要想解决脏读的问题，可以提高数据库的事务隔离级别，把事务隔离级别设置为读已提交。
（2）读已提交（Read Committed）
这个隔离级别可以解决脏读的问题。
在该隔离级别下，不允许2个未提交的事务之间并行执行，但它允许在一个事务执行的过程中，另外一个事务得到执行并提交。这样，会出现一种情况，第一个事务前后两次select出来的某行数据，值可能不一样。值改变的原因是，穿插执行的事务2对该行数据进行了update操作。在同一个事务中，两次select出来的值不相同的问题称为不可重复读问题。要想解决不可重复读问题，需要把数据的隔离级别设置为可重复读。
（3）可重复读（Repeatable Read）
在这个隔离级别下，可以解决不可重复读的问题。
在该隔离级别下，在一个事务使用某行的数据的过程中，不允许别的事务再对该行数据进行操作。可重复读应该是给数据库的行加上了锁。这种隔离级别下，依旧允许别的事务在该表中插入和删除数据，于是就会出现，在事务1执行的过程中，如果先后两次select出符合某个条件的行，如果在这两次select直接另一个事务得到了执行，insert或delete了某些行，就会出现先后两次select出来的符合同一个条件的结果不一样，第一次select好像出现了幻觉一样，因此，这个问题也被称为幻读。要想解决幻读问题，需要将数据库的隔离级别设置为串行化。
（4）串行化（Serialization）
串行化可以解决幻读的问题。
它要求事务的执行完全串行执行。所以失去了并发的效率。

Mysql的默认隔离级别为可重复读。
总而言之，数据的事务隔离级别分为4种，从低到高依次为读未提交，读已提交，可重复读，串行化。与数据库事务隔离级别相关的问题有3个，分别是脏读，不可重复读，幻读。
脏读问题需要用读已提交来解决，但读已提交会存在不可重复读问题。
不可重复读问题需要用可重复读来解决，但可重复读会存在幻读问题。
幻读问题需要用串行化来解决。

前提

• 在MySQL中只有使用了InnoDB数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
• 事务用来管理insert、update、delete语句，因为这些操作才会“破坏”数据，查询（select）语句是不会的。
• 默认MySQL数据库的事务是开启，执行SQL后自动提交。
  

提交（commit）

多条语句时，批量执行，事务提交

set @@autocommit = 0;
begin;
insert into student (id) values (5);
update student set name=‘陈曦’ where id=5;
commit;

有了事务，多步操作就形成了原子性操作，高并发下也不会引起数据错乱。

回滚（rollback）

多条语句，批量执行，insert插入重复的主键导致失败，事务回滚。

set @@autocommit = 0;
begin;
insert into student (id) values (5);
update student set name=‘陈曦2’ where id=5;
rollback;

数据准备

员工表：emp

Oracle:

create table emp (
    empno number(4) not null,
    ename varchar2(10),
    job varchar2(9),
    mgr number(4),
    hiredate date,
    sal number(7, 2),
    comm number(7, 2),
    deptno number(2)
);

insert into emp values(100, ‘jack’, ‘副总’, null, ‘03-5月-02’, 90000, null, 1);
insert into emp values(200, ‘tony’, ‘总监’, 100, ‘02-2月-15’, 10000, 2000, 2);
insert into emp values(300, ‘hana’, ‘经理’, 200, ‘02-2月-17’, 8000, 1000, 2);
insert into emp values(400, ‘leo’, ‘员工’, 300, ‘02-2月-19’, 3000, 200.12, 2);
insert into emp values(500, ‘liu’, ‘员工’, 300, ‘02-3月-19’, 3500, 200.58, 2);

MySQL:

create table emp (
    empno numeric(4) not null,
    ename varchar(10),
    job varchar(9),
    mgr numeric(4),
    hiredate date,
    sal numeric(7, 2),
    comm numeric(7, 2),
    deptno numeric(2)
);

insert into emp values(100, ‘jack’, ‘副总’, null, ‘2002-05-03’, 90000, null, 1);
insert into emp values(200, ‘tony’, ‘总监’,100, ‘2015-02-02’, 10000, 2000, 2);
insert into emp values(300, ‘hana’, ‘经理’, 200, ‘2017-02-02’, 8000, 1000, 2);
insert into emp values(400, ‘leo’, ‘员工’, 300, ‘2019-02-22’, 3000, 200.12, 2);
insert into emp values(500, ‘liu’, ‘员工’, 300, ‘2019-03-19’, 3500, 200.58, 2);

部门表:dept

Oracle:

create table dept (
    deptno number(2) not null,
    dname varchar2(14),
    loc varchar2(13)
);

insert into dept values(1, ‘accounting’, ‘一区’);
insert into dept values(2, ‘research’, ‘二区’);
insert into dept values(3, ‘operations’, ‘二区’);

MySQL:

create table dept(
    deptno numeric(2) not null,
    dname varchar(14),
    loc varchar(13)
);

insert into dept values(1, ‘accounting’, ‘一区’);
insert into dept values(2, ‘research’, ‘二区’);
insert into dept values(3, ‘operations’, ‘二区’);

工资级别表:salarygrade

Oracle:

create table salarygrade(
    grade number,
    losal number,
    hisal number
);

MySQL:

create table salarygrade(
    grade numeric,
    losal numeric,
    hisal numeric
);

基础函数

lower、upper

变小写

select lower(dname) from dept;

upper
变大写

select upper(dname) from dept;

length

select dname, length(dname) from dept;

substr

截取，从1开始

select dname, substr(dname, 1, 3) from dept;

concat

拼字符串

select dname, concat(dname, ‘(’) from dept;
select dname, concat(dname, ‘(’, loc) from dept;
select dname, concat(dname, ‘(’, loc, ‘)’) from dept;

综合案例：首字母大写

select dname,
    substr(dname, 1, 1), #截取首个字母。
    upper(substr(dname, 1, 1)), #将首个字母变成大写。

    substr(dname, 2, length(dname)), #截取出除了第一个字母以外的字符。

    concat(upper(substr(dname, 1, 1)), substr(dname, 2, length(dname))) #首字母大写实现方式二。

    from dept;

replace

select loc, replace(loc, ‘区’, ‘区域’) from dept;

round、ceil、floor

round四舍五入，ceil向上取整（大于目标数的最小整数），floor向下取整（小于目标数的最大整数）。

select ename, comm,

    round(comm, 1), round(comm, 2), round(comm, 3),
    ceil(comm),
    floor(comm)

    from emp;

uuid

select uuid();

select replace(uuid(), ‘-’, ‘’),
    length(replace(uuid(), ‘-’, ‘’));

日期函数

now

select now();
select current_date();
select current_time();
select curdate();
select curtime();

select sysdate from dual #oracle当前时间，dual内置虚拟表
trunc和floor都可以

last_day

每月最大日期

select ename, hiredate, last_day(hiredate) from emp;

date

select hiredate, year(hiredate), month(hiredate), day(hiredate) from emp;

date_format

日期转字符串，注意格式的大小写。
%Y 4位 %y 2位
%H 24小时 %h 12小时

select date_format(now(), ‘%Y-%m-%d’);
select date_format(now(), ‘%Y-%m-%d %H:%i:%s’);
select date_format(now(), ‘%Y年%m月%d日’);
select date_format(now(), ‘%Y年%m月%d日 %H时%i分%s秒’);

str_to_date

字符串转日期

select str_to_date(‘2020-05-08’, ‘%Y-%m-%d’) from emp;
select year(str_to_date(‘2020-05-08’, ‘%Y-%m-%d’)) from emp;

条件查询

distinct

使用distinct关键字，去除重复的记录行

select loc from dept;
select distinct loc from dept;

where

select * from emp where 1=1;
select * from emp where 1=0;
select * from emp where empno=100;
select * from emp where ename=‘tony’ and deptno=2;

#Oracle区分大小写，Mysql不区分大小写

select * from dept where dname=‘ACCOUNTING’;
select * from dept where dname=‘Accounting’;
select * from dept where dname=‘accounting’;

like

通配符%代表0到n个字符，通配符下划线_代表1个字符

select * from emp where ename like ‘t%’; #t字母开头，效率高
select * from emp where ename like ‘%n%’; #中间含有n
select * from emp where ename like ‘t___’; #3个下划线
select * from emp where ename like ‘__n%’; #2个下划线

and&or

and并且，or或者

select * from dept where dname=‘accounting’ and loc=‘一区’;
select * from dept where dname=‘accounting’ or loc=‘二区’;

null

select * from emp where mgr is null; #字段内容为null的
select * from emp where mgr is not null; #字段内容不为null的

nvl

字段值为null时替换；loc为null替换为‘无’
insert into dept (deptno, dname) values (4, ‘workspace’);
select deptno, dname, nvl(loc, ‘无’) as loc from dept;

between and

between x and y 在x和y之间的值

select * from emp
where sal between 5000 and 10000;
等价于
select * from emp
where sal >= 5000 and sal <= 10000;

union

把多个结果集合并，前提条件，两个结果集列对应，个数和类型一致

select * from emp where empno=100
union
select * from emp where empno=200;

报错：The used SELECT statements have a different number of columns

select * from emp
union
select * from dept;

没有实际意义

select empno, ename from emp
union
select deptno, dname from dept;

limit

分数最高的记录：按分数排序后，limit n，返回前n条。oracle做的很不好，实现繁琐，而mysql做的很好，语法简洁高效。

select * from emp limit 3 #返回前3条
select * from emp limit 0, 3 #返回前3条（offset, count）offset从0开始
select * from emp limit 1, 3 #返回第2到4条，共计3条

子查询

概念:子查询是指嵌入在其他select语句中的select语句，也叫嵌套查询。

单行子查询

返回结果为一个

列出tony所在部门的所有人员

select deptno from emp where ename=‘tony’;
select * from emp where deptno = (select deptno from emp where ename=‘tony’);

deptno = 使用等号，后面的查询结果只能为一个值

多行子查询

select * from emp where job in (‘经理’, ‘员工’);
select * from emp where job in (select distinct job from emp);

例子：查询

薪资统计

薪水大于等于10000的员工

select * from emp where sal>=10000;

薪水在5000到10000之间的员工

select * from emp where sal>=5000 and sal<=10000;

入职统计

2015年以前入职的老员工

select * from emp where date_format(hiredate, ‘%Y-%m-%d’)<=‘2015-01-01’;

2019年以后签约的员工，日期进行格式转换后方便比较

select * from emp where date_format(hiredate, ‘%Y-%m-%d’)>=‘2019-01-01’;

2015年到2019年入职的员工

select * from emp where
str_to_date(hiredate, ‘%Y-%m-%d’)>=‘2015-01-01’
and
str_to_date(hiredate, ‘%Y-%m-%d’)<=‘2019-12-31’;

年薪统计

公司13薪，年底双薪，统计员工的年薪=sal13+comm13
副总不按月奖金计算

select empno, ename, job, sal * 13+comm13 from emp;
select empno, ename, job, sal * 13+nvl(comm13, 0) from emp;
select empno, ename, job, sal * 13+nvl(comm13, 0) as 年收入 from emp;
select empno, ename, job, sal * 13+nvl(comm13, 0) as ‘年收入’ from emp;
select empno, ename, job, sal * 13+nvl(comm*13 ,0) as “年收入” from emp;

计算时字段为数值类型应该按0来计算，按null达不到我们预期效果。

备份和恢复

概念:
数据库是我们业务系统的核心，业务系统的数据都保存在数据库中，一旦数据丢失，将带来难以估量的损失。特别现在出现黑客劫持，黑客攻击数据库服务器后，将上面的数据全部加密，如果不交付赎金，就自动删除数据。这种现象已经屡有发生，我们应该养成备份的习惯。防止这种极端情况的发生。

备份

方式一：

C:>mysqldump -uroot -proot yhdb > d:/yhdb20190901-tony.sql

注意：
备份不是在mysql环境里，而是直接在操作系统环境中执行mysqldump命令
MySQL的dump为sql纯文本文件，oracle备份为二进制文件，命令有差异

方式二：

C:>mysqldump -uroot -proot --databases yhdb > d:\yh.sql

加—databases参数，导出的sql脚本中会含有创建数据和打开数据的步骤
起名规则：数据库名称-备份日期-备份人

恢复

方式一：
Mysql习惯创建sql的备份文件没有创建数据库的sql语句，需手动创建

MariaDB [yhdb]> create database yhdb charset utf8; #创建库，设置u8
MariaDB [yhdb]> use yhdb; #打开yhdb数据库
MariaDB [yhdb]> source d:/yhdb-20190901-tony.sql #恢复数据
MariaDB [yhdb]> show tables; #展示所有表

方式二：
使用—databases参数导出的sql含有数据库创建脚本，就可以无需创建数据库

C:>mysql -uroot -proot < d:/yh.sql

数据库优化

• where条件查询时要类型匹配，否则索引失效

customer表的id字段为varchar类型，按整数类型查询全表遍历，查的很快也是由于缓存而非使用索引。

• like原则
  like ‘宋%’ 会使用索引
  like ‘%宋%’ 索引失效
• 查询字段尽量不要使用*

select * from tb_item;
select id, title, sell_point, status, created, updated from tb_item;

• 量变引起质变,不同字段类型查询性能差异很大

select count(1) from customer_varchar ; 一千万数据，耗时2:14:364
select count(1) from customer_bigint; 一千万数据，耗时15:959
select count(1) from customer_int ; 一千万数据，耗时14:352
select count(1) from customer_char; 一千万数据，耗时1:55:409

• 分组

select age, count() from customer_age_char group by age ; 34:352
select age, count() from customer_age_int group by age; 18:798

小结

中文乱码

如果在dos命令下执行insert插入中文数据，数据又乱码，那现在sqlYog客户端执行下面命令：

set names utf8;
set names gbk;

设置客户端字符集和服务器端相同。如果不知道它到底用的什么编码？怎么办呢？很简单，两个都尝试下，哪个最后操作完成，查询数据库不乱码，就用哪个。
为何会造成乱码呢？
MySQL数据库默认字符集是lantin1，也就是以后网页中遇到的ISO8859-1，它是英文字符集，不支持存放中文。我们创建库时，可以指定字符集：

create database yhdb charset utf8;

但这样很容易造成服务器和客户端编码集不同，如服务器端utf8，客户端ISO8859-1。mysql和客户端工具都有习惯的默认编码设置，好几个地方，要都统一才可以保证不乱码。
我们只要保证创建数据库时用utf8，使用可视化工具一般就基本正确。

注释

/* 很多注释内容 */
“#” 一行注释内容
“–” 一行注释内容，这个使用较多

聚合（aggregation）

count

记录总数

select * from emp;
select count() from emp;
select count() from emp where ename like ‘t%’;
select count(1) from emp;
select count(empno) from emp;

习惯使用*的方式，推荐使用后两种方式

max

最大值

select max(sal) from emp;

min

最小值

select min(sal) from emp;
select min(sal), max(sal) from emp;

利用子查询得到最大薪资的人员信息，如果最大薪资相同可能多条结果
select ename, max(sal) from emp

子查询时后面的sql只能返回一个值
select ename from emp where sal = (select max(sal) from emp)

avg

平均值

select avg(sal) from emp;

select ename, avg(sal) from emp #注意sql不是想出来的，而是根据业务去实现的，这句话虽然能执行，但却是错误的，没有业务的实际意义。平均薪资跟某个人有什么关系呢？

高于平均工资的员工有

select * from emp where sal >= (select avg(sal) from emp);

sum

合计

select sum(sal) from emp;

分组（group）

groupby

group by 用于对查询的结果进行分组统计
having 子句类似where限制返回结果，where用在主句中，having用在分组中
注意：使用分组限制会居多

错误，统计时非统计字段必须分组，能执行，但无业务意义

select deptno,max(sal) from emp;

每个部门最高的薪资和平均薪资

select deptno, max(sal),AVG(sal) from emp
group by deptno
order by max(sal);

每个部门每个岗位的最高薪资和平均薪资，结果中的非聚合列必须出现在分组中，否则业务意义不对

select deptno, job, max(sal), avg(sal) from emp
group by deptno,job
order by max(sal);

having

分组后数据的过滤，就是where语句，只是having专门配合groupby
平均工资小于8000的部门

select deptno, avg(sal) from emp
group by deptno
having abg(sal)<8000

学生姓名重名名单*

select name, count(name) from student
group by name
having count(name)>1;

学生成绩统计

创建表添加数据

drop table tb_cousre_score;
create table tb_cousre_score
(
id numeric,
name varchar(20),
course varchar(20),
score numeric
);

insert int tb_cousre_score (id, name, course, score) values (1, ‘张三’, ‘语文’, 88);
insert int tb_cousre_score (id, name, course, score) values (1, ‘张三’, ‘语文’, 67);
insert int tb_cousre_score (id, name, course, score) values (1, ‘张三’, ‘数学’, 76);
insert int tb_cousre_score (id, name, course, score) values (1, ‘张三’, ‘英语’, 43);
insert int tb_cousre_score (id, name, course, score) values (1, ‘张三’, ‘历史’, 56);
insert int tb_cousre_score (id, name, course, score) values (1, ‘张三’, ‘化学’, 11);
insert int tb_cousre_score (id, name, course, score) values (2, ‘李四’, ‘语文’, 54);
insert int tb_cousre_score (id, name, course, score) values (2, ‘李四’, ‘数学’, 81);
insert int tb_cousre_score (id, name, course, score) values (2, ‘李四’, ‘英语’, 64);
insert int tb_cousre_score (id, name, course, score) values (2, ‘李四’, ‘历史’, 93);
insert int tb_cousre_score (id, name, course, score) values (2, ‘李四’, ‘化学’, 27);
insert int tb_cousre_score (id, name, course, score) values (3, ‘王五’, ‘语文’, 24);
insert int tb_cousre_score (id, name, course, score) values (3, ‘王五’, ‘数学’, 25);
insert int tb_cousre_score (id, name, course, score) values (3, ‘王五’, ‘英语’, 8);
insert int tb_cousre_score (id, name, course, score) values (3, ‘王五’, ‘历史’, 45);
insert int tb_cousre_score (id, name, course, score) values (3, ‘王五’, ‘化学’, 1);
commit;

学生的成绩条数

select name, count(name) from tb_cousre_score
group by name;

行列转置

select id, name,
case when course=‘语文’ then score else 0 end 语文
from tb_cousre_score

注意：单纯这样没有什么业务意义，一般配合分组和聚合函数

每门最高成绩

分组groupby，排序orderby，聚合（最大值）max，
判断casewhen类似java中的if-else
多次考试取最高的成绩，张三有2次语文成绩67和88

select id, name,
max(case when course=‘语文’ then score else 0 end) 语文,
max(case when course=‘数学’ then score else 0 end) 数学,
max(case when course=‘英语’ then score else 0 end) 英语,
max(case when course=‘历史’ then score else 0 end) 历史,
max(case when course=‘化学’ then score else 0 end) 化学
from tb_cousre_score
group by id, name
order by id

每门成绩总和

select id,NAME,
sum(case where course=‘语文’ then score else 0 end) 语文,
sum(case where course=‘数学’ then score else 0 end) 数学,
sum(case where course=‘英语’ then score else 0 end) 英语,
sum(case where course=‘历史’ then score else 0 end) 历史,
sum(case where course=‘化学’ then score else 0 end) 化学
from tb_cousre_score
group by id, name
order by id

集合

union 并集去重

select * from dept where loc=‘一区’
union
select * from dept where loc=‘二区’

实际开发中可以连接多个表的数据 a union b union c

select * from dept
union
select * from dept
union
select * from dept

union all 并集

会有重复数据，不进行合并

select * from dept
union all
select * from dept

intersect 交集

select * from dept where loc is not null
intersect
select * from dept

minus 差集

mysql不支持，可以使用left join变相实现，而oracle支持，很少用

select * from dept where loc is not null
minus
select * from dept

select * from dept
minus
select * from dept where loc is not null

注意：两者的差异，第一个结果为空，第二个有一条记录，为何？因为差集的意思是返回存在在第一个集合中，不存在在第二个集合中的数据。和数学上的差集有所不同。

多表联查

笛卡尔积

多表查询是指基于两个和两个以上的表的查询。在实际应用中，查询单个表可能不能满足你的需求，如显示员工表emp中不只显示deptno，还要显示部门名称，而部门名称dname在dept表中。

把两个表的数据都拼接起来

select * from emp, dept;

查询出员工编号、员工工资及所在部门的名称

select
d.dname,
e.empno, e.ename, e.sal
from emp e, dept d;

上面这种查询两个表的方式称为：笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但作为概念必须了解，多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。
这点很值得注意，实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，先在内存中构建一个大的结果集，然后在进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。

内连接 inner

#显示部门2的员工和工资

SELECT d.dname,e.ename,e.sal FROM emp e,dept d
WHERE e.deptno=d.deptno AND e.deptno=2

SELECT
d.dname,e.ename,e.job
FROM
(SELECT deptno,dname FROM dept) d
INNER JOIN
(SELECT deptno,ename,job FROM emp) e
ON d.deptno = e.deptno
WHERE d.deptno=2

左连接 left

也称为左外连接left outer join

SELECT
d.dname,e.ename,e.job
FROM
(SELECT deptno,dname FROM dept) d
LEFT JOIN
(SELECT deptno,ename,job FROM emp) e
ON d.deptno=e.deptno

下面是Oracle的独特语法，更加简洁，但不推荐，没有上面的语法更加清晰

select * from dept d,emp e where d.deptno=e.deptno(+)
select d.dname,e.ename,e.job from dept d,emp e
where d.deptno = e.deptno(+)

右连接 right

也称为右外连接right outer join

select
d.dname,e.ename,e.job
from
(select deptno,dname from dept) d
right join
(select deptno,ename,job from emp) e
on d.deptno = e.deptno

下面是Oracle的独特语法：

select * from dept d,emp e where d.deptno(+) = e.deptno
select d.dname,e.ename,e.job from dept d,emp e
where d.deptno(+) = e.deptno

自连接 self

一般只分为：内连接、左连接、右连接，自连接是指在同一张表的连接查询，下面代码本质还是内连接。

通过别名区分

SELECT worker.ename, boss.ename FROM emp worker, emp boss
WHERE worker.mgr = boss.empno AND worker.ename = ‘tony’;

简写

select w.ename, b.ename from emp w, emp b
where w.mgr = b.empno and w.ename = ‘tony’;

With

使用with子句可以让子查询重用相同的with查询块，通过select调用，很少用

WITH num AS (SELECT d.deptno FROM dept d WHERE d.deptno=1)
SELECT e.ename,e.job,e.sal FROM emp e WHERE e.deptno IN (SELECT * FROM num);

万能连接

多表联查有个技巧，其实所有的连接方式都可以转换为左连接！如果记录内容完整，如每个部门对应有员工，每个员工对应有部门，内连接就等价左连接，结果内容一样。右连接是按右侧表关联，那把它换到左边，那不就是左连接，所以换先位置它们就等价。所以记住左连接写法即可。
左连接写的过程，写它是有套路的，这个套路记住，就特别简单。

实现步骤：

• 第一步：左边查询括号写别名
• 第二步：右边查询括号写别名
• 第三步：left join恒中间，后面加on是条件
• 第四步：select挑字段，别名后面写字段

多表联查案例

班级学生（一对多）

SELECT
c.name,
s.*
FROM
(SELECT classid,NAME FROM class) c
LEFT JOIN
(SELECT classid,id,NAME,sex,birthday,salary FROM student) s
ON c.classid=s.classid

部门员工（一对多）

SELECT
d.deptno,d.dname,d.loc,
e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm
FROM
(SELECT deptno,dname,loc FROM dept) d
LEFT JOIN
(SELECT deptno,empno,ename,job,mgr,hiredate,sal,comm FROM emp) e
ON d.deptno=e.deptno

员工和上级（自关联，一对一）

通过对表的两个查询SQL拼接而成
SELECT
e.deptno,e.empno,e.ename,
e.mgr,m.ename manager,
e.job,e.hiredate,e.sal,e.comm
FROM
(SELECT deptno,empno,ename,job,mgr,hiredate,sal,comm FROM emp) e
LEFT JOIN
(SELECT empno,ename FROM emp) m
ON e.mgr=m.empno

部门、员工和上级（三级关联）

两次leftjoin，把第一次的结果在()成一个表
SELECT
e.deptno,e.dname,e.loc,
e.empno,e.ename,e.job,
e.mgr,m.ename manager,
e.hiredate,e.sal,e.comm
FROM
(
SELECT
d.deptno,d.dname,d.loc,
e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm
FROM
(SELECT deptno,dname,loc FROM dept) d
LEFT JOIN
(SELECT deptno,empno,ename,job,mgr,hiredate,sal,comm FROM emp) e
ON d.deptno=e.deptno
) e
LEFT JOIN
(SELECT empno,ename FROM emp) m
ON e.mgr=m.empno

商品分类和商品（一对多）

SELECT
c.name,i.title,i.price
FROM
(SELECT cid,title,price FROM tb_item) i
LEFT JOIN
(SELECT id,NAME FROM tb_item_cat) c
ON i.cid=c.id

商品和商品描述（一对一）

SELECT
i.title,i.price,d.item_desc
FROM
(SELECT id,title,price FROM tb_item) i
LEFT JOIN
(SELECT item_id,item_desc FROM tb_item_desc) d
ON i.id=d.item_id

用户、角色、中间表（多对多）

#某个用户所拥有的角色
SELECT role_id,NAME FROM role_p
WHERE role_id IN
(
SELECT role_id FROM role_user_p
WHERE user_id=(SELECT user_id FROM user_p WHERE user_id=100)
)

查询综合练习

drop table if exists courses;
drop table if exists scores;
drop table if exists students;
drop table if exists teachers;

create table courses
(
cno varchar(5) not null, --课程编号
cname varchar(10) not null, --课程名称
tno varchar(10) not null, --讲师编号 fk
primary key (cno)
);

create table scores
(
sno varchar(3) not null, --学生编号
cno varchar(5) not null, --课程编号
degree numeric(10,1) not null, --分数
primary key (sno, cno)
);

create table students
(
sno varchar(3) not null, --学生编号
sname varchar(4) not null, --学生姓名
ssex varchar(2) not null, --学生性别
sbirthday datetime, --学生生日
class varchar(5), --学生班级编号
primary key (sno)
);

create table teachers
(
tno varchar(3) not null, --老师编号
tname varchar(4) not null, --老师姓名
tsex varchar(2) not null, --老师性别
tbirthday datetime not null, --老师生日
prof varchar(6), --老师职称
depart varchar(10), --老师所教系
primary key (tno)
);

alter table courses add constraint FK_Reference_3 foreign key (tno)
references teachers (tno) on delete restrict on update restrict;

alter table scores add constraint FK_Reference_1 foreign key (sno)
references students (sno) on delete restrict on update restrict;

alter table scores add constraint FK_Reference_2 foreign key (cno)
references courses (cno) on delete restrict on update restrict;

insert into students (sno, sname, ssex, sbirthday, class) values (108, ‘曾华’, ‘男’, ‘1977-09-01’, 95033);
insert into students (sno, sname, ssex, sbirthday, class) values (105, ‘匡明’, ‘男’, ‘1975-10-02’, 95031);
insert into students (sno, sname, ssex, sbirthday, class) values (107, ‘王丽’, ‘女’, ‘1976-01-23’, 95033);
insert into students (sno, sname, ssex, sbirthday, class) values (101, ‘李军’, ‘男’, ‘1976-02-20’, 95033);
insert into students (sno, sname, ssex, sbirthday, class) values (109, ‘王芳’, ‘女’, ‘1975-02-10’, 95031);
insert into students (sno, sname, ssex, sbirthday, class) values (103, ‘陆君’, ‘男’, ‘1974-06-03’, 95031);

insert into teachers (tno, tname, tsex, tbirthday, prof, depart) values (804, ‘易天’, ‘男’, ‘1958-12-02’, ‘副教授’, ‘计算机系’);
insert into teachers (tno, tname, tsex, tbirthday, prof, depart) values (856, ‘王旭’, ‘男’, ‘1969-03-12’, ‘讲师’, ‘电子工程系’);
insert into teachers (tno, tname, tsex, tbirthday, prof, depart) values (825, ‘李萍’, ‘女’, ‘1972-05-05’, ‘助教’, ‘计算机系’);
insert into teachers (tno, tname, tsex, tbirthday, prof, depart) values (831, ‘陈冰’, ‘女’, ‘1977-08-14’, ‘助教’, ‘电子工程系’);

insert into courses (cno, cname, tno) values (‘3-105’, ‘计算机导论’, 825);
insert into courses (cno, cname, tno) values (‘3-245’, ‘操作系统’, 804);
insert into courses (cno, cname, tno) values (‘6-166’, ‘模拟电路’, 856);
insert into courses (cno, cname, tno) values (‘6-106’, ‘概率论’, 831);
insert into courses (cno, cname, tno) values (‘9-888’, ‘高等数学’, 831);

insert into scores (sno, cno, degree) values (103, ‘3-245’, 86);
insert into scores (sno, cno, degree) values (105, ‘3-245’, 75);
insert into scores (sno, cno, degree) values (109, ‘3-245’, 68);
insert into scores (sno, cno, degree) values (103, ‘3-105’, 92);
insert into scores (sno, cno, degree) values (105, ‘3-105’, 88);
insert into scores (sno, cno, degree) values (109, ‘3-105’, 76);
insert into scores (sno, cno, degree) values (101, ‘3-105’, 64);
insert into scores (sno, cno, degree) values (107, ‘3-105’, 91);
insert into scores (sno, cno, degree) values (108, ‘3-105’, 78);
insert into scores (sno, cno, degree) values (101, ‘6-166’, 85);
insert into scores (sno, cno, degree) values (107, ‘6-106’, 79);
insert into scores (sno, cno, degree) values (108, ‘6-166’, 81);

desc courses;
desc scores;
desc students;
desc teachers;

select * from courses;
select * from scores;
select * from students;
select * from teachers;

• 左连接

select c.cno, c.cname, t.tno, t.tname
from
(select tno, tname from teachers) t
left join
(select tno, cno, cname from courses) c
on t.tno=c.tno;

• 内连接

select c.cno, c.cname, t.tno, t.tname
from
(select tno, tname from teachers) t
inner join
(select tno, cno, cname from courses) c
on t.tno=c.tno;

• 右连接

select c.cno, c.cname, t.tno, t.tname
from
(select tno, tname from teachers) t
right join
(select tno, cno, cname from courses) c
on t.tno=c.tno;

#查询students表的所有记录

select * from students;

#查询students表中的所有记录的sname、ssex和class列

select sname, ssex, class from students;

#查询teachers表所有的单位即不重复的depart列

select distinct depart from teachers;

#查询scores表中成绩在60到80之间的所有记录

select * from scores where degree between 60 and 80;
select * from scores where degree>=60 and degree<=80;

#查询scores表中成绩为85，86或88的记录

select * from scores where degree in (85, 86, 88);
select * from scores where degree=85 or degree=86 or degree=88;

#查询students表中“95031”班或性别为“女”的同学记录

select * from students where class=‘95031’ or ssex=‘女’;

#以班级class降序查询students表的所有记录

select * from students order by class desc;

#以cno升序、degree降序查询scores表的所有记录

select * from scores order by cno asc, degree desc;

#查询“95031”班的学生人数

select count(1) from students where class=‘95031’;

#查询每个班的学生人数

select class, count(class) from students group by class;

#查询scores表中的最高分的学生学号和课程号

select sno, cno, degree from scores where degree=(select max(degree) from scores);
select sno, cno, degree from scores order by degree desc limit 1;

#查询‘3-105’号课程的平均分

select avg(degree), round(avg(degree), 2) from scores where cno=‘3-105’;

#查询最高分

select max(degree) from scores;

#查询最低分

select min(degree) from scores;

#查询最低分大于70，最高分小于90的sno列

select sno from scores
group by sno
having min(degree)>70 and max(degree)<90;

#查询95033班和95031班全体学生的记录

select * from students;
select * from students where class in (‘95033’, ‘95031’);
select * from students where class=‘95033’ or class=‘95031’;
select * from students where class in (‘95033’,‘95031’) order by class desc;

#查询存在有85分以上成绩的课程cno

select * from scores;
select distinct cno from scores where degree>85;

#查询所有教师和同学的name、sex和birthday

select sname, ssex, sbirthday from students
union
select tname, tsex, tbirthday from teachers;

#查询所有“女”教师和“女”同学的name、sex和birthday

select sname, ssex, sbirthday from students where ssex=‘女’
union
select tname, tsex, tbirthday from teachers where tsex=‘女’;

#查询所有任课教师的tname和depart

select tname, depart from teachers
where tno in (
select tno from courses
);

#查询所有未讲课的教师的tname和depart

select tname, depart from teachers
where tno not in (
select tno from courses
);

#查询至少有2名男生的班号

#查询students表中姓“王”的同学记录

#查询students表中不姓“王”的同学记录

#查询students表同名的同学记录

#查询students表中每个学生的姓名和年龄

#查询students表中最大和最小的sbirthday日期值

#以班号和年龄从大到小的顺序查询student表中的全部记录

#查询“男”教师及其所上的课程

#查询最高分同学的sno、cno和degree列

#查询课程对应的老师姓名、职称、所属系

#查询scores表中至少有5名学生选修的并以3开头的课程的平均分数

#查询所有学生的sname、cno和degree列

#查询所有学生的sno、cname和degree列

#查询所有学生的sname、cname和degree列

#创建等级grade表，现查询所有同学的sno、cno和rank级别列

#查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录

#查询scores中选学一门以上课程的同学中分数为非最高分成绩的记录

#查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录

#查询和学号为108的同学同年出生的所有学生的sno、sname和sbirthday列

#查询“95033”班所选课程的平均分*

#查询“张旭“教师任课的学生成绩*

#查询选修某课程的同学人数多于5人的教师姓名

#查询出“计算机系“教师所教课程的成绩表

#查询“计算机系”与“电子工程系“不同职称的教师的tname和prof

#查询选修编号为“3-105“课程且成绩至少高于任意选修编号为“3-245”的同学的成绩的cno、sno和degree,并按degree从高到低次序排序

#查询选修编号为“3-105”且成绩高于所有选修编号为“3-245”课程的同学的cno、sno和degree

#查询成绩比该课程平均成绩低的同学的成绩表*

#查询和“李军”同性别的所有同学的sname

#查询和“李军”同性别并同班的同学sname

#查询所有选修“计算机导论”课程的“男”同学的成绩表