Skip to main content

MySQL Bootcamp

Lesson Objectives#

  1. What is a DB?
  2. What are the different ways to store data?
  3. What are the advantages of a database?
  4. What does SQL Stand for? What is it?
  5. Diagram MySQL structure
-- CREATE DB/TABLESSHOW DATABASES;CREATE DATABASE our_database_name;USE our_database_name;CREATE TABLE people (first_name VARCHAR(20), age INT);DESCRIBE people;
-- CRUDINSERT INTO people (first_name, age) VALUES ('Matt' , 34);SELECT age FROM people;SELECT * FROM people;UPDATE people SET weight = 300 WHERE first_name = 'Bill';DELETE FROM people WHERE first_name = "Bill";
-- OPERATORSSELECT * FROM people WHERE age != 63;SELECT * FROM people WHERE age < 63;SELECT * FROM people WHERE age > 63;SELECT * FROM people WHERE age >= 63;SELECT * FROM people WHERE age <= 63;SELECT * FROM people WHERE first_name first_name LIKE "%Charlie%";SELECT * FROM people WHERE first_name NOT LIKE "%Charlie%";SELECT * FROM people WHERE age IS NULL;SELECT * FROM people WHERE age IS NOT NULL;

-- AND/ORSELECT * FROM people WHERE first_name = 'Matt' AND age = 43;SELECT * FROM people WHERE first_name = 'Matt' OR age = 49;
-- ORDERSELECT * FROM people ORDER BY age DESC;SELECT * FROM people ORDER BY first_name DESC;SELECT * FROM people ORDER BY age ASC LIMIT 2;SELECT * FROM people ORDER BY age ASC LIMIT 2 OFFSET 1;SELECT * FROM people ORDER BY age DESC, first_name ASC;
-- ALTER TABLEALTER TABLE people ADD COLUMN weight FLOAT;ALTER TABLE people DROP COLUMN height;ALTER TABLE people MODIFY COLUMN height FLOAT;
ALTER TABLE people ADD COLUMN height FLOAT AFTER first_name;ALTER TABLE people MODIFY COLUMN height FLOAT AFTER age;
ALTER TABLE people ADD COLUMN id INT FIRST;ALTER TABLE people MODIFY COLUMN height FLOAT FIRST;
ALTER TABLE people ADD COLUMN dob DATETIME;ALTER TABLE people CHANGE dob date_of_birth DATETIME;
ALTER TABLE people ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
-- AGGREGATIONSELECT COUNT(*), age FROM people GROUP BY age;SELECT SUM(salary), age FROM people GROUP BY age;SELECT AVG(salary), age FROM people GROUP BY age;SELECT MIN(salary), age FROM people GROUP BY age;SELECT MAX(salary), age FROM people GROUP BY age;SELECT GROUP_CONCAT(first_name), age FROM people GROUP BY age;SELECT GROUP_CONCAT(first_name), age, height FROM people GROUP BY age, height;
-- JOINSSELECT * FROM people JOIN companies ON people.employer_id = companies.id;SELECT * from people JOIN companies;SELECT * FROM people RIGHT JOIN companies ON people.employer_id = companies.id;SELECT * FROM people LEFT JOIN companies ON people.employer_id = companies.id;