SQL Server Database

One. SQL Server database commands

1. Database

(1) Database creation

Create DATABASE name

On primary

(

Name= ' database name ',

Filename= ' Database full path and database name, database file suffix. mdf ',

)

Log on

(

Name= ',

Filename= ' database log file full path and database log file name, database log file suffix. ldf '

)

(2) Database deletion

drop database name;

2. Data Sheet

(1) Data sheet creation

CREATE TABLE data table name

(

Data column name data column type data constraint,

Data column name data column type data constraint,

......,

Data column name data column type data constraint

)

(2) Data Sheet modification

ALTER TABLE Add data column name data column type data column constraint,

ALTER TABLE ALTER DATA column name data column type data column constraint,

ALTER TABLE delete data column name data column type data column constraint,

(3) Data Sheet deletion

drop table Data table name

3. Data Sheet Management

(1) Data table Data Increase command

Insert into data table name (data column, data column,......, data column) values (data row, data row,......, data row)

(2) Data table Data Delete command

Delete from data table name (delete data does not reset increase identifier)

Truncate data table name (delete data and reset increment identifier)

(3) Data table Data Modification command

Update data table name set data column name = data row name, data column name = data row name,......, data column name = data row name

(4) Data table data Query command

SELECT * FROM data table name

SELECT *, data column name,....., data column name from data table name

(5) Data table Data Sorting command

SELECT * FROM data table name order by data column name ASC

(6) Data table Data Grouping command

SELECT * FROM data table name group by data column name ORDER by ASC

(7) Data Table Joint Query command

SELECT * FROM data a table name inner JOIN data table B table name on data a table column = data table B table column

(8) Data table join query command

SELECT * FROM data a table name left JOIN data table B table name on data a table column = data table B table column

SELECT * FROM data a table name right join data table B table name on data a table column = data table B table column

4. Data Table Index

5. Data table constraints

ALTER TABLE ADD CONSTRAINT constraint name constraint type

ALTER TABLE ALTER CONSTRAINT CONSTRAINT name constraint type

ALTER TABLE DELETE constraint constraint name constraint type

6. Datasheet view

Create VIEW View name

As

The SQL statements encapsulated in the view for displaying data

7. Data Sheet Transactions

8. Data table wildcard characters

9. Data table Stored Procedures

create proc Stored procedure name

Call variables in stored procedures (C # passed in parameters)

As

SQL statements encapsulated in a stored procedure

Two. SQL Server database application

Public SqlConnection getconnection ()
{
return new SqlConnection (@ "server=grapeme\sqlexpress; database=goodstable; uid=sa; password=0910;");
}

Public SqlCommand Getcommandproc (string procname)
{
using (SqlConnection connection = getconnection ())
{
using (SqlCommand command = new SqlCommand ())
{
Connection. Open ();
Command.commandtype = CommandType.StoredProcedure;
Command.commandtext = procname;
return command;
}
}
}

Public DataTable GetList (SqlCommand command, String tableName)
{
using (SqlConnection connection = getconnection ())
{
Connection. Open ();
SqlDataAdapter ad = New SqlDataAdapter (Command.commandtext, connection);
DataSet ds = new DataSet ();
Ad. Fill (DS);
Return DS. Tables[tablename];
}
}

Public DataTable GetList (string sql,string tableName)
{
using (SqlConnection connection = getconnection ())
{
Connection. Open ();
SqlDataAdapter ad = new SqlDataAdapter (SQL, connection);
DataSet ds = new DataSet ();
Ad. Fill (DS);
Return DS. Tables[tablename];
}
}

public int ExecuteNonQuery (String sql)
{
using (SqlConnection connection = getconnection ())
{
using (SqlCommand command = new SqlCommand (SQL, connection))
{
Connection. Open ();
return command. ExecuteNonQuery ();
}
}
}

public Object ExecuteScalar (String sql)
{
using (SqlConnection connection = getconnection ())
{
using (SqlCommand command = new SqlCommand (SQL, connection))
{
Connection. Open ();
return command. ExecuteScalar ();
}
}
}

Public SqlDataReader ExecuteReader (String sql)
{
using (SqlConnection connection = getconnection ())
{
using (SqlCommand command = new SqlCommand (SQL, connection))
{
Connection. Open ();
return command. ExecuteReader ();
}
}
}

SQL Server Database