OpenWritings.net

Basic

Create a table

--Create a table called t1 which has 2 columns, col1 and col2. 
--col1 is defined to allow only 1 characters and col2 is defined to allow integer.
CREATE TABLE t1( col1 CHAR(1), col2 INT);

Insert data in table

INSERT INTO t1 VALUES('A', 123); -- Insert 'A' and 123 into table t1.
INSERT INTO t1 VALUES('Z', 23 ); -- Insert 'Z' and 23 into table t1.
INSERT INTO t1 VALUES('B', 23 ); -- Insert 'Z' and 23 into table t1.

View data in table

--View data of 1st column.       View data of 1st & 2nd column.       View data of all columns(*).
SELECT col1 FROM t1;             SELECT col1, col2 FROM t1;           SELECT * FROM t1;
-- Results:                      Results:                             Results:
-- +------+                      +------+------+                      +------+------+
-- | col1 |                      | col1 | col2 |                      | col1 | col2 |
-- +------+                      +------+------+                      +------+------+
-- | A    |                      | A    |  123 |                      | A    |  123 |
-- | Z    |                      | Z    |   23 |                      | Z    |   23 |
-- | B    |                      | B    |   23 |                      | B    |   23 |
-- +------+                      +------+------+                      +------+------+ 

Delete rows in table

-- Delete from table t1 where records in col1 is equal to 'Z'
DELETE FROM t1 WHERE col1='Z';
 
-- Delete all rows from table t1.
DELETE FROM t1;

Change value of records in table

-- Change all records which has value 123 to 555.
UPDATE t1 SET col2=555 WHERE col2=123;
 
-- Change value of all records of col1 to X.
UPDATE t1 SET col1='X';

Useful Statements

SELECT INTO

Create a new table with results returned by SELECT statement. This statement is very useful because you don't have to defined beforehand the description of the table specified.

--The important thing to remember here is to insert INTO after SELECT.
--The INNER JOIN serves as an example. You can make the SELECT query as complex as you want.
SELECT *
INTO MyNewTable
FROM TableA INNER JOIN TableB ON TableA.col = TableB.col;

LEFT JOIN

Return all records of the left table, and fill them with NULL values if it can’t find a matching record on the right table.

RIGHT JOIN

RIGHT join is the opposite of LEFT join. It will return all records of the right table, and fill them with NULL values if it can't find a matching record on the left table.

IN() operator

DELETE TableA.*
FROM TableA
WHERE TableA.Col IN (
		SELECT TableB.Col
		FROM TableB
		GROUP BY TableB.Col
		)
;
--Make sure that the nested select doesn't refer to the table that you will delete from.