Home > Database > Mysql Tutorial > body text

Detailed explanation of basic command examples for MySQL database operations

小云云
Release: 2018-01-13 14:13:21
Original
1546 people have browsed it

This article mainly introduces the basic commands of MySQL database for the initial use of MySQL. Friends who need it can refer to it. I hope it can help everyone.

1. Create a database:

 create data data _name;
Copy after login

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());
Copy after login

2. Select a database

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

Selected database:

Via command line client:

use data _name
Copy after login

Via

php: mysql_select_db()
Copy after login
 $conn = mysql_connect(“localhost”,”username”,”password”) or
 die ( “could not connect to localhost”);
 mysql_select_db(“test”,$conn) or
 die (“could not select data ”);
Copy after login

3. Create a table

create table table_name
Copy after login

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)
 )
Copy after login

You need to type the entire command on the command line client

Used in php, 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 data ”);
 $query = “create table my_table (col_1 int not null primary key,
  col_2 text
  )”;
 mysql_query($query) or
 die (mysql_error());
Copy after login

4. Create index

 index index_name(indexed_column)
Copy after login

5. Table type

ISAM MyISAM BDB Heap

Declaration table Type syntax:

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

MyISAM is used by default

6. Modify the table

 alter table table_name
Copy after login

Change the table name

 alter table table_name rename new_table_name
Copy after login

or (in higher versions)

 rename table_name to new_table_name
Copy after login

Add and delete columns

Add columns:

alter table table_name add column column_name colomn attributes
Copy after login

For example:

 alter table my_table add column my_column text not null
Copy after login

first specifies that the inserted column is located in the first column of the table

after Put 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
Copy after login

Delete column:

alter table table_name drop column column name
Copy after login

Add and delete index:

 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
Copy after login

such as :

alter table_name test10 drop primary key
Copy after login

Change column definition:

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

 alter table table_name change original_column_name new_column_name int not null
Copy after login

Note: The column attributes must be redefined! ! !

 alter table table_name modify col_1 clo_1 varchar(200)
Copy after login

7. Enter information into the table (insert)

 insert into table_name (column_1,column_2,column_3,…..)
 values (value1,value2,value3,……)
Copy after login

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 characters Escape

For example:

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

The characters that need to be escaped are: single quotation mark 'double quotation mark' backslash\ percent sign % underscore_

You can use two consecutively Single quotes escape single quotes

8. Updata statement

 updata table_name set col__1=vaule_1,col_1=vaule_1 where col=vaule
Copy after login

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

9. Delete tables and databases

 drop table table_name
 drop data data _name
Copy after login

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: The database must be selected before using this command

In PHP, you can use mysql_list_tables() to get the list of tables

11. View the attributes and properties of the columns Type

 show columns from table_name
 show fields from table_name
Copy after login

Use mysql_field_name(), mysql_field_type(), mysql_field_len() to get similar information!

12. Basic select statement

requires the table to be selected. , and the required column names. To select all columns, use * to represent all field names

 select column_1,column_2,column_3 from table_name
Copy after login

or

 select * from table_name
Copy after login

Use mysql_query() to send a query to Mysql

13. Where clause

Limit the record rows returned from the query (select)

 select * from table_name where user_id = 2
Copy after login

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

For example:

select * from users where city = ‘San Francisco&#39;
Copy after login

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

 select * from users where userid=1 or city=&#39;San Francisco&#39;
 select 8 from users where state=&#39;CA&#39; and city=&#39;San Francisco&#39;
Copy after login

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!=&#39;1111′ or zip=&#39;1111′ or zip is null
Copy after login

If you want to find any value (except All records except null values) can be

 select * from table_name where zip is not null
Copy after login

14. Use distinct

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

 select distinct city,state from users where state=&#39;CA&#39;
Copy after login

15. Use between

Use between to select values ​​within 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&#39; and ‘m&#39;
Copy after login

16. Use in/not in

If a column may return several possible values, you can use the in predicate

 select * from users where state=&#39;RI&#39; or state=&#39;NH&#39; or state=&#39;VT&#39; or state=&#39;MA&#39; or state=&#39;ME&#39;
Copy after login

can be rewritten as:

select * from users where state in (‘RI&#39;,&#39;NH&#39;,&#39;VY&#39;,&#39;MA&#39;,&#39;ME&#39;)
Copy after login

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&#39;,&#39;NH&#39;,&#39;VT&#39;,&#39;MA&#39;,&#39;ME&#39;)
Copy after login

Seventeen, use like

If you need to use wildcards, use like

 select * from users where fname like ‘Dan%&#39; %匹配零个字符
 select * from users where fname like ‘J___&#39; 匹配以J开头的任意三字母词
Copy after login

like in Mysql is not case-sensitive

18. order by

The order by statement can be returned in the specified query The order of the rows can be sorted by any column type. By placing asc or desc at the end, you can set the order in ascending or descending order. If not set, the default is asc

 select * from users order by lname,fname
Copy after login

You can set it as needed Sort by any number of columns, or mix asc and desc Number of rows

Get the first 5 rows in the table:

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

得到表的第二个5行:

  select * from users limit 5,5
Copy after login

二十、group by 与聚合函数

使用group by后Mysql就能创建一个临时表,记录下符合准则的行与列的所有信息

count() 计算每个集合中的行数

 select state,count(*) from users group by state
Copy after login

*号指示应该计算集合中的所有行

 select count(*) from users
Copy after login

计算表中所有的行数

可以在任何函数或列名后使用单词as,然后指定一个作为别名的名称。如果需要的列名超过一个单词,就要使用单引号把文本字符串括起来

sum() 返回给定列的数目
min() 得到每个集合中的最小值
max() 得到每个集合中的最大值
avg() 返回集合的品均值
having

限制通过group by显示的行,where子句显示在group by中使用的行,having子句只限制显示的行。

二十一、连接表

在select句的from部分必须列出所有要连接的表,在where部分必须显示连接所用的字段。

select * from companies,contacts where companies.company_ID=contacts.company_ID
Copy after login

当对一个字段名的引用不明确时,需要使用table_name.column_name语法指定字段来自于哪个表

二十二、多表连接

在select后面添加额外的列,在from子句中添加额外的表,在where子句中添加额外的join参数–>

相关推荐:

TP5的数据库操作

PHP使用ORM进行数据库操作

MySQL教程--通过配置文件连接数据库操作详解

The above is the detailed content of Detailed explanation of basic command examples for MySQL database operations. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!