MySQL lecture

MySQL lecture

• 1.
  MySQL Database OverviewMySQL Database Overview Instructor: Rick Palmer, SCWCD [email_address]
• 2.
  Topics Covered DatabaseConcepts Standard CRUD Operations using MySQL
• 3.
  Database Concepts RefresherEntity: an object, concept, or event. Field (column): describes a single characteristic of an entity. Record (row): collection of fields (characteristics) describing each entity. Table: collection of records for a specific entity. Database: collection of tables.
• 4.
  MySQL Open Sourcerelational database management system: www.mysql.com Supports Structured Query Language (SQL) – a standardized way to communicate with databases. Very popular (NASA, Yahoo, Powell's Books, Novell, U.S. Census Bureau, etc)
• 5.
  Navigating in MySQLGet a list of existing databases: mysql> show databases; Specify the database to use: mysql> use mysql; Get a list of tables in the database: mysql> show tables; Describe a table: mysql> describe user;
• 6.
  MySQL CRUD Operations Create a new database: mysql> CREATE DATABASE cis; Create a new table: mysql> use cis; -> CREATE TABLE vehicles -> (VIN varchar(17), Year int(4), Make varchar(20), -> Model varchar(20), Mileage int(10)); Insert data into the table: mysql> INSERT INTO vehicles (VIN, Year, Make, Model, Mileage) -> VALUES ('8YTR754', 2002, 'Ford', 'Mustang', 21000); Create a SQL script describing the database : C:\mysql\bin > mysqldump –u root –p cis > cis.sql Create a database and data using a SQL script: mysql> source C:\mysql\ bin\cis.sql (NOTE: no semi-colon)
• 7.
  MySQL C RUD Operations Retrieving all data from a table: mysql> SELECT * FROM vehicles; +----------+-------+-----------+----------+----------+ | VIN | Year | Make | Model | Mileage | +----------+-------+-----------+----------+----------+ | 8YTR754 | 2002 | Ford | Mustang | 21000 | | 4GKU390 | 2001 | Chevrolet | Corvette | 35000 | | 92A84UJ | 1998 | Dodge | Viper | 89256 | +----------+-------+-----------+----------+----------+ Selecting a specific row of data: mysql> SELECT * FROM vehicles WHERE VIN = '8YTR754'; +----------+-------+-----------+----------+----------+ | VIN | Year | Make | Model | Mileage | +----------+-------+-----------+----------+----------+ | 8YTR754 | 2002 | Ford | Mustang | 21000 | +----------+-------+-----------+----------+----------+
• 8.
  MySQL CR UD Operations Update all records in a table mysql> UPDATE vehicles SET Mileage = 25000; Update specific records mysql> UPDATE vehicles SET Mileage = 25000 -> WHERE VIN = '8YTR754'; Update multiple columns of a specific record mysql> UPDATE vehicles -> SET Mileage = 25000, LastServiceDate = '08/30/2003' -> WHERE VIN = '8YTR754';
• 9.
  MySQL CRU D Operations Delete all rows in a table (does not delete table) mysql> DELETE FROM vehicles; Delete specific rows in a table mysql> DELETE FROM vehicles -> WHERE VIN = '8YTR754'; Delete the entire table (remove from database) mysql> DROP TABLE vehicles;