Basic statement for adding, deleting, modifying, and querying mysql, and basic statement for adding, deleting, and modifying mysql

Basic statement for adding, deleting, modifying, and querying mysql, and basic statement for adding, deleting, and modifying mysql

Syntax

Here is the general SQL syntax for inserting data INTO a MySQL table using the INSERT INTO command:

INSERT INTO table_name ( field1, field2,...fieldN )            VALUES            ( value1, value2,...valueN );

To insert string data, double or single quotation marks must be kept to all values, for example, "value ".

1. Insert data from the command prompt

This inserts data INTO the MySQL table using the SQL INSERT INTO command: tutorials_tbl

Example

root@host# mysql -u root -p password;Enter password:mysql> use test;Database changedmysql> INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) VALUES ("Learn PHP", "Bjpowernode", NOW());Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO tutorials_tbl   ->(tutorial_title, tutorial_author, submission_date)   ->VALUES   ->("Learn MySQL", "Bjpowernode", NOW());Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO tutorials_tbl   ->(tutorial_title, tutorial_author, submission_date)   ->VALUES   ->("JAVA Tutorial", "bjpowernode", '2015-05-06');Query OK, 1 row affected (0.01 sec)mysql>

Note: Please note that all Arrow symbols (->) are not part of the SQL command; it represents a new line, which is not given a semicolon when the Enter key is pressed at the MySQL prompt, command to automatically create the end of a row.

In the preceding example, the value corresponding to tutorial_id is not provided, because it is automatically created when the table is created, and the AUTO_INCREMENT option is provided for this field. Therefore, MySQL automatically allocates the insert ID value. Here, NOW () is the MySQL function that returns the current date and time.

Syntax

The following is the SELECT command syntax of the general SQL statement to obtain data from the MySQL table:

SELECT field1, field2,...fieldN table_name1, table_name2...[WHERE Clause][OFFSET M ][LIMIT N]

Distinct can use one or more separated commas to separate multiple tables and use the WHERE clause to include various conditions. However, the WHERE clause is an optional part of the SELECT command.

Explain can read one or more fields in a SELECT command

Asterisk (*) can be specified to replace the selected field. In this case, all fields are returned.

Conditions can specify any condition after the WHERE clause

 You can use OFFSET to specify an OFFSET. SELECT returns records from there. The default offset value is 0.

LIMIT can use the LIMIT attribute to LIMIT the number of returned results.

1. read data from the command prompt

This will use the SQL SELECT command to read data from the MySQL table tutorials_tbl

Example

The following example returns all records from the tutorials_tbl table:

root@host# mysql -u root -p password;Enter password:mysql> use test;Database changedmysql> SELECT * from tutorials_tbl +-------------+----------------+-----------------+-----------------+| tutorial_id | tutorial_title | tutorial_author | submission_date |+-------------+----------------+-----------------+-----------------+|      1 | Learn Java   | John Poul    | 2016-05-21   ||      2 | Learn MySQL  | Abdul S     | 2016-05-21   ||      3 | JAVA Tutorial | Sanjay     | 2016-05-21   |+-------------+----------------+-----------------+-----------------+3 rows in set (0.01 sec)mysql>

Syntax

Here is the general SQL syntax for modifying data to a MySQL table using the UPDATE command:

UPDATE table_name SET field1=new-value1, field2=new-value2[WHERE Clause]

The lifecycle operation can update one or more fields.

Conditions can be specified to use any conditions in the WHERE clause.

Values can be used to update values in a table at a time.

The WHERE clause is useful when you want to update selected rows in a table.

1. Update data from the command prompt

Use the SQL UPDATE command and the WHERE clause to UPDATE the selected data to the MySQL table tutorials_tbl.

Example

The following example updates the tutorial_title field in the record whose tutorial_id is 3.

root@host# mysql -u root -p password;Enter password:mysql> use test;Database changedmysql> UPDATE tutorials_tbl   -> SET tutorial_title='Learning JAVA'   -> WHERE tutorial_id=3;Query OK, 1 row affected (0.04 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql>

Syntax

Here is the general SQL syntax for deleting data from a MySQL table using the DELETE command:

DELETE FROM table_name [WHERE Clause]

If the WHERE clause is not specified, all records in the MySQL table will be deleted.

Conditions can be specified to use any conditions in the WHERE clause.

 All records in a table can be deleted at a time.

The WHERE clause is useful when you want to delete a table and select a specific row.

1. delete data from the command prompt

Use the SQL DELETE command to DELETE the data of the MySQL table tutorials_tbl selected in the WHERE clause.

Example

The following example deletes a record whose tutorial_id is 3 in the tutorial_tbl table.

root@host# mysql -u root -p password;Enter password:mysql> use test;Database changedmysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3;Query OK, 1 row affected (0.23 sec)mysql>

Summary

The above is the basic statement for adding, deleting, modifying, and querying mysql. I hope it will be helpful to you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!