Day 3 - Basics of MySQL

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