mysql-01到03-SQL语句

drop table if exists province;
create table province(
            id  bigint primary key auto_increment ,
            name  varchar(64) ,   
            municipality int default '1' ,
            description varchar(255)
);
insert into province (id,Name,Municipality,Description) values (1,'HuBei',0,'WuHan');
insert into province (id,Name,Municipality,Description) values (2,'ChongQing',1,'ZhiXiaShi');
insert into province (id,Name,Municipality,Description) values (3,'HuNan',0,'ChangSha');
insert into province (id,Name,Municipality,Description) values (4,'BeiJing',1,'ShouDu');
commit；


drop table if exists city;
create table city(
      id  bigint primary key auto_increment ,
      name varchar(255) ,

      pid bigint not null,
      foreign key (pid) references province (id),

      description varchar(255)
);

insert into city (id,name,pid,description) values (1,'WuHan',1,'ShengHui');
insert into city (id,name,pid,description) values (2,'ChongQing',1,'ZhiXiaShi');
insert into city (id,name,pid,description) values (4,'BeiJing',1,'ShouDu');
commit;


drop table if exists county;
 create table county(
       id  bigint primary key auto_increment ,
       name varchar(255) ,

       cid bigint not null ,
       foreign key (cid) references city(id),

       description varchar(255)
);
insert into county (id,name,cid,description) values (1,'CaoDianQu',1,'WuHanShiQu');
insert into county (id,name,cid,description) values (2,'JieFangBei',1,'ChongQingShiQr');



drop table if exists detailaddress;
create table dtailaddress(
       id  bigint primary key auto_increment ,
       address varchar(255) not null,

       cid bigint,
       foreign key(cid) references county(id)

);

drop table if exists faculty;
create table faculty(
       id bigint   primary key  auto_increment ,
       name varchar(255) ,
       createDate  date,
       description varchar(255)
);


drop table if exists major;
create table major(
     id  bigint   primary key  auto_increment,
     code bigint ,
     name varchar(255),
     createDate  date,
     fid bigint not null ,

     foreign key(fid) references faculty(id),


     description varchar(255)
);


drop table if exists calssinfo;
create table classinfo(
    id bigint  primary key auto_increment,
    name varchar(255),
    createDate date  not null,
    studentNo  bigint,  
    mid bigint not null,
    foreign key (mid)  references major(id),
    description varchar(255)
);


drop table if exists students;
create table students(
    id bigint primary key auto_increment,
    studentNo bigint ,
    name varchar(255) ,
    gender char(2) default '1',
    birth date,
    phone varchar(255),
    daid bigint,
    apartment varchar(255),
    desription  varchar(255) 
);

drop table if exists sunbject;
create table subject(
        id bigint  primary key  auto_increment ,
    name varchar(255) ,
    oblgatory int(11) default '1' ,
    status int(11)  default '1',
    createdate char(11),
    description varchar(255)
);

drop table if exists score;
create table score(
         id bigint primary key auto_increment ,
     score int(11), 
     testDate date,
     ispass int(11) default '1',

     studentsid bigint,
     foreign key(studentsid) references students(id),

     subjectid bigint,
     foreign key(subjectid) references subject(id),
     description varchar(255)

);