经典的SQL语句收藏

下面是Sql Server 和 Access 操作数据库结构的常用Sql，希望对你有所帮助。

新建表：
create table [表名]
(
[自动编号字段] int IDENTITY (1,1) PRIMARY KEY ,
[字段1] nVarChar(50) default \'默认值\' null ,
[字段2] ntext null ,
[字段3] datetime,
[字段4] money null ,
[字段5] int default 0,
[字段6] Decimal (12,4) default 0,
[字段7] image null ,
)

删除表：
Drop table [表名]

清空表内记录：
Truncate table [表名]

复制表结构：
select * into [新表名] from [旧表名] where 1<>1

插入数据：
INSERT INTO [表名] (字段1,字段2) VALUES (100,\'51WINDOWS.NET\')

删除数据：
DELETE FROM [表名] WHERE [字段名]>100
DELETE FROM [表名] WHERE id in (1,2,3)

更新数据：
UPDATE [表名] SET [字段1] = 200,[字段2] = \'51WINDOWS.NET\' WHERE [字段三] = \'HAIWA\'
UPDATE `cdb_name` SET `field_name` = replace (`field_name`,'from_str','to_str')

新增字段：
ALTER TABLE [表名] ADD [字段名] NVARCHAR (50) NULL
alter table ly_bbs_lyb（表名）?add wz（字段名） varchar(150)（字段类型及长度） null（允许为空）

删除字段：
ALTER TABLE [表名] DROP COLUMN [字段名]

修改字段：
ALTER TABLE [表名] ALTER COLUMN [字段名] NVARCHAR (50) NULL

重命名表：(Access 重命名表，请参考文章：在Access数据库中重命名表)
sp_rename \'表名\', \'新表名\', \'OBJECT\'

新建约束：
ALTER TABLE [表名] ADD CONSTRAINT 约束名 CHECK ([约束字段] <= \'2000-1-1\')

删除约束：
ALTER TABLE [表名] DROP CONSTRAINT 约束名

新建默认值
ALTER TABLE [表名] ADD CONSTRAINT 默认值名 DEFAULT \'51WINDOWS.NET\' FOR [字段名]

删除默认值
ALTER TABLE [表名] DROP CONSTRAINT 默认值名

单表查询(全部字段):
select * form [表名]
单表查询(部分字段):
select 字段1,字段2 form [表名]
带条件查询:
select * form [表名] where [字段1]='AAA' and [字段2]=0
排序(降序,从大到小--Desc 升序,从小到大--Asc):
select * form [表名] Order By [字段1] Desc

多表查询：
select table1.*,table2 form 表1，表2，表3 where 条件

删除Sql Server 中的日志，减小数据库文件大小
dump transaction 数据库名 with no_log
backup log 数据库名 with no_log
dbcc shrinkdatabase(数据库名)
exec sp_dboption \'数据库名\', \'autoshrink\', \'true\'

\\\'添加字段通用函数
Sub AddColumn(TableName,ColumnName,ColumnType)
Conn.Execute(\"Alter Table \"&TableName&\" Add \"&ColumnName&\" \"&ColumnType&\"\")
End Sub

\\\'更改字段通用函数
Sub ModColumn(TableName,ColumnName,ColumnType)
Conn.Execute(\"Alter Table \"&TableName&\" Alter Column \"&ColumnName&\" \"&ColumnType&\"\")
End Sub

\\\'检查表是否存在

sql=\"select count(*) as dida from sysobjects where id = object_id(N\'[所有者].[表名]\') and OBJECTPROPERTY(id, N\'IsUserTable\') = 1\"

set rs=conn.execute(sql)

response.write rs(\"dida\")\'返回一个数值，0代表没有，1代表存在

判断表的存在:
select * from sysobjects where id = object_id(N\'[dbo].[tablename]\') and OBJECTPROPERTY(id, N\'IsUserTable\') = 1

某个表的结构
select * from syscolumns where id = object_id(N\'[dbo].[你的表名]\') and OBJECTPROPERTY(id, N\'IsUserTable\') = 1

create table student(
Sno int not null primary key,
Sname char(10)not null,
Ssex bit not null,
Sage tinyint not null,
Sdept char(20) not null)

create table course(
Cno int not null primary key,
Cname char(20)not null,
Cpno int not null,
Ccredit tinyint not null)

create table sc(
Sno int not null,
Cno int not null,
Grade tinyint not null
foreign key(Sno)references student(Sno)
foreign key(Cno)references course(Cno)
)

 

(1)
seleCt top 1 S.sno,sname
from SC,S
where Cno='C2' and SC.sno=S.sno
order by grade desC;

(2)
seleCt sname,age
from Student,SC
where SC.sno not in(
seleCt SC.sno
from SC
where Cno='C2' )and SC.sno=S.sno;
(3)
seleCt sno, avg(grade) as average
from SC
group by sno
having(avg(grade)>80);
(3)法二
seleCt sno, avg(grade) ' average'
from SC
group by sno
having(avg(grade)>80);

(4)
delete from SC 
where SC.sno in(
seleCt sno
from S
where sname='S5');
(5)
seleCt sname
from S
where sdept='英语'and sex='男';
(6)
seleCt SC.sno,avg(grade) as average
from S,SC
where S.sno=SC.sno
group by SC.sno;

(7)
seleCt S.sname as 姓名 ,grade as 成绩 ,C.cname as 选修课程
from SC,S,C
where S.sno=SC.sno and SC.cno=C.cno and SC.cno in(
seleCt cno
from C
where cname='DB');
(8)
select TOP 1 sno as 学号,grade as 分数,cname as 课程名
from SC,C
where SC.cno=C.cno and cname='OS'
order by grade desc;
(9)
select Sname
from?S
where not exists(
select *
from SC
where Sno=S.Sno and Cno=1);
（10）
select Sname
from S
where not exists(
select *
from C
where not exists(
select *
from SC
where Sno=S.Sno and Cno=C.Cno));

(11)
select distinct Sno
from SC,SCX
where not exists(
select * 
from SC SCY
where SCY.Sno=95001 and
not exists(
select *
from SC SCZ
where SCZ.Sno=SCX.Sno and SCZ.Cno=SCY.Cno));
(12)
select top 3 Cno as 课程号, Sno
from SC
where Cno=1
order by Grade desc;

create database stu

use stu

create table S 
( 
sno char(6), 
sname char(10), 
age int, 
sex char(2), 
constraint PK_S primary key (sno), 
constraint CK_age check(age>=0 and age<=150) 
)

create table C 
( 
cno char(8), 
cname char(16), 
credit int, 
constraint PK_C primary key (cno), 
constraint CK_credit check (credit>=0) 
)

create table SC 
( 
sno char(6), 
cno char (8), 
constraint PK_SC primary key (sno,cno), 
constraint FK_s foreign key (sno) references S(sno), 
constraint FK_c foreign key (cno) references C(cno) 
)

insert into S values ('001','zhang',19,'男')

insert into S values('002','li',16,'女')

select * from S
随机排序===========================================
1、mssql
SELECT TOP 10 *
FROM Table
where 单价>50 and 单价<80
ORDER BY NEWID()

2、access
SELECT TOP 10 *
FROM Table
where 单价>50 and 单价<80
ORDER BY Rnd(Id)
Rnd(ID) 其中的ID是自动编号字段，可以利用其他任何数值来完成，比如用姓名字段(UserName)
SELECT TOP 10 *
FROM Table
where 单价>50 and 单价<80
ORDER BY Rnd(Len(UserName))

3、mysql
SELECT *
FROM Table
where 单价>50 and 单价<80
Order By Rand() Limit 10

时间比较=======================
如果 sql
select * from 进货管理_进货单 where 录单日期 between '2009-01-01' and '2009-04-01'

如果 access
select * from 进货管理_进货单 where 录单日期 between #2009-01-01# and #2009-04-01#
导入TXT文件===================
SQL ＝ “SELECT * INTO?临时表 FROM [Text;HDR=NO;DATABASE=” ＋ 取目录名 (TXT文件名) ＋ “].[” ＋ 取文件名 (TXT文件名) ＋ “]”
这个SQL语句是不用第一行字段当字段名，字段名为F1、F2..

SQL ＝ “SELECT * INTO?tmp_1 FROM [Text;DATABASE=” ＋ 取目录名 (TXT文件名) ＋ “].[” ＋ 取文件名 (TXT文件名) ＋ “]” 
这个SQL语句是用第一行文本当字段名的

------------------------------------
字段类型：
2 : "SmallInt",?// 整型
3 : "Int",?// 长整型
4 : "Real",?// 单精度型
5 : "Float",?// 双精度型
6 : "Money",?// 货币
7 : "DateTime",?// 日期时间
11 : "Bit",?// 是否
13 : "TimeStamp",
17 : "TinyInt",?// 字节
72 : "UniqueIdentifier",?// 同步复制 ID
128 : "Binary",
129 : "Char",
130 : "NChar",
131 : "Decimal",?// 小数
133 : "DateTime",
135 : "SmallDateTime",
200 : "VarChar",
201 : "Text",
202 : "VarChar",?// 文本
203 : "Text",?// 备注
204 : "Binary",?// 二进制
205 : "Image"?// OLE 对象

---------------------------------
连接TXT
.版本 2

连接文本 ＝ “Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=” ＋ 取运行目录 () ＋ “\” ＋ “;Extensions=asc,csv,tab,txt;Persist Security Info=False”
查询SQL ＝ “Select * From SsqList.txt”