Basic SQL

By xngo on February 24, 2019

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.

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.