postgreSQL高阶功能_02

概述-- 测试表 CREATE TABLE employees ( employee_id serial PRIMARY KEY, employee_name VARCHAR (255) NOT NULL ); CREATE TABLE keys ( employee_id INT PRIMARY KEY, effective_date DATE NOT NULL, FOREIGN KEY (empl

-- 测试表
CREATE table employees (
employee_ID serial PRIMARY KEY,
employee_name VARCHAR (255) NOT NulL
);

CREATE table keys (
employee_ID INT PRIMARY KEY,
effective_date DATE NOT NulL,
FOREIGN KEY (employee_ID) REFERENCES employees (employee_ID)
);

CREATE table hipos (
employee_ID INT PRIMARY KEY,
FOREIGN KEY (employee_ID) REFERENCES employees (employee_ID)
);

-- 测试数据
INSERT INTO employees (employee_name)
VALUES
(‘Joyce EDWards‘),
(‘Diane Collins‘),
(‘Alice Stewart‘),
(‘JulIE Sanchez‘),
(‘Heather Morris‘),
(‘Teresa Rogers‘),
(‘Doris Reed‘),
(‘Gloria Cook‘),
(‘Evelyn Morgan‘),
(‘Jean Bell‘);

INSERT INTO keys
VALUES
(1,‘2000-02-01‘),
(2,‘2001-06-01‘),
(5,‘2002-01-01‘),
(7,‘2005-06-01‘);

INSERT INTO hipos
VALUES
(9,‘2000-01-01‘),‘2002-06-01‘),‘2006-06-01‘),
(10,‘2005-06-01‘);

select employee_ID from keys

select employee_ID from hipos

select employee_ID from keys
-- 返回交集，同时存在两个表查询出来的数据
intersect
select employee_ID from hipos
order by employee_ID desc

select employee_ID from keys
-- 返回并集
union
select employee_ID from hipos

select employee_ID from keys
-- 返回差集，
except
select employee_ID from hipos

---------------- ---------------------
CREATE table sales (
brand VARCHAR NOT NulL,
segment VARCHAR NOT NulL,
quantity INT NOT NulL,
PRIMARY KEY (brand,segment)
);

INSERT INTO sales (brand,segment,quantity)
VALUES
(‘ABC‘,‘Premium‘,100),
(‘ABC‘,‘Basic‘,200),
(‘XYZ‘,300);

select * from sales；
select brand,sum(quantity) from sales group by brand;
select segment,sum(quantity) from sales group by segment;
select sum(quantity) from sales;

-- 分组集 grouPing sets
-- 这个有点不实用，但留个印象，存在即合理
select brand,sum(quantity) from sales
group by grouPing sets ((brand,segment),(brand),(segment),());

select grouPing(brand) as grouPing_brand,grouPing(segment) as grouPing_segment,brand,()) order by brand,segment;

-- cube 在 grouPing sets 的基础上进行的简化 *** 作，会生成所有可能的分组集
select brand,sum(quantity) from sales
group by cube (brand,segment);

select grouPing(brand) as grouPing_brand,segment) order by brand,segment;

-- rollup 在其上进行快捷的 *** 作
select brand,sum(quantity) from sales
group by rollup (brand,segment);
-- 下面SQL查询的分组集有
-- （segment,brand）
-- （segment）
-- （）
select brand,sum(quantity) from sales
group by rollup (segment,brand);

-- EXTRACT() 函数用于返回日期/时间的单独部分select * from rental;select extract(year from rental_date) as y,count(rental_ID) from rental group by rollup(extract(year from rental_date)) ___________________________________________________-- 子查询 select AVG(rental_rate) from film;select film_ID,Title,rental_rate from film where rental_rate > 2.98select film_ID,rental_rate from film where rental_rate > (select avg(rental_rate) from film);

总结

以上是内存溢出为你收集整理的postgreSQL高阶功能_02全部内容，希望文章能够帮你解决postgreSQL高阶功能_02所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。