MySQL学习作业1

MySQL学习作业1

create database company;
use company
create table offices(
	officeCode int(10) primary key,
	city varchar(50) not null,
	address varchar(50),
	country varchar(50)not null,
	postalCode varchar(15) unique
);
show create table offices;
/*
CREATE TABLE `offices` (
  `officeCode` int NOT NULL,
  `city` varchar(50) NOT NULL,
  `address` varchar(50) DEFAULT NULL,
  `country` varchar(50) NOT NULL,
  `postalCode` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`officeCode`),
  UNIQUE KEY `postalCode` (`postalCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

create table employees(
	employeeNumber int(11) primary key auto_increment,
	lastName varchar(50) not null,
	firstName varchar(50) not null,
	mobile varchar(25) unique,
	officeCode int(10) not null,
	jobTitle varchar(50) not null,
	birth datetime not null,
	note varchar(255),
	sex varchar(5),
	constraint fk_employees_offices
	foreign key(officeCode) references offices(officeCode)
	); 
show create table employees;
/*
CREATE TABLE `employees` (
  `employeeNumber` int NOT NULL AUTO_INCREMENT,
  `lastName` varchar(50) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `mobile` varchar(25) DEFAULT NULL,
  `officeCode` int NOT NULL,
  `jobTitle` varchar(50) NOT NULL,
  `birth` datetime NOT NULL,
  `note` varchar(255) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`employeeNumber`),
  UNIQUE KEY `mobile` (`mobile`),
  KEY `fk_employees_offices` (`officeCode`),
  CONSTRAINT `fk_employees_offices` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/
show tables;

alter table employees modify mobile varchar(25) after officeCode;

alter table employees change birth employee_birth datetime;

alter table employees modify sex char(1) not null;

alter table employees drop note;

alter table employees add favoriate_activity varchar(100);
show create table employees;
/*
CREATE TABLE `employees` (
  `employeeNumber` int NOT NULL AUTO_INCREMENT,
  `lastName` varchar(50) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `officeCode` int NOT NULL,
  `mobile` varchar(25) DEFAULT NULL,
  `jobTitle` varchar(50) NOT NULL,
  `employee_birth` datetime DEFAULT NULL,
  `sex` char(1) NOT NULL,
  `favoriate_activity` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`employeeNumber`),
  UNIQUE KEY `mobile` (`mobile`),
  KEY `fk_employees_offices` (`officeCode`),
  CONSTRAINT `fk_employees_offices` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

/*
在这里我们需要先解除外键约束再删除外键约束才能正常删除表offices
*/
alter table employees drop foreign key fk_employees_offices;
alter table employees drop officeCode;
drop table offices;

alter table employees rename employees_info;
show create table employees_info;
/*
CREATE TABLE `employees_info` (
  `employeeNumber` int NOT NULL AUTO_INCREMENT,
  `lastName` varchar(50) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `mobile` varchar(25) DEFAULT NULL,
  `jobTitle` varchar(50) NOT NULL,
  `employee_birth` datetime DEFAULT NULL,
  `sex` char(1) NOT NULL,
  `favoriate_activity` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`employeeNumber`),
  UNIQUE KEY `mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/