Creating an Index Create an index for tblemployees based on (fldName1, CREATE INDEX ON DESC); idxName tblName fldName2 lastname, firstname. Indexes can be created on one or more fields Fields must be designated NOT NULL If appropriate, the field can be sorted in descending order In MySQL Workbench, indexes are shown in the Navigator under the appropriate table Indexes can also be created when you define the table CREATE TABLE tblName field lists INDEX(field1, field2); Using an Index Select lastname, firstname from employees. Note: Indexes are automatically used whenever your query sorted. includes a WHERE clause that includes the index fields in the same order they are specified in the index or Select just last. Just first when your query includes an ORDER BY clause that (not sorted) includes the index fields in the same order they are specified in the index. Displaying Current Indexes Try it SHOW INDEXES FROM tblName; Deleting an Index DROP INDEX ON idxName tblName; Database Security Because we are using a portable version of MySQL SQL databases provide two levels of security. with only one user, these Views provide some level of security by restricting commands cannot be what data a user can see. demonstrated. The and commands provide GRANT REVOKE varying levels of access to tables The types of privileges that are available in SQL vary depending on the implementation. MySQL allows you to control the following privileges: Select Insert Update Delete Create Drop Grant References Index Alter
Creating an Index CREATE INDEX idxName ON tblName (fldName1, fldName2 DESC); Indexes can be created on one or more fields Fields must be designated NOT NULL If appropriate, the field can be sorted in descending order In MySQL Workbench, indexes are shown in the Navigator under the appropriate table Create an index for tblemployees based on lastname, firstname. Indexes can also be created when you define the table CREATE TABLE tblName field lists INDEX(field1, field2); Using an Index Indexes are automatically used whenever your query includes a WHERE clause that includes the index fields in the same order they are specified in the index or when your query includes an ORDER BY clause that includes the index fields in the same order they are specified in the index. Select lastname, firstname from employees. Note: sorted. Select just last. Just first (not sorted) Displaying Current Indexes SHOW INDEXES FROM tblName; Try it Deleting an Index DROP INDEX idxName ON tblName; Database Security SQL databases provide two levels of security. Views provide some level of security by restricting what data a user can see. The GRANT and REVOKE commands provide varying levels of access to tables The types of privileges that are available in SQL vary depending on the implementation. MySQL allows you to control the following privileges: Select Insert Update Delete Create Drop Grant References Index Alter Because we are using a portable version of MySQL with only one user, these commands cannot be demonstrated.
6%20-%20Views,%20Indexes%20&%20Security.docx