SQL必知必会笔记

2022-08-15 21:00:42

SQL必知必会笔记

第一课了解SQL

主键列中的值不允许修改或更新
主键值不能重用(如果某行从表中删除，他的主键不能赋给以后的新行)

第二课检索数据

SQL语句不区分大小写，一般对SQL关键字使用大写，而对列名和表名使用小写。

select语句

  SELECT prod_name FROM Products;

DISTINCT

使用DISTINCT关键字返回不同的值,DISTINCT关键字必须直接放在列名的前面。

  SELECT DISTINCT vend_id
  FROM Products;

限制结果

TOP(SQL Server)
使用TOP关键字来限制返回多少行
```
 SELECT TOP prod_name
 RROM Products;
```

ROWNUM(Oracle) 使用ROWNUM关键字来计算行

 SELECT prod_name
 FROM Products
 WHERE ROWNUM <=5;

LIMIT(MySQL) 使用LIMIT关键字来限制返回多少行

 # 返回前5条
 SELECT prod_name
 FROM Products
 LIMIT 5;

 # 指定从那条开始
 SELECT prod_name
 FROM Products
 LIMIT 5 OFFSET 5;  -- 返回5-9行，数据从第0行开始。

注释

    /* 这是一段
    注释*/
    # 这是一条注释
    SELECT prod_name -- 这也是一条注释
    FROM Products;

第三课排序检索数据

排序数据

使用ORDER BY来对输出进行排序。

  SELECT prod_name
  FROM Products
  ORDER BY prod_name;

注意：ORDER BY必须是SELECT语句中最后一条子句，如果不是最后的子句，会出现错误消息。

按多个列排序

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

指定排序方向

使用DESC关键字进行降序排序。

  SELECT prod_id,prod_price,prod_name
  FROM Products
  ORDER BY prod_price DESC,prod_name; -- 按price降序，name升序

DESC只应用到直接位于其前面的列名，如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。
大多数数据库管理系统中默认A和a的字典序中是相同的，当时允许管理员改变这种行为。

第四课过滤数据

WHERE子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
!<	不小于
>	大于
>=	大于等于
!>	不大于
BETWEEN	在指定的两个值之间
IS NULL	为NULL值
IS NOT NULL	不为NULL

  SELECT prod_name,prod_price
  FROM Products
  WHERE prod_price BETWEEN 5 AND 10;

检查字段是否为空值，不能用=NULL，而要用IS NULL

  SELECT prod_name,prod_price
  FROM Products
  WHERE prod_price IS NULL;

第五课高级数据过滤

AND，OR 操作符

AND的优先级高于OR，AND和OR进行组合时推荐使用圆括号进行明确分组。

IN操作符

  SELECT prod_name,prod_price
  FROM Products
  WHERE vend_id IN ('DLL01','BRS01');

IN操作符完成了OR相同的功能，但执行更快。

NOT操作符

WHEREz子句中用来否定其后条件的关键字。 NOT可以用在要过滤的列前，不仅仅是在其后。

  SELECT prod_name,prod_price
  FROM Products
  WHERE NOT vend_id = 'DLL01';

第六课用通配符进行过滤

LIKE操作符

% 通配符
百分号表示0个，1个或多个任意字符。但WHERE name LIKE ‘%’不会匹配值为NULL的行。
```
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';
```

_ 通配符下划线只匹配单个字符

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

第七课创建计算字段

字段拼接

使用加号+或两个竖杆||，视DBMS而定

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

TRIM函数

使用RTRIM()去掉字符串右边的空格，使用LTRIM()去掉左边的空格，使用TRIM()去掉两边的空格

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

使用别名

别名使用AS关键字

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

算数计算

支持加减乘除运算符

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

第八课使用数据处理函数

UPPER()函数

Upper()函数将文本转换为大写

  SELECT vend_name,UPPER(vend_name) AS vend_name_upcase
  FROM Vendors
  ORDER BY vend_name;

常用文本处理函数

函数	说明
left()	返回字符串左边的字符
LENGTH()	返回字符串长度
LOWER()	将字符串转换为小写
LTRIM()	去掉字符串左边的空格
RIGHT()	返回字符串右边的字符
RTRIM()	去掉字符串右边的空格
SOUNDEX()	返回字符串的SOUNDEX值
UPPER()	将字符串转换为大写

第九课汇总数据

SQL聚集函数

函数	说明
AVG()	平均值
COUNT()	行数
MAX()	最大值
MIN()	最小值
SUM()	值之和

如果指定列名，则COUNT()函数会忽略指定列的值为空的行，但COUNT(* )不会忽略空行。

  SELECT COUNT(* ) AS num_cust
  FROM Customers;

  SELECT COUNT(cust_email) AS num_cust
  FROM Customers;

DISTINCT

对于所有执行计算，默认ALL参数，如果要包含不同的值，指定DISTINCT参数。

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

第十课分组数据

GROUP BY

GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。

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

HAVING

WHERE在数据分组之前进行过滤，HAVING在数据分组后进行过滤。WHERE排除的行不包括在分组中。

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

一般在使用GROUP BY子句时，应该也给出ORDER BY子句，这是保证数据正确排序的唯一方法。

第十一课使用子查询

子查询即嵌套查询

  SELECT cust_id
  FROM Orders
  WHERE order_num IN (SELECT order_num
                      FROM OrderItems
                      WHERE prod_id = 'GRAN01')；



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

第十二课联结表

创建联结

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

创建高级联结

表别名

Oracle不支持AS关键字，直接指定表名即可。(因此，应该是Customers C，而不是Customers AS C)。

自联结

查询所有与Jim Jones同一公司的所有顾客

  #方法一：子查询
  SELECT cust_id,cust_name,cust_contact
  FROM Customers
  WHERE cust_name = (SELECT cust_name
                    FROM Customers
                    WHERE cust_contact = 'Jim Jones');

  #方法二：联结
  SELECT c1.cust_id,c1.cust_name,c1.cust_contact
  FROM Customers AS c1, 大专栏  SQL必知必会笔记Customers AS c2
  WHERE c1.cust_name = c2.cust_name
  AND c2.cust_contact = 'Jim Jones';

许多DBMS处理联结远比处理子查询快。

自然联结

外联结

联结包含了那些在相关表中没有关联的行，这种联结称为外联结。外联接可以是左向外联接、右向外联接或完整外部联接。
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：
1）LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

  #内联结
  select a.* ,b.* from a inner join b on a.id=b.parent_id
  #左联结,a在左b在右,若b中没有a中对应行,显示为空,所有的a都会出现
  select a.* ,b.* from a left join b on a.id=b.parent_id
  #右联结,a在左b在右,若a中没有b中对应行,显示为空,所有的b都会出现
  select a.* ,b.* from a right join b on a.id=b.parent_id
  #完全联结
  select a.* ,b.* from a full join b on a.id=b.parent_id

第十四课组合查询

UNION

  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';

  #功能相同
  SELECT cust_name,cust_contact,cust_email
  FROM Customers
  WHERE cust_state IN ('IL','IN','MI')
  OR cust_name = 'Fun4ALL';

UNION规则

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分割。
UNION中的每个查询必须包含相同的列、表达式或聚集函数
列数据类型必须兼容
使用UNION ALL返回所有匹配行

使用UNION时，重复的行会被自动取消。如果想返回所有的匹配行，可使用UNION ALL。

UNION结果排序

在用UNION组合查询时，只能使用一条ORDER BY子句，而且必须位于最后一条SELECT语句后。

第十五课插入数据

插入完整的行

有两种方式，指定列名或使用默认列顺序。推荐使用指定列名

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

不管使用哪种INSERT语法，VALUES的数目必须正确。如果不提供列名，则必须给每个表列提供一个值；如果提供列名，则必须给列出的每个列一个值。否则，就会产生一条错误消息，相应的行不能成功插入。

插入部分行

省略的列必须满足一下条件：

该列定义为允许NULL值
在表定义中给出默认值。这表示如果不给出值，将使用默认值。

插入检索出的数据

    INSERT INTO Customers(cust_id,
                          cust_contact,
                          cust_email,
                          cust_name,
                          cust_address,
                          cust_city,
                          cust_state,
                          cust_zip)
    SELECT cust_id,
           cust_contact,
           cust_email,
           cust_name,
           cust_address,
           cust_city,
           cust_state,
           cust_zip
    FROM CustNew; INSERT SELECT 中SELECT语句可以包含WHERE子句，以过滤插入的数据。

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

  SELECT *
  INTO CustCopy
  FROM Customers;

更新和删除数据

更新数据

  UPDATE Customers
  SET cust_email = 'KIN@store.com',
      cust_contact = 'Sam'
  WHERE cust_id = '10000005';

删除某列的值，可以设置它为NULL

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

删除数据

从表中删除特定行

DELETE FROM Customers
WHERE cust_id = '10000006';

删除所有行
不要使用DELETE，可以使用TRUNCATE TABLE，速度更快。

创建和操纵表

创建表

如果不指定是否为可以为空，则默认可以为NULL，只有不允许NULL值的列可以作为主键。

    CREATE TABLE OrderItems
    (
      order_id       INTEGER        NOT NULL     primary key, --第一种主键创建方式
      order_num      INTEGER        NOT NULL,
      order_item     INTEGER        NOT NULL,
      pro_id         CHAR(10)       NOT NULL,
      quantity       INTEGER        NOT NULL      DEFAULT 1,
      order_state    CHAR(50)       NULL,
      order_zip      CHAR(10)         ,
      --第二种主键创建方式：在这里写入primary key (order_id,order_num)
    ) ### 更新表

  ALTER TABLE Vendors
  ADD vend_phone CHAR(20);

  ALTER TABLE Vendors
  DROP COLUMN vend_phone;

删除表

  DROP TABLE CustCopy;

重命名表

不同DBMS语法不同

第十八课使用视图

为什么使用视图

重用SQL语句
简化复杂的SQL操作
使用表的一部分而不是全部
保护数据，授予用户部分表的部分权限而不是全部
更改数据格式和表示。

创建视图

使用CREATE VIEW来创建视图。

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

利用视图简化复杂的联结

  SELECT cust_name,cust_contact
  FROM ProductsCustomers
  WHERE pro_id = 'RGAN01';

利用视图来重新格式化检索出的数据

  CREATE VIEW VendorLocations AS
  SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
        AS vend_title
  FROM Vendors;

  SELECT *
  FROM VendorLocations;

过滤不想要的字段

使用视图计算字段

第十九课使用存储过程

简单来说，存储过程就是为以后使用而保存的一条或多条SQL语句。

执行存储过程

使用EXCUTE关键字。

  EXECUTE AddNewProduct('JTS01',
                        'Stuffed Eiffel Tower',
                        6.49,
                        'introduce of product'); ### 创建存储过程 不同DBMS不同语法，一下是Oracle版本

  CREATE PROCEDURE MailingListCount(
    ListCount OUT INTEGER
  )
  IS
  v_rows INTEGER;
  BEGIN
      SELECT COUNT(* ) INTO v_rows
      FROM Customers
      WHERE NOT cust_email IS NULL;
      ListCount := v_rows;
  ENDS;

第二十课管理事务处理

提交事务

  BEGIN TRANSACTION
  DELETE OrderItems WHERE order_num = 12345
  DELETE Orders WHERE order_num = 12345
  COMMIT TRANSACTION

使用保留点

  SAVE TRANSACTION delete1;
  ROLLBACK TRANSACTION delete1;

第二十一课使用游标

游标可以实现在检索出来的行中前进或退后

创建游标

  DECLARE CustCursor CURSOR
  FOR
  SELECT * FROM Customers
  WHERE cust_email IS NULL

使用游标

使用OPEN CURSOR 语句打开游标。
使用FETCH语句访问游标数据。
使用CLOSE语句关闭游标。

第二十二课高级SQL特性

主键

任意两行的主键都不相同
每行都具有一个主键
包含主键值的列不允许修改和更新
主键值不能重用

#创建表时指定主键
CREATE TABLE Vendors ( vend_id CHAR(50) NOT NULL PRIMARY KEY, vend_name CHAR(30) NULL );

#使用CONSTRAINT语法来指定主键 ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY (vend_id);

外键

CREATE TABLE Orders ( cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id) ); #或者 ALTER TABLE Orders ADD CONSTRAINT FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

检查约束

使用CHECK语句来检查约束

  CREATE TABLE OrderItems
  (
    ...
    quantity INTEGER NOT NULL CHECK(quantity>0)
  );
  #或者
  ADD CONSTRAINT CHECK (gender LIKE '[MF]');

索引

  CREATE INDEX prod_name_ind
  ON PRODUCTS (prod_name);

触发器

关键字TRIGGER，不同DBMS语法不同

码农公寓

SQL必知必会笔记

第一课 了解SQL

第二课 检索数据

select语句

DISTINCT

限制结果

注释

第三课 排序检索数据

排序数据

按多个列排序

指定排序方向

第四课 过滤数据

WHERE子句操作符

第五课 高级数据过滤

AND，OR 操作符

IN操作符

NOT操作符

第六课 用通配符进行过滤

LIKE操作符

第七课 创建计算字段

字段拼接

TRIM函数

使用别名

算数计算

第八课 使用数据处理函数

UPPER()函数

常用文本处理函数

第九课 汇总数据

SQL聚集函数

DISTINCT

第十课 分组数据

GROUP BY

HAVING

第十一课 使用子查询

第十二课 联结表

创建联结

创建高级联结

表别名

自联结

自然联结

外联结

第十四课 组合查询

UNION

UNION规则

使用UNION ALL返回所有匹配行

UNION结果排序

第十五课 插入数据

插入完整的行

插入部分行

插入检索出的数据

更新和删除数据

更新数据

删除数据

创建和操纵表

创建表

删除表

重命名表

第十八课 使用视图

创建视图

利用视图简化复杂的联结

利用视图来重新格式化检索出的数据

过滤不想要的字段

使用视图计算字段

第十九课 使用存储过程

执行存储过程

第二十课 管理事务处理

提交事务

使用保留点

第二十一课 使用游标

创建游标

使用游标

第二十二课 高级SQL特性

主键

外键

检查约束

索引

触发器

相关文章

第一课了解SQL

第二课检索数据

第三课排序检索数据

第四课过滤数据

第五课高级数据过滤

第六课用通配符进行过滤

第七课创建计算字段

第八课使用数据处理函数

第九课汇总数据

第十课分组数据

第十一课使用子查询

第十二课联结表

第十四课组合查询

第十五课插入数据

第十八课使用视图

第十九课使用存储过程

第二十课管理事务处理

第二十一课使用游标

第二十二课高级SQL特性