数据库数据基础创建（20191008）_j018数库创建-CSDN博客

本文链接：https://blog.csdn.net/qq_39204060/article/details/102390895

创建数据库 ten

CREATE DATABASE `ten`

使用数据库 ten

USE ten;

- 创建表

创建学生表 student

CREATE TABLE `Student123`(
    `s_id` VARCHAR(20) NOT NULL  COMMENT'学号',
    `s_name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT'姓名',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '' COMMENT'生日',
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '' COMMENT'性别',
    PRIMARY KEY(`s_id`)
);

在这里插入图片描述
将表 “student123” 中的 “s_birth” 列的数据类型改为“date”

ALTER TABLE student123  MODIFY s_birth DATE;

课程表 course

create table `course`(
`c_id` int(4) PRIMARY KEY AUTO_INCREMENT comment'课程编号',
`c_name` varchar(20) comment'课程名字',
`t_id` varchar(20) comment'教授老师'
);

在这里插入图片描述

教师表 teacher

CREATE TABLE `teacher`(
`t_id` VARCHAR(20) COMMENT'教师编号',
`t_name` VARCHAR(20) NOT NULL DEFAULT'无' COMMENT'教师姓名',
PRIMARY KEY(`t_id`)
)COMMENT '教师表';

在这里插入图片描述

成绩表 score

create table `ccore`(
    `s_id` VARCHAR(20) comment'学生标号',
    `c_id`  VARCHAR(20) comment'课程编号',
    `s_score` INT(3) comment'学生成绩',
    PRIMARY KEY(`s_id`,`c_id`)
);

在这里插入图片描述

插入数据

student

INSERT INTO student123 VALUE('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO student123 VALUE('02' , 'QQ浏览器' , '1990-12-21' , '男');
INSERT INTO student123 VALUE('03' , '网易云音乐' , '1990-05-20' , '男');
INSERT INTO student123 VALUE('04' , 'QQ音乐' , '1990-08-06' , '男');
INSERT INTO student123 VALUE('05' , '小米手机' , '1991-12-01' , '女');
INSERT INTO student123 VALUE('06' , 'MySQL' , '1992-03-01' , '女');
INSERT INTO student123 VALUE('07' , 'Tim' , '1989-07-01' , '女');
INSERT INTO student123 VALUE('08' , 'WPS文字' , '1990-01-20' , '女');

course

INSERT INTO course VALUE('01' , '语文' , '02');
INSERT INTO course VALUE('02' , '数学' , '01');
INSERT INTO course VALUE('03' , '英语' , '03');

teacher

INSERT INTO teacher VALUE('01' , 'Tom');
INSERT INTO teacher VALUE('02' , '大山');
INSERT INTO teacher VALUE('03' , '江海');
INSERT INTO teacher(t_id,t_name) VALUES ('05',"问我"),('06',"非法");

score

INSERT INTO ccore(s_id,c_id,s_score)  VALUES
('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);