数据库小结1

 一、基础

1、创建数据库

CREATE DATABASE database-name

2、删除数据库

 drop database dbname

3、备份sql server

 --- 创建 备份数据的 device

USE master

EXEC sp_addumpdevice 'disk',’testBack', :\mssql7backup\MyNwind_1.dat' --- 开始 备份

BACKUP DATABASE pubs TO testBack

4、创建新表

 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

例子：

CREATE TABLE tableA(

    [id] [int] NOT NULL PRIMARY KEY,

    [Name][nvarchar](50) NOT NULL,

    [Age] [int] NOT NULL,)

根据已有的表创建新表：

A：create table tab_new like tab_old (使用旧表创建新表SQL server2008不可以)

B：create table tab_new as select col1,col2… from tab_old definition only

C: select *into  tableAnew from tableA(复制表结构和表数据。旧表创建新表无主键。SQL server2008R2可以用 亲测)

D：select * into table3 from tableA where 1=2;(复制表数据，主键复制不过去)

E: insert into table3 select * from tableA where id=1;(如果表存在，导入数据)

F: select * into table5 from tableA where 1<>1;（旧表创建新表，只有表结构）

5、删除新表

drop table tabname

6、增删一列

①增加一列：Alter table tabname add column col type

注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

示例：增加一列sex,类型是int：Alter table  table4 add sex  int ;

②删除一列：alter table [表名] drop COLUMN 字段名

示例：alter table table4 drop COLUMN sex;

7、添加主键：

 Alter table tabname add primary key(col)

说明：删除主键：

 Alter table tabname drop primary key(col)

8、创建索引：

create [unique] index idxname on tabname(col….) 删除索引：drop index idxname

注：索引是不可更改的，想更改必须删除重新建。

9、创建视图：

create view viewname as select statement

 删除视图：drop view viewname

10、选择,插入,删除,更新,查找,排序,总数,求和,平均,最大,最小基本的sql语句

选择：select * from table1 where 范围

插入：insert into table1(field1,field2) values(value1,value2)

删除：delete from table1 where 范围

更新：update table1 set field1=value1 where 范围 示例：

update table4 set Name='王五', Age='11'  where  id=1

查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料

排序：select * from table1 order by field1,field2 [desc]

总数：select count as totalcount from table,

求和：select sum(field1) as sumvalue from table1

平均：select avg(field1) as avgvalue from table1

最大：select max(field1) as maxvalue from table1

最小：select min(field1) as minvalue from table1

11、几个高级查询运算词

A： UNION 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B： EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

C： INTERSECT 运算符

NTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。 注：使用运算词的几个查询结果行必须是一致的

12、使用外连接

A、left （outer） join：

左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a =                b.c

     在使用left join时，on和where条件的区别如下：1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。可以理解为，左表记录保留，右表不满足条件的填写为null 2、where条件是在临时表生成好后，再对临时表进行过

滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

 

B：right （outer） join:

右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有

行。

C：full/cross （outer） join：

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

13、分组:Group by:

一张表，一旦分组 完成后，查询后只能得到组相关的信息。

组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准)

在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据

在selecte统计函数中的字段，不能和普通字段放在一起；

14、对数据库进行操作：

  分离数据库：EXEC sp_detach_db DatabaseName

附加数据库：sp_attach_db后接表明，

需要完整的路径名

15.如何修改数据库的名称：

sp_renamedb 'old_name', 'new_name’

16、左匹配、模糊、右匹配、全匹配

左匹配：左边开始是字段条件的比如1左匹配，就是1开头的字段

右匹配：右边开始是条件的

17、SQL模糊查询条件的四种匹配模式

①、%：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号(%%)表示：

例如 SELECT * FROM [user] WHERE u_name LIKE '%三%'
将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。

再例如需要找出u_name中既有“三”又有“猫”的记录，请使用and条件
SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%'

再例如 SELECT * FROM [user] WHERE u_name LIKE '%三%猫%'
虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”。备注：%三：表示左匹配。三%：表示右匹配。%三%：表示模糊查询

②、_ ： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：

例如 SELECT * FROM [user] WHERE u_name LIKE '_三_'
只找出“唐三藏”，这样u_name为三个字且中间一个字是“三”的；

再例如 SELECT * FROM [user] WHERE u_name LIKE '三__'
只找出“三脚猫”这样name为三个字且第一个字是“三”的；

③、[ ] ：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

例如 SELECT * FROM [user] WHERE u_name LIKE '[张李王]三'
将找出“张三”、“李三”、“王三”（而不是“张李王三”）；

再例如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”
SELECT * FROM [user] WHERE u_name LIKE '老[1-9]'
将找出“老1”、“老2”、……、“老9”；

如果要找“-”字符请将其放在首位：'张三[-1-9]'；

Oracle 10g以上的版本用法为：

select * from flow_user where regexp_like(username, '[张王李]飞')

④、[^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

例如 SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三'
将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；
再例如 SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]'
将排除“老1”到“老4”，寻找“老5”、“老6”、……、“老9”。

注：oracle like 不支持正则，你可以使用支持like的正则regexp_like

⑤、查询内容包含通配符时

* 表示查找的是所有信息,例如select * from tbl_user 

由于通配符的缘故，导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现，而把特殊字符用“[ ]”括起便可正常查询。据此我们写出以下函数：
function sqlencode(str)
str=replace(str,"[","[[]") '此句一定要在最前
str=replace(str,"_","[_]")
str=replace(str,"%","[%]")

。

sqlencode=str
end function

注：
在查询前将待查字符串先经该函数处理即可，并且在网页上连接数据库用到这类的查询语句时侯要注意：
例如 Select * FROM user Where name LIKE '老[^1-4]'

18、select isnull(max(字段1+1),1) from table

 从table中查询“字段1+1”的最大值，如果“字段1+1”的最大值为null，则值取1，否则取“字段1+1”的最大值。