数据库常用命令概括

一.数据库：

1.创建数据库

create database database_name

2.相应的删除数据库

drop database database_name

3.以及对数据库的备份

注意需要一个存储备份的设备device，或者说空间

use master

EXEC sp_addumpdevice  'disk'   ,   'testBack',     'c:\mssql7backup\MyNwind_1.dat'

有了备份空间使如下语句进行备份

backup database pubs to testback

4.修改数据库的名字

sp_renamedb 'old_name','new_name'

5.分离数据库

sp_detach_db 'c:\mssql7backup\MyNwind_1.dat'

6.附加数据库

sp_attach_db 'c:\mssql7backup\MyNwind_1.dat'

7.压缩数据库

dbcc shrinkdatabase(database_name)

二,表

1.创建新表

方式一：直接创建新表

create table table_name

( 

      stu_id   char(10)   not null  primary key,

      name   char(10)  not null,

)

初始化表

truncate table table_name

方式二：按原来旧表的方式创建新表（相当于把旧表所有的属性都加到了新表）

create table table_new like tab_old

create table table_new as select col1,col2...from table_old definition only （相当于是把旧表的某一些属性加到了新表）

 2.相应的删除表

drop table table_name 

注意到这个drop是真的删除了这个表，属性也被删除了，空间必然得到释放；而我们常常会使用的是清空数据，释放空间

这个时候就要用到truncate会删除表中所有的数据，表示清空，这个时候表还在，只是值都为null。

truncate table table_name 

3.对表做添加属性操作(修改表，加属性)

alter table table_name add column col type

这里值得注意的是：这样操作后，列不能删除，类型也不能更改，最多能改的是varchar()的长度。

4.在表中添加主键（修改表，某列col加主键）

  alter table table_name add primary key(col)

相应的删除主键（修改表，删除某列col主键）

alter table table_name drop primary key(col)

5.给表建立(唯一或不唯一)索引(建索引在表某列或某几列)

create 【unique】index index_name on table_name(col....)

相应的删除索引

drop index index_name

这里值得说明的是：索引是不可以再去更改的，要想为其他列建立索引，就必须新建

6.复制表select  into（只复制结构，源表名：table_old,新表名：table_new）

方式一：select *   into table_new from table_old where 范围（注意这个只能用在SQLserver）

方式二：select  top() *into table_new from table_old

7.拷贝表 insert into (拷贝数据，源表名：table_old,新表名：table_new)

insert into table_new(col1,col2,col3..) select (col,col,col...) from table_new

注意，如果是跨数据库之间的表的拷贝，那要使用绝对路径去添加（from in '具体的数据库的绝对路径' where 范围）

insert into table_new(col1,col2,col3..)select (col1,col2,col3..) from in '  "&Server.MapPath(".")& " \data.mdb" &"' where 范围

三.视图简解

1.创建视图

create view view_name as select statment

2.相应的删除视图

drop view view_name

3.查看与某表相关的视图，存储过程，函数

select table_name.* from sysobjects a,syscomments table_name2 where table_name.id =table_name2.id and table_name.text like '%table_name%' 

4.查看数据库对象的相关信息

sp_depends view_name

5.查看数据库对象的数据类型信息

sp_help view_name

四.单表的增删改查

1.选择范围

select * from table_name where 范围

注意这里的范围：

有时候很简单，提示一个使用 in 的技巧，表示范围是不是在这个值中

如 select *from table_name where a [not] in ('值1'，'值2'，'值3')

还有 between 

如 select *from table_name where num [not] between num1 and num2 

2.插入表中（列1，列2...）对应的(值1，值2....)

insert into table_name( field1,field2...) values(values1,values2.....)

3.删除表中行数

delete from table_name where 范围

注意这里要提到一个知识点：删除重复的记录（distinct）

A方式：delete from table_name where id  in (select id  from table_name group by id having count(id)>1)

注意这里出现分组的知识点，那我们先把分组的讲清楚：

分组，group by 指的是对数据按某个属性进行分组，配合having  条件语句最佳

B方式：使用distinct

select distinct *  from table_name 

部分去重：select distinct col1,col2 from table_name 

4.改表中某列某范围的值

update table_name set fieled1=values where 范围

5.查找某范围内符合要求的值

select * from table_name where 范围 like '%value1%'

这里说一下匹配字的知识：

[  ] 匹配的是一个范围中的任意一个字符；_     匹配的是一个任意字符；  [ ^]   不匹配这个范围；   %    匹配任意个字符

    注意：特殊字符需要转义，使用[ ] 可以转义 如[@#$%^]这样的方式去匹配，不过这样用^是不能转义的；也可以使用Escape去转义 如 '  'Esacpe'  \@#$%   '

如 :

A.select  *  from table_name where column_id like '[1-9]_db[^a-c]%'

表示查询的是 某表中columu_id列中以1-9开头的 第二位是任意字符 三四为一定为db第五位一定不是a-c的数据

B. select * from table_name where "averylongstring"   like '  % '+book+' %   '

使用+和%连接，匹配时book会被具体的内容替代

C.select charindex('789','123456789',2) 表示从第第2为匹配789，返回值为7

charindex(short,long,start)这个函数是说从start位置在长串中匹配短串，然后返回匹配的最开始位置

6.排序（某范围从大到小(降序desc)，从小到大(acs)）

select * from table_name order by field1,field2 desc

7.查找运算函数

总数：select cout as totalcount from table_name

某范围求和：select sum(field1) as sumvalue from table_name

求平均：select avg(field1) as avgvakue from table_name

求最大：select max(field1) as maxvalue from table_name

求最小：select min(field1) as minvalue from table_name

五.表间的操作运算

1.union运算符(将两个表组合并消去重复行成一个结果表，加上All则不消除重复行)（求并集）

如：

select column_id  from table_name1union select column_id from table_name2

select column_id  from table_name1 union  All select column_id from table_name2

2.except 运算符（将在table_name1中但不在table_name2中的行并消除重复行成一个结果表，加上All则不消除重复行）（求补集）

select column_id  from table_name1 except select column_id from table_name2

select column_id  from table_name1  except  All select column_id from table_name2

3.intersect 运算符（将在table_name1并且也在table_name2中的行并消除重复的行成一个结果表，加上All则不消除重复行）（求交集）

select column_id  from table_name1 intersect select column_id from table_name2

select column_id  from table_name1  intersect  All select column_id from table_name2

六.多表的查询操作

1.使用外连接（有三种方式：注意这个ｏｕｔｅｒ关键字可以省略，默认的）

A.左（外）连接 left　(outer) join－组成结果表中包括的是子句中指定左表的所有行，而不仅仅只是连接列所匹配的行；若左表的某行在右表中没有匹配行，则结果表中右表的所有选择列表均为空值。

select table_name1.name,table_name2.name from table_name1 left jointable_name2 on table_name1.id=table_name.id 

B.右（外）连接right　(outer)join－组成结果表中包括的是子句中指定右表的所有行，而不仅仅只是连接列所匹配的行；若右表的某行在左表中没有匹配行，则结果表中左表的所有选择列表均为空值。

select table_name1.name,table_name2.name from table_name1 right join table_name2 on table_name1.id=table_name.id 

Ｃ．完整（外）连接full　(outer)join－组成的结果表中包含左表和右表的所有行；若某行在另一个表中没有匹配行，则匹配列为空值。

select table_name1.name,table_name2.name from table_name1 full　join table_name2 on table_name1.id=table_name.id

2.使用内（inner）连接（使用=或者<  >等比较运算符根据每个表共有的列的值来匹配两表中的行）

Ａ．相等连接　（结果只会显示匹配好的行）

如：select table_name1.name,table_name2.name from table_name1  inner　join table_name2 on table_name1.id=table_name.id

Ｂ.　自然连接（是一种特殊的等值连接，要求比较的属性必须是相同的元素组，并且结果表中会把重复的属性行去掉）

１．纯自然连接（natual join）：当源表和目标共享相同名称的列时，就可以在它们之间执行自然连接，而不需要指定连接列

如：select table_name1.col,table_name2.col from table_name1 natual join table_name2（ｓｑｌ）

　 select table_name1.col,table_name2.col from table_name1,table_name2（ｏｒａｃｌｅ）

（

注意：如果使用纯自然连接时，若没有相同的列，会产生交叉连接的（笛卡尔积）

交叉连接：结果表中返回行是左表中每一行与右表中所有行的组合：

如：select table_name1.id,table_name2.name from table_name1 cross join table_name2

）

２.自然连接（join using ）／（join on）－（注意关键字 on，using和natual 不能同时出现）

如：join using　：当源表和目标表有多个共同的列名，而不想它们作为连接列时。

select table_name1.col,table_name2.col from table_name1 join table_name2using (col1,col2．．．)（ＳＱＬ）

select table_name1.col,table_name2.col from table_name1，table_name2  where table_name1.col=table_name2.col（Ｏｒａｃｌ）

join on：显示陈述连接列，也可以用来指定一些条件来限制连接创建的结果集

　　select table_name1.col,table_name2.col from table_name1join table_name2 on(table_name1.co=table_name2.col)（ＳＱＬ）

select table_name1.col,table_name2.col from table_name1，table_name2where table_name1.co=table_name2.col（Ｏｒａｃｌ）

七.基于前面基础的查询扩充

1.列出数据库中的所有的表名(U 代表用户)

select name from sysobjects where xtype ='U' and status>=0 order by name 

2.列出表里中所有的列名（属性名）

select name from syscolumns where id =object_id('table_name')

3.随机选择记录

select newid()

4.随机的选出10条数据

select top 10 *from table_name order by newid()

5.某范围选择前10条记录

select top 10 *from table_name where 范围

6.数据库中时间的查询

A.显示文章，提交人，和最后回复时间

select    a.title ,  a.username ,  b.adddate   from   table a  ,  (select max(adddate) adddate  from table where table.title =a.title) b

B.日程安排(table_name)提前五分钟提醒

select * from table_name where datediff('minute',f开始时间,getdate())>5