MYSQL入门01

准备需要用到的四张表

# 创建数据库
CREATE DATABASE IF NOT EXISTS company;
USE company;
# 创建offices
CREATE TABLE IF NOT EXISTS offices(
officeCode INT(10) NOT NULL UNIQUE,
city VARCHAR(50) NOT NULL,
address VARCHAR(50),
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15) UNIQUE,
PRIMARY KEY(officeCode));
# 创建 employees
CREATE TABLE IF NOT EXISTS employeeNumber(  
employeeNumber INT(11) NOT NULL AUTO_INCREMENT UNIQUE,
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),
PRIMARY KEY (`employeeNumber`)
) CHARSET=utf8;
# 修改表名
ALTER TABLE employeeNumber RENAME employees;
# 添加外键
ALTER TABLE employees 
ADD CONSTRAINT fk_offices_employees 
FOREIGN KEY (officeCode) REFERENCES 
offices (officeCode) ;
# 删除外键fk_employee_offices
ALTER TABLE company.`employees` DROP FOREIGN KEY fk_employee_offices;
#创建Market数据库
CREATE DATABASE IF NOT EXISTS Market;

# 使用Market
USE Market;
# 创建 customers
CREATE TABLE IF NOT EXISTS customers(
c_num INT(10) NOT NULL UNIQUE AUTO_INCREMENT,
c_name VARCHAR(50),
c_contact VARCHAR(50),
c_city VARCHAR(50),
c_birth DATETIME NOT NULL,
PRIMARY KEY(c_num))CHARSET=utf8;
# 创建 orders
CREATE TABLE IF NOT EXISTS orders(
o_num INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
o_date DATE,
c_id INT(11),
PRIMARY KEY(o_num))CHARSET=utf8;
# 设置外键
ALTER TABLE orders ADD CONSTRAINT
fk_orders_customers FOREIGN KEY(c_id)
REFERENCES Market(c_num);

1.查看该库下几个表以及查看两张表结构。 

SHOW TABLES FROM company;

2.将表employees的mobile字段修改到officeCode字段后面。

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

3.将表employees的birth字段改名为employee_birth。

ALTER TABLE employees CHANGE birth employee_birth DATETIME;

4.修改sex字段，数据类型为CHAR(1)，非空约束。

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

5.删除字段note。

ALTER TABLE employees DROP note;

6.增加字段名favoriate_activity，数据类型为VARCHAR(100)。

ALTER TABLE employees ADD favoriate_activity VARCHAR(100);

7.删除表offices。 

ALTER TABLE company.`employees` DROP FOREIGN KEY fk_offices_employees;
DROP TABLE IF EXISTS offices;

8.将表employees名称修改为employees_info。 

RENAME TABLE employees TO employees_info;