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.

Google Online Preview   Download