SQL学习（一）

一  建表：

CREATE TABLE tablename(colname datatype [NOT NULL]

                                     {,colname datatype [NOT NULL]...}

                                     [,PRIMARY KEY (colname {,colname...})];

注： ｜,[] ,{}, ...在sql里不会出现，所以用其标记

二    简单的select语句：

select distinct ordno,x.cid,x.aid,x.pid,

                     .40*(x.qty*p.price)-.01*（c.dicnt+a.ercent）*（x.qty*p.price）as heji

                      from orders as x,customers as c,agents as a,products as p

                       where c.cid=x.cid and a.aid= x.aid and p.pid=x.aid;

x,c,a,p分别为orders,customers,agents,products的别名，关键字as可省略

ordno,cid, aid, pid ,heji为列名

heji 前面的as不可省略，他定义.40*(x.qty*p.price)-.01*（c.dicnt+a.ercent）*（x.qty*p.price）显示为heji

distinct确保检索后的每一行是唯一的

找出至少被两个顾客订购的产品的pid值

select distinct x1.pid

           from orders x1,orders x2

           where x1.pid=x2.pid and x1.pid<x2.pid;,