MySQL Notes

1
SHOW DATABASES;

1
CREATE DATABASE <database_name>;

1
DROP DATABASE <database_name>;

1
USE <database_name>;

1
SELECT database();

1
SHOW TABLES;

1
2
3
4
5
CREATE TABLE <table_name>
(
  <column_name> <data_type> <constraint>,
  ...
);

1
2
SHOW COLUMNS FROM <table_name>;
DESC <table_name>;

1
DROP TABLE <table_name>,...;

1
2
INSERT INTO <table_name>(<column_name>,...) VALUES(<value>,...),...;
INSERT INTO <table_name> SET <column_name>=<value>,...;

1
SELECT <column_name> AS <alias_name>,... FROM <table_name> WHERE <condition>;

1
UPDATE <table_name> SET <column_name>=<value>,... WHERE <condition>;

1
DELETE FROM <table_name> WHERE <condition>;

1
SELECT DISTINCT <column_name> FROM <table_name>;

1
SELECT <column_name> FROM <table_name> ORDER BY {<column_name> | <column_number>};

1
SELECT <column_name> FROM <table_name> LIMIT [<start_index>, ]<num_of_entries>;

1
SELECT <column_name> FROM <table_name> WHERE <condition>;

1
SELECT COUNT({* | <column_name>}) FROM <table_name> GROUP BY <column_name> HAVING <condition>;

1
SELECT {MIN | MAX | SUM | AVG}(<column_name>) FROM <table_name> GROUP BY <column_name> HAVING <condition>;

1
CONCAT(<column_name>,...);

1
SUBSTRING(<column_name>, <start_index>[, <stop_index>]);

1
REPLACE(<column_name>, <pattern_string>, <replace_string>);

1
REVERSE(<column_name>);

1
CHAR_LENGTH(<column_name>);

1
2
UPPER(<column_name>);
LOWER(<column_name>);

1
IFNULL(<column_name>, <default_value>)

1
2
ROUND(X)
ROUND(X, D)

1
IF(<condition>, <yes_value>, <no_value>)

1
2
3
4
5
6
CREATE TABLE comments
(
  context VARCHAR(255),
  created_at TIMESTAMP DEFAULT NOW(),
  changed_at TIMESTAMP DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP
);

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE <table_name>
(
  <column_name> <data_type> NOT NULL,
  <column_name> <data_type> UNIQUE,
  <column_name> <data_type> PRIMARY KEY,
  <column_name> <data_type> FOREIGN KEY REFERENCES <table_name>(<column_name>),
  <column_name> <data_type> DEFAULT <default_value>,
  <column_name> <data_type> CHECK (<condition>),
  PRIMARY KEY (<column_name>,...),
  FOREIGN KEY (<column_name>) REFERENCES <table_name>(<column_name>),
  CONSTRAINT <constraint_name> {CHECK ... | PRIMARY ... | FOREIGN ... },
  ...
);

1
SELECT <column_name> FROM <table_name> WHERE <condition> {AND | OR} <condition>;

1
SELECT <column_name> FROM <table_name> WHERE <column_name> LIKE <pattern_string>;

1
SELECT <column_name> FROM <table_name> WHERE <column_name> BETWEEN <min_value> AND <max_value>;

1
SELECT <column_name> FROM <table_name> WHERE <column_name> IN (<value>,...);

1
CASE WHEN <condition> THEN <value> ... ELSE <value> END

1
SELECT <column_name> FROM <table_name> JOIN <table_name> ON <condition>;

1
SELECT <column_name> FROM <table_name> {LEFT | RIGHT} JOIN <table_name> ON <condition>;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
  username
FROM
  likes
  INNER JOIN users ON likes.user_id = users.id
GROUP BY
  user_id
HAVING
  COUNT(*) = (
    SELECT
      COUNT(*)
    FROM
      photos
  );

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE TABLE students
(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE papers
(
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  grade INT,
  student_id INT,
  FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE
);

SELECT
  name,
  IFNULL(AVG(grade), 0) AS average,
  CASE
    WHEN IFNULL(AVG(grade), 0) >= 75 THEN 'PASSING'
    ELSE 'FAILING'
  END AS passing_status
FROM
  students
  LEFT JOIN papers ON students.id = papers.student_id
GROUP BY
  students.id
ORDER BY
  average DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE reviewers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE series (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  released_year YEAR(4),
  genre VARCHAR(255)
);

CREATE TABLE reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  rating DECIMAL(2, 1),
  series_id INT,
  reviewer_id INT,
  FOREIGN KEY (series_id) REFERENCES series(id),
  FOREIGN KEY (reviewer_id) REFERENCES reviewers(id)
);

SELECT
  title,
  rating,
  name
FROM
  reviews
  JOIN reviewers ON reviews.reviewer_id = reviewers.id
  JOIN series ON reviews.series_id = series.id
ORDER BY
  title,
  rating DESC;