sqlserver常用命令行操作(啟動(dòng)、停止、暫停)

啟動(dòng)sql

成都創(chuàng)新互聯(lián)公司作為成都網(wǎng)站建設(shè)公司，專(zhuān)注網(wǎng)站建設(shè)公司、網(wǎng)站設(shè)計(jì)，有關(guān)企業(yè)網(wǎng)站設(shè)計(jì)方案、改版、費(fèi)用等問(wèn)題，行業(yè)涉及白烏魚(yú)等多個(gè)領(lǐng)域，已為上千家企業(yè)服務(wù)，得到了客戶的尊重與認(rèn)可。

server

Net

Start

MSSqlServer

暫停sql

server

Net

Pause

MSSqlServer

重新啟動(dòng)暫停的sql

server

Net

Continue

MSSqlServer

停止sql

server

Net

stop

MSSqlServer

命令行方式修改sql

server

sa

的密碼

一直都是使用企業(yè)管理器操作sql的，昨天幫一朋友部署網(wǎng)站，租的國(guó)外vps主機(jī)，登陸上去只看到sql的圖標(biāo)正常運(yùn)行的，企業(yè)管理器和查詢分析器的影都沒(méi)看到，汗一個(gè)。。。空間提供商也不給技術(shù)支持，暈了，只有自己想辦法了。

今天網(wǎng)上google一下，終于搞定。

在cmd

窗口下

復(fù)制代碼

代碼如下:

C:\Documents

and

Settings\Administratorosql

-E

1

sp_password

null,'abc123','sa'

2

go

Password

changed.

1

exit

大功告成，sa的密碼修改成了abc123

關(guān)鍵是osql這個(gè)東東，具體查看

SQL

Server

命令行工具

isql

和

osql

常用命令

命令行操作有時(shí)比在圖形界面下用鼠標(biāo)還高效，所以高手常用命令行操作，下面簡(jiǎn)介SQL

Server

命令行工具

isql

和

osql。

isql

實(shí)用工具使您得以輸入

Transact-SQL

語(yǔ)句、系統(tǒng)過(guò)程和腳本文件；并且使用

DB-Library

與

SQL

Server

2000

進(jìn)行通訊。

osql

實(shí)用工具使您得以輸入

Transact-SQL

語(yǔ)句、系統(tǒng)過(guò)程和腳本文件。該實(shí)用工具通過(guò)

ODBC

與服務(wù)器通訊。

◆

信任連接：

isql

-E

或

osql

-E

◆

察看所有數(shù)據(jù)庫(kù)：

use

master

exec

sp_helpdb

GO

◆

察看數(shù)據(jù)庫(kù)

pubs：

use

master

exec

sp_helpdb

pubs

GO

◆

察看數(shù)據(jù)庫(kù)

pubs

中的對(duì)象：

USE

pubs

EXEC

sp_help

GO

相當(dāng)于

Oracle

的

SELECT

table_name

FROM

user_objects;

◆

察看數(shù)據(jù)庫(kù)

pubs

中的表

employee

結(jié)構(gòu)：

USE

pubs

EXEC

sp_help

employee

GO

相當(dāng)于

Oracle

的

SQL*PLUS

中的

DESC

employees

◆

SELECT

語(yǔ)句：

USE

pubs

SELECT

*

FROM

employee

GO

◆

當(dāng)使用單引號(hào)分隔一個(gè)包括嵌入單引號(hào)的字符常量時(shí)，用兩個(gè)單引號(hào)表示嵌入單引號(hào)，例如：

SELECT

'O''Leary'

GO

◆

用7.個(gè)雙引號(hào)表示嵌入雙引號(hào)，例如：

SELECT

'O"Leary'

GO

◆

SQL

Server

數(shù)據(jù)庫(kù)信息查詢

use

master

exec

sp_helpdb

pubs

GO

或：

use

master

SELECT

name,

dbid

FROM

sysdatabases

GO

◆

查數(shù)據(jù)庫(kù)對(duì)象

(相當(dāng)于

Oracle

的

SELECT

*

FROM

user_tables;)

USE

pubs

EXEC

sp_help

GO

或

use

master

SELECT

name,

id

FROM

pubs.dbo.sysobjects

WHERE

type='U'

GO

◆

查字段

(相當(dāng)于

Oracle

的

SQL*PLUS

中的

DESC

employees

)

USE

pubs

EXEC

sp_help

employee

GO

◆

查看指定

USE

pubs

SELECT

name,

id,

xtype,

length

FROM

syscolumns

WHERE

id=277576027

GO

USE

pubs

SELECT

*

FROM

syscolumns

WHERE

id=277576027

GO

◆

查看數(shù)據(jù)類(lèi)型名字的定義：

SELECT

name,

xtype

FROM

systypes

GO

◆

從命令行啟動(dòng)“查詢分析器”

isqlw

◆

isql命令

描述

GO

執(zhí)行最后一個(gè)

GO

命令之后輸入的所有語(yǔ)句。

RESET

清除已輸入的所有語(yǔ)句。

ED

調(diào)用編輯器。

!!

command

執(zhí)行操作系統(tǒng)命令。

QUIT

或

EXIT(

)

退出

isql。

CTRL+C

不退出

isql

而結(jié)束查詢。

僅當(dāng)命令終止符

GO（默認(rèn)）、RESET、ED、!!、EXIT、QUIT

和

CTRL+C

出現(xiàn)在一行的開(kāi)始（緊跟

isql

提示符）時(shí)才可以被識(shí)別。isql

忽視同一行中這些關(guān)鍵字后輸入的任何內(nèi)容。

sqlserver 2005表設(shè)計(jì)上出現(xiàn)了問(wèn)題，求教。

你對(duì)應(yīng)了幾次員工表？一次吧

有兩個(gè)員工編號(hào)，應(yīng)該對(duì)應(yīng)兩次

如何用cmd命令行操作sqlserver

運(yùn)行 osql -S 【數(shù)據(jù)庫(kù)服務(wù)器】 -U 【登陸用戶名】 -P 【登陸密碼】

出現(xiàn) 1 表示連接成功，這時(shí)候你可以輸入sql語(yǔ)句來(lái)進(jìn)行操作了。

以CMD命令行來(lái)操作Sqlserver，DotNetGeek 覺(jué)得沒(méi)有多大的用處，而且，select出來(lái)的數(shù)據(jù)排版顯示不整齊美觀，

如果你在一臺(tái)沒(méi)有Sqlserver的電腦恰巧想去操作一下公司的Sqlserver，可以使用這個(gè)方法。

JSPWeb開(kāi)發(fā)中一個(gè)關(guān)于數(shù)據(jù)庫(kù)操作類(lèi)該這么寫(xiě)，類(lèi)中要帶有查詢，增刪改，以及關(guān)閉數(shù)據(jù)庫(kù)操作方法，請(qǐng)高手指

package com.oa.dao.impl; import java.sql.Connection; /SPAN/liimport java.sql.PreparedStatement; /SPAN/liimport java.sql.ResultSet; /SPAN/liimport java.sql.SQLException; /SPAN/liimport java.sql.Statement; /SPAN/liimport java.util.List; /SPAN/liimport java.util.Map; /SPAN/li import org.apache.commons.logging.Log; /SPAN/liimport org.apache.commons.logging.LogFactory; /SPAN/li import com.oa.core.DynaForm; /SPAN/liimport com.oa.dao.Dao; /SPAN/liimport com.oa.jdbc.ConnectionFactory; /SPAN/liimport com.oa.jdbc.DaoAccessException; /SPAN/liimport com.oa.jdbc.DybaBean; /SPAN/liimport com.oa.jdbc.JdbcPage; /SPAN/liimport com.oa.jdbc.Page; /SPAN/liimport com.oa.jdbc.RowsDynaClass; /SPAN/li /** /SPAN/li * 基類(lèi)DAO，實(shí)現(xiàn)一些基本共有方法 * * @author Gao_wx Mar 26, 2008 */ public class BaseDao implements Dao { /SPAN/li protected Log logger = LogFactory.getLog(BaseDao.class); /SPAN/li // 獲取數(shù)據(jù)庫(kù)鏈接 /SPAN/li protected Connection getConnection() { /SPAN/li return ConnectionFactory.getConnection(); /SPAN/li } // 在此鏈接上，根據(jù)sql語(yǔ)句獲取List /SPAN/li protected ListDybaBean getList(Connection conn, String sql) /SPAN/li throws DaoAccessException { /SPAN/li try { /SPAN/li Statement st = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = st.executeQuery(sql); return getList(rs); /SPAN/li } catch (SQLException e) { /SPAN/li throw new DaoAccessException(sql, e); /SPAN/li } } // 根據(jù)Result獲取List /SPAN/li protected ListDybaBean getList(ResultSet rs) throws SQLException { /SPAN/li RowsDynaClass rsdc = new RowsDynaClass(rs); /SPAN/li return rsdc.getRows(); /SPAN/li } // 根據(jù)sql語(yǔ)句獲取List /SPAN/li protected ListDybaBean getList(String sql) throws DaoAccessException { /SPAN/li Connection conn = getConnection(); return getList(conn, sql); /SPAN/li } // 在此連接上，根據(jù)sql語(yǔ)句，id加載一條記錄 /SPAN/li protected DybaBean loadById(Connection conn, String sql, int id) /SPAN/li throws DaoAccessException { /SPAN/li try { /SPAN/li PreparedStatement pst = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); pst.setInt(1, id); /SPAN/li ResultSet rs = pst.executeQuery(); List list = getList(rs); if (list.size() 1) { /SPAN/li logger.warn("加載了2條以上的記錄，請(qǐng)檢查sql：" + sql); /SPAN/li } if (list.size() == 0) { /SPAN/li logger.warn("數(shù)據(jù)庫(kù)沒(méi)有此記錄！"); /SPAN/li return null; /SPAN/li } else { /SPAN/li return (DybaBean) list.get(0); /SPAN/li } } catch (SQLException e) { /SPAN/li throw new DaoAccessException(sql, e); /SPAN/li } } // 根據(jù)sql語(yǔ)句，id，加載一條記錄 /SPAN/li protected DybaBean loadById(String sql, int id) throws DaoAccessException { /SPAN/li Connection conn = getConnection(); return loadById(conn, sql, id); /SPAN/li } // 在此連接上，根據(jù)Sql和id刪除一條記錄，返回影響行數(shù) /SPAN/li protected int deleteById(Connection conn, String sql, int id) /SPAN/li throws SQLException { /SPAN/li PreparedStatement pst = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); pst.setInt(1, id); /SPAN/li int del = pst.executeUpdate(); /SPAN/li return del; /SPAN/li } // 根據(jù)sql語(yǔ)句，id刪除一條記錄 /SPAN/li protected int deleteById(String sql, int id) throws DaoAccessException { /SPAN/li Connection conn = getConnection(); try { /SPAN/li int del = deleteById(conn, sql, id); /SPAN/li return del; /SPAN/li } catch (SQLException e) { /SPAN/li throw new DaoAccessException(sql, e); /SPAN/li } } protected Page getPage(DynaForm form, String sql) throws DaoAccessException { /SPAN/li Connection conn = getConnection(); Page page = getPage(conn, form, sql); return page; /SPAN/li } // 獲取分頁(yè)對(duì)象 /SPAN/li protected Page getPage(Connection conn, DynaForm form, String sql) /SPAN/li throws DaoAccessException { /SPAN/li int p = form.getInt("p"); /SPAN/li if (p 1) { /SPAN/li p = 1; /SPAN/li } int size = form.getInt("size"); /SPAN/li if (size 10) { /SPAN/li size = 10; /SPAN/li } Page page; try { /SPAN/li page = new JdbcPage(conn, sql, p, size); /SPAN/li } catch (SQLException e) { /SPAN/li throw new DaoAccessException(sql, e); /SPAN/li } return page; /SPAN/li } }

在SQL server2000數(shù)據(jù)庫(kù)中,連接操作包含哪幾個(gè)類(lèi)別,試分別加以說(shuō)明?

如果只是連接SQL

Server操作，一般就是使用

SQLConnection對(duì)象，如果要連接ACCESS或Excel

就以用到OLEDBConnection對(duì)象。如果是要對(duì)SQL

Server數(shù)據(jù)庫(kù)增、刪、改、查的操作，就還要用到:

–

Command對(duì)象，用于執(zhí)行SQL命令

–

AdapterData對(duì)象(數(shù)據(jù)適配器)

，是連接數(shù)據(jù)庫(kù)與數(shù)據(jù)集的數(shù)據(jù)橋梁

–

DataSet

對(duì)象(數(shù)據(jù)集)，存放數(shù)據(jù)的內(nèi)存區(qū)域

–

DataReader

對(duì)象(數(shù)據(jù)閱讀器)

，用于執(zhí)行Command命令后高速讀取數(shù)據(jù)

誰(shuí)可以提供vb寫(xiě)的數(shù)據(jù)庫(kù)操作類(lèi)，sqlserver的，包括查詢，增刪改等操作，越詳細(xì)越要，萬(wàn)分感謝

下面是多數(shù)據(jù)庫(kù)的基本操作類(lèi)，供你參考。

Imports?System??

Imports?System.Data.SqlClient??

Imports?System.Configuration??

Imports?System.Collections.Generic??

Imports?System.Text??

Imports?System.Data??

Imports?System.Configuration.ConfigurationSettings??

Imports?System.Data.OleDb??

Public?Class?DB??

Private?Shared?com?As?OleDb.OleDbCommand??

Private?Shared?reader?As?OleDb.OleDbDataReader??

Private?Shared?adapter?As?OleDb.OleDbDataAdapter??

Private?Shared?conn?As?OleDb.OleDbConnection??

'''?summary??

'''?獲取數(shù)據(jù)庫(kù)連接??

'''?/summary??

'''?value/value??

'''?returns/returns??

'''?remarks/remarks??

Public?Shared?ReadOnly?Property?NewConn()?As?OleDbConnection??

Get??

Dim?connectionString?As?String??

'connectionString?=?System.Configuration.ConfigurationSettings.GetConfig("Supermarket")??

'連接2010數(shù)據(jù)庫(kù)??

'connectionString?=?"Provider=Microsoft.ACE.OLEDB.12.0;Data?Source=F:\GCU.accdb"??

'連接03-07數(shù)據(jù)庫(kù)??

connectionString?=?"Provider=Microsoft.Jet.OLEDB.4.0;Data?Source=F:\JL\GCU.MDB"??

'應(yīng)該在這里先判斷conn是否為Nothing??

If?conn?Is?Nothing?Then??

conn?=?New?OleDb.OleDbConnection(connectionString)??

End?If??

If?conn.State??ConnectionState.Open?Then??

conn.Open()??

End?If??

Return?conn??

End?Get??

End?Property??

'''?summary??

'''?執(zhí)行增刪改(無(wú)參)??

'''?/summary??

'''?param?name="sql"執(zhí)行的Sql語(yǔ)句/param??

'''?returns/returns??

'''?remarks/remarks??

Public?Shared?Function?ExecuteNonQuery(ByVal?sql?As?String)?As?Integer??

com?=?New?OleDb.OleDbCommand(sql,?NewConn())??

Return?com.ExecuteNonQuery()??

End?Function??

'''?summary??

'''?執(zhí)行增刪改(有參)??

'''?/summary??

'''?param?name="sql"/param??

'''?param?name="para"/param??

'''?returns/returns??

'''?remarks/remarks??

Public?Shared?Function?ExecuteNonQuery(ByVal?sql?As?String,?ByVal?para?As?OleDbParameter())?As?Integer??

com?=?New?OleDb.OleDbCommand(sql,?NewConn)??

com.Parameters.AddRange(para)??

Return?com.ExecuteNonQuery()??

End?Function??

'''?summary??

'''?執(zhí)行增刪改的存儲(chǔ)過(guò)程??

'''?/summary??

'''?param?name="para"/param??

'''?param?name="ProcedureName"/param??

'''?returns/returns??

'''?remarks/remarks??

Public?Shared?Function?ExecuteNonQuery(ByVal?para?As?SqlParameter(),?ByVal?ProcedureName?As?String)?As?Integer??

Dim?cmd?As?OleDb.OleDbCommand??

cmd?=?New?OleDb.OleDbCommand()??

cmd.Connection?=?NewConn()??

cmd.CommandText?=?ProcedureName??

cmd.CommandType?=?CommandType.StoredProcedure??

cmd.Parameters.AddRange(para)??

Return?com.ExecuteNonQuery??

End?Function??

'''?summary??

'''?執(zhí)行查詢(返回一個(gè)結(jié)果集，無(wú)參)??

'''?/summary??

'''?param?name="sql"/param??

'''?returns/returns??

'''?remarks/remarks??

Public?Shared?Function?GetScalar(ByVal?sql?As?String)?As?String??

Dim?dataset?As?DataSet??

dataset?=?New?DataSet()??

com?=?New?OleDb.OleDbCommand(sql,?NewConn)??

adapter?=?New?OleDbDataAdapter(com)??

adapter.Fill(dataset)??

If?dataset.Tables.Count??0?And?dataset.Tables(0).Rows.Count??0?Then??

Return?dataset.Tables(0).Rows(0)(0).ToString()??

End?If??

Return?"Null"??

End?Function??

'''?summary??

'''?執(zhí)行查詢(返回一個(gè)結(jié)果集，有參)??

'''?/summary??

'''?param?name="sql"/param??

'''?param?name="para"/param??

'''?returns/returns??

'''?remarks/remarks??

Public?Shared?Function?GetScalar(ByVal?sql?As?String,?ByVal?para?As?SqlParameter())?As?Integer??

com?=?New?OleDb.OleDbCommand(sql,?NewConn)??

com.Parameters.AddRange(para)??

Return?Convert.ToInt32(com.ExecuteScalar())??

End?Function??

'''?summary??

'''?執(zhí)行查詢(返回一行數(shù)據(jù),無(wú)參)??

'''?/summary??

'''?param?name="sql"/param??

'''?returns/returns??

'''?remarks/remarks??

Public?Shared?Function?GetReader(ByVal?sql?As?String)?As?OleDbDataReader??

com?=?New?OleDb.OleDbCommand(sql,?NewConn)??

reader?=?com.ExecuteReader()??

Return?reader??

End?Function??

'''?summary??

'''?執(zhí)行查詢(返回一行數(shù)據(jù),有參)??

'''?/summary??

'''?param?name="sql"/param??

'''?param?name="para"/param??

'''?returns/returns??

'''?remarks/remarks??

Public?Shared?Function?GetReader(ByVal?sql?As?String,?ByVal?para?As?SqlParameter())?As?OleDbDataReader??

com?=?New?OleDb.OleDbCommand(sql,?NewConn)??

com.Parameters.AddRange(para)??

reader?=?com.ExecuteReader()??

Return?reader??

End?Function??

'''?summary??

'''?執(zhí)行查詢(返回一個(gè)數(shù)據(jù)集,無(wú)參)??

'''?/summary??

'''?param?name="sql"/param??

'''?returns/returns??

'''?remarks/remarks??

Public?Shared?Function?GetDataSet(ByVal?sql?As?String)?As?DataTable??

Dim?dataset?As?DataSet??

dataset?=?New?DataSet()??

com?=?New?OleDb.OleDbCommand(sql,?NewConn)??

adapter?=?New?OleDbDataAdapter(com)??

adapter.Fill(dataset)??

Return?dataset.Tables(0)??

End?Function??

'''?summary??

'''?執(zhí)行查詢(返回一個(gè)數(shù)據(jù)集,有參)??

'''?/summary??

'''?param?name="sql"/param??

'''?param?name="para"/param??

'''?returns/returns??

'''?remarks/remarks??

Public?Shared?Function?GetDataSet(ByVal?sql?As?String,?ByVal?para?As?SqlParameter())?As?DataTable??

Dim?dataset?As?DataSet??

dataset?=?New?DataSet()??

com?=?New?OleDbCommand(sql,?NewConn)??

com.Parameters.AddRange(para)??

adapter?=?New?OleDbDataAdapter(com)??

adapter.Fill(dataset)??

Return?dataset.Tables(0)??

End?Function??

End?Class