sql——聚合与排列+数据的插、删、更新

把变量转化数值

聚合与排列

cast(列名 as bigint)

聚合查询

聚合函数——中能在select、having、order by子句中使用

• count：记录表中数据的行数
• sum：记录表中数值列中数据的合计值
• avg：… 平均值
• max：…最大值
• min：…最小值

1. 只有count函数包含null，其余聚合函数在计算时将null排除在外
2. max、min函数适用于任何数据类型的列

计算值的种类（使用distinct剔除重复行最后得到种类个数）

select count(distinct 商品种类)
from Products;

SELECT SUM(cast(销售单价 as bigint)), SUM(DISTINCT cast(销售单价 as bigint))
FROM Products;

分组、为查询结果指定条件

聚合键中包含NULL

结果中以不确定行（空行）形式表现出来

group by子句

把表分为几组再进行汇总处理。其中group by子句中指定的列称为聚合键/分组列

select 列名1,列名2,列名3,...
from 表名
group by 列名1,列名2,列名3,...

例：

select 商品种类,count(*) as count
from Products
group by 商品种类

子句的书写顺序（暂定）

1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

使用WHERE子句和GROUP BY子句进行汇总处理：
根据where指定的条件过滤再进行汇总

select 列名1,列名2,列名3,...
from 表名
where 条件
group by 列名1,列名2,列名3,...

select 销售单价,count(*) as count
 from Products
 where 商品种类='衣服'
group by 销售单价;

常见错误：
1.使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名。
2.在GROUP BY子句中不能使用SELECT子句中定义的别名。
3.GROUP BY子句结果的显示是无序的。

having 子句

写在group by字句之后，可用于指定组的条件
having 子句可跟聚合键、聚合函数、常数
例：

select 进货单价,count(*) as count
 from Products
group by 进货单价;

select 进货单价,count(*) as count
 from Products
group by 进货单价
having 进货单价>500;

区分：where用于指定行的条件，having用于指定组的条件

对查询结果进行排序——order by子句

• 默认升序排列，可用关键字ASC、DESC以列为单位指定为升序、降序
• 指定多个排序键：默认按照排序基准列1排序，该列出现相同值时，按排序基准列2排序…
• 排序键的列包含NULL时，会在开头或末尾进行汇总
• 排序键可使用列的别名
• 排序键可使用表中存在但不在select子句中的列/聚合函数

select 列名1,列名2,列名3,...
from 表名
order by 排序基准列1 (ASC/DESC),排序基准列2 (ASC/DESC)...----写在select语句末尾

使用 HAVING 子句时 SELECT 语句的顺序
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

数据的插删、更新

插入

insert语句

可使用group by，having，order by语句
回忆：建立数据表：create table语句

• insert 以行为单位插入数据语句，包含列清单和值清单
• 除数字外，插入的内容需用单引号括起来（包括日期）
• 对表进行全列insert时，可以省略表名后的列清单

insert into 表名 (列名1,列名2,...) values ('值1','值2',...)
insert into 表名 values ('值1','值2','值3',...)

插入null 值

直接在值清单中插入即可（此时对应的列不能设置农not null约束）

插入默认值

需在创建表时指定默认值约束

create table productins
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER DEFAULT 0,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));

• 显性插入默认值：

INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0007', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');

• 隐性插入默认值：在列清单和VALUES 中省略设定了默认值的列即可
• 省略INSERT语句中的列名，就会自动设定为该列的默认值（没有默认值时会设定
  为NULL）。

从其他表中删除复制数据

• 从其他表中复制数据：

insert into 目标表 (目标表列名1,列名2,...)
select 要复制的各列名
from 原有数据表

insert into ProductCopy(商品名称,商品种类,销售单价,进货单价,登记日期)
select 商品名称,商品种类,销售单价,进货单价,登记日期 
  from Products;  -------将表Products中的数据复制到新表ProductCopy中

删除

将表完全删除——drop table语句

删除表中的数据但保留表——delete语句

• 删除全部数据行：delete from 表名;
• 删除部分数据行：delete from 表名 where 条件;

delete from Products
where 进货单价 is null;   ---删除进货单价为空的行

更新

• 全表更新：update 表名 set 表名='xxx';

update Products
	set 登记日期='2009-10-10'

• 指定更新

update 表名 
	set 表名='xxx' 
	where 条件;

update Products 
 set 进货单价=进货单价*10   -----更新方式/内容
 where 商品种类='厨房用具';   ----指定更新对象

• NULL清空
  即将列表更新为NULL。
  只有未设置NOT NULL约束的列才可更新为NULL。

update Products 
	set 登记日期='NULL'
where 商品种类='衣服'

• 多列更新

update Products 
 set 列1=更新1
 	 列2=更新2
 	 ...
 where 商品种类='厨房用具';   ----指定更新对象

update Products 
 set (列1，列2,...)=(更新1,更新2,...)
 	 ...
 where 商品种类='厨房用具';   ----指定更新对象

事务

定义：是需要在同一个处理单元中执行的一系列更新处理的集合。

begin transaction;  -------事务开始语句
    DML语句1;
    DML语句2;
    DML语句3;
    ...
commit;             -------事务结束语句

例：

begin transaction;
	 ----运动T恤的销售单价降低1000
	update Products
	 set 销售单价=销售单价-1000
	where 商品名称='运动T恤';
	-----运动T恤销售单价上浮1000
	update Products
	 set 销售单价=销售单价+500
	where 商品名称='运动T恤';
commit;

• 取消处理——rollback
  是取消事务包含的全部更新处理的结束指令，相当于文件处理中的放弃保存

begin transaction; 
    DML语句1;
    DML语句2;
    DML语句3;
    ...
rollback;            

事务的ACID特性
1.A原子性——事务执行结束后所有更新处理将全部执行/不执行，不存在部分执行的情况
2.C一致性（完整性）——事务执行中包含的处理需男足数据库提前设置的约束，如主键约束、NOT NULL 约束
3.I隔离性——不同事务之间互不干扰，独立进行
4.D持久性——事务提交后该时间点的数据状态会被保存。即使由于系
统故障导致数据丢失，数据库也一定能通过某种手段进行恢复