TIL_201021

Today I learned

• SQL query

W3SCHOOL 문서 참고

@@ MySQL 쿼리문을 정리했다. MySQL을 설치하고, workbench GUI 툴을 이용하여 가장 어려웠던 JOIN문을 이해하려고 노력했다.
ON 조건문과 LEFT, RIGHT JOIN의 관계는 어떤지에서 자꾸 헷갈렸다. 처음 개념을 받아들일때 잘못 받아들인 걸로 시간 할애가 컸다. 직접 찍어보고 눈으로 보면서 이해할 수 있었다.
SQL문 쿼리를 넣을때 한글을 넣고 싶은데 자꾸 incorrect string error가 나서, 이 글을 참고하여 utf-8 설정을 잡아줬다.

SQL Syntax

SQL SELECT

SELECT CustomerName,City FROM Customers;

WHERE Clause

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

//example
SELECT * FROM Customers
WHERE Country='Mexico';

SQL AND, OR and NOT Operators

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');

SQL ORDER BY

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

SELECT * FROM Customers
ORDER BY Country;

SELECT * FROM Customers
ORDER BY Country DESC;

SELECT * FROM Customers
ORDER BY Country, CustomerName;

//다음 SQL 문장은 "국가" 및 "고객 이름" 열별로 정렬된 "고객" 테이블에서 모든 고객을 선택한다. 즉, 국가별로 주문하지만 일부 행이 동일한 국가를 가진 경우 CustomerName에서 주문:
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

SQL INSERT INTO Statement

삽입 쿼리

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

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

//특정한 열 몇개만 선택해서 넣을 수도 있다.
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

SQL NULL Values

//NULL
SELECT column_names
FROM table_name
WHERE column_name IS NULL;

//NULL 이 아닌 것을 찾을 때
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

SQL Update

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

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';

업데이트에서 WHERE문을 빠트리면 모든 레코드가 변경된다.

(레코드 = row)

SQL DELETE

DELETE FROM table_name WHERE condition;

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

SQL SELECT TOP

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

SELECT * FROM Customers
LIMIT 3;

SELECT * FROM Customers
WHERE ROWNUM <= 3;

SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;

SQL MIN() and MAX() Functions

SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;

SELECT MIN(Price) AS SmallestPrice
FROM Products;

SELECT MAX(Price) AS LargestPrice
FROM Products;

SQL 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;

SQL LIKE

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

//a로 시작하는 문자를 찾아라
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

//두번째글자가 r
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';

a 알파벳 a 대소문자의 구분이 없다

_ 어떤 문자이든 문자가 존재해야한다 (한칸에 한문자)

% 문자가 있어도 없어도 되는 자리 (한칸이 여러문자를 의미하기도한다)

SQL Wildcards

//Using the % Wildcard
SELECT * FROM Customers
WHERE City LIKE 'ber%';

//Using the _ Wildcard
SELECT * FROM Customers
WHERE City LIKE '_ondon';

//Using the [charlist] Wildcard
//or 연산자와 같다. b나 s나 p 중 하나
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';
//a,b,c중 하나
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

//Using the [!charlist] Wildcard
SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';
//위와 의미가 같다
SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';

The SQL IN Operator

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);

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

SQL BETWEEN

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

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

WHERE Price NOT BETWEEN 10 AND 20;

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);

SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

SQL Aliases

SELECT column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name;

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

SQL Joins

두가지 테이블을 JOIN

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
****

SQL UNION

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

//UNION ALL 중복값 반환
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

//Another UNION Example
SELECT 'Customer' AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers

SQL GROUP BY (그룹으로 묶는)

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

SQL Database

MySQL 접속

mysql -u(계정 접근) [계정명] -p(비밀번호 입력)
$ mysql -u root -p

SQL CREATE DATABASE Statement

CREATE DATABASE databasename;

SQL DROP DATABASE Statement

DROP DATABASE databasename;

SQL CREATE TABLE Statement

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

//Create Table Using Another Table
CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

테이블 정보 조회

DESCRIBE [테이블 이름]

SQL DROP TABLE Statement

DROP TABLE table_name;

SQL ALTER TABLE Statement

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

SQL NOT NULL Constraint(제약 조건)

ALTER TABLE Persons
MODIFY Age int NOT NULL;

SQL UNIQUE Constraint (제약 조건)

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);

ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

ALTER TABLE Persons
DROP INDEX UC_Person;

SQL PRIMARY KEY Constraint

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
//id, LastName으로 구성된 PK_Person PRIMARY KEY

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

ALTER TABLE Persons
DROP PRIMARY KEY;

SQL FOREIGN KEY Constraint

다른 테이블과의 연관관계를 가진 키.

//The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

SQL DEFAULT Constraint

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);

CREATE TABLE Orders (
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE()
);

ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';

ALTER TABLE Persons
ALTER City DROP DEFAULT;

SQL AUTO INCREMENT Field

CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);

//시작값 설정, 1씩 증가
ALTER TABLE Persons AUTO_INCREMENT=100;

SQL Working With Dates

MySQL comes with the following data types for storing a date or a date/time value in the database:

• DATE - format YYYY-MM-DD
• DATETIME - format: YYYY-MM-DD HH:MI:SS
• TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
• YEAR - format YYYY or YY

SELECT * FROM Orders WHERE OrderDate='2008-11-11'