Scribd
Upload
2 views

MySQL-class-4

The document provides a comprehensive overview of commonly used MySQL queries for database management, including creating databases, tables, and records, as well as updating, deleting, and selecting records. It also explains MySQL constraints, detailing types such as NOT NULL, UNIQUE, CHECK, DEFAULT, and ENUM, along with examples for each. Additionally, it highlights the differences between DELETE and TRUNCATE commands in SQL.

Uploaded by

Candy Man
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views

MySQL-class-4

The document provides a comprehensive overview of commonly used MySQL queries for database management, including creating databases, tables, and records, as well as updating, deleting, and selecting records. It also explains MySQL constraints, detailing types such as NOT NULL, UNIQUE, CHECK, DEFAULT, and ENUM, along with examples for each. Additionally, it highlights the differences between DELETE and TRUNCATE commands in SQL.

Uploaded by

Candy Man
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 20

MySQL Queries

A list of commonly used MySQL queries to create database, use


database, create table, insert record, update record, delete
record, select record, truncate table and drop table are given
below.

1) MySQL Create Database


MySQL create database is used to create database. For example

create database db1;

2) MySQL Select/Use Database


MySQL use database is used to select database. For example

use db1;
3) MySQL Create Query
MySQL create query is used to create a table, view, procedure and
function. For example:

CREATE TABLE customers


(id int(10),
name varchar(50),
city varchar(50),
PRIMARY KEY (id )
);

4) MySQL Alter Query


MySQL alter query is used to add, modify, delete or drop colums of a
table. Let's see a query to add column in customers table:

ALTER TABLE customers


ADD age varchar(50);
5) MySQL Insert Query
MySQL insert query is used to insert records into table. For
example:

insert into customers values(101,'lrahu','delhi');

6) MySQL Update Query


MySQL update query is used to update records of a table. For
example:

update customers set name='bob', city='london' where id=101;


7) MySQL Delete Query
MySQL update query is used to delete records of a table from
database. For example:

delete from customers where id=101;

8) MySQL Select Query


Oracle select query is used to fetch records from database. For
example:

SELECT * from customers;


9) MySQL Truncate Table Query
MySQL update query is used to truncate or remove records of a
table. It doesn't remove structure. For example:

truncate table customers;

10) MySQL Drop Query


MySQL drop query is used to drop a table, view or database. It
removes structure and data of a table if you drop table. For
example:

drop table customers;


DELETE is a SQL command that removes one or

multiple rows from a table using conditions.

TRUNCATE is a SQL command that removes all the

rows from a table without using any condition.


MySQL Constraints

The constraint in MySQL is used to specify the rule that allows or restricts
what values/data will be stored in the table.

Types of MySQL Constraints

Constraints in MySQL is classified into two types:

Column Level Constraints: These constraints are applied only to the

single column that limits the type of particular column data.

Table Level Constraints: These constraints are applied to the entire

table that limits the type of data for the whole table.
CREATE TABLE new_table_name (
col_name1 datatype constraint,
col_name2 datatype constraint,
col_name3 datatype constraint,
.........
);
Constraints used in MySQL
The following are the most common constraints used in the
MySQL:
NOT NULL
CHECK
DEFAULT
PRIMARY KEY
AUTO_INCREMENT
UNIQUE
INDEX
ENUM
FOREIGN KEY
NOT NULL Constraint
This constraint specifies that the column cannot have NULL or empty
values. The below statement creates a table with NOT NULL constraint.
mysql> CREATE TABLE Student(Id INTEGER, LastName TEXT NOT NULL, Fi
rstName TEXT NOT NULL, City VARCHAR(35));
Execute the queries listed below to understand how it works:
mysql> INSERT INTO Student VALUES(1, 'Hanks', 'Peter', 'New York');

mysql> INSERT INTO Student VALUES(2, NULL, 'Amanda', 'Florida');


UNIQUE Constraint
This constraint ensures that all values inserted into the column will be
unique. It means a column cannot stores duplicate values. MySQL allows
us to use more than one column with UNIQUE constraint in a table. The
below statement creates a table with a UNIQUE constraint:
mysql> CREATE TABLE ShirtBrands(Id INTEGER, BrandName VARCHAR(40)
UNIQUE, Size VARCHAR(30));
Execute the queries listed below to understand how it works:
mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Pantalo
ons', 38), (2, 'Cantabil', 40);

mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Raymon


d', 38), (2, 'Cantabil', 40);
CHECK Constraint

mysql> CREATE TABLE Persons (


ID int NOT NULL,
Name varchar(45) NOT NULL,
Age int CHECK (Age>=18)
);
Execute the listed queries to insert the values into the table:
mysql> INSERT INTO Persons(Id, Name, Age)
VALUES (1,'Robert', 28), (2, 'Joseph', 35), (3, 'Peter', 40);

mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Robert', 15);


DEFAULT Constraint

mysql> CREATE TABLE Persons (


ID int NOT NULL,
Name varchar(45) NOT NULL,
Age int,
City varchar(25) DEFAULT 'New York'
);
Execute the listed queries to insert the values into the table:
mysql> INSERT INTO Persons(Id, Name, Age, City)
VALUES (1,'Robert', 15, 'Florida'),
(2, 'Joseph', 35, 'California'),
(3, 'Peter', 40, 'Alaska');

mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Brayan', 15);


ENUM Constraint
The ENUM data type in MySQL is a string object. It allows us to limit the value
chosen from a list of permitted values in the column specification at the time of
table creation. It is short for enumeration, which means that each column may
have one of the specified possible values. It uses numeric indexes (1, 2, 3…) to
represent string values.
The following illustration creates a table named "shirts" that contains three
columns: id, name, and size. The column name "size" uses the ENUM data type
that contains small, medium, large, and x-large sizes.
mysql> CREATE TABLE Shirts (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(35),
size ENUM('small', 'medium', 'large', 'x-large')
);
Next, we need to insert the values into the "Shirts" table using the below
statements:
mysql> INSERT INTO Shirts(id, name, size)
VALUES (1,'t-shirt', 'medium'),
(2, 'casual-shirt', 'small'),
(3, 'formal-shirt', 'large');
Now, execute the SELECT statement to see the inserted values into the table:
mysql> SELECT * FROM Shirts;
Output

You might also like