MySQL：⼿写的SQL语句.可直接执⾏---学⽣表,⽼师表，课程表，班级表，成绩表MySQL: ⼿写的SQL语句.可直接执⾏
学⽣表,⽼师表，课程表，班级表，成绩表
删除 表 如果 存在 表名
drop table if EXISTS tbl_score; – 删除成绩(先删除外键表再删主表)
drop table if EXISTS tbl_student; – 删除学⽣
drop table if EXISTS tbl_class; – 删除班级
drop table if EXISTS tbl_teacher; – 删除⽼师
drop table if EXISTS tbl_course; – 删除课程
create table 表名 (
列名1 类型 约束1 约束2 ,
列名2 类型 约束,
…
)
常见约束/列的限制条件
primary key ：主键特点：唯⼀不能重复!
auto_incement: ⾃增,⾃增列必须为int类型
表中必须有主键，为了防⽌未设置主键，习惯添加整数⾃增的id列为虚拟主键
default: 默认
not null ⾮空
unique : 唯⼀约束!
常⽤的数据类型:
整数
varchar(长度) 可变长度 常⽤
char(N) 固定字符长度
浮点类型/⼩数: float/double
date.datetime, time
班级对象(tbl_class) : 唯⼀标识id，班级编号,⼈数
create table tbl_class(
id int primary key auto_increment COMMENT ‘注释主键’, – 主键⾃增
cname varchar(50),
size int # 班级⼈数
mysql面试题sql语句多表联查);
插⼊ 到 表名(列1名，列2名，列3名) 值(列1值，列2值，列3值…)
insert into 表名(…) values(…)
insert into tbl_class values(default,‘⼈⼯智能⼀班’,30); # 不制定列名，必须为所有列都插⼊数据，主键⾃增⽤default代替!
insert into tbl_class values(default,'⼈⼯智能⼀班',30);
insert into tbl_class values(default,'数据分析⼀班',30);
insert into tbl_class values(default,'爬⾍⼀班',30);
insert into tbl_class values(default,'⼤数据',30);
学⽣对象(student): 唯⼀标识id，姓名，性别，年龄,⽣⽇，电话，地址，邮箱，班级编号
create table tbl_student(
id int primary key auto_increment, #id⾃增
sname varchar(50) not null, – ⾮空
minheight兼容sex char(2), – 2个字符
age int ,
birthday date, – ⽇期年⽉⽇
phone VARCHAR(11), – 字符串(必须为字符串，因为项⽬中经常对⼿机号进⾏截取，拼接等操作) email varchar(50),wsdl和soap的区别
addr varchar(100),
c_id int, – 外键类型必须和主键类型相同! 学⽣需要班级名，实际保存的是班级外键(班级id)
foreign key(c_id) references tbl_class(id) – 外键 (外键列名) 参考 主表(主键)
);
插⼊数据 (如果班级id不存, 直接报错!)
必须先删除从表，再删除主! 学⽣参考班级, 学⽣就是主表!
insert into tbl_student values(default,'张A','男',20,'1980-04-03','188********','zhanga@163','北京',1); insert into tbl_student values(default,'张B','男',20,'1980-04-03','188********','zhanga@163',
'北京',1); insert into tbl_student values(default,'赵C','⼥',21,'1980-04-03','188********','zhanga@163','天津',1); insert into tbl_student values(default,'赵D','⼥',21,'1980-04-03','188********','zhanga@163','天津',1); insert into tbl_student values(default,'钱E','⼥',21,'1980-04-03','188********','zhanga@163','上海',1); insert into tbl_student values(default,'钱F','男',17,'1980-04-03','188********','zhanga@163','上海',1); insert into tbl_student values(default,'孙G','男',16,'1980-04-03','188********','zhanga@163','杭州',1); insert into tbl_student values(default,'孙H','⼥',17,'1980-04-03','188********','zhanga@163','杭州',2); insert into tbl_student values(default,'李I','男',26,'1980-04-03','188********','zhanga@qq','苏州',2);
insert into tbl_student values(default,'李J','⼥',34,'1980-04-03','188********','zhanga@163','苏州',2); insert into tbl_student values(default,'周K','⼥',40,'1980-04-03','188********','zhanga@163','四川',2); insert into tbl_student values(default,'周L','男',32,'1980-04-03','188********','zhanga@163','四川',2); insert into tbl_student values(default,'吴M','⼥',16,'1980-04-03','188********','zhanga@163','⼴州',2); insert into tbl_student values(default,'吴N','⼥',20,'1980-04-03','188********','zhanga@163','⼴州',3); insert into tbl_student values(default,'郑O','男',22,'1980-04-03','188********','zhanga@163','重庆',4); insert into tbl_student values(default,'郑P','男',23,'1980-04-03','188********','zhanga@163','重庆',4); ins
ert into tbl_student values(default,'王Q','⼥',24,'1980-04-03','188********','zhanga@163','北京',1); insert into tbl_student values(default,'王R','男',28,'1980-04-03','188********','zhanga@163','天津',1); insert into tbl_student values(default,'冯S','⼥',39,'1980-04-03','188********','zhanga@163','⼭东',1); insert into tbl_student values(default,'冯T','男',20,'1980-04-03','188********','zhanga@163','⼭东',2); insert into tbl_student values(default,'陈U','⼥',25,'1980-04-03','188********','zhanga@163','⼭西',2); insert into tbl_student values(default,'陈V','男',28,'1980-04-03','188********','zhanga@163','⼭西',3); insert into tbl_student values(default,'楚W','⼥',28,'1980-04-03','188********','zhanga@163','⼭西',4); insert into tbl_student values(default,'楚X','男',29,'1980-04-03','188********','zhanga@qq','河北',1);
insert into tbl_student values(default,'魏Y','⼥',22,'1980-04-03','188********','zhanga@163','河北',2); insert into tbl_student values(default,'魏Z','男',21,'1980-04-03','188********','zhanga@163','河南',3);
#select * from tbl_student;
⽼师对象(teacher): 唯⼀标识id，姓名，性别，年龄，电话
create table tbl_teacher(
id int primary key auto_increment,
tname varchar(50)not null,
sex char(2),
age int,
phone varchar(11)
);
insert into tbl_teacher values(default,'成龙','男',58,'1766666601');
insert into tbl_teacher values(default,'黎明','男',58,'1766666601');
insert into tbl_teacher values(default,'孙俪','⼥',35,'1766666601');
insert into tbl_teacher values(default,'范冰冰','⼥',35,'1766666601');
select * from tbl_teacher
课程对象(course): 唯⼀标识id，课程名，开课时间，课时数量
create table tbl_course(
id int primary key auto_increment,
cname varchar(11)not null unique,#⾮空，唯⼀
start_time datetime,
count int
);
insert into tbl_course values(default,'⼈⼯智能AI',now(),80);-- now()表⽰⾃带⽇期函数,
insert into tbl_course values(default,'数据分析',now(),70);
insert into tbl_course values(default,'爬⾍',now(),60);
insert into tbl_course values(default,'⼤数据',now(),90);
成绩对象(score) : id，分数，课程名, 学⽣名
create table tbl_score(
id int primary key auto_increment,
score float(4,1),-- 4位, ⼩数点保留1位
s_id int not null,
c_id int not null,
foreign key(s_id)references tbl_student(id),#建表时指定外键
免费网站电影电视免费在线观看foreign key(c_id)references tbl_course(id)#建表时指定外键
);
修改表表名增加关系关系名外键 (外键列) 参考主表(主键) #alter table tbl_score add constraint FK_scoreid_courseid foreign key(c_id) references tbl_course(id);
第1个⼈，第4门课
insert into tbl_score values(default,58,1,1);
insert into tbl_score values(default,89,1,1);css怎么添加背景音乐
insert into tbl_score values(default,78,1,2);
insert into tbl_score values(default,65,1,3);
insert into tbl_score values(default,90,1,4);
第2个⼈4门课
insert into tbl_score values(default,90,2,1);
insert into tbl_score values(default,54,2,2);
insert into tbl_score values(default,89,2,2);
insert into tbl_score values(default,87,2,3);
insert into tbl_score values(default,92,2,4);
第3个⼈3门课mvc是什么免疫疫苗
insert into tbl_score values(default,90,3,1);
insert into tbl_score values(default,89,3,2);
insert into tbl_score values(default,87,3,3);
第4个⼈3门课
insert into tbl_score values(default,87,4,1);
insert into tbl_score values(default,86,4,2);
insert into tbl_score values(default,90,4,3);
insert into tbl_score values(default,67,4,4);
第5个⼈4门课
insert into tbl_score values(default,98,5,1);
insert into tbl_score values(default,90,5,2);
insert into tbl_score values(default,78,5,3);
insert into tbl_score values(default,98,5,4);
select * from tbl_score;
删除主键和外键
alter table 表名 drop foreign key 外键（区分⼤⼩写）; alter table 表名 drop primary key; alter table tbl_score drop foreign key
FK_scoreid_courseid
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论