MySQL Data sheet additions and deletions

Http://www.runoob.com/mysql/mysql-tutorial.html

First, MySQL create data table

The following information is required to create a MySQL data table:

• Table name
• table field Name
• Define each table field

Grammar

The following is the general SQL syntax for creating MySQL data tables:

(column_name column_type); 

In the following example we will create a data table Runoob_tbl in the Runoob database:

CREATE TABLE IF not EXISTS' Runoob_tbl '(   ' runoob_id 'INT UNSIGNED auto_increment, ' Runoob_title 'varchar(+) not null, ' runoob_author ' varchar(+) not null , ' submission_date ' date, PRIMARY KEY ( ' runoob_id ' )) ENGINE=InnoDB DEFAULT CHARSET=UTF8;         

Instance parsing:

• If you do not want the fields to be null , you can set the field's property to not null, and you will get an error if the data entered in the field is null when you manipulate the database.
• Auto_increment defines a property that is self-increasing, typically used for a primary key, and the value is automatically added to 1.
• The PRIMARY key keyword is used to define the column as the primary key. You can use multiple columns to define a primary key, and the columns are separated by commas.
• The engine sets the storage engines and CHARSET sets the encoding.

Second, MySQL insert data

Use the INSERT INTO SQL statement in the MySQL table to insert the data.

You can insert data into a data table by mysql> the command Prompt window, or insert data through a PHP script.

Grammar

The following is the INSERT into SQL syntax for inserting data into a MySQL data table:

( field1, field2,... )( value1, value2,... );

If the data is a character type, you must use either single or double quotation marks, such as "value".

Third, MySQL DELETE statement

You can use the delete from command of SQL to delete records from the MySQL data table.

You can execute the command at the mysql> command prompt or in a PHP script.

Grammar

The following is a general syntax for SQL DELETE statements to delete data from a MySQL data table:

[Clause] 

• If you do not specify a WHERE clause, all records in the MySQL table will be deleted.
• You can specify any condition in the WHERE clause
• You can delete records at once in a single table.

The WHERE clause is useful when you want to delete a record specified in a data table.

Iv. MySQL UPDATE Changes

If we need to modify or update the data in MySQL, we can use the SQL Update command to operate:

Grammar

The following is the general SQL syntax for the UPDATE command to modify MySQL data table data:

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

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

The WHERE clause is useful when you need to update the data for a specified row in a data table.

Updating data from the command prompt

Below we will use the WHERE clause in the SQL Update command to update the data specified in the RUNOOB_TBL table:

V. MySQL query data

The MySQL database uses the SQL SELECT statement to query the data.

You can query the data in the database through the mysql> Command Prompt window, or query the data through PHP scripts.

Grammar

The following is a general SELECT syntax for querying data in a MySQL database:

SELECT column_name,column_namefrom table_name[Clause][LIMIT N] [ OFFSET M  ]

• In a query statement you can use one or more tables, separate the tables with commas (,), and use the where statement to set the query criteria.
• The SELECT command can read one or more records.
• You can use the asterisk (*) instead of the other fields, and the SELECT statement returns all the field data for the table
• You can use the WHERE statement to include any condition.
• You can use the LIMIT property to set the number of records returned.
• You can specify the data offset for the SELECT statement start query by using offset. By default, the offset is 0.

MySQL Data sheet additions and deletions