SQL기본 select, from, where / insert,update..

FROM

~~ FROM Table 어떤 테이블로부터 가져온다는 뜻

SELECT

말 그대로 선택 →

select * 하게되면 전부 가져오는것

SELECT column1, column2, ...
FROM table_name;

위와 같은 방식으로 작성하여 사용

DISTINCT

The SELECT DISTINCT statement is used to return only distinct (different) values.

유일한 값으로 가져온다는 뜻으로, 중복 없이 가져오게된다.

SELECT DISTINCT column1, column2, ...
FROM table_name;

Example

-- Write a statement that will select the City column from the Customers table.
SELECT City FROM Customers;

-- Select all the different values from the Country column in the Customers table.
SELECT DISTINCT Country FROM Customers;

WHERE

필터링 하는 것

The WHERE clause is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified condition.

LIKE or NOT LIKE

특정 문자 또는 문자열을 포함하고 있는 값을 검색하고 싶을 때 사용.

% : 0개 이상의 문자열과 대치

_ : 임의의 한 개의 문자와 대치

%,_ 를 검색하고싶을땐? → @% , @_

--Genres에서 name이 B로 시작하는것 가져오기
select * from genres g
where g.Name like 'B%';

AND, OR and NOT Operators

생각대로 and , or , not 연산자를 사용하여 조건을 추가할 수 있다.

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

-- Customers 테이블에서 Country는 Germany이고, City는 Berlin이나 Munchen인 records를 골라라
SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');

--Select all records where the City column has the value "Berlin".
SELECT * FROM Customers
WHERE City = Berlin;

--The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:
SELECT * FROM Customers
WHERE Country='Mexico';

--#Select all records where the City column has the value 'Berlin' and the PostalCode column has the value 12209.
Select * From Customers
Where City = 'Berlin' and PostalCode = 12209

SELECT * FROM Customers
Where 

INSERT

The INSERT INTO statement is used to insert new records in a table.

새로운 레코드(행)을 추가할 때 사용한다.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INTO의 ( ... ) 와 VALUES의 ( ... )의 개수가 당연히 같아야한다.
해당 테이블의 column을 전부 채우지 않을 경우 null이 입력된다

NULL Values

A field with a NULL value is a field with no value.

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

UPDATE

The UPDATE statement is used to modify the existing records in a table.

현재 있는 records를 업데이트한다

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Customers테이블에서, Country가 Norway인 레코드들의 City를 Oslo로 바꾼다

Set the value of the City columns to 'Oslo', but only the ones where the Country column has the value "Norway".

update Customers
set City = 'Oslo'
where Country = 'Norway';

DELETE

삭제하는것

말그대로 삭제이며, from으로 어디서 삭제할지 명시해줘야한다

DELETE FROM table_name WHERE condition;

MIN(), MAX()

최대 최소 뽑기

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;

COUNT(), AVG() and SUM()

개수새기, 평균, 합계 함수

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Wildcard 와일드카드

A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Symbol	Description	Example
%	Represents zero or more characters	bl% finds bl, black, blue, and blob
_	Represents a single character	h\t finds hot, hat, and hit
[]	Represents any single character within the brackets	h[oa]t finds hot and hat, but not hit
^	Represents any character not in the brackets	h[^oa]t finds hit, but not hot and hat
-	Represents a range of characters	c[a-b]t finds cat and cbt

--Select all records where the first letter of the City starts with anything from an "a" to an "f".
SELECT * FROM Customers
WHERE City LIKE '[a-f]%';

Select all records where the first letter of the City is NOT an "a" or a "c" or an "f".

SELECT * FROM Customers
WHERE City LIKE '[^acf]%';

IN, BETWEEN

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;