数据库大讲解第一讲

**

数据库大讲解第一讲

**

第一节数据库表创建 作者：董哥

预先创建好需要创建的数据库，有databases:employe,t1,test1 ,test等等

创建数据库，及各数据库表

主要采用的是Mysql数据库，其中会有提到其他数据库：

1. 前期·准备
   前期准备创建数据库employe并创建多个表
   前期是随便练习后边知识由浅入深
   前边几页是练习代码所需要的各种表
   里面有中文符号最好自己动手敲
   创建数据库
   CREATE DATABASE employe /*!40100 DEFAULT CHARACTER SET utf8 */;

Host: 28.5.8.60 (Version 5.7.17)

Date: 2019-08-29 16:51:37

Writer: 煮不烂的鸡蛋 (Build 1.26)

Structure for table “customer_tbl”

DROP TABLE IF EXISTS customer_tbl;
CREATE TABLE customer_tbl (
cust_id varchar(10) NOT NULL DEFAULT ‘’,
cust_name varchar(30) NOT NULL DEFAULT ‘’,
cust_address varchar(20) NOT NULL DEFAULT ‘’,
cust_city varchar(15) NOT NULL DEFAULT ‘’,
cust_state char(2) NOT NULL DEFAULT ‘’,
cust_zip bigint(20) DEFAULT NULL,
cust_phone char(11) DEFAULT NULL,
cust_fax varchar(255) DEFAULT NULL,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Data for table “customer_tbl”

INSERT INTO customer_tbl VALUES (‘090’,‘gyui’,‘nlkhlk’,‘inadd’,‘in’,46224,‘223323’,’’),
(‘109’,‘nanweg’,’ apt a4556 a’,‘broa’,‘in’,47950,‘153782’,NULL),
(‘12’,‘hjklk’,‘oiioh’p’,‘danv’,‘il’,47978,‘323323’,‘2232313213’),
(‘232’,‘sgah nka’,‘798 agfhj’,‘inadd’,‘in’,47856,‘156324’,NULL),
(‘345’,‘sadf’,‘sadfaf’,‘leba’,‘in’,49967,‘123436’,NULL);

Structure for table “employe_tbl”

DROP TABLE IF EXISTS employe_tbl;
CREATE TABLE employe_tbl (
emp_id varchar(9) NOT NULL DEFAULT ‘’,
last_name varchar(15) NOT NULL DEFAULT ‘’,
first_name varchar(15) NOT NULL DEFAULT ‘’,
middle_name varchar(15) DEFAULT NULL,
address varchar(30) NOT NULL DEFAULT ‘’,
city varchar(15) NOT NULL DEFAULT ‘’,
state char(2) NOT NULL DEFAULT ‘’,
zip bigint(5) NOT NULL DEFAULT ‘0’,
phone char(10) DEFAULT NULL,
pager char(10) DEFAULT NULL,
PRIMARY KEY (emp_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Data for table “employe_tbl”

INSERT INTO employe_tbl VALUES
(‘213764555’,‘glass’,‘brandon’,‘scott’,‘1710 main st’,‘whiteland’,‘in’,47885,‘3178984432’,‘3175709980’),
(‘220984332’,‘wallace’,‘marian’,NULL,‘7889 keystone ave’,‘indianapolis’,‘in’,46741,‘3178989266’,NULL),
(‘311549902’,‘stephens’,‘tina’,‘dawn’,‘rr 3 box 17 a’,‘greenwood’,‘in’,47890,‘3178784465’,NULL),
(‘313782439’,‘glass’,‘jacob’,NULL,‘3789 white river blvd’,‘indianapolis’,‘in’,45734,‘1235112311’,‘8887345678’),
(‘442346889’,‘plew’,‘tina’,‘carol’,‘3301 beacon’,‘indianapolis’,‘in’,46624,‘3178886665’,NULL),
(‘443679021’,‘spurgeon’,‘tiffany’,NULL,‘5 george court’,‘indianapolis’,‘in’,46234,‘3178552233’,NULL);

Structure for table “employe_pay_tbl”

DROP TABLE IF EXISTS employe_pay_tbl;
CREATE TABLE employe_pay_tbl (
emp_id varchar(11) NOT NULL DEFAULT ‘’,
position varchar(15) NOT NULL DEFAULT ‘’,
date_hire date DEFAULT NULL,
pay_rate decimal(4,2) DEFAULT NULL,
date_last_raise date DEFAULT NULL,
salary decimal(8,2) DEFAULT NULL,
bonus decimal(6,2) DEFAULT NULL,
PRIMARY KEY (emp_id),
CONSTRAINT emp_fk FOREIGN KEY (emp_id) REFERENCES employe_tbl (emp_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Data for table “employe_pay_tbl”

INSERT INTO employe_pay_tbl VALUES (‘220984332’,‘shipper’,‘2006-07-22’,13.31,‘1999-07-01’,NULL,NULL),(‘311549902’,‘marketing’,‘1999-05-23’,NULL,‘2009-05-01’,40000.00,NULL),(‘313782439’,‘salesman’,‘2007-06-28’,NULL,‘1879-01-12’,20000.00,1000.00),(‘442346889’,‘team leader’,‘2000-06-17’,17.85,‘2009-06-01’,NULL,NULL),(‘443679021’,‘shipper’,‘2001-01-14’,18.15,‘1999-01-14’,NULL,NULL);

Structure for table “orders_tbl”

DROP TABLE IF EXISTS orders_tbl;
CREATE TABLE orders_tbl (
ord_num varchar(11) NOT NULL DEFAULT ‘’,
cust_id varchar(255) NOT NULL DEFAULT ‘’,
prod_id varchar(255) NOT NULL DEFAULT ‘’,
qty bigint(11) unsigned NOT NULL DEFAULT ‘0’,
ord_date date DEFAULT NULL,
PRIMARY KEY (ord_num)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Data for table “orders_tbl”

INSERT INTO orders_tbl VALUES (‘16c17’,‘090’,‘222’,200,‘2009-10-17’)
,(‘18d778’,‘109’,‘90’,108,‘2009-10-17’),(‘32a132’,‘345’,‘222’,2,‘2009-10-10’),
(‘56a901’,‘232’,‘11235’,1,‘2009-10-22’),(‘56a917’,‘12’,‘907’,100,‘2009-03-30’);

Structure for table “products_tbl”