Creating Databases and Tables in MySQL

Using the command line

Example:

CREATE DATABASE employees;
USE employees;
CREATE TABLE employee_details (firstname VARCHAR(20), lastname VARCHAR(20),
                               startdate DATE, salary DECIMAL);

Loading Data in MySQL

Command line backup and restore

• Backup using mysqldump utility

mysqldump -u root employees > employeesbackup.sql

employees: name of the database

employeesbackup.sql: name of the file in which to create the backup

The greater than sign ( > ) signifies output to the .sql file, or backup

• Restore using mysql

mysql - u root restored_employees < employeesbackup.sql

This runs all of the SQL statements in the backup file to recreate the objects and restore the data in the destination database.

The less than sign ( < ) signifies input to the database, or restore

• Restore using source command

mysql> source employeesbackup.sql

If you’re already at the mysql command prompt, you can restore a dump file by using the source command with the name of the dump file (employeesbackup.sql).

This method can also be used execute SQL scripts from file.

Importing data files

• Importing using load data infile statement

load data infile 'employeesdata.csv' into table employees_details

You can use the mysql load data infile statement to import the contents of a CSV file('employeesdata.csv') into an existing MySQL table(employees_details)

• Importing using mysqlimport utility

mysqlimport employees employees_details.csv

you can use the mysqlimport utility passing the name of the database(employees) that the table resides in and the name of the CSV file(employees_details.csv).

The table name is inferred from the name of the CSV file, so must ensure that the file name matches the table name exactly.

Hands-on Lab : Getting started with MySQL command line

Hands-on Lab : Create Tables and Load Data in MySQL using phpMyAdmin

Hands-on Lab: Keys and Constraints in MySQL

Summary & Highlights

At this point in the course, you know:

MySQL is a free, open-source RDMS that you can download and install on your own systems or access on the Cloud. You can either self-manage a Cloud instance of MySQL or use a managed services provider, including IBM Cloud, Amazon RDS for MySQL, Azure Database for MySQL, or Google Cloud SQL for MySQL.

MySQL includes several options for creating databases and tables, loading and querying data, and importing and exporting data relational databases:

• mysql and mysqladmin command line interfaces. You use these CLIs to run SQL statements.
• MySQL Workbench. A desktop application for designing, developing, and administering MySQL databases.
• phpMyAdmin. An easy to use, third-party web interface for working with MySQL databases.
• API calls.

Using phpMyAdmin, you can:

• Add and modify columns after you create a table.
• Use backup and restore functionality to populate databases.
• Use import and export functionality to populate tables and save their data to files.
• Create primary keys by defining a primary index on one or more columns.
• Use autoincrement to automatically generate sequential numeric data in a column.

When creating foreign keys, you can define ON DELETE and ON UPDATE actions.

MySQL columns are NOT NULL by default.

You can configure a column to only accept unique values.

Practice Quiz: MySQL

TOTAL POINTS 5

Question 1

Which edition of MySQL is free to use under a General Public License?

• Enterprise
• Community
• Standard
• Cluster

Correct. Community Edition is free to download and install.

Question 2

True or False: You can only use the command line interface or a graphical user interface, like phpMyAdmin, to create MySQL databases and tables.

• True
• False

Correct. You can also create MySQL databases and tables using API calls.

Question 3

Which MySQL statements can you use to populate a single database table with data from a CSV file? Select two answers.

• INSERT
• load data infile
• mysqlimport
• source

Correct. This statement imports the contents of a CSV file into an existing MySQL table.

Question 4

True or False: A primary key is a type of index.

• True
• False

Correct. Creating a primary key automatically creates an index on that field.

Question 5

You created a new table in your database and later decide that you want to ensure that one of the fields always contains a value. What should you do?

• Turn on the Not Null option for the field.
• There is no need to change anything in your table definition to make this happen.
• Turn off the Distinct values option for the field.
• Select the Unique option for the field.

Correct. Turning on the Not Null option ensures the field contains a value always.

Graded Quiz: MySQL

LATEST SUBMISSION GRADE 100%

Question 1

In MySQL Workbench, which page in the UI do you use to inspect the objects in your database?

• Object Browser
• Administration
• Schemas
• Visual Data Editor

Correct. You can use the Schemas page to access the objects in your database and work with the data.

Question 2

When creating a MySQL database using phpMyAdmin, at which point in the process do you select the encoding method for the data?

• When you name the new database.
• When you define the columns in the table.
• When you are shown a summary of the structure of the new table.
• When you add tables to the database.

Correct. When you create a new database, you can name it and specify the encoding for the data on the Databases tab.

Question 3

Which methods of populating MySQL database tables is best for small amounts of data? Select two answers.

• Manually enter the rows using the phpMyAdmin interface.
• Insert the rows from a data file.
• Restore the rows from a backup.
• Run SQL INSERT statements to enter the rows.

Correct. This is one method of entering a small number of rows of data.

Question 4

When comparing primary keys to foreign keys, which of the following statements only describe foreign keys?

• Are always indexed.
• Cannot contain nulls.
• Must be unique within the table.
• Are related to a unique primary key.

Correct. Foreign keys do not have to be unique within the column, but the primary key they are related to must be unique.

Question 5

Which tool should you use if you want to administer MySQL from a graphical web interface?

• phpMyAdmin
• MySQL Workbench
• mysql
• mysqladmin

Correct. The phpMyAdmin tool is a popular third-party web interface for administering MySQL.