插入数据
-- 插入完整行
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);
-- 插入部分行(省略列为NULL或默认值)
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA');
-- 插入检索出的数据(INSERT SELECT)
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
SELECT cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country FROM customersnew;
-- 从一个表复制到另一个表
CREATE TABLE custcopy AS SELECT * FROM customers;
更新数据
UPDATE customers SET cust_email = 'kim@thetoystore.com' WHERE cust_id = '1000000005';
UPDATE customers SET cust_email = NULL WHERE cust_id = '1000000005'; -- NULL删除某个列的值
删除数据
DELETE FROM customers WHERE cust_id = '1000000011'; -- 删除指定行
TRUNCATE TABLE custcopy; -- 删除所有行
创建表
CREATE TABLE Products
(
prod_id char(10) NOT NULL , PRIMARY KEY
vend_id char(10) NOT NULL ,
prod_name char(255) NOT NULL ,
prod_price decimal(8,2) NOT NULL DEFAULT 0.00 ,
prod_desc text NULL
);
更新表
ALTER TABLE vendors ADD vend_phone CHAR(20) DEFAULT '000000'; -- 添加vend_phone列，并设置默认值
ALTER TABLE vendors DROP COLUMN vend_phone; -- 删除vend_phone列
-- 添加外键
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
-- 删除外键
ALTER TABLE orderitems DROP FOREIGN KEY fk_orderitems_products;
删除表
DROP TABLE custcopy;
重命名表
RENAME TABLE old_table to new_table;
《SQL必知必会》系列索引:
