全面的实用教程 SQL是一种编程语言,用于管理以表格形式(即表)存储在关系数据库中的数据。 关系数据库由多个相互关联的表组成。表之间的关系是在共享列的意义上形成的。 有许多不同的关系数据库管理系统(例如MySQL,PostgreSQL,SQL Server)。他们采用的SQL语法可能略有不同。但是,两者之间的差别很小,因此,如果您学习如何使用一种,则可以轻松切换到另一种。 在本文中,我们将介绍30个示例,这些示例涉及SQL的以下操作: · 创建数据库和表 · 将数据插入表 · 从表中删除数据 · 更新表格 · 使用各种选择语句查询表 在您的计算机或云中有许多使用SQL的替代方法。我目前正在通过终端在Linux计算机上使用MySQL。另一个常用的替代方法是安装MySQL Workbench。 实例1我们首先从终端连接到MySQL服务器并创建一个数据库。 ~$ sudo mysql -u root 我们将被提示输入密码。现在,我们已连接到计算机中的MySQL服务器。 以下命令创建一个名为'零售'的数据库。 mysql> create database retail;mysql> use retail; 我们不在尚未包含任何表的零售数据库中。 实例2我们将首先使用create table命令创建一个名为' customer'的表。 mysql> create table customer ( -> cust_id int primary key, -> age int, -> location varchar(20), -> gender varchar(20) -> ); 我们在括号内定义列的名称和关联的数据类型。将cust_id列指定为主键。
实例3我们将创建第二个表,称为'订单'。 mysql> create table orders ( -> order_id int primary key, -> date date, -> amount decimal(5,2), -> cust_id int, -> foreign key (cust_id) references customer(cust_id) -> on delete cascade -> ); 在一开始,我们提到关系表通过共享列相互关联。关联两个表的列是外键。
订单表中的cust_id列是外键,并将订单表与客户表相关。我们在创建表时指定此条件。 在最后一行,我们用'在删除级联上'短语指定另一个条件。它告诉MySQL当删除客户表中的一行时该怎么做。订单表中的每一行都属于一个客户。客户表中的每一行都包含一个唯一的客户ID,并代表一个客户。如果客户表中的行被删除,则意味着我们不再有该客户。结果,属于该客户的订单不再具有关联的客户ID。'删除时级联'表示没有关联客户ID的订单也将被删除。 实例4零售数据库现在包含两个表。我们可以使用show table命令查看数据库中存在的表。 mysql> show tables;+------------------+| Tables_in_retail |+------------------+| customer || orders |+------------------+ 注意:SQL中的命令以分号(';')结尾。 实例5desc或describe命令从列名,数据类型和一些其他信息的角度概述了该表。 mysql> desc orders;+----------+--------------+------+-----+---------+-------+| 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> alter table orders add is_sale varchar(20); 我们编写列名和数据类型以及add关键字。 mysql> desc orders;+----------+--------------+------+-----+---------+-------+| 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列已添加到订单表中。 实例7alter table也可以用于删除语法稍有更改的列。 mysql> alter table orders drop is_sale; 使用drop关键字而不是add关键字。我们也不必编写数据类型来删除列。 实例8我们有表,但它们不包含任何数据。填充表的一种方法是insert语句。 mysql> insert into customer values ( -> 1000, 42, 'Austin', 'female' -> ); 指定的值以相同的顺序插入到列中。因此,我们需要保持顺序一致。 实例9我们可以通过分隔每一行来同时插入多行。 mysql> insert into customer values -> (1001, 34, 'Austin', 'male'), -> (1002, 37, 'Houston', 'male'), -> (1003, 25, 'Austin', 'female'), -> (1004, 28, 'Houston', 'female'), -> (1005, 22, 'Dallas', 'male'), -> ; 我添加了一些行,并以相同的方式填充了orders表。 还有其他方法可以用数据填充表。例如,我们可以使用加载数据infile或加载数据本地infile语句来加载csv文件。 实例10delete from语句可用于删除表中的现有行。我们需要通过提供条件来标识要删除的行。例如,下面的语句将删除订单ID为17的行。 mysql> delete from orders -> where order_id = 17; 如果我们不指定条件,则删除给定表中的所有行。 实例11我们还可以更新现有行。让我们更新订单表中的一行。 +----------+------------+--------+---------+| order_id | date | amount | cust_id |+----------+------------+--------+---------+| 1 | 2020-10-01 | 24.40 | 1001 |+----------+------------+--------+---------+ 这是订单表中的第一行。我们想将订单金额更改为27.40。 mysql> update orders -> set amount = 27.40 -> where order_id = 1;mysql> select * from orders limit 1;+----------+------------+--------+---------+| order_id | date | amount | cust_id |+----------+------------+--------+---------+| 1 | 2020-10-01 | 27.40 | 1001 |+----------+------------+--------+---------+ 我们将更新后的值写在set关键字之后。通过在where关键字之后提供条件来标识要更新的行。 实例12如果要通过复制现有表的结构来创建表,则可以使用带有like关键字的create table语句。 mysql> create table orders_copy like orders;mysql> show tables;+------------------+| Tables_in_retail |+------------------+| customer || orders || orders_copy |+------------------+ orders_copy表具有与orders表相同的结构,但不包含任何数据。 实例13我们还可以通过使用create table和select语句一起使用数据创建现有表的副本。 mysql> create table new_orders -> select * from orders; 似乎是两个单独的语句的组合。第一行创建表,第二行用orders表中的数据填充该表。 实例14drop table语句可用于删除数据库中的表。 mysql> drop table orders_copy, new_orders;mysql> show tables;+------------------+| Tables_in_retail |+------------------+| customer || orders |+------------------+ 我们已经成功删除了在上一个示例中创建的表。 我们在数据库中有两个关系表。以下示例将说明我们如何使用选择查询从这些表中检索数据。 实例15最简单的查询是查看表中的所有列。 mysql> select * from orders -> limit 3;+----------+------------+--------+---------+| 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> select order_id, amount -> from orders -> limit 3;+----------+--------+| order_id | amount |+----------+--------+| 1 | 27.40 || 2 | 36.20 || 3 | 65.45 |+----------+--------+ 实例17我们可以使用where子句指定要选择的行的条件。以下查询将返回2020–10–01的所有订单。 mysql> select * from orders -> where date = '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 |+----------+------------+--------+---------+ 实例18where子句接受多个条件。让我们在上一个示例中为查询添加另一个条件。 mysql> select * from orders -> where date = '2020-10-01' and amount > 50;+----------+------------+--------+---------+| order_id | date | amount | cust_id |+----------+------------+--------+---------+| 3 | 2020-10-01 | 65.45 | 1002 |+----------+------------+--------+---------+ 实例19我们可能想对查询结果进行排序,这可以通过使用order by子句来完成。 以下查询将返回2020–10–02的订单,并根据金额对它们进行排序。 mysql> select * from orders -> where date = '2020-10-02' -> order by amount;+----------+------------+--------+---------+| 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 * from orders -> where date = '2020-10-02' -> order by amount desc;+----------+------------+--------+---------+| 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 |+----------+------------+--------+---------+ 实例21SQL是一种通用语言,也可以用作数据分析工具。它提供许多功能,可在从数据库查询时分析和转换数据。 例如,我们可以在订单表中计算唯一天数。 mysql> select count(distinct(date)) as day_count -> from orders;+-----------+| day_count |+-----------+| 4 |+-----------+ 订单表包含4个不同日期的订单。' as'关键字用于重命名查询结果中的列。否则,该列的名称将为' count(distinct(date))'。 实例22订单表中有4天。我们还可以找出每天有多少订单。group by子句将帮助我们完成此任务。 mysql> select date, count(order_id) as order_count -> from orders -> group by date;+------------+-------------+| date | order_count |+------------+-------------+| 2020-10-01 | 3 || 2020-10-02 | 5 || 2020-10-03 | 6 || 2020-10-04 | 2 |+------------+-------------+ 我们计算订单并将其按日期列分组。 实例23我们将计算每天的平均订单金额,并根据平均金额以降序排列结果。 mysql> select date, avg(amount) -> from orders -> group by date -> order by avg(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> select date, avg(amount) -> from orders -> group by date -> having avg(amount) > 30 -> order by avg(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> select date, max(amount) -> from orders -> group by date;+------------+-------------+| 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> select cust_id, max(amount) - min(amount) as dif -> from orders -> group by cust_id -> order by dif desc -> limit 3;+---------+-------+| cust_id | dif |+---------+-------+| 1007 | 46.00 || 1009 | 28.95 || 1002 | 24.35 |+---------+-------+ diff列是通过从最大数量减去最小数量获得的。 实例27我们现在切换到客户表。让我们找出每个城市有多少女性和男性顾客。我们需要在group by子句中同时写位置和性别列。 mysql> select location, gender, count(cust_id) -> from customer -> group by location, 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> select customer.location, avg(orders.amount) as avg -> from customer -> join orders -> on customer.cust_id = orders.cust_id -> group by customer.location;+----------+-----------+| location | avg |+----------+-----------+| Austin | 33.333333 || Dallas | 34.591667 || Houston | 44.450000 |+----------+-----------+ 由于我们从两个不同的表中选择列,因此将使用关联的表名指定列名。上面查询的第二,第三和第四行基于每个表中的cust_id列将customer andorders表联接在一起。 请注意,列名不必相同。无论我们使用' on'关键字提供什么列名,都将基于这些列进行比较或匹配。 实例29我们希望查看在2020–10–03年下订单的客户的平均年龄。 mysql> select avg(c.age) as avg_age -> from customer c -> join orders o -> on c.cust_id = o.cust_id -> where o.date = '2020-10-03';+---------+| avg_age |+---------+| 30.0000 |+---------+ 我们也可以为表名使用别名。当我们需要多次键入表名时,它很方便。 实例30我们想查看订单量最大的客户的位置。 mysql> select c.location, o.amount -> from customer c -> join orders o -> on c.cust_id = o.cust_id -> where o.amount = (select max(amount) from orders) -> ;+----------+--------+| location | amount |+----------+--------+| Dallas | 80.20 |+----------+--------+ 在此查询中,我们有一个嵌套的select语句。金额条件是使用订单表中单独的select语句计算得出的。 可以通过其他方式完成此任务。我选择了这种方法来介绍嵌套查询的概念。 结论我相信本文中的30个示例将全面介绍SQL。我们涵盖了以下主题: · 用关系表创建数据库 · 修改表格 · 将数据插入表 · 从表中删除数据 · 编写查询以从表中检索数据 当然,SQL可以完成更高级的查询和操作。熟悉基础知识后,最好继续进行更高级的操作。 感谢您的阅读。如果您有任何反馈意见,请告诉我。 (本文由闻数起舞翻译自undefined的文章《30 Examples to Master SQL》,转载请注明出处,原文链接:https:///30-examples-to-master-sql-c8004705479a) |
|
来自: 群先 > 《ORACLE SQL》