常用SQL

一、基础SQL语句

参考文章

创建数据库
create database db_name;
删除数据库
drop database db_name;
数据库备份
--- 创建备份数据的 device
USE master
exec sp_addumpdevice 'disk', 'testBack', 'd:\mysql7backup\MyNwind_1.dat'
--- 开始备份
BACKUP DATABASE pubs TO testBack
创建新表
create table tb_name(
column_name type [not null] primary key [auto_increment],
column_name2 type2......)
从旧表选字段建新表
create table tb_name like old_tb
或
create table tb_name as select col1,col2 from old_tb definition only
修改字段数据类型 modify
alter table tb_name modify col_name new_type [not null];
修改字段名称 change
alter table tb_name change old_name new_name;
删除字段
alter table tb_name drop col_name;
创建索引
create [unique] index index_name on tb_name(column(length));
综合常用语句
选择：select * from tb_name where 范围
插入：insert into tb_name(field1,field2) values(v1,v2)
删除：delete from tb_name where 范围
更新：update tb_name set field1=v1 where 范围
查找：select * from tb_name where field1 like ’v1%’ ---切勿左模糊
排序：select * from tb_name order by field1,field2 [asc]
总数：select count as totalcount from tb_name
求和：select sum(field1) as sumvalue from tb_name
平均：select avg(field1) as average from tb_name
最大：select max(field1) as maxvalue from tb_name
最小：select min(field1) as minvalue from tb_name
连接（连表）
left join 左连接
select a.a, b.b from a left join b on a.a=b.b;

right join 同理右连接或 full join

group by 分组
一张表分组完成后，查询后只能得到组相关的信息。
组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准)
select sum(col) from tb_name group by col2;
对数据库进行操作：
分离数据库： sp_detach_db;附加数据库：sp_attach_db 后接表明，附加需要完整的路径名

&bsp;

二、常见

top子句
SQL server的语法：
select top num | percent col_name from tb_name;
--
例子：从表students中选取前2行的数据；
select top 2 * from students;
--
从表中取前30%的学生姓名：
select top 30 percent name from students;
--
MySQL的语法：
select col_name from tb_name limit num offset num;
--
如：查看3行数据，偏移量5，意思从第5行开始看到第8行：
select * from students limit 3 offset 5
复制表-不带数据
select top 0 * into new from old;
拷贝表-带数据
insert into new(col1,col2,col3) select d,e,f from old;
子查询
select x,y from a where x in (select d from b);
between 范围之间
select * from tb_name where id between n1 and n2;
同理not between
mysql中可用大于> 或者小于< and 连接。
in 在里面
select * from tb_name where id in (v1, v2, v3);
同理 not in
注意not in在连表查询时如有null值，则出现无数据bug不好用，可使用not exist
说明：两张关联表，删除主表中已经在副表中没有的信息
delete from tb1 where not exists ( select * from tb2 where tb1.field1=tb2.field1 )
随机选择
select newid();
删除重复记录
delet from tb_name where id not in (select max(id) from tb_name group by (col1, col2...));
或者
select distinct * into temp from tb_name
delete from tb_name
insert into tb_name select * from temp
列出数据库里所有的表名
select name from sysobjects where type='U' // U代表用户
列出表里的所有的列名
select name from syscolumns where id=object_id('TableName')
压缩数据库
dbcc shrinkdatabase(dbname)
转移数据库给新用户以已存在用户权限
exec sp_change_users_login 'update_one','newname','oldname'
go
检查备份集
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
--
USE tablename -- 要操作的数据库名
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日志文件的大小(M)
--
Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
--
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
--
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
说明：更改某个表
exec sp_changeobjectowner 'tablename','dbo'