TechCodeview

U ovom SQL članku naučit ćete kako koristiti ključnu riječ BETWEEN u SQL izjavama u bazi podataka. Ovdje ćemo također detaljno raspravljati o operatoru NOT BETWEEN.

Što je Između u SQL-u?

Ključna riječ BETWEEN je operator u Structured Query Language. Omogućuje korisnicima baze podataka pristup vrijednostima unutar navedenog raspona.

Jednostavno možemo koristiti operator BETWEEN u WHERE klauzuli INSERT, UPDATE, SELECT i DELETE SQL upita.

Ovaj operator daje vrijednost TRUE kada je vrijednost stupca manja ili jednaka visokoj vrijednosti i veća ili jednaka niskoj vrijednosti. Početna i završna vrijednost uključene su u operator BETWEEN.

booleov u c

Sintaksa logičkog operatora BETWEEN:

 SELECT Column_Name_1, Column_Name_2, Column_Name_3, ......, Column_Name_N FROM Table_Name WHERE Column_Name BETWEEN Value_1 AND Value_2; 

U sintaksi, vrijednost_1 navodi početnu vrijednost raspona, a vrijednost_2 završnu vrijednost raspona.

Ako želite koristiti operator BETWEEN u SQL izjavama, morate slijediti sljedeće korake istim redoslijedom:

1. Napravite bazu podataka u sustavu.
2. Napravite novu SQL tablicu.
3. Unesite podatke u tablicu.
4. Pregledajte umetnute podatke.
5. Upotrijebite operator BETWEEN za prikaz podataka u tablici na više načina.

Sada ćemo ukratko objasniti svaki korak jedan po jedan s najboljim SQL primjerom:

Korak 1: Stvorite jednostavnu novu bazu podataka

Najprije morate stvoriti novu bazu podataka u bazi podataka Structured Query Language. Sljedeća naredba CREATE stvara new IIT_Koledž Baza podataka:

 CREATE Database IIT_College; 

Korak 2: Napravite novu tablicu

Sada upotrijebite sljedeću SQL sintaksu za stvaranje nove tablice u bazi podataka:

 CREATE TABLE table_name ( 1st_Column data type (character_size of 1st Column), 2nd_Column data type (character_size of the 2nd column ), 3rd_Column data type (character_size of the 3rd column), ... 

 Nth_Column data type (character_size of the Nth column) ); 

Sljedeća naredba CREATE stvara Fakultet_Info stol u IIT_Koledž Baza podataka:

 CREATE TABLE Faculty_Info ( Faculty_ID INT NOT NULL PRIMARY KEY, Faculty_First_Name VARCHAR (100), Faculty_Last_Name VARCHAR (100), Faculty_Dept_Id INT NOT NULL, Faculty_Joining_Date Varchar (80), Faculty_City Varchar (80), Faculty_Salary INT ); 

Korak 3: Umetnite podatke u tablicu

Sljedeći INSERT upiti umeću zapise fakulteta u tablicu Faculty_Info:

 INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1001, Arush, Sharma, 4001, 2020-01-02, Delhi, 20000); INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1002, Bulbul, Roy, 4002, 2019-12-31, Delhi, 38000 ); INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1004, Saurabh, Sharma, 4001, 2020-10-10, Mumbai, 45000); INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1005, Shivani, Singhania, 4001, 2019-07-15, Kolkata, 42000); INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1006, Avinash, Sharma, 4002, 2019-11-11, Delhi, 28000); INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary)VALUES (1007, Shyam, Besas, 4003, 2021-06-21, Lucknow, 35000); 

Korak 4: Pregledajte umetnute podatke

Sljedeća izjava SELECT prikazuje zapise tablice Faculty_Info na zaslonu računala:

 SELECT * FROM Faculty_Info; 

Korak 5: Koristite operator BETWEEN

Sljedeći upit koristi operator BETWEEN s numeričkim podacima:

 SELECT * FROM Faculty_Info WHERE Faculty_salary BETWEEN 25000 AND 40000; 

Ovaj SELECT upit prikazuje sve fakultete u izlazu čija je plaća između 25000 i 40000.

Rezultat gornje izjave prikazan je u sljedećem izlazu:

Sljedeći upit koristi logički operator BETWEEN sa znakovnim ili tekstualnim vrijednostima:

 SELECT * FROM Faculty_Info WHERE Faculty_City BETWEEN D AND L; 

Ovaj upit prikazuje evidenciju svih onih fakulteta čije ime grada počinje bilo kojim slovom između D i L.

Sljedeći upit koristi logički operator BETWEEN s formatom datuma

prelamanje riječi css

 SELECT * FROM Faculty_Info WHERE Faculty_Joining_Date BETWEEN 2020-01-02 AND 2021-06-21; 

Ovaj upit prikazuje evidenciju svih onih Fakulteta čije je pridruživanje između 02.01.2020 i 21.06.2021.

Sljedeći upit koristi klauzulu DISTINCT s operatorom BETWEEN u upitu SELECT:

 SELECT DISTINCT (Faculty_City) FROM Faculty_Info WHERE Faculty_Dept_Id BETWEEN 4001 AND 4003; 

Sljedeći upit ažurira podatke tablice pomoću operatora BETWEEN u naredbi UPDATE:

 UPDATE Faculty_Info SET Faculty_Salary = 60000 WHERE Faculty_Joining_Date BETWEEN 2019-07-15 AND 2021-01-02; 

Ovaj upit UPDATE ažurira plaću onih fakulteta čiji je datum pridruživanja između

2019-07-15 i 2021-01-02.

Za provjeru rezultata gornjeg upita upišite sljedeću naredbu SELECT:

hashmap

 SELECT * FROM Faculty_Info; 

Što je NOT BETWEEN u SQL-u?

NOT BETWEEN je operator u Structured Query Language, koji je upravo suprotan operatoru BETWEEN. Omogućuje nam pristup onim vrijednostima iz tablice koje ne dolaze u zadani raspon.

Operator NOT BETWEEN može se koristiti u SQL upitima INSERT, UPDATE, SELECT i DELETE.

Sintaksa za korištenje operatora NOT BETWEEN u Structured Query Language je dana u nastavku:

 SELECT Column_Name_1, Column_Name_2, Column_Name_3, ......, Column_Name_N FROM Table_Name WHERE Column_Name NOT BETWEEN Value_1 AND Value_2; 

Ako želite koristiti operator NOT BETWEEN u izjavama SQL-a, morate slijediti dolje navedene korake jedan po jedan u istom nizu:

1. Napravite bazu podataka u sustavu.
2. Napravite novu SQL tablicu.
3. Unesite podatke u tablicu.
4. Pregledajte umetnute podatke.
5. Koristite operator NOT BETWEEN za pregled specifičnih podataka različitih stupaca.

Korak 1: Stvorite jednostavnu novu bazu podataka

Prvo, morate napraviti novu bazu podataka u Structured Query Language.

Sljedeći upit stvara novi Civilna_industrija Baza podataka u SQL poslužitelju:

apstraktne metode

 CREATE Database Civil_Industry; 

Korak 2: Napravite novu tablicu

Sljedeći upit stvara Radnik_Informacije stol u Građanska_industrija Baza podataka:

 CREATE TABLE Worker_Info ( Worker_ID INT NOT NULL, Worker_Name VARCHAR (100), Worker_Gender Varchar(20), Worker_Age INT NOT NULL DEFAULT 18, Worker_Address Varchar (80), Worker_Salary INT NOT NULL ); 

Korak 3: Umetnite vrijednosti

Sljedeći INSERT upiti umeću zapise radnika u tablicu Worker_Info:

 INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1001, Arush, Male, Agra, 35000); INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1002, Bulbul, Female, Lucknow, 42000); INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1004, Saurabh, Male, 20, Lucknow, 45000); INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1005, Shivani, Female, Agra, 28000); INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1006, Avinash, Male, 22, Delhi, 38000); INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1007, Shyam, Male, Banglore, 20000); 

Korak 4: Pregledajte podatke tablice

Sljedeći upit prikazuje podatke tablice Worker_Info.

 SELECT * FROM Worker_Info; 

Korak 4: Koristite operator NOT BETWEEN

Izjava u nastavku koristi operator NOT BETWEEN s numeričkim podacima:

 SELECT * FROM Worker_Info WHERE Worker_salary NOT BETWEEN 25000 AND 40000; 

Ovaj SELECT upit prikazuje sve radnike u izlazu čija plaća nije veća ili jednaka 25000 manja ili jednaka 40000.

Rezultat gornjeg upita prikazan je u sljedećem izlazu:

Sljedeći upit koristi logički operator NOT BETWEEN sa znakovnim ili tekstualnim vrijednostima:

 SELECT * FROM Worker_Info WHERE Worker_Address NOT BETWEEN D AND L; 

Ovaj upit prikazuje evidenciju svih onih radnika čija imena gradova ne počinju niti jednim slovom između D i L.