Vendors表:
| vend_id | vend_name | vend_address | vend_city | vend_state | vend_zip | vend_country |
| BRE02 | Bear Emporium | 500 Park Street | Anytown | OH | 44333 | USA |
| BRS01 | Bears R Us | 123 Main Street | Bear Town | MI | 44444 | USA |
| DLL01 | Doll House Inc. | 555 High Street | Dollsville | CA | 99999 | USA |
| FNG01 | Fun and Games | 42 Galaxy Road | London | N | N16 6PS | England |
| FRB01 | Furball Inc. | 1000 5th Avenue | New York | NY | 11111 | USA |
| JTS01 | Jouets et ours | 1 Rue Amusement | Paris | N | 45678 | France |
Products表:
| prod_id | vend_id | prod_name | prod_price | prod_desc |
| BNBG01 | DLL01 | Fish bean bag toy | 3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02 | DLL01 | Bird bean bag toy | 3.49 | Bird bean bag toy, eggs are not included |
| BNBG03 | DLL01 | Rabbit bean bag toy | 3.49 | Rabbit bean bag toy, comes with bean bag carrots |
| BR01 | BRS01 | 8 inch teddy bear | 5.99 | 8 inch teddy bear, comes with cap and jacket |
| BR02 | BRS01 | 12 inch teddy bear | 8.99 | 12 inch teddy bear, comes with cap and jacket |
| BR03 | BRS01 | 18 inch teddy bear | 11.99 | 18 inch teddy bear, comes with cap and jacket |
| RGAN01 | DLL01 | Raggedy Ann | 4.99 | 18 inch Raggedy Ann doll |
| RYL01 | FNG01 | King doll | 9.49 | 12 inch king doll with royal garments and crown |
| RYL02 | FNG01 | Queen doll | 9.49 | 12 inch queen doll with royal garments and crown |
Customers表:
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
| 1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | 44444 | USA | John Smith | sales@villagetoys.com |
| 1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | 43333 | USA | Michelle Green | N |
| 1000000003 | Fun4All | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | jjones@fun4all.com |
| 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | N |
Orders表:
| order_num | order_date | cust_id |
| 20005 | 2004/5/1 0:00 | 1000000001 |
| 20006 | 2004/1/12 0:00 | 1000000003 |
| 20007 | 2004/1/30 0:00 | 1000000004 |
| 20008 | 2004/2/3 0:00 | 1000000005 |
| 20009 | 2004/2/8 0:00 | 1000000001 |
Orderitems表:
| order_num | order_item | prod_id | quantity | item_price |
| 20005 | 1 | BR01 | 100 | 5.49 |
| 20005 | 2 | BR03 | 100 | 10.99 |
| 20006 | 1 | BR01 | 20 | 5.99 |
| 20006 | 2 | BR02 | 10 | 8.99 |
| 20006 | 3 | BR03 | 10 | 11.99 |
| 20007 | 1 | BR03 | 50 | 11.49 |
| 20007 | 2 | BNBG01 | 100 | 2.99 |
| 20007 | 3 | BNBG02 | 100 | 2.99 |
| 20007 | 4 | BNBG03 | 100 | 2.99 |
| 20007 | 5 | RGAN01 | 50 | 4.49 |
| 20008 | 1 | RGAN01 | 5 | 4.99 |
| 20008 | 2 | BR03 | 5 | 11.99 |
| 20008 | 3 | BNBG01 | 10 | 3.49 |
| 20008 | 4 | BNBG02 | 10 | 3.49 |
| 20008 | 5 | BNBG03 | 10 | 3.49 |
| 20009 | 1 | BNBG01 | 250 | 2.49 |
| 20009 | 2 | BNBG02 | 250 | 2.49 |
| 20009 | 3 | BNBG03 | 250 | 2.49 |
检索
1. select prod_id, prod_name,prod_price from products;
2. select * from products;
排序检索:
1. select prod_id, prod_price, prod_name from products order by prod_price; 按价格由低到高排序
2. select prod_id,prod_price,prod_name from products order by prod_price DESC, prod_id; 按价格高到低,id低到高排序
3. select * from products orber by 2,3; 按照第2,3行排序
过滤数据:
1. select prod_name,prod_price from products where prod_price=3.49;
2. select prod_name,prod_price from products where prod_price<>3.49;
等同于select prod_name,prod_price from products where prod_price!=3.49;
3. select prod_name,prod_price from products where prod_price between 5 and 10;
4. select prod_name,prod_price,vend_id from products where (vend_id='DLL01' or vend_id='BRS01') and prod_price>=10; 其中括号不可省,否则结果错误,因为and会有优先权
5. select prod_name,prod_price,vend_id from products where vend_id IN('DLL01','BRS01') order by prod_name;
等同于:select prod_name,prod_price,vend_id from products where vend_id='DLL01' OR vend_id='BRS01' order by prod_name;
6. select prod_name, vend_id from products where NOT vend_id='DLL01' order by prod_name;
通配符:
%表示通配0个以上的字符
1. select prod_id,prod_name from products where prod_name like 'fish%';
2. select prod_id,prod_name from products where prod_name like '%bean bag%';
3. select prod_name from products where prod_name like 'F%Y%'; %表示0个以上,包含0
_ 表示前面匹配单个字符.
4. select prod_id,prod_name from products where prod_name like '__ inch teddy bear'; 12,18能被匹配, 个位数的就不会匹配
想想和它的区别: select prod_id,prod_name from products where prod_name like '% inch teddy bear'; 可以匹配个位数的
5. select concat(vend_name,' (',vend_country,' )') as vend_title from vendors order by vend_name; 组成一个新的列
6. select prod_id, quantity,item_price,quantity*item_price as expanded_price from orderitems where order_num=20008;
使用数据处理函数:
LEFT(), RIGHT():返回串左/右边的字符
LENGTH(): 返回串的长度
LOWER()/UPPER(): 小写/大写
LTRIM(): 去掉串左边的空格, RTRIM():去掉
1. select vend_name,upper(vend_name) as vend_name_upcase from vendors order by vend_name; 将字符大写
2. select cust_name,cust_contact from customers where soundex(cust_contact)=soundex('Michael green'); 模糊匹配
3. select order_num from orders where year(order_date)=2004;
sql server的语句不同: select * from orders where datepart(yy,order_date)=2004;
oracle的语句:select * from orders where to_number(to_char(order_date,'YY'))=2004;
4. select * from orders where to_days('2004-05-01')-to_days(order_date)<=90;
数值处理函数: ABS(), COS(),EXP(),PI(),SIN(),SQRT(),TAN()
汇总数据:
聚集函数: AVG(), COUNT(),MAX(),MIN(),SUM()
1. select avg(prod_price) as average from products where vend_id='DLL01';
2. select count(*) as num_cust from customers;计算所有行数,不管里面数据是否有空
select count(cust_email) as num_cust from customers;如果有的行 ename为空,将不计数
select count(*) from customers where cust_email is NULL; 注意这个用法, 不能用cust_email='NULL'.
3. select max(prod_price) as max_price from products; select min(prod_price) as max_price from products;
4. select sum(quantity) as items_ordered from orderitems where order_num=20005;
5. select avg(distinct prod_price) as avg_price from products where vend_id='DLL01';排除掉重复数据,所以与下面的值不同:
select avg(prod_price) as avg_price from products where vend_id='DLL01';
DISTINCT可以通用于所有的汇总数据函数
组合聚集函数:
select count(*) as num_items,min(prod_price) as price_min, max(prod_pric e) as price_max,avg(prod_price) as price_avg from products;
分组数据: group 可以理解为每个....
1. group by 可以包含任意数目的列,
2. 如果group by子句中嵌套了分组, 数据将在最后规定的分组上进行汇总.换句话说,建立分组时,指定的所有列都一起计算.
3. group by子句中列出的每个列都必须是检索列或有效的表达式,不能是聚集函数.如果在select中使用表达式,则必须在group by子句中指定相同的表达式.不能使用别名
4. 除聚集计算语句外,select中没格列都必须在groupby子句中给出.
6. 如果分组咧中有null,则null将作为一个分组返回,如果有多行,将它们分为一组
1. select vend_id,count(*) as num_prods from products group by vend_id; 计算每个供应商供应几件商品
过滤分组:用having来过滤, 和where作用一样,但是where不能用于分组,只能过滤列. 即,where过滤列, having过滤分组
也可以说,where在分组之前进行过滤,而having在分组之后进行过滤
1. select cust_id,count(*) as orders from orders group by cust_id having count(*)>=2;每个客户订单号大于2个的分组
2. select vend_id,count(*) as num_prods from products where prod_price>=4 group by vend_id having count(*) >=2; 2个以上价格为4以上的产品的供应商
拆解为 价格为4以上的供应商, 用group by表示, 人数超过2个 用having count(*)>=2
总结 ...的部门,或者...的人, 通常,部门和人就是group by的对象
超过n个的数据, 就用having...
排序:
3. select order_num,count(*) as items from orderitems group by order_num having count(*)>=3 order by items,order_num; 包含3个或3个以上物品的订单号和订单物品的数目. 再对数目排序后对订单号排序输出.
总结:select语句的顺序:
select==from==where==group by==having==order by==;
子查询: 嵌套在其他查询中的查询
1. select cust_id from orders where order_num in(select order_num from orderitems where prod_id='RGAN01'); 查找RGAN01对应的客户id,需要借助orderitems里的订单号
即: orders.order_num=orderitems.order_num.
2. select cust_name,cust_contact from customers where cust_id in(select cust_id from orders where order_num in(select order_num from orderitems where prod_id='RGAN01'));
通过上面的例子再获取customers表里的客户信息.
即: customers.cust_id=orders.cust_id
orders.order_num=orderitems.order_num;
**********************
新建2个表:depart,team
create table depart(dept varchar(10),no);
insert into depart values(('sw',101),('hw',102),('se',103),('hr',104));
create table team(no int,name varchar(20));
insert into team values(101,'软件');
insert into team values(102,'硬件');
insert into team values(103,'测试');
insert into team values(104,'人事');
emp.dept=depart.dept depart.no=team.no
*****************************
3. select cust_name,cust_state,(select count(*) from orders where orders.cust_id=customers.cust_id) as orders from customers; 查询每个客户有几个订单.
联结join
1. select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id=products.vend_id;
=select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_id; 更规范
多表联结:
上一个例子: select cust_id from orders where order_num in(select order_num from orderitems where prod_id='RGAN01');
就可以改为:
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='RGAN01';
高级联结:
上一章的例子可以改成:
使用表别名的目的,一是减少SQL语句长度,二是,在单条select语句中不止一次使用相同的表
select name from team as T, emp as E,depart as D where T.no=D.no and E.dept=D.dept and empno='1001';
前面的联结都是内部联结或等值联结的简单联结,还有其他联结: 自联结, 自然联结,外部联结
自联结:
出Jim Jones所在公司工作的所有客户.用子查询的话
select cust_name, cust_contact from customers where cust_name=(select cust_name from customers where cust_contact='Jim Jones');
自联结的话:
select c1.cust_name, c1.cust_contact from customers as c1, customers as c2 where c1.cust_name=c2.cust_name and c2.cust_contact='Jim Jones';
外联结:将一个表中的行与另一个表中的行相关联,
select customers.cust_id, orders.order_num from customers left outer join orders on customers.cust_id=orders.cust_id;
select products.prod_id, orderitems.quantity from products left outer join orderitems on products.prod_id=orderitems.prod_id;
带聚集函数的联结: 列出所有客户及每个客户所下的订单数
select customers.cust_id,count(orders.order_num) as num_order from customers inner join orders on customers.cust_id=orders.cust_id group by customers.cust_id;
组合查询:
1. select cust_name,cust_contact,cust_email from customers where cust_state in('IL','IN','MI');
2. select cust_name,cust_contact,cust_email from customers where cust_name='Fun4All';
可以把两句连起来用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';
当然也可以使用:
select cust_name,cust_contact,cust_email from customers where cust_state in('IL','IN','MI')
OR cust_name='Fun4All';
而且我们可以发现, 可以取消重复的行.
如果不想去掉重复行:
select cust_name,cust_contact,cust_email from customers where cust_state in('IL','IN','MI')
UNION all
select cust_name,cust_contact,cust_email from customers where cust_name='Fun4All';
也可以排序:
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'
order by cust_name;
插入数据:
1. insert into customers values(....);
2. insert into customers(cust_id) values('1000000007');
将custnew的内容添加到已存在的表customers中
3. insert into customers select * from custnew;
复制整个表的内容到新表:
Mysql: create table custcopy as select * from customers;
Oracle: select * into custcopy from customers;
新建表的时候可以用以下参数: ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
更新和删除数据:
1. update custcopy set cust_email='kim@thetoystore.com' where cust_id='1000000005';
2. update customers set cust_contact='Sam Roberts', cust_email='sam@toyland.com' where cust_id='1000000006';
3. delete from customers where cust_id='1000000006';
delete from customers 会删除所有数据,如果想删除表的话用
truncate table customers;
所以在使用update或delete需要遵循以下习惯:
1. 除非打算更新或者删除每一行,否则尽量都是使用不带where的update或者delete语句
2. 保证每个表都有主键, 并尽可能像where那样使用
3. 对update或者delete使用where之前,用select测试下.
4. 使用强制实施引用完整的数据库,防止执行不带where的update或delete
创建表和操作表
1. 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 varchar(1000) null
);
更新表:
1. alter table vendors add vend_phone char(20);
2. alter table vendors drop column vend_phone;
复杂的表结构更改需要手动删除过程, 以下步骤:
1. 用新的列布局创建一个新表
2. 使用insert select语句从旧表复制数据到新表
3. 检验包含所需数据的新表
4. 重命名旧表
5. 用旧表原来的名字命名新表
6. 根据需要,重新创建触发器,存储过程,索引和外键
删除表:
drop table custcopy;
重命名表:
rename table custcopy to customercopy;
使用视图:
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';
等同于以前的例子:
select cust_id,cust_name from customers,orders,orderitems where customers.cust_id=orders.cust_id and orders.order_num=orderitems.order_num and prod_id='RGAN01';
也可以用视图重新格式化检索出的数据.
create view vendorloc as
select concat(vend_name,'(',vend_country,')') as vend_title from vendors;
存储过程:
有些复杂的操作需要多条语句完成.需要执行的具体sql语句及其次序也不是固定的, 可能会根据哪些物品在库存中哪些不在而变化.
1. 通过把处理封装在容易使用的单元中, 简化复杂的操作.
2. 由于不要求反复建立一系列操作步骤,保证数据的一致性.还能防止错误.
3. 简化对变动的管理.如果表名,列名或业务逻辑有变化,只需要更改存储过程的代码,安全性.
4. 以编译过的形式存储,提高性能
5. 存在一些只能用在单个请求中的sql元素和特性,存储过程可以使用它们来编写功能更强更灵活的代码.
存储过程的执行比编写要频繁. 语句:EXECUTE, 后面接存储过程名和需要传递给它的任何参数.
简单例子:
1. create procedure cust()
select cust_name from customers;
call cust();
2.
delimiter $$
create procedure p3(n int)
begin
select * from products where prod_price>n;
end $$
delimiter ;
调用存储过程: call p3(3);
存储过程和函数的区别： 名称不同 ：存储过程：procedure 函数function 存储过程没有返回值
show create procedure test_cursor; 查看创建的存储过程代码
show procedure status; 查看系统中的存储过程
事务处理,
维护数据库的完整性.
ACID Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
事务: transaction
回退: rollback
提交:commit
保留点:savepoint
给系统添加订单的步骤:
1. 检查数据库中是否存在相应的客户, 如果不存在, 添加.
2. 提交客户信息
3. 检索客户ID
4. 添加一行到orders表
5. 如果添加过程有故障,回退
6. 检索orders表中赋予的新订单ID
7. 对于订购的每项物品, 添加新行到orderitems表
8. 如果在添加新行到orderitems时出现故障, 回退所有添加的orderitems行和orders行.
set autocommit=0 禁止自动提交
set autocommit=1 开启自动提交
例子:
1. set autocommit=0
2. delete from custcopy where cust_id="1000000007";
3. select * from custcopy; 会发现上述一条记录被删除,但是非自动提交
4. rollback;
5. select * from custcopy; 上述记录又回来了. 如果4步换成commit,就会真正删除数据.
游标:
和存储过程一起使用:
delimiter $
drop procedure if exists test_cursor$
create procedure test_cursor(in param varchar(20),out result varchar(900))
begin
declare name varchar(20);
declare state varchar(10);
declare done int;
declare cur_test cursor for select cust_name, cust_state from customers;
declare continue handler for sqlstate '02000' set done=1;
if(param) then
select concat_ws(',',cust_name, cust_state) into result from customers where cust_id=param;
else
open cur_test;
repeat
fetch cur_test into name,state;
select concat_ws(',',result, name,state) into result;
until done end repeat;
close cur_test;
end if;
end $
delimiter ;
call test_cursor('1000000006',@test);
select @test;
call test_cursor('',@test);
select @test;
SQL特性
1. 约束:主键,外键,唯一约束
----------------------
-- Define primary keys
----------------------
ALTER TABLE Customers ADD PRIMARY KEY (cust_id);
ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);
ALTER TABLE Orders ADD PRIMARY KEY (order_num);
ALTER TABLE Products ADD PRIMARY KEY (prod_id);
ALTER TABLE Vendors ADD PRIMARY KEY (vend_id);
----------------------
-- Define foreign keys
----------------------
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);
检查约束:
create table orderitems(
...,
quantity integer not null check (quantity>0),
...
);
也可以这种方式: add constraint check(gender like '[MF]');
索引:
create index prod_name_ind on products(prod_name);
应用在数据特别多的时候,数据库查询会耗费内存.可以通过建立字段索引方式降低检索时间:
create index idx_id_name on products(prod_id,prod_name);
触发器: 一种特殊的存储过程,
触发器中的代码具有以下数据的访问权:
1. insert操作中的所有新数据
2 update操作中的所有新数据和旧数据
3. delete操作中删除的数据
例子:
DROP TABLE IF EXISTS tab1;
CREATE TABLE tab1( tab1_id varchar(11)DROP TABLE IF EXISTS tab2;
CREATE TABLE tab2( tab2_id varchar(11));DROP TRIGGER IF EXISTS t_afterinsert_on_tab1;
delimiter $
CREATE TRIGGER t_afterinsert_on_tab1
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
insert into tab2(tab2_id) values(new.tab1_id);
END$
delimiter ;
SELECT * FROM tab1;
SELECT * FROM tab2;
结果是, insert tab1表的内容同时也插入到了tab2表中.
删除例子:
DROP TRIGGER IF EXISTS t_afterdelete_on_tab1;
delimiter $
CREATE TRIGGER t_afterdelete_on_tab1
AFTER DELETE ON tab1
FOR EACH ROW
BEGIN
delete from tab2 where tab2_id=old.tab1_id;
END$
delimiter ;
复制一个表: 推荐!!!
drop table if exists c; create table c like merchandise; insert into c select * from merchandise
还有一种方式: 表的主键等信息不能copy
drop table if exists b; create table b as select * from merchandise