| 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 !!
