Show mysql version:
SELECT VERSION();
Show available databases:
SHOW DATABASES;
Switch to a specific database:
USE dbname
Show available tables:
SHOW TABLES;
Find out what commands are available from mysql monitor:
HELP
From within the mysql monitor, bring up a vi window and edit the last command:
edit
Tell me what columns are available in a table:
SHOW COLUMNS FROM tableName;
DESCRIBE tableName;
MySQL equivalent of MS SQL TOP clause = LIMIT
SELECT col1 FROM table1 LIMIT 10;
Join - this only works if the matchingKeyColumn exists in both tables and has the same name in each
SELECT co1l, col2
FROM table1
INNER JOIN table2
USING (matchingKeyColumn);
Insert -
INSERT INTO table
VALUES ('Fred', 'Flintstone');
Delete -
DELETE FROM table
WHERE col1 = '123';
Truncate -
TRUNCATE TABLE table1;
UPDATE table1
SET col1 = 'Fred'
WHERE col2 = 'Flintstone';
Create -
CREATE TABLE tableName (
col1 SMALLINT(5) NOT NULL DEFAULT 0,
col2 CHAR(128) DEFAULT NULL,
PRIMARY KEY (col1)
);
Get SQL statement needed to create existing table:
SHOW CREATE TABLE tableName;
Drop -
DROP TABLE table1;
Reveal indexes:
SHOW INDEX FROM table1;
Create index while creating table:
Notice KEY = just an index whereas PRIMARY KEY = the primary key
CREATE TABLE table1 (
col1 SMALLINT(5) NOT NULL DEFAULT 0,
col2 CHAR(128) DEFAULT NULL,
PRIMARY KEY (col1),
KEY col2 (col2)
);
Composite index:
Notice three columns make up the composite index
CREATE TABLE table1 (
cust_id INT(4) NOT NULL DEFAULT 0,
firstname CHAR(50),
secondname CHAR(50),
surname CHAR(50),
PRIMARY KEY (cust_id),
KEY names (firstname, secondname, surname)
);
[Summarized from Learning MySQL]
