More »
Loading...

Resource

Data Types

Types

  • CHAR
  • String (0 - 255)
  • VARCHAR
  • String (0 - 255)
  • TINYTEXT
  • String (0 - 255)
  • TEXT
  • String (0 - 65535)
  • BLOB
  • String (0 - 65535)
  • MEDIUMTEXT
  • String (0 - 16777215)
  • MEDIUMBLOB
  • String (0 - 16777215)
  • LONGTEXT
  • String (0 - 429496-7295)
  • LONGBLOB
  • String (0 - 429496-7295)
  • TINYINT x
  • Integer (-128 to 127)
  • SMALLINT x
  • Integer (-32768 to 32767)
  • MEDIUMINT x
  • Integer (-8388608 to 8388607)
  • INT x
  • Integer (-2147-483648 to 214748-3647)
  • BIGINT x
  • Integer (-9223-372-036-854-775808 to 922337-203-685-477-5807)
  • FLOAT
  • Decimal (precise to 23 digits)
  • DOUBLE
  • Decimal (24 to 53 digits)
  • DECIMAL
  • "-DOU-BLE-" stored as string
  • DATE
  • YYYY-MM-DD
  • DATETIME
  • YYYY-MM-DD HH:MM:SS
  • TIMESTAMP
  • YYYYMM-DDH-HMMSS
  • TIME
  • HH:MM:SS
  • ENUM
  • One of preset options
  • SET
  • Selection of preset options

Operators

Assignment Operators

Logical Operators

String

String Comparison

Regular Expressions

Numeric

Arithmetic Operators

Date

Group

Information

Encrypt

Miscellaneous

CRUD: Create

Create

  • Creates database "movies_db"
  • CREATE SCHEMA IF NOT EXISTS movies_db
  • Creates table "movies" with two columns (title is required & year is default null -This is the case even without specifying-)
  • CREATE TABLE actors (title VARCHAR(200) NOT NULL, year INTEGER NULL)
  • Creates "genre" table with primary key: id which auto increments, and a unique key: name (can't name two things the same name)
  • CREATE TABLE genres (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL UNIQUE KEY)

CRUD: READ

Read

  • Returns all columns
  • SELECT * FROM table
  • Returns all columns
  • SELECT * FROM table1, table2, ...
  • Returns specific column
  • SELECT field1, field2, ... FROM table1, table2, ...
  • Returns rows that match condition
  • SELECT ... FROM ... WHERE condition
  • SELECT * FROM movies WHERE title IS NOT NULL AND LIKE "%godfather%" AND year BETWEEN 1970 AND 1973 ORDER BY year ASC, title DESC;
  • Returns with orders
  • SELECT ... FROM ... WHERE condition GROUP BY field
  • Returns withd orders and match condition
  • SELECT ... FROM ... condition GROUP BY field HAVING condition2
  • Returns second 10 rows (10-20)
  • SELECT ... FROM ... WHERE condition LIMIT 10, 10
  • Returns with no repeats
  • SELECT DISTINCT field1 FROM ...
  • Returns all columns from the movies and genre tables for all genres and all movies that have been assigned to a genre
  • SELECT * FROM movies RIGHT OUTER JOIN genres ON movies.genre_id = genre_id;
  • Returns all columns from the movies and genre tables for all movies and all genres that have been assigned to a movie
  • SELECT * FROM movies LEFT OUTER JOIN genres ON movies.genre_id = genre_id;
  • Returns all columns from the movies and genre tables for each movie that has been assigned a genre
  • SELECT * FROM movies JOIN genres ON movies.genre_id = genres.id;
  • Returns all movies (and the respective genre names) that have been assigned a genre
  • SELECT * FROM movies JOIN genres ON movies.genre_id = genres.id;
  • Returns all columns from the movies and genre tables for all movies (under alias: "movie_title") and all genres that have been assigned to a movie (genres aliased as "genre_name")
  • SELECT movies.title AS movie_title genres.name AS genre_name FROM movies JOIN genres ON movies.genre_id = genres.id;
  • Returns the total number of rows in the table reviews for movie_id=1 with an alias result of "review_count"
  • SELECT COUNT(id) AS review_count FROM reviews WHERE movie_id=1;
  • Returns the minimum, maximum and two identical columns (derived in different ways) of averages of all score (score is a column) in the table reviews for movie_id=1 with an alias result of "review_count"
  • SELECT MIN(score) AS min_score, MAX(score) AS max_score, SUM(score) / COUNT(score) as avg_score, AVG(score) AS avg_score_2 FROM reviews WHERE movie_id=1;
  • Returns all titles in movies table and their corresponding calculated statistics: min, max, and average reviews (each value header is replaced by an alias)from the reviews table for all movies made after 2000 that have a rating over an average of 3. If they are null, "null" is replaced with "0".
  • SELECT title, MIN(score) AS minimum_score, MAX(score) AS maximum_score, IFNULL(AVG(score),0) AS average FROM movies LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id WHERE year_released > 2000 GROUP BY movie_id HAVING average > 3
  • Returns
  • SELECT CONCAT(LOWER(first_name)," ",LOWER(last_name)) AS full_name, CONCAT(SUBSTRING(LOWER(email),1,10),"...") AS partial_email, LENGTH(username) AS username_length FROM users HAVING username_length < 19;
  • Returns "bar" without the spaces
  • SELECT TRIM(' bar ');
  • Returns "barxxx". Trims the x's away from before the "bar".
  • SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
  • Returns "bar". Trims away the x's before and after the "bar".
  • SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
  • Returns "barx". Trims away the "xyz" after the bar.
  • SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');

CRUD: UPDATE

Update

  • Inserts Avatar into the title column and 2009 into year
  • INSERT INTO movies VALUES ("Avatar", 2009)
  • Same as above but specifies the order it is entered & enters two rows at once.
  • INSERT INTO movies (year, title) VALUES (2009, "Avatar"), (NULL, "Avatar 2")
  • Same as above
  • INSERT INTO movies SET title="Back to the Future", year=1985
  • Changes the year of the movie: Avatar 2 (SET SQL SAFE UPDATES may not be required)
  • SET SQL_SAFE_UPDATES=0; UPDATE movies SET year=2015 WHERE title="Avatar 2";
  • Changes the year to 2016 and the title to Avatar Reloaded.
  • UPDATE movies SET year=2016, title="Avatar Reloaded" WHERE title="Avatar 2";
  • Changes the year to 2016 and the title to Avatar Reloaded.
  • UPDATE movies SET year=2016, title="Avatar Reloaded" WHERE title="Avatar 2";
  • Renames tables movie and actors to movie_table and actor_table, respectively.
  • RENAME TABLE movies TO movie_table, actors TO actor_table;
  • Add a new column "genre" to movies
  • ALTER TABLE movies ADD COLUMN genre VARCHAR(100);
  • Add two new columns "genre" (specified as first) and dob (specified as last) to movies
  • ALTER TABLE movies ADD (genre VARCHAR(100) FIRST, dob DATE AFTER);
  • Renames the column dob to Date of Birth and pob to Place of Birth
  • ALTER TABLE movies CHANGE COLUMN (dob Date_of_Birth VARCHAR(100)); ALTER TABLE movies CHANGE COLUMN (pob Place_of_Birth VARCHAR(100));
  • Ads a new column "genre_id" as foreign key to the movies table
  • ALTER TABLE movies ADD COLUMN genre_id INTEGER NULL, ADD CONSTRAINT FOREIGN KEY (genre_id) REFERENCES genres(id);

CRUD: DELETE

Delete

  • Deletes 2009 movie Avatar from the movies table
  • DELETE FROM movies WHERE title="Avatar" AND year = 2009;
  • Deletes the table "reviews" and all of its data
  • DROP TABLE IF EXISTS reviews;
  • Deletes all of the data from the table "movie_table"
  • TRUNCATE movie_table;
  • Deletes the column date of birth in movies table
  • ALTER TABLE movies DROP date_of_birth;
  • Deletes the database movie_db_3 & the data in it
  • DROP DATABASE IF EXISTS movie_db_3;
  • Deletes the database movie_db_3 & the data in it
  • TRUNCATE TABLE movie_table;

USERS

Safety

  • Data Manipulation ONLY: How to Create a "READ-ONLY" Account: "guest" that can connect from any domain to all of the tables in a database called "treehouse_db" using the password: pa$$word. And then resets and restarts all user privleges in order to enact new user.
  • GRANT SELECT ON treehouse_db.* TO guest@'%' IDENTIFIED BY "pa$$word"; FLUSH PRIVILEGES;
  • Data Manipulation ONLY: How to Create a "READ/WRITE" Account: "user" that can connect from any domain to all of the tables in a database called "treehouse_db" using the password: pa$$word. And then resets and restarts all user privleges in order to enact new user.
  • GRANT SELECT, INSERT, UPDATE, DELETE ON treehouse_db.* TO user@'%' IDENTIFIED BY "pa$$word"; FLUSH PRIVILEGES;
  • Database Definition: How to Create a "READ/WRITE" Account: "admin" that can connect from any domain to all of the tables in a database called "treehouse_db" using the password: pa$$word. And then resets and restarts all user privleges in order to enact new user.
  • GRANT ALTER, CREATE, DROP ON treehouse_db.* TO admin@'%' IDENTIFIED BY "pa$$word"; FLUSH PRIVILEGES;

Performance

Indexing

  • Creates an index of the last name column called "last_name_idx", because the last names are used often in queries. Then checks the index by doing a query on a last name "Chalkley". Then uses EXPLAIN key to return metadata about how the query was performed. EXPLAIN should return "Type: ref" and "Extra: Using index condition"
  • CREATE INDEX last_name_idx ON users(last_name); SELECT * FROM users WHERE last_name = "Chalkley"

Connecting to Host

Protocols

  • Port usually used for mysql
  • 3306

Ad