以下基于mysql
一个商品表
create table Product (
product_id char(4) not null,
product_name varchar(100) not null,
product_type varchar(32) not null,
sale_price integer ,
purchase_price integer ,
regist_date date ,
primary key (product_id));
导入sql语句
// 进入mysql命令行
source 路径
删除表
drop table product
重命名表
rename table Poduct to Product
增加列
alter table product add column product_name_pinyin varchar(100);
删除列
alter table product drop column product_name_pinyin;
更改列为主键
alter table product add primary key (product_id);
插入行
begin transaction;
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2019-12-30');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2019-12-29');
INSERT INTO Product VALUES ('0003', '运动T恤','衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2019-12-28');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2019-12-27');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2019-12-26');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2019-12-25');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2019-12-24');
commit;
SELECT
查询列设置别名
select product_id as id, product_name as name, purchase_price as price from product;
select product_id as "商品编号", product_name as "商品名称", purchase_price as "进货单价" from product;
设置汉语要用双引号""
设置常数显示
select '商品' as string, 38 as number, '2019-02-24' as date, product_id, product_name from product;
显示结果每一行都会有 商品，38，2019-02-24这三个数据
从结果中删除重复行
select distinct product_type from product;
select distinct product_type, regist_date from product;
如果有NULL，会被当成一类来显示
where
select product_name, product_type from product where product_type='衣服';
select product_name from product where product_type = '衣服';
注释
-- 单行注释
/* 多行注释
*/
算数运算符
- +
- -
- *
- /
select product_name, sale_price, sale_price * 2 as "sale_price_x2" from product;
比较运算符
-- 等于
select product_name, product_type from product where sale_price = 500;
-- 不等于
select product_name, product_type from product where sale_price <> 500;
/*
还有
>=
>
<=
<
*/
联合使用
select product_name, sale_price, purchase_price from product where sale_price - purchase_price >= 500;
NULL不能比较，只能用 is null & is not null 来判断
select product_name, purchase_price from product where purchase_price is null;
逻辑运算符
not
select product_name, product_type, sale_price from product where not sale_price >= 1000;
and & or
select product_name, purchase_price from product where product_type='厨房用具' and sale_price >= 3000;
select product_name, purchase_price from product where product_type='厨房用具' or sale_price >= 3000;
select product_name, product_type, regist_date from product where product_type='办公用品' and (regist_date = '2019-12-29' or regist_date = '2019-12-24');
and运算符优先级高于or，所以要加上括号
聚合与排序
对表进行聚合查询
- COUNT: 计算表中的记录数（行数）
- SUM: 计算表中数值列中数据的合计值
- AVG: 计算表中数值列中数据的平均值
- MAX: 求出表中任意列中数据的最大值
- MIN: 求出表中任意列中数据的最小值
- DISTINCT: 删除重复值
-- 计算行数
select count(*) from product;
-- count(*)会得到包含NULL的数据行数，count(列名)会得到NULL之外的数据行数
-- 计算合计值
select sum(sale_price) from product;
-- 计算平均值
select avg(sale_price) from product;
-- avg自动把NULL排除，所以这时候数量是6
-- 最大值，最小值
select max(sale_price), min(purchase_price) from product;
-- min和max理论上适用于任何数据类型的列，字符串、日期等的比较也不会有问题，但是SUM/AVG不行
-- 计算删除重复数据后的数据行数
select count(distinct product_type) from product;
-- 所有的聚合函数都可以使用distinct
所有包含列名的聚合函数，都会把NULL排除在外
对表进行分组
先把表分成几组，在进行汇总处理
GROUP BY
-- 将数据按照product_type分类，并计算每个分类的数量
select product_type, count(*) from product group by product_type;
select purchase_price, count(*) from product where product_type='衣服' group by purchase_price;
- 如果有group by，那么select语句只能使用group by指定的列名
- group by 不能使用 select 中列的别名
- group by 字句结果的显示是无序的
- 只有 select, having, order by 字句中能够使用聚合函数
为聚合结果指定条件
- 使用COUNT函数等对表中数据进行汇总操作时，为其指定条件的不是where字句，而是HAVING字句
- 聚合函数可以在SELECT字句、HAVING字句和ORDER BY字句中使用
- HAVING字句要写在GROUP BY字句之后
- WHERE字句用来指定数据行的条件，HAVING字句用来指定分组的条件
-- 按照商品种类进行分组后的结果中，取出”包含的数据行数为2行的组“
select product_type, count(*) from product group by product_type having count(*) = 2;
-- 取出销售单价大于等于2500的分组数据
select product_type, avg(sale_price) from product group by product_type having avg(sale_price) >= 2500;
聚合键所对应的条件不应该卸载HAVING字句当中，而应该书写在WHERE字句当中
对查询结果进行排序
select product_id, product_name, sale_price, purchase_price from product order by sale_price;
select product_id, product_name, sale_price, purchase_price from product order by sale_price DESC;
-- order by 字句中可以使用列的别名
select product_id as id, product_name, sale_price as sp, purchase_price from product order by sp, id;
-- select字句中未包含的列也可以在order by字句中使用
select product_name, sale_price, purchase_price from product order by product_id;
-- 在order by字句中可以使用select字句中未使用的列和聚合函数
select product_type, count(*) from product group by product_type order by count(*);
未指定ORDER BY字句中排列顺序时会默认使用升序进行排列
排序键中包含NULL时，会在开头或末尾进行汇总
为什么order by可以使用别名，group by不可以呢？
这里要知道使用HAVING字句时SELECT语句的顺序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
SELECT字句的执行顺序是在GROUP BY字句之后，ORDER BY字句之前，因此在执行GROUP BY字句时，SELECT语句中定义的别名无法被识别
数据更新
数据的插入
-- 通常的insert
insert into ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) values ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
-- 多行insert
insert into ProductIns values ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'), ('0003', '运动T恤', '衣服', 4000, 2800, NULL), ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
-- 省略列清单
insert into ProductIns values ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
-- 插入NULL
insert into ProductIns (product_id, product_name, product_type, sale_price, purchase, regist_date) values ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
-- 插入默认值(显式方式)
insert into ProductIns (product_id, product_name, product_type, sale_price, purchase, regist_date) values ('0007', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');
-- 插入默认值(隐式方式)
insert into ProductIns (product_id, product_name, product_type, purchase, regist_date) values ('0007', '擦菜板', '厨房用具', 790, '2009-04-28');
-- 使用显式方式更容易理解
从其他表中复制数据
insert into ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date) select product_id, product_name, product_type, sale_price, purchase_price, regist_date from Product;
-- 多种多样的select
insert into ProductType (product_type, sum_sale_price, sum_purchase_price) select product_type, sum(sale_price), sum(purchase_price) from Product group by product_type;
数据的删除
drop table 删除整个表，delete 删除表中的数据
-- 清空表
delete from ProductType;
-- 根据条件删除
delete from Product where sale_price >= 4000;
delete只能使用where字句，group by、having、order by不能用，没有意义
truncate与delete相似，但是前者只能删除表中的全部数据，而不能通过where字句指定条件来删除部分数据，因为它只能删除全部，所以处理速度别delete更快
数据的更新
update
update Product set regist_date='2009-10-10';
-- 指定条件
update Product set sale_price = sale_price * 10 where product_type='厨房用具';
-- 使用NULL进行更新
update Product set regist_date = NULL where product_id='0008';
-- 多列更新
update Product set sale_price = sale_price * 10, purchase_price = purchase_price / 2 where product_type = '厨房用具';
事务
- 事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务，可以对数据库中的数据更新处理的提交和取消进行管理
- 事务处理的终止指令包括COMMIt（提交处理）和ROLLBACK（取消处理）两种
- DBMS的事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）四种特性，统称为ACID特性
事务是需要在同一个处理单元中执行的一系列更新处理的集合
start transaction;
update Product set sale_price = sale_price+1000 where product_id='0001';
commit;
-- rollback 回滚到开始事务之前的状态
当开启一个连接对数据库进行操作时，启动事务，修改，还没有提交，此时启动另一个连接，是看不到还没有提交的事务修改后的数据的。这时候如果执行 delete 操作，就会阻塞住，只有等之前的事务提交后才会继续执行delete
复杂查询
视图
视图并不保存数据，它保存的是select语句
create view ProductSum (product_type, cnt_product) as select product_type, count(*) from product group by product_type;
select product_type, cnt_product from ProductSum;
drop view ProductSum;
从视图中查找就是调用视图的select语句
视图支持嵌套，但不建议，影响性能
从表中更新数据，查询视图也会同步更新，因为本质是select。从视图更新数据，也会影响到表，但是有限制
- SELECT子句中未使用DISTINCT
- FROM字句中只有一张表
- 未使用GROUP BY子句
- 未使用HAVING子句
子查询
一张一次性视图
select product_type, cnt_product from (select product_type, count(*) as cnt_product from Product group by product_type) as ProductSum;
-- 就是将用来定义视图的select语句直接用于from字句当中，as ProductSum就是子查询的名称，但是是一次性的，执行完之后就消失
子查询作为内存查询会首先执行，且没有层数上的限制
标量子查询
只能返回表中某一行的某一列的值
-- 找出售价高于平均售价的产品
select product_id, product_name, sale_price from Product where sale_price > avg(sale_price);
-- 很明显上面是错误的写法，where字句不能有聚合函数，所以这里就要用到标量子查询
select product_id, product_name, sale_price from Product where sale_price > (select avg(sale_price) from Product);
标量子查询的书写位置不局限于where字句中，通常任何可以使用单一值的位置都可以使用
-- select子句使用标量子查询
select product_id, product_name, sale_price, (select avg(sale_price) from Product) as avg_price from Product;
-- having子句使用标量子查询
select product_type, avg(sale_price) from Product group by product_type having avg(sale_price) > (select avg(sale_price) from Product);
注意：标量子查询的结果一定不能返回多行
关联子查询
如果要找出产品售价高于每个商品种类的平均售价的产品，理论上
select product_id, product_name, sale_price from Product where sale_price > (select avg(sale_price) from Product group by product_type);
这里用了标量子查询，但是是错误的，标量子查询的返回结果必须是单一值，这里返回了多个值（每个商品种类的平均售价），比较是不成立的
用关联子查询来解决
select product_id, product_name, sale_price from Product as P1 where sale_price > (select avg(sale_price) from Product as P2 where P1.product_type = P2.product_type group by product_type);
关联子查询相比于标量子查询增加了一个判断，用来找出子查询返回多个结果的相匹配的那一个，这样就可以用来比较了，要注意关联名称的范围
网友评论