SQL Database

Let’s Start:
 MySQL is a database system used on the web
 MySQL is a database system that runs on a server
 MySQL is ideal for both small and large applications
 MySQL is very fast, reliable, and easy to use
 MySQL uses standard SQL
 MySQL compiles on a number of platforms
 MySQL is free to download and use
 MySQL is developed, distributed, and supported by Oracle Corporation
 MySQL is named after co-founder Monty Wideness’s daughter: My

The data in a MySQL database are stored in tables.

A table is a collection of related data, and it consists of columns and rows.

SQL vs MYSQL:
SQL: It is a structured query programming language that manages the
relational database management system
MYSQL: It is a relational database management system that uses SQL.

A query is a question or a request.

Query: Database Code.
Database:
CREATE DATABASE databasename;
DROP DATABASE databasename;

Table:
- CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
- DROP TABLE table_name;
- TRUNCATE TABLE table_name;
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Data Types:
- VARCHAR(size)
- TEXT(size)
- BOOL
- INT(size)
- INTEGER(size)
- FLOAT(size, d)
- DATE
Constraints:
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);

 Not Null:
o ID int NOT NULL,

 UNIQUE:
o ID int NOT NULL UNIQUE,
 Primary Key:
o ID int NOT NULL PRIMARY KEY,
o CONSTRAINT PK_Person PRIMARY KEY (ID)
 FOREIGN KEY:
o FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
 Check:
o Age int CHECK (Age>=18)
o CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sand
nes')
 DEFAULT:
o City varchar(255) DEFAULT 'Sandnes'
o OrderDate date DEFAULT GETDATE()
 AUTO INCREMENT:
o Personid int NOT NULL AUTO_INCREMENT,

CREATE TABLE posts (

id int(10) ,

post TEXT NOT NULL,

user_id int(10),

CONSTRAINT pk_posts PRIMARY KEY(id),

CONSTRAINT fk_post_user FOREIGN KEY (user_id) REFERENCES

users(id)
Alter Table:
 ALTER TABLE table_name
ADD column_name datatype;
o ALTER TABLE Customers
ADD Email varchar(255);

 ALTER TABLE table_name

DROP COLUMN column_name;
o ALTER TABLE Customers
DROP COLUMN Email;

 ALTER TABLE table_name

RENAME TO new_table_name;
o ALTER TABLE Student RENAME TO Student_Details;

 ALTER TABLE table_name

RENAME COLUMN old_name to new_name;
o ALTER TABLE Student RENAME Column name TO
FIRST_NAME;
Insert Into Table:
 INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
 INSERT INTO table_name
VALUES (value1, value2, value3, ...);
o INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

Update Statement:
 UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
o UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
o UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';
o UPDATE Customers
SET ContactName='Juan';

Delete Statement:
 DELETE FROM table_name WHERE condition;
o DELETE FROM Customers WHERE
CustomerName='Alfreds Futterkiste';
 DELETE FROM table_name;
o DELETE FROM Customers;

Drop Table:
 DROP TABLE Customers;
Select Data From Database:
 SELECT column1, column2, ...
FROM table_name;
o SELECT CustomerName, City FROM Customers;

 SELECT * FROM Customers;

o return all columns

DISTINCT:
 SELECT DISTINCT column1, column2, ...
FROM table_name;
o SELECT DISTINCT Country FROM Customers;
o SELECT Country FROM Customers;

WHERE Clause: used to filter records

 SELECT column1, column2, ...

FROM table_name
WHERE condition;
o SELECT * FROM Customers
WHERE Country='Mexico';
o SELECT * FROM Customers
WHERE CustomerID=1;
o SELECT * FROM Customers
WHERE CustomerID > 80;

Operators that can be used in the where clause:

Ex)
Between:
 SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
o SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
o SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20

IN:
 SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
o SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
o SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

AND:
 SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
o SELECT * FROM Customers
WHERE Country = 'Germany'
AND City = 'Berlin'
AND PostalCode > 12000;

OR:
 SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
o SELECT *
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';
NOT
 SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
o SELECT * FROM Customers
WHERE NOT Country = 'Spain';

LIKE:
 SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

 The percent sign % represents zero, one, or multiple characters

 The underscore sign _ represents one, single character

o SELECT * FROM Customers

WHERE CustomerName LIKE 'a%';
o SELECT * FROM Customers
WHERE city LIKE 'L_nd__';
o SELECT * FROM Customers
WHERE city LIKE '%L%';
o SELECT * FROM Customers
WHERE CustomerName LIKE 'La%';
o SELECT * FROM Customers
WHERE CustomerName LIKE '%a';
o SELECT * FROM Customers
WHERE CustomerName LIKE 'b%s';
o SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';
o SELECT * FROM Customers
WHERE CustomerName LIKE 'a%' OR CustomerName LIKE 'b
%';
here