********* tables ********
users
products
states
posts
comments
*********************
SELECT * FROM users
SELECT * FROM users LIMIT 5
SELECT * FROM users LIMIT 5 OFFSET 2
SELECT * FROM users ORDER by users.user_email DESC
SELECT user_login FROM users
SELECT user_login, user_email FROM users
SELECT user_login FROM users ORDER by users.user_email DESC
SELECT * FROM products WHERE price = 11.99
SELECT * FROM products WHERE price < 9.99
SELECT * FROM products WHERE book_name = "WILD"
SELECT * FROM products WHERE author LIKE "%Miller%"
INSERT INTO states (state,drink,year,image) VALUES ("New Jersey","Vodka",2018,"vodka.jpg")
DELETE FROM `states` WHERE states.id = 33
SELECT * FROM `states` WHERE state = "Delaware"
UPDATE states SET drink = "Chocolate Oat Milk" WHERE state = "Delaware"
DELETE FROM states WHERE state ="floraida"
INSERT INTO `facebook_users` (`ID`, `name`, `email`, `password`) VALUES (NULL, 'chris', 'chris@gmail.com', '1244'), (NULL, 'sonu', 'sonu@gmail.com', '1111');
SELECT * FROM posts JOIN users on users.id = posts.post_author
SELECT posts.post_title, users.user_nicename FROM posts JOIN users on users.id = posts.post_author
SELECT posts.post_title, users.user_nicename FROM posts JOIN users on users.id = posts.post_author LIMIT 1
SELECT p.post_title, users.user_nicename FROM posts AS p JOIN users on users.id = p.post_author
SELECT p.post_title, users.user_nicename FROM posts AS p JOIN users on users.id = p.post_author LIMIT 1
SELECT p.post_title, u.user_nicename FROM posts AS p JOIN users AS u on u.id = p.post_author
SELECT p.post_title AS "Post Title", u.user_nicename FROM posts AS p JOIN users AS u on u.id = p.post_author
SELECT p.post_title AS "Post Title", u.user_nicename, count(*) as "Total Posts" FROM posts AS p JOIN users AS u on u.id = p.post_author
SELECT * from comments JOIN posts ON comments.post_id = posts.ID
SELECT comments.comment_author,posts.post_title from comments JOIN posts ON comments.post_id = posts.ID
SELECT * FROM purchases JOIN users ON purchases.user_id = users.ID JOIN products ON purchases.product_id = products.ID
SELECT users.user_nicename, products.book_name FROM purchases JOIN users ON purchases.user_id = users.ID JOIN products ON purchases.product_id = products.ID
SELECT posts.post_title, comments.comment_content FROM posts LEFT JOIN comments ON posts.ID = comments.ID
SELECT posts.post_title, comments.comment_content FROM posts LEFT JOIN comments ON posts.ID = comments.ID WHERE comments.comment_content IS null
SELECT users.user_email FROM users UNION SELECT comments.comment_author_email FROM comments
SELECT posts.post_title, COUNT(comments.ID) FROM posts LEFT JOIN comments on comments.ID = posts.ID GROUP BY posts.ID
***** same result *****
SELECT users.user_email FROM users WHERE id IN (SELECT post_author FROM posts WHERE post_status = "draft")
SELECT users.user_email FROM users JOIN posts on posts.post_author = users.ID WHERE posts.post_status = "draft"
****************
Truncate will delete all the rows of any given table (but the table will still exist).
TRUNCATE TABLE facebook_users
*********
DROP deletes all the rows of data, and the table will also be deleted.
DROP TABLE facebook_users
************
ALTER TABLE `comments` CHANGE `postt_id` `post_id` INT(11) NULL DEFAULT NULL
= equality
<> non-equality
!= non-equality
< less than
<= less than or equal
> greater than
>= greater than or equal
!> not greater than
BETWEEN
IS NULL
AND
OR
Aggregate Functions
AVG()
COUNT()
MAX()
MIN()
SUM()
SQL Datatypes
Here are some of the more common datatypes that you'll be using in your database whether it's MYSQL, SQL Server, or Oracle.
TINYINT(Size)
A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. "Size" specifies the maximum display width (which is 255)
INT(Size)
A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. "Size" specifies the maximum display width (which is 255).
BIGINT(Size)
A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 1844674407370955
VARCHAR(256)
A VARIABLE length string that can contain letters, numbers, and special characters. The length can be from 0 to 65535
LONGTEXT
A string with up to 4,294,967,295 characters
DECIMAL(size, d)
"Size" is the total number of digitals. "d" is the number of digits after the decimal point. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0. For money, a suitable recommendation is to use DECIMAL(10, 2)
DATETIME
Used to set the date, Google instructions for more info on this!
TIMESTAMP
The current timestamp (in UNIX time) when the row was created.
C - Create
R - Read
U - Update
D - Delate