Shulou(Shulou.com)05/31 Report--

This article mainly introduces what are the basic commands of MySQL database operation, which are very detailed and have certain reference value. Friends who are interested must finish reading them.

First, create a database:

Create data data _ name

Two methods of creating database in php: (mysql_create_db (), mysql_query ())

$conn = mysql_connect ("localhost", "username", "password") or die ("could not connect to localhost"); mysql_create_db ("data _ name") or die ("could not create data"); $string = "create data data _ name"; mysql_query ($string) or die (mysql_error ())

II. Selected database

Before you can create a table, you must select the database where the table you want to create is located

Selected database:

Through the command line client:

Use data _ name

Pass through

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 data")

Third, create a 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))

You need to type the entire command on the command line client

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 data"); $query = "create table my_table (col_1 int not null primary key, col_2 text)"; mysql_query ($query) or die (mysql_error ())

Fourth, create an index

Index index_name (indexed_column)

Type of table

ISAM MyISAM BDB Heap

The syntax for declaring a table type:

Create table table_name type=table_type (col_name column attribute)

MyISAM is used by default

VI. Modify the table

Alter table table_name

Change the table name

Alter table table_name rename new_table_name

Or (in higher version)

Rename table_name to new_table_name

Add and remove columns

Add columns:

Alter table table_name add column column_name colomn attributes

For example:

Alter table my_table add column my_column text not null

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

After puts the new column after the existing column

For example:

Alter table my_table add column my_next_col text not null firstalter table my_table add column my_next_col text not null after my_other _ column

Delete the column:

Alter table table_name drop column column name

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

Such as:

Alter table_name test10 drop primary key

Change the column definition:

You can change the name or properties of a column with the change 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: the properties of the column must be redefined!

Alter table table_name modify col_1 clo_1 varchar (200)

7. Enter information into the table (insert)

Insert into table_name (column_1,column_2,column_3, … .) Values (value1,value2,value3, …)

If you want to deposit a string, you need to enclose the string in single quotation marks, but you need to pay attention to the change of meaning of the character

Such as:

Insert into table_name (text_col,int_col) value (\ 'hello world\', 1)

The characters that need to be escaped are: single quotation mark 'double quotation mark' backslash\% underscore _

You can escape single quotation marks using two consecutive single quotation marks

VIII. Updata statement

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

The where part 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

These are all the contents of this article entitled "what are the basic commands for MySQL database operation?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.