数据库大讲解第三讲

**

数据库大讲解第三讲

**

数据库表创建 作者：董哥

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

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

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

1. 前期·准备
   前期准备创建数据库employe并创建多个表
   前期是随便练习后边知识由浅入深
   前边几页是练习代码所需要的各种表
   里面有中文符号最好自己动手敲
   创建数据库
   DROP TABLE IF EXISTS emp_pay_trmp;//预先清空数据库表
   CREATE TABLE emp_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,’’);