SQL 쿼리 끄적끄적

1. Create db

CREATE DATABASE my_db;

2. CREATE TABLE

CREATE TABLE Persons

(

PersonID int,

LastName varchar(255),

FisrtName varchar(255),

Address varchar(255),

City varchar(255)

);

CREATE TABLE PersonNotNull

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

* 외래키 참조 테이블 생성

CREATE TABLE Orders

(

O_Id int NOT NULL,

OrderNo int NOT NULL,

P_Id int,

PRIMARY KEY(O_Id),

FOREIGN KEY(P_Id) REFERENCES Persons(P_Id)

)

3. DROP

DROP DATABASE database_name

DROP TABLE table_name

4. INSERT

INSERT INTO Persons (FirstName, LastName)

VALUES ('Lars','Monsen')

5. FUNCTION

1. SELECT AVG(column_name) FROM table_name

2. *count(*) : record 수

SELECT COUNT(*) FROM table_name;

3. Round(n,i)

- n을  i+1번째에서 반올림한 결과를 나타냄

SELECT ProductName, ROUND(Price,0) AS RoundedPrice

FROM Products;