sql语句进阶教程

转载自:http://blog.csdn.net/u011001084/article/details/51318434

最近从图书馆借了本介绍sql的书,打算复习一下基本语法,记录一下笔记,整理一下思路,以备日后复习之用。

ps:本文适用sql server2008语法。

一、关系型数据库和sql

实际上准确的讲,sql是一门语言,而不是一个数据库。

什么是sql呢?简而言之,sql就是维护和使用关系型数据库中的的数据的一种标准的计算机语言。

1.1 sql语言主要有3个主要的组成部分。

  1. dml(data manipulation language)数据操纵语言。这个模块可以让我们检索、修改、增加、删除数据库中的数据。
  2. ddl(data definition language)数据定义语言。是的我们能够创建和修改数据库本身。如:ddl提供alter语句,他让我们可以修改数据库中表的设计。
  3. dcl(data control language)数据控制语言,用于维护数据库的安全。

在sql术语中,记录(record)和字段(field)实际上就称为行(row)和列(column)。

1.2 主键和外键

主键之所以有必要:

  • 首先使你唯一标识表中单独的一行。主键确保了唯一性。
  • 可以很容易的将一个表和另一个表关联。
  • 主键一般就会自动默认创建索引,提高了查询速度。

外键就是说a表中的某个字段,同时是b中的主键,那么这个字段就是a表中的外键。希望a表中的这个外键的值必须是b中已经存在的值。

1.3 数据类型

一般来讲,有3中重要的数据类型:

  1. 数字(numeric)
  2. 字符(character)
  3. 以及日期/时间(date/time)

bit是数字型,它只允许两个值,0和1。

字符类型区别^1:

类型 长度 说明
char 固定长度
nchar 固定长度 处理unicode数据类型(所有的字符使用两个字节表示)
varchar 可变长度 效率没char高 灵活
nvarchar 可变长度 处理unicode数据类型(所有的字符使用两个字节表示)
  • 1字节=8位
  • bit就是位,也叫比特位,是计算机表示数据最小的单位。
  • byte就是字节,1byte=8bit,1byte就是1b;
  • 一个字符=2字节;

1.3 空值

空值不等于空格或空白。使用null表示空值。

二、简单增删改查

2.1 查(列名有空格的情况)

1
2
select [ last name]
from customers

用方括号将有空格的列名括起来。
ps: mysql中用重音符`(~)按键。oracle用双引号。

查询顺序,sql执行顺序^2:

1
2
3
4
5
6
select -1>选择列,-2>distinct,-3>top
1>…from 表
2>…where 条件
3>…group by 列
4>…having 筛选条件
6>…order by 列

2.2 增

1
2
3
4
5
6
insert into tablename
(columnlist)
values
(rowvalues1)
(rowvalues2)
(repeat any number of times)

2.3 改

1
2
3
update  table
set column1=expression1,column2=expression2(repeat any number of times)
where condition

2.4 删

1
2
3
delete
from table
where condition

删除前可以验证一下:

1
2
3
4
select 
count(*)
from table
where condition

 

如果想要删除所有的行,可以:

1
delete from table

 

或者

1
truncate table table

 

truncate table优势在于速度更快,但是不提供记录事务的结果。
另外一个不同点是,truncate table重新设置了用于自增型的列的当前值,delete不会。

三、别名

关键字:as

3.1 计算字段

使用计算字段可以做如下的事情:

  • 选择特定的单词或者数值
  • 对单个或者多个列进行计算
  • 把列和直接量组合在一起。

3.2 直接量

这个直接量和表中的数据没有任何关系,就是为了说明所用,下面这种类型的表达式就叫做直接量(literal value)。

1
2
select '直接量' as `类型`,firstname,lastname 
from `customers` ;

如图,结果中直接量就在一列中了。

3.3 算数运算

例子1:

1
2
select  num*price as total
from orders

 

例子2:

1
2
select  firstname+' '+lastname as 'fullname'
from users

 

在mysql中连接要是用concat函数:

1
2
3
select orderid,firstname,lastname,
concat(firstname,' ',lastname) as 'fullname'
from orders

 

3.4 别名

1)列的别名

1
2
select firstname as fn
from customers

 

2) 表的别名

1
2
select firstname 
from customers as cu

 

说明:

  1. 列的别名是为了显示用的,别名会作为查询结果的表头,不能在where中使用列的别名,会出错!!!
  2. 表的别名确实是为了方便操作用的,可以在where中使用列的别名进行!

四、使用函数

函数要有一组圆括号跟在关键字后边,圆括号告诉我们,这是一个函数!

4.1 字符函数

left&right

left(charactervalue,numberofcharacters)
含义:选择charactervalue字段的左边numberofcharacters几个字符。
ps:right是右边几个字符。

ltrim&rtrim

ltrim(charactervalue)
可以删除左边开始的空格。rtrim作用类似。

substring

substring(charactervalue,startpositon,numberofcharacters)
含义:选择从开始位置(包括),n个长度的字符。

1
2
select 
substring('thewhitegoat',4,5) as 'the answer'

 

返回:white

4.2 日期/时间函数

getdate

1
select getdate()

返回当前日期和时间。
ps:在mysql中,等价函数是now,在oracle中是current_date

datepart

能够分析具体的日期,并且返回诸如该日期是该月中的第几天,或者该年份中的第几周等信息。

1
datepart(datepart,datevalue)

 

datepart可以是许多不同的值,如下都是有效值:

  • year
  • quarter
  • month
  • dayofyear
  • day
  • week
  • weekday
  • hour
  • minute
  • second

datediff

可以让我们得到任意两个日期之间相差的天数(或周数、月数等)。

1
datediff(datepart1,startdate1,startdate2)

 

datediff function expression resulting value
datediff(day,’7/8/2009’,’8/14/2009’) 37
datediff(week,’7/8/2009’,’8/14/2009’) 5
datediff(month,’7/8/2009’,’8/14/2009’) 1
datediff(year,’7/8/2009’,’8/14/2009’) 0

ps:mysql中,datediff函数只允许我们计算两个日期之间的天数,如果想要得到一个正数,结束的日期通常要作为第一个参数:

1
datediff(enddate,startdate)

 

oracle中没有等价函数

4.3 数值函数

round

允许我们四舍五入。

1
round(numericvalue,decimalpalaces)

 

rand

用来产生随机数

1
rand([seed])

 

没有参数时,它会返回0-1之间的一个随机数。

1
select rand() as 'random value'

 

可选参数seed有的情况下,每次将返回相同的值。这让我想起了python中的random包。看来很多时候,一些东西是共通的啊。

pi

pi()函数
如果想要对它保留两位小数,可以通过复合函数进行:

1
select round(pi(),2)

 

将会返回:3.14

4.4 转换函数

cast函数

允许我们把数据从一种类型转换成另一种类型。

1
cast(expression as datetype)

 

例子:

1
2
3
select 
'2009-04-11' as 'original date',
cast('2009--04-11' as datetime) as 'converted date'

 

isnull函数,很有用

可以把null值转换成一个有意义的值。

1
2
3
select description,
isnull(color,'unknown') as 'color'
from products

 

五、排序函数

5.1 添加排序

1
2
3
select columnlist
from tablelist
order by columnlist

默认是升序,asc,因此,上面等价于:

1
2
3
select columnlist
from tablelist
order by columnlist asc

 

5.2 降序

使用desc关键字:

1
2
3
select columnlist
from tablelist
order by columnlist desc

 

5.3 根据多列

1
2
3
4
5
select 
firstname,
lastname
from customers
order by lastname, firstname

注意:列的顺序很重要,首先按照lastname排序,然后按照firstname排序。

5.4 根据计算字段

1
2
3
select lastname+','+firstname as 'name'
from customers
order by name

因此,从这儿可以知道,列别名不可以用在where中,但可以用在order by中。
例子

1
2
3
select firstname,lastname
from customers
order by lastname+firstname as 'name'

 

5.5 排序补充内容

当数据升序时,出现顺序是如下:

null->数字->字符
注意:此时,该列中的数字其实是按照字符来算的,因此,升序时,23也是排在5之前的。

六、基于列的逻辑-case

6.1 if-then-else逻辑

包含列和case表达式的select语句,大概如下:

1
2
3
4
5
select 
column1,
column2,
caseexpression
from table

 

6.2 case-简单格式

1
2
3
4
5
6
7
select 
case columnorexpression
when value1 then result1
when value2 then result2
(repeat when-then any number of times)
[else  defaultresult]
end

case表达式对于把不好理解的值转换成有意义的描述是很有用的。

1
2
3
4
5
6
7
8
select 
case categorycode
when 'f' then 'fruit'
when 'v' then 'vegetable'
else 'other'
end as 'category',
productdescription as 'description'
from products

 

6.3 case-查询格式

1
2
3
4
5
6
7
select 
case 
when condition1 then result1
when condition2 then result2
(repeat when-then any number of times)
[else  defaultresult]
end

这种格式允许在关键字when后边放置较为复杂的条件表达式。

相关问题:

  • stackoverflow-sql server: case when or then else end => the or is not supported

七、基于行的逻辑

7.1 应用查询条件

终于派到where出场了,注意写法顺序,再写一遍:

1
2
3
4
5
6
select -1>选择列,-2>distinct,-3>top
1>…from 表
2>…where 条件
3>…group by 列
4>…having 筛选条件
6>…order by 列

 

7.2 限制行-top

1
2
3
4
select 
top number 
columnlist
from table

7.3 top和order by结合

关键字top的另一个用途是,把它和order by子句结合起来,基于特定分类,得到带有最大值的一定数量的行。

假设你想看到shakespeare所著的销量最多的书。

1
2
3
4
5
6
7
select 
top1
title as 'book title',
currentmonthsales as 'quantuty sold'
from books
where author='shakespeare'
order by currentmonthsales desc

 

ps: 学会利用google搜索,例如,我想要知道oracle中类似top作用的关键字是什么,可以:

八、布尔逻辑

关键字:and/or/not/between/in/is/null

8.1 or

or子句意味着,如果确定任意条件为真,那么就该选中该行。

1
2
3
4
select userid,name,phone
from users
where age<18
or age>60

 

8.2 使用圆括号

1
2
3
4
5
6
7
select customername,
sate,
quantitypurchased
from orders
where state ='il'
or state='ca'
and quantitypurchased>8

本来想要的结果是对来自il或者ca的客户,同时,只看数量大于8的订单。但是上面执行的结果不是这样的,因为,sql总是会先处理and操作符!!!然后才会处理or操作符。所以,上述语句中,先看到and并执行如下的条件

1
2
state= 'ca'
and quantitypurchased>8

 

因此,要用括号来规定顺序:

1
2
3
4
5
6
7
select customername,
sate,
quantitypurchased
from orders
where (state ='il'
or state='ca')
and quantitypurchased>8

 

8.3 not操作符

not操作符表示对后边的内容否定或者取反。

1
2
3
select customername,state
from orders
where not (state='il' or sate='ny')

 

这个其实可以用and改写的!!!
not操作符在逻辑上不是必须的。

8.4 between操作符

1
2
3
4
5
select customername,
sate,
quantitypurchased
from orders
where quantitypurchased between 8 and 10

8.5 in操作符

假设你想看到il或者ny的行:

1
2
3
4
select *
from orders
where state='il'
or state='ca'

 

可以改写成:

1
2
3
select *
from orders
where state in ('il','ca')

 

8.9 布尔逻辑-is null

为了将某字段null值的行或0的行包括进来:

1
2
3
4
select *
from products
where weight=0
or weight is null

 

或者

1
2
3
select *
from products
where isnull(weight,0)=0

 

九、模糊匹配

9.1 like和%搭配

%通配符可以表示任意的字符,它可以表示0个,1个,任意多个字符。

9.2 通配符

除了%以外,还有下划线(_)、方括号起来的characterlist,以及用方括号括起来的脱字符号(^)加上characterlist。

  • 下划线表示一个字符
  • [characterlist]表示括号中字符的任意一个
  • [^characterlist]表示不能是括号中字符的任意一个
    例子:
    1
    2
    3
    4
    5
    select 
    firstname,
    lastname
    from actors
    where firstname like '[cm]ary'

检索以c或者m开头并以ary结尾的所有行。

9.3 按照读音匹配

soundex和difference

十、汇总数据

10.1消除重复

使用distinct

1
2
select distince name,age
from users

 

如果age不同,即使name相同,那么这一行就不会被删除重复。

10.2 聚合函数

count\sum\avg\min\max,他们提供了对分组数据进行计数、求和、取平均值、取最小值和最大值等方法。

1
2
3
4
5
select 
avg(grade) as 'average quiz score'
min(grade) as 'minimum quiz score'
from grades
where gradetype='quiz'

 

count函数可以有3中不同方式使用它。

1.count函数可以用来返回所有选中行的数目,而不管任何特定列的值。
例如:下面语句返回gradetype为’homework’的所有行的数目:

1
2
3
4
select 
count(*) as 'count of homework rows'
from grades
where gradetype='homework'

 

这种方式,会计数所有行的个数,即使其中有*null

2.第二种方式指定具体的列

1
2
3
4
select 
count(grades) as 'count of homework rows'
from grades
where gradetype='homework'

 

第一种方式返回3,这一种方式返回2,为什么???因为,这种方式要满足grades这一列有值,null值的行不会计数。

3.使用关键字distinct。

1
2
3
select 
count(distinct feetype) as 'number of fee types'
from fees

 

这条语句计数了feetype列唯一值的个数。

10.3 分组数据-group by

1
2
3
4
5
6
select
gradetype as 'grade type',
avg(grade)as 'average grade'
from grades
group by gradetype
order by gradetype

感觉像excel中的分类汇总功能。
如果想把grade为null值的当做0,那么可以用:

1
2
3
4
5
6
select
gradetype as 'grade type',
avg(isnull(grade,0))as 'average grade'
from grades
group by gradetype
order by gradetype

 

  • group by子句中的列的顺序是没有意义的;
  • order by子句中的列的顺序是有意义的。

10.4 基于聚合查询条件-having

当针对带group by的一条select语句应用任何查询条件时,人们必须要问查询条件是应用于单独的行还是整个组。

实际上,where子句是单独的执行查询条件。sql提供了一个名为having的关键字,它允许对组级别使用查询条件。
例子:
查看选修了类型为选修“a”,平均成绩在70分以上的学生姓名,平均成绩。

1
2
3
4
5
6
7
8
select 
name,
avg(isnull(grades,0)) as 'average grades'
from grades
where gradetype='a'
group by name
having avg(isnull(grades,0))>70
order by name

 

修要修类型为a,那么,这是这对行的查询,因此这里要用where。
但是,还要筛选平均成绩,那么,这是一个平均值,建立在聚合函数上的,并不是单独的行,这就需要用到关键字having。需要先将student分组,然后把查询结果应用到基于全组的一个聚合统计上。

where只保证我们选择了gradetype是a的行,having保证平均成绩至少70分以上。

注意:如果想要在结果中添加gradetype的值,如果直接在select后边添加这个列,将会出错。这是因为,所有列都必须要么出现在group by中,要么包含在一个聚合函数中。

1
2
3
4
5
6
7
8
9
select 
name,
gradetype,
avg(isnull(grades,0)) as 'average grades'
from grades
where gradetype='a'
group by name,gradetype
having avg(isnull(grades,0))>70
order by name

 

十一、组合表

11.1 内连接来组合表-inner join

通过书中的描述,我感觉内连接更像是用来将主键表、外键表连接起来的工具。
例如:
a表:

userid name age
1 michael 26
2 hhh 25
3 xiang 20

b表:

orderid userid num price
1 1 2 3
2 2 6 6
3 1 5 5

如上表格,那么要连接这两个表格,查询订单1的客户姓名,年龄,订单号:
方式一:

1
2
3
4
select name,age,orderid
from a,b
where a.userid=b.userid
and orderid=1

 

方式二,使用现在的内连接实现:

1
2
3
4
5
select name,age,orderid
from a
inner join b
on a.userid=b.userid
and orderid=1

 

on关键字指定两个表如何准确的连接。

内连接中表的顺序:from 子句指定了a表,inner join 子句指定b表,我们调换a,b顺序,所得到的结果相同的!只是显示列的顺序可能会不同而已。

不建议使用方式一的格式。关键字inner join on的优点在于显示地表示了连接的逻辑,那是它们唯一的用途。wehere的含义不够明显。因为它是条件的意思啊,不是连接的!

11.2 外连接

外连接分为左连接(left outer join)、右连接(right outer join)、全连接(full outer join)。

outer是可以省略的。

左连接(left join)

1
2
3
4
5
select name,age,orderid
from a
left join b
on a.userid=b.userid
and orderid=1

外连接的强大之处在于,主表中的数据必然都会保留,从表中列没有值的情况,用null补充。

left join 左边的表为主表,右边的表为从表。

11.3 自连接

自连接必然用到表的别名。

1
2
3
4
select a.name,b.name as managername
from worker as a
left join worker as b
on a.managerid=b.id

 

11.4 创建视图

1
2
3
create view viewname as
selectstatement
[with check option]

视图中不能包含order by子句。

[with check option]表示对视图进行update,insert,delete操作时任然保证了视图定义时的条件表达式。

删除视图:

1
drop view viewname

 

修改视图:

1
2
alter view viewname as
selectstatement

 

视图的优点

  1. 简化用户的操作
  2. 使用户以多角度看待同一数据
  3. 对重构数据库提供了一定程度的逻辑独立性
  4. 对机密数据提供安全保护

十二、补充

12.1 子查询

可以用3种主要的方式来指定子查询,总结如下:

  • 当子查询是tablelist的一部分时,它指定了一个数据源。
  • 当子查询是condition的一部分时,它成为查询条件的一部分。
  • 当子查询是columnlist的一部分时,它创建了一个单个的计算的列。

12.2 索引

索引是一种物理结构,可以为数据库表中任意的列添加索引。

索引的目的是,当sql语句中包含该列的是偶,可以加速数据的检索。

索引的缺点是,在数据库中,索引需要更多的存储硬盘。另一个负面因素是,索引通常会降低相关的列数据更新速度。这是因为,任何时候插入或者修改一行记录时,索引都必须重新计算该列中的值的正确的排列顺序。

可以对任意的列进行索引,但是只能指定一个列作为主键。指定一个列作为主键意味着两件事情:首先这个列成为了索引,其次保证这列包含唯一的值。

1
2
create index index2
on mytable (columnfour)

 

删除一个索引:

1
2
drop indx index2
on mytable

 

 

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐