# How to Round Timestamps in MySQL
Rounding or truncating timestamps are especially useful when you're [grouping by time](https://popsql.com/learn-sql/mysql/how-to-group-by-time-in-mysql/). If you are rounding by year or date, you can use the corresponding functions:

```sql
SELECT YEAR(now());  -- or DATE();
```

However, care must be done if you are grouping by months. Using `MONTH()` will, for example, make November 2018 and November 2017 both just translate to "11". If that is what you want, then you can use MONTH(). However, if you want to distinguish between months of different years, you need to use `DATE_FORMAT()`:

```sql
SELECT date_format(now(),'%Y-%m'); -- round to the month
SELECT date_format(now(),'%Y-%m-%d'); -- round to the day
SELECT date_format(now(),'%Y-%m-%d %H'); -- round to the hour
SELECT date_format(now(),'%Y-%m-%d %H:%i'); -- round to the minute
```

How to Round Timestamps

# How to add index in MySQL

## Enhancing MySQL query performance with indexing

Indexes are essential tools in databases, ensuring efficient data retrieval. Indexing in MySQL plays a pivotal role in optimizing query performance, especially as your dataset grows. Without the appropriate indexes, you might experience high CPU usage on your database server, slow response times, and ultimately, dissatisfied users. Indexes are data structures that store a subset of the data in a table, organized in a way that allows the database management system to quickly locate and retrieve rows that match specific criteria.

## Getting started with indexing in MySQL

### Using the CREATE INDEX command

1. To add an index to a table in MySQL or create a table index, you can use the `CREATE INDEX` command. The basic syntax is:

        CREATE INDEX index_name ON table_name (column_name);

2. For instance, if you want to add an index to the `email` column in the `users` table, the command would be:

        CREATE INDEX email_idx ON users (email);

### Multiple-column index
1. Sometimes, you might need to add an index that spans multiple columns, especially if those columns are frequently used together in queries. A multiple-column index often performs better than several single-column indexes. The syntax is:

        CREATE INDEX index_name ON table_name (column1, column2);

2. If your `column1` and `column2` contain user IDs and Organization ID, this is how your query would look:

        CREATE INDEX user_id_and_org_id_idx ON users (user_id, org_id);

### Unique index
1. A unique index ensures that the indexed columns do not have duplicate values. This can be particularly useful for columns like email addresses, where uniqueness is crucial. The standard syntax for creating unique index is:

        CREATE UNIQUE INDEX index_name ON table_name (column_name);

2. To create unique index for the `email` column, with the index name `users_email_uq`, here’s the query:

        CREATE UNIQUE INDEX users_email_uq ON users (email);

### Partial index or filtered index

In some cases, you might want to index only a specific portion of a string column instead of an entire table. This type of indexing is particularly useful for indexing historical data, rare or extreme values, or indexing based on status.  For instance, you can create an index on the first 20 characters of a the `name` column that holds company names:
```sql
CREATE INDEX company_part_name_idx ON companies (name(20));
```

### Storage order in index

Starting with MySQL version 8.x, you can specify the storage order of a column in an index. This can be beneficial if you also need to display the column in a particular order. By default, the order is ascending. In the following example, we are changing the order to descending using `DESC`:
```sql
CREATE INDEX reverse_name_idx ON companies (name DESC);
```

### Functional key parts

1. MySQL versions 8.0.13 and above support functional key parts. Functional key parts allow you to create an index on a function of one or more columns rather than on the columns themselves. This feature can be particularly useful in scenarios where you want to index computed values, apply functions to columns, or use expressions in your queries:

        CREATE INDEX index_name ON table_name 
        (expression_function(column_name));

2. In the following example, the `idx_full_name` index is created on the result of the `CONCAT` function applied to the `first_name` and `last_name` columns.

        CREATE INDEX idx_full_name ON employees((CONCAT(first_name, ' ', last_name)));

## Identifying and resolve indexing issues in MySQL

Troubleshooting MySQL indexing issues can help improve the responsiveness and performance of your database queries. Remember to carefully plan and test any index changes in a development or staging environment before applying them to a production database. Indexing decisions should be based on your specific query patterns and use cases, and regular monitoring and maintenance are essential for maintaining optimal performance.

### Missing index

Missing indexes, refer to indexes that have not been created on columns frequently used in query conditions (e.g., in the `WHERE` clause) or in join conditions (e.g., in` JOIN` operations). When these indexes are absent, queries can become inefficient, leading to slower data retrieval and decreased overall database performance.
```sql
SELECT * FROM orders WHERE customer_id = 123;
```
To identify missing indexes, you can use `EXPLAIN` or `EXPLAIN ANALYZE`. For the above example, let us create an index on the `customer_id` column.
```sql
CREATE INDEX idx_customer_id ON orders(customer_id);
```

### Redundant index

Redundant indexes in MySQL refer to indexes that are created on the same set of columns as other existing indexes, providing little to no additional benefit in terms of query performance. These redundant indexes can lead to increased storage requirements, slower data modification operations, and additional maintenance overhead without improving the efficiency of database queries. Let us look at the following example where there are two indexes for the same column `column1`:
```sql
CREATE INDEX idx_column1 ON table1(column1);
CREATE INDEX idx_column1_column2 ON table1(column1, column2);
```
To resolve the above indexing issue, let us combine redundant indexes into a single, more efficient composite index (single index for `table1`):
```sql
DROP INDEX idx_column1 ON table1;
```

### Composite index order

Sometimes, the order of columns in a composite (multi-column) index does not match the query conditions or the order of columns used in a` JOIN` operation. This can lead to suboptimal query performance.
```sql
SELECT * FROM products WHERE category_id = 1 AND brand_id = 2;
```
Let us create a composite index with columns in the correct order to match the existing index order. Note that this may not always be feasible, especially if you have many queries using different column orders.
```sql
CREATE INDEX idx_category_brand ON products(category_id, brand_id);
```

### Low cardinality index

Low cardinality index is an index on a column that has a relatively small number of unique values compared to the total number of rows in a table. In other words, a low cardinality column has few distinct values, and many rows share the same value. Indexing a column with low cardinality may not improve query performance.
```sql
CREATE INDEX idx_status ON orders(status);
```
Consider whether indexing such a column is necessary or beneficial. In some cases, indexing a low cardinality column may not provide significant benefits, and it may be more efficient to focus on indexing columns with higher selectivity (i.e., columns with many distinct values) or optimizing query design in other ways. Careful consideration should be given to the specific use cases and query patterns in your database to determine whether indexing a low cardinality column is appropriate.

### Index fragmentation

Index fragmentation refers to a condition where the physical storage of index data becomes disorganized or inefficient over time due to data modifications such as` INSERT`,` UPDATE`, and `DELETE` operations. As data in a table changes, the corresponding indexes may become less efficient, leading to performance degradation in query execution.
```sql
OPTIMIZE TABLE your_table;
```
Regularly optimize tables to rebuild indexes and regain performance.

### Large index

Large indexes consume significant storage space and can slow down data modification operations. Evaluate whether such an index is necessary and consider the trade-offs.
```sql
CREATE INDEX idx_large_column ON table1(large_column);
```

### Over-indexing

Having too many indexes can increase maintenance overhead and slow down data modifications.
```sql
CREATE INDEX idx_column1 ON table1(column1);
CREATE INDEX idx_column2 ON table1(column2);
CREATE INDEX idx_column3 ON table1(column3);
```
Review the necessity of each index and remove redundant or unused ones.

### Covering index or index-only query

Covering index is a type of database index that includes all the columns required to fulfill a specific query without the need to access the actual data rows in the table. In other words, a covering index "covers" a query by including all the information needed in the index itself, allowing the database engine to satisfy the query directly from the index structure. This can significantly improve query performance.
```sql
SELECT name, email FROM customers WHERE registration_date >= '2023-01-01';
```
Create a covering index that includes all the columns required by the query.
```sql
CREATE INDEX idx_registration_date ON customers(registration_date, name, email);
```

## MySQL indexes for database optimization

Creating and managing indexes in MySQL is a crucial aspect of database optimization. By understanding and utilizing the various index types and options available, you can ensure efficient data retrieval and optimal database performance. Always remember to monitor and adjust your indexes as your data and query patterns evolve. With this tutorial, you can now create index in MySQL easily and troubleshoot any indexing errors you may encounter.

## Towards next steps

Now that you know how to create indexes, if you want to drop an index, go to the next tutorial, [How to Drop an Index in MySQL using Drop Index and Alter Table Commands](https://popsql.com/learn-sql/mysql/how-to-drop-an-index-in-mysql)

## FAQs

### What is an index in MySQL?

An index in MySQL is a database structure that provides a quick and efficient way to look up rows in a table based on the values in one or more columns. Indexes enhance query performance by reducing the need for full table scans when filtering, sorting, or joining data.

### What types of indexes are available in MySQL?

MySQL supports various types of indexes, including single-column indexes, composite (multi-column) indexes, unique indexes, full-text indexes for text-based searches, and spatial indexes for geospatial data. The choice of index type depends on your specific use case and query patterns.

### When should I use indexes in MySQL?

You should use indexes in MySQL when you frequently query a table based on specific columns in `WHERE` clauses, `JOIN` conditions, or `ORDER BY` clauses. Indexes are most beneficial for read-heavy workloads but should be carefully selected to avoid over-indexing and index maintenance overhead.

### What are some common mistakes to avoid when using indexes in MySQL?

Common mistakes when using indexes include over-indexing (creating too many indexes), not monitoring index usage, neglecting index maintenance, using low cardinality columns for indexing, and not considering the order of columns in composite indexes. Proper indexing strategy and regular monitoring are crucial to avoid these pitfalls.

### How do I check if my queries are using indexes in MySQL?

You can use the `EXPLAIN` statement before your query to view the query execution plan, which provides information about which indexes are used (or not used) in the query. For example:
```sql
EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';
```
Additionally, MySQL provides tools and commands for monitoring index usage, such as the MySQL Performance Schema and the `SHOW INDEX` statement.

How to Add an Index

# How to Insert in MySQL
## Basic

The simplest way to insert a row in MySQL is to use the `INSERT INTO` command and specify values for all columns. If you have 10 columns, you have to specify 10 values and they have to be in order how the table was defined:

```sql
-- Assuming the users table has only three columns: first_name, last_name, and email, and in that order
INSERT INTO users VALUES ('John', 'Doe', 'john@doe.com');
```

## Specifying a Column List

It's optional, but specifying a column list before the `VALUES` keyword is highly recommended:

```sql
INSERT INTO users (first_name, last_name, email, birth_date, city, state)
VALUES ('John', 'Doe', 'john@doe.com','2000-01-01','Los Angeles','CA');
```

Having a column list has the following advantages:

*   You don't have to remember the column order as defined in the table.
*   You don't have to specify a value for all columns, just the required ones.
*   In case there are many columns, it is easier to match a value to the column it's intended for when you see it in the statement, rather than having to look at the table definition.
*   `INSERT` statements without a column lists are invalidated once a column is added or removed from the table. You need to modify your query to reflect the new or deleted column in order for them to work again.

## Using the SET Keyword

Although it is not frequently used, MySQL also allows the `SET` keyword in the `INSERT` statement. This is useful when there are many columns to be inserted because it's easier to read:

```sql
INSERT INTO users SET
  first_name='Vincent',
  last_name='Aviles',
  birth_date='1973-08-14',
  profession='Artist',
  educational_attainment='College Degree',
  city='Daly City',
  current_points=543,
  date_registred='2018-11-30';
```

The limitation of using the `SET` keyword is it can only be used to insert one row at a time.

## Inserting Multiple Rows

You can insert multiple rows in one `INSERT` statement by having multiple sets of values enclosed in parentheses:

```sql
INSERT INTO users (first_name, last_name)
VALUES
  ('John','Lennon'),
  ('Paul','McCartney'),
  ('George','Harrison'),
  ('Ringo','Starr');
```

You can also use `INSERT` with a `SELECT` command to copy data from an existing table. Note the `VALUES` keyword is omitted:

```sql
INSERT INTO beta_users (first_name, last_name)
SELECT first_name, last_name
FROM users
where beta = 1;
```

## Inserting JSON Values

Starting with version 5.7.8, MySQL supports JSON data types. If you want to insert into a JSON column, just wrap the valid JSON in a single quoted string:

```sql
INSERT INTO test_json
VALUES ('{"beta": true, "status": "for review", "test_count": 1}');
```

We also have a tutorial on [querying JSON columns](https://popsql.com/learn-sql/mysql/how-to-query-a-json-column-in-mysql/).

## Handling Conflicts/Duplicates

If inserting a row would violate a unique constraint, there are different ways to handle it, depending on your requirements.

Using `INSERT IGNORE` will quietly discard the row to be inserted:

```sql
INSERT IGNORE INTO products (product_id, product_name, stocks)
VALUES (1, 'VPN Product 1', 50);
```

See it in action:

```sql
mysql> create table products
    -> (product_id int not null primary key, product_name varchar(100), stocks int);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT IGNORE INTO products (product_id, product_name, stocks)
    -> VALUES (1, 'VPN Product 1', 50);
Query OK, 1 row affected (0.01 sec)

mysql> select * from products;
+------------+---------------+--------+
| product_id | product_name  | stocks |
+------------+---------------+--------+
|          1 | VPN Product 1 |     50 |
+------------+---------------+--------+
1 row in set (0.00 sec)

mysql> INSERT IGNORE INTO products (product_id, product_name, stocks)
    -> VALUES (1, 'VPN Product 1', 40);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from products;
+------------+---------------+--------+
| product_id | product_name  | stocks |
+------------+---------------+--------+
|          1 | VPN Product 1 |     50 |
+------------+---------------+--------+
1 row in set (0.00 sec)
```

Note that the stocks value did not change.

If you want to do an "upsert" (update the row it exists, otherwise insert), you can use `INSERT... ON DUPLICATE KEY UPDATE`:

```sql
INSERT INTO products (product_id, product_name, stocks)
VALUES (1, 'VPN Product 1', 45)
ON DUPLICATE KEY UPDATE stocks = VALUES(stocks);
```

In action:

```sql
mysql> INSERT INTO products (product_id, product_name, stocks)
    -> VALUES (1, 'VPN Product 1', 45)
    -> ON DUPLICATE KEY UPDATE stocks=VALUES(stocks);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from products;
+------------+---------------+--------+
| product_id | product_name  | stocks |
+------------+---------------+--------+
|          1 | VPN Product 1 |     45 |
+------------+---------------+--------+
1 row in set (0.00 sec)
```

MySQL also has the `REPLACE` keyword. When it detects a duplicate, it will delete the conflicting row and insert the new one.

```sql
REPLACE INTO products (product_id, product_name, stocks)
VALUES (1, 'VPN Product 1', 45)
ON DUPLICATE KEY UPDATE stocks = VALUES(stocks);
```

Be careful when using `REPLACE` vs `INSERT ON DUPLICATE KEY` because `REPLACE` deletes the entire row, so it affects foreign keys and can produce unexpected results if the table uses `AUTO_INCREMENT` to assign a value.

How to Insert

# How to Update in MySQL
To update all rows in a MySQL table, just use the `UPDATE` statement without a `WHERE` clause:

```sql
UPDATE products SET stocks=100;
```

You can also update multiple columns at a time:

```sql
UPDATE products SET stocks=100, available=true;
```

Usually you only want to update rows that match a certain condition. You do this by specifying a `WHERE` clause:

```sql
--This will update only one row that matches product_id=1
UPDATE products SET stocks=100, available=true
WHERE product_id=1;

--This will update multiple rows that match Category='Electronics'
UPDATE products SET stocks=50, available=true
WHERE category='Electronics';
```

How to Update

# How to Delete in MySQL
To delete rows in a MySQL table, use the `DELETE FROM` statement:

```sql
DELETE FROM products WHERE product_id=1;
```

The `WHERE` clause is optional, but you'll usually want it, unless you really want to delete every row from the table.

How to Delete

# How to Create a Table in MySQL
Here's an example of creating a `users` table in MySQL:

```sql
CREATE TABLE users (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, -- Auto incrementing IDs
  name VARCHAR(100), -- String column of up to 100 characters
  preferences JSON, -- JSON columns are great for storing unstructured data and are supported starting MySQL version 5.7.8
  created_at TIMESTAMP -- Always store time in UTC
);
```

Within the parentheses are called column definitions separated by commas. The minimum required fields for a column definition are column name and data type, which is what is shown above for columns `name`, `preferences`, and `created_at`. The id column has extra fields to identify it as the primary key column and use an auto-incrementing feature to assign it values.

This is also a chance to specify [not null constraints](https://popsql.com/learn-sql/mysql/how-to-add-a-not-null-constraint-in-mysql/), [default values](https://popsql.com/learn-sql/mysql/how-to-add-a-default-value-to-a-column-in-mysql/), and an optional `ENGINE` keyword:

```sql
CREATE TABLE users (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) not null,
  active boolean default true
) ENGINE=INNODB;
```

Storage engines are MySQL components that handle the SQL operations for different table types. This allows developers to extend capabilities of MySQL. When not specified, the default engine used is INNODB.

How to Create a Table

# How to Delete a Table in MySQL
Dropping a table in MySQL is simple:

```sql
DROP TABLE inactive_users;
```

Be careful - there's no "undo" for this!

How to Delete a Table

# MySQL: Rename a Table with Alter Table or Rename Table Command
MySQL offers two ways to rename tables. The first one uses the `ALTER TABLE` syntax:

```sql
ALTER TABLE old_table_name RENAME new_table_name;
```

The second way is to use `RENAME TABLE`:

```sql
RENAME TABLE old_table_name TO new_table_name;
```

`RENAME TABLE` offers more flexibility. It allows renaming multiple tables in one statement. This can be useful when replacing a table with a new pre-populated version:

```sql
RENAME TABLE products TO products_old, products_new TO products;
```

The above statement is executed left to right, so there's no conflict naming `products_new` to `products` since the existing table has already been renamed to `products_old`. Furthermore, this is done atomically.

How to Rename a Table

# How to Truncate a Table in MySQL
Be very careful with this command. It will empty the contents of your MySQL table and there is no undo. This is useful in development, but you'll rarely want to do this in production.

```sql
TRUNCATE TABLE table_name;

--the TABLE keyword is actually optional:
TRUNCATE table_name;
```

If the table contains an `AUTO_INCREMENT` column, the counter for it will also get reset. Note that this behavior is different vs other databases where you need to reset the counter yourself.

How to Truncate a Table

# How to Duplicate a Table in MySQL
You can duplicate or "clone" a table's contents by executing a `CREATE TABLE ... AS SELECT` statement:

```sql
CREATE TABLE new_table AS SELECT * FROM original_table;
```

Please be careful when using this to clone big tables. This can take a lot of time and server resources.

Note also that `new_table` inherits ONLY the basic column definitions, null settings and default values of the `original_table`. It does not inherit indexes and auto\_increment definitions.

To inherit all table definitions, use the `CREATE TABLE... LIKE` syntax:

```sql
CREATE TABLE new_table LIKE original_table;
```

This makes the structure of `new_table` exactly like that of `original_table`, but DOES NOT copy the data. To copy the data, you'll need `INSERT ... SELECT`:

```sql
INSERT INTO new_table SELECT * FROM original_table;
```

Again, be careful when doing this to big tables.

How to Duplicate a Table

# How to Add a Column to MySQL Table
Adding a column in MySQL involves using the `ALTER TABLE` command.

Here's an example of adding a `created_at` datetime column to your `users` table:

```sql
ALTER TABLE users ADD created_at DATETIME;
```

Adding a string (varchar) column with a not null constraint:

```sql
ALTER TABLE users ADD bio VARCHAR(100) NOT NULL;
```

Adding a boolean column with a default value:

```sql
ALTER TABLE users ADD active BOOLEAN DEFAULT TRUE;
```

MySQL offers extensive documentation on supported datatypes in their [documentation](https://dev.mysql.com/doc/refman/8.0/en/data-types.html).

How to Add a Column

# How to Remove a Column in MySQL
Dropping a column in MySQL involves using the `ALTER TABLE` command. The typical syntax is as follows:

```sql
ALTER TABLE table_name DROP COLUMN column_name;

--The COLUMN keyword is actually optional
ALTER TABLE table_name DROP column_name;
```

How to Remove a Column

# How to Change a Column Name in MySQL
## MySQL 5.6.x and 5.7.x

Renaming a column in MySQL involves using the `ALTER TABLE` command. For MySQL version 5.6 .x and 5.7.x, the typical syntax is as follows:

```sql
ALTER TABLE table_name CHANGE old_column_name new_column_name <column definition>;

ALTER TABLE products CHANGE product_name product_full_name VARCHAR(100) NOT NULL;
```

Note that you MUST restate the full column definition, otherwise undeclared attributes will go back to default. For example, not stating `NOT NULL` will result in the column allowing NULLS.

To ensure that you do not miss anything, you can use the `SHOW CREATE TABLE` command to see the full column definition:

```sql
mysql> SHOW CREATE TABLE products\G
*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `product_id` bigint(20) NOT NULL,
  `product_name` varchar(100) NOT NULL,

(The rest of the output is truncated for brevity)
```

Then use that as basis for the `ALTER TABLE` command.

## MySQL 8.0

While MySQL 8.0 accepts the above syntax, it also support an easier way:

```sql
ALTER TABLE products RENAME COLUMN product_name TO product_full_name;
```

This is a lot easier since there's no longer a need to restate the full column definition. But if you need to change both the column name and something in the definition, you can use the `ALTER TABLE ... CHANGE` command to do it in one go.

How to Change a Column Name

# How to Set a Column with Default Value in MySQL with Alter Table Command
To add a default value to a column in MySQL, use the `ALTER TABLE ... ALTER ... SET DEFAULT` command:

```sql
--Example: Products have a default stock of 0
ALTER TABLE products ALTER COLUMN stocks integer SET DEFAULT 0;

--Example: Products are available by default (removed optional COLUMN keyword)
ALTER TABLE products ALTER available SET DEFAULT true;
```

How to Set a Column with Default Value

# How to Remove a Default Value to a Column in MySQL
To remove a default value to a column in MySQL, use the `ALTER TABLE ... ALTER ... DROP DEFAULT` command:

```sql
--Example: Products have a default stock of 0
ALTER TABLE products ALTER COLUMN stocks integer DROP DEFAULT;

--Example: Products are available by default (removed optional COLUMN keyword)
ALTER TABLE products ALTER available DROP DEFAULT;
```

How to Remove a Default Value to a Column

# How to Add a Not Null Constraint in MySQL
Not null constraints are a great way to add another layer of validation to your data. Sure, you could perform this validation in your application layer, but shit happens: somebody will forget to add the validation, somebody will remove it by accident, somebody will bypass validations in a console and insert nulls, etc. The only way to really be sure is to enforce it in your column definition. If you're validating nulls on the database layer as well, you're protected 💪

To enforce `NOT NULL` for a column in MySQL, you use the `ALTER TABLE .... MODIFY` command and restate the column definition, adding the `NOT NULL` attribute.

```sql
--Example: Products have a default stock of 0
ALTER TABLE products MODIFY stocks INT NOT NULL;
```

Note that you MUST restate the full column definition, otherwise undeclared attributes will go back to default settings. For example, not restating the DEFAULT clause will unset the default value.

To ensure that you do not miss anything, you can use the `SHOW CREATE TABLE` command to see the full column definition:

```sql
mysql> SHOW CREATE TABLE products\G
*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `product_id` bigint(20) NOT NULL,
  `product_name` varchar(100) DEFAULT '',
  `stocks` int(11) DEFAULT '0',

(The rest of the output is truncated for brevity)
```

Use the current definition and add `NOT NULL` for the correct modification:

```sql
ALTER TABLE products MODIFY stocks INT NOT NULL DEFAULT 0;
```

How to Add a Not Null Constraint

# How to Remove a Not Null Constraint in MySQL
To remove a `NOT NULL` constraint for a column in MySQL, you use the `ALTER TABLE .... MODIFY` command and restate the column definition, removing the `NOT NULL` attribute.

```sql
--Example: Products have a default stock of 0
ALTER TABLE products MODIFY stocks INT;
```

Note that you MUST restate the full column definition, otherwise undeclared attributes will go back to default settings. For example, not restating the DEFAULT clause will unset the default value.

To ensure that you do not miss anything, you can use the `SHOW CREATE TABLE` command to see the full column definition:

```sql
mysql> SHOW CREATE TABLE products\G
*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `product_id` bigint(20) NOT NULL,
  `product_name` varchar(100) DEFAULT '',
  `stocks` int(11) NOT NULL DEFAULT '0',

(The rest of the output is truncated for brevity)
```

Use the current definition and remove the NOT NULL for the correct modification:

```sql
ALTER TABLE products MODIFY stocks INT DEFAULT 0;
```

How to Remove a Not Null Constraint

# How to Drop an Index in MySQL using Drop Index and Alter Table Commands
To drop a non-primary key index, use the `DROP INDEX` command:

```sql
DROP INDEX index_name ON table_name;
```

The syntax requires the table name to be specified because MySQL allows index names to be reused on multiple tables.

Primary keys in MySQL are always named `PRIMARY` (not case sensitive). But because `PRIMARY` is a reserved keyword, backticks are required when you use it in the `DROP INDEX` command.

```sql
--Enclose PRIMARY in backticks to refer to the name, not the reserved word
DROP INDEX `PRIMARY` ON table_name;
```

Alternatively, MySQL also allows to drop indexes using the `ALTER TABLE` command:

```sql
-- Drop a non-primary key index
ALTER TABLE table_name DROP INDEX index_name;

-- Drop the primary key. This time we are using the reserved words so no backticks
ALTER TABLE table_name DROP PRIMARY KEY;
```

How to Drop an Index

# How to Create a View in MySQL
Views allow to encapsulate or "hide" complexities, or allow limited read access to part of the data.

To create a view, use the `CREATE VIEW` command:

```sql
CREATE OR REPLACE VIEW view_name AS <select statement>;
```

While optional, the `OR REPLACE` part is frequently used so the the view is updated if already exists.

Some examples:

```sql
-- A view to show only beta users
CREATE VIEW beta_users_vw AS
SELECT * FROM users WHERE beta = 1;

-- A view to limit read access to only certain columns
CREATE VIEW users_basic_vw AS
SELECT first_name, last_name, telephone_number
FROM users;

-- A view for management so they only need to do a "SELECT * FROM top_20_customers_vw" instead of learning a complex SQL
CREATE OR REPLACE VIEW top_20_customers_vw AS
SELECT c.customer_name, sum(p.price*od.quantity) order_total
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_details od USING (order_id)
JOIN products p USING (product_id)
GROUP BY c.customer_name
ORDER BY order_total DESC
LIMIT 20;
```

You may notice all view names above end in `_vw`. This is an example convention some developers adopt to easily distinguish views from tables.

How to Create a View

# How to Drop a View in MySQL
To drop a view, use the `DROP VIEW` command:

```sql
DROP VIEW view_name;
```

How to Drop a View

# How to Alter Sequence in MySQL
Auto incrementing columns in tables start at 1 by default, but sometimes you may want them to start at a different number. These numbers are known as "sequences" in other databases but are implemented differently in MySQL. An auto incrementing column is part of table definition and is modified using the `ALTER TABLE` command.

First, you can check for the next value using the `SHOW CREATE TABLE COMMAND`:

```sql
mysql> SHOW CREATE TABLE users\G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
   <<other lines omitted for brevity>>
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2
```

To change it:

```sql
ALTER TABLE users AUTO_INCREMENT=1000;
```

How to Alter Sequence

# How to Query Date and Time in MySQL

MySQL has the following functions to get the current date and time:

```sql
SELECT now();  -- date and time
SELECT curdate(); --date
SELECT curtime(); --time in 24-hour format
```

To find rows between two dates or timestamps:

```sql
SELECT *
FROM events
where event_date between '2018-01-01' and '2018-01-31';

-- Can include time by specifying in YYYY-MM-DD hh:mm:ss format:
SELECT *
FROM events
WHERE event_date BETWEEN '2018-01-01 12:00:00' AND '2018-01-01 23:30:00';
```

To find rows created within the last week:

```sql
SELECT *
FROM events
WHERE event_date > date_sub(now(), interval 1 week);
```

There's also `DATE_ADD()`. For example, to find events scheduled between one week ago and 3 days from now:

```sql
SELECT *
FROM events
WHERE event_date BETWEEN date_sub(now(), interval 1 week) AND date_add(now(), interval 3 day);
```

You can extract part of a timestamp by applying the corresponding function:

```sql
SELECT year(now()); -- or month(), day(), hour(), minute(), second()
```

To get a day of week from a timestamp, use the `DAYOFWEEK()` function:

```sql
-- returns 1-7 (integer), where 1 is Sunday and 7 is Saturday
SELECT dayofweek('2018-12-12');

-- returns the string day name like Monday, Tuesday, etc
SELECT dayname(now());
```

To convert a timestamp to a unix timestamp (integer seconds):

```sql
-- This will assume time to be 12am
SELECT unix_timestamp('2018-12-09');

-- You can specify an exact timestamp to be converted down to the second
SELECT unix_timestamp('2018-12-09 14:53:21');

-- calling unix_timestamp without a parameter will be like calling it for current timestamp
SELECT unix_timestamp(); -- same as SELECT unix_timestamp(now());
```

To calculate the difference between two timestamps, convert them to unix timestamps then perform the subtraction:

```sql
-- show seconds between delivery and shipping timestamps
SELECT unix_timestamp(delivered_at) - unix_timestamp(shipped_at);
FROM deliveries;

-- convert computed difference to hh:mm:ss format:
SELECT sec_to_time(unix_timestamp(delivered_at) - unix_timestamp(shipped_at))
FROM deliveries;
```

Note that MySQL also has `DATEDIFF()` and `TIMEDIFF()` functions, but they can only used with purely date values or purely time values, respectively.

How to Query Date and Time

# How to Group by Time in MySQL

When you want to group by minute, hour, day, week, etc., it's tempting to just group by your timestamp column, however, then you'll get one group per second, which is likely not what you want. Instead, you need to "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc. The function you need here is `DATE_FORMAT`:

```sql
SELECT
   date_format(created_at,'%Y-%m-%d %H-%i'), -- leave out -%i if you want to group by hour
   count(1)
FROM users
GROUP BY 1;
```

Grouping by date is easier as you can just use the \`DATE() function:

```sql
SELECT
   date(created_at),
   count(1)
FROM users
GROUP BY 1;
```

If you don't have new users for every minute/hour/day, you're going to have gaps in your data. To have one row per interval, even when there's no data, you'll want to [generate data](https://popsql.com/learn-sql/mysql/how-to-avoid-gaps-in-data-in-mysql/) for it.

How to Group by Time

# How to Convert UTC to Local Time Zone in MySQL

MySQL does not store time zone data when storing timestamps. It also uses the host server time as basis for generating the output of `NOW()`;

To convert a timestamp from one time zone to another, you can use the `CONVERT_TZ` function:

```sql
-- using named time zones
SELECT CONVERT_TZ('2018-01-01 12:00:00','UTC','MET');

-- using offset time zones
SELECT CONVERT_TZ('2018-01-01 12:00:00','+00:00','+10:00');
```

How to Convert UTC to Local Time Zone

# How to Use Coalesce in MySQL

Say you're looking at a MySQL integer column where some rows are null:

```sql
select
  day,
  tickets
from stats;
```

```sql
    day     | tickets
------------+-------
 2018-01-01 |     1
 2018-01-02 |   null
 2018-01-03 |     3
```

Instead of having that null, you might want that row to be `0`. To do that, use the `ifnull` function, which returns the first non-null argument it's passed:

```sql
select
  day,
  ifnull(tickets, 0)
from stats;
```

```sql
    day     | tickets
------------+-------
 2018-01-01 |     1
 2018-01-02 |     0
 2018-01-03 |     3
```

How to Use Coalesce

# How to Calculate Percentiles in MySQL
MySQL is still behind other databases when it comes to analytical/window functions. But there are ways to get things done. For example, to get a top-down percentile ranking of film lengths from the [Sakila Sample Database](https://dev.mysql.com/doc/sakila/en/):

```sql
SELECT
  f.title,
  ROUND(100.0 * (SELECT COUNT(*) FROM film AS f2 WHERE f2.length <= f.length) / totals.film_count, 1) AS percentile
FROM film f
CROSS JOIN (
  SELECT COUNT(*) AS film_count
  FROM film
) AS totals
ORDER BY percentile DESC;
```

How to Calculate Percentiles

# How to Get the First Row per Group in MySQL

Starting with version 8.0.2, MySQL now offers a way to easily number rows. For example, if we want to list the number the films in the [Sakila Sample Database](https://dev.mysql.com/doc/sakila/en/), grouped by rating and ordered by release year:

```sql
SELECT *,
  row_number() OVER (PARTITION BY rating ORDER BY release_year) as row_num
FROM film;
```

Now if you only want to get the first row for each rating, you can use a [common table expression](https://popsql.com/learn-sql/mysql/how-to-write-a-common-table-expression-in-mysql/):

```sql
WITH _films AS (
  SELECT *,
    row_number() OVER (PARTITION BY rating ORDER BY release_year) as row_num
  FROM film;
)
SELECT *
FROM _films
WHERE row_num = 1;
```

How to Get the First Row per Group

# How to Avoid Gaps in Data in MySQL

If you're [grouping by time](https://popsql.com/learn-sql/mysql/how-to-group-by-time-in-mysql/) and you don't want gaps in your report data, you need to generate a series of time values and use it to do an outer join with your data. Prior to MySQL 8, you can do this using variables. In the following example we extracted the number of rentals per hour from the [Sakila Sample Database](https://dev.mysql.com/doc/sakila/en/):

```sql
-- The first line is to make the first value of statement below 0:00:00, not 1:00:00
-- LIMIT 720 gives 30 days worth of hourly values
SET @n:=('2005-05-25' - INTERVAL 1 HOUR);
SELECT
  hours.this_hour,
  count(rental.rental_id)
FROM
  (SELECT (SELECT @n:= @n + INTERVAL 1 HOUR) this_hour
   FROM inventory LIMIT 720) hours
LEFT JOIN rental ON (hours.this_hour=date_format(rental.rental_date,'%Y-%m-%d %H:00:00'))
GROUP BY hours.this_hour;
```

There are serious limitations with the above method. For one thing, you may ask what does the `inventory` table have to do with rentals? It does not have anything to do with it directly. The `inventory` table got chosen because it has a few thousand rows and we needed one that has at least 720 to generate our series of time values. In reality any table with a large number of rows can be used for this. But what if there is none available?

Fortunately, starting with MySQL 8, you can instead use a [common table expression](https://popsql.com/learn-sql/mysql/how-to-write-a-common-table-expression-in-mysql/):

```sql
WITH RECURSIVE my_hours AS
(
  SELECT 0 as inc
  UNION ALL
  SELECT 1+inc
  FROM my_hours WHERE inc<=720
)
SELECT
  hours.this_hour,
  count(rental.rental_id)
FROM
  (SELECT '2005-05-25' + interval inc hour as this_hour
   FROM my_hours) as hours
LEFT JOIN rental ON (hours.this_hour=date_format(rental.rental_date,'%Y-%m-%d %H:00:00'))
GROUP BY hours.this_hour;
```

How to Avoid Gaps in Data

# How to Do Type Casting in MySQL

By default, MySQL is not strict with type casting. For example, adding a numeric value in string quotes to another numeric value with not give the usual errors other databases and programming languages will give:

```sql
mysql> select 1 + '1';
+---------+
| 1 + '1' |
+---------+
|       2 |
+---------+
```

However, should the need arise, you can use the `CAST()` function to force the type of a value. You can cast to the following types: `BINARY`, `CHAR`, `DATE`, `DATETIME`, `TIME`,`DECIMAL`, `SIGNED`, `UNSIGNED`.

```sql
-- cast float to unsigned integer
SELECT CAST(1.0 AS UNSIGNED);

-- cast string to date
SELECT CAST('2018-12-12' AS DATE);

-- cast string to decimal
SELECT CAST('12.345' AS DECIMAL(5,3));

-- cast string to time:
SELECT CAST('12:45' AS TIME);
```

How to Do Type Casting

# How to Write a Common Table Expression in MySQL

Common table expressions (CTEs) are a great way to break up complex queries. MySQL started supporting this in version 8. Here's a simple query to illustrate how to write a CTE:

```sql
with beta_users as (
  select *
  from users
  where beta is true
)
select events.*
from events
inner join beta_users on beta_users.id = events.user_id;
```

You can find more complex examples of using CTE's in [How to Avoid Gaps in Data in MySQL](https://popsql.com/learn-sql/mysql/how-to-avoid-gaps-in-data-in-mysql/) and in [Calculating Cumulative Sums in MySQL](https://popsql.com/learn-sql/mysql/how-to-calculate-cumulative-sum-running-total-in-mysql).

How to Write a Common Table Expression

# How to Import a CSV in MySQL

Importing a CSV into MySQL requires you to [create a table](https://popsql.com/learn-sql/mysql/how-to-create-a-table-in-mysql) first. [Duplicating an existing table's structure](https://popsql.com/learn-sql/mysql/how-to-duplicate-a-table-in-mysql) might be helpful here too.

This tutorial focuses on the `LOAD DATA` command which is available if you use the MySQL command line client. By default it assumes your source data is delimited using tabs (vs commas), has strings that contain the tab delimiter in single quotes, and each row of data ends with the newline (`\n`) character.

First you need to connect to MySQL:

```sql
$ mysql -u username -p -h database.host.name database_name
```

The `-p` option will result in the user being prompted for a password. `-h database.host.name` is optional if the MySQL server is on the same host. Also optional is `database_name` but it is better to specify the database/schema name now rather than later.

Here are example uses of the `LOAD DATA` command:

```sql
-- source file is located in the MySQL Server data directory, is tab delimited, and the columns exactly match the table
LOAD DATA INFILE 'user_data.tsv' INTO TABLE users;

-- source file is local, is comma delimited and contains only some columns. Database is remote
LOAD DATA LOCAL INFILE '/tmp/user_data.csv'
  INTO TABLE users (first_name, last_name, email)
  FIELDS TERMINATED BY ',';

-- source file is comma delimited, strings are enclosed by double quotes, lines are terminated by carriage return/newline pairs, has a single header row that has to be ignored
LOAD DATA INFILE 'data.txt' INTO TABLE my_table_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

-- Ignore a column in the source file by assigning it to a user variable and not assigning the variable to a table column
LOAD DATA INFILE 'data.txt'
  INTO TABLE my_table_name (column1, @dummy, column2, @dummy, column3);

-- Populate a column the source file does not have any data for using the SET clause
LOAD DATA INFILE 'file_no_timestamps.txt'
  INTO TABLE my_table_with_timestamps
  (column1, column2)
  SET timestamp_column = CURRENT_TIMESTAMP;
```

How to Import a CSV

# How to Compare Two Values When One is Null in MySQL

Imagine you're comparing two MySQL columns and you want to know how many rows are different. No problem, you think:

```sql
SELECT count(1)
FROM items
WHERE width != height;
```

Not so fast. If some of the widths or heights are null, they won't be counted! Surely that wasn't your intention. That's where you need a null-aware operator like `<=>`:

```sql
SELECT count(1)
FROM items
WHERE NOT (width <=> height);
```

Because `<=>` is actually a null-aware version of the equal operator, we need to negate it with `NOT`. Now, your count will be "null aware" and you'll get the result you want.

How to Compare Two Values When One is Null

# How to Write a Case Statement in MySQL

Case statements are useful when you're dealing with multiple `IF` statements in your select clause. It comes in two forms:

```sql
SELECT
  CASE
    WHEN score < 70 THEN 'failed'
    WHEN score BETWEEN 70 AND 80 THEN 'passed'
    WHEN score BETWEEN 81 AND 90 THEN 'very good'
    ELSE 'outstanding'
  END AS performance
FROM test_scores;

SELECT
  CASE grade
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Good'
    WHEN 'C' THEN 'Needs Improvement'
    ELSE 'Failed'
  END AS grade_interpretation
FROM grades;
```

How to Write a Case Statement

# How to Query a JSON Column in MySQL

Starting with version 5.7.8, MySQL supports JSON columns. This gives the advantage of storing and querying unstructured data. Here's how you can query a JSON column in MySQL:

```sql
-- Getting the params.name string value from events table
SELECT params->>'$.name'
FROM events;

-- Getting rows where the browser.name is Chrome
-- This also shows the difference of using -> vs ->>
-- Using -> will cause strings to be enclosed in quotes
SELECT browser->>'$.name', browser->'$.name'
FROM events
WHERE browser->>'$.name' = 'Chrome';

-- Give me the first index of a JSON array
SELECT properties->>'$.my_array[0]'
FROM events;

-- Going deeper to get the X resolution only
SELECT properties->'$.resolution.x'
FROM events;
```

How to Query a JSON Column

# How to Have Multiple Counts in MySQL

To do multiple counts in one query in MySQL, you can combine `COUNT()` with `IF()`:

```sql
SELECT
  COUNT(1), -- Count all users
  COUNT(IF(gender='male', 1, 0)), -- Count male users
  COUNT(IF(beta=true, 1, 0)) -- Count beta users
  COUNT(IF(active=true AND beta = false, 1, 0)) -- Count active non-beta users
FROM users;
```

How to Have Multiple Counts

# How to Calculate Cumulative Sum-Running Total in MySQL

Let's say for our MySQL DVD rental database, we want to see a hockey stick graph of our cumulative rentals by day. First, we'll need a table with a day column and a count column:

```sql
SELECT
  date(rental_date) as day,
  count(rental_id) as rental_count
FROM rental
GROUP BY day;

day        | rental_count
-----------+--------------
2005-05-24 | 8
2005-05-25 | 137
2005-05-26 | 174
2005-05-27 | 166
2005-05-28 | 196
```

Then we use this to do our cumulative totals. Before MySQL version 8 you can use variables for this:

```sql
SELECT t.day,
       t.rental_count,
       @running_total:=@running_total + t.rental_count AS cumulative_sum
FROM
( SELECT
  date(rental_date) as day,
  count(rental_id) as rental_count
  FROM rental
  GROUP BY day ) t
JOIN (SELECT @running_total:=0) r
ORDER BY t.day;

day        | rental_count | cumulative_sum
-----------+--------------+----------------
2005-05-24 | 8            |	8
2005-05-25 | 137          |	145
2005-05-26 | 174          |	319
2005-05-27 | 166          |	485
2005-05-28 | 196          |	681
```

For MySQL 8 you can use a windowed `SUM()` and also a [MySQL common table expression (CTE)](https://popsql.com/learn-sql/mysql/how-to-write-a-common-table-expression-in-mysql/) instead of a subquery to make it more readable, the result is the same:

```sql
with data as (
  select
    date(rental_date) as day,
    count(rental_id) as rental_count
  from rental
  group by day
)

select
  day,
  rental_count,
  sum(rental_count) over (order by day) as cumulative_sum
from data;
```

MySQL

Ready for a modern SQL editor?