SQL基础教程（1）

作者: leejnull | 来源:发表于2020-01-08 22:35 被阅读0次

以下基于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);

关联子查询相比于标量子查询增加了一个判断，用来找出子查询返回多个结果的相匹配的那一个，这样就可以用来比较了，要注意关联名称的范围

来自 https://leejnull.github.io/2020/01/08/2020-01-08-01/