MySQL必知必会读书笔记二

暂时只更到视图之前的内容了

后续的东西有机会再补

#插入数据
INSERT INTO customers(cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
	VALUES('Pep E','100 Main Street','Los Angeles','CA','90046','USA',NULL,NULL); #安全插入的方式
INSERT INTO customers(cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
	VALUES('Pep E','100 Main Street','Los Angeles','CA','90046','USA',NULL,NULL),
	('Pep C','100 Main Street','Los Angeles','CA','90046','USA',NULL,NUL); #插入多个行
	#插入检索出的数据
INSERT INTO customers(cust_id, cust_contact, cust_name, cust_address, cust_city, cust_state, cust_zip)
	SELECT cust_id, cust_contact, cust_name, cust_address, cust_city, cust_state, cust_zip FROM custnew;
		#这个例子把从custnew表中的数据检索插入customers中

#更新和删除数据
UPDATE customers SET cust_email = 'zzaxg@163.com' WHERE cust_id = 10005; #如果不设置WHERE子句，那么会更新所有行
DELETE FROM customers WHERE cust_id = 10006;

#创建和操纵表
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
	#InnoDB支持事务
	#更新表
ALTER TABLE vendors ADD vend_phone CHAR(20); #添加列
ALTER TABLE vendors DROP COLUMN vend_phone; #删除列
	#删除表
DROP TABLE customers2;
	#重命名表
RENAME TABLE customers2 TO customers;