How to create tables in MySQL and add various constraints

--Using Database hr


Use HR;

--Create a table in the database
--------------------------------------jobs Table----------------------------------------------------------
--Determine if table jobs exists in the database
drop table if exists JOBS;
--Create TABLE jobs
CREATE TABLE JOBS
(
Job_idvarchar (Ten) NOT null,--position number, primary key
Job_titlenvarchar () not null,--position name
Min_salaryfloatnot NULL,--Job minimum salary, not less than $1000 data type with no money type in MySQL
Max_salaryfloatnot null-The position is the most well-paid, not less than the minimum wage

);

--Create a constraint for the jobs table
--Add a PRIMARY KEY constraint
ALTER TABLE JOBS
Add constraint primary key (job_id);
-Minimum salary, not less than 1000 yuan
ALTER TABLE JOBS
Add constraint check (min_salary>=1000);
-the highest salary of the job, not less than the minimum salary
ALTER TABLE JOBS
Add check (max_salary>=min_salary);

--Inserting test data
INSERT into JOBS values (' 100 ', ' manager ', 12000,19990), (' 101 ', ' Supervisor ', 6200,8999);
--select * from JOBS;

--------------------------------------------------structure of the Departments department table----------------------------------------------------- --------
--Determine if table departments exists in the database
DROP table if exists departments;
--Create TABLE departments
CREATE TABLE Departments
(
Department_idint NOT NULL,--department number, primary key, self-increment.
Department_namenvarchar (+) NOT NULL,--department name
Manager_idint--Department supervisor number, foreign key, reference Employees table employee_id

);

--Create constraints on table departments
--Create a constraint for the primary key
ALTER TABLE Departments
Add constraint primary key (DEPARTMENT_ID);
--Add auto-grow to primary key
ALTER TABLE Departments
Modify department_id int auto_increment;
--Department supervisor number, foreign key, reference Employees table employee_id
--ALTER TABLE departments
--Add Constraint fk_manager_id foreign key (manager_id) references EMPLOYEES (employee_id);

--Inserting test data
--INSERT INTO departments values (NULL, ' Finance department ', $), (NULL, ' purchasing department ', 300);
--select * from departments;

------------------------------------------------structure of the Employees staff table--------------------------------------------------------- --------------
--Determine if table employees exists in the database
drop table if exists EMPLOYEES;
--Create TABLE employees
CREATE TABLE EMPLOYEES
(
Employee_idintnot NULL,--employee number, primary key, self-increment (starting at 100, 1 increase each time)
First_namenvarchar (4) Not NULL,--employee name
Last_namenvarchar (4) Not NULL,--employee last Name
Emailvarchar (+) NOT NULL,--Staff mail
Phone_numbervarchar () Not NULL,--Staff phone
Hire_date TIMESTAMP NOT NULL,--GETDATE () Staff entry time
Job_idvarchar (Ten) NOT NULL,--clerk's position number, foreign key, reference to Jobs table job_id
Salaryfloat,--staff salary, more than 0;
Manager_idint,--Employee supervisor number, foreign key, reference to Employees table employee_id
Department_idint--Employee department number, foreign key, reference to departments table department_id

) auto_increment=100;
--Adding constraints
--Add a PRIMARY KEY constraint
ALTER TABLE EMPLOYEES
Add primary key (EMPLOYEE_ID);
--Add a self-increment to the primary key
ALTER TABLE EMPLOYEES
Modify employee_id int auto_increment;
--GETDATE () Staff entry time
ALTER TABLE EMPLOYEES
Modify Hire_datetimestamp default ' 2016-7-23 ';
--Add Constraint staff position number, foreign key, reference to Jobs table job_id----------------------------Add a FOREIGN KEY constraint when adding constraint
--ALTER TABLE EMPLOYEES
--Add Constraint fk_job_id foreign key (job_id) references Jbos (job_id);
--staff salary, more than 0
ALTER TABLE EMPLOYEES
Add check (salary>0);
--Employee Supervisor number, foreign key, reference Employees table employee_id
ALTER TABLE EMPLOYEES
Add constraint fk_employee_id foreign key (employee_id) references EMPLOYEES (employee_id);
--Employee department number, foreign key, reference departments table department_id
ALTER TABLE EMPLOYEES
Add constraint fk_department_id foreign key (DEPARTMENT_ID) references departments (DEPARTMENT_ID);

How to create tables in MySQL and add various constraints