Question

在数据库中创建学生表

Answer 1

问：在数据库中创建学生表

答：一、创建学生管理数据库sql CREATE DATABASE XXdb ON PRIMARY ( NAME = XXdb_data, FILENAME = 'C:\TEST\XXdb_data.mdf', SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ) LOG ON ( NAME = XXdb_log, FILENAME = 'C:\TEST\XXdb_log.ldf', SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 5MB );

答：二、创建学生、课程和成绩三个数据库表sqlCopy codeUSE XXdb;-- 学生表CREATE TABLE student( stu_id CHAR(6) PRIMARY KEY, stu_name VARCHAR(10) NOT NULL, stu_gender CHAR(2) NOT NULL, stu_birthdate DATE, stu_address VARCHAR(100));-- 课程表CREATE TABLE course( course_id CHAR(3) PRIMARY KEY, course_name VARCHAR(20) NOT NULL, course_property VARCHAR(10) NOT NULL);

答：- 成绩表CREATE TABLE score( stu_id CHAR(6) NOT NULL, course_id CHAR(3) NOT NULL, stu_score INT, PRIMARY KEY(stu_id, course_id), FOREIGN KEY(stu_id) REFERENCES student(stu_id), FOREIGN KEY(course_id) REFERENCES course(course_id));

答：三、设置合理的主键和check约束

答：USE XXdb;-- 学生表：主键为学号ALTER TABLE student ADD CONSTRAINT PK_stu_id PRIMARY KEY(stu_id);-- 课程表：主键为课程号ALTER TABLE course ADD CONSTRAINT PK_course_id PRIMARY KEY(course_id);-- 成绩表：主键为学号和课程号，分别引用学生表和课程表ALTER TABLE score ADD CONSTRAINT PK_score PRIMARY KEY(stu_id, course_id);ALTER TABLE score ADD CONSTRAINT FK_score_stu_id FOREIGN KEY(stu_id) REFERENCES student(stu_id);ALTER TABLE score ADD CONSTRAINT FK_score_course_id FOREIGN KEY(course_id) REFERENCES course(course_id);

答：-- 学生表：为性别字段创建check约束ALTER TABLE student ADD CONSTRAINT CHK_stu_gender CHECK(stu_gender IN('男', '女'));

答：四、向数据库表中添加记录

答：USE XXdb;-- 添加学生记录INSERT INTO student VALUES('202201', '赵亮', '男', '2000-03-09', '观山湖区一');INSERT INTO student VALUES('202202', '郭婷婷', '女', '2001-04-20', '花溪区');INSERT INTO student VALUES('202203', '张海', '男', '2002-11-13', '贵安新区一');-- 添加课程记录INSERT INTO course VALUES('C01', 'C语言程序设计', '必修课');INSERT INTO course VALUES('C02', '演讲与口才', '选修课');-- 添加成绩记录INSERT INTO score VALUES('202201', 'C01', 96);INSERT INTO score VALUES('202202', 'C01', 84);以上是我的回复，希望对您有所帮助。