MySql CheatSheet

#data #database #mysql

Anuja Fole Oct 16 2020 · 1 min read
Share this
Description Command
Login mysql -u root -p
Create User CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
Show users SELECT User, Host FROM mysql.user;
Grant all Privileges on all database GRANT ALL PRIVILEGES ON * . * TO 'userna'@'localhost';
FLUSH PRIVILEGES;
Show Grants SHOW GRANTS FOR 'someuser'@'localhost';
Remove Grants REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'someuser'@'localhost';
Delete User DROP USER 'someuser'@'localhost';
Exit exit;
Create Database CREATE DATABASE [IF NOT EXISTS] database_name;
Use the database or change the current database USE database_name;
List all databases on the sql server. SHOW DATABASES;
Delete Database DROP DATABASE [IF EXISTS] database_name;
Create a table (ex.) CREATE TABLE users(
id INT AUTO_INCREMENT,
first_name VARCHAR(100),
last_name VARCHAR(100),
email VARCHAR(50),
password VARCHAR(20),
location VARCHAR(100),
dept VARCHAR(100),
is_admin TINYINT(1),
register_date DATETIME,
PRIMARY KEY(id)
);
To see all the tables in the db. SHOW TABLES;
Add a new column into a table:   ALTER TABLE table
ADD [COLUMN] column_name;
ALTER TABLE users ADD age VARCHAR(3);
Modify a column ALTER TABLE users MODIFY COLUMN age INT(3);
Drop a column from a table ALTER TABLE table_name
DROP [COLUMN] column_name;
Add index with a specific name to a table on a column: ALTER TABLE table
ADD INDEX [name](column, ...);
Add Primary Key into the table ALTER TABLE table_name
ADD PRIMARY KEY (column_name,...);
Remove the primary key of a table: ALTER TABLE table_name
DROP PRIMARY KEY;
Drop a table DROP TABLE [IF EXISTS] table_name;
Show the columns of the table DESCRIBE table_name;
Show the information of a column in a table DESCRIBE table_name column_name;
Insert a row into the table "users" INSERT INTO users (first_name, last_name, email, password, location, dept, is_admin, register_date) values ('Brad', 'Traversy', '[email protected]', '123456','Massachusetts', 'development', 1, now());
Insert multiple rows INSERT INTO users (first_name, last_name, email, password, location, dept, is_admin, register_date) values ('Fred', 'Smith', '[email protected]', '123456', 'New York', 'design', 0, now()), ('Sara', 'Watson', '[email protected]', '123456', 'New York', 'design', 0, now()),('Will', 'Jackson', '[email protected]', '123456', 'Rhode Island', 'development', 1, now()),('Paula', 'Johnson', '[email protected]', '123456', 'Massachusetts', 'sales', 0, now()),('Tom', 'Spears', '[email protected]', '123456', 'Massachusetts', 'sales', 0, now());
Select clause SELECT * FROM users;
SELECT first_name, last_name FROM users;
Where clause SELECT * FROM users WHERE location='Massachusetts';
SELECT * FROM users WHERE location='Massachusetts' AND dept='sales';
SELECT * FROM users WHERE is_admin = 1;
SELECT * FROM users WHERE is_admin > 0;
Delete a row DELETE FROM users WHERE id = 6;
Update a row UPDATE users SET email = '[email protected]' WHERE id = 2;
Order By clause SELECT * FROM users ORDER BY last_name ASC;
SELECT * FROM users ORDER BY last_name DESC;
Concatenate Columns SELECT CONCAT(first_name, ' ', last_name) AS 'Name', dept FROM users;
Select Distinct rows SELECT DISTINCT location FROM users;
Between Clause SELECT * FROM users WHERE age BETWEEN 20 AND 25;
Like(searching in the string) SELECT * FROM users WHERE dept LIKE 'd%';
SELECT * FROM users WHERE dept LIKE 'dev%';
SELECT * FROM users WHERE dept LIKE '%t';
SELECT * FROM users WHERE dept LIKE '%e%';
SELECT * FROM users WHERE dept LIKE '__e%';
Note: here '%' means any number of characters and "_" single character
Not Like SELECT * FROM users WHERE dept NOT LIKE 'd%';
IN SELECT * FROM users WHERE dept IN ('design', 'sales');
Create and Remove Index CREATE INDEX LIndex On users(location);
DROP INDEX LIndex ON users;
New table with Foreign Key CREATE TABLE posts(
id INT AUTO_INCREMENT,
user_id INT,
title VARCHAR(100),
body TEXT,
publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
Inner Join SELECT
users.first_name,
users.last_name,
posts.title,
posts.publish_date
FROM users
INNER JOIN posts
ON users.id = posts.user_id
ORDER BY posts.title;
Left Join(similarly we can use right outer join) SELECT
comments.body,
posts.title
FROM comments
LEFT JOIN posts ON posts.id = comments.post_id
ORDER BY posts.title;

Join on multiple tables SELECT
comments.body,
posts.title,
users.first_name,
users.last_name
FROM comments
INNER JOIN posts on posts.id = comments.post_id
INNER JOIN users on users.id = comments.user_id
ORDER BY posts.title;
Aggregate Functions SELECT COUNT(id) FROM users;
SELECT MAX(age) FROM users;
SELECT MIN(age) FROM users;
SELECT SUM(age) FROM users;
SELECT UCASE(first_name), LCASE(last_name) FROM users;
Group by SELECT age, COUNT(age) FROM users GROUP BY age;
SELECT age, COUNT(age) FROM users WHERE age > 20 GROUP BY age;
SELECT age, COUNT(age) FROM users GROUP BY age HAVING count(age) >=2;
Load a CSV file into a table. LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);  
Dump all databases for backup. The backup file is SQL commands to recreate all db's. [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql  
Dump one database for backup. [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
Dump a table from a database. [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql  
Restore database (or database table) from backup. [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql  

Hope these commands help. Keep Exploring !!

Comments
Read next