mysql中针对表的增删改查及约束的操作

题目：

 

 

 

1、

CREATE DATABASE company;
 
 CREATE TABLE offices(
 officeCode INT(10) PRIMARY KEY NOT NULL UNIQUE,
 city VARCHAR(50) NOT NULL,
 address VARCHAR(50),
 country VARCHAR(50) NOT NULL,
 postaCode VARCHAR(50) UNIQUE
 );
 
 CREATE TABLE employees( 
employeeNumber INT(11) PRIMARY KEY NOT NULL UNIQUE 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) 
);

ALTER TABLE employees ADD CONSTRAINT fk_officeCode FOREIGN KEY(officeCode) REFERENCES offices(officeCode);

2、

SHOW TABLES FROM company;
SHOW COLUMNS FROM employees;
SHOW COLUMNS FROM offices;

3、

ALTER TABLE employees MODIFY COLUMN mobile VARCHAR(25) AFTER officeCode;
 

4、

ALTER TABLE employees CHANGE birth employees_birth DATETIME;
 

5、

ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;

6、

ALTER TABLE employees DROP note;

7、

ALTER TABLE employees ADD favoriate_activity VARCHAR(100);

8、

ALTER TABLE employees_info DROP FOREIGN KEY fk_officeCode;
#因为之前office表中与employess表中有一个外键约束，所以要删除office表必须先删除这个约束，删除
office，否则删除不了。因为约束主要用于保证数据库的完整性。当表中数据有相互依赖性时,可以保护相关的数据不被删除。
DROP TABLE offices;

9、

ALTER TABLE employees RENAME employees_info;