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
Joel G Mathew, known in tech circles by the pseudonym Droidzone, is an opensource and programming enthusiast.
He is a full stack developer, whose favorite languages are currently Python and Vue.js. He is also fluent in Javascript, Flutter/Dart, Perl, PHP, SQL, C and bash shell scripting. He loves Linux, and can often be found tinkering with linux kernel code, and source code for GNU applications. He used to be an active developer on XDA forums, and his tinkered ROMS used to be very popular in the early 2000s.
His favorite pastime is grappling with GNU compilers, discovering newer Linux secrets, writing scripts, hacking roms, and programs (nothing illegal), reading, blogging. and testing out the latest gadgets.
When away from the tech world, Dr Joel G. Mathew is a practising ENT Surgeon, busy with surgeries and clinical practise.