Examples of SQL queries to the MySQL database. Examples of SQL queries to MySQL database Parameters or arguments

11.05.2021

Graphic arts

This MySQL tutorial explains how to use the MySQL UPDATE statement with syntax and examples.

Syntax

In its simplest form, the syntax for the UPDATE statement when updating one table in MySQL is:

UPDATE table SET column1 = expression1, column2 = expression2, ... ;

However, the full syntax for the MySQL UPDATE statement when updating one table is:

UPDATE [ LOW_PRIORITY ] [ IGNORE ] table SET column1 = expression1, column2 = expression2, ... ] ;

The syntax for the UPDATE statement when updating one table with data from another table in MySQL is:

UPDATE table1 SET column1 = (SELECT expression1 FROM table2 WHERE conditions) ;

The syntax for the MySQL UPDATE statement when updating multiple tables is:

UPDATE table1, table2, ... SET column1 = expression1, column2 = expression2, ... WHERE table1.column = table2.column AND conditions;

Parameters or Arguments

LOW_PRIORITY If LOW_PRIORITY is provided, the update will be delayed until there are no processes reading from the table. LOW_PRIORITY may be used with MyISAM, MEMORY and MERGE tables that use table-level locking. IGNORE optional. If IGNORE is provided, all errors encountered during the update are ignored. If an update on a row would result in a violation of a primary key or unique index, the update on that row is not performed. column1, column2 expression1, expression2 column1, column2. So column1 expression1, column2 would be assigned the value of expression2, and so on. WHERE conditions Optional. The conditions that must be met for the update to execute. ORDER BY expression Optional. It may be used in combination with LIMIT to sort the records appropriately when limiting the number of records to be updated. LIMIT number_rows Optional. If LIMIT is provided, it controls the maximum number of records to update in the table. At most, the number of records specified by number_rows will be updated in the table.

Example - Update single column

Let's look at a very simple MySQL UPDATE query example.

UPDATE customers SET last_name = "Anderson" WHERE customer_id = 5000;

This MySQL UPDATE example would update the last_name to "Anderson" in the customers table where the customer_id is 5000.

Example - Update multiple columns

Let's look at a MySQL UPDATE example where you might want to update more than one column with a single UPDATE statement.

UPDATE customers SET state = "California", customer_rep = 32 WHERE customer_id > 100;

When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.

state to "California" and the customer_rep to 32 where the customer_id is greater than 100.

Example - Update table with data from another table

Let's look at an UPDATE example that shows how to update a table with data from another table in MySQL.

UPDATE customers
SET city = (SELECT city
FROM suppliers
WHERE suppliers.supplier_name = customers.customer_name)
WHERE customer_id > 2000;

This UPDATE example would update only the customers table for all records where the customer_id is greater than 2000. supplier_name from the suppliers table matches the customer_name from the customers table, the city from the suppliers table would be copied to the city field in the customers table.

Example - Update multiple tables

Let's look at a MySQL UPDATE example where you might want to perform an update that involves more than one table in a single UPDATE statement.

UPDATE customers, suppliers SET customers.city = suppliers.city WHERE customers.customer_id = suppliers.supplier_id;

This MySQL UPDATE statement example would update the city field in the customers table to the city from the suppliers table where the customer_id matches the supplier_id.

UPDATE Syntax

Single table syntax:
UPDATE shya_tabletsh
SET column_name1=expression1 [, name_ column2=expression2 ...]

Multi-table syntax:

UPDATE table_name [, table_name...] SET column_name 1=expression1 [,column_name2=expression2...]
The UPDATE statement updates the columns of existing table rows with new values. The SET construct lists the columns to be modified and the values to be assigned to them. If a WHERE clause is specified, it specifies which rows should be updated. Otherwise, all table rows are updated. If an ORDER BY clause is specified, the rows will be updated in the specified order. The LIMIT construct imposes a limit on the number of rows to be updated.
The UPDATE statement supports the following modifiers:

If the LOW_PRIORITY keyword is specified, UPDATE execution is deferred until all other clients have finished reading the table.
If the IGNORE keyword is specified, the update operation will not be aborted even if duplicate key errors occur. Rows that cause conflicts will not be updated.

If you are using columns from a table table_name in expressions, UPDATE uses the current value of the columns. For example, the following statement increments the value of the age column by one:
mysql> UPDATE persondata SET age=age+l;
Assignments in UPDATE are done from left to right. For example, the following statement doubles the value of the age column and then increments it: mysql> UPDATE persondata SET age=age*2, age=age+l;
If you set the value of a column to what it is, MySQL detects this and does not update.
If you update a column that has been declared NOT null to NULL, it is set to the default value appropriate for the particular data type and increments the warning counter by one. The default value is 0 for numeric columns, the empty string ("") for character columns, and null for datetime columns.
UPDATE returns the number of rows that have actually been updated. In MySQL 3.22 and later, the mysql_info() function of the C API API returns the number of rows that matched the query and were updated, as well as the number of warnings that occurred during the UPDATE.
Since MySQL 3.23, you can use limit number of lines to limit the scope of the UPDATE.
The LIMIT construct works like this:

Prior to MySQL 4.0.13, LIMIT was a limit on the number of rows processed. The operator quit as soon as he updated number of lines rows that matched the WHERE clause.
Starting with MySQL 4.0.13, limit is the string matching limit. The operator exits as soon as it finds number of lines rows that satisfy the WHERE clause, whether or not they were actually updated.

If the UPDATE statement includes an order by clause, then the rows are updated in the order specified by that construct. ORDER BY can be used starting with MySQL 4.0.0.
Starting with MySQL 4.0.0, it is also possible to perform UPDATE operations that operate on multiple tables at once:
UPDATE items,month SET items.price=month.price WHERE items.id-month. id/ This example demonstrates an inner join using the comma operator, but multi-table UPDATEs can use any type of join that is allowed in a SELECT statement, such as LEFT JOIN.
On a note!

You cannot use ORDER BY or LIMIT in multi-table UPDATE statements.

Prior to MySQL 4.0.18, it was necessary to have the UPDATE privilege on all tables used in a multi-table UPDATE, even if they were not actually updated. Starting with MySQL 4.0.18, these tables, whose columns are read-only but not updated, require only the SELECT privilege.
If you use a multi-table UPDATE statement on InnoDB tables that have foreign key constraints defined, the MySQL optimizer may process them in a different order than their parent-child relationship. In this case, the statement will fail and the transaction will be rolled back. Instead, update a single table and rely on the ON UPDATE property that the InnoDB engine provides for automatic update related tables. UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2, ...]

The UPDATE statement updates the columns with their new values in the rows of an existing table. The SET statement specifies which columns are to be modified and what values are to be set in them. The WHERE clause, if present, specifies which rows to update. Otherwise, all rows are updated. If an ORDER BY clause is specified, the rows will be updated in the order specified in it.

If the LOW_PRIORITY keyword is specified, execution of this UPDATE command is delayed until other clients have finished reading this table.

If the IGNORE keyword is specified, then the update command will not be aborted even if the update encounters a duplicate key error. Rows that cause conflicts will not be updated.

If a column from the specified expression is accessed by the tbl_name argument, then the UPDATE command uses its current value for that column. For example, the following command sets the age column to a value one greater than its current value:

mysql> UPDATE persondata SET age=age+1;

The UPDATE command assigns values from left to right. For example, the following command duplicates the age column, then increments it:

mysql> UPDATE persondata SET age=age*2, age=age+1;

If a column is set to its current value, then MySQL notices this and does not update it.

The UPDATE command returns the number of rows actually changed. In MySQL version 3.22 and later, the mysql_info() C API function returns the number of rows that were found and updated and the number of warnings that occurred when the UPDATE was executed.

In MySQL version 3.23, LIMIT # can be used to ensure that only a given number of rows have been modified.

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

1. The most simple SQL queries

1. Will list ALL databases.

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

SHOW tables in base_name;

2. Simple SELECT (select) queries to the MySQL database

SELECT- a query that selects already existing data from the database. You can specify certain selection options for selection. For example, the essence of the request in Russian sounds like this - SELECT such and such columns FROM such and such a 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. Displays 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 from 2.

SELECT * FROM tbl_name LIMIT 2,3;
4. Selects (SELECT) ALL (*) records from (FROM) table tbl_name and sorts 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. Selects ( SELECT) ALL (*) records from ( FROM) tables users and sort 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 the field fname corresponds to 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 with na, and sorts 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 your 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 repeating 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 the table users where age has the values 18,19 and 21.

SELECT * FROM users WHERE age IN (18,19,21);
13. Selects the MAXIMUM 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 result of the query will be 55.

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

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

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

3. Simple INSERT (new entry) 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 added to the table. If there are more columns, then the remaining ones will remain either empty or with default values.

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

4. Simple UPDATE queries against the MySQL database

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

1. Table users age becomes 18.

UPDATE users SET age = "18" WHERE id = "3";
2. Everything is the same as in the first request, it just shows the syntax of the request, where two or more fields are overwritten.
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 (remove record) queries against MySQL database

DELETE is a query that removes a row from a table.

1. Removes a row from a table users WHERE id equals 10.

DELETE FROM users WHERE id = "10";

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

DROP is a query that deletes a table.

1. Deletes the entire table tbl_name.

DROP TABLE tbl_name;

7. Complex MySQL database queries

Curious Queries That Even Experienced Users Can Use

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 SELECTs columns id,name,country IN TABLES users,admins WHERE registration_date(date) no older than 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 query in query in SQL. Update age among users to 18+, where gender is male. I don't recommend this type of request. From personal experience I will say that it is better to create several separate ones - they will be worked out faster.

8. MySQL and PHP database queries

In MySQL queries in a PHP page, you can insert variables as compare values and so on. A couple of examples

1. Selects all records from the table users, where the field fname matches the value of the variable $name.

SELECT * FROM users WHERE fname="$name";
2. Table users WHERE id is 3 field value age changes to the value of the $age variable.

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

Attention! If you are interested in any other example, then write a question in the comments!

If we need to change or update data in MySQL, we can use the SQL UPDATE command to work. ,

grammar

Following is the UPDATE command to modify MySQL Sheet Data General SQL Syntax:

UPDATE table_name SET field1=new-value1, field2=new-value2

You can update one or more fields at the same time.
You can specify any condition in the WHERE clause.
You can also update the data in a separate table.

When you need to update the data given in the rows of a table, INEKE is very useful.

Command line to update data

Below we will update the w3big_tbl specified in the data table using the UPDATE INEKE SQL command:

examples

The following example will update the data table as w3big_title w3big_id field value 3:

# mysql -u root -p password; Enter password:******* mysql> use w3big; Database changed mysql> UPDATE w3big_tbl -> SET w3big_title="(!LANG:Learning JAVA" -> WHERE w3big_id=3; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> !}