首页 > 编程学习 > MySQL 运维

MySQL 运维

发布时间:2022/9/13 16:18:18

数据库概述

为什么要使用数据库

持久化

DB:数据库

DBMS:数据库管理系统

SQL:结构化查询语言

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

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

image-20220909112817469

常见数据库有哪些

mysql,redis,mongoDB,es,oracl,

RDBMS,非RDBMS

关系型数据库

实质

二维表格形式

以行和列形式存储

优势:

1.复杂查询:一表和多表之间做复杂查询

2.事务支持:安全性能高

非关系型数据库

性能很高,基于键值存储,不需要经过SQL层的解析

键值型数据库

经典场景是内存缓存

文档型数据库

搜索引擎数据库

列式数据库

图形数据库

表,记录字段

E-R,模型三个主要模型:实体集 、 属性 、 联系集 。

一个实体集(class)对应于数据库中的一个表(table),一个实体(instance)则对应于数据库表 中的一行(row),也称为一条记录(record)。一个属性(attribute)对应于数据库表中的一列 (column),也称为一个字段(field)。

image-20220909114144137

表关联的关系

一对一,一对多,多对一,多对多

一对一

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

基础信息表学号、姓名、手机号码、班级、系别

档案信息表学号、身份证号码、家庭住址、籍贯、紧急联系人、..

建表原则

外键位移

外键主键

一对多

举例: 员工表:编号、姓名、...、所属部门

部门表:编号、名称、简介

image-20220909114358195

多对多

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

image-20220909114500084

image-20220909114542315

“订单”表和“产品”表有一种多对多的关系,这种关系是通过与“订单明细”表建立两个一对多关系来
定义的。一个订单可以有多个产品,每个产品可以出现在多个订单中。
产品表 :“产品”表中的每条记录表示一个产品。
订单表 :“订单”表中的每条记录表示一个订单。
订单明细表 :每个产品可以与“订单”表中的多条记录对应,即出现在多个订单中。一个订单
可以与“产品”表中的多条记录对应,即包含多个产品

多对多关系建表原则:需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向
各自一方的主键。

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

自我引用

MySQL环境搭建

\1. MySQL的卸载 步骤1:

停止MySQL服务

在卸载之前,先停止MySQL8.0的服务。按键盘上的“Ctrl + Alt + Delete”组合键,打开“任务管理器”对话 框,可以在“服务”列表找到“MySQL8.0”的服务,如果现在“正在运行”状态,可以右键单击服务,选择“停 止”选项停止MySQL8.0的服务,如图所示。

image-20220909121727576

软件的卸载

方式1:通过控制面板方式

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

image-20220909121755216

方式2:通过360或电脑管家等软件卸载
略
方式3:通过安装包提供的卸载功能卸载
你也可以通过安装向导程序进行MySQL8.0服务器程序的卸载。
① 再次双击下载的mysql-installer-community-8.0.26.0.msi文件,打开安装向导。安装向导会自动检测已
安装的MySQL服务器程序。
② 选择要卸载的MySQL服务器程序,单击“Remove”(移除),即可进行卸载。
步骤3:残余文件的清理
如果再次安装不成功,可以卸载后对残余文件进行清理后再安装。
(1)服务目录:mysql服务的安装目录
(2)数据目录:默认在C:\ProgramData\MySQL
如果自己单独指定过数据目录,就找到自己的数据目录进行删除即可。
注意:请在卸载前做好数据备份
在操作完以后,需要重启计算机,然后进行安装即可。如果仍然安装失败,需要继续操作如下步
骤4。

步骤4:清理注册表(选做)
如果前几步做了,再次安装还是失败,那么可以清理注册表。
如何打开注册表编辑器:在系统的搜索框中输入 regedit
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之类

步骤5:删除环境变量配置

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

image-20220909121913948

MySQL的下载、安装、配置

1. 下载地址
官网:https://www.mysql.com
2. 打开官网,点击DOWNLOADS
然后,点击 MySQL Community(GPL) Downloads
点击 MySQL Community Server
在General Availability(GA) Releases中选择适合的版本
Windows下的MySQL8.0安装有两种安装程序
mysql-installer-web-community-8.0.26.0.msi 下载程序大小:2.4M;安装时需要联网安
装组件。
mysql-installer-community-8.0.26.0.msi 下载程序大小:450.7M;安装时离线安装即
可。推荐。
如果安装MySQL5.7版本的话,选择 Archives ,接着选择MySQL5.7的相应版本即可。这里下载最近
期的MySQL5.7.34版本。
 MySQL8.0 版本的安装
MySQL下载完成后,找到下载文件,双击进行安装,具体操作步骤如下。
步骤1:双击下载的mysql-installer-community-8.0.26.0.msi文件,打开安装向导。
步骤2:打开“Choosing a Setup Type”(选择安装类型)窗口,在其中列出了5种安装类型,分别是
Developer Default(默认安装类型)、Server only(仅作为服务器)、Client only(仅作为客户端)、
Full(完全安装)、Custom(自定义安装)。这里选择“Custom(自定义安装)”类型按钮,单击“Next(下
一步)”按钮。

image-20220909122257925

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

image-20220909122309283

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

image-20220909122336864

image-20220909122345123

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

image-20220909122356566

安装完成后在“Status”(状态)列表下将显示“Complete”(安装完成),如图所示。

image-20220909122406531

image-20220909122539537

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

image-20220909122557873

Server Machine(服务器) :该选项代表服务器,MySQL服务器可以同其他服务器应用程序一起
运行,例如Web服务器等。MySQL服务器配置成适当比例的系统资源。
Dedicated Machine(专用服务器) :该选项代表只运行MySQL服务的服务器。MySQL服务器配置
成使用所有可用系统资源。

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

image-20220909122615178

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

image-20220909122628468

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

image-20220909122645404

image-20220909122650730

步骤7:完成配置,如图所示。单击“Finish”(完成)按钮,即可完成服务器的配置。

image-20220909122702714

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

image-20220909122711623

如果不配置MySQL环境变量,就不能在命令行直接输入MySQL登录命令。下面说如何配置MySQL的环境
变量:
步骤1:在桌面上右击【此电脑】图标,在弹出的快捷菜单中选择【属性】菜单命令。 步骤2:打开【系
统】窗口,单击【高级系统设置】链接。 步骤3:打开【系统属性】对话框,选择【高级】选项卡,然
后单击【环境变量】按钮。 步骤4:打开【环境变量】对话框,在系统变量列表中选择path变量。 步骤
5:单击【编辑】按钮,在【编辑环境变量】对话框中,将MySQL应用程序的bin目录(C:\Program
Files\MySQL\MySQL Server 8.0\bin)添加到变量值中,用分号将其与其他路径分隔开。 步骤6:添加完成
之后,单击【确定】按钮,这样就完成了配置path变量的操作,然后就可以直接输入MySQL命令来登录
数据库了。

2.6 MySQL5.7 版本的安装、配置

2.6 MySQL5.7 版本的安装、配置
安装
此版本的安装过程与上述过程除了版本号不同之外,其它环节都是相同的。所以这里省略了MySQL5.7.34
版本的安装截图。
配置
配置环节与MySQL8.0版本确有细微不同。大部分情况下直接选择“Next”即可,不影响整理使用。
这里配置MySQL5.7时,重点强调:与前面安装好的MySQL8.0不能使用相同的端口号。
2.7 安装失败问题
MySQL的安装和配置是一件非常简单的事,但是在操作过程中也可能出现问题,特别是初学者。
问题1:无法打开MySQL8.0软件安装包或者安装过程中失败,如何解决?
在运行MySQL8.0软件安装包之前,用户需要确保系统中已经安装了.Net Framework相关软件,如果缺少
此软件,将不能正常地安装MySQL8.0软件。

image-20220909123309170

image-20220909123315182

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

image-20220909123403800

问题2:卸载重装MySQL失败?
该问题通常是因为MySQL卸载时,没有完全清除相关信息导致的。
解决办法是,把以前的安装目录删除。如果之前安装并未单独指定过服务安装目录,则默认安装目录是
“C:\Program Files\MySQL”,彻底删除该目录。同时删除MySQL的Data目录,如果之前安装并未单独指定
过数据目录,则默认安装目录是“C:\ProgramData\MySQL”,该目录一般为隐藏目录。删除后,重新安装
即可。
问题3:如何在Windows系统删除之前的未卸载干净的MySQL服务列表?
操作方法如下,在系统“搜索框”中输入“cmd”,按“Enter”(回车)键确认,弹出命令提示符界面。然后输
入“sc delete MySQL服务名”,按“Enter”(回车)键,就能彻底删除残余的MySQL服务了。

3. MySQL的登录
3.1 服务的启动与停止
MySQL安装完毕之后,需要启动服务器进程,不然客户端无法连接数据库。
在前面的配置过程中,已经将MySQL安装为Windows服务,并且勾选当Windows启动、停止时,MySQL也
自动启动、停止。
方式1:使用图形界面工具
步骤1:打开windows服务
方式1:计算机(点击鼠标右键)→ 管理(点击)→ 服务和应用程序(点击)→ 服务(点
击)
方式2:控制面板(点击)→ 系统和安全(点击)→ 管理工具(点击)→ 服务(点击)
方式3:任务栏(点击鼠标右键)→ 启动任务管理器(点击)→ 服务(点击)
方式4:单击【开始】菜单,在搜索框中输入“services.msc”,按Enter键确认
步骤2:找到MySQL80(点击鼠标右键)→ 启动或停止(点击)

image-20220909123425580

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

3.2 自带客户端的登录与退出 当MySQL服务启动完成后,便可以通过客户端来登录MySQL数据库。注意:确认服务是开启的。 登录方式1:MySQL自带客户端 开始菜单 → 所有程序 → MySQL → MySQL 8.0 Command Line Client

说明:仅限于root用户

格式:

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

MySQL演示使用

4.1 MySQL的使用演示
1、查看所有的数据库
show databases;

为什么 Workbench 里面我们只能看到“demo”和“sys”这 2 个数据库呢? 这是因为,Workbench 是图形化的管理工具,主要面向开发人 员,“demo”和“sys”这 2 个数据库已经够用 了。如果有特殊需求,比如,需要监控 MySQL 数据库各项性能指标、直接操作 MySQL 数据库系统文件 等,可以由 DBA 通过 SQL 语句,查看其它的系统数据库。

创建自己的数据库

create database 数据库名;
#创建atguigudb数据库,该名称不能与已经存在的数据库重名。
create database atguigudb;

使用自己的数据库

use 数据库名;
#使用atguigudb数据库
use atguigudb;

说明:如果没有使用use语句,后面针对数据库的操作也没有加“数据名”的限定,那么会报“ERROR 1046
(3D000): No database selected”(没有选择数据库)
使用完use语句之后,如果接下来的SQL都是针对一个数据库操作的,那就不用重复use了,如果要针对另
一个数据库操作,那么要重新use。

查看某个库的所有表格

show tables from 数据库名;

创建新的表格

create table 表名称(
字段名 数据类型,
字段名 数据类型
);

如果是最后一个字段,后面就用加逗号,因为逗号的作用是分割每个字段。

#创建学生表
create table student(
id int,
name varchar(20) #说名字最长不超过20个字符
);

查看一个表的数据

select * from 数据库表名称;
#查看学生表的数据
select * from student;

添加一条记录

insert into 表名称 values(值列表);
#添加两条记录到student表中
insert into student values(1,'张三');
insert into student values(2,'李四');

报错

mysql> insert into student values(1,'张三');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'name' at
row 1
mysql> insert into student values(2,'李四');
ERROR 1366 (HY000): Incorrect string value: '\xC0\xEE\xCB\xC4' for column 'name' at
row 1
mysql> show create table student;

字符集的问题。 8、查看表的创建信息

show create table 表名称\G
#查看student表的详细创建信息
show create table student\G
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

上面的结果显示student的表格的默认字符集是“latin1”不支持中文。

9、查看数据库的创建信息

show create database 数据库名\G
#查看atguigudb数据库的详细创建信息
show create database atguigudb\G

#结果如下
*************************** 1. row ***************************
Database: atguigudb
Create Database: CREATE DATABASE `atguigudb` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

删除表格

drop table 表名称;
#删除学生表
drop table student;

删除数据库

drop database 数据库名;
#删除atguigudb数据库
drop database atguigudb;

MySQL的编码设置

MySQL5.7中 问题再现:命令行操作sql乱码问题

mysql> INSERT INTO t_stu VALUES(1,'张三','男');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at
row 1

步骤1:查看编码命令

show variables like 'character_%';
show variables like 'collation_%';

步骤2:修改mysql的数据目录下的my.ini配置文件

default-character-set=utf8 #默认字符集
[mysqld] # 大概在76行左右,在其下添加
...
character-set-server=utf8
collation-server=utf8_general_ci

注意:建议修改配置文件使用notepad++等高级文本编辑器,使用记事本等软件打开修改后可能会 导致文件编码修改为“含BOM头”的编码,从而服务重启失败。

步骤3:重启服务
步骤4:查看编码命令
show variables like 'character_%';
show variables like 'collation_%';

image-20220909124115859

image-20220909124125519

MySQL8.0中 在MySQL 8.0版本之前,默认字符集为latin1,utf8字符集指向的是utf8mb3。网站开发人员在数据库设计 的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码,就会出现乱码的问题。从MySQL 8.0 开始,数据库的默认编码改为 utf8mb4 ,从而避免了上述的乱码问题。

MySQL图形化管理工具

工具1. MySQL Workbench
MySQL官方提供的图形化管理工具MySQL Workbench完全支持MySQL 5.0以上的版本。MySQL Workbench
分为社区版和商业版,社区版完全免费,而商业版则是按年收费。
MySQL Workbench 为数据库管理员、程序开发者和系统规划师提供可视化设计、模型建立、以及数据库
管理功能。它包含了用于创建复杂的数据建模ER模型,正向和逆向数据库工程,也可以用于执行通常需
要花费大量时间的、难以变更和管理的文档任务。
下载地址:http://dev.mysql.com/downloads/workbench/。
使用:
首先,我们点击 Windows 左下角的“开始”按钮,如果你是 Win10 系统,可以直接看到所有程序。接着,
找到“MySQL”,点开,找到“MySQL Workbench 8.0 CE”。点击打开 Workbench,如下图所示:

image-20220909124215269

image-20220909124248781

这是一个图形化的界面,我来给你介绍下这个界面。
上方是菜单。左上方是导航栏,这里我们可以看到 MySQL 数据库服务器里面的数据 库,包括数据
表、视图、存储过程和函数;左下方是信息栏,可以显示上方选中的数据 库、数据表等对象的信
息。
中间上方是工作区,你可以在这里写 SQL 语句,点击上方菜单栏左边的第三个运行按 钮,就可以执
行工作区的 SQL 语句了。
中间下方是输出区,用来显示 SQL 语句的运行情况,包括什么时间开始运行的、运行的 内容、运行
的输出,以及所花费的时长等信息。

工具2. Navicat
Navicat MySQL是一个强大的MySQL数据库服务器管理和开发工具。它可以与任何3.21或以上版本的
MySQL一起工作,支持触发器、存储过程、函数、事件、视图、管理用户等,对于新手来说易学易用。
其精心设计的图形用户界面(GUI)可以让用户用一种安全简便的方式来快速方便地创建、组织、访问和
共享信息。Navicat支持中文,有免费版本提供。 下载地址:http://www.navicat.com/。
工具3. SQLyog
SQLyog 是业界著名的 Webyog 公司出品的一款简洁高效、功能强大的图形化 MySQL 数据库管理工具。
这款工具是使用C++语言开发的。该工具可以方便地创建数据库、表、视图和索引等,还可以方便地进行
插入、更新和删除等操作,同时可以方便地进行数据库、数据表的备份和还原。该工具不仅可以通过
SQL文件进行大量文件的导入和导出,还可以导入和导出XML、HTML和CSV等多种格式的数据。 下载地
址:http://www.webyog.com/,读者也可以搜索中文版的下载地址。

image-20220909124923969

image-20220909124933559

工具4:dbeaver
DBeaver是一个通用的数据库管理工具和 SQL 客户端,支持所有流行的数据库:MySQL、PostgreSQL、
SQLite、Oracle、DB2、SQL Server、 Sybase、MS Access、Teradata、 Firebird、Apache Hive、Phoenix、
Presto等。DBeaver比大多数的SQL管理工具要轻量,而且支持中文界面。DBeaver社区版作为一个免费开
源的产品,和其他类似的软件相比,在功能和易用性上都毫不逊色。

image-20220909124953866

image-20220909125010438

image-20220909125034353

image-20220909125041084

image-20220909125202845

出现这个原因是MySQL8之前的版本中加密规则是mysql_native_password,而在MySQL8之后,加密规则 是caching_sha2_password。解决问题方法有两种,第一种是升级图形界面工具版本,第二种是把MySQL8 用户登录密码加密规则还原成mysql_native_password。 第二种解决方案如下,用命令行登录MySQL数据库之后,执行如下命令修改用户密码加密规则并更新用 户密码,这里修改用户名为“root@localhost”的用户密码规则为“mysql_native_password”,密码值为 “123456”,如图所示。

#使用mysql数据库
USE mysql;
#修改'root'@'localhost'用户的密码规则和密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc123';
#刷新权限
FLUSH PRIVILEGES;

MySQL目录结构与源码

image-20220909125224023

MySQL 源代码获取

首先,你要进入 MySQL下载界面。 这里你不要选择用默认的“Microsoft Windows”,而是要通过下拉栏,
找到“Source Code”,在下面的操作系统版本里面, 选择 Windows(Architecture Independent),然后点
击下载。
接下来,把下载下来的压缩文件解压,我们就得到了 MySQL 的源代码。
MySQL 是用 C++ 开发而成的,我简单介绍一下源代码的组成。
mysql-8.0.22 目录下的各个子目录,包含了 MySQL 各部分组件的源代码:

image-20220909125246531

sql 子目录是 MySQL 核心代码;
libmysql 子目录是客户端程序 API;
mysql-test 子目录是测试工具;
mysys 子目录是操作系统相关函数和辅助函数;

源代码可以用记事本打开查看,如果你有 C++ 的开发环境,也可以在开发环境中打开查看。

image-20220909125302995

问题1: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: 即可用修改后的新密码登陆。

问题2:mysql命令报“不是内部或外部命令”

如果输入mysql命令报“不是内部或外部命令”,把mysql安装目录的bin目录配置到环境变量path中。如 下:

image-20220909125327454

问题4:命令行客户端的字符集问题

mysql> INSERT INTO t_stu VALUES(1,'张三','男');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at
row 1

原因:服务器端认为你的客户端的字符集是utf-8,而实际上你的客户端的字符集是GBK。

查看所有字符集:SHOW VARIABLES LIKE 'character_set_%'

解决方案,设置当前连接的客户端字符集 “SET NAMES GBK;”

问题5:修改数据库和表的字符编码

修改编码:
(1)先停止服务,(2)修改my.ini文件(3)重新启动服务
说明:
如果是在修改my.ini之前建的库和表,那么库和表的编码还是原来的Latin1,要么删了重建,要么使用
alter语句修改编码。
mysql> create database 0728db charset Latin1;
Query OK, 1 row affected (0.00 sec)
mysql> use 0728db;
Database changed
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)
+--------+-----------------------------------------------------------------+
|Database| Create Database |
+--------+-----------------------------------------------------------------+
| 0728db | CREATE DATABASE `0728db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

sql概述sql分类

sql分类
DDL 数据定义语言
CREATE\ALTER\DROP\RENAME\TRUNCATE
DML:数据操作语言
INSTER\DELETE\UPDATE\SELECT
DCL:数据控制语言
COMMIT\ROLLBACK\SAVEPOINT\GRANT\REVOKE



基本SELECT语句

SELECT 1; #没有任何子句
SELECT 9/2; #没有任何子句
语法
SELECT 标识选择哪些列
FROM 标识从哪个表中选择
选择全部列
SELECT *
FROM departments;
一般情况下,除非需要使用表中所有的字段数据,最好不要使用通配符‘*’。使用通配符虽然可以节
省输入查询语句的时间,但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通
配符的优势是,当不知道所需要的列的名称时,可以通过它获取它们。
在生产环境下,不推荐你直接使用 SELECT * 进行查询。

选择特定列

SELECT department_id, location_id
FROM departments;
列的别名
重命名一个列
便于计算
紧跟列名,也可以在列名和别名之间加入关键字AS,别名使用双引号,以便在别名中包含空格或特
殊的字符并区分大小写。
AS 可以省略
建议别名简短,见名知意
举例
SELECT department_id, location_id
FROM departments;
SELECT last_name AS name, commission_pct comm
FROM employees;

SELECT last_name "Name", salary*12 "Annual Salary"
FROM employees;

去除重复行

SELECT DISTINCT department_id
FROM employees;
SELECT DISTINCT department_id,salary
FROM employees;
这里有两点需要注意:
FROM employees 会报错。
2. DISTINCT 其实是对后面所有列名的组合进行去重,你能看到最后的结果是 74 条,因为这 74 个部
门id不同,都有 salary 这个属性值。如果你想要看都有哪些不同的部门(department_id),只需
要写 DISTINCT department_id 即可,后面不需要再加其他的列名了。



空值参与运算

所有运算符或列值遇到null值,运算的结果都为null
SELECT employee_id,salary,commission_pct,
12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;

这里你一定要注意,在 MySQL 里面, 空值不等于空字符串。一个空字符串的长度是 0,而一个空值的长
度是空。而且,在 MySQL 里面,空值是占用空间的。

着重号

错误的

mysql> SELECT * FROM ORDER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'ORDER' at
line 1

正确的

mysql> SELECT * FROM `ORDER`;
+----------+------------+
| order_id | order_name |
+----------+------------+
| 1 | shkstart |
| 2 | tomcat |
| 3 | dubbo |
+----------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM `order`;
+----------+------------+
| order_id | order_name |
+----------+------------+
| 1 | shkstart |
| 2 | tomcat |
| 3 | dubbo |
+----------+------------+
3 rows in set (0.00 sec)

结论

我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同,请在
SQL语句中使用一对``(着重号)引起来。
SELECT 查询还可以对常数进行查询。对的,就是在 SELECT 查询结果中增加一列固定的常数列。这列的
取值是我们指定的,而不是从数据表中动态取出的。
你可能会问为什么我们还要对常数进行查询呢?
SQL 中的 SELECT 语法的确提供了这个功能,一般来说我们只从一个表中查询数据,通常不需要增加一个
固定的常数列,但如果我们想整合不同的数据源,用常数列作为这个表的标记,就需要查询常数。
比如说,我们想对 employees 数据表中的员工姓名进行查询,同时增加一列字段 corporation ,这个
字段固定值为“尚硅谷”,可以这样写:
SELECT '尚硅谷' as corporation, last_name FROM employees;

显示表结构

使用DESCRIBE 或 DESC 命令,表示表结构。

DESCRIBE employees;
或
DESC employees;
mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id    | int         | NO   | PRI | 0       |       |
| first_name     | varchar(20) | YES  |     | NULL    |       |
| last_name      | varchar(25) | NO   |     | NULL    |       |
| email          | varchar(25) | NO   | UNI | NULL    |       |
| phone_number   | varchar(20) | YES  |     | NULL    |       |
| hire_date      | date        | NO   |     | NULL    |       |
| job_id         | varchar(10) | NO   | MUL | NULL    |       |
| salary         | double(8,2) | YES  |     | NULL    |       |
| commission_pct | double(2,2) | YES  |     | NULL    |       |
| manager_id     | int         | YES  | MUL | NULL    |       |
| department_id  | int         | YES  | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)


其中,各个字段的含义分别解释如下:
Field:表示字段名称。
Type:表示字段类型,这里 barcode、goodsname 是文本型的,price 是整数类型的。
Null:表示该列是否可以存储NULL值。
Key:表示该列是否已编制索引。PRI表示该列是表主键的一部分;UNI表示该列是UNIQUE索引的一
部分;MUL表示在列中某个给定值允许出现多次。
Default:表示该列是否有默认值,如果有,那么值是多少。
Extra:表示可以获取的与给定列有关的附加信息,例如AUTO_INCREMENT等。

条件

SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件
使用WHERE 子句,将不满足条件的行过滤掉
WHERE子句紧随 FROM子句

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;
#第03章_基本的SELECT语句

#1. SQL的分类
/*
DDL:数据定义语言。CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE


DML:数据操作语言。INSERT \ DELETE \ UPDATE \ SELECT (重中之重)


DCL:数据控制语言。COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE


学习技巧:大处着眼、小处着手。

*/

/*
2.1 SQL的规则 ----必须要遵守
- SQL 可以写在一行或者多行。为了提高可读性,各子句分行写,必要时使用缩进
- 每条命令以 ; 或 \g 或 \G 结束
- 关键字不能被缩写也不能分行
- 关于标点符号
  - 必须保证所有的()、单引号、双引号是成对结束的
  - 必须使用英文状态下的半角输入方式
  - 字符串型和日期时间类型的数据可以使用单引号(' ')表示
  - 列的别名,尽量使用双引号(" "),而且不建议省略as

2.2 SQL的规范  ----建议遵守
- MySQL 在 Windows 环境下是大小写不敏感的
- MySQL 在 Linux 环境下是大小写敏感的
  - 数据库名、表名、表的别名、变量名是严格区分大小写的
  - 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
- 推荐采用统一的书写规范:
  - 数据库名、表名、表别名、字段名、字段别名等都小写
  - SQL 关键字、函数名、绑定变量等都大写


3. MySQL的三种注释的方式


*/

USE dbtest2;

-- 这是一个查询语句
SELECT * FROM emp;

INSERT INTO emp 
VALUES(1002,'Tom'); #字符串、日期时间类型的变量需要使用一对''表示

INSERT INTO emp 
VALUES(1003,'Jerry');

# SELECT * FROM emp\G

SHOW CREATE TABLE emp\g

/*
4. 导入现有的数据表、表的数据。
方式1:source 文件的全路径名
举例:source d:\atguigudb.sql;


方式2:基于具体的图形化界面的工具可以导入数据
比如:SQLyog中 选择 “工具” -- “执行sql脚本” -- 选中xxx.sql即可。
*/

#5. 最基本的SELECT语句: SELECT 字段1,字段2,... FROM 表名 
SELECT 1 + 1,3 * 2;

SELECT 1 + 1,3 * 2
FROM DUAL; #dual:伪表

# *:表中的所有的字段(或列)
SELECT * FROM employees;

SELECT employee_id,last_name,salary
FROM employees;


#6. 列的别名
# as:全称:alias(别名),可以省略
# 列的别名可以使用一对""引起来,不要使用''。
SELECT employee_id emp_id,last_name AS lname,department_id "部门id",salary * 12 AS "annual sal"
FROM employees;

# 7. 去除重复行
#查询员工表中一共有哪些部门id呢?
#错误的:没有去重的情况
SELECT department_id
FROM employees;
#正确的:去重的情况
SELECT DISTINCT department_id
FROM employees;

#错误的:
SELECT salary,DISTINCT department_id
FROM employees;

#仅仅是没有报错,但是没有实际意义。
SELECT DISTINCT department_id,salary
FROM employees;

#8. 空值参与运算
# 1. 空值:null
# 2. null不等同于0,'','null'
SELECT * FROM employees;

#3. 空值参与运算:结果一定也为空。
SELECT employee_id,salary "月工资",salary * (1 + commission_pct) * 12 "年工资",commission_pct
FROM employees;
#实际问题的解决方案:引入IFNULL
SELECT employee_id,salary "月工资",salary * (1 + IFNULL(commission_pct,0)) * 12 "年工资",commission_pct
FROM `employees`;

#9. 着重号 ``

SELECT * FROM `order`;

#10. 查询常数
SELECT '尚硅谷',123,employee_id,last_name
FROM employees;

#11.显示表结构

DESCRIBE employees; #显示了表中字段的详细信息

DESC employees;

DESC departments;

#12.过滤数据

#练习:查询90号部门的员工信息
SELECT * 
FROM employees
#过滤条件,声明在FROM结构的后面
WHERE department_id = 90;

#练习:查询last_name为'King'的员工信息
SELECT * 
FROM EMPLOYEES
WHERE LAST_NAME = 'King'; 


课后练习

#第03章_基本的SELECT语句的课后练习

# 1.查询员工12个月的工资总和,并起别名为ANNUAL SALARY
#理解1:计算12月的基本工资
SELECT employee_id,last_name,salary * 12 "ANNUAL SALARY"
FROM employees;

#理解2:计算12月的基本工资和奖金
SELECT employee_id,last_name,salary * 12 * (1 + IFNULL(commission_pct,0)) "ANNUAL SALARY"
FROM employees;


# 2.查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id
FROM employees;

# 3.查询工资大于12000的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary > 12000;

# 4.查询员工号为176的员工的姓名和部门号
SELECT last_name,department_id
FROM employees
WHERE employee_id = 176;

# 5.显示表 departments 的结构,并查询其中的全部数据 
DESCRIBE departments;

SELECT * FROM departments;


比较运算符

-- 算数运算符
-- SELECT 100,100+100,100-100,100/100,

-- + 没有连接作用,只表示加法运算

-- SELECT 100 + '1'
-- SELECT 100 + 'a'
-- SELECT 100 + NULL;

-- SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5
-- FROM dual;

-- 取模运算
-- SELECT 12%5,12%-5 ,12 MOD -5,-12 MOD -5

-- 比较运算符
-- 等于 = <=> <> != <= >= < >
-- SELECT 1=1,1!=2,1='1',1='2',1='a',0='a'

-- SELECT 'a'='a','ab'='ba',# 按ASCII码比较

-- SELECT 1=NULL,NULL=NULL
-- SELECT last_name,salary 
-- FROM employees 
-- WHERE salary=6000
-- WHERE commission_pct =NULL # 不会有任何结果

-- SELECT 1 <=> 2,1<=>'a',0 <=> 'a'

-- SELECT last_name ,salary ,commission_pct
-- FROM employees
-- WHERE commission_pct<=>NULL

-- SELECT NULL<>NULL,1<>NULL,4<>3
-- SELECT last_name,salary 
-- FROM employees 
-- -- WHERE salary=6000
-- WHERE commission_pct is NOT NULL 

-- SELECT LEAST('g','b','t','n'),GREATEST('g','b','t','n')
-- SELECT LEAST(first_name,last_name),LEAST(LENGTH(first_name),LENGTH(last_name))
-- FROM employees

-- SELECT salary FROM employees WHERE salary BETWEEN 
-- 6000 and 8000 
-- SELECT salary FROM employees WHERE salary  < 6000 OR  salary >8000 
 
-- SELECT salary FROM employees WHERE salary BETWEEN   6000 AND  8000 

-- SELECT last_name ,salary,department_id FROM employees
-- WHERE department_id=10 OR department_id = 20 	

-- SELECT last_name,salary,department_id FROM employees
-- WHERE department_id in (10,20,30)

-- SELECT last_name,salary,department_id FROM employees
-- WHERE salary not in (10000,2000)

--  模糊查询
-- 包含last_name 中包含字符 a 的员工信息 ,一个或多个

-- SELECT last_name FROM employees
-- WHERE last_name LIKE '%a%'

-- SELECT last_name FROM employees
-- WHERE last_name LIKE 'a%'

-- SELECT last_name FROM employees
-- WHERE last_name LIKE '%a%' and last_name LIKE '%e%' 



-- SELECT last_name FROM employees
-- WHERE last_name LIKE '%ae%' OR last_name LIKE '%ea%' 


-- SELECT last_name FROM employees
-- WHERE last_name LIKE '_a%'

-- SELECT last_name FROM employees
-- WHERE last_name LIKE '_\_a%'

SELECT 'shk' REGEXP '^s' ,'shk' REGEXP 't$', 'shk' REGEXP 'hk' 


逻辑运算符


-- SELECT last_name ,salary ,department_id
-- FROM employees 
-- WHERE department_id=50 AND salary >2000

-- SELECT last_name ,salary ,department_id
-- FROM employees 
-- -- WHERE commission_pct  is not NULL 
-- WHERE commission_pct   <=> NULL 


-- SELECT 10 & ~1
-- SELECT 4<<1 , 8>>2
# 1.选择工资不在5000到12000的员工的姓名和工资

-- SELECT last_name,salary FROM employees 
-- WHERE salary not in (5000,12000)
# 2.选择在20或50号部门工作的员工姓名和部门号

-- SELECT last_name,department_id FROM employees
-- WHERE department_id=20 OR department_id =50

# 3.选择公司中没有管理者的员工姓名及job_id
-- SELECT job_id ,last_name FROM employees WHERE manager_id is NULL
# 4.选择公司中有奖金的员工姓名,工资和奖金级别
-- SELECT last_name,salary ,commission_pct FROM employees WHERE commission_pct is not null 
# 5.选择员工姓名的第三个字母是a的员工姓名
-- SELECT last_name FROM employees WHERE last_name LIKE '__a%'
# 6.选择姓名中有字母a和k的员工姓名
-- SELECT last_name FROM employees WHERE last_name LIKE '%a%k%' and last_name LIKE '%k%a%'
# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
-- SELECT first_name FROM employees WHERE first_name like '%e'
-- SELECT employee_id,first_name,last_name
-- FROM employees
-- WHERE first_name REGEXP 'e$';

# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
-- SELECT last_name,job_id FROM employees WHERE department_id BETWEEN 80 AND 100
# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id

-- SELECT last_name,salary ,manager_id FROM employees WHERE manager_id in(100,101,110)


排序与分页

-- 排序与分页

-- 如果没有使用排序操作,默认情况下查询返回数据是按照添加数据显示的

-- ORDER BY 对查询数据进行排序操作
-- 升序操作 ASC
-- 降序操作 DESC
-- SELECT employee_id ,last_name ,salary FROM employees 
-- ORDER BY salary DESC
-- 
-- SELECT employee_id ,last_name ,salary FROM employees 
-- ORDER BY salary asc
-- 如果order_by没有显示排名升序,默认是升序排列

-- 使用列别名排序
-- SELECT employee_id ,salary ,salary*12 annual 
-- FROM employees 
-- ORDER BY annual DESC
-- 
-- SELECT employee_id ,salary 
-- FROM employees 
-- WHERE department_id in(50,60,70)
-- ORDER BY department_id DESC
-- 列的别名只能在order_by中使用,不能在where或中能够使用

-- WHERE 需要声明在from 后,order_by 之前

-- SELECT employee_id ,salary 
-- FROM employees 
-- WHERE department_id in(50,60,70)
-- ORDER BY department_id DESC

-- 二级排序

-- SELECT employee_id ,salary department_id 
-- FROM employees 
-- ORDER BY department_id DESC ,salary ASC

-- limit 分页

-- SELECT employee_id ,last_name
-- FROM employees
-- LIMIT 0,10

-- SELECT employee_id ,last_name
-- FROM employees
-- LIMIT 10,10

-- 公式 (pageNo-1)*pageSize ,pageSize

-- 声明顺序
-- LIMIT 格式 LIMIT 位置偏移量,条目数
--  LIMIT 0,条目数 等价于 LIMIT 条目数


-- SELECT employee_id,last_name,salary
-- FROM employees
-- WHERE salary >1000
-- ORDER BY salary DESC
-- -- LIMIT 0,10
-- LIMIT 10
-- 
-- 显示31,32
-- SELECT employee_id ,last_name
-- FROM employees
-- LIMIT 31,2
-- 
-- SELECT employee_id ,last_name
-- FROM employees
-- LIMIT 2 OFFSET 31

-- 显示最高工资
-- SELECT employee_id ,last_name,salary
-- FROM employees
-- ORDER BY salary DESC 
-- LIMIT 0,1


#1. 查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序显示
-- SELECT department_id ,salary*12 annul,last_name 
-- FROM employees 
-- ORDER BY annul DESC ,last_name ASC

#2. 选择工资不在 8000 到 17000 的员工的姓名和工资,按工资降序,显示第21到40位置的数据
-- SELECT salary ,last_name
-- FROM employees
-- WHERE salary not BETWEEN 8000 AND 17000
-- ORDER BY salary DESC
-- LIMIT 21,10
#3. 查询邮箱中包含 e 的员工信息,并先按邮箱的字节数降序,再按部门号升序
-- SELECT email 
-- FROM employees
-- WHERE email LIKE '%e%'
-- ORDER BY LENGTH(email) DESC,department_id ASC


排序数据

1 排序规则
使用 ORDER BY 子句排序
ASC(ascend): 升序
DESC(descend):降序
ORDER BY 子句在SELECT语句的结尾。

单列排序

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;

SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;

多列排序

SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
可以使用不在SELECT列表中的列排序。
在对多列进行排序的时候,首先排序的第一列必须有相同的列值,才会对第二列进行排序。如果第
一列数据中所有值都是唯一的,将不再对第二列进行排序。

分页

分页
2.1 背景
背景1:查询返回的记录太多了,查看起来很不方便,怎么样能够实现分页查询呢?
背景2:表里有 4 条数据,我们只想要显示第 2、3 条数据怎么办呢?
2.2 实现规则
分页原理
所谓分页显示,就是将数据库中的结果集,一段一段显示出来需要的条件。
MySQL中使用 LIMIT 实现分页
格式
LIMIT [位置偏移量,] 行数
--前10条记录:
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录:
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录:
SELECT * FROM 表名 LIMIT 20,10;

MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”,意思是获取从第5条记录开始后面的3条记录,和“LIMIT 4,3;”返回的结果相同。

分页显式公式:(当前页数-1)*每页条数,每页条数

SELECT * FROM table
LIMIT(PageNo - 1)*PageSize,PageSize;

注意:LIMIT 子句必须放在整个SELECT语句的最后! 使用 LIMIT 的好处

约束返回结果的数量可以 减少数据表的网络传输量 ,也可以 提升查询效率 。如果我们知道返回结果只有
1 条,就可以使用 LIMIT 1 ,告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需
要扫描完整的表,只需要检索到一条符合条件的记录即可返回。
在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关
键字,而且需要放到 SELECT 语句的最后面。
如果是 SQL Server 和 Access,需要使用 TOP 关键字,比如:
如果是 DB2,使用 FETCH FIRST 5 ROWS ONLY 这样的关键字:
如果是 Oracle,你需要基于 ROWNUM 来统计行数:
需要说明的是,这条语句是先取出来前 5 条数据行,然后再按照 hp_max 从高到低的顺序进行排序。但
这样产生的结果和上述方法的并不一样。我会在后面讲到子查询,你可以使用
--前10条记录:
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录:
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录:
SELECT * FROM 表名 LIMIT 20,10;
SELECT * FROM table
LIMIT(PageNo - 1)*PageSize,PageSize;
SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC
SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY
SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC;

SELECT last_name,salary
FROM employees
ORDER BY salary DESC)
WHERE rownum < 10;

多表查询

为什么多表查询

原因一: 我们如果以前面的问题为例,将员工表,部门表,和我们的城市位置表三个表合并到一起,那么我们原本的员工表可能一共有20个字段,但是这时候三个表合并到一起之后我们假如合并的表中有40个字段,这个时候我们如果是通过员工名查询员工的编号,我们之前就直接在员工表中查询就可以了,但是这个时候三个表合成一个表之后我们的三个表中如果查询的话,这个时候就会有很多字段是’冗余字段’,并且我们可能有很多员工可能刚刚来公司,这个时候这些员工可能是没有登记部门信息的,那么这个时候就会有很多的员工后面的部门相关的字段都为null,这也就会导致数据很冗余

原因二: 我们要查询数据表中的数据,我们就要将数据表加载到内存中,而我们三表合一之前,如果我们的员工表中有20个字段我们需要20次IO来读取数据,但是我们三表合一之后我们的这个大表这个时候可能就有了40个字段,那么原来是时候如果我们是要从员工表中来查询一个数据,这个时候我们可能需要I/O的次数是20次,那么我们三表合一之后我们原本在员工表中就可以查找的数据我们要来大表中来查询,这个时候我们可能需要I/O的次数是40此,并且我们的I/O是和我们的硬盘进行交互,我们通过I/O和硬盘交互是很慢的,也就是很影响效率的
原因三: 我们的表中的数据越多,我们的表就越难去维护

原因四: 我们三表合一之前我们如果一个人去查询员工表的时候,这个时候就会有其他的人去查询部门表和城市位置表,这个时候的效率就会比较高,但是如果是我们三表合一之后,这个时候如果有一个人在查询我们的大表之后我们的大表可能就会被锁住,这个时候其他的人就不可以对大表进行查询了,这样也就是很影响效率

#案例:查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;

结果
+-----------+----------------------+
| last_name | department_name |
+-----------+----------------------+
| King | Administration |
| King | Marketing |
| King | Purchasing |
| King | Human Resources |
| King | Shipping |
| King | IT |
| King | Public Relations |
| King | Sales |
| King | Executive |
| King | Finance |
| King | Accounting |
| King | Treasury |
...
| Gietz | IT Support |
| Gietz | NOC |
| Gietz | IT Helpdesk |
| Gietz | Government Sales |

分析错误情况:

SELECT COUNT(employee_id) FROM employees;
#输出107行
SELECT COUNT(department_id)FROM departments;
#输出27行
SELECT 107*27 FROM dual;

上述错误叫笛卡尔积错误

笛卡尔积的理解

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能
组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素
个数的乘积数。

SQL92中,笛卡尔积也称为 交叉连接 ,英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交 叉连接。它的作用就是可以把任意表进行连接,即使这两张表不相关。在MySQL中如下情况会出现笛卡 尔积:

#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;

笛卡尔积的错误会在下面条件下产生:

省略多个表连接条件
链接条件无效
所有表中的所有行互相连接
为了避免笛卡尔积, 可以在 WHERE 加入有效的连接条件。
加入连接条件
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件

在 WHERE子句中写入连接条件。
正确写法:
#案例:查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
在表中有相同列时,在列名之前加上表名前缀。

多表查询分类讲解

等值连接 vs 非等值连接

等值连接

departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id

image-20220911143520170

多个连接条件AND操作符

区分重复的别名

多个表中有相同列时,必须在列名之前加上表名前缀

在不同表中具有相同列名可以用表名加以区分

SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

表别名

为什么使用别名
简化查询
别名前使用表名前缀可以提高查询效率
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;
【 强制 】对于数据库中表记录的查询和变更,只要涉及多个表,都需要在列名前加表的别名(或
表名)进行限定。
说明 :对多表进行查询记录、更新记录、删除记录时,如果对操作列没有限定表的别名(或表
名),并且操作列在多个表中存在时,就会抛异常。
正例 :select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;
反例 :在某业务中,由于多表关联查询语句没有加表的别名(或表名)的限制,正常运行两年
后,最近在 某个表中增加一个同名字段,在预发布环境做数据库变更后,线上查询语句出现出
1052 异常:Column 'name' in field list is ambiguous。

连接多个表

image-20220911145100646

总结:连接 n个表,至少需要n-1个连接条件。比如,连接三个表,至少需要两个连接条件。
练习:查询出公司员工的 last_name,department_name, city

非等值连接


image-20220911145136501

自连接,非自连接

当table1和table2本质上是同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义。然后两 个表再进行内连接,外连接等查询。

查询employees表,返回“Xxx works for Xxx”

SELECT CONCAT(worker.last_name ,' works for '
, manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的
行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
如果是左外连接,则连接条件中左边的表也称为 主表 ,右边的表称为 从表 。
如果是右外连接,则连接条件中右边的表也称为 主表 ,左边的表称为 从表 。

使用(+)创建连接

在 SQL92 中采用(+)代表从表所在的位置。即左或右外连接中,(+) 表示哪个是从表。 Oracle 对 SQL92 支持较好,而 MySQL 则不支持 SQL92 的外连接。

#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

而且在 SQL92 中,只有左外连接和右外连接,没有满(或全)外连接。

使用JOIN...ON子句创建连接的语法结构:

SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
for t1 in table1:
    for t2 in table2:
        if condition1:
            for t3 in table3:
                if condition2:
                    output t1 + t2 + t3
语法说明:
可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
ON 子句使语句具有更高的易读性。
关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的,都表示内连接

内连接

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

外连接(OUTER JOIN)的实现

左外连接(LEFT OUTER JOIN)

#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

右外连接

FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

需要注意的是,LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中,在 SQL92 中不存在,
只能用 (+) 表示。

满外连接(FULL OUTER JOIN)

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
需要注意的是,MySQL不支持FULL JOIN,但是可以用 LEFT JOIN UNION RIGHT join代替。

UNION的使用

合并查询结果 利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并
时,两个表对应的列数和数据类型必须相同,并且相互对应。各个SELECT语句之间使用UNION或UNION
ALL关键字分隔。
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION操作符

UNION 操作符返回两个查询的结果集的并集,去除重复记录。

UNION ALL操作符

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重。

注意:执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据
不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效
率。

举例:查询部门编号>90或邮箱包含a的员工信息

#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
#方式2
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

举例:查询中国用户中男性的信息以及美国用户中年男性的用户信息

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

7种SQL JOINS的实现

image-20220911150225935

#中图:内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
#左上图:左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右上图:右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#左中图:A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
#右中图:B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作,效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ (B - A∩B)
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
语法格式小结

左中图
#实现A - A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;
右中图
#实现B - A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

左下图
#实现查询结果是A∪B
#用左外的A,union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;
#实现A∪B - A∩B 或 (A - A∩B) ∪ (B - A∩B)
#使用左外的 (A - A∩B) union 右外的(B - A∩B)
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句


SQL99语法新特性

自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法,比如 NATURAL JOIN 用来表示自然连接。我们可以把
自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ,然后进行 等值
连接

在SQL92标准中:

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;
在 SQL99 中你可以写成
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING连接
当我们进行连接的时候,SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配
合JOIN一起使用。比如:
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
你能看出与自然连接 NATURAL JOIN 不同的是,USING 指定了具体的相同的字段名称,你需要在 USING
的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下
面的 SQL 查询结果是相同的:
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
表连接的约束条件可以有三种方式:WHERE, ON, USING
WHERE:适用于所有关联查询
ON :只能和JOIN一起使用,只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起
写,但分开写可读性更好。
USING:只能和JOIN一起使用,而且要求两个关联字段在关联表中名称一致,而且只能表示关联字
段值相等
#关联条件
#把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
#把关联条件写在on后面,只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;
#把关联字段写在using()中,只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同,而且只能表示=
#查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);
#n张表关联,需要n-1个关联条件
#查询员工姓名,基本工资,部门名称
SELECT last_name,job_title,department_name FROM employees,departments,jobs
WHERE employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
SELECT last_name,job_title,department_name
FROM employees INNER JOIN departments INNER JOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;

我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样,非常消耗资源,会让 SQL 查询性能下 降得很严重,因此不要连接不必要的表。在许多 DBMS 中,也都会有最大连接表的限制。

【强制】超过三个表禁止 join。需要 join 的字段,数据类型保持绝对一致;多表关联查询时, 保 证被关联的字段需要有索引。 说明:即使双表 join 也要注意表索引、SQL 性能。

代码


-- DESC employees

-- DESC departments

-- DESC locations

-- SELECT *FROM locations
-- WHERE location_id =2500

-- 笛卡尔积的错误
-- 错误实现方式,每个员工的都每个部门匹配一遍
-- 错误的原因,缺少了多表的连接条件
-- SELECT employee_id ,department_name
-- FROM employees,departments

-- SELECT employee_id ,department_name
-- FROM employees cross JOIN departments


-- 两个表的连接条件

-- SELECT employee_id ,department_name,employees.department_id
-- FROM employees,departments
-- WHERE employees.`department_id` = departments.department_id
-- 如果查询语句中出现了多个表中豆村在的字段,则必须指明此字段所在的表
-- 从sql优化,建议多表查询时,每个字段前都指明其所在的表


-- 可以给表起别名,在select和 WHERE中使用


-- SELECT emp.employee_id ,dept.department_name,emp.department_id
-- FROM employees emp ,departments dept
-- WHERE emp.`department_id` = dept.department_id

-- 查询员工 employee_id,last_name,department_name,city


-- SELECT employee_id,last_name,department_name,city
-- FROM employees e , departments d, locations l
-- WHERE e.department_id = d.department_id
-- and d.location_id = l.location_id
-- 如果有n的表实现多表查询,则需要n-1个连接条件

-- 多表查询的分类
-- 角度1:等值连接 非等值连接

-- SELECT *FROM job_grades
-- SELECT last_name,salary ,grade_level
-- FROM employees e, job_grades j
-- WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal








-- 自连接,非自连接
-- 查员工姓名,及其管理者的id和姓名
-- SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
-- FROM employees emp,employees mgr
-- WHERE emp.manager_id = mgr.employee_id

-- 内连接,外连接

-- 内连接 合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个
-- SELECT employee_id,department_name
-- FROM employees e , departments d
-- WHERE e.department_id = d.department_id

-- 外连接 合并具有同一列的两个以上的表的行,结果集中除了包含一个表与另一个
-- 还查询到左表或右表中不匹配的行

-- 外连接的分类
-- 外连接的分类,左外连接,右外连接,满外连接

/*
两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的
行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
*/

-- 左外连接 则连接条件中左边的表也称为 主表 ,右边的表称为 从表 。

-- 右外连接 则连接条件中右边的表也称为 主表 ,左边的表称为 从表 。
-- "所有"表示它是外连接

-- 查询所有的员工的 last_name , department_name

-- SQL92 实现外连接,使用+
-- SELECT employee_id,department_name
-- FROM employees e,departments d
-- WHERE e.department_id = d.department_id

-- SQL99语法
-- 内连接
-- SELECT last_name,department_name
-- FROM employees e JOIN departments d
-- on e.department_id = d.department_id

-- SELECT last_name,department_name
-- FROM employees e JOIN departments d 
-- on e.department_id = d.department_id 
-- JOIN locations l
-- on d.location_id = l.location_id

-- SELECT last_name ,department_name 
-- FROM employees e LEFT OUTER JOIN  departments d
-- on e.department_id = d. department_id
-- 
-- 
-- SELECT last_name ,department_name 
-- FROM employees e LEFT  JOIN  departments d
-- on e.department_id = d. department_id
 
-- SELECT last_name ,department_name 
-- FROM employees e RIGHT JOIN  departments d
-- on e.department_id = d. department_id

-- 满外连接 不支持FULL  JOIN
-- SELECT last_name ,department_name 
-- FROM employees e FULL  JOIN  departments d
-- on e.department_id = d. department_id


-- union 和 UNION ALL使用
-- UNION 返回操作符两个查询结果集的并集,去除重复记录
-- UNION ALL 返回两个查询结果集的并集,对于量集的重复部分,不去重

-- 7种JOIN 实现
-- 内连接
-- SELECT employee_id ,department_name 
-- FROM employees e JOIN department_id
-- on e.department_id = d.d.department_id
-- -- 左外连接
-- SELECT employee_id ,department_name 
-- FROM employees e LEFT JOIN department_id
-- on e.department_id = d.d.department_id
-- -- 右外连接
-- SELECT employee_id ,department_name 
-- FROM employees e RIGHT JOIN department_id
-- on e.department_id = d.d.department_id
-- 
-- -- 左中
-- SELECT employee_id ,department_name 
-- FROM employees e LEFT JOIN department_id
-- on e.department_id = d.d.department_id
-- WHERE d.department_id is NULL
-- -- 右中
-- SELECT employee_id ,department_name 
-- FROM employees e RIGHT JOIN department_id
-- on e.department_id = d.d.department_id
-- WHERE e.department_id is NULL
-- 
-- -- 满外连接
-- 
-- -- 左下
-- SELECT employee_id ,department_name 
-- FROM employees e LEFT JOIN department_id
-- on e.department_id = d.d.department_id
-- UNION ALL
-- SELECT employee_id ,department_name 
-- FROM employees e RIGHT JOIN department_id
-- on e.department_id = d.d.department_id
-- WHERE e.department_id is NULL
-- 
-- -- 左下
-- SELECT employee_id ,department_name 
-- FROM employees e LEFT JOIN department_id
-- on e.department_id = d.d.department_id
-- WHERE d.department_id is NULL
-- UNION ALL
-- SELECT employee_id ,department_name 
-- FROM employees e RIGHT JOIN department_id
-- on e.department_id = d.d.department_id
-- 
-- -- 右下
-- SELECT employee_id ,department_name 
-- FROM employees e LEFT JOIN department_id
-- on e.department_id = d.d.department_id
-- WHERE d.department_id is NULL
-- UNION ALL
-- SELECT employee_id ,department_name 
-- FROM employees e RIGHT JOIN department_id
-- on e.department_id = d.d.department_id
-- WHERE e.department_id is NULL

# 1.显示所有员工的姓名,部门号和部门名称。

-- SELECT e.last_name , e.department_id,d.department_name
-- FROM employees e
-- LEFT OUTER JOIN departments d
-- on e.department_id = d.department_id
-- 

# 2.查询90号部门员工的job_id和90号部门的location_id
-- 
-- SELECT e.job_id,d.location_id
-- FROM employees e JOIN departments d
-- on e.department_id = d.department_id
-- WHERE d.department_id=90
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
-- 
-- SELECT e.last_name , d.department_name , d.location_id , l.city
-- FROM employees e LEFT JOIN departments d
-- on e.department_id = d. department_id
-- LEFT JOIN locations l
-- on d.location_id = l. location_id
-- WHERE e.commission_pct is NOT NULL
-- 


# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name

-- SELECT e.last_name , e.job_id , d.department_id , d.department_name 
-- FROM employees e JOIN departments d
-- on e.department_id = d. department_id
-- join locations l
-- on d.location_id = l.location_id
-- WHERE l.city = 'Toronto'



# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资,其中员工所在部门的部门名称为’Executive’
#

SELECT department_name, street_address, last_name, job_id, salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE department_name = 'Executive'



-- 6.选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
-- employees Emp# manager Mgr#
-- kochhar 101 king 100

SELECT last_name 'employees',
employee_id 'Emp',
last_name 'manager'
employee_id 'Mgr'
FROM employees emp LEFT JOIN employees mgr
on emp.manager_id = mgr.employee_id

# 7.查询哪些部门没有员工
# 8. 查询哪个城市没有部门
# 9. 查询部门名为 Sales 或 IT 的员工信息

单行函数

什么是函数

函数在计算机语言的使用中贯穿始终,函数的作用是什么呢?它可以把我们经常使用的代码封装起来, 需要的时候直接调用即可。这样既 提高了代码效率 ,又 提高了可维护性 。在 SQL 中我们也可以使用函数 对检索出来的数据进行函数操作。使用这些函数,可以极大地 提高用户对数据库的管理效率 。

不同DBMS函数的差异

我们在使用 SQL 语言的时候,不是直接和这门语言打交道,而是通过它使用不同的数据库软件,即
DBMS。DBMS 之间的差异性很大,远大于同一个语言不同版本之间的差异。实际上,只有很少的函数是被 DBMS 同时支持的。比如,大多数 DBMS 使用(||)或者(+)来做拼接符,而在 MySQL 中的字符串拼接函数为concat()。大部分 DBMS 会有自己特定的函数,这就意味着采用 SQL 函数的代码可移植性是很
差的,因此在使用函数的时候需要特别注意。
MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制
函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里,我将这些丰富的内置函数再分为两
类: 单行函数 、 聚合函数(或分组函数) 。

数值函数

image-20220913091713629

SELECT
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;

image-20220913092130987

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;

image-20220913092143674

SELECT
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;

image-20220913092155095

image-20220913092106300

SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90))
FROM DUAL;

三角函数

image-20220913092305700

SELECT
SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1)
)
FROM DUAL;

image-20220913092333014

image-20220913092346891

mysql> SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4)
-> FROM DUAL;
+----------+------------+------------------+-------------------+-----------+---------+
| POW(2,5) | POWER(2,4) | EXP(2) | LN(10) | LOG10(10) | LOG2(4) |
+----------+------------+------------------+-------------------+-----------+---------+
| 32 | 16 | 7.38905609893065 | 2.302585092994046 | 1 | 2 |
+----------+------------+------------------+-------------------+-----------+---------+
1 row in set (0.00 sec)

进制间的转换

image-20220913092420202

mysql> SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
-> FROM DUAL;
+---------+---------+---------+--------------+
| BIN(10) | HEX(10) | OCT(10) | CONV(10,2,8) |
+---------+---------+---------+--------------+
| 1010 | A | 12 | 2 |
+---------+---------+---------+--------------+
1 row in set (0.00 sec)

字符串函数

image-20220913093031879

image-20220913093053389

image-20220913093105684

日期和时间函数

获取日期、时间

image-20220913093151866

日期与时间戳的转换

image-20220913093248354

mysql> SELECT UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
| 1576380910 |
+-----------------------+
1 row in set (0.01 sec)
mysql> SELECT UNIX_TIMESTAMP(CURDATE());
+---------------------------+
| UNIX_TIMESTAMP(CURDATE()) |
+---------------------------+
| 1576339200 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP(CURTIME());
+---------------------------+
| UNIX_TIMESTAMP(CURTIME()) |
+---------------------------+
| 1576380969 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11')
+---------------------------------------+
| UNIX_TIMESTAMP('2011-11-11 11:11:11') |
+---------------------------------------+
| 1320981071 |
+---------------------------------------+
1 row in set (0.00 sec)
| FROM_UNIXTIME(1576380910) |
+---------------------------+
| 2019-12-15 11:35:10 |
+---------------------------+
1 row in set (0.00 sec)

获取月份、星期、星期数、天数等函数

image-20220913093332808

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

image-20220913093405336

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

EXTRACT(type FROM date) 返回指定日期中特定的部分,type指定返回的值

EXTRACT(type FROM date) type值含义

image-20220913093440980

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;

时间和秒钟转换的函数

image-20220913093524967

| TIME_TO_SEC(NOW()) |
+--------------------+
| 78774 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT SEC_TO_TIME(78774);
+--------------------+
| SEC_TO_TIME(78774) |
+--------------------+
| 21:52:54 |
+--------------------+
1 row in set (0.12 sec)

计算日期和时间的函数

image-20220913093610823

image-20220913093619303

ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;
SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;

image-20220913093723005

SELECT
ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-
01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,
10)
FROM DUAL;
mysql> SELECT ADDTIME(NOW(), 50);
+---------------------+
| ADDTIME(NOW(), 50) |
+---------------------+
| 2019-12-15 22:17:47 |
+---------------------+
1 row in set (0.00 sec)
+-------------------------+
| 2019-12-15 23:18:46 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBTIME(NOW(), '1:1:1');
+-------------------------+
| SUBTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 21:23:50 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBTIME(NOW(), '-1:-1:-1');
+----------------------------+
| SUBTIME(NOW(), '-1:-1:-1') |
+----------------------------+
| 2019-12-15 22:25:11 |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT FROM_DAYS(366);
+----------------+
| FROM_DAYS(366) |
+----------------+
| 0001-01-01 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT MAKEDATE(2020,1);
+------------------+
| MAKEDATE(2020,1) |
+------------------+
| 2020-01-01 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(2020,32);
+-------------------+
| MAKEDATE(2020,32) |
+-------------------+
| 2020-02-01 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKETIME(1,1,1);
+-----------------+
| MAKETIME(1,1,1) |
+-----------------+
| 01:01:01 |
+-----------------+
1 row in set (0.00 sec)
| PERIOD_ADD(20200101010101,1) |
+------------------------------+
| 20200101010102 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TO_DAYS(NOW());
+----------------+
| TO_DAYS(NOW()) |
+----------------+
| 737773 |
+----------------+
1 row in set (0.00 sec)

查询 7 天内的新增用户数有多少?

SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

日期的格式化与解析

image-20220913093917898

上述 非GET_FORMAT 函数中fmt参数常用的格式符:

image-20220913093947577

image-20220913094039875

mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
+--------------------------------+
| DATE_FORMAT(NOW(), '%H:%i:%s') |
+--------------------------------+
| 22:57:34 |
+--------------------------------+
1 row in set (0.00 sec)


SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y')
FROM DUAL;
SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s')
FROM DUAL;
SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;


mysql> SELECT GET_FORMAT(DATE, 'USA');
+-------------------------+
| GET_FORMAT(DATE, 'USA') |
+-------------------------+
| %m.%d.%Y |
+-------------------------+
1 row in set (0.00 sec)
SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),
FROM DUAL;


mysql> SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d');
+-----------------------------------------------+
| STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') |
+-----------------------------------------------+
| 2020-01-01 |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

image-20220913094156540

SELECT IF(1 > 0,'正确','错误')
->正确

SELECT IFNULL(null,'Hello Word')
->Hello Word
SELECT CASE
WHEN 1 > 0
THEN '1 > 0'
WHEN 2 > 0
THEN '2 > 0'
ELSE '3 > 0'
END
->1 > 0
SELECT CASE 1
WHEN 1 THEN '我是1'
WHEN 2 THEN '我是2'
ELSE '你是谁'
SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪'
WHEN salary>=10000 THEN '潜力股'
WHEN salary>=8000 THEN '屌丝'
ELSE '草根' END "描述"
FROM employees;
SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款'
WHEN 2 THEN '已付款'
WHEN 3 THEN '已发货'
WHEN 4 THEN '确认收货'
ELSE '无效订单' END
FROM t_order;
mysql> SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END;
+---------------------------------------------------------------------+
| CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END |

+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END;
+--------------------------------------------------------------------+
| CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END |
+--------------------------------------------------------------------+
| unknown |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+------------------------------------------------+
| CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+------------------------------------------------+
| 1 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+-------------------------------------------------+
| CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+-------------------------------------------------+
| -1 |
+-------------------------------------------------+
1 row in set (0.00 sec)
SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0))
FROM employees;
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;

#第07章_单行函数

#1.数值函数
#基本的操作
SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5),12 MOD 5,12 % 5
FROM DUAL;

#取随机数
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;

#四舍五入,截断操作
SELECT ROUND(123.556),ROUND(123.456,0),ROUND(123.456,1),ROUND(123.456,2),
ROUND(123.456,-1),ROUND(153.456,-2)
FROM DUAL;

SELECT TRUNCATE(123.456,0),TRUNCATE(123.496,1),TRUNCATE(129.45,-1)
FROM DUAL;

#单行函数可以嵌套
SELECT TRUNCATE(ROUND(123.456,2),0)
FROM DUAL;

#角度与弧度的互换

SELECT RADIANS(30),RADIANS(45),RADIANS(60),RADIANS(90),
DEGREES(2*PI()),DEGREES(RADIANS(60))
FROM DUAL;


#三角函数
SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1))
FROM DUAL;

#指数和对数
SELECT POW(2,5),POWER(2,4),EXP(2)
FROM DUAL;

SELECT LN(EXP(2)),LOG(EXP(2)),LOG10(10),LOG2(4)
FROM DUAL;

#进制间的转换
SELECT BIN(10),HEX(10),OCT(10),CONV(10,10,8)
FROM DUAL;


#2. 字符串函数

SELECT ASCII('Abcdfsf'),CHAR_LENGTH('hello'),CHAR_LENGTH('我们'),
LENGTH('hello'),LENGTH('我们')
FROM DUAL;

# xxx worked for yyy
SELECT CONCAT(emp.last_name,' worked for ',mgr.last_name) "details"
FROM employees emp JOIN employees mgr
WHERE emp.`manager_id` = mgr.employee_id;

SELECT CONCAT_WS('-','hello','world','hello','beijing')
FROM DUAL;
#字符串的索引是从1开始的!
SELECT INSERT('helloworld',2,3,'aaaaa'),REPLACE('hello','lol','mmm')
FROM DUAL;

SELECT UPPER('HelLo'),LOWER('HelLo')
FROM DUAL;

SELECT last_name,salary
FROM employees
WHERE LOWER(last_name) = 'King';

SELECT LEFT('hello',2),RIGHT('hello',3),RIGHT('hello',13)
FROM DUAL;

# LPAD:实现右对齐效果
# RPAD:实现左对齐效果
SELECT employee_id,last_name,LPAD(salary,10,' ')
FROM employees;

SELECT CONCAT('---',LTRIM('    h  el  lo   '),'***'),
TRIM('oo' FROM 'ooheollo')
FROM DUAL;

SELECT REPEAT('hello',4),LENGTH(SPACE(5)),STRCMP('abc','abe')
FROM DUAL;


SELECT SUBSTR('hello',2,2),LOCATE('lll','hello')
FROM DUAL;

SELECT ELT(2,'a','b','c','d'),FIELD('mm','gg','jj','mm','dd','mm'),
FIND_IN_SET('mm','gg,mm,jj,dd,mm,gg')
FROM DUAL;

SELECT employee_id,NULLIF(LENGTH(first_name),LENGTH(last_name)) "compare"
FROM employees;

#3. 日期和时间函数

#3.1  获取日期、时间
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),
UTC_DATE(),UTC_TIME()
FROM DUAL;

SELECT CURDATE(),CURDATE() + 0,CURTIME() + 0,NOW() + 0
FROM DUAL;

#3.2 日期与时间戳的转换
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2021-10-01 12:12:32'),
FROM_UNIXTIME(1635173853),FROM_UNIXTIME(1633061552)
FROM DUAL;

#3.3 获取月份、星期、星期数、天数等函数
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;


SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

#3.4 日期的操作函数

SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),
EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(QUARTER FROM '2021-05-12')
FROM DUAL;

#3.5 时间和秒钟转换的函数
SELECT TIME_TO_SEC(CURTIME()),
SEC_TO_TIME(83355)
FROM DUAL;

#3.6 计算日期和时间的函数

SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR),
DATE_ADD(NOW(),INTERVAL -1 YEAR),
DATE_SUB(NOW(),INTERVAL 1 YEAR)
FROM DUAL;


SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;


SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),32),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;

#3.7 日期的格式化与解析
# 格式化:日期 ---> 字符串
# 解析:  字符串 ----> 日期

#此时我们谈的是日期的显式格式化和解析

#之前,我们接触过隐式的格式化或解析
SELECT *
FROM employees
WHERE hire_date = '1993-01-13';

#格式化:
SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),
DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%h:%i:%S'),
DATE_FORMAT(NOW(),'%Y-%M-%D %h:%i:%S %W %w %T %r')
FROM DUAL;

#解析:格式化的逆过程
SELECT STR_TO_DATE('2021-October-25th 11:37:30 Monday 1','%Y-%M-%D %h:%i:%S %W %w')
FROM DUAL;

SELECT GET_FORMAT(DATE,'USA')
FROM DUAL;

SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;

#4.流程控制函数
#4.1 IF(VALUE,VALUE1,VALUE2)

SELECT last_name,salary,IF(salary >= 6000,'高工资','低工资') "details"
FROM employees;

SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) "details",
salary * 12 * (1 + IF(commission_pct IS NOT NULL,commission_pct,0)) "annual_sal"
FROM employees;

#4.2 IFNULL(VALUE1,VALUE2):看做是IF(VALUE,VALUE1,VALUE2)的特殊情况
SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details"
FROM employees;

#4.3 CASE WHEN ... THEN ...WHEN ... THEN ... ELSE ... END
# 类似于java的if ... else if ... else if ... else
SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精' 
			     WHEN salary >= 10000 THEN '潜力股'
			     WHEN salary >= 8000 THEN '小屌丝'
			     ELSE '草根' END "details",department_id
FROM employees;

SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精' 
			     WHEN salary >= 10000 THEN '潜力股'
			     WHEN salary >= 8000 THEN '小屌丝'
			     END "details"
FROM employees;

#4.4 CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END
# 类似于java的swich ... case...
/*

练习1
查询部门号为 10,20, 30 的员工信息, 
若部门号为 10, 则打印其工资的 1.1 倍, 
20 号部门, 则打印其工资的 1.2 倍, 
30 号部门,打印其工资的 1.3 倍数,
其他部门,打印其工资的 1.4 倍数

*/
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1
								     WHEN 20 THEN salary * 1.2
								     WHEN 30 THEN salary * 1.3
								     ELSE salary * 1.4 END "details"
FROM employees;

/*

练习2
查询部门号为 10,20, 30 的员工信息, 
若部门号为 10, 则打印其工资的 1.1 倍, 
20 号部门, 则打印其工资的 1.2 倍, 
30 号部门打印其工资的 1.3 倍数

*/
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1
								     WHEN 20 THEN salary * 1.2
								     WHEN 30 THEN salary * 1.3
								     END "details"
FROM employees
WHERE department_id IN (10,20,30);

#5. 加密与解密的函数
# PASSWORD()在mysql8.0中弃用。
SELECT MD5('mysql'),SHA('mysql'),MD5(MD5('mysql'))
FROM DUAL;

#ENCODE()\DECODE() 在mysql8.0中弃用。
/*
SELECT ENCODE('atguigu','mysql'),DECODE(ENCODE('atguigu','mysql'),'mysql')
FROM DUAL;
*/

#6. MySQL信息函数

SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),
USER(),CURRENT_USER(),CHARSET('尚硅谷'),COLLATION('尚硅谷')
FROM DUAL;

#7. 其他函数
#如果n的值小于或者等于0,则只保留整数部分
SELECT FORMAT(123.125,2),FORMAT(123.125,0),FORMAT(123.125,-2)
FROM DUAL;

SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2)
FROM DUAL;
#以“192.168.1.100”为例,计算方式为192乘以256的3次方,加上168乘以256的2次方,加上1乘以256,再加上100。
SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876)
FROM DUAL;

#BENCHMARK()用于测试表达式的执行效率
SELECT BENCHMARK(100000,MD5('mysql'))
FROM DUAL;
# CONVERT():可以实现字符集的转换
SELECT CHARSET('atguigu'),CHARSET(CONVERT('atguigu' USING 'gbk'))
FROM DUAL;
-- 
-- 
-- 


# 1.显示系统时间(注:日期+时间)
#
-- SELECT CURRENT_DATE('%Y-%M-%D')
-- SELECT CURRENT_TIME('%H-%m-%S')
SELECT CURRENT_DATE(),CURRENT_TIME()

-- 2.查询员工号,姓名,工资,以及工资提高百分之20%后的结果(new salary)


SELECT employee_id ,last_name,salary,salary*(1.2)'new salary' FROM employees

-- 3.将员工的姓名按首字母排序,并写出姓名的长度(length)

SELECT last_name ,LENGTH(last_name) 'len_name'
FROM employees
ORDER BY last_name ASC


-- 4.查询员工id,last_name,salary,并作为一个列输出,别名为OUT_PUT
-- 
SELECT concat(employee_id,',',last_name,',',salary) out_put
FROM employees 
-- -- 

# 5.查询公司各员工工作的年数、工作的天数,并按工作年数的降序排序

SELECT DATEDIFF(SYSDATE(),hire_date) /365 work_years,DATEDIFF(SYSDATE(),hire_date) worked_days
FROM employees



# 6.查询员工姓名,hire_date , department_id,满足以下条件:雇用时间在1997年之后,department_id
-- 为80 或 90 或110, commission_pct不为空

SELECT last_name,hire_date,department_id
FROM employees
WHERE DATE_FORMAT(hire_date,'%Y')>='1997'
AND department_id in (80,90,110) 


# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name,hire_date
FROM employees
WHERE DATEDIFF(SYSDATE(),hire_date)>10000


# 8.做一个查询,产生下面的结果

-- <last_name> earns <salary> monthly but wants <salary*3>
-- 
SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary, 0) , ' monthly but wants ',
TRUNCATE(salary * 3, 0)) "Dream Salary"
FROM employees;
# 9.使用case-when,按照下面的条件:
-- job grade
-- AD_PRES A
-- ST_MAN B
-- IT_PROG C
-- SA_REP D
-- ST_CLERK E

SELECT last_name Last_name, job_id Job_id, CASE job_id WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
ELSE 'F'
END "grade"
FROM employees


聚合函数

聚合函数用于一组数据,并对一组数据返回一个值

image-20220913161118676

AVG和SUM函数

可以对数值型数据使用AVG 和 SUM 函数。

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

MIN和MAX函数

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

SELECT MIN(hire_date), MAX(hire_date)
FROM employees;

COUNT函数

COUNT(*)返回表中记录总数,适用于任意数据类型。

SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

COUNT(expr) 返回expr不为空的记录总数

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;

问题:用count(*),count(1),count(列名)谁好呢?

Copyright © 2010-2022 mfbz.cn 版权所有 |关于我们| 联系方式|豫ICP备15888888号