第四章 数据库——数据查询Ⅰ

 

一．select 语句

select [top子句] <列名1 as 别名1，列名2 as 别名2....>

From <表名>

[where<查询条件表达式>]

[order  by <排序的列名>[asc或desc]

1.top 子句用来指定需要显示的行数，可直接跟数字，也可以指定比例。

select top 10 <列名> from <表名>        --显示前行

select top 10 percent<列名> from <表名>  --显示总行数的10%

2.用as为查询的列取别名，不改变原表中的列名，只在显示查询结果时显示别名。

3.如果缺省where子句，则表示查询所有记录,

4.查询所有列，可以在列名处使用“*” ,查询多列时，使用“，”分隔各列

5.如果不写asc和desc，则默认排序为按指定列名升序排列。

     asc为升序，desc为降序。

二．简单查询 

   使用astore数据库

/******

查询所有的商品类别

*********/

select * from kind

select kindID, kindName,remark from kind

/***************************

使用列别名，表别名， 使用as

****************************/

select kindID as 类别编号, kindName as 类别名称,remark as 备注from kind as k

/*************************

使用列别名，表别名 省略as

*************************/

select kindID 类别编号, kindName 类别名称,remark 备注from kind k

/******

使用=重新命名

*********/

select 类别编号=k.kindID, 类别名称=k.kindName,备注=k.remark from kind  k

/******

查询每种商品的销售额

select 中如果对列进行了计算，需要进行重新命名

*********/

select p.productName 商品名称,p.price*p.orders 销售额from product p

/******

查看每种商品的价格，按照价格进行降序排列

order by 排序的字段排序类型[asc/desc]  asc升序desc降序默认为升序

*********/

select p.productName,p.price from product p order by p.price desc

/******

查询最贵的10种商品

先按照价格进行降序排列，然后取前10个

*********/

select top 10 p.productName,p.price from product p order by p.price desc

/******

查询50% 的商品的价格，按照价格高低排序

*********/

select top 50 percent p.productName,p.price from product p order by p.price desc

三.where条件查询

1.单条件查询

2.多条件查询

/***********************************

查询正在销售的，库存少于10的商品，并按照价格高低显示

order by 应该位于where条件后面，意味着先选出符合要求的行，然后再进行排序

************************************/

select productName,price

from product

where stop=0 and instore<10

order by price desc

/***********************************

查询正在销售的，库存少于10的商品，并按照销售额排序

***********************************/

select productName,price,price*orders 销售额

from product

where stop=0 and instore<10

order by 销售额desc

或者

select productName,price,price*orders 销售额

from product

where stop=0 and instore<10

order by price*orders desc

3.模糊查询 

/**************************

使用like关键字和通配符‘%’和‘-’

查询联系人中所有的先生

“%”代表一个或多个字符。

“-”代表一个字符。

****************************/

select * from dbo.supply s where s.contact like '%先生' 

4.范围查询 

/*****************************

查询所有商品名中带有’肉‘字的商品，

并要求显示商品名称，价格，库存金额，

价格要求在30-50之间

并按照库存金额降序排列，显示前10条

*******************************/

select top 10 productName,price,price*instore 库存金额

from product

where price>=30 and price<=50 and productName like '%肉%'

order by 库存金额desc

/*******

使用between ...and...

between ... and ...是包含边界的

*******/

select top 10 productName,price,price*instore 库存金额

from product

where price between 30 and 50 and productName like '%肉%'

order by 库存金额desc

/**********

使用in

查询所有北京，上海的供应商

************/

select company,contact,city

from supply

where city='北京' or city='上海' 

或者

select company,contact,city

from supply

where city in('北京','上海')

5.null查询

/************

查询没有传真的供应商

*************/

select * from supply where fax  is not  null

6.子查询

/****************************************************************

子查询的概念：

在一个查询中嵌套另外一个查询，叫做子查询

查询所有’饮料‘类别的商品

首先使用一个查询，查询出’饮料‘类的编号,然后再用一个查询查询出所有商品

第二个查询需要用到第一个查询的结果

*****************************************************************/

select * from product p where p.kindID=(select k.kindID from kind k where k.kindName='饮料') 

/**********************

查询所有类别为’饮料‘和'点心'的商品

************************/

select * from product p where p.kindID in(select k.kindID from kind k where k.kindName in('饮料','点心'))

/**********************

分页查询

分页查询产品表，每页显示5条记录

**************************/

--查询第一页

select top 5* from product

--查询第二页

--首先查询出第一页的productID

select top 5 productID from product

--再查出productID不是前5个productID的记录

select * from product p1 where p1.productID not in(select top 5 p2.productID from product p2)

--取前5个

select top 5 * from product p1 where p1.productID not in(select top 5 p2.productID from product p2)

/***********************************

分页公式:

select top 页大小 显示的字段from 表名 where （主键not in(select top (页码-1)*页大小 主键列from 表)

***********************************/

四．函数

1.字符串函数

函数

描述

举例

charindex

用来寻找一个指定的字符串在另一个字符串中的起始位置

select charindex(’a’,’bbaccad’,1)

返回：3

len

返回传递给它的字符串长度

select len(‘SQL Server课程’)

返回：12

lower

把传递给它的字符串改成小写

select lower(‘SQL Server’)

返回：sql server

upper

把传递给它的字符串改成大写

select upper(‘sql server’)

返回：SQL SERVER

ltrim

清除字符串左边的空格

select ltrim(‘   abc  ’)

返回：abc  （后面空格保留）

rtrim

清除字符串右边的空格

select rtrim(‘  abc   ’)

返回：  abc(前面空格保留)

left

从字符串左边返回指定数目的字符

select left(‘my sql’,4)

返回：my s

right

从字符串右边返回指定数目的字符

select right（‘my sql’,4）

返回： sql

replace

替换一个字符串中的某些字符

select replace(‘ab-cd-ef’,’-’,’%’)

返回：ab%cd%ef 

stuff

在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符

select stuff(‘abcdef’,2,3,’*’)

返回：a*ef

/******

查询产品名称中带有’肉‘字的商品

******/

select * from product where productName like '%肉%'

或者

select * from product where charindex('肉',productName,1)<>0

/**************去掉字符串左边空格**************/

select * from product where productName=ltrim('   猪肉')

/***********去掉左右空格***********/

select * from product where productName=rtrim(ltrim('     猪肉   '))

/***********去掉中间，左右空格***********/

select * from product where productName=replace(rtrim(ltrim('     猪  肉  ')),' ','')

/**********************************

不区分大小写查询

************************************/

select * from product where lower(productName)=lower(ltrim(' 猪肉'))

2.日期函数

函数

描述

举例

getdate

取得当前的系统日期

select getdate()

返回：当前的日期

dateadd

将指定的数值加到指定的日期部分上

select dateadd(mm,4,’1990-1-1’)

返回：1990-5-1

datediff

两个日期指定日期部分的差值

select datediff(yy,’1990-1-1’,’1995-1-1’)

返回：5

datename

日期中指定日期部分的字符串形式

select datename(dw,’2000-1-1’)

返回：星期六

datepart

日期中指定日期部分的整数形式

select datepart(day,’2010-9-20’)

返回：20

select dateadd(mm,-3,getDate())   --当前日期三个月以前

select dateadd(yy,-3,getDate())    --当前日期三年以前

select dateadd(dd,-3,getDate())    --当前日期三天以前

select dateadd(ww,-3,getDate())   --当前日期三周以前

select datename(w,getDate())  --当前日期星期部分的字符串形式

3.数学函数

函数

描述

举例

abs

取数值表达式的绝对值

select abs(-15)

返回：15

ceiling

返回大于或等于所给数值表达式的最小整数

Select ceiling(10.5)

返回：11

floor

返回小于或等于所给数值表达式的最大整数

Select floor(10.5)

返回：10

power

取数值表达式的幂值

Select power(5,2)

返回：25

round

将数值表达式四舍五入为指定精度

Select round(10.5367,1)

返回：10.5

sign

对于正数返回+1，负数返回-1，对于0返回0

Select sian(-12)

返回：-1

sqrt

取浮点表达式的平方根

Select sqrt(9)

返回：3

4.系统函数

函数

描述

举例

convert

用来转变数据类型

Select convert (varchar(5),12345)

返回：字符串12345

Current_user

返回当前用户的名字

Select current_user()

返回：你登陆的用户名

datalength

返回指定表达式的字节数

Select datalength(‘中国A盟’)

返回：7

host_name

返回当前用户所登陆的计算机名字

Select host_name()

返回：你所登陆的计算机名字

System_user

返回当前所登陆的系统用户名称

Select system_user()

返回：当前所登陆的系统用户名

user_name

从给定的用户ID返回用户名

Select user_name(1)

返回：从任意数据库中返回”dbo”

select '人民币:'+convert(varchar(6),price) 价格from product

“+”用于字符串之间起到连接的作用。

用于数字间则做加法运算

5.案例

案例Ⅰ

某公司印了一张充值卡，卡的密码是随机生成的，现在出现这个问题：

     卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”；

      请编写SQL语句实现以上要求；

数据库表名：Card；密码字段名：PassWord；

update Card set Password=replace(Password,'o',’0’)

update Card set Password=replace(Password,'i',’1’)

或者

update Card set Password=replace(replace(Password,'i',’1’),'o',’0’)

案例Ⅱ

在数据库表中有如下数据

       13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2

     现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排序，输出要排成这样：

      13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2

 数据库表名：SellRecord；字段名：ListNumber；

select  listNumber 

from sellRecored 

order by

 left(listNumber ,charindex(listNumber,'-',1)-1),right(listNumber,len(listNumber )-charindex(listNumber,'-',1))

 

案例Ⅲ

/*********************************************

计算users表中的奇数行和偶数行分别是多少行

思路:

复制users表，并添加一个新的自动增长的字段，那么这个字段的值从开始每次增长

所以这个字段实际就是表示数据的行号

查询的时候使用子查询分别查出季奇数行和偶数行的条数

**********************************************/

use myBookShop

go

--判断临时表是否存在，如果存在则删除

if exists(select * from sysobjects where name='users_temp')

drop table users_temp

--复制表 rowID表示每一行的行号

select identity(int,1,1) rowID,loginId into users_temp

from users

select 奇数行=(select count(*) from users_temp where rowID%2=1)

,偶数行=(select count(*) from users_temp where rowID%2=0)

drop table users_temp

五．聚合函数

use  MyBookShop

go

/***************************

价格超过50的书籍总共有多少本

*****************************/

select count(id) as 数目 from books where unitprice>50

/***************************

书籍的平均价格

*****************************/

select avg(unitprice) 平均价格 from books

/***************************

最贵的书籍的价格

*****************************/

select max(unitprice) from books

/***************************

最贵的书籍的价格和书籍名称 不准确做法：如果最贵的书籍有多本，则不能查出其他的书籍

*****************************/

select top 1 Title,UnitPrice from books order by UnitPrice desc

/***************************

最贵的书籍的价格和书籍名称 准确做法

*****************************/

select  Title,UnitPrice from books where UnitPrice=(select max(UnitPrice) from books)

六．group by子句

SELECT <列名，聚合函数>  

FROM <表名>

[WHERE 查询表达式]

[GROUP BY <列名>]

[HAVING 查询表达式]

[ORDER BY <列名>[ASC|DESC]]

group by用来分组，having只能跟在group by子句后面，用来筛选满足条件的组

执行顺序：

首先：执行where条件筛选出满足条件的行

其次：执行执行分组group by

然后：对组进行筛选having

最后：排序order by

/********************************

查询的时候，如果将满足条件的记录按照某一列进行分组之后，

再使用聚合函数，那么这个聚合函数将会作用到每一组上。

也就是有多少个组，聚合函数就会返回多少个值。

**********************************/

/**********************************

查询出每一类图书的平均价格

***********************************/

select CategoryId 类别编号,avg(UnitPrice) 平均价格

from Books 

group by CategoryId

/**********************************

查询出每一类图书的平均价格，显示图书类别

相关子查询：子查询要用到父查询的数据

***********************************/

select 类别=(select Name from Categories c where c.Id=b.CategoryId) ,avg(UnitPrice) 平均价格

from Books b 

group by CategoryId

/**********************************

查询出平均价格大于50的类别和平均价格

思路：首先按照价格来进行分组，分完组之后，求出每一组的平均价格，然后再将平均价格小于50的组丢弃掉

使用having子句可以对组进行过滤。

注意having只能与group by一起使用，因为having是对组进行过滤的。

***********************************/

select 类别=(select Name from Categories c where c.Id=b.CategoryId) ,avg(UnitPrice) 平均价格

from Books b 

group by CategoryId 

having avg(UnitPrice)>50

/**********************************

查询出平均价格大于50的类别和平均价格，每一个类别中的图书要求是2005年以后出版的，按价格升序排列

***********************************/

select 类别=(select Name from Categories c where c.Id=b.CategoryId) ,avg(UnitPrice) 平均价格

from Books b 

where datepart(yy,PublishDate)>2005 

group by CategoryId 

having avg(UnitPrice)>50

order by avg(UnitPrice)