爱华网--内连接
use webshop
select goods.g_ID,goods.g_Name,types.t_ID,types.t_Name
from goods
join types
on goods.t_ID=types.t_ID
--条件查询
select goods.g_ID,goods.g_Name,types.t_ID,types.t_Name
from goods,types
where goods.t_ID=types.t_ID
--三表连接
select orders.o_ID,o_date,g_Name,d_price,d_number
from orders
join orderdetails
on orders.o_ID=orderdetails.o_ID
join goods
on orderdetails.g_ID=goods.g_ID
--自身连接
select g2.g_ID 商品号,g2.g_Name 商品名称,g2.g_price 商品价格
from goods g1
join goods g2
on g1.g_Name='三星SGH-C178' andg1.g_price<=g2.g_price
order by g2.g_price
--左外连接
selecttypes.t_ID,types.t_name,goods.g_Name,goods.g_number,goods.g_price
from types
left outer join goods
on types.t_ID=goods.t_ID
--右外连接
selecttypes.t_ID,types.t_name,goods.g_Name,goods.g_number,goods.g_price
from goods
right outer join types
on types.t_ID=goods.t_ID
--完整外连接
create database test
use test
create table student
(
s_ID int,
s_Name nvarchar(5),
s_ClassID int
)
insert into student values(1,'张三',1)
insert into student values(2,'李四',2)
insert into student values(3,'转校生赵六',null)
select * from student
create table class
(
c_ID int,
c_Name nvarchar(10)
)
insert into class values(1,'一班')
insert into class values(2,'二班')
insert into class values(3,'三班')
select * from class
select student.s_Id,student.s_Name,class.c_ID,class.c_Name
from student
full outer join class
on student.s_ClassID=class.c_Id
--交叉连接
select * from class
cross join student
select class.* ,student.*
from class,student
--子查询举例
select g_ID,g_Name,t_ID
from goods
where t_ID
in
(
select t_ID from goods where g_Name='摩托罗拉W380'
)
--创建视图,视图可以隐藏数据库真实的表结构
CREATE VIEW myview
AS
SELECTOrders.o_ID, OrderDetails.d_Price, Goods.g_Name, Goods.g_Price,
OrderDetails.d_Number
FROMGoods
INNER JOIN
OrderDetails ON Goods.g_ID = OrderDetails.g_ID
INNER JOIN
Orders ON OrderDetails.o_ID = Orders.o_ID
--查询视图
select * from myview
--创建索引,相当于书的目录，可以增加查询速度
create nonclustered index suoyin1
on student(s_ID)
