《MySQL必知必会》19~23章

第十九~第二十三章

第十九章

INSERT 是用来插入(或添加)行到数据库表的,有以下几种方式

· 插入完整的行

· 插入行的一部分

· 插入多行

· 插入某些查询的结果

可针对每个表或每个用户利用MySQL的安全机制禁止使用INSERT语句

插入完整的行,要求表名和插入新行的值

INSERT INTO customers
VALUES(NULL,
       'Pep E. LaPew',
       '100 Main Street',
       'Los Angeles',
       'CA',
       '90046',
       'USA',
       NULL,
       NULL);

这种格式下,每个列必须提供一个值,次序也应该与定义列的次序相同,没有值则填NULL(假定该列允许为空值),若某列由MySQL自动增量,也指定为NULL,但这种语法不安全(表的结构以后可能会变动),应尽量避免使用

更安全的方法

INSERT INTO customers(cust_name,
            cust_address,
            cust_city,
            cust_state,
            cust_zip,
            cust_country,
            cust_contact,
            cust_email)
VALUES('Pep E. LaPew',
      '100 Main Street',
      'Los Angeles',
      'CA',
      '90046',
      'USA',
      NULL,
      NULL);

VALUES中的值以之前指定的次序(不需要按照原有表的次序)填写,这种方法即使表的结构改变也能正确工作,因为cust_id的值是不必要的,所以可以不用出现

若满足以下条件之一在INSERT语句中可以省略

· 该列定义为允许NULL值(无值或者空值)

· 在表定义中给出默认值,表示如果不给出值,将使用默认值

若数据检索更重要,可以在INSERT和INTO之间添加关键字LOW_PRIORITY降低INSERT的优先级

插入多行可以使用多条INSERT语句多次插入,或者,只要每条INSERT语句中的列名和次序相同,可以如下形式

INSERT INTO customers(cust_name,
            cust_address,
            cust_city,
            cust_state,
            cust_zip,
            cust_country)
VALUES(
	'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles',
    'CA',
    '90046'
    'USA'
),
    (
	'M, Martian',
    '42 Galaxy Way',
    'New York',
    'NY',
    '11213',
    'USA'
);

每组值用一对圆括号括起来,用逗号分隔,该方法比使用多条INSERT语句更快

插入检索出的数据,由一条INSERT语句和SELECT语句组成,比如我们想从一个表中合并客户列表到customers表中,不需要每次读取一行再插入(假设有一个名为custnew的表,结构与customers相同,要注意custnew表中的cust_id不能与customers中的重复或者省略这列值在导入到另一个表时有MySQL生成)

INSERT INTO customers(cust_id,
	cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
SELECT cust_id,
	   cust_contact,
	   cust_email,
	   cust_name,
	   cust_address,
	   cust_city,
	   cust_state,
	   cust_zip,
	   cust_country
FROM custnew;

若这个表为空,则不插入,这是合法的,也可以在INSERT和SELECT省略cust_id这一列,有MySQL生成值

INSERT和SELECT中的列名不一定要相同,MySQL使用的是列的位置,SELECT中的第一列将用来填充INSERT指定的第一列……这对于从使用不同列名的表导入数据是非常有用的

在INSERT SELECT中SELECT语句可包含WHERE子句来过滤插入的数据

第二十章

更新(修改)表中的数据可以使用UPDATE,可以更新特定的行或者更新所有行

UPDATE主要由更新的表、列名和新的值、确定更新行的过滤条件三部分组成,注意WHERE子句的重要性

如客户10005现在有了电子邮件地址,需要更新

UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

若没有WHERE子句,将会更新所有行

更新更多的列

UPDATE customers
SET cust_name = 'The Fudds',
	cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

在UPDATE中也可以使用子查询

在更新多行时,为避免一行或多行出现错误而取消整个操作(取消后之前更新的行恢复到原有值)可以写作UPDATE IGNORE customers…

为删除某个列的值,可以将其设为NULL(前提是允许为NULL)

UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;

用DELETE删除特定行或者所有行,同样不要省略WHERE子句

DELETE FROM customers
WHERE cust_id = 10006;

若不加WHERE子句,将删除表中每个客户

DELETE删除整行而不是列,删除列使用UPDATE,但DELETE不删除表本身

删除所有行用TRUNCATE TABLE语句,速度更快(实际是删除原来的表并重新生成一个表,而不是逐行删除)

更新和删除的原则

· 除非确实打算修改每行,否则必须使用WHERE子句

· 保证每个表都有主键,尽可能像WHERE子句那样使用

· 可以先使用SELECT来测试使用的WHERE是否过滤正确的行,保证正确性

· 使用强制实施引用完整性的数据库,这样MySQL将不允许删除具有与其他表相关联数据的行

· MySQL没有撤销按钮,小心使用UPDATE和DELETE

第二十一章

可以用CREATE TABLE来创建表,事实上,使用交互式工具时,界面工具生成相应的MySQL语句并执行

创建一个新表需要给出表的名字,表列的名字和定义,或者可以给出更多信息

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,
    custy_email		char(255)	NULL,
    PRIMARY KEY (cust_id)
)	ENGINE=InnoDB;

缩进的大小和各列之间的空格没有规定

表名紧跟在CREATE TABLE后,表的定义在圆括号之中,每列的定义以列名开始,后跟列的数据类型,表的主键可以在创建表时指定

在创建新表时,指定的表名必须不存在,若要防止意外覆盖已有的表,先手工删除该表,可以在表名后给出IF NOT EXISIT,检查表名是否存在并在表名不存在时创建

如果指定了NOT NULL,则在插入时必须有值,NULL为默认设置,如果不指定NOT NULL,则认为指定的是NULL

要注意NULL不是空串,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;

主键也可以在创建表之后定义,只能使用不允许NULL的列

AUTO_INCREMENT告诉MySQL,本列每增加一行时自动增量,这样给每个行分配一个唯一的cust_id,每个表只允许一个AUTO_INCREMENT列,而且它必须被索引(如:通过使它成为主键)

覆盖AUTO_INCREMENT,可以简单地在INSERT语句中指定一个值,只要之前没有出现过即可,该值将代替自动生成的值,后续的增量将开始使用该手工插入的值

SELECT last_insert_id()

该语句返回最后一个AUTO_INCREMENT值

如果插入行时没有给出值,MySQL允许指定此时使用的默认值

CREATE TABLE orderitems
(
	order_num	int			NOT NULL,
    order_item	char(10)	NOT NULL,
    prod_id		char(10)	NOT NULL,
    quantity	int			NOT NULL DEFAULT 1,	
    item_price	decimal(8,2)NOT NULL,
    PRIMARY KEY (order_num, order_item)
)	ENGINE=InnoDB; 

在不给出数量的情况下使用数量1,但不允许使用函数作为默认值,只支持常量

MySQL有一个具体管理和处理数据的内部引擎 ,在使用CREATE TABLE或者SELECT等语句时,由该引擎来处理请求,MySQL有多种引擎,具有不同的功能和特性,如果忽略ENGINE=语句,则使用默认引擎(很可能是MyISAM)

· InnoDB是一个可靠的事务处理引擎,不支持全文本搜索

· MEMORY在功能上等同于MyISAM,但数据存储在内存上(不是磁盘)中,速度很快,适合于临时表

· MyISAM性能极高,支持全文本搜索,但不支持事务处理

引擎类型可以混用,如一个表用MyISAM,另一个用InnoDB,但有一个缺陷,外键不能跨引擎,即用一个引擎的表不能引用使用不同引擎的表的外键

可使用ALTER TABLE更新表

给vendors表增加一个名为vend_phone的列,必须明确其数据类型

ALTER TABLE vendors
ADD vend_phone CHAR(20);

删除刚刚添加的列

ALTER TABLE vendors
DROP COLUMN vend_phone;

还可以定义外键

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) 
REFERENCES orders (order_num);

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products
FOREIGN KEY (prod_id) 
REFERENCES products (prod_id);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (cust_id) 
REFERENCES customers (cust_id);

ALTER TABLE products
ADD CONSTRAINT fk_products_vendors
FOREIGN KEY (vend_id) 
REFERENCES vendors (vend_id);

如果是对单个表进行多个更改,可以使用单条ALTER TABLE语句,每个更改用逗号分隔

复杂的表结构更改一般需要手动删除过程,应该在使用ALTER TABLE改动表前做好备份,数据库表的更改不能撤销

删除表(整个表而不是其内容),不能撤销,永久删除

DROP TABLE customers2;

重命名表

RENAME TABLE customers2 TO customers;

对多个表重命名

RENAME TABLE backup_customers TO customers,
			 backup_vendors TO vendors,
			 backup_products TO products;

第二十二章

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

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';

如果把上述整个查询包装成一个名为productcustomers的虚拟表就会方便很多

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

作为视图,不包含表中的任何列或数据,它包含的是一个SQL查询

可以用与表基本相同的方式利用它们,可以执行SELECT操作,过滤排序数据,联结到其他表或者视图,等等

使用视图可以简化操作,在编写查询后可以多次重用,它仅使用表的一部分,保护数据

视图的使用规则和限制

· 命名要唯一,但数目没有限制

· 视图可以嵌套,可以从其他视图中检索数据的查询来构造一个视图

· ORDER BY可以用在视图中,如果该视图的SELECT语句中也含有ORDER BY,则原有的ORDER BY会被覆盖

视图用CREATE VIEW语句来创建

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

用DROP VIEW viewname; 删除视图

更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW,如果要更新的视图不存在,则第2条更新语句会创建一个视图,若存在,则会替换

视图最常见的应用之一是隐藏复杂的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;    

这个视图返回已订购了任意产品的所有客户的列表

执行

SELECT * FROM productcustomers

将列出订购了任意产品的客户

为了检索产品TNT2的客户

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

可以用视图重新格式化检索出的数据

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

这条语句使用与以前的SELECT语句相同的查询创建视图,为了检索出以创建所有邮件标签的数据

SELECT *
FROM vendorlocations;

可以用视图过滤不想要的数据,比如过滤没有电子邮件的客户

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

现在可以像使用其他表一样使用该视图

SELECT *
FROM customeremaillist;

如果在使用视图时也有WHERE子句,则和视图中的WHERE子句会自动组合

视图对于简化计算字段的使用特别有用

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
	   prod_id,
	   quantity,
	   item_price,
	   quantity*item_price AS expanded_price
FROM orderitems;

为检索订单20005的详细内容

SELECT *
FROM orderitemexpanded
WHERE order_num = 20005;

视图是可更新的,可以使用INSERT、UPDATE和DELETE,更新一个视图将更新其基表,如果对视图增加或删除行,实际是对其基表增加或删除行,但并非所有视图都可以更新,如果视图定义中有如下操作,则不能进行视图更新

· 分组(GROUP BY和HAVING)

· 联结

· 子查询

·

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

· DISTINCT

· 导出(计算)列

第二十三章

存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合

使用存储过程的优点:简单、安全、高性能

MySQL把执行存储过程称为调用,使用CALL接受存储过程的名字以及需要传递给它的任意此参数

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);

存储过程可以显示结果,也可以不显示结果

创建一个返回产品平均价格的存储过程

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;

如果存储过程接受参数,可以在()中列出来,BEGIN和END用来限定存储过程体,这个创建的过程不返回数据,只是为以后使用而创建

在命令行的模式下,由于代码里有两个分号,会出现错误,可以临时更改分隔符,除了\以外,任何字符都可以用作语句分隔符

DELIMITER //

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END //

DELIMITER ;

使用这个过程

CALL productpricing();

存储过程实际上是一种函数

存储过程创建以后,被保存在服务器上供使用,直至被删除,删除语句如下

DROP PROCEDURE productpricing;

注意最后没有()

如果不确定要删除的过程是否存在,使用DROP PROCEDURE IF EXISTS

一般存储过程并不显示结果,而是把结果传给指定的变量

以下是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;

SELECT语句中,INTO子句和FROM子句顺序可调换

此存储过程接受3个参数,每个参数必须具有指定的类型,这里使用十进制,关键字OUT指出相应的参数用来从存储过程中传出一个值,MySQL还支持IN(传递给存储过程)、INOUT(传入和传出),SELECT语句检索值,并通过INTO保存到相应变量

调用此存储过程

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);

为了显示检索出的产品的平均价格

SELECT @priceaverage;

获得三个值

SELECT @pricehigh, @pricelow, @priceaverage;

使用IN和OUT参数,ordertotal接受订单号并返回该订单的合计

CREATE PROCEDURE ordertotal(
	IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT Sum(item_price*quantity)
	FROM order_items
	WHERE order_num = onumber
	INTO ototal;
END;

INTO子句可以跟在SELECT之后

调用新的存储过程

CALL ordertotal(20005, @total);

显示合计

SELECT @total;

为了得到另一个订单的合计显示,再次调用存储过程,重新显示变量

CALL ordertotal(20009, @total);
SELECT @total;

建立智能存储过程

CREATE PROCEDURE ordertotal(
	IN onumber INT
	IN taxable BOOLEAN
	OUT ototal DECIMAL(8,2)
)	COMMENT 'Obtain order total, optionally adding tax'
BEGIN

	DECLARE total DECIMAL(8,2);
	DECLARE taxrate INT DEFAULT 6;
	
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO total;
	
	IF taxable THEN
		SELECT total+(total/100*taxrate) INTO total;
	END IF;
	
	SELECT total INTO ototal;
	
END;

IF语句检查是否需要增加营业税到局部变量total,最后用另一个SELECT语句将total保存到ototal

COMMENT值不是必须的,如果给出将在SHOW PROCEDURE STATUS的结果中显示

试验此存储过程

CALL ordertotal(20005, 0, @total);
SELECT @total;
CALL ordertotal(20005, 1, @total);
SELECT @total;

BOOLEAN值非零(包括负数)为真,0为假

IF还支持ELSEIF和ELSE(前者使用THEN子句,后者不使用)

为显示用来创建一个存储过程的CREATE语句

SHOW CREATE PROCEDURE ordertotal;

为了获得包括何时、由谁创建等详细信息的存储过程列表

SHOW PROCEDURE STATUS

上述语句列出所有存储过程,可以用LIKE过滤

SHOW PROCEDURE STATUS LIKE 'ordertotal';
《MySQL必知必会》19~23章《MySQL必知必会》19~23章 tmhhh 发布了76 篇原创文章 · 获赞 7 · 访问量 9512 私信 关注
上一篇:hibernate实体之间的关联关系(一对多,多对多)


下一篇:Mybatis(上)