home › Working with photos › Removing records from SQL database. SQL query to add and delete Sql records add data

Removing records from SQL database. SQL query to add and delete Sql records add data

This article is devoted to filling database tables with data, that is, we will study SQL commands for inserting new records. It should be said that writing to a table can be done in two ways:

Method number 1.
Let's try to record a new country in the countries table. The syntax for adding will be as follows:
INSERT INTO table_name (field_1, field_2, ...) VALUES (Value_1, Value_2, ...); Based on our table structure, the SQL query would look like this:
INSERT INTO countries (country_name, acronym_name) VALUES ("Russia", "RU"); This is how we entered into our table a record about the country "Russia". Everything should be clear and simple here, the main thing is to carefully look at which fields you specify in the first brackets, and write the values in the second in the same order.

Method number 2.
The second method, in my opinion, is a little easier, since you see what and to which field you are assigning. Believe me, if the table has a huge number of columns, then it is very easy to confuse or overlook the order of the fields in the first, and the order of the values in the second brackets. The syntax for the second method is:
INSERT INTO table_name SET field_1 = Value_1, field_2 = Value_2,…; Let's use this example to add some information to the plate. persons, since there are more fields, and you will immediately feel the advantage of the second method:
INSERT INTO persons SET first_name = "Ivan", last_name = "Dulin", registration_date = "2012-06-14", country = "1"; Now our plate contains the following data:

You probably noticed that we did not specify a value for age, but it turned out to be filled ... That's right - for this field we set the default value as 25. So now our Ivan Dulin is listed in the database with the age of 25 years. It might not be a good example to make a default value for the age field, but you can set such properties to such fields as, for example, the site's user rating, or the number of page views. For them, the value is initially set to 0.

You should also pay attention to the date format in MySQL: YYYY-MM-DD. If you do not adhere to it, then your records will simply not be included in the table.

As you can see, the profession field is filled as NULL, this is an empty value. For this field.

As an addition, consider another command like this:
LOAD DATA LOCAL INFILE "D: \ zapros.txt" INTO TABLE persons; What do you think we have done now ?! And we did the following: added data to the table persons from file zapros.txt, which is located on disk D. The information in the file should be of the following structure:

The file data structure must meet the following requirements:

Each new entry should be described with a new line.
Data must be specified for absolutely all fields. As you can see, we specified NULL for the id field, because we have it auto-incrementing, so MySQL will enter the required value by itself.
The fields are separated from each other by a tab character (Tab key).
The information entered must match the data types of the specific field. That is, for example, a date in the format YYYY-MM-DD, an integer for an integer, etc.

Thus, you have learned how to enter new data into your database tables. To consolidate the studied material, enter the following data yourself:

Professions table:

Persons table:

first_name	last_name	age	registration_date	country	profession
Leonid	Bilak	45	2012-06-20	2	1
Yuri	Nazarov	22	2012-06-21	3	1
Alla	Savenko	25	2012-06-22	2	3
Irina	Nikolaeva	31	2012-06-22	1	3

Be sure to add these records, since we will need them to study the SELECT statement (selection of information from the database), which we will consider in the next (fourth) article on studying SQL queries.

At times, programmers have a burning desire to read something from the database. Programmers become jittery and irritable, lose sleep, and feverishly poke their fingers at the keyboard. For the sake of everyone's good and world peace, let's take a look at basic database manipulation from C # using ADO.NET and OLE DB. Although this mechanism supports various databases like Oracle, here and now we will use MS SQL Server.

Two main tasks when working with a database

2. Execution of a sql command that does something on the server (insert, update, delete, call a function or stored procedure

3. Sequential reading from select-selection line by line. It is mainly used in web applications, in desktop applications it is easier to download the entire sample into RAM at once, the savings from reading only the necessary lines are insignificant.

4. A rare case. Automatic updating of the table in the database based on changes in the DataTable (usually edited through a visual interface). In real life, data is usually read through a complex query with a heap join or view, so automatic synchronization is not a good idea.

The main classes used for these purposes: OleDbConnection - connection to the base, create with a string containing connection parameters, open, close, OleDbCommand - create with a connection instance and sql command, if you just need to execute update or get a single value, then that's enough class, OleDbDataAdapter - created with OleDbCommand, specializes in one-time reading of rowsets in a DataTable, can automatically create DataTable columns based on a selection, transfer changes from DataTable to a table in the database, OleDbDataReader - sequential reading of rows in DataTable one at a time (it works inside OleDbDataAdapter), DataTable / DataSet is the main container for data. The OleDbType enumeration stores the data types of the database.

Using System.Data; using System.Data.OleDb; // connection string, Windows user system is used (Integrated Security = SSPI;) string connString = "Provider = SQLOLEDB.1; Integrated Security = SSPI; Persist Security Info = False; Initial Catalog = databaseName; Data Source = serverName"; // alternative connection string using MS SQL Server authentication // connString = "Provider = SQLOLEDB.1; Persist Security Info = False; Initial Catalog = databaseName; Connect Timeout = 20; Data Source = serverName; Uid = userName; Pwd = userPassword ; " OleDbConnection dbConn = new OleDbConnection (connString); dbConn.Open (); someDataTable = new DataTable (); OleDbDataAdapter dbAdapter = new OleDbDataAdapter ("select COLUMN1, COLUMN2 from TEST_TABLE ORDER BY COLUMN2", dbConn); // the internal structure of an empty table will be created automatically based on the read data, if the structure of the table has already been set (for example, through a typed DataSet), then the data will be written to columns with matching names or new columns will be added dbAdapter.Fill (someDataTable); // alternative option for filling the table in the DataSet // dbAdapter.Fill (someDataSet, "someDataTable"); dbConn.Close ();

2. Execution of a sql command that does something on the server (insert, update, delete, call a function or stored procedure.

Especially noteworthy are the problems with dates. Implementation of dates in .Net is extremely crooked - initially dates cannot be empty, but in real life they are empty all the time. The most correct solution is to use a special date class that fixes the jambs of programmers from Microsoft. More lazy developers keep all dates in the code as strings and convert them to DateTime only when necessary, for example, when writing to a database or DataTable. Nullable DateTime does not help, since an empty date in the interface should look like an empty string, and when written to the database as DBNull.Value, the banal null in the code is not converted to these values without additional dancing with a tambourine.

The first option involves banal gluing of the query string. It is considered bad practice and is especially dangerous in web applications, as it is vulnerable to hacker attacks. The problem with empty dates is not easily solved. In addition, when working with dates, an additional problem appears - different formats of date strings depending on the regional settings of the .Net Framework, development environment and sql server. It may look surreal - the same query works in SQL Managment Studio, but crashes when executed from the code. Partly saved by the special format of the date string. Nevertheless, this is often done in small programs for internal use, the existence of which the outside world will never know.

OleDbCommand dbCommand = dbConn.CreateCommand (); dbCommand.CommandText = "INSERT INTO TEST_TABLE (INT_COLUMN, VARCHAR_COLUMN, DATETIME_COLUMN) VALUES (" + intVariable "," + stringVariable "," + dateTimeVariable.ToString ("yyyyyMMd" dbCommand.ExecuteNonQuery ();

The correct option is to create a command with a set of strongly typed parameters. Consider that there was no previous paragraph.

OleDbCommand dbCommand = dbConn.CreateCommand (); dbCommand.CommandText = "INSERT INTO TEST_TABLE (INT_COLUMN, VARCHAR_COLUMN, DATETIME_COLUMN) VALUES (?,?,?)"; dbCommand.Parameters.Add ("INT_COLUMN", OleDbType.Integer) .Value = intVariable; dbCommand.Parameters.Add ("VARCHAR_COLUMN", OleDbType.VarChar) .Value = stringVariable; if (stringDate == "") (dbCommand.Parameters.Add ("DATETIME_COLUMN", OleDbType.DateTime) .Value = DBNull.Value;) else (dbCommand.Parameters.Add ("DATETIME_COLUMN", OleDbTime) Convert.ToDateTime (stringDate);) dbCommand.ExecuteNonQuery ();

The stored procedure is called in the same way, for the sake of variety, for the sake of another option for writing values to parameters (it is not related specifically to the stored procedure):

OleDbCommand someDbComm = new OleDbCommand ("someStoredProcedure", this.dbConn); someDbComm.CommandType = CommandType.StoredProcedure; someDbComm.Parameters.Add ("@ parameter1", OleDbType.VarChar); someDbComm.Parameters.Add ("@ parameter2", OleDbType.VarChar); someDbComm.Parameters.Value = "(! LANG: Every problem always has a solution - simple, convenient and, of course, erroneous"; someDbComm.Parameters.Value = "Henry Louis Mencken"; someDbComm.ExecuteNonQuery(); !}

An extended version of the parameter description with an indication of the field size and binding to a specific column of the table.

DbCommand.Parameters.Add ("VARCHAR_COLUMN", OleDbType.VarChar, 100, "VARCHAR_COLUMN"). Value = stringVariable;

If we do not need to bind the command parameter to a specific DataTable field, then it is best not to specify the size at all, for example, if the string is less than the specified Varchar length, then the kind .Net framework will add spaces to the string to the specified length, spoiling the data transmitted to the server.

The value of a single field is read by the ExecuteScalar () method

It should be especially noted that ExecuteScalar returns Object and if the request did not return anything at all, then the result will be null and the conversion to the normal data type will fail with an error. If a situation is possible when we get nothing in response, then we need to do this:

OleDbCommand dbCommand = dbConn.CreateCommand (); dbCommand.CommandText = "SELECT TEST_COLUMN FROM TEST_TABLE WHERE ID_COLUMN =?"; dbCommand.Parameters.Add ("INT_COLUMN", OleDbType.Integer) .Value = intVariable; object resultObj = dbCommand.ExecuteScalar () int result = -1; // default value meaning empty result if (resultObj! = null) (result = Convert.ToInt32 (dbCommand.ExecuteScalar ());)

3. Sequential reading from select-selection line by line
Reading one line (several are read in a loop);

OleDbCommand dbCommand = new OleDbCommand (select PERSON_ID, NAME, SURNAME from TEST_TABLE, dbConn); OleDbDataReader dbReader = dbCommand.ExecuteReader (); dbReader.Read (); string name = Convert.ToString (dbReader ["NAME"]); string surname = Convert.ToString (dbReader ["SURNAME"]); dbReader.Close ();

4. A rare case. Automatic updating of the table in the database based on changes in the DataTable (usually edited through the visual interface).

It is necessary to register four commands for DbAdapter for each possible case - select, insert, update, delete.

DbAdapter.InsertCommand = new OleDbCommand ("insert into TEST_TABLE (NAME, FAMIL, AGE) values (?,?,?)", DbConnection); dbAdapter.InsertCommand.Parameters.Add ("NAME", OleDbType.VarChar, 100, "NAME"); dbAdapter.InsertCommand.Parameters.Add ("FAMIL", OleDbType.VarChar, 100, "FAMIL"); // for data types with constant length, the length specified in the command is ignored dbAdapter.InsertCommand.Parameters.Add ("AGE", OleDbType.Integer, 100, "AGE"); // add the update command dbAdapter.UpdateCommand = new OleDbCommand ("update TEST_TABLE set NAME =?, FAMIL =?, AGE =? where ID =?", dbConnection); dbAdapter.UpdateCommand.Parameters.Add ("NAME", OleDbType.VarChar, 100, "NAME"); dbAdapter.UpdateCommand.Parameters.Add ("FAMIL", OleDbType.VarChar, 100, "FAMIL"); dbAdapter.UpdateCommand.Parameters.Add ("AGE", OleDbType.Integer, 100, "AGE"); dbAdapter.UpdateCommand.Parameters.Add ("ID", OleDbType.Integer, 100, "ID"); // add a delete command dbAdapter.DeleteCommand = new OleDbCommand ("delete from TEST_TABLE where ID =?", dbConnection); dbAdapter.DeleteCommand.Parameters.Add ("ID", OleDbType.Integer, 100, "ID"); try (// transfers all changes from the DataTable to the table in the database dbAdapter.Update (table);) catch (Exception error) (MessageBox.Show ("Error while saving data!" + error.Message); return;) MessageBox. Show ("Changes have been saved!");

In this article, we will analyze perhaps some of the most important SQL queries... it queries to add and remove records from a database table... Since, VERY often it is necessary add new records to the table, and to do it in automatic mode, then this material is required for study.

To start SQL query to add a new record to the table:

INSERT INTO users (login, pass) values ("TestUser", "123456")

When adding an entry, the first command is " INSERT INTO", then the name of the table we are inserting the record into. Next comes the names of the fields we want to fill in in parentheses. And then in parentheses after the word" values"we begin to list the values of those fields that we have selected. After executing this query, a new record will appear in our table.

Sometimes it takes update a record in a table, for this there is the following SQL query:

UPDATE users SET login = "TestUser2", pass = "1234560" WHERE login = "TestUser"

This query is more complex, since it has the construction " WHERE", but about it a little lower. First comes the command" UPDATE"followed by the table name followed by" SET"we describe the values of all the fields that we want to change. It would be simple, but the question arises:" Which record should you update?". For this there is" WHERE". In this case, we are updating the record, the field" login"which matters" TestUser". Please note that if there are several such records, then absolutely everything will be updated! This is very important to understand, otherwise you risk losing your table.

Let's talk a little more about " WHERE". In addition to simple tests for equality, there are also inequalities, as well as logical operations: AND and OR.

UPDATE users SET login = "TestUser2", pass = "1234560" WHERE id< 15 AND login="TestUser"

The SQL query will update those records id which are less 15 AND field " login" has the meaning " TestUser". I hope you figured out the design." WHERE"because it is very important. Exactly." WHERE"used when fetching records from tables, and this is the most frequently used task when working with databases.

And finally, a simple one SQL query to delete records from a table:

DELETE FROM users WHERE login = "TestUser2"

After the command " DELETE FROM"there is the name of the table in which you want to delete records. Next, we describe the" WHERE "construction. WHERE", any number of them can be deleted.

Deleting records

To delete records from a table, use the DELETE statement:

DELETE FROM tablename WHERE condition;

This statement removes records from the specified table (rather than individual column values) that satisfy the specified condition. A condition is a logical expression whose various constructs have been discussed in previous labs.

The following query removes records from the Customer table in which the LName column value is "Ivanov":

DELETE FROM Customer

WHERE LName = "Ivanov"

If the table contains information about several clients with the last name Ivanov, then all of them will be deleted.

The WHERE clause can contain a subquery for data selection (SELECT statement). The subqueries in the DELETE statement work exactly the same as in the SELECT statement. The following query removes all clients from the city of Moscow, while the unique identifier of the city is returned using a subquery.

DELETE FROM Customer

WHERE IdCity IN (SELECT IdCity FROM City WHERE CityName = "Moscow")

Transact-SQL extends standard SQL by allowing another FROM clause in a DELETE statement. This extension, which specifies a join, can be used instead of a subquery in a WHERE clause to specify the rows to delete. It allows you to set data from the second FROM and delete the corresponding rows from the table in the first FROM clause. In particular, the previous query can be rewritten as follows

DELETE FROM Customer

FROM Customer k INNER JOIN

The operation of deleting records from a table is dangerous in the sense that it is associated with the risk of irreversible data loss in the event of semantic (but not syntax) errors in the formulation of an SQL expression. To avoid the trouble, before deleting records, it is recommended that you first run the appropriate fetch query to see which records will be deleted. So, for example, before executing the previously discussed delete query, it would not hurt to execute the corresponding fetch query.

SELECT *

FROM Customer k INNER JOIN

City c ON k.IdCity = c.IdCity AND c.CityName = "Moscow"

To delete all records from a table, simply use the DELETE statement without the WHERE keyword. In this case, the table itself with all the columns defined in it is saved and is ready for inserting new records. For example, the following query removes all product records.

DELETE FROM Product

Self-study assignment: Formulate a SQL query to delete all orders that do not contain any items (that is, all empty orders).

The content of the article
1. The most basic MySQL queries
2. Simple SELECT queries
3. Simple INSERT (new record) queries
4. Simple UPDATE (rewrite, append) queries
5. Simple DELETE (delete record) queries
6. Simple DROP (delete table) queries
7. Complex MySQL queries
8. MySQL queries and PHP variables

1. The simplest SQL queries

1. Will display a list of ALL databases.

SHOW databases;
2. Lists ALL tables in the base_name Database.

SHOW tables in base_name;

2. Simple SELECT queries against MySQL database

SELECT- a query that selects already existing data from the database. For selection, you can specify certain selection parameters. For example, the essence of the query in Russian is: SELECT such and such columns FROM such and such table WHERE the parameter of such and such column is equal to the value.

1. Selects ALL data in the tbl_name table.

SELECT * FROM tbl_name;
2. Will display the number of records in the tbl_name table.

SELECT count (*) FROM tbl_name;
3. Selects (SELECT) from (FROM) table tbl_name limit (LIMIT) 3 records, starting at 2.

SELECT * FROM tbl_name LIMIT 2,3;
4. Selects (SELECT) ALL (*) records from (FROM) table tbl_name and sorts them (ORDER BY) by id in order.

SELECT * FROM tbl_name ORDER BY id;
5. Selects (SELECT) ALL records from (FROM) table tbl_name and sorts them (ORDER BY) by id in REVERSE order.

SELECT * FROM tbl_name ORDER BY id DESC;
6. Selects ( SELECT) ALL (*) records from ( FROM) tables users and sorts them ( ORDER BY) on the field id in ascending order, limit ( LIMIT) the first 5 records.

SELECT * FROM users ORDER BY id LIMIT 5;
7. Selects all records from the table users where is the field fname matches the value Gena.

SELECT * FROM users WHERE fname = "Gena";
8. Selects all records from the table users where the field value fname begin with Ge.

SELECT * FROM users WHERE fname LIKE "Ge%";
9. Selects all records from the table users, where fname ends in na, and orders the entries in ascending order of value id.

SELECT * FROM users WHERE fname LIKE "% na" ORDER BY id;
10. Selects all data from columns fname, lname from the table users.

SELECT fname, lname FROM users;

11. Let's say you have a country in the user data table. So if you want to display ONLY a list of occurring values (so that, for example, Russia is not displayed 20 times, but only one), then use DISTINCT. It will deduce from the mass of repeated values Russia, Ukraine, Belarus. So from the table users columns country ALL UNIQUE values will be displayed

SELECT DISTINCT country FROM users;
12. Selects ALL row data from a table users where age has the values 18,19 and 21.

SELECT * FROM users WHERE age IN (18,19,21);
13. Selects the MAX value age in the table users... That is, if you have the highest value in the table age(from English age) is 55, then the query result will be 55.

SELECT max (age) FROM users;
14. Selects data from the table users by fields name and age WHERE age takes the smallest value.

SELECT name, min (age) FROM users;
15. Selects data from the table users on the field name WHERE id NOT EQUAL 2.

SELECT name FROM users WHERE id! = "2";

3. Simple INSERT (new record) queries

INSERT- a query that allows you to INITIALLY insert a record into the database. That is, it creates a NEW record (line) in the database.

1. Makes a new entry in the table users, in field name inserts Sergey, and in the field age inserts 25. Thus, a new row with the given values is appended to the table. If there are more columns, then they will remain either empty or with the default values.

INSERT INTO users (name, age) VALUES ("Sergey", "25");

4. Simple UPDATE queries to the MySQL database

UPDATE- a query that allows you to REWRITE field values or ADD something to an existing row in the database. For example, there is a ready-made string, but you need to overwrite the age parameter in it, since it has changed over time.

1. In the table users age becomes 18.

UPDATE users SET age = "18" WHERE id = "3";
2. Everything is the same as in the first query, just the syntax of the query is shown, where two or more fields are overwritten.
In the table users WHERE id is 3 field value age becomes 18, and country Russia.

UPDATE users SET age = "18", country = "Russia" WHERE id = "3";

5. Simple DELETE (delete record) queries to the MySQL database

DELETE- a query that removes a row from the table.

1. Deletes a row from the table users WHERE id is equal to 10.

DELETE FROM users WHERE id = "10";

6. Simple DROP (delete a table) queries to the MySQL database

DROP- a query that drops a table.

1. Drops the entire table tbl_name.

DROP TABLE tbl_name;

7. Complex queries to the MySQL database

Curious queries that might come in handy even for advanced users

SELECT id, name, country FROM users, admins WHERE TO_DAYS (NOW ()) - TO_DAYS (registration_date)<= 14 AND activation != "0" ORDER BY registration_date DESC;
This complex query SELECT columns id, name, country IN TABLES users, admins WHERE registration_date(date) not older 14 days And activation NOT EQUAL 0 , Sort by registration_date in reverse order (new at the beginning).

UPDATE users SET age = "18+" WHERE age = (SELECT age FROM users WHERE male = "man");
The above is an example of the so-called request in request in SQL. Update user age to 18+ where gender is male. I do not recommend such query options. From personal experience, I will say that it is better to create several separate ones - they will be worked out faster.