一、插入
SELECT * FROM student;
SELECT * FROM major;
INSERT INTO student(id,stuname,gender,majorid)VALUES(1,'Tom','女',2);
SELECT * FROM student;
INSERT INTO student VALUES(2,'jerry','1995-10-12',NULL,DEFAULT,1001,1);
SELECT * FROM student;
对于INSERT 的时候主键确定的问题,这里下面采用一种主键自增的方法
P.S:记住不要手动插入主键的值
DROP TABLES IF EXISTS stuinfo;
USE myemployees;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY AUTO_INCREMENT ,
stuname VARCHAR(20) NOT NULL,
borndate DATETIME ,
email VARCHAR(20) CHECK (email LIKE '%@%'),
gender CHAR(1) DEFAULT '男',
seat INT UNIQUE ,
majorId INT,
CONSTRAINT fk_major_studio FOREIGN KEY (majorid) REFERENCES major(id)
);
SELECT * FROM stuinfo;
INSERT INTO stuinfo(stuname,gender,majorid)VALUES('maomao','MALE',3);
SELECT * FROM stuinfo;
二、 更新
UPDATE 表名 SET 列名 = 新值,列名 = 新值,列名 = 新值,。。。 WHERE 检索条件
UPDATE stuinfo SET gender = '女',seat = '1001' WHERE stuname = 'ruhua';
SELECT * FROM stuinfo;
UPDATE stuinfo SET email = 'sanmao@163.com',seat = 1002,gender = '女' WHERE id = 1;
SELECT * FROM stuinfo;
三、 删除
DELETE FROM stuinfo WHERE stuid = 3 AND seat = '19001';
SELECT * FROM stuinfo;