持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，而持久化的实现过程大多通过各种关系数据库来完成。
持久化的主要作用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。
DB：数据库（Database）
即存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据。
DBMS：数据库管理系统（Database Management System）
是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。
SQL：结构化查询语言（Structured Query Language）
专门用来与数据库通信的语言。
数据库管理系统(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。
数据库管理系统、数据库和表的关系如图所示：
目前互联网上常见的数据库管理软件有Oracle、MySQL、Microsoft SQL Server、DB2、PostgreSQL、Access、Sybase、Informix等。以下是2022年DB-Engines Ranking对各数据库受欢迎程度进行调查后的统计结果（查看数据库排名网址：https://db-engines.com/en/ranking官网首页地址：https://db-engines.com）。
Oracle
1979 年，Oracle 2 诞生，它是第一个商用的 RDBMS（关系型数据库管理系统）。随着 Oracle 软件的名气越来越大，公司也改名叫 Oracle 公司。
2007年，总计85亿美金收购BEA Systems。
2009年，总计74亿美金收购SUN。此前的2008年，SUN以10亿美金收购MySQL。意味着Oracle 同时拥有了MySQL 的管理权，至此 Oracle 在数据库领域中成为绝对的领导者。
2013年，甲骨文超越IBM，成为继Microsoft后全球第二大软件公司。
如今 Oracle 的年收入达到了 400 亿美金，足以证明商用（收费）数据库软件的价值。
Microsoft SQL Server
SQL Server 是微软开发的大型商业数据库，诞生于1989年。C#、.net等语言常使用，与WinNT完全集成，也可以很好地Microsoft BackOffice产品集成。
DB2
IBM公司的数据库产品,收费的。常应用在银行系统中。
PostgreSQL
PostgreSQL 的稳定性极强，最符合SQL标准，开放源码，具备商业级DBMS质量。PG对数据量大的文本以及SQL处理较快。
SyBase
已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner。
SQLite
嵌入式的小型数据库，应用在手机端。 零配置，SQlite3不用安装，不用配置，不用启动，关闭或者配置数据库实例。当系统崩溃后不用做任何恢复操作，再下次使用数据库的时候自动恢复。
informix
IBM公司出品，取自Information 和Unix的结合，它是第一个被移植到Linux上的商业数据库产品。仅运行于unix/linux平台，命令行操作。 性能较高，支持集群，适应于安全性要求极高的系统，尤其是银行，证券系统的应用。
MySQL的历史就是整个互联网的发展史。互联网业务从社交领域、电商领域到金融领域的发展，推动着应用对数据库的需求提升，对传统的数据库服务能力提出了挑战。高并发、高性能、高可用、轻资源、易维护、易扩展的需求，促进了MySQL的长足发展。
MySQL从5.7版本直接跳跃发布了8.0版本 ，可见这是一个令人兴奋的里程碑版本。MySQL 8版本在功能上做了显著的改进与增强，开发者对MySQL的源代码进行了重构，最突出的一点是多MySQL Optimizer优化器进行了改进。不仅在速度上得到了改善，还为用户带来了更好的性能和更棒的体验。
为什么如此多的厂商要选用MySQL？
大概总结的原因主要有以下几点：
1、开放源代码，使用成本低。
2、性能卓越，服务稳定。
3、软件体积小，使用简单，并且易于维护。
4、历史悠久，社区用户非常活跃，遇到问题可以寻求帮助。
5、许多互联网公司在用，经过了时间的验证。
Oracle 更适合大型跨国企业的使用，因为他们对费用不敏感，但是对性能要求以及安全性有更高的要求。
MySQL 由于其体积小、速度快、总体拥有成本低，可处理上千万条记录的大型数据库，尤其是开放源码这一特点，使得很多互联网公司、中小型网站选择了MySQL作为网站数据库（Facebook，Twitter，YouTube，阿里巴巴/蚂蚁金服，去哪儿，美团外卖，腾讯）。
从排名中我们能看出来，关系型数据库绝对是 DBMS 的主流，其中使用最多的 DBMS 分别是 Oracle、MySQL 和 SQL Server。这些都是关系型数据库（RDBMS）。
这种类型的数据库是最古老的数据库类型，关系型数据库模型是把复杂的数据结构归结为简单的二元关系 （即二维表格形式）。
关系型数据库以行(row)和列(column) 的形式存储数据，以便于用户理解。这一系列的行和列被称为表(table) ，一组表组成了一个库(database)。
表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系型数据库，就是建立在关系模型基础上的数据库。
SQL 就是关系型数据库的查询语言。
复杂查询 可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
事务支持 使得对于安全性能很高的数据访问要求得以实现。
非关系型数据库，可看成传统关系型数据库的功能阉割版本 ，基于键值对存储数据，不需要经过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等。
NoSQL 对 SQL 做出了很好的补充，比如实际开发中，有很多业务需求，其实并不需要完整的关系型数据库功能，非关系型数据库的功能就足够使用了。这种情况下，使用性能更高 、 成本更低的非关系型数据库当然是更明智的选择。比如：日志收集、排行榜、定时器等。
关系型数据库的典型数据结构就是数据表 ，这些数据表的组成都是结构化的（Structured）。
将数据放到表中，表再放到库中。
一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
表具有一些特性，这些特性定义了数据在表中如何存储，类似Java和Python中 “类”的设计。
ORM思想 (Object Relational Mapping)体现：
数据库中的一个表 <-> Java中的一个类
表中的一条数据 <-> 类中的一个对象（或实体）
表中的一个列 <–> 类中的一个字段、属性(field)
表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。
四种：一对一关联、一对多关联、多对多关联、自我引用
在实际的开发中应用不多，因为一对一可以创建成一张表。
举例：设计 学生表 ：学号、姓名、手机号码、班级、系别、身份证号码、家庭住址、籍贯、紧急联系人、…
两种建表原则：
外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一。
外键是主键：主表的主键和从表的主键，形成主外键关系。
常见实例场景： 客户表和订单表 ， 分类表和商品表 ， 部门表和员工表 。
举例：
一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键
要表示多对多关系，必须创建第三个表，该表通常称为联接表 ，它将多对多关系划分为两个一对多关系。将这两个表的主键都插入到第三个表中。
举例1：学生-课程
学生信息表 ：一行代表一个学生的信息（学号、姓名、手机号码、班级、系别…）
课程信息表 ：一行代表一个课程的信息（课程编号、授课老师、简介…）
选课信息表 ：一个学生可以选多门课，一门课可以被多个学生选择
学号 课程编号
1 1001
2 1001
1 1002
举例2：产品-订单
“订单”表和“产品”表有一种多对多的关系，这种关系是通过与“订单明细”表建立两个一对多关系来定义的。一个订单可以有多个产品，每个产品可以出现在多个订单中。
步骤一：停止MySQL服务
在卸载之前，先停止MySQL8.0的服务。按键盘上的“Ctrl + Alt + Delete”组合键，打开“任务管理器”对话框，可以在“服务”列表找到“MySQL8.0”的服务，如果现在“正在运行”状态，可以右键单击服务，选择“停止”选项停止MySQL8.0的服务，如图所示。
步骤二：软件的卸载
方式1：通过控制面板方式
卸载MySQL8.0的程序可以和其他桌面应用程序一样直接在“控制面板”选择“卸载程序”，并在程序列表中找到MySQL8.0服务器程序，直接双击卸载即可，如图所示。这种方式删除，数据目录下的数据不会跟着删除。
方式2：通过第三方软件
比如：360软件管家等软件
方式3：通过安装包提供的卸载功能卸载
也可以通过安装向导程序进行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”即可，如图所示。
步骤三：残余文件的清理
如果再次安装不成功，可以卸载后对残余文件进行清理后再安装。
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 Community Server 社区版本，开源免费，自由下载，但不提供官方技术支持，适用于大多数普通用户。
- MySQL Enterprise Edition 企业版本，需付费，不能在线下载，可以试用30天。提供了更多的功能和更完备的技术支持，更适合于对数据库的功能和可靠性要求较高的企业客户。
- MySQL Cluster 集群版，开源免费。用于架设集群服务器，可将几个MySQL Server封装成一个Server。需要在社区版或企业版的基础上使用。
- MySQL Cluster CGE 高级集群版，需付费。
下载地址：
访问oracle官网https://www.oracle.com,找到Products->MySQL
或者直接访问：https://www.mysql.com
步骤：
1、进入官网首页
2、选择“DOWNLOADS”
3、点击“MySQL Community (GPL) Downloads »”下载社区版
4、点击“MySQL Community Server”
在General Availability(GA) Releases中选择适合的版本
Windows平台下提供两种安装文件：MySQL二进制分发版（.msi安装文件）和免安装版（.zip压缩文件）。一般来讲，应当使用二进制分发版，因为该版本提供了图形化的安装向导过程，比其他的分发版使用起来要简单，不再需要其他工具启动就可以运行MySQL。
这里在Windows 系统下推荐下载 MSI安装程序 ；点击 Go to Download Page 进行下载即可。
5、点击“Go to Download Page”进入如下界面
Windows下的MySQL8.0安装有两种安装程序
如果安装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种安装类型，分别是Developer Default（默认安装类型）、Server only（仅作为服务器）、Client only（仅作为客户端）、Full（完全安装）、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服务器将占用最少的系统资源。
Development MachineServer Machine（服务器）：该选项代表服务器，MySQL服务器可以同其他服务器应用程序一起运行，例如Web服务器等。MySQL服务器配置成适当比例的系统资源。
Development MachineDedicated 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安装为Windows服务，并且勾选当Windows启动、停止时，MySQL也自动启动、停止。
方式一：使用图形界面工具
步骤1：打开windows服务
步骤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 -h 主机名 -P 端口号 -u 用户名 -p密码
举例：
mysql -h localhost -P 3306 -u root -pabc123 #此处root用户的密码是abc123
注意：
1、-p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格。如：
mysql -hlocalhost -P3306 -uroot -pabc123
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服务版本的信息：
c:>mysql -V
c:>mysql --version
登录后，通过以下方式查看当前版本信息：
mysql>select version();
exit或quit或ctrl+c强制登录
一、查看所有的数据库
show databases;
“information_schema”是 MySQL 系统自带的数据库，主要保存 MySQL 数据库服务器的系统信息，比如数据库的名称、数据表的名称、字段名称、存取权限、数据文件所在的文件夹和系统使用的文件夹，等等。
“performance_schema”是 MySQL 系统自带的数据库，可以用来监控 MySQL 的各类性能指标。
“sys”数据库是 MySQL 系统自带的数据库，主要作用是以一种更容易被理解的方式展示 MySQL 数据库服务器的各类性能指标，帮助系统管理员和开发人员监控 MySQL 的技术性能。
“mysql”数据库保存了 MySQL 数据库服务器运行时需要的系统信息，比如数据文件夹、当前使用的字符集、约束检查信息，等等。
二、Workbench
官网下载地址：https://www.mysql.com/products/workbench/
为什么 Workbench 里面我们只能看到“demo”和“sys”这 2 个数据库？
这是因为，Workbench 是图形化的管理工具，主要面向开发人 员，“demo”和“sys”这 2 个数据库已经够用了。如果有特殊需求，比如，需要监控 MySQL 数据库各项性能指标、直接操作 MySQL 数据库系统文件等，可以由 DBA 通过 SQL 语句，查看其它的系统数据库。
二、创建自己的数据库
SQL（Structured Query Language）结构化查询语言，它不仅用于关系型数据库，而且也用于非关系型数据库，如大数据很多技术中依然使用SQL。
SQL 是1986年10月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。1989年4月，ISO提出了具有完整性特征的SQL89标准，1992年11月又公布了SQL92标准。
不同数据库厂商又在标准的基础上进行了个性的扩充，同时也造成一个问题，不同数据库语法不同。如：mysql中字符串使用varchar，而oracle中字符串使用varchar2。这在数据库迁移（换数据库）时会非常麻烦。
SQL规范非常成熟，分为下面的部分：
CRUD：创建（create）、更新（update）、读取（retrieve）和删除（delete）
面试题：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.小数
面试题：datetime和timestamp有什么区别？
数据库字段提供对日期类型的支持有date（日期）、time（时间）、datetime（日期+时间）、timestamp（时间戳）
datetime：日期+时间，存储和显示是一样的。
timestamp：时间戳，存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数。
blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计，但目前主流都不会直接存储这样的数据，而只存储其访问路径，文件放在磁盘上。
word:blob [blɒb] n.小圆块；小斑点；一滴 v.弄脏
主键是一条记录的唯一标识，具有唯一性，不能重复。
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;
对于约束的好处是，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。
学生表
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只是删除表的数据。
delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录。
对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除，然后重建表的定义，所以自增主键会重新开始计数。
delete from student; #删除表中的所有数据，主键自增值不会重新计数。
truncate student; #删除表中的所有数据，主键自增值会从1开始计数。
drop table student; #删除表，包括表结构和表中的所有数据。
不声明字段，顺序必须和表的一致。
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;
查询所有数据
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;
可以看到删除数据是非常危险的，那如果我们直接在企业的生产环境操作这样的语句怎么防止误操作呢？
先设置手动提交，默认是自动提交
set @@autocommit = 0; 默认是1，手动提交
savepoint chen;
delete from student;
rollback to chen;
release savepoint chen;
先设置存储点，如果发现发现误操作删了数据，还可以回滚
set @@autocommit=0;
begin;
……语句
rollback;
事务必须满足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.耐久性; 持久性;
答案：事务的四大特性为原子性，一致性，隔离性，持久性
⑴原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
⑵一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
⑶隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
⑷持久性（Durability）
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
下面举例说明，首先我们创造一张表；一列是用户，一列是金额。
| id | user | money |
|---|---|---|
| 1 | A | 1000 |
| 2 | B | 1000 |
(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个，分别是脏读，不可重复读，幻读。
脏读问题需要用读已提交来解决，但读已提交会存在不可重复读问题。
不可重复读问题需要用可重复读来解决，但可重复读会存在幻读问题。
幻读问题需要用串行化来解决。
前提
多条语句时，批量执行，事务提交
set @@autocommit = 0;
begin;
insert into student (id) values (5);
update student set name=‘陈曦’ where id=5;
commit;
有了事务，多步操作就形成了原子性操作，高并发下也不会引起数据错乱。
多条语句，批量执行，insert插入重复的主键导致失败，事务回滚。
set @@autocommit = 0;
begin;
insert into student (id) values (5);
update student set name=‘陈曦2’ where id=5;
rollback;
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);
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’, ‘二区’);
Oracle:
create table salarygrade(
grade number,
losal number,
hisal number
);
MySQL:
create table salarygrade(
grade numeric,
losal numeric,
hisal numeric
);
变小写
select lower(dname) from dept;
upper
变大写
select upper(dname) from dept;
select dname, length(dname) from dept;
截取，从1开始
select dname, substr(dname, 1, 3) from dept;
拼字符串
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;
select loc, replace(loc, ‘区’, ‘区域’) from dept;
round四舍五入，ceil向上取整（大于目标数的最小整数），floor向下取整（小于目标数的最大整数）。
select ename, comm,
round(comm, 1), round(comm, 2), round(comm, 3),
ceil(comm),
floor(comm)
from emp;
select uuid();
select replace(uuid(), ‘-’, ‘’),
length(replace(uuid(), ‘-’, ‘’));
select now();
select current_date();
select current_time();
select curdate();
select curtime();
select sysdate from dual #oracle当前时间，dual内置虚拟表
trunc和floor都可以
每月最大日期
select ename, hiredate, last_day(hiredate) from emp;
select hiredate, year(hiredate), month(hiredate), day(hiredate) from emp;
日期转字符串，注意格式的大小写。
%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秒’);
字符串转日期
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关键字，去除重复的记录行
select loc from dept;
select distinct loc from dept;
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’;
通配符%代表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或者
select * from dept where dname=‘accounting’ and loc=‘一区’;
select * from dept where dname=‘accounting’ or loc=‘二区’;
select * from emp where mgr is null; #字段内容为null的
select * from emp where mgr is not null; #字段内容不为null的
字段值为null时替换；loc为null替换为‘无’
insert into dept (deptno, dname) values (4, ‘workspace’);
select deptno, dname, nvl(loc, ‘无’) as loc from dept;
between x and y 在x和y之间的值
select * from emp
where sal between 5000 and 10000;
等价于
select * from emp
where sal >= 5000 and sal <= 10000;
把多个结果集合并，前提条件，两个结果集列对应，个数和类型一致
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 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
customer表的id字段为varchar类型，按整数类型查询全表遍历，查的很快也是由于缓存而非使用索引。
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，使用可视化工具一般就基本正确。
/* 很多注释内容 */
“#” 一行注释内容
“–” 一行注释内容，这个使用较多
记录总数
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;
习惯使用*的方式，推荐使用后两种方式
最大值
select max(sal) from emp;
最小值
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)
平均值
select avg(sal) from emp;
select ename, avg(sal) from emp #注意sql不是想出来的，而是根据业务去实现的，这句话虽然能执行，但却是错误的，没有业务的实际意义。平均薪资跟某个人有什么关系呢？
高于平均工资的员工有
select * from emp where sal >= (select avg(sal) from emp);
合计
select sum(sal) from emp;
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);
分组后数据的过滤，就是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
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
会有重复数据，不进行合并
select * from dept
union all
select * from dept
select * from dept where loc is not null
intersect
select * from dept
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)，又称直积。一般笛卡尔积没有实际的业务意义，但作为概念必须了解，多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。
这点很值得注意，实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，先在内存中构建一个大的结果集，然后在进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。
#显示部门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 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 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
一般只分为：内连接、左连接、右连接，自连接是指在同一张表的连接查询，下面代码本质还是内连接。
通过别名区分
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查询块，通过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);
多表联查有个技巧，其实所有的连接方式都可以转换为左连接！如果记录内容完整，如每个部门对应有员工，每个员工对应有部门，内连接就等价左连接，结果内容一样。右连接是按右侧表关联，那把它换到左边，那不就是左连接，所以换先位置它们就等价。所以记住左连接写法即可。
左连接写的过程，写它是有套路的，这个套路记住，就特别简单。
实现步骤：
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
#查询所有选修“计算机导论”课程的“男”同学的成绩表