MYSQL Cheat Sheet MySQL is a popular relational database management system (RDBMS) used for managing and manipulating databases. B...
MYSQL Cheat Sheet |
MySQL is a popular relational database management system (RDBMS) used for managing and manipulating databases. Below is a cheat sheet that covers essential MySQL commands and operations.
Connecting to MySQL
bashmysql -u [username] -p
- Connect to MySQL with a specified username. You will be prompted for the password.
bashmysql -u [username] -p [database_name]
- Connect to a specific database.
Basic Commands
sql SHOW DATABASES;
- List all databases.
sqlUSE [database_name];
- Select a database to use.
sql SHOW TABLES;
- List all tables in the selected database.
sql DESCRIBE [table_name];
- Show the structure of a table.
sql SELECT VERSION();
- Display the MySQL version.
Creating Databases and Tables
sql CREATE DATABASE [database_name];
- Create a new database.
sql DROP DATABASE [database_name];
- Delete a database.
sql CREATE TABLE [table_name] (
column1 datatype,
column2 datatype,
column3 datatype,
...
);
- Create a new table with specified columns and datatypes.
sql DROP TABLE [table_name];
- Delete a table.
Basic CRUD Operations
Insert Data
sql INSERT INTO [table_name] (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
- Insert a new record into a table.
Select Data
sql SELECT column1, column2, column3, ...
FROM [table_name]
WHERE condition;
- Retrieve specific columns from a table based on a condition.
sql SELECT * FROM [table_name];
- Retrieve all columns from a table.
Update Data
sql UPDATE [table_name]
SET column1 = value1, column2 = value2, ...
WHERE condition;
- Update existing records in a table.
Delete Data
sql DELETE FROM [table_name]
WHERE condition;
- Delete records from a table based on a condition.
Advanced Queries
Join
sql SELECT columns
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;
- Combine rows from two or more tables based on a related column.
Inner Join
sql SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;
- Select records that have matching values in both tables.
Left Join
sql SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
- Select all records from the left table and the matched records from the right table.
Right Join
sql SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;
- Select all records from the right table and the matched records from the left table.
Full Join (MySQL doesn't directly support FULL JOIN, use UNION instead)
sql SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;
- Combine results of both left and right joins.
Group By
sql SELECT column, COUNT(*)
FROM [table_name]
GROUP BY column;
- Group rows that have the same values in specified columns into summary rows.
Order By
sql SELECT column1, column2
FROM [table_name]
ORDER BY column1 ASC, column2 DESC;
- Sort the result set in ascending or descending order.
Indexes
Create Index
sql CREATE INDEX [index_name]
ON [table_name] (column1, column2, ...);
- Create an index on a table for faster retrieval.
Drop Index
sql DROP INDEX [index_name] ON [table_name];
- Remove an index from a table.
Constraints
Primary Key
sql ALTER TABLE [table_name]
ADD PRIMARY KEY (column);
- Set a column as the primary key.
Foreign Key
sql ALTER TABLE [table_name]
ADD CONSTRAINT [constraint_name] FOREIGN KEY (column)
REFERENCES [other_table] (other_column);
- Set a column as a foreign key.
Unique Key
sql ALTER TABLE [table_name]
ADD UNIQUE (column);
- Ensure all values in a column are unique.
Transactions
Start Transaction
sql START TRANSACTION;
- Begin a new transaction.
Commit
sql COMMIT;
- Save the changes made in the current transaction.
Rollback
sql ROLLBACK;
- Undo changes made in the current transaction.
User Management
Create User
sql CREATE USER '[username]'@'localhost' IDENTIFIED BY '[password]';
- Create a new MySQL user.
Grant Privileges
sql GRANT ALL PRIVILEGES ON [database_name].* TO '[username]'@'localhost';
- Grant privileges to a user.
Revoke Privileges
sql REVOKE ALL PRIVILEGES ON [database_name].* FROM '[username]'@'localhost';
- Revoke privileges from a user.
Delete User
sql DROP USER '[username]'@'localhost';
- Delete a MySQL user.
Conclusion
This cheat sheet provides a quick reference to essential MySQL commands and operations. Whether you are managing databases, performing CRUD operations, or optimizing queries, these commands will help you work more efficiently with MySQL.
from
https://intitute.blogspot.com/2018/10/mysql-cheat-sheet.html
MySQL Data Types
CHAR String (0 - 255) VARCHAR String (0 - 255) TINYTEXT String (0 - 255) TEXT String (0 - 65535) BLOB String (0 - 65535) MEDIUMTEXT String (0 - 16777215) MEDIUMBLOB String (0 - 16777215) LONGTEXT String (0 - 4294967295) LONGBLOB String (0 - 4294967295) TINYINT x Integer (-128 to 127) SMALLINT x Integer(-32768 to 32767) MEDIUMINT x Integer (-8388608 to 8388607) INT x Integer (-2147483648 to 2147483647) BIGINT x Integer (-9223372036854775808 to 9223372036854775807) FLOAT Decimal (precise to 23 digits) DOUBLE Decimal (24 to 53 digits) DECIMAL "DOUBLE" stored as string DATE YYYY-MM-DD DATETIME YYYY-MM-DD HH:MM:SS TIMESTAMP YYYYMMDDHHMMSS TIME HH:MM:SS ENUM One of preset options SET Selection of preset options
MySQL Mathematical Functions
ABS COS SIGN SIN MOD TAN FLOOR ACOS CEILING ASIN ROUND ATAN, ATAN2 DIV COT EXP RAND LN LEAST LOG, LOG2, LOG10 GREATEST POW DEGREES POWER RADIANS SQRT TRUNCATE PI
MySQL Date and Time Functions
DAYOFWEEK DATE_SUB WEEKDAY ADDDATE DAYOFMONTH SUBDATE DAYOFYEAR EXTRACT MONTH TO_DAYS DAYNAME FROM_DAYS MONTHNAME DATE_FORMAT QUARTER TIME_FORMAT WEEK CURRENT_DATE YEAR CURRENT_TIME YEARWEEK NOW HOUR SYSDATE MINUTE UNIX_TIMESTAMP SECOND FROM_UNIXTIME PERIOD_ADD SEC_TO_TIME PERIOD_DIFF TIME_TO_SEC DATE_ADD
BIT_COUNT DES_ENCRYPT DATABASE DES_DECRYPT USER LAST _ INSERT_ID SYSTEM_USE FORMAT SESSION_USER VERSION CURRENT_USER CONNECTION_ID PASSWORD GET_LOCK OLD_PASSWORD RELEASE_LOCK ENCRYPT IS_FREE_LOCK DECODE BENCHMARK MD5 INET_NTOA SHA1 INET_ATON AES_ENCRYPT FOUND_ROWS AES_DECRYPT STRCMP
MySQL String Functions
ASCII SUBSTRING
ORD MID
CONV SUBSTRING_INDEX
BIN LTRIM
OCT RTRIM
HEX TRIM
CHAR SOUNDEX
CONCAT SPACE
CONCAT_WS REPLACE
LENGTH REPEAT
CHAR_LENGTH REVERSE
BIT_LENGTH INSERT
LOCATE ELT
INSTR FIELD
LPAD LCASE
RPAD UCASE
LEFT LOAD _FILE
RIGHT QUOTE
MySQL Grouping Functions
AVG MAX BIT_AND STD BIT_OR STDDEV COUNT SUM GROUP_CONCAT VARIANCE MIN
COMMENTS