《SQL必知必会》笔记（施工中）

MySQL安装

安装步骤

 以下配置默认：

 

 

 win+R键打开并输入services.msc，调出服务界面，选择MySQL服务，将登录页选成“本地系统帐户”后，再切到安装界面。

 点击execute：

 安装完成

验证安装完成

进入安装目录：

 输入cmd：

进入命令行：

 

启动报错：error 1045

 

启动MySQL： 

 修改my.ini文件：

在[mysqld]下添加skip-grant-tables

 

 参考：解决mysql：ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO/YES) - html中文网

然后成功启动：

《SQL必知必会》学习笔记

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

第7课 创建计算字段

select concat(vend_name, '(', vend_country, ')')
	as vend_title
from vendors
order by vend_name;

select prod_id,
	quantity,
    item_price,
    quantity*item_price as expanded_price
from orderitems
where order_num=20008;

第13课 创建高级联结

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

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

select customers.cust_id, orders.order_num
from customers left outer join orders
on customers.cust_id=orders.cust_id

小结：

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

第14课 组合查询

并（UNION）

select cust_name, cust_contact, cust_email
from customers
where cust_state in ('IL','IN','MI')
union
select cust_name, cust_contact, cust_email
from customers
where 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 as
select * from customers;

小结：

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

第16课 更新和删除数据

16.1 更新数据（UPDATE）

#更新一列
update customers
set cust_email='kim@thetoystore.com'
where cust_id='1000000005';

#更新多列
update customers
set cust_contact='Sam Roberts', cust_email='sam@toyland.com'
where cust_id='1000000006';

16.2 删除数据（DELETE）

-- 从表中删除一行
delete from customers
where 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 vendors
add vend_phone char(20);

# 删除列
alter table vendors
drop 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 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 cust_name, cust_contact
from productcustomers
where prod_id='RGAN01';

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

select concat(vend_name, '(', vend_country, ')')
	as vend_title
from vendors
order by vend_name;

# 把拼接转换为视图
create view vendorlocations as
select concat(vend_name, '(', vend_country, ')')
	as vend_title
from vendors;

# 从视图中检索
select *
from vendorlocations

18.2.3 用视图过滤不想要的数据

create view CustomerEmailList as
select cust_id, cust_name, cust_email
from customers
where cust_email is not null;

# 从视图中检索
select *
from CustomerEmailList;

18.2.4 使用视图与计算字段

create view OrderItemsExpanded as
select order_num,
	prod_id,
    quantity,
    item_price,
    quantity*item_price as expanded_price
from orderitems;

# 从视图中检索
select *
from OrderItemsExpanded
where 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()
	begin
		select avg(prod_price) as priceaverage
		from 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)
)
begin
	select min(prod_price)
    into p1
    from products;
    select max(prod_price)
    into ph
    from products;
    select avg(prod_price)
    into pa
	from 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)
)
begin
	select 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 taxable
delimiter //
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文档。

第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 transaction
insert 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()
begin
	declare ordernumbers cursor
	for
	select 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 cursor
	declare ordernumbers cursor
	for
	select 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 cursor
	declare ordernumbers cursor
	for
	select 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 cursor
	declare ordernumbers cursor
	for
	select 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_id			char(10)	not null primary key,
    vned_name		char(50)	not null,
    vend_address	char(50)	null,
    vend_city		char(50)	null,
    vend_state		char(50)	null,
    vend_zip		char(10)	null,
    vend_country	char(50)	null
);

-- 给表的vend_id列定义使其成为主键
alter table vendors
add constraint primary key(vend_id);

>22.1.2 外键（FOREIGN KEY）

-- 定义外键
create table orders
(
	order_num	integer		not null primary key,
    order_date	datetime	not null,
    cust_id		char(10)	not null references customers(cust_id)
);

-- 定义外键
alter table orders
add constraint
foreign key(cust_id) references customers(cust_id);

>22.1.3 唯一约束（UNIQUE）

>22.1.4 检查约束

-- 检查约束保证所有物品的数量大于0
create table orders
(
	order_num	integer		not null,
    order_item	integer		not null,
    prod_id		char(10)	not null,
    quantity	integer		not null check(quantity>0),
    item_price	money		not null	-- money goes error in MySQL
);

-- 检查名为gender的列只包含M或F
alter table customers
add constaint check(gender like '[MF]')	-- check goes error in MySQL

22.2 索引（INDEX）

-- 在Products表的产品名列上创建一个简单索引
create index prod_name_ind
on products(prod_name);

22.3 触发器（TRIGGER）

-- 创建触发器
create trigger newproduct 
after insert on products
for each row select 'Product added';

-- 删除触发器
drop trigger newproduct;

-- 创建insert触发器
create trigger neworder
after insert on orders
for 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 orders
for each row
begin
	insert into archive_orders(order_num, order_date, cust_id)
    values(old.order_num, old.order_date, old.cust_id);
end //	-- ?
delimiter ;

-- 创建update触发器
create trigger updatevendor
before update on vendors
for each row set new.vend_state = upper(new.vend_state);

小结：

本课讲授如何使用SQL的一些高级特性。约束是实施引用完整性的重要部分，索引可改善数据检索的性能，触发器可以用来执行运行前后的处理，安全选项可用来管理数据访问。不同的DBMS可能会以不同的形式提供这些特性，更详细的信息请参阅具体的DBMS文档。