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