MySQL语句

#删除表DROP TABLE my_teacher;DROP TABLE my_student;DROP TABLE my_course;DROP TABLE my_sc;#创建表CREATE TABLE my_teacher(     `tid` INT PRIMARY KEY AUTO_INCREMENT COMMENT 'id编号',#自增长主键    `tname` VARCHAR(20) NOT NULL COMMENT '姓名',    `tage` INT COMMENT '年龄',    `tmoney` INT COMMENT '工资',    `tgender` ENUM('男','女') DEFAULT '男' COMMENT '性别')CHARSET utf8;CREATE TABLE my_student(     `sid` INT PRIMARY KEY AUTO_INCREMENT COMMENT 'id编号',#自增长主键    `sname` VARCHAR(20) NOT NULL COMMENT '姓名',    `sage` INT COMMENT '年龄',    `sgender` ENUM('男','女') DEFAULT '男' COMMENT '性别')CHARSET utf8;CREATE TABLE my_course(     `cid` INT PRIMARY KEY COMMENT '课程编号',#主键    `cname` VARCHAR(20) COMMENT '课程名称',    `tid` INT COMMENT '老师编号',    FOREIGN KEY(tid) REFERENCES my_teacher(tid)#外键)CHARSET utf8;CREATE TABLE my_sc(     `sid` INT COMMENT '学生id编号',    `cid` INT COMMENT '课程id编号',    `score` TINYINT DEFAULT 60 COMMENT '成绩',    PRIMARY KEY(`sid`,`cid`),#复合主键    FOREIGN KEY(sid) REFERENCES my_student(sid)#外键)CHARSET utf8;#表结构SHOW VARIABLES LIKE 'auto_increment%';#查看自增长信息:auto_increment_increment 步长、auto_increment_offset 起始值SET auto_increment_increment=3;#修改步长ALTER TABLE my_sc ADD CONSTRAINT student_foreign_cid FOREIGN KEY(cid) REFERENCES my_course(cid);#添加外键约束DESC my_sc;#查看表结构SHOW CREATE TABLE my_sc;#查看创建语句#视图CREATE VIEW view_sc AS SELECT * FROM my_sc;#创建视图CREATE VIEW view_student AS SELECT * FROM my_student;DROP VIEW view_student;#删除视图SELECT * FROM view_sc;#增加记录INSERT INTO my_teacher(`tname`,`tage`,`tmoney`) VALUES('刘赫',45,6300);INSERT INTO my_teacher(`tname`,`tage`,`tmoney`,`tgender`) VALUES('邓娟',25,3500,'女');INSERT INTO my_teacher(`tname`,`tage`,`tmoney`) VALUES('廖敏',32,35000);INSERT INTO my_student(`sname`,`sage`,`sgender`) VALUES('唐晶晶',19,'女');INSERT INTO my_student(`sname`,`sage`) VALUES('卢成',20);INSERT INTO my_student(`sname`,`sage`,`sgender`) VALUES('胡平珺',20,'女');INSERT INTO my_course(`cid`,`cname`,`tid`) VALUES(1,'数学',1);INSERT INTO my_course(`cid`,`cname`,`tid`) VALUES(2,'生物',1);INSERT INTO my_course(`cid`,`cname`,`tid`) VALUES(3,'语文',2);INSERT INTO my_course(`cid`,`cname`,`tid`) VALUES(4,'信息',3);INSERT INTO my_course(`cid`,`cname`,`tid`) VALUES(5,'英语',2);INSERT INTO my_sc(`sid`,`cid`,`score`) VALUES(1,1,78);INSERT INTO my_sc(`sid`,`cid`,`score`) VALUES(1,2,80);INSERT INTO my_sc(`sid`,`cid`,`score`) VALUES(1,3,90);INSERT INTO my_sc(`sid`,`cid`,`score`) VALUES(1,5,69);INSERT INTO my_sc(`sid`,`cid`,`score`) VALUES(2,1,50);INSERT INTO my_sc(`sid`,`cid`,`score`) VALUES(2,2,78);INSERT INTO my_sc(`sid`,`cid`,`score`) VALUES(2,2,72);INSERT INTO my_sc(`sid`,`cid`,`score`) VALUES(2,4,95);INSERT INTO my_sc(`sid`,`cid`,`score`) VALUES(2,5,55);INSERT INTO my_sc(`sid`,`cid`,`score`) VALUES(3,1,78);INSERT INTO my_sc(`sid`,`cid`,`score`) VALUES(3,3,82);INSERT INTO my_sc(`sid`,`cid`,`score`) VALUES(3,5,89);#查看记录SELECT * FROM my_teacher;SELECT * FROM my_student; SELECT * FROM my_course;SELECT * FROM my_sc;SELECT * FROM my_student LIMIT 0,3;#0起始位置，3每页显示记录数量SELECT sid,sname,AVG(score) FROM my_student NATURAL JOIN my_sc GROUP BY sid,sname ORDER BY AVG(score) DESC;