This article shows you how to create a database with MySQL. In order to create a new database, use the "MySQL" command console and enter all the necessary commands one at a time. In this case the database engine, ie the DBMS, must be running.
Steps
Part 1 of 3: Accessing the MySQL Command Line
Step 1. Make sure the MySQL server is up and running
If the DBMS is not running or is not reachable, you will not be able to execute the commands necessary to create the database.
You can check the status of the server by starting the MySQL Workbench program, selecting the server to be scanned and observing the "Server Status" indicator visible in the "Administration - Server Status" tab
Step 2. Copy the full path to the MySQL installation folder
This data varies according to the hardware platform in use (a Windows system or a Mac):
- Windows - Copy the following path C: / Program Files / MySQL / MySQL Workbench 8.0 CE / making sure to replace the last folder name with the name of the MySQL product in use.
- Mac - copy the following path /usr/local/mysql-8.0.13-osx10.13-x86_64/ making sure to replace the last folder name with the one relative to the folder where you installed MySQL.
Step 3. Log in to the command console on your computer
If you are using a Windows system, you will have to open the "Command Prompt", while if you are using a Mac you will have to open a "Terminal" window.
Step 4. Navigate to the MySQL installation folder
Type the command cd followed by a blank space, then paste the path to the MySQL installation folder and press the Enter key. For example, if you are using a Windows system, in most cases you will need to run the following command:
cd C: / Program Files / MySQL / MySQL Workbench 8.0 CE
Step 5. Run the command to login to the MySQL server
For example, to log in to the server using the "me" user account, use the following command to press the Enter key:
mysql -u me -p
Step 6. Enter the password for the indicated account
Type the login password for the MySQL user account you used to connect to the server, then press the Enter key. This will connect you to the server and have the MySQL command console available.
- After logging in, you should see the "MySQL>" prompt appear within the command line. From this point on, whatever command is entered will be executed by the MySQL server and no longer from the command console of the system in use (Windows or Mac).
- Understand the basic syntax in order to create a correct MySQL command. All MySQL commands must always end with the ";" character. However, you can also type the command, press the Enter key, type the semicolon and press Enter again.
Part 2 of 3: Create a Database
Step 1. Create the database file
Run the "create database" command by typing the following text create database, add the name you want to assign to the database and end the command with a semicolon, then press the Enter key. For example, to create the "Pet Records" database you need to run the following command:
create Pet_Records database;
- Remember that the database name cannot contain any whitespace. If you need to separate the words, you can use the special character "_" (for example the name "Customer Master" will become "Customer_ Master").
- Each MySQL command must end with the symbol ";". If you forgot to enter it the first time, you can type it after the symbol …, which appeared after pressing the Enter key, and press it a second time.
Step 2. View the list of databases on MySQL
You can consult the list of all databases currently existing on the MySQl server to which you are connected by typing the following command and pressing the Enter key:
show databases;
Step 3. Select the database you just created
You can select the database to work on using the use [name] command, where the "[name]" parameter represents the name of the database. For example, if you want to use the "Pet Records" database created in the previous steps, you will need to enter the following command and press the Enter key:
use Pet_Records;
Step 4. Wait for the confirmation message to appear
When you see the text "Database changed" appear under the last command executed, you can go ahead and start creating the database structure.
Part 3 of 3: Create a Table
Step 1. Learn to use the different table-related commands
Before moving on to the actual creation of a table in your database, you need to understand some fundamental aspects concerning the functioning of this basic element of a data structure:
- Name - represents the name of the table and must be the first parameter inserted after the "create table" command. The rules that must follow the names of the tables are the same as those used for the database name (for example there cannot be empty spaces).
- Column names - are the single fields that characterize the structure of the table. All column names should be placed in parentheses (see next step for an example).
- Field size - this aspect must be taken into consideration when using certain types of data, for example "VARCHAR" (which refers to a string of characters with variable length, i.e. it is possible to insert a number of characters between one and the maximum string). The "CHAR" data type refers to a fixed-length character string (in this case if a field of type CHAR (1) is declared inside there will always be only one character while in the case of a CHAR (3) inside there will be three characters and so on).
-
Date - if you need to use dates within a table, you will need to use the "DATE" command to indicate that the content of a particular column should be formatted as a date. The only format accepted by MySQL for inserting dates into tables and querying the database is
YYYY-MM-DD
Step 2. Create the table structure
Before you can start storing data inside a table, you need to create it by declaring its internal structure. Use the following command as a template and press the Enter key:
create table name (column1 varchar (20), column2 varchar (30), column3 char (1), column4 date);
- For example, to create a table called "Pets" composed of two columns of type "VARCHAR", one of type "CHAR" and one of type "DATE", you will need to use the following command:
create table Pets (Name varchar (20), Race varchar (30), Gender char (1), Ddn date);
Step 3. Insert a data record into the newly created table
In this case you need to use the "insert" command to insert one record at a time into the database:
insert into [table name] values ('column1 value', 'column2 value', 'column3 value', 'column4 value');
-
For example in the case of the "Pets" table created in the previous step, to insert a data record inside it, you will have to use the following command:
insert into Pets values ('Fido', 'Husky', 'M', '2017-04-12');
- If the contents of a table field are not present or must remain empty, you can use the special value NULL inside the "insert" command.
Step 4. Enter the rest of the data (if applicable)
In the case of a very small database you can choose to insert the data into the tables one record at a time, this means that you will have to do it using an "insert" command for each record of data to be stored in the table. If you have chosen to trade this way, skip the next step.
Step 5. Load the data using a text file
If the database you are creating consists of a large set of data, you can perform record insertion using a text file specially formatted according to the structure of the target table. In this case, loading will be much more efficient and faster than manual loading which involves inserting one record at a time into the table. Use the following command:
load data local infile '/path/file_name.txt' into table [table_name] lines terminated by '\ r / n';
-
For example, in the case of the "Pets" table, you will need to use a command similar to the following:
load data local infile 'C: / Users / [username] /Desktop/pets.txt' into table Pets lines terminated by '\ r / n';
- If you are using a Mac, you will need to use the '\ r' character instead of '\ r / n' as the terminator of individual lines of text within the file.
Step 6. View the tables present in the database
Use the show databases command; to view all the databases on the server, then select the one you want to query using the select * from [DB_name]; command, where the "[DB_name]" parameter is the name of the chosen database. For example, in the case of the "Pet Records" database created in the previous steps, you will need to use the following code:
show databases; select * from Pet_Records;
Advice
-
The most commonly used data types within a database include the following:
- CHAR([length]) - this is a fixed-length character string;
- VARCHAR([length]) - is a variable-length character string whose maximum extension is indicated by the [length] parameter;
- TEXT - contains a variable-length text string whose maximum size can be 64KB;
- INT([length]) - is a 32-bit integer with a maximum number of digits indicated by the [length] parameter (remember that the '-' sign of negative numbers is considered as a digit and therefore affects the length of the number);
- DECIMAL([length], [decimal]) - indicates a decimal number with a maximum number of digits indicated by the [length] parameter. The [decimal] parameter indicates the maximum number of decimal digits allowed;
- AT YOUR PLACE - represents a date with the following format (year, month, day);
- TIME - represents a time value with the following format (hours, minutes, seconds);
- ENUM("value1", "value2",….) - it can contain one of the values indicated and allowed in the declaration phase;
-
Here are some optional parameters that may be useful:
- NOT NULL - the indicated field cannot assume a "NULL" value, therefore it cannot be left empty;
- DEFAULT [default_value] - if no value is provided for the field in question, the one indicated by the [default_value] parameter is used;
- UNSIGNED - refers to the numeric fields and indicates that the field in question admits only unsigned numbers, consequently negative numbers cannot be entered;
- AUTO_INCREMENT - the value of the field in question is automatically increased by one unit each time a new row is added to the table.
Warnings
- Make sure you enter the database and table creation commands correctly by carefully checking their syntax before executing them.
- If the server on which MySQL is installed is not running when you log in to the database command console, you will not be able to proceed with creating the database.
