1、说明:创建数据库
CREATE
DATABASE
database
-
name
2、说明:删除数据库
drop
database
dbname
3、说明:备份sql server
--- 创建 备份数据的 device
USE master
EXEC
sp_addumpdevice
'disk'
,
'testBack'
,
'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP
DATABASE
pubs
TO
testBack
4、说明:创建新表
create
table
`
tabname`(`col1` type1 [
not
null
] [
primary
key
],`col2` type2 [
not
null
],..)
根据已有的表创建新表:
A:
create
table
tab_new
like
tab_old (使用旧表创建新表)
B:
create
table
tab_new
as
select
col1,col2…
from
tab_old definition
only
5、说明:删除新表
drop
table
tabname
6、说明:显示所有表
show
tables
7、说明:查询表结构
describe
pet;
8、说明:删除新表
drop
table
tabname
Alter
table
tabname
add
column
col type
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加
varchar
类型的长度。
10、说明:添加主键:
Alter
table
tabname
add
primary
key
(col)
说明:删除主键:
Alter
table
tabname
drop
primary
key
(col)
11、说明:创建索引:
create
[
unique
]
index
idxname
on
tabname(col….)
删除索引:
drop
index
idxname
注:索引是不可更改的,想更改必须删除重新建。
12、说明:创建视图:
create
view
viewname
as
select
statement
删除视图:
drop
view
viewname
13、说明:创建存储过程
-- 创建一个存储过程(传出参数和传入参数)
use dates;
CREATE PROCEDURE proc2(OUT s int,namess VARCHAR(20))
BEGIN
SELECT COUNT(*) INTO s FROM pet WHERE name=namess;
END
-- 使用
SET @p_in=0;
SET @name='Fluffy';
CALL proc2(@p_in,@name);
SELECT @p_in,@name;
删除存储过程:
drop
procedure
sp_name
显示存储过程:
show procdure stauts like '名称';
14、流程控制构造:
IF语句:IF 条件1 THEN 执行1 ELSEIF 条件2 THEN 执行2 ELSE 执行3 END IF;
IF(expr1,expr2,expr3:):如果expr为true,则返回expr2,否则返回expr3.
SELECT IF(1>2,2,3);SELECT IF(1<2,'yes ','no');SELECT IF(STRCMP('test','test1'),'no','yes');
得到3 得到'yes' 得到 'no'
CASE语句:CASE 比较元素 WHEN 条件1 THEN 执行1 WHEN 条件2 THEN 执行2 ELSE 执行3 END CASE;
SELECT CASE 1 WHEN 1 THEN 'one'SELECT CASE BINARY 'B'
WHEN 2 THEN 'two' ELSE 'more' END; WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
得到 'one' 得到 NULL
LOOP语句:LOOP 执行代码 END LOOP;---代码重复执行,直到跳出为止
WHILE语句:WHILE 条件 DO 执行代码 END WHILE;
---代码重复执行,直到条件成立
15、说明:触发器---对于具有相同触发程序动作时间和事件的给定表,不能有两个触发程序;但可有不同动作触发程序。
CREATE TRIGGER语法:
CREATE TRIGGER
trig_name trig_time trig_event
ON
tbl_name
FOR EACH ROW
trig_stmt
trig_name为触发器名称,trig_time为触发时间(BEFORE或AFTER),trig_event为语句类型(INSERT、UPDATE、DELETE),tbl_name为表名,trig_stmt为触发代码
DROP TRIGGER语法:
DROP TRIGGER 触发器名称
16
、说明:几个简单的基本的sql语句
选择:
select
*
from
table1
where
范围
插入:
insert
into
table1(field1,field2)
values
(value1,value2)
从另表插入:
insert
into
表1(列1)
select
列2from
表2where
范围
EXISTS:
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);---如果一个子查询返回任何的行,则EXISTSsubquery为FALSE
删除:
delete
from
table1
where
范围
update
table1
set
field1=value1
where
范围
查找:
select
*
from
table1
where
field1
like
’%value1%’
---like的语法很精妙,查资料!
排序:
select
*
from
table1
order
by
field1,field2 [asc或
desc
]
总数:
select
count
as
totalcount
from
table1
求和:
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
唯一:select distinct filesfrom
table1
ADTIME(expr,expr2):
SELECT ADDTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');
--两个时间相加,expr为时间货日期表达式,expr2为时间达式
位组函数:selectyear,month,BIT_COUNT(BIT_OR(1<<day)) ASdays from
table1 GROUP BY year,month ---根据天计算访问量,排除重复数据!
DATE(expr):
SELECT DATE('2003-12-31 01:02:03');
--提取日期或时间日期表达式expr中的日期部分
DAYNAME(date):SELECT DAYNAME('1998-02-05');--返回date对应工作日名称
DAYOFMONTH(date):
SELECT DAYOFMONTH('1998-02-05');--返回date对应该月日期,范围是1到31
DAYOFWEEK(date):SELECT DAYOFWEEK('1998-02-05');---返回对应date第几周,1为周末
FROM_DAYS(N):SELECT FROM_DAYS(729669);---给定一个天数 N, 返回一个DATE值。
TO_DAYS(date):SELECT TO_DAYS('1997-10-07');---给定一个日期date,可简化,如950424, 返回一个天数 (从年份0开始的天数 )
MD5(str):
SELECT MD5('sdads');---对文本进行MD5加密
HOUR(time):
SELECTHOUR('10:05:03');---返回time 对应的小时数。对于日时值的返回值范围是从 0 到 23 。
LAST_DAY(date):SELECT LAST_DAY
('2003-02-05');
---获取一个日期或日期时间值,返回该月最后一天对应的值。若参数无效,则返回NULL。
MINUTE(time):SELECTMINUTE('1998-02-05 10:05:03');---返回 time 对应的分钟数,范围是从 0 到 59。
MONTH(date):SELECTMONTH('1998-02-03');---
返回
date
对应的月份,范围时从 1 到 12。
NOW():SELECTNOW();---返回当前日期和时间值
QUARTER(date):SELECTQUARTER('1998-02-03');---返回date 对应的一年中的季度值,范围是从 1到 4。
SECOND(time):SELECTSECOND('10:05:03');---返回time 对应的秒数, 范围是从 0到59
SUBTIME(expr,expr2):SELECT SUBTIME('1997-12-31 23:59:59','1 1:1:1');---吧两个时间相减
DATE_FORMAT(date,格式):%Y(年份|4位)、%y(年份|2位)、%M(月份|January-December)、%m(月份|00-12)、%W(周|周日-周六)、%w(周|0-6)、
%p(上下午|上午AM、下午PM)
%D(
日期|0th.1st.2nd.)、%d(
日期|00-31)、%H(
小时|0-23)、%h(
小时|01-12)、%i(
分钟|00-59)、%S(
秒|00-59);
示例:SELECT DATE_FORMAT('1995-04-24 12:21:00','%H:%i:%S') 查询得到'12:21:00'
SELECT DATE_FORMAT('1995-04-24 12:21:00','%W:%M:%Y') 查询得到'Saturday October 1995'
17、说明:字符串函数
LENGTH(str):返回值为字符串str 的长度,单位为字节
如:SELECT LENGTH('text')-->4
LOWER(str):返回字符串str的小写字母格式
如:SELECT LOWER(''QUADRATICALLY'')-->'quadratically'
REPLCAE(str,from_str,to_str):返回字符串str 以及所有被字符串to_str替代的字符串from_str
如:SELECT REPLACE('www.mysql.com', 'w', 'Ww');-->''WwWwWw.mysql.com''
SUBSTRING(str,length):从字符串str返回一个子字符串,起始于位置length
如:SELECT SUBSTRING('Quadratically',5);-->'ratically'
TRIM(str):返回字符串str,吧空格去掉
如:SELECT TRIM(' bar ');
->'bar'
BINARY a1=a2:两个字符串进行比较,返回0或1,也比较大小写,有空格也会比较
如:SELECT BINARY 'a' = 'A';
-》0SELECT BINARY 'a' = 'a ';--》0
A:
UNION
运算符
UNION
运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当
ALL
随
UNION
一起使用时(即
UNION
ALL
),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B:
EXCEPT
运算符
EXCEPT
运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当
ALL
随
EXCEPT
一起使用时 (
EXCEPT
ALL
),不消除重复行。
C:
INTERSECT
运算符
INTERSECT
运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当
ALL
随
INTERSECT
一起使用时 (
INTERSECT
ALL
),不消除重复行。
注:使用运算词的几个查询结果行必须是一致的。
19、说明:使用外连接
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
B:
right
(
outer
)
join
:
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。
C:
full
/
cross
(
outer
)
join
:
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
20、分组:
Group
by
:
一张表,一旦分组完成后,查询后只能得到组相关的信息。
组相关的信息:(统计信息)
count
,
sum
,
max
,
min
,
avg
分组的标准)
在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据
在selecte统计函数中的字段,不能和普通的字段放在一起;
21、对数据库进行操作:
分离数据库: sp_detach_db; 附加数据库:sp_attach_db 后接表明,附加需要完整的路径名
22.如何修改数据库的名称:
sp_renamedb
'old_name'
,
'new_name'
二、提升
1、
说明:子查询(表名1:a 表名2:b)
select
a,b,c
from
a
where
a
IN
(
select
d
from
b ) 或者:
select
a,b,c
from
a
where
a
IN
(1,2,3)
2、说明:外连接查询(表名1:a 表名2:b)
select
a.a, a.b, a.c, b.c, b.d, b.f
from
a
LEFT
OUT
JOIN
b
ON
a.a = b.c
3、说明:
between
的用法,
between
限制查询数据范围时包括了边界值,
not
between
不包括
select
*
from
table1
where
time
between
time1
and
time2
select
a,b,c
from
table1
where
a
not
between
数值1
and
数值2
4、说明:
in
的使用方法
select
*
from
table1
where
a [
not
]
in
(‘值1’,’值2’,’值4’,’值6’)
5、说明:一条sql 语句搞定数据库分页
select
top
10 b.*
from
(
select
top
20 主键字段,排序字段
from
表名
order
by
排序字段
desc
) a,表名 b
where
b.主键字段 = a.主键字段
order
by
a.排序字段
6、说明:随机取出10条数据
select
top
10 *
from
tablename
order
by
newid()
7、说明:随机选择记录
select
newid()
8、说明:删除重复记录
9、
delete
from
tablename
where
id
not
in
(
select
max
(id)
from
tablename
group
by
col1,col2,...)
10、说明:列出数据库里所有的表名
select
name
from
sysobjects
where
type=
'U'
// U代表用户
11、说明:列出表里的所有的列名
select
name
from
syscolumns
where
id=object_id(
'TableName'
)
12、说明:列示type、vender、pcs字段,以type字段排列,
case
可以方便地实现多重选择,类似
select
中的
case
。
select
type,
sum
(
case
vender
when
'A'
then
pcs
else
0
end
),
sum
(
case
vender
when
'C'
then
pcs
else
0
end
),
sum
(
case
vender
when
'B'
then
pcs
else
0
end
)
FROM
tablename
group
by
type
显示结果:
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
13、说明:初始化表table1
TRUNCATE
TABLE
table1
14、说明:选择从10到15的记录(asc是从后往前排,倒过来,所以先选前15个,倒过来取5个)
select
top
5 *
from
(
select
top
15 *
from
table
order
by
id
asc
) table_别名
order
by
id
desc
例如1:一张表有一万多条记录,表的第一个字段 RecID 是自增长字段, 写一个SQL语句,找出表的第31到第40个记录。
select
top
10 recid
from
A
where
recid
not
in
(
select
top
30 recid
from
A)
15:获取当前数据库中的所有用户表
select
Name
from
sysobjects
where
xtype=
'u'
and
status>=0
16:获取某一个表的所有字段
select
name
from
syscolumns
where
id=object_id(
'表名'
)
两种方式的效果相同
17:查看与某一个表相关的视图、存储过程、函数
select
a.*
from
sysobjects a, syscomments b
where
a.id = b.id
and
b.text
like
'%表名%'
18:查看当前数据库中所有存储过程
select
name
as
存储过程名称
from
sysobjects
where
xtype=
'P'