Database Basics

							       What can a database do?



 Store information
 Edit information

 Delete information

 Retrieve information
                      SQL



SQL (Structured Query Language) is a language
used for interacting with databases.

SELECT artist FROM songs WHERE songtitle = ‘Downer';
       MySQL Tables & Columns


SONGS
song_id     3
songtitle       Downer
artist          Nirvana
album           Bleach
length          1:48
genre           Rock
         MySQL Column Types

After you determine which columns you'll need in
your table, you'll need to define a datatype for
each column. There are three basic types:

 Text
 Numbers

 Dates and times
Woah, that’s a lot …
             Common Datatypes

MEDIUMINT    used for IDs
VARCHAR      used for varying lengths (255 or less)
CHAR      used for fixed lengths
TEXT         used to store lots of text like blog entries
DATETIME     used for storing dates
                Creating a table

CREATE TABLE songs (
song_id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
songtitle VARCHAR(100) NOT NULL,
seconds MEDIUMINT(100) NOT NULL,
artist VARCHAR(100) NOT NULL,
album VARCHAR(100) NOT NULL,
genre VARCHAR(100) NOT NULL,
lastplayed DATETIME NOT NULL,
PRIMARY KEY(song_id)‫‏‬
);


* Note: You can use PHPMyAdmin to create tables as well.
                 Primary Key


 Every table should have one.
 It should contain a value.

 That value must never change.

 It must be unique for each record in the table.



Think of a primary key as a license plate for a car.
Or, a person’s social security number
              Auto_increment



If you add 'AUTO_INCREMENT' into the column
description, it will assign the next-highest number
available for the columns value. This is commonly
used in conjunction with Primary Keys.
                 Inserting Rows

INSERT INTO songs (
     name,
     duration,
     artist,
     album,
     genre
) VALUES (
     'Carriage',
     '4:05',
     'Counting Crows',
     'Hard Candy',
     'Rock'
);
                 Selecting Rows



SELECT name, artist FROM songs;

SELECT name FROM songs WHERE artist = 'Counting Crows';

SELECT name FROM songs ORDER BY song_id;

SELECT * FROM songs LIMIT 10;
                 Updating Rows




UPDATE songs SET name = 'Carriage' WHERE name = 'Cariage';
                 Deleting Rows




DELETE FROM songs WHERE artist = 'Counting Crows';
                      Functions



SELECT count(*) FROM songs;



Text: CONCAT(), LENGTH(), UPPER(), LOWER()‫‏‬
Numbers: ABS(), CEILING(), FLOOR(), FORMAT()‫‏‬
               Formatting Dates




SELECT DATE_FORMAT(lastplayed, '%M %e, %Y');