Mysql 建表与插入数据示例

drop table if exists student_info;
create table student_info(
sno varchar(10) primary key ,
sname varchar(10),
sage int(3),
ssex char(1),
school varchar(10), 
class varchar(10),
address varchar(20)
)engine = Innodb default charset = utf8mb4 comment='学生表';


insert into student_info (sno,name,sex,age,school,class,address) values(
'001','赵光明','男',15,'第一中学','初三','人民路1号');
insert into student_info (sno,name,sex,age,school,class,address) values(
'002','钱仁义','男',15,'第一中学','初三','人民路2号');
insert into student_info (sno,name,sex,age,school,class,address) values(
'003','孙解放','男',15,'第一中学','初三','人民路3号');
insert into student_info (sno,name,sex,age,school,class,address) values(
'004','李建设','男',15,'第一中学','初三','人民路4号');
insert into student_info (sno,name,sex,age,school,class,address) values(
'005','周前进','男',15,'第一中学','初三','人民路5号');
insert into student_info (sno,name,sex,age,school,class,address) values(
'006','吴胜利','男',15,'第一中学','初三','人民路6号');
insert into student_info (sno,name,sex,age,school,class,address) values(
'007','郑国强','男',15,'第一中学','初三','人民路7号');
insert into student_info (sno,name,sex,age,school,class,address) values(
'008','王忠诚','男',15,'第一中学','初三','人民路8号');
insert into student_info (sno,name,sex,age,school,class,address) values(
'009','张三疯','男',15,'第一中学','初三','人民路9号');
insert into student_info (sno,name,sex,age,school,class,address) values(
'010','陈二狗','男',15,'第一中学','初三','人民路10号');
insert into student_info (sno,name,sex,age,school,class,address) values(
'011','程小鸭','男',15,'第一中学','初三','人民路11号');

 

drop table if exists course_info;
create table course_info(
sno varchar(10),
name varchar(10),
course varchar(10),
teacher varchar(20) 
)engine = Innodb default charset = utf8mb4 comment='课程表';


insert into course_info values(
'001','赵光明','语文','黄老师'),
('002','钱仁义','语文','黄老师'),
('003','孙解放','语文','黄老师'),
('004','李建设','语文','黄老师'),
('005','周前进','语文','黄老师'),
('006','吴胜利','语文','黄老师'),
('007','郑国强','语文','黄老师'),
('008','王忠诚','语文','黄老师'),
('009','张三疯','语文','黄老师'),
('010','陈二狗','语文','黄老师'),
('011','程小鸭','语文','黄老师');

 

 

drop table if exists score_info;
create table score_info(
sno varchar(10),
name varchar(10),
course varchar(10),
score varchar(20)
)engine = Innodb default charset = utf8mb4 comment='成绩表';


INSERT INTO score_info (sno,name,course,score) VALUES 
('001','赵光明','语文','90')
,('002','钱仁义','语文','89')
,('003','孙解放','语文','85')
,('004','李建设','语文','92')
,('005','周前进','语文','96')
,('006','吴胜利','语文','94')
,('007','郑国强','语文','83')
,('008','王忠诚','语文','80')
,('009','张三疯','语文','79')
,('010','陈二狗','语文','75')
;
INSERT INTO score_info (sno,name,course,score) VALUES 
('011','程小鸭','语文','83')
,('001','赵光明','数学','83')
,('002','钱仁义','数学','84')
,('003','孙解放','数学','89')
,('004','李建设','数学','95')
,('005','周前进','数学','78')
,('006','吴胜利','数学','76')
,('007','郑国强','数学','71')
,('008','王忠诚','数学','90')
,('009','张三疯','数学','89')
;
INSERT INTO score_info (sno,name,course,score) VALUES 
('010','陈二狗','数学','94')
,('011','程小鸭','数学','96')
,('001','赵光明','英语','88')
,('002','钱仁义','英语','94')
,('003','孙解放','英语','89')
,('004','李建设','英语','83')
,('005','周前进','英语','65')
,('006','吴胜利','英语','85')
,('007','郑国强','英语','73')
,('008','王忠诚','英语','95')
;
INSERT INTO score_info (sno,name,course,score) VALUES 
('009','张三疯','英语','92')
,('010','陈二狗','英语','91')
,('011','程小鸭','英语','87')
;

 

 

create table emp_info (
eno varchar(3) primary key comment '职工编号',
ename varchar(5) comment '职工姓名',
edept varchar(5) comment '部门',
birthday varchar(10) comment '出生日期',
hiredate varchar(10) comment '入职日期',
salary int(4) comment '薪水'
) engine = Innodb default charset = utf8mb4 comment='职工信息表';


insert into emp_info values
 ('111','赵光明','生产部','1990-01-01','2019-05-28','5000'),
('112','钱仁义','生产部','1993-04-07','2019-05-28','5600'),
('113','张三疯','生产部','1992-03-08','2019-05-28','5900'),
('114','李建设','生产部','1991-05-09','2019-05-28','6000'),
('115','周前进','销售部','1990-11-24','2019-10-28','4500'),
('116','吴胜利','销售部','1989-09-11','2019-10-28','4600'),
('117','陈二狗','销售部','1989-12-31','2019-10-28','4400'),
('118','王忠诚','销售部','1995-07-22','2019-10-28','4500'),
('119','孙解放','后勤部','1994-10-08','2019-10-28','4000'),
('120','程小鸭','后勤部','1996-04-21','2020-03-12','4300'),
('121','张三疯','后勤部','1987-09-25','2020-03-12','4100'),
('122','陈二狗','人力部','1986-12-14','2020-03-12','3800'),
('123','程小鸭','人力部','1990-06-21','2021-06-10','4000'),
('124','哈士奇','财务部','1992-04-18','2021-06-10','4200'),
('125','贾精忠','财务部','1993-02-13','2021-06-10','3900');