SQL必知必会(第二部分 11-17课)

第11课. 使用子查询

  • 子查询,即嵌套在其他查询中的查询
    1.使用IN子句,子查询总是由内向外处理。

–列出订购物品RGAN01的所有顾客

--Orders中的order_num是唯一订单号
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id **IN**(SELECT cust_id
       			FROM Orders
       			WHERE order_num  **IN**(SELECT order_num
       						FROM OrderItems
       						WHERE prod_id='RGAN01') )

对于能嵌套的子查询的数目没有限制。
作为子查询的SELECT语句只能查询单个列

2.创建计算字段。

  • 完全限定列名,指定表名和列名。用句点(.)分割表名和列名。

–显示每个顾客的订单总数。

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

第12课.联结表

相同的数据出现多次决不是一件好事。

  • 关系表的设计就是要把信息分解成多个表,一类数据一个表。各表通过某些共同的值互相关联(所以才叫关系数据库)。
    关系数据表的伸缩性远比非关系数据库要好。

  • 可伸缩:能够适应不断增加的工作量而不失败。

联结

  • 联结,是一种机制,用来在一条SELECT语句中关联表。使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。
    联结不是物理实体(在实际的数据表中并不存在),在查询执行期间一直存在。
  • 只要指定联结的所有表以及关联它们的方式就可以创建联结。
SELECT vend_name, prod_name, prod_price --前两列在一个表中,第三列在另一个表中
FROM Vendors, Products --两个表
WHERE Vendors.vend_id=Products.vend_id; --联结 过滤条件
--没有WHERE子句,第一个表中的每一行将与第二个表中的每一行配对

在一条SELECT语句中联结几个表时,相应的关系是在运行时构造的。

  • 笛卡尔积 :由没有联结条件的表关系返回的结果。检索出的行的数目僵尸第一个表中的行数乘以第二个表中的行数。返回笛卡尔积的联结,也称叉联结
  • 保证所有联结都有WHERE子句

内联结

等值联结,也称内联结,基于两个表之间的相等测试

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

两个表之间的关系以INNER JOIN 指定的部分FROM子句,联结条件用特定的ON子句而不是WHERE。
SQL不限制一条SELECT语句中可以联结的表的数目。

子查询有时可用联结完成相同的功能。

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

第13课.创建高级联结

SQL允许给表名起别名

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

表别名只在查询执行中使用。与列别名不一样,表别名不返回到客户端

自联结

--给与Jim Jones同一公司的所有顾客发送一封邮件。
--cust_name为顾客名, cust_contact为顾客的联系名
SELECT c1.cust_id, c1.cust_name, c1.cust_contact 
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name=c2.cust_name
      AND c2.cust_contact= 'Jim Jones';

自然联结

排除多次出现,使每一列只返回一次。
只能选择那些唯一的列,一般通过对一个表使用通配符(SELECT *),而对其他表的列使用明确的子集来完成。

外联结

即联结中包含了那些在相关表中没有关联行的行
LEFT OUTER JOIN, LEFT指的是OUTER JOIN 左边的表
RIGHT OUTER JOIN
全外联结:包含两个表的不关联的行。 FULL OUTER JOIN

第14课.组合查询

允许执行多个查询(多条SELECT语句),并将结果作为一个查询结果集返回。这些查询组合通常称为并(union)或复合查询。

OR 关键字

给出每条SELECT语句,在各条语句之间放上关键字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' ;
  • UNION必须由两条或两条以上的SELECT语句组成
  • UNION中的每个查询必须包含相同的列、表达式或聚集函数(不需要相同的次序)
  • 列数据类型必须兼容
  • UNION从查询结果集中自动去除重复的行
    UNION ALL返回所有的行(如果需要每个条件的匹配行全部出现(包括重复行),必须使用UNION ALL,而不是WHERE)。
  • 使用组合查询UNION时,只能使用一条ORDER BY子句,必须位于最后一条SELECT之后。

EXCEPT(有时称为MINUS)可用来检索只在第一个表中存在而在第二个表中不存在的行。

INSERSECT 可用来检索两个表中都存在的行。

第15课.插入数据

INSERT 将行插入(或添加)到数据表中。
存储到表中每一列的数据在VALUES子句中给出,必须给每一列提供一个值,没有值则用NULL。各列必须以在表中的定义出现的次序填充。

--将一个新顾客插入到Customers表中
INSERT INTO Customers
VALUES ('100000006', 'Toy Land', '123 Street', 'New York', 'NY',
	 '11111', 'USA', NULL, NULL);


--将一个新顾客插入到Customers表中,在表名后的括号里明确给出列名。
--不一定按出现在表中的实际次序。必须给出列出列的每一个值。
INSERT INTO Customers (cust_id, cust_name, cust_address, cust_city,
	 cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES ('100000006', 'Toy Land', '123 Street', 'New York', 'NY',
  '11111', 'USA', NULL, NULL);

通过省略列,可以用INSERT插入部分行

可以利用INSERTSELECT语句的结果插入表中。

--想把另一表中的顾客列合并到Customers表中
INSERT INTO Customers (cust_id, cust_name, cust_address, cust_city,
      cust_state, cust_zip, cust_country, cust_contact, cust_email)
SELECT cust_id, cust_name, cust_address, cust_city,
      cust_state, cust_zip, cust_country, cust_contact, cust_email
FROM CustNew;

使用的是列的位置,不是列名。SELECT中的第一列(不管其列名)将用来填充表中的表列中指定的第一列。

  • INSERT 通常只插入一行。
  • INSERT SELECT可以用一条INSERT插入多行。

讲标的内容复制到一个全新的表(运行中创建的表),可以使用SELECT INTO语句。

SELECT *
INTO CustCopy --CustCopy为新表
FROM Customers;

第16课.更新和删除数据

16.1.更新

更新(修改)表中的数据,可以使用UPDATE语句。
基本的UPDATE语句,由三部分组成:

  • 要更新的表
  • 列名 和 它们的新值
  • 确定要更新哪些行的过滤条件。

SET命令用来将心智赋给被更新的列。

UPDATE Customers --以要更新的表名开始
SET cust_email = 'kim@thetoystore.com' 
WHERE cust_id= '10000005' --没有WHERE子句,会更新表中的所有行

更新多个列,只需要使用一条SET命令,每个“列=值”对之间用逗号分隔

UPDATE Customers  
SET cust_contact='Sme Roberts',   
        cust_email= 'sam@toyland.com'
WHERE cust_id= '10000006' 

要删除某个列的值,可设置它为NULL。

16.2.删除 DELETE

不要省略WHERE子句。

DELETE FROM Customeres
WHERE cust_id= '100000006';

1.DELETE删除整行而不是删除列。要删除指定的列,需要使用UPDATE语句。
2.DELETE不删除表本身。如果想从表中删除所有行,使用TRUNCATE TABLE语句。

在进行UPDATE或DELETE语句使用WHERE子句前,应该先用SELECT进行测试,保证它过滤的是正确的结果。

第17课.创建和操纵表

17.1创建表

用程序创建表,可以使用SQL的CREATE TABLE语句。
利用CREATE TABLE创建表,必须给出下列信息:

  • 新表的名字
  • 表列的名字和定义,各列用逗号隔开
  • 有的DBMS要求指定表的位置。

创建新表时,指定的表名必须不存在。

--创建Products表
CREATE TABLE Products
(--每列的定义以表名(在表中必须是唯一的)开始,后跟列的数据类型。
--MySQL,varchar必须替换为text
	prod_id		CHAR(10)		NOT NULL,
	vend_id		CHAR(10)		NOT NULL,
	prod_name	CHAR(254)		NOT NULL    DEFAULT '',
	prod_price	DECIMAL(8,2)		NOT NULL,
	prod_desc	VARCHAR(1000)		NULL--可以省略NULL
);

每个表列的状态要么时NULL列,要么是NOT NULL列。
NULL为默认设置,如果不指定NOT NULL,就认为指定的是NULL列。

只有不允许NULL值的列可作为主键,允许NULL值的列不能作为唯一的标识。
不要把NULL值与空字符串(’ ')相混淆。

SQL允许指定默认值,在插入行时如果不给出值,DBMS将自动采用默认值。默认值在CREATE TABLE语句的列定义中用关键字DEFAULT指定。
默认值经常用于日期或时间戳列。可用(MySQL)
**DEFAULT CURRENT_DATE( )**将系统日期用作默认日期。

17.2.更新表

使用ALTER TABLE语句,必须给出:

  • 要更改的表名。
  • 列出要做哪些改变。
--给Vendors表增加名为vend_phone的列。
ALTER TABLE Vendors
ADD vend_phone CHAR(20);

-给Vendors表删除名为vend_phone的列。
ALTER TABLE Vendors
DROP COLUMN vend_phone;

17.3.删除表

DROP TABLE删除整个表。

DROP TABLE CustCopy; 

删除不能撤销。

17.4.重命名表

RENAME语句

ALTER TABLE 旧表名 RENAME  新表名;

添加字段:ALTER TABLE 表名 ADD 字段名 数据类型 [属性];
修改字段名:ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];
删除字段:ALTER TABLE 表名 DROP 字段名;
添加主键约束:ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名(主键字段);
添加外键约束:ALTER TABLE 表名 ADD CONSTRAINT 主键名 FOREIGN KEY 表名(外键字段)REFERENCES 关联表名(关联字段);

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值