mysql orderitems_mysql

/*

* vendors表

*-----------------------------------------------------------------------------

* vendors表存储销售产品的供应商。每个供应商在这个表中有一个记录，供应商ID(vend_id)列用来匹配产品和供应商。

* vend_id 唯一的供应商

* vend_name 供应商名

* vend_address 供应商的地址

* vend_city 供应商的城市

* vend_state 供应商的州

* vend_zip 供应商的邮政编码

* vend_country 供应商的国家

* 这个表使用vend_id作为主键，vend_id为一个自动增量自段

*

* products 表

* -----------------------------------------------------------------------------------------

* products表包含产品目录，每一行一个产品。每个产品有唯一的ID(prod_id),通过vend_id(供应商的唯一的ID)关联到他的供应商

* prod_id 唯一的产品ID

* vend_id 产品供应商ID

* prod_name 产品名

* prod_price 产品价格

* prod_desc 产品描述

* 这个表用prod_id作为主键，为实施引用完整性，应该在vend_id上定义一个外键，关联到vendors的vend_id。

*

* customers

* ----------------------------------------------

* customers表存储所有顾客的信息。每个顾客有唯一的ID(cust_id)

* cust_id 唯一的顾客ID

* cust_name 顾客名

* cust_address 顾客的地址

* cust_city 顾客的城市

* cust_state 顾客的州

* cust_zip 顾客的邮政编码

* cust_country 顾客的国家

* cust_contact 顾客的联系名

* cust_email 顾客的联系email地址

* 这个表用cust_id作为的他的主键，cust_id是一个自动增量字段。

*

* order表

* ----------------------------------------------------------------------------------------

* order表存储顾客订单。每一个订单唯一的编号(order_num),订单用cust_id(关联到customer表的顾客唯一ID)与相应的顾客关联

* order_num 唯一订单号

* order_date 订单日期

* cust_id 订单顾客ID(关系到customers表的cust_id)

* 这个表使用order_num作为他的主键，order_num是一个自动增量字段。为实施引用完整性，应该在cust_id定义外键，关联到customers的cust_id

*

* orderitems表

* ----------------------------------------------------------------------------------------------

* orderitems表存储每个订单中的实际物品，每个订单的每个物品占一行。对orders中的每一行，orderitems中有一行或多行。每个订单物品由订单号

* 加订单物品唯一标识。订单物品通过order_num(关联到ordrs中的订单唯一ID)与他们相应的订单相关联。此外，每个订单项包含订单物品的产品ID(他

* 关联物品到products表)。

* order_num 订单号(关联到orders表的order_num)

* order_item 订单物品号

* prod_id 产品ID(关联到products表的prod_id)

* quantity 物品数量

* item_price 物品价格

* 这个表使用order_num和order_item作为主键，为实施引用完整性，应该在order_num上定义外键，prod_id定义外键。

*

* productnotes表

* ------------------------------------------------------------------------------------------------

* 存储与特定产品有关的注释。并非所有的产品都有相关的注释，而有的产品可能有许多相关的注释。

* note_id 唯一注释ID

* prod_id 产品ID

* note_date 增加注释的日期

* note_text 注释文本

* 这个表使用note_id作为主键。note_text使用FULLTEXT搜索进行索引，ENGINE=MyISAM。

*

* 1.SHOW DATABASES;

* 2.SHOW TABLES;

* 3.SHOW COLUMNS FROW customers;

* 4.DESCRIBE customers;

* 5.SELECT pro_name FROM products;

* 6.SELECT pro_id,prod_price FROM products;

* 7.SELECT * FROM products;

* 8.SELECT DISTINCT vend_id FROM products;

* 9.SELECT pro_name FROM products LIMIT 5;

* 10.SELECT por_naem FROM products LIMIT 5,5;

* 11.SELECT products.prod_name FROM products;

* 12.SELECT products.prod_name FROM crashcourse.products;

* 13.SELECT pord_name FROM products ORDER BY prod_name;

* 14.SELECT prod_name FROM products ORDER BY prod_price,prod_name;

* 15.SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC;

* 16.SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name;

* 17.SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;

* 18.SELECT prod_name,prod_price FROM products WHERE prod_price = 2.50;

* 19.SELECT vend_id,prod_name FROM products WHERE vend_id <>1003;

* 20.SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 ADN 10;

* 21.SELECT prod_name FROM products WHERE prod_price IS NULL;

* 22.SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id=1003 AND prod_price<=10;

* 23.SELECT prod_name,prod_price FROM products WHERE vend_id=1002 OR vend_id=1003;

* 24.SELECT prod_name,prod_price FROM products WHERE (vend_id=1002 OR vend_id=1003) AND prod_price>=10;

* 25.SELECT prod_name,prod_price FROM products WHERE vend_id IN(1002,1003) ORDER BY prod_name;

* 26.SELeCT prod_name,prod_price FROM products WHERE vend_id NOT IN(1002,1003) ORDER BY prod_name;

* 27.SELECT prod_id,prod_name FROM prodcucsts WHERE prod_name LIKE 'jet%';

* 28.select prod_id,prod_name from prodcuts where prod_nama like '_ton anvil';

* 29.select prod_name from products where prod_name regexp '1000' order by prod_name;

* !30.select prod_name from products where prod_name regexp '.000' order by prod_name;

* !31.select prod_name from products where prod_name regexp binary '.000' order by prod_name;

* !32.select prod_name form products wheer prod_naem regexp '1000|2000' order by prod_name;

* !33.select prod_name form prodcuts where prod_name regexp '[123] Ton' ORDER BY prod_name;

* !34.select prod_name from products where prod_name regexp '[1-5] Ton' ORDER BY prod_name;

* !35.select Prod_name from producst where prod_name regexp '\\.' ORDER BY vend_name;

* !36.select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)' ORDER BY prod_name;

* !37.select prod_name from products where prod_name regexp '^[0-9\\.]' ORDER BY prod_name;

* !38.select concat(vend_name,'(',vend_country,')') from vendors ORDER BY vend_name;

* !39.select concat(rtrim(vend_name),'(',rtrim(vend_country),')') from vendors order by vend_name;

* !40.select concat(rtrim(vend_name),'(',rtrim(vend_country),')') as vend_title from vendors order by vend_name;

* !41.select prod_id,quantity,quantity*item_price as expanded_price from orderitems where order_num=2005;

* !42.select vend_name,upper(vend_name) as vend_name_upcase from vendors;

* !43.select cust_name,cust_contact from customers where soundex(cust_contact) = soundex('Y LIe');

* !44.select cust_id,order_num form orders where date(order_date) = '2005-09-01';

* !45.select avg(prod_price) as avg_price form products;

* !46.select count(*) as num_cust from customers;

* !47.select count(*) as num_items from products;

* !48.select cust_id,count(*) as orders from orders group by cust_id having count(*)>=2;

* !49.select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price)>=50 order by ordertotal;

* !50.select cust_id from orders where order_num IN (select order_num from orderitems where prod_id='TNT2');

* !51.select cust_name,cust_contact from customers where cust_id in(select cust_id from order where order_num in(select order_num from orderitems where prod_id='TNT2'));

* !52.select cust_name,cust_state,(select count(*) from orders where orders.cust_id=customers.cust_id) as orders from customers;

* order by cust_name;

* !53.select vend_name,prod_name,prod_price form vendors,products where vendors.vend_id=products.vend_id order by vend_name,prod_name;

* !54.select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_id;

* !55.select cust_name,cust_contact from customers as c,ordres as o,ordritesm as oi where c.cust_id=o.cust_id and oi.order_num

* =o.ordr_num and prod_id='TNT2';

* !56.select customers.cust_id,orders.ordr_num from customers inner join orders oncsutomers.cust_id=ordrs.cust_id;

* !57.select customers.csut_id,ordrs.ordr_num form customers right outer join orders on ordrs.cust_id=customrs.cust_id;

* !58.create table productnotes(not_id int not null auto_increment,

* prod_id char(10) not null,

* note_date datetime not null,

* note_text text null,

* primary key(note_id),

* fulltext(note_text)

* )engine=myisam;

* !59.select note_text from productnotes where match(note_text) against('rabbit');

* !60.select note_text,match(note_text) against('rabbit') as rank from productnotes;

*

*

* 61.insert into customers values(null,'pep e.lapw','100 main street','los angels','cs',null);

* 62.update customers set cust_email='elemr@fudd.com' where cust_id=10005;

* 63.update customers set cust_name='the fudds',cust_email='elem@fuddcom' where cust_id=100005;

* 64.delete from customers where cust_id=100006;

* 65.create table custtomers(cust_id int not null auto_increment,

* cust_name char(50) not null,

* cust_address char(50) null,

* cust_city char(50) null,

* cust_state char(5) null,

* cust_zip char(10) char(50) null,

* primary key(cust_id)

* )engine=innodb;

* 66.alter table vendors add vend_phone char(20);

* 67.alter table vendors drop column vend_phone;

* 68.drop table customers3;

*

*

*

*

*

*

*

*

*

*

*

*/

?>