[JAVA] Let's learn SQL SELECT statement for beginners

If you don't remember the SELECT statement when retrieving data from the database I have the experience that time has passed without getting it as I expected.

In the SELECT statement ・ Get a specific record ・ Acquire only product data of 〇〇 yen or more -Acquire only data that includes certain text data (example: T-shirt) You can see that you need to remember and so on.

Now, let's output what we learned from the SELECT statement.

Premise

Table box

Column id name message price time

We will proceed on the premise that it is registered in.

Get a specific column from a table

• When only name is acquired from the table box

sql

SELECT name　FROM　box;

Translation
② Which column do you want to get?(SELECT name)① Which table?(FROM　box;)

Since there are multiple tables in the database, It is necessary to specify the column (name) after specifying the table (box).

For example

sql

When creating a login database(Table: login)Also
username(Column: name)Suppose you put on.
When creating a database for product registration(Table: touroku)Also
Product name(Column: name)Suppose you put on.

① is required to distinguish the names.

Get all data

• When getting all from the table box

sql

SELECT * FROM box;

Translation
② Which column do you want to get?(SELECT *[all])① Which table?(FROM　box;)



Supplement:
Of course, the same process will be performed even if all column names are entered.

SELECT id name message time FROM box;

Get specific data

• When acquiring "a certain column (which meets the conditions)" from the table box

"WHERE" will appear. After deciding which table to get the SELECT (which column) Furthermore, "acquire only the data including trousers"

sql

SELECT * FROM box WHERE name = "pants";

Get only the data with pants in column name from all columns in table box

Note: Do not enclose numbers in quotation marks!

sql

 
* If there is a column price

SELECT * FROM box WHERE  price= 4800;

Get specific data with comparison operator

• When acquiring "only data of XX yen or more" from the table box

sql

* If there is a column price

SELECT * FROM box WHERE  price >= 5000;

Translation
Get only 5000 yen or more from all columns of the table box


SELECT * FROM box WHERE  price <= 5000;

Translation
Get less than 5,000 yen from all columns in the table box

Get data containing a certain character -LIKE operator-

% = Treat as wildcard A wildcard ... matches any string.

What is the LIKE operator? The condition that the specified column is a record containing 〇〇

sql

① With the LIKE operator%When attached to the front and back

SELECT * FROM box WHERE name LIKE = "%pants%";

Can't get(○ Obtained × Cannot be obtained)

pants= ○

Damaged trousers= ○

Trousers Dabo Dabo= ○



② With the LIKE operator%If you put in front

SELECT * FROM box WHERE name LIKE = "%pants";

Can't get(○ Obtained × Cannot be obtained)

pants= ○

Damaged trousers= ○

Trousers Dabo Dabo= ×



③ With the LIKE operator%When attached to the back

SELECT * FROM box WHERE name LIKE = "pants%";

Can't get(○ Obtained × Cannot be obtained)

pants= ○

Damaged trousers= ×

Trousers Dabo Dabo= ○

Use the NOT operator to get anything other than 〇〇

• When acquiring "only data that does not include other than XX" from the table box

sql

① If you want to get all the data except pants

SELECT * FROM box WHERE NOT name = "pants";

② If you want to get all the data except the word trousers

SELECT * FROM box WHERE NOT name LIKE "%pants%";



③ If you want to get all the data that does not include 〇〇 yen

SELECT * FROM box WHERE NOT price > 5000;

In short, use the NOT operator if you want to get everything else except that.

Get column data without contents

• When getting "No contents (NULL)" from the table box

When acquiring data related to NULL = Do not add equality!

sql

-When fetching the data of the specified column that is NULL
SELECT * FROM box WHERE name IS NULL;

-When acquiring non-null of the specified column data
SELECT * FROM box WHERE name IS NOT NULL;

Here comes the "other than" NOT operator.

When acquiring data related to NULL = Do not add equality!

Let's select multiple conditions with AND operator and OR operator

AND operator

• Search the table box for data that meets both "Condition 1 and Condition 2"

OR operator

• Search the table box for data that meets either "Condition 1 or Condition 2"

sql

・ AND operator
SELECT * FROM box WHERE price >= 2000 AND name = "pants";
Search for trousers over 2000 yen from the table box

・ OR operator
SELECT * FROM box WHERE name = "pants" OR name = "T-shirt";
Search table box for data containing pants or T-shirts

How to sort the data in ascending and descending order -ORDER BY-

ORDER BY ASC ※ascending order

ORDER BY DESC ※descending order

sql

・ Price(Amount of money)I want to sort from the largest!
SELECT * FROM box ORDER BY DESC;
If you want to sort from the smallest[ASC]

T-shirt 6000
Trousers 4800
Trousers 2800
T-shirt 2000

・ I want to sort the price of column name pants in descending order.
SELECT * FROM box WHERE name ="pants" ORDER BY DESC;
If you want to sort from the smallest[ASC]

Trousers 4800
Trousers 2800

Decide how many items to acquire -LIMIT-

LIMIT = limit

Since it can be used together with WHERE and ORDER, ・ 10 cases from the top ・ 10 cases from the bottom ・ Column name trousers only 10 cases And so on

sql

・ I want to get only 10 cases!
SELECT * FROM box LIMIT 10;