本博客主要整理《MysqL必知必会》中常用的SQL语句。
建表语句本博客中的sql全都依赖于下面表结构。
######################################### MysqL Crash Course# http://www.forta.com/books/0672327120/# Example table creation scripts################################################################# Create customers table########################CREATE table customers( 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) NulL,cust_country char(50) NulL,cust_contact char(50) NulL,cust_email char(255) NulL,PRIMARY KEY (cust_ID)) ENGINE=InnoDB;########################## Create orderitems table#########################CREATE table orderitems( order_num int NOT NulL,order_item int NOT NulL,prod_ID char(10) NOT NulL,quantity int NOT NulL,item_price decimal(8,2) NOT NulL,PRIMARY KEY (order_num,order_item)) ENGINE=InnoDB;###################### Create orders table#####################CREATE table orders( order_num int NOT NulL auto_INCREMENT,order_date datetime NOT NulL,cust_ID int NOT NulL,PRIMARY KEY (order_num)) ENGINE=InnoDB;######################## Create products table#######################CREATE table products( prod_ID char(10) NOT NulL,vend_ID int NOT NulL,prod_name char(255) NOT NulL,prod_price decimal(8,2) NOT NulL,prod_desc text NulL,PRIMARY KEY(prod_ID)) ENGINE=InnoDB;####################### Create vendors table######################CREATE table vendors( vend_ID int NOT NulL auto_INCREMENT,vend_name char(50) NOT NulL,vend_address char(50) NulL,vend_city char(50) NulL,vend_state char(5) NulL,vend_zip char(10) NulL,vend_country char(50) NulL,PRIMARY KEY (vend_ID)) ENGINE=InnoDB;############################ Create productnotes table###########################CREATE table productnotes( note_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;###################### define foreign keys#####################ALTER table orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);ALTER table orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_ID) REFERENCES products (prod_ID);ALTER table orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_ID) REFERENCES customers (cust_ID);ALTER table products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_ID) REFERENCES vendors (vend_ID);insert相关语句
######################################### MysqL Crash Course# http://www.forta.com/books/0672327120/# Example table population scripts################################################################### Populate customers table##########################INSERT INTO customers(cust_ID,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)VALUES(10001,'Coyote Inc.','200 Maple Lane','Detroit','MI','44444','USA','Y Lee','[email protected]');INSERT INTO customers(cust_ID,cust_contact)VALUES(10002,'Mouse House','333 Fromage Lane','Columbus','OH','43333','Jerry Mouse');INSERT INTO customers(cust_ID,cust_email)VALUES(10003,'Wascals','1 Sunny Place','MuncIE','IN','42222','Jim Jones','[email protected]');INSERT INTO customers(cust_ID,cust_email)VALUES(10004,'Yosemite Place','829 RiversIDe Drive','Phoenix','AZ','88888','Y Sam','[email protected]');INSERT INTO customers(cust_ID,cust_contact)VALUES(10005,'E Fudd','4545 53rd Street','Chicago','IL','54545','E Fudd');######################### Populate vendors table########################INSERT INTO vendors(vend_ID,vend_name,vend_address,vend_city,vend_state,vend_zip,vend_country)VALUES(1001,'Anvils R Us','123 Main Street','SouthfIEld','48075','USA');INSERT INTO vendors(vend_ID,vend_country)VALUES(1002,'LT SupplIEs','500 Park Street','Anytown','44333',vend_country)VALUES(1003,'Acme','555 High Street','Los Angeles','CA','90046',vend_country)VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111',vend_country)VALUES(1005,'Jet Set','42 galaxy Road','London',NulL,'N16 6PS','England');INSERT INTO vendors(vend_ID,vend_country)VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris','45678','France');########################## Populate products table#########################INSERT INTO products(prod_ID,vend_ID,prod_name,prod_price,prod_desc)VALUES('ANV01',1001,'.5 ton anvil',5.99,'.5 ton anvil,black,complete with handy hook');INSERT INTO products(prod_ID,prod_desc)VALUES('ANV02','1 ton anvil',9.99,'1 ton anvil,complete with handy hook and carrying case');INSERT INTO products(prod_ID,prod_desc)VALUES('ANV03','2 ton anvil',14.99,'2 ton anvil,prod_desc)VALUES('ol1',1002,'Oil can',8.99,'Oil can,red');INSERT INTO products(prod_ID,prod_desc)VALUES('FU1','Fuses',3.42,'1 doZen,extra long');INSERT INTO products(prod_ID,prod_desc)VALUES('SliNG',1003,'Sling',4.49,'Sling,one size fits all');INSERT INTO products(prod_ID,prod_desc)VALUES('TNT1','TNT (1 stick)',2.50,'TNT,red,single stick');INSERT INTO products(prod_ID,prod_desc)VALUES('TNT2','TNT (5 sticks)',10,pack of 10 sticks');INSERT INTO products(prod_ID,prod_desc)VALUES('FB','Bird seed','Large bag (suitable for road runners)');INSERT INTO products(prod_ID,prod_desc)VALUES('FC','Carrots','Carrots (rabbit hunting season only)');INSERT INTO products(prod_ID,prod_desc)VALUES('SAFE','Safe',50,'Safe with combination lock');INSERT INTO products(prod_ID,prod_desc)VALUES('DTNTR','Detonator',13,'Detonator (plunger powered),fuses not included');INSERT INTO products(prod_ID,prod_desc)VALUES('JP1000',1005,'JetPack 1000',35,'JetPack 1000,intended for single use');INSERT INTO products(prod_ID,prod_desc)VALUES('JP2000','JetPack 2000',55,'JetPack 2000,multi-use');######################## Populate orders table#######################INSERT INTO orders(order_num,order_date,cust_ID)VALUES(20005,'2005-09-01',10001);INSERT INTO orders(order_num,cust_ID)VALUES(20006,'2005-09-12',10003);INSERT INTO orders(order_num,cust_ID)VALUES(20007,'2005-09-30',10004);INSERT INTO orders(order_num,cust_ID)VALUES(20008,'2005-10-03',10005);INSERT INTO orders(order_num,cust_ID)VALUES(20009,'2005-10-08',10001);############################ Populate orderitems table###########################INSERT INTO orderitems(order_num,order_item,prod_ID,quantity,item_price)VALUES(20005,1,'ANV01',5.99);INSERT INTO orderitems(order_num,2,'ANV02',3,9.99);INSERT INTO orderitems(order_num,'TNT2',5,10);INSERT INTO orderitems(order_num,4,'FB',item_price)VALUES(20006,'JP2000',55);INSERT INTO orderitems(order_num,item_price)VALUES(20007,100,item_price)VALUES(20008,'FC',2.50);INSERT INTO orderitems(order_num,item_price)VALUES(20009,'ol1',8.99);INSERT INTO orderitems(order_num,'SliNG',4.49);INSERT INTO orderitems(order_num,'ANV03',14.99);############################## Populate productnotes table#############################INSERT INTO productnotes(note_ID,note_date,note_text)VALUES(101,'2005-08-17','Customer complaint:Sticks not indivIDually wrapped,too easy to mistakenly detonate all at once.Recommend indivIDual wrapPing.');INSERT INTO productnotes(note_ID,note_text)VALUES(102,'2005-08-18','Can shipped full,refills not available.Need to order new can if refill needed.');INSERT INTO productnotes(note_ID,note_text)VALUES(103,'SAFE','Safe is combination locked,combination not provIDed with safe.This is rarely a problem as safes are typically blown up or dropped by customers.');INSERT INTO productnotes(note_ID,note_text)VALUES(104,'2005-08-19','Quantity varIEs,sold by the sack load.All guaranteed to be bright and orange,and suitable for use as rabbit bait.');INSERT INTO productnotes(note_ID,note_text)VALUES(105,'2005-08-20','Included fuses are short and have been kNown to detonate too quickly for some customers.Longer fuses are available (item FU1) and should be recommended.');INSERT INTO productnotes(note_ID,note_text)VALUES(106,'2005-08-22','Matches not included,recommend purchase of matches or detonator (item DTNTR).');INSERT INTO productnotes(note_ID,note_text)VALUES(107,'2005-08-23','Please note that no returns will be accepted if safe opened using explosives.');INSERT INTO productnotes(note_ID,note_text)VALUES(108,'2005-08-25','Multiple customer returns,anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer consIDers using heavIEr anvils.');INSERT INTO productnotes(note_ID,note_text)VALUES(109,'Item is extremely heavy. Designed for dropPing,not recommended for use with slings,ropes,pulleys,or tightropes.');INSERT INTO productnotes(note_ID,note_text)VALUES(110,'Customer complaint: rabbit has been able to detect trap,food apparently less effective Now.');INSERT INTO productnotes(note_ID,note_text)VALUES(111,'2005-09-02','Shipped unassembled,requires common tools (including oversized hammer).');INSERT INTO productnotes(note_ID,note_text)VALUES(112,'Customer complaint:Circular hole in safe floor can apparently be easily cut with handsaw.');INSERT INTO productnotes(note_ID,note_text)VALUES(113,'2005-09-05','Customer complaint:Not heavy enough to generate flying stars around head of victim. If being purchased for dropPing,recommend ANV02 or ANV03 instead.');INSERT INTO productnotes(note_ID,note_text)VALUES(114,'2005-09-07','Call from indivIDual trapped in safe plummeting to the ground,suggests an escape hatch be added.Comment forwarded to vendor.');-- 启动MysqL服务MysqLd MysqL -hlocalhost -P3306 -uroot -p quit-- 选择数据库testuse test；-- 建数据库语句模板Create database [if not exists] 数据库名 [数据库选项] -- 建数据库例子,在建数据库时一般只要指定charset就可以了（有时你还可以设置校验规则 ）CREATE DATABASE if not exists my_database charset utf8;-- 选择数据库use my_database;-- 删除数据库语句模板drop database [if exists] 数据库名 [数据库选项] -- 删除数据库例子,在建数据库时一般只要指定charset就可以了drop DATABASE if exists my_database;-- 一般只要修改数据库的字符集就可以了，校验规则会默认选和字符集匹配的ALTER DATABASE my_database CHaraCTER SET gbk;-- 查询数据库名字以my打头的数据库SHOW DATABASES liKE 'my%'; -- 显示所有的数据库SHOW DATABASES;-- 显示数据库的创建结构SHOW CREATE DATABASE my_database;-- 使用help命名，查看帮助help showSelect [distinct] select_expr [from tbl_name] [where] [group by] [having] [order by] [limit]-- 查询每个供应商的产品数SELECT vend_ID,COUNT(*) AS num_prods FROM products GROUP BY vend_ID HAVING num_prods > 2;SELECT vend_ID,COUNT(*) AS num_prods FROM products GROUP BY vend_ID HAVING COUNT(*) > 2;-- 查询每个供应商最贵的产品价格SELECT t1.vend_ID,MAX(prod_price) FROM vendors t1 left JOIN products t2 ON t1.vend_ID = t2.vend_ID GROUP BY t1.vend_ID;-- 查询来自底特律订单数量大于2的客户SELECT t1.`cust_ID`,t1.`cust_name`,t1.`cust_city`,t2.`order_num`,COUNT(order_num) AS numFROM customers t1 left JOIN orders t2 ON t1.`cust_ID` = t2.`cust_ID`WHERE t1.`cust_city` = 'Detroit'GROUP BY t1.`cust_ID` HAVING num >=2;以上是内存溢出为你收集整理的《SQL 必知必会》建表语句全部内容，希望文章能够帮你解决《SQL 必知必会》建表语句所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）