SQL

1
SQL
Structured Query Language
2
SQL ???? Structured Query Language

• ??????????????????????????????????????????????????
  ??? ?????????????????????????????????????????????
  ? ??????????????????? ????????????????????????????
  ???????????? SQL Server , Microsoft Access ,
  MySQL ,DB2 ?????????? Oracle ?????????????????????
  SQL ???????????????????

3
SQL ??????????????????????? 4 ????? ???

• Data Manipulate(DML)
• ?????????????????????? ?????? INSERT, UPDATE,
  DELETE, ROLLBACK, COMMIT
• Data Definition (DDL)
• ?????????????????????????????????? ?????? CREATE,
  ALTER, DROP
• Query
• ?????????????????????????? ??? SELECT
• Data Control
• ???????????????????????????

4
Relational Database

• Table ?????????? ??????? (Column) ????????????
  (Row) ???????????????????? ???????????
  ???????????? ????????????????? (???? ????????????
  primary key, ???????????) ???????????????????????

Column
Row
5
???????? Database

• ????????
• CREATE DATABASE tot

• ????????
• CREATE DATABASE databasename

6
???????? Table

• ????????
• CREATE TABLE employee
• (first varchar(15),
• last varchar(20),
• age number(3),
• address varchar(30),
• city varchar(20),
• state varchar(20))

• ????????
• CREATE TABLE tablename
• (column1 datatype constraint,
• column2 datatype constraint,
• column3 datatype constraint,)

7
?????????????

• ????????
• INSERT INTO tablename
• (column1,column2,...)
• VALUES(value1,value2,...)
• ????????
• INSERT INTO employee
• (first, last, age, address, city, state)
• VALUES ('Luke', 'Duke', 45, '2130 Boars Nest',
• 'Hazard Co', 'Georgia')

8
?????????????????

• ????????
• UPDATE tablename
• SET columnname1 newvalue1 ,columnname2"
  "newvalue2"...
• WHERE column1  OPERATOR value  AND OR column2
  OPERATOR value
• ????????
• UPDATE phone_book
• SET area_code 623
• WHERE prefix 979

9
???????????

• ????????
• DELETE FROM tablename
• WHERE column  OPERATOR value  ANDOR column 
  OPERATOR value
• ????????
• DELETE FROM employeeWHERE firstname 'Mike' or
  firstname 'Eric'

10
????? Table

• DROP statement ????????????? Table ??????????
• ????????
• DROP TABLE tablename
• ????????
• DROP TABLE employee

11
SQL Query

• SELECT Statement
• GROUP BY
• HAVING
• ORDER BY
• ????????????????? Boolean Operator
• IN ??? BETWEEN

12
SELECT Statement

• ???????????? SELECT ??????????????????????????????
  ????????????? ????????????????????????????????????
  ???
• ????????
• SELECT column FROM table1,table2WHERE
  conditions GROUP BY column-list HAVING
  conditions ORDER BY column-list   ASC DESC
  
• ????????
• SELECT name, age, salaryFROM employeeWHERE age
  gt 50

SELECT name, age, salaryFROM employeeWHERE name
LIKE j
13
SELECT Statement (cont.)

• ?????????????????????? (comparison operator)
• ???????????????????????????

14
ORDER BY

• ORDER BY clause ???? clause ??????????????????????
  ????????????????????????????????
  (?????????????????????????? ???? ????????????)
  ??????????????????????????????ASC Ascending
  order (??????????????????????) -
  ???????????????DESC Descending order
  (??????????????????????)
• ????????
• SELECT column1, SUM(column2 )FROM
  list-of-tablesORDER BY olumn-list  ASC DESC
• ????????
• SELECT employee_id,name, age, salary,deptFROM
  employeeWHERE dept SalesORDER BY salary
  DESC

15
?????????????? ??? Boolean Operator

• AND operator
• SELECT employee_id, name, salaryFROM
  employeeWHERE salary gt 20000.00 AND dept
  'Programmer'
• OR operator
• SELECT employee_id, name, salaryFROM
  employeeWHERE (dept 'Sales') OR (dept
  'Programmer')

16
IN ??? BETWEEN

• IN
• SELECT employee_id, name, salaryFROM
  employeeWHERE name IN ("jenifer lopez","will
  human")
• BETWEEN
• SELECT employee_id, age, name, salaryFROM
  employeeWHERE age BETWEEN 30 AND 40

17
Aggregate Function

• ????????
• SELECT AVG(salary)FROM employee
• ?????????????????????????????????????????????????
  ?????????????????????????? salary ??? table
  employee
• SELECT MAX(salary)FROM employeeWHERE dept
  'Programmer'
• ?????????????????????????????????????????????
  (salary) ?????????????????????? (title) ????
  Programmer