MySQL高级学习（9）存储过程练习

1. 建表

# 新建库

create database bigData;

use bigData;

#1 建表dept

CREATE TABLE dept(  

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,   

dname VARCHAR(20) NOT NULL DEFAULT "",  

loc VARCHAR(13) NOT NULL DEFAULT ""  

) ENGINE=INNODB DEFAULT CHARSET=UTF8 ;  

#2 建表emp

CREATE TABLE emp  

(  

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  

empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/  

ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/  

job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/  

mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/  

hiredate DATE NOT NULL,/*入职时间*/  

sal DECIMAL(7,2) NOT NULL,/*薪水*/  

comm DECIMAL(7,2) NOT NULL,/*红利*/  

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/  

)ENGINE=INNODB DEFAULT CHARSET