mysql code snippets

UniversityDB
CREATE TABLE IF NOT EXISTS Student_Personal_Information (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE,
    address VARCHAR(100),
    phone_number VARCHAR(15),
    email VARCHAR(100)
);


CREATE TABLE IF NOT EXISTS Courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    course_code VARCHAR(10) UNIQUE,
    professor VARCHAR(50),
    start_date DATE,
    end_date DATE
);


CREATE TABLE IF NOT EXISTS Grade (
    grade_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    grade VARCHAR(50),
    FOREIGN KEY (student_id) REFERENCES Student_Personal_Information(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);


INSERT INTO Student_Personal_Information (first_name, last_name, date_of_birth, address, phone_number, email)
VALUES
    ('Maria', 'Gonzales', '1998-03-15', '123 Main St, Manggahan, Pasig City', '09123456789', '[email protected]'),
    ('Jose', 'Rodriguez', '1997-05-22', '456 Elm St, Maybunga, Pasig City', '09234567890', '[email protected]'),
    ('Luz', 'Fernandez', '1999-11-10', '789 Oak St, Pinagbuhatan, Pasig City', '09345678901', '[email protected]'),
    ('Ramon', 'Ortiz', '1996-08-03', '234 Birch St, Kapasigan, Pasig City', '09456789012', '[email protected]'),
    ('Carmen', 'Santiago', '2000-02-28', '567 Cedar St, Bangbang, Pasig City', '09567890123', '[email protected]');


INSERT INTO Courses (course_name, course_code, professor, start_date, end_date)
VALUES
    ('Advanced Database Management System', 'ADMS101', 'Dr. Juan Santos', '2023-01-10', '2023-05-10'),
    ('Advanced System Analysis and Design', 'ASAD201', 'Prof. Cristina Lopez', '2023-02-15', '2023-06-15'),
    ('Management of Information System', 'MIS301', 'Dr. Manuel Reyes', '2023-03-20', '2023-07-20'),
    ('Introduction to Computing', 'COMP101', 'Prof. Lourdes Garcia', '2023-01-15', '2023-05-15'),
    ('Fundamentals of Programming', 'PROG101', 'Dr. Andres Torres', '2023-01-20', '2023-05-20');

INSERT INTO Grade (student_id, course_id, grade)
VALUES
    (1, 1, '1.25'),
    (2, 1, '1.50'),
    (3, 2, '1.00'),
    (4, 3, '1.50'),
    (5, 4, '1.25'),
    (1, 5, '1.50'),
    (2, 5, '1.75'),
    (3, 4, '1.00'),
    (4, 3, '1.25'),
    (5, 2, '1.50');

SELECT * FROM Student_Personal_Information;
SELECT * FROM Courses;
SELECT * FROM Grade;
Add a Boolean column in MySQL table with default value
-- Add in existing table
ALTER TABLE table_name
ADD COLUMN is_active BOOLEAN DEFAULT 1

-- Add while creating the table
CREATE TABLE table_name (
    is_active BOOLEAN NOT NULL default 1
);
MySQL Query to change column type
ALTER TABLE users
MODIFY COLUMN username varchar(255);
Show all column names of a table MySql query
-- First method
DESCRIBE table_name;

-- Second method
SHOW COLUMNS FROM table_name;

-- Third method
SELECT column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';

-- Get column names in a single line (comma seperated)
SELECT group_concat(column_name)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';
Apply JOIN on multiple columns of the tables MySql
--using OR operator
Select 
    employee.name, employee.email, employee_address.street
FROM Employee
INNER JOIN employee_address
    ON employee.Id = employee_address.employee_id 
    OR employee.pincode = employee_address.street_code 
    OR employee.parmanent_code = employee_address.location_code 

--using AND operator
Select 
    employee.name, employee.email, employee_address.street
FROM Employee
INNER JOIN employee_address
    ON employee.Id = employee_address.employee_id 
    AND employee.pincode = employee_address.street_code 
    AND employee.parmanent_code = employee_address.location_code
Select second highest column value MySQL
SELECT
    (SELECT DISTINCT
            column_name
        FROM
            table_name
        ORDER BY column_name DESC
        LIMIT 1 OFFSET 1
    ) AS second_highest_value
;
Order by Date where date in string format MySQL
SELECT Id, FirstName, Lastname
FROM Students 
ORDER BY STR_TO_DATE(CreatedDate, '%d-%m-%Y') ASC;

-- USE 'DESC' in place of 'ASC' if you want result in decending format
Delete a column from Table MySQL
ALTER TABLE Students
DROP COLUMN Email;
Add comment in Sql or Mysql
-- USE -- FOR SINGLE LINE COMMENT
-- SELECT * FROM STUDENTS

/* BLOCK LEVEL COMMENTS - COMMENT OUT A FULL BLOCK */
/*
SELECT NAME
FROM USERS
*/
murata mysqlにcomman lineからアクセスする方法
mysql -u root -p test_db
# test_dbはデータベース名
Remove or drop table MySQL
-- Remove single table
DROP TABLE table_name;

-- Remove multiple tables
DROP TABLE table_name_1, table_name_2;
Rename table MySQL
-- SINGLE TABLE
RENAME TABLE courses TO subjects;

-- MULTIPLE TABLES
RENAME TABLE 
    courses TO subjects,
    topics TO subject_topics;
Add unique constraint MySQL
-- While creating table (Single)
CREATE TABLE table_name( colum_1 data_type,  column_2 data_type UNIQUE );

-- While creating table (Combine multiple column)
CREATE TABLE table_name(
   column_1 data_type,
   column_2 data_type,
   UNIQUE(column_1, column_2)
);

-- Add unique constraints on existing table
ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
UNIQUE (column_name);
Show all Databases MySQL
SHOW DATABASES;
Create and select database MySQL
-- To create database
CREATE DATABASE db_name;

--To select database
USE db_name;
In Operator MySQL
-- Match with list values
SELECT 
    first_name, 
    last_name,
    city
FROM
    Students
WHERE
    city IN ('Delhi' , 'Newyork');


--Match with subquery records
SELECT    
	first_name, 
    last_name,
    city
FROM    
	Students
WHERE city IN
(
    SELECT 
        city
    FROM 
        city_list
);
Get distinct records MySQL
SELECT DISTINCT
    column_name1, column_name2
FROM
    table_name;
Order by ascending and descending MySQL
SELECT * FROM table_name FROM ORDER BY column_name ASC
SELECT * FROM table_name FROM ORDER BY column_name DESC
Add foreign key in existing table Mysql
ALTER TABLE 
ContactDetails 
ADD 
EmployeeId INT UNSIGNED NOT NULL;

ALTER TABLE 
ContactDetails 
ADD 
CONSTRAINT fk_EMP_CONTACT FOREIGN KEY (EmployeeId) REFERENCES Employee(Id);
Get duplicate rows and its counts MySQL
SELECT 
    column_name, 
    COUNT(column_name)
FROM
    table_name
GROUP BY column_name
HAVING COUNT(column_name) > 1;
Select rows from table SQL query
//select all rows from table
SELECT * FROM table_name

//select rows based on a condition
SELECT * FROM table_name WHERE column_name='value'
Add columns, alter table SQL query
ALTER TABLE 
    table_name 
ADD (
    Column_name1 data_type, 
    Column_name2 data_type (size)
);
Insert data into table mysql query
INSERT INTO table_name (Column1, Column2, Column3)
VALUES(1, 'Value2', 'Value3')

or

INSERT INTO table_name
SET
Column1 = 'Value1',
Column2 = 'Value2',
Column3 = 'Value3'
Create table with constraints MySQL query
CREATE TABLE table_name(
    Id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    Column1 char(40) DEFAULT NULL,
    Column2 varchar(100) DEFAULT '',
    Date timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(Id),
    UNIQUE (Column1)
)