1 / 20

Databases

Databases. Using MySQL Creating Tables Queries. Databases. A database is a collection of data organized for efficient access A relational database is a collection of tables Columns represent attributes Rows represent entities Usually, want one attribute (primary attribute) to be unique.

dian
Download Presentation

Databases

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. Databases Using MySQL Creating Tables Queries

  2. Databases • A database is a collection of data organized for efficient access • A relational database is a collection of tables • Columns represent attributes • Rows represent entities • Usually, want one attribute (primary attribute) to be unique

  3. Sample Table

  4. Sample Tables

  5. Structured Query Language • A language create, query and modify relational databases. • SQL commands are not case-sensitive • table and column names are case sensitive in MySQL • White space is not critical • Strings are delimited by single quotes • SQL is not procedural - you specify what result you want not how to get to that result

  6. MySQL • There are a number of database vendors • MySQL is a free database system • installed on onyx • Interacting with MySQL from a shell mysql -p -u php_user show databases \g use databaseName \g show tables \g source loadTables.sql \q

  7. Programming with MySQL • Many programming languages have modules or libraries that support access to databases • perl has the DBI module • PHP has separate modules for each database type as well as the PEAR module which provides a uniform interface to all of them • Java has the JDBC API (java.sql)

  8. Setting Up a Database • CREATE DATABASE databaseName; • USE databaseName; • CREATE TABLE tableName (col1 type1, col2 type2, …); • DROP DATABASE databaseName; • DROP TABLE tableName

  9. Types for Attributes

  10. Modifiers and Keys • NOT NULL for attribute that must be given a value • DEFAULT "value" to provide default value • int values can be labeled auto_increment to automatically generate unique numbers • PRIMARY KEY (columnName) • KEY keyName (colName1[,colName2])

  11. Example 1 CREATE TABLE DISHES (dish_id INT, dish_name VARCHAR(255), price DECIMAL(4,2), is_spicy INT);

  12. Modifying the Table Data • INSERT • UPDATE • DELETE

  13. Inserting data • INSERT INTO tableName VALUES (v1, v2, …,vn); • use NULL as placeholder for missing values • can insert multiple rows at a time using comma-separated lists • INSERT INTO tableName SET cola=va, colb = vb; • provide values for all columns that can't be null • INSERT INTO tableName (cola, colb, …) VALUES (va, vb, …)

  14. Deleting Data • DELETE FROM tableName; • removes all data from the table • DELETE FROM tableName WHERE colName=value;

  15. Updating Data • UPDATE tableName SET col = value; • updates value of col for all rows in table • UPDATE tableName SET col = value WHERE col=value; • updates only rows which satisfy WHERE clause

  16. Querying the database • SELECT * FROM tableName • show entire table • SELECT col1, col2 FROM tableName • show listed columns from tableName for all rows • SELECT * from tableName WHERE col=value • show rows that match selection criteria

  17. Ordering and Grouping • SELECT * FROM tableName ORDER BY colName [DESC] • sort the rows • You can group rows with the same value in a particular column so that you can compute aggregate values (count, sum, avg, max, min) • SELECT colName, count(*) FROM tableName GROUP BY col2 • Use HAVING with a condition to select groups for which an aggregate function meets a particular requirement

  18. WHERE Clauses • WHERE clause limits the rows retrieved by specifying a condition that must be met • look for a particular value or range of values in some column • Use AND and OR to combine conditions • use LIKE for string matching

  19. Things I left out • JOIN - sometimes you need to combine information from two or more tables in a single query. • You can use LIMIT to restrict the number of rows in the output • SELECT DISTINCT outputs a single copy of identical rows in the output

  20. Sources • MySQL website • http://www.mysql.com/ • MySQL Visual Quickstart Guide by Larry Ullman • PHP and MySQL by Hugh E. Williams and David Lane

More Related