Basic SQL syntax [〕

3. Common SQL commands:

(1) Data Record Filtering:

SQL = "select * from data table where field name = Field Value Order by field name [DESC]"

SQL = "select * from data table where field name like \ '% Field Value % \ 'order by field name [DESC]"

SQL = "select top 10 * from data table where field name order by field name [DESC]"

SQL = "select * from data table where field name in (\ 'value 1 \', \ 'value 2 \', \ 'value 3 \')"

SQL = "select * from data table where field name between value 1 and value 2"

(2) update data records:

SQL = "update data table set field name = field value where condition expression"

SQL = "update data table set field 1 = value 1, Field 2 = value 2 ...... Field n = value n where condition expression"

(3) Delete data records:

SQL = "delete from data table where condition expression"

SQL = "delete from data table" (delete all data table Records)

(4) add data records:

SQL = "insert into data table (Field 1, Field 2, Field 3 ...) Values (value 1, value 2, value 3 ...) "

SQL = "insert into target data table select * from source data table" (add records of source data table to target data table)

(5) statistical functions of data records:

AVG (field name) returns the average value of a table column
Count (* | field name) statistics on the number of data rows or the number of data rows with values in a column
Max (field name) obtains the maximum value of a table column.
Min (field name) obtains the minimum value of a table column.
Sum (field name) adds the values in the data column

The method for referencing the above functions:

SQL = "select sum (field name) as Alias from data table where condition expression"
Set rs = conn. excute (SQL)

Use RS ("alias") to obtain the calculation value. Use the same method for other functions.

(5) Create and delete data tables:

Create Table data table name (Field 1 type 1 (length), Field 2 type 2 (length )...... )

Example: Create Table tab01 (name varchar (50), datetime default now ())

Drop table data table name (permanently delete a data table)

Insert, delete, and modify SQL statements with Parameters
// Insert
Private void insert_click (Object sender, system. eventargs E)
{
String connstr = "Server =.; database = person; uid = sa; Pwd = ;";
String commandstr = "insert into person (name, id_position, tel, salary) values (@ name, @ title, @ phone number, @ salary )";
Sqlconnection conn;
Sqlcommand com;
Conn = new sqlconnection (connstr );
Conn. open ();
COM = new sqlcommand (commandstr, Conn );
Com. Parameters. Add (New sqlparameter ("@ name", sqldbtype. Char ));
Com. Parameters. Add (New sqlparameter ("@ title", sqldbtype. Char ));
Com. Parameters. Add (New sqlparameter ("@ phone", sqldbtype. Char ));
Com. Parameters. Add (New sqlparameter ("@ ", sqldbtype. INT ));
Com. Parameters ["@ name"]. value = textbox1.text;
Com. Parameters ["@ title"]. value = textbox2.text;
Com. Parameters ["@ phone"]. value = textbox3.text;
Com. Parameters ["@ "]. value = "/convert. toint32 (textbox4.text );
Com. executenonquery ();
Conn. Close ();
Showperson ();

}
// Delete
Private "Void delete_click (Object sender, system. eventargs E)
{

String connstr = "Server =.; database = person; uid = sa; Pwd = ;";
String commandstr = "delete from person where name = @ name ";
Sqlconnection conn;
Sqlcommand com;
Conn = new sqlconnection (connstr );
Conn. open ();
COM = new sqlcommand (commandstr, Conn );
Com. Parameters. Add (New sqlparameter ("@ name", sqldbtype. Char ));
Com. Parameters ["@ name"]. value = textbox1.text;
Com. executenonquery ();
Conn. Close ();
Showperson ();
}
// Modify
Private void update_click (Object sender, system. eventargs E)
{


String connstr = "Server =.; database = person; uid = sa; Pwd = ;";
String commandstr = "Update person set id_position = @ title, tel = @ phone, salary = @ salary where name = @ name ";
Sqlconnection conn;
Sqlcommand com;
Conn = new sqlconnection (connstr );
Conn. open ();
COM = new sqlcommand (commandstr, Conn );
Com. Parameters. Add (New sqlparameter ("@ name", sqldbtype. Char ));
Com. Parameters. Add (New sqlparameter ("@ title", sqldbtype. Char ));
Com. Parameters. Add (New sqlparameter ("@ phone", sqldbtype. Char ));
Com. Parameters. Add (New sqlparameter ("@ ", sqldbtype. INT ));
Com. Parameters ["@ name"]. value = textbox1.text;
Com. Parameters ["@ title"]. value = textbox2.text;
Com. Parameters ["@ phone"]. value = textbox3.text;
Com. Parameters ["@ "]. value = "/convert. toint32 (textbox4.text );
Com. executenonquery ();
Conn. Close ();
Showperson ();
}