Databases
create DATABASE CLASSDB; -- Create a database using CREATE DATABASE command
SHOW DATABASES; -- List all the DBs in your MYSQL Server
CREATE DATABASE IF NOT EXISTS CLASSDB; -- it will only create the database
if it doesn't exits
DROP DATABASE CLASSDB; -- Deleting a database
USE CLASSDB; -- Select a DB to work
SHOW TABLES; -- List all the tables in the selected DB
CREATE DATABASE FBDB; -- Create a new database
USE FBDB; -- Select the new database
CREATE TABLE USERS (
EMAIL VARCHAR(50) ,
PASSWORD VARCHAR(50) ,
USERNAME VARCHAR(50) ,
ID INT PRIMARY KEY AUTO_INCREMENT
); -- Create a table
SHOW TABLES; -- List all the tables in the selected DB
DESC USERS; -- Describe the table
INSERT INTO USERS (USERNAME, EMAIL, PASSWORD) VALUES
('SANKET', 'SANKET@GMAIL.COM', '123456'); -- Insert data into the table
INSERT INTO USERS (USERNAME, EMAIL, PASSWORD) VALUES
('SARTHAK', 'SJ@GMAIL.COM', '123456'); -- Insert data into the table
SELECT ID, EMAIL, USERNAME FROM USERS; -- Select data from the table
SELECT * FROM USERS; -- Select all the data from the table
INSERT INTO USERS (USERNAME, EMAIL, PASSWORD) VALUES
('JD', 'JD@GMAIL.COM', '123456'),
('RIYA', 'RY@GMAIL.COM', '123456'),
('ROHIT', 'RR@GMAIL.COM', '123456') ; -- Insert multiple data into the table
-- CREATE A POSTS TABLE WITH ID, CONTENT, USER_ID, CREATED_AT COLUMNS
CREATE TABLE POSTS (
ID INT PRIMARY KEY AUTO_INCREMENT,
CONTENT VARCHAR(255),
USER_ID INT, -- TO WHOM THE POST BELONGS
CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO POSTS (CONTENT, USER_ID) VALUES
('HELLO WORLD', 1); -- Insert data into the table
INSERT INTO POSTS (CONTENT, USER_ID) VALUES
('HELLO WORLD', 1); -- Insert data into the table
INSERT INTO POSTS (CONTENT, CREATED_AT, USER_ID) VALUES
('HELLO WORLD AGAIN', '2021-01-01 12:00:00', 1); -- Insert data into the
table
SELECT * FROM POSTS; -- Select all the data from the table
SELECT * FROM USERS WHERE ID = 3; -- Select all the data from the table
SELECT * FROM POSTS WHERE USER_ID = 1 AND CONTENT = 'HELLO WORLD'; -- Select
all the data from the table
-- OPERATOR IN MYSQL: =, !=, <, >, <=, >=, AND, OR, NOT, IN, BETWEEN, LIKE,
IS NULL, IS NOT NULL
SELECT * FROM POSTS WHERE CONTENT LIKE '%AGAIN'; -- Select all the data from
the table
-- %AGAIN% SUBSTRING MATCH
-- %AGAIN STARTS WITH ANYTHING BUT ENDS WITH AGAIN
-- AGAIN% STARTS WITH AGAIN BUT CAN HAVE ANYTHING AFTER THAT
SELECT * FROM POSTS WHERE CONTENT LIKE '%WORLD' ORDER BY CREATED_AT ASC;
DELETE FROM POSTS WHERE ID = 1; -- Delete a row from the table
DROP TABLE POSTS; -- Delete a table
UPDATE POSTS SET CONTENT = 'MY WORLD' WHERE ID = 2; -- Update a row in the
table
