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. スクリーンショット 2021-01-02 11.11.01.png

Uninflected word

sample.sql


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

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

id name email address sex
1 ... ... ... male
2 ... ... ... Female
3 ... ... ... male
4 ... ... ... male
5 ... ... ... Female
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)

id name price
1 Shortcake ...
2 Chocolate cake ...
3 Cheesecake ...
4 Chocolate ice cream ...
5 Ganache ...
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.

Recommended Posts

Basic knowledge of SQL statements
Basic knowledge
Basic knowledge of Ruby on Rails
[Basic knowledge of Java] Scope of variables
[Java] Personal summary of conditional statements (basic)
Java basic knowledge 1
Basic knowledge of Java development Note writing
[Basic knowledge of Java] About type conversion
Way of thinking when studying basic program knowledge
[Ruby] Basic knowledge of class instance variables, etc.
Summary of basic knowledge of Rails acquired by progate
java basic knowledge memo
Basic format of Dockefile
Introduction to Java for beginners Basic knowledge of Java language ①
[Swift] Types of types-Basic knowledge-
Basic methods of Ruby arrays
Review the basic knowledge of ruby that is often forgotten
[Practice! 】 Execution of SQL statement
[Docker] Introduction of basic Docker Instruction
Basic knowledge in method definition
Super basic usage of Eclipse
[Java] Summary of for statements
[Ruby] List of basic commands
Summary of basic functions of ImageJ
Review of Ruby basic grammar
Understand the basic mechanism of log4j2.xml
The basic basis of Swift dialogs
The basic basis of Swift's Delegate
Basic usage of java Optional Part 1
Basic processing flow of java Stream
Basic structure of Java source code
Summary of basic migration knowledge rails db: rollback and column addition / deletion