MySQL: Connecting, Creating, Displaying Databases

• Guide reformatted with SQL commands in code blocks and explanations.

Connecting to a MySQL Server

• Connect to a MySQL server using the mysql command-line client with a username and password, the password will be prompted after you run the command: mysql -u [username] -p
• Connect directly to a specific database on the MySQL server with the command: mysql -u [username] -p [database]
• Export data from a database using the mysqldump tool, redirect the output to a file with the command: mysqldump -u [username] -p [database] > data_backup.sql
• Exit the MySQL client with the command: quit or exit
• See a list of available commands within the client using the command: help

Creating and Displaying Databases

• Create a new database named zoo with the command: CREATE DATABASE zoo;
• List all databases on the server with the command: SHOW DATABASES;
• Switch to using the zoo database with the command: USE zoo;
• Delete the zoo database with the command: DROP DATABASE zoo;
• List all tables in the current database with the command: SHOW TABLES;
• Get detailed information about a table named animal (e.g., column names, data types, default values) with the command: DESCRIBE animal;

Creating Tables

• A simple table named habitat with an id and name column:

CREATE TABLE habitat (
id INT,
name VARCHAR(64)
);

• To automatically increment the id with each new record, use AUTO_INCREMENT, the column must be a primary or unique key:

CREATE TABLE habitat (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(64)
);

• Create a table animal with a foreign key referencing the habitat table:

CREATE TABLE animal (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(64),
species VARCHAR(64),
age INT,
habitat_id INT,
FOREIGN KEY (habitat_id) REFERENCES habitat(id)
);

Modifying Tables

• The ALTER TABLE statement is used to modify an existing table's structure.
• Rename the animal table to pet: ALTER TABLE animal RENAME pet;
• Add a new column name to the animal table:

ALTER TABLE animal
ADD COLUMN name VARCHAR(64);

• Rename the id column to identifier: ALTER TABLE animal RENAME COLUMN id TO identifier;
• Change the data type of the name column to VARCHAR(128):

ALTER TABLE animal
MODIFY COLUMN name VARCHAR(128);

• Delete the name column from the animal table:

ALTER TABLE animal
DROP COLUMN name;

• Completely delete the animal table: DROP TABLE animal;

Querying Data

• The SELECT command retrieves data from a table.
• Single-table query that calculates the average age of animals by species, excluding id = 3, filtering groups with an average age greater than 3, and sorting by average age in descending order:

SELECT species, AVG(age) AS average_age
FROM animal
WHERE id != 3
GROUP BY species
HAVING AVG(age) > 3
ORDER BY AVG(age) DESC;

• Multiple-table query, this example joins the city and country tables (using INNER, LEFT, or RIGHT JOIN) based on a matching country_id:

SELECT city.name, country.name
FROM city
[INNER | LEFT | RIGHT] JOIN country
ON city.country_id = country.id;

• Basic math can be performed with +, -, *, /.
• Calculate the number of seconds in a week:

SELECT 60 * 60 * 24 * 7;  -- result: 604800

Aggregation and Grouping

• Aggregate functions summarize data within groups.
• Count all rows in the animal table:

SELECT COUNT(*)
FROM animal;

• Count non-NULL values in the name column:

SELECT COUNT(name)
FROM animal;

• Count unique values in the name column:

SELECT COUNT(DISTINCT name)
FROM animal;

• Count animals grouped by species:

SELECT species, COUNT(id)
FROM animal
GROUP BY species;

• Calculate the average, minimum, and maximum ages of animals grouped by habitat:

SELECT habitat_id, AVG(age), MIN(age), MAX(age)
FROM animal
GROUP BY habitat;

Inserting Data

• The INSERT command adds data to a table.
• Insert multiple rows into the habitat table: INSERT INTO habitat VALUES (1, 'River'), (2, 'Forest');
• Insert a single row, specifying only the name column (other columns get default values or NULL): INSERT INTO habitat (name) VALUES ('Savanna');

Updating Data

• The UPDATE command modifies existing data.
• Update the species and name of an animal with id = 2:

UPDATE animal
SET
species = 'Duck',
name = 'Quack'
WHERE id = 2;

Deleting Data

• The DELETE command removes rows from a table.
• Delete the row where id = 1 from the animal table: DELETE FROM animal WHERE id = 1;
• Delete all data from the animal table (faster than DELETE for wiping everything): TRUNCATE TABLE animal;

Casting

• The CAST() function changes a value's data type.
• Cast a decimal number to a signed integer:

SELECT CAST(1234.567 AS signed);  -- result: 1235

• Cast a column to a double data type: SELECT CAST(column AS double);

Text Functions

• Fetch city names that are not 'Berlin':

SELECT name
FROM city
WHERE name != 'Berlin';

Text Operators

• Fetch city names starting with 'P' or ending with 's':

SELECT name
FROM city
WHERE name LIKE 'P%' OR name LIKE '%s';

• Fetch city names with exactly one letter followed by 'ublin' (e.g., Dublin, Lublin):

SELECT name
FROM city
WHERE name LIKE '_ublin';

Concatenation

• Concatenate two strings: SELECT CONCAT('Hi ', 'there!'); -- result: Hi there!
• If any argument is NULL, the result is NULL: SELECT CONCAT('Great ', 'day', NULL); -- result: NULL
• Concatenate with a separator (e.g., a space): SELECT CONCAT_WS(' ', 1, 'Olivier', 'Norris'); -- result: 1 Olivier Norris

Other Useful Text Functions

• Count characters in a string: SELECT LENGTH('LearnSQL.com'); -- result: 12
• Convert a string to lowercase: SELECT LOWER('LEARNSQL.COM'); -- result: learnsql.com
• Convert a string to uppercase: SELECT UPPER('LearnSQL.com'); -- result: LEARNSQL.COM
• Extract part of a string (from position 9 to the end): SELECT SUBSTRING('LearnSQL.com', 9); -- result: .com
• Extract a specific length (first 5 characters): SELECT SUBSTRING('LearnSQL.com', 1, 5); -- result: Learn
• Replace part of a string: SELECT REPLACE('LearnSQL.com', 'SQL', 'Python'); -- result: LearnPython.com

Numeric Functions

• Get the remainder of a division: SELECT MOD(13, 2); -- result: 1
• Round a number to the nearest integer: SELECT ROUND(1234.56789); -- result: 1235
• Round to three decimal places: SELECT ROUND(1234.56789, 3); -- result: 1234.568
• Round a number up to the next integer:
  • SELECT CEIL(13.1); -- result: 14
  • SELECT CEIL(-13.9); -- result: -13
• Round a number down to the previous integer:
  • SELECT FLOOR(13.8); -- result: 13
  • SELECT FLOOR(-13.2); -- result: -14
• Truncate a number toward 0 (removing decimals or to a specified precision):
  • SELECT TRUNCATE(13.56, 0); -- result: 13
  • SELECT TRUNCATE(-13.56, 1); -- result: -13.5
• Get the absolute value of a number: SELECT ABS(-12); -- result: 12
• Get the square root of a number: SELECT SQRT(9); -- result: 3

Useful NULL Functions

• Fetch city names where the rating is not NULL:

SELECT name
FROM city
WHERE rating IS NOT NULL;

• Replace NULL with a meaningful value:

SELECT domain, COALESCE(domain, 'domain missing')
FROM contacts;

• Avoid division by zero by converting 0 to NULL:

SELECT last_month, this_month,
this_month * 100.0 / NULLIF(last_month, 0) AS better_by_percent
FROM video_views;

Date and Time

• MySQL supports five main time-related types: DATE, TIME, DATETIME, TIMESTAMP, and YEAR.
• Get the current time: SELECT CURRENT_TIME;
• Get the current date: SELECT CURRENT_DATE;
• Get the current timestamp (date and time): SELECT NOW();

Creating Values

• Create a date, time, or datetime by casting strings: SELECT CAST('2021-12-31' AS date), CAST('15:31' AS time), CAST('2021-12-31 23:59:29' AS datetime);
• In simple conditions, casting can be skipped:

SELECT airline, flight_no, departure_time
FROM airport_schedule
WHERE departure_time < '12:00';

Extracting Parts of Dates

• Extract the year, month, or day from a date:
  • SELECT YEAR(CAST('2021-12-31' AS date)); -- result: 2021
  • SELECT MONTH(CAST('2021-12-31' AS date)); -- result: 12
  • SELECT DAY(CAST('2021-12-31' AS date)); -- result: 31

Date Arithmetic

• Add or subtract an interval from a DATE:
  • SELECT ADDDATE('2021-10-31', INTERVAL 2 MONTH); -- result: '2021-12-31'
  • SELECT ADDDATE('2014-04-05', INTERVAL -3 DAY); -- result: '2014-04-02'
• Add or subtract an interval from a TIMESTAMP or DATETIME:
  • SELECT TIMESTAMPADD(MONTH, 2, '2014-06-10 07:55:00'); -- result: '2014-08-10 07:55:00'
  • SELECT TIMESTAMPADD(MONTH, -2, '2014-06-10 07:55:00'); -- result: '2014-04-10 07:55:00'
• Add or subtract TIME from a DATETIME:
  • SELECT ADDTIME('2018-02-12 10:20:24', '12:43:02'); -- result: '2018-02-12 23:03:26'
  • SELECT ADDTIME('2018-02-12 10:20:24', '-12:43:02'); -- result: '2018-02-11 21:37:22'
• Find the difference between two dates (in days): SELECT DATEDIFF('2015-01-01', '2014-01-02'); -- result: 364
• Find the difference between two times: SELECT TIMEDIFF('09:30:00', '07:55:00'); -- result: '01:35:00'
• Find the difference between two datetimes in weeks: SELECT TIMESTAMPDIFF(WEEK, '2018-02-26', '2018-03-21'); -- result: 3