a guide to mysql
DESCRIPTION
7. A Guide to MySQL. Objectives. Understand, define, and drop views Recognize the benefits of using views Use a view to update data Grant and revoke users’ database privileges Understand the purpose, advantages, and disadvantages of using an index. Objectives (continued). - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/1.jpg)
A Guide to MySQL
7
![Page 2: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/2.jpg)
A Guide to MySQL 2
Objectives
• Understand, define, and drop views
• Recognize the benefits of using views
• Use a view to update data
• Grant and revoke users’ database privileges
• Understand the purpose, advantages, and disadvantages of using an index
![Page 3: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/3.jpg)
A Guide to MySQL 3
Objectives (continued)
• Create, use, and drop an index
• Understand and obtain information from the system catalog
• Use integrity constraints to control data entry
![Page 4: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/4.jpg)
A Guide to MySQL 4
Defining and Using Views
• View: an application program’s or individual user’s picture of the database
• Base tables: existing, permanent tables in a relational database
• View is a derived table because data in it is retrieved from the base table
![Page 5: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/5.jpg)
A Guide to MySQL 5
Defining and Using Views (continued)
• Not supported in MySQL 4.1; is supported in MySQL 5.0
• Usually includes less information than full database:
– Simplifies data processing for the user
– Provides a measure of security by omitting sensitive information unavailable to user
![Page 6: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/6.jpg)
A Guide to MySQL 6
Defining and Using Views (continued)
• Created by a defining query: indicates rows and columns to include
• Uses CREATE VIEW command:
CREATE VIEW, followed by name of view, AS, and then query
![Page 7: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/7.jpg)
A Guide to MySQL 7
Defining and Using Views (continued)
![Page 8: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/8.jpg)
A Guide to MySQL 8
Defining and Using Views (continued)
![Page 9: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/9.jpg)
A Guide to MySQL 9
Defining and Using Views (continued)
• Data shown Figure 7-2 does not exist in this form
• Not a temporary table
• To query a view, merge query that created view with query to select specific data
![Page 10: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/10.jpg)
A Guide to MySQL 10
Defining and Using Views (continued)
![Page 11: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/11.jpg)
A Guide to MySQL 11
Defining and Using Views (continued)
![Page 12: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/12.jpg)
A Guide to MySQL 12
• Can assign column names in view that are different than base table
• Include new column names in parentheses, following the name of the view
• Output will display new column names
Defining and Using Views (continued)
![Page 13: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/13.jpg)
A Guide to MySQL 13
Defining and Using Views (continued)
![Page 14: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/14.jpg)
A Guide to MySQL 14
• Defining query of view can be any valid SQL query• View can join two or more tables
Defining and Using Views (continued)
![Page 15: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/15.jpg)
A Guide to MySQL 15
Defining and Using Views (continued)
![Page 16: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/16.jpg)
A Guide to MySQL 16
Defining and Using Views (continued)
![Page 17: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/17.jpg)
A Guide to MySQL 17
Defining and Using Views (continued)
![Page 18: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/18.jpg)
A Guide to MySQL 18
• Benefits of views:
– Provide data independence
– Can often be used even after database structure changes
– Different users can view same data differently
– A view can contain only those columns required by a given user
Defining and Using Views (continued)
![Page 19: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/19.jpg)
A Guide to MySQL 19
Using a View to Update Data
• Benefits of views are for retrieval purposes only
• Updating data through a view is dependent on type of view
![Page 20: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/20.jpg)
A Guide to MySQL 20
Updating Row-and-Column Subset Views
• Can update (usually) if view contains primary key
• Cannot update when primary key not included
![Page 21: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/21.jpg)
A Guide to MySQL 21
No primary key
Updating Row-and-Column Subset Views (continued)
![Page 22: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/22.jpg)
A Guide to MySQL 22
Updating Views Involving Joins
![Page 23: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/23.jpg)
A Guide to MySQL 23
Updating Views Involving Joins (continued)
![Page 24: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/24.jpg)
A Guide to MySQL 24
Updating Views Involving Joins (continued)
• Can update when a view is derived by joining two tables on primary key of each table
• Cannot update when view involves joining by matching the primary key of one table with a column that is not the primary key
• Encounter more severe problems if neither of the join columns is a primary key
![Page 25: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/25.jpg)
A Guide to MySQL 25
Updating Views Involving Statistics
• Most difficult to update
• Cannot add rows to a view that includes calculations
![Page 26: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/26.jpg)
A Guide to MySQL 26
Dropping a View
• Remove a view that is no longer needed with DROP VIEW command
• The DROP VIEW command removes only the view definition; base table and data remain unchanged
![Page 27: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/27.jpg)
A Guide to MySQL 27
Security
• Prevention of unauthorized access to a database:
– Some users may be able to retrieve and update anything in database
– Other users may be able to retrieve data but not change data
– Other users may be able to access only a portion of data
![Page 28: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/28.jpg)
A Guide to MySQL 28
Security (continued)
• GRANT command: main mechanism for providing access to database
• Database administrator can grant different types of privileges to users and revoke them later
• Privileges include rights to select, insert, update, index, and delete table data
![Page 29: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/29.jpg)
A Guide to MySQL 29
Security (continued)
• Database administrator uses REVOKE command to remove privileges from users
• Format is similar to GRANT command
![Page 30: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/30.jpg)
A Guide to MySQL 30
Indexes
• Speeds up the searching of tables
• Similar to an index in a book
![Page 31: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/31.jpg)
A Guide to MySQL 31
Indexes (continued)
![Page 32: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/32.jpg)
A Guide to MySQL 32
Indexes (continued)
• MySQL manages indexes
• User determines columns on which to build indexes
• Disadvantages:
– Index occupies disk space
– DBMS must update index as data is entered
![Page 33: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/33.jpg)
A Guide to MySQL 33
Creating an Index
• Use CREATE INDEX command:– Name the index
– Identify the table
– Identify the column or columns
![Page 34: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/34.jpg)
A Guide to MySQL 34
Creating an Index (continued)
![Page 35: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/35.jpg)
A Guide to MySQL 35
Dropping an Index
• Use DROP INDEX to delete an index:
DROP INDEX followed by name of index to drop
• Permanently deletes index
![Page 36: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/36.jpg)
A Guide to MySQL 36
Creating Unique Indexes
• To ensure uniqueness of non-primary key data, you can create a unique index; use CREATE UNIQUE INDEX command
• A unique index will reject any update that would cause a duplicate value in the specified column
![Page 37: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/37.jpg)
A Guide to MySQL 37
System Catalog• Contains information about tables in database; also
called data dictionary
• Use SHOW TABLES command to list all tables in database
• Use SHOW COLUMNS command to list all columns in a table
• Use SHOW INDEX command to list all indexes in a table
• Use SHOW GRANTS command to list privileges
![Page 38: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/38.jpg)
A Guide to MySQL 38
System Catalog (continued)
![Page 39: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/39.jpg)
A Guide to MySQL 39
System Catalog (continued)
![Page 40: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/40.jpg)
A Guide to MySQL 40
System Catalog (continued)
![Page 41: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/41.jpg)
A Guide to MySQL 41
System Catalog (continued)
![Page 42: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/42.jpg)
A Guide to MySQL 42
Integrity Constraints in SQL
• Rule for the data in the database
• Examples in Premiere Products:
– A sales rep’s number must be unique
– The sales rep number for a customer must match an exiting sales rep number
– Item classes for parts must be AP, HW, or SG
![Page 43: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/43.jpg)
A Guide to MySQL 43
Integrity Constraints in SQL (continued)
• Integrity support: process of specifying integrity constraints for the database
• Clauses to support integrity constraints can be specified within a CREATE TABLE or ALTER TABLE command:
– ADD PRIMARY KEY
– ADD FOREIGN KEY
![Page 44: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/44.jpg)
A Guide to MySQL 44
Integrity Constraints in SQL (continued)
• Primary keys: use ADD PRIMARY KEY clause on ALTER TABLE command to add after creating a table
• Foreign keys: column in one table whose value matches the primary key in another
• Legal values: the CHECK clause ensures only legal values are allowed in a given column
![Page 45: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/45.jpg)
A Guide to MySQL 45
Integrity Constraints in SQL (continued)
![Page 46: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/46.jpg)
A Guide to MySQL 46
Integrity Constraints in SQL (continued)
• Error messages refer to parent and child
• When specifying a foreign key, table containing foreign key is the child
• Table referenced by foreign key is parent
![Page 47: A Guide to MySQL](https://reader035.vdocuments.us/reader035/viewer/2022062517/56813cda550346895da67ee8/html5/thumbnails/47.jpg)
A Guide to MySQL 47
Summary
• Views (purpose, creation, and use)
• Security features (GRANT, REVOKE)
• Indexes (purpose, creation, and use)
• Dropping indexes
• System catalog information
• Integrity constraints