Transcript Day 2 - Basic Database Backbone

Day 3 - Basics of MySQL
What is MySQL
 How to make basic tables
 Simple MySQL commands.

What is MySQL?


MySQL is a true multi-user, multi-threaded SQL
database server. SQL is the most popular
relational database in the world.
Goals of system:
–
–
–
–
–
CSE 498
Client/server implementation of a relational database system.
Speed of transactions and queries going through.
Robustness of the system.
Easy to use and learn.
Be able to handle large systems easily and quickly.
Day 3 - Basics of MySQL
2/8
What is a relational database?

Relational DB
– A relational database is the reality of a E/R diagram model. It
uses the basic parts of the E/R diagram and enforces them in a
database.

Basic Parts
– Keys & Attributes are the same
– Table: Entity relation of a E/R diagram.
– Record: An instance of a row of data in a table.
CSE 498
Day 3 - Basics of MySQL
3/8
Data Types & Basics

Data Types
–
–
–
–
–

Integers: TinyInt, SmallInt, MediumInt, Int, BigInt
Real Numbers: Float, Double, Decimal, Real
Time: Date, DateTime, Timestamp, Time, Year (Y2K Compliant)
String: Char, VarChar
Text: TinyText, MediumText, LongText
Basic MySQL Commands
– Note: All commands/queries end with a semi-colon.
– show tables;
• Command will show you the name of all the tables currently in the DB.
–
desc <table name>;
• Command will show you information about the requested table.
CSE 498
Day 3 - Basics of MySQL
4/8
CREATE TABLE command

Command for creating a table in the DB
– CREATE TABLE table_name (attribute definition, …)
– attribute definition : column_name data_type (options)
– options : NOT NULL/NULL, AUTO INCREMENT,
PRIMARY KEY, DEFAULT default_value,
(reference_definition)
– reference_definition :
REFERENCE table_name (column_name, …)
MATCH FULL/MATCH PARTIAL

Example: Category table from Day 2
> CREATE TABLE category (cat_id INT NOT NULL PRIMARY KEY, cat_name var_char NOT NULL);
CSE 498
Day 3 - Basics of MySQL
5/8
ALTER TABLE & DROP TABLE Command

ALTER TABLE - Used to change a table.
– ALTER TABLE table_name (alter_spec)
– Three basic alter_specs:
• ADD : Used to add a column, index specification, or primary key specification
• CHANGE: Used to change a column specification in the table
• DROP: Used to drop a column from the table.
– Example: Add a column to the category table.
> ALTER TABLE category ADD cat_alias var_char NULL;

DROP TABLE - Used to delete a table.
– DROP TABLE table_name
– Note: Be careful with this one because once it’s gone, it’s gone.
CSE 498
Day 3 - Basics of MySQL
6/8
Insert Command


Used to insert data into tables.
Format
– INSERT INTO table_name (col_name, …) VALUES
(expression, …), (expression, …), …

Example: Insert info into the category table…
> INSERT INTO category (cat_id, cat_name) VALUES (1, “Action/Adventure”);
CSE 498
Day 3 - Basics of MySQL
7/8
DELETE & UPDATE Commands


Both commands use a query format to run the
command on the data in a table. Queries are
explained in the next set of slides.
DELETE - Used to delete information from a table.
– DELETE FROM table_name WHERE (rule)
– Example: Delete the ‘oldies’ category from the table.
> DELETE FROM category WHERE cat_name = “Oldies”;

UPDATE - Used to update table information.
– UPDATE table_name SET col_name = expression WHERE (rule)
– Example: Change category “Scary Movies” to “Horror”
> UPDATE category SET cat_name = “Horror” WHERE cat_name = “Scary Movies”;
CSE 498
Day 3 - Basics of MySQL
8/8