Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief introduction to the latest and most complete basic commands of mysql database. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on the latest and most complete basic commands of mysql database can bring you some practical help.

First, create a database:

Create database database_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")

1.

Mysql_create_db ("database_name") or

Die ("could not create database")

two。

$string = "create database database_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 database_name

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

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

$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 column: 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 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

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

For example: 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

The latest and most complete mysql database basic commands will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.