select prod_id,prod_price,prod_name from products order by  prod_price desc,prod_name；  \\降序排序prod_price 如果有重复再按prod_name排序
select prod_name,prod_price from products where prod_price between 5 and 10;        \\查询5-10之间的值
select prod_id,prod_price,prod_name from products where vend_id = 'DLL01' and prod_price <= 4;     \\多个条件使用and
select prod_id,prod_price,prod_name from products where vend_id = 'DLL01' or vend_id = 'BRS01';     \\or 匹配任意一条都将被打印出来
select prod_id,prod_price,prod_name from products where (vend_id = 'DLL01' or vend_id = 'BRS01') and prod_price >= 10;   \\or和and结合满足条件查询
select vend_id,prod_name,prod_price from products where vend_id in ('DLL01','BRS01') order by prod_name;     \\in操作符,只匹配符合DLL01 和 BRS01
select prod_name from products where not vend_id = 'DLL01' order by prod_name;           \\not   匹配不为DLL01的条件
select prod_id,prod_name from products where prod_name like 'Fish%';     \\百分号%通配符,匹配所有以Fish开头的条件
select prod_id,prod_name from products where prod_name like  '_ inch teddy bear%';    \\下划线_只匹配一个字符,匹配几个字符加几个下划线_
select vend_name || '(' || vend_country ||')' from vendors order by vend_name;      \\  "||"字段拼接,将vend_name段与vend_country字段拼接,
注:mysql中的"||"等同于or ,mysql使用concat函数拼接
select rtrim(vend_name) || '(' || rtrim(vend_country) ||')' from vendors order by vend_name;    \\使用rtrim函数将空格去掉
select vend_name || '('|| vend_country ||')' as vend_title from vendors order by vend_name;     \\as为子句取别名
select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems where order_num = 20008;   \\计算列quantity*item_price
select order_num from orders where DATE_PART('year', order_date) = 2004;        \\使用DATE_PART时间函数查询
select avg(prod_price) as avg_price from products;           \\使用AVG()函数返回products表中的平均值
select count(*) as num_cust from customers;                  \\ 使用count()函数返回表中所有行数
select max(prod_price) as max_price from products;           \\使用max()函数返回prod_price列最大的值
select min(prod_price) as max_price from products;           \\使用max()函数返回prod_price列最小的值
select sum(quantity*item_price) as itemsz_ordered from orderitems  where order_num = 20005;       \\使用sum()函数计算两列总和并返回两列相乘结果的总和
select avg(distinct prod_price) as avg_price from products where vend_id = 'DLL01';          \\DISTINCT  排除相同的值
select count(*) as num_items,min(prod_price) as price_min,max(prod_price) as price_max,avg(prod_price) as price_avg from products;    \\使用多个聚合函数
select vend_id,count(*) as num_prods from products  group by vend_id;              \\使用group by 对vend_id  进行分组
select cust_id,count(*) as orders from orders group by cust_id having count(*) >=2;    \\使用having关键字对分组进行过滤
select cust_id,cust_name,cust_contact from customers where cust_id in ( select cust_id from orders where order_num in ( select order_num from orderitems where prod_id = 'RGAN01') );   \\子查询,不解释
select cust_name,cust_state,(select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name;            \\ orders.cust_id  注:orders表的cust_id字段
select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_id;      \\联接查询,联接vendors和products表用where关联
select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;    \\联接查询,等同于逗号,联接
select prod_name,vend_name,prod_price,quantity from orderitems,products,vendors where products.vend_id = vendors.vend_id and orderitems.prod_id = products.prod_id and order_num = 20007;    \\多表联接
select cust_name,cust_contact from customers as c,orders as o,orderitems as oi where c.cust_id = o.cust_id and oi.order_num = o.order_num and prod_id = 'RGAN01';   使用as给表取别名
select c1.cust_id,c1.cust_name,c1.cust_contact from customers as c1,customers as c2 where c1.cust_name = c2.cust_name and c2.cust_contact = 'Jim Jones';    \\表的自联接,c1出现第一次,c2出现的第二次
select customers.cust_id,orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id;    \\外部联接
select customers.cust_id,count(orders.order_num) as num_ord from customers inner join orders on customers.cust_id = orders.cust_id group by customers.cust_id;     \\使用聚合函数联接
select cust_name,cust_contact,cust_email from customers where cust_state in ('IL','IN','MI') union select cust_name,cust_contact,cust_email from customers where cust_name = 'Fun4All';    \\使用union将两条sql语句组合查询,如果使用union  all 将返回所有行
insert into customers(a,b,c) values('a值','b值','c值');           \\插入数据
insert into customers(a,b,c) select a,b,c from rest-table;        \\插入从另一张表读入的数据,注:类型要相同
select * into custcopy from customers;              \\select into 将一张表插入到一张新表并创建新表,相当于copy
create table custcopy as select * from customers;      \\mysql 复制表
update 表明 set 字段=新值 where                       \\update 操作
delete from 表明 where                                 \\ delete 操作
create view productcustomers as  select cust_name,cust_contact,prod_id from customers,orders,orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num;  \\创建视图
select random()      select (random()*(2*100))::integer          \\随机函数