MySQL Commands Cheat Sheet for Windows: Basic Usage

Posted on Mar 1, 2025 in Technology

MySQL Commands Cheat Sheet for Windows

This cheat sheet provides a quick reference to basic MySQL commands for Windows users.

Creating Databases

Command: create database foo;

Showing MySQL Databases

Command: show databases;

Connecting to MySQL Database (Method 1)

Note: Navigate to your MySQL installation directory, then to the /bin directory.

Command: mysql -u usernamehere -p

Connecting to MySQL Database (Easiest Method)

Command: In Windows, type “MySQL” in the search bar and click on the “MySQL v x.x Command Line Client”. Enter your password when prompted.

Using a Database

Command: use foo;

Creating Tables

Command: create table foo_data ( f_name VARCHAR(20), l_name VARCHAR(20), title VARCHAR(20), age INT );

Showing Tables

Command: show tables;

Inserting Data into a Table

Command: insert into foo_data (f_name, l_name, birth) values ("pwn", "sauce", 1988);

Querying a Table for Information

Command: select l_name from foo_data;

Enumerating Table Columns

Note: DESCRIBE lists all column names and their data types.

Command: describe foo_data;

Importing MySQL Data from a .dat File

Note: Open cmd.exe (Command Prompt) and navigate to your MySQL /bin directory.

Command: mysql -u usernamehere -p passwordhere < data.dat

Displaying the Entire Table

Command: select * from foo_data;

Comparison Operators

=, >, <, >=, <=, !=

Command: select l_name from foo_data where f_name = 'sinful';

Command: select l_name from foo_data where age > 15;

Command: select l_name from foo_data where age < 25;

Command: select l_name from foo_data where age <= 20;

Command: select l_name from foo_data where age >= 30;

Pattern Matching (Words)

Command: select f_name from foo_data where f_name like "%sinful%";

Pattern Matching (Beginning Letters)

Command: select f_name from foo_data where f_name like 's%';

Pattern Matching (Ending Letters)

Command: select f_name from foo_data where f_name like '%s';

Logical Operators

AND, OR, NOT

Command: select l_name from foo_data where age > 32 AND salary = 32000;

Command: select l_name from foo_data where age > 32 AND title NOT LIKE "%haxer%";

Command: select l_name from foo_data where age > 32 OR age < 24;

IN, NOT IN, BETWEEN

Command: select l_name from foo_data where title IN ('programmer', 'hacker');

Command: select l_name from foo_data where title NOT IN ('programmer', 'hacker');

Command: select l_name from foo_data where age BETWEEN 27 AND 30;

Order By (ASC – Default)

Command: select f_name, l_name from foo_data order by salary;

Order By (DESC)

Command: select f_name, l_name from foo_data order by salary DESC;

Limit

Command: select f_name, l_name from foo_data limit 5;

Extracting Subsets

Command: select f_name, l_name from foo_data limit 10, 5;

Distinct

Command: select distinct title from foo_data;

Minimum

Command: select min(age) from foo_data;

Maximum

Command: select max(age) from foo_data;

SUM

Command: select sum(age) from foo_data;

AVG

Command: select avg(age) from foo_data;

Adding, Subtracting, Multiplying, Dividing

Command: select sum(salary) + sum(perks) from foo_data;

Command: select sum(salary) - sum(perks) from foo_data;

Command: select sum(salary) * sum(perks) from foo_data;

Command: select sum(salary) / sum(perks) from foo_data;

Naming Columns

Command: select sum(salary) AS 'total salary' from foo_data;

Count

Command: select count(*) from foo_data;

Group By

Command: select f_name, l_name, title from foo_data group by title;

SQL Version

Command: select version();

Now

Command: select now();

Displaying Day, Month, & Year

Command: select dayofmonth(current_date());

Command: select month(current_date());

Command: select year(current_date());

Displaying Text Strings

Command: select 'I hack things';

Concatenation

Command: select concat(f_name, " ", l_name) from foo_data;

Update

Command: update foo_data set salary=69000, perks=40000 where title = 'hacker';