The basic commands for MySQL database you want are here (latest version)

First, create the database:

CREATE DATABASE database_name;

Two ways to create a database in PHP: (mysql_create_db (), mysql_query ())

$conn = mysql_connect ("localhost", "username", "password") or

Die ("Could not connect to localhost");

1.

mysql_create_db ("database_name") or

Die ("Could not create database");

2.

$string = "CREATE DATABASE database_name";

mysql_query ($string) or

Die (Mysql_error ());

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M02/95/FE/wKiom1kb5zSgl0RVAAMZZS0cvJQ768.jpg-wh_500x0-wm_ 3-wmp_4-s_476835875.jpg "title=" 2531170_181316797000_2.jpg "alt=" Wkiom1kb5zsgl0rvaamzzs0cvjq768.jpg-wh_50 "/>

Second, selected database

Before you create a table, you must select the database that contains the table you want to create

Select a database:

via the command line client: use database_name

by php:mysql_select_db ()

$conn = mysql_connect ("localhost", "username", "password") or

Die ("Could not connect to localhost");

mysql_select_db ("Test", $conn) or

Die ("Could not select Database");

Third, create the table

CREATE TABLE table_name

Such as:

CREATE TABLE table_name

(

column_1 column_type column attributes,

Column_2 column_type column attributes,

Column_3 column_type column attributes,

Primary KEY (column_name),

Index Index_name (column_name)

)

The command line client needs to type the entire command

Used in PHP, the mysql_query () function

Such as:

$conn = mysql_connect ("localhost", "username", "password") or

Die ("Could not connect to localhost");

mysql_select_db ("Test", $conn) or

Die ("Could not select Database");

$query = "CREATE TABLE my_table (col_1 int not null primary key,

Col_2 text

)”;

mysql_query ($query) or

Die (Mysql_error ());

Iv. Creating an Index

Index Index_name (indexed_column)

V. Types of tables

ISAM MyISAM BDB Heap

Syntax for declaring a table type:

CREATE TABLE table_name Type=table_type

(col_name column attribute);

Use MyISAM by default

Vi. Modification of the table

ALTER TABLE TABLE_NAME

Change table name

ALTER TABLE table_name Rename New_table_name

or (in the higher version)

Rename table_name to New_table_name

Adding and Removing columns

Add column: ALTER TABLE table_name ADD COLUMN column_name COLOMN attributes

Example: ALTER TABLE my_table add column my_column text NOT NULL

First specifies that the inserted column is in the top column of the table

After the new column is placed after the column that already exists

Example: ALTER TABLE my_table add column My_next_col text NOT NULL first

ALTER TABLE my_table Add column My_next_col text not null after My_other _column

Delete column: ALTER TABLE table_name drop COLUMN Column name

To add and remove indexes:

ALTER TABLE table_name ADD index index_name (column_name1,column_name2,......)

ALTER TABLE table_name add unique index_name (column_name)

ALTER TABLE TABLE_NAME ADD PRIMARY KEY (My_column)

ALTER TABLE table_name DROP INDEX Index_name

Example: ALTER TABLE_NAME TEST10 DROP PRIMARY key

To change a column definition:

You can change the name or property of a column by using the ALTER or modify command. To change the name of a column, you must also redefine the properties of the column. For example:

ALTER TABLE table_name change original_column_name new_column_name int NOT NULL

Note: You must redefine the properties of the column!!!

ALTER TABLE table_name Modify COL_1 clo_1 varchar (200)

Vii. entering information into the table (insert)

INSERT INTO table_name (column_1,column_2,column_3,.....)

VALUES (Value1,value2,value3,......)

If you want to save a string, you need to enclose the string with a single quote "'", but be aware of the character's meaning

Example: INSERT INTO table_name (TEXT_COL,INT_COL) value (\ ' Hello world\ ', 1)

Characters that need to be escaped are: single quote ' double quotation mark ' backslash \ percent% underline _

Single quotes can be escaped using two single quotes in a row

Viii. updata Statements

Updata table_name set col__1=vaule_1,col_1=vaule_1 where Col=vaule

The where section can have any comparison operator

Such as:

Table Folks

ID fname iname Salary

1 Don Ho 25000

2 Don Corleone 800000

3 Don Juan 32000

4 Don Johnson 44500

Updata folks set fname= ' Vito ' where id=2

Updata folks set fname= ' Vito ' where fname= ' Don '

Updata folks set salary=50000 where salary<50000

Ix. Delete tables, databases

DROP TABLE table_name

DROP DATABASE database_name

The drop TABLE command can be used in PHP via the mysql_query () function

Deleting a database in PHP requires using the mysql_drop_db () function

X. List all available tables in the database (show tables)

Note: You must select a database before you can use this command

In PHP, you can use Mysql_list_tables () to get the list in the table

Xi. viewing the properties and types of a column

Show columns from table_name

Show fields from table_name

You can get similar information using Mysql_field_name (), Mysql_field_type (), Mysql_field_len ()!

12. Basic SELECT statement

Requires the table to be selected and the name of the column that is required. To select all columns, available * represents all field names

Select Column_1,column_2,column_3 from table_name

Or

SELECT * FROM table_name

Use mysql_query () to send queries to MySQL

13. WHERE clause

Limit the rows of records returned from a query (select)

SELECT * FROM table_name where user_id = 2

If you want to compare columns that store strings (char, varchar, and so on), you need to enclose the strings to be compared in single quotes in the WHERE clause.

Example: SELECT * from users where city = ' San Francisco '

You can compare several operators at a time by adding and or or to the WHERE clause

SELECT * from users where userid=1 or city= ' San Francisco '

Select 8 from users where state= ' CA ' and city= ' San Francisco '

NOTE: null values cannot be compared with any operators in the table, and for null values you need to use the is null or is NOT NULL predicate

SELECT * from the users where zip!= ' 1111′or zip= ' 1111′or zip is null

If you want to find all of the records that contain any value (other than null values), you can

SELECT * FROM table_name where ZIP was not null

14. Using DISTINCT

When distinct is used, the MySQL engine deletes rows that have the same result.

Select DISTINCT city,state from users where state= ' CA '

XV, using between

Use between to select values within a range, between can be used for numbers, dates, and text strings.

Such as:

SELECT * from users where lastchanged between 20000614000000 and 20000614235959

SELECT * from users where lname between ' a ' and ' m '

16. Using In/not in

If a column may return several possible values, you can use the in predicate

SELECT * from users where state= ' RI ' or state= ' NH ' or state= ' VT ' or state= ' MA ' or state= ' ME '

Can be rewritten as: SELECT * from the users where state in (' RI ', ' NH ', ' VY ', ' MA ', ' ME ')

If you want to achieve the same result, but the result set is reversed, you can use the not in predicate

SELECT * from user where the state isn't in (' RI ', ' NH ', ' VT ', ' MA ', ' ME ')

17. Use Like

If you need to use wildcards, you want to use a like

SELECT * from users where fname like ' dan% '% matches 0 characters

SELECT * from the users where fname like ' j___ ' matches any three-letter word starting with J

Like in MySQL does not distinguish between letter case

18. ORDER BY

An ORDER BY statement can specify the order of the rows returned in the query, sort any column type by placing ASC or DESC at the end to set in ascending or descending order, or, if not set, by default using ASC

SELECT * from the users ORDER by Lname,fname

You can sort by as many columns as you want, or you can mix ASC and DESC

SELECT * from the Users order by lname ASC, fname DESC

19. Limit

Limit limits the number of rows returned from the query, you can specify the number of rows to start and the number of rows you want to return

Get the first 5 rows in the table:

SELECT * from Users limit 0,5

SELECT * from the users order by Lname,fname limit 0,5

Get the second 5 rows of a table:

SELECT * from Users limit 5,5

20. Group BY and aggregation functions

After using group BY, MySQL can create a temporary table that records all the information about the rows and columns that meet the criteria

Count () calculates the number of rows in each collection

Select State,count (*) from the Users group by state

* number indicates that all rows in the collection should be evaluated

Select COUNT (*) from users

Count all rows in a table

You can use the Word as after any function or column name, and then specify a name for the alias. If you need more than one word for a column name, enclose the text string in single quotation marks.

SUM () returns the number of the given column

Min () Gets the minimum value in each set

Max () Gets the maximum value in each collection

AVG () returns the product mean value of the collection

Having

Limits the rows displayed through group BY, where clauses display the rows used in group by, and the HAVING clause restricts only the rows that are displayed.

21. Connection Table

All tables to be joined must be listed in the From section of the SELECT statement, and the fields used for the connection must be displayed in the Where section.

SELECT * FROM Companies,contacts where companies.company_id=contacts.company_id

When a reference to a field name is ambiguous, you need to use the Table_name.column_name syntax to specify which table the field is from

22. Multi-Table Connection

Add additional columns after the Select, add additional tables in the FROM clause, and add additional join parameters in the WHERE clause –>

This article is from the "SUN" blog, be sure to keep this source http://sun510.blog.51cto.com/9640486/1926622

The basic commands for MySQL database you want are here (latest version)