Mysql Cheat Sheet (Page 2 of 2) in pdf

Mysql Cheat Sheet Page 2

Sorting

1) Ascending (forward):

SELECT col1, col2 FROM table ORDER BY col2;

2) Descending (backward):

SELECT col1, col2 FROM table ORDER BY col2 DESC;

VI.

Counting

mysql> SELECT COUNT(*) FROM table;

VII.

Sum

mysql> SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

VIII.

Grouping (GROUP BY groups together all records for a certain column)

mysql> SELECT Customer,

-> SUM(OrderPrice) FROM Orders

-> GROUP BY Customer

IX.

Updating

mysql> UPDATE Persons

-> SET Address='Nissestien 67', City='Sandnes'

-> WHERE LastName='Tjessem' AND FirstName='Jakob'

Deleting

mysql> DELETE FROM Persons

-> WHERE LastName='Tjessem' AND FirstName='Jakob'

XI.

Pattern Matching

1) General

mysql> SELECT * FROM table WHERE rec LIKE "blah%";

(% is wildcard - arbitrary # of chars)

2) Find 5-char values:

SELECT * FROM table WHERE rec like "_____";

(_ is any single character)

XII.

Maximum value:

mysql> SELECT MAX(col_name) AS somename FROM table;

XIII.

Altering a Table

1) Adding a column to an already-created table:

mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;

2) Removing a column:

mysql> ALTER TABLE tbl DROP COLUMN col;

XIV.

Batch mode (feeding in a script, source refers to file location):

mysql> source batch_file;

Ex: mysql> \. myfile.sql

XV.

Backup and Restore (Don't login to mysql before running these commands)

mysqldump --user=USERNAME -p --databases DB_NAME > DUMPFILE.SQL

mysql -u USERNAME -p DB_NAME < DUMPFILE.SQL

XVI.

Other Commands to be aware of: FOREIGN KEY(keyInAnotherTable), DROP TABLE table_name,

Mysql Cheat Sheet Page 2

Related Articles

Related forms

Related Categories