Asas SQL | SELECT Kenyataan | Pengurusan Pangkalan Data

pengenalan

Penyataan SELECT, salah satu pernyataan yang paling biasa digunakan dalam SQL, digunakan untuk memilih data dalam jadual. Makmal ini akan mempelajari SELECT dan cara menerapkannya pada amalan sebenar.

Objektif Pembelajaran

• PILIH sintaks Asas
• Syarat simbol matematik
• DAN ATAU DALAM
• Kad liar
• Isih
• Fungsi dan pengiraan terbina dalam SQL
• Subkueri dan menyertai pertanyaan

Persediaan

Sebelum kita mula, muat turun jadual data yang berkaitan dan buat pangkalan data bernama mysql_labex (3 jadual: jabatan, pekerja, projek).

Mulakan perkhidmatan MySQL dan log masuk sebagai root.

cd ~/project
sudo service mysql start
mysql -u root

Terdapat dua fail create-database.sql dan insert-data.sql, yang terletak dalam ~/project/.

Muatkan data dalam fail. Anda perlu memasukkan arahan dalam konsol MySQL untuk membina pangkalan data:

source ~/project/create-database.sql
source ~/project/insert-data.sql

Dalam penyata pengendalian pangkalan data, yang paling kerap digunakan, juga dianggap paling penting ialah pertanyaan SELECT. Dalam makmal sebelumnya, kami telah menggunakan SELECT * FROM table_name; kenyataan di banyak tempat untuk melihat segala-galanya dalam jadual. SELECT boleh digunakan dengan kata kunci pelbagai kekangan, yang merangkumi pelbagai ciri. Makmal ini akan memperkenalkan kegunaan ini secara terperinci.

Kenyataan SELECT asas

Format asas pernyataan SELECT:

SELECT row name  FROM table name WHERE constraint;

Jika anda ingin menanyakan semua kandungan jadual, maka soal nama lajur dengan asterisk *, yang mewakili semua lajur dalam jadual akan disoal. Dalam kebanyakan kes, kita hanya perlu melihat lajur jadual yang ditentukan, seperti untuk melihat nama dan umur jadual pekerja:

USE mysql_labex;
SELECT name,age FROM employee;

MariaDB [mysql_labex]> SELECT name,age FROM employee;
+------+------+
| name | age  |
+------+------+
| Tom  |   26 |
| Jack |   24 |
| Rose |   22 |
| Jim  |   35 |
| Mary |   21 |
| Alex |   26 |
| Ken  |   27 |
| Rick |   24 |
| Joe  |   31 |
| Mike |   23 |
| Jobs | NULL |
| Tony | NULL |
+------+------+
12 rows in set (0.000 sec)

Keadaan simbol matematik

Pernyataan SELECT selalunya mempunyai kekangan WHERE, digunakan untuk mencapai pertanyaan yang lebih tepat. WHERE kekangan boleh mempunyai notasi matematik (=, <,>,>=, <=). Kami baru sahaja bertanyakan nama dan umur, dan sekarang mari buat sedikit pengubahsuaian:

SELECT name,age FROM employee WHERE age>25;




</p>
<p>Tapis hasil dengan umur lebih 25:<br>
</p>

<pre class="brush:php;toolbar:false">MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age>25;
+------+------+
| name | age  |
+------+------+
| Tom  |   26 |
| Jim  |   35 |
| Alex |   26 |
| Ken  |   27 |
| Joe  |   31 |
+------+------+
5 rows in set (0.000 sec)

Atau cari nama, umur dan telefon pekerja bernama Mary:

SELECT name,age,phone FROM employee WHERE name='Mary';

Keputusan:

MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE name='Mary';
+------+------+--------+
| name | age  | phone  |
+------+------+--------+
| Mary |   21 | 100101 |
+------+------+--------+
1 row in set (0.000 sec)

"DAN" & "ATAU"

Kita boleh mempunyai lebih daripada satu kekangan selepas WHERE, dan berdasarkan hubungan logik keadaan ini, kita boleh menggunakan ATAU dan DAN untuk menyambung:

Penapis - umur kurang daripada 25, atau umur lebih daripada 30

SELECT name,age FROM employee WHERE age<25 OR age>30;

MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age<25 OR age>30;
+------+------+
| name | age  |
+------+------+
| Jack |   24 |
| Rose |   22 |
| Jim  |   35 |
| Mary |   21 |
| Rick |   24 |
| Joe  |   31 |
| Mike |   23 |
+------+------+
7 rows in set (0.000 sec)

Penapis - umur melebihi 25 tahun dan umur kurang daripada 30

SELECT name,age FROM employee WHERE age>25 AND age<30;

Jika kita perlu memasukkan umur 25 dan 30, gunakan umur ANTARA 25 DAN 30 :

MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age>25 AND age<30;
+------+------+
| name | age  |
+------+------+
| Tom  |   26 |
| Alex |   26 |
| Ken  |   27 |
+------+------+
3 rows in set (0.000 sec)

MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age BETWEEN 25 AND 30;
+------+------+
| name | age  |
+------+------+
| Tom  |   26 |
| Alex |   26 |
| Ken  |   27 |
+------+------+
3 rows in set (0.000 sec)

MASUK & BUKAN MASUK

Kata kunci IN ​​dan NOT IN ​​digunakan untuk menapis hasil dalam julat tertentu. Sebagai contoh, kami ingin mencari orang dalam dpt3 atau dpt4:

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');

Untuk TIDAK DALAM, seperti dalam perintah berikut, kami akan mendapatkan orang yang tidak berada di dpt1 mahupun dpt3:

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');

MariaDB [mysql_labex]> SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');
+------+------+--------+--------+
| name | age  | phone  | in_dpt |
+------+------+--------+--------+
| Tom  |   26 | 119119 | dpt4   |
| Rose |   22 | 114114 | dpt3   |
| Rick |   24 | 987654 | dpt3   |
| Mike |   23 | 110110 | dpt4   |
| Tony | NULL | 102938 | dpt3   |
+------+------+--------+--------+
5 rows in set (0.000 sec)

MariaDB [mysql_labex]> SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');
+------+------+--------+--------+
| name | age  | phone  | in_dpt |
+------+------+--------+--------+
| Tom  |   26 | 119119 | dpt4   |
| Jack |   24 | 120120 | dpt2   |
| Mary |   21 | 100101 | dpt2   |
| Joe  |   31 | 110129 | dpt2   |
| Mike |   23 | 110110 | dpt4   |
| Jobs | NULL |  19283 | dpt2   |
+------+------+--------+--------+
6 rows in set (0.000 sec)

Kad bebas

Kata kunci LIKE digunakan dengan kad bebas dalam pernyataan SQL, dengan kad bebas mewakili aksara yang tidak diketahui. Kad bebas dalam SQL ialah _ dan %. Yang _ mewakili aksara yang tidak ditentukan, %mewakili tak tentu aksara yang tidak ditentukan.

Sebagai contoh, jika anda hanya ingat bahawa empat digit pertama nombor telefon ialah 1101 dan dua digit terakhir dilupakan, anda boleh menggantikannya dengan dua _ kad bebas:

SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';

dan di sini kami mempunyai nombor telefon bermula dengan 1101:

MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
+------+------+--------+
| name | age  | phone  |
+------+------+--------+
| Joe  |   31 | 110129 |
| Mike |   23 | 110110 |
+------+------+--------+
2 rows in set (0.000 sec)

Dalam kes lain, seperti apabila anda hanya mengingati huruf pertama nama, dan anda tidak tahu panjang nama itu, kemudian gunakan % kad bebas dan bukannya aksara tidak tentu:

SELECT name,age,phone FROM employee WHERE name LIKE 'J%';

Di sini kami mempunyai nama yang bermula dengan J:

MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE name LIKE 'J%';
+------+------+--------+
| name | age  | phone  |
+------+------+--------+
| Jack |   24 | 120120 |
| Jim  |   35 | 100861 |
| Joe  |   31 | 110129 |
| Jobs | NULL |  19283 |
+------+------+--------+
4 rows in set (0.000 sec)

Isih hasil anda

Untuk menjadikan hasil pertanyaan lebih teratur dan mudah diikuti, kami mungkin perlu menyusunnya mengikut peraturan tertentu. PESAN OLEH amat berguna. Secara lalai, ORDER BY berada dalam susunan menaik dan dengan menggunakan ASC dan DESC, kita juga boleh mendapatkan hasil dalam menaik dan menurun pesanan.

Sebagai contoh, kami mengisih gaji dalam susunan menurun, pernyataan SQL:

SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;

MariaDB [mysql_labex]> SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;
+------+------+--------+--------+
| name | age  | salary | phone  |
+------+------+--------+--------+
| Jobs | NULL |   3600 |  19283 |
| Joe  |   31 |   3600 | 110129 |
| Ken  |   27 |   3500 | 654321 |
| Rick |   24 |   3500 | 987654 |
| Mike |   23 |   3400 | 110110 |
| Tony | NULL |   3400 | 102938 |
| Alex |   26 |   3000 | 123456 |
| Mary |   21 |   3000 | 100101 |
| Jim  |   35 |   3000 | 100861 |
| Rose |   22 |   2800 | 114114 |
| Jack |   24 |   2500 | 120120 |
| Tom  |   26 |   2500 | 119119 |
+------+------+--------+--------+
12 rows in set (0.000 sec)

Fungsi dan pengiraan terbina dalam SQL

SQL membenarkan pengiraan data dalam jadual. Dalam hal ini, SQL mempunyai lima fungsi terbina dalam yang melakukan hasil SELECT:

Function:	COUNT	SUM	AVG	MAX	MIN
For:	count numbers	sum up	average	maximum value	minimum value

The COUNT function can be used for any data type (because it is only a count), while SUM and AVG functions can only calculate numeric data types. MAX and MIN can be used for numeric, string, or datetime data types.

For example, when we want to calculate the maximum and minimum value of salary, we use a statement like this:

SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;

You may have noticed a tiny detail. Use AS keyword can rename value. E.g. Max value is renamed into max_salary:

MariaDB [mysql_labex]> SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
+------------+-------------+
| max_salary | MIN(salary) |
+------------+-------------+
|       3600 |        2500 |
+------------+-------------+
1 row in set (0.000 sec)

Subqueries

The SELECT statements discussed above all involve data in only one table, but sometimes you have to process multiple tables to get the information you need. For example, you want to know a few projects done by the department where the employee named "Tom" is located. Employee information is stored in the employee table, but the project information is stored in the project table.

We can use subqueries to deal with such situations:

SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

MariaDB [mysql_labex]> SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
    -> WHERE of_dpt IN
    -> (SELECT in_dpt FROM employee WHERE name='Tom');
+--------+---------------+
| of_dpt | count_project |
+--------+---------------+
| dpt4   |             2 |
+--------+---------------+
1 row in set (0.000 sec)

Subqueries can also be extended to three, four or more layers.

Join

When dealing with multiple tables, the subquery is only useful when the results are from the same table. However, if you need to display data in two or more tables, you must use the join operation.

The basic idea is to connect two or more tables as a new table to operate, as follows:

SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;

This result is the number of employees in each department, where employee id and name from the employee table, people_num from the department table:

MariaDB [mysql_labex]> SELECT id,name,people_num
    -> FROM employee,department
    -> WHERE employee.in_dpt = department.dpt_name
    -> ORDER BY id;
+----+------+------------+
| id | name | people_num |
+----+------+------------+
|  1 | Tom  |         15 |
|  2 | Jack |         12 |
|  3 | Rose |         10 |
|  4 | Jim  |         11 |
|  5 | Mary |         12 |
|  6 | Alex |         11 |
|  7 | Ken  |         11 |
|  8 | Rick |         10 |
|  9 | Joe  |         12 |
| 10 | Mike |         15 |
| 11 | Jobs |         12 |
| 12 | Tony |         10 |
+----+------+------------+
12 rows in set (0.000 sec)

Another connection statement format is to use the JOIN ON syntax. The statement is the same as:

SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;

Result is the same.

Summary

In this lab we learned the basic use of SELECT statement:

• Basic syntax
• Mathmatical symbol conditions
• AND OR IN
• Wildcards
• Sort
• SQL built-in functions and calculations
• Sunqueries and connection queries

---

? Practice Now: SQL's SELECT Statement

---

Want to Learn More?

• ? Learn the latest MySQL Skill Trees
• ? Read More MySQL Tutorials
• ? Join our Discord or tweet us @WeAreLabEx

Atas ialah kandungan terperinci Asas SQL | SELECT Kenyataan | Pengurusan Pangkalan Data. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!