What is Database?
A database is a separate application that stores a collection of data. Each database
has one or more distinct APIs for creating, accessing, managing, searching and
replicating the data it holds.
Other kinds of data stores can be used, such as files on the file system or large hash
tables in memory, but data fetching and writing would not be so fast and easy with
those types of systems.
So nowadays, we use relational database management systems (RDBMS) to store
and manage huge volume of data. This is called relational database because all the
data is stored into different tables and relations are established using primary keys or
other keys known as foreign keys.
A Relational DataBase Management System (RDBMS) is a software that:
Enables you to implement a database with tables, columns and indexes.
Guarantees the Referential Integrity between rows of various tables.
Updates the indexes automatically.
Interprets an SQL query and combines information from various tables.
RDBMS Terminology:
Before we proceed to explain MySQL database system, let's revise few definitions related to database.
Database: A database is a collection of tables, with related data.
Table: A table is a matrix with data. A table in a database looks like a simple
spreadsheet.
Column: One column (data element) contains data of one and the same kind, for
example the column postcode.
Row: A row (= tuple, entry or record) is a group of related data, for example the data
of one subscription.
Redundancy: Storing data twice, redundantly to make the system faster.
Primary Key: A primary key is unique. A key value can not occur twice in one table.
With a key, you can find at most one row.
Foreign Key: A foreign key is the linking pin between two tables.
create database retail;
use retail;
CREATE TABLE salespeople (
snum INT NOT NULL,
sname VARCHAR(30) NOT NULL,
city VARCHAR(30) NOT NULL,
comm DECIMAL(4,2) NOT NULL,
PRIMARY KEY (snum)
);
INSERT INTO salespeople VALUES (1001, 'Peel', 'London', 0.12);
Salespeople
SNUM SNAME CITY COMM
1001 Peel London .12
1002 Serres San Jose .13
1004 Motika London .11
1007 Rifkin Barcelona .15
1003 AxelRod New York .10
1005 Fran London .26
CREATE TABLE customer (
cnum INT NOT NULL,
cname VARCHAR(30) NOT NULL,
city VARCHAR(30) NOT NULL,
rating int not null,
snum int NOT NULL,
PRIMARY KEY (cnum),
FOREIGN KEY (snum) REFERENCES salespeople(snum)
);
INSERT INTO customer VALUES (2001, 'Hoffman', 'London',100,
1001);
Customers
CNUM CNAME CITY RATING SNUM
2001 Hoffman London 100 1001
2002 Giovanni Rome 200 1003
2003 Liu San Jose 200 1002
2004 Grass Berlin 300 1002
2006 Clemens London 100 1001
2008 Cisneros San Jose 300 1007
2007 Pereira Rome 100 1004
CREATE TABLE orders (
onum INT NOT NULL,
amt DECIMAL(7,2) NOT NULL,
odate Date NOT NULL,
cnum int NOT NULL,
PRIMARY KEY (onum),
FOREIGN KEY (cnum) REFERENCES customer(cnum)
);
INSERT INTO orders VALUES (3001, 18.69, '1996-03-10', 2008);
Orders
ONUM AMT ODATE CNUM
3001 18.69 10/03/96 2008
3003 767.19 10/03/96 2001
3002 1900.10 10/03/96 2007
3005 5160.45 10/03/96 2003
3006 1098.16 10/03/96 2008
3009 1713.23 10/04/96 2002
3007 75.75 10/04/96 2002
3008 4723.00 10/05/96 2006
3010 1309.95 10/06/96 2004
3011 9891.88 10/06/96 2006
show tables ;
describe orders;
Update clause
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
Delete clause
DELETE FROM table_name [WHERE Clause]
