MySQL第一章

CREATE TABLE student1(
   studentNo INT(4) PRIMARY KEY COMMENT'学号',
   loginPwd VARCHAR(20) NOT NULL COMMENT'密码',
   studentName VARCHAR(50) NOT NULL COMMENT'姓名',
   sex CHAR(2) NOT NULL DEFAULT '男' COMMENT'性别',
   gradeID INT(4) UNSIGNED COMMENT'年级编号',
   phone VARCHAR(50) COMMENT'电话',
   address VARCHAR(255) DEFAULT '地址不详' COMMENT'地址',
   bornDate DATETIME COMMENT'出生日期',
   email VARCHAR(60) COMMENT'邮箱',
   identityCard VARCHAR(18) NOT NULL UNIQUE KEY COMMENT'身份证号码'
) COMMENT='学生表'

#修改表名
ALTER TABLE student1 RENAME TO students

#添加字段
ALTER TABLE students ADD nickName VARCHAR(30) NOT NULL

#修改字段
ALTER TABLE students CHANGE nickName liu INT

#删除字段
ALTER TABLE students DROP liu
#创建一个表
CREATE TABLE grade(
        gradeID INT(4) UNSIGNED,
        gradeName VARCHAR(30) NOT NULL
);

#添加主键       
ALTER TABLE grade ADD CONSTRAINT pk_graddeid
PRIMARY KEY grade(gradeID)

#添加外键
ALTER TABLE students ADD CONSTRAINT fk_student1_grade
FOREIGN KEY(gradeID)
REFERENCES grade (gradeID)

CREATE TABLE result(
        studentNo INT(4) PRIMARY KEY COMMENT'学号',
        resultID INT(4) UNSIGNED,
        resultName VARCHAR(50) NOT NULL
);
#添加成绩表主外键
#主键约束:学号，课程编号和日构成组合主键
ALTER TABLE `result` ADD CONSTRAINT pk_sno_sno_date
PRIMARY KEY result(`studentNo`,`subjectNo`,`examDate`)

#外键约束:主表student和从表result通过studentNo字段建立主外键关联
ALTER TABLE students ADD CONSTRAINT fk_result_student1
FOREIGN KEY(`studentsNo`)
REFERENCES `students`(`studentsNo`)