Mysql Cheat Sheet
ADVERTISEMENT
1
2CISC3410
MySQL Cheat Sheet
I.
Starting MySQL
mysql -u <username> -p
II.
Database Operations
1) Selecting a database:
mysql> USE database;
2) Listing databases:
mysql> SHOW DATABASES;
3) Creating a database:
mysql> CREATE DATABASE db_name;
4) Identify the currently selected database:
mysql> SELECT DATABASE();
III.
Table Operations
1) Listing tables in a db:
mysql> SHOW TABLES;
2) Describing the format of a table:
mysql> DESC table;
3) Creating a table:
mysql> CREATE TABLE pet (id INT NOT NULL, name VARCHAR(20), sex
CHAR(1), birth DATE, PRIMARY KEY(id));
4) Auto-incrementing rows:
mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT);
5) Inserting one row at a time:
mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
(Use NULL for NULL)
6) Load tab-delimited data into a table:
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
(Use \n for NULL)
7) Drop Table: mysql> DROP TABLE table_name
IV.
Querying a database
1) Retrieving information (general):
SELECT from_columns FROM table WHERE conditions;
2) All values:
SELECT * FROM table;
3) Some values:
SELECT * FROM table WHERE rec_name = "value";
4) Multiple critera:
SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";
5) Selecting specific columns:
SELECT column_name FROM table;
6) Retrieving unique output records:
SELECT DISTINCT column_name FROM table;
7) Selecting from multiple tables (Inner Join):
(Example)
mysql> SELECT pet.name, comment
-> FROM pet, event
-> WHERE pet.name = event.name;
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education