sql查询前50条_您必须知道的前50条SQL查询
sql查询前50条
In this article, we’ll go over the most common SQL queries that you should know to be able to use SQL efficiently.
在本⽂中，我们将介绍最常见SQL查询，您应该知道它们能够有效地使⽤SQL。
将覆盖哪些SQL查询？ (What SQL Queries Will Be Covered?)
We’ll be going over queries that are used in the day-to-day situations of a database administrator. Commands by themselves will not really do anything unless they’re formed into a query. Let’s get right into this.
我们将遍历数据库管理员⽇常使⽤的查询。 除⾮将命令形成查询，否则命令本⾝不会真正执⾏任何操作。 让我们开始吧。
1.创建数据库和表 (1. Create Databases and Tables )
To start working with SQL tables, you’ll need to create them first. Tables reside within Databases and you can create both tables and databases using the CREATE keyword.
要开始使⽤SQL表，您需要⾸先创建它们。 表驻留在数据库中，您可以使⽤CREATE关键字创建表和数据库。
Let’s create a database, then enter into the database and then create a table.
让我们创建⼀个数据库，然后输⼊数据库，然后创建⼀个表。
1
2CREATE db_name;
3/* Use the specified database for executing further queries */
4USE db_name;
5CREATE TABLE authors (author_name VARCHAR(70), author_email VARCHAR(80), author_pay int);
2.将单值插⼊表中SQL查询 (2. SQL Queries to Insert Single Values into Tables)
After creating a table, it’s time to add data to the tables. Let’s add some data to our authors table.
创建表后，是时候向表中添加数据了。 让我们将⼀些数据添加到我们的authors表中。
1
2INSERT INTO authors (author_name, author_email)
3VALUES("Joe", "joe@journaldev", 50000);
4.将多个值插⼊表 (4. Insert Multiple Values into Tables)
If you have to insert multiple values, the above method is inefficient and adding them with a single query will be much better. You can insert multiple values with the below query.
如果必须插⼊多个值，则上述⽅法效率不⾼，并且通过单个查询添加它们会更好。 您可以使⽤以下查询插⼊多个值。
1
2INSERT INTO authors (author_name, author_email)
3VALUES ("Joe", "joe@journaldev", 50000),
4("Jane", "jane@journaldev", 70000),
5("John", "john@journaldev", 20000);
5. SQL查询以从表中检索所有数据 (5. SQL Queries to Retrieve All Data From A Table)
Now that you know how to create databases and tables and insert data into it, this query will help you display the data within the tables.
既然您知道如何创建数据库和表并将数据插⼊其中，此查询将帮助您在表中显⽰数据。
We use the select query to retrieve data from different tables. This is one of the queries that you’ll learn when you’re beginning with your SQL learning.
我们使⽤选择查询从不同的表中检索数据。 这是开始学习SQL时将学习的查询之⼀。
1
2SELECT * FROM authors;
6.从表中检索特定列 (6. Retrieve Specific Columns From A Table)
The above command will retrieve everything from the table. But if you want to retrieve only a specific column, you can use the below query.
上⾯的命令将从表中检索所有内容。 但是，如果您只想检索特定的列，则可以使⽤以下查询。
1
2SELECT author_name FROM authors;
7.使⽤WHERE关键字检索特定数据 (7. Retrieve Specific Data with WHERE Keyword)
If we use the * operator, the entire table is displayed. We can narrow down our results with the use of the WHERE keyword to display specific rows.
如果我们使⽤*运算符，则会显⽰整个表格。 我们可以使⽤WHERE关键字来显⽰特定⾏来缩⼩结果范围。
In the below query, I’ll extract the author with the email jane@journaldev.
在下⾯的查询中，我将使⽤电⼦邮件jane@journaldev提取作者。
1
2SELECT * FROM authors WHERE author_email = "jane@journaldev";
8.更新单⾏SQL查询 (8. SQL Queries to Update Single Rows)
Inserted data may need updates and changes and we can use the UPDATE command to update any rows.
插⼊的数据可能需要更新和更改，我们可以使⽤UPDATE命令更新任何⾏。
With a combination of UPDATE, SET and WHERE commands, we can update data for specific rows.
结合使⽤UPDATE，SET和WHERE命令，我们可以更新特定⾏的数据。
1
2UPDATE authors SET author_name="Jordan" where
3author_email="joe@jornaldev";
9.更新多⾏ (9. Update Multiple Rows)
If we skip the WHERE keyword from the above command, we’ll be able to update all the rows in a specific table.
如果从上述命令中跳过WHERE关键字，我们将能够更新特定表中的所有⾏。
1
2UPDATE authors SET author_name="Jordan";
The command will update all the author’s names to Jordan.
该命令会将所有作者的姓名更新为Jordan。
10.删除单⾏ (10. Delete Single Row)
You can delete single or multiple rows with the use of the DELETE command paired with the WHERE command.
您可以使⽤DELETE命令和WHERE命令配对来删除单⾏或多⾏。
Let’s delete the author with the email john@jornaldev.
让我们通过电⼦邮件john@jornaldev删除作者。
1
2DELETE FROM authors WHERE author_email="john@journaldev";
11.删除多⾏ (11. Delete Multiple Rows)
To delete multiple rows of the table, you can enter multiple WHERE conditions using the boolean AND or OR.
要删除表的多⾏，可以使⽤布尔AND或OR输⼊多个WHERE条件。
1
2DELETE FROM authors WHERE author_email="john@journaldev"
3OR author_email="jane@journaldev";
12.计数⾏ (12. Counting Rows)
We can count rows using the COUNT keyword. This will print the count of the author’s emails.
我们可以使⽤COUNT关键字对⾏进⾏计数。 这将打印作者的电⼦邮件计数。
1
2SELECT COUNT(author_email) FROM authors;
13.获取数据总和 (13. Get a Sum for Data)
Similar to how we used the COUNT keyword above, we can use the SUM keyword to get the total for a specific column. Let’s get the total pay for authors.
与我们上⾯使⽤COUNT关键字的⽅式类似，我们可以使⽤SUM关键字来获取特定列的总数。 让我们获取作者的总薪⽔。
1
2SELECT SUM(author_pay) FROM authors;
14.获取数据平均值 (14. Get Average Values for Data)
Now that we know how to get the total, let’s get the average pay for our authors.
现在，我们知道了如何获得总⾦额，现在让我们获取作者的平均⼯资。
1
2SELECT AVG(author_pay) FROM authors;
15.创建视图 (15. Creating Views)
Views are a very interesting feature of SQL. They’re like virtual tables that contain specific data that we’ve selected. We can manipulate and view data from those views
视图是SQL的⼀个⾮常有趣的功能。 它们就像虚拟表，其中包含我们选择的特定数据。 我们可以从这些视图中操纵和查看数据
1
2CREATE VIEW high_pay_authors AS SELECT * FROM authors
3WHERE author_pay > 50000;
This will create a virtual table named “high_pay_authors” which can be used as an independent table to view and manipulate data.
这将创建⼀个名为“ high_pay_authors”的虚拟表，该表可⽤作独⽴的表来查看和操作数据。
16.将列添加到表 (16. Add Columns to a Table)
Using the ALTER keyword, we can add columns to a table.
使⽤ALTER关键字，我们可以将列添加到表中。
1
2ALTER TABLE authors ADD author_age int;
distinct查询
This will add an integer column for the author’s age.
这将为作者的年龄添加⼀个整数列。
17.从表中删除列 (17. Remove Column From a Table)
With the use of the DROP keyword, you can also remove columns from a table.
通过使⽤DROP关键字，您还可以从表中删除列。
1
2ALTER TABLE authors DROP COLUMN author_age;
This will delete the column but make sure you have the required privileges to drop the columns.
这将删除该列，但请确保您具有删除这些列所需的特权。
18.⽤于查询值SQL查询 (18. SQL Queries to Search for Values )
We already looked at the WHERE keyword to search for specific values within a database table and return the row that has the specific value. But we don’t always know the exact value.
我们已经查看了WHERE关键字，以在数据库表中搜索特定值并返回具有特定值的⾏。 但是我们并不总是知道确切的值。
For such situations, we can use pattern matching with the LIKE keyword.
在这种情况下，我们可以将模式匹配与LIKE关键字⼀起使⽤。
1
2SELECT * FROM authors WHERE author_name LIKE "j%"
This query will pattern match all the names that start with the letter “j” which in our case will return all the rows.
此查询将匹配所有以字母“ j”开头的名称，在本例中将返回所有⾏。
19.从列交换数据 (19. Swapping Data From Columns)
To switch the data between two columns, you can directly use the command below.
要在两列之间切换数据，可以直接使⽤下⾯的命令。
1
2UPDATE authors SET author_email=author_name, author_name=author_email
This simple query will swap all the data from the author_email column to the author_name column and vice versa.
这个简单的查询会将所有数据从author_email列交换到author_name列，反之亦然。
20.重命名表 (20. Rename Tables )
Depending on your SQL version and privileges, you can use the below command to rename a table.
根据您SQL版本和特权，可以使⽤以下命令重命名表。
1
2sp_RENAME authors authors_renamed;
This command will rename our “authors” table to “authors_renamed”.
此命令会将我们的“作者”表重命名为“ authors_renamed”。
21.从表中返回唯⼀值 (21. Return Unique Values from a Table)
In our table above, we have multiple people but all of the names and emails are unique. If we had a table with multiple rows had columns with the same values, we use the DISTINCT keyword to return only unique values from the tables.
在上⾯的表格中，我们有多个⼈，但是所有的名称和电⼦邮件都是唯⼀的。 如果我们的表中的多⾏具有相同的值的列，则可以使⽤DISTINCT关键字从表中仅返回唯⼀值。
1
2SELECT DISTINCT author_name FROM authors;
22.⼦集表SQL查询 (22. SQL Queries for Subsetting Tables)
When working with larger databases, it only makes sense to view limited data at a time. The TOP command allows us to display only a specific number of rows from the table in the output.
当使⽤较⼤的数据库时，仅⼀次查看有限的数据才有意义。 TOP命令允许我们在输出中仅显⽰表中特定数量的⾏。
1
2SELECT TOP 50 FROM authors;
23.备份数据库SQL查询 (23. SQL Queries to Backup Databases)
This is one of the SQL queries that you must get used to or at least create a script to automate backups because backups are very important.
这是您必须习惯SQL查询之⼀，或者⾄少创建⼀个脚本来⾃动执⾏备份，因为备份⾮常重要。
1
2BACKUP DATABASE db_name
3TO DISK = "/home/databases/db_name-backup.db";
The above command will backup the database db_name to a file named db_name-backup.db.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。