MYSQL Cheat Sheet | Radarhot com

$type=ticker$count=12$cols=4$cate=0

MYSQL Cheat Sheet

MYSQL Cheat Sheet   MySQL is a popular relational database management system (RDBMS) used for managing and manipulating databases. B...

Institute It. Training | Kursus Komputer Jakarta Timur | WA. +628978298280 |

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

bash
mysql -u [username] -p
  • Connect to MySQL with a specified username. You will be prompted for the password.
bash
mysql -u [username] -p [database_name]
  • Connect to a specific database.

Basic Commands

sql
SHOW DATABASES;
  • List all databases.
sql
USE [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 - 429496­7295)

LONGBLOB String   (0 - 429496­7295)

TINYINT x Integer (-128 to 127)

SMALLINT x Integer(-32768 to 32767)

MEDIUMINT x Integer (-8388608 to 8388607)

INT x Integer     (-2147­483648 to 214748­3647)

BIGINT x Integer  (-9223­372­036­854­775808 to 922337­203­685­477­5807)

FLOAT Decimal     (precise to 23 digits)

DOUBLE Decimal    (24 to 53 digits)

DECIMAL "­DOU­BLE­" stored as string

DATE              YYYY-MM-DD

DATETIME          YYYY-MM-DD HH:MM:SS

TIMESTAMP         YYYYMM­DDH­HMMSS

TIME              HH:MM:SS

ENUM              One of preset options

SET               Selection of preset options


MySQL Mathem­atical 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_F­ORMAT

QUARTER TIME_F­ORMAT

WEEK CURREN­T_DATE

YEAR CURREN­T_TIME

YEARWEEK NOW

HOUR SYSDATE

MINUTE UNIX_T­IME­STAMP

SECOND FROM_U­NIXTIME

PERIOD_ADD SEC_TO­_TIME

PERIOD­_DIFF TIME_T­O_SEC

DATE_ADD

MySQL Miscel­laneous Functions


BIT_COUNT    DES_EN­CRYPT

DATABASE     DES_DE­CRYPT

USER LAST  _ I­NSE­RT_ID

SYSTEM­_USE   FORMAT

SESSIO­N_USER VERSION

CURREN­T_USER CONNEC­TION_ID

PASSWORD     GET_LOCK

OLD_PA­SSWORD RELEAS­E_LOCK

ENCRYPT      IS_FRE­E_LOCK

DECODE       BENCHMARK

MD5          INET_NTOA

SHA1         INET_ATON

AES_EN­CRYPT  FOUND_ROWS

AES_DE­CRYPT  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

Nama

Aljabar,102,Aritmatika,62,Autocad,17,Bimbel Jakarta Timur,76,Bimbingan Belajar,13,Biologi,49,Corel Draw,10,CPNS,29,Fisika,89,Geometri,147,Ilmu Pengetahuan,62,Info,15,Inspirasi,23,IPA,214,Islami,8,Kalkulus,13,Kimia,70,Kombinatorika,13,Manajemen,10,Matematika,555,Metode,461,Microsoft,7,MYOB,5,Operasi Hitung,54,OSN,54,PAT PAS UAS,35,Pemrograman,51,Pengukuran,12,Photoshop,16,RadarHot Com,23,SEO,10,Soal,312,Software,12,Statistika,24,Teknisi,9,Trigonometri,21,Tutorial,24,Ujian Sekolah,30,video,540,Wirausaha,5,
ltr
item
Radarhot com: MYSQL Cheat Sheet
MYSQL Cheat Sheet
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9f9ugc5vf4Oy6j8PUVjb5IWRhAWBuAzcucsxMsjR0FGRcyeIDEFWdbv7o7dRvJsXv6n-K_ZbFSGuZvUFu0cO3Jt7nTsg4F64_PGUgGF_D5SyI5PnCPqNTJqls65DJqbzVU1WunUKyLxLI/w640-h366/mysql_cheat_sheet1.png
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9f9ugc5vf4Oy6j8PUVjb5IWRhAWBuAzcucsxMsjR0FGRcyeIDEFWdbv7o7dRvJsXv6n-K_ZbFSGuZvUFu0cO3Jt7nTsg4F64_PGUgGF_D5SyI5PnCPqNTJqls65DJqbzVU1WunUKyLxLI/s72-w640-c-h366/mysql_cheat_sheet1.png
Radarhot com
https://www.radarhot.com/2024/07/mysql-cheat-sheet.html
https://www.radarhot.com/
https://www.radarhot.com/
https://www.radarhot.com/2024/07/mysql-cheat-sheet.html
true
7351726107056679100
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content