首页 数据库技术 正文
掌握SQL，学习这30个实例就足够了

 2022-10-27    343  

SQL是一种编程语言，用于管理以表格形式(即表)存储在关系数据库中的数据。

关系数据库由多个相互关联的表组成。表之间的关系是在共享列的意义上形成的。

有许多不同的关系数据库管理系统(例如MySQL，PostgreSQL，SQL Server)。他们采用的SQL语法可能略有不同。但是，两者之间的差别很小，因此，如果您学习如何使用一种，则可以轻松切换到另一种。

在本文中，我们将介绍30个示例，这些示例涉及SQL的以下操作：

  1. 创建数据库和表
  2. 将数据插入表
  3. 从表中删除数据
  4. 更新表格
  5. 使用各种选择语句查询表

    在您的计算机或云中有许多使用SQL的替代方法。我目前正在通过终端在Linux计算机上使用MySQL。另一个常用的替代方法是安装MySQL Workbench。

    实例1

    我们首先从终端连接到MySQL服务器并创建一个数据库。

    ~$sudomysql-uroot

    我们将被提示输入密码。现在，我们已连接到计算机中的MySQL服务器。

    以下命令创建一个名为"零售"的数据库。

    mysql>createdatabaseretail;
mysql>useretail;

    我们不在尚未包含任何表的零售数据库中。

    实例2

    我们将首先使用create table命令创建一个名为" customer"的表。

    mysql>createtablecustomer(
->cust_idintprimarykey,
->ageint,
->locationvarchar(20),
->gendervarchar(20)
->);

    我们在括号内定义列的名称和关联的数据类型。将cust_id列指定为主键。

    主键是唯一标识每一行的列。就像熊猫数据框的索引一样。

    实例3

    我们将创建第二个表，称为"订单"。

    mysql>createtableorders(
->order_idintprimarykey,
->datedate,
->amountdecimal(5,2),
->cust_idint,
->foreignkey(cust_id)referencescustomer(cust_id)
->ondeletecascade
->);

    在一开始，我们提到关系表通过共享列相互关联。关联两个表的列是外键。

    外键是将表与另一个表相关联的东西。外键包含另一个表的主键。

    订单表中的cust_id列是外键，并将订单表与客户表相关。我们在创建表时指定此条件。

    在最后一行，我们用"在删除级联上"短语指定另一个条件。它告诉MySQL当删除客户表中的一行时该怎么做。订单表中的每一行都属于一个客户。客户表中的每一行都包含一个唯一的客户ID，并代表一个客户。如果客户表中的行被删除，则意味着我们不再有该客户。结果，属于该客户的订单不再具有关联的客户ID。"删除时级联"表示没有关联客户ID的订单也将被删除。

    实例4

    零售数据库现在包含两个表。我们可以使用show table命令查看数据库中存在的表。

    mysql>showtables;
+------------------+
|Tables_in_retail|
+------------------+
|customer|
|orders|
+------------------+

    注意：SQL中的命令以分号(";")结尾。

    实例5

    desc或describe命令从列名，数据类型和一些其他信息的角度概述了该表。

    mysql>descorders;
+----------+--------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+----------+--------------+------+-----+---------+-------+
|order_id|int(11)|NO|PRI|NULL||
|date|date|YES||NULL||
|amount|decimal(5,2)|YES||NULL||
|cust_id|int(11)|YES|MUL|NULL||
+----------+--------------+------+-----+---------+-------+

    实例6

    我们可以修改现有表。例如，alter table命令可用于添加新列或删除现有列。

    让我们在订单表中添加一列" is_sale"。

    mysql>altertableordersaddis_salevarchar(20);

    我们编写列名和数据类型以及add关键字。

    mysql>descorders;
+----------+--------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+----------+--------------+------+-----+---------+-------+
|order_id|int(11)|NO|PRI|NULL||
|date|date|YES||NULL||
|amount|decimal(5,2)|YES||NULL||
|cust_id|int(11)|YES|MUL|NULL||
|is_sale|varchar(20)|YES||NULL||
+----------+--------------+------+-----+---------+-------+

    is_sale列已添加到订单表中。

    实例7

    alter table也可以用于删除语法稍有更改的列。

    mysql>altertableordersdropis_sale;

    使用drop关键字而不是add关键字。我们也不必编写数据类型来删除列。

    实例8

    我们有表，但它们不包含任何数据。填充表的一种方法是insert语句。

    mysql>insertintocustomervalues(
->1000,42,'Austin','female'
->);

    指定的值以相同的顺序插入到列中。因此，我们需要保持顺序一致。

    实例9

    我们可以通过分隔每一行来同时插入多行。

    mysql>insertintocustomervalues
->(1001,34,'Austin','male'),
->(1002,37,'Houston','male'),
->(1003,25,'Austin','female'),
->(1004,28,'Houston','female'),
->(1005,22,'Dallas','male'),
->;

    我添加了一些行，并以相同的方式填充了orders表。

    还有其他方法可以用数据填充表。例如，我们可以使用加载数据infile或加载数据本地infile语句来加载csv文件。

    实例10

    delete from语句可用于删除表中的现有行。我们需要通过提供条件来标识要删除的行。例如，下面的语句将删除订单ID为17的行。

    mysql>deletefromorders
->whereorder_id=17;

    如果我们不指定条件，则删除给定表中的所有行。

    实例11

    我们还可以更新现有行。让我们更新订单表中的一行。

    +----------+------------+--------+---------+
|order_id|date|amount|cust_id|
+----------+------------+--------+---------+
|1|2020-10-01|24.40|1001|
+----------+------------+--------+---------+

    这是订单表中的第一行。我们想将订单金额更改为27.40。

    mysql>updateorders
->setamount=27.40
->whereorder_id=1;
mysql>select*fromorderslimit1;
+----------+------------+--------+---------+
|order_id|date|amount|cust_id|
+----------+------------+--------+---------+
|1|2020-10-01|27.40|1001|
+----------+------------+--------+---------+

    我们将更新后的值写在set关键字之后。通过在where关键字之后提供条件来标识要更新的行。

    实例12

    如果要通过复制现有表的结构来创建表，则可以使用带有like关键字的create table语句。

    mysql>createtableorders_copylikeorders;
mysql>showtables;
+------------------+
|Tables_in_retail|
+------------------+
|customer|
|orders|
|orders_copy|
+------------------+

    orders_copy表具有与orders表相同的结构，但不包含任何数据。

    实例13

    我们还可以通过使用create table和select语句一起使用数据创建现有表的副本。

    mysql>createtablenew_orders
->select*fromorders;

    似乎是两个单独的语句的组合。第一行创建表，第二行用orders表中的数据填充该表。

    实例14

    drop table语句可用于删除数据库中的表。

    mysql>droptableorders_copy,new_orders;
mysql>showtables;
+------------------+
|Tables_in_retail|
+------------------+
|customer|
|orders|
+------------------+

    我们已经成功删除了在上一个示例中创建的表。

    我们在数据库中有两个关系表。以下示例将说明我们如何使用选择查询从这些表中检索数据。

    实例15

    最简单的查询是查看表中的所有列。

    mysql>select*fromorders
->limit3;
+----------+------------+--------+---------+
|order_id|date|amount|cust_id|
+----------+------------+--------+---------+
|1|2020-10-01|27.40|1001|
|2|2020-10-01|36.20|1000|
|3|2020-10-01|65.45|1002|
+----------+------------+--------+---------+

    " *"选择所有列，而limit关键字对要显示的行数施加约束。

    实例16

    通过写列名而不是" *"，我们只能选择某些列。

    mysql>selectorder_id,amount
->fromorders
->limit3;
+----------+--------+
|order_id|amount|
+----------+--------+
|1|27.40|
|2|36.20|
|3|65.45|
+----------+--------+

    实例17

    我们可以使用where子句指定要选择的行的条件。以下查询将返回2020–10–01的所有订单。

    mysql>select*fromorders
->wheredate='2020-10-01';
+----------+------------+--------+---------+
|order_id|date|amount|cust_id|
+----------+------------+--------+---------+
|1|2020-10-01|27.40|1001|
|2|2020-10-01|36.20|1000|
|3|2020-10-01|65.45|1002|
+----------+------------+--------+---------+

    实例18

    where子句接受多个条件。让我们在上一个示例中为查询添加另一个条件。

    mysql>select*fromorders
->wheredate='2020-10-01'andamount>50;
+----------+------------+--------+---------+
|order_id|date|amount|cust_id|
+----------+------------+--------+---------+
|3|2020-10-01|65.45|1002|
+----------+------------+--------+---------+

    实例19

    我们可能想对查询结果进行排序，这可以通过使用order by子句来完成。

    以下查询将返回2020–10–02的订单，并根据金额对它们进行排序。

    mysql>select*fromorders
->wheredate='2020-10-02'
->orderbyamount;
+----------+------------+--------+---------+
|order_id|date|amount|cust_id|
+----------+------------+--------+---------+
|5|2020-10-02|18.80|1005|
|6|2020-10-02|21.15|1009|
|4|2020-10-02|34.40|1001|
|7|2020-10-02|34.40|1008|
|8|2020-10-02|41.10|1002|
+----------+------------+--------+---------+

    实例20

    默认情况下，order by子句对行进行升序排序。我们可以使用desc关键字将其更改为降序。

    mysql>select*fromorders
->wheredate='2020-10-02'
->orderbyamountdesc;
+----------+------------+--------+---------+
|order_id|date|amount|cust_id|
+----------+------------+--------+---------+
|8|2020-10-02|41.10|1002|
|4|2020-10-02|34.40|1001|
|7|2020-10-02|34.40|1008|
|6|2020-10-02|21.15|1009|
|5|2020-10-02|18.80|1005|
+----------+------------+--------+---------+

    实例21

    SQL是一种通用语言，也可以用作数据分析工具。它提供许多功能，可在从数据库查询时分析和转换数据。

    例如，我们可以在订单表中计算唯一天数。

    mysql>selectcount(distinct(date))asday_count
->fromorders;
+-----------+
|day_count|
+-----------+
|4|
+-----------+

    订单表包含4个不同日期的订单。" as"关键字用于重命名查询结果中的列。否则，该列的名称将为" count(distinct(date))"。

    实例22

    订单表中有4天。我们还可以找出每天有多少订单。group by子句将帮助我们完成此任务。

    mysql>selectdate,count(order_id)asorder_count
->fromorders
->groupbydate;
+------------+-------------+
|date|order_count|
+------------+-------------+
|2020-10-01|3|
|2020-10-02|5|
|2020-10-03|6|
|2020-10-04|2|
+------------+-------------+

    我们计算订单并将其按日期列分组。

    实例23

    我们将计算每天的平均订单金额，并根据平均金额以降序排列结果。

    mysql>selectdate,avg(amount)
->fromorders
->groupbydate
->orderbyavg(amount)desc;
+------------+-------------+
|date|avg(amount)|
+------------+-------------+
|2020-10-01|43.016667|
|2020-10-04|42.150000|
|2020-10-03|37.025000|
|2020-10-02|29.970000|
+------------+-------------+

    实例24

    我们要修改上一个示例中的查询，并且只包含平均金额大于30的天。

    mysql>selectdate,avg(amount)
->fromorders
->groupbydate
->havingavg(amount)>30
->orderbyavg(amount)desc;
+------------+-------------+
|date|avg(amount)|
+------------+-------------+
|2020-10-01|43.016667|
|2020-10-04|42.150000|
|2020-10-03|37.025000|

    重要的是要注意，查询中语句的顺序很重要。例如，如果将order by子句放在having子句之前，则会产生错误。

    实例25

    我们想找出每天的最大订购量。

    mysql>selectdate,max(amount)
->fromorders
->groupbydate;
+------------+-------------+
|date|max(amount)|
+------------+-------------+
|2020-10-01|65.45|
|2020-10-02|41.10|
|2020-10-03|80.20|
|2020-10-04|50.10|

    实例26

    我们要在select语句中组合多个聚合函数。为了说明这一点，让我们详细说明前面的示例。我们希望看到每个客户的最大订单量与最小订单量之间的差异。我们还希望根据升序的差异对结果进行排序，并显示前三个结果。

    mysql>selectcust_id,max(amount)-min(amount)asdif
->fromorders
->groupbycust_id
->orderbydifdesc
->limit3;
+---------+-------+
|cust_id|dif|
+---------+-------+
|1007|46.00|
|1009|28.95|
|1002|24.35|
+---------+-------+

    diff列是通过从最大数量减去最小数量获得的。

    实例27

    我们现在切换到客户表。让我们找出每个城市有多少女性和男性顾客。我们需要在group by子句中同时写位置和性别列。

    mysql>selectlocation,gender,count(cust_id)
->fromcustomer
->groupbylocation,gender;
+----------+--------+----------------+
|location|gender|count(cust_id)|
+----------+--------+----------------+
|Austin|female|2|
|Austin|male|1|
|Dallas|female|2|
|Dallas|male|2|
|Houston|female|2|
|Houston|male|1|
+----------+--------+----------------+

    实例28

    客户表和订单表基于cust_id列相互关联。我们可以使用SQL连接从两个表中查询数据。

    我们希望在客户表中查看每个城市的平均订单金额。

    mysql>selectcustomer.location,avg(orders.amount)asavg
->fromcustomer
->joinorders
->oncustomer.cust_id=orders.cust_id
->groupbycustomer.location;
+----------+-----------+
|location|avg|
+----------+-----------+
|Austin|33.333333|
|Dallas|34.591667|
|Houston|44.450000|
+----------+-----------+

    由于我们从两个不同的表中选择列，因此将使用关联的表名指定列名。上面查询的第二，第三和第四行基于每个表中的cust_id列将customer andorders表联接在一起。

    请注意，列名不必相同。无论我们使用" on"关键字提供什么列名，都将基于这些列进行比较或匹配。

    实例29

    我们希望查看在2020–10–03年下订单的客户的平均年龄。

    mysql>selectavg(c.age)asavg_age
->fromcustomerc
->joinorderso
->onc.cust_id=o.cust_id
->whereo.date='2020-10-03';
+---------+
|avg_age|
+---------+
|30.0000|
+---------+

    我们也可以为表名使用别名。当我们需要多次键入表名时，它很方便。

    实例30

    我们想查看订单量最大的客户的位置。

    mysql>selectc.location,o.amount
->fromcustomerc
->joinorderso
->onc.cust_id=o.cust_id
->whereo.amount=(selectmax(amount)fromorders)
->;
+----------+--------+
|location|amount|
+----------+--------+
|Dallas|80.20|
+----------+--------+

    在此查询中，我们有一个嵌套的select语句。金额条件是使用订单表中单独的select语句计算得出的。

    可以通过其他方式完成此任务。我选择了这种方法来介绍嵌套查询的概念。

    结论

    我相信本文中的30个示例将全面介绍SQL。我们涵盖了以下主题：

    • 用关系表创建数据库
    • 修改表格
    • 将数据插入表
    • 从表中删除数据
    • 编写查询以从表中检索数据

    当然，SQL可以完成更高级的查询和操作。熟悉基础知识后，最好继续进行更高级的操作。

  •  标签：  
  • MySQL
    •  

发表评论：

原文链接：https://77isp.com/post/10271.html

=========================================

https://77isp.com/ 为 “云服务器技术网” 唯一官方服务平台，请勿相信其他任何渠道。

热门文章
热门标签
最新留言
友情链接

首页 IT运维空间 网云互联 1818IP网

本站资源均来自原创及互联网，仅供学习与参考，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。

苏ICP备2022008373号-1