1 / 8

Day 3 - Basics of MySQL

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:

zorion
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Day 3 - Basics of MySQL What is MySQL How to make basic tables Simple MySQL commands.

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  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”; Day 3 - Basics of MySQL

More Related