SQL Tutorials

When running queries in your production environment, you have to be careful so you don’t bring it down or make a mistake that will cause your environment to act abnormally. Here are some things you need to be careful about: Run query with READ UNCOMMITTED option The MySQL READ UNCOMMITTED, also…

MySQL supports the native JSON data type since version 5.7.8. The native JSON data type allows you to store JSON documents more efficiently than the JSON text format in the previous versions. MySQL stores JSON documents in an internal format that allows quick read access to document elements. The…

A MySQL trigger is a stored program that is invoked automatically in response to an event such as INSERT, UPDATE, or DELETE to a particualar table. There are two types of triggers Row-level trigger: a row-level trigger is activated for each row that is inserted, updated, or deleted. Statement-level…

Most of the times, you want to backup your databases to have backups to prevent loosing data. If you don’t backup your databases, a code bug or a server/disk failure could be a disaster. To help save you lots of time and frustration, it is strongly recommended that you take the precaution of…

A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds. A deadlock can occur when transactions lock rows in…

Information Schema INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.…

MySQL Events or scheduled events are tasks that execute according to a specified schedule. They are stored in the database and executed at one or more intervals. For example, you can create an event that optimizes a table by backfilling data from another table the database that runs at 1:00 AM…

An index is a data structure such as B-Tree that improves the speed of data retrieval on a table at the cost of additional writes and storage to maintain it. Indexes are a type of lookup table that is used to improve the performance of SELECT queries. Without an index, MySQL performs lookups by…

A full-text index in MySQL is an index of type FULLTEXT. Full-text indexes can be used only with InnoDB or MyISAM tables, and can be created only for CHAR , VARCHAR , or TEXT columns. MySQL provides a built-in full-text ngram parser that supports Chinese, Japanese, and Korean (CJK), and an…

Show connections that your MySQL server has Show all users of your MySQL server Describe a table

Stop MySQL server sudo systemctl stop mysql // OR /etc/init.d/mysql stop Start MySQL server without loading grant table The ampersand & at the end of the command above will cause the program to run in the background, so you can continue to use the shell. When the –skip-grant-tables option is used,…

MySQL Advanced Queries 1.a Write a query to find the top 3 highest salaries in the company. SELECT salary FROM employee ORDER BY salary DESC LIMIT 3; 1.b Write a query to find the top 3 highest salary employees in the company. SELECT * FROM employee ORDER BY salary DESC LIMIT 3; 1.c Write a […]

MySQL Fundamental Questions 1. What is a database? A database is an organized collection of data. It stores data in a way that data can be easily accessed and maintained. It has tables that contain rows and columns. Example: School Management Database, Bank Management Database, etc. 2. What is…

Views are virtual tables what consist of columns and rows from real tables within a database. Views don’t contain the data they display. Views are practically for read-only but some databases allow updating on views. Here is how you create a view. You can just JOINs if needed to generate your…

DATE_FORMAT(date, format) %a Abbreviated weekday name (Sun to Sat) %b Abbreviated month name (Jan to Dec) %c Numeric month name (0 to 12) %D Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, …) %d Day of the month as a numeric value (01 to 31) %e Day of the […]

INTEGERINT can be sign or unsign. The ZEROFILL attribute can be used to add zero padding. AUTO_INCREMENT is used with INT but only with positive integers. MySQL is not supporting negative int with auto_increment at the time of writing. DECIMALDecimal is used for numbers with decimal. Example: for…

The MySQL LAST_INSERT_ID() function returns the most recently generated integer successfully inserted for an AUTO_INCREMENT column. CREATE TABLE user( id INT AUTO_INCREMENT, name VARCHAR(250) NOT NULL …, PRIMARY KEY(id) ); If you insert multiple rows into the table using a single INSERT statement,…

LAST_INSERT_ID Function The LAST_INSERT_ID functions returns the last id of the column that uses AUTO_INCREMENT for its id. It’s also important to know that this function has be called after an insert or else if you just call it without an insert statement it will give you 0. REPLACE Function…

The SHOW FULL PROCESSLIST statement shows all threads that are currently running. It is very handy to use if you get the “too many connections” error message and want to find out what is going on. Sometimes some connections are hanging and taking up resources that other threads might need. When you…

IF function The IF function is used to evaluate an expression and return a value. The IF function consists of three parts. 1. condition to evaluate.2. expression(can be another IF function) or value if the condition is evaluated to true.3. expression(can be another IF function) or value if the…

Stored procedures are SQL statements that you can save into a database and reuse over and over again. Syntax to create a stored procedure. To execute a store procedure, call its name. Advantages of Stored Procedures Fast as stored procedures can be cached and the big queries can be reduced to one…

The GROUP BY clause is used to group sets of rows into groups and returns only one row from each group. It is mostly used with aggregate functions but it does not have to. sdfsd GROUP BY with aggregate functions AVG – average value of a columnSUM – sum value of a columnMAX- […]

A sub query is a query within a query. A subquery can take place in a SELECT, FROM, and WHERE clause. A subquery can be used in a INSERT, UPDATE, SELECT, and DELETE query. The main query is called the outer query and the subquery in called the inner query. The sub query is […]

Self Join is designed to return rows with other rows within the same table. Table1 is joined with itself. We are joining employees to their managers. You must use ALIAS for the table to distinguish the difference.

The CROSS JOIN is used to join all rows from one table to all rows of another table.

The RIGHT JOIN clause is used to join multiple tables, return all rows from the right table(table2) and the matched rows from the left table(table1). Non matched rows from table1 will return nulls. Here we are going to use RIGHT JOIN to show customers and their addresses.

The LEFT JOIN clause is used to join two or more tables together. The main table will return all of its rows and the matching rows from the joining tables. If the joining tables don’t have the matching rows then MySQL will use NULL to represent those rows. Basically, you are getting everything from…

The INNER JOIN is used to query join multiple tables together. The join condition must match column values from both tables. Basically you are getting what both tables have in common. Here we are joining the customer table and the address table to show the customer address details. For each row in…

The ORDER BY clause is used to sort the result set which is not sorted by default. You can sort the result set by one or multiple columns and either in the ascending or descending order. ASC – sort in ascending orderDESC – sort in descending order* by default if [ASC|DESC] is omitted then […]

The BETWEEN is used in the WHERE clause to check if a value is in a range. Here is an example of BETWEEN and AND. We are querying customers that have sales rep with id between 1 and 7. BETWEEN can also be done by using >= and <=. Here is an example of […]

IS NULL is used in the WHERE clause to check if a value is NULL or not. IS NULL in action.

The LIMIT clause, as you might guess, is used to limit the number of rows returned in the result set. It has two parameters or arguments. The first parameter is offset which tells mysql how many rows to skip before starting to retrieve. The second parameter is count which tells mysql how many rows…

The LIKE operator is used to check if a string contains another string. These two wildcards are used with LIKE:1. “%” – check for a match of one or more characters2. “_” – check for a match of a single character If you are searching for something that ends with a certain pattern of […]

The WHERE clause is used in a SELECT statement with conditions to filter the result set. It is also used in the UPDATE and DELETE statement. There can be one or many conditions and a condition must be evaluated to true or false. A row must satisfy all conditions to be included in the […]

The SELECT statement is used to read or retrieve rows(data) from one or more database tables or views. You can retrieve one, two, or three, or all columns depending on what you need. Each row you retrieve will have the same number of columns. Here is an example of a SELECT statement. Select…

To delete data from a table, DELETE statement is used. Once data is deleted from your tables you can’t recover it. It is gone. It is recommended to back up your database regularly so that if data is gone you can still recover it from your backups. The WHERE clause is optional but if […]

The UPDATE clause is used to update an existing row. It can be used to update a single row, a group of rows, or all rows within a table. How to use UPDATE1. Specify the tables you want to update.2. Set the column values to their new values.3. The WHERE clause is optional but […]

The INSERT command inserts one or more rows into a table. Let’s see how the syntax works. INSERT INTO table_name – this query is an insert query into a table.VALUES – values for the columns you have chosen.* The number of columns and values must be equal otherwise it won’t work. * It is […]

Sometimes we are given a task where we need to model a hierarchy of different complexity levels and not really sure how to do that properly in the most efficient, reliable, and flexible way. Let’s review one of the data modeling patterns that give us some answers for that. Consider we have a…