Mysql数据库常用语句——创建数据库/表

1、创建一个数据库empdb

代码示例：

CREATE DATABASE empdb CHARACTER SET utf8;

2、设计2张表

department部门表：id,dep_name,dep_location

employee员工表：eid,ename,age,dep_id

代码示例：

-- 部门表

-- PRIMARY KEY 主建 AUTO_INCREMENT自增约束

CREATE TABLE department(

                     id INT PRIMARY KEY AUTO_INCREMENT,

                     dep_name VARCHAR(30),

                     dep_location VARCHAR(30));

-- 员工表

-- 外键约束 CONSTRAINT fk_eid FOREIGN KEY (dept_id) REFERENCES department(id))

CREATE TABLE employee(

                     eid INT PRIMARY KEY AUTO_INCREMENT,

                     ename VARCHAR(20),

                     age INT,

                     dept_id INT,

                     CONSTRAINT fk_eid FOREIGN KEY (dept_id) REFERENCES department(id));

-- 删除外键约束

ALTER TABLE employee DROP FOREIGN KEY fk_eid;

-- 已存在添加外键约束

ALTER TABLE employee ADD CONSTRAINT fk_eid FOREIGN KEY (dept_id) REFERENCES department(id);