Database Operations

SHOW DATABASES;
USE database_name;
CREATE DATABASE [IF NOT EXISTS] db_name;
DROP DATABASE [IF EXISTS] db_name;
SHOW CREATE DATABASE db_name;
ALTER DATABASE db_name CHARACTER SET charset_name;

Tible Creatoin Examples

DROP TABLE IF EXISTS user_main;
CREATE TABLE `user_main` (
 `user_id` varchar(10) NOT NULL,
 `login_name` varchar(10) NOT NULL,
 `passwd` varchar(10) NOT NULL,
 PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS user_profile;
CREATE TABLE `user_profile` (
 `profile_id` varchar(10) NOT NULL,
 `user_age` varchar(10),
 `user_addr` varchar(50),
 `main_user_id` varchar(10) NOT NULL,
 PRIMARY KEY (`profile_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Column Attributes

• NOT NULL: Rqeuires explicit value assignment
• AUTO_INCREMENT: Automatically increments primary key values
• PRIMARY KEY: Defines primary key constraint
• ENGINE: Specifies storage engine
• DEFAULT: Sets default column value

USE demo_db;
CREATE TABLE product_category(
 cat_id INT AUTO_INCREMENT,
 cat_name VARCHAR(20),
 PRIMARY KEY(cat_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE temp_data(
 record_id INT,
 record_date DATE
);

Table Structure Operations

-- Clone table structure
CREATE TABLE new_table LIKE original_table;
DESC new_table;

-- View tables
SHOW TABLES;
DESC table_name;
SHOW CREATE TABLE table_name;

-- Drop tables
DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

Table Modification Commands

-- Rename table
RENAME TABLE old_name TO new_name;

-- Add column
ALTER TABLE table_name ADD column_desc VARCHAR(20) DEFAULT "default text";

-- Modify column type
ALTER TABLE table_name MODIFY column_desc VARCHAR(50);

-- Rename column
ALTER TABLE table_name CHANGE old_name new_name VARCHAR(30);

-- Drop column
ALTER TABLE table_name DROP column_name;

Data Manipulation Language (DML)

CREATE TABLE employee(
 emp_id INT,
 emp_name VARCHAR(20),
 emp_age INT,
 gender CHAR(1),
 emp_addr VARCHAR(40)
);

-- Insert data
INSERT INTO employee (emp_id, emp_name, emp_age, gender, emp_addr) 
VALUES(1, 'John Doe', 25, 'M', 'New York');
INSERT INTO employee VALUES(2, 'Jane Smith', 30, 'F', 'London');

-- Update data
UPDATE employee SET gender = 'F' WHERE emp_id = 1;

-- Delete data
DELETE FROM employee WHERE emp_id = 2;
DELETE FROM employee;
TRUNCATE TABLE employee;

Data Query Language (DQL)

Sample Data Setup

DROP TABLE IF EXISTS staff;
CREATE TABLE staff(
 staff_id INT,
 staff_name VARCHAR(20),
 gender CHAR(1),
 salary DOUBLE,
 join_date DATE,
 department VARCHAR(20)
);

INSERT INTO staff VALUES(1, 'Michael', 'M', 7500, '2013-02-04', 'Engineering');
INSERT INTO staff VALUES(2, 'Sarah', 'F', 4000, '2010-12-02', 'Engineering');
INSERT INTO staff VALUES(3, 'David', 'M', 9500, '2008-08-08', 'Engineering');
INSERT INTO staff VALUES(4, 'Lisa', 'F', 5500, '2015-10-07', 'Marketing');
INSERT INTO staff VALUES(5, 'Kevin', 'M', 5200, '2011-03-14', 'Marketing');

Basic Queries

-- Query all data
SELECT * FROM staff;

-- Specific columns
SELECT staff_id, staff_name FROM staff;

-- Column aliases
SELECT
 staff_id AS 'ID',
 staff_name AS 'Name',
 gender AS 'Gender',
 salary AS 'Salary',
 join_date 'Join Date',
 department 'Department'
FROM staff;

-- Distinct values
SELECT DISTINCT department AS 'Department' FROM staff;

-- Calculations
SELECT staff_name, salary + 1000 FROM staff;

Conditional Queries

-- Exact match
SELECT * FROM staff WHERE staff_name = "Michael";
SELECT * FROM staff WHERE salary = 5500;

-- Range queries
SELECT * FROM staff WHERE salary BETWEEN 5000 AND 10000;
SELECT * FROM staff WHERE salary IN (4000, 7500, 9500);

-- Pattern matching
SELECT * FROM staff WHERE staff_name LIKE "%Sa%";
SELECT * FROM staff WHERE staff_name LIKE 'M%';
SELECT * FROM staff WHERE staff_name LIKE '__vi%';

-- Null checks
SELECT * FROM staff WHERE department IS NULL;
SELECT * FROM staff WHERE department IS NOT NULL;

Advanced Query Techniques

Sorting Results

-- Single column sort
SELECT * FROM staff ORDER BY salary;
SELECT * FROM staff ORDER BY salary DESC;

-- Multi-column sort
SELECT * FROM staff ORDER BY salary DESC, staff_id ASC;

Aggregate Functions

-- Count records
SELECT COUNT(DISTINCT staff_id) FROM staff;

-- Statistical functions
SELECT COUNT(salary), MAX(salary), MIN(salary), AVG(salary) FROM staff;

-- Conditional aggregates
SELECT COUNT(*) FROM staff WHERE salary > 5000;
SELECT COUNT(DISTINCT staff_name) FROM staff WHERE department = "Engineering";
SELECT AVG(salary) FROM staff WHERE department = "Marketing";

Grouping Data

-- Basic grouping
SELECT department, COUNT(staff_name) FROM staff GROUP BY department;

-- Group with aggregates
SELECT gender, AVG(salary) FROM staff GROUP BY gender;

-- Complex grouping
SELECT department, AVG(salary) FROM staff 
WHERE department IS NOT NULL 
GROUP BY department 
ORDER BY AVG(salary) DESC;

-- Group with having clause
SELECT department, AVG(salary) FROM staff 
GROUP BY department 
HAVING AVG(salary) > 6000 AND department IS NOT NULL;

Result Limiting

-- First N records
SELECT * FROM staff LIMIT 5;

-- Offset and limit
SELECT * FROM staff LIMIT 3, 6;