{"id":476,"date":"2016-05-08T11:34:50","date_gmt":"2016-05-08T16:34:50","guid":{"rendered":"https:\/\/researchbdev.wpengine.com\/digital-computational-studies\/?p=476"},"modified":"2017-04-20T09:22:19","modified_gmt":"2017-04-20T14:22:19","slug":"sequel-pro-creating-a-database","status":"publish","type":"post","link":"https:\/\/research.bowdoin.edu\/digital-computational-studies\/student-research\/sql\/sequel-pro-creating-a-database\/","title":{"rendered":"Sequel Pro: Creating a Database"},"content":{"rendered":"

This walkthrough\u00a0has been created using this tutorial<\/a> as a resource.<\/p>\n

    \n
  1. Start your machine’s MySQL Server instance through System Preferences. \u00a0See this walkthrough<\/a> for assistance in doing so.<\/li>\n
  2. Download Sequel Pro here<\/a>.<\/li>\n
  3. Open Sequel Pro.\"Screen<\/a><\/li>\n
  4. Create a Standard connection to your MySQL Server, using IP address 127.0.0.1 as the Host and your MySQL root credentials for the Username and Password. \u00a0No extra information is necessary.
    \n    \"Screen<\/a><\/li>\n
  5. Once connected,\u00a0you will automatically be in the “Query” section of Sequel Pro, where you will type and run MySQL commands. \u00a0In this demo, I will create a database of classmates, but feel free to mimic the syntax used in order to create a database of your choosing. \u00a0To create your database, type CREATE DATABASE classmate_database;\u00a0<\/strong>then press “Run Previous” just below in order to run your code and create your database.
    \n    \"Screen<\/a>To check that you’ve successfully created the database, navigate to the “TABLES” section of the Sequel Pro window\u00a0and locate classmate_database<\/strong>.
    \n    \"Screen<\/a><\/li>\n
  6. In order to work with the database your have just created, use the command “USE” and type USE classmate_database;<\/strong> and run the command just as you did before.
    \n    \"Screen<\/a><\/li>\n
  7. Now, you’ll need to create a table in order to add some actual content to your database. \u00a0To learn more about the organization of a database or, more specifically, the relationship between a database and its tables, check out this post. \u00a0To create the table, unsurprisingly, we’ll use the command “CREATE TABLE” to create a table of classmates and proceed to indicate the primary key column and an initial (unique) column for the database. \u00a0Type and run the following code:
    \nCREATE TABLE classmates (<\/strong>
    \n\u00a0 \u00a0 \u00a0 \u00a0 student_id INT NOT NULL,<\/strong>
    \n\u00a0 \u00a0 \u00a0 \u00a0 student_lastName VARCHAR(30) NOT NULL,<\/strong>
    \n\u00a0 \u00a0 \u00a0 \u00a0 PRIMARY KEY (student_id),<\/strong>
    \n\u00a0 \u00a0 \u00a0 \u00a0 UNIQUE (student_lastName)<\/strong>
    \n);
    \n    \"Screen<\/a>
    \n<\/a><\/strong>Let’s take some time to understand the code we’ve just used. \u00a0CREATE DATABASE<\/strong> is self explanatory; however, it’s important to notice\u00a0the way we set student_id as the PRIMARY KEY<\/strong> here and set student_lastName as a UNIQUE<\/strong> key. \u00a0Both primary and unique keys are indices whose members must all be unique. \u00a0Here, that means that no students can have the same ID and that no students can have the same last name (which may not be a realistic assumption). \u00a0The primary key is special in that it is used for identification. \u00a0In student_id, INT<\/strong> specifies that the column’s values will be integers and NOT NULL<\/strong> indicates that the value cannot be null. \u00a0If a null value is attempted for this column, an error will occur. \u00a0In student_lastName, VARCHAR(30)<\/strong> specifies that the column’s value will be composed characters with a maximum length of 30. \u00a0Here, VARCHAR is used instead of MySQL’s CHAR because VARCHAR allows user to indicate a string of variable length, while CHAR’s declaration indicates a string of static length. \u00a0We use VARCHAR here so that last names of different lengths can be inserted into the column.<\/li>\n
  8. Now is a good time to check that your table has been created. \u00a0Click on the “Content” button on the top toolbar on the Sequel Pro window. \u00a0If you find student_id<\/strong> and student_lastName\u00a0<\/strong>as columns, you have successfully created the beginnings of your database!
    \n    \"Screen<\/a>You can resume coding by returning to the “Query” button on the top toolbar.<\/li>\n
  9. Now, let’s add some actual values to our table using the “INSERT INTO” and “VALUES” command by typing and running the following code:
    \nINSERT INTO classmates<\/strong>
    \n\u00a0 \u00a0 \u00a0 \u00a0 (student_id, student_lastName)<\/strong>
    \nVALUES<\/strong>
    \n\u00a0 \u00a0 \u00a0 \u00a0 (1, “Lastname1”),<\/strong>
    \n\u00a0 \u00a0 \u00a0 \u00a0 (2, “Lastname2”),<\/strong>
    \n\u00a0 \u00a0 \u00a0 \u00a0 (3, “Lastname3”);
    \n<\/strong>Be sure to contain the last name values in quotes and to assign unique id number values to each student.
    \n\"ScreenTo check to make sure that your data was entered correctly, again navigate to the “Content” button. \u00a0If you click to drop the student_id<\/strong> or student_lastName<\/strong> columns down, you should find your data!
    \n    \"Screen<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"

    This walkthrough\u00a0has been created using this tutorial as a resource. Start your machine’s MySQL Server instance through System Preferences. \u00a0See this walkthrough for assistance in doing so. Download Sequel Pro here. Open Sequel Pro. Create a Standard connection to your MySQL Server, using IP address 127.0.0.1 as the Host and your MySQL root credentials for […]<\/p>\n","protected":false},"author":106,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_genesis_hide_title":false,"_genesis_hide_breadcrumbs":false,"_genesis_hide_singular_image":false,"_genesis_hide_footer_widgets":false,"_genesis_custom_body_class":"","_genesis_custom_post_class":"","_genesis_layout":"","footnotes":""},"categories":[17],"tags":[],"class_list":{"0":"post-476","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-sql","7":"entry"},"_links":{"self":[{"href":"https:\/\/research.bowdoin.edu\/digital-computational-studies\/wp-json\/wp\/v2\/posts\/476","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/research.bowdoin.edu\/digital-computational-studies\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/research.bowdoin.edu\/digital-computational-studies\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/research.bowdoin.edu\/digital-computational-studies\/wp-json\/wp\/v2\/users\/106"}],"replies":[{"embeddable":true,"href":"https:\/\/research.bowdoin.edu\/digital-computational-studies\/wp-json\/wp\/v2\/comments?post=476"}],"version-history":[{"count":0,"href":"https:\/\/research.bowdoin.edu\/digital-computational-studies\/wp-json\/wp\/v2\/posts\/476\/revisions"}],"wp:attachment":[{"href":"https:\/\/research.bowdoin.edu\/digital-computational-studies\/wp-json\/wp\/v2\/media?parent=476"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/research.bowdoin.edu\/digital-computational-studies\/wp-json\/wp\/v2\/categories?post=476"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/research.bowdoin.edu\/digital-computational-studies\/wp-json\/wp\/v2\/tags?post=476"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}