mysql ready reckoner

Create database:

CREATE DATABASE IF NOT EXISTS dbl;

List databases:

SHOW DATABASES;

Eg:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| tbl        |
+--------------------+
2 rows in set (0.00 sec)

Work on a database (Use a database):

USE database;

Eg:

mysql> use tbl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Create a user:

CREATE USER 'username'@'localhost';

Set Password for user:

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('mypasswordintext');

Grant all privileges on database to user:

GRANT ALL ON dbl.* TO 'username'@'localhost';

Note that you shouldn’t use quotes for the table name.

Create table with fields:

 CREATE TABLE tbl (
    -> id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> userid VARCHAR(30) NOT NULL,
    -> password VARCHAR(30) NOT NULL,
    -> role VARCHAR(20) NOT NULL,
    -> email VARCHAR(50)
    -> );

List tables in the database:

SHOW TABLES;

Eg:

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_episodestbl |
+-----------------------+
| Animals               |
+-----------------------+
1 row in set (0.00 sec)

Show the structure of a table:

DESCRIBE table;

Eg:

mysql> DESCRIBE users;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| userid   | varchar(30) | NO   | PRI | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
| role     | varchar(20) | NO   |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

Insert (Replace if it already exists) data:

 REPLACE INTO `users`
    -> SET `user` = "myusr",
    -> `pass` = "mypass",
    -> `role` = "admin";

Replace works only if the primary key has a different value. If primary key is autoincremented (a bad idea), it duplicates rows.

Alternately,

INSERT INTO `episodestbl`.`users` (`userid`, `password`, `role`, `email`) VALUES ('someone', 'password', 'user', [email protected]');

Deleting a column:

alter table auth drop id;

Deleting duplicate rows:
SELECT * FROM names;

+----+--------+
| id | name   |
+----+--------+
| 1  | google |
| 2  | yahoo  |
| 3  | msn    |
| 4  | google |
| 5  | google |
| 6  | yahoo  |
+----+--------+

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

Show all table data:

SELECT * FROM TableName;

mysql> SELECT * FROM Authentication;
+--------+-----------------+---------------+-----------------------+---------------+---------------+
| user   | pword           | who           | email                 | ip            | loginfailures |
+--------+-----------------+---------------+-----------------------+---------------+---------------+
| jack   | gobbledegook    | administrator | [email protected]     | 116.68.73.178 |             0 |
| jones  | otheroall       | user          | [email protected]| 163.47.12.220 |             0 |
+--------+-----------------+---------------+-----------------------+---------------+---------------+
2 rows in set (0.00 sec)

Deleting a column:

alter table auth drop id;

Change a field to a unique id:

ALTER TABLE auth ADD UNIQUE uid (userid);

Add a field to a table:

USE databasename;
ALTER TABLE tablename ADD newfield VARCHAR(15) NOT NULL;

Useful links:
https://en.wikibooks.org/wiki/MySQL/CheatSheet
http://cse.unl.edu/~sscott/ShowFiles/SQL/CheatSheet/SQLCheatSheet.html