Last update: 13.07.2017
To add data, the INSERT command is used, which has the following formal syntax:
INSERT table_name [(column_list)] VALUES (value1, value2, ... valueN)
At the beginning there is an INSERT INTO statement, then in brackets you can specify a list of columns, separated by commas, into which data should be added, and at the end after the word VALUES, in brackets, the values to be added for the columns are listed.
For example, let's say the following database was created earlier:
CREATE DATABASE productsdb; GO USE productsdb; CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL)
Let's add one line to it using the INSERT command:
INSERT Products VALUES ("iPhone 7", "Apple", 5, 52000)
After successful completion of SQL Server Management Studio should show "1 row(s) affected" in the message box:
Note that the values for the columns in parentheses after the VALUES keyword are passed in the order in which they are declared. For example, in the CREATE TABLE statement above, you can see that the first column is Id. But since the IDENTITY attribute is set for it, the value of this column is automatically generated, and it can be omitted. The second column represents ProductName, so the first value, the string "iPhone 7", will be passed to that column. The second value, the string "Apple", will be passed to the third column Manufacturer, and so on. That is, the values are passed to the columns as follows:
ProductName: "iPhone 7"
Manufacturer: Apple
Also, when entering values, you can specify the immediate columns in which values will be added:
INSERT INTO Products (ProductName, Price, Manufacturer) VALUES ("iPhone 6S", 41000, "Apple")
Here the value is specified for only three columns. And now the values are passed in the order of the columns:
ProductName: "iPhone 6S"
Manufacturer: Apple
For unspecified columns (in this case ProductCount) will add a default value if the DEFAULT attribute is set, or NULL. However, unspecified columns must be nullable or have a DEFAULT attribute.
We can also add multiple lines at once:
INSERT INTO Products VALUES ("iPhone 6", "Apple", 3, 36000), ("Galaxy S8", "Samsung", 2, 46000), ("Galaxy S8 Plus", "Samsung", 1, 56000)
In this case, three rows will be added to the table.
Also, when adding, we can specify that the default value be used for the column using the DEFAULT keyword or NULL:
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES ("Mi6", "Xiaomi", DEFAULT, 28000)
In this case, the default value will be used for the ProductCount column (if it is set, if not, then NULL).
If all columns have a DEFAULT attribute that defines a default value, or are nullable, then you can insert default values for all columns:
INSERT INTO Products DEFAULT VALUES
But if you take the Products table, then such a command will fail, since several fields do not have a DEFAULT attribute and at the same time do not allow null values.
Deleting entries
The DELETE statement is used to remove records from a table:
DELETE FROM tablename WHERE condition;
This statement removes from the specified table records (not individual column values) that satisfy the specified condition. The condition is boolean expression, whose various designs were discussed in previous labs.
The following query removes records from the Customer table in which the value of the LName column 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 to select data (the SELECT statement). Subqueries in a DELETE statement work exactly the same way as they do in a SELECT statement. The following query removes all customers from the city of Moscow, with the city's unique identifier returned using a subquery.
DELETE FROM Customer
WHERE IdCity IN (SELECT IdCity FROM City WHERE CityName = "Moscow")
Transact-SQL extends standard SQL by allowing you to use another FROM clause in a DELETE statement. This extension, which specifies a join, can be used instead of a subquery in the WHERE clause to specify the rows to be deleted. It allows you to set data from the second FROM and remove the corresponding rows from the table in the first FROM clause. In particular, the previous query could 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 case of semantic (but not syntactic) errors in the formulation of the SQL statement. To avoid trouble, before deleting records, it is recommended that you first run the appropriate select query to see which records will be deleted. So, for example, before executing the delete query discussed earlier, it would not hurt to execute the corresponding select query.
SELECT *
FROM Customer k INNER JOIN
City c ON k.IdCity = c.IdCity AND c.CityName = "Moscow"
To remove all records from a table, just use the DELETE statement without the WHERE keyword. At the same time, the table itself with all the columns defined in it is saved and ready for inserting new records. For example, the following query removes records for all products.
DELETE FROM Product
Task for independent work: Formulate on SQL language a request to delete all orders that do not contain any goods (i.e. all empty orders).
Change requests are used to add (INSERT INTO), delete (DELETE) and update (UPDATE) records in tables.
The INSERT INTO command can be used to add one or more records to the end of a table.
The syntax of the INSERT INTO command is as follows:
INSERT INTO Table_Name [(Field_Name)] VALUES (Data_Values);
For example, let's put a record in the "Groups" table containing data (1, BF-16a, 1, 1) in the corresponding columns (Group ID, Name, Course, Semester).
For this request in the format:
INSERT INTO Groups (GroupCode, Name, Course, Semester) VALUES (1, BF-16a, 1, 1);
we will enter it on the query form by executing the "Query Designer" command on the "Create" tab, in the "SQL Mode" mode from the View menu.
We save the query with the name "Add_1_records". As a result, an unrelated object will appear in the "Navigation Area" - "Add_1_records" (Fig.1.).
Rice. 1.
After saving the query, you need to execute this query by clicking on the "Run" icon. As a result of executing the “Add_1_records” command, the first record will appear in the empty table (Fig. 2.)
Rice. 2.
The UPDATE command is intended to modify already existing records in the columns of the table, which allows you to update the values in the given columns of the table.
The syntax for the UPDATE command is as follows:
UPDATE Table_Name SET Column_Name = New_Value WHERE Filter_Condition;
The SET clause specifies the names of the columns whose data needs to be changed. The WHERE clause is optional, but it can only point to the rows of the columns (tables) that need to be updated.
Let's create a query to change a record in the "Groups" table of the sql_training_st.mdb database.
For example, let's change an already existing entry for the first key field id in the "Name" column of the "Groups" table.
Request in the format:
UPDATE Groups SET Name="BF-16b" WHERE ID=1;
We save the query with the name "Change_record_1". As a result, an object will appear in the "Navigation Area" - "Change_record_1" (Fig. 3.).
Rice. 3.
After saving the query, you need to execute this query by clicking on the "Run" icon. As a result of executing the Change_Record_1 command, a dialog box will appear in which you must enter the value of the ID=1 parameter and click OK. After these actions in the "Groups" table, the entry in the "Name" field will change from BF-16a to BF-16b (Fig. 4.).
Rice. 4.
The DELETE command is designed to remove records from a table.
Format of request to delete records:
DELETE FROM Table_Name WHERE Selection_Condition;
Let's create a query to change a record in the "Groups" table of the sql_training_st.mdb database.
For example, let's delete the existing entry for the first key field id in all columns (fields) of the "Groups" table.
For this request in the format:
DELETE FROM Groups WHERE ID=1;
create on the query form by executing the "Query Designer" command on the "Create" tab, in the "SQL Mode" mode from the View menu.
We save the query with the name "Delete_record_1". As a result, an object will appear in the "Navigation Area" - "Delete_record_1" (Fig. 5.).
Rice. 5.
After saving the query, you need to execute this query by clicking on the "Run" icon. As a result of executing the "Delete_record_1" command, a dialog box will appear in which you must enter the value of the ID=1 parameter and click OK. After these steps, a dialog box "Confirm the deletion of records" will open. After confirmation in the "Groups" table, the data in all fields will be deleted, i.e. the record is deleted (Fig. 6.).
Rice. 6.
It should be noted that to delete data in specific fields of the table, it is advisable to use the UPDATE command and replace the values in the required fields with Null. If it is necessary to drop the table as a whole, then the DROP TABLE statement should be used (the syntax of the statement is: DROP TABLE TableName;).
Let's restore the first record in the "Groups" table by executing the "Add_1_records" command, as a result we will restore the first record (Fig. 7.).
Rice. 7.
General syntax:
a) inserting one new row into the table:
INSERT<таблица>[(columns)]
VALUES
b) Inserting into the table one or more rows taken from the source specified in the subquery:
INSERT<таблица>[(columns)]
Rules:
The number of items in the Columns section must match the number of items in the Values section; data types must match or allow implicit conversion;
In the case where the "Columns" section is missing, the "Values" section must contain as many values as there are total columns in the edited table, in addition, the values must be listed in the same order in which the table columns are declared; therefore, it is recommended to always explicitly specify the list of columns to which new values will be added;
In the case of syntax (b), the SELECT subquery can be addressed either to the same table, or to another database table, or to another database on the server;
If a default value is set for some column of the table, and it is required to use this value in the added record, then in the "Values" section for this column, you must write keyword DEFAULT.
Examples :
INSERT INTO detail (detail_name)
VALUES ("bearing")
INSERT INTO supply (supplier_id, supply_quantity, supply_cost,
supply_date, detail_id)
VALUES (4, 177, 453.45, "9/25/2009", 1)
INSERT supplier (supplier_type, supplier_name)
VALUES (DEFAULT, "Smirnov S. S.")
General syntax:
UPDATE<таблица>
SET<столбец> = <значение> [, <столбец> = <значение>]
Examples :
SET weight = 210
SET material_name = "Tin"
WHERE material_id = 2
SET department_id = NULL,
delivery_date = delivery_date + 1 /*increase the delivery date by a day
WHERE delivery_id = 1 OR department_id > 3
SET detail_name = detail_name + "!!!"
WHERE detail_name LIKE "_а%" AND weight BETWEEN 6 AND 10
General syntax:
DELETE<таблица>
<условие>
Examples :
WHERE detail_id IN (2, 5, 8)
WHERE weight IS NULL
Exercise:
Populate the database with ManagementStudio. To do this, use the "Open Table" command of the context menu of the table in the "Object Browser" (Fig. 4.1).
SELECT<список столбцов>
FROM<таблица(-ы) источник>
SELECT and FROM clauses must be present in every query; other sections may be missing or partially present.
The most simple queries:
1. Select the name and weight of the parts
Rice. 5.1. Query #1 Results
2. Select all information from the table of materials
Rice. 5.2. Results of Query #2
The uniqueness of DISTINCT
3. Select unique supplier codes from the supply table
Rice. 5.3. Results of Query #3
WHERE constraint
4. Select the quantity and delivery date of the part with code 1
Rice. 5.4. Query #4 Results
5. Select vendor names with codes 1, 4 and 6
Rice. 5.5. Results of query #5
6. Select all information about deliveries made before 10/1/2009
Rice. 5.6. Results of Query #6
7. Select all part information that does not start with the letter "B" (in any case) and whose weight is less than 50
Rice. 5.7. Results of query #7
8. Select the name and code of the material for parts with a weight of 5 to 10 g or having the letter “n” in the name in the third position
Rice. 5.8. Results of query #8
9. Select vendor names no longer than 15 characters
Rice. 5.9. Results of query #9
10. Select months and years of parts supply
Rice. 5.10. Results of query #10
Sorting ORDER BY
11. Sort deliveries first by vendor code and then by delivery date
Rice. 5.11. Results of query #11
12. Select the names of suppliers with codes 4, 6, 8, 9, sorted alphabetically in reverse order
Rice. 5.12. Results of query #12
Aggregation and grouping GROUP BY
Rice. 5.13. Results of query #13
14. Determine the average weight of parts made of material with code 2
Rice. 5.14. Results of Query #2
15. From the deliveries made before 10/1/2009, choose the largest delivery and the smallest
Rice. 5.15. Results of query #15
16. For suppliers with codes in the range 5 - 8, calculate the total number of parts supplied by them
Rice. 5.16. Results of query #16
Rice. 5.17. Results of query #17
HAVING grouping restriction
18. Select materials for which the total weight of parts made from them is not more than 20
Rice. 5.18. Results of query #18
19. From the deliveries of 2008, select the parts that were delivered more than once
Rice. 5.19. Results of query #19
Type conversionCAST
20. Get information about delivery dates in text form
Rice. 5.20. Results of query #20
21. Get information from the parts table as strings "Part X has weight Y"
Rice. 5.21. Results of query #21
AlternativesCASE
22. Divide parts into light (weighing up to 20), medium (between 20 and 50) and heavy
Rice. 5.22. Results of query #22
Handling NULL Values
23. Get information about the details and their weights, and if the weight is not specified for the part, then write -100 instead of NULL
Rice. 5.23. Results of query #23
Existence function EXISTS
24. Select the name and material code of only those parts that have ever been supplied
Rice. 5.24. Results of query #24
25. Select the names of those materials from which not a single part is made
Rice. 5.25. Results of query #25
Subqueries
26. Get information about the most recent (by date) delivery
Rice. 5.26. Results of query #26
27. Get all deliveries of parts from material code 2
Rice. 5.27. Results of query #27
28. For each supplier, get information about the very first (by date) of its delivery
Rice. 5.28. Results of query #28
29. For each supplier, get his name and date of the last order
Rice. 5.29. Results of query #29
JOIN tables
30. Get a table of the view: the name of the part, the name of the material from which this part is made
Rice. 5.30. Results of query #30
31. Get a table of the form: supplier, part name, quantity and delivery date for parts that have a specified weight
Rice. 5.31. Results of query #31
32. Select all information about those parts that have ever been supplied
Rice. 5.32. Results of query #32
Rice. 5.33. Results of query #33
34. Select the names of materials and parts made from them
Rice. 5.34. Results of query #34
35. Get all data about suppliers, deliveries and details
Rice. 5.35. Results of query #35
Joining UNION tables
36. Get a table of names and weights of parts, and the last line of the table should contain totals in the form of the total weight of all parts
Rice. 5.36. Results of query #36
37. Get a table of two fields, where the first field is the name of the part, material, supplier or delivery date, and the second field is the length of the string from the first field
Rice. 5.37. Results of query #37
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 the table can be done in two ways:
Method number 1.
Let's try to add a new country entry to the countries table. The syntax for adding would be:
INSERT INTO table_name (field_1, field_2, ...) VALUES (Value_1, Value_2, ...); Based on our table structure, the SQL query would be:
INSERT INTO countries (country_name, acronym_name) VALUES ("Russia", "RU"); This is how we entered an entry about the country "Russia" into our table. 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 brackets in the same order.
Method number 2.
The second way, in my opinion, is a little easier, because you see what and what field you assign. 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 enter some information into the plate persons, since there are more fields, and the advantage of the second method will immediately be felt:
INSERT INTO persons SET first_name="Ivan", last_name="Dulin", registration_date="2012-06-14", country="1"; Now our table has the following data:
You probably noticed that we did not specify a value for age, but it turned out to be filled ... Everything is correct - for given field we set the default to 25. So now our Ivan Dulin is listed in the database as 25 years old. It may not be the best example to make a default value for the age field, but you can set properties on such fields as, for example, the rating of the user of the site, or the number of page views. They are 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 entries simply will not be included in the table.
As you can see, the profession field is filled in as NULL, this is empty value. For this field, we set the property "not required to fill (NULL)".
As an addition, consider the following command:
LOAD DATA LOCAL INFILE "D:\zapros.txt" INTO TABLE persons; What do you think we have done now?! And we did the following: we added data to the table persons from a file request.txt, which is located on drive D. The information in the file must be of the following structure:
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 |
