Day 3 basics of mysql
This presentation is the property of its rightful owner.
Sponsored Links
1 / 8

Day 3 - Basics of MySQL PowerPoint PPT Presentation


  • 113 Views
  • Uploaded on
  • Presentation posted in: General

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:

Download Presentation

Day 3 - Basics of MySQL

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Day 3 basics of mysql

Day 3 - Basics of MySQL

What is MySQL

How to make basic tables

Simple MySQL commands.


What is mysql

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:

    • 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


What is a relational database

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.

Day 3 - Basics of MySQL


Data types basics

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.

Day 3 - Basics of MySQL


Create table command

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);

Day 3 - Basics of MySQL


Alter table drop table command

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.

Day 3 - Basics of MySQL


Insert command

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”);

Day 3 - Basics of MySQL


Delete update commands

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”;

Day 3 - Basics of MySQL


  • Login