Request a change for additional SQL instructions. Apply SQL queries to a MySQL database Adding values ​​to a sql table

Zmіst statі
1. The simplest way to write MySQL
2. Just SELECT (select) and enter
3. Just INSERT (new entry)
4. Just UPDATE (rewrite, append)
5. Just DELETE (record visibility)
6. Just DROP (view the table)
7. Folder MySQL request
8. MySQL and PHP changes

1. Simplest SQL queries

SHOW databases;
2. View a list of ALL tables in the database base_name.

SHOW tables in base_name;

2. Simply SELECT (vibrate) to the MySQL database

1. Selects ALL data from the tbl_name table.

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

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

SELECT * FROM tbl_name LIMIT 2,3;
4. Select (SELECT) ALL (*) records from (FROM) table tbl_name and sort them (ORDER BY) by the id field 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 the id field in reverse order.

SELECT * FROM tbl_name ORDER BY id DESC;
6. Vibrate ( SELECT) ALL (*) records from ( FROM) tables usersі sorts їх ( ORDER BY) field id in order of growth, limit ( LIMIT) first 5 entries.

SELECT * FROM users ORDER BY id LIMIT 5;
7. Selects all records from the table users, de field fname confirms the meaning Gena.

SELECT * FROM users WHERE fname="Gena";
8. Selects all records from the table users, de value of the field fname starts with Ge.

SELECT * FROM users WHERE fname LIKE "Ge%";
9. Selects all records from the table users, de fname will end with na, and ordinal entries in order of increasing 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. It is possible that your data table has a region. So, if you want to display ONLY a list of values ​​that are narrowed (for example, Russia was not displayed 20 times, but only one), then choose DISTINCT. You see, it’s so important that Russia, Ukraine, Belarus are repeated. In this manner, from the table users speakers country ALL UNIQUE values ​​will be displayed

SELECT DISTINCT country FROM users;
12. Selects all row data from the table users de age The values ​​are 18.19 and 21.

SELECT * FROM users WHERE age IN (18,19,21);
13. Select the MAXIMUM value age in the table users. Then your table has the greatest value age(from English Vіk) is equal to 55, then the result will be 55.

SELECT max(age) FROM users;
14. Viber data from table users in the fields nameі age DE age assumes the lowest value.

SELECT name, min(age) FROM users;
15. Viber data from table users field name DE id NOT EQUAL 2.

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

3. Simply INSERT (new entry)

1. Rob new entry in the table users, in the field name inserts Sergiy, and in the field age inserts 25. In this way, a new row with these values ​​is added to the table. If there are more columns, then the stench will be eliminated either when they are empty or when they are installed after cleaning.

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

4. Simply UPDATE the MySQL database

1. At the table users age turns 18.

UPDATE users SET age = "18" WHERE id = "3";
2. All the same as in the first entry, I will simply enter the indications of the syntax, where two or more fields will be overwritten.
In the table users DE id is equal to 3 field values age turns 18 and country Russia.

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

5. Simply DELETE (record data) to the MySQL database

1. Removes a row from the table users DE id more than 10.

DELETE FROM users WHERE id = "10";

6. Simply DROP (delete table) to the MySQL database

1. Views the entire table tbl_name.

DROP TABLE tbl_name;

7. Easy to write to a MySQL database

SELECT id,name,country FROM users,admins WHERE TO_DAYS(NOW()) - TO_DAYS(registration_date)<= 14 AND activation != "0" ORDER BY registration_date DESC;
Denmark folding powered by VIBIRAI columns id,name,country AT THE TABLES users,admins DE registration_date(date) not older 14 days I activation NOT GOOD 0 , Sortuvati for registration_date in the turn order (new to the cob).

UPDATE users SET age = "18+" WHERE age = (SELECT age FROM users WHERE male = "man");
Guidance is greater than the butt of the so-called I'll ask you to ask at SQL. Update the age of the middle class to 18+, and become a human. I don’t recommend such options. I’ll tell you in particular, it’s better to make a bunch of okremikh - the stinks will be cleared up sooner.

8. Request MySQL and PHP databases

1. Selects all records from the table users, de field fname confirms the value of the change $name.

SELECT * FROM users WHERE fname="$name";
2. At the table users DE id is equal to 3 field values age The value of the variable $age changes.

UPDATE users SET age = "$age" WHERE id = "3";

Respect! If you like another butt, then write food in the comments!

The change request is used to add (INSERT INTO), delete (DELETE) and update (UPDATE) records in tables.

INSERT INTO command

The INSERT INTO command can be used to add one or more records to the end of the table.

The syntax of the INSERT INTO command looks like this:
INSERT INTO Table_Name [(Field_Name)] VALUES (Data_Values);

For example, let's place a record in the "Groups" table to include the data (1, BF-16a, 1, 1) of the following items (Group Code, Title, Course, Semester).

For whom do I ask the format:
INSERT INTO Group (Group Code, Name, Course, Semester) VALUES (1, BF-16a, 1, 1);
entered on the query form by selecting the "Query Designer" command on the "Creation" tab in the "SQL Mode" mode from the View menu.

Save "Add_1_records" with them. As a result, an unrelated object will appear in the “Transition Area” - “Add_1_records” (Fig. 1.).

Rice. 1.

After saving, you must click on the “Vicon” icon. As a result of issuing the command “Add_1_record”, the first record will appear in the empty table (Small 2)

Rice. 2.

UPDATE command

The UPDATE command is used to change existing entries in table columns, which allows you to update the values ​​of specified table columns.

The syntax of the UPDATE command looks like this:
UPDATE Table_Name SET Table_Name = New_Value WHERE Mind_Select;

The SET clause specifies the names of the participants whose data needs to be changed. The WHERE statement is not compulsory, but instead you can specify as many rows of columns (tables) as you need to update.

Let's change the record of the "Groups" table of the sql_training_st.mdb database.

For example, we can already change the existing record of the first key field id in the “Name” column of the “Groups” table.

Request format:
UPDATE Group SET Name = "BF-16b" WHERE ID = 1;

Save the name "Change_record_1". As a result, an object will appear in the transition area - Change_record_1 (Fig. 3.).

Rice. 3.

After saving, you must click on the “Vicon” icon. As a result of entering the command “Change_record_1”, a dialog will appear in which you need to enter the value ID = 1 and click the OK button. After these actions, the entry in the “Name” field in the “Groups” table will change from BF-16a to BF-16b (Fig. 4.).

Rice. 4.

DELETE command

The DELETE command is assigned to a number of records from the table.

Format for the selected entries:
DELETE FROM Table_Name WHERE Mind_Selection;

Let's change the record of the "Groups" table of the sql_training_st.mdb database.

For example, we can see the same record of the first key field id in all columns (fields) of the “Groups” table.

For whom do I ask the format:
DELETE FROM Groups WHERE ID=1;
You can create a query on a form by selecting the "Query Designer" command on the "Creation" tab in the "SQL Mode" mode from the View menu.

Save the name "Visible_entry_1". As a result, an object will appear in the transition area - View_record_1 (Small 5.).

Rice. 5.

After saving, you must click on the “Vicon” icon. As a result of selecting the command “View_record_1”, a dialog box will appear in which you need to enter the value ID=1 and press the OK button. After this, the dialog “Confirm deleted entries” will open. After confirming the "Groups" table, data will be visible in all fields, then. entry was seen (Mal. 6.).

Rice. 6.

Please note that to delete data from specific fields of the table, you only need to use the UPDATE command and replace the values ​​in the fields that require Null. If you need to view the table as a whole, use the DROP TABLE operator (operator syntax: DROP TABLE Table_Name;).

The first record in the “Groups” table, after selecting the command “Add_1_record”, the result is the first record (Small 7.).

Rice. 7.

In addition to the SELECT statement discussed earlier, the manipulation of DML (Data Manipulation Language) data includes three other statements: INSERT, UPDATE, and DELETE. Similar to the SELECT statement, these three statements operate on either tables or statements. This article looks at the INSERT statement, and the other two statements look at the current statement.

INSERT instruction inserts rows (or parts of rows) to the 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) Keep the syntax nice

The first form of the instruction allows you to insert one row (or parts) into the table. Another form of the INSERT statement allows you to insert into a table the result set of the SELECT statement or procedures that are stored behind an additional EXECUTE statement. The procedure that is saved is to rotate the data to insert into the table. Izhstructi Select, stalling with an Instroy Insert, you can see the valuation of the sin -nibe, the dignity is inserted into the yak, for improving the Sumy type of Dani Vidpovydny Stovptziv.

For both forms, the type of skin data that is inserted depends on the type of data in the subsection of the table. All strings and time data are laid out at the foot; There is no need to place numerical values ​​in the tabs.

Inserting one row

For both forms of the INSERT statement, explicitly inserting a list of clauses is not obligatory. The list of sleepers is equal to the number of all sleepers in the table.

DEFAULT VALUES parameter inserts standard values ​​for all items. In combination with the TIMESTAMP data type or the IDENTITY data type, values ​​are inserted automatically created by the system. For other types of data, non-zero values ​​are inserted after the entries, such as this, or NULL in the protimal form. Since a NULL value is not allowed for insertion and no value has been assigned to it, the resulting INSERT statement ends with a message and a prompt message is displayed.

The example below shows the insertion of rows into the Employee table of the SampleDb database, demonstrating the appropriate INSERT statement for inserting a small amount of data into the database:

USE SampleDb; INSERT INTO Employee VALUES (34990, "Andriy", "Batonov", "D1"); INSERT INTO Employee VALUES (38640, "Olexiy", "Vasin", "d3");

There are two different ways to insert values ​​into a new row. The INSERT statement in the application below explicitly uses the NULL keyword and inserts NULL values ​​into the subtype:

USE SampleDb; INSERT INTO Employee VALUES (34991, "Andriy", "Batonov", NULL);

To insert values ​​into some (or not all) columns of the table, you must explicitly specify the columns. Unassigned columns may either allow NULL values, or they may have a default value assigned to them.

USE SampleDb; INSERT INTO Employee(Id, FirstName, LastName) VALUES (34992, "Andriy", "Batonov");

The front two butts are equal. The only column in the Employee table that allows NULL values ​​is the DepartmentNumber column, and for other columns this value was protected by the NOT NULL clause in the CREATE TABLE statement.

The order of magnitude proposition VALUES INSERT statements can be arranged in the order specified in the CREATE TABLE statement. In this case, the current order must coincide with the order in which the types of items are reinsuranced in the list of items. Below the indications, insert data in the order that appears as the output:

USE SampleDb; INSERT INTO Employee(DepartamentNumber, LastName, Id, FirstName) VALUES ("d1", "Batonov", 34993, "Andriy");

Inserting multiple rows

Another form of the INSERT statement inserts into a table one or more rows selected by a query. The example below shows the insertion of rows into a table using another form of the INSERT statement. In this section, you will be asked to select the numbers and names of divisions, issued from Moscow, and then select the resulting set into the new table created earlier.

The new table MoscowDepartment, which is created in the application, has the same columns as the original Department table, except for the additional Location column. The subscript in the INSERT statement selects all rows from the Department table, for which the value of the Location column is the same as "Moscow", which is then inserted into a new table.

The example below shows another way to insert rows into a table using another form of the INSERT statement. In this phase, you will be asked to select personnel numbers, project numbers and dates to start working on the project for all employees with the “Manager” position, which will work on the p2 project with the upcoming selection of the resulting set to a new table, what happens on the cob with this question:

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 following the additional INSERT statement, the MoscowDepartment and ManagerTeam tables (in the examples above) were empty. Since the table had already created and added rows to the data, new rows were added to it.

View of deleted entries

To delete records from the table, the DELETE statement is used:

DELETE FROM tablename WHERE mind;

This operator removes records from the designated table (and not outside the column values) that represent the values ​​of the mind. Umova is a whole logical expression, various designs of which were examined in previous laboratory studies.

The next query displays records from the Customer table, where the value of the LName column is equal to "Ivaniv":

DELETE FROM Customer

WHERE LName = "Ivaniv"

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

In the WHERE statement you can apply for data selection (SELECT statement). Additions in the DELETE statement work the same way as in the SELECT statement. The next query removes all clients from the Moscow location, whereby the unique location identifier is followed by an additional query.

DELETE FROM Customer

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

Transact-SQL extends standard SQL by allowing you to add another FROM clause to a DELETE statement. This extension, in which the join is specified, can be used instead of the nested clause in the WHERE clause to insert the rows that are being deleted. It allows you to specify data from another FROM and delete subordinate rows from the table from the first FROM. Zokrema, the front row can be copied in this manner

DELETE FROM Customer

FROM Customer k INNER JOIN

The operation of deleting records from a table is not safe because it is associated with the risk of irrevocable data loss due to semantic (rather than syntactic) errors during the formulation of the SQL virus. To avoid any inconsistencies, before deleting records, it is recommended that you first select the selected entry to see which records will be deleted. So, for example, before selecting the previously looked at question, do not set the current search to be selected.

SELECT *

FROM Customer k INNER JOIN

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

To delete all records from a table, it is enough to use the DELETE statement without the WHERE keyword. In this case, the table itself is saved with all the necessary columns and is ready until new records are inserted. For example, the next entry displays records about all products.

DELETE FROM Product

Plant for independent work: Formulate my SQL to record all the orders that are in the warehouse for goods (that is, all empty orders).

This article is assigned to the top table of the database with data, so you can use the SQL command to insert new records. It should be said that a record to a table can be created in two ways:

Method No. 1.
Try making an entry about the new country in the countries table. The syntax for adding will be:
INSERT INTO Table_name (field_1, field_2, ...) VALUES (Value_1, Value_2, ...); Coming from 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. Here everything is clear and simple, it’s important to wonder what fields you indicate in the first arms, and in that order write down the values ​​in the others.

Method No. 2.
Another method, in my opinion, is a little simpler, as long as you add what you need to each field. Believe me, since in the table of anonymous characters, it is easy to confuse or overlook the order of fields in the first ones, and the order of values ​​in other arms. The syntax for the other method is:
INSERT INTO Table_name SET field_1 = Value_1, field_2 = Value_2, ... ; Let's add all the information to the plate on this application persons, there are more fields there, and there is clearly an advantage to another method:
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 may have noticed that we didn’t specify a value for age, but it turned up as a replacement... That’s right - for this field we set a value for age like 25. So, now our Ivan Dulin is listed in the database as 25 years old. It is possible that it is not the most convenient way to work on the importance of cleaning for the field of the century, but it is possible to install such powers to such fields, such as, for example, the rating of the website or the number of views of the story. The initial value is set to 0.

Next, pay attention to the MySQL date format: YYYY-MM-DD. If this is not completed, your entries simply will not be entered into the table.

Like Bachimo, the profession field is left as NULL, and the value is empty. For which field we set the authority “not required until filled (NULL)”.

As an addition, let’s look at this command:
LOAD DATA LOCAL INFILE "D:\zapros.txt" INTO TABLE persons; How do you respect what they gave us all at once?! And we did the same: we added data to the table persons z file requests.txt, which is located on drive D. The information in the file has the following structure:

1. Kozhen new entry is guilty of peeing from a new row
2. Data of guilt are indicated for all fields. As you can note, we entered the NULL value for the id field, and we also have it auto-incrementing, so MySQL itself entered the required value.
3. The fields are supplemented one by one with a tab character (Tab key).
4. The information entered must match the data type of the specific field. For example, a date in the format PPRR-MM-DD, a whole number for an integer, etc.

Profession table:

People table: