oracle基础

1.           输出表中的所有列  select *  from 表名

2.           输出表中的部分列  select 字段名表 from 表名

3.           为结果集内的列指定列名  select {字段名1 [ as 列的别名]  [，…n]}  from 表名

4.           消除取值重复的行，在select 后面紧跟 distinct

5.           限制返回的行数    select top 行数 列名表 from 表名

6.           查询满足条件的元组  select *  from 表名 where 查询条件

1)           比较：列表达式1 比较运算符[列]表达式2

2)           确定范围：列表达式 [NOT] Between 起始值 And 终止值

select *

from 学生

where 出生日期 between '1980-05-01'and '1982-12-31'

3)           确定集合：列表达式 [NOT] IN (列值1，列值2，……)

select *

from 班级

where 系部代码 in('01','02')

4)           字符匹配：列表达式 [NOT] Like ‘匹配串’ [escape<’换码字符’>] 

匹配串中可以有：%、_、[…]、[^…]

select *

from 课程

where 课程名 like 'Delphi/_6.0' escape'/'

5)           涉及空值的查询：列表达式 IS  [NOT] NULL

6)           多重条件查询：使用AND、OR、NOT连接多个查询条件

7.           对查询的结果排序：select * from 表名 [where 查询条件]  ORDER BY <列名[ASC|DESC]> [,…n]

select 学号,成绩

from 课程注册

where 课程号='0001'

order by 成绩 asc

8.           对数据进行统计

1)           使用集合函数：可以前带DISTINCT或ALL

统计记录个数                                   COUNT(*)

统计一列中值的个数                         COUNT(列名)

计算一列中值的总和                         SUM(列名)

计算一列中值的平均值       AVG(列名)

计算一列中值的最大值       MAX(列名)

计算一列中值的最小值       MIN(列名)

2)           对结果进行分组操作：select * from 表名 [where 查询条件]GROUP BY 列名[HAVING  筛选条件表达式]

select 课程号,COUNT(*) AS 选课人数

from 课程注册

GROUP BY 课程号

HAVING COUNT(*)>=3

3)           使用COMPUTE子句：可以对查询结果集中的所有记录进行汇总统计，并显示所有参加汇总记录的详细信息。select * from 表名 [where 查询条件] ORDER  BY 列名 COMPUTE 集和函数 [BY 列名]

select *

from 课程注册

ORDER BY 学号

COMPUTE SUM(成绩) BY 学号

9.           用查询结果生成新表：select *  into 新表名  from 表名 [where 查询条件]

10.          合并结果集：相当于集合的并操作

select 语句

 {union   select 语句}

   [，…n]

11.          连接查询（在一个查询中涉及多个表）

1)           交叉连接查询

              交叉连接：又称非限制连接（广义笛卡尔积），它是将两个表不加约束地连接在一起，连接产生的结果集的纪录为两个表中记录的交叉乘积，结果集的列为两个表属性列的和。交叉连接产生的结果集一般没有什么实际应用的意义，实际使用很少。

              语法格式：SELECT<列名表> FROM 表名1  CROSS JOIN 表名2

                                            <列名表>中罗列的列名是结果集中出现的列名（取自表名1和表名2中的部分或全部）

2）         等值与非等值连接查询

连接查询中用来连接两个表的条件称为连接条件，连接条件的一般格式为；

[<表名1>.]<列名>  <比较运算符>  [<表名2>.]<列名>

<比较运算符>主要有：=，>，<，>=，<=，!=。

当比较运算符主要为=时，成为等值连接，其语法格式为：

SELECT <列名表> FROM 表名1  [INNER] JOIN 表名2 ON [<表名1>.]<列名>=[<表名2>.]<列名>

使用其它运算符的连接为非等值连接。（还可以有其他条件符号）

SELECT *

FROM 产品 INNER JOIN 产品销售 ON 产品.产品编号=产品销售.产品编号

ALTER TABLE 产品

ADD  产量 INT

UPDATE 产品

SET 产量=28

WHERE 产品编号='0001'

UPDATE 产品

SET 产量=30

WHERE 产品编号='0002'

UPDATE 产品

SET 产量=40

WHERE 产品编号='0003'

UPDATE 产品

SET 产量=40

WHERE 产品编号='0004'

SELECT *

FROM 产品

SELECT *

FROM 产品销售

SELECT *

FROM 产品 INNER JOIN 产品销售 ON 产品.产量>产品销售.销量

SELECT *

FROM 产品 INNER JOIN 产品销售 ON 产品.产量>产品销售.销量 AND 产品.产品编号=产品销售.产品编号

或

SELECT *

FROM 产品 , 产品销售

where 产品.产量>产品销售.销量 AND 产品.产品编号=产品销售.产品编号

 

3）         自身连接查询（目的是为了在一个表中查找具有相同列值的行）

一个表与其自身进行的连接（也可以理解为一个表的两个副本之间的连接）。

在进行自身连接查询时，必须为表指定两个别名，且对所有列的引用均要用别名限定。

例：查找不同课程成绩相同的学生的学号、课程号和成绩

select a.学号,a.课程号

from 课程注册 as a join 课程注册 as b

on a.学号=b.学号 and a.课程号!=b.课程号

4）         外连接查询

不仅将满足条件的记录列出，而且还可以将不满足条件的记录的基本信息列出。外连接分为：

左外连接：将左表作为主表，主表中所有记录分别与右表的每一条记录进行连接组合，结果集中除了满足条件的记录外，还有主表中不满足连接条件的记录并在右表的相应列上填充NULL值。语法如下，

SELECT 列名表

FROM 表名1 LEFT [OUTER] JOIN 表名2

ON 表名1.列名=表名2.列名

例：将“产品”表左外连接“产品销售”表

SELECT *

FROM 产品 LEFT OUTER JOIN 产品销售

ON 产品.产品编号=产品销售.产品编号

右外连接：将右表作为主表，主表中所有记录分别与左表的每一条记录进行连接组合，结果集中除了满足条件的记录外，还有主表中不满足连接条件的记录并在左表的相应列上填充NULL值。语法如下，

SELECT 列名表

FROM 表名1 RIGHT [OUTER] JOIN 表名2

ON 表名1.列名=表名2.列名

例：将“产品”表右外连接“产品销售”表

SELECT *

FROM 产品 RIGHT OUTER JOIN 产品销售

ON 产品.产品编号=产品销售.产品编号

全连接：讲左表所有记录分别与右表的每一条记录进行连接组合，结果集中除了满足连接条件的记录外，还有左、右表中不满足连接条件的记录并在左、右表的相应列上填充NULL值。语法如下，

SELECT 列名表

FROM 表名1 FULL [OUTER] JOIN 表名2

ON 表名1.列名=表名2.列名

例：用全连接的方法连接“产品”表与“产品销售”表

SELECT *

FROM 产品 FULL OUTER JOIN 产品销售

ON 产品.产品编号=产品销售.产品编号

5）         复合连接条件查询

在ON连接表达式中可以有多个连接条件。

例如：查询成绩在75分以上的学生的学号、姓名，选修课的课程号、课程名、专业代码、成绩，任课教师的教师编号、姓名。

SELECT B.课程号,D.课程名,C.教师编号,C.姓名,A.学号,A.姓名,B.专业代码,B.专业学级,B.学期,B.成绩

FROM 学生 AS A JOIN 课程注册 AS B

  ONA.学号=B.学号 AND B.成绩>75

 JOIN 教师 AS C

   ONB.教师编号=C.教师编号

 JOIN 课程 AS D

   ONB.课程号=D.课程号

或

SELECT 课程注册.课程号,课程.课程名,教师.教师编号,教师.姓名,学生.学号,学生.姓名,

课程注册.专业代码,课程注册.专业学级,课程注册.学期,课程注册.成绩

FROM 学生, 课程注册,教师,课程

WHERE 学生.学号=课程注册.学号 AND 课程注册.成绩>75 AND 课程注册.教师编号=教师.教师编号 AND 课程注册.课程号=课程.课程号

12.          子查询

SELECT 语句可以嵌套在其他许多语句中，这些嵌套的SELECT语句就称为子查询，子查询的SELECT查询总是使用圆括号括起来，且不能包括COMPUTE或FOR BROWSE子句。

SQL Server对嵌套查询的求解方法是由里向外处理，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立父查询的查询条件。嵌套查询可以用多个简单的查询构造复杂的查询，但嵌套不能超过32层。

1）         带有IN运算符的子查询

子查询的结果是一个结果集，父查询通过IN运算符将父查询中的一个表达式与子查询结果集中的每一个值进行比较，如果表达式的值与子查询结果集中的任何一个值相等，父查询中的“表达式 IN （子查询）”条件表达式返回TRUE，否则返回FALSE。NOT IN运算符与IN运算符结果相反。

 

父查询 (子查询）

例如：查询有产品销售记录的产品信息。

  查询选修了课程名为“大学语文”的学生学号和姓名。

SELECT 学号,姓名  FROM 学生   WHERE 学号 IN  

(SELECT 学号   FROM 课程注册   WHERE 课程号 IN

      (SELECT 课程号      FROM 课程      WHERE 课程名='大学语文'

      )

  )

2）         带有比较运算符的子查询

父查询通过比较运算符将父查询中的一个表达式与子查询结果（单值）进行比较，如果比较运算的结果是TRUE，父查询中的“表达式  比较运算符（子查询）”条件表达式返回TRUE，否则返回FALSE。

例如：列出高于平均分的学生的信息。

 

SELECT * FROM 学生 WHERE 学号 IN

 (SELECT 学号    FROM 课程注册    WHERE 成绩>

     (SELECT AVG(成绩)  FROM 课程注册

)

    )

3）         带有ANY或ALL运算符的子查询

子查询中返回单值是可以用比较运算符，当返回的是一个结果集时，可以使用ANY或ALL运算符（必须同时使用比较运算符）。

ANY或ALL与比较运算符一起使用的语义

运 算 符              语    义

>ANY     大于子查询结果中的某个值

>ALL      大于子查询结果中的所有值

<ANY     小于子查询结果中的某个值

<ALL      小于子查询结果中的所有值

>=ANY   大于等于子查询结果中的某个值

>=ALL    大于等于子查询结果中的所有值

<=ANY   小于等于子查询结果中的某个值

<=ALL    小于等于子查询结果中的所有值

=ANY     等于子查询结果中的某个值

=ALL      等于子查询结果中的所有值

例如：查询比“010101001”班中某一学生年龄大的其他班的学生学号与姓名。

SELECT 学号,姓名 FROM 学生  WHERE 出生日期 <ANY

  (SELECT 出生日期     FROM  学生    WHERE  班级代码='010101001')

   AND 班级代码<>'010101001'

                查询比“010101001”班中所有学生年龄都小的其他班的学生学号与姓名。

SELECT 学号,姓名   FROM 学生    WHERE 出生日期 > ALL  

(SELECT 出生日期     FROM  学生     WHERE 班级代码='010101001')

                         AND 班级代码<>'010101001'

 

Sql语句资料

 

1.           查询表内所有内容

Select * from 表名

2．命令建库

              Createdatabase 库名 on(name = 库名_dat，filename = “ 路径\库名.mdf ” size = ?mb)

3．命令插入数据

              Use库名

              Insertinto 表名 values( 数据 )

备注：字符型加单引号，数据时间用逗号分开

4．命令建表

              Createtable 库名.dbo.表名(not null 非空 null为空 primary key 主键 中间用逗号分开)

5．命令更新数据

              Update表名 set 列名 ＝ 新数据 where 条件

6．命令查询排序

              Select* from 表名 order by 列名

备注：一般情况配列顺序为正序排列 由小到大 倒序排列：由大到小 加关键字 desc

7．求平均数arg

              Select‘年龄合计’ ＝ sum(字段名) from 表名

8．显示相同数据的总数count

              Selectcount(*) from 表名 where 条件

9．查询一个字段的最大值max

              Selectmax(字段名) from 表名

10．同一数据库中表数据插入新表

              Insert  into 表名（字段名）select 字段名 from 表名 where 条件

11．命令修改数据类型

              Altertable表名 alter column 字段名 新数据类型

12．别名查询

              Selecta.性命，b.电话 from javaa,java.b

              Wherea.ID = b.ID

              From后面定义表的别名

              Where条件

              Select字段名 from 表名

              Union

Select 字段名 from 表名

15．补充主键

                            Altertable 表名 add primary key 字段名

16．查看表信息

                            Excesp_help 表名

17．命令建立索引非簇索引

                            Createindex 索引名 on 表名 字段名

18．删除索引

                            Dropindex 表名 字段名

 

19．建立备份设备

                            Excesp_add umpdevice ‘disk’,’备份的文件名’,’备份地址’

备注：disk 为 备份设备的类型

20．删除备份设备

Exce sp_dropdevice ‘备份文件名’

21．备份数据库

Backup database 库名 to 备份的文件名

22．恢复数据库

Restore database 库名 to 备份的文件名

23．建立视图单表视图

              Createview 视图名（字段名，字段名）

              As

              Select字段名.字段名 from 数据来源表

24．多表视图别名建立

              Createciew 视图名（字段名，字段名）

              As

Select a.字段，a.字段，b.字段，b.字段

From 表名a，表名 b

Where 条件

25．创建触发器

                            Insert事件

                            Createtrigger 触发器名

                            On表名

                            Forinsert

                            As

                            Insertinto 表名 select name,age from inserted

 

                            update事件

                            Insert事件

                            Createtrigger 触发器名

                            On表名

                            Forinsert

                            As

                            Declare@变量名 数据类型（长度）

                            Select@变量名 ＝ 字段 fromdeleted

Select @变量名 ＝ 字段 from inserted

Update 表名 set 字段 ＝@变量

26．创建单一查询的存储过程

                            Createprocedure 名称 @变量名称 数据类型（长度）

                            As

                            Select* from 表名 where 条件字段名 like @变量名称

                            执行exce 存储名称

27．增加约束（默认约束）

                            Altertable add constraint 约束名 default 默认值

                            For字段名

28．检查约束

                            Altertable add constraint 约束名 check 检查条件