https://www.cheatography.com/guslong/cheat-sheets/essential-mysql/
http://cse.unl.edu/~sscott/ShowFiles/SQL/CheatSheet/SQLCheatSheet.html
http://www.mysqltutorial.org/mysql-cheat-sheet.aspx
https://gist.github.com/hofmannsven/9164408
http://williamqliu.github.io/2015/04/10/sql.html
https://support.rackspace.com/how-to/installing-mysql-server-on-centos/
MySQL Data Types
Integers (marked x) that are "UNSIGNED" have the same range of values but start from 0 (i.e., an UNSIGNED TINYINT can have any value from 0 to 255).
Select queries
| Creating and modifying
Matching data
| Joins
JOIN syntax:
SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.id; String functions mySQL
MySQL calculation functions
Create table with auto-incrementing primary key
|
The following statement imports data from the
c:\tmp\discounts.csv file into the discountstable.
The field of the file is terminated by a comma indicated by
FIELD TERMINATED BY ',' and enclosed by double quotation marks specified by ENCLOSED BY '" ‘.
Each line of the CSV file is terminated by a newline character indicated by
LINES TERMINATED BY '\n' .
Because the file has the first line that contains the column headings, which should not be imported into the table, therefore we ignore it by specifying
IGNORE 1 ROWS option.
Access monitor:
mysql -u [username] -p; (will prompt for password)
Show all databases:
show databases;
Access database:
mysql -u [username] -p [database] (will prompt for password)
Create new database:
create database [database];
Select database:
use [database];
Show all tables:
show tables;
Show table structure:
describe [table];
Create new table with columns:
CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);
Adding a column:
ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);
Adding a column with an unique, auto-incrementing ID:
ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;
Inserting a record:
INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');
MySQL function for datetime input:
NOW()
Selecting records:
SELECT * FROM [table];
Explain records:
EXPLAIN SELECT * FROM [table];
Selecting parts of records:
SELECT [column], [another-column] FROM [table];
Counting records:
SELECT COUNT([column]) FROM [table];
Counting and selecting grouped records:
SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];
Selecting specific records:
SELECT * FROM [table] WHERE [column] = [value]; (Selectors: <, >, !=; combine multiple selectors with AND, OR)
Select records containing
[value]: SELECT * FROM [table] WHERE [column] LIKE '%[value]%';
Select records starting with
[value]: SELECT * FROM [table] WHERE [column] LIKE '[value]%';
Select records starting with
val and ending with ue: SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';
Select a range:
SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];
Select with custom order and only limit:
SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value];(Order: DESC, ASC)
Updating records:
UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];
Deleting records:
DELETE FROM [table] WHERE [column] = [value];
Delete all records from a table (without dropping the table itself):
DELETE FROM [table]; (This also resets the incrementing counter for auto generated columns like an id column.)
Removing table columns:
ALTER TABLE [table] DROP COLUMN [column];
Deleting tables:
DROP TABLE [table];
Deleting databases:
DROP DATABASE [database];
Custom column output names:
SELECT [column] AS [custom-column] FROM [table];
Export a database dump (more info here):
mysqldump -u [username] -p [database] > db_backup.sql
Use
--lock-tables=false option for locked tables (more info here).
Import a database dump (more info here):
mysql -u [username] -p -h localhost [database] < db_backup.sql
Logout:
exit;Aggregate functions
Select but without duplicates:
SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 2015-01-01 00:00:00
Calculate total number of records:
SELECT SUM([column]) FROM [table];
Count total number of
[column] and group by [category-column]: SELECT [category-column], SUM([column]) FROM [table] GROUP BY [category-column];
Get largest value in
[column]: SELECT MAX([column]) FROM [table];
Get smallest value:
SELECT MIN([column]) FROM [table];
Get average value:
SELECT AVG([column]) FROM [table];
Get rounded average value and group by
[category-column]: SELECT [category-column], ROUND(AVG([column]), 2) FROM [table] GROUP BY [category-column];Multiple tables
Select from multiple tables:
SELECT [table1].[column], [table1].[another-column], [table2].[column] FROM [table1], [table2];
Combine rows from different tables:
SELECT * FROM [table1] INNER JOIN [table2] ON [table1].[column] = [table2].[column];
Combine rows from different tables but do not require the join condition:
SELECT * FROM [table1] LEFT OUTER JOIN [table2] ON [table1].[column] = [table2].[column]; (The left table is the first table that appears in the statement.)
Rename column or table using an alias:
SELECT [table1].[column] AS '[value]', [table2].[column] AS '[value]' FROM [table1], [table2];Users functions
List all users:
SELECT User,Host FROM mysql.user;
Create new user:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
Grant
ALL access to user for * tables: GRANT ALL ON database.* TO 'user'@'localhost';Find out the IP Address of the Mysql Host
SHOW VARIABLES WHERE Variable_name = 'hostname'; (source)MySQL Command From a Bash Shell in One Line
Use the following command for quickly execution of MySQL query from a Linux Bash Shell :
# mysql -u [user] -p[pass] -e "[mysql commands]"
Example :
# mysql -u root -pSeCrEt -e "show databases"
Example :
Run a MySQL Query From a Bash script using EOF
Use the following syntax in your Bash scripts for running MySQL commands :
mysql -u [user] -p[pass] << EOF
[mysql commands]
EOF
[mysql commands]
EOF
Example :
#!/bin/bash mysql -u root -pSeCrEt << EOF use mysql; show tables; EOF
Execute a MySQL Command Remotely
Use -h option to specify a MySQL server's IP address:
# mysql -h [ip] -u [user] -p[pass] -e "[mysql commands]"
Example :
# mysql -h 192.168.1.10 -u root -pSeCrEt -e "show databases"
Example :
Specify a Database to Use
Use -D option to specify the name of MySQL database :
# mysql -D [db name] -u [user] -p[pass] -e "[mysql commands]"
Example :
# mysql -D clients -u root -pSeCrEt -e "show tables"
Example :
sudo mysqldump -u <local_user> \ --databases world \ --single-transaction \ --compress \ --order-by-primary \ -p <local_password> | mysql -u <RDS_user_name> \ --port=3306 \ --host=hostname \ -p <RDS_password>
#!/usr/bin/env bash FILE_NAME="example_db-`date +%Y%m%d%H%M`.sql.gz" SAVE_DIR="/backup" S3BUCKET="mysqlbackupbucket" # Get MYSQL_USER and MYSQL_PASSWORD
# export MYSQL_USER=dbuser; export MYSQL_PASSWORD=t0ps3r3!
source /home/backup/.env
mysqldump -u ${MYSQL_USER} -p${MYSQL_PASSWORD} example_db | gzip > ${SAVE_DIR}/${FILE_NAME}
if [ -e ${SAVE_DIR}/${FILE_NAME} ]; then
# Upload to AWS
aws s3 cp ${SAVE_DIR}/${FILE_NAME} s3://${S3BUCKET}/${FILE_NAME}
# Test result of last command run
if [ "$?" -ne "0" ]; then
echo "Upload to AWS failed"
exit 1
fi
# If success, remove backup file
rm ${SAVE_DIR}/${FILE_NAME}
# Exit with no error
exit 0
fi
# Exit with error if we reach this point
echo "Backup file not created"
exit 1
http://sqlfiddle.com/#!9/f20c2/3
SELECT word, SUM(amount)
FROM Data
Group By word (with rollup)
(Saving)
mysqldump -ume -pblah myDB table1
table2 > my.sql
timestamp(12) (for YYYYMMDDHHMMSS)
SHOW COLUMNS FROM table;
SHOW COLUMNS FROM Animals;
column1 type [[NOT] NULL]
[AUTO_INCREMENT],
column2 type [[NOT] NULL]
[AUTO_INCREMENT],
...
other options,
PRIMARY KEY (column(s)) );
LastName varchar(30) NOT NULL,
FirstName varchar(30) NOT NULL,
StudentID int NOT NULL,
Major varchar(20),
Dorm varchar(20),
PRIMARY KEY (StudentID) );
(list of values);
INSERT INTO table SET
column1=value1,
column2=value2,
...
columnk=valuek;
INSERT INTO table (column1,column2,...)
VALUES (value1,value2...);
('Smith','John',123456789,'Math','Selleck');
INSERT INTO Students SET
FirstName='John',
LastName='Smith',
StudentID=123456789,
Major='Math';
INSERT INTO Students
(StudentID,FirstName,LastName)
VALUES (123456789,'John','Smith');
SELECT statement;
(See below)
(StudentID,FirstName,LastName)
SELECT StudentID,FirstName,LastName
FROM OtherStudentTable;
WHERE LastName like '%son';
[WHERE condition(s)];
(Omit WHERE to delete all data)
WHERE LastName='Smith';
DELETE FROM Students
WHERE LastName like '%Smith%';
AND FirstName='John';
DELETE FROM Students;
column1=value1,
column2=value2,
...
columnk=valuek
[WHERE condition(s)];
LastName='Jones' WHERE
StudentID=987654321;
UPDATE Students SET
LastName='Jones', Major='Theatre'
WHERE StudentID=987654321 OR
(MAJOR='Art' AND FirstName='Pete');
column type options;
Hometown varchar(20);
DROP COLUMN column;
DROP COLUMN Dorm;
columns
FROM table
[WHERE condition(s)];
FROM Students
WHERE StudentID LIKE '%123%';
FROM table;
FROM Students;
FROM table
[ORDER BY column(s) [DESC]];
FROM Students
ORDER BY LastName, FirstName DESC;
Aliases
column2 [AS alias2], ...
FROM table1;
FROM Students;
FROM table
[GROUP BY column(s)];
FROM Students
GROUP BY LastName;
FROM table
[GROUP BY column(s)]
[HAVING condition(s)];
FROM Students
GROUP BY LastName
HAVING LastName like '%son';
FROM table1,table2,...
[WHERE condition(s)];
FROM Students,Assignments
WHERE AssignmentID=12 AND
Students.StudentID=Assignments.StudentID;
Aliases
FROM table1 [alias1],
table2 [alias2],...
[WHERE condition(s)];
FROM Students S,Assignments A
WHERE S.StudentID=A.StudentID AND
A.AssignmentID=12;
column1 [AS alias1],
column2 [AS alias2], ...
FROM table1 [alias1],
table2 [alias2],...
[WHERE condition(s)]
[GROUP BY column(s)]
[HAVING condition(s)]
[ORDER BY column(s) [DESC]];
FROM Students S,Assignments A
WHERE S.StudentID=A.StudentID AND
A.AssignmentID=12
GROUP BY Points
HAVING Points > 10
ORDER BY Cnt, Points DESC;
MySQL Commands
Working with Database
Working with Table
Altering table structure
Working with Index
Querying Data
ORDER BYclause.Modifying Data
Search
RLIKEoperator.