The latest and most complete mysql database basic commands
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.
