SQL第15-16课：更新和删除数据

insert 需要一定的安全权限。

mysql> insert into Customers
    -> values('1000000006',
    -> 'Toy Land',
    -> '123 Any Street',
    ->  'New York',
    -> 'NY',
    -> '11111',
    ->  'USA',
    -> NULL,
    ->  NULL);
Query OK, 1 row affected (0.00 sec)

数据顺序必须与原来的数据结构保持一致。

而下面这种方法可以按insert者自己制定的顺序insert而不用管原来的顺序。

mysql> insert into Customers(
    -> cust_id,
    -> cust_name,
    -> cust_address,
    -> cust_city,
    -> cust_state,
    -> cust_zip,
    -> cust_country,
    -> cust_contact,
    -> cust_email)
    -> values('1000000006',
    -> 'Toy Land',
    -> '123 Any Street',
    ->  'New York',
    -> 'NY',
    -> '11111',
    ->  'USA',
    -> NULL,
    ->  NULL);
Query OK, 1 row affected (0.00 sec)

可以insert部分列，没有指定值的列会被填充NULL。

insert select

mysql> insert into Customers(
    -> cust_id,
    -> cust_name,
    -> cust_address,
    -> cust_city,
    -> cust_state,
    -> cust_zip,
    -> cust_country,
    -> cust_contact,
    -> cust_email);
    -> select cust_id,
    -> cust_name,
    -> cust_address,
    -> cust_city,
    -> cust_state,
    -> cust_zip,
    -> cust_country,
    -> cust_contact,
    -> cust_email
    -> from CustNew;

复制表

mysql> create table CustCopy as
    -> select * 
    -> from Customers;
Query OK, 7 rows affected (0.01 sec)

更新表内容，删除列可以用update

mysql> update Customers 
    -> set cust_email='kim@thetoystore.com'
    -> where cust_id='1000000005';

删除行

mysql> delete  from Customers
    -> where cust_id='1000000006';

需要删除所有行用truncate table。update和delete都是WHERE的子句。