SQL Operators Tutorial - Bitwise, Comparison, Arithmetic og Logical Operator Query Eksempler

I sin kerne er internettet og alle dets applikationer kun data.

Hver e-mail, tweet, selfie, banktransaktion og mere er bare data, der sidder i en database et eller andet sted.

For at disse data skal være nyttige, skal vi være i stand til at hente dem. Det er dog ikke nok bare at hente dataene - dataene skal også være nyttige og relevante for vores situation.

På databaseniveau beder vi om specifik information fra databasen ved at skrive en SQL-forespørgsel. Denne SQL-forespørgsel specificerer de data, vi vil modtage, og det format, vi vil modtage det i.

I denne artikel ser vi på alle de mest almindelige måder at filtrere en SQL-forespørgsel på.

Her er hvad vi dækker:

  • Opsætning af din database
  • Oprettelse af brugere
  • Indsættelse af brugere
  • Filtrering af data med WHERE
  • Logiske operatører ( AND/ OR/ NOT)
  • Sammenligningsoperatorer ( <, >, <=, >=)
  • Aritmetiske operatorer ( +, -, *, /, %)
  • Eksistensoperatører ( IN/ NOT IN)
  • Delvis matchning ved hjælp af LIKE
  • Håndtering af manglende data ( NULL)
  • Brug af IS NULLogIS NOT NULL
  • Sammenligningsoperatører med datoer og tidspunkter
  • Eksistens ved hjælp af EXISTS/NOT EXISTS
  • Bitvise operatører
  • Konklusion

Opsætning af din database

For at filtrere vores data skal vi naturligvis først have nogle.

Til disse eksempler bruger vi PostgreSQL, men de forespørgsler og begreber, der vises her, oversættes let til ethvert andet moderne databasesystem (som MySQL, SQL Server osv.).

For at arbejde med vores PostgreSQL-database kan vi bruge psql- det interaktive PostgreSQL-kommandolinjeprogram. Hvis du har en anden databaseklient, som du kan lide at arbejde med, er det også fint!

Lad os begynde med at oprette vores database. Med PostgreSQL allerede installeret, kan vi køre psqlkommandoen createdb på vores terminal for at oprette en ny database. Jeg ringede til mig fcc:

$ createdb fcc 

Lad os derefter starte den interaktive konsol ved hjælp af kommandoen psqlog oprette forbindelse til den database, vi lige har oprettet ved hjælp af \c :

$ psql psql (11.5) Type "help" for help. john=# \c fcc You are now connected to database "fcc" as user "john". fcc=# 

Oprettelse af brugere

Nu hvor vi har en database, lad os oprette en databasetabel for at modellere en potentiel bruger i vores fiktive system.

Vi kalder denne tabel users, og hver række i denne tabel repræsenterer en af ​​vores brugere.

Denne userstabel har kolonner, som vi forventer at beskrive en bruger - ting som navn, e-mail og en alder.

psqlLad os oprette usersbordet inde i vores session :

CREATE TABLE users( id SERIAL PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT NOT NULL, age INTEGER NOT NULL );

Outputtet viser, CREATE TABLEhvilke midler der er oprettelse af en tabel, der var vellykket.

Bemærk: Jeg har ryddet i psqloutput i disse eksempler for at gøre det lettere at læse, så rolig, hvis output vist her ikke er nøjagtigt, hvad du har set i din terminal.

Lad os se på indholdet af vores brugertabel:

SELECT * FROM users; id | first_name | last_name | email | age ----+------------+-----------+-------+----- (0 rows) 

Vi har ikke indsat nogen data i vores tabel, så vi ser bare den tomme tabelstruktur.

Hvis du ikke er fortrolig med SQL-forespørgsler, er den, vi lige har kørt, SELECT * FROM usersen af ​​de enkleste, du kan skrive.

Nøgleordet SELECTspecificerer, hvilken kolonne (r) du vil returnere ( *betyder "alle kolonner"), og FROMnøgleordet angiver, hvilken tabel du vil vælge fra (i dette tilfælde users).

SELECT * FROM usersbetyder det virkelig at returnere alle rækker og alle kolonner fra userstabellen.

Hvis vi ønskede at returnere bestemte kolonner fra userstabellen, kunne vi erstatte dem SELECT *med de kolonner, vi gerne vil returnere - for eksempel SELECT id, name FROM users.

Indsættelse af brugere

En tom tabel er ikke særlig interessant, så lad os indsætte nogle data i vores tabel, så vi kan øve på at spørge imod den:

INSERT INTO users(first_name, last_name, email, age) VALUES ('John', 'Smith', '[email protected]', 25), ('Jane', 'Doe', '[email protected]', 28), ('Xavier', 'Wills', '[email protected]', 35), ('Bev', 'Scott', '[email protected]', 16), ('Bree', 'Jensen', '[email protected]', 42), ('John', 'Jacobs', '[email protected]', 56), ('Rick', 'Fuller', '[email protected]', 16);

Hvis vi kører denne indsætningserklæring i vores psqlsession, ser vi output INSERT 0 7. Dette betyder, at vi med succes har indsat 7 nye rækker i vores tabel.

Hvis vi kører vores SELECT * FROM usersforespørgsel igen, ser vi nu disse data:

SELECT * FROM users; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | [email protected] | 56 2 | Rick | Fuller | [email protected] | 16 3 | Bree | Jensen | [email protected] | 42 4 | Bev | Scott | [email protected] | 16 5 | Xavier | Wills | [email protected] | 35 6 | Jane | Doe | [email protected] | 28 7 | John | Smith | [email protected] | 25 (7 rows) 

Filtrering af data med WHERE

Indtil videre har vi lige returneret alle rækkerne fra vores bord. Dette er standardopførelsen for forespørgslen. For at returnere et mere selektivt sæt af rækker skal vi filtrere rækkerne ved hjælp af en WHEREklausul.

Der er mange måder at filtrere vores rækker ved hjælp af en WHEREklausul. Den enkleste operatør vi kan bruge er operatør lighed: =.

Sig, at vi ønskede at finde brugere, hvis fornavn var "John":

SELECT * FROM users WHERE first_name = 'John'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | [email protected] | 56 7 | John | Smith | [email protected] | 25 (2 rows) 

Her vedhæftes vi nøgleordet WHEREtil vores forespørgsel efterfulgt af en erklæring lighed: first_name = 'John'.

Our database first looks at the FROM keyword to determine what data to fetch. So, the database will read this query, see FROM users, and go and fetch all of the rows for the users table from the disk.

Once all of the rows have been retrieved from the users table, it then runs the WHERE clause against each row and only returns rows where the first_name column value equals "John."

In our data, there are two rows that match that first name.

If we wanted to find a particular "John" in our system, we could query based on a column that we know is unique — like our id column.

To find the "John Jacobs" row specifically, we could query by his ID:

SELECT * FROM users WHERE id = 1; id | first_name | last_name | email | age ----+------------+-----------+------------------+----- 1 | John | Jacobs | [email protected] | 56 (1 row) 

Here only one record matched the condition of id = 1, so we only got back one row.

Logical operators (AND / OR / NOT)

We can filter by more than just the equality operator. We can also use the boolean logical operators that are found in most programming languages: and, or, and not.

In many programming languages and and or are represented by && and ||. In SQL, they're simply AND and OR.

Instead of querying by ID, let's try to find the record for the person named "John Smith." To do this, we can use an AND in our WHERE clause to look for both the first name and last name condition:

SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Smith'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 7 | John | Smith | [email protected] | 25 (1 row) 

To find people with a first name of "John" or a last name of "Doe":

SELECT * FROM users WHERE first_name = 'John' OR last_name = 'Doe'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | [email protected] | 56 6 | Jane | Doe | [email protected] | 28 7 | John | Smith | [email protected] | 25 (3 rows) 

Her indeholdt vores resultat både Johns såvel som Jane Doe .

Disse ANDog ORbetingelser kan også lænkes sammen. Lad os sige, at vi ønskede at finde nogen, der hedder nøjagtigt "John Smith", eller nogen med efternavnet "Doe":

SELECT * FROM users WHERE ( first_name = 'John' AND last_name = 'Smith' ) OR last_name = 'Doe'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 6 | Jane | Doe | [email protected] | 28 7 | John | Smith | [email protected] | 25 (2 rows) 

Hvis vi ønskede at invertere denne tilstand og finde brugere, der ikke hedder "John Smith" og heller ikke har et efternavn på "Doe", kunne vi tilføje NOToperatøren:

SELECT * FROM users WHERE NOT ( ( first_name = 'John' AND last_name = 'Smith' ) OR last_name = 'Doe' ); id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 4 | Bev | Scott | [email protected] | 16 5 | Bree | Jensen | [email protected] | 42 6 | John | Jacobs | [email protected] | 56 7 | Rick | Fuller | [email protected] | 16 3 | Xavier | Wills | [email protected] | 35 (5 rows)
Bemærk: Alle har deres egen personlige stil for, hvordan de kan lide at formatere forespørgsler - gør hvad der giver mening for dig!

Sammenligningsoperatorer ( <, >, <=, >=)

Similar to other programming languages, SQL also the comparison operators: <, >, <=, >=.

Let's practice using these operators against our users' age column.

Let's say we wanted to find users that were eighteen years or older:

SELECT * FROM users WHERE age >= 18; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | [email protected] | 56 3 | Bree | Jensen | [email protected] | 42 5 | Xavier | Wills | [email protected] | 35 6 | Jane | Doe | [email protected] | 28 7 | John | Smith | [email protected] | 25 (5 rows) 

What about users that are older than 25, but less than or equal to 35 years old?

SELECT * FROM users WHERE age > 25 AND age <= 35; id | first_name | last_name | email | age ----+------------+-----------+-------------------+----- 5 | Xavier | Wills | [email protected] | 35 6 | Jane | Doe | [email protected] | 28 (2 rows) 

Arithmetic operators (+, -, *, /, %)

We can also perform mathematical calculations on our data.

Our users table has an age column, what if we wanted to find half of each person's age?

SELECT *, age / 2 AS half_of_their_age FROM users; id | first_name | last_name | email | age | half_of_their_age ----+------------+-----------+---------------------+-----+------------------- 1 | John | Jacobs | [email protected] | 56 | 28 2 | Rick | Fuller | [email protected] | 16 | 8 3 | Bree | Jensen | [email protected] | 42 | 21 4 | Bev | Scott | [email protected] | 16 | 8 5 | Xavier | Wills | [email protected] | 35 | 17 6 | Jane | Doe | [email protected] | 28 | 14 7 | John | Smith | [email protected] | 25 | 12 (7 rows) 

Here we select all of the table columns (using SELECT *), and we also select a new aggregate calculation: age / 2. We also give this value a descriptive name (half_of_their_age) with an alias using the AS keyword.

We can also find who's age is an even number by using the modulus or remainder operator (%):

SELECT * FROM users WHERE (age % 2) = 0; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | [email protected] | 56 2 | Rick | Fuller | [email protected] | 16 3 | Bree | Jensen | [email protected] | 42 4 | Bev | Scott | [email protected] | 16 6 | Jane | Doe | [email protected] | 28 (5 rows) 

We can find who's age is an odd number by changing our = condition to a "not equals" using != or :

SELECT * FROM users WHERE (age % 2)  0; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 5 | Xavier | Wills | [email protected] | 35 7 | John | Smith | [email protected] | 25 (2 rows) 

Existence operators (IN / NOT IN)

If we wanted to check that a column value existed in a list of values, we can use IN or NOT IN:

SELECT * FROM users WHERE first_name IN ('John', 'Jane', 'Rick'); id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Smith | [email protected] | 25 2 | Jane | Doe | [email protected] | 28 6 | John | Jacobs | [email protected] | 56 7 | Rick | Fuller | [email protected] | 16 (4 rows) 

Similarly, we can use NOT IN to negate that condition:

SELECT * FROM users WHERE first_name NOT IN ('John', 'Jane', 'Rick'); id | first_name | last_name | email | age ----+------------+-----------+------------------+----- 3 | Xavier | Wills | [email protected] | 35 4 | Bev | Scott | [email protected] | 16 5 | Bree | Jensen | [email protected] | 42 (3 rows) 

Partial matching using LIKE

Sometimes, we may want to search for rows based on a partial-search.

Say for example we wanted to find all users that signed up for our application using a Gmail address. We can do a partial match against a column using the LIKE keyword. We can also specify a wildcard (or "match anything") in the match string using %.

To find users with an email that ends in gmail.com:

SELECT * FROM users WHERE email LIKE '%gmail.com'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Smith | [email protected] | 25 (1 row) 

The string %gmail.com means "match anything that ends in gmail.com."

If we look back at our users data, we'll notice that we actually have two users with a gmail.com address:

('John', 'Smith', '[email protected]', 25), ('Jane', 'Doe', '[email protected]', 28), 

However, Jane's email has a capital "G' in her email address. Or previous query didn't pick up this record because it was matching exactly against gmail.com with a lowercase "g."

To do a case-insensitive match, we just need to substitute LIKE for ILIKE:

SELECT * FROM users WHERE email ILIKE '%gmail.com'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Smith | [email protected] | 25 2 | Jane | Doe | [email protected] | 28 (2 rows) 

The wildcard symbol % at the beginning of the string means anything that ends in "gmail.com" will be returned. That could be [email protected] or [email protected] — as long as it ends in gmail.com.

We can also add as many wildcards (%) as we want.

For example, the search term %j%o% will return any emails that follow the pattern followed by a j, followed by , followed by an o, followed by :

SELECT * FROM users WHERE email ILIKE '%j%o%'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Smith | [email protected] | 25 2 | Jane | Doe | [email protected] | 28 5 | Bree | Jensen | [email protected] | 42 6 | John | Jacobs | [email protected] | 56 (4 rows) 

Dealing with missing data (NULL)

Next let's look at how we deal with rows with columns that have missing data.

To do that, let's add another column to our users table: first_paid_at.

This new column will be a TIMESTAMP (similar to a datetime in other languages), and it will represent the first date and time that a user paid us money for our application. Maybe we want to send them a nice card or some flowers on the anniversary of using our app?

We could drop our users table using DROP TABLE users; and re-create it, but that would also delete all of the data in our table.

To change a table without dropping it and losing the data, we can use ALTER TABLE:

ALTER TABLE users ADD COLUMN first_paid_at TIMESTAMP; 

That command returns the result ALTER TABLE, so our ALTER query succeeded.

If we query our users table now, we'll notice that this new column doesn't have any data in it:

SELECT * FROM users; id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+--------------- 1 | John | Smith | [email protected] | 25 | 2 | Jane | Doe | [email protected] | 28 | 3 | Xavier | Wills | [email protected] | 35 | 4 | Bev | Scott | [email protected] | 16 | 5 | Bree | Jensen | [email protected] | 42 | 6 | John | Jacobs | [email protected] | 56 | 7 | Rick | Fuller | [email protected] | 16 | (7 rows) 

Our first_paid_at column is empty, and the result from our psql query shows it as an empty column. This column is not technically empty — it contains a special value that psql is choosing not to display in its output: NULL.

NULL is a special value in databases. It's the absence or lack of a value, and it doesn't behave as we expect it would.

To illustrate this, let's look at the simple SELECT statements below:

SELECT 1 = 1, 1 = 2; ?column? | ?column? ----------+---------- t | f (1 row) 

Here we simply selected 1 = 1 and 1 = 2. As we expect, the result of these two statements is t and f (or TRUE and FALSE). 1 is equal to 1, and 1 is not equal to 2.

Now let's try the same with NULL:

SELECT 1 = NULL; ?column? ---------- (1 row) 

We might expect this value to be FALSE, but the return value is actually NULL.

To visualize these NULLs a little better, let's set how psql displays NULL values using the \pset option:

fcc=# \pset null 'NULL' Null display is "NULL". 

Now if we run that query again we'll see the NULL output we expect:

SELECT 1 = NULL; ?column? ---------- NULL (1 row) 

So 1 is not equal to NULL, what about NULL = NULL?

SELECT NULL = NULL; ?column? ---------- NULL (1 row) 

Oddly enough, NULL is not equal to NULL.

It helps to think of NULL as an unknown value. Is an unknown value equal to 1? Well, we don't know — it's unknown. Is an unknown value equal to an unknown value? Again, it's unknown. In this way NULL makes a little more sense.

Using IS NULL and IS NOT NULL

We can't use the equality operator with NULL, but we can use two operators specifically designed for it: IS NULL and IS NOT NULL.

SELECT NULL IS NULL, NULL IS NOT NULL; ?column? | ?column? ----------+---------- t | f (1 row) 

These values come out as expect: NULL IS NULL is true, and NULL IS NOT NULL is false.

That's all fine and weird, but how do we use this?

Well first let's get some data in our first_paid_at column:

UPDATE users SET first_paid_at = NOW() WHERE id = 1; UPDATE 1 UPDATE users SET first_paid_at = (NOW() - INTERVAL '1 month') WHERE id = 2; UPDATE 1 UPDATE users SET first_paid_at = (NOW() - INTERVAL '1 year') WHERE id = 3; UPDATE 1 

In those UPDATE statements above we've set three different users first_paid_at columns: User ID 1 to the current time (NOW()), User ID 2 to one month ago, and User ID 3 to one year ago.

First, let's find users that have paid us and users who haven't:

SELECT * FROM users WHERE first_paid_at IS NULL; id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+--------------- 4 | Bev | Scott | [email protected] | 16 | NULL 5 | Bree | Jensen | [email protected] | 42 | NULL 6 | John | Jacobs | [email protected] | 56 | NULL 7 | Rick | Fuller | [email protected] | 16 | NULL (4 rows) SELECT * FROM users WHERE first_paid_at IS NOT NULL; id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+---------------------------- 1 | John | Smith | [email protected] | 25 | 2020-08-11 20:49:17.230517 2 | Jane | Doe | [email protected] | 28 | 2020-07-11 20:49:17.233124 3 | Xavier | Wills | [email protected] | 35 | 2019-08-11 20:49:17.23488 (3 rows) 

Comparison operators with dates and times

Now that we have some data, let's use our same comparison operators against this new TIMESTAMP field.

Let's try to find users that paid us for the first within the past week. To do this, we can take the current time, NOW(), and subtract from it one week using the INTERVAL keyword:

SELECT * FROM users WHERE first_paid_at > (NOW() - INTERVAL '1 week'); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+---------------------------- 1 | John | Smith | [email protected] | 25 | 2020-08-11 20:49:17.230517 (1 row) 

We could also use a different interval, such as three months ago:

SELECT * FROM users WHERE first_paid_at < (NOW() - INTERVAL '3 months'); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+-----------------+-----+--------------------------- 3 | Xavier | Wills | [email protected] | 35 | 2019-08-11 20:49:17.23488 (1 row) 

Let's try to find users that first paid us between one to six months ago.

We could combine our conditions again using AND, but instead of using less than and greater than operators let's use the BETWEEN keyword:

SELECT * FROM users WHERE first_paid_at BETWEEN (NOW() - INTERVAL '6 month') AND (NOW() - INTERVAL '1 month'); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+-------------------+-----+---------------------------- 2 | Jane | Doe | [email protected] | 28 | 2020-07-11 20:49:17.233124 (1 row) 

Existence using EXISTS / NOT EXISTS

Another way to check for existence is to use EXISTS and NOT EXISTS.

These operators filter out rows by checking for the existence (or non-existence) of a condition. This condition is usually a query against another table.

To set this up, let's create a new table called posts. This table will hold posts that a user can make in our system.

CREATE TABLE posts( id SERIAL PRIMARY KEY, body TEXT NOT NULL, user_id INTEGER REFERENCES users NOT NULL ); 

It's a simple table. It only contains an ID, a field to store the post text (body), and a reference to the user that wrote the post (user_id).

Let's insert some data into this new table:

INSERT INTO posts(body, user_id) VALUES ('Here is post 1', 1), ('Here is post 2', 1), ('Here is post 3', 2), ('Here is post 4', 3); 

In the data that we inserted into the posts table, User ID 1 has two posts, User ID 2 has one post, and User ID 3 also has one post.

To find users that do have posts, we can use EXISTS.

The EXISTS keyword takes a subquery. If anything is returned from that subquery (even a row with just the value of NULL), the database will include that row in the result set.

From the PostgreSQL docs on EXISTS:

Argumentet for EXISTS er en vilkårlig SELECT-sætning eller underforespørgsel. Underforespørgslen evalueres for at afgøre, om den returnerer nogen rækker. Hvis den returnerer mindst en række, er resultatet af EXISTS "sandt"; Hvis underforespørgslen ikke returnerer nogen rækker, er resultatet af EXISTS "falsk".

EXISTSer bare på udkig efter eksistensen af en række fra underforespørgslen - det betyder ikke noget, hvad der er i den.

Her er et eksempel på brugere, der har indlæg ved hjælp af EXISTS:

SELECT * FROM users WHERE EXISTS ( SELECT 1 FROM posts WHERE posts.user_id = users.id ); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+---------------------------- 1 | John | Smith | [email protected] | 25 | 2020-08-11 20:49:17.230517 2 | Jane | Doe | [email protected] | 28 | 2020-07-11 20:49:17.233124 3 | Xavier | Wills | [email protected] | 35 | 2019-08-11 20:49:17.23488 (3 rows) 

Som vi forventede, fik vi bruger 1, 2 og 3 tilbage.

Vores EXISTSunderforespørgsel kontrollerer for en postspost, hvor postens user_idmatcher idkolonnen på usersbordet. Vi vendte tilbage 1i vores, SELECTfordi vi kan returnere noget her - databasen vil bare se, at noget faktisk blev returneret.

Similarly, we could find users that don't have any posts by changing EXISTS to NOT EXISTS:

SELECT * FROM users WHERE NOT EXISTS ( SELECT 1 FROM posts WHERE posts.user_id = users.id ); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+--------------- 4 | Bev | Scott | [email protected] | 16 | NULL 5 | Bree | Jensen | [email protected] | 42 | NULL 6 | John | Jacobs | [email protected] | 56 | NULL 7 | Rick | Fuller | [email protected] | 16 | NULL (4 rows) 

Finally, we could also re-write this query to use IN or NOT IN instead of EXISTS or NOT EXISTS, like this:

SELECT * FROM users WHERE users.id IN ( SELECT user_id FROM posts ); 

This technically works, but as a general rule if you are testing for existence of another record it is generally more performant to use EXISTS. The IN and NOT IN operator are generally better used for checking a value against a static list like we did earlier:

SELECT * FROM users WHERE first_name IN ('John', 'Jane', 'Rick'); 

Bitwise operators

Although in practice the bitwise operators are not often used, for completeness let's look at a simple example.

If we wanted to (for some reason) look at the age of our users in binary and play with flipping those bits around, we could use a variety of bitwise operators.

As an example, let's look at the bitwise "and" operator: &.

SELECT age::bit(8) & '11111111' FROM users; ?column? ---------- 00010000 00101010 00111000 00010000 00011001 00011100 00100011 (7 rows)

To perform a bitwise calculation we first have to convert our age column from an integer to binary — in this example we cast it into an eight-bit binary string using ::bit(8).

Next we can "and" the result of our age in binary format with another binary string, 11111111.  Since a binary AND only returns 1 if both bits are 1's, this all 1's string keeps the output interesting.

Almost every other bitwise operator uses the same format:

SELECT age::bit(8) | '11111111' FROM users; -- bitwise OR SELECT age::bit(8) # '11111111' FROM users; -- bitwise XOR SELECT age::bit(8) <> '00000001' FROM users; -- bitwise shift right

The bitwise "not" operator (~) is a little different in that it is applied to a single term — similar to the regular NOT operator:

SELECT ~age::bit(8) FROM users; ?column? ---------- 11101111 11010101 11000111 11101111 11100110 11100011 11011100 (7 rows)

And finally, the most useful of the bitwise operators: concatenation.

A common use of this operator is to combine strings of text together. For example if we wanted to build a calculated property of a "full name" for users, we could use concatenation:

SELECT first_name || ' ' || last_name AS name FROM users; name -------------- Bev Scott Bree Jensen John Jacobs Rick Fuller John Smith Jane Doe Xavier Wills (7 rows)

Here we concatenate (or "combine") the first_name, a space (' '), and the last_name property to build a name value.

Conclusion

So that's an overview of basically every query filtering operator you'll ever need to use!

Der er et par flere operatører, som vi ikke dækkede her, men disse operatører bruges enten ikke meget ofte eller bruges på nøjagtig samme måde som ovenfor - så de burde ikke udgøre dig nogen problemer.

Hvis du kunne lide dette indlæg, skriver jeg lignende ting på min blog her.

Tak for læsningen!

John