多表查询、视图、索引 查询表索引

--内连接
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)

  

爱华网本文地址 » http://www.413yy.cn/a/25101010/29985.html

更多阅读

查询、下载QQ空间背景音乐,包括上传歌曲 qq空间背景音乐上传

查询、下载QQ空间背景音乐,包括上传歌曲——简介我们看到别人空间喜欢的背景音乐,很想下载和收藏,但是苦于找不到下载地址而无奈放弃,这个网站很方便的帮助了我们找到下载地址,不仅如此,还可以试听和下载。查询、下载QQ空间背景音乐,包括

声明:《多表查询、视图、索引 查询表索引》为网友永不止步分享!如侵犯到您的合法权益请联系我们删除