Helaian Penipuan SQL: Panduan Pantas Untuk Perintah Penting

Sebagai pengaturcara atau penganalisis data, anda sering mendapati diri anda bekerja dengan jumlah data yang besar. Salah satu alat paling berkuasa yang anda gunakan ialah SQL (Bahasa Pertanyaan Berstruktur).

Helaian tipu SQL ini memberi anda objek dan perintah SQL yang paling biasa, termasuk jenis data, pernyataan DML dan DDL, fungsi agregat, fungsi rentetan dan tarikh serta subkueri.

Ia direka bentuk untuk menjadi rujukan pantas dan berguna yang boleh anda capai apabila anda berinteraksi dengan pangkalan data hubungan.

Jika anda seperti kami dan ingin memuat turun dan mencetak atau menyimpan helaian cheat pada komputer anda, anda boleh memuat turunnya di bawah.

Muat turun Helaian Cheat SQL

Isi kandungan

• Jenis Data SQL
  • 1. Jenis Berangka
  • 2. Jenis Tarikh Dan Masa
  • 3. Jenis Rentetan
• SELECT Kenyataan
• Klausa MANA
• PESANAN MENGIKUT Klausa
• Menyertai Berbilang Jadual dalam SQL
  • 1. JOIN DALAM
  • 2. KIRI SERTAI
  • 3. SERTAI BETUL
  • 4. SERTAI PENUH
• Fungsi Agregat SQL
  • 1. Fungsi COUNT
  • 2. Fungsi SUM
  • 3. Fungsi AVG
  • 4. Fungsi MAX
  • 5. Fungsi MIN
• Fungsi Rentetan Biasa
• Fungsi Angka Biasa
• Fungsi Tarikh Biasa
• KUMPULAN MENGIKUT DAN MEMILIKI
  • 1. Kumpulan Mengikut Klausa
  • 2. Mempunyai Klausa
  • Petua Pantas
• Subkueri
  • 1. IN Operator
  • 2. Operator WUJUD
  • 3. MANA-MANA ​​Operator
  • 4. SEMUA Operator
• Manipulasi Data (DML)
  • 1. INSERT Kenyataan
  • 2. KEMASKINI Kenyataan
  • 3. PADAM Kenyataan
• Pengurusan Pangkalan Data Dengan DDL
  • 1. CIPTA Penyata
  • 2. Penyata ALTER
    • TAMBAH LAjur BAHARU
    • Ubah suai Jenis Data Lajur
    • Gugurkan Lajur
    • Tambah Kekangan Unik
    • Tambah kunci asing antara jadual
  • 3. Penyata DROP
• Transaksi
  • 1. BERMULA
  • 2. KOMITED
  • 3. KEMBALI
• Fikiran Akhir

Jenis Data SQL

Jenis data untuk sistem pengurusan pangkalan data tertentu (DBMS) boleh berbeza-beza (cth. Microsoft SQL Server vs MySQL ). Walau bagaimanapun, terdapat beberapa yang terdapat pada kebanyakan sistem. Anda boleh membahagikannya kepada tiga kategori:

1. angka

2. Tarikh dan masa

3. Tali

1. Jenis Berangka

Ini adalah jenis angka yang paling biasa:

• INTEGER : Nombor bulat tanpa titik perpuluhan.

• SMALLINT : Julat nombor bulat yang lebih kecil

• BIGINT : Julat nombor bulat yang lebih besar.

• PERPULUHAN(p, s) atau NUMERIC(p, s) : Sebagai contoh, perpuluhan(5,2) akan muat 123.45.

• REAL : Nombor titik terapung, dengan ketepatan sekurang-kurangnya 6 digit perpuluhan.

• FLOAT(n) : Nombor titik terapung, dengan ketepatan sekurang-kurangnya n digit.

2. Jenis Tarikh Dan Masa

• TARIKH : Nilai tarikh, biasanya dalam format 'YYYY-MM-DD'.

• MASA : Nilai masa, biasanya dalam format 'HH:MM:SS'.

• DATETIME atau TIMESTAMP : Gabungan nilai tarikh dan masa.

3. Jenis Rentetan

• CHAR(n) : Rentetan panjang tetap dengan n aksara.

• VARCHAR(n) atau CHARACTER VARYING(n) : Rentetan panjang berubah-ubah.

• TEKS : Rentetan panjang berubah-ubah dengan panjang maksimum ditentukan oleh DBMS.

SELECT Kenyataan

Pernyataan SELECT digunakan untuk mendapatkan data daripada satu atau lebih jadual. Anda boleh menentukan lajur yang anda ingin dapatkan semula dan dari jadual mana. Kenyataan SELECT asas kelihatan seperti ini:

SELECT column1, column2
FROM table;

Untuk mendapatkan semula semua rekod daripada lajur 'name' dan 'country_id' daripada jadual 'city', pertanyaan SQL anda kelihatan seperti ini:

SELECT name, country_id
FROM city;

Klausa MANA

Klausa WHERE membenarkan anda menapis hasil pernyataan SELECT berdasarkan syarat tertentu.

SELECT column1, column2
FROM table
WHERE condition;

Untuk mendapatkan semula rekod daripada jadual 'bandar' yang 'populasi' melebihi 1,000,000, pertanyaan anda kelihatan seperti ini:

SELECT name, population
FROM city
WHERE population > 1000000;

PESANAN MENGIKUT Klausa

Klausa ORDER BY membolehkan anda mengisih hasil pernyataan SELECT mengikut satu atau lebih lajur. Anda boleh mengisih keputusan dalam susunan menaik (ASC) atau menurun (DESC):

SELECT column1, column2
FROM table
ORDER BY column1 ASC, column2 DESC;

Sebagai contoh, untuk mendapatkan semula rekod daripada jadual 'bandar' yang diisih mengikut 'populasi' dalam tertib menurun, pertanyaan anda kelihatan seperti ini:

SELECT name, population
FROM city
ORDER BY population DESC;

Menyertai Berbilang Jadual dalam SQL

Terdapat empat gabungan yang biasa digunakan dalam SQL:

1. SERTAI DALAM

2. KIRI SERTAI

3. BETUL SERTAI

4. SERTAI PENUH

1. JOIN DALAM

INNER JOIN mendapatkan semula rekod yang mempunyai nilai yang sepadan dalam kedua-dua jadual.

Mari kita ambil contoh pangkalan data artis dan album, dan anda ingin mencari semua gabungan artis dan album. Inilah JOIN DALAM:

SELECT *
FROM artists AS a
INNER JOIN albums AS b
ON a.artist_id = b.artist_id;

Dengan JOIN DALAM, hanya baris dengan nilai yang sepadan dalam medan yang ditentukan akan dikembalikan dalam keputusan.

2. KIRI SERTAI

A LEFT JOIN juga dikenali sebagai LEFT OUTER JOIN. Ia mengembalikan semua rekod dari jadual kiri dan rekod sepadan dari jadual kanan. Jika tiada padanan dalam jadual yang betul, hasilnya akan mengandungi nilai NULL.

Contohnya, untuk mendapatkan senarai semua artis dan album masing-masing (jika mereka ada), anda boleh menggunakan LEFT JOIN:

SELECT *
FROM artists AS a
LEFT JOIN albums AS b
ON a.artist_id = b.artist_id;

Pertanyaan ini akan mengembalikan semua artis, walaupun mereka tidak mempunyai sebarang album yang dikaitkan dengan mereka dalam jadual album.

3. SERTAI BETUL

A RIGHT JOIN juga dikenali sebagai RIGHT OUTER JOIN. Ia mengembalikan semua rekod dari jadual kanan dan rekod sepadan dari jadual kiri. Jika tiada padanan dalam jadual kiri, hasilnya akan mengandungi nilai NULL.

Contohnya, untuk mendapatkan maklumat tentang semua album dan artis berkaitannya (jika wujud), anda akan menggunakan RIGHT JOIN:

SELECT *
FROM artists AS a
RIGHT JOIN albums AS b
ON a.artist_id = b.artist_id;

Pertanyaan ini akan mengembalikan semua album, walaupun mereka tidak mempunyai artis yang berkaitan dalam jadual artis.

4. SERTAI PENUH

SERTAI PENUH juga dikenali sebagai SERTAI LUAR PENUH. Ia menggabungkan hasil gabungan KIRI dan KANAN. Dalam erti kata lain, ia mengembalikan semua baris dari jadual kiri dan kanan dan mengisi nilai yang hilang dengan NULL apabila tiada padanan.

Berikut ialah contoh menggunakan jadual artis dan album:

SELECT *
FROM artists AS a
FULL JOIN albums AS b
ON a.artist_id = b.artist_id;

Pertanyaan ini mengembalikan semua baris daripada kedua-dua jadual, mengisi NULL di mana tiada padanan dalam mana-mana jadual.

Fungsi Agregat SQL

Fungsi agregat digunakan untuk mengira hasil tunggal daripada set nilai input. Mereka dipanggil "agregat" kerana mereka mengambil berbilang input dan mengembalikan satu output. Yang paling biasa ialah:

1. COUNT

2. JUMLAH

3. AVG

4. MAX

5. MIN

1. Fungsi COUNT

Fungsi COUNT membolehkan anda mengira bilangan baris dalam hasil pertanyaan. Anda boleh menggunakan fungsi agregat ini untuk menentukan jumlah bilangan rekod dalam jadual atau bilangan rekod yang sepadan dengan kriteria tertentu.

Berikut ialah contoh:

SELECT COUNT(*) FROM employees;

Pertanyaan ini akan mengembalikan jumlah bilangan pekerja dalam jadual 'pekerja'. Perlu diingat bahawa menambah klausa WHERE boleh memperhalusi hasil anda:

SELECT COUNT(*) FROM employees WHERE department = 'HR';

2. Fungsi SUM

The SUM function calculates the total sum of a numeric column. It’s useful when you need to calculate the total value of a particular numeric field. For example, this query returns the total sum of all employee salaries:

SELECT SUM(salary) FROM employees;

3. AVG Function

The AVG function computes the average value of a numeric column. This function is helpful when you want to find the average of a particular numeric field. For instance, this query returns the average salary of all employees:

SELECT AVG(salary) FROM employees;

4. MAX Function

The MAX function finds the maximum value of a column. This is often used to find the highest value in a numeric field or the most recent date in a datetime field. For example, this query returns the highest salary:

SELECT MAX(salary) FROM employees;

5. MIN Function

Lastly, the MIN function helps you find the minimum value of a column. For example, this query returns the lowest salary:

SELECT MIN(salary) FROM employees;

Remember, you can use WHERE clauses in these queries and JOIN with multiple tables.

Common String Functions

Here are the most common string functions that are found in most SQL dialects (the exact syntax can vary):

1. LEN or LENGTH(string): Returns the length of a string.

2. UPPER(string): Converts a string to upper case.

3. LOWER(string): Converts a string to lower case.

4. SUBSTR or SUBSTRING(string, start, length): Extracts a portion from a string.

5. TRIM(string): Removes leading and trailing spaces from a string.

6. LTRIM(string): Removes leading spaces from a string.

7. RTRIM(string): Removes trailing spaces from a string.

Common Numeric Functions

Here are the most common numeric functions that are found in most SQL dialects (the exact syntax can vary):

1. ABS(number): Returns the absolute value of a number.

2. ROUND(number, decimal_places): Rounds a number to a certain number of decimal places.

3. FLOOR(number): Rounds down the number to the nearest integer.

4. CEIL or CEILING(number): Rounds up the number to the nearest integer.

5. RAND(): Returns a random float value from 0 to 1.

6. MOD(n, m): Returns the remainder of n divided by m.

7. POWER(base, exponent): Raises a number to the power of another number.

8. LOG(number): Returns the natural logarithm of a number.

Common Date Functions

Here are the most common date functions that are found in most SQL dialects (the exact syntax can vary):

1. NOW(): Returns the current date and time.

2. DATE(datetime): Extracts the date part of a date or datetime expression.

3. TIME(datetime): Extracts the time part of a date or datetime expression.

4. YEAR(date): Returns the year part.

5. MONTH(date): Returns the month part.

6. DAY(date): Returns the day of the month part.

7. HOUR(time): Returns the hour part from a time.

8. MINUTE(time): Returns the minute part from a time.

9. SECOND(time): Returns the second part from a time.

GROUP BY And HAVING

When working with SQL queries, you may want to further summarize and filter your aggregated data. The GROUP BY and HAVING clauses provide this functionality.

1. Group By Clause

The GROUP BY clause allows you to group rows that share the same values in specified columns. It is commonly used with aggregate functions. This is the syntax:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2;

For example, if you want to calculate the total sales amount for each product category, this is the query:

SELECT product_category, SUM(sales_amount)
FROM sales_data
GROUP BY product_category;

TIP: Combining GROUP BY and COUNT is a good way of finding duplicate values.

2. Having Clause

If you want to filter the aggregated results further, you can use the HAVING clause. The syntax is:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition;

If you want to find product categories with total sales of more than $1,000,000, you would write:

SELECT product_category, SUM(sales_amount)
FROM sales_data
GROUP BY product_category
HAVING SUM(sales_amount) > 1000000;

Quick Tips

• Always use the GROUP BY clause before the HAVING clause.

• The SELECT statement can only contain specified column names, aggregate functions, constants, and expressions.

• When using the HAVING clause, filter conditions should be applied to the aggregate functions rather than directly to the grouped columns.

By understanding and properly applying the GROUP BY and HAVING clauses, you can better organize and analyze your data using SQL.

Subqueries

A subquery is also known as an inner or nested query. This is a query embedded within another SQL statement (such as a SELECT statement) or even inside another subquery.

Subqueries allow you to retrieve data based on the output of another query. The most common operators used with subqueries are:

1. IN

2. EXISTS

3. ANY

4. ALL

1. IN Operator

The IN operator tests if a value is within a set of values generated by the inner query. The syntax for using the IN operator with a subquery is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM other_table);

This returns rows from the outer query where the specified column value matches any of the values provided by the subquery.

Suppose you have an employee table and a departments table. You want to find employees who work in departments based at the head office. Here is a sample query

SELECT first_name, last_name
FROM employee
WHERE department IN (SELECT department FROM departments
                     WHERE location = "HQ");

For a more in-depth look, check out our article on the SQL WHERE IN syntax.

2. EXISTS Operator

The EXISTS operator checks if there is at least one row resulting from the subquery. You can use the EXISTS operator to filter rows based on the existence of related data in another table. This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT column_name FROM other_table WHERE condition);

When the subquery returns at least one row, the EXISTS operator returns true, and the relevant rows from the outer query are included in the result.

3. ANY Operator

The ANY operator is used to compare a value to any value in a set of values provided by a subquery. It’s commonly used with comparison operators like =, <, >, <=, or >=.

This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY (SELECT column_name FROM other_table WHERE condition);

This will return rows from the outer query where the specified column value meets the condition against any value from the subquery.

4. ALL Operator

The ALL operator compares a value to all values within a set of values provided by a subquery. The conditions must be true for every value in the subquery’s result. This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL (SELECT column_name FROM other_table WHERE condition);

This returns rows from the outer query only if the specified column value satisfies the condition against all values in the subquery’s output.

Data Manipulation (DML)

Data Manipulation Language (DML) is a sub-language within SQL for managing and updating data. The most common statements are:

1. INSERT

2. UPDATE

3. DELETE

1. INSERT Statement

The INSERT statement allows you to insert rows into a table. Here’s the basic syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

For example, if you want to insert a new row into a ‘users’ table with columns ‘id’, ‘name’, and ’email’, you would use the following query:

INSERT INTO users (id, name, email)
VALUES (1, 'John Doe', '[email protected]');

2. UPDATE Statement

The UPDATE statement allows you to modify existing row data in a table. This is the syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

For example, if you want to update the email address of a user with the id ‘1’ in the ‘users’ table, your query would look like this:

UPDATE users
SET email = '[email protected]'
WHERE id = 1;

TIP: remember to include a WHERE clause to avoid updating all rows in the table by mistake.

3. DELETE Statement

The DELETE statement allows you to remove rows from a table. Here’s the syntax:

DELETE FROM table_name
WHERE condition;

Sebagai contoh, jika anda ingin memadamkan pengguna dengan id '1' daripada jadual 'pengguna', pertanyaan anda akan kelihatan seperti ini:

DELETE FROM users
WHERE id = 1;

TIP : sentiasa sertakan klausa WHERE untuk menentukan baris mana yang hendak dipadamkan dan untuk mengelakkan pemadaman semua baris dalam jadual.

Pengurusan Pangkalan Data Dengan DDL

Bahasa Definisi Data (DDL) ialah sub-bahasa SQL yang digunakan untuk mencipta dan mengubah jadual dan pangkalan data itu sendiri. Pernyataan DDL yang paling biasa ialah:

1. CIPTA

2. ALTER

3. JATUHKAN

1. CIPTA Penyata

Pernyataan CREATE membolehkan anda mencipta objek pangkalan data baharu, seperti jadual, paparan atau indeks baharu. Apabila membuat jadual baharu, anda perlu menentukan lajur, jenis datanya dan sebarang kekangan.

Berikut ialah contoh membuat jadual pesanan:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    product VARCHAR(255) NOT NULL,
    customer_id INT NOT NULL
);

PETUA: pilih jenis data dan kekangan yang sesuai untuk memastikan integriti data dalam jadual anda.

Untuk paparan yang lebih terperinci, lihat artikel kami tentang operasi jadual SQL asas .

2. Penyata ALTER

Pernyataan ALTER membantu anda mengubah suai objek pangkalan data sedia ada. Kegunaan biasa termasuk:

• menambah, mengubah suai atau menjatuhkan lajur.

• menambah atau mengalih keluar kekangan daripada jadual sedia ada.

• menambah kunci utama dan asing.

TAMBAH LAjur BAHARU

ALTER TABLE users ADD COLUMN age INTEGER;

Ubah suai Jenis Data Lajur

ALTER TABLE users ALTER COLUMN age TYPE FLOAT;

Gugurkan Lajur

ALTER TABLE users DROP COLUMN age;

Tambah Kekangan Unik

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE(email);

Tambah kunci asing antara jadual

ALTER TABLE users ADD FOREIGN KEY (country_id) REFERENCES Country(country_id);

3. Penyata DROP

Pernyataan DROP membolehkan anda mengalih keluar objek pangkalan data seperti jadual, pandangan atau indeks. Gunakannya dengan berhati-hati, kerana ia akan memadamkan objek yang ditentukan dan semua datanya secara kekal.

Berikut ialah contoh:

DROP TABLE users;

PETUA : Pastikan anda mempunyai sandaran yang betul sebelum melaksanakan pernyataan DROP.

Jika anda ingin mengetahui lebih lanjut tentang pemodelan data, lihat video ini:

Transaksi

Transaksi memainkan peranan penting dalam mengekalkan integriti pangkalan data , terutamanya apabila berbilang operasi berkaitan dilaksanakan serentak. Terdapat tiga operasi asas dalam mengendalikan transaksi:

1. BERMULA

2. KOMITED

3. KEMBALIKAN

1. BERMULA

Pernyataan BEGIN menandakan permulaan transaksi. Setelah melaksanakan arahan ini, anda sedang mewujudkan titik permulaan untuk set pernyataan SQL anda.

BEGIN;

2. KOMITED

Untuk memuktamadkan perubahan anda dan mengekalkannya dalam pangkalan data, gunakan pernyataan COMMIT. Ini memastikan bahawa semua operasi dalam transaksi dilaksanakan dengan jayanya dan kekal.

COMMIT;

Berikut ialah contoh transaksi penuh menggunakan contoh klasik pemindahan dana antara akaun:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

3. KEMBALI

Apabila bekerja dengan urus niaga, adalah penting untuk mengetahui cara membuat asal perubahan apabila ralat berlaku. Pernyataan ROLLBACK membalikkan semua perubahan yang dibuat sejak permulaan transaksi:

ROLLBACK;

Berikut ialah contoh transaksi dengan pengendalian ralat menggunakan ROLLBACK:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
IF @@ERROR <> 0
    ROLLBACK;
ELSE
    COMMIT;

Fikiran Akhir

Seperti yang anda lihat dalam helaian tipu SQL ini, SQL menawarkan pelbagai arahan dan fungsi yang membolehkan anda mencipta, memanipulasi dan bertanya data dalam pangkalan data hubungan.

Daripada arahan asas seperti SELECT, INSERT, UPDATE, dan DELETE, kepada binaan yang lebih kompleks seperti JOIN dan subquery, kepada fungsi agregat yang telah kami bincangkan, SQL menyediakan fleksibiliti untuk mengendalikan pelbagai tugas data.

Teruskan kembali ke helaian panduan ini untuk mempercepatkan perjalanan anda untuk mempelajari SQL !