mysql Cheat sheet

Connect: mysql -u [username] -p -h [hostname] -P [port_number] [db_name]

Exit: exit;

FROM > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT

TINYINT

SMALLINT

MEDIUMINT

INT

BIGINT

FLOAT

DOUBLE

DECIMAL(M,D)

CHAR(M)

VARCHAR(M)

TEXT(M)

BLOB

DATE

TIME

DATETIME

YEAR

TIMESTAMP

BOOLEAN

ENUM('value1', 'value2', ...)

SET('option1', 'option2', ...)

"M" refers to the total number of digits you want to store

"N" is the number of digits to be stored after the decimal point

CREATE DATABASE database_name [CHARACTER SET utf8];

SHOW DATABASES;

USE database_name;

DROP DATABASE database_name;

ALTER DATABASE database_name CHARACTER SET utf8;

CREATE TABLE [IF NOT EXISTS] table_name (column1 datatype COMMENT 'string', ...);

CREATE TEMPORARY TABLE table;

SHOW TABLES;

DESCRIBE table_name;

DROP TABLE [IF EXISTS] table_name;

ALTER TABLE table RENAME TO new_table_name

ALTER TABLE table RENAME col1 TO col2

ALTER TABLE table ADD column_name datatype;

ALTER TABLE table ADD column_name datatype FIRST;

ALTER TABLE table ADD column_name datatype AFTER another_field;

ALTER TABLE table DROP column_name;

ALTER TABLE table CHANGE field1 new_field_name datatype FIRST

ALTER TABLE table CHANGE field1 new_field_name datatype NOT NULL ..

ALTER TABLE table ALTER field1 SET DEFAULT ...

ALTER TABLE table ALTER field1 DROP DEFAULT;

ALTER TABLE table MODIFY field1 datatype NOT NULL ...

ALTER TABLE table MODIFY field1 type1 FIRST;

ALTER TABLE table MODIFY field1 type1 AFTER another_field;

CREATE TABLE table_name ( column1 datatype constraint, ...);

CREATE TABLE table_name ( column_name datatype [ UNSIGNED ] [ NULL | NOT NULL ] [DEFAULT default_value] [AUTO_INCREMENT], ...);

CREATE TABLE table_name ( column_name INT AUTO_INCREMENT, ..., PRIMARY KEY (column_name) );

CREATE TABLE table_name ( column_name INT, FOREIGN KEY (column_name) REFERENCES other_table(other_column) );

CREATE TABLE table_name ( column1 INT, column2 INT, UNIQUE KEY `key_name` (column1, column2) );

CREATE TABLE table_name ( column_name INT, CHECK (column_name > 10) );

CREATE TABLE table_name ( City varchar(255) DEFAULT 'Danang' );

CREATE TABLE table_name ( column_name datatype UNSIGNED, );

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value1, value2, ...);

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

DELETE FROM table_name;

DELETE FROM table_name WHERE condition;

DELETE FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition

TRUNCATE table_name;

SELECT * FROM table_name;

SELECT column_name AS alias_name FROM table_name;

SELECT c.OrderID FROM Customers AS c;

SELECT * FROM table1, table2;

SELECT column1, column2, ... FROM table_name;

SELECT column1, column2, ... FROM table_name WHERE condition;

SELECT ... FROM ... WHERE condition ORDER BY column1, column2;

SELECT ... FROM ... WHERE condition ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

SELECT ... FROM ... WHERE condition LIMIT 10;

SELECT ... FROM ... WHERE condition LIMIT [offset,] row_count;

SELECT ... FROM ... WHERE condition LIMIT row_count OFFSET offset

SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column AND table1.column2 = table2.column2;

SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

SELECT c1, c2 FROM table1 UNION[ALL] SELECT c1, c2 FROM t2;

WHERE column_name = value;

WHERE column_name <> value;

WHERE column_name != value;

WHERE column_name > value;

WHERE column_name < value;

WHERE column_name >= value;

WHERE column_name <= value;

WHERE column_name BETWEEN value1 AND value2;

WHERE column_name IN (value1, value2, ...);

WHERE column_name NOT IN (value1, value2, ...);

WHERE column_name IS NULL;

WHERE column_name IS NOT NULL;

WHERE column_name LIKE 'd%';

WHERE column_name LIKE '%d%';

WHERE column_name NOT LIKE 'd%';

WHERE column_name1 = value AND column_name2 = value;

WHERE column_name1 = value OR column_name2 = value;

SELECT COUNT(id) FROM table_name;

SELECT MAX(age) FROM table_name;

SELECT MIN(age) FROM table_name;

SELECT SUM(age) FROM table_name;

SELECT AVG(age) FROM table_name;

SELECT UCASE(first_name), LCASE(first_name) FROM table_name;

SELECT DISTINCT field1 FROM ...;

SELECT DISTINCT field1, field2 FROM;

SELECT CONCAT(first_name, ' ', last_name) AS 'Name', dept FROM users;

SELECT DISTINCT location FROM users;

SELECT NOW();

SELECT CURDATE();

SELECT CURTIME();

SELECT YEAR(NOW());

SELECT TIMESTAMPDIFF(MINUTE, '2023-01-01 12:00:00', NOW());

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

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;

SELECT * FROM table1 WHERE column_name IN (SELECT column_name FROM table2 WHERE condition);

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

CREATE INDEX index_name ON table_name (column_name);

CREATE TABLE t1 (col1 VARCHAR(10), col2 VARCHAR(20), INDEX index_name (col1, col2)

CREATE TABLE t1 (col1 VARCHAR(10), col2 VARCHAR(20), INDEX index_name (col1, col2(10))

CREATE INDEX index_name ON table_name (column1, column2, ...);

CREATE UNIQUE INDEX index_name ON table_name (column_name);

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

DROP INDEX index_name ON table_name;

SELECT * FROM table_name FOR UPDATE;

SELECT * FROM table_name FOR SHARE;

LOCK TABLES table_name READ;

LOCK TABLES table_name WRITE;

UNLOCK TABLES;

START TRANSACTION;

COMMIT;

ROLLBACK;

SET AUTOCOMMIT = 1;

mysqldump -u Username -p dbNameYouWant > databasename_backup.sql

mysql -u Username -p dbNameYouWant < databasename_backup.sql;

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

CREATE USER 'user'@'%';

SELECT * FROM mysql.user;

SELECT CURRENT_USER();

DROP USER 'user'@'host';

ALTER USER 'user'@'hostname' IDENTIFIED BY 'newPass';

UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE USER='user-name-here' AND Host='host-name-here';

GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';

SHOW GRANTS FOR 'someuser'@'localhost';

GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';

REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions

FLUSH PRIVILEGES;

SHOW PROCESSLIST;

show status where `variable_name` = 'Threads_connected';

show variables like 'max_connections';

SET GLOBAL max_connections = 150;