SQL实验操作文章指南

连接数据库

#后台数据库由 MogDB 3.0.0 赞助
su - omm
#第一次进入数据库启动中...等待15秒
gsql -d postgres
> \l
复制

内置Oracle/MySQL兼容包：

select * from v$version;
select * from v$tablespace;

\d oracle.dba_objects
select count(*) from dba_tables;
select * from dba_users;
select count(*) from dba_sequences;

select sysdate from dual;
select count(*) from tab;

select dbms_metadata.get_ddl('table','employees','public');

select sys_guid();

--更多请参考 https://gitee.com/enmotech/compat-tools
复制

内置Mogila样本数据集

\c mogila
\dt

--按长度排序列出前十电影
select
    film_id,
    title,
    length
from
    film
order by
    length desc limit 10;

--列出每个电影类别中有多少部电影
select
    category.name,
    count(category.name) category_count
from
    category
left join film_category on
    category.category_id = film_category.category_id
left join film on
    film_category.film_id = film.film_id
group by
    category.name
order by
    category_count desc;

--显示按出演电影的数量排序的演员
select
    actor.first_name,
    actor.last_name,
    count(actor.first_name) featured_count
from
    actor
left join film_actor on
    actor.actor_id = film_actor.actor_id
group by
    actor.first_name,
    actor.last_name
order by
    featured_count desc;

--获取所有活跃客户的列表，按其姓名排序
select
    first_name,
    last_name
from
    customer
where
    active = 1
order by first_name asc;

--查看租DVD数量最多的客户，以及租借次数
select
    customer.first_name,
    customer.last_name,
    count(customer.first_name) rentals_count
from
    customer
left join rental on
    customer.customer_id = rental.customer_id
group by
    customer.first_name,
    customer.last_name
order by rentals_count desc;

--查看每个出租店的总收入
select
    store.store_id,
    sum(payment.amount) as "total revenue"
from
    store
left join inventory on
    inventory.store_id = store.store_id
left join rental on
    rental.inventory_id = inventory.inventory_id
left join payment on
    payment.rental_id = rental.rental_id
where
    payment.amount is not null
group by
    store.store_id
order by
    sum(payment.amount) desc;

--按总收入列出前5个电影类型
select
    category.name,
    film.title,
    sum(payment.amount) as "gross revenue"
from
    film
left join film_category on
    film_category.film_id = film.film_id
left join category on
    film_category.category_id = category.category_id
left join inventory on
    inventory.film_id = film.film_id
left join rental on
    rental.inventory_id = inventory.inventory_id
left join payment
    on payment.rental_id = rental.rental_id
where
    payment.amount is not null
group by
    category.name,
    film.title
order by
    sum(payment.amount) desc
limit 5;

复制

初始化HR对象和数据

cd /var/lib/opengauss
gsql -d postgres -f HR_Sample.sql
gsql -d postgres
> \d
复制

查询HR示例

--demo1
SELECT first_name "First", last_name "Last", 
SALARY "Salary", COMMISSION_PCT "%"
FROM employees
WHERE salary >=11000 AND commission_pct IS NOT NULL;

--demo2
SELECT first_name "First", last_name "Last", salary * 12 "Annual Compensation"
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY hire_date;

--demo3
SELECT e.first_name || ' ' || e.last_name "Name",
  l.city || ', ' || c.country_name "Location"
FROM employees e, departments d, locations l, countries c
WHERE e.department_id=d.department_id AND
  d.location_id=l.location_id AND
  l.country_id=c.country_id
ORDER BY last_name;

--nvl2 demo
SELECT first_name || ' ' || last_name "Name",
  NVL((commission_pct * 100), 0) "Comm Rate",
  NVL2(commission_pct, 
    ROUND(salary * 12 + commission_pct * 300000, 2),
    salary * 12) "With $300K Sales"
FROM employees
WHERE job_id LIKE '%_M%' AND department_id = 80;
复制