mysql add, delete, modify and query basic statements

grammar

Here is the generic SQL syntax for INSERT INTO command to insert data into a MySQL table:

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

To insert string type data, double or single quotes are required to preserve all values, for example: "value".

1. Insert data from the command prompt

This will use the SQL INSERT INTO command to insert data into the MySQL table: tutorials_tbl

Example

root@host#mysql -u root -p password;
Enter password:
mysql> use test;
Database changed
mysql> 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 the arrow symbol (->) is not a part of the SQL command; it indicates a new line and is created automatically at the end of the line by pressing the Enter key at the MySQL prompt without giving a semicolon.

In the above example, no value is provided for tutorial_id because it is automatically created when the table is created and we have given the AUTO_INCREMENT option to this field. Therefore MySQL automatically assigns the value of the insert ID. Here, NOW() is a MySQL function that returns the current date and time.

grammar

Following is the common SQL SELECT command syntax to get data from a MySQL table:

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

 You can use one or more commas to separate from multiple tables and include various conditions using the WHERE clause, but the WHERE clause is an optional part of the SELECT command

You can specify to read one or more fields in a SELECT command

You can specify an asterisk (*) to replace the selected field. In this case, all fields will be returned

You can specify any conditions after the WHERE clause

You can use OFFSET to specify an offset from where SELECT starts returning records. By default, the offset value is 0.

You can use the LIMIT attribute to limit the number of items returned

1. Read data from the command prompt

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

Example

The following example will return all records from the tutorials_tbl table:

root@host#mysql -u root -p password;
Enter password:
mysql> use test;
Database changed
mysql> 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>

grammar

Here is the general SQL syntax of UPDATE command to modify data in MySQL table:

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

It is entirely possible to update one or more fields.

You can specify any condition using the WHERE clause.

Can update the values ​​in one table at a time.

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

1. Update data from the command prompt

Use SQL UPDATE command along with WHERE clause to update the selected data into MySQL table tutorials_tbl.

Example

The following example will update the tutorial_title field in the record where tutorial_id is 3.

root@host#mysql -u root -p password;
Enter password:
mysql> use test;
Database changed
mysql> 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: 0
mysql>

grammar

Here is the general SQL syntax of DELETE command to delete data from a MySQL table:

DELETE FROM table_name [WHERE Clause]

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

You can specify any condition using the WHERE clause.

All the records in a table can be deleted at once.

The WHERE clause is very useful when you want to select specific rows from a table for deletion.

1. Delete data from the command prompt

Using SQL DELETE command, the data of MySQL table tutorials_tbl selected in WHERE clause will be deleted.

Example

The following example will delete a record whose tutorial_id is 3 from the tutorial_tbl table.

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

Summarize

The above is the basic MySQL add, delete, modify and query statements introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!