MySQL Common query operations

Here is a summary of common MySQL database queries:

Displays all data for a data table.

Mysql> SELECT * FROM [table name];

Returns the column information for the specified data table.

Mysql> Show columns from [table name];

Use the value "blog" Filter to display selected rows.

Mysql> select * FROM [table name] WHERE user = "blog";

Displays all records that contain the name "Bob" and the phone number "5889".

Mysql> SELECT * FROM [table name] WHERE name = "Bob" and Phone_number = ' 5889 ';

Displays all records that do not contain the name "Bob" and the phone number is "5889" and is sorted in the Phone_number field.

Mysql> select * FROM [table name] WHERE name! = "Bob" and Phone_number = ' 5889 ' ORDER by Phone_number;

Displays all records with the name beginning with the letter "Bob" and the phone number "5889".

Mysql> select * FROM [table name] WHERE name is like "bob%" and Phone_number = ' 5889 ';

Displays the 1th to 5th Records whose name begins with the letter "Bob" and the phone number is "5889".

Mysql> select * FROM [table name] WHERE name is like "bob%" and Phone_number = ' 5889 ' limit 1, 5;

Use regular expressions to find records. Use regular expression binary to enforce case sensitivity. This command finds any records that begin with a.

Mysql> select * FROM [table name] WHERE rec rlike "^a";

Returns the only distinct record.

mysql> SELECT DISTINCT [column name] from [table name];

Displays the selected record in ascending or descending order.

Mysql> SELECT [col1],[col2] from [table name] ORDER by [col2] DESC;

Returns the number of rows.

Mysql> SELECT COUNT (*) from [table name];

Counts the sum of the specified column values.

Mysql> SELECT SUM (*) from [table name];

junction table.

Mysql> Select Lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid= Person.personid=statement to join birthday in person table with primary illustration ID;

Create the data table Example 1.

mysql> CREATE TABLE [table name] (firstname varchar), MiddleInitial varchar (3), LastName varchar, suffix varchar (3), OfficeId varchar (+), UserID varchar (username), varchar (8), email varchar (+), phone varchar (+), groups varchar ( ), Datestamp Date,timestamp time,pgpemail VARCHAR (255));

Create the data table Example 2.

mysql> CREATE TABLE [table name] (PersonID int () NOT NULL auto_increment primary key,firstname varchar), MiddleName varchar (+), Lastnamevarchar (() Default ' Bato ');

This article is from the "Bill of Operations Notes" blog, please be sure to keep this source http://chenshoubiao.blog.51cto.com/6159058/1842664

MySQL Common query operations