526互联

第2次作业-SQL语句的基本使用

发布时间 2023-12-01 11:25:30作者: 。。刘
这个作业属于哪个课程 https://edu.cnblogs.com/campus/uzz/cs3
这个作业要求在哪里 https://edu.cnblogs.com/campus/uzz/cs3/homework/13041
这个作业的目标 <第2次作业-SQL语句的基本使用>

1.使用SQL语句创建数据库studentsdb.

create database studentsdb;

运行结果

2.使用SQL语句选择studentsdb为当前使用数据库。

use studentsdb;

运行结果

3.使用SQL语句在studentsdb数据库创建数据表student_info、curriculum、grade。

创建student_info表

create table student_info(
    学号 char(4) not null primary key,
    姓名 char(8) not null,
    性别 char(2),
    出生日期 date,
    家庭住址 varchar(50)
    );

运行结果

创建curriculum表

create table curriculum(
    课程编号 char(4) not null primary key,
    课程名称 varchar(50),
    学分 int
    );

运行结果

创建grade表

create table grade(
    学号 char(4) not null,
    课程编号 char(4) not null,
    分数 int,
    primary key(学号,课程编号)
    )engine =Innodb default charset = utf8mb4;

运行结果

4.使用SQL语句INSERT向studentsdb数据库的student_info、curriculum、grade表插入数据

向student_info中插入数据

insert into student_info (学号,姓名,性别,出生日期,家庭住址)
    values("0001","张青平","男",'2000-10-01',"衡阳市东风路77号"),
    ("0002","刘东阳","男",'1998-12-09',"东阳市八一北路33号"),
    ("0003","马晓夏","女",'1995-05-12',"长岭市五一路763号"),
    ("0004","钱忠理","男",'1994-09-23',"滨海市洞庭大道279号"),
    ("0005","孙海洋","男",'1995-04-03',"长岛市解放路27号"),
    ("0006","郭小斌","男",'1997-11-10',"南山市红旗路113号"),
    ("0007","肖月玲","女",'1996-12-07',"东方市南京路11号"),
    ("0008","张玲珑","女",'1997-12-24',"滨江市新建路97号");

运行结果


向curriculum插入数据

insert into curriculum (课程编号,课程名称,学分)values
    ("0001","计算机应用基础",2),
    ("0002","C语言程序设计",2),
    ("0003","数据库原理及应用",2),
    ("0004","英语",4),
    ("0005","高等数学",4);

运行结果


向grade表插入数据

insert into grade (学号,课程编号,分数)values
("0001","0001",80),
("0001","0002",91),
("0001","0003",88),
("0001","0004",85),
("0001","0005",77),
("0002","0001",73),
("0002","0002",68),
("0002","0003",80),
("0002","0004",79),
("0002","0005",73),
("0003","0001",84),
("0003","0002",92),
("0003","0003",81),
("0003","0004",82),
("0003","0005",75);

运行结果