mysql非线性数据库_MySQL第四章数据库和表的插入和删除

set SQL_MODE = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';

USE test;

SET names utf8;

INSERT INTO students #使用valuse 插入

(student_id,student_name,student_sex,student_major,student_address,student_birthday)

VALUES(0001,'朱元璋','1','政治','南京皇宫','1328') ;

INSERT INTO students #使用set 插入

SET student_id = 0002,student_name = '朱允炆',student_sex = '1',student_major = '政治',student_address = '南京皇宫后到民间',student_birthday = '1377';

INSERT INTO students #插入多条数据

(student_id,student_name,student_sex,student_major,student_address,student_birthday)

VALUES(0003,'朱棣','1','政治军事','凤阳到南京到北京','1360'),

(0004,'朱高炽','1','政治','北京','1378'),

(0007,'','','','','');

#合并数据到表，首先创建一个表student并输入数据

CREATE TABLE IF NOT EXISTS student

(ID INT(8),

name CHAR(20),

sex CHAR(1),

major CHAR(50),

address CHAR(50),

birthday CHAR(20));

#接下来在表student中输入数据

INSERT INTO student

(ID,name,sex,major,address,birthday)

VALUES(0005,'朱瞻基','1','政治','北京故宫','1398'),

(0006,'朱祁镇','1','放牧和政治','瓦剌和北京','1427');

#最后合并表 使用SELECT命令检索数据

INSERT INTO students

(student_name,student_sex,student_major,student_address,student_birthday)

SELECT name,sex,major,address,birthday FROM student;

#替换主键重复的列

REPLACE INTO students

VALUES(0007,'朱祁钰','1','政治','北京','1428'); #这里有一个顺序错误，留着后面第48行修改

#删除表数据 在删除数据之前先复制表

CREATE TABLE students_copy SELECT * FROM students;

DELETE FROM students WHERE student_id = 1;

#`TRUNCATE是先删除原来的表，再重新创建一个表。DELETE是逐行删除。！！注意：一旦删除不可恢复，要小心！

TRUNCATE TABLE students;

#修改表数据 先将student_copy 名字改为students

UPDATE test.students

SET student_birthday='1428',student_address='北京'#更改多个值时，用逗号隔开

WHERE student_name='朱祁钰';

#使用UPDATE语句修改多个表 假设上一布没有修改朱祁钰的生日和住址。修改表一的name和表二的address，当表一的ID等于表二的ID时

UPDATE students,students_copy

SET students.student_name='明朝皇帝',students_copy.student_address='北京'

WHERE students_copy.student_id=students.student_id;