MSQL基本增删改语句汇总练习

USE work; 2 3 /*创建选修数据表*/ 4 DROP TABLE IF EXISTS sc; 5 CREATE TABLE sc( 6 sno CHAR(8) COMMENT ‘学号‘, 7 cno CHAR(3) COMMENT ‘课程号‘, 8 grade 　　　　SMALLINT COMMENT ‘成绩‘ 9 ); 10 11 12 /*创建学生数据表*/ 13 DROP TABLE IF EXISTS student; 14 CREATE TABLE student( 15 sno CHAR(8) COMMENT‘学号‘, 16 sname VARCHAR(20) COMMENT ‘姓名‘, 17 sgender CHAR(2) COMMENT ‘性别‘, 18 sage INT COMMENT ‘年龄‘, 19 sdept VARCHAR(30) 　　 COMMENT ‘所系‘, 20 sedat VARCHAR(30) COMMENT ‘入学日期‘ 21 ); 22 23 /*创建课程数据表*/ 24 DROP TABLE IF EXISTS course; 25 CREATE TABLE course( 26 cno CHAR(3) COMMENT ‘课程号‘, 27 cname VARCHAR(30) COMMENT ‘课程名‘, 28 ccredit SMALLINT COMMENT ‘学分‘ 29 ); 30 31 32 /*插入数据*/ 33 INSERT INTO student(sno,sname,sgender,sage,sdept,sedat) 34 VALUES 35 (‘20131001‘,‘王力‘,‘男‘,19,‘计算机‘,‘2013/9/11‘), 36 (‘20131002‘,‘张红‘,‘女‘,20,‘计算机‘,‘2013/9/11‘), 37 (‘20132001‘,‘孙国庆‘,‘男‘,18,‘信息‘,‘2013/9/11‘); 38 INSERT INTO course(cno,cname,ccredit) 39 VALUES 40 (‘101‘,‘数据结构‘,4), 41 (‘102‘,‘数据库‘,3), 42 (‘103‘,‘C语言程序设计‘,6); 43 INSERT INTO sc(sno,cno,grade) 44 VALUES 45 (‘20131001‘,‘101‘,‘90‘), 46 (‘20131001‘,‘102‘,‘76‘), 47 (‘20131001‘,‘103‘,‘88‘), 48 (‘20131002‘,‘101‘,‘56‘); 49 50 /*修改表结构*/ 51 ALTER TABLE student ADD sphone char(11); 52 ALTER TABLE student MODIFY COLUMN sphone char(20); 53 ALTER TABLE student DROP COLUMN sphone; 54 70 71 /*对数据表添加修改约束*/ 72 -- 添加主键约束 73 ALTER TABLE student ADD CONSTRAINT pk_student PRIMARY KEY(sno); 74 ALTER TABLE course ADD CONSTRAINT pk_course PRIMARY KEY(cno); 75 ALTER TABLE sc ADD CONSTRAINT pk_sc PRIMARY KEY(sno,cno); 76 77 -- 当主表中的数据删除，从表的对应行也删除 78 -- 添加外键约束 79 ALTER TABLE sc ADD CONSTRAINT fk_student_sc FOREIGN KEY(sno) REFERENCES student(sno) ON DELETE CASCADE; 80 ALTER TABLE sc ADD CONSTRAINT fk_course_sc FOREIGN KEY(cno) REFERENCES course(cno) ON DELETE CASCADE; 81 82 -- 添加非空约束 83 ALTER TABLE student MODIFY COLUMN sname VARCHAR(20) NOT NULL; 84 ALTER TABLE course MODIFY COLUMN cname VARCHAR(30) NOT NULL; 85 86 -- 添加唯一约束 87 -- ALTER TABLE course ADD UNIQUE(cname); 88 ALTER TABLE course ADD CONSTRAINT u_cname UNIQUE(cname); 89 90 -- 添加CHECK约束 91 ALTER TABLE student ADD CONSTRAINT ck_sgender CHECK(sgender in(‘男‘,‘女‘)); 92 ALTER TABLE student ADD CONSTRAINT ck_sage CHECK(sage BETWEEN 15 AND 35); 93 94 /*删除刚刚添加的约束*/ 95 96 -- 先删除外键约束才能删除主表的主键约束 97 ALTER TABLE sc DROP FOREIGN KEY fk_student_sc; 98 ALTER TABLE sc DROP FOREIGN KEY fk_course_sc; 99 100 ALTER TABLE student DROP PRIMARY KEY; 101 ALTER TABLE course DROP PRIMARY KEY; 102 ALTER TABLE sc DROP PRIMARY KEY; 103 104 ALTER TABLE student MODIFY COLUMN sname VARCHAR(20) NULL; 105 ALTER TABLE course MODIFY COLUMN cname VARCHAR(30) NULL; 106 107 ALTER TABLE course DROP INDEX u_cname; 108 -- 不能删除，报错ALTER TABLE course DROP INDEX(cname); 109 110 -- mysql数据库不支持check约束，添加也无效，删除更不支持 111 /*ALTER TABLE student DROP CHECK ck_sgender; 112 ALTER TABLE student DROP CHECK ck_sage;*/ 113 114 115 116 SELECT * FROM student; 117 SELECT * FROM course; 118 SELECT * FROM sc;

 

MSQL基本增删改语句汇总练习

标签：div   har   报错   删除   asc   表结构   日期   class   c语言程序