MySQL基础操作:创建与查询学生及成绩表

需积分: 18 12 下载量 65 浏览量 更新于2024-09-09 收藏 17KB DOCX 举报
在这个MySQL查询练习中,我们首先学习了如何创建和管理数据库表结构。首先,通过`DROPTABLEIFEXISTS`语句删除名为`score`的表,如果它已经存在。然后,我们创建了一个名为`score`的新表,用于存储学生的分数。这个表有五个字段:`id`(主键,自动递增),`stu_id`(学生ID),`c_name`(课程名称,默认为空),`grade`(分数,默认也为NULL),以及一个唯一索引`UNIQUEKEY`用于标识每个记录。 `score`表的定义如下: 1. `id`:整数类型,非空,自动递增。 2. `stu_id`:整数类型,非空,用于关联学生数据。 3. `c_name`:可变长度字符串,可以为NULL,存储课程名称。 4. `grade`:整数类型,可以为NULL,存储成绩。 接下来,我们使用`INSERTINTO`语句向`score`表中插入了一些示例数据,如学生901的计算机课程得分为98,英语得分为80等。 此外,练习还涉及到了另一个表`student`,该表用于存储学生的个人信息。我们先用`DROPTABLEIFEXISTS`同样删除已存在的`student`表,然后创建了一个新的`student`表,包括以下字段: 1. `id`:整数类型,非空,作为主键。 2. `name`:固定长度字符串,不能为空,存储学生姓名。 3. `sex`:可变长度字符串,默认为NULL,表示性别。 4. `birth`:年份类型,默认为NULL,存储出生年份。 5. `department`:可变长度字符串,默认为NULL,表示所在系别。 6. `address`:可变长度字符串,默认为NULL,存储地址。 最后,我们插入了一条学生901的信息,包括姓名、性别、出生年份、系别和地址。 这个练习涵盖了基本的SQL操作,如创建表、删除表、插入数据以及在实际场景中使用表之间的关联。这对于理解关系型数据库管理和数据查询至关重要。通过这些操作,你可以学习如何执行SQL查询来获取特定学生的信息,如所有计算机课程得分超过90分的学生,或者按课程名称分组的平均成绩等。
2018-12-18 上传
create table sailors( sid char(10) primary key, sname char(20), rating int, age int); create table boats( bid char(10) primary key, bname char(20), color char(10)); create table reserves( sid char(10) , bid char(10) , rdate date, primary key(sid,bid,rdate), foreign key (sid) references sailors(sid) on delete cascade, foreign key (bid) references boats(bid) on delete cascade); insert into sailors(sid,sname,rating,age) values("22","dustin",7,45) ("29","brustus",1,33), ("31","lubber",8,56), ("32","andy",8,26), ("58","rusty",10,35), ("64","horatio",7,35), ("71","zorba",10,35), ("74","horatio",9,35), ("85","art",3,26), ("86","john",1,17), ("95","bob",3,64), ("96","frodo",3,26), ("98","tom",3,17); insert into boats(bid,bname,color) values("101","A","red"), ("102","B","green"), ("103","C","blue"), ("104","D","white") ("105","E","red"), ("106","F","blue"), ("107","G","green"); insert into reserves(sid,bid,rdata) values("22","101","2010-01-08"), ("22","102","2010-01-09"), ("29","103","2010-01-09"), ("31","102","2010-02-11"), ("22","104","2010-03-08"), ("22","103","2010-03-10"), ("32","105","2010-03-11"), ("32","106","2010-03-18"), ("32","102","2010-03-19"), ("58","104","2010-03-20"), ("64","105","2010-03-20"), ("95","101","2010-04-02"), ("85","102","2010-04-05"), ("22","101","2010-04-07"), ("22","105","2010-05-01"), ("22","106","2010-06-18"), ("22","107","2010-07-09"), ("31","106","2010-08-06"), ("32","105","2010-08-06"), ("29","104","2010-08-07"), ("64","103","2010-09-05"), ("58","102","2010-09-09"), ("64","104","2010-11-03"), ("64","105","2010-11-04"), ("31","106","2010-12-0