Please enable JavaScript.
Coggle requires JavaScript to display documents.
Database Bootcamp - Coggle Diagram
Database Bootcamp
02-Create DB and tables
-
-
-
-
-
-
Create Table
create table <table_name> (<col_name> <datatype>, <col_name> <datatype>);
-
-
08-Revisiting Datatypes
8.1-CHAR and VARCHAR
-
CREATE TABLE dogs (name CHAR(5), breed VARCHAR(10));
8.2-DECIMALS
CREATE TABLE items(price DECIMAL(5,2));
Use decimals to deal with money, when precision is imp
-
8.4-DATE, DATETIME
INSERT INTO people (name, birthdate, birthtime, birthdt)
VALUES('Padma', '1983-11-11', '10:07:35', '1983-11-11 10:07:35');
8.5-CURDATE, CURTIME, NOW
8.5-Formatting Dates
SELECT DATE_FORMAT(birthdt, '%m/%d/%Y at %h:%i') FROM people;
8.6-DATE Math
SELECT birthdt, DATE_ADD(birthdt, INTERVAL 1 MONTH) FROM people;
8.7-TIMESTAMPS
CREATE TABLE comments ( content VARCHAR(100), created_at TIMESTAMP DEFAULT NOW() );
03-Inserting Data
3.1-Insert Command
insert into <table_name> (<col1>, <col2>) values (<val1>, <val2>);
-
-
3.3-Multiple Inserts
insert into <table_name> (<col1>, <col2>) values (<val1>, <val2>),
(<val1>, <val2>)...;
-
-
-
3.7-Primary keys
PRIMARY KEY (<col_name>), AUTO_INCREMENT
04-CRUD Commands
4.1-Create
INSERT INTO cats(name, age) VALUES(‘Taco’, 14);
4.2-Read
SELECT * FROM cats;
SELECT name FROM cats;
SELECT age FROM cats;
SELECT cat_id FROM cats;
SELECT name, age FROM cats;
-
4.4-Aliases
SELECT name AS 'cat name', breed AS 'kitty breed' FROM cats;
-
-
-
07-Aggregate functions
-
7.2-GROUP BY
SELECT author_lname, COUNT(*)
FROM books GROUP BY author_lname;
-
-
-
-
06-Refining Selections
6.1-DISTINCT
SELECT DISTINCT author_fname, author_lname FROM books;
6.2-ORDER BY
SELECT author_fname, author_lname FROM books ORDER BY author_lname, author_fname;
6.3-LIMIT
SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 10,1;
6.4-LIKE
-
SELECT title, stock_quantity FROM books WHERE stock_quantity LIKE '__';
09-Logical Operators
-
-
9.3-GREATER THAN
SELECT title, released_year FROM books
WHERE released_year >= 2000 ORDER BY released_year;
9.4-LESS THAN
SELECT title, released_year FROM books
WHERE released_year <= 2000;