Structured Query Language (SQL) Fundamentals
互联网
1101
- Abstract
- Table of Contents
- Materials
- Figures
- Literature Cited
- Supplementary Material
Abstract
Relational databases provide the most common platform for storing data. The Structured Query Language (SQL) is a powerful tool for interacting with relational database systems. SQL enables the user to concoct complex and powerful queries in a straightforward manner, allowing sophisticated data analysis using simple syntax and structure. This unit demonstrates how to use the MySQL package to build and interact with a relational database.
GO TO THE FULL PROTOCOL:
PDF or HTML at Wiley Online LibraryTable of Contents
- Basic Protocol 1: Creating a Database
- Support Protocol 1: Changing a Schema
- Support Protocol 2: Adding Users and Permissions
- Basic Protocol 2: Adding Data to a Table
- Alternate Protocol 1: A Batch Method to Load Data into a Table
- Basic Protocol 3: Removing Data from a Table
- Basic Protocol 4: Changing Data in a Table
- Basic Protocol 5: Retrieving Data
- Alternate Protocol 2: Batch Processing Command Scripts
- Commentary
- Figures
- Tables
GO TO THE FULL PROTOCOL:
PDF or HTML at Wiley Online LibraryMaterials
Basic Protocol 1: Creating a Database
Necessary Resources
Support Protocol 1: Changing a Schema
Necessary Resources
Support Protocol 2: Adding Users and Permissions
Necessary Resources
Basic Protocol 2: Adding Data to a Table
Necessary Resources
Alternate Protocol 1: A Batch Method to Load Data into a Table
Necessary Resources
Basic Protocol 3: Removing Data from a Table
Necessary Resources
Basic Protocol 4: Changing Data in a Table
Necessary Resources
Basic Protocol 5: Retrieving Data
Necessary Resources
Alternate Protocol 2: Batch Processing Command Scripts
Necessary Resources
|
GO TO THE FULL PROTOCOL:
PDF or HTML at Wiley Online LibraryFigures
-
Figure 9.2.1 Example schema for protocol examples. Each box represents a table to be used in the primers database. The table name is at top, and the fields are shown within. Arrows from fields to other tables shows the relational schema. View Image -
Figure 9.2.2 Output obtained upon submitting the command in , step . The annotation in parentheses following the type indicates the valid range or size of the variable. View Image -
Figure 9.2.3 Checking permissions for a specific user. The first line shows that the users are allowed to connect to the database server, and are required to login. The following line(s) show their privileges on specific databases. View Image -
Figure 9.2.4 The grants table reflects the revocation of the DELETE privilege. View Image -
Figure 9.2.5 Output obtained upon submitting the command in , step . View Image -
Figure 9.2.6 Output obtained upon submitting the command in , step . View Image -
Figure 9.2.7 Output obtained upon submitting the command with the WHERE clause (see , step ). View Image -
Figure 9.2.8 Output obtained upon submitting the command in , step . View Image -
Figure 9.2.9 Output obtained upon submitting the command in , step . View Image -
Figure 9.2.10 Output obtained upon submitting the command in , step . View Image -
Figure 9.2.11 Output obtained upon submitting the command in , step . View Image -
Figure 9.2.12 Output obtained upon submitting the command in , step . View Image -
Figure 9.2.13 Output obtained upon submitting the command in , step . View Image -
Figure 9.2.14 Output obtained upon submitting the command in , step . View Image -
Figure 9.2.15 Query and output obtained from , step . View Image -
Figure 9.2.16 SOURCE command output from running script in , step . View Image -
Figure 9.2.17 Input redirect output from , step . View Image -
Figure 9.2.18 SQL commands echoed output using ‐vvv switch in , step . View Image
Videos
Literature Cited
| Key References | |
| DuBois, P. 1999. MySQL. New Riders. Indianapolis, Ind. | |
| A comprehensive guide, with many examples and tutorials. An excellent reference for beginners. | |
| Gulutzan, P. and Pelzer, T. 1999. SQL‐99 Complete, Really. CMP Books. Gilroy, Calif. | |
| A complete description of the SQL99 standards. | |
| Kline, K. and Kline, D. 20001. SQL in a Nutshell. O'Reilly and Associates. Sebastopol, Calif. | |
| A compact yet comprehensive guide to SQL statements. Includes several different SQL dialects like MySQL and Oracle. | |
| Internet Resources | |
| http://www.mysql.com | |
| The main MySQL site. | |
| http://www.useractive.com | |
| A hands‐on tutorial. |
GO TO THE FULL PROTOCOL:
PDF or HTML at Wiley Online LibrarySupplementary Material
Note: To access the files listed below, download the associated
Zip file at the bottom of the page (right click/save as).
1. First data file containing the larger search set: cpbbuffer.txt
2. Second data file containing the larger search set: cpboligo.txt
3. Third data file containing the larger search set: cpbprotocol.txt
4. Fourth data file containing the larger search set: cpbsequence.txt
5. Tab-delimited trial data file: oligo.txt
6. Data file containing a set of four SQL commands: script.txt
CPBI9.2.zip
