Learn Mysql

MySQL

Click here to go II part

1. SQL:

SQL is a language which is used to interact with relational database management systems.

Relational Database Management system is basically just a software which we can use to create and manage different databases.

MySQL: MySql is one of the most popular database management system for beginners.

Query: Queries are used to query a database.

Database Schema: Database schema is basically just like all of the different tables and all the different relation that the database is going to store.

What is Database (DB) ?

=> Any collection of related information like Phone book, Shopping list, To-do list, your 5 best friends, Facebook user base etc.

Database can be stored in different ways..

On paper

in mind

on a computer

This power point

Comments section etc.

Database Management System (DBMS)

A special software program that helps users create and maintain database.

Makes it easy to manage large amount of information.
Handles Security.
Backups
Importing/Exporting data
Concurrency
Interacts with software application.

Two main types of Databases.

i. Relational Databases (SQL)

ii. Non-Relational Databases (No Sql / Not just SQL)

i. Relational Databases (SQL):

Organize data into one or more tables.
- Each table has columns and rows
- A unique key identifies each row.

ii. Non-Relational Databases (No Sql / Not just SQL) :

Organize data is anythings but a traditional table.

Key-value stores
Documents (JSON, XML, etc)
Graphs
Flexible Tables

i. Relational Databases (SQL):

Relational Database Management Systems (RDBMS)

Help users create and maintain a relational database

mySql, Oracle, postgreSql, mariaDB etc.

Structured query language (SQL)

Standardized language for interacting with RDBMS.
Used to perform CRUD operations, as well as other administrative
tasks ( user management, security, backup etc.)
Used to define tables and structures
SQL code used on one RDBMS is not always portable to another without modification.

ii. Non-Relational Databases (No Sql / Not just SQL) :

Non relational database is not relational database.

Non-Relational Databases (No Sql / Not just SQL) :

Non-Relational Database Management Systems ( NRDMS )

Help users create and maintain a non-ralational database.

mongoDB, dynamoDB, apache cassandra, firebase etc.

Implementation Specific

Any non-relational database falls under this category, so there's no set language standard.
Most NRDBMS will implement their own language for performing CRUD and
administrative operations on the database.

Database Queries:

Queries are requests made to the database management system for specific information.

As the database's structure become more and more complex, it becomes more difficult to get the specific pieces of information we want.

A good search is a Query.

Wrap Up:

Database is any collection of related information.
Computer are great for storing databases.
Database Management System (DBMS) make it easy to create, maintain and secure a database.
DBMS allow you to perform CRUD operations and other administrative tasks.
Two types of Databases, Relational Database and Non-Relational Database.
Relational Database use SQL and store data in tables with rows and columns.
Non-Relational data store data using other data structures.

10.

Column define single attributes.
Row is horizontal entry which represents a single student.

Column represents single attributes.
Row represents an entry or actual student.

Understand from below image:

11.

Primary key:

When ever we create a table on a relational database. We always gonna have very special column

which is called Primary key.

Primary key is basically an attributes which uniquely define the row in the database.

"Student Id " from above image show Primary key.

Primary key always unique each row of the table.

Primary key could be anything that uniquely identified. It could be Number/String etc.

Below user table, email is Primary Key.

Surrogate Key:

Surrogate Keys is basically a key that has no mapping to anything in the real world.

This is employee ID, this is what we would call a Surrogate Key.

It's essentially just, you know like in this case a random number that we assign to an employee.

Surrogate Keys is a type of primary key.

Surrogate is a key that has no mapping to anything in the real world.

SSN => Social Security Number

Here, we are using Social Security Number as the primary key of the table.

SSN is an example of natural key.

natural key is a key that has a mapping or has a purpose in the real world, not just in the database.

But

A surrogate key is a primary key that has no mapping to the real world. keep in mind.

SSN key Demo:

Foreign Key:

Foreign key is basically an attribute that we can store on a database table that will link us to another database table.

Here I have this same exact employee table.

And then I also have this other attribute over here, branch_id.

branch_id is what we would call a foreign key.

foreign key stores the primary key of a row in another database table.

So, above image, we have our employee table and I'm defining information about the employee.

But let's say that an employee belongs to a specific branch in our company,

So, a company might have different branches.

And we can store the information about what branch the employee belongs to inside of a foreign key.

So, the foreign key over here is actually a primary key inside of another table.

In our case a branch table.

So let see the image:

Down here, we have this branch table in our database. Branch is it's own separate table and we have the branch has a primary key over here which is 2,3,1.

And over here we can define which branch a specific employee belongs to, by referring to the ID,

the primary key of the branch.

So, here Jan Levinson, her branch id is 1, which means she is in the Corporate branch, right?

Because branch ID number 1 is Corporate.

Michael Scoot, his branch ID is 2, which means he is in the Scranton branch.

So, this number is mapping this row over here into this other table. And that's what a foreign key does.

A foreign key is essentially just a way that we can define relationships between two tables.

So, a foreign key is just a primary key of another table.

A foreign key allows us to link up or define relationships between tables.

And over there you'll see on the branch table, I also defined another foreign key which is mgr_id.

This is actually going to be a foreign key which connects branch to the employee table.

So, mgr_id is going to be the ID of a particular employee who is the manager of the branch.

So, let's take a look at the Scranton branch, So the branch ID is 2.

The name of the branch is Scranton and the manager ID (mgr_id) is 101.

Let's check it out:

So, over here in the employee table, employee 101 is Michael Scott that means is that Michael Scott is the manager of the Scranton branch.

So we were able to define that relation by using these foreign keys.

A Foreign key is able to help us to define relationships between the tables.

Note: It's also important to note that a particular table can have more than one foreign key on it.

Below table have two foreign key.

Composite key:

Composite key is basically a key that needs two attributes.

=========================================================================

SQL Basics

Structured Query Language ( SQL ):

SQL is a language used for interacting with Relational Database Management System( RDBMS )

You can use SQL to get the RDBMS to do things for you.

Create, retrieve, update and delete data
Create and manage databases
Design and create databases tables.
Perform administration tasks ( Security, user management, import/export, etc )

SQL implementations vary between systems

Not all RDBMS follow the SQL standard to "T"
The concepts are the same but the implementation may vary.

Structured Query Language ( SQL )

SQL is actually a hybrid language, it's basically 4 types of languages in one.

Data Query Language ( DQL )

Used to query the database for information.
Get information that is already stored there.

Data Definition Language (DDL )

Used for defining database schemas.

Data Control Language ( DCL )

Used for controlling access to the data in the database.
User and permission management.

Data Manipulation Language ( DML )

Used for inserting, updating and deleting data from the database.

Queries:

A query is a set of instructions given to the RDBMS ( Written is SQL ) that tell the RDBMS what information you want it to retrieve for you.

TONS of data in a DB.
Often hidden in a complex schema.
Goal is to only get the data you need.

SELECT employee.name, employee.age
FROM employee
WHERE employee.salary > 30000

Note: MySql is a Relational Database Management System.

Create a database with below command:
mysql> create database Girrafe;

Girrafe is a database name.

These are the some most common datatype that we are gonna using.

INT------------ > Whole Number

DECIMAL ( M, N )------------------------------> Decimal Number exact value

VARCHAR (l)--------------------------------------> String of text of length l

BLOB ---------------------------------------------- > Binary Large Object, stores large data

DATE ---------------------------> 'YYYY-MM-DD'

TIMESTAMP ------------------------------->'YYYY-MM-DD HH:MM:SS' - Used for recording.

2. Creating a table with student_id, name, major

 
    CREATE TABLE student(
        student_id INT PRIMARY KEY,
        name VARCHAR(20),
        major VARCHAR(20)
    );

    DESCRIBE student;

student_id INT PRIMARY KEY------------------> student id should be primary key

name VARCHAR(20)----------------> Maximum 20 character allow

major VARCHAR(20)---------------> Maximum 20 character allow

DESCRIBE student; ---------------> Describe will show everything about student table.

How to Delete a Table:

 
    DROP TABLE student;
 

DROP TABLE will delete the table

How to add another column into an existing table.

Here I add gpa with value 3 and 2 value after decimal

    
 ALTER TABLE student ADD gpa DECIMAL(3,2);
 

How to delete a particular column from table.

Creating a table and insert value into table

 
    CREATE TABLE student(
        student_id INT PRIMARY KEY,
        name VARCHAR(20),
        major VARCHAR(20)
    );

    INSERT INTO student VALUES (1,'Jack','Biology');

    SELECT * FROM student;

inserting two value only into table. ( student_id and name )

        CREATE TABLE student(
            student_id INT PRIMARY KEY,
            name VARCHAR(20),
            major VARCHAR(20)
        );

    INSERT INTO student (student_id,name) VALUES (2,'Alexandra');

Note: Can not add or insert Duplicate value.

Create a table that name should not Null and major should be Unique.

    CREATE TABLE student(
        student_id INT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        major VARCHAR(20) UNIQUE
    );

    INSERT INTO student VALUES (1,'Ketty','Computer Sc');
    INSERT INTO student VALUES (2,'Jhon','Physics');
    INSERT INTO student VALUES (3,'Akon','Mathematics');

When name Null and major not Unique you will get error. See below

    CREATE TABLE student(
        student_id INT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        major VARCHAR(20) UNIQUE
    );

    INSERT INTO student VALUES (1,'Ketty','Computer Sc');
    INSERT INTO student VALUES (2,'Jhon','Physics');
    INSERT INTO student VALUES (3,'Akon','Mathematics');
 
 
    -- Error: Column 'name' cannot be null
    INSERT INTO student VALUES (4,NULL,'Chemistry');
 

    -- Error: Duplicate entry 'Computer Sc' for key 'student.major'
    INSERT INTO student VALUES (5,'VinDiesel','Computer Sc');
 

Create table that have default major value.

    CREATE TABLE student(
        student_id INT PRIMARY KEY,
        name VARCHAR(20) ,
        major VARCHAR(20) DEFAULT 'Undecided' 
    );

    INSERT INTO student (student_id, name) VALUES(1, "Anjelina");

Create a student table and make that table student_id auto increment automatically.

    CREATE TABLE student(
        student_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) ,
        major VARCHAR(20) 
    );

    INSERT INTO student(name,major) VALUES("Paul","Computer Science");
    INSERT INTO student(name,major) VALUES('Rock', "Chemistry");
    INSERT INTO student(name,major) VALUES('Stathanan', "Mathematics");

    SELECT * FROM student;
 

Note: I using popsql ide.

Update value

Here we updating major 'Computer Science' to 'Com'

Remember it will update all the major 'Computer Science' to 'Com'.

    CREATE TABLE student(
        student_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) ,
        major VARCHAR(20) 
    );

    INSERT INTO student(name,major) VALUES("Paul","Computer Science");

    UPDATE student SET major = 'Com' WHERE major = 'Computer Science';
 

How to update particular Name and major. Using student_id

    CREATE TABLE student(
        student_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) ,
        major VARCHAR(20) 
    );

    INSERT INTO student(name,major) VALUES("Paul","Computer Science");
    INSERT INTO student(name, major) VALUES("Vin", "Chemistry");


    -- Updating name "Paul" to "Alan Walker"
    UPDATE student SET name = 'Alan Walker' WHERE student_id = 1;

    -- Updating major "Chemistry" to "Biology"
    UPDATE student SET major = "Biology" WHERE student_id = 2;

    -- it will show the whole table.
    SELECT * FROM student;
 

Update all major to Bio-Chemistry. Here we use "OR" operator

        CREATE TABLE student(
            student_id INT PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(20) ,
            major VARCHAR(20) 
        );

      INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
      INSERT INTO student(name, major) VALUES("Vin", "Biology");

    
      UPDATE student SET major = 'Bio-Chemistry' WHERE major = 'Computer Science' OR major = 'Biology';

      SELECT * FROM student;

Updating a particular id name and major subject

    CREATE TABLE student(
        student_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) ,
        major VARCHAR(20) 
    );

    INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
    INSERT INTO student(name, major) VALUES("Vin", "Biology");

    -- Updating student_id:2 to Tom and Undecided 
    UPDATE student SET name = 'Tom', major = 'Undecided' WHERE student_id = 2;

Update all major subject to one subject (eg: all major is Maths )

    CREATE TABLE student(
        student_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) ,
        major VARCHAR(20) 
    );

    INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
    INSERT INTO student(name, major) VALUES("Vin", "Biology");

    -- Now all major subject is 'Maths' 
    UPDATE student SET major = 'Maths';

How to delete row.

It will delete all of the row inside the table.

    CREATE TABLE student(
        student_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) ,
        major VARCHAR(20) 
    );

    INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
    INSERT INTO student(name, major) VALUES("Vin", "Biology");

    -- it will delete all of the row inside the table
    DELETE FROM student;

Delete a specific Row

    CREATE TABLE student(
        student_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) ,
        major VARCHAR(20) 
    );

    INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
    INSERT INTO student(name, major) VALUES("Vin", "Biology");
    INSERT INTO student(name, major) VALUES("Michael", "Physics");


    -- deleting a specific row 
    DELETE FROM student WHERE student_id = 2;
   
    SELECT * FROM student;
 

This will delete any of the table who have name = 'Vin' and major = 'Biology'

    CREATE TABLE student(
        student_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) ,
        major VARCHAR(20) 
    );

    INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
    INSERT INTO student(name, major) VALUES("Vin", "Biology");
    INSERT INTO student(name, major) VALUES("Michael", "Physics");

    -- This will delete any of the table who have name = 'Vin' and major = 'Biology' 
    DELETE FROM student WHERE name = 'Vin' AND major = 'Biology';
   
    SELECT * FROM student;
 

Basics Queries

Getting information from the Database.

Show all the name from the table.

    CREATE TABLE student(
        student_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) ,
        major VARCHAR(20) 
    );

    INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
    INSERT INTO student(name, major) VALUES("Vin", "Biology");
    INSERT INTO student(name, major) VALUES("Michael", "Physics");
    INSERT INTO student(name, major) VALUES("Amy", "Com Sc");

    // it will show all the name form the table
    SELECT name FROM student;
 

Show all the name and major from the table

    CREATE TABLE student(
        student_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) ,
        major VARCHAR(20) 
    );

    INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
    INSERT INTO student(name, major) VALUES("Vin", "Biology");
    INSERT INTO student(name, major) VALUES("Michael", "Physics");
    INSERT INTO student(name, major) VALUES("Amy", "Com Sc");

 // it will show all the name and major form the table    SELECT name, major FROM student;
    or
    SELECT student.name, student.major FROM student; 
 

Show all the name and major order by name and order by name with Descending oreder

    CREATE TABLE student(
        student_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) ,
        major VARCHAR(20) 
    );

    INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
    INSERT INTO student(name, major) VALUES("Vin", "Biology");
    INSERT INTO student(name, major) VALUES("Michael", "Physics");
    INSERT INTO student(name, major) VALUES("Amy", "Com Sc");

    // Here we use order by name
    SELECT student.name, student.major FROM student ORDER BY name;
 
    // Here we use order by name with Desending order. 
   SELECT student.name, student.major FROM student ORDER BY name DESC;
 
 

Order by student_id

 
 SELECT student.name, student.major FROM student ORDER BY student_id;

Order by major Descending

 
    SELECT * FROM student ORDER BY major DESC;
 

This will show only 2 row limit

 
    SELECT * FROM student LIMIT 2;
 

It will show only two row limit by Descending order.

 
    SELECT * FROM student ORDER BY student_id DESC LIMIT 2;
 

This is filtering where major is only "Biology". Only that row will show

 
  SELECT * FROM student WHERE major = "Biology";
 

It will only show those that major are Biology and Physics

 
    SELECT name, major FROM student WHERE major = "Biology" OR major = "Physics";