【SQL学习】常用命令

数据库操作

创建

create database mydb 
on primary
 (
name='mydb_name.mdf',
filename='E:\mydb_filename.mdf'
 )
1
2
3
4
5
6

删除

DROP DATABASE DatabaseName
1

测试是否存在

//here '1' represent to the first property of database, actually one if you use '*' to replace '1'
//you will get many information of this database
select 1 from master.dbo.sysdatabases where [name]='DatabaseName'
select count(*) from master.dbo.sysdatabases where [name]='DatabaseName'
1
2
3
4

查询所有数据库名字

SELECT Name FROM Master..SysDatabases
1

查询所有字段名

SELECT Name FROM SysColumns WHERE id=Object_Id('TableName') 
1

表格

查询所有表格名

XType='S':表示所有系统表; 
XType='U':表示所有用户表; 
SELECT Name FROM DatabaseName..SysObjects Where XType='U'
1
2
3

插入一个记录

insert into mytb values('xxx','string')
1