MySQL练习01

1. 创建数据库db_practices02，将SQL语句写在下面表格中

CREATE DATABASE db_practices02;

2、在数据库db_practices02中创建学生信息表tb_student，表的字段信息如下表所示（注意字段的属性），将SQL语句写在下面表格中

USE db_practices02; CREATE TABLE tb_student (          sno VARCHAR(20) NOT NULL PRIMARY KEY COMMENT'学号',          sname VARCHAR(20) NOT NULL COMMENT'姓名',          ssex VARCHAR(20) NOT NULL COMMENT'性别',          sbirthday DATETIME COMMENT'出生日期',          class VARCHAR(20) COMMENT'班级' );

3、在数据库db_practices02中创建教师信息表tb_teacher，表的字段信息如下表所示（注意字段的属性），将SQL语句写在下面表格中

USE db_practices02; CREATE TABLE tb_teacher (          tno VARCHAR(20) NOT NULL PRIMARY KEY COMMENT'教师编号',          tname VARCHAR(20) NOT NULL COMMENT'姓名',          tsex VARCHAR(20) NOT NULL COMMENT'性别',          tbirthday DATETIME COMMENT'出生日期',          prof VARCHAR(20) COMMENT'职称',          depart VARCHAR(20) NOT NULL COMMENT'科系' );

4、在数据库db_practices02中创建课程表tb_course，表的字段信息如下表所示（注意字段的属性），将SQL语句写在下面表格中

USE db_practices02; CREATE TABLE tb_course (          cno VARCHAR(20) NOT NULL PRIMARY KEY COMMENT'课程编号',          cname VARCHAR(20) NOT NULL COMMENT'课程名称',          tno VARCHAR(20) NOT NULL COMMENT'授课教师编号',          CONSTRAINT FK_TNO FOREIGN KEY(tno) REFERENCES tb_teacher(tno) );

5、在数据库db_practices02中创建成绩表tb_score，表的字段信息如下表所示（注意字段的属性），将SQL语句写在下面表格中

USE db_practices02; CREATE TABLE tb_score (          sno VARCHAR(20) NOT NULL COMMENT'学生学号',          cno VARCHAR(20) NOT NULL COMMENT'课程编号',          gegree DECIMAL(4,1) COMMENT'成绩',          PRIMARY KEY(sno DESC,cno),          CONSTRAINT FK_SNO FOREIGN KEY(sno) REFERENCES tb_student(sno),          CONSTRAINT FK_CNO FOREIGN KEY(cno) REFERENCES tb_course(cno) );

6、向tb_student表中添加数据，数据如下所示，将SQL语句写在下面表格中

INSERT INTO tb_student (sno,sname,ssex,sbirthday,class) VALUES ('101','李军','男','1976-02-20','95033'), ('102','陆君','男','1974-06-03','95031'), ('105','匡明','男','1975-10-02','95031'), ('107','王丽','女','1976-01-23','95033'), ('108','曾华','男','1977-09-01','95033'), ('109','李军','女','1975-02-10','95031');

7、向tb_teacher表中添加数据，数据如下所示，将SQL语句写在下面表格中

INSERT INTO tb_teacher (tno,tname,tsex,tbirthday,prof,depart) VALUES ('804','李诚','男','1958-12-02 11:28:21','副教授','计算机系'), ('825','王萍','女','1972-05-05 00:00:00','助教','计算机系'), ('831','刘冰','女','1977-08-14 00:00:00','助教','电子工程系'), ('856','张旭','男','1969-03-12 00:00:00','讲师','电子工程系');

8、向tb_course表中添加数据，数据如下所示，将SQL语句写在下面表格中

INSERT INTO tb_course(cno,cname,tno) VALUE ('3-105','计算机导论','825'), ('3-245','操作系统','804'), ('6-166','数字电路','856'), ('9-888','高等数学','831');

9、向tb_score表中添加数据，数据如下所示，将SQL语句写在下面表格中

INSERT INTO tb_score(sno,cno,degree) VALUE ('103','3-245','86.0'), ('105','3-245','75.0'), ('109','3-245','68.0'), ('103','3-105','92.0'), ('105','3-105','88.0'), ('109','3-105','76.0'), ('101','3-105','64.0'), ('107','3-105','91.0'), ('108','3-105','78.0'), ('101','6-166','85.0'), ('107','6-166','79.0'), ('108','6-166','81.0');