DML语言

# 插入值的类型要与列的类型一致或兼容( '123' -> 123)
insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id) values(13,'tangyixin','g','1990-4-23','18988888888',NULL,2);

# 不可以为NULL的列必须插入值，可以为NULL的列如何插入值
insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id) values(13,'tangyixin','g','1990-4-23','18988888888',NULL,2);
insert into beauty(id,name,sex,borndate,phone,boyfriend_id) values(13,'tangyixin','g','1990-4-23','18988888888',2);

# 列的顺序可以调换
insert into beauty(name, sex, id, phone) values('jiangxin', 'g', 16, '110');

# 列数和值的个数必须一致
# insert into beauty(name, sex, id, phone, boyfriend_id) values('guanxiaotong', 'g', 17, '110');

# 可以省略列名,默认所有列，而且列的顺序和表中列的顺序一致
insert into beauty values(18, 'zhangfei', 'm', null, '119', null, null);

insert into beauty set id=19, name='liutao', phone='999';

# 
insert into beauty values(20, 'zhangfei0', 'm', null, '119', null, null),
(21, 'zhangfei1', 'm', null, '119', null, null),
(22, 'zhangfei2', 'm', null, '119', null, null);
# 
insert into beauty(id, name, phone) select 26, 'songqian', '11809866';
insert into beauty(id, name, phone) select id, boyname, '11809866' from boys where id < 3;

# 解除输入的安全模式
SET SQL_SAFE_UPDATES = 0;

# 修改beauty表中姓唐的女生的电话为 13899888899
update beauty set phone = '13899888899' where name like 'tang%';

# 修改boys表中id号为2的名称为张飞，魅力值为10
update boys set boyname ='zhangfei', usercp = 10 where id = 2;

# 修改张无忌的女朋友的手机号为114
update boys bo inner join beauty b on bo.id = b.boyfriend_id set b.phone = 114 where bo.boyName = 'zhangwuji';

# 修改没有男朋友的女神的男朋友编号都为2号
update boys bo right join beauty b on bo.id = b.boyfriend_id set b.boyfriend_id = 2 where bo.id is null;

# 单表的删除
# 删除手机号以9结尾的女生信息
delete from beauty where phone like '%9';
# 多表的删除
# 删除张无忌的女朋友的信息
delete b from beauty b inner join boys bo on b.boyfriend_id = bo.id where bo.boyName = 'zhangwuji';
# 删除黄晓明的信息和他女朋友的信息
delete b, bo from beauty b inner join boys bo on b.boyfriend_id = bo.id where bo.boyName = 'huangxiaoming';

# 将魅力值大于100的男生信息删除
truncate table boys;