Transact-SQL数据库基本操作

Transact-SQL数据库基本操作

一．创建、修改、删除数据库(database)

/*创建数据库*/

create database USER_INFO

/*

ON（可选）：指定存放数据库的数据文件信息；

LOG ON（可选）：指明事务日志文件的明确定义。

FILENAME用于指定数据库文件存储目录，否则默认存放在

%Program Files%Microsoft SQLServer\MSSQL\Data

*/

ON

(

NAME = USER_INFO_Data,

FILENAME ='d:\My Documents\

SQL Server 2000-DB\USER_INFO.mdf'

)

LOG ON

(

NAME = USER_INFO_log,

FILENAME ='d:\My Documents\

SQL Server 2000-DB\USER_INFO.ldf'

)

/*修改数据库名称*/

sp_renamedb 'USER_INFO','ExamSystem';

/*删除数据库*/

drop database USER_INFO

二．创建、修改、删除数据表(table)

/*创建部门表*/

create table DEPARTMENT

(DEPT_ID         int            NOT NULL,

 DEPT_NAME       varchar(20)  NOT NULL,

 PARENT_DEPT_ID  int          default NULL,

 primary key (DEPT_ID)

)

/*创建用户信息表*/

create table USER_INFO

(

/*字段名   数据类型       能否为空 */

USER_NO  CHAR(6)      NOT NULL,

NAME     VARCHAR(20)  NOT NULL,

DEPT_ID  INTEGER      NOT NULL,

JOB_ID   INTEGER      NOT NULL,

GRADE    TINYINT      NOT NULL,

STATE    TINYINT      NOT NULL

primary key (USER_NO)/*定义主键*/

foreign key (DEPT_ID)/*指定外键*/

            references DEPARTMENT

            on delete set null

)

/*references DEPARTMENT on delete[restrict|cascade|set null]*/

1.restrict:表示当表DEPTMENT中的某DEPT_ID被表USER_INFO引用时，禁止删除表DEPTMENT中的DEPT_ID=USER_INFO.DEPT_ID的部门记录。

2.cascade: 表示当表DEPTMENT中的某DEPT_ID被表USER_INFO引用时，若删除表DEPTMENT中的DEPT_ID=USER_INFO.DEPT_ID的部门记录，则表USER_INFO中的该条记录也被删除。

3.set null: 表示当表DEPTMENT中的某DEPT_ID被表USER_INFO引用时，若删除表DEPTMENT中的DEPT_ID=USER_INFO.DEPT_ID的部门记录，则表USER_INFO中的该条记录的DEPT_ID相应更改为null。

/*修改数据表名称*/

sp_rename 'USER_INFO','USER_DETAILS';

/*删除表*/

drop table USER_INFO

三．增加、修改、删除表字段(attribute)

/*修改字段名:将表USER_INFO的GRADE字段重命名为RANK*/

sp_rename  'USER_INFO.[GRADE]','RANK','COLUMN';

/*修改字段的数据类型*/

alter table USER_INFO modify JOB_ID tinyint;

/*增加新字段*/

alter table USER_INFO ADD DELETED tinyint;

/*删除字段*/

alter table USER_INFO drop STATE;

四．创建、修改、删除数据记录(record)

/*增加记录*/

insert into USER_INFO

values('000001','Funny',5055,18,3,1);

insert into USER_INFO

values('000002','John',5056,19,3,1);

/*修改记录:如果没有where条件限制，将应用到所有记录*/

update USER_INFO set NAME='Fantasy' where USER_NO=

'000001';

/*删除记录:如果没有where条件限制，将应用到所有记录*/

delete from USER_INFO where USER_NO='000001';

五．查询数据记录(query analysis)

1. 查询记录数和指定列上的取值数

/*没有指定字段，将查询符合条件的记录条数(查询部门表中的部门记录总数)*/

select count(*) from DEPARTMENT;

/*查询部门表中PARENT_DEPT_ID字段上的取值数:

PARENT_DEPT_ID非空，包括重复值*/

select count(PARENT_DEPT_ID) from DEPARTMENT;

/*查询部门表中PARENT_DEPT_ID字段上的取值数(上级部门总数):

PARENT_DEPT_ID非空，不包括重复值*/

select count(distinct PARENT_DEPT_ID) from DEPARTMENT;

/*查询部门表中上级部门号为8的记录中

DEPT_ID字段上的取值数(8号部门的子部门数)*/

select count(DEPT_ID) from DEPARTMENT where PARENT_DEPT_ID=8;

2.查询记录或指定列上的取值

/*查询表USER_INFO中的所有记录*/

select * from USER_INFO

/*查询表DEPARTMET中的所有记录PARENT_DEPT_ID字段上的取值:

包括NULL和重复值*/

select  PARENT_DEPT_ID  from DEPARTMENT;

/*查询表DEPARTMET中的所有记录PARENT_DEPT_ID字段上的取值:

包括NULL，不包括重复值*/

select  distinct PARENT_DEPT_ID  from DEPARTMENT;

/*查询表DEPARTMET中的所有记录PARENT_DEPT_ID字段上的取值:

不包括NULL和重复值*/

select  distinct PARENT_DEPT_ID  from DEPARTMENT where PARENT_DEPT_ID is not null;

3.分组查询

/*分组查询，包括NULL，不包括重复值*/

select  PARENT_DEPT_ID  from DEPARTMENT group by PARENT_DEPT_ID;

/*分组查询，不包括NULL，不包括重复值*/

select  PARENT_DEPT_ID  from DEPARTMENT where PARENT_DEPT_ID is not null

group by PARENT_DEPT_ID;

/*查询所有非空上级部门及其子部门数量*/

select  PARENT_DEPT_ID,count(*)  from DEPARTMENT where PARENT_DEPT_ID is not null

group by PARENT_DEPT_ID;

---- 模糊匹配查询(不区分字母大小写) ----

/*左匹配:查询姓名以l|L开头的用户记录*/

select * from USER_INFO where NAME like 'l%'

/*右匹配:查询姓名以ry|Ry|rY|RY结尾的用户记录*/

select * from USER_INFO where NAME like '%rY';

/*全匹配:查询姓名中包含n|N的用户记录*/

select * from USER_INFO where NAME like '%n%'

4.多表查询 where为其连接条件

/*查询所有用户及其部门详细信息*/

select USER_INFO.*,DEPARTMENT.*

from   USER_INFO , DEPARTMENT

where  USER_INFO.DEPT_ID=DEPARTMENT.DEPT_ID

/*查询上级部门号为6的用户编号和姓名，量词in和exists前可加not否定式 */

--嵌套查询方式1

select USER_NO,NAME from USER_INFO

where DEPT_ID in

(

select DEPT_ID from DEPARTMENT

where PARENT_DEPT_ID=6

)

--嵌套查询方式2

select USER_NO,NAME from USER_INFO

where 6 IN

(

select PARENT_DEPT_ID from   DEPARTMENT

where  DEPARTMENT.DEPT_ID=USER_INFO.DEPT_ID

)

--嵌套查询方式3

select USER_NO,NAME from USER_INFO

where exists

(

select * from   DEPARTMENT

where  DEPARTMENT.DEPT_ID=USER_INFO.DEPT_ID

       AND PARENT_DEPT_ID=6

)

5.集合查询

/*查询至少属于部门5和部门7的用户编号和姓名(或运算)*/

(select  USER_NO,NAME from  USER_INFO where DEPT_ID=5)

union

(select  USER_NO,NAME from  USER_INFO where DEPT_ID=7);

--效果同嵌套查询方式1的集合查询式

select USER_NO,NAME from USER_INFO

where DEPT_ID=some

(

select DEPT_ID from DEPARTMENT

where PARENT_DEPT_ID=6

);

参考：

《SQL Server 2000数据库概述》

《Eclipse连接数据库及基本操作》