• 我要登录|
  • 免费注册
    |
  • 我的丁香通
    • 企业机构:
    • 成为企业机构
    • 个人用户:
    • 个人中心
  • 移动端
    移动端
丁香通 logo丁香实验_LOGO
搜实验

    大家都在搜

      大家都在搜

        0 人通过求购买到了急需的产品
        免费发布求购
        发布求购
        点赞
        收藏
        wx-share
        分享

        Structured Query Language (SQL) Fundamentals

        互联网

        1094
        • 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 Library

        Table 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 Library

        Materials

        Basic Protocol 1: Creating a Database

          Necessary Resources
        • Hardware
        • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system
        • Software
        • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions (see protocol 3 ). MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com.

        Support Protocol 1: Changing a Schema

          Necessary Resources
        • Hardware
        • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system
        • Software
          • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions. MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com.
        • Files
          • The primers database created in protocol 1 . Schema shown in Figure

        Support Protocol 2: Adding Users and Permissions

          Necessary Resources
        • Hardware
        • Computer capable of running MySQL
        • Software
        • Newly installed copy of MySQL, version 3.22.11 or higher
        • Files
        • The primers database created in protocol 1

        Basic Protocol 2: Adding Data to a Table

          Necessary Resources
        • Hardware
        • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system.
        • Software
        • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions. MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com.
        • Files
        • The primers database created in protocol 1 , as altered in protocol 2

        Alternate Protocol 1: A Batch Method to Load Data into a Table

          Necessary Resources
        • Hardware
        • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system
        • Software
        • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions. MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com.
        • Files
        • The primers database created in protocol 1 , as altered in protocol 2
        • Tab‐delimited trial data file, oligo.txt . This file is available at the Current Protocols Website: http://www3.interscience.wiley.com/c_p/cpbi_sampledatafiles.htm.

        Basic Protocol 3: Removing Data from a Table

          Necessary Resources
        • Hardware
        • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system
        • Software
        • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions. MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com.
        • Files
        • Primer database created in protocol 1 , as altered in protocol 2 , loaded with the data from oligo.txt as shown in protocol 5

        Basic Protocol 4: Changing Data in a Table

          Necessary Resources
        • Hardware
        • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system.
        • Software
        • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions. MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com
        • Files
        • The primers database created in protocol 1 , as altered in protocol 2 .

        Basic Protocol 5: Retrieving Data

          Necessary Resources
        • Hardware
        • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system
        • Software
        • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions. MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com.
        • Files
        • The primers database created in protocol 1 , as altered in protocol 2
        • Four data files containing the larger search set: cpboligo.txt , cpbprotocol.txt , cpbbuffer.txt , and cpbsequence.txt . These files are available from the Current Protocols Web site: http://www3.interscience.wiley.com/c_p/cpbi_sampledatafiles.htm.

        Alternate Protocol 2: Batch Processing Command Scripts

          Necessary Resources
        • Hardware
        • Computer capable of running MySQL
        • Software
        • Working installation of MySQL version 3.22.11 or higher.
        • Files
        • The primers database created in protocol 1 , as altered in protocol 2 , and loaded with the larger search data set as described in protocol 8 , step
        • Script.txt file, which contains a set of four SQL commands. This file is available from the Current Protocols Web site: http://www3.interscience.wiley.com/c_p/cpbi_sampledatafiles.htm.
        GO TO THE FULL PROTOCOL:
        PDF or HTML at Wiley Online Library

        Figures

        •   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 Library

        Supplementary 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
        
        

         

        
        
        

         

        
        
        

         

        
        
        

         

         
        ad image
        提问
        扫一扫
        丁香实验小程序二维码
        实验小助手
        丁香实验公众号二维码
        扫码领资料
        反馈
        TOP
        打开小程序