SQL基础篇

时间:2022-10-11 19:13:57

SQL 基础及安装配置

SQL(Structured Query Language):结构化查询语言,用于访问和处理数据库的标准的计算机语言,可面向数据库执行查询、读取、插入、更新、删除数据,还可以创建数据库、建表、建存储过程、建视图,还可以通过SQL设置表、存储过程和视图的权限

常见的关系型数据库(RDBMS)

RDBMS是关系型数据库的英文缩写,目前常见的有MySQL、MS SQL Server、IBM DB2、Oracle、Microsoft Access等

首先明确的认知是数据库这三个字代表的是一个用于存数据的软件,它本身相当于一个大型的仓库,以关系型数据库为例,它存储数据的形式是以表的形式存在的,而表具有行和列,其他类型的数据库存储数据的形式各不相同,用途也各不相同

其次如果数据库是个软件,那么就有这个软件的安装以及界面,换句话说,如果不用SQL来操作,通过数据库软件的用户界面同样可以操作任何维度的数据处理,只是相对SQL来说效率比较低,需要通过手点按钮以及键盘输入的方式来处理和数据相关的事宜,举个例子我们都用Office,无论是Microsoft Office还是WPS Office,我们都是打开该软件在里边进行编辑,数据库同样有这种形式,只不过数据库领域诞生了SQL,通过编程语言来实现对数据的处理,大大提升了效率

数据库安装及配置

官方下载安装

可以通过如下官方地址下载,MySQL8.0的安装文件,MySQL历经了很多年,目前已经发布到了8.x,下载完成后应该是一个名字类似于mysql-installer-community-8.0.30.0.msi的文件,双击即可启动安装,如下图所示 SQL基础篇

MySQL的安装有很多个模式,如图中所示默认的是开发者模式也就是Developer Default,另外还有服务器模式、客户端模式、全部安装、自定义安装,他们的区别在于安装的内容不同,通常选在模式的模式即可,在该界面的右侧有每个模式的介绍,就是带滚动条的框框里,同样可以练习英文

点击右下角的Next按钮,安装程序会引导到如下图所示界面 SQL基础篇 图中的英文介绍写的很明白,这一步是安装程序检测到当天机器内缺少Visual Studio的相关组件,安装程序会尝试自动修复这个问题,继续点击右下角的Next按钮,会因为上一步缺少组件而弹出警告 SQL基础篇 因为机器内缺少的是Visual Studio相关的MySQL组件,而Visual Studio是微软的集成开发工具,用不到它因此这个警告不需要处理,点击Yes按钮即可,然后安装程序会引导到如下界面 SQL基础篇 这个界面中列出了我们选择Develoer Default模式后,所需要安装的全部内容,此处无需知道每个内容的作用,只需要确保都安装成功即可,点击Execute按钮,执行安装过程,主意安装 SQL基础篇 列表中有个MySQL Workbench就是操作数据库的用户界面,MySQL Server就是数据库本身,MySQL Shell是一个命令行工具,就像CMD一样,但是它可以直接链接到数据库,然后执行操作数据库的命令其他的几个今后用到的时候再说跟数据库路由和开发语言相关,安装过程比较长,中间还可能某一个组件会安装出错,此处需要特别有耐心,安装成功并安装完成的状态如下图所示 SQL基础篇 然后继续点击右下角的Next按钮,安装程序会引到到配置页面 SQL基础篇 此处点击右下角的Next即可,界面引导到网络配置界面 SQL基础篇 不需要做任何修改,再次点击右下角的Next按钮,界面引导到权限配置界面 SQL基础篇 英文是何等的重要,各位,继续点击Next按钮 SQL基础篇

在此界面输入帐号为Root的密码,Root是MySQL的第一个帐号也是超级管理员帐号,拥有最大的权限 SQL基础篇

输入完成后,会自动进行密码复杂度校验,推荐大写小写数字特殊字符的混合密码,复杂度越高安全性越高,然后继续点击Next按钮,页面会引导到服务配置界面 SQL基础篇

此处无需做任何修改,继续点击Next按钮,界面会引到执行配置的界面,因为我们做了很多配置,但都没有执行,只是做了选择,接下里安装程序会执行这些配置 SQL基础篇

直接点击Execute按钮即可 SQL基础篇

执行配置遇到了错误,点击右侧的Log标签可以查看错误原因 SQL基础篇

处理此类异常情况跟写程序遇到执行失败一样,靠的是经验,这也是区分高级初级的根本所在,处理完再次点击Execute按钮,如下图所示表示安装成功完成,然后点击Finish按钮结束安装,后续的任何操作都不需要再做,全部取消即可,会有一些配置数据库集群的后续操作,无需理会 SQL基础篇

配置账号权限

然后在开始菜单里找到MySQL,打开子菜单会有MySQL Workbench,启动它,它就是操作数据库的界面,如下图所示 SQL基础篇 单击该数据库即可进入操作界面 SQL基础篇SQL基础篇

数据安装完成了,并且有了一个Root账号和设置的密码,但通常为了安全,该账号是不能用来远程连接的,比如使用navicate或者dbeaver等工具链接,该账号的属性也不语许用于远程的场景,因此需要新建一个可以远程连接的账号,点击该界面左侧选项栏里的Users and Privileges页面会进入到如下界面 SQL基础篇 点击一下User列表里的Root,可以看到这个帐号的详情,详情中有个选项Authentication Type,这个选项的值是caching_sha2_password 这个类型就决定了该帐号是不能用来远程的,点击下边的Add Account按钮新建一个可以让我们远程的帐号 SQL基础篇 注意Authentication Type选项要选择Standard,否则还是不能用于远程数据库 SQL基础篇

然后切换到第三个选项卡Administrative Roles,配置该账号的权限,此处全部勾选即可 SQL基础篇

然后点击Apply创建完毕 SQL基础篇

到此配置基本完毕,可以使用navicate或者dbeaver远程工具连接该数据库了 SQL基础篇 远程连接成功后即可执行SQL进行对数据库的操作了,例如创建一个数据库实例 SQL基础篇

SQL 语法及概念

SQL是一种非常简单的易学的语言,其语法也相对比较简单,其中最重要的一点是:SQL是对大小写不敏感的,也就是说ABC和abc,对SQL来说是一样的

在讲SQL语法之前,首先要介绍一下关系型数据库里的数据表,一个数据库通常包含一个或者多个表,每个表有自己的名字,这个名字也是该表的唯一标识,每个表有若干行和列组成,如下Person表为例

表名:Person,该表由第一行字段名和3行+5列的数据组成,每一行都描述一个人,每一列都是某个人的某一项信息

ID LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

SQL语句后的分号

SQL语句后的分号并不是必须的,假如使用的数据库是MS Access 和 SQL Server 2000则不必再每条SQL语句后使用分号,不过有些数据库是必须使用分号的,分号是在数据库系统中分隔每条SQL语句的标准方法,这样便可以在服务器的相同请求中执行一条以上的语句

SQL DML和DDL

可以把SQL分为两个类型:数据操作语言(DML)和数据定义语言(DDL)

  • 查询和更新指令构成了SQL的DML部分:

    • SELECT-从数据库中获取数据
    • UPDATE-更新数据库表中的数据
    • DELETE-从数据库表中删除数据
    • INSERT INTO-向数据库中插入数据
  • 对库、表、索引的操作构成了SQL的DDL部分:

    • CREATE DATABASE-创建数据库
    • ALTER DATABASE-修改数据库
    • CREATE TABLE-创建新表
    • DROP TABLE-删除表
    • CREATE INDEX-创建索引
    • DROP INDEX-删除索引

SQL SELECT语句

SELECT语句用于从表中获取数据,获取的数据会存储在一个结果表中,称为结果集

SELECT语法

SELECT 列名称 FROM 表名称 || SELECT * FROM 表名称

因SQL对大小写不敏感,因此SELECT等效于select

"Persons" 表:

ID LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

获取具体字段内容

从Persons表中获取LastName FirstName两个字段的内容,如下语句所示:

SELECT LastName, FirstName FROM Persons

执行此语句后得到的结果集如下表所示

LastName FirstName
Adams Jhon
Bush George
Carter Thomas

获取全部字段内容

从Persons表中获取全部列,则使用星号(*)号代替列名,如下语句所示:

SELECT * FROM Persons

执行此语句得到的结果集如下表所示

ID LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

返回唯一不同值(去重)

在表中可能存在重复的值,在某些场景下也许只需要列出不同的值,SQL提供了关键词DISTINCT用于去重

SELECT DISTINCT 列名称 FROM 表名称

"Orders"表:

Company OrderNumber
IBM 3532
W3School 2356
Apple 4698
W3School 6953

可以看出Company列里有重复的值即W3School,如果简单的使用SELECT Company FROM Orders会显示重复的值,如下所示

Company
IBM
W3School
Apple
W3School

如需从 Company列中仅选取唯一不同的值,需要使用 SELECT DISTINCT语句:

SELECT DISTINCT Company FROM Orders

则执行结果为:

Company
IBM
W3School
Apple

SQL UPDATE 语句

Update 语句用于修改表中的数据

UPDATE语法

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

Person表:

LastName FirstName Address City
Gates Bill Xuanwumen 10 Beijing
Wilson Champs-Elysees

更新某一行中的一个列

为 lastname 是 "Wilson" 的人添加 firstname:

UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson' 

结果集:

LastName FirstName Address City
Gates Bill Xuanwumen 10 Beijing
Wilson Fred Champs-Elysees

更新某一行中的若干列

修改地址(address),并添加城市名称(city):

UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'

结果集:

LastName FirstName Address City
Gates Bill Xuanwumen 10 Beijing
Wilson Fred Zhongshan 23 Nanjing

SQL INSERT INTO 语句

INSERT INTO 语句用于向表格中插入新的行

INSERT INTO语法

INSERT INTO 表名称 VALUES (值1, 值2,....)

也可以指定所要插入数据的列:

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

插入新的行

"Persons" 表:

LastName FirstName Address City
Carter Thomas Changan Street Beijing

执行插入语句:

INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

结果集:

LastName FirstName Address City
Carter Thomas Changan Street Beijing
Gates Bill Xuanwumen 10 Beijing

在指定的列中插入数据

"Persons" 表:

LastName FirstName Address City
Carter Thomas Changan Street Beijing
Gates Bill Xuanwumen 10 Beijing

执行插入语句:

INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

结果集:

LastName FirstName Address City
Carter Thomas Changan Street Beijing
Gates Bill Xuanwumen 10 Beijing
Wilson Champs-Elysees

SQL GROUP BY 语句

用于结合合计函数,根据一个或多个列对结果集进行分组

GROUP BY语法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

SQL GROUP BY 实例

"Orders" 表:

ID OrderDate OrderPrice Customer
1 2008/12/29 1000 Bush
2 2008/11/23 1600 Carter
3 2008/10/05 700 Bush
4 2008/09/28 300 Bush
5 2008/08/06 2000 Adams
6 2008/07/21 100 Carter

使用 GROUP BY 语句对客户进行组合,查找每个客户的总金额(总订单) 使用下列 SQL 语句:

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

结果集类似这样:

Customer SUM(OrderPrice)
Bush 2000
Carter 1700
Adams 2000

如果省略 GROUP BY 会出现什么情况:

SELECT Customer,SUM(OrderPrice) FROM Orders

结果集类似这样:

Customer SUM(OrderPrice)
Bush 5700
Carter 5700
Bush 5700
Bush 5700
Adams 5700
Carter 5700

上面的结果集不是期望结果,那么为什么不能使用上面这条 SELECT 语句呢?解释如下:上面的 SELECT 语句指定了两列(Customer 和 SUM(OrderPrice))。"SUM(OrderPrice)" 返回一个单独的值("OrderPrice" 列的总计),而 "Customer" 返回 6 个值(每个值对应 "Orders" 表中的每一行)。因此,得不到正确的结果。GROUP BY 语句便可以解决这个问题

GROUP BY 一个以上的列

也可以对一个以上的列应用 GROUP BY 语句,就像这样:

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

SQL ORDER BY 排序

ORDER BY 语句用于根据指定的列对结果集进行排序,默认情况下按照升序对记录进行排序,如果希望按照降序对记录进行排序,可以使用 DESC 关键字

Orders 表:

Company OrderNumber
IBM 3532
W3School 2356
Apple 4698
W3School 6953

实例 1

以字母顺序显示公司名称:

SELECT Company, OrderNumber FROM Orders ORDER BY Company

结果集:

Company OrderNumber
Apple 4698
IBM 3532
W3School 6953
W3School 2356

实例 2

以字母顺序显示公司名称(Company),并以数字顺序显示顺序号(OrderNumber):

SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber

结果集:

Company OrderNumber
Apple 4698
IBM 3532
W3School 2356
W3School 6953

实例 3

以逆字母顺序显示公司名称:

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

结果集:

Company OrderNumber
W3School 6953
W3School 2356
IBM 3532
Apply 4698

实例 4

以逆字母顺序显示公司名称,并以数字顺序显示顺序号:

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

结果集:

Company OrderNumber
W3School 2356
W3School 6953
IBM 3532
Apply 4698

注意:在以上的结果中有两个相等的公司名称 (W3School),只有这一次,在第一列中有相同的值时,第二列是以升序排列的,如果第一列中有些值为 nulls 时,情况也是这样的

SQL AND&OR 运算符

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来,如果第一个条件和第二个条件都成立,则 AND 运算符显示一条记录,如果第一个条件和第二个条件中只要有一个成立,则 OR 运算符显示一条记录

"Persons" 表:

LastName FirstName Address City
Adams John Oxford Street London
Bush George Fifth Avenue New York
Carter Thomas Changan Street Beijing
Carter William Xuanwumen 10 Beijing

AND 运算符实例

使用 AND 来显示所有姓为 "Carter" 并且名为 "Thomas" 的人:

SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'

结果集:

LastName FirstName Address City
Carter Thomas Changan Street Beijing

OR 运算符实例

使用 OR 来显示所有姓为 "Carter" 或者名为 "Thomas" 的人:

SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'

结果集:

LastName FirstName Address City
Carter Thomas Changan Street Beijing
Carter William Xuanwumen 10 Beijing

结合 AND 和 OR 运算符

我们也可以把 AND 和 OR 结合起来(使用圆括号来组成复杂的表达式):

SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William')
AND LastName='Carter'

结果集:

LastName FirstName Address City
Carter Thomas Changan Street Beijing
Carter William Xuanwumen 10 Beijing

SQL WHERE语句

如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句,语法如下

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

下面的运算符可在 WHERE 子句中使用:

操作符 描述
= 等于
<> 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 模糊匹配

在某些版本的 SQL 中,操作符 <> 可以写为 !=

WHERE语法

如果只希望选取居住在城市 "Beijing" 中的人,我们需要向 SELECT 语句添加 WHERE 子句:

SELECT * FROM Persons WHERE City='Beijing'

"Persons" 表:

LastName FirstName Address City Year
Adams John Oxford Street London 1970
Bush George Fifth Avenue New York 1975
Carter Thomas Changan Street Beijing 1980
Gates Bill Xuanwumen 10 Beijing 1985

结果集:

LastName FirstName Address City Year
Carter Thomas Changan Street Beijing 1980
Gates Bill Xuanwumen 10 Beijing 1985

引号的使用

请注意,我们在例子中的条件值周围使用的是单引号,SQL 使用单引号来环绕文本值(大部分数据库系统也接受双引号),如果是数值则不要使用引号,如下示例所示

文本值

这是正确的:

SELECT * FROM Persons WHERE FirstName='Bush'

这是错误的:

SELECT * FROM Persons WHERE FirstName=Bush
数值

这是正确的:

SELECT * FROM Persons WHERE Year>1965

这是错误的:

SELECT * FROM Persons WHERE Year>'1965'