[DB – SQL] DML – Selecting/Inserting/Updating/Deleting Data

The Data Manipulation Language (DML) part of SQL lets you manipulate data, such as inserting, updating, deleting, and retrieving.

1. Inserting Data into a Table

INSERT INTO table_name (first_field, ... last_field)
VALUES (first_value, ... last_value);

To insert records into a table:

  • keywords “INSERT INTO”
  • table_name
  • a list of column names enclosed in parenthesis (optional)
  • a keyword “VALUES”
  • a list of values enclosed in parenthesis

The values will match up with the column names that you specify. Strings should be enclosed in single quotes, and numbers should not.

 

2.  Inserting Data into a Table using SQL

Special care is needed to insert the “DATE” type data. It is usually dependent on the current type definition of the date. The following SQL statements assumes that the type of date is “dd-Mon-yy”.

INSERT INTO Weather (City, State, High_temp, Low_temp, Cur_date)
VALUES ('Arlington', 'Illinois', 18, 3, '14-JAN-12');

INSERT INTO Weather (City, State, High_temp, Low_temp, Cur_date)
VALUES ('Chicago', 'Illinois', 0, -11, '20-MAR-12');

INSERT INTO Weather (City, State, High_temp, Low_temp, Cur_date)
VALUES ('New York', 'New York', 10, -5, '10-JAN-12');

INSERT INTO Weather (City, High_temp, Low_temp, Cur_date)
VALUES ('Seattle', 7, 0, '12-JAN-12');

 

3.  Inserting Data into a Table in C#

using (SqlConnection db_con = new SqlConnection(@"Server=MY-PC/SQLEXPRESS;Database=TestDB;Trusted_Connection=True;"))
{
  db_con.Open();
  using (SqlCommand cmd = db_con.CreateCommand())
  {
    // data for inserting
    string[] city = { "New York", "Seattle", "Chicago", "Arlington" };
    string[] state = { "New York", "", "Illinois", "Illinois" };
    int[] high = { 10, 7, 0, 18 };
    int[] low = { -5, 0, -11, 3 };
    string[] dates = { "2012-01-10", "2012-01-12", "2012-03-20", "2012-01-14" };

    for (int i = 0; i < 4; i++)
    {
      // setting SQL command
      cmd.CommandText =
        "INSERT INTO Weather " +
        "VALUES (" +
        "'" + city[i] + "'," +
        "'" + state[i] + "'," +
        high[i] + "," +
        low[i] + "," +
        "'" + dates[i] + "'" +
        ")";

      // Execute
      int result = cmd.ExecuteNonQuery();
      Console.WriteLine(" {0} rows are added ", result);
    }
  }
}

 

4. Selecting Data

SELECT field_name [, field_name, ...]
FROM table_name
[ WHERE field_name OPERATOR value AND/OR field_name OPERATOR value - ];

Operators:

  • = : Equal to
  • > : Greater than
  • < : Less than
  • >= : Greater than or equal to
  • <= : Less than or equal to
  • <> : Not equal to
  • LIKE : pattern matching operator, The percent sign “%” can be used as a wild card to match any possible character that might appear before or after the characters specified.
SELECT City
FROM Weather
WHERE State LIKE 'Illin%';

SELECT City
FROM Weather
WHERE High_temp>10 OR Low_temp<-4;

 

5. Updating Data

UPDATE table_name
SET field_name = new_value [, next_field = newvalue2 ...]
WHERE field_name OPERATOR value [ AND|OR field_name OPERATOR value];

The update statement is used to update or change records that match a specified criteria.

UPDATE Weather
SET Low_temp=5
WHERE CITY LIKE 'Arlington';

UPDATE Weather
SET High_temp=-3, Cur_date = '2013-01-14'
WHERE CITY LIKE 'Chicago';

UPDATE Weather
SET High_temp=12, Low_Temp=2, Cur_date = '2013-01-12'
WHERE CITY LIKE 'New York';

UPDATE Weather
SET State = 'Washington', Low_temp=1
WHERE CITY LIKE 'Sea%';

 

6. Deleting Records

DELETE FROM table_name
WHERE field_name OPERATOR value [ AND|OR field_name OPERATOR value];

The delete statement is used to delete records or rows from the table.

DELETE FROM Weather
WHERE State LIKE 'New%';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s