Basic commands for MySQL database operations, mysql database commands

Basic commands for MySQL database operations, mysql database commands

1. Create a database:

 create data data _name；

Two methods for creating 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 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 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

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

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

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, place the new column behind 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 column:

alter table table_name drop column 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 data data _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.

11. View column attributes and types

 show columns from table_name show fields from table_name

Use mysql_field_name (), mysql_field_type (), and mysql_field_len () to obtain similar information!

12. Basic select statements

Specify the selected table and the required column name. To select all columns, * indicates 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

Restrict record 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, and Other types), you must enclose the strings to be compared using single quotation marks in the where clause.

For example:

select * from users where city = ‘San Francisco'

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

 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 operators in the table. For null values, use the is null or is not null predicates.

 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 values), you can

 select * from table_name where zip is not null

14. Use distinct

When distinct is used, the Mysql engine deletes rows with the same results.

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

15. Use

You can use between to select values in a certain range. between can be used for numbers, dates, and text strings.

For example:

 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'

It can be rewritten:

select * from users where state in (‘RI','NH','VY','MA','ME') 

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

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

17. Use like

If you want to use wildcard characters, use like

Select * from users where fname like 'Dan % '% match zero character select * from users where fname like 'J ___' match any three-letter word starting with J

Like in Mysql is case-insensitive

18. order

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

 select * from users order by lname,fname

You can sort multiple columns as needed, or use both asc and desc.

 select * from users order by lname asc, fname desc

19th, limit

Limit limits the number of rows returned from the query. You can specify the Starting number of rows and the number of rows to be returned.

Obtain the first five rows in the table:

 select * from users limit 0,5  select * from users order by lname,fname limit 0,5

Obtain the Second Five 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 information of rows and columns that comply with the rules.

Count () calculates the number of rows in each set.

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

* Indicates that all rows in the set should be calculated.

 select count(*) from users

Calculate the number of all rows in the table.

You can use the word as after any function or column name, and then specify a name as an alias. If the column name is more than one word, enclose the text string with single quotation marks.

Sum () returns the number of given Columns
Min () is used to obtain the minimum value in each set.
Max () is used to obtain the maximum value in each set.
Avg () returns the product mean of the set.
Having

Restrict rows displayed by group by. The where clause displays the rows used in group by. The having clause only limits the rows displayed.

21. Connecting table

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

select * from companies,contacts where companies.company_ID=contacts.company_ID

When the reference to a field name is unknown, you need to use the table_name.column_name syntax to specify the table from which the field comes from.

22. Multi-table join

Add additional columns after select, add additional tables to the from clause, and add additional join parameters to the where clause.->