Asas SQL | SELECT Kenyataan | Pengurusan Pangkalan Data

pengenalan

PILIH pernyataan, salah satu pernyataan yang paling biasa digunakan dalam SQL, digunakan untuk memilih data dalam jadual. Makmal ini akan mempelajari SELECT dan cara mengaplikasikannya 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 di ~/project/.

Muat 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 operasi 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, makamenyiasat nama lajurdengan 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

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

SELECT name,age FROM employee WHERE age>25; 
 
Tapis hasil dengan umur lebih 25:
 
 
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)
        

         Salin selepas log masuk
        
       

Atau cari nama, umur dan telefon pekerja bernama Mary:

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

Hasil:

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 syarat ini, kita boleh menggunakanORdanDANuntuk 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 tahun

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

Jika kita perlu memasukkan umur 25 dan 30, gunakanumur 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 kunciINdanNOT INdigunakan untuk menapis hasil dalam julat tertentu. Sebagai contoh, kami ingin mencari orang didpt3ataudpt4:

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

UntukTIDAK MASUK, seperti dalam arahan berikut, kita akan mendapatkan orang yang tidak masukdpt1mahupundpt3:

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 kunciLIKEdigunakan 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, %mewakilitak tentuaksara 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 bermula denganJ:

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.PESANAN OLEHamat berguna. Secara lalai,ORDER BYadalah dalam susunanmenaik, dan dengan menggunakanASCdanDESC, kita juga boleh mendapatkan hasil dalam tertib menaik dan menurun.

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:

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 thejoinoperation.

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!