educoder 数据库系统概论 sql server 基础数据查询

1.基本select查询

USE Mall
GO

SET NOCOUNT ON

---------- retrieving multiple column ----------
-- ********** Begin ********** --
select prod_name,prod_price
from Products


-- ********** End ********** --

GO

---------- retrieving all column ----------
-- ********** Begin ********** --
select *
from Products

-- ********** End ********** --

GO

2.带限制条件的查询和表达式查询

USE Mall
Go

SET NOCOUNT ON

---------- retrieving with limited ----------
-- ********** Begin ********** --
select top 2 prod_name
from Products

-- ********** End ********** --
GO

---------- retrieving with expression ----------
-- ********** Begin ********** --
select prod_price,prod_price*0.8 as discount_price
from Products


-- ********** End ********** --
GO

3. 使用where语句进行检索

USE Mall
Go

SET NOCOUNT ON

---------- retrieving with range ----------
-- ********** Begin ********** --
select prod_name,prod_price
from Products
where prod_price between 3 and 5


-- ********** End ********** --

GO

---------- retrieving with nomatches ----------
-- ********** Begin ********** --
select prod_name,prod_price
from Products
where prod_name <> 'lion toy'


-- ********** End ********** --

GO

4.通配符[]的使用

USE Mall
Go

SET NOCOUNT ON

---------- retrieving with wildcard [] ----------
-- ********** Begin ********** --
select *
from Products
where  prod_name like '[^b]%'



-- ********** End ********** --

GO

 5.通配符_的使用

USE Mall
Go

SET NOCOUNT ON

---------- retrieving with wildcard _ ----------
-- ********** Begin ********** --
select *
from Products
where prod_name like '1_ inch lion toy'

-- ********** End ********** --

GO

 6.通配符%的使用

USE Mall
Go

SET NOCOUNT ON

---------- retrieving with wildcard % ----------
-- ********** Begin ********** --
select *
from Products
where prod_name like '%toy%'


-- ********** End ********** --

GO