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;