SHOW SELECT
|
1 |
SHOW TABLES |
|
1 |
SHOW COLUMNS FROM tablename |
MULTIPLE COLUMN
|
1 |
SELECT column FROM tablename |
|
1 |
SELECT tablename.column FROM tablename |
|
1 |
SELECT column1, column2 FROM tablename |
|
1 |
SELECT * FROM tablename |
LIMIT
|
1 |
SELECT column1, column2 FROM tablename LIMIT 5 |
|
1 |
SELECT column1, column2 FROM tablename LIMIT 5, 10 |
SORTING RESULT
|
1 |
SELECT column FROM tablename ORDER BY column |
example :
|
1 2 3 |
SELECT name FROM customers ORDER BY name SELECT name, address FROM customers ORDER BY id SELECT state, city, name FROM customers ORDER BY state, name |
SORT DIRECTION
|
1 |
SELECT column FROM tablename ORDER BY column DESC |
|
1 |
SELECT column FROM tablename ORDER BY column ASC |
|
1 |
SELECT column1, column2 FROM tablename ORDER BY column1 DESC LIMIT 1 |
example :
|
1 2 3 4 5 |
SELECT name, zip FROM customers ORDER BY zip DESC SELECT name, zip FROM customers ORDER BY name DESC SELECT name, zip FROM customers ORDER BY zip ASC SELECT name, zip FROM customers ORDER BY name ASC SELECT name, id FROM customers ORDER BY id DESC LIMIT 1 |
Basic Data FILTERING and WHERE
|
1 |
SELECT column1, column2 FROM tablename WHERE column1=value |
|
1 |
SELECT column1, column2 FROM tablename WHERE column1 != value |
|
1 |
SELECT column1, column2 FROM tablename WHERE column1 > value |
|
1 |
SELECT column1, column2 FROM tablename WHERE column1 < value |
|
1 |
SELECT column1, column2 FROM tablename WHERE column1 <= value |
|
1 |
SELECT column1, column2 FROM tablename WHERE column1 >= value |
|
1 |
SELECT column1, column2 FROM tablename WHERE column1 BETWEEN value1 AND value2 |
|
1 |
SELECT column3, column4 FROM tablename WHERE column4 = 'value' |
example :
|
1 2 3 4 5 6 7 8 |
SELECT id, name FROM customers WHERE id=54 SELECT id, name FROM customers WHERE id!=54 SELECT id, name FROM customers WHERE id > 10 SELECT id, name FROM customers WHERE id < 10 SELECT id, name FROM customers WHERE id <= 8 SELECT id, name FROM customers WHERE id >= 8 SELECT id, name FROM customers WHERE id BETWEEN 25 AND 30 SELECT name, state FROM customers WHERE state = 'CA' |
Advanced FILTERING USING AND and OR
|
1 |
SELECT column2, column3, column4 FROM tablename WHERE column3='value3' AND column4='value4' |
|
1 |
SELECT column2, column3, column4 FROM tablename WHERE column3='value3' OR column4='value4' |
|
1 |
SELECT column1, column2, column3 FROM tablename WHERE column1=value1 OR column1=value2 AND column3='value3' |
|
1 |
SELECT column1, column2, column3 FROM tablename WHERE (column1=value1 OR column1=value2) AND column3='value3' |
|
1 |
SELECT column1, column2, column3 FROM tablename WHERE column1=value1 OR (column1=value2 AND column3='value3') |
example :
|
1 2 3 4 5 |
SELECT name, state, city FROM customers WHERE state='CA' AND city='Hollywood' SELECT name, state, city FROM customers WHERE state='CA' OR city='Boston' SELECT id, name, city FROM customers WHERE id=1 OR id=2 AND city='Raleigh' SELECT id, name, city FROM customers WHERE (id=1 OR id=2) AND city='Raleigh' SELECT id, name, city FROM customers WHERE id=1 OR (id=2 AND city='Raleigh') |
IN or NOT IN
|
1 |
SELECT column1, column2 FROM tablename WHERE column2='value1' OR column2='value2' OR column3='value3' |
|
1 |
SELECT column1, column2 FROM tablename WHERE column2 IN ('value1','value2','value3') |
|
1 |
SELECT column1, column2 FROM tablename WHERE column2 NOT IN ('value1','value2','value3') |
example :
|
1 2 3 |
SELECT name, state FROM customers WHERE state='CA' OR state='NC' OR state='NY' SELECT name, state FROM customers WHERE state IN ('CA','NC','NY') SELECT name, state FROM customers WHERE state NOT IN ('CA','NC','NY') |
LIKE ‘%’
|
1 |
SELECT column1 FROM tablename WHERE column1 LIKE 'value%' |
|
1 |
SELECT column1 FROM tablename WHERE column1 LIKE '%value' |
|
1 |
SELECT column1 FROM tablename WHERE column1 LIKE '%value%' |
|
1 |
SELECT column1 FROM tablename WHERE column1 LIKE 'value%value' |
example :
|
1 2 3 4 |
SELECT name FROM items WHERE name LIKE 'new%' SELECT name FROM items WHERE name LIKE '%new' SELECT name FROM items WHERE name LIKE '%computer%' SELECT city FROM customers WHERE city LIKE 'h%d' |
LIKE ‘_’
|
1 |
SELECT column1 FROM tablename WHERE column1 LIKE '_ value' |
example :
|
1 |
SELECT name FROM items WHERE name LIKE '_ new computer' |
Regular Expressions REGEXP
|
1 |
SELECT column FROM tablename WHERE column REGEXP 'value' |
|
1 |
SELECT column FROM tablename WHERE column REGEXP '.value' |
|
1 |
SELECT column FROM tablename WHERE column REGEXP 'value|value' |
|
1 |
SELECT column FROM tablename WHERE column REGEXP '[12345] value' |
|
1 |
SELECT column FROM tablename WHERE column REGEXP '[^12345] value' |
|
1 |
SELECT column FROM tablename WHERE column REGEXP '[1-5] value' |
example :
|
1 2 3 4 5 6 |
SELECT name FROM items WHERE name REGEXP 'new' SELECT name FROM items WHERE name REGEXP '.boxes' SELECT name FROM items WHERE name REGEXP 'gold|car' SELECT name FROM items WHERE name REGEXP '[12345] boxes of frogs' SELECT name FROM items WHERE name REGEXP '[^12345] boxes of frogs' SELECT name FROM items WHERE name REGEXP '[1-5] boxes of frogs' |
Creating Custom Columns
|
1 |
SELECT CONCAT(column1, ', ', column2) FROM tablename |
|
1 |
SELECT CONCAT(column1, ', ', column2) AS new_column FROM tablename |
|
1 |
SELECT column1, column2, column2-1 FROM tablename |
|
1 |
SELECT column1, column2, column2-1 AS new_column FROM tablename |
|
1 |
SELECT column1, column2, column2+1 AS new_column FROM tablename |
|
1 |
SELECT column1, column2, column2*1 AS new_column FROM tablename |
|
1 |
SELECT column1, column2, column2/1 AS new_column FROM tablename |
example :
|
1 2 3 4 5 6 7 |
SELECT CONCAT(city, ', ', state) FROM customers SELECT CONCAT(city, ', ', state) AS new_address FROM customers SELECT name, cost, cost-1 FROM tablename SELECT name, cost, cost-1 AS sale_price FROM tablename SELECT name, cost, cost+1 AS sale_price FROM tablename SELECT name, cost, cost*1 AS sale_price FROM tablename SELECT name, cost, cost/1 AS sale_price FROM tablename |
FUNCTION UPPER, SQRT, AVG, SUM
|
1 |
SELECT column1, UPPER(column1) FROM tablename |
|
1 |
SELECT column1, SQRT(column1) FROM tablename |
|
1 |
SELECT AVG(column1) FROM tablename |
|
1 |
SELECT SUM(column1) FROM tablename |
|
1 |
SELECT COUNT(column1) FROM tablename |
example :
|
1 2 3 4 5 |
SELECT name, UPPER(name) FROM customers SELECT cost, SQRT(cost) FROM items SELECT AVG(cost) FROM items SELECT SUM(bids) FROM items SELECT COUNT(name) FROM items |
Aggregate Functions
|
1 |
SELECT COUNT(column1) FROM tablename WHERE column2=6 |
|
1 |
SELECT AVG(column1) FROM tablename WHERE column2=6 |
|
1 |
SELECT COUNT(*) AS new_column, MAX(column1) AS max, AVG(column1) AS avg FROM tablename WHERE column_2=12 |
example :
|
1 2 3 |
SELECT COUNT(name) FROM items WHERE seller_id=6 SELECT AVG(cost) FROM items WHERE seller_id=6 SELECT COUNT(*) AS item_count, MAX(cost) AS max, AVG(cost) AS avg FROM items WHERE seller_id=12 |
GROUP BY
|
1 |
SELECT column1, COUNT(*) AS new_column FROM tablename GROUP BY column1 |
|
1 |
SELECT column1, COUNT(*) AS new_column FROM tablename GROUP BY column1 HAVING COUNT(*) >=3 |
|
1 |
SELECT column1, COUNT(*) AS new_column FROM tablename GROUP BY column1 HAVING COUNT(*) >=3 ORDER BY new_column DESC |
example :
|
1 2 3 |
SELECT seller_id, COUNT(*) AS item_count FROM items GROUP BY seller_id SELECT seller_id, COUNT(*) AS item_count FROM items GROUP BY seller_id HAVING COUNT(*) >=3 SELECT seller_id, COUNT(*) AS item_count FROM items GROUP BY seller_id HAVING COUNT(*) >=3 ORDER BY item_count DESC |
SUBQUERIES 1
|
1 |
SELECT AVG(column1) FROM tablename |
|
1 |
SELECT column1, column2 FROM tablename WHERE column2(463) ORDER BY column2 DESC |
|
1 |
SELECT column1, column2 FROM tablename WHERE column2(SELECT AVG(column2) FROM tablename) ORDER BY column2 DESC |
example :
|
1 2 3 |
SELECT AVG(cost) FROM items SELECT name, cost FROM items WHERE cost>(463) ORDER BY cost DESC SELECT name, cost FROM items WHERE cost>(SELECT AVG(cost) FROM items) ORDER BY cost DESC |
SUBQUERIES 2
|
1 |
SELECT column1 FROM tablename WHERE column2 LIKE '%bla bla bla' |
|
1 |
SELECT column2, MIN(column3) FROM tablename WHERE column1 LIKE '%bla bla bla' AND column1 IN(1,2,3) |
|
1 |
SELECT column2, MIN(column3) FROM tablename WHERE column1 LIKE '%bla bla bla' AND column1 IN(SELECT column1 FROM tablebname WHERE column2 LIKE '%bla bla bla') |
example :
|
1 2 3 |
SELECT seller_id FROM items WHERE name LIKE '%boxes of frogs' SELECT name, MIN(cost) FROM items WHERE name LIKE '%boxes of frogs' AND seller_id IN(68,6,18) SELECT name, MIN(cost) FROM items WHERE name LIKE '%boxes of frogs' AND seller_id IN(SELECT seller_id FROM items WHERE name LIKE '%boxes of frogs') |
JOIN TABLES
|
1 |
SELECT table1.column1, table1.column2, table2.column2, table2.column3 FROM table1, table2 WHERE table1.column1=column4 ORDER BY table1.column1 |
example :
|
1 |
SELECT customers.id, customers.name, items.name, items.cost FROM customers, items WHERE customers.id=seller_id ORDER BY customers.id |
OUTER JOIN
|
1 |
SELECT i.column3, i.column4, c.column5 FROM tablename1 AS c, tablename2 AS i WHERE i.column3=c.column1 |
|
1 |
SELECT tablename1.column2, tablename2.column2 FROM tablename1, tablename2 WHERE tablename2.column1=column2 |
|
1 |
SELECT tablename1.column2, tablename2.column2 FROM tablename1, LEFT OUTER JOIN tablename2 ON tablename1.column1=column2 |
|
1 |
SELECT tablename1.column2, tablename2.column2 FROM tablename1, RIGHT OUTER JOIN tablename2 ON tablename1.column1=column2 |
example :
|
1 2 3 4 |
SELECT i.seller_id, i.name, c.id FROM cutomers AS c, items AS i WHERE i.seller_id=c.id SELECT customers.name, items.name FROM customers, items WHERE customers.id=seller_id SELECT customers.name, items.name FROM customers, LEFT OUTER JOIN items ON customers.id=seller_id SELECT customers.name, items.name FROM customers, RIGHT OUTER JOIN items ON customers.id=seller_id |
UNION
|
1 |
SELECT column1, column2, column3 FROM tablename WHERE column3>190 UNION SELECT column1, column2, column3 FROM tablename WHERE column2>1000 |
|
1 |
SELECT column1, column2, column3 FROM tablename WHERE column3>190 UNION ALL SELECT column1, column2, column3 FROM tablename WHERE column2>1000 |
example :
|
1 2 |
SELECT name, cost, bids FROM items WHERE bids>190 UNION SELECT name, cost, bids FROM items WHERE cost>1000 SELECT name, cost, bids FROM items WHERE bids>190 UNION ALL SELECT name, cost, bids FROM items WHERE cost>1000 |
Text Searching
|
1 |
SELECT column2, column3 FROM tablename WHERE Match(column2) Against('string') |
|
1 |
SELECT column2, column3 FROM tablename WHERE Match(column2) Against('+string -string' IN BOOLEAN MODE) |
example :
|
1 2 |
SELECT name, cost FROM items WHERE Match(name) Against('baby') SELECT name, cost FROM items WHERE Match(name) Against('+baby -coat' IN BOOLEAN MODE) |
INSERT
|
1 |
INSERT INTO tablename VALUES ('value1','value2','value3','value4','value5') |
|
1 |
INSERT INTO tablename(column1, column2, column3, column4, column5) VALUES ('value1','value2','value3','value4','value5') |
example :
|
1 2 |
INSERT INTO items VALUES ('101','bacon strips','7.95','1','0') INSERT INTO items(id, name, cost, seller_id, bids) VALUES ('101','bacon strips','7.95','1','0') |
MULTIPLE INSERT
|
1 |
INSERT INTO tablename(column1,column2,column3,column4,column5) VALUES ('value1','value2','value3','value4','value5'),('value1','value2','value3','value4','value5'),('value1','value2','value3','value4','value5') |
example :
|
1 |
INSERT INTO items(id, name, cost, seller_id, bids) VALUES ('104','beef chops','7.99','1','0'),('105','jelly pockets','4.50','1','0'),('106','sack of ham','9.95','1','0') |
UPDATE
|
1 |
UPDATE tablename SET column2='value2' WHERE column1=value1 |
|
1 |
UPDATE tablename SET column2='value2', column3=value3 WHERE column1=value1 |
example :
|
1 2 |
UPDATE items SET name='puddinghammock' WHERE id=106 UPDATE items SET name='pasta pasta', bids=60 WHERE id=106 |
DELETE
|
1 |
DELETE FROM tablename WHERE column1=value1 |
example :
|
1 |
DELETE FROM items WHERE id=106 |
CREATE TABLE
|
1 |
CREATE TABLE tablename(column1 type, column2 type, column3 type, PRIMARY KEY(column1)) |
example :
|
1 |
CREATE TABLE users(id int, username varchar(30), password varchar(20), PRIMARY KEY(id)) |
NOT NULL & AUTO INCREMENT
|
1 |
CREATE TABLE tablename(column1 type NOT NULL AUTO_INCREMENT, column2 type(30) NOT NULL, column3 type(20) NOT NULL, PRIMARY KEY(id)) |
example :
|
1 |
CREATE TABLE users(id int NOT NULL AUTO_INCREMENT, username varchar(30) NOT NULL, password varchar(20) NOT NULL, PRIMARY KEY(id)) |
ALTER
|
1 |
ALTER TABLE tablename ADD column_new type(10) |
|
1 |
ALTER TABLE tablename DROP COLUMN tablename |
example :
|
1 2 |
ALTER TABLE users ADD samplecolumn varchar(10) ALTER TABLE users DROP COLUMN samplecolumn |
DROP
|
1 |
DROP TABLE tablename |
example :
|
1 |
DROP TABLE users |
RENAME TABLE
|
1 |
RENAME TABLE tablename_exist TO tablename_new |
example :
|
1 |
RENAME TABLE costumers TO users |
VIEWS
|
1 |
CREATE VIEW tableview AS SELECT column1, column2, column3 FROM ORDER BY tablename DESC LIMIT 10 |
|
1 |
CREATE VIEW tableview AS SELECT Concat(column3,',',column4) AS new_column FROM tablename |
example :
|
1 2 |
CREATE VIEW mostbids AS SELECT id, name, bids FROM ORDER BY bids DESC LIMIT 10 CREATE VIEW mailing AS SELECT Concat(city,',',state) AS address FROM users |