- 浏览: 574819 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
liuzeyuss:
好文章,顶顶
java 线程死锁的检测 -
ysite:
hugh.wang 写道请问你这个能还原吗?短网址用的是HAS ...
Java版短网址(ShortUrl)的算法 -
lv双:
没有注释,看的费劲
java 线程死锁的检测 -
dgj:
好帖子竟然没人顶...
java 线程死锁的检测 -
天空趋虚:
不好,你这只是记录式的博客,不是分享式的博客,对浏览着不友好. ...
一些技术牛人的博客
首先是建立数据库的脚本
下面是往里面添加数据的脚本
注释的地方可能会引起报错,有人用的话自己去掉吧 。
-------------------------------------------- -- Sams Teach Yourself SQL in 10 Minutes -- http://www.forta.com/books/0672325675/ -- Example table creation scripts for MySQL. -------------------------------------------- ------------------------- -- Create customers table ------------------------- CREATE TABLE customers ( cust_id char(10) NOT NULL , 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 ); -------------------------- -- 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 ); ---------------------- -- Create orders table ---------------------- CREATE TABLE orders ( order_num int NOT NULL , order_date datetime NOT NULL , cust_id char(10) NOT NULL ); ------------------------ -- Create products table ------------------------ CREATE TABLE products ( prod_id char(10) NOT NULL , vend_id char(10) NOT NULL , prod_name char(255) NOT NULL , prod_price decimal(8,2) NOT NULL , prod_desc text NULL ); ----------------------- -- Create vendors table ----------------------- CREATE TABLE vendors ( vend_id char(10) NOT NULL , 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 ); ---------------------- -- Define primary keys ---------------------- ALTER TABLE customers ADD PRIMARY KEY (cust_id); ALTER TABLE orderItems ADD PRIMARY KEY (order_num, order_item); ALTER TABLE orders ADD PRIMARY KEY (order_num); ALTER TABLE products ADD PRIMARY KEY (prod_id); ALTER TABLE vendors ADD PRIMARY KEY (vend_id); ---------------------- -- 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);
下面是往里面添加数据的脚本
---------------------------------------------- -- Sams Teach Yourself SQL in 10 Minutes -- http://www.forta.com/books/0672325675/ -- Example table population scripts for MySQL. ---------------------------------------------- --------------------------- -- 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('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com'); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact) VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green'); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com'); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com'); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact) VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard'); ------------------------- -- Populate vendors table ------------------------- INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France'); -------------------------- -- Populate products table -------------------------- INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown'); ------------------------ -- Populate orders table ------------------------ INSERT INTO orders(order_num, order_date, cust_id) VALUES(20005, '2004-05-01', '1000000001'); INSERT INTO orders(order_num, order_date, cust_id) VALUES(20006, '2004-01-12', '1000000003'); INSERT INTO orders(order_num, order_date, cust_id) VALUES(20007, '2004-01-30', '1000000004'); INSERT INTO orders(order_num, order_date, cust_id) VALUES(20008, '2004-02-03', '1000000005'); INSERT INTO orders(order_num, order_date, cust_id) VALUES(20009, '2004-02-08', '1000000001'); ---------------------------- -- Populate orderItems table ---------------------------- INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20005, 1, 'BR01', 100, 5.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20005, 2, 'BR03', 100, 10.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20006, 1, 'BR01', 20, 5.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20006, 2, 'BR02', 10, 8.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20006, 3, 'BR03', 10, 11.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20007, 1, 'BR03', 50, 11.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20007, 2, 'BNBG01', 100, 2.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20007, 3, 'BNBG02', 100, 2.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20007, 4, 'BNBG03', 100, 2.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20007, 5, 'RGAN01', 50, 4.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20008, 1, 'RGAN01', 5, 4.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20008, 2, 'BR03', 5, 11.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20008, 3, 'BNBG01', 10, 3.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20008, 4, 'BNBG02', 10, 3.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20008, 5, 'BNBG03', 10, 3.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 1, 'BNBG01', 250, 2.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 2, 'BNBG02', 250, 2.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 3, 'BNBG03', 250, 2.49);
注释的地方可能会引起报错,有人用的话自己去掉吧 。
发表评论
-
第一.二.三范式的定义与用途
2011-04-08 10:33 1794设计范式(范式,数据库 ... -
数据库的优化
2011-03-31 17:05 11691.对查询进行优化,应尽量避免全表扫描,首先应考虑在 wher ... -
DDL,DML,DCL,TCL四种语言的简介
2011-03-02 11:44 12161.DDL (Data Definition Language ... -
使用union进行结果集合并的原则
2011-02-15 15:57 12691,union必须有两个或两个以上的select语句组合,每条 ... -
mysql中join,left join,right join 的区别
2011-02-15 15:33 7916先看例子: 首先是join select vend_nam ... -
关于mysql 的字符串拼接问题
2011-02-15 11:50 2150[size=medium] 以前用Oracle的时候,字符串拼 ... -
mysql的错误代码121和150的意思
2011-02-15 11:11 1693今天用mysql数据库的时候,出现了121和150的错误,经过 ... -
数据库中的主键
2011-02-15 09:41 1884一,什么是主键? 唯一标示表中每行的这个列或这组列成为主键,主 ... -
关于mysql中时间的处理
2011-01-21 11:39 1097这里是一个使用日期函数的例子。下面的查询选择了所有记录,其da ... -
关于mysql的大小写问题
2011-01-21 10:11 1313mysql到底是区别大小写还是不区别呢,这取决于所使用的操作系 ... -
从linux登录和登出mysql
2011-01-21 09:41 18024首先最常用的mysql登录命令行为: babyduncan ... -
ubuntu下登陆mysql
2011-01-19 14:48 5851babyduncan@ubuntu:~$ mysql -h ... -
mysql创建用户的方法
2011-01-14 18:02 934grant all on test.* to 'root' ... -
mysql自学笔记
2011-01-06 17:16 1436现在我们就可以调用一 ...
相关推荐
mysql数据库分组统计数据库 练习脚本 DDL
数据库脚本数据库脚本数据库脚本数据库脚本数据库脚本
数据库练习脚本。。。。。。。。。。。。。。。。。。。。。。。。。。
mysql在windows、linux数据库自动备份删除脚本 oracl在windows、linux数据库自动备份删除脚本 sql server在windows、linux数据库自动备份删除脚本
oracle和MySQL数据库备份脚本oracle和MySQL数据库备份脚本
数据库基础脚本数据库基础脚本数据库基础脚本数据库基础脚本
PostgreSQL数据库备份脚本,结合系统计划任务可以实现数据库的自动化备份。密码直接设置于脚本当中。
Oracle数据库备份脚本 用于备份数据库里的所有数据
Nacos 2.1.2 数据库初始化脚本
nacos2.0.2版本依赖数据库sql脚本
01-25 ##note: ##fun_num fun函数后台运行次数 ##sql_num 每个函数sql运行次数 ##********************************##数据库变量设置dbhost=192.168.1.182dbbase=rechargedbuser=infosmsdbpass=info
Loadrunner测试数据库性能测试SQL语句性能的脚本例子.pdf
oracle数据库表按年分区脚本实战例子,特点:上手性好,易二次开发和调试。
Oracle 数据库 常用巡检脚本
这是我自己写的全国的省市数据库及脚本,里面包括创建数据库的脚本和插入数据的脚本
mysql-student数据库脚本,供平时常用的mysql数据库脚本
msql数据库备份,批处理脚本,减少人工手动备份数据库的工作量
可以直接运行,也可以直接导入到数据库,瞬间填满您的数据库。... 超大数据库脚本 超大数据库脚本 超大数据库脚本 超大数据库脚本 超大数据库脚本 超大数据库脚本 超大数据库脚本 超大数据库脚本 超大数据库脚本
shell连接oracle数据库工具脚本:支持select/insert/update/delete 部署位置:/root/sysmonitor db:数据库文件夹 dbconfig.properties:数据库配置文件, dbConnectTest.sh:连接测试文件 dbExecurteSQL.sh:...
MS SQL 样本数据库脚本 MS SQL 样本数据库脚本 MS SQL 样本数据库脚本