SQL常用语法总结

来源：互联网发布：nginx 404 重定向编辑：程序博客网时间：2024/06/06 00:28

表的相关数据：

字段

用来模拟一个事物的某一个静态特征

记录

字段的组合表示的是一个具体事物

表

记录的组合表示的是同一类型事物的集合

表和字段，记录的关系

字段是实物的属性

记录是事物本身

表是事物的集合

列

字段的另一种称谓

属性

字段的另一种称谓

元组

记录的另一种称谓

约束：

定义：对一个表中的属性操作的限制

主键约束：不允许重复元素

外键约束：通过外键约束从语法上保证了本事务所关联的其他事物一定存在

事物和事物之间的关系式通过外键来体现的

Check约束：保证事物的属性的取值在合法的范围之内

Create table student

(

Stu_id int primary key,

stu_sal int check(stu_sal<=4000 andstu_sal>=1000)

)

Default约束: 保证事物的属性一定会有一个值

Create table student

(

Stu_id int primary key,

stu_sal int check(stu_sal<=4000 andstu_sal>=1000),

stu_sex nchar(1) default(‘男’)---SQL server数据库中单引号（‘’）表示字符串双引号（“”）模拟一个对象的名字

）

唯一约束：unique 保证了失误属性的取值不允重复，但允许为空但不允许多列为空只能一列为空

Create table student

(

Stu_idint primary key,

stu_sal int check(stu_sal<=4000 andstu_sal>=1000),

stu_sex nchar(1) default(‘男’),

stu_name nvarchar(50) unique---(在此表中stu_name 不能重复唯一键可以为空)

）

Not null

要求用户必须为该属性附一个值，否则语法错误

表和约束的区别：

数据库是通过表来解决事物的存储问题的

数据库是通过约束来解决事物取值的有效性和合法性的问题

建表的过程就是指定事物属性以及事物属性各种有数的过程

什么是关系：

数据库中的关系：

分类：一对一，一对多，多对多

定义：表和表之间的关系

实现方式：通过设置不同形式的外键来表示表和表的不同关系

一对一：

（A表与B表）

既可以把表A的主键充当表B的外键

也可以把表B的主键充当表A的外键

一对多：

外键添在多的一方

多对多：

Create table classroom

(

classID int primary key,

num int not null,

className nvarchar(100)

)

Create table teacher

(

teacherID int primary key,

teacherName nvarchar(100)

)

Create table class_teacher-mapping

(

classID int constrain fk_classID foreign keyreferences classroom(classID),

teacherID int foreign key references teacher(teacherID),

kecheng nvarchar(50),

constrain pk_classID_teacherID primary key (classID ,teacherID)

)

多对多关系必须通过单独的一张表来表示

什么是主键

能唯一标示一个事物的字段或字段组合

含有主键的表叫主键表

主键的特征：

主键通常是整数，不建议使用字符做主键（如果主键用于集群式服务才可以考虑用字符）

主键的值通常不允许修改，除非本记录删除

主键不要定义成ID，而要定义成表名ID或表名_ID

任何一张表，强烈建议不要使用有业务含义的字段从当主键

我们通常都是在表中单独添加一个整形的编号从当主键字段

主键是否连续增长不是十分的重要

什么是外键

来至另一个表或多张表的主键或唯一键

注意：

外键通常来至另外表的主键而不是唯一键，因为唯一键可能为null

外键不一定来至另外表，也可能来自本表的主键

含有外键的表叫外键表，外键来自的那一张表叫做主键表

问题：主键表和外键表先删哪个？

先删外键表

如果先删主键表，外键表的数据引用失败，系统报错

Identity【主键自动增长，用户不需要为identity修饰的主键赋值】

查询（重点）：注意执行顺序

1.计算列

Select * from emp

Select empno ,ename from emp

Select ename , sal*12 as “年薪”, sal as”月薪” from emp

Select 5 from emp --输出的行数是emp表的行数每行只有一个字段值为5

2.Distinct（不允许重复）

Select distinct deptno from emp--distinct 会过滤掉重复记录也可过虑掉null

Select distinct comm ,deptno from emp--distint comm,deptno整体过滤

Select deptno,distinctcomm from emp--error逻辑上有错误

3.Between

Select * from emp where sal between 1500 and 3000 --等价于：select * from emp where sal<=3000 or sal>=1500

Select * from emp where sal not between 1500 and 3000 --等价于：select * from emp where sal>=3000 or sal,+1500

4.In(属于若干个孤立的值)

Select 8 * from emp where sal in (1500,3000)--查询结果只有1500 和 3000

Select 8 * from emp where sal not in (1500,3000)--查询结果除1500 和 3000以外的记录

数据库中不等于有两种表示：!=,<> 推荐使用第二种

5.Top

Select top 2* from emp

Select top 15 percent * from emp--也可以用百分比记录数取整（进一法）

可以用来分页

Selecttop 2 *from emp

wheresal between 1500 and 3000

orderby sal desc

(先执行条件，再执行select)

6.null【空没有值】

Select * from emp wherecomm not is null

--------null不能参与<> != 等运算不能参与数学运算

--------null只能参与 is not is

--------注意0和null的区别

---------任何数据都允许为null

--------- 任何数字与null运算结果都是null

6.order by（排序）

select * from emp order by sal------默认升序

select * from emp order by deptno， sal-------先按deptno排序，如果deptno相同再按sal排序

select* from emp order by deptno desc， sal-------desc只对deptno有效

8模糊查询

Select * from emp whereename like ‘%A%’----查找ename中包含A的记录

格式：

Select 字段的集合 from 表名 where 某个字段的名字 llike 匹配的条件

匹配的条件通常含有通配符：

%：任意零个或多个字符

_[下划线]：任意单个字符

[a-f]:a到f中的任意单个字符

[a,f]:a或f

[^a-c]：不是a，也不是b，也不是c的任意单个字符

通配符要用单引号括起来

通配符作为为普通字符

Select*from student where name like ‘%\%%’escape’\’—查找名字中有%的记录

escape 关键字把’\’作为转义的标志，也就是说\%中的%将作为普通字符，其中转义字符的标志可以为任意字符：

Select* from student where name like ‘%/%%’ escape’/’—作用同上

9聚合函数

函数的分类：

单行函数：

每一行返回一个值

多行函数：

聚合函数是多行函数

多行返回一个值

例子：select lower(ename) from emp,----返回所有记录—lower表示输出的字符为小写，upper表示输出字段为大写

聚合函数分类：

Max(),min(),avg()—平均数,count()—求个数

Count()函数：

Count(*)返回表中所有的记录的个数

Count(字段名)返回字段值非空的记录，重复记录也会被当做有效记录

Count(distinct 字段名)返回字段不重复并且非空记录的个数

Select count(*)from emp,--返回emp表所有记录的个数

Select count(distinct depton) from emp,-deptno重复的记录会被忽略

Select count(comm)from emp—comm为null的记录不会被当做有效记录，也就是不会被统计

注意：单行函数和多行函数不能同时使用!

10 group by

Select deptno,avg(sal) as “部门的平均工资” from emp groupby deptno

总结：使用group by之后select中只能出现分组后的整体信息，不能出现组内的详细信息

格式：group by 字段集合

功能：把表中的记录按照字段分成不同的组

例子：

查询不同部门的平均工资

Select deptno,avg(sal) as “部门平均工资” from emp groupby deptno

注意：理解：group by a,b,c的用法

先按a分组，如果a相同，再按b分组，如果b相同，再按c分组

最终统计的是最小分组的信息

11 having【对分组之后的信息进行过滤】

///having子句是用来对分组之后的数据进行过滤

因此使用having时通常都会先使用group by

///如果没有使用group by 但使用了having把所有的记录当成一组来进行过滤…极少使用

///having 子句出现的字段必须是分组的整体信息

Having子句不允许出现组内的详细信息

///尽管select字段中可以出现别名

但是having子句中不能出现字段的别名，只能使用字段的原始名字

Select deptno avg(sal)as”平均工资” from emp group by deptnohaving avg(sal)>2000

Having 和 where的异同

相同：都是对数据的过滤，只保留有效的数据

Having和where都不允许出现字段的别名

不同：where是对原始的数据记录过滤，having是对分组之后的记录过虑 where必须写在having的前面，顺序不可颠倒

注意：select语句的参数的顺序是不允许变化的

Select语句格式：

Select ……….

From …………

Where ………….

Group by …………

Having ………….

Order by…………..

12连接查询

定义：将两个或者两个以上的表以一定的连接条件连接起来从中检索出满足条件的数据

分类：

内连接【重难点】

例子：select “E”.ename as’员工名’ “D”.dnameas’部门名称’

Fromemp “E”

Joindept “D”

On “E”.deptno=”D”.deptno

1 select ……from A ,B 的用法

Select * fromemp ,dept ----查询结果的列数是两张表列数的和，行数是两张表行数的乘积（笛卡尔积）

(emp 8 列14 行，dept 3列 5行)

2 select ……from A ,B where…… 的用法（对笛卡尔积过滤）

Select * fromemp ,dept

Whereempno=4369---输出5行（empno是emp中的一列）

3 select ……from A jion B on……的用法

select “E”.enameas’员工名’ “D”.dname as’部门名称’

From emp “E”----------般会取个别名

Join dept “D”------join是连接关键字

On “E”.deptno=”D”.deptno---链接条件（注意字体颜色的顺序）

-----------查询结果：14行 11列

Select * from emp

Join dept

Onemp.deptno=dept.deptno

-----------查询结果：14行 11列

注意：如果要查询的多张表内有相同的字段，可以为表制定别名，用别名来确定字段来自哪张表

----------------格式不变

4 select ……from A ,B where……（SQL92标准）与select ……from A jion Bon……（SQL99标准）的比较

两语句功能相同推荐使用SQl99标准

SQL99标准更容易理解

SQL99标准中，on 和where分工不同

On指定链接条件

Where对连接后的临时表的数据进行过滤

5 select ,from,where, join, on ,group by, order, top ,having的混合使用

查询的顺序：

Select top……

From A ……

Join B ……

On ……

Join C……

On ……

Where ……

Group by ……

Having ……

Order by ……

例子：

Select *from emp “E”

Joindept “D”

On “E”.deptno=”D”.deptno

Where“E”.sal>2000

三表查询
select * from emp “E”

Joindept “D”

On“E”.deptno=”D”.deptno

Joinsalgrade “S”

On“E”.sal>=”S”.losal and “E”.sal<=”S”.hisal

Where “E”.sal>=2000

外连接

定义：不但返回满足连接条件的所有记录，而且会返回部分不满足条件

的记录

分类：左外连接:返回一个事物的相关信息，如果该事物没有相关信息，

则输出null

不但返回满足连接条件的所有记录，而且会返回左表不满足

条件的记录

Select *from dept “D”

Left jion emp “E”

On E.deptno=D.deptno

右外连接：与左外链接类似

不但返回满足条件的所有记录，而且会返回右表不满足条件

的记录

完全连接：(full 表示查询为完全连接查询)

两个表中匹配的所有行记录

左表中那些在右表中找不到匹配的行的记录，这些记录的右边全

为null

右表中那些在左表中找不到匹配的行的记录，这些记录的左边全为

null

Select * fromproductStocts

Fulljoin orderform

On productStocks.pid=orderform.pid

交叉连接

（略）

自连接

定义：一张表自己和自己连接起来查询数据

例子：不用聚合函数，求薪水最高的员工信息

(带有聚合函数)

Select *from emp where sal=(select max(sal) from emp)

(不用聚合函数)

Select *from emp

Where empon not in(

Select distinct “E”.empno

From emp ”E1”

Join emp“E2”

On “E1”.sal<”E2”.sal

)

联合 :

定义：表和表之间的的数据以纵向的方式连接在一起

注意：我们以前讲的所有的连接是以横向的方式连接在一起的

例子：输出每个员工的姓名工资上司姓名

Select “E1”.ename,”E2”.sal,”E2”.ename“上司的名字”

Fromemp “E1

Join emp ”E2”

On “E1”.mgr=”E2”.empno

Union

Select ename ,sal , ‘已是最大老板’ from emp where mgr is null

注意：

若干个select 字句要联合成功必须满足两个条件

1 这若干个select 字句输出的列必须相等

2 这若干个select字句输出列的数据类型必须是兼容的

分页查询（重点）：

工资从高到低排序，输出工资式第4-6的员工信息

Select top 3 *

From emp

Where empno not in (selecttop 3 empno from emp order by sal desc)

Order by sal desc

工资从高到低排序，输出工资式第10-12的员工信息

Select top 3 *

From emp

Where empno not in (selecttop 9 empno from emp order by sal desc)

Order by sal desc

总结：

假设每一页显示n条信息，当前页数为m

表名A 主键A_id

Select top n *

From A

Where A_id not in(select top (m-1)*n A_id from emp)

视图

Create view v$_emp1

Select deptno ,avg(sal)“ave_sal”

From emp

Group by deptno

---------------------------

Select * from v$_emp1

Where avg_sal=(selectmax(avg_sal) from v$_emp1)

为什么需要视图：

简化查询避免了代码冗余

什么是视图：

代码上看视图就是一个select语句，逻辑上可以当成一个临时表

视图格式：

Create view 视图名字

Select语句---select的前面不能添加begin 后面不能添加end

注意问题：

创建视图的select语句须为所有的计算列指定别名

视图不是物理表，是虚拟表

不建议通过视图更新视图所依附的原始表的数据或结果

事物【重点】

什么是事物：

一系列操作要么全部执行成功，要么全部执行失败

为什么需要事物：

事物可以保证避免数据处于一种不合理的中间状态

利用事物可以实现多个用户对共享资源的同时访问

事物和线程的关系

事物也通过锁来解决很多问题

线程同步就是通过锁来解决的

如何创建事物：

开始事物： begin transaction

提交事务：commit transaction

回滚（撤销）事物： rollback transaction

一旦事物提交或回滚，则事物结束

事物的三种运行模式：

自动提交：

每个单独的语句都是一个事物，如果成功执行，则自动提交，如果错误，则自动回滚

显式事物：

每个事物均以begin transaction语句显式开始

以 commit 或 rollback 语句显式结束

隐性事物：

在一个事物完成时新事物隐式开启，但每个事物仍以commit 或rollback语句结束

事物错误处理：

Try…Catch语句的具体语法格式如下：

BEGIN TRY

<语句或语句块1>

END TRY

BEGIN CATCH

<语句或语句块2>

END CATCH

事物实例：

Begin transaction

Declare @errorSum int

Set @errorSum =0

Update bank setcurrentMoney=currentMoney-1000

Where customerEname=’张三’

Set@errorSum=@errorSum+@@error

Update bank setcurrentMoney=currentMoney+1000

Where customerEname=’李四’

Set@errorSum=@errorSum+@@error

If(@errorSum<>0)

Begin

Print ‘转账失败’

Rollbacktransaction

End

Else

Begin

Print’转账成功’

Commit transaction

End

存储过程

Sql server 2008中提供了三种存储过程类型

自定存储过程扩展存储过程系统存储过程

Create procedure 语句创建存储过程

语法的基本形式：

Create procedure 存储过程的名字

Parameter_namedata_type,……----参数列表

Withprocedure_option(可选)

Sql-statement

例子：

Create procedureGetEmployeeInfo

@lastnamevarchar(50),

@firstnamevarchar(50)

SelectLastNme,FirstName ,JobTitle,Department

From tb_Employee

WhereLastName=@lastname and FirstName=@firstname

带有OUTPUT类型参数的存储过程

Create procedureComputePlus

@firstpara decinal(10,2),

@secondpara decinal(10,2),

@plusresultdecinal(10,2) output

Set@plusresult=@firstpara+@seccondpara

执行存储过程：

Exec 存储过程名字‘参数1’, ‘参数2’……

带有output参数的存储过程的执行

Declare @resultdecinal(10,2)

Exec ComputePlus123,568,@result output

Print @result

删除存储过程： drop procedure 存储过程名字

索引:在数据库中，索引是表中数据和相应存储位置的列表

索引的基本类型：聚集索引，非聚集索引

聚集索引：是一种数据表的物理顺序与索引顺序相同的索引

非聚集索引：与聚集索引相反

创建索引：create index 或使用图形工具

例子：

Create unique clusteredindex ind_books_ISBN

On books(ISBN)

Create uniquenonclustered index ind_books_title

On books(title)

游标

触发器

触发器是一种特殊的存储过程 SqlServer2008 提供两种触发器：DML ，DDL

DML触发器（在数据库中发生数据操纵语言事件时将调用DML）

分为三种类型：insert ，update ，delete

语法：

Create triggertrigger_name

On table_name orview_name

With encryption

{ for|after|instead of}{delete[,][insert][,][update]}

As sql_statement

触发器的工作原理：向表中插入数据时，insert触发器执行。当insert触发器触发时，新的纪录增加到触发器表中和inserted表中 inserted表是一个逻辑表，保存了所插入记录的备份，允许用户参考insert语句中数据。触发器可以检查inserted表，来确定触发器的操作是否应该执行和如何执行

Delete 和 update触发器的工作原理和起相同

例子：

Create triggert_accountData_insert

On accountData

With encryption

For insert

Declare@insertActionAmount money

Select@insertActionAmount = accountAmount

From inserted

Insert intoaudiAccountData(audit_log_actionType,audit_log_amount)

Values(‘insert’,@insertActionAmount)

Create triggeraccountData_delete

On accountData

With encryption

For delete

Declare@deleteActionAmount money

Select@deleteActionAmount =accountAmount

From deleted

Insert intoaudiAccountData(audit_log_actionType,audit_log_amount)

Values(‘delete’,@insertActionAmount)

DDL触发器：DDL触发器的触发事件主要是create,alter,drop以及grant ,deny,revork

并且触发的时间条件只有after

没有instead of

语法：

Create trigger trigger_name

On {all server|database}

With encryption

{for|after}{event_tpye}

As sql_statement

例子：

Create triggersafetyAction

On database

For drop_table,after_table

Print N’禁止删除或修改当前数据库中的表’

Rollback

0 0