**
数据库大讲解第三讲
**
数据库表创建 作者:董哥
预先创建好需要创建的数据库,有databases:employe,t1,test1 ,test等等
创建数据库,及各数据库表
主要采用的是Mysql数据库,其中会有提到其他数据库:
- 前期·准备
前期准备创建数据库employe并创建多个表
前期是随便练习后边知识由浅入深
前边几页是练习代码所需要的各种表
里面有中文符号最好自己动手敲
创建数据库
DROP TABLE IF EXISTSemp_pay_trmp
;//预先清空数据库表
CREATE TABLEemp_pay_trmp
(
city
char(20) CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci NOT NULL DEFAULT ‘’,
last_name
char(23) CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci NOT NULL DEFAULT ‘’,
first_na
varchar(255) DEFAULT NULL,
pay_rate
double(2,0) DEFAULT NULL,
salary
int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Data for table “emp_pay_trmp”
INSERT INTO emp_pay_trmp
VALUES (‘greenwood’,‘stephens’,‘tina’,NULL,NULL),(‘indianapolis’,‘plew’,‘linda’,NULL,NULL),(‘whiteland’,‘glass’,NULL,NULL,NULL),(‘indianapolis’,‘plew’,‘linda’,NULL,NULL),(‘indianapolis’,‘plew’,‘linda’,NULL,NULL),(‘indianapolis’,‘plew’,‘linda’,NULL,NULL);
Structure for table “employe1”
DROP TABLE IF EXISTS employe1
;
CREATE TABLE employe1
(
emp_id
char(9) NOT NULL,
position
varchar(15) NOT NULL,
emp_name
varchar(20) NOT NULL,
emp_page
int(10) DEFAULT NULL,
emp_phone
int(13) DEFAULT NULL,
pay_rate
double(4,2) NOT NULL,
UNIQUE KEY emp_phone
(emp_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Data for table “employe1”
INSERT INTO employe1
VALUES (‘112’,'mysql> select ',‘晓丽’,21112,212,99.99),(‘113’,‘2255’,‘战台’,456,NULL,0.00),(‘114’,‘1213’,‘董静’,45,NULL,59.00),(‘118’,‘1200’,‘华斯’,8966,1546,66.00);
Structure for table “employe2”
DROP TABLE IF EXISTS employe2
;
CREATE TABLE employe2
(
emp_id
char(9) NOT NULL,
position
varchar(15) NOT NULL,
emp_name
varchar(20) NOT NULL,
emp_page
int(10) DEFAULT NULL,
emp_phone
char(12) DEFAULT NULL,
pay_rate
double(4,2) NOT NULL,
UNIQUE KEY emp_phone
(emp_phone
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Data for table “employe2”
INSERT INTO employe2
VALUES (’’,’’,’’,NULL,NULL,22.00),(’’,’’,’’,NULL,NULL,11.00),(‘101’,’’,’’,NULL,NULL,0.00),(‘101’,’’,’’,NULL,NULL,0.00);
Structure for table “employee_tb2”
DROP TABLE IF EXISTS employee_tb2
;
CREATE TABLE employee_tb2
(
emp_id
char(9) NOT NULL,
emp_name
varchar(20) NOT NULL,
emp_page
int(10) DEFAULT NULL,
emp_phone
int(13) DEFAULT NULL,
PRIMARY KEY (emp_id
),
UNIQUE KEY emp_phone
(emp_phone
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Data for table “employee_tb2”
INSERT INTO employee_tb2
VALUES (‘1001’,‘123’,NULL,1000),(‘1002’,‘123’,NULL,1001);
Structure for table “employee_tbl”
DROP TABLE IF EXISTS employee_tbl
;
CREATE TABLE employee_tbl
(
emp_id
char(9) NOT NULL,
emp_name
varchar(20) NOT NULL,
emp_page
int(10) DEFAULT NULL,
PRIMARY KEY (emp_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Data for table “employee_tbl”
INSERT INTO employee_tbl
VALUES (‘101’,‘dongjinghanng’,NULL);
Structure for table “employee1”
DROP TABLE IF EXISTS employee1
;
CREATE TABLE employee1
(
emp_id
char(9) NOT NULL,
position
varchar(15) NOT NULL,
emp_name
varchar(20) NOT NULL,
emp_page
int(10) DEFAULT NULL,
emp_phone
int(13) DEFAULT NULL,
UNIQUE KEY emp_phone
(emp_phone
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Data for table “employee1”
INSERT INTO employee1
VALUES (‘112’,’’,‘张好’,NULL,123456);
Structure for table “student”
DROP TABLE IF EXISTS student
;
CREATE TABLE student
(
Student_id
char(9) NOT NULL,
Student_sname
varchar(10) NOT NULL,
Student_age
datetime DEFAULT NULL,
Student_city
varchar(10) NOT NULL,
salary
double(5,2) DEFAULT NULL,
Student_st
varchar(10) DEFAULT NULL,
PRIMARY KEY (Student_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Data for table “student”
INSERT INTO student
VALUES (‘101’,‘张三’,NULL,‘北京’,56.00,‘117’),(‘102’,‘王五’,‘1995-02-06 00:00:00’,‘北京’,78.00,’’),(‘103’,‘赵四’,‘1898-05-12 00:00:00’,‘广州’,98.00,‘103’),(‘104’,‘王偶’,‘1763-12-04 00:00:00’,‘上海’,45.00,‘123’),(‘105’,‘王五’,‘1990-01-12 00:00:00’,‘北京’,67.00,’’);