Mysql建表常用命令

数据库命令

学生(学号，姓名，性别，出生日期，身份证号，家庭住址，联系电话，邮政编码，政治面貌，简历，是否退学，是否休学，楼号，房间号，床位号，班级号)

系(系号，系名，系主任，办公室，电话)

班级(班级号，班级名称，专业，班级人数，入学年份，教室，班主任，班长，系号)

课程(课程号，课程名，学期)

教师(教师号，姓名，性别，出生日期，职称，系号)

宿舍(楼号，房间号，住宿性别，床位数)

选修(学号，课程号，成绩)

讲授(教师编号，课程号)

mysql -uroot -p123456
set names gbk;#解决cmd乱码
show databases;
drop database student;
create database student charset=utf8;
use student;

#系表
DROP TABLE IF EXISTS `Department`;
create table Department
(
DptNumber int primary key,
Dptname char(30) not null,
DptMan char(30) not null,
DptHouseNumber char(30) not null,
DptPhone char(22) not null
)character set = utf8;

ALTER TABLE `Department` CONVERT TO CHARACTER SET utf8;

insert into Department values(001,'计算机','王五','计算机','18392945245');
#学生表
DROP TABLE IF EXISTS `Student`;
create table Student
(
StuSno char(10) primary key,
StuName char(20) not null,
StuGender char(1),
StuBirthday date not null,
StuID char(18) unique,
StuPhone char(12) not null,
StuAddress char(20) not null,
StuPolitics char(10) not null,
StuDptNumber int not null,
FOREIGN KEY (StuDptNumber) REFERENCES Department(DptNumber)
);
insert into Student values('184804002','张三',0,'1999-11-09','411123199911097990','13987012343','北京','共青团员',001);
insert into Student values('184804003','晓儿',1,'1999-01-09',411123199901097960,'13987012993','上海','群众',001);
insert into Student values(‘184804004’,'小小',0,'1999-09-09',411123199909097890,'13987562343','北京','共青团员',001);
select *from student;

#班级表
create table Class
(
ClsSno int primary key,
ClsClass varchar(10) not null,
ClsName char(20) not null,
ClsMajor varchar(10) not null,
ClsNumber varchar(15) not null,
ClsTime date not null,
ClsTeacher nvarchar(20) not null,
ClsDepartment int not null,
foreign key(ClsDepartment) references Department(DptNumber)
);

#老师表
create table Teacher
(
TeacNumber varchar(20) primary key,
TeacName varchar(20) not null,
TeacGender char(1),
TeacDptName char(20) not null,
TeacBrithday date not null,
TeacMajoy varchar(20) not null,
TeacDepartment int not null,
foreign key(TeacDepartment) references Department(DptNumber)
);
#宿舍表
create table Dorm
(
DormNumber char(20) not null,
DormRoom char(20) not null,
DormGender int not null,
DormSum int not null,
primary key(DormNumber,DormRoom)
);