美文网首页技巧文章
SQL基础语法笔记

SQL基础语法笔记

作者: 火炬广场老大爷 | 来源:发表于2020-12-22 11:17 被阅读0次

    1、数据检索

    • 选择单列

    从Products中选出prod_name列

    SELECT prod_name
FROM Products;

    • 选择多个列

    SELECT prod_id, prod_name, prod_price
FROM Products;

    • 选择所有列

    SELECT *
FROM Products;

    • 选择前五行

    SELECT TOP 5 prod_name
FROM Products;

    • 选择从第5行开始的5行数据

    SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;

    2、排序检索

    • 排序数据

    SELECT prod_name
FROM Products
ORDER BY prod_name;

    提取prod-name，并且用它来排序。

    • 按照多个列排序

    SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

    • 按照列位置排序

    SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

    • 指定排序方向

    • 降序：
    SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;
    • 只降第一个：
    SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;
    • 所有的都降
      如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字。

    3、过滤数据

    • where子句

    SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

    在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于WHERE 之后，否则将会产生错误。一般来说，order by一般都放在最后。

    • where子句的操作符

    be469e4d2f0882f0d8ef210254a0512.png
    • 列出所有价格小于 10 美元的产品
    SELECT prod_name, prod_price
FROM Products
WHERE prod_price < 10;
    • 检索所有价格小于等于 10 美元的产品
    SELECT prod_name, prod_price
FROM Products
WHERE prod_price <= 10;
    • 列出所有不是供应商 DLL01 制造的产品：
    SELECT vend_id, prod_name
FROM Products
WHERE vend_id <> 'DLL01';

    观察上述 WHERE 子句中的条件，会看到有的值括在单引号内，而有的值未括起来。单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。

    • 使用 BETWEEN 操作符，它检索价格在 5 美元和 10美元之间的所有产品:
    SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;
    • 空值检查
    SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

    • 组合where子句

    • AND
    SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;
    • OR操作符
    SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;
    • 任何时候使用具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序，即使它确实如你希望的那样。使用圆括号没有什么坏处，它能消除歧义。
    SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
AND prod_price >= 10;
    • IN字符
      IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。 IN 取一组由逗号分隔、括在圆括号中的合法值。
    SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;

    此 SELECT 语句检索由供应商 DLL01 和 BRS01 制造的所有产品。 IN 操作符后跟由逗号分隔的合法值，这些值必须括在圆括号中。 IN 操作符完成了与 OR 相同的功能！

    • NOT字符
    SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

    这里的 NOT 否定跟在其后的条件，因此， DBMS 不是匹配 vend_id 为DLL01，而是匹配非 DLL01 之外的所有东西。

    • 通配符过滤

    • 找出所有以词 Fish 起头的产品：
    SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

    如果使用的是 Microsoft Access，需要使用*而不是%。

    • 可以使用多个通配符
    SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

    有一种情况下把通配符放在搜索模式中间是很有用的，就是根据邮件地 址 的 一 部 分 来 查 找 电 子 邮 件 ， 例 如

    WHERE email LIKE 'b%@forta.com'。

    下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。

    SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

    如果使用的是 Microsoft Access，需要使用?而不是_。
    方括号（ []）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。
    找出所有名字以 J 或 M 起头的联系人，可进行如下查询：

    SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

    这一搜索模式使用了两个不
    同的通配符。 [JM]匹配方括号中任意一个字符，它也只能匹配单个字符。因此，任何多于一个字符的名字都不匹配。 [JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。
    注意：
    不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

    4、创建计算字段

    • 拼接

    拼接（concatenate）：将值联结到一起（将一个值附加到另一个值）构成单个值。
    我们来举一个简单例子，创建由两列组成的标题。
    Vendors 表包含供应商名和地址信息。假如要生成一个供应商报表，需要在格式化的名称（位置）中列出供应商的位置。此报表需要一个值，而表中数据存储在两个列 vend_name 和 vend_country 中。此外，需要用括号将 vend_country 括起来，这些东西都没有存储在数据库表中。
    解决办法是把两个列拼接起来。在 SQL 中的 SELECT 语句中，可使用一个特殊的操作符来拼接两个列。根据你所使用的 DBMS，此操作符可用加号（ +）或两个竖杠（ ||）表示。

    SELECT vend_name + ' (' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;

    有的时候可能带来空格，所以需要去掉空格，可以写成这个样子：

    SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name;

    RTRIM()函数去掉值右边的所有空格。通过使用 RTRIM()，各个列都进行了整理。
    顺便给这些新搞的数据取一个新名字：

    SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;

    • 算术计算

    Orders 表包含收到的所有订单， OrderItems 表包含每个订单中的各项物品。下面的 SQL 语句检索订单号 20008 中的所有物品：

    SELECT prod_id, quantity, item_price
FROM OrderItems
WHERE order_num = 20008;

    item_price 列包含订单中每项物品的单价。如下汇总物品的总价格

    SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

    5、使用函数进行计算

    与 SQL 语句不一样， SQL 函数不是可移植的。这意味着为特定 SQL 实现编写的代码在其他实现中可能不正常。

    • 文本处理函数

    1.png

    SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。 SOUNDEX 考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。虽然 SOUNDEX 不是 SQL 概念，但多数DBMS 都提供对 SOUNDEX的支持。

    • 时间处理函数

    不同 DBMS 的日期时间处理函数可能不同。关于具体 DBMS 支持的日期时间处理函数，请参阅相应的文档。

    • 数值处理函数

    2.png

    6、汇总数据

    • 聚集函数

    常用的聚集函数


    3.png

    举例：

    SELECT AVG(prod_price) AS avg_price
FROM Products;

    SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

    第二条前一条的不同之处在于，它包含了 WHERE 子句。此WHERE 子句仅过滤出 vend_id 为 DLL01 的产品，因此 avg_price 中返回的值只是该供应商产品的平均值。
    使用 COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（ NULL）还是非空值。使用COUNT(column)对特定列中具有值的行进行计数， 忽略 NULL 值。
    MAX()返回指定列中的最大值。 MAX()要求指定列名。

    • 聚集不同值

    SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

    只包含不同的值，指定 DISTINCT 参数。它与前面的 SELECT 语句相同，但使用了 DISTINCT 参数，因此平均值只考虑各个不同的价格。

    • 组合聚集函数

    SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM Products;

    7、数据分组

    • 分组

    SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

    上面的 SELECT 语句指定了两个列： vend_id 包含产品供应商的 ID，num_prods 为计算字段（用 COUNT(*)函数建立）。 GROUP BY 子句指示DBMS 按 vend_id 排序并分组数据。这就会对每个 vend_id 而不是整个表计算 num_prods 一次。从输出中可以看到，供应商 BRS01 有 3 个产品，供应商 DLL01 有 4 个产品，而供应商 FNG01 有 2 个产品。
    因为使用了 GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。 GROUP BY 子句指示 DBMS 分组数据，然后对每个组而不是整个结果集进行聚集。

    • 过滤分组

    SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

    SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

    • select子句顺序

    1.png

    8、子查询

    其实就是嵌套，完毕。

    SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

    9、联结

    SQL 最强大的功能之一就是能在数据查询的执行中联结（ join）表。

    • 创建联结关系

    SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

    目前为止使用的联结称为等值联结（ equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（ inner join）。

    • 给表格改名

    SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';

    可以看到， FROM 子句中的三个表全都有别名。 Customers AS C 使用 C作为 Customers 的别名，如此等等。

    • 不同类型联结的区别

    • 自联结

    假如要给与 Jim Jones 同一公司的所有顾客发送一封信件。这个查询要求首先找出 Jim Jones 工作的公司，然后找出在该公司工作的顾客：

    SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name FROM Customers WHERE cust_contact = 'Jim Jones');

    这就是自联结，联结自己。自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多 DBMS 处理联结远比处理子查询快得多。

    • 外联结

    要检索包括没有订单顾客在内的所有顾客，可如下进行：

    SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

    这条 SELECT 语句使用了关键字 OUTER JOIN来指定联结类型（而不是在 WHERE 子句中指定）。但是，与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行。在使用 OUTERJOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（ RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN左边的表）。上面的例子使用 LEFT OUTER JOIN 从 FROM 子句左边的表（ Customers 表）中选择所有行。为了从右边的表中选择所有行，需要使用 RIGHT OUTER JOIN，如下例所示：

    SELECT Customers.cust_id, Orders.order_num
FROM Customers RIGHT OUTER JOIN Orders
ON Orders.cust_id = Customers.cust_id;

    • 使用带集聚函数的联结

    要检索所有顾客及每个顾客所下的订单数

    SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

    也可以这样：

    SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

    10、组合查询

    两次查询一起进行

    SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

    UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION分隔（因此， 如果组合四条 SELECT语句，将要使用三个 UNION关键字）。
    UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
    列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。
    UNION 从查询结果集中自动去除了重复的行；换句话说，它的行为与一条 SELECT 语句中使用多个 WHERE 子句条件一样。
    这是 UNION 的默认行为，如果愿意也可以改变它。事实上，如果想返回所有的匹配行，可使用 UNION ALL 而不是 UNION。

    11、数据插入

    • insert

    INSERT 用来将行插入（或添加）到数据库表。插入有几种方式：
     插入完整的行；
     插入行的一部分；
     插入某些查询的结果。

    INSERT INTO Customers
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);

    这个例子将一个新顾客插入到 Customers 表中。存储到表中每一列的数据在 VALUES 子句中给出，必须给每一列提供一个值。如果某列没有值，如上面的 cust_contact 和 cust_email 列，则应该使用 NULL 值（假定
    表允许对该列指定空值）。各列必须以它们在表定义中出现的次序填充。
    编写 INSERT 语句的更安全（不过更烦琐）的方法如下：

    INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);

    这个例子与前一个 INSERT 语句的工作完全相同，但在表名后的括号里明确给出了列名。在插入行时， DBMS 将用 VALUES 列表中的相应值填入列表中的对应项。 VALUES 中的第一个值对应于第一个指定列名，第二个值对应于第二个列名，如此等等。
    下面的 INSERT 语句填充所有列（与前面的一样），但以一种不同的次序填充。因为给出了列名，所以插入结果仍然正确（因为指定了列名称，甚至可以只插入部分列，你指定哪个列就插入哪个列）：

    INSERT INTO Customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip)
VALUES('1000000006',
NULL,
NULL,
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111');

    INSERT 还存在另一种形式，可以利用它将 SELECT 语句的结果插入表中，这就是所谓的INSERT SELECT。顾名思义，它是由一条 INSERT 语句和一条 SELECT语句组成的。假如想把另一表中的顾客列合并到 Customers 表中。不需要每次读取一行再将它用 INSERT 插入，可以如下进行：

    INSERT INTO Customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM CustNew;

    这 个 例 子 使 用 INSERT SELECT 从 CustNew 中 将 所 有 数 据 导 入Customers。 SELECT 语句从 CustNew 检索出要插入的值，而不是列出它们。 SELECT 中列出的每一列对应于 Customers 表名后所跟的每一列。这条语句将插入多少行呢？这依赖于 CustNew 表有多少行。如果这个表为空，则没有行被插入（也不产生错误，因为操作仍然是合法的）。如果这个表确实有数据，则所有数据将被插入到 Customers。

    • 从一个表复制到另一个表

    有一种数据插入不使用 INSERT 语句。要将一个表的内容复制到一个全新的表（运行中创建的表），可以使用 SELECT INTO 语句。
    与 INSERT SELECT 将数据添加到一个已经存在的表不同， SELECT INTO将数据复制到一个新表（有的 DBMS 可以覆盖已经存在的表，这依赖于所使用的具体 DBMS）。

    SELECT *
INTO CustCopy
FROM Customers;

    这条 SELECT 语句创建一个名为 CustCopy 的新表，并把 Customers 表的整个内容复制到新表中。因为这里使用的是 SELECT ，所以将在CustCopy 表中创建（并填充）与 Customers 表的每一列相同的列。要想只复制部分的列，可以明确给出列名，而不是使用通配符。

    12、数据更新与删除

    • 更新数据

    更新（修改）表中的数据，可以使用 UPDATE 语句。
    基本的 UPDATE 语句由三部分组成，分别是：
     要更新的表；
     列名和它们的新值；
     确定要更新哪些行的过滤条件。
    举一个简单例子。客户 1000000005 现在有了电子邮件地址，因此他的记录需要更新，语句如下：

    UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';

    更新多个列的语法稍有不同：

    UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';

    在更新多个列时，只需要使用一条 SET 命令。
    要删除某个列的值，可设置它为 NULL（假如表定义允许 NULL 值）。如下进行：

    UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

    • 删除数据

    从一个表中删除（去掉）数据，使用 DELETE 语句。有两种使用 DELETE的方式：
     从表中删除特定的行；
     从表中删除所有行。
    从 Customers 表中删除一行：

    DELETE FROM Customers
WHERE cust_id = '1000000006';

    13、操纵表

    • 创建表

    利用 CREATE TABLE 创建表，必须给出下列信息：
     新表的名字，在关键字 CREATE TABLE 之后给出；
     表列的名字和定义，用逗号分隔；
     有的 DBMS 还要求指定表的位置。

    CREATE TABLE Products
(
prod_id CHAR(10) NOT NULL,
vend_id CHAR(10) NOT NULL,
prod_name CHAR(254) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc VARCHAR(1000) NULL
);

    CREATE TABLE Orders
(
order_num INTEGER NOT NULL,
order_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL
);

    这条语句创建本书中所用的 Orders 表。 Orders 包含三列：订单号、订单日期和顾客 ID。这三列都需要，因此每一列的定义都含有关键字 NOTNULL。这就会阻止插入没有值的列。如果插入没有值的列，将返回错误，且插入失败。
    下一个例子将创建混合了 NULL 和 NOT NULL 列的表：

    CREATE TABLE Vendors
(
vend_id CHAR(10) NOT NULL,
vend_name CHAR(50) NOT NULL,
vend_address CHAR(50) ,
vend_city CHAR(50) ,
vend_state CHAR(5) ,
vend_zip CHAR(10) ,
vend_country CHAR(50)
);

    SQL 允许指定默认值，在插入行时如果不给出值， DBMS 将自动采用默认值。默认值在 CREATE TABLE 语句的列定义中用关键字 DEFAULT 指定。

    CREATE TABLE OrderItems
(
order_num INTEGER NOT NULL,
order_item INTEGER NOT NULL,
prod_id CHAR(10) NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1,
item_price DECIMAL(8,2) NOT NULL
);

    • 更新表

    更新表定义，可以使用 ALTER TABLE 语句。

    ALTER TABLE Vendors
ADD vend_phone CHAR(20);

    • 删除表

    DROP TABLE CustCopy;

    • 创建视图

    CREAT VIEW

    CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

    相关文章

      网友评论

        本文标题：SQL基础语法笔记

        本文链接：https://www.haomeiwen.com/subject/pqbjnktx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        技巧文章

        关于我们|服务条款|联系我们|SQL基础语法笔记|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！