DML数据操纵语言

1.添加 insert 命令

语法：insert into 表名(字段名1，字段名2，字段名3...) values(数据1，数据2，数据3....);

insert into customer (custid, name) values (10000, '马化腾');

insert into customer (custid, name) values (10001, '马云');

insert into customer (custid, name) values (10002, '刘强东');

insert into customer (custid, name) values (10003, '马化腾');

insert into customer (custid, name) values (10004, '马化腾');

 

insert into goods(goodsid, goods_name, goods_price)values(1, 'QQ会员', 10.01);

insert into goods(goodsid, goods_name, goods_price)values(2, '禄钻', 20.01);

insert into goods(goodsid, goods_name, goods_price)values(3, '康帅博', 5);

insert into goods(goodsid, goods_name, goods_price)values(4, '二手电脑', 1000.01);

insert into goods(goodsid, goods_name, goods_price)values(5, '三手电脑', 500.01);

 

insert into ord (ordid, custid, goodsid) values (1, 10000, 1);

insert into ord (ordid, custid, goodsid) values (2, 10002, 1);

insert into ord (ordid, custid, goodsid) values (3, 10002, 3);

insert into ord (ordid, custid, goodsid) values (4, 10000, 3);

insert into ord (ordid, custid, goodsid) values (5, 10003, 3);

 

insert into t_test values (1,'lavasoft','123456',NULL);

insert into t_test values (2,'hello',NULL,NULL);

insert into t_test values (3,'haha',zz,tt);

 

2.修改 update 命令

语法：update 表名称 set 字段 1=值 1，字段 2=值 2，...[WHERE 更新条件(s)];

--将customer表中custid为10004的名称改为任郑华

update customer set name='任郑华' where custid = 10004；

update customer set name='任郑华1' where custid = 10002；

 

--将goods 表中编号为3的goods_name改为酸奶，good_price改为10元

update goods set goods_name='酸奶',goods_price='10.0' where custid = 5；

 

3.删除 delete 命令

1).drop，直接将表删掉是 删除整个表

2).delete，删除表中数据，不删除表；可以撤销删除数据表中的数据

3).truncate，删除表中数据，不删除表；不可撤销删除表中的数据，删除速度比delete更 快

语法：delete form 表名 .[WHERE 更新条件(s)];

delete from goods;

delete from goods where goodsid=1;

delete from goods order by goodsid;

delete from goods limit 1;