General syntax:
a) inserting one new row into the table:
INSERT<таблица> [(columns)]
VALUES (values)
b) Inserting one or more rows into the table, 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 when the "Columns" section is missing, the "Values" section must contain the same number of values \u200b\u200bas there are total columns in the edited table, in addition, the values \u200b\u200bmust 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 \u200b\u200bwill be added;
In the case of using the syntax (b), the SELECT subquery can be addressed either to the same table, or to another table in the database, or to another database on the server;
If a default value is set for a certain column of the table, and you want to use this value in the added record, then in the "Values" section, you must write the DEFAULT keyword for this column.
Examples of :
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, "09/25/2009", 1)
INSERT supplier (supplier_type, supplier_name)
VALUES (DEFAULT, "Smirnov S. S.")
Modification of existing records (update)
General syntax:
UPDATE<таблица>
SET<столбец> = <значение> [, <столбец> = <значение>]
Examples of :
SET weight \u003d 210
SET material_name \u003d "Tin"
WHERE material_id \u003d 2
SET department_id \u003d NULL,
delivery_date \u003d delivery_date + 1 / * increase the delivery date by one day
WHERE delivery_id \u003d 1 OR department_id\u003e 3
SET detail_name \u003d detail_name + "!!!"
WHERE detail_name LIKE "_а%" AND weight BETWEEN 6 AND 10
Deleting records from a table (delete)
General syntax:
DELETE<таблица>
<условие>
Examples of :
WHERE detail_id IN (2, 5, 8)
WHERE weight IS NULL
The task:
Populate the database with ManagementStudio tools. To do this, use the "Open Table" command from the context menu of the table in the "Object Explorer" (Fig. 4.1).
Data fetch queries
General select query syntax
SELECT<список столбцов>
FROM<таблица(-ы) источник>
SELECT and FROM clauses must be present in every query; other sections may be missing or partially present.
Examples of implementing requests
The most basic queries:
1. Select the name and weight of the parts
Figure: 5.1. Results of execution of query # 1
2. Select all information from the table of materials
Figure: 5.2. Results of query # 2 execution
DISTINCT uniqueness
3. Select unique supplier codes from the delivery table
Figure: 5.3. Results of query # 3
WHERE constraint
4. Select the quantity and delivery dates of the part with code 1
Figure: 5.4. Results of query # 4
5. Select supplier names with codes 1, 4 and 6
Figure: 5.5. Results of query # 5
6. Select all information about deliveries made before 1.10.2009
Figure: 5.6. Results of query # 6
7. Select all information about parts that do not start with the letter "B" (in any case) and whose weight is less than 50
Figure: 5.7. Results of query # 7 execution
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
Figure: 5.8. Results of query # 8
9. Select supplier names no longer than 15 characters
Figure: 5.9. Results of query # 9
10. Select months and years of delivery of parts
Figure: 5.10. Results of query # 10
Sort ORDER BY
11. Sort deliveries first by vendor code and then by delivery date
Figure: 5.11. Results of query # 11
12. Select supplier names with codes 4, 6, 8, 9, sorted alphabetically in reverse order
Figure: 5.12. Results of query # 12 execution
Aggregation and grouping GROUP BY
Figure: 5.13. Results of query # 13
14. Determine the average weight of parts made of material with code 2
Figure: 5.14. Results of query # 2 execution
15. From the deliveries made before 1.10.2009, select the largest delivery and the smallest
Figure: 5.15. Results of query # 15 execution
16. For suppliers with codes in the range of 5 - 8, calculate the total number of parts supplied by them
Figure: 5.16. Results of query # 16
Figure: 5.17. Results of query # 17
Restriction on HAVING groupings
18. Select materials for which the total weight of parts made from them is not more than 20
Figure: 5.18. Results of query # 18
19. From the supplies of 2008, select parts that were supplied more than once
Figure: 5.19. Results of query # 19 execution
Type conversionCAST
20. Get information about delivery dates in text form
Figure: 5.20. Results of execution of query # 20
21. Get information from the table of parts in the form of rows "Part X has weight Y"
Figure: 5.21. Results of query # 21
AlternativesCASE
22. Divide the parts into light (up to 20), medium (between 20 and 50) and heavy
Figure: 5.22. Results of query # 22 execution
Handling NULL Values
23. Get information about the parts and their weights, and if the part has no weight, then write -100 instead of NULL
Figure: 5.23. Results of query # 23
EXISTS existence function
24. Select the name and material code of only those parts that have ever been supplied
Figure: 5.24. Results of query # 24
25. Choose the names of those materials from which no part is made
Figure: 5.25. Results of query # 25
Subqueries
26. Get information about the most recent (by date) delivery
Figure: 5.26. Results of query # 26
27. Receive all shipments of parts from material code 2
Figure: 5.27. Results of execution of query # 27
28. For each supplier, get information about the very first (by date) delivery
Figure: 5.28. Results of execution of query # 28
29. For each supplier get his name and the date of the last order
Figure: 5.29. Results of query # 29 execution
JOIN tables
30. Get a table of the form: the name of the part, the name of the material from which this part is made
Figure: 5.30. Results of query # 30
31. Get a table of the form: supplier, part name, quantity and delivery date for parts with weight set
Figure: 5.31. Results of query # 31
32. Select all information about those parts that have ever been supplied
Figure: 5.32. Results of query # 32
Figure: 5.33. Results of query # 33 execution
34. Select the names of materials and parts made from them
Figure: 5.34. Results of query # 34
35. Get all data about suppliers, supplies and parts
Figure: 5.35. Results of execution of query # 35
Joining UNION tables
36. Get a table of names and weights of parts, and the last row of the table should contain totals in the form of the total weight of all parts
Figure: 5.36. Results of execution 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
Figure: 5.37. Results of query # 37 execution
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 the 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 be 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 \u200b\u200bin the second in the same order.
Method number 2.
The second method, in my opinion, is a little simpler, 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 \u200b\u200bin the second brackets. The syntax for the second method is:
INSERT INTO table_name SET field_1 \u003d Value_1, field_2 \u003d 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 \u003d "Ivan", last_name \u003d "Dulin", registration_date \u003d "2012-06-14", country \u003d "1"; Now our plate contains the following data:
You may have 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 an age of 25 years. It might not be a good example to set 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. 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 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 have 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 the 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.
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, as we will need them to study the SELECT statement (selecting information from the database), which we will consider in the next (fourth) article on studying SQL queries.
In addition to the SELECT statement discussed earlier, Data Manipulation Language (DML) contains three other statements: INSERT, UPDATE, and DELETE. Like the SELECT statement, these three statements operate on either tables or views. This article covers the INSERT statement and the other two statements are discussed in the next article.
INSERT statement inserts rows (or parts of rows) into a table. There are two different forms of this instruction:
INSERT tab_name [(col_list)] DEFAULT VALUES | VALUES ((DEFAULT | NULL | expression) [, ... n]) INSERT INTO tab_name | view_name [(col_list)] (select_statement | execute_statement) Syntax Conventions
The first form of the statement allows you to insert one row (or part of it) into the table. And the second form of INSERT allows you to insert into a table the result set of a SELECT statement or a stored procedure executed through an EXECUTE statement. The stored procedure must return data to be inserted into the table. When used with an INSERT statement, a SELECT statement can select values \u200b\u200bfrom a different or from the same table into which the data is inserted, as long as the data types of the corresponding columns are compatible.
For both forms, the data type of each inserted value must be compatible with the data type of the corresponding table column. All string and temporary data must be enclosed in quotation marks; numeric values \u200b\u200bdo not need to be quoted.
Insert one line
For both forms of the INSERT statement, an explicit list of columns is optional. Not listing the columns is equivalent to specifying all the columns in the table.
DEFAULT VALUES parameter inserts default values \u200b\u200bfor all columns. Columns with the TIMESTAMP data type or IDENTITY property are inserted by default with values \u200b\u200bthat are automatically generated by the system. For columns of other data types, the corresponding non-zero default is inserted, if any, or NULL, otherwise. If NULL values \u200b\u200bare not allowed for a column and no default value has been defined for the column, the INSERT statement will fail and a message is displayed.
The example below shows inserting rows into the Employee table of the SampleDb database, demonstrating the use of an INSERT statement to insert a small amount of data into the database:
USE SampleDb; INSERT INTO Employee VALUES (34990, "Andrey", "Batonov", "d1"); INSERT INTO Employee VALUES (38640, "Alexey", "Vasin", "d3");
There are two different ways to insert values \u200b\u200binto a new row. The INSERT statement in the example below explicitly uses the NULL keyword and inserts a NULL value into the appropriate column:
USE SampleDb; INSERT INTO Employee VALUES (34991, "Andrey", "Batonov", NULL);
To insert values \u200b\u200binto some (but not all) of the columns in a table, you usually need to explicitly specify those columns. Unspecified columns must either allow null values \u200b\u200bor have a default value defined.
USE SampleDb; INSERT INTO Employee (Id, FirstName, LastName) VALUES (34992, "Andrey", "Batonov");
The previous two examples are equivalent. In the Employee table, the only column that allows NULLs is DepartmentNumber, and for all other columns this value was denied by the NOT NULL clause in the CREATE TABLE statement.
Order of values \u200b\u200bin vALUES offer INSERT statements may differ from the order specified in the CREATE TABLE statement. In such a case, their order must match the order in which the corresponding columns are listed in the column list. Below is an example of inserting data in a different order from the original:
USE SampleDb; INSERT INTO Employee (DepartamentNumber, LastName, Id, FirstName) VALUES ("d1", "Batonov", 34993, "Andrey");
Insert multiple lines
The second form of the INSERT statement inserts into the table one or more rows selected by the subquery. The example below shows how to insert rows into a table using the second form of the INSERT statement. In this case, a query is performed to select the numbers and names of departments located in Moscow, and the resulting result set is loaded into a new table created earlier.
The new table MoscowDepartment created in the example above has the same columns as the existing Department table, except for the missing Location column. The subquery in the INSERT statement selects all rows in the Department table for which the Location column is Moscow, which is then inserted into the new table that was created at the beginning of the query.
The example below shows another way to insert rows into a table using the second form of the INSERT statement. In this case, a request is made to select personnel numbers, project numbers and project start dates for all employees with the "Manager" position who are working on the p2 project, followed by loading the resulting result set into a new table created at the beginning of the query:
USE SampleDb; CREATE TABLE ManagerTeam (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, EnterDate DATE); INSERT INTO ManagerTeam (EmpId, ProjectNumber, EnterDate) SELECT EmpId, ProjectNumber, EnterDate FROM Works_on WHERE Job \u003d "Manager";
Before inserting rows using the INSERT statement, the MoscowDepartment and ManagerTeam tables (in the examples above) were empty. If the table already existed and contained rows with data, then new rows would be added to it.
Change requests are used to add (INSERT INTO), delete (DELETE), and update (UPDATE) records in tables.
INSERT INTO command
INSERT INTO command can be used to add one or more records to the end of a table.
The syntax for the INSERT INTO command is as follows:
INSERT INTO Table_Name [(Field_name)] VALUES (Data_values);
For example, put in the "Groups" table a record containing data (1, BF-16a, 1, 1) in the appropriate columns (Group Code, Name, Course, Semester).
To do this, a request in the format:
INSERT INTO Groups (Group Code, Name, Course, Semester) VALUES (1, BF-16a, 1, 1);
enter 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 request with the name "Add_1_records". As a result, an unrelated object - "Add_1_records" will appear in the "Navigation area" (Fig. 1.).
Figure: 1.
After saving the query, you must execute the query by clicking on the Run icon. As a result of executing the command "Add_1_records", the first record will appear in an empty table (Fig. 2.)
Figure: 2.
UPDATE command
The UPDATE command is designed to update existing records in table columns, which allows you to update values \u200b\u200bin specified table columns.
The syntax for the UPDATE command is as follows:
UPDATE Table_Name SET Column_name \u003d New_Value WHERE Select_condition;
The SET clause specifies the names of the columns to change. The WHERE clause is optional, but it can only point to the rows of 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 the existing entry for the first key id field in the Name column of the Groups table.
Request in the format:
UPDATE Groups SET Name \u003d "BF-16b" WHERE ID \u003d 1;
Save the query with the name "Change_record_1". As a result, an object “Change_record_1” will appear in the “Navigation area” (Fig. 3.).
Figure: 3.
After saving the query, you must execute the 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 parameter ID \u003d 1 and click OK. After these actions in the "Groups" table, the record in the "Name" field will change from BF-16a to BF-16b (Fig. 4.).
Figure: 4.
DELETE command
The DELETE command is designed to delete records from a table.
Format of request to delete records:
DELETE FROM Table_Name WHERE Criteria;
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 record for the first key id field in all columns (fields) of the "Groups" table.
To do this, a request in the format:
DELETE FROM Groups WHERE ID \u003d 1;
create a query on the 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.).
Figure: five.
After saving the query, you must execute the query by clicking on the Run icon. As a result of executing the command "Delete_record_1", a dialog box will appear, in which you must enter the value of the parameter ID \u003d 1 and click OK. After these actions, the "Confirm the deletion of records" dialog box will open. After confirmation in the "Groups" table, the data in all fields will be deleted, i.e. the entry has been deleted (Fig. 6.).
Figure: 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 \u200b\u200bin the required fields with Null. If you need to drop a table as a whole, you should use the DROP TABLE operator (operator syntax: DROP TABLE Table_Name;).
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.).
Figure: 7.
The SQL statement INSERT INTO and INSERT SELECT are used to insert new rows into a table. There are two ways to use instructions:
- Values \u200b\u200bOnly: The first method is for specifying only the data values \u200b\u200bto be inserted without column names.
Syntax:
INSERT INTO table_name VALUES (value1, value2, value3, ...); table_name: the name of the table. value1, value2, ..: values \u200b\u200bof first column, second column, ... for new record
- Column names and values: The second method specifies the column names and row values \u200b\u200bto insert:
Syntax:
INSERT INTO table_name (column1, column2, column3, ..) VALUES (value1, value2, value3, ...); table_name: the name of the table. column1: name of first column, second column ... value1, value2, ..: values \u200b\u200bof first column, second column, ... for new record
Requests:
Method 1 ( inserting only values):
INSERT INTO Student VALUES ("5", "HARSH", "WEST BENGAL", "8759770477", "19");
Result:
After using INSERT INTO SELECT, the Student table will now look like this:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 5 | HARSH | WEST BENGAL | 8759770477 | 19 |
Method 2 ( inserting values \u200b\u200binto specified columns only):
INSERT INTO Student (ROLL_NO, NAME, Age) VALUES ("5", "PRATIK", "19");
Result:
The Student table will now look like this:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 5 | PRATIK | null | null | 19 |
Note that columns with no values \u200b\u200bare set to null.
Using SELECT in an INSERT INTO statement
You can use the MySQL INSERT SELECT statement to copy rows from one table and insert them into another.
Using this operator is similar to using INSERT INTO. The difference is that a SELECT statement is used to select data from another table. Following are the different ways to use INSERT INTO SELECT:
- Insert all columns of a table: You can copy all table data and paste it into another table.
Syntax:
INSERT INTO first_table SELECT * FROM second_table; first_table: name of the first table. second_table: name of the second table.
We used a SELECT statement to copy data from one table and an INSERT INTO statement to insert it into another.
- Insert individual table columns... You can only copy the columns of a table that you want to paste into another table.
Syntax:
INSERT INTO first_table (column_names1) SELECT column_names2 FROM second_table; first_table: name of the first table. second_table: name of the second table. column_names1: column names, separated by comma (,) for table 1.column_names2: column names, separated by comma (,) for table 2.
We used a SELECT statement to copy data only from the selected columns of the second table and a MySQL INSERT INTO SELECT statement to insert it into the first table.
- Copying specific rows from a table... You can copy specific rows from a table for later insertion into another table using a WHERE clause with a SELECT statement. In this case, you need to use the corresponding condition in the WHERE.
Syntax:
Table 2: LateralStudent
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 7 | SOUVIK | DUMDUM | 9876543210 | 18 |
| 8 | NIRAJ | NOIDA | 9786543210 | 19 |
| 9 | SOMESH | ROHTAK | 9687543210 | 20 |
Requests:
Method 1 ( insert all rows and columns):
INSERT INTO Student SELECT * FROM LateralStudent;
Result:
This query will insert all data from the LateralStudent table into the Student table. After applying SQL INSERT INTO SELECT, the Student table will look like this:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 7 | SOUVIK | DUMDUM | 9876543210 | 18 |
| 8 | NIRAJ | NOIDA | 9786543210 | 19 |
| 9 | SOMESH | ROHTAK | 9687543210 | 20 |
Method 2 ( inserting individual columns):
INSERT INTO Student (ROLL_NO, NAME, Age) SELECT ROLL_NO, NAME, Age FROM LateralStudent;
Result:
This query will insert data from the ROLL_NO, NAME, and Age columns of the LateralStudent table into the Student table. The rest of the columns in the Student table will be set to null. After applying SQL INSERT SELECT, the table will look like this:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 7 | SOUVIK | Null | null | 18 |
| 8 | NIRAJ | Null | null | 19 |
| 9 | SOMESH | Null | null | 20 |
- Selecting specific rows for insertion:
Result:
This query will only select the first row from the LateralStudent table to insert into the Student table. After applying INSERT SELECT, the table will look like this:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 7 | SOUVIK | DUMDUM | 9876543210 | 18 |
This publication is a translation of the article "SQL INSERT INTO Statement" prepared by the friendly project team
