--创建数据库
CREATEDATABASE MySchool CHARACTER SET utf8 ;
--删除数据库
DROPDATABASE MySchool;
--创建学生表
CREATETABLE student(
sid INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
PRIMARY KEY(sid)
);
--删除表
DROPTABLE student;
--添加新字段
ALTERTABLE student ADD COLUMN sex BIT NULL AFTER NAME;
ALTERTABLE student ADD COLUMN age SMALLINT NULL AFTER sex;
ALTERTABLE student ADD COLUMN email VARCHAR(50) NULL AFTER age;
--删除字段
ALTERTABLE student DROP COLUMN email;
--修改字段
ALTERTABLE student CHANGE sex sex CHAR(2) NULL;
ALTERTABLE student CHANGE email email VARCHAR(50) NULL;
--数据库插入数据
INSERTINTO student (NAME, sex, age, email) VALUES ('宋仲基', '男', '25', '22@qq.com');
--更新表数据
UPDATEstudent SET sex = '男' WHERE sid = '1';
--表中数据删除
DELETEFROM student WHERE sid = '2';
--查询
SELECT* FROM student LIMIT 0, 1000;
--创建分数表 外键关联
CREATETABLE score(
cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
score INT,
sid INT,
FOREIGN KEY(sid) REFERENCES student(sid)
);
--多表联查
SELECT* FROM student t1 LEFT JOIN score t2 ON t1.`sid`=t2.`sid`;
SELECT* FROM student t1 RIGHT JOIN score t2 ON t1.`sid`=t2.`sid`;
SELECT* FROM student t1, score t2 WHERE t1.`sid`=t2.`sid`;
--修改表,给表中字段增加外键关联
ALTERTABLE `myschool`.`score`ADD CONSTRAINT `FK_score`FOREIGN KEY (`cid`)REFERENCES `course` (`cid`);