mysql常用语句

插入数据

INSERT INTO customers(cust_name,cust_address) VALUES('Danny','New York');

插入检索出的数据

一般用于合并旧表，注意新表和旧表id(主键)不能重复，否则有冲突

INSERT INTO new_customers(new_cust_name,new_cust_address) SELECT cust_name,cust_address from customer;

更新数据

UPDATE customers SET cust_name = 'Jessy' WHERE cust_id = 1001;

删除数据

注意，这里指的是删除表的内容，比如customer表的某一个用户信息，而不删除表本身

• 删除特定的行

DELETE FROM customers WHERE cust_id=1001;

注意这里相比UPDATE，需要添加FROM表示从哪个表删除

• 删除所有行（清空表）

DELETE FROM customers;

• 清空表TRUNCATE

作用与delete from删除表数据相同，但是根据《Mysql crash course》建议，清空表中所有数据使用TRUNCATE更高效，因为它是删除整张表(包括结构)，再创建一个新的空表。

TRUNCATE customers;

建议：在UPDATE或者DELETE之前。建议使用SELECT确保要更新或者删除的数据准确。

删除整张表，包括表名

drop table customer;

创建表

CREATE TABLE new_cust(
  cust_id int NOT NULL AUTO_INCREMENT,
  cust_name char(50) NOT NULL,
  cust_addr varchar(32) NULL,
  PRIMARY KEY (cust_id)
)ENGINE=InnoDB;

ENGINE需要注意两点
1.InnoDB支持事务，不支持全文检索；Myisam不支持事务，支持全文检索;
2.外键不支持跨引擎

修改表的字段

添加字段(列)

ALTER TABLE new_cust ADD cust_phone char(20) NULL;

删除字段（列）

ALTER TABLE new_cust DROP COLUMN cust_phone;

修改字段

alter table <表名> change <字段名> <字段新名称> <字段的类型>。

ALTER TABLE customers CHANGE name c_name varchar(32);

添加外键

ALTER TABLE Orders
ADD CONSTRAINT fk_Orders_Persons
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id);

fk_Orders_Persons为外键名称

删除外键

ALTER TABLE Orders
DROP FOREIGN KEY fk_Orders_Persons

重命名表

RENAME TABLE customers TO customer

视图

• 为什么使用视图
  • 重用SQL语句
  • 简化复杂的SQL操作，不必关系细节
  • 使用表的组成部分而不是整张表
  • 保护数据
  • 更改数据格式和表示

创建视图(联结多张表)

CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num =  orders.order_num;

上面联结了三张表
orderitems是订单商品信息，
orders是订单信息，包含创建日期和客户id
customers是客户信息表

这样productcustomers就是订购了任意产品的客户名称和商品信息表(虚拟)

使用视图

SELECT cust_name,cust_contact,prod_id FROM productcustomers
WHERE prod_id='TNT2';

使用视图格式化数据

加入我们要返回下面的格式化数据

SELECT Concat(Rtrim(vend_name), '(' , Rtrim(vend_country),')')
AS vend_title FROM vendors
ORDER BY vend_name;

image.png

使用视图的话，就不必每次都创建这样的格式，而是可以直接复用格式。

CREATE VIEW vendorlocations AS
SELECT Concat(Rtrim(vend_name), '(' , Rtrim(vend_country),')')
AS vend_title FROM vendors
ORDER BY vend_name;

这样我们就可以直接使用了

SELECT * FROM vendorlocations;

image.png

视图的更新

视图的更新需要根据情况
以下情况是不能更新的

• 分组（groupby,having）
• 联结
• 子查询
• 并
• 聚集函数（MIn(),Count(),Sum()等）
• DISTINCT
• 导出列（计算列）

实际上，我们应该谨慎使用视图更新，因为视图主要就是用于方便数据检索的。而不是普通的数据表。