How to Create a Database in MySQL

PDF download Download Article
PDF download Download Article

This wikiHow teaches you how to create a database in MySQL. In order to create a database, you'll have to open the "mysql" command line interface and enter your database commands while the server is running.

Part 1
Part 1 of 3:

Opening the MySQL Command Line

PDF download Download Article
  1. If your MySQL server isn't currently online, you can't create a database.
    • You can check the server's status by opening MySQL Workbench, selecting your server, and looking at the "Server Status" indicator on the "Administration - Server Status" tab.
  2. This path will vary depending on whether you're using a Windows computer or a Mac:
    • Windows — Copy C:/Program Files/MySQL/MySQL Workbench 8.0 CE/ making sure to replace the last folder's name with the most current MySQL name.
    • Mac — Copy /usr/local/mysql-8.0.13-osx10.13-x86_64/ making sure to replace the last folder's name with the most current MySQL folder name.
    Advertisement
  3. You'll use Command Prompt on a Windows computer, while Mac users will open Terminal.
  4. Type cd and a space, paste in the path to the installation folder, and press ↵ Enter . For example, you'd do the following on most Windows computers: 
     cd 
C:\Program Files\MySQL\MySQL Workbench 8.0 CE
  5. For example, to open the login command for a user named "me", you'd type in the following and press ↵ Enter : 
     mysql 
 - 
 u 
 me 
 - 
 p
  6. Type in the password for your MySQL user account, then press ↵ Enter . This will log you in and connect your command line application to the MySQL prompt.
    • You should see the "MySQL>" tag appear your command line application. From this point on, any commands you enter will be processed through the MySQL command line app.
    • Understand how to enter MySQL commands. MySQL commands must be entered with a semicolon (;) immediately after the last part of the command, though you can also enter the command, type a semicolon, and press ↵ Enter again.
    7. Advertisement
Part 2
Part 2 of 3:

Creating a Database

PDF download Download Article
  1. You'll do this by typing in the "create database" command create database , adding your database's name and a semicolon, and pressing ↵ Enter . For a database named "Pet Records", for example, you'd enter the following: 
     create 
 database 
 Pet_Records 
 ;
    • Your database's name cannot have any spaces in it; if you want to add a space to the name, you'll have to use an underscore (for example, "Friends of Mine" would become "Friends_of_Mine").
    • Every MySQL command must end in a semicolon. If you miss the semicolon the first time, you can type it in next to the ... which appears and then press ↵ Enter again.
  2. You can bring up a list of current databases by typing in the following and then pressing ↵ Enter : 
     show 
 databases 
 ;
  3. You can select your database from the list by typing use name where "name" is the database's name. For example, for your "Pet Records" database, you would type the following and press ↵ Enter : 
     use 
 Pet_Records 
 ;
  4. Once you see the phrase "Database changed" appear below your last-typed command, you're free to proceed with creating the database's content.
    5. Advertisement
Part 3
Part 3 of 3:

Creating a Table

PDF download Download Article
  1. There are a few main aspects of your table that you'll want to know before creating one:
    • Title — Your title will go directly after the "create table" command, and must follow the same rules as your database's name (e.g., no spaces).
    • Column Heading — You can determine column headings by typing different names into a set of parentheses (see the next step's example).
    • Cell Length — When determining cell length, you'll use either "VARCHAR" (variable characters, meaning that you can type in between one and the VARCHAR's limit number of characters) or "CHAR" (requires no more and no less than the specified number of characters; for example, CHAR(1) requires one character, CHAR(3) requires three characters, and so on).
    • Date — If you want to add a date to your chart, you'll use the "DATE" command to indicate that the column's contents will be formatted as a date. The date should be entered in YYYY-MM-DD format.
  2. Before you can input data for your chart, you'll need to create the chart's structure by typing in the following and then pressing ↵ Enter : 
     create 
 table 
 name 
 ( 
 column1 
 varchar 
 ( 
 20 
 ), 
 column2 
 varchar 
 ( 
 30 
 ), 
 column3 
 char 
 ( 
 1 
 ), 
 column4 
 date 
 );
    • For example, to create a table called "Pets" with two VARCHAR columns, a CHAR column, and a date column, you might write the following:
      •  create 
 table 
 Pets 
 ( 
 Name 
 varchar 
 ( 
 20 
 ), 
 Breed 
 varchar 
 ( 
 30 
 ), 
 Sex 
 char 
 ( 
 1 
 ), 
 DOB 
 date 
 );
  3. Using the "insert" command, you can enter your database's information line-by-line: 
     insert 
 into 
 name 
 values 
 ( 
 'column1 value' 
 , 
 'column2 value' 
 , 
 'column3 value' 
 , 
 'column4 value' 
 );
    • For the "Pets" table example used previously, your line might look like this: 
       insert 
 into 
 Pets 
 values 
 ( 
 'Fido' 
 , 
 'Husky' 
 , 
 'M' 
 , 
 '2017-04-12' 
 );
    • You can enter the word NULL for a column's contents if the column is blank.
  4. If your database is relatively small, you can insert the rest of the data line-by-line by using the "insert" code. If you elect to do this, skip the next step.
  5. If you have a database which requires more lines of information than are practical to insert by hand, you can reference a text file that contains the data by using the following code: [1] 
     load 
 data 
 local 
 infile 
 '/path/name.txt' 
 into 
 table 
 name 
 lines 
 terminated 
 by 
 '\r\n' 
 ;
    • For the "Pets" example, you would write something like the following: 
       load 
 data 
 local 
 infile 
 'C:/Users/name/Desktop/pets.txt' 
 into 
 table 
 Pets 
 lines 
 terminated 
 by 
 '\r\n' 
 ;
    • On a Mac computer, you'll need to use the "lines terminated by" command with '\r' instead of '\r\n' .
  6. Enter the show databases; command, then select your database by typing in select * from name; where "name" is the database's name. For example, if using the "Pet Records" database, you'd enter the following: 
     show 
 databases 
 ; 
 select 
 * 
 from 
 Pet_Records 
 ;
    7. Advertisement

Community Q&A

Search
Add New Question
  • Question
    What are the basics of MySQL?
    Community Answer
    (1) Getting started with MySQL; (2) querying data; (3) filtering data; (4) sorting data; (5) joining tables; (6) grouping data; (7) MySQL sub query; (8) using set operators; (9) modifying data in MySQL; (10) MySQL transaction; (11) managing MySQL databases and tables; (12) MySQL indexes; (13) MySQL data types; 14. MySQL constraints; (15) MySQL globalization; (16) MySQL import and export.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 5 Helpful 19
  • Question
    How do I create a new database and place that database in a specific directory location?
    Community Answer
    in 'phpmyadmin,' create a database with its table name and put it in a php directory.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 9 Helpful 15
  • Question
    How do I use Oracle in a database?
    Community Answer
    Oracle is a type of database, not something you use in a database. The cost of an Oracle database instance is generally $100,000 per core (that's a processor). To get started using Oracle, go to Oracle.com.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 4 Helpful 7
See more answers
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      • Some commonly used data types include the following:
        • CHAR ( length ) - fixed length character string
        • VARCHAR ( length ) - variable length character string with max length length
        • TEXT - variable length character string with max length of 64KB of text
        • INT ( length ) - 32-bit integer with max length digits (the '-' is counted as a 'digit' for a negative number)
        • DECIMAL ( length , dec ) - Decimal number up to total length display characters; the dec field indicates the maximum number of decimal places allowed
        • DATE - Date value (year, month, date)
        • TIME - Time value (hours, minutes, seconds)
        • ENUM (" value1 "," value2 ", ....) - List of enumerated values
        Thanks
        Helpful 0 Not Helpful 0
      • Some optional parameters include the following:
        • NOT NULL - A value must be provided. The field cannot be left blank.
        • DEFAULT default-value - If no value is given, the default-value is assigned to the field.
        • UNSIGNED - For numeric fields, ensures that the number is never negative.
        • AUTO_INCREMENT - The value will be incremented automatically each time a row is added to the table.
        Thanks
        Helpful 0 Not Helpful 0
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      Warnings

      • If your MySQL server isn't running when you attempt to log into the "mysql" command line, you won't be able to proceed.
        Thanks
        Helpful 6 Not Helpful 0
      • As with any coding, make sure that your commands are spelled and spaced exactly right before you attempt to enter them.
        Thanks
        Helpful 3 Not Helpful 2
      Advertisement

      You Might Also Like

      How to
      Send SQL Queries to MySQL from the Command Line
      How to
      Add the Google Play Store to an Amazon Fire Tablet
      How to
      Play Minesweeper
      How to
      Open EPUB Files
      Using Rufus to Create Bootable USB Drives: A Step-By-Step Guide
      A Step-by-Step Guide to Compare Two Excel Files
      How to
      Save an Animation in Blender
      How to
      Make an Exe File
      2 Simple Ways to Create an RSS Feed
      How to
      Add a Password to a RAR File
      How to
      Turn on Microsoft Defender in Windows 10 and 11
      How to
      Change the Hostname on Linux Without Rebooting
      How to
      Edit DLL Files
      How to
      Crack Software by Modifying DLL Files
      Advertisement

      Expert Interview

      Thanks for reading our article! If you’d like to learn more about programming, check out our in-depth interview with Tyrone Showers .

      References

      1. ↑ https://dev.mysql.com/doc/refman/8.0/en/loading-tables.html

      About This Article

      In other languages
      Thanks to all authors for creating a page that has been read 1,215,648 times.

      Reader Success Stories

      • Wallace Howery

        May 25, 2017

        "Helped me get a MySQL database created and running as part of a hibernate test project. Note that it is important ..." more
      More reader stories Hide reader stories
      Share your story

      Is this article up to date?

      Advertisement