Basic knowledge of SQL statements

What is SQL

** Language for writing queries (instructions to send to the database) **

SQL statement is output on the terminal when data is acquired from DB via model with Ruby on Rails.

Uninflected word

• (Specify which column of which table to call data)

sample.sql

SELECT "Column name"
FROM "table name"; 
/*The actual column specification is without quotation marks*/

• Concrete example

sample.sql

/*Uninflected word*/
SELECT name
FROM users;

/*Multiple designation*/
SELECT name, email
FROM users;

/*Specify all columns*/
SELECT *
FROM users; 

Acquisition of specific data (Where column name = value specification)

Extract specific data from non-unique columns such as gender and category Example: Specify only men

SELECT *
FROM users
WHERE sex = "male";
/*String type data""Surround with*/

Data acquisition for a specific range (WHERE + comparison operator)

SELECT *
FROM products
WHERE price > 1000;
/*Get data that the price is higher than 1000 yen*/

SELECT *
FROM posts
WHERE created_at <= "2020-12-01";
/* 2020/12/Get data posted before 01*/

Acquisition of data containing specific information (WHERE + LIKE)

SELECT *
FROM products
WHERE name LIKE "%cake%";
/*String"cake"Is included(id:1,2,3)Data acquisition*/

SELECT *
FROM products 
WHERE name LIKE "chocolate%";
/*Character string at the beginning of the sentence"chocolate"Is included(id:2,4)Data acquisition*/

SELECT *
FROM products 
WHERE name LIKE "%chocolate";
/*String at the end of the sentence"chocolate"Is included(id:5)Data acquisition*/

Acquisition of data that does not meet specific conditions (WHERE + NOT)

SELECT *
FROM products
WHERE NOT name LIKE "%cake%";
/*(String"cake"Acquisition of data that does not correspond to (including)*/

SELECT *
FROM products
WHERE NOT price >= 1000;
/*Acquisition of data that does not correspond to (price is 1000 yen or more)*/

SELECT *
FROM users
WHERE NOT created_at <= "2020-12-1";
/* (Registration date is 2020/12/Acquisition of data that does not correspond to (before 01)*/

Get data for column = (NULL or NOT NULL)

SELECT *
FROM users
WHERE introduction IS NULL;
/*Get data with null introduction*/

SELECT *
FROM users 
WHERE image_id IS NOT NULL;
/* image_Get data whose id is not null*/

Specifying multiple conditions (WHERE + AND / OR)

SELECT *
FROM users
WHERE address LIKE "%Tokyo%"
AND age >= 20;
/*(String in address column"Tokyo"(Including) and(Acquisition of data (age column is 20 or more)*/
/*Data of people over 20 years old living in Tokyo*/

SELECT *
FROM users
WHERE address LIKE "%Chiba%"
OR address LIKE "%Ibaraki Prefecture%";
/*Acquisition of data of people whose address is (Chiba prefecture or Ibaraki prefecture)*/

Sorting of acquired data (ORDER BY ascending or descending order)

SELECT *
FROM users 
ORDER BY age ASC;
/*Ascending order: Sort from younger age (smaller age value) data*/

SELECT *
FROM products
ORDER BY price DESC;
/*Descending: Sort by data with the highest price (larger price value)*/

SELECT *
FORM products
WHERE price <= 1000
ORDER BY calorie ASC;
/*Multiple designation+Sort: Sort from the lowest calorie to 1000 yen or less*/

Limit on the number of data to be acquired (LIMIT)

SELECT *
FROM posts
WHERE user_id = 1
LIMIT 5;
/* user_Get only 5 posts by user with id 1*/

SELECT *
FROM orders
WHERE user_id = 2
ORDER BY charge DESC
LIMIT 10;
/* (user_User with id 2)& Get only 10 items (in descending order of order amount)*/

Supplement

SQL statements are often seen on the terminal when retrieving data with Rudy on Rails applications, so just understanding the basic grammar will make it easier to understand the standard output of the terminal.

We would appreciate it if you could contact us if there are any mistakes in the description.