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;
