MySQ的L数据查询.docVIP

.0.Mysql查询数据 本章所用到的数据表 /*顾客表*/ CREATE TABLE customers ( c_id int NOT NULL AUTO_INCREMENT, c_name char(50) NOT NULL, c_address char(50) NULL, c_city char(50) NULL, c_zip char(10) NULL, c_contact char(50) NULL, c_email char(255) NULL, PRIMARY KEY (c_id) ); /*水果表*/ /*供货方表*/CREATE TABLE fruits ( f_id char(10) NOT NULL, s_id INT NOT NULL, f_name char(255) NOT NULL, f_price decimal(8,2) NOT NULL, PRIMARY KEY(f_id) ) ; CREATE TABLE suppliers ( s_id int NOT NULL AUTO_INCREMENT, s_name char(50) NOT NULL, s_city char(50) NULL, s_zip char(10) NULL, s_call CHAR(50) NOT NULL, PRIMARY KEY (s_id) ) ; /*订单明细表CREATE TABLE orderitems ( o_num int NOT NULL, o_item int NOT NULL, f_id char(10) NOT NULL, quantity int NOT NULL, item_price decimal(8,2) NOT NULL, PRIMARY KEY (o_num,o_item) ) ; /*订单表*/ CREATE TABLE orders ( o_num int NOT NULL AUTO_INCREMENT, o_date datetime NOT NULL, c_id int NOT NULL, PRIMARY KEY (o_num) ) ; /*--------------------------插入数据--------------------------*/ INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip, c_contact, c_email) VALUES(10001, RedHook, 200 Street , Tianjin, 300000, LiMing, LMing@163.com), (10002, Stars, 333 Fromage Lane, Dalian, 116000, Zhangbo,Jerry@), (10003, Netbhood, 1 Sunny Place, Qingdao, 266000, LuoCong, NULL), (10004, JOTO, 829 Riverside Drive, Haikou, 570000, YangShan, sam@); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES(a1, 101,apple,5.2), (b1,101,blackberry, 10.2), (bs1,102,orange, 11.2), (bs2,105,melon,8.2), (t1,102,banana, 10.3), (t2,102,grape, 5.3), (o2,103,coconut, 9.2), (c0,101,cherry, 3.2), (a2,103, apricot,2.2), (l2,104,lemon, 6.4), (b2,104,berry, 7.6), (m1,106,mango, 15.6), (m2,105,xbabay, 2.6), (t4,107,xbababa, 3.6), (m3,105,xxtt, 11.6), (b5,107,xxxx, 3.6); INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call)