HappyTomatoLife

SQL Operators SQL Arithmetic Operators +, -, *, /, % SQL Bitwise Operators &, |, ^ SQL Comparison Operators =, >, =,

SQL Comments 주석 Single Line Comments -- single line comments SELECT * FROM Customers; SELECT * FROM Customers --Where city='Berlin'; Multi-line Comments /* multi line comments */ SELECT * FROM Customers;

SQL Stored Procedures for SQL Server What is a Stored Procedure? 반복해서 사용할 수 있는 프로세저 Stored Procedure Syntax procedure 생성CREATE PROCEDURE procedure_name AS sql_statement GO; procedure 실행EXEC procedure_name; Stored Procedure Example CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO; EXEC SelectAllCustomers; Stored Procedure with One Parameter CREATE PROCEDURE SelectAllCustomers @Ci..

SQL NULL Functions SQL IFNULL(), ISNULL(), COALESCE(), NVL() Functions null 값을 허용하고 싶을 때 MySQL IFNULL() 이용SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products; SQL server ISNULL() SELECT ProductName, UnitPrice * (UnitsInSTock + ISNULL(UnitsOnOrder, 0)) FROM Products; MS 엑세스 IsNULL() Oracle NVL()

SQL CASE Expression The SQL CASE Expression C언어의 case와 동일한 기능 CASE Syntax CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END; SQL CASE Examples SELECT OrderID, Quantity, CASE WHEN Quantity > 30 THEN 'The qunatity is greater than 30' WHEN Quantity = 30 THEN 'The quantity is 30' ELSE 'The quantity is under 30' END AS ..

SQL INSERT INTO SELECT The SQL INSERT INTO SELECT Statement 한 테이블의 데이터를 복사해서 다른 테이블에 삽입한다. 단, 이 명령문을 이용하려면 원본 테이블과 대상 테이블의 타입이 일치해야 한다. INSERT INTO SELECT Syntax INSERT INTO table2 SELECT * FROM table1 WHERE condition; INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition; SQL INSERT INTO SELECT Examples INSERT INTO Customers (Custo..

SQL ANY and ALL Operators The SQL ANY and ALL Operators ANY, ALL 연산자를 이용하면, single column value와 다른 value와 비교를 할 수 있다. The SQL ANY Operator 범위 값 중 하나에 대해 조건이 참인지를 검토하는 연산자 만약 하위 쿼리 중 하나라도 조건을 만족하면, TRUE를 반환한다. ANY Syntax SELECT column_name(s) FROM table_name WHERE column_name operator ANY( SELECT column_name FROM table_name WHERE condition ); 참고) operator는 =, , >, >=, 99); SELECT ProductName FRO..

SQL EXISTS Operator The SQL EXISTS Operator 하위 쿼리를 만족하는 레코드가 있는지 테스트 if랑 비슷한 역할 만약 하위 쿼리가 1개 이상의 레코드를 반환하는 경우 EXISTS 연산자는 TRUE를 반환한다 . EXISTS Syntax SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition); SQL EXISTS Examples SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supp..

SQL HAVING Clause The SQL HAVING Clause WHERE 처럼 condtion을 더하는 구문인데, WHERE과 달리 COUNT()과 같은 aggregate function과 함께 이용할 수 있다. HAVING Syntax SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s); SQL HAVING Examples SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5; SELECT COUNT(CustomerID)..

SQL GROUP BY Statement The SQL GROUP BY Statement 그룹 짓는 함수 주로 aggregate function과 함께 사용된다. COUNT(), MAX(), MIN(), SUM(), AVG() 등등 GROUP BY Syntax SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s); SQL GROUP BY Examples SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; SELECT City, Country, COUNT(CustomerID) FROM Customers GROUP..