SQL 基础教程 SQL语句集

环境 PostgreSQL 9.6.4
终端字符集编码 'gbk'

chapter01 数据库和 SQL


sudo chown -R postgres PostgreSQL

sudo -u postgres psql -U postgres
create database shop;
\encoding 'gbk'

\q

sudo -u psql -U postgres -d shop

psql -U user -d dbname

\c dbname
\l
\dt
\d tblname
\di

sql 中

等于号是 =, 而不是 ==;
单行注释为 --, 多行注释 /**/;
语句结束符为 ,


create table Product (
  product_id char(4)  not null,
  product_name varchar(20) not null,
  product_type varchar(20) not null,
  sale_price integer,
  purchase_price integer,
  register_date date,
  primary key(product_id)
);

drop table Product;

alter table Product add column sales integer;
alter table Product drop column sales;

show client_encoding;
set client_encoding to 'gbk'
set client_encoding to 'utf8'

begin transaction;

INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

commit;

sudo -u postgres pg_ctl -D /usr/local/PostgreSQL/9.6/data/ -w start
sudo -u postgres pg_ctl -D /usr/local/PostgreSQL/9.6/data/ -w restart
sudo -u postgres pg_ctl -D /usr/local/PostgreSQL/9.6/data/ -w stop

exercise


create table Addressbook (
  regist_no integer primary key,
  name varchar(128) not null,
  address varchar(256) not null,
  tel_no char(10),
  mail_address char(20)
);

alter table Addressbook add column postal_code char(8) not null;
drop table Addressbook;

chapter02 查询基础

2.1

select product_id, product_name, product_type from Product;

-- 查询结果中列的顺序和 select 子句中的顺序相同
select product_type, product_id, product_name from Product;

select * from Product;

select product_id as id, product_name as name, product_type as type from Product;

-- 别名可以使用中文,  使用中文需要双引号括起来
select product_id as "商品编号", product_name as "商品名称", product_type as "商品类型" from Product;

-- select 子句不仅能书写列名, 还可以书写常数
select '商品' as String, 66 as number, '2017-08-20' as date, product_id from Product;

-- distinct 关键字只能用在第一个关键字前, 将 select 子句的所有字段联合起来判断相等性
select distinct product_type, register_date from product

-- 首先通过 where 子句查询出符合条件的记录, 然后使用 select 子句指定列
select product_name, product_type from product where product_type = '衣服';
-- 查询条件可以不显示出来
select product_name from product where product_type = '衣服';

2.2

-- 注意要起个别名, 因为之前的字段名字不可用
select sale_price * 2  as double_price from product;

-- 所有包含 null 的计算, 结果肯定是 null
select sale_price * null  as multi_null from product;

-- <> 不等号
select product_name, sale_price from product where sale_price <> 500;

select product_name, sale_price, purchase_price from Product where sale_price - purchase_price >= 500;

-- 不能对 null 使用比较运算符, 不管怎么用, 查询结果都为空; SQL 专门用来判断是否为null的是 is null 运算符
select product_name, purchase_price from product where purchase_price is null;
select product_name, purchase_price from product where purchase_price is not null;

2.3

逻辑运算符 not, and, or

and 运算符的优先级高于 or 运算符
善用括号()

select product_name, purchase_price, sale_price from product where sale_price >= 500;
select product_name, purchase_price, sale_price from product where not sale_price >= 500;

select product_name, product_type, register_date from Product
where product_type = '办公用品' and register_date = '2009-09-11' or register_date = '2009-09-20';

select product_name, product_type, register_date from Product
where product_type = '办公用品' and (register_date = '2009-09-11' or register_date = '2009-09-20');

SQL 中逻辑运算时三值逻辑, 真值除了真假之外还有 unknown, 关于 unknown 运算如下

P	Q	P AND Q
真	unknown	unknown
假	unknown	假
unknown	unknown	unknown

P	Q	P OR Q
真	unknown	真
假	unknown	unknown
unknown	unknown	unknown

可以看出了 unknown 是一种介于真假之间的逻辑状态

exercise

select product_name, register_date from product where register_date >'2009-04-28';

-- SELECT语句①
SELECT product_name, sale_price, purchase_price
  FROM Product
 WHERE sale_price >= purchase_price + 500;


-- SELECT语句②
SELECT product_name, sale_price, purchase_price
  FROM Product
 WHERE sale_price - 500 >= purchase_price;

select product_name, product_type, (sale_price * 0.9 - purchase_price) as profit from product
where sale_price * 0.9 - purchase_price  > 100 and (product_type = '办公用品' or product_type = '厨房用具');

chapter03 聚合和排序

3.1

聚合函数中使用 distinct, 删除重复数据

-- count(*) 会得到包含 null 的数据行数, count(<列名>) 会得到 null 之外的数据行数
select count(*) from Product;
select count(*) from Product where product_type = '衣服';
select count(distinct product_type) from Product;


select count(purchase_price) from Product;

select sum(sale_price), sum(purchase_price) from Product;
select sum(sale_price), sum(distinct sale_price) from Product;

select avg(sale_price), avg(purchase_price) from Product;

select max(register_date), min(register_date) from product;

3.2

group by 子句的书写位置

select -> from -> where -> group by

上述语句的执行顺序

from -> where -> group by -> select

select product_type, count(product_type) from Product group by product_type;

-- group by 可以包含 null 的分组
select purchase_price, count(*) from Product group by purchase_price;

-- 有 where 子句, 先根据 where 子句指定的条件进行过滤, 然后再进行汇总处理
select purchase_price, count(*) from Product where product_type = '衣服' group by purchase_price;

3.3

having 子句必须写在 group by 子句之后, 执行顺序也在 group by 之后

书写规范

where 子句 = 指定行所对应的条件
having 子句 = 指定组所对应的条件


select product_type, count(*) from Product group by product_type having count(*) = 2;

3.4

order by 子句书写顺序在 having 之后, 排序时, 默认为升序

ascendent adj 递增的
descendent adj 递减的

DBMS 中, 语句执行顺序

from -> where -> group by -> having -> select -> order by


select * from Product order by sale_price;
select * from Product order by sale_price desc, product_id;

-- 排序键包含 null, 会在开头或者末尾汇总

chapter04 数据更新

4.1


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 ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');

-- 多条记录插入, 记录间用逗号隔开, 语句结尾用分号结束
insert into ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) values
  ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
  ('0003', '运动T恤', '衣服', 4000, 2800, NULL),
  ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20'),
  ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15'),
  ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20'),
  ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');

-- 对表进行全列插入时, 可以省略列清单
insert into ProductIns values ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

-- default 使用默认值
insert into ProductIns values ('0009', '圆珠笔', '办公用品', default, NULL, '2009-11-11');


-- 从其他表复制数据 insert ... select ...
create table ProductCopy (
  product_id char(4)  not null,
  product_name varchar(20) not null,
  product_type varchar(20) not null,
  sale_price integer,
  purchase_price integer,
  register_date date,
  primary key(product_id)
);

insert into ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, register_date)
select * from product;

create table productType (
  product_type varchar(32) not null,
  sum_sale_price integer,
  sum_purchase_price integer,
  primary key (product_type)
);

/*
insert ... select 中的 select 子句可以前面学到的是任意select 查询语句
同样对表进行全列插入时, 可以省略 列清单
*/
insert into productType
select product_type, sum(sale_price), sum(purchase_price) from product group by product_type;

select * from producttype;

4.2

delete 语句删除对象不是表或者列, 而是记录.


delete from ProductCopy;
insert into ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, register_date)

delete from ProductCopy where sale_price >= 4000;
truncate ProductType;

-- 删除列
alter table ProductCopy drop column register_date;
alter table ProductCopy add column register_date date;

update ProductCopy set register_date = '2017-08-19';
update ProductCopy set sale_price = sale_price * 10 where product_type = '厨房用具';
update ProductCopy set register_date = null where product_id = '0008';

-- 多列更新
update Productcopy set sale_price = sale_price / 10, purchase_price = purchase_price + 99 where product_type = '厨房用具';

4-4

事务

ACID: 原子性, 一致性, 隔离性, 持久性


begin transaction;
update ProductCopy set register_date = '2016-08-19';
commit;

begin transaction;
update ProductCopy set register_date = '3016-08-19';
rollback;

exercise


create table ProductMargin (
  product_id char(4) not null,
  product_name varchar(100) not null,
  sale_price integer,
  purchase_price integer,
  margin integer,
  primary key (product_id)
);

insert into ProductMargin (product_id, product_name, sale_price, purchase_price, margin)
  select product_id, product_name, sale_price, purchase_price, sale_price - purchase_price from ProductCopy;

update ProductMargin set sale_price = sale_price + 200 where product_name = '擦菜板';
update ProductMargin set margin = sale_price - purchase_price where product_name = '擦菜板';

chapter05 复杂查询

5.1 视图

create view ProductSum (product_type, count_product)
  AS select product_type, count(product_type) from product group by product_type;

-- 多重视图
create view ProductSumJim (product_type, count_product)
 AS
 select * from ProductSum where product_type = '办公用品';

-- cascade 串联
drop view ProductSum cascade;

5.2 子查询

create view ProductSum (product_type, count_product)
  AS select product_type, count(*) from product group by product_type;

select product_type, count_product from ProductSum;

-- 等价于
select product_type, count_product from (select product_type, count(*) as count_product from product group by product_type) as ProductSum1;

-- 标量子查询
select product_name, product_type, sale_price from product where sale_price > (select avg(sale_price) from Product);

select product_name, product_type, sale_price, (select avg(sale_price) from Product) from product;

-- 关联子查询 (group by product_type) 可省略
select product_name, product_type, 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);

exercise


create view ViewExer5_1 (product_name, sale_price, register_date)
  AS select product_name, sale_price, register_date from Product where sale_price >= 1000 and register_date = '2009-09-20';
select * from ViewExer5_1;

insert into ViewExer5_1 values ('刀子', 300, '2009-11-02');
/*
ERROR:  null value in column "product_id" violates not-null constraint
DETAIL:  Failing row contains (null, 刀子, null, 300, null, 2009-11-02).
*/

select product_id, product_name, product_type, sale_price, (select avg(sale_price) from product) as sale_price_all from Product;

select product_id, product_name, product_type, sale_price, (
  select avg(sale_price) from product as p2 where p1.product_type = p2.product_type
) as sale_price_all
from Product as p1;

chapter06 函数谓词 case 表达式

5.1 函数

select current_date, current_time, current_timestamp;

select current_timestamp,
extract(year from current_timestamp) as year,
extract(month from current_timestamp) as month,
extract(day from current_timestamp) as day,
extract(hour from current_timestamp) as hour,
extract(minute from current_timestamp) as minute,
extract(second from current_timestamp) as second;

5.2谓词

CREATE TABLE ShopProduct
(shop_id    CHAR(4)       NOT NULL,
 shop_name  VARCHAR(200)  NOT NULL,
 product_id CHAR(4)       NOT NULL,
 quantity   INTEGER       NOT NULL,
 PRIMARY KEY (shop_id, product_id));

 --MySQL
 START TRANSACTION;

 INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0001',	30);
 INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0002',	50);
 INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0003',	15);
 INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0002',	30);
 INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0003',	120);
 INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0004',	20);
 INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0006',	10);
 INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0007',	40);
 INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0003',	20);
 INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0004',	50);
 INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0006',	90);
 INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0007',	70);
 INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D',	'福冈',		'0001',	100);

COMMIT;

select * from shopproduct;  

select  product_name, sale_price from product as p
where exists (select * from shopproduct as sp where shop_id = '000C' and p.product_id =   sp.product_id);

5.3 case 表达式


-- 使用简单CASE表达式的情况
SELECT product_name,
       CASE product_type
            WHEN '衣服'    THEN 'A：' || product_type
            WHEN '办公用品' THEN 'B：' || product_type
            WHEN '厨房用具' THEN 'C：' || product_type
            ELSE NULL
        END AS abc_product_type
  FROM Product;



--List6-A
--使用查询CASE表达式的情况（代码清单6-41再刊）
SELECT product_name,
       CASE WHEN product_type = '衣服'    THEN 'A：' || product_type
            WHEN product_type = '办公用品' THEN 'B：' || product_type
            WHEN product_type = '厨房用具' THEN 'C：' || product_type
            ELSE NULL
       END AS abc_product_type
  FROM Product;

posted @ 2017-08-19 16:19 nowgood 阅读(373) 评论(0) 收藏举报

刷新页面返回顶部

nowgood

梅花香自苦寒来

SQL 基础教程 SQL语句集

chapter01 数据库和 SQL

exercise

chapter02 查询基础

exercise

chapter03 聚合和排序

chapter04 数据更新

exercise

chapter05 复杂查询

exercise

chapter06 函数谓词 case 表达式

公告

nowgood

梅花香自苦寒来

SQL 基础教程 SQL语句集

chapter01 数据库和 SQL

exercise

chapter02 查询基础

exercise

chapter03 聚合和排序

chapter04 数据更新

exercise

chapter05 复杂查询

exercise

chapter06 函数 谓词 case 表达式

公告

chapter06 函数谓词 case 表达式