SQL必知必会PartOne—SQL基础之三
目录
1. 分组数据
2. 使用子查询
3. 联结表
4. 组合查询
5. 插入数据
6. 更新和删除数据
7. 创建和操纵表
正文
1. 分组数据
创建分组:分组是使用SELECT 语句的 GROUP BY 子句建立的。
SELECT vend_id ,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id ;
关于GROUP BY 的规定
- GROUP BY 子句可以包含任意数目的列,因而可以对分组进行嵌套,更细致地进行数据分组;
- 如果在GROUP BY 子句嵌套了分组 , 数据将在最后指定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别列取回数据) ;
- GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT 中使用表达式,则必须在GROUP BY 子句中指定相同的表达式,不能用别名 ;
- 大多数SQL 实现不允许 GROUP BY 列带有长度可变的数据类型(如:文本或备注型字段) ;
- 除了聚集计算语句外,SELECT 语句中的每一列都必须都必须在GROUP BY 子句中给出 ;
- 如果分组中包含具有NULL值的行,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组 ;
- GROUP BY 子句必须出现在WHERE 子句之后,ORDER BY 之前。
2. 使用子查询
子查询在一定程度上可以认为是嵌套查询,将一个SELECT查询语句的结果作为另外一个SELECT查询语句的条件等情况,可以视为是子查询。
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'RGAN01');
注意: 作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回错误!
--作为计算字段使用子查询 !!!请使用完全限制列名,如果进行表内联结可以使用 AS 关键字 赋予名字
SELECT cust_name ,
cust_state ,
( SELECT COUNT(*)
FROM orders
WHERE orders.cust_id = custmers.cout_id ) AS orders
FROM custmers
ORDER BY cust_name ;
3. 联结表
可伸缩性 : 能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好。
联结:联结是一种机制,用来在一条SELECT 语句中关联表。使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。
SELECT vend_name,prod_name, prod_price
FROM vendors ,products
WHERE vendors.vend_id = products.vend_id ;
4. 组合查询
组合查询通常称为:并(union)或复合查询(compound query)
主要有两种情况需要组合查询:
1. 在一个查询中从不同的表返回结构数据 ;
2. 对一个表执行多个查询,按照一个查询返回数据。
4.1 使用UNION
使用 UNION很简单 ,所要做的只是给出每条SELECT 语句,在每个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';
4.2 UNION 规则
- UNION 必须由两条或两条以上的SELECT 语句组成 ,语句之间使用关键字UNION 分隔。
- UNION 中的每一个查询必须包含相同的列、表达式、或聚集函数。
- 列数据类型必须兼容:类型不必完全相同,但是必须是DBMS可以隐含转换的类型。
注意:UNION 会自动的消除重复数据的行,如果不想消除重复的数据,可以使用 UNION ALL
如果要对结果排序使用 ORDER BY,这个ORDER BY语句只能在语句的最后并且只能有一个。
5. 插入数据
INSERT用来将行插入(或添加)到数据库表。插入有几种方式:
- 插入完整的行;
- 插入行的一部分;
- 插入某些查询结果
A. 插入完整的行:使用基本的INSERT语法,它要求指定表名和插入到新行中的值。
INSERT INTO customers
VALUES ('1000006' ,
'Toy Land' ,
'123 Any ' ,
'New York' ,
'NY' ,
'11111' ,
'USA' ,
NULL ,
NULL );
B. 插入行的一部分
INSTER INTO customers(cust_id ,
cout_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('1000006' ,
'Toy Land' ,
'123 Any ' ,
'New York' ,
'NY' ,
'11111' ,
'USA' ,);
注意:如果表中不允许有NULL值或者默认值,这时却省略了表中的值,DBMS就会产生错误,相应的行不能插入。
C. 插入查询结果
INSTER INTO customers(cust_id ,
cout_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT (cust_id ,
cout_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
FROM custNew;
D. 从一个表复制到另外一个表
SELECT *
INTO CustCopy
FROM Customers;
MariaDB , MySQL , Oracle ,PostgreSQL ,SQLite 语法:
CREATE TABLE CustCopy AS
SELECT * FROM Customers;
SELECT INTO 注意
- 任何SELECT选项和子句都可以使用,包括WHERE 和 GROUP BY;
- 可利用联结从多个表插入数据
- 不管从多少个表中检索数据,数据都只能插入到一个表中。
6. 更新和删除数据
更新数据:UPDATE 语句由3部分构成:要更新的表、列名和它们新的值、确定要更新哪些行的过滤条件
A. 更新表中的特定行(当不限定过滤条件时更新所有行–WHERE 进行条件限定):
UPDATE Customers
SET cust_contact = 'sam',
cout_email = 'aim_long@163.com'
WHERE cust_id = '10006';
B. 删除数据(当不限定过滤条件时更新所有行–WHERE 进行条件限定):
DELETE FROM Customters
WHERE cust_id = '100006'
更新和删除的指导原则
- 除非确实打算更新或删除每一行,否则绝对不要使用不带WHERE 子句的UPDATE或DELETE语句
- 保证每一个表都有主键,尽可能像WHERE那样使用它。
- 在UPDATE 或DELETE 语句使用WHERE 子句前,应该先用SELECT 进行测试,保证它过滤的是正确的记录,以防编写的WHERE 子句不正确。
- 使用强制实施引用完整性的数据库,这样DBMS将不允许删除其数据与其他表相关联的行。
- 有的DBMS允许数据库管理员施加约束,防止执行不带WHERE子句的UODATE 或DELETE 语句。如果与采用的DBMS 支持这个特性,应该使用它。
7. 创建和操纵表
表创建的基础:1.表名—在关键字GREATE TABLE 之后; 2. 表列的名和定义(逗号分隔);3.有的DBMS 还要求指定表的位置。
CREATE TABLE Produces
( prod_id CHAR(10) NOT NULL ,
prod_name CHAR(10) NOT NULL,
prod_update_state CHAR(10) NOT NULL DEFAULT 'Y'
);
更新表
- 1.理想状态下,不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来的可能的需求,避免今后对表的结构做出大改动。
- 2.所有的DBMS 都允许给现有的表增加列,不过对所增加的列,不过对所增加列的数据类型有所限制。
- 3.许多DBMS 都不允许删除或更改表中的列。
- 4.多数DBMS 允许重新命名表中的列。
- 5.许多DBMS 限制已经填有数据的列进行更改,对未填有数据的列几乎没有限制。
使用ALTER TABLE 更改表结构必须给出下面的信息:
- 1.在 ALTER TABLE 之后要给出要更改的表名;
- 2.列表要做出哪些更改。
ALTER vendors
ADD vend_phone CHAR(20);
ALTER TABLE Vendors
DROP COLUMN vend_phone ;
在复杂的表结构更改一般需要手动删除过程,它涉及一下步骤:
- 使用新的布局创建一个新表;
- 使用INSERT INTO 语句从旧表复制数据到新表。有必要的话,可以使用转换函数和计算字段;
- 检验包含所需数据的表;
- 重命名旧表(如果确定,可以删除它);
- 用原来旧表的名字重新命名新表;
- 根据需要,重新创建触发器、存储过程、索引和外键。
删除表
DROP TABLE CustCopy ;
--删除表没有确认,也无法撤销