MySQL数据库

--创建数据库

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`);