T-SQL 操作数据库

 SQL语言的分类

SQL是一个标准的数据库语言，是面向集合的描述性非过程化语言

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1、数据查询语言DQL (Data Query Language) 

 

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：

 

SELECT <字段名表>

 

FROM <表或视图名>

 

WHERE <查询条件>

 

2、  数据操纵语言DML(Data Manipulation Language)

 

数据操纵语言DML主要有三种形式：

 

1) 插入：INSERT

 

2) 更新：UPDATE

 

3) 删除：DELETE

 

3、  数据定义语言DDL(Data Definition Language)

 

数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：

 

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

 

| | | | |

 

表 视图 索引 同义词 簇

 

4、  数据控制语言DCL(Date Control Language)

 

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

 

1) GRANT：授权。

 

2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。

 

回滚---ROLLBACK

 

回滚命令使数据库状态回到上次最后提交的状态。其格式为：

 

SQL>ROLLBACK;

 

3) COMMIT [WORK]：提交。

 

在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。

 

提交数据有三种类型：显式提交、隐式提交及自动提交。下面分别说明这三种类型。

 

(1) 显式提交

 

用COMMIT命令直接完成的提交为显式提交。其格式为：

 

SQL>COMMIT；

 

(2) 隐式提交

 

用SQL命令间接完成的提交为隐式提交。这些命令是：

 

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

 

(3) 自动提交

 

若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：

 

SQL>SET AUTOCOMMIT ON；

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

 

创建数据库  

 

use   master       -- 指定使用 master  数据库

go

 if exists( select * from  sysdatabases  where name =  “ school ” )

drop database school   -- 若 school 数据库不存在，则删除

go

create database school       -- 创建数据库 school

On [primary]

 (

 name= school _dat,    -- 数据库主文件逻辑名称

filename=‘C:\Data\school.mdf’,    -- 数据库主文件物理名称

size=10,    -- 指定主文件初始大小

maxsize=50,   -- 指定主文件最大值

filegrowth=5   -- 指定主文件增长值

)

 log on

 (

 name=‘ school _log’,   -- 数据库日志文件逻辑名称

filename='c:\data\school.ldf',  -- 数据库主文件物理名称

 size=5,  -- 指定日志文件初始大小

maxsize=25mb ,   -- 指定日志文件最大值

filegrowth=5  -- 指定日志文件增长值

 )

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

 

创建数据库表

 

vuse school   --   指定数据库

go

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

drop table student  – 如果已存在 student 表，则删除

go

create table student

(

     id int   identity(1,1)    primary  key  not null,               --     编号 , 标识列

    sno  varchar(12) not null,                                        --     学号    唯一

    sex int not null,                                                         --    用户性别

    age int not null,                                                       --   年龄

    address varchar(50) not null                                   --   地址

)

go

 

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

 

创建约束

主键约束    primary  key

唯一约束    unique

默认约束    default

外键约束    foreign key

检查约束    check

注意！

主键： FK_

唯一： UQ_

默认： DF_

外键： FK_

检查： CK_

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

 

为数据库表添加主键约束

Alter  Table  stuInfo

Add Constraint  PK_stuNo  Primary Key (stuNo)

 

为数据库表添加唯一约束

Alter  Table  stuInfo

Add Constraint  UQ_SNO  unique(SNO)

 

为数据库表添加默认约束

ALTER TABLE student

ADD  CONSTRAINT   DF_Sex  DEFAULT (1)  FOR sex

 

为数据库表添加外键约束

alter table bbsSection

add constraint FK_CID  FOREIGN  KEY(CID) REFERENCES Class(CID)

 

为数据库表添加检查约束

ALTER TABLE sex

ADD CONSTRAINT CK_Sex CHECK (sex = 0 or sex = 1)

 

常用检查约束表达式

 

age >0  and age < 101       年龄在 1 到 100 之间

sex =0  or  sex = 1  性别只能为 0 或 1 

len(password)=4           密码长度等于 4

password like  ‘ [0-9] [a-z][a-z][a-z] ’    密码为四位，第一位是数字，后三位是小写字母  

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

 

简单   ：增，删，改，查

增： insert  into  student[(name,age,address)] values( ‘ jack ’ ,21, ’ 北京 ’ )

删： delete from student where id = 1

改： update student  set name = ‘tom’ , age =22  where id = 2

查：  select * from student where id = 3

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

 

深入查询

1, 查询 student 表中所有数据 , 以 name 列排序

select * from student order by name [asc or  desc]

2, 查询 student 表中前 3 条数据

select top 3 * from student

Mysql  没有 top, 使用  limit

Select *From student limit 0,4

3, 查询 student 表中不重复的数据

  select distinct name[,address] from student  

 

4, 两表联查 (where  写法 )

select student.name,class.name from student,class  where student.cid = class.cid

5, 两表联查 (join on)

select student.name,class.name from student Join class on student.cid = class.cid

 

6,join 的其他类型

[inner] join: 只显示符合条件的记录，默认为 jion

left [outer] jion :  显示左边表中所有的记录，以及右边表中符合条件的记录

right [outer] jion: 显示右边表中所有的记录，以及左边表中符合条件的记录

cross join : 将一个表的每一条记录和另一个表的每一条记录搭配成新的记录，不需要用 on 来设置条件

 

 

select student.name,class.name

from student   left  join class

on student.cid = class.cid

 

 

select student.name,class.name

from student   right  join class

on student.cid = class.cid

 

select student.name,class.name

from student   cross  join class

on student.cid = class.cid

 

 

7 ，使用 where  子句条件查询

 

7.1  可以灵活应用比较运算符：

=       等于

<>     不等于

!=      不等于

>        大于

>=     大于等于

!>      不大于

<       小于

<=      小于等于

!<      不小于

select * from student where id <5

select * from student where id <>4

select * from student where id !> 5

 

7.2  使用 and( 和 )  ， or( 或 )  连接不同   查询

select * from student where id <5  and id >2

select * from student where id !<5  and id <7

7.3   使用 in  查询

select * from student where id in (2,3,4

 

7.4   使用 like 模糊查询

通配符：

%( 替代任意多个   字符的任意字符串 )

_( 替代任何单个字符 ) 

[]( 替代指定范围 [a.f] 或集合 [abcdef] 中的任何单个字符 )

[^]( 替代不属于指定范围 [a.f] 或集合 [abcd] 的任何单个字符 )

select * from student where  name like ‘[0-9]’

select * from student where  name like  ‘ [^abcd] ’

select * from student where  name like  ‘  [abc]_ ’

select * from student where  name like  ‘ [^0-9]% ’

注意 !

like  ‘ abc [_] ’       等同字符  : abc_

like  ‘ abc[%] ’       等同字符  : abc%  

like  ‘ ab[%]c       等同字符 : ab%c

like  ‘ []] ’                等同字符 : ]

like  ‘ []]abc ’         等同字符 : ]abc

 注：当where多个条件时，需要在查询条件外面加();

例：select * from student where age > 23 and (name like  ‘ [^0-9]% ’ or name like ‘  [abc]_ ’ or name like ‘[0-9]’)

 

8.1   使用 any  查询     只要有一行使结果为真，则结果为

select * from student where age > any (select count from class)

8.2   使用 some 查询     和 any 使用相同

select * from student where age > some(select count from class)

8.3   使用 all  查询     要求子查询的所有行都使结果为真 ,  则结果为真

select * from student where age > all (select count from class)

 

 

9 , 使用 order by  子句排序 asc | desc

select * from student order by name  [asc | desc]

select * from student order by address ,name [asc | desc]

10.1 ,  使用 group by  简单分组

select cid,count(id) from student group by cid

10.2 ,  使用 group by  分组， with cube 汇总

select cid,age,count(id) count from student group by cid,age with cube

10.3 ,  使用 group by  分组， with rollup 汇总

select cid,age,count(id) count from student group by cid,age with rollup

注意！

要查询的列必需包含在聚合函数或 GROUP BY  子句中

 me where id not in

       (select top  行数  x ( 页数 -1) id form tableName)

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

 

 

视图查询

 

视图：是另一种查看数据库中一个或多个表中的数据的方法，视图是一种虚拟表，通常是作为一个或多个表的行或列的子集创建的。

视图通常用来进行了以下 3 种操作：

1 、筛选表中的行

2 、防止未经许可的用户访问敏感数据

3 、将多个物理数据表抽象为一个逻辑表据表

使用 T — SQL 创建视图

Create VIEW view_name as <select  语句 >

例： use stuDB go

      if exists(select *from sysobjects where name= ‘ view_stuinfo_stumarks ’ )

drop VIEW view_stuinfo_stumarks go

create VIEW view_stuinfo_stumarks

as

select  姓名 =stuName, 学号 =stuinfo.stuNo ，笔试成绩 =writtenExam ，机试成绩 =labExam ，

平均分 =(writtenExam+labExam) / 2

 from stuinfo left join stumarks on stuinfo.stuno = stumarks.stuNo

go

查询视图

Select *from view_stuinfo_stumarks

 

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

 

 

Oracle 中去掉重复值

 

SELECT distinct p.viewDocumentID FROM Print p,ViewDocument  WHERE  p.isvalid =1 AND ( p.printerID=:useId")

SELECT count(distinct p.viewDocumentID) FROM Print p,ViewDocument v WHERE p.isvalid =1 AND ( p.printerID=:useId “ )

Oracle 添加字段

添加单列

ALTER   TABLE   表名    ADD   列名   数据类型

添加多字段

ALTER   TABLE   表名    ADD   (   列名 1   数据类型 1, 列名 2   数据类型 2)

Oracle 修改字段

修改单列数据类型

ALTER   TABLE   表名    MODIFY    列名   数据类型   

同时修改多列数据类型  

ALTER   TABLE   表名    MODIFY   (   列名 1   数据类型 1, 列名 2   数据类型 2) 

 

 

Oracle 删除字段

删除单列  

ALTER   TABLE   表名    DROP   COLUMN   列名  

删除多列

ALTER   TABLE   表名    DROP   ( 列名 1, 列名 2)

Oracle 同时添加和修改多列  

ALTER   TABLE   表名    ADD   (   列名 1   数据类型 1, 列名 2   数据类型 2)

MODIFY   (   列名 3   数据类型 3, 列名 4   数据类型 4)

 

 

11 、使用 group by all  分组查询

select cid,count(id) count from student where cid >2 group by all cid

12 、使用 having  子句分组查询

select cid,count(id) count from student group by cid having cid >2

13 、简单分页查询

Select top  行数  *from tableNa