MySQL插入、更新、删除记录的测试

-- 测试插入记录 INSERT
CREATE TABLE IF NOT EXISTS user(
id tinyint unsigned auto_increment key,
username varchar(20) not null unique,
password char(32) not null,
email varchar(50) not null default '12345678@qq.com',
age tinyint unsigned default 18
);

-- 不指定具体的字段名
insert into user values(1,'king','king','king@qq.com',20);

insert user value(2,'queen','queen','queen@qq.com',30);

-- 列出指定字段
insert user(username,password) values('A','AA');

insert user(password,username) values('BB','B');

-- 一次插入多条记录
insert user values(6,'C','CC','C@qq.com',35),
(8,'D','DD','D@qq.com',28);

-- 通过INSERT SET 形式插入记录
insert into user set id=98,username='test',password='this is aa test',email='123@qq.com',age=47;

create table if not exists testUser(
id tinyint unsigned auto_increment key,
username varchar(20) not null unique
);

-- 将查询结果插入到表中
insert testUser select id,username from user;
-- 字段数目不匹配
insert testUser select * from user;

insert testUser(username) select username from user;


-- 将用户表中所有的用户年龄更新为15
update user set age=5;

update user set age=20,email='test@qq.com';

-- 将第一条记录的password,email,age 改变
update user set password='king@qq.com',email='123@qq.com',age=99 where id=1;

update user set age=age-5 where id>=3;

update user set age=default where username='A';

-- 删除testUser表中的记录
delete from testUser;

-- 删除user表中id=1的用户
delete from user where id=1;

-- 彻底清空user表
truncate table user;