- 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';
- 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.