Some Utility Commands

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 / DESCRIBE db_name: Show all columns of a database.

SHOW STATUS: Show server state.

SHOW CREATE DATABASE/TABLE: Show scripts creating a specific database/table. 

SHOW GRANTS: Show privileges of the current user.

SHOW ERRORS/WARNINGS: Show server errors/warning information. 

Keywords 

DISTINCT: Applies to all columns, not only to the column before this keyword. 

  • DISTINCT col_name 

LIMIT: Return 1 or n rows. 

  • LIMIT n: Return top n rows 
  • LIMIT m, n: Return n rows after the mth (not included) row. 
  • LIMIT n OFFSET m: The result of this is the same as LIMIT m, n .

ORDER BY 

  • ORDER BY col_name_1, col_name_2, ... 
  • The result is ordered by col_name_2 if and only if the value of col_name_1 are equal.
  • It should be placed after the FROM clause, WHERE clause and before the LIMIT clause 
  • Options: 
    • DESC: Descending order 
    • ASC: Ascending order (default) 

Speed of Manipulating Data: Database Level > Application Level 

By default, MySQL is not case-sensitive when matching a string. 

BETWEEN start_val AND end_val: match all values in the range of [start_val, end_val] .

IS NULL: Check columns that contain a NULL value. 

  • WHERE col_name IS NULL 
  • The database will not return rows whose field contains NULL value, even when filtering at other columns’ conditions

AND / OR: Connect multiple WHERE clauses’ conditions.

  • Caution: The priority of AND is over OR, that is, when given more than three conditions, the MySQL first combines conditions using AND, then performs the OR operation.
  • Include your conditions in () thus causing exceptions due to issues above.

IN: Denote conditions listed in a pair of (), delimited by comma. The IN operator works as a better form of OR, and it runs much faster than it.

  • WHERE col_name IN (condition_A, condition_B, ...)

NOT: Invert the conditions after this operator.

  • NOT IN / BETWEEN / EXISTS

Search Pattern and Regular Expression

Search Pattern = Character(s) + Wildcard(s)

  • WHERE col_name LIKE 'Search Pattern'

Wildcards / Patterns

Wildcards/PatternsDescription
%Zero, one, or multiple characters
_A single character
word%Search values start at ‘word’
%word%Search values contain ‘word’
a%bSearch any values that start with ‘a’ and ends with ‘b’

Please 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.

Be noticed that the regular expression that MySQL supports is a subset.

  • WHERE col_name REGEXP 'Regular Expression Pattern'
  • REGEXP BINARY 'Regular Expression Pattern': Makes the search case-sensitive.

The difference between LIKE and REGEXP:

  • LIKE 'Search Pattern' matches the whole column, even the 'Search Pattern' occurs in the value of a row, it will return nothing.
  • On the other side, REGEXP matches in the value of each row of the column, if matched, it will return the row.
  • Using ^ and $ enables the REGEXP to work as the LIKE operator.

Some Regular Expressions

Regular ExpressionDescription
|OR, caution the combination with other characters
[123…abc…]OR, matches any of the characters listed in brackets
^NOT, exclude anything that occurs after the ^
Define a range, e.g. 1-9, a-z

Escaping: Add the prefix \\.

\\ can also be used to refer to meta character.

Additionally, there is a built-in character set called character class that includes some useful regular expressions.

Character Classes

Character ClassDescription
[: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 [\\f\\n\\r\\t\\v ])

Sometimes we want to search something occurs more than once, and repetition metacharacters might be the solution for this.

To start a search at a specific place of a text, please use anchor metacharacters.

Anchor Metacharacters

Anchor CharacterDescription
^Start of text
$End of text
[[:<:]]Start of word
[[:>:]]End of word

The test of a regular expression:

  • SELECT 'text' REGEXP 'regexp';

Functions

Concatenation and Trimming:

  • Concat(col_name, 'str1', ...)
  • RTrim(col_name), LTrim(col_name), Trim(col_name)
  • RTrim(' str '), LTrim(' str '), Trim(' str ')

Alias (derived columns) for calculated columns:

  • SELECT function(args) AS alias_col ...

The test of a function:

  • SELECT function(args);

Functions are less portable than standard SQL statements, so comments and documentation are required when you use them.

Commonly Used Text-Manipulation Functions

FunctionDescription
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
Soundex()Returns a string’s SOUNDEX value
SubString()Returns characters from within a string
Upper()Converts a string to uppercase

The usage of Soundex():

  • WHERE Soundex(col_name) = Soundex('str'): Find values pronounce like 'str'.

Commonly Used Date and Time Manipulation Functions

FunctionDescription
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.

  • The Date() function extracts the date part from its argument.
  • WHERE Date(col_name) = 'yyyy-MM-dd'
  • Likewise, so does Year(), Month(), etc.

SQL Aggregate Functions

To summarize data without retrieving all of them, use aggregate functions.

SQL Aggregate Functions

FunctionDescription
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

AVG() ignores NULL rows, and it only applies to a single field.

MAX(), MIN() ignores NULL rows, and they accept numeric and date type as the argument.

SUM() ignores NULL rows.

The main usage of Count() is to count the number of rows in a table/column no matter if there exists a NULL value.

DISTINCT may only be used with COUNT() if a column name is specified. DISTINCT may not be used with COUNT(*)

When specifying alias names contain the results of an aggregate function, try not to use the name of an actual column in the table.

The GROUP BY clause instructs MySQL to group the data and then perform the aggregate on each group rather than on the entire result set.

Grouping Data

GROUP BY

  • You can build nested groups using GROUP BY.
  • All the columns specified are evaluated together when grouping is established.
  • Aside from aggregate calculation statements, each column in your SELECT statement should be in the GROUP BY clause.
  • If the grouping column contains a row with a NULL value, NULL will be returned as a group. If there are multiple rows with NULL values, they’ll all be grouped together.
  • The GROUP BY clause must come after any WHERE clause and before any ORDER BY clause.

ROLLUP

  • GROUP BY col_name ROLLUP: Obtain values at each group and at a summary level (for each group),

HAVING supports all of WHERE’s operators. And the only difference is that WHERE filters rows and HAVING filters groups.

WHERE filters before data is grouped, and HAVING filters after data is grouped. Thus rows that are eliminated by a WHERE clause are not included in a group. This could change the calculated values, which in turn could affect which groups are filtered based on the use of those values in the HAVING clause.

ORDER BY versus GROUP BY

ORDER BYGROUP BY
Sorts generated output.Groups rows. The output might not be in group order, however.
Any columns (even columns not selected) may be used.Only selected columns or expressions columns may be used, and every selected column expression must be used.
Never required.Required if using columns (or expressions) with aggregate functions.

As a rule, anytime you use a GROUP BY clause, you should also specify an ORDER BY clause. That is the only way to ensure that data is sorted properly. Never rely on GROUP BY to sort your data.

Subqueries

Subqueries are always processed starting with the innermost SELECT statement and working outward. 

When using a subquery in a WHERE clause, make sure that the SELECT statements have the same number of columns as in the WHERE clause.

Subqueries are usually conjuncted with IN, they can also be used to test for equality (using =), non-equality (using <>), etc..

Build queries with subqueries incrementally. Build and test the innermost query first. Then build and test the outer query with hard-coded data, and only after you have verified that it is working embed the subquery. Then test it again. And keep repeating these steps as for each additional query. 

Scale: Be able to handle an increasing load without failing. A well-designed database or application is said to scale well.

A join is created by MySQL as needed, and it persists for the duration of the query execution.

Cartesian Product: The results returned by a table relationship without a join condition. The number of rows retrieved is the number of rows in the first table multiplied by the number of rows in the second table.

Table aliases are only used during query execution and never returned to the client.

  • ... FROM table_A AS a, ...

Equijoin (Inner Join)

A join based on the testing of equality between two tables, using the special ON clause instead of a WHERE clause. According to the ANSI standard, it’s preferable to use INNER JOIN syntax.

  • ... FROM table_A INNER JOIN table_B ON table_A.col_name = table_B.col_name

Self Join

  • ... FROM table_A AS a1, table_A AS a2 ...
  • Self joins are often used to replace statements using subqueries that retrieve data from the same table, as the outer one.

Natural Join

  • Select columns that are unique, using a wildcard (SELECT *) for one table and explicit subsets of the columns for all other tables.
  • Natural Join is a subset of Inner Join, and removes duplicates results from equijoin.

Outer Join

  • Outer Joins include rows with no related rows.
  • When using OUTER JOIN syntax you must use the RIGHT or LEFT keywords to specify the table from which to include all rows.
  • ... FROM table_A LEFT OUTER JOIN table_B ...: Select all the rows matched from the table_A (the table at left), vice versa.

Aggregate functions can be used with joins.

  • SELECT table_A, Aggregate(table_B.col_name) AS b FROM table_A INNER JOIN table_B ON ...

Make sure you’ve test each join separately before testing them together.

Combining Queries

Two scenarios in which you’d use combined queries (UNION):

  • 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.
  • Syntax:Query_1 UNION Query_2 UNION ...

Any SELECT statement with multiple WHERE clauses can be specified as a combined query.

UNION Rules

  • Each query in a UNION must 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).

The UNION automatically removes any duplicate rows from the query result set. If you would like to keep the same rows, consider using UNION ALL.

When combining queries with a UNION, only one ORDER BY clause may be used, and it must occur after the final SELECT statement.

Full-Text Searching

Skipped

Inserting Data

Several Cases Using INSERT:

  • 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 VALUES(...);: Not Recommended
  • 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.

LOW_PRIORITY

  • You can instruct MySQL to lower the priority of your INSERT statement by adding the keyword LOW_PRIORITY in between INSERT and INTO. Likewise, this also applies to the UPDATE and DELETE statements.
  • INSERT LOW_PRIORITY INTO

Updating and Deleting Data

UPDATE

  • UPDATE table_name SET col_name = ... WHERE ...
  • When updating multiple columns, use commas to separate SET clauses.
    • ... SET col_name_1 = ..., SET col_name_2 = ..., ...

By default, when the update goes wrong, the entire operation will be canceled unless using the IGNORE keyword.

Delete some column’s value: Update a column’s value as NULL.

DELETE

  • DELETE is to remove rows from a table. Even all the rows are gone, the table still exists.
  • DELETE FROM table_name WHERE col_name = ...
  • TRUNCATE TABLE: 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 UPDATE or a DELETE without a WHERE clause unless you do intend to update and delete every row.
  • Run SELECT before UPDATE and DELETE thus not affecting the unrelated rows.
  • Use database enforced referential integrity.

Creating and Manipulating Tables

CREATE

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;

AUTO_INCREMENT:  Automatically incremented each time a row is added. What’s more, only one AUTO_INCREMENT column is allowed per table, and it must be indexed (for example, by making it a primary key). Function last_insert_id() return an id of the latest row inserted.

Default value: Functions are not allowed.

For columns to be calculated or aggregated, conventionally, using a default value rather than NULL.

Engine Types

  • InnoDB
    • transaction-safe
    • suited for temporary table cause data is stored in memory.
  • MyISAM
    • full-text searching supported
  • Engines types can be mixed across tables only if no foreign key exists.

Ideally, tables should never be altered after they contain data.

ALTER

  • ALTER TABLE table_name [ADD/DROP] ..., [ADD/DROP] ...;
  • ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY(col_name_1) REFERENCES table_name_2 (col_name_2);

Complex table structure changes usually require a manual move process involving these steps:

  1. Create a new table with the new column layout.
  2. Use the INSERT SELECT statement to copy the data from the old table to the new table. Use conversion functions and calculated fields, if needed.
  3. Verify that the new table contains the desired data.
  4. Rename the old table.
  5. Rename the new table with the name previously used by the old table.
  6. Re-create any triggers, stored procedures, indexes, and foreign keys as needed.

DROP

  • DROP table_name;

RENAME

  • RENAME current_name TO table_name, current_name_2 TO ...;

Using Views

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 BY may be used in a view, but it will be overridden if ORDER BY is also used in the SELECT that 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 SELECT statement which joins a table and a view.

Using 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 are used you’ll not be able to update the view:

  • Grouping (using GROUP BY and HAVING)
  • Joins
  • Subqueries
  • Unions
  • Aggregate functions (Min()Count()Sum(), and so forth)
  • DISTINCT
  • Derived (calculated) columns

Working with Stored Procedures

Using the stored procedures:

  • CALL proc_name(@param_1, @param_2, ...);

Create a stored procedure:

  • IN / OUT: To denote the parameter passed into/out of the procedure.
  • INOUT: To pass parameters to and from stored procedures.
CREATE proc_name(
    IN param_a TYPE,
    OUT param_b TYPE,
    ...
)
BEGIN
    SELECT ...
    FROM ...
    WHERE ... -- e.g. Do sth. with param_a
    INTO param_b
END

Note that multiple rows and columns could not be returned via a parameter. 

All MySQL variable names must begin with @ when calling a procedure.

Inspecting Stored Procedures

  • SHOW CREATE PROCEDURE proc_name;: To display the CREATE statement used to create a stored procedure
  • SHOW PROCEDURE STATUS;: To obtain a list of stored procedures including details on when and who created them
    • SHOW PROCEDURE STATUS LIKE 'proc_name'; To obtain the detail of a specific procedure.

Using Cursors

 A cursor is a database query stored on the MySQL server. Once the cursor is stored, applications can scroll or browse up and down through the data as needed. MySQL cursors may only be used within stored procedures (and functions).

Using cursors involves several distinct steps:

  1. During the process of declaring a cursor, it merely defines the SELECT statement to be used.
  2. After that, the cursor must be opened for use. This process retrieves the data using the previously defined statement.
  3. With the cursor populated with data, individual rows can be fetched (retrieved) as needed.
  4. When it is done, the cursor must be closed.

Creating Cursors:

CREATE PROCEDURE proc_name()
BEGIN
    DECLARE CURSOR cursor_name
    FOR
    SELECT ...
    ...
END

Opening and Closing Cursors

  • OPEN cursor_name
  • CLOSE cursor_name
  • MySQL will close it automatically when the END statement is reached.

Consuming Cursor Data

  • After a cursor is opened, each row can be accessed individually using a FETCH statement. FETCH specifies what is to be retrieved and where retrieved data should be stored. It also move the internal row pointer forward within the cursor so the next FETCH statement will retrieve the next row (and not the same one over and over).
CREATE PROCEDURE proc_name()
BEGIN
    -- Declare local variables
    DECLARE local_var TYPE;
    DECLARE CURSOR cursor_name
    FOR
    SELECT ...
    ...;
    OPEN cursor_name;
    FETCH col_name INTO local_var;
    CLOSE cursor_name;
END
  • Iterating over rows
CREATE PROCEDURE proc_name()
BEGIN
   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE int_var INT;

   -- Declare the cursor
   DECLARE cursor_name CURSOR
   FOR
   SELECT ...;

   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

   -- Open the cursor
   OPEN cursor_name;

   -- Loop through all rows
   REPEAT

      -- Get order number
      FETCH col_name INTO int_var;

   -- End of loop
   UNTIL done END REPEAT;

   -- Close the cursor
   CLOSE cursor_name;

END;
  • '02000' is a MySQL Error Code that represents ‘Not Found’.
  • See more: https://dev.mysql.com/doc/refman/8.0/en/error-handling.html
  •  Local variables defined with DECLARE must be defined before any cursors or handlers are defined, and handlers must be defined after any cursors. 
  • LOOP statement can be used to repeat code until the LOOP is manually exited using a LEAVE statement. The syntax of the REPEAT statement better suites for looping through cursors.

Using Triggers

trigger is a MySQL statement (or a group of statements enclosed within BEGIN and END statements) that are automatically executed by MySQL in response to any of these statements:

  • DELETE
  • INSERT
  • UPDATE

Create triggers:

CREATE TRIGGER trigger_name BEFORE/AFTER INSERT 
ON table_name
FOR EACH ROW ... -- THE CODE TO BE EXECUTED AFTER INSERT A ROW.

For MySQL, triggers are only supported on tables.

If a BEFORE trigger fails, MySQL will not perform the requested operation. In addition, if either a BEFORE trigger or the statement itself fails, MySQL will not execute an AFTER trigger (if one exists).

Drop a triger:

  • DROP TRIGGER triger_name;

INSERT Triggers

INSERT triggers are executed before or after an INSERT statement is executed. Be aware of the following:

  • Within INSERT trigger code, you can refer to a virtual table named NEW to access the rows being inserted.
  • In a BEFORE INSERT trigger, the values in NEW can also be updated (allowing you to change values about to be inserted).
  • For the AUTO_INCREMENT column, NEW will contain 0 before and the new automatically generated value after.
  • Use BEFORE for any data validation and cleanup.
CREATE TRIGGER trigger_name AFTER INSERT ON table_name
FOR EACH ROW
    SELECT NEW.col_name;

DELETE Triggers

DELETE triggers are executed before or after a DELETE statement is executed. Be aware of the following:

  • Within DELETE trigger code, you can refer to a virtual table named OLD to access the rows being deleted.
  • The values in OLD are all read-only and cannot be updated.
CREATE TRIGGER delete_triger BEFORE DELETE ON table_name
FOR EACH ROW
BEGIN
   ...
END;

UPDATE Triggers

UPDATE triggers are executed before or after an UPDATE statement is executed. Be aware of the following:

  • Within UPDATE trigger code, you can refer to a virtual table named OLD to access the previous (pre-UPDATE statement) values and NEW to access the new updated values.
  • In a BEFORE UPDATE trigger, the values in NEW may also be updated (allowing you to change values about to be used in the UPDATE statement).
  • The values in OLD are all read-only and cannot be updated.
CREATE TRIGGER update_trigger BEFORE/AFTER UPDATE ON table_name
FOR EACH ROW ...;
-- DO STH. WITH NEW/OLD, OR ANY OTHER THINGS...

Important points when using triggers:

  • Creating triggers might require special security access. However, trigger execution is automatic. If an INSERTUPDATE, or DELETE statement may be executed, any associated triggers will be executed, too.
  • Triggers should be used to ensure data consistency (case, formatting, and so on). The advantage of performing this type of processing in a trigger is that it always happens, and happens transparently, regardless of client application.
  • One critical use for triggers is in creating an audit trail, i.e. to log changes (even before and after states if needed) to another table.
  • The CALL statement is not supported in MySQL triggers.

Managing Transaction Processing

Some terms about transaction:

  • 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 a start of a transaction,

  • START TRANSACTION

The kinds of statements can be undo:

  • INSERT
  • UPDATE
  • DELETE

DROP and CREATE statements are available in a transaction, however you cannot ROLLBACK them.

START TRANSACTION;
-- DO SOMETHING
COMMIT;

Using SAVEPOINT:

  • To support the rollback of partial transactions, put the unique placeholder defined by SAVEPOINT.
  • SAVEPOINT save_point_1;
  • ROLLBACK TO save_point_1;

Savepoints are automatically released after a transaction completes.

Changing the Default Commit Behavior

  • SET autocommit=0;
  • The autocommit flag is per connection, not server-wide.

 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;
  • SHOW COLLATION;

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.

CREATE TABLE mytable
(
...
) DEFAULT CHARACTER SET char_set_example
  COLLATE collate_example;

How MySQL determines what to use:

  • If both CHARACTER SET and COLLATE are specified, those values are used.
  • If only CHARACTER SET is specified, it is used along with the default collation for that character set (as specified in the SHOW CHARACTER SET results).
  • If neither CHARACTER SET nor COLLATE are specified, the database default is used.

Use COLLATE to specify an alternate collation sequence:

SELECT ... ORDER BY ... COLLATE example_collate;

COLLATE can also be used with GROUP BYHAVING, aggregate functions, aliases, and more.

Managing Security

Users should have appropriate access to the data they need, no more and no less.

The MySQL Administrator provides a graphical user interface that can be used to manage users and account rights.

MySQL user accounts and information are stored in a database named mysql

To obtain a list of all user accounts:

  • SELECT user FROM user;

Creating User Accounts

  • CREATE USER user_name IDENTIFIED BY 'P@s$W0rd';

Deleting User Accounts

  • DROP USER user_name;
  • DROP USER deletes user accounts and all associated account 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;
  • Use SHOW GRANTS if you’ve already logged in with the user you would like to know the rights.
  • Result: 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).

To set rights the GRANT statement is used. 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;

The REVOKE is to cancel specific rights and permissions of a user.

  • REVOKE [OPERATION] ON database.table FROM user_name;

GRANT and REVOKE can be used to control access at several levels:

  • Entire server, using GRANT ALL and REVOKE ALL
  • Entire database, using ON database.*
  • Specific tables, using ON database.table
  • Specific columns
  • Specific stored procedures
PrivilegeDescription
ALLAll privileges except GRANT OPTION
ALTERUse of ALTER TABLE
ALTER ROUTINEUse of ALTER PROCEDURE and DROP PROCEDURE
CREATEUse of CREATE TABLE
CREATE ROUTINEUse of CREATE PROCEDURE
CREATE TEMPORARY TABLESUse of CREATE TEMPORARY TABLE
CREATE USERUse of CREATE USERDROP USERRENAME USER, and REVOKE ALL PRIVILEGES
CREATE VIEWUse of CREATE VIEW
DELETEUse of DELETE
DROPUse of DROP TABLE
EXECUTEUse of CALL and stored procedures
FILEUse of SELECT INTO OUTFILE and LOAD DATA INFILE
GRANT OPTIONUse of GRANT and REVOKE
INDEXUse of CREATE INDEX and DROP INDEX
INSERTUse of INSERT
LOCK TABLESUse of LOCK TABLES
PROCESSUse of SHOW FULL PROCESSLIST
RELOADUse of FLUSH
REPLICATION CLIENTAccess to location of servers
REPLICATION SLAVEUsed by replication slaves
SELECTUse of SELECT
SHOW DATABASESUse of SHOW DATABASES
SHOW VIEWUse of SHOW CREATE VIEW
SHUTDOWNUse of mysqladmin shutdown (used to shut down MySQL)
SUPERUse of CHANGE MASTERKILLLOGSPURGE MASTER, and SET GLOBAL. Also allows mysqladmin debug login.
UPDATEUse of UPDATE
USAGENo access

If a database or table is removed (with a DROP statement) any associated access rights will still exist. And if the database or table is re-created in the future, those rights will apply to them.

Changing Passwords

  • SET PASSWORD FOR user_name = Password('Your Password');
  • When no user name is specified, SET PASSWORD updates the password for the currently logged in user.
  • The new password must be encrypted by being passed to the Password() function.

Database Maintenance

Backing Up Data

  • Use the command line mysqldump utility 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 mysqlhotcopy utility 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 BACKUP TABLE or 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 RESTORE TABLE.
  • To ensure that all data is written to disk (including any index data) you might need to use a FLUSH TABLES statement 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 MyISAM tables. 

  • CHECK TABLE table_name;
  • CHANGED checks tables that have changed since the last check
  • EXTENDED performs the most thorough check
  • FAST only checks tables that were not closed properly
  • MEDIUM checks all deleted links and performs key verification
  • QUICK performs a quick scan only
  • If MyISAM table 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 TABLE should be used to reclaim previously used space, thus optimizing the performance of the table.

mysqld command line options:

  • --help displays help, a list of options.
  • --safe-mode loads the server minus some optimizations.
  • --verbose displays full text messages (use in conjunction with --help for more detailed help messages).
  • --version displays 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 hostname.err in the data directory. This name can be changed using the --log-error command-line option.
  • 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 hostname.log in the data directory. This name can be changed using the --log command-line option.
  • The binary log logs all statements that updated (or could have updated) data. The log is usually named hostname-bin in the data directory. This name can be changed using the --log-bin command-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 hostname-slow.log in the data directory. This name can be changed using the --log-slow-queries command-line option.

When logging is being used, the FLUSH LOGS statement can be used to flush and restart all log files.