返回
网站建设知识
MySQL必知必会笔记
2025-07-22 10:01  点击:0

温馨提示：本文代码全部以MySQL 5.7实现。

第7课 创建计算字段

select concat(vend_name, '(', vend_country, ')')as vend_titlefrom vendorsorder by vend_name;
select prod_id,quantity,    item_price,    quantity*item_price as expanded_pricefrom orderitemswhere order_num=20008;

第13课 创建高级联结

自联结（SELF-JOIN）、自然联结（NATURAL JOIN）和外联结（OUTER JOIN）。

外联结有两种基本形式：左外联结和右外联结。

select customers.cust_id, orders.order_numfrom customers left outer join orderson customers.cust_id=orders.cust_id

小结：

本课是上一课的延续，首先讲授了如何以及为什么使用别名，然后讨论不同的联结类型以及每类联结所使用的语法。我们还介绍了如何与联结一起使用聚集函数，以及在使用联结时应该注意的问题。

第14课 组合查询

并（UNION）

select cust_name, cust_contact, cust_emailfrom customerswhere cust_state in ('IL','IN','MI')unionselect cust_name, cust_contact, cust_emailfrom customerswhere cust_name = 'Fun4All';
小结：

这一课讲授如何用UNION操作符来组合SELECT语句。利用UNION，可以把多条查询的结果作为一条组合查询返回，不管结果中有无重复。使用UNION可极大地简化复杂的WHERE子句，简化从多个表中检索数据的工作。

第15课 插入数据

插入（INSERT）

15.1.1 插入完整的行

insert into customers (cust_id,cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)value ('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);
提示：INSERT通常只插入一行。
15.2 从一个表复制到另一个表

SELECT INTO（MySQL不支持）

create table custcopy asselect * from customers;
小结：

这一课介绍如何将行插入到数据库中。我们学习了使用INSERT的几种方法，为什么要明确使用列名，如何用INSERT SELECT从其它表中导入行，如何用SELECT INTO将行导出到一个新表。下一课将讲述如何使用UPDATE和DELETE进一步操作数据。

第16课 更新和删除数据

16.1 更新数据（UPDATE）

#更新一列update customersset cust_email='kim@thetoystore'where cust_id='1000000005';
#更新多列update customersset cust_contact='Sam Roberts', cust_email='sam@toyland'where cust_id='1000000006';

16.2 删除数据（DELETE）

delete from customerswhere cust_id='1000000006';
删除表中所有行（TRUNCATE TABLE）

小结：

这一课讲述了如何使用UPDATE和DELETE语句处理表中的数据。我们学习了这些语句的语法，知道了它们可能存在的危险，了解了为什么WHERE子句对UPDATE和DELETE语句很重要，还学习了为保证数据安全而应遵循的一些指导原则。


第17课 创建和操纵表

17.1 创建表（CREATE TABLE）

create table products(prod_id char(10) not null,vend_id char(10) not null,prod_name char(254) not null,prod_price decimal(8,2) not null,prod_desc text(1000) null);
17.1.3 指定默认值（DEFAULT）
create table orderitems(order_num integer not null,    order_item integer not null,    prod_id char(10) not null,    quantity integer not null default 1,    item_price decimal(8,2) not null);
将系统日期用作默认日期：DEFAULT CURRENT_DATE()

17.2 更新表（ALTER TABLE）

#增加列alter table vendorsadd vend_phone char(20);
# 删除列alter table vendorsdrop column vend_phone;

17.3 删除表（DROP TABLE）

drop table custcopy;
17.4 重命名表
重命名表（RENAME）

小结：

这一课介绍了几条新的SQL语句。CREATE TABLE用来创建新表，ALTER TABLE用来更改表列（或其他诸如约束或索引等对象），而DROP TABLE用来完整地删除一个表。这些语句必须小心使用，并且应该在备份后使用。由于这些语句的语法在不同的DBMS中有所不同，所以更详细的信息请参阅相应的DBMS文档。

第18课 使用视图

18.2 创建视图

创建视图（CREATE VIEW）

删除视图（DROP VIEW）

18.2.1 利用视图简化复杂的联结

创建一个名为ProductCustomers的视图：

create view productcustomers asselect cust_name, cust_contact, prod_idfrom customers, orders, orderitemswhere customers.cust_id=orders.cust_idand orderitems.order_num=orders.order_num;
从视图中检索：
select cust_name, cust_contactfrom productcustomerswhere prod_id='RGAN01';

18.2.2 用视图重新格式化检索出的数据
拼接字段格式的例子

select concat(vend_name, '(', vend_country, ')')as vend_titlefrom vendorsorder by vend_name;
# 把拼接转换为视图create view vendorlocations asselect concat(vend_name, '(', vend_country, ')')as vend_titlefrom vendors;
# 从视图中检索select *from vendorlocations
18.2.3 用视图过滤不想要的数据
create view CustomerEmailList asselect cust_id, cust_name, cust_emailfrom customerswhere cust_email is not null;
# 从视图中检索select *from CustomerEmailList;
18.2.4 使用视图与计算字段
create view OrderItemsExpanded asselect order_num,prod_id,    quantity,    item_price,    quantity*item_price as expanded_pricefrom orderitems;
# 从视图中检索select *from OrderItemsExpandedwhere order_num=20008;
小结：

视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装SELECT语句的层次，可用来简化数据处理，重新格式化或保护基础数据。

第19课（第23课） 使用存储过程（《MySQL必知必会》）

23.2 为什么要使用存储过程

有三个主要好处，即简单、安全、高性能。

23.3 使用存储过程
23.3.1 执行存储过程（CALL PROCEDURE）

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

23.3.2 创建存储过程(CREATE PROCEDURE)

-- mysql命令行客户机的分隔符delimiter //-- 告诉命令行实用程序使用//作为新的语句结束分隔符create procedure productpricing()beginselect avg(prod_price) as priceaveragefrom products;end //delimiter ;-- 恢复为原来的语句分隔符
-- 使用这个存储过程call productpricing();
23.3.3 删除存储过程(DROP PROCEDURE)
drop procedure productpricing;
23.3.4 使用参数
-- 创建带参数的存储过程delimiter //create procedure productpricing(out p1 decimal(8,2),    out ph decimal(8,2),    out pa decimal(8,2))beginselect min(prod_price)    into p1    from products;    select max(prod_price)    into ph    from products;    select avg(prod_price)    into pafrom products;end //delimiter ;
-- 使用带参数的存储过程call productpricing(@pricelow,@pricehigh,                    @priceaverage);
-- 显示检索出的3个值select @pricehigh, @pricelow, @priceaverage;
-- 使用in和out参数delimiter //create procedure ordertotal(in onumber int,    out ototal decimal(8,2))beginselect sum(item_price*quantity)    from orderitems    where order_num=onumber    into ototal;end //delimiter ;
-- 调用并显示call ordertotal(20005, @total);select @total;

23.3.5 建立智能存储过程

-- 建立存储过程：获得合计，把营业税有条件地添加到合计，返回合计-- 先删除同名的存储过程drop procedure ordertatal;-- Name: ordertotal-- Parameters: onumber = order number-- taxable = 0 if not taxable, 1 if taxabledelimiter //create procedure ordertotal(in onumber int,    in taxable boolean,    out ototal decimal(8,2)) comment 'Obtain order total, optionally adding tax'begin-- declare定义局部变量-- declare variable for total    declare total decimal(8,2);    -- declare tax percentage    declare taxrate int default 6;    -- get the order total    select sum(item_price*quantity)    from orderitems    where order_num = onumber    into total;    -- is this taxable?    if taxable then-- yes, so add taxrate to the total        select total+(total/100*taxrate) into total;end if;    -- and finally, save to out variable    select total into ototal;end //delimiter ;
-- 调用并显示call ordertotal(20005, 1, @total);select @total;

23.3.6 检查存储过程(SHOW CREATE PROCEDURE)

show create procedure ordertotal;
-- 获得全部存储过程详细信息show procedure status;
-- 获得该存储过程详细信息show procedure status like 'ordertotal';
小结：
这一课介绍了什么是存储过程，为什么使用存储过程。我们介绍了招行存储过程的语法，使用存储过程的一些方法。存储过程是个相当重要的主题，一课内容无法全部涉及。各种DBMS对存储过程的实现不一，你使用的DBMS可能提供了一些这里提到的功能，也有其他未提及的功能，更详细的介绍请参阅具体的DBMS文档。[page]

第20课 管理事务处理
20.2 事务处理
20.2.1 使用ROLLBACK
delete from orders;rollback;
20.2.2 使用COMMIT
start transaction;delete from orderitems where order_num = 20009;delete from orders where order_num = 20009;commit;
20.2.3 使用保留点
-- 设置保留点savepoint delete1;
-- 回退到保留点rollback to delete1;
-- SQL Server例子，待以后改写成MySQL的begin transactioninsert into customes(cust_id, cust_name)values('1000000010','Toys Emporium');save transaction startorder;insert into orders(order_num, order_date, cust_id)values(20100,'2001/12/1','1000000010');if @@error<>() rollback transaction startorder;insert into orderitems(order_num, order_item, prod_id, quantity, item_price)values(20100,1,'BR01',100,5.49);if @@error<>() rollback transaction startorder;insert into orderitems(order_num, order_item, prod_id, quantity, item_price)values(20100,2,'BR03',100,10.99);if @@error<>() rollback transaction startorder;commit transaction
小结：
这一课介绍了事务是必须完整执行的SQL语句块。我们学习了如何使用COMMIT和ROLLBACK语句对何时写数据、何时撤销进行明确的管理；还学习了如何使用保留点，更好地控制回退操作。事务处理是个相当重要的主题，一课内容无法全部涉及。各种DBMS对事务处理的实现不同，详细内容请参考具体的DBMS文档。
第21课 使用游标
游标（CURSOR）
21.2 使用游标
21.2.1 创建游标
delimiter //create procedure processorders()begindeclare ordernumbers cursorforselect order_num from orders;end //delimiter ;
21.2.2 使用游标（FETCH）
open ordernumbers
-- 从游标中检索第一行delimiter //create procedure processorders()begin-- declare local variables    declare o int;    -- declare the cursordeclare ordernumbers cursorforselect order_num from orders;    -- open the cursor    open ordernumbers;    -- get order number    fetch ordernumbers into o;    -- close the cursor    close ordernumbers;end //delimiter ;
-- 循环检索数据delimiter //create procedure processorders()begin-- declare local variables    declare done boolean default 0;    declare o int;    -- declare the cursordeclare ordernumbers cursorforselect order_num from orders;    -- declare continue handler    declare continue handler for sqlstate '02000' set done=1;    -- open the cursor    open ordernumbers;    -- loop through all rows    repeat-- get order number        fetch ordernumbers into o;-- end of loop    until done end repeat;    -- close the cursor    close ordernumbers;end //delimiter ;
-- 循环检索数据并进行处理delimiter //create procedure processorders()begin-- declare local variables    declare done boolean default 0;    declare o int;    declare t decimal(8,2);    -- declare the cursordeclare ordernumbers cursorforselect order_num from orders;    -- declare continue handler    declare continue handler for sqlstate '02000' set done=1;    -- create a table to store the results    create table if not exists ordertotals (order_num int, total decimal(8,2));    -- open the cursor    open ordernumbers;    -- loop through all rows    repeat-- get order number        fetch ordernumbers into o;        -- get the total for this order        call ordertotal(o, 1, t);        -- insert order and total into ordertotals        insert into ordertotals(order_num, total)        values(o, t);-- end of loop    until done end repeat;    -- close the cursor    close ordernumbers;end //delimiter ;
-- 调用并显示call processorders();select * from ordertotals;
21.2.3 关闭游标
close ordernumbers
小结：
我们在本课讲授了什么是游标，为什么使用游标。你使用的DBMS可能会提供某种形式的游标，以及这里没有提及的功能。更详细的内容请参阅具体的DBMS文档。

第22课 高级SQL特性
22.1 约束（CONSTRAINT）
>22.1.1 主键(PRIMARY KEY)
-- 创建主键create table vendors(vend_idchar(10)not null primary key,    vned_namechar(50)not null,    vend_addresschar(50)null,    vend_citychar(50)null,    vend_statechar(50)null,    vend_zipchar(10)null,    vend_countrychar(50)null);
-- 给表的vend_id列定义使其成为主键alter table vendorsadd constraint primary key(vend_id);
>22.1.2 外键（FOREIGN KEY）
-- 定义外键create table orders(order_numintegernot null primary key,    order_datedatetimenot null,    cust_idchar(10)not null references customers(cust_id));
-- 定义外键alter table ordersadd constraintforeign key(cust_id) references customers(cust_id);
>22.1.3 唯一约束（UNIQUE）
>22.1.4 检查约束
-- 检查约束保证所有物品的数量大于0create table orders(order_numintegernot null,    order_itemintegernot null,    prod_idchar(10)not null,    quantityintegernot null check(quantity>0),    item_pricemoneynot null-- money goes error in MySQL);
-- 检查名为gender的列只包含M或Falter table customersadd constaint check(gender like '[MF]')-- check goes error in MySQL
22.2 索引（INDEX）
-- 在Products表的产品名列上创建一个简单索引create index prod_name_indon products(prod_name);
22.3 触发器（TRIGGER）
-- 创建触发器create trigger newproduct after insert on productsfor each row select 'Product added';
-- 删除触发器drop trigger newproduct;
-- 创建insert触发器create trigger neworderafter insert on ordersfor each row select new.order_num;-- 不允许触发器返回order_num?-- 测试此触发器insert into orders(order_date, cust_id)values(now(), 10001);
-- 创建delete触发器delimiter //create trigger deleteorder before delete on ordersfor each rowbegininsert into archive_orders(order_num, order_date, cust_id)    values(old.order_num, old.order_date, old.cust_id);end //-- ?delimiter ;
-- 创建update触发器create trigger updatevendorbefore update on vendorsfor each row set new.vend_state = upper(new.vend_state);
小结：
本课讲授如何使用SQL的一些高级特性。约束是实施引用完整性的重要部分，索引可改善数据检索的性能，触发器可以用来执行运行前后的处理，安全选项可用来管理数据访问。不同的DBMS可能会以不同的形式提供这些特性，更详细的信息请参阅具体的DBMS文档。