MySQL basic Syntax command

1. Build a table

Create The MySQL data sheet requires the following information:

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

General Syntax:

CREATE TABLE table_name (column_name column_type);

Instance:

CREATE TABLE IF  not EXISTS' runoob_tbl ' (' runoob_id ' )INTUNSIGNED auto_increment, ' Runoob_title 'VARCHAR( -) not NULL, ' Runoob_author 'VARCHAR( +) not NULL, ' submission_date ' date,PRIMARY KEY(' runoob_id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

Instance parsing:

• If you don't want the field NULL you can set the properties of a field to not null, An error occurs if the data entered in the field is null when the database is being manipulated .
• Auto_incremen T defines a property that is self-increasing, typically used for the primary key, and the value is automatically added to 1.
• PRIMARY KEY off The key word is used to define the column primary key. You can use multiple columns to define a primary key, and the columns are separated by commas.
• ENGINE set up the storage engine, CHARSET sets the encoding.

Delete Table Syntax:

DROP TABLE table_name;

2. Adding and deleting changes

(1) Insert data: INSERT INTO

Grammar:

INSERT  into table_name (field1, Field2,... fieldn)                        VALUES                        (value1, value2,... valuen);

Instance:

INSERT  into Runoob_tbl  (Runoob_title, Runoob_author, submission_date)                   VALUES                   ("Learning PHP", "Rookie Tutorial", now ());

(2) query data: SELECT

SELECT Column_name,column_name  from table_name [WHERE Clause] [[LIMIT N]

Analytical:

• 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 specify the data offset for the SELECT statement start query by using offset . By default, the offset is 0.
• You can use the LIMIT property to set the number of records returned.

(3) Updating data: Update

Grammar:

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

Instance:

UPDATE SET runoob_title=' learning C + + 'WHERE runoob_id=;

Parse: Update data for the Runoob_title field of runoob_id 3.

(4) Deleting data: Delete

Grammar:

DELETE  from [WHERE Clause]

Syntax parsing:

• 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.

Instance:

DELETE  from WHERE runoob_id=3;

Instance resolution: Deletes a record of runoob_id 3 in the Runoob_tbl table.

3. Clause syntax

(1) WHERE clause

Grammar:

SELECT  from table_name1, table_name2 ... [condition1 [and [OR]] condition2 .....

Syntax parsing: The above is a general syntax for a SELECT statement to read data from a data table using a WHERE clause, other similar.

• You can use one or more tables in a query statement, use commas , split between tables, and use the where statement to set the query criteria.
• You can specify any condition in the WHERE clause.
• You can specify one or more conditions by using and OR OR.
• The WHERE clause can also be applied to the SQL DELETE or UPDATE command.
• The WHERE clause is similar to the IF condition in a program language, and reads the specified data according to the field values in the MySQL table.
• The string comparison of the WHERE clause is case insensitive. You can use the BINARY keyword to set the WHERE clause string comparison to be case sensitive. WHERE BINARY.

List of operators:

Operator	Describe	Instance
=	Equals, detects if two values are equal, returns true if equal	(A = B) returns FALSE.
<>! =	does not equal, detects whether two values are equal if not equal returns True	(A! = B) returns TRUE.
>	Greater than sign, detects if the left value is greater than the right value, and returns True if the left value is greater than the right value	(A > B) returns FALSE.
<	Less than sign, detects if the left value is less than the right value, and returns True if the left value is less than the right value	(A < B) returns TRUE.
>=	Greater than equals sign, detects if the left value is greater than or equal to the right value, if the left value is greater than or equal to the right value returns True	(A >= B) returns false.
<=	Less than equals sign, detects if the left value is less than or equal to the right value, if the left value is less than or equal to the right value returns True	(A <= B) returns True

Instance:

SELECT runoob_id, Runoob_title, Runoob_author, submission_date         from runoob_tbl        WHERE Runoob_author="Runoob.com"

Instance parsing: Reading data from Runoob_author to runoob.com

(2) LIKE clause

Grammar:

SELECT from table_nameWHERElike[and[OR]  =' somevalue '

Syntax parsing:

• You can specify any condition in the WHERE clause.
• You can use the LIKE clause in the WHERE clause.
• Like is usually used in conjunction with%, similar to a meta-character search.
• You can use the LIKE clause instead of equal sign =. If percent% is not used , the LIKE clause has the same effect as equals sign =.
• You can specify one or more conditions by using and OR OR.
• You can use the where ... in the DELETE or UPDATE command. Like clause to specify criteria

Instance:

SELECT *  from Runoob_tbl  WHERElike'%COM ';

Instance resolution: Get all the records in the Runoob_author field that end in COM from the RUNOOB_TBL table

4. Other syntax

(1) Union merger

(2) Order by sort

(3) Group by group

(4) Jion connection

(5) Alter modify table

Turn from: Rookie tutorial http://www.runoob.com/mysql/mysql-tutorial.html

MySQL basic Syntax command