关于新建数据库和数据表

使用MySQL 8.0创建数据库和数据表

（一）创建名为 YGGL 的数据库：

SQL语句： 

CREATE DATABASE YGGL ;

 

（二）在数据库中创建数据表，以Employees ；Departments ；Salary ；为例：

（1）要先进入名为 YGGL 的数据库：

SQL语句：

USE YGGL ;

 

（2）创建数据表：

CREATE TABLE <表名>

（<字段1> <数据类型1> [<列级完整性约束1>] ,

   <字段2> <数据类型2> [<列级完整性约束2>] ，....

,<表级完整性约束1>

,<表级完整性约束2>

）；

1.首先创建Departments关系表和创建Salary关系表：

SQL语句：

 

CREATE TABLE Departments
(
department_number char(1) PRIMARY KEY,
department_name varchar(30),
remarks varchar(40) NULL
);

 

CREATE TABLE Salary
(
number char(6) PRIMARY KEY NOT NULL,
income double(20,2),
pay double(20,2)
);

2.创建创建Employees关系表：

（这个表最后建，这样可以给它进行外键的约束）

SQL语句：

 

CREATE TABLE Employees
(
number char(6) PRIMARY KEY,
name char(8),
education char(4),
birthday date,
sax char(1),
time char(2),
address varchar(30),
phone char(18) DEFAULT '不详',
department_number char(1),
CONSTRAINT A1 FOREIGN KEY(department_number) REFERENCES DEPARTMENTS (department_number),
CONSTRAINT A2 FOREIGN KEY(number) REFERENCES SALARY (number),
CONSTRAINT A3 CHECK(sax>=0 AND sax<=1)
);

 

（上面的创建数据表是没有数据的，只是创建了表格有几行几列，并且规定了其内容的字符类型，接下来是要插入数据）

（3）插入数据：

INSERT INTO <表名> (<列名1>,<列名2>,...)

VALUES('<内容1>','<内容2>',...)；

 

Departments关系表

SQL语句：

 

insert into Departments(department_number,Department_name)
values('1',"财务部");

insert into Departments(department_number,Department_name)
values('2',"人力资源部");

insert into Departments(department_number,Department_name)
values('3',"经理办公室");

insert into Departments(department_number,Department_name)
values('4',"研发部");

insert into Departments(department_number,Department_name)
values('5',"市场部");

 

Salary关系表

SQL语句：

 

insert into Salary(number,income,pay)
values('000001',"2100.80","123.09");

insert into Salary(number,income,pay)
values('010008',"1582.62","88.03");

insert into Salary(number,income,pay)
values('020010',"2860.00","198.00");

insert into Salary(number,income,pay)
values('020018',"2347.68","180.00");

insert into Salary(number,income,pay)
values('102201',"2569.88","185.65");

insert into Salary(number,income,pay)
values('102208',"1980.00","100.00");

insert into Salary(number,income,pay)
values('108991',"3259.98","281.52");

insert into Salary(number,income,pay)
values('111006',"1987.01","79.58");

insert into Salary(number,income,pay)
values('210678',"2240.00","121.00");

insert into Salary(number,income,pay)
values('308759',"2531.98","199.08");

insert into Salary(number,income,pay)
values('504209',"2066.15","108.00");

insert into Salary(number,income,pay)
values('302566',"2980.70","210.20");

 

Employees关系表

SQL语句：

 

insert into Employees(number,name,education,birthday,sax,time,address,phone,department_number)
values('000001','王林','大专','1966-01-23','1','8','中山路32','83355668','2');

insert into Employees(number,name,education,birthday,sax,time,address,phone,department_number)
values('010008','伍春华','本科','1976-03-28','0','3','北京路10','83321321','1');

insert into Employees(number,name,education,birthday,sax,time,address,phone,department_number)
values('020010','王向荣','硕士','1982-12-09','1','2','四牌楼10','83792361','1');

insert into Employees(number,name,education,birthday,sax,time,address,phone,department_number)
values('020018','李丽','大专','1960-07-30','0','6','中山路102','83413301','1');

insert into Employees(number,name,education,birthday,sax,time,address,phone,department_number)
values('102201','刘明','大专','1972-10-18','1','3','虎距路100','83606608','5');

insert into Employees(number,name,education,birthday,sax,time,address,phone,department_number)
values('102208','朱俊','硕士','1965-09-28','1','2','牌楼巷','84708817','3');

insert into Employees(number,name,education,birthday,sax,time,address,phone,department_number)
values('111006','张石兵','本科','1974-10-01','1','1','解放路','84563418','5');

insert into Employees(number,name,education,birthday,sax,time,address,phone,department_number)
values('308759','叶凡','本科','1978-11-18','1','2','北京西路','83308901','4');

 

（这样三个表都建立好啦~ ）