SQL bermaksud Bahasa Pertanyaan Berstruktur. Perintah SQL adalah petunjuk yang digunakan untuk berkomunikasi dengan pangkalan data untuk melakukan tugas, fungsi, dan pertanyaan dengan data.
Perintah SQL dapat digunakan untuk mencari pangkalan data dan melakukan fungsi lain seperti membuat tabel, menambahkan data ke tabel, mengubah data, dan menjatuhkan tabel.
Berikut adalah senarai arahan asas SQL (kadang-kadang disebut klausa) yang harus anda ketahui sama ada anda akan bekerja dengan SQL.
PILIH dan DARI
Yang SELECT
sebahagian daripada pertanyaan menentukan lajur data untuk menunjukkan dalam keputusan. Ada juga pilihan yang dapat Anda terapkan untuk menunjukkan data yang bukan lajur tabel.
Contoh di bawah menunjukkan tiga lajur SELECT
mengedit FROM
jadual "pelajar" dan satu lajur yang dikira. Pangkalan data menyimpan studentID, FirstName, dan LastName pelajar. Kita boleh menggabungkan lajur Nama Pertama dan Nama Akhir untuk membuat lajur dikira Nama Penuh.
SELECT studentID, FirstName, LastName, FirstName + ' ' + LastName AS FullName FROM student;
+-----------+-------------------+------------+------------------------+ | studentID | FirstName | LastName | FullName | +-----------+-------------------+------------+------------------------+ | 1 | Monique | Davis | Monique Davis | | 2 | Teri | Gutierrez | Teri Gutierrez | | 3 | Spencer | Pautier | Spencer Pautier | | 4 | Louis | Ramsey | Louis Ramsey | | 5 | Alvin | Greene | Alvin Greene | | 6 | Sophie | Freeman | Sophie Freeman | | 7 | Edgar Frank "Ted" | Codd | Edgar Frank "Ted" Codd | | 8 | Donald D. | Chamberlin | Donald D. Chamberlin | | 9 | Raymond F. | Boyce | Raymond F. Boyce | +-----------+-------------------+------------+------------------------+ 9 rows in set (0.00 sec)
BUAT JADUAL
CREATE TABLE
melakukan apa yang terdengar: ia membuat jadual dalam pangkalan data. Anda boleh menentukan nama jadual dan lajur yang seharusnya ada dalam jadual.
CREATE TABLE table_name ( column_1 datatype, column_2 datatype, column_3 datatype );
JADUAL ALTER
ALTER TABLE
mengubah struktur jadual. Inilah cara anda menambah lajur ke pangkalan data:
ALTER TABLE table_name ADD column_name datatype;
SEMAK
The CHECK
kekangan digunakan untuk menghadkan julat nilai yang boleh diletakkan dalam satu ruangan.
Sekiranya anda menentukan CHECK
kekangan pada satu lajur, ia hanya membenarkan nilai tertentu untuk lajur ini. Sekiranya anda menentukan CHECK
batasan pada jadual, ia dapat membatasi nilai dalam lajur tertentu berdasarkan nilai di lajur lain dalam baris.
SQL berikut membuat CHECK
kekangan pada lajur "Umur" ketika jadual "Orang" dibuat. Yang CHECK
ini untuk memastikan kekangan bahawa anda tidak boleh mempunyai mana-mana orang di bawah 18 tahun.
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );
Untuk membenarkan penamaan CHECK
batasan, dan untuk menentukan CHECK
kekangan pada beberapa lajur, gunakan sintaks SQL berikut:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City="Sandnes") );
DI MANA
(AND
,OR
, IN
, BETWEEN
, Dan LIKE
)
The WHERE
fasal digunakan untuk mengehadkan bilangan baris dikembalikan.
Sebagai contoh, pertama kami akan menunjukkan kepada anda satu SELECT
kenyataan dan keputusan tanpa satu WHERE
kenyataan. Kemudian kami akan menambah WHERE
pernyataan yang menggunakan kelima kelayakan di atas.
SELECT studentID, FullName, sat_score, rcd_updated FROM student;
+-----------+------------------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+------------------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | 2017-08-16 15:34:50 | | 3 | Spencer Pautier | 1000 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | | 6 | Sophie Freeman | 1200 | 2017-08-16 15:34:50 | | 7 | Edgar Frank "Ted" Codd | 2400 | 2017-08-16 15:35:33 | | 8 | Donald D. Chamberlin | 2400 | 2017-08-16 15:35:33 | | 9 | Raymond F. Boyce | 2400 | 2017-08-16 15:35:33 | +-----------+------------------------+-----------+---------------------+ 9 rows in set (0.00 sec)
Sekarang, kami akan mengulangi SELECT
pertanyaan tetapi kami akan menghadkan baris yang dikembalikan menggunakan WHERE
penyataan.
STUDENT studentID, FullName, sat_score, recordUpdated FROM student WHERE (studentID BETWEEN 1 AND 5 OR studentID = 8) AND sat_score NOT IN (1000, 1400);
+-----------+----------------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+----------------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | | 8 | Donald D. Chamberlin | 2400 | 2017-08-16 15:35:33 | +-----------+----------------------+-----------+---------------------+ 5 rows in set (0.00 sec)
KEMASKINI
Untuk mengemas kini rekod dalam jadual, anda menggunakan UPDATE
pernyataan.
Gunakan WHERE
syarat untuk menentukan rekod yang ingin anda kemas kini. Anda boleh mengemas kini satu atau lebih lajur pada satu masa. Sintaksnya adalah:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Berikut adalah contoh mengemas kini Nama rekod dengan Id 4:
UPDATE Person SET Name = “Elton John” WHERE Id = 4;
Anda juga boleh mengemas kini lajur dalam jadual dengan menggunakan nilai dari jadual lain. Gunakan JOIN
klausa untuk mendapatkan data dari beberapa jadual. Sintaksnya adalah:
UPDATE table_name1 SET table_name1.column1 = table_name2.columnA table_name1.column2 = table_name2.columnB FROM table_name1 JOIN table_name2 ON table_name1.ForeignKey = table_name2.Key
Berikut adalah contoh mengemas kini Pengurus semua rekod:
UPDATE Person SET Person.Manager = Department.Manager FROM Person JOIN Department ON Person.DepartmentID = Department.ID
KUMPULAN OLEH
GROUP BY
membolehkan anda menggabungkan baris dan data agregat.
Berikut adalah sintaks GROUP BY
:
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
MEMPUNYAI
HAVING
membolehkan anda menyaring data yang digabungkan dengan GROUP BY
klausa sehingga pengguna mendapat sekumpulan rekod terhad untuk dilihat.
Berikut adalah sintaks HAVING
:
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;
AVG ()
"Rata-rata" digunakan untuk menghitung rata-rata lajur numerik dari set baris yang dikembalikan oleh pernyataan SQL.
Berikut adalah sintaks untuk menggunakan fungsi:
SELECT groupingField, AVG(num_field) FROM table1 GROUP BY groupingField
Berikut adalah contoh menggunakan jadual pelajar:
SELECT studentID, FullName, AVG(sat_score) FROM student GROUP BY studentID, FullName;
SEBAGAI
AS
membolehkan anda menamakan semula lajur atau jadual menggunakan alias.
SELECT user_only_num1 AS AgeOfServer, (user_only_num1 - warranty_period) AS NonWarrantyPeriod FROM server_table
Ini menghasilkan output seperti di bawah.
+-------------+------------------------+ | AgeOfServer | NonWarrantyPeriod | +-------------+------------------------+ | 36 | 24 | | 24 | 12 | | 61 | 49 | | 12 | 0 | | 6 | -6 | | 0 | -12 | | 36 | 24 | | 36 | 24 | | 24 | 12 | +-------------+------------------------+
Anda juga boleh menggunakan AS untuk memberikan nama ke jadual untuk memudahkan rujukan dalam bergabung.
SELECT ord.product, ord.ord_number, ord.price, cust.cust_name, cust.cust_number FROM customer_table AS cust JOIN order_table AS ord ON cust.cust_number = ord.cust_number
Ini menghasilkan output seperti di bawah.
+-------------+------------+-----------+-----------------+--------------+ | product | ord_number | price | cust_name | cust_number | +-------------+------------+-----------+-----------------+--------------+ | RAM | 12345 | 124 | John Smith | 20 | | CPU | 12346 | 212 | Mia X | 22 | | USB | 12347 | 49 | Elise Beth | 21 | | Cable | 12348 | 0 | Paul Fort | 19 | | Mouse | 12349 | 66 | Nats Back | 15 | | Laptop | 12350 | 612 | Mel S | 36 | | Keyboard| 12351 | 24 | George Z | 95 | | Keyboard| 12352 | 24 | Ally B | 55 | | Air | 12353 | 12 | Maria Trust | 11 | +-------------+------------+-----------+-----------------+--------------+
PERINTAH OLEH
ORDER BY
gives us a way to sort the result set by one or more of the items in the SELECT
section. Here is an SQL sorting the students by FullName in descending order. The default sort order is ascending (ASC
) but to sort in the opposite order (descending) you use DESC
.
SELECT studentID, FullName, sat_score FROM student ORDER BY FullName DESC;
COUNT
COUNT
will count the number of rows and return that count as a column in the result set.
Here are examples of what you would use COUNT for:
- Counting all rows in a table (no group by required)
- Counting the totals of subsets of data (requires a Group By section of the statement)
This SQL statement provides a count of all rows. Note that you can give the resulting COUNT column a name using “AS”.
SELECT count(*) AS studentCount FROM student;
DELETE
DELETE
is used to delete a record in a table.
Be careful. You can delete all records of the table or just a few. Use the WHERE
condition to specify which records you want to delete. The syntax is:
DELETE FROM table_name WHERE condition;
Here is an example deleting from the table Person the record with Id 3:
DELETE FROM Person WHERE Id = 3;
INNER JOIN
JOIN
, also called Inner Join, selects records that have matching values in two tables.
SELECT * FROM A x JOIN B y ON y.aId = x.Id
LEFT JOIN
A LEFT JOIN
returns all rows from the left table, and the matched rows from the right table. Rows in the left table will be returned even if there was no match in the right table. The rows from the left table with no match in the right table will have null
for right table values.
SELECT * FROM A x LEFT JOIN B y ON y.aId = x.Id
RIGHT JOIN
A RIGHT JOIN
returns all rows from the right table, and the matched rows from the left table. Opposite of a left join, this will return all rows from the right table even where there is no match in the left table. Rows in the right table that have no match in the left table will have null
values for left table columns.
SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id
FULL OUTER JOIN
A FULL OUTER JOIN
returns all rows for which there is a match in either of the tables. So if there are rows in the left table that do not have matches in the right table, those will be included. Also, if there are rows in the right table that do not have matches in the left table, those will be included.
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName
INSERT
INSERT
is a way to insert data into a table.
INSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, 'value_2', value_3);
LIKE
LIKE
is used in a WHERE
or HAVING
(as part of the GROUP BY
) to limit the selected rows to the items when a column has a certain pattern of characters contained in it.
This SQL will select students that have FullName
starting with “Monique” or ending with “Greene”.
SELECT studentID, FullName, sat_score, rcd_updated FROM student WHERE FullName LIKE 'Monique%' OR FullName LIKE '%Greene';
+-----------+---------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+---------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | +-----------+---------------+-----------+---------------------+ 2 rows in set (0.00 sec)
You can place NOT
before LIKE
to exclude the rows with the string pattern instead of selecting them. This SQL excludes records that contain “cer Pau” and “Ted” in the FullName column.
SELECT studentID, FullName, sat_score, rcd_updated FROM student WHERE FullName NOT LIKE '%cer Pau%' AND FullName NOT LIKE '%"Ted"%';
+-----------+----------------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+----------------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | | 6 | Sophie Freeman | 1200 | 2017-08-16 15:34:50 | | 8 | Donald D. Chamberlin | 2400 | 2017-08-16 15:35:33 | | 9 | Raymond F. Boyce | 2400 | 2017-08-16 15:35:33 | +-----------+----------------------+-----------+---------------------+ 7 rows in set (0.00 sec)