SQL笔记15：数据插入

插入数据

插入完整的行

例1

INSERT INTO customers
VALUES(1000000006,
		'Toy Land',
        '123 Any Street',
        'New York',
        'NY',
        '11111',
        'USA',
        NULL,
        NULL);

例2

INSERT INTO customers(cust_id,
					  cust_name,
					  cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email)
VALUES(1000000007,
		'Toy Land',
        '123 Any Street',
        'New York',
        'NY',
        '11111',
        'USA',
        NULL,
        NULL);

插入部分行

例3

INSERT INTO customers(cust_id,
					  cust_name,
					  cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country)
VALUES(1000000008,
		'Toy Land',
        '123 Any Street',
        'New York',
        'NY',
        '11111',
        'USA');

插入检索出的数据

例4

INSERT INTO customers(cust_id,
					  cust_contact,
                      cust_email,
                      cust_address,
                      cust_city,
                      cust_sate,
                      cust_zip,
                      cust_country)
SELECT cust_id,
	   cust_contact,
       cust_email,
       cust_address,
	   cust_city,
       cust_sate,
       cust_zip,
	   cust_country
FROM custNew;

复制表

例5

CREATE TABLE custcopy AS SELECT * FROM customers;

挑战题

1.

Using INSERT and columns specified, add yourself to the Customers table.

INSERT INTO customers(cust_id,
					  cust_name,
					  cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country)
VALUES(1000000009,
		'Jianguo Peng',
        '123 Any Street',
        'New York',
        'NY',
        '11111',
        'CN');

2.

Make a backup copy of your Orders and OrderItems tables.

CREATE TABLE copy_orders AS SELECT * FROM orders;

CREATE TABLE copy_orderitems AS SELECT * FROM orderitems;