5天学会mysql_5天学完《MySQL必知必会》学习笔记之第四天

女爷i 2022-11-06 01:50 181阅读 0赞

本篇知识点

更新删除数据、创建操纵表、视图、存储过程

更新和删除数据使用UPDATE语句更新(修改)表中的数据：更新表中特定行(使用WHERE语句

更新表中所有行

UPDATE语句以要更新的表名开始，SET命令用来赋值

UPDATE customers

SET cust\_email = 'elmer@fund.com'

WHERE cust\_id = 10005;

更新多个列

UPDATE customers

SET cust\_name = 'The Fudds',

cust\_email = 'elmer@fudd.com'

WHERE cust\_id = 10005;

使用IGNORE关键字，即使在更新多个行时发生错误，也能继续进行更新

UPDATE IGNORE customers删除某个列的值，可使用UPDATE设置它为NULL(假如表定义允许NULL值)

UPDATE customers

SET cust\_email = NULL

WHERE cust\_id = 10005;

其中NULL用来去除cust\_mail列中的值使用DELETE语句从一个表中删除数据：从表中删除特定行

从表中删除所有行

DELETE删除整行，删除列(值)用UPDATE

DELETE FROM customers WHERE cust\_id = 10006;

DELETE不删除表本身；删除所有行用TRUNCATE TABLE

创建和操纵表使用CREATE TABLE创建表时，须给出下列信息：新表的名字，在关键字CREATE TABLE之后给出；

表列的名字和定义，用逗号分隔

创建练习使用的customers表

CREATE TABLE customers IF NOT EXISTS

(

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;

在表名后给出IF NOT EXISTS，仅在表名不存在时创建它。NULL值表示没有值或缺值，允许NULL值的列允许在插入行时不给出该列的值，否之必须给值

CREATE TABLE orders

(

order\_num int NOT NULL AUTO\_INCREMENT ,

order\_date datetime NOT NULL ,

cust\_id int NOT NULL ,

PRIMARU KEY (order\_num)

) ENGINE = InnoDB ;

NULL值和NOT NULL可以在创建时混用主键值必须唯一；创建由多个列组成的主键，应该以逗号分隔的列表给出各列名：

CREATE TABLE orderitems

(

order\_num int NOT NULL ,

order\_item int NOT NULL ,

prod\_id char(10) NOT NULL ,

quantity int NOT NULL ,

item\_price decimal(8,2) NOT NULL ,

PRIMARY KEY (order\_num, order\_item)

) ENGINE = InnoDB;

订单号(order\_num)和订单物品(order\_item)的组合是唯一的，从而适合作为主键。

主键：其值唯一标识表中每个行的列；主键中只能使用定义为NOT NULL的列。使用AUTO\_INCREMENT，当一列增加一行时自动增量，给该列赋予一个可用的值，一般用在id列，可用作主键值；每个表只允许一个AUTO\_INCREMENT列，且它必须被索引。

使用last\_insert\_id( )函数可获得使用AUTO\_INCREMENT列时获得的值

SELECT last\_insert\_id( )

返回一个AUTO\_INCREMENT值指定默认值：如果在插入行时没有给值，可指定使用默认值

quantity int NOT NULL DEFAULT 1,

MySQL只支持常量作为默认值引擎类型InnoDB是一个可靠的事务处理引擎，不支持全文本搜索

MEMORY功能等同与MyISAM，数据存储在内存中，速度很快(适合于临时表)

MyISAM性能很高，支持全文本搜索，不支持事务处理

外键不能跨引擎使用ALTER TABLE语句更新表定义，须给出：ALTER TABLE之后给出要更改的表名

更改的列表

给表添加一个列

ALTER TABLE vendors

ADD vend\_phone CHAR(20);删除刚添加的列

ALTER TABLE vendors

DROP COLUMN vend\_phone;使用ALTER TABLE定义外键：

ALTER TABLE orderitems

ADD CONSTRAINT fk\_orderitems\_orders

FOREIGN KEY (order\_num) REFERENCES orders(order\_num);

对单个表进行多个更改，可使用单挑ALTER TABLE语句，每个更改用逗号分隔删除表

DROP TABLE customers2;重命名表

RENAME TABLE customers2 TO customers;

可以对多个表重命名，用逗号分开。

使用视图视图是虚拟的表；视图只包含使用时动态检索数据的查询。

为什么使用视图：重用SQL语句

简化复杂的SQL操作。编写查询后，可以方便地重用它而不必知道基本细节

使用表的组成部分而非整个表

保护表数据。用户仅被授予表的特定部分的访问权限。

更改数据格式和表使。视图可返回与底层表的表示和格式不同的数据

视图是用来查看存储在别处的数据的一种设施，它本身不包含数据。

视图的创建：用CREATE VIEW语句创建

使用SHOW CREATE VIEW viewname；来查看创建视图的语句

用DROP删除视图，语法为DROP VIEW viewname

更新视图时，可先DROP再CREATE，也可直接CREATE OR REPLACE VIEW.

使用视图隐藏复杂的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 ;

这条语句创建一个名为productcustomers的视图，它联结三个表。

为检索订购了产品TNT2的客户，可如下进行：

SELECT cust\_name, cust\_contact

FROM productcustomers

WHERE prod\_id = 'TNT2';使用视图重新格式化检索出的数据

CREATE VIEW vendorlocations AS

SELECT Concat(Rtrim(vend\_name), '(', Rtrim(vend\_country), ')')

AS vend\_title

FROM vendors

ORDER BY vend\_name;用视图过滤不想要的数据

CREATE VIEW customermaillist AS

SELECT cust\_id, cust\_name, cust\_email

FROM customers

WHERE cust\_email IS NOT NULL ;

定义customeremaillist视图，过滤没有电子邮件地址的客户。使用视图与计算字段

CREATE VIEW orderitemsexpanded AS

SELECT order\_num,

prod\_id,

quantity,

item\_price,

quantity\*item\_price AS expanded\_price

FROM orderitems ;视图是可更新的(对它们使用INSERT, UPDATE和DELETE)，但是如果视图定义中有以下操作(使得MySQL不能正确地确定被更新的基数据)，则不能进行视图更新：分组(使用GROUP BY和HAVING)

联结

子查询

并

聚集函数(Min( ), Count( ), Sum( )等)

DISTINCT

导出(计算)列

使用存储过程存储过程，就是为了以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件。

执行存储过程称为调用，使用语句CALL

CALL productpricing (@pricelow,

@pricehigh,

@priceaverage);

其中，执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。创建存储过程 例子：一个返回产品平均价格的存储过程

CREATE PROCEDURE productpricing( )

BEGIN

SELECT Avg(prod\_price) AS priceaverage

FROM products;

END;

此存储过程名为productpricing，用CREATE PROCEDURE productpricing( )语句定义，若存储过程接受参数，将在( )中列举出来。BEGIN和END语句用来限定存储过程体，存储过程体本身仅是一个简单的SELECT语句。mysql命令行客户机的分隔符 (我懒得打字总结了)摘自《MySQL必知必会》使用存储过程

CALL productpricing( ) ;删除存储过程

DROP PROCEDURE productpricing ;使用参数

CREATE PROCEDURE productpricing (

OUT pl DECIMAL (8, 2) ,

OUT ph DECIMAL (8, 2) ,

OUT pa DECIMAL (8, 2)

)

BEGIN

SELECT Min(prod\_price)

INTO pl

FROM products;

SELECT Max(prod\_price)

INTO ph

FROM products;

SELECT Avg(prod\_price)

INTO pa

FROM products;

END;

此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键词OUT指出相应的参数用来从存储过程传出一个值(返回给调用者)。MySQL支持IN(传递给存储过程)、OUT(从存储过程传出)和INOUT(对存储过程传入和传出)类型的参数。调用此存储过程须指定3个变量名

CALL productpricing (@pricelow,

@pricehigh,

@priceaverage);

变量以@开始。调用时上条语句不显示数据，它返回可以显示的变量。显示数据

SELECT @priceaverage;显示3个数据

SELECT @pricehigh, @pricelow, @priceaverage;使用IN和OUT参数

CREATE PROCEDURE ordertotal (

IN onumer INT,

OUT ototal DECIMAL(8, 2)

)

BEGIN

SELECT Sum(item\_price\*quantity)