MySQL

Common Commands

Show Tables

show tables;

Counting

• https://www.mysqltutorial.org/mysql-count/

SELECT COUNT(*) FROM my_table;
SELECT COUNT(*) FROM my_table WHERE my_col_val = 2;

Installation and Verification

• Macs do not come with MySQL installed.
• MAMP is a popular local web host which includes MySQL.

Check the MySQL version

• Check the MySQL version running in MAMP:

/Applications/MAMP/Library/bin/mysql --version

• Check the MySQL version running in XAMPP:

/Applications/XAMPP/xamppfiles/bin/mysql --version

MySQL Keys, Indexes, and Constriaints

• https://www.w3resource.com/mysql/creating-table-advance/constraint.php
• https://www.mysqltutorial.org/mysql-index/
• https://www.mysqltutorial.org/mysql-primary-key/

A primary key is a column or a set of columns that uniquely identifies each row in the table. The primary key follows these rules:

• A primary key must contain unique values. If the primary key consists of multiple columns, the combination of values in these columns must be unique.
• A primary key column cannot have NULL values. Any attempt to insert or update NULL to primary key columns will result in an error. Note that MySQL implicitly adds a NOT NULL constraint to primary key columns.
• A table can have one and only one primary key.

KEY is normally a synonym for INDEX.
The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition.
This was implemented for compatibility with other database systems.

Dates and Datetime

• https://javorszky.co.uk/2016/06/06/today-i-learned-about-mysql-and-timezones/
• https://stackoverflow.com/questions/1116529/best-practice-for-storing-the-date-in-mysql-from-php
• https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

Using DATETIME makes using internal mysql date functions possible.

If you pass in 0 as DATETIME, you will get 0000-00-00 00:00:00. That’s because 0 is treated as false. Null becomes null, and any int becomes null due to them being not valid datetime values.

This type does not hold timezone information, so what you put in is what you’re going to get out, regardless of timezones.

The good thing here is that if your code assumes that all time information stored in the database is in UTC, this is perfect. That however means you need to make sure your code passes in UTC timezoned string, and when it reads back it will turn it back into whatever normal timezone the site / application has.

MySQL’s DATETIME field lacks a timezone – always store dates in UTC (i.e. avoid NOW() – use UTC_TIMESTAMP()) otherwise you’re going to get into a big mess as you try to internationalize your app and for whatever reason change your servers timezone.

SELECT NOW();
# 2020-03-10 10:49:10

SELECT UTC_TIMESTAMP();
# 2020-03-10 16:49:10

Tests

• The following image contains the text for a SQL Injection attack test.
• WARNING Pasting the following code into a form and submitting it could destroy your database.