Home Database Mysql Tutorial Detailed explanation of basic command examples for MySQL database operations

Detailed explanation of basic command examples for MySQL database operations

Jan 13, 2018 pm 02:13 PM
mysql Order database

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!

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

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values ​​to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the &quot;MySQL Native Password&quot; plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

How to create a MySQL table using PHP? How to create a MySQL table using PHP? Jun 04, 2024 pm 01:57 PM

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

Detailed tutorial on establishing a database connection using MySQLi in PHP Detailed tutorial on establishing a database connection using MySQLi in PHP Jun 04, 2024 pm 01:42 PM

How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos Jul 18, 2024 am 05:48 AM

Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps

See all articles