Mysql

What is a database

store data : so it can be persisted
structure data : so it is easier to manipulate
process data : to derive data value from it

DAtabases

a standard interface for accessing data
multiple user with simultaneous ability to modify data.
changes to the data without risk of losing data
tools for data backup, restore and recovery
security

RDBMS

relational database management system (RDBMS)
based on the relational model

Relational Data Model means:

tables
rows
colums
data type

ER diagram

dataBase Design

Primary keys

uniquely specifies an record within a table
composed from one or more data attributes of that data entity.

Foreign keys

a field in a relational table that matches the primary key column of another table

Index

way of providing quicker access to data.

Database normalization

FN1

there are no repeating or duplicate keys
each records is unique
each cell contains only a value

FN2

all non-keys fields depend on all components of the primary key

FN3

no non-key field depends upon another

SQL

Structured Query Language

DDL: defines properties of data objects

create, alter, drop

DML: used to retrieve, add, edit and delete data

select, insert, update, delete

DCL : controls acces to databases objects

grant, revoke

Transaction control: groups dml statements into trasactions

 commit, rollback

Database transaction

unit of work performed within a database management system against a database
by defition is ACID: atomic, consistent, isolated and durable

BEGIN WORK //do work here
//commit or rollback at the end of the workCOMMIT  ROLLBACK

Create tables

CREATE TABLE employee (
   id INT NOT NULL AUTO_INCREMENT,
   firstName VARCHAR(100) NOT NULL,
   lastName VARCHAR(100) NOT NULL,
   hiredDate DATE,
   salary INT, 
   PRIMARY KEY ( id )
 );

NOT NULL is being used because we do not want this field to be NULL.
AUTO_INCREMENT tells to MySQL to go ahead and add the next available number to the id field.
PRIMARY KEY is used to define a column as primary key. You can use multiple columns separated by comma to define a primary key.

Select statement

select <%EXPRESSIONS%> from <%TABLE REFERENCES%>
select * from employee;
select * from employee where salary > 1000;
select * from employee order by salary desc;

Where: only some rows are returned
Group by: allows you to consolidate the query
Order by: allows you to sort the query

Aggregate functions

Count

 select count(*) from employee;

select sum(salary) from employee;

select sum(salary) from employee group by departamentId;

Insert statement

 insert into employee(id, firstName, lastName) values ( 1, "Posa", "Bogdan");

Update statement

update employee set salary = 1000;
update employee set salary = 1000 where id = 1;

Delete statement

delete from employee;
delete from employee where id = 1;

Transactions

begin work;
delete from employee;
select * from employee;
rollback;

JOINs

inner join

 select * from employee e 
   join departament d on d.id = e.departamentId

left join

 select * from employee e    left join departament d on d.id = e.departamentId;

right join

 select * from employee e 
   right join departament d on d.id = e.departamentId;