Some Basic MySQL Commands

Enter Mysql

mysql -u root -p

Create Database

create DATABASE testdb;

Select Database

use testdb;

Delete Database

drop DATABASE testdb;

Drop Table

drop table table_name;

Show Tables

show tables;

Show data in table

SELECT * FROM table_name;

Create Table

CREATE TABLE contacts_table (id INT, name VARCHAR(20), email VARCHAR(20));

Insert data into table

INSERT INTO contacts_table (id,name,email) VALUES(2,"John","John83@incredigeek.com");

List one row in table

SELECT * FROM table_name LIMIT 1;

Delete Row in Table

DELETE FROM table_name WHERE row_name=data_to_delete;

Delete all “users” from a WordPress database that do not contain admin in the username.

DELETE FROM `wp-users` WHERE user_login NOT LIKE "%admin%"

Add Auto Increment to Table

ALTER TABLE  `table_name` ADD  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Create table with Date and Time timestamp

CREATE TABLE table_name (id INT, timeStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Show MySQL Users

select * from mysql.users;

Create MySQL User

GRANT ALL ON mysqldb.* TO username@localhost IDENTIFIED BY 'password';

Delete MySQL user

DROP USER 'username'@'localhost';

Add Column to the end of MySQL Table

ALTER TABLE mysqltable ADD email VARCHAR(60);

Add Column to the begging of MySQL Table

ALTER TABLE mysqltable ADD email VARCHAR(60) FIRST;

Insert Column after Specific Column in MySQL Table

ALTER TABLE mysqltable ADD email VARCHAR(60) AFTER columnname;