mysql 练习

http://www.cnblogs.com/wupeiqi/articles/5729934.html   练习

http://www.cnblogs.com/wupeiqi/p/5748496.html  答案

 

 

建表语句

CREATE DATABASE
IF NOT EXISTS myschool;

USE myschool;

DROP TABLE
IF EXISTS class;

CREATE TABLE `class` (
    `cid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `caption` VARCHAR (50) DEFAULT NULL,
    PRIMARY KEY (`cid`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '班级表;';

INSERT INTO class (caption)
VALUES
    ('三年二班'),
    ('一年三班'),
    ('三年一班');

DROP TABLE
IF EXISTS student;

CREATE TABLE `student` (
    `studentNO` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
    `loginPwd` VARCHAR (20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `studentName` VARCHAR (50) NOT NULL COMMENT '学生姓名',
    `gender` enum ('男', '女') DEFAULT '男' COMMENT '性别',
    `gradeId` INT (4) UNSIGNED DEFAULT NULL COMMENT '年级编号',
    `class_id` INT (11) DEFAULT 1 COMMENT '班级编号',
    `phone` VARCHAR (50) DEFAULT NULL COMMENT '联系电话',
    `address` VARCHAR (255) DEFAULT '地址不详' COMMENT '地址',
    `bornDate` datetime DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) DEFAULT NULL COMMENT '邮件账号',
    `identityCard` VARCHAR (18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentNO`),
    UNIQUE KEY `identityCard` (`identityCard`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '学生表';

INSERT INTO student (
    studentName,
    gender,
    class_id
)
VALUES
    ('钢蛋', '女', 1),
    ('铁锤', '女', 1),
    ('山炮', '男', 2);

DROP TABLE
IF EXISTS course;

CREATE TABLE `course` (
    `cid` INT NOT NULL AUTO_INCREMENT,
    `cname` CHAR (3) NOT NULL,
    `teach_id` INT DEFAULT NULL,
    PRIMARY KEY (`cid`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

INSERT INTO course(
    cname,
    teach_id
    
)
VALUES
    ('生物',1),
    ('体育',1),
    ('物理',2);

DROP TABLE
IF EXISTS score;

CREATE TABLE `score` (
    `sid` INT NOT NULL AUTO_INCREMENT,
    `student_id` INT  DEFAULT NULL,
    `corse_id` INT  DEFAULT NULL,
    `number` INT  DEFAULT NULL,
        PRIMARY KEY (`sid`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

INSERT INTO score(
    student_id,
    corse_id,
    number
)
VALUES
    (1,1,60),
    (1,2,59),
    (2,2,100);

DROP TABLE
IF EXISTS teacher;

CREATE TABLE `teacher` (
    `tid` INT NOT NULL AUTO_INCREMENT,
    `tname` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`tid`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

INSERT INTO teacher (tname)
VALUES
    ('波多'),
    ('苍空'),
    ('饭岛');