50个SQL语句(MySQL版)建表插入数据

  • Z时代
  • 2024-01-10
  • 分类：综合

database

本学期正在学习数据库，前段时间老师让我们做一下50个经典SQL语句，当时做的比较快，有一些也是百度的，自我感觉理解的不是很透彻。

所以从本篇随笔开始，我将进行50个经典SQL语句的复盘，加深理解。

答案仅供参考，不一定完全正确，若发现错误或有更好的，欢迎评论，互相交流，一起成长！！！

--------------------------表结构--------------------------

student(StuId,StuName,StuAge,StuSex) 学生表

teacher(TId,Tname) 教师表

course(CId,Cname,C_TId) 课程表

sc(SId,S_CId,Score) 成绩表

----------------------------------------------------------

建表：

createdatabase MySchool characterset "utf8mb4";
use MySchool;
createtable student
(
    StuId varchar(5) primarykey,
    StuName varchar(10) notnull,
    StuAge int,
    StuSex char(2) notnull
);
createtable teacher
(
    TId varchar(3) primarykey,
    Tname varchar(10) notnull
);
createtable course
(
    CId varchar(3) primarykey,
    Cname varchar(20) notnull,
    C_TId varchar(3) notnull,
FOREIGNKEY (C_TId) REFERENCES teacher(TId)
);
createtable sc
(
    SId varchar(5) notnull,
FOREIGNKEY (SId) REFERENCES student(StuId),
    S_CId varchar(3) notnull,
FOREIGNKEY (S_CId) REFERENCES course(CId),
    Score float
);

插入数据：

insertinto student value("1000","张无忌",18,"男"),
("1001","周芷若",19,"女"),
("1002","杨过",19,"男"),
("1003","赵敏",18,"女"),
("1004","小龙女",17,"女"),
("1005","张三丰",18,"男"),
("1006","令狐冲",19,"男"),
("1007","任盈盈",20,"女"),
("1008","岳灵珊",19,"女"),
("1009","韦小宝",18,"男"),
("1010","康敏",17,"女"),
("1011","萧峰",19,"男"),
("1012","黄蓉",18,"女"),
("1013","郭靖",19,"男"),
("1014","周伯通",19,"男"),
("1015","瑛姑",20,"女"),
("1016","李秋水",21,"女"),
("1017","黄药师",18,"男"),
("1018","李莫愁",18,"女"),
("1019","冯默风",17,"男"),
("1020","王重阳",17,"男"),
("1021","郭襄",18,"女");
insertinto teacher value("001","姚明"),
("002","叶平"),
("003","叶开"),
("004","孟星魂"),
("005","独孤求败"),
("006","裘千仞"),
("007","裘千尺"),
("008","赵志敬"),
("009","阿紫"),
("010","郭芙蓉"),
("011","佟湘玉"),
("012","白展堂"),
("013","吕轻侯"),
("014","李大嘴"),
("015","花无缺"),
("016","金不换"),
("017","乔丹");
insertinto course value("001","企业管理","002"),
("002","马克思","008"),
("003","UML","006"),
("004","数据库","007"),
("005","逻辑电路","006"),
("006","英语","003"),
("007","电子电路","005"),
("008","思想概论","004"),
("009","西方哲学史","012"),
("010","线性代数","017"),
("011","计算机基础","013"),
("012","AUTO CAD制图","015"),
("013","平面设计","011"),
("014","Flash动漫","001"),
("015","Java开发","009"),
("016","C#基础","002"),
("017","Oracl数据库原理","010");
insertinto sc value("1001","003",90),
("1001","002",87),
("1001","001",96),
("1001","010",85),
("1002","003",70),
("1002","002",87),
("1002","001",42),
("1002","010",65),
("1003","006",78),
("1003","003",70),
("1003","005",70),
("1003","001",32),
("1003","010",85),
("1003","011",21),
("1004","007",90),
("1004","002",87),
("1005","001",23),
("1006","015",85),
("1006","006",46),
("1006","003",59),
("1006","004",70),
("1006","001",99),
("1007","011",85),
("1007","006",84),
("1007","003",72),
("1007","002",87),
("1008","001",94),
("1008","012",85),
("1008","006",32),
("1009","003",90),
("1009","002",82),
("1009","001",96),
("1009","010",82),
("1009","008",92),
("1010","003",90),
("1010","002",87),
("1010","001",96),
("1011","009",24),
("1011","009",25),
("1012","003",30),
("1013","002",37),
("1013","001",16),
("1013","007",55),
("1013","006",42),
("1013","012",34),
("1000","004",16),
("1002","004",55),
("1004","004",42),
("1008","004",34),
("1013","016",86),
("1013","016",44),
("1000","014",75),
("1002","016",100),
("1004","001",83),
("1008","013",97);

以上是 50个SQL语句(MySQL版)建表插入数据 的全部内容， 来源链接： utcz.com/z/533421.html

回到顶部