MySQL

What is a Database?

A database is a structured collection of interrelated data stored together to serve multiple applications.

---

MySQL Elements

Literals

Literals refer to fixed data values:

17        -- Numeric literal'Harry'   -- Text literal12.5      -- Real literal

---

Data Types

MySQL provides several data types:

# Numeric TypesTINYINT, SMALLINT, MEDIUMINT, INT, BIGINTFLOAT(M,D), DOUBLE(M,D), DECIMAL(M,D) # Date/Time TypesDATE        -- YYYY-MM-DDDATETIME    -- YYYY-MM-DD HH:MM:SSTIME        -- HH:MM:SSYEAR        -- YYYY # String/Text TypesCHAR(M)        -- Fixed-length stringVARCHAR(M)     -- Variable-length stringTEXT           -- Large textBLOB           -- Binary large object (for files/images)ENUM('x','y')  -- One value from a defined setSET('x','y')   -- Multiple values from a defined set

---

NULL Values

NULL represents missing/unknown data.

---

Comments

/* Multi-line comment */# Single-line comment-- Single-line comment

---

MySQL Calculations

SELECT 5+8;   -- AdditionSELECT 15-5;  -- SubtractionSELECT 5*5;   -- MultiplicationSELECT 24/4;  -- Division

Tip: SQL is case-insensitive, but keywords are usually written in UPPERCASE for readability.

---

Accessing Databases

SHOW DATABASES;      -- List all databasesUSE database_name;   -- Switch to a databaseSHOW TABLES;         -- List all tables in the current database

---

Creating Tables

CREATE TABLE table_name (  column1 datatype,  column2 datatype,  column3 datatype);

---

Inserting Data

INSERT INTO table_name (col1, col2) VALUES (val1, val2);INSERT INTO table_name VALUES (val1, val2, val3); -- All columnsINSERT INTO table_name (col1, col2, col3) VALUES (val1, val2, NULL); -- NULL insertINSERT INTO table_name (date_col) VALUES ('2021-12-10'); -- Date insert

---

Retrieving Data (SELECT)

SELECT * FROM table_name;                       -- All columnsSELECT col1, col2 FROM table_name;              -- Specific columnsSELECT DISTINCT col1 FROM table_name;           -- Unique valuesSELECT col1, col2 AS alias FROM table_name;     -- Column aliasSELECT * FROM table_name WHERE condition;       -- Filter rowsSELECT * FROM table_name WHERE col BETWEEN 10 AND 20;  -- Range filterSELECT * FROM table_name WHERE col IN (1,2,3);  -- List matchSELECT * FROM table_name WHERE col NOT IN (1,2,3);SELECT * FROM table_name WHERE col LIKE 'Ha%';  -- Pattern matchSELECT * FROM table_name WHERE col IS NULL;     -- NULL search

---

Constraints

NOT NULL   -- Disallow NULLDEFAULT    -- Set default valueUNIQUE     -- Ensure unique valuesCHECK (condition) -- Ensure condition is truePRIMARY KEY(col1)  -- Unique + Not NullFOREIGN KEY (col) REFERENCES other_table(col)

---

Modifying Data

UPDATE table_nameSET col1 = new_value, col2 = new_valueWHERE condition;

---

Deleting Data

DELETE FROM table_name WHERE condition;

---

Ordering Results

SELECT * FROM table_name ORDER BY col ASC;SELECT * FROM table_name ORDER BY col DESC;SELECT * FROM table_name ORDER BY col1 ASC, col2 DESC;

---

Grouping Data

SELECT col, COUNT(*) FROM table_name GROUP BY col;SELECT col, AVG(salary) FROM table_name GROUP BY col HAVING AVG(salary)>50000;

---

Altering Table Structure

ALTER TABLE table_name ADD new_column datatype;ALTER TABLE table_name MODIFY column_name datatype;ALTER TABLE table_name CHANGE old_name new_name datatype;

---

Dropping Table

DROP TABLE table_name;

---

MySQL Functions

String Functions

SELECT CHAR(72,97,114,114,121);SELECT CONCAT('Harry','Bhai');SELECT LOWER('Harry');SELECT UPPER('CodeWithHarry');SELECT SUBSTRING('HelloWorld', 1, 5);SELECT TRIM('   Harry   ');SELECT INSTR('CodeWithHarry','Harry');SELECT LENGTH('Harry');

Numeric Functions

SELECT MOD(11,4);SELECT POWER(2,3);SELECT ROUND(15.193,1);SELECT SQRT(144);SELECT TRUNCATE(15.75,1);

Date/Time Functions

SELECT CURDATE();SELECT NOW();SELECT DATE('2021-12-10 12:00:00');SELECT YEAR(NOW());SELECT MONTH(NOW());SELECT DAY(NOW());SELECT SYSDATE();

Aggregate Functions

SELECT AVG(col) FROM table_name;SELECT COUNT(*) FROM table_name;SELECT MAX(col) FROM table_name;SELECT MIN(col) FROM table_name;SELECT SUM(col) FROM table_name;

---

Joins

-- Inner JoinSELECT t1.col, t2.colFROM table1 t1INNER JOIN table2 t2 ON t1.id = t2.id; -- Left JoinSELECT ...FROM table1LEFT JOIN table2 ON table1.id = table2.id; -- Right JoinSELECT ...FROM table1RIGHT JOIN table2 ON table1.id = table2.id; -- Full Join (MySQL Workaround)SELECT ...FROM table1LEFT JOIN table2 ON table1.id=table2.idUNIONSELECT ...FROM table1RIGHT JOIN table2 ON table1.id=table2.id; -- Self JoinSELECT a.col, b.colFROM table a, table bWHERE a.id < b.id;

---

Indexes (Performance)

CREATE INDEX idx_name ON table_name(column_name);DROP INDEX idx_name ON table_name;SHOW INDEX FROM table_name;

---

Views (Virtual Tables)

CREATE VIEW view_name AS SELECT col1, col2 FROM table_name WHERE condition;DROP VIEW view_name;

---

Transactions (Atomic Operations)

START TRANSACTION;UPDATE accounts SET balance = balance - 500 WHERE id = 1;UPDATE accounts SET balance = balance + 500 WHERE id = 2;COMMIT; -- Save changesROLLBACK; -- Undo changes

---

User Management & Privileges

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';FLUSH PRIVILEGES;