T-SQL Basics. DML. Examples of SQL queries to the MySQL database Adding entire rows
In previous sections, we looked at the work of retrieving data from pre-created tables. Now it’s time to figure out how we can create/delete tables, add new records and delete old ones. For these purposes in SQL There are operators such as: CREATE- creates a table, ALTER- changes the table structure, DROP- deletes a table or field, INSERT- adds data to the table. Let's start getting acquainted with this group of operators from the operator INSERT.
1. Adding entire lines
As the name suggests, the operator INSERT used to insert (append) rows to a database table. Adding can be done in several ways:
- - add one full line
- - add part of a line
- - add query results.
So, to add a new row to a table, we need to specify the table name, list the column names and specify the value for each column using the construct INSERT INTO table_name (field1, field2 ...) VALUES (value1, value2...). Let's look at an example.
INSERT INTO Sellers (ID, Address, City, Seller_name, Country) VALUES("6", "1st Street", "Los Angeles", "Harry Monroe", "USA")
You can also change the order of column names, but at the same time you need to change the order of the values in the parameter VALUES.
2. Adding part of the lines
In the previous example, when using the operator INSERT we have explicitly marked the table column names. Using this syntax, we can skip some columns. This means that you enter values for some columns but do not provide them for others. For example:
INSERT INTO Sellers (ID, City, Seller_name) VALUES("6", "Los Angeles", "Harry Monroe")
In this example, we did not specify a value for two columns Address And Country. You can exclude some columns from the statement INSERT INTO, if this allows the table definition. In this case, one of the conditions must be met: this column is defined as valid NULL(absence of any value) or the specified default value in the table definition. This means that if no value is specified, the default value will be used. If you are missing a column from a table that does not allow values to appear in its rows NULL and does not have a default value defined, the DBMS will generate an error message and this line will not be added.
3. Adding selected data
In the previous example, we inserted data into tables by entering them manually in the query. However, the operator INSERT INTO allows us to automate this process if we want to insert data from another table. For this purpose in SQL there is such a construction as INSERT INTO ... SELECT .... This design allows you to simultaneously select data from one table and insert it into another. Let's assume we have another table Sellers_EU with a list of sellers of our goods in Europe and we need to add them to the general table Sellers. The structure of these tables is the same (the same number of columns and the same names), but the data is different. To do this, we can write the following query:
INSERT INTO Sellers (ID, Address, City, Seller_name, Country) SELECTID, Address, City, Seller_name, Country FROM Sellers_EU
You need to pay attention so that the values of internal keys are not repeated (field ID), otherwise an error will occur. Operator SELECT may also include suggestions WHERE to filter data. It should also be noted that the DBMS does not pay attention to the names of the columns contained in the statement SELECT, only the order in which they are arranged is important to her. Therefore, the data in the first specified column that was selected due to SELECT, will be filled in the first column of the table in any case Sellers, specified after the operator INSERT INTO, regardless of the field name.
4. Copying data from one table to another
Often when working with databases, there is a need to create copies of any tables for the purpose of backup or modification. To make a full copy of a table, SQL provides a separate statement SELECT INTO. For example, we need to create a copy of the table Sellers, you will need to write the request as follows:
SELECT * INTO Sellers_new FROM Sellers
Unlike the previous design INSERT INTO ... SELECT ... When data is added to an existing table, the design copies the data to the new table. You can also say that the first construct imports data, and the second exports. When using the design SELECT ... INTO ... FROM ... The following should be considered:
- - you can use any sentences in the operator SELECT, such as GROUP BY And HAVING
- - you can use a join to add data from multiple tables
- - data can only be added to one table, no matter how many tables it was taken from.
Last update: 07/13/2017
To add data, use the INSERT command, which has the following formal syntax:
INSERT table_name [(column_list)] VALUES (value1, value2, ... valueN)
First comes the INSERT INTO expression, then in parentheses you can specify a comma-separated list of columns to which data should be added, and at the end, after the word VALUES, the values to be added for the columns are listed in parentheses.
For example, suppose the following database was previously created:
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 execution in SQL Server Management Studio, the message "1 row(s) affected" should appear in the message field:
It is worth considering 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 specified for it, the value of this column is automatically generated and 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 to 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. Moreover, now the values are transmitted in the order of the columns:
ProductName: "iPhone 6S"
Manufacturer: "Apple"
For unspecified columns (in this case ProductCount), a default value will be added if the DEFAULT attribute is specified, or a NULL value. However, unspecified columns must be nullable or have a DEFAULT attribute.
We can also add several 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 column should have a default value using the DEFAULT keyword, or a NULL value:
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES ("Mi6", "Xiaomi", DEFAULT, 28000)
In this case, the default value for the ProductCount column will be used (if it is set, if it is not, then NULL).
If all columns have a DEFAULT attribute that specifies a default value, or are nullable, you can insert default values for all columns:
INSERT INTO Products DEFAULT VALUES
But if we take the Products table, then such a command will fail with an error, since several fields do not have the DEFAULT attribute and at the same time do not allow the NULL value.
Deleting entries
To remove records from a table, use the DELETE operator:
DELETE FROM Tablename WHERE condition;
This operator removes records (not individual column values) from the specified table that satisfy the specified condition. A condition is a logical expression, the various constructions of which have been discussed in previous labs.
The following query deletes records from the Customer table where 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 statement may contain a subquery for selecting data (SELECT statement). Subqueries in a DELETE statement work exactly the same as in a 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 you to use an additional FROM clause in a DELETE statement. This extension, which specifies a join, can be used in place of a subquery in the WHERE clause to specify the rows to be deleted. It allows you to specify data from the second FROM and remove 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 syntactic) errors in the formulation of the SQL expression. To avoid any hassle, 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 run the corresponding select 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 deletes records for all products.
DELETE FROM Product
Assignment for independent work: Formulate a query in SQL to delete all orders that do not contain a single product (i.e., all empty orders).
In addition to the SELECT statement discussed earlier, the 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 discusses 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 instruction allows you to insert one row (or part of it) into the table. And the second form of the INSERT statement allows you to insert into a table the result set of a SELECT statement or a stored procedure executed by 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 from a different or the same table into which the data is being 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 quotes; Numeric values do not need to be enclosed in quotation marks.
Inserting a single row
For both forms of the INSERT statement, specifying the column list explicitly is optional. Not listing columns is the same as specifying all columns in the table.
DEFAULT VALUES parameter inserts default values for all columns. Columns with the TIMESTAMP data type or IDENTITY property are inserted by default with values that are automatically generated by the system. For columns of other data types, the corresponding non-null default value is inserted if available, or NULL otherwise. If a column does not allow NULL values and does not have a default value defined, the INSERT statement fails and a message is displayed.
The example below inserts rows into the Employee table in 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 into a new row. The INSERT statement in the example below explicitly uses the NULL keyword and inserts a NULL value into the corresponding column:
USE SampleDb; INSERT INTO Employee VALUES (34991, "Andrey", "Batonov", NULL);
To insert values into some (but not all) columns of a table, you usually need to explicitly specify those columns. Unspecified columns must either allow NULL values or 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 NULL values is the DepartmentNumber column, and all other columns were disabled by the NOT NULL clause in the CREATE TABLE statement.
Order of values in VALUES offer INSERT statements may differ from the order specified in the CREATE TABLE statement. In this 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");
Inserting multiple rows
The second form of the INSERT statement inserts one or more rows selected by a subquery into the table. 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 executed 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 MoscowDepartment table 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 value is "Moscow", which are then inserted into the new table 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 query is executed to select personnel numbers, project numbers, and project start dates for all employees with the position “Manager” who work on project p2 and then load 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 = "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.
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 recording in a table can be done in two ways:
Method number 1.
Let's try to add an entry about the new country to the countries table. The adding syntax 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 will be like this:
INSERT INTO countries (country_name, acronym_name) VALUES ("Russia", "RU"); This is how we added an entry about the country “Russia” to our table. Everything here should be clear and simple, the main thing is to carefully look at which fields you indicate in the first brackets, and write the values in the same order in the second.
Method No. 2.
The second method, in my opinion, is a little simpler, since you see what you assign and to which field. Believe me, if a 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 of 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 table. 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 contains the following data:
You probably noticed that we did not specify a value for age, but it turned out to be filled in... Everything is correct - 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 may not be the best example to set a default value for the age field, but you can set such properties to fields such as, for example, the site 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 entries simply will not be entered into the table.
As you can see, the profession field is filled in as NULL, this is an empty value. For this field.
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: added data to the table persons from file requests.txt, which is located on drive D. The information in the file should have the following structure:
The file data structure must meet the following requirements:
- Each new entry must be described on a new line
- Data must be specified for absolutely all fields. As you can see, we specified the value NULL for the id field, because it is auto-incrementing, so MySQL itself will enter the required value.
- Fields are separated from each other by a tab character (Tab key).
- The information you enter must match the data types of the specific field. That is, for example, a date in the format YYYY-MM-DD, an integer for integer, etc.
Profession 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 entries, since we will need them to study the SELECT statement (selecting information from the database), which we will look at in the next (fourth) article on studying SQL queries.
