Basic knowledge about MySQL

1. Create a database:
Create database database_name;
Two methods for creating a database in php: mysql_create_db (), mysql_query ())
$ Conn = mysql_connect ("localhost", "username", "password") or
Die ("cocould not connect to localhost ");
1.
Mysql_create_db ("database_name") or
Die ("cocould not create database ");
2.
$ String = "create database database_name ";
Mysql_query ($ string) or
Die (mysql_error ());
 
2. Select a database
Before creating a table, you must select the database where the table to be created is located.
Selected database:
Use the command line Client: use database_name
Using php: mysql_select_db ()
$ Conn = mysql_connect ("localhost", "username", "password") or
Die ("cocould not connect to localhost ");
Mysql_select_db ("test", $ conn) or
Die ("cocould not select database ");

3. Create a table
Create table table_name
For example:
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)
)
On the command line client, enter the entire command
Use the mysql_query () function in php
For example:
$ Conn = mysql_connect ("localhost", "username", "password") or
Die ("cocould not connect to localhost ");
Mysql_select_db ("test", $ conn) or
Die ("cocould 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 ());

4. Create an index
Index index_name (indexed_column)
 
V. Table type
ISAM MyISAM BDB Heap
Syntax for declaring table types:
Create table table_name type = table_type
(Col_name column attribute );
MyISAM is used by default.
6. modify a table
Alter table table_name
Change table name
Alter table table_name rename new_table_name
Or (later)
Rename table_name to new_table_name
Add and delete 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 located in the first column of the table.
After, place the new column behind the existing column.
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 name
Add and delete 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
For example, alter table_name test10 drop primary key
Change the column definition:
You can use the change or modify command to change the column name or attribute. To change the column name, you must also redefine the attributes of the column. For example:
Alter table table_name change original_column_name new_column_name int not null
Note: You must redefine the column attributes !!!
Alter table table_name modify col_1 clo_1 varchar (200)
 
VII. input information to the table (insert)
Insert into table_name (column_1, column_2, column_3 ,.....)
Values (value1, value2, value3 ,......)
If you want to store a string, you need to enclose the string with single quotes ('), but note the meaning of the characters.
For example, insert into table_name (text_col, int_col) value (\ 'Hello world \ ', 1)
Escape characters: single quotation marks, double quotation marks, backslash, percent sign, and underscore _
Two single quotes can be used consecutively to escape single quotes

VIII. updata statements
Updata table_name set col _ 1 = vaule_1, col_1 = vaule_1 where col = vaule
The where part can have any comparison operators.
For example:
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

9. Delete tables and databases
Drop table table_name
Drop database database_name
In php, the drop table command can be used through the mysql_query () function.
To delete a database in php, use the mysql_drop_db () function.

10. list all available tables in the database (show tables)
Note: You must select a database before using this command.
In php, you can use mysql_list_tables () to get the list in the table.