Mysql-学习笔记（==》插入修改数据二）-CSDN博客

USE db;

-- 建立学生信息表
CREATE TABLE student
(
sno INT UNSIGNED NOT NULL AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
sage TINYINT UNSIGNED NOT NULL,
sbirthday DATETIME,
saddress VARCHAR(50),
sscore DECIMAL(4,2),
PRIMARY KEY(sno)
)ENGINE=MYISAM AUTO_INCREMENT=201601 DEFAULT CHARSET=utf8;

-- 删除表
DROP TABLE student;
-- 查看表的结构
DESC student;

-- insert 操作插入数据（into可写可不写建议写）
INSERT INTO student VALUES(NULL,'李四',18,'2000-1-18','武汉',82);

INSERT student VALUES(NULL,'张三飞',18,'2008-1-18','上海',82.5);

-- 插入两条记录
INSERT INTO student VALUES(NULL,'老王',18,'2000-1-18','武汉',82),(NULL,'张飞',55,'546-1-18','郑州',45.1);

-- 复制已有的记录的属性并且插入
INSERT INTO student(sname,sage,sbirthday,saddress,sscore)
SELECT sname,sage,sbirthday,saddress,sscore FROM student;

INSERT student(sname,sage) VALUES('Rose',18),('Jack',20);

-- replace 当有相同primary或unique主键时，替换，没有相同的则跟insert功能相同
REPLACE INTO student(sname,sage) VALUES('张先生',18),('关羽',20);

REPLACE INTO student VALUES(201609,'asda',18,'2000-1-18','武汉',82.6);

-- 修改数据
UPDATE student SET sscore=85,saddress='上海',sbirthday='1966-2-3' WHERE sno=201603;

-- 删除数据
DELETE FROM student WHERE sno=201603;
DELETE FROM student;/*无条件删除所有记录*/

TRUNCATE TABLE student;/*直接清空表的全部记录，auto_increment自动编号从1开始分配（1，2，3...）*/

-- 查看查询数据显示
SELECT * FROM student;