Tutorial Pangkalan Data SQL Terbaik

SQL bermaksud Bahasa Pertanyaan Berstruktur. Ini adalah alat yang paling umum digunakan untuk memanipulasi dan mengelola data dalam pangkalan data relasional (sering disebut sebagai "pangkalan data SQL").

SQL biasanya disebut "sekuel". Variannya yang paling popular adalah MySQL, PostgreSQL, dan SQLite - versi SQL yang biasanya digunakan untuk membuat prototaip. Ini memperkenalkan konsep mengakses banyak catatan dengan satu perintah tunggal, menggunakan SQL Queries.

Kami mengesyorkan bermula dengan tutorial pangkalan data SQL 4 jam freeCodeCamp.

Kami juga mengesyorkan kursus Harvard CS50 mengenai pangkalan data dan SQL.

Dan jika anda berminat, inilah tutorial 9 jam keseluruhan mengenai reka bentuk pangkalan data hubungan sehingga anda dapat membina sistem RDBMS anda sendiri menggunakan SQL.

Beberapa pernyataan dan pertanyaan SQL biasa

Pernyataan Pilih SQL

Pilih dan Dari klausa

Bahagian PILIH pertanyaan biasanya untuk menentukan lajur data yang akan ditunjukkan dalam hasil. Ada juga pilihan yang dapat Anda terapkan untuk menunjukkan data yang bukan lajur tabel.

Contoh ini menunjukkan tiga lajur yang dipilih dari jadual "pelajar" dan satu lajur yang dikira. Pangkalan data menyimpan studentID, FirstName, dan LastName pelajar. Kita boleh menggabungkan lajur Nama Pertama dan 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)

Kekangan CHECK digunakan untuk mengehadkan julat nilai yang dapat diletakkan dalam lajur.

Sekiranya anda menentukan kekangan CHECK pada satu lajur, ia hanya membenarkan nilai tertentu untuk lajur ini.

Sekiranya anda menentukan batasan CHECK pada jadual, ia dapat membatasi nilai dalam lajur tertentu berdasarkan nilai di lajur lain di baris.

SEMAK SQL di BUAT JADUAL

SQL berikut membuat batasan CHECK pada lajur "Umur" ketika jadual "Orang" dibuat. Kekangan CHECK memastikan bahawa anda tidak boleh mempunyai orang di bawah 18 tahun:

MySQL:

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );

Akses SQL Server / Oracle / MS:

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18) );

Untuk membenarkan penamaan batasan CHECK, dan untuk menentukan batasan CHECK pada beberapa lajur, gunakan sintaks SQL berikut:

Akses MySQL / SQL Server / Oracle / MS:

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") );

SEMAK SQL pada ALTER TABLE

Untuk membuat kekangan CHECK pada lajur "Umur" apabila jadual sudah dibuat, gunakan SQL berikut:

Akses MySQL / SQL Server / Oracle / MS:

ALTER TABLE Persons ADD CHECK (Age>=18);

Untuk membenarkan penamaan batasan CHECK, dan untuk menentukan batasan CHECK pada beberapa lajur, gunakan sintaks SQL berikut:

Akses MySQL / SQL Server / Oracle / MS:

ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City="Sandnes");

Jatuhkan Kekangan CHECK

Untuk melepaskan kekangan CHECK, gunakan SQL berikut:

Akses SQL Server / Oracle / MS:

ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge;

MySQL:

ALTER TABLE Persons DROP CHECK CHK_PersonAge; 

SQL Di mana Klausa

WHEREFasal (dan / atau, IN, BETWEEN, dan LIKE)

The WHEREfasal digunakan untuk mengehadkan bilangan baris dikembalikan.

Dalam kes ini, kelima-lima ini akan digunakan adalah WHEREklausa yang agak tidak masuk akal .

Berikut adalah senarai pelajar penuh semasa untuk dibandingkan dengan WHEREset keputusan klausa:

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)

Baris akan ditunjukkan bahawa…

  • WHERE ID pelajar berumur antara 1 hingga 5 (termasuk)
  • OR pelajarID = 8

Berikut adalah pertanyaan yang dikemas kini, di mana sebarang rekod yang mempunyai skor SAT yang ada dalam senarai ini (1000, 1400) tidak akan ditunjukkan:

select 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)

* Seperti semua perkara SQL ini, terdapat JAUH LEBIH BANYAK bagi mereka daripada apa yang terdapat dalam panduan pengenalan ini.

Saya harap ini sekurang-kurangnya memberi anda cukup untuk memulakan.

Sila lihat manual untuk pengurus pangkalan data anda dan selamat mencuba pilihan yang berbeza sendiri.

Penyataan Kemas Kini SQL

Untuk mengemas kini rekod dalam jadual, anda menggunakan UPDATEpernyataan.

Be careful. You can update all records of the table or just a few. Use the WHERE condition to specify which records you want to update. It is possible to update one or more columns at a time. The syntax is:

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

Here is an example updating the Name of the record with Id 4:

UPDATE Person SET Name = “Elton John” WHERE Id = 4;

You can also update columns in a table by using values from other tables. Use JOIN clause to get data from multiple tables. The syntax is:

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

Here is an example updating Manager of all records:

UPDATE Person SET Person.Manager = Department.Manager FROM Person JOIN Department ON Person.DepartmentID = Department.ID

What an Update query can do

An update query gives the DBA or SQL-using programmer the ability to update many records with one command.

Important Safety Tip! Always have a backup copy of what you are about to change BEFORE you change it!

This section will:

  • add a new field to the student table
  • test the logic to update that field with a school assigned email address
  • update the new field.

Here is the student table as we start this process:

SELECT * FROM student;
+-----------+------------------------+-----------+------------------+---------------------+---------------------+ | studentID | FullName | sat_score | programOfStudy | rcd_Created | rcd_Updated | +-----------+------------------------+-----------+------------------+---------------------+---------------------+ | 1 | Monique Davis | 400 | Literature | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | Programming | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 | | 3 | Spencer Pautier | 1000 | Programming | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | Programming | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | Programming | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 | | 6 | Sophie Freeman | 1200 | Programming | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 | | 7 | Edgar Frank "Ted" Codd | 2400 | Computer Science | 2017-08-16 15:35:33 | 2017-08-16 15:35:33 | | 8 | Donald D. Chamberlin | 2400 | Computer Science | 2017-08-16 15:35:33 | 2017-08-16 15:35:33 | | 9 | Raymond F. Boyce | 2400 | Computer Science | 2017-08-16 15:35:33 | 2017-08-16 15:35:33 | +-----------+------------------------+-----------+------------------+---------------------+---------------------+ 9 rows in set (0.00 sec)

Alter the table and add a new field

 ALTER TABLE `fcc_sql_guides_database`.`student` ADD COLUMN `schoolEmailAdr` VARCHAR(125) NULL AFTER `programOfStudy`;

The student table after the alter is executed.

mysql> SELECT FullName, sat_score, programOfStudy, schoolEmailAdr FROM student; +------------------------+-----------+------------------+----------------+ | FullName | sat_score | programOfStudy | schoolEmailAdr | +------------------------+-----------+------------------+----------------+ | Monique Davis | 400 | Literature | NULL | | Teri Gutierrez | 800 | Programming | NULL | | Spencer Pautier | 1000 | Programming | NULL | | Louis Ramsey | 1200 | Programming | NULL | | Alvin Greene | 1200 | Programming | NULL | | Sophie Freeman | 1200 | Programming | NULL | | Edgar Frank "Ted" Codd | 2400 | Computer Science | NULL | | Donald D. Chamberlin | 2400 | Computer Science | NULL | | Raymond F. Boyce | 2400 | Computer Science | NULL | +------------------------+-----------+------------------+----------------+ 9 rows in set (0.00 sec)

TESTING the logic (VERY important step!)

SELECT FullName, instr(FullName," ") AS firstSpacePosition, concat(substring(FullName,1,instr(FullName," ")-1),"@someSchool.edu") AS schoolEmail FROM student;
+------------------------+--------------------+------------------------+ | FullName | firstSpacePosition | schoolEmail | +------------------------+--------------------+------------------------+ | Monique Davis | 8 | [email protected] | | Teri Gutierrez | 5 | [email protected] | | Spencer Pautier | 8 | [email protected] | | Louis Ramsey | 6 | [email protected] | | Alvin Greene | 6 | [email protected] | | Sophie Freeman | 7 | [email protected] | | Edgar Frank "Ted" Codd | 6 | [email protected] | | Donald D. Chamberlin | 7 | [email protected] | | Raymond F. Boyce | 8 | [email protected] | +------------------------+--------------------+------------------------+ 9 rows in set (0.00 sec)

A note about concat(): in MySQL this command is used to combined strings, not so in other SQL versions (check your manual). In this usage it works like this: The substring of the FullName field up to but not including the first space is combined with “@someSchool.edu”. In the real world this would HAVE TO be much more complex and you would need to ensure that the email address is unique.

Doing the update

We’ll pretend that this is what we want and update the table with this information:

UPDATE student SET schoolEmailAdr = concat(substring(FullName,1,instr(FullName," ")-1),"@someSchool.edu") WHERE schoolEmailAdr is NULL;

Success!

mysql> SELECT FullName, sat_score, programOfStudy, schoolEmailAdr FROM student; +------------------------+-----------+------------------+------------------------+ | FullName | sat_score | programOfStudy | schoolEmailAdr | +------------------------+-----------+------------------+------------------------+ | Monique Davis | 400 | Literature | [email protected] | | Teri Gutierrez | 800 | Programming | [email protected] | | Spencer Pautier | 1000 | Programming | [email protected] | | Louis Ramsey | 1200 | Programming | [email protected] | | Alvin Greene | 1200 | Programming | [email protected] | | Sophie Freeman | 1200 | Programming | [email protected] | | Edgar Frank "Ted" Codd | 2400 | Computer Science | [email protected] | | Donald D. Chamberlin | 2400 | Computer Science | [email protected] | | Raymond F. Boyce | 2400 | Computer Science | [email protected] | +------------------------+-----------+------------------+------------------------+ 9 rows in set (0.00 sec)