MySQL Cheatsheet Part 2

To change a table name:
ALTER TABLE cars RENAME motors;

To add a column to the table:
ALTER TABLE cars
ADD COLUMN name VARCHAR(64);

To change a column name: ALTER TABLE cars
RENAME COLUMN id TO identifier;

To change a column data type:
ALTER TABLE cars

MODIFY COLUMN name VARCHAR(128);

To delete a column:
ALTER TABLE cars
DROP COLUMN name;a

To delete a table:
DROP TABLE cars;

To select data from a table, use the SELECT command.

An example of a single-table query:
SELECT species, AVG(age) AS average_age
FROM student
WHERE id != 3
GROUP BY species
HAVING AVG(age) > 3
ORDER BY AVG(age) DESC;


An example of a multiple-table query:
SELECT city.name, country.name
FROM city
[INNER | LEFT | RIGHT] JOIN country
ON city.country_id = country.id;
Use +, -, *, / to do some basic math.
To get the number of seconds in a week:
SELECT 60 * 60 * 24 * 7; -- result: 604800

- AVG(expr) − average value of expr for the group.
- COUNT(expr) − count of expr values within the group. MAX(expr) − maximum value of expr values within the group.
- MIN(expr) − minimum value of expr values within the group.
- SUM(expr) − sum of expr values within the group.

To count the rows in the table:
SELECT COUNT(*) FROM cars;

To count the non-NULL values in a column:
SELECT COUNT(name) FROM cars;

To count unique values in a column:
SELECT COUNT(DISTINCT name) FROM cars;

To count the cars by vendor:
SELECT vendor, COUNT(id)
FROM cars
GROUP BY vendor;

To get the average, minimum, and maximum ages by type :
SELECT type_id, AVG(age),
MIN(age), MAX(age)
FROM cars
GROUP BY type;

From time to time, you need to change the type of a value.
Use the CAST() function to do this.
In MySQL, you can cast to these data types:
CHAR     NCHAR  BINARY DATE  DATETIME
DECIMAL  DOUBLE FLOAT  REAL  SIGNED
UNSIGNED TIME   YEAR   JSON  spatial_type
To get a number as a signed integer:
SELECT CAST(1234.567 AS signed); -- result: 1235
To change a column type to double:
SELECT CAST(column AS double);

FILTERING THE OUTPUT

To fetch the city names that are not Berlin:
SELECT name
FROM city WHERE name != 'Berlin';

TEXT OPERATORS
To fetch the city names that start with a 'P' or end with an 's':
SELECT name
FROM city
WHERE name LIKE 'P%' OR name LIKE '%s';
To fetch the city names that start with any letter followed by 'ublin' (like Dublin in Ireland or Lublin in Poland):
SELECT name FROM city WHERE name LIKE '_ublin';

CONCATENATION
Use the CONCAT() function to concatenate two strings:
SELECT CONCAT('Hi ', 'there!'); -- result: Hi there!
If any of the string is NULL, the result is NULL:
SELECT CONCAT(Great ', 'day', NULL); -- result: NULL
MySQL allows specifying a separating character (separator) using the CONCAT_WS() function. The separator is placed between the concatenated values:
SELECT CONCAT_WS(' ', 1, 'Olivier', 'Norris'); -- result: 1 Olivier Norris OTHER USEFUL TEXT FUNCTIONS
To get the count of characters in a string:
SELECT LENGTH('LearnSQL.com'); -- result: 12
To convert all letters to lowercase:
SELECT LOWER('LEARNSQL.COM'); -- result: learnsql.com
To convert all letters to uppercase:
SELECT UPPER('LearnSQL.com'); -- result: LEARNSQL.COM
To get just a part of a string:
SELECT SUBSTRING('LearnSQL.com', 9);
-- result: .com
SELECT SUBSTRING('LearnSQL.com', 1, 5); -- result: Learn
To replace a part of a string:
SELECT REPLACE('LearnSQL.com', 'SQL',
'Python');
-- result: LearnPython.com