MsSQL简单使用

发表于 更新于 分类于 ， 阅读次数： Waline： 阅读次数： 本文字数： 4.7k 阅读时长 ≈ 10 分钟
MsSQL简单用法

开始

查询所有数据库

1
2
3
sqlcmd
1> SELECT * FROM SysDatabases
2> go

使用数据库

1
2
3
1> USE master
go
已将数据库上下文更改为 "master"。

创建数据库 sqlTest，并使用

1
2
3
4
5
1> CREATE DATABasE sqlTest
2> go
1> USE sqlTest
2> go
已将数据库上下文更改为 "sqlTest"。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
## 判断当前数据库是否存在`user_table`表
if object_id(N'user_table',N'U') is not null
print '存在'
ELSE 
print '不存在'
go
 
CREATE TABLE user_table (
用户ID char(8) PRIMARY KEY,
姓名 varchar(8) NOT NULL,
性别 char(2)  CHECK(性别 = '男' OR 性别 = '女'),
成绩 numeric(4,1) CHECK(成绩 >= 0 AND 成绩 <= 100),
创建日期 DATETIME DEFAULT GETDATE(),
)

复制一个数据库中表到另一个数据库

1
2
3
4
IF NOT EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'user_table') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) 
BEGIN 
SELECT * INTO sqlTest.dbo.user_table FROM master.dbo.user_table
go

增加数据

追加字段

1
2
3
4
5
6
ALTER TABLE user_table ADD
cID INT IDENTITY (1,1),
telNumber varchar(11)
# 查询结果
SELECT TOP (10) *
  FROM [sqlTest].[dbo].[user_table]

插入数据

1
2
3
4
# 单行
 INSERT INTO user_table VALUES (100001,'张三','男',98)
# 多行
 INSERT INTO user_table VALUES (100002,'李四','男',89),(100003,'王五','男',58),(100004,'侯六','男',77),(100005,'小七','女',100)

删除数据

删除数据库中某一表

1
DROP TABLE user_table

删除某个字段

1
2
ALTER TABLE user_table
DROP COLUMN 字段名

修改数据

更新数据

1
UPDATE user_table SET telNumber = '13987654321' WHERE cID = 5

更新字段

1
EXEC SP_RENAME 'user_table.电话号码','联系方式'

修改字段属性

1
ALTER TABLE "dbo"."user_table" ALTER COLUMN "姓名" CHAR(6) NOT NULL

查询数据

查询当前数据库所有的数据表名：

1
SELECT * FROM SysObjects WHERE XType='U' ORDER BY Name

查询前1000条数据

1
SELECT TOP (1000) [用户ID],[姓名],[性别],[成绩] FROM [master].[dbo].[user_table]

稍复杂查询

1
2
SELECT * FROM user_table ORDER BY 1 ASC OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY
# ASC=>升序；DESC=>降序；OFFSET=>去掉0行元素；

查询数据库中的所有数据库名

1
SELECT Name FROM Master..SysDatabases ORDER BY Name

查询当前数据库中所有表名

1
2
3
4
5
SELECT Name FROM SysObjects WHERE XType='U' ORDER BY Name
# 或者
SELECT Name FROM Sys.Objects WHERE XType='U' ORDER BY Name
# 或者
SELECT name FROM Sys.tables
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
xtype char(2) 对象类型。可以是下列对象类型中的一种： 
C = CHECK 约束 
D = 默认值或 DEFAULT 约束 
F = FOREIGN KEY 约束 
L = 日志 
FN = 标量函数 
IF = 内嵌表函数 
P = 存储过程 
PK = PRIMARY KEY 约束（类型是 K） 
RF = 复制筛选存储过程 
S = 系统表 
TF = 表函数 
TR = 触发器 
U = 用户表 
UQ = UNIQUE 约束（类型是 K） 
V = 视图 
X = 扩展存储过程

查询表中的字段

查询字段名称

1
SELECT name FROM SysColumns WHERE id=object_id(N'表名')

查询所有表的结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT       
SO.name 表名,  
SC.name 表列名,  
SC.colid 索引,  
ST.name 类型  
FROM         
sysobjects   SO, -- 对象表  
syscolumns   SC, -- 列名表  
systypes ST  -- 数据类型表   
WHERE          
SO.id = SC.id   
  AND   SO.xtype = 'U'-- 类型U表示表，V表示视图  
  AND   SO.status >= 0  
  AND   SC.xtype = ST.xusertype  
ORDER BY    
SO.name, SC.colorder -- 按表名、列名排序

查询某张特定表的结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT       
  SO.name 表名,  
  SC.name 表列名,  
  SC.colid 索引,  
  ST.name 类型  
FROM         
  sysobjects   SO, -- 对象表  
  syscolumns   SC, -- 列名表  
  systypes   ST  -- 数据类型表   
WHERE          
  SO.id = SC.id   
   AND   SO.xtype = 'U'           -- 类型U表示表，V表示视图  
   AND   SO.status >= 0          -- status >= 0 为非系统对象  
   AND   SC.xtype = ST.xusertype  
   AND   SO.name = 'user_table'  -- 某张特定表  
ORDER BY    
  SO.name, SC.colorder     -- 按表名、列名排序

其他

MySql
查询表的所有字段名：

1
SELECT * FROM Information_schema.columns WHERE table_name='table_name' AND table_schema='table_schema' ORDER BY ORDINAL_POSITION

ACCESS
查看所有表名：
1
SELECT name FROM MSysObjects WHERE type=1 AND flags=0

MSysObjects是系统对象，默认情况是隐藏的。通过工具、选项、视图、显示、系统对象可以使之显示出来。
Oracle
1
2
SELECT cname FROM col WHERE tname='ZW_YINGYEZ'
SELECT column_name FROM user_tab_columns WHERE table_name='ZW_YINGYEZ'

查询表字段数
1
SELECT count(column_name) FROM user_tab_columns WHERE table_name='表名';

SQLServer中获取表结构

1
SELECT a.COLUMN_NAME,a.DATA_TYPE,a.CHARACTER_MAXIMUM_LENGTH,b.value FROM InformatiON_schema.COLUMNS AS a  LEFT JOIN sys.extended_properties AS b ON a.TABLE_NAME=OBJECT_NAME(b.major_id) AND a.ORDINAL_POSITION=b.minor_id WHERE a.TABLE_NAME='user_table'

SQLServer语句查询某表中所有字段的名称、类型、长度等属性信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT  
字段名=a.name,
标识=cAse WHEN COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 THEN '√' ELSE '' end,
主键=cAse WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype='PK' AND name IN (SELECT name FROM sysIndexes WHERE Indid IN(SELECT Indid FROM sysIndexkeys WHERE id = a.id AND colid=a.colid))) THEN '√' ELSE '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=cAse WHEN a.isnullable=1 THEN '√'ELSE '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype=b.xusertype 
INNER joIN sysobjects d ON a.id=d.id AND d.xtype='U' AND d.name<>'dtproperties' 
LEFT JOIN syscomments e ON a.cdefault=e.id 
LEFT JOIN sys.extended_properties g ON a.id=g.major_id AND a.colid=g.minor_id 
LEFT JOIN sys.extended_properties f ON d.id=f.major_id AND f.minor_id =0 
WHERE d.name='user_table'
ORDER BY a.name

参考资料
SQL复制数据库某一个表到另一个数据库中
SqlServer判断表是否存在
Sql中获取表结构（字段名称，类型，长度，说明）

相关文章