Basic commands for MySQL database operations

1. Create a database:

 create data data _name;

Two methods to create a 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());

2. Select the database

Before creating a table, you must select the database where the table is to be created.

Select database:

Via the command line client:

use data _name

pass

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

3. Create a table

create table table_name

like:

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

In the command line client, you need to type the entire command

Use mysql_query() function in PHP

like:

 $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());

4. Create an index

 index index_name(indexed_column)

5. Table Types

ISAM MyISAM BDB Heap

The syntax for declaring a table type is:

 create table table_name type=table_type
 (col_name column attribute);

MyISAM is used by default

6. Modify the table

 alter table table_name

Changing Table Names

 alter table table_name rename new_table_name

Or (in higher versions)

 rename table_name to new_table_name

Adding and Removing 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 located 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

To delete a column:

alter table table_name drop column column name

Adding and removing 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

like:

alter table_name test10 drop primary key

To change the column definition:

Use the change or modify command to change the name or properties of a column. To change the name of a column, you must also redefine the column's properties. For example:

 alter table table_name change original_column_name new_column_name int not null

Note: You must redefine the column properties! ! !

 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 store a string, you need to use single quotes "'" to enclose the string, but you need to pay attention to the escape sequence of characters.

like:

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

The characters that need to be escaped are: single quote ' double quote " backslash \ percent sign % underscore _

You can use two consecutive single quotes to escape a single quote.

8. update statement

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

The where part can have any comparison operator

like:

table folks
id fname iname salary
1 Don Ho 25000
2 Don Corleone 800000
3 Don Juan 32000
4 Don Johnson 44500
update 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 data data _name

In PHP, you can use the drop table command through the mysql_query() function

To delete a database in PHP, you need to 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 a list of tables.

11. View column properties and types

 show columns from table_name
 show fields from table_name

You can get similar information using mysql_field_name(), mysql_field_type(), and mysql_field_len()!

12. Basic select statement

It requires the table from which the selection is to be made, and the column names required. To select all columns, use * to represent 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 returned from a query (select)

 select * from table_name where user_id = 2

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

like:

select * from users where city = 'San Francisco'

By adding and or or to the where clause, you can compare several operators at once.

 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 operator in the table. For null values, you need to use the is null or is not null predicate.

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

If you want to find all records that contain any value (except null), you can

 select * from table_name where zip is not null

14. Use distinct

When using distinct, the MySQL engine will delete rows with the same result.

 select distinct city,state from users where state='CA'

15. Use between

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

like:

 select * from users where lastchanged between 20000614000000 and 20000614235959
 select * from users where lname between 'a' and 'm'

16. Use 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 users where state in ('RI','NH','VY','MA','ME')

If you want to achieve the same result, but with an opposite result set, you can use the not in predicate.

 select * from user where state not in ('RI','NH','VT','MA','ME')

17. Use like

If you need to use wildcards, use like

 select * from users where fname like 'Dan%' % matches zero characters select * from users where fname like 'J___' matches any three-letter word starting with J

Mysql like is not case sensitive

18. order by

The order by clause can specify the order of rows returned in the query. It can sort any column type. You can set the order in ascending or descending order by placing asc or desc at the end. If you do not set it, asc is used by default.

 select * from users order by lname,fname

You can sort by as many columns as you want, or mix asc and desc

 select * from users order by lname asc, fname desc

19. Limit

limit limits the number of rows returned from the query. You can specify the starting number of rows 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 users order by lname,fname limit 0,5

Get the second 5 rows of the table:

  select * from users limit 5,5

20. group by and aggregate functions

After using group by, Mysql can create a temporary table and record all the information of the rows and columns that meet the criteria.

count() counts the number of rows in each collection

 select state,count(*) from users group by state

The * 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 to use as an alias. If the column name you need is more than one word, you need to use single quotes to enclose the text string.

sum() returns the number of columns in a given column
min() Get the minimum value in each set
max() Get the maximum value in each set
avg() returns the mean of a set of values
having

Limits the rows displayed by group by, the where clause displays the rows used in group by, and the having clause only limits the rows displayed.

21. Connection table

All tables to be connected must be listed in the from part of the select statement, and the fields used for the connection must be displayed in the where part.

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 comes from.

22. Multi-table connection

Add extra columns after the select, add extra tables in the from clause, and add extra join parameters in the where clause –>