Texas A&M University
Advanced MySQL Topics
View(filtered view)
• a virtual table
o It does not physically exist.
o Rather, it is created by a query joining one or more tables.
• In effect every view is a filter of the table data
o Security
▪ filter can restrict both the columns and the rows of the referenced tables.
▪ this application program’s or individual user’s picture of the database
• so the USER of the database doesn’t see the REAL data
▪ serves as a security mechanism
▪ Users cannot see or access the remaining data in the underlying tables.
▪ You can grant Permissions to users to use the view and revoke Permissions from the base Publishers table.
• They will not be able to query on the Publishers table.
o Simplification
▪ A view also serves as a mechanism to simplify query execution
▪ Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.
▪ Once you have defined a view, you can reference it like any other table in a database.
o Advantages of views
▪ Data independence
▪ Each user has his or her own view
▪ View should contain only fields required by the user
▪ Greatly simplifies user’s perception of database
▪ Security
• The view it is stored in the database!!!
o notice the icon change!!
|View vs. Table Icon in PhpMyAdmin |
|[pic] |
|View Example |
|Objective: want users to see only two columns from the original table |
| |
|Publishers (Original) |
|Publd |
|PubName |
|City |
|State |
|Country |
| |
|0736 |
|New Moon Books |
|Boston |
|MA |
|USA |
| |
|0877 |
|Binnet & Hardly |
|Washington |
|DC |
|USA |
| |
|1389 |
|Algodata Infosystems |
|Berkeley |
|CA |
|USA |
| |
|1622 |
|Five Lakes Publishing |
|Chicago |
|IL |
|USA |
| |
| |
|Query: |
|create view `VW_Publishers` as select `Publd`, `PubName` from `Publishers` |
| |
|VW Publishers (Result) |
|Publd |
|PubName |
| |
|0736 |
|New Moon Books |
| |
|0877 |
|Binnet & Hardly |
| |
|1389 |
|Algodata Infosystems |
| |
|1622 |
|Five Lakes Publishing |
| |
|Create a view of the Rep table, name it “Temp_Rep” in your database |
| |
|What would this SQL command do? |
|create view `VW_Publishers` as select `PubName`, concat(`City`, `State`, `Country`) as `location` from `Publishers` |
|What does concat do?? |
Updating and Dropping a VIEW
• You can update a VIEW without dropping it by using the following syntax:
CREATE OR REPLACE VIEW view_name AS SELECT columns FROM table
WHERE predicates;
DROP VIEW view_name;
Drop the view we just created
Indexes
• USUALLY the primary key (autoincrement)
o unless the primary key is unusually long
• Conceptually similar to book index
o Increase data retrieval efficiency
o Record numbers automatically assigned and used by DBMS
• Index key: field or combination of fields on which index is built
• Advantages
o Makes some data retrieval (and deletion) more efficient
• Disadvantages
o Occupies space on disk
o DBMS must update index whenever corresponding data are updated
• Create an index on a field (or fields) when:
o Field is the primary key of the table
o Field is the foreign key in a relationship
o Field will be frequently used as a sort field
o Need to frequently locate a record based on a value in this field
|Example Table with Index |
|[pic] |
|Table with no index |
|[pic] |
|Index creation |
|Adding an Index to and existing table |Creating a table with an index |
|ALTER TABLE `test` ADD INDEX ( `test1` ) |CREATE TABLE `student`.`test2` ( |
| |`test1` INT NOT NULL , |
| |`test2` INT NOT NULL , |
| |INDEX ( `test1` ) |
| |) ENGINE = MYISAM ; |
Security
• Prevention of unauthorized access to database
• Database administrator determines types of access various users can have
• SQL security mechanisms
o GRANT: provides privileges to users
o REVOKE: removes privileges from users
GRANT SELECT ON Customer TO Jones;
REVOKE SELECT ON Customer FROM Jones;
Integrity Rules
• Two integrity rules must be enforced by a relational DBMS
o Entity integrity
▪ No field that is part of primary key may accept null values
o Referential integrity
▪ Foreign key: field(s) whose value is required to match the value of the primary key for a second table
▪ Table whose primary key the field is to match
• if table A contains a foreign key that matches the primary key of table B, the values of this foreign key must match the value of the primary key for some row in table B or be null
|Referential Integrity – Foreign key Example |
|[pic] |
| |
|So what is the link between those tables? |
| |
|Notice: |
|Each table has its own primary key (which is called ID in both tables). |
|Notice the attribute Cust_ID in the Orders table. This attribute indicates the customer that made each particular order. |
|The values in the Cust_ID column refer to the values in the ID column of the Customer table. |
|The Cust_ID column is called a Foreign Key, since it refers to the key (primary key) of another (foreign) table. |
PRIMARY KEY (CustomerNum)
FOREIGN KEY (RepNum) REFERENCES Rep
Structure Changes
• Examples of changes to database structure
o Adding and removing tables and fields
o Changing characteristics of existing fields
o Creating and dropping indexes
• moving fields is NOT possible!!
• SQL ALTER TABLE command changes table’s structure
|Structure Changes |
|Add a column |
|ALTER TABLE Customer ADD CustType CHAR(1) |
|Changing properties of existing fields |
|ALTER TABLE Customer CHANGE COLUMN CustomerName TO CHAR(40) |
|Deleting a field from a table |
|ALTER TABLE Part DELETE Warehouse |
|Deleting (dropping) a table |
|DROP TABLE SmallCust |
The “AS” feature
• You can rename a selection into any name you wish
|Normal Selection |Selection rename with as |
|SELECT `CustomerName` FROM `customer` |SELECT `CustomerName` AS `pain_in_necks` |
| |FROM `customer` |
|[pic] |[pic] |
The Default feature
• Sets a column to a default value IF no value is given
• Can only be set when
o CREATING the table
o Adding a new field (column)
|Default Example |
|CREATE TABLE t ( ts1 TIMESTAMP DEFAULT 0 ) |
|Alter table `table` add `fieldname` datatype default value |
|Create a table that contains below: |
| |
| |
|fName varchar(30) |
| |
|lName varchar(30) |
| |
|Major varchar(30) |
| |
|GPA float |
| |
| |
| |
|By default sets GPA to 0.00 |
|By default sets Major to “General Studies” |
Administrative Tools
• Stored Procedures
o Special file used to store a query that is run often
o Placed on the server
o Improves overall performance
o Convenience
o To use a stored procedure: CALL followed by the procedure name
• Triggers
o Action that occurs automatically in response to an associated database operation such as an INSERT, UPDATE, or DELETE command
o Stored and compiled on the server
• Not available on the current server we have!!
|Creating procedures |
|CREATE PROCEDURE getUserName(IN userID INT, OUT userName VARCHAR(20)) |
|BEGIN |
|SELECT username INTO userName FROM user_info WHERE UserID=user_ID; |
|END |
| |
|CALL getName(683, @userName) |
| |
|SELECT @userName |
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- lab 2 data definition language ddl eng alaa o shama
- class notes semester it iv sem subject name dbms
- data base management systems lab manual lendi
- sql tutorial learn transact sql language
- database programming with sql
- guide to using sql synonyms and the rename
- renaming columns and constraints
- oracle 9i streaming the fast information sharing
- ceg computer science dept home
- exercises home yeshiva university
Related searches
- texas a m money education center
- texas a m grading scale
- texas a m 2020 2021 academic schedule
- texas a m extension child care training
- texas a m academic calendar
- texas a m 1098 t
- texas a m campuses in texas
- texas a m financial aid portal
- texas a m university campuses
- texas a m online engineering degree
- texas a m online engineering masters
- texas a m college station tuition