search
HomeDatabaseMysql TutorialA SQL guide that programmers who develop PHP must master

Introduction

SQL has been applied to every corner around us, believe it or not. Every application that manipulates data of any kind needs to store the data somewhere. Whether it's big data, or a packet with just a few rows; whether it's government, or a startup; whether it's a large database spanning multiple servers, or a phone running its own small database, SQL is everywhere.

But, what is SQL? SQL stands for StructuredQuery Language, and typically, it is pronounced "ess-que-el". SQL is a database language specifically built to communicate with databases. SQL is a simple language, similar to the English language, in that commands have a similar structure to English sentences. Those sentences are organized into declarative statements, so SQL is also called a declarative language.

SQL 新手指南

There are already many tools available for visually writing SQL queries, so why learn a whole new language? When you use certain SQL tools, it is important to understand the SQL language and understand what the visualization tool is doing and why it is doing it. Sometimes, it is necessary to write some SQL statements manually, not only because it is the fastest way, but also because it is more powerful and often the only way to accomplish the intended goal.

Introduction to database

As we just mentioned, SQL is the database language. So, what is a database? A database is a storage mechanism designed to access stored information and its operations. Information in the database is stored in objects called tables. The name of a table is its unique identity and consists of columns and rows. A column contains the column name, the column's data type, and other attributes of the column. The row contains the records or data for that column. Most tables in the database will have relationships or links, one-to-one or one-to-many relationships. This is why this kind of database is called a relational model database.

Regarding describing the database structure, the easiest way is to compare it with the Excel spreadsheet . They have many similarities. A database is an independent document. A sheet in a spreadsheet is a table, and each sheet has a name. Columns and rows are the same as in a database. The SQL language is used to create new tables, alter existing tables, to obtain data, update data or delete data.

For example, we have a large collection of lines from well-known movies, stored in any separate text file. Even if we organize it carefully and keep it in Excel spreadsheets, the problems we face still exist. By storing lines this way, we can't quickly get all the lines from a movie, or get all the lines for a character. If we put a text file or spreadsheet into a database and create tables with relationships, all problems are solved. What does relational really mean? A relational model is a method of describing data and the relationships between these data entities. In our case, the relationship is between each line and the table where the movie title is stored, or all the characters are stored in the table.

The following is a simplified example. There is only one table as an example, and the table name is "Movie_quotes". It has four columns, one for the text of the line, one for the actor character who said the line, one for the movie, and the year. We've included eight lines from the movie, and our sample table looks like this:

Movie_quotes
Q_TEXT Q_CHARACTER Q_MOVIE Q_YEAR
I’ll be back The Terminator The Terminator 1984
I find your lack of faith disturbing. Darth Vader Star Wars 1977
It’s a trap! Admiral Ackbar Star Wars 1983
Never tell me the odds. Han Solo Star Wars 1980
Do. Or do not. There is no try. Yoda Star Wars 1980
Stupid is as stupid does. Forrest Gump Forrest Gump 1994
My mama always said: Life was like a box of chocolates.You never know what you’re gonna get. Forrest Gump Forrest Gump 1994
Run, Forrest! Run! Jenny Curran Forrest Gump 1994

When discussing databases, it's worth mentioning that there is a brand new database movement that has emerged among people who need to store data, and it is NoSQL. They are document-based systems, and although they are becoming very popular, there are still a large number of relational databases in use today. Even if NoSQL databases have some kind of query language, a large part of them (because they were almost all invented after SQL) still have some similarity to SQL.

Four Basic SQL Operations (CRUD)

There are many SQL commands, however, there are four common SQL operations that can do something with a table and its data:

  • Create – Populate data into the table.

  • Read – Query data from a table.

  • Update – Modify existing data in the table.

  • Delete – Remove data from the table.

The first letters of these basic SQL operations form the abbreviation "CRUD", and they are regarded as a basic set of four basic functions or features that every database must have.

By introducing the basic features, we will introduce the basic and most important SQL commands: `CREATE`, `INSERT`, `SELECT `, `UPDATE`, `DELETE`, and `DROP`.

Create data

First, we need to create a table in the database. To create a new table, `CREATE TABLE` is used. The simple syntax format of the `CREATE TABLE` statement is as follows:

CREATE TABLE table_name
(column_1 data_type,
column_2 data_type,
column_3 data_type);

First, the `CREATE TABLE` keyword is followed by the table name. This is an excellent example of SQL's simplicity and similarity to English. The keyword is followed by an opening parenthesis, which defines additional parameters: the column name and column data type, followed by a closing parenthesis. It must be mentioned that all SQL statements should end with `;`.

There are not many rules to follow. Table names and column names must begin with a letter and can be followed by letters, numbers, or underscores. They cannot exceed 30 characters in length. Using SQL reserved words as table or column names (such as `select`, `create`, `insert`, etc.) is prohibited.

In the example, the simplest column names might be `TEXT`, `CHARACTER`, `MOVIE`, and `YEAR`. However, the problem is that these column names are reserved words. To avoid any possible conflicts, we will create column names prefixed with `Q_`.

Data types vary between databases, but the most common types are used here:

  • `char(size)` – fixed-length string, enclosed in parentheses The parameters in are indicated.

  • `varchar(size)` – a variable length string, indicated by the parameters in parentheses.

  • `number(size)` – numeric value, the parameters in parentheses indicate the total length.

  • `date` – Date value.

  • `number(size, d)` – A numeric value with a total length of `size` and decimal places represented by `d`.

The data type specifies what type of data can be stored in the specified column. If the `Q_CHARACTER` column is used to store movie names, then the specified column should have a `varchar` (variable-length character) data type. The type of the column that stores the movie year is `number`, the corresponding column in our example is `Q_YEAR`.

For the desired table structure, the final SQL command to create the table is as follows:

CREATE TABLE Movie_quotes
(‘Q_TEXT’ varchar(200),
‘Q_CHARACTER’ varchar(20),
‘Q_MOVIE’ varchar(20),
‘Q_YEAR’ number(4));

The result of this SQL command will create an empty table with each column as follows:

  • `Q_TEXT` can accept a string of 200 characters in length.

  • `Q_CHARACTER` can accept a string of 20 characters in length.

  • `Q_MOVIE` can accept a string of 20 characters in length.

  • `Q_YEAR` accepts four digits for a year.

SQL 新手指南

接下来,用我们的电影台词数据填充这张表。有很多可用的 GUI 工具,来管理数据库中的表和数据。不过,写一个 SQL 脚本常常更快,该脚本基本上是 SQL 命令的集合,将被顺序执行。当你需要用大量数据填充表时,这种方式尤为方便。

向表插入或添加一行数据的 SQL 命令是 `INSERT`。格式如下:

INSERT INTO table_name
(column_1, column_2, ... column_n)
VALUES (value_1, value_2, ... value_n);

为了向表插入一行数据, `INSERT` 关键字跟着 `INTO` 关键字和表名。然后是列名,放在圆括号里,用逗号隔开,这是可选的,但是,指明要插入的列,以确保正确的数据插入相应的列,这是一种良好实践。最后一部分,用 `VALUES` 关键字定义了要插入的那些数据,数据列表以圆括号结束。请注意,字符串应该放在单引号里,数字不应如此。

用来填充例子中 `Movie_quotes` 表的 SQL 脚本,如下:

INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('I’ll be back', 'The Terminator', 'The Terminator', 1984);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('I find your lack of faith disturbing.', 'Darth Vader', 'Star Wars', 1977);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('It’s a trap!', 'Admiral Ackbar', 'Star Wars', 1983);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('Never tell me the odds.', 'Han Solo', 'Star Wars', 1980);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('Do. Or do not. There is no try.', 'Yoda', 'Star Wars', 1980);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('Stupid is as stupid does.', 'Forrest Gump', 'Forrest Gump', 1994);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('My mama always said: Life was like a box of chocolates. You never know what you’re gonna get.', 'Forrest Gump', 'Forrest Gump', 1994);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('Run, Forrest! Run!', 'Jenny Curran', 'Forrest Gump', 1994);

读取数据

数据库中有了存好的数据,现在我们可以查询数据,看看我们的表里存储了什么,我们还能用不同的方式过滤和分类数据。

`SELECT` 语句用于查询、或选择我们想从数据库中返回的数据。我们从非常简单的查询开始,但是 `SELECT` 有很多不同的选项和扩展,这为我们最终的需要提供了很大的灵活性。基本的 `SELECT` 语句的语法如下:

SELECT column_1, column_1, ... column_n
FROM table_name;

指出列名,决定了哪一列将被返回到结果里,以及按什么顺序。如果我们想选择所有的列,或我们不知道表中的确切列名,我们可以使用通配符 `*`,它将从数据库中选择所有列:

SELECT * FROM table_name;

对于本例,显示所有数据的查询,如下:

SELECT * FROM Movie_quotes;

SQL 新手指南

仅仅显示电影台词、年份的查询,如下:

SELECT Q_TEXT, Q_YEAR FROM Movie_quotes;

有时候我们不想从表中返回所有数据。当表中有大量数据、或我们在搜索匹配某些标准的特定数据时,就属于这种情况。对此,我们可以使用 `WHERE` 语句。`WHERE` 语句将过滤记录,限制从数据库中获取哪些记录、以满足具体定义的标准:

SELECT column_1, column_1, ... column_n
FROM table_name
WHERE column_name operator value;

注意,`WHERE` 语句是可选的,但是如果我们决定用到它,下面的操作符是可用的:

  • `=` – 等于。

  • `>` – 大于。

  • `

  • `>=` – 大于或等于。

  • `

  • `` – 不等于。

  • `BETWEEN` – 在两个值之间。

  • `LIKE` – 搜索一种模式。

  • `IN` – 针对一个列的多种可能值。

数学操作符无需解释了。`BETWEEN` 操作符搜索两个声明值的、中间的值,包括等于两端的情况。`LIKE` 模式匹配操作符是非常强大的操作符,支持选择和我们的规定类似的行。百分号 `%` 被用做通配符,以匹配任何可能字符,它可出现在具体字符串的前面或后面。

例如,为了得到来自电影《Stars Wars》中的台词,我们可以这样写:

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = ‘Star Wars’;

SQL 新手指南

请注意,`WHERE` 语句是大小写敏感的,下面的 SQL 语句将不会返回结果:

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = ‘STAR WARS’;

除了 `WHERE` 子句,还可组合逻辑运算符 `AND` 和 `OR`。如果我们对相同列使用多个 `AND` 逻辑操作符,那么我们应该考虑使用 `IN` 子句替代。

做为示例,我们返回来自电影《Star Wars》和《The Terminator》中的所有电影台词:

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = ‘Star Wars’ AND Q_MOVIE = ‘The Terminator’;

SQL 新手指南

就上面的例子,更好的写法就是使用 `IN` 语句替代:

SELECT * FROM Movie_quotes
WHERE Q_MOVIE IN (‘Star Wars’, ‘The Terminator’);

至此,我们一直在讨论如何从数据库中过滤数据。返回的行将按照它们进入(提交到)数据库的顺序进行排序。为了控制数据显示的顺序,我们可以通过包含 `ORDER BY` 子句来过滤输出数据。`ORDER BY` 子句包含了指定分类顺序的一个、或多个列名:

SELECT column_1, column_1, ... column_n
FROM table_name
WHERE column_name operator value
ORDER BY column_name;

为了扩展我们刚才《Star Wars》电影台词的例子,现在按照年份排序:

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = ‘Star Wars’
ORDER BY Q_YEAR;

SQL 新手指南

一个列的排序,默认是按照从最低值到最高值升序排列。为了把列的排序改为降序,我们可以在列名后面加上 `DESC` 关键字:

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = ‘Star Wars’
ORDER BY Q_YEAR DESC;

SQL 新手指南

`ORDER BY` 语句不限于单个列。你可以包含逗号分隔的、列的清单来排序。返回的行将根据第一个指定列,然后按顺序根据接下来指定的列排序。切记,用来排序的列不必包含在被选择列的清单里。我们可以像这样来写查询:

SELECT Q_TEXT, Q_CHARACTER, Q_MOVIE FROM Movie_quotes
WHERE Q_MOVIE = ‘Star Wars’
ORDER BY Q_YEAR DESC;

更新数据

在我们开始插入数据之后,并没有被限制为只能读取数据。我们能够对任何行里的、任何列下的、任何数据进行修改。`UPDATE` 语句用于更新或修改记录。

`UPDATE` 的语法如下:

UPDATE table_name
SET column_name = new_value
WHERE column_name operator value;

当我们使用 `UPDATE` 时,慎重地构造一个 `WHERE` 子句是十分重要的。`WHERE` 子句指定了哪一条记录或哪些记录应该被更新。如果我们在执行 `UPDATE` 语句时、而没有使用 `WHERE` 子句,我们将更新指定列的所有数据。

让我们看看 `Movie_quotes` 表里的电影台词。我们让所有的台词以标点符号结束,《The Terminator》除外。对于如何使用 `UPDATE` 语句,这是一个极好的例子:

UPDATE Movie_quotes
SET Q_TEXT = ‘I’ll be back!’
WHERE Q_MOVIE = ‘The Terminator’;

之前解释了,如果我们不小心遗漏了 `WHERE` 子句,或我们故意把所有的台词行更新为「I’ll be back!」。通过单单选中电影《The terminator》所在行,我们就可以更新指定行的一列数据。

删除数据

当数据库被大量使用时,从数据库中移除陈旧的数据,迟早会变得有必要。我们能够只删除表中的一些行、或删除整个表。

`DELETE` 语句用于删除表中的行。该命令的语法如下:

DELETE FROM table_name
WHERE column_name operator value;

重申,和 `UPDATE` 语句一样,`WHERE` 子句指定了哪一条记录或哪些记录应该被删除。如果没有指定 `WHERE` 子句,所有的行和列将被删除:

DELETE FROM Movie_quotes;

假设我们不再喜欢电影《Forrest Gump》了,想从电影中删除其台词。为了从电影中删除所有台词,我们可以编写如下 SQL 命令:

DELETE FROM Movie_quotes
WHERE Q_MOVIE = ‘Forrest Gump’;

最终,我们有了足够多的电影。我们对电影台词不再感兴趣了,我们想把兴趣移到音乐上。我们开始收集歌词。根据我们目前所学到的 SQL 知识,修改数据库是非常简单的。

首先,我们需要清空数据库里、不再感兴趣的数据。为了删除包含所有行的表,我们可以使用 `DROP TABLE` 语句。切记 `DROP TABLE` 语句不同于使用 `DELETE` 语句,和删除表里的所有记录也不同。删除表里的所有记录,会留给我们表本身及其定义的所有表结构;包括列的数据类型定义和该表的其它相关的数据库信息。`DROP TABLE` 移除了表、移除表的定义,还有所有的行。

`DROP TABLE` 语句的语法如下:

DROP TABLE table_name;

为了从数据库中删除 `Movie_quotes`,我们可以这样写:

DROP TABLE Movie_quotes;

现在我们的数据库是空的,准备接受新数据。我们从所有的 CRUD 过程开始,创建名为 `Song_Lyrics` 的新表,根据我们新收藏的歌曲,建立一个歌词数据库。

结论

本文我们浏览了涵盖 CRUD 四个基本的数据库功能:如何创建新数据、读取数据、更新我们想要修改的数据、以及最后的如何删除不想要的数据。这包含了基本的、但是最重要的 SQL 命令,比如:`CREATE TABLE`, `INSERT INTO`, `SELECT`, `UPDATE`, `DELETE` 和 `DROP`。

这些基本的 SQL 命令支持大量的数据管理,但是每个介绍到的命令都有很多选项和额外的功能,有些是本文没有介绍的,要注意这一点。总之,当 SQL 开发人员新手在开始数据库工作、以及使用一门新语言 SQL 时,本文中的基本知识应该能为他们开个好头。

The above is the detailed content of A SQL guide that programmers who develop PHP must master. For more information, please follow other related articles on the PHP Chinese website!

Statement
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
How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationHow to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLAn efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesSteps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

See all articles

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

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),