Character sets and Collations

• MySQL | Binary and Non-binary strings
  A binary string is a sequence of bytes. They have the binary character set and collation, and the comparison / sorting is done based on the numeric values of the bytes. A non-binary string is a sequence of characters. It is associated with a character set and collation.
• MySQL | Character sets
  A character set defines the set of characters that can be stored in a string. Each character is made up of a symbol and its corresponding encoding.
• Checking character set in MySQL
  We can check the character set in MySQL on a database, table, column, string level using varying syntax.
• Checking collation in MySQL
  This article describes how to check the collation of a string in MySQL.
• MySQL | Collations
  A collation is a set of rules that defines how to compare and sort characters in a character set. A collation can only belong to a single character set, however, a character set can have many collations.
• Setting the character set in MySQL
  We can set the character set at a server, database, table or column level in MySQL.
• UTF-8 representation in MySQL
  MySQL’s actual equivalent of utf-8 is a character set known as utf8mb4.  You should never use utf-8 since this can lead to troubling inconsistencies when changing vendors since this can lead to troubling inconsistencies when changing vendors. The reason MySQL’s utf-8 still exists is for backward compatibility with previous versions of MySQL.

Data types Cookbook

• MySQL | Automatic Initialization and updating for TIMESTAMP and DATETIME
  For TIMESTAMP and DATETIME columns in a table in MySQL you can: assign the current timestamp as the default value, assign the auto-update value, or assign both a default value and auto-update value.
• Difference between DATETIME and TIMESTAMP in MySQL
  The TIMESTAMP type in MySQL is often used to track changes to records such as keeping track of the last modified time of a record, while DATETIME is more suited for storing date and time information that is likely to fall outside the range of TIMESTAMP.
• Difference between Signed and Unsigned in MySQL
  UNSIGNED only stores positive numbers (or zero) while SIGNED can store negative numbers.
• When to use INT or STRING in MySQL
  When you need to select a data type for what appears to be a number, always think about whether you would need to do any computations with them. If you do not need to do any arithmetic with the value, you can simply choose the data type to be string.

Database Cookbook

• Accessing tables in other databases in MySQL
  In MySQL we can explicitly prepend the table name with the database name in order to access a table in a separate database (without having to navigate to that database first).
• Checking size of databases in MySQL
  We can query the information_schema.tables table to check the size of databases in MySQL.
• Creating a database in MySQL
  We can use the following general syntax to create a database in MySQL: CREATE DATABASE database_name;
• Importing an SQL file using the command line in MySQL
  We can import an SQL file using the following command line syntax in MySQL: mysql -u username -p database_name < file_name.sql.
• Removing databases in MySQL
  We can remove databases in MySQL using the following general syntax: DROP DATABASE IF EXISTS database_name;
• Selecting databases in MySQL
  We can select a database to use using the following general syntax in MySQL: USE database_name;
• Showing all databases in MySQL
  We can use the command SHOW DATABASES; to show all the databases in MySQL.
• Showing current database
  We can use the SELECT DATABASE(); command to show the current selected database in MySQL.

Functions Cookbooks

• Concatenating multiple rows into one field in MySQL
  We can concatenate multiple results into one field in MySQL using the GROUP_CONCAT(~) method.
• Counting the number of rows in a table in MySQL
  We can count the number of rows in a table in MySQL using the COUNT(~) aggregate function.
• Finding duplicate values in MySQL
  It is possible to find duplicate values in MySQL using the COUNT aggregate method.
• Performing regular expression replace in MySQL
  We can perform regular expression replace in MySQL using the REGEXP_REPLACE(~) method.
• Replacing a string in MySQL
  We can replace a particular string with another string in MySQL using the REPLACE(~) method.

Sample tables

• Sample table: customer
  Code for generating the customer sample table.
• Sample table: drivers_license
  Code for generating the drivers license sample table.
• Sample table: employment
  Code for generating the employment sample table.
• Sample table: extracurricular
  Code for generating the extracurricular sample table.
• Sample table: info
  Code for generating the info sample table.
• Sample table: product
  Code for generating the product sample table.
• Sample table: professors
  Code for generating the professors sample table.
• Sample table: pupil
  Code for generating the pupil sample table.
• Sample table: students
  Code for generating the students sample table.
• Sample table: teacher
  Code for generating the teacher sample table.

Server settings Cookbook

• Checking all running queries in MySQL
  We can check all running queries on the MySQL server using the SHOW PROCESSLIST command.
• Checking server configuration settings in MySQL
  We can check server configuration settings in MySQL using the SHOW VARIABLES command.
• Checking server status information in MySQL
  We can check server status information in MySQL using the SHOW STATUS statement.
• Ending problematic queries in MySQL
  You can end problematic processes in MySQL by using the KILL statement.

Tables Cookbooks

• Column Cookbooks

  • Adding a default column value in MySQL
    We can add a default column value in MySQL using the DEFAULT constraint.
  • Adding a new column to a table in MySQL
    We can add a new column to an existing table in MySQL using an ALTER table_name ADD new_column statement.
  • Changing column data type in MySQL
    We can change the column type in MySQL using syntax like ALTER TABLE table_name MODIFY column_name new_type;
  • Modifying a column to allow NULL in MySQL
    By default columns in MySQL can take on NULL values unless you have explicitly specified the column as NOT NULL or UNIQUE.
  • Removing a column from a table in MySQL
    We can remove a column from a table in MySQL using an ALTER TABLE statement.
  • Renaming a column in MySQL
    We can permanently rename a column in MySQL using an ALTER TABLE statement.
  • Resetting AUTO_INCREMENT in MySQL
    We can reset the AUTO_INCREMENT of a field and have it start counting from 1 again using an ALTER TABLE statement.
  • Specifying position of a new column in MySQL
    In MySQL you can specify the position of a new column using the FIRST / AFTER keywords.
  • Temporarily renaming a column in MySQL
    We can provide columns with aliases in MySQL using the AS keyword.

• General table Cookbook

  • Backing up a table in MySQL
    We are able to back up a table to a sql file in MySQL using the following command: mysqldump -u {username} -h {host} -p database_name table_name > /filepath/backup.sql
  • Cloning a table in MySQL
    We can clone the structure of an existing table to a new table in MySQL using the general syntax: CREATE TABLE new_table LIKE original_table;
  • Creating a table in MySQL
    An article on how to create tables in MySQL.
  • Creating a temporary table in MySQL
    We can create temporary tables in MySQL using a CREATE TEMPORARY TABLE statement.
  • Deleting a table in MySQL
    It is possible to delete a table in MySQL using the following general syntax DROP TABLE IF EXISTS table_name;
  • Finding all tables with a specific column name in MySQL
    It is possible to check for all tables within a database that contain a specific column name by querying the INFORMATION_SCHEMA table.
  • Removing a temporary table in MySQL
    A temporary table will automatically be dropped at the end of a session, however, you can also explicitly remove it using the following general syntax: DROP TEMPORARY TABLE table_name;
  • Renaming a table in MySQL
    In MySQL It is possible to rename a table using an ALTER TABLE or RENAME TABLE statement.
  • Restoring a table from an sql file in MySQL
    We can restore a table from an .sql file in MySQL using the following command: mysql -u {username} -p database_name < /filepath/backup.sql
  • Showing all tables in MySQL
    We can show all tables in a database by running SHOW TABLES.
  • Showing all the fields of a table in MySQL
    We can use the DESCRIBE statement to remind ourselves of the configurations of a table.

• Key Cookbooks

  • Adding foreign key to an existing table in MySQL
    We can add a foreign key to an existing table in MySQL using the following general syntax: ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES parent_table(parent_column);
  • Changing the primary key in MySQL
    In order to change the primary key for a particular table in MySQL, you must first remove the current primary key and then add the new primary key.
  • Referencing column and referenced column are incompatible in MySQL
    This error occurs in MySQL when the column you are attempting to add a foreign key constraint on does not have a matching data type with the column you are linking to in the parent table.
  • Searching all foreign keys to a table or column in MySQL
    We can search for all foreign key constraints pointing to a particular table or a particular column in MySQL. The syntax varies slightly between searching for tables and searching for columns.
  • Temporarily disabling a foreign key constraint in MySQL
    We can temporarily disable foreign key constraints on a session level in MySQL by using the following: SET FOREIGN_KEY_CHECKS = 0;

• Row Cookbooks

  • Deleting a row from a table in MySQL
    We can delete rows from a table in MySQL using the DELETE statement.
  • Inserting rows into a table in MySQL
    We can insert rows into a table in MySQL using an INSERT INTO statement.
  • Removing duplicate rows in MySQL
    We can remove duplicate rows from a table in MySQL using a DELETE statement.
  • Selecting the nth row in a table in MySQL
    We can retrieve the nth row in a table in MySQL using LIMIT x OFFSET y syntax.
  • Updating rows in a table in MySQL
    We can update the information in a table in MySQL by using the UPDATE statement. To update multiple rows at once, we can make use of a INSERT INTO statement with a ON DUPLICATE KEY UPDATE construction.