Install MySQL in Docker Way
Pull from Docker Official Images
docker pull mysql:latest
docker run -itd --name MySQL-Name -p 3306:3306 -e MYSQL_ROOT_PASSWORD=YourPassword mysql
If the installation was successful, you will see mysql running in a container.
|USE db_name||Choose this database to work at.|
|SHOW DATABASES||Show all databases on this server.|
|SHOW TABLES||Show all tables in the current database.|
|SHOW COLUMNS FROM table_name; DESCRIBE table_name||Show information about a table's columns.|
|SHOW STATUS||Show server state represented as a format like |
|SHOW CREATE DATABASE db_name; SHOW CREATE TABLE table_name;||Show scripts creating the database/table specified.|
|SHOW GRANTS||Show privileges of the current user.|
|SHOW ERRORS/WARNINGS||Show server errors/warning information.|
DISTINCT col_name_1, col_name_2, ...: Applies to all columns, not only to the column before this keyword.
LIMIT : Return 1 or n rows.
LIMIT n: Return n rows
LIMIT m, n: Return n rows start from the m+1 th row, i.e., (m, m + n].
LIMIT n OFFSET m: The same effect as
LIMIT m, n.
ORDER BY col_name_1, col_name_2, ...
- The result is ordered by
col_name_2if and only if the value of
- It should be placed after the
WHEREclause and before the
DESC: Descending order
ASC: Ascending order (default)
BETWEEN start AND end : Match all values in
[start, end] .
IS NULL : Check
WHERE col_name IS NULL
- The database will not return rows whose field contains
NULLvalue, even when filtering at other columns' conditions.
OR : Connect multiple
WHERE clauses' conditions.
- CAUTION : The priority of
OR. (Just like any programming language.)
()properly to avoid exceptions casing by priority issues.
IN : A better form of
OR , which let us create faster, more readable and dynamical queries.
WHERE col_name IN (value_set)
value_set: A set of values to match, delimited by
EXISTS(query) : To determine if a sub query has results.
true: 1 or more rows.
false: no result row found.
NOT : To invert all conditions after this operator.
NOT EXISTS(...)/ ...
Search Pattern = Character(s) + Wildcard(s)
SELECT ... FROM ... WHERE col_name LIKE 'Search Pattern'
|Wildcards / Patterns||Description|
|%||Zero, one, or multiple characters|
|_||A single character|
|word%||Search values start at 'word'|
|%word%||Search values contain 'word'|
|a%b||Search any values that start with 'a' and ends with 'b'|
Do not put the wildcard at the beginning of a search pattern (if not necessary) or overuse wildcards because it will slow down the searching.
By default, MySQL is not case-sensitive when matching a string.
Noticed that the regular expression that MySQL supports is a subset.
WHERE col_name REGEXP 'Regular Expression'
WHERE col_name REGEXP BINARY 'Regular Expression': Makes the search case-sensitive.
|.||Matches any single character except |
|*||Matches by using the regex before |
|+||Matches by using the regex before |
|?||Matches by using the regex before |
|str_A|str_B||Matches str_A, str_B or both. Noticed that there is no space between str and |.|
|[...]||Matches any of the characters listed in brackets.|
|[^...]||Excludes any character that occurs after the |
|-||Defines a range, e.g. |
Escaping Special Character: Add a prefix
Additionally, there is a built-in character set called
character class that includes some useful regular expressions.
|[:alnum:]||Any letter or digit, (same as [a-zA-Z0-9])|
|[:alpha:]||Any letter (same as [a-zA-Z])|
|[:blank:]||Space or tab (same as [\t])|
|[:cntrl:]||ASCII control characters (ASCII 0 through 31 and 127)|
|[:digit:]||Any digit (same as [0-9])|
|[:lower:]||Any lowercase letter (same as [a-z])|
|[:upper:]||Any uppercase letter (same as [A-Z])|
|[:space:]||Any whitespace character including the space (same as |
To start a search at a specific place of a text, use anchor metacharacters.
|^text||Match at the start of the text.|
|text$||Match at the end of the text.|
|^$||Match a |
Test a Regex
SELECT 'TEST 123' REGEXP 'YOUR REGEX' AS is_match
Functions are less portable than standard SQL statements, so comments and documentation are required when you use them.
Set an alias for calculated columns:
SELECT FUNCTION(args) AS alias_col ...
|Left()||Returns characters from the left of the string|
|Length()||Returns the length of a string|
|Locate()||Finds a substring within a string|
|Lower()||Converts the string to lowercase|
|LTrim()||Trims white space from the left of a string|
|Right()||Returns characters from the right of the string|
|RTrim()||Trims white space from right of the string|
|SubString()||Returns characters from within a string|
|Upper()||Converts a string to uppercase|
Concatenation and Trimming
CONCAT(col_name, 'str1', ...)
RTRIM(col_name), LTRIM(col_name), TRIM(col_name)
RTRIM(' str '), LTRIM(' str '), TRIM(' str ')
Date and Time Manipulation Functions
|AddDate()||Add to a date (days, weeks, etc.)|
|AddTime()||Add to a time (hours, minutes, etc.)|
|CurDate()||Returns the current date|
|CurTime()||Returns the current time|
|Date()||Returns the date portion of a date time|
|DateDiff()||Calculates the difference between two dates|
|Date_Add()||Highly flexible date arithmetic function|
|Date_Format()||Returns a formatted date or time string|
|Day()||Returns the day portion of a date|
|DayOfWeek()||Returns the day of week for a date|
|Hour()||Returns the hour portion of a time|
|Minute()||Returns the minute portion of a time|
|Month()||Returns the month portion of a date|
|Now()||Returns the current date and time|
|Second()||Returns the second portion of a time|
|Time()||Returns the time portion of a date time|
|Year()||Returns the year portion of a date|
The date type must be formatted as
'yyyy-MM-dd' thus eliminating ambiguity.
To manipulate with the date, use
Date()function extracts the date part from its argument.
WHERE Date(col_name) = 'yyyy-MM-dd'
- Likewise, so does
SQL Aggregate Functions
To summarize data without retrieving all of them, use aggregate functions.
|AVG()||Returns a column’s average value|
|COUNT()||Returns the number of rows in a column|
|MAX()||Returns a column’s highest value|
|MIN()||Returns a column’s lowest value|
|SUM()||Returns the sum of a column’s values|
NULL rows, and it only applies to a single field.
NULL rows, and they accept numeric and date type as the argument.
The main usage of
Count() is to count the number of rows in a table/column no matter if there exists a
DISTINCT may only be used with
COUNT() if a column name is specified.
DISTINCT may not be used with
When specifying alias names contain the results of an aggregate function, try not to use the name of an actual column in the table.
You cannot use aggregate functions in
GROUP BY clause instructs MySQL to group the data and then perform the aggregate on each group rather than on the entire result set.
Test a function
- You can build nested groups using
- All the columns specified are evaluated together when grouping is established.
- Aside from aggregate calculation statements, each column in your
SELECTstatement should be in the
- If the grouping column contains a row with a
NULLwill be returned as a group. If there are multiple rows with
NULLvalues, they’ll all be grouped together.
GROUP BYclause must come after any
WHEREclause and before any
GROUP BY col_name WITH ROLLUP: Obtain values at each group and at a summary level (for each group).
HAVING supports all of
GROUP BY is required if using columns (or expressions) with aggregate functions.
The difference between
WHEREfilters data not qualified for groups. Thus rows eliminated by a
WHEREclause are not included in a group thus taking effects on the result of gouping.
HAVINGfilters specific groups using some condition after
Subqueries are always processed inside out.
When using a subquery, make sure columns SELECTED in subquery are the same as their counterparts in the parent WHERE clause.
Subqueries are usually conjuncted with
IN, they can also be used to test for equality (using
=), non-equality (using
Test the queries from the innermost to the main query respectively when debugging.
Do not use too much subquery.
Give alias to your subqueries and columns to avoid the unexpected Cartesian Product.
Just remember the picture below.
Joins that both two tables to be joined are the same table (often used to replace subqueries).
Inner Join (Equijoin)
Returns records that have matching values in both tables.
SELECT <select_list> FROM table_a AS A INNER JOIN table_b AS b ON A.KEY = B.KEY
- No need to specify the column names, i.e., always
SELECT *when you use a natural join.
- Natural join is a subset of inner join, and removes duplicate results from equijoin.
SELECT * FROM table_a NATURAL JOIN table_b;
LEFT / RIGHT (OUTER) JOIN
Returns all records from the left / right table, and the matched records from the right / left table.
SELECT <select_list> FROM table_a as A LEFT JOIN table_b as B ON A.Key = B.Key
Left / Right Excluding JOIN
This query will return all of the records in the left / right table that do not match any records in the right / left table.
SELECT <select_list> FROM table_a as A LEFT JOIN table_b as B ON A.Key = B.Key WHERE B.Key is NULL
Two scenarios in which you’d use
To return similarly structured data from different tables in a single query.
To perform multiple queries against a single table returning the data as one query.
Query_1 UNION Query_2 UNION ...
SELECT statement with multiple
WHERE clauses can be specified as a combined query.
- Each query in a
UNIONmust contain the same columns, expressions, or aggregate functions (although columns need not be listed in the same order).
- Column datatypes must be compatible: They need not be the exact same type, but they must be of a type that MySQL can implicitly convert (for example, different numeric types or different date types).
UNION automatically removes any duplicate rows from the query result set. If you would like to keep the same rows, consider using
When combining queries with a
UNION, only one
ORDER BY clause may be used, and it must occur after the final
Creating and Manipulating Tables
# Create a Table CREATE TABLE table_name [IF NOT EXISTS] ( col_name_1 datatype NOT NULL AUTO_INCREMENT, col_name_2 ..., ..., PRIMARY KEY (col_name_1) )ENGINE=InnoDB # or ENGINE=MyISAM
MyISAM: full-text searching supported
- Engines types can be mixed across tables only if no foreign key exists.
#table_name / ..., ...; # table_name CONSTRAINT fk_name (col_name_1) table_name_2 (col_name_2);
Complex table structure changes usually require a manual move process involving these steps:
- Create a new table with the new column layout.
- Use the
INSERT SELECTstatement to copy the data from the old table to the new table. Use conversion functions and calculated fields, if needed.
- Verify that the new table contains the desired data.
- Rename the old table.
- Rename the new table with the name previously used by the old table.
- Re-create any triggers, stored procedures, indexes, and foreign keys as needed.
RENAME current_name TO table_name, current_name_2 TO ...;
- To insert a single complete row
- To insert a single partial row
- To insert multiple rows
- To insert the results of a query
INSERT INTO table (col_name_1, ...) VALUES (...);
Specifies column names, and still works in the case that the structure of table has changed.
INSERT INTO table (col_name_1, ...) VALUES (...), (...), (...);
Insert multiple rows at a time, as long as the order of your columns is identical. This technique can improve the performance of your database processing, as MySQL will process multiple insertions in a single
INSERT faster than performing multiple
INSERT statements at a time.
INSERT INTO table (col_name_1, ...) SELECT ... FROM ...
Insert results from a
SELECT statement. The column position from
SELECT decides the order of
INSERT statement to populate data. i.e. The first column in the
SELECT (regardless of its name) will be used to insert into the first specified table column, and so on. This is useful when importing data from tables that use different column names.
You can instruct MySQL to lower the priority of your
INSERT statement by adding the keyword
LOW_PRIORITY in between
INTO. Likewise, this also applies to the
INSERT LOW_PRIORITY INTO table (col_name_1, ...) VALUES (...);
Update a column
UPDATE table_name SET col_name = ... WHERE ...
Update multiple columns
UPDATE table_name SET col_name = ..., SET col_name_2 = ..., ... WHERE ...
By default, when the update goes wrong, the entire operation will be canceled unless using the
Delete some column's value: Update a column's value as
DELETE is to remove rows from a table. Even all the rows are gone, the table still exists.
DELETE FROM table_name WHERE ...
Delete all rows of a table. This operation drops a table and then recreates the table.
Guidelines for Updating and Deleting Data
- Never execute an
WHEREclause unless you do intend to update and delete every row.
DELETEthus not affecting the unrelated rows.
- Use database enforced referential integrity.
Views are virtual tables that contain queries that dynamically retrieve data when used.
The reasons we use views:
- To reuse and simplify complex SQL operations.
- To access specific subsets of tables.
- To change data formatting and representation. Views can return data formatted and presented differently from their underlying tables.
You can perform
SELECT operations, filter and sort data, join views to other views or tables, and possibly even add and update data.
Because views contain no data, any retrieval needed to execute a query must be processed every time the view is used. Thus nested views / complex query statements may degrade the performance.
View Rules and Restrictions
- Views can be nested; that is, a view may be built using a query that retrieves data from another view.
ORDER BYmay be used in a view, but it will be overridden if
ORDER BYis also used in the
SELECTthat retrieves data from the view.
- Views cannot be indexed, nor can they have triggers or default values associated with them.
- Views can be used in conjunction with tables, for example, to create a
SELECTstatement which joins a table and a view.
CREATE VIEW view_name AS SELECT ...;
SHOW CREATE VIEW view_name; : Show the statement used to create this view.
DROP VIEW view_name: Delete the view.
CREATE OR REPLACE VIEW view_name : Create a view if it does not exist and replace it if it does.
If any of the following options is used, you’ll not be able to update the view:
- Grouping (using
- Aggregate functions (
Sum(), and so forth)
- Derived (calculated) columns
Managing Transaction Processing
|Transaction||A block of SQL statements|
|Rollback||The process of undoing specified SQL statements|
|Commit||Writing unsaved SQL statements to the database tables|
|Savepoint||A temporary placeholder in a transaction set to which you can issue a rollback (as opposed to rolling back an entire transaction)|
To denote the start of a transaction,
Statements can be undo :
CREATE statements are available in a transaction, however you cannot
START TRANSACTION; # DO SOMETHING COMMIT;
To support the rollback of partial transactions, put the unique placeholder defined by
... SAVEPOINT save_point_1; ... ROLLBACK TO save_point_1;
Savepoints are automatically released after a transaction completes.
Changing the Default Commit Behavior
autocommitflag is a client-side option.
Globalization and Localization
Character sets are collections of letters and symbols.
Encodings are the internal representations of the members of a character set.
Collations are the instructions that dictate how characters are to be compared.
Statements show available character sets and collations:
SHOW CHARACTER SET;
Statements show information about the character set and collation of the database:
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
To specify a character set and collation for a table, append the statements below after the
CREATE TABLE mytable ( ... ) DEFAULT CHARACTER SET charset_example COLLATE collate_example;
How MySQL determines which to use:
- If both
COLLATEare specified, those values are used.
- If only
CHARACTER SETis specified, it is used along with the default collation for that character set (as specified in the
SHOW CHARACTER SETresults).
- If neither
COLLATEare specified, the database default is used.
COLLATE to specify an alternate collation sequence:
SELECT ... ... ORDER BY ... COLLATE example_collate;
COLLATE can also be used with
HAVING, aggregate functions, aliases, and more.
Security and Maintenance
Users should have appropriate access to the data they need, no more and no less.
MySQL user accounts and information are stored in a database named
To obtain a list of all user accounts:
SELECT user FROM user;
Creating User Accounts
CREATE USER user_name IDENTIFIED BY 'Your Password';
Deleting User Accounts
DROP USER user_name;
DROP USERdeletes user's account and all associated rights and privileges.
With user accounts created, you must next assign access rights and privileges. Newly created user accounts have no access to databases at all, that is, they cannot perform any operations.
To show the rights granted to a user,
SHOW GRANTS FOR user_name;
SHOW GRANTSif you've already logged in with the user you would like to know the rights.
USAGE ON ...means no right to do anything.
Users are defined as
user@host. If no host name is specified, a default hostname of % is used (effectively granting access to the user regardless of the hostname).
At a minimum,
GRANT requires that you specify
- The privilege being granted
- The database or table being granted access to
- The user name
The example of granting all privileges to a user (the same as a root user):
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost'; FLUSH PRIVILEGES;
REVOKE is to cancel specific rights and permissions of a user.
REVOKE [OPERATION] ON database.table FROM user_name;
REVOKE can be used to control access at several levels:
- Entire server : Use
- Entire database : Use
- Specific tables : Use
- Specific columns
- Specific stored procedures
|ALL||All privileges except GRANT OPTION|
|ALTER||Use of ALTER TABLE|
|ALTER ROUTINE||Use of ALTER PROCEDURE and DROP PROCEDURE|
|CREATE||Use of CREATE TABLE|
|CREATE ROUTINE||Use of CREATE PROCEDURE|
|CREATE TEMPORARY TABLES||Use of CREATE TEMPORARY TABLE|
|CREATE USER||Use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES|
|CREATE VIEW||Use of CREATE VIEW|
|DELETE||Use of DELETE|
|DROP||Use of DROP TABLE|
|EXECUTE||Use of CALL and stored procedures|
|FILE||Use of SELECT INTO OUTFILE and LOAD DATA INFILE|
|GRANT OPTION||Use of GRANT and REVOKE|
|INDEX||Use of CREATE INDEX and DROP INDEX|
|INSERT||Use of INSERT|
|LOCK TABLES||Use of LOCK TABLES|
|PROCESS||Use of SHOW FULL PROCESSLIST|
|RELOAD||Use of FLUSH|
|REPLICATION CLIENT||Access to location of servers|
|REPLICATION SLAVE||Used by replication slaves|
|SELECT||Use of SELECT|
|SHOW DATABASES||Use of SHOW DATABASES|
|SHOW VIEW||Use of SHOW CREATE VIEW|
|SHUTDOWN||Use of mysqladmin shutdown (used to shut down MySQL)|
|SUPER||Use of CHANGE MASTER, KILL, LOGS, PURGE MASTER, and SET GLOBAL. Also allows mysqladmin debug login.|
|UPDATE||Use of UPDATE|
If a database or table is removed (with a
DROP statement) any associated access rights will still exist. That is, if a database or a table is re-created in the future, those rights are still applied to them.
Backing Up Data
- Use the command line
mysqldumputility to dump all database contents to an external file. This utility should ideally be run before regular backups occur so the dumped file will be backed up properly.
- The command line
mysqlhotcopyutility can be used to copy all data from a database (this one is not supported by all database engines).
- You can also use MySQL to dump all data to an external file using
SELECT INTO OUTFILE. Both statements take the name of a system file to be created, and that file must not already exist or an error will be generated. Data can be restored using
- To ensure that all data is written to disk (including any index data) you might need to use a
FLUSH TABLESstatement before performing your backup.
To check that table keys are correct.
ANALYZE TABLE returns status information.
ANALYZE TABLE table_name;
CHECK TABLE supports a series of modes for use with
CHECK TABLE table_name;
CHANGEDchecks tables that have changed since the last check
EXTENDEDperforms the most thorough check
FASTonly checks tables that were not closed properly
MEDIUMchecks all deleted links and performs key verification
QUICKperforms a quick scan only
MyISAMtable access produces incorrect and inconsistent results, you might need to repair the table using
REPAIR TABLE. This statement should not be used frequently, and if regular use is required, there is likely a far bigger problem that needs addressing.
If you delete large amounts of data from a table,
OPTIMIZE TABLEshould be used to reclaim previously used space, thus optimizing the performance of the table.
mysqld command line options:
--helpdisplays help, a list of options.
--safe-modeloads the server minus some optimizations.
--verbosedisplays full text messages (use in conjunction with
--helpfor more detailed help messages).
--versiondisplays version information and then quits.
The primary log files:
- The error log contains details about startup and shutdown problems and any critical errors. The log is usually named
datadirectory. This name can be changed using the
- The query log logs all MySQL activity and can be very useful in diagnosing problems. This log file can get very large very quickly, so it should not be used for extended periods of time. The log is usually named
datadirectory. This name can be changed using the
- The binary log logs all statements that updated (or could have updated) data. The log is usually named
datadirectory. This name can be changed using the
--log-bincommand-line option. Note that this log file was added in MySQL 5; the update log is used in earlier versions of MySQL.
- As its name suggests, the slow query log logs any queries that execute slowly. This log can be useful in determining where database optimizations are needed. The log is usually named
datadirectory. This name can be changed using the
When logging is being used, the
FLUSH LOGS statement can be used to flush and restart all log files.
ERROR 1045 (28000)
Problem : Access denied for user ...
Case : Wrong username or password.
Solution : Input the right password or username.
ERROR 2003 (HY000)
Problem :Can't connect to MySQL server on ...
Case 1: Port is not accessible to you becasuse of the safety strategy settings.
Solution: Add the TCP 3306 port (or other port numbers you set in MySQL) at your service provider's control panel.
Case 2: Port was not open for your IP address
First, check the MySQL configuration file
my.cnf and see if the line below was commented out. If not, edit the value as
# bind_address = 127.0.0.1 bind_address = 0.0.0.0
Second, check the default firewall of your system. For instance, use the command below to list avalable ports (CentOS 7).
As you can see, the port 3306 was not included in the accessible ports. In order to add the port, run this instruction.
firewall-cmd --zone=public --add-port=3306/tcp --permanent
After that, restart the firewall and check if the new setting works.
firewall-cmd --reload firewall-cmd --state
Additionally, you can treat some other firewalls, like
iptables , the similar way.