MySQL

mysql -h 128.66.203.247 -u B23CSE1 -psumo@123

use B23CSE1

MAC

/usr/local/mysql/bin/mysql -h 128.66.203.247 -u
B23CSE1 -psumo@123

use B23CSE1
Data Types

MySQL supports various data types, including:

● Numeric types (INT, FLOAT, DOUBLE, etc.)

● String types (VARCHAR, CHAR, TEXT, etc.)
● Date and time types (DATE, TIME, DATETIME, etc.)
● Boolean type (BOOLEAN, BOOL)
CREATE DATABASE

CREATE DATABASE database_name;

CREATE DATABASE [IF NOT EXISTS] database_name;

The CREATE DATABASE IF NOT EXISTS statement in MySQL is used to create a

new database with the specified name if it does not already exist. If the database with
the given name already exists, the statement will have no effect, and no error will be
generated.
Example

CREATE DATABASE IF NOT EXISTS your_database;

In this example, a database named your_database will be created if it doesn't exist. If a

database with that name already exists, nothing will happen, and the statement will not
produce an error.
SHOW TABLES

USE your_database_name;

SHOW TABLES;

Example:

Suppose you want to show the tables in a database called mydatabase:

USE mydatabase;

SHOW TABLES;
DROP DATABASE

DROP DATABASE database_name;

Example:

DROP DATABASE IF EXISTS mydatabase;

NOTE: This example drops the database named mydatabase. If the database
doesn't exist, it won't raise an error.
CREATE TABLE

CREATE TABLE example_table (

id INT,

name VARCHAR(50),

age INT,

birthdate DATE

);
CREATE TABLE
CREATE TABLE table_name (

column1 datatype,

column2 datatype,
...

PRIMARY KEY (one_or_more_columns), -- Optional: Define a primary key

UNIQUE (one_or_more_columns), -- Optional: Define a unique constraint

FOREIGN KEY (column_references) -- Optional: Define a foreign key
REFERENCES referenced_table (referenced_column)
);
Create Table Syntax:

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

first_name VARCHAR(50),

last_name VARCHAR(50),

department_id INT,

FOREIGN KEY (department_id) REFERENCES departments (department_id)

);
 Constraints are rules applied to a column
or a set of columns in a table to enforce
Creating Tables with Constraints:
data integrity. Common constraints
CREATE TABLE students ( include Primary Key, Foreign Key, Unique
student_id INT PRIMARY KEY, Key, Not Null, and Null.
student_name VARCHAR(50) NOT NULL,
date_of_birth DATE,
UNIQUE (student_name)
);

CREATE TABLE courses (

course_id INT PRIMARY KEY,
course_name VARCHAR(50) NOT NULL
);

CREATE TABLE enrollments (

enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
Data Manipulation Commands

Adding Table Rows:

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

INSERT INTO students (student_id, student_name, date_of_birth) VALUES (1,

'John Doe', '2000-01-01');
To add multiple records to a MySQL table Syntax
INSERT INTO your_table (column1, column2, column3)
VALUES
('value1_1', 'value1_2', 'value1_3'),
('value2_1', 'value2_2', 'value2_3'),
('value3_1', 'value3_2', 'value3_3’);

INSERT INTO your_table

VALUES
('value1_1', 'value1_2', 'value1_3'),
('value2_1', 'value2_2', 'value2_3'),
('value3_1', 'value3_2', 'value3_3');
Data Manipulation Commands:
Basic Retrieval Operations:
SELECT * FROM students;

SELECT with WHERE Clause Syntax:

SELECT column1, column2, ...

FROM table_name

WHERE condition;

SELECT employee_id, first_name, last_name, salary

FROM employees
WHERE salary > 50000;
Delete Table Rows:

DELETE FROM students WHERE student_id = 1;

Renaming and Destroying Table:

ALTER TABLE students RENAME TO new_students;

Drop (delete) a table in MySQL

DROP TABLE table_name;

EXAMPLE:

DROP TABLE new_students;

DROP

The DROP statement is used to remove a database, table, index, or view in

MySQL.

DROP TABLE table_name;

TRUNCATE statement

The TRUNCATE statement is used to remove all the records from a table, but it
retains the table structure for future use.

TRUNCATE TABLE table_name;

EXAMPLE:

TRUNCATE TABLE employees;

Create other table from an existing table
Write a query to create other table from an existing table with all fields.
CREATE TABLE new_table_name AS
SELECT * FROM existing_table_name;

EXAMPLE:
CREATE TABLE employees_copy AS
SELECT * FROM employees;
RENAME statement

In MySQL, the RENAME statement can be used to rename tables.

RENAME TABLE old_table_name TO new_table_name;

EXAMPLE:

RENAME TABLE employee TO worker;

DELETE statement

In MySQL, the DELETE statement is used to delete records from a table. The
basic syntax is as follows:

DELETE FROM table_name

WHERE condition;

Suppose you have a table named employees and you want to delete all records
where the department is 'HR':

DELETE FROM employees

WHERE department = 'HR';

EXAMPLE

Write a query that ensures column name id,firstname,lastname will not

accept any null values when employee details table is created.

create table employee_details(id int(11) not null,first_name

char(20) not null,last_name char(20) not null);

desc employee_details;
EXAMPLE
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE,
email VARCHAR(100) UNIQUE,
grade INT CHECK (grade >= 0 AND grade <= 100)
);
EXAMPLE
In this example:
student_id is the primary key, ensuring each student has a unique identifier.

first_name and last_name are marked as NOT NULL, ensuring that these
columns cannot contain null values.

email is marked as UNIQUE, ensuring that each email address is unique across
all records.

grade has a CHECK constraint, ensuring that the grade is between 0 and 100.
EXAMPLE

If you want to alter constraints on the age column in an existing table named
employee_details, you can use the ALTER TABLE statement. Here's an example:

ALTER TABLE employee_details

ADD CHECK (age >= 18);

EXAMPLE
If you want to modify an existing CHECK constraint, you can drop the existing constraint and
add a new one. For instance, if there's an existing CHECK constraint named
check_age_range, and you want to change the condition, you could do:

Drop the existing constraint

ALTER TABLE employee_details

DROP CHECK check_age_range;

Add a new CHECK constraint

ALTER TABLE employee_details

ADD CHECK (age >= 21);

MySQL Aliases

SELECT CustomerName AS Customer, ContactName AS "Contact Person"

FROM Customers;
NOT NULL Constraint

Ensures that a column cannot have NULL values.

column_name data_type NOT NULL

CREATE TABLE Employees (

employee_id INT NOT NULL,
employee_name VARCHAR(50) NOT NULL
);
PRIMARY KEY Constraint

Specifies a column to uniquely identify each row in a table.

column_name data_type PRIMARY KEY

CREATE TABLE Students (

student_id INT PRIMARY KEY,
student_name VARCHAR(50),
age INT
);
FOREIGN KEY Constraint
Specifies a column or a set of columns in one table that refers to the primary key or a unique
key in another table.

column_name data_type REFERENCES

referenced_table_name(referenced_column_name)

CREATE TABLE Orders (

order_id INT PRIMARY KEY,
product_id INT,
quantity INT,
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
DEFAULT Constraint

Specifies a default value for a column when no value is provided.

column_name data_type DEFAULT default_value

CREATE TABLE Contacts (

contact_id INT PRIMARY KEY,
contact_name VARCHAR(50),
phone_number VARCHAR(20) DEFAULT 'N/A'
);
UNIQUE Constraint

Ensures that all values in a column are unique.

CREATE TABLE Products (

product_id INT PRIMARY KEY,
product_name VARCHAR(50),
product_code VARCHAR(20) UNIQUE
);
CHECK Constraint
CHECK(condition)

CREATE TABLE Employees (

employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
salary DECIMAL(10, 2),
CHECK (salary >= 0)
);

NOTE: DECIMAL(10, 2) means that the salary column can store numeric values with a
total of 10 digits, with 2 digits allowed after the decimal point. For example, it can store
values like 12345.67 or 9876.54.
ALTER TABLE statement to rename a column
In MySQL, you can use the ALTER TABLE statement to rename a column. The syntax is
as follows:

ALTER TABLE table_name

CHANGE COLUMN old_column_name new_column_name data_type;

table_name: The name of the table containing the column.

old_column_name: The current name of the column.

new_column_name: The new name you want to give to the column.

data_type: The data type of the column (optional if you're only changing the column name).
EXAMPLE: ALTER TABLE statement to rename a column

Suppose you have a table named employees with a column named

emp_firstname, and you want to rename it to first_name:

ALTER TABLE employees

CHANGE COLUMN emp_firstname first_name VARCHAR(255);

Rename two or more than two columns.

To rename two or more columns in MySQL, you can use the ALTER TABLE
statement with multiple CHANGE COLUMN clauses. Here's the syntax:

ALTER TABLE table_name

CHANGE COLUMN old_column1_name new_column1_name data_type,

CHANGE COLUMN old_column2_name new_column2_name data_type,

...;
EXAMPLE: Rename two or more than two columns.

ALTER TABLE employees

CHANGE COLUMN emp_firstname first_name VARCHAR(255),

CHANGE COLUMN emp_lastname last_name VARCHAR(255),

CHANGE COLUMN emp_salary salary DECIMAL(10,2);

Update Table:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE is a SQL command used to modify the existing records in a table. It

allows you to change the values of one or more columns in one or more rows
based on a specified condition.
UPDATE students SET date_of_birth = '1999-12-31' WHERE student_id = 1;
Alter Table:

ALTER TABLE students ADD COLUMN email VARCHAR(50);

ALTER is a SQL command used to modify the structure of a table. It allows you to
add, modify, or delete columns in an existing table.

ALTER TABLE table_name

ADD COLUMN column_name datatype;

Primary Key Constraint

In MySQL, when you define a constraint, you can explicitly specify its name. This is
particularly useful when you want to have more control over the constraint's name for
easier identification, management, or when you're working with foreign key constraints.

CREATE TABLE table_name (

column1 INT,
column2 VARCHAR(50),
PRIMARY KEY (column1) CONSTRAINT pk_constraint_name
);
 Unique Constraint

CREATE TABLE table_name (

column1 INT,
column2 VARCHAR(50),
UNIQUE (column1) CONSTRAINT unique_constraint_name
);
Check Constraint (Introduced in MySQL 8.0.16 and later)

CREATE TABLE table_name (

column1 INT,
column2 VARCHAR(50),
CONSTRAINT check_constraint_name CHECK (column1 > 0)
);
Adding Constraint Later using ALTER TABLE

ALTER TABLE table_name

ADD CONSTRAINT constraint_name constraint_type (column1);
Foreign Key Constraint

CREATE TABLE child_table (

column1 INT,
column2 VARCHAR(50),
FOREIGN KEY (column1) REFERENCES parent_table (column1)
CONSTRAINT fk_constraint_name
);
DATABASE USERS
• For a small personal database, one person defines, constructs, and manipulates the database.
• For a large organization, many people are involved in the design, use and maintenance of the large database.

work to maintain database

environment and are not involved in
its contents

day-to-day use of a large database

• Authorizing access to the database
• Coordinating and monitoring its use
• Acquiring software and hardware resources
These users are computer professionals who
• Security issues and poor system response time
write application programs using some
tools. E.g., Software developers

• Identifying the data to be

stored People whose jobs require access to
the database for querying, updating
• Choosing appropriate
and generating reports.
structures to represent
and store this data

High level Managers

Occasionally access the
database for different
types of information Reservation agents E.g. Analyst. Maintain personal
These users interact with system databases using ready
each time. Unsophisticated users who have made program
without writing program. They
very less knowledge of database form their request in a database
system. e.g. Clerk in bank query language. E.g. Analyst.
What is an Instance ?

● An Instance is the collection of information stored in the database at a particular time.

• For example, if we have a table schema that stores student information, an instance would be the data
stored in the table, i.e., all students' names, roll numbers, email addresses, etc.
DATABASE STATE: CURRENT STATE OF DATABASE
Schema

• A schema represents the logical structure of a database.

• It is a blueprint that defines how data should be stored and organized in a database. A
schema also describes what data can be stored in which table and how these tables are
related to each other.
 External Schema

It represents how the data is visible to users.

Describes the logical structure of the database

Conceptual Schema

Internal Schema

Database design at a Lowest level

This schema is hidden from the users and managed
by the DBA.