SQL

Your Trail

Table of Contents [show]

Keywords

relational databases

Copyright Information

This article is licensed under GNU Public License. Contact webmasters for more information on copyright issues.

1 What is SQL

SQL is short for Structured Query Language, which defines a unified querying interface for database systems. Most major database systems we ever heard of today supports SQL: Oracle, DB2, MS SQL, mySQL, postgreSQL, etc. Note that some database system might have their own extensions. For example, you can use "desc tablename" in mySQL to get a basic description of a table (columns names, types, etc), however, this statement is not standard SQL and thereby is not necessarily supported by other database systems, for example, in MS SQL, you have to use predefined stored procedure to get description of a table.

2 What is SQL for

SQL defines and interface for querying relational databases, i.e., databases organized as two-dimensional tables. Object-oriented databases, for example, is not directly supported by SQL. As we know, in a relational database, data is decomposed into multiple 2-dimensional tables. For example, we have an employee management database in which we need to management basic employee information, products they sell, and also who sold what (employee performance). In a relational database, we could decompose the data into two tables as follows (example done in postgreSQL):

-- Database: employee

-- DROP DATABASE employee;

CREATE DATABASE employee
  WITH OWNER = yang
       ENCODING = 'UNICODE'
       TABLESPACE = pg_default;

-- Table: employees

-- DROP TABLE employees;

CREATE TABLE employees
(
  employee_id int4 DEFAULT nextval('public."employee_id_seq"'::text),
  employee_sn int4,
  name char(50)
) 
WITHOUT OIDS;
ALTER TABLE employees OWNER TO yang;

-- Table: orders

-- DROP TABLE orders;

CREATE TABLE orders
(
  "OrderID" int4 DEFAULT nextval('public."order_id_seq"'::text),
  "ProdID" int4,
  "Product" varchar,
  "EmployeeID" int4
) 
WITHOUT OIDS;
ALTER TABLE orders OWNER TO yang;

Of course, you could use more tables as your system becomes more and more complex, for example, you might want to decompose tables into: employees, employee_orders, orders, order_products, products. In the new table structure, employee_orders assotiates employees with orders, and order_products assotiates orders with products.

3 Join statements

Sometimes you need to get data from multiple tables. The "join" statements were designed to join multiple tables together, the basic grammar for join is

SELECT xxx FROM orgi_table (inner|left|right)JOIN joined_table ON orig_table.colmunX = joined_table.columnY

3.1 Inner Join

This is the default join mode. An inner join of two tables returns concatinate(orig_table.rowA, joined_table.rowB) where there is a match (rowA.columnX == rowB.columnY). Any rows in orig_table or joined_table with no match will not be returned

3.2 Left Join

A left join of two tables returns concatinate(orig_table.rowA, joined_table.rowB) where there is a match (rowA.columnX == rowB.columnY), and contatinate(orig_table.rowA, NULL) when there is no match. In other words, Left join is similar to inner join, except that rows in orig_table without a match will also not be returned (by concatinating NULL instead)

3.3 Right Join

A left join of two tables returns concatinate(orig_table.rowA, joined_table.rowB) where there is a match (rowA.columnX == rowB.columnY), and contatinate(NULL, joined_table.rowB) when there is no match. In other words, Right join is similar to inner join, except that rows in joined_table without a match will also not be returned (by concatinating NULL instead)

4 How to concatenate two strings

SELECT CONCAT(cola, colb) AS aaa FROM tableaaa;

5 How to replace a part of a string?

mysql> update clips set img = replace(img, 'small', 'large') where source like 'abc';