A Software Guy Blog
0%

200809 TIL

Posted on Edited on In , ,

Data Manipulation Language, DML

  • SELECT - 검색
  • INSERT - 등록
  • UPDATE - 수정
  • DELETE - 삭제

SELECT

1
SELECT(DISTINCT) {column_name} (ALIAS) FROM {table_name};
SELECT 검색하고자 하는 데이터(column)를 나열
DISTINCT 중복행을 제거
ALIAS 나타날 컬럼에 대한 다른 이름 부여
FROM 선택한 컬럼이 있는 테이블을 명시한다

Select Examples

  • Search whole data
1
SELECT * FROM DEPARTMENT;
  • Search certain column
1
SELECT empno, name, job from employee;
  • Put alias to column
1
SELECT empno as 사번, name as 이름, job as 직업 from employee;

or

1
SELECT empno 사번, name 이름, job 직업 from employee;
  • Column concatenation
    • using concat func
1
SELECT concat(empno, '-', deptno) AS '사번-부서번호' FROM employee;
  • Removing redundant row
1
SELECT DISTINCT deptno FROM employee;
  • Using ORDER BY
1
SELECT(DISTINCT) {column_name} ((as) ALIAS) FROM {table_name} ORDER BY {column_name} (ASC, DESC)
ASC by asending order (default)
DESC by desending order 
1
SELECT empno, name, job FROM employee order by name desc;
  • Using WHERE
1
2
SELECT(DISTINCT) {column_name} FROM {table_name} WHERE {contidion}
ORDER BY {column_name or statement} (ASC, DESC)
1
SELECT name, hiredate, FROM employee WHERE hiredate < '1981-01-01';
1
2
3
4
SELECT name, deptno FROM employee WHERE deptno in (10, 30);
SELECT name, deptno FROM employee WHERE deptno = 10 or deptno = 30;
SELECT * from employee WHERE deptno = 10 and salary < 1500;
SELECT * from employee WHERE deptno = 30 and salary < 1500;

  • LIKE keyword

    • using wildcard to select pattern string
      • %: 0-9a-zA-Z several charactor
      • _: single charactor
    1
    SELECT * FROM employee WHERE name LIKE 'A%'
    1
    SELECT * FROM employee WHERE name like '_A%';
    1
    SELECT * FROM employee WHERE name like '%A%';

  • Using SQL function

    • UCASE, UPPER
    1
    SELECT UPPER('SEoul'), UCASE('seOUL`);
    • LCASE, LOWER
    1
    SELECT lower(name) FROM employee;

    • SUBSTRING

      1
      SELECT SUBSTRING('Happy Day', 3, 2);

    • LPAD, RPAD

      1
      SELECT LPAD('hi', 5, '?'), LPAD('joe', '7', '*');

    • TRIM, LTRIM, RTRIM

      1
      SELECT LTRIM(' hello '), RTRIM(' hello ');

    • ABS(x)

    • MOD(n, m)

  • CAST: type casting CAST(expression AS type)

  • Group function

    • COUNT()
    • COUNT(DISTINCT)
    • AVG()
    • MIN()
    • MAX()
    • SUM()
    • GROUP_CONCAT()
    • VARIANCE()
    • STDEV()

  • Using group function with group by clause

    1
    SELECT deptno, avg(salary), sum(salary) FROM employee GROUP BY deptno;

INSERT

1
2
INSERT INTO {table_name} (field1, field2, field3, ...)
  VALUES (value_field1, value_field2, value_field3, ...)
  • The primary key must have value, since primary key cannot be set NULL value.

UPDATE

1
2
3
UPDATE {table_name}
    SET field1 = value_field1, field2 = value_field2, field3 = value_field3 ...
  WHERE {condition}
  • When WHERE condition is omitted, every row is affected by UPDATE statement.

DELETE

1
2
3
DELETE
  FROM {table_name}
WHERE {condition}
  • Keep in mind that WHERE condition must be included unless you intend to delete all data.

JOIN

1
2
SELECT employee.name, department.name FROM employee
JOIN department ON employee.deptno = department.deptno;

Data Definition Language, DDL

Create Table

1
2
3
4
5
6
7
CREATE TABLE {table_name}(
  field_name1 type [NULL | NOT NULL][DEFAULT][AUTO_ENCREMENT],
  field_name2 type [NULL | NOT NULL][DEFAULT][AUTO_ENCREMENT],
  field_name2 type [NULL | NOT NULL][DEFAULT][AUTO_ENCREMENT],
  ...
  PRIMARY KEY (field_name)
)
  • Creating another employ table
1
DESC EMPLOYEE
Field Type Null Key Default Extra
empno int NO PRI NULL
name varchar(10) YES NULL
job varchar(9) YES NULL
boss int YES MUL NULL
hiredate varchar(12) YES NULL
salary decimal(7,2) YES NULL
comm decimal(7,2) YES NULL
deptno int YES MUL NULL 
1
2
3
4
5
6
7
8
9
create table employee2 (
  empno integer not null primary key,
  name varchar(10),
  job varchar(9),
  boss integer,
  hiredate varchar(12),
  salary decimal(7,2),
  comm decimal(7,2),
  deptno integer);
  • Creating book table
1
2
3
4
CREATE TABLE book(
  isbn varchar(10) primary key,
  title varchar(20) not null,
  price integer not null);

Alter Table

  • Add column field to table
1
2
ALTER TABLE {table_name}
  ADD {field_name} type [NULL | NOT NULL][DEFAULT][AUTO_INCREMENT];

  • example

    1
    2
    ALTER TABLE book
      add author varchar(20) NOT NULL;
  • Delete column field on table
1
2
ALTER TABLE {table_name}
  drop {field_name};

  • example

    1
    2
    ALTER table book
      DROP price;
  • Update column field (both its name and property)
1
2
ALTER TABLE {table_name}
  CHANGE {old_field_name} {new_field_name} [NULL | NOT NULL][DEFAULT][AUTO_INCREMENT];

  • example

    1
    2
    Alter table employee2
      CHANGE deptno dept_no int(11);
  • Renaming Table
1
ALTER TABLE {old_table_name} rename {new_table_name}

  • example

    1
    ALTER TABLE employee2 rename employee3;

Drop Table

1
DROP TABLE {table_name};
  • If there is some relational dependencies between tables, it should be deleted in reverse order of creation.