Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
Module No. 8
MySQL/MSSQL Server Basics
Course Title: Advanced Database Systems Course IT 116
Code:
Instructor: SHERWIN M. CAACBAY, MIT Term & AY: 2nd Sem, AY 2020-
YSSANDREA KNERRE R. 2021
ZINAMPAN, MIT
Email wincsureq@gmail.com Contact No: 09551987227
Address: yssandreaknerrezinampan@gm 09394572402
ail.com
I. Overview
MySQL is the most popular Open Source Relational SQL Database
Management System. MySQL is one of the best RDBMS being used for
developing various web-based software applications. MySQL is developed,
marketed and supported by MySQL AB, which is a Swedish company. This
tutorial will give you a quick start to MySQL and make you comfortable with
MySQL programming.
II. Intended Learning Outcome (ILOs)
At the end of the lesson, the student should be able to:
A. Distinguish the different functions of the different directories in
MySQL/MSSQL Server
B. Explain MySQL Executables/ MSSQL Server Executables
C. Differentiate the three User Interfaces of MySQL
D. Explain the different parts of User Interface of MSSQL Server
E. Connect to MySQL Monitor/ MSSQL Server UI and to familiarize with
its environment.
III. Learning Resources & References
1. Book References-APA Style
Jeffrey A. Hoffer, Mary B. Prescott, Fred R. McFadden, Modern
Database Management, 6th edition, 2002
2. Internet
https://www.tutorialspoint.com/mysql/index.htm
https://dev.mysql.com/doc/refman/8.0/en/data-directory.html
https://flylib.com/books/en/3.483.1.31/1/
IV. Lecture Content/Summary of Lesson
1.1 MySQL/MSSQL
We will assume in this discussion that you have installed MySQL in the
default location on your operating system. If you have installed it
elsewhere, the basic directory structure will still be the same, but the base
locations will be different.
If your system came with a vendor-supplied version of MySQL, you may
have a different structure. For example, if you have Red Hat 9, you will
find that it has reorganized the files to fit into its overall filing system. If
you have installed MySQL from a MySQL-supplied RPM, the files should be
in the locations documented here.
You should also note that the directory structure will vary slightly
depending on which optional items you installed and whether you
Page 1 of 7
Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
installed a binary distribution or a source distribution. In this book, we
assume that you installed a binary distribution as recommended in
Chapter 1, "Installing MySQL."
If you have a Unix-like operating system, the default location for the
MySQL files is in /usr/local/mysql. If you have Windows, the default
location is C:\mysql.
At that location, you will find a set of directories, including the following
(assuming that you installed from a binary distribution):
o bin: This directory contains the MySQL server and client programs
and several other useful compiled programs. The contents of this
directory are covered in the next section of this chapter.
o scripts: This directory contains a set of Perl scripts that perform
useful tasks. We will look at these in the next section of this
chapter.
o data: This is where your actual database data resides.
o docs (Linux) or Docs (Windows): You will find the MySQL
documentation in this directory, in HTML format.
o sql-bench (Linux) or bench (Windows): This directory contains a
benchmarking suite.
There are also a couple of other directories that the average user will
rarely go into: include (containing header files), lib (containing libraries
used by MySQL), share (containing MySQL's error messages), and
examples (only in Windows, containing examples of using the libmysql
DLL). You may need to know where these directories are if you install
other software (such as PHP) that links to MySQL, but you are unlikely to
interact with them directly.
1.2 Overview of Executables
The MySQL executables are in the bin and scripts directories. Let's begin
by looking in the bin directory.
You will find the mysqld executable and any variations for your operating
system in here. This is the MySQL server program, the one you set up to
run automatically when you started your system in Chapter 1.
You will also find mysql, the MySQL monitor, here. This is the basic
command-line client.
As well as these two main programs, you will find several others. We will
cover the usage of many of these through the course of this book. Some
particularly important programs are listed here:
o mysqladmin: Used to perform many administrative functions.
o myisamchk: Used to check and repair damaged MyISAM tables.
o mysqldump: Used to back up your databases.
Page 2 of 7
Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
o mysqlbinlog: Used to read the contents of the binary log, essential
for disaster recovery.
o mysqlshow: Used to get information about databases and tables.
We will use mysqld and mysql throughout the course of this book, and
we'll revisit these other programs in Part V, "Administering MySQL."
Now, look in the scripts directory. The programs in this directory are
interpreted Perl scripts, rather than compiled programs as in the bin
directory. Again, we will cover the usage of some of these as you work
your way through this book. The main one we will use in this book is
mysqlhotcopy, which is used for backing up databases. (This is covered in
Chapter 14, "Backup and Disaster Recovery.")
1.3 Overview of User Interfaces
You have various options as to the user interface or client to MySQL that
you choose to use. The three most popular user interfaces are the
command-line interface mysql (also known as the MySQL monitor), MySQL
Control Center (MySQLCC for short), and phpMyAdmin.
The MySQL monitor comes with your basic installation. It is a command-
line interface. This is always available as an option, it is simple to use, and
it works on all platforms.
The MySQL Control Center (MySQLCC) is a graphical user interface. It is
written using the Qt windowing toolkit, which is cross-platform. At the time
of writing, MySQLCC was available for Unix and Windows, and it is planned
to be available for OS X in the future.
phpMyAdmin is a Web-based interface for using MySQL. It is very popular
with ISPs that supply MySQL for use in developing Web applications.
If you have MySQL installed, you already have the MySQL monitor.
MySQLCC is an official MySQL product, but depending on which MySQL
version you have, it may be a separate download. You can get it from
www.mysql.com/downloads/mysqlcc.html
MySQLCC is extremely simple to install, and you can find instructions for
this at
www.mysql.com/products/mysqlcc/install.html
phpMyAdmin can be downloaded from
www.phpmyadmin.net/
If you want to install phpMyAdmin yourself, you will first need a working
Web server and PHP installation.
For the purposes of this book, it does not matter which user interface you
choose to use. The functionality is basically the same for all of them. You
enter queries as text, and the results are displayed as text, regardless of
which user interface you use.
Page 3 of 7
Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
The examples given were usually tested using the MySQL monitor, but
you can use whatever you like ”mysql, MySQLCC, phpMyAdmin, or any
other front end you find on the Net. For some tasks , you will need to exit
from your user interface and use a command line.
1.4 Quick Introduction to the MySQL Monitor
We will now cover the basic use of the MySQL monitor. We covered
logging in to mysql in Chapter 1. Just as a reminder, you can connect to
MySQL using
mysql -u username -p
The client program has some other switches you may find useful. If you
are connecting to a MySQL database on another machine, you can use
the -h switch to specify the host; for example:
mysql -h hostname -u username -p
A really useful option to mysql is the --i-am-a- dummy option. You can also
invoke this option in a less pejorative way using -- safe-updates. For example:
mysql --i-am-a-dummy -u root -p
The effect of this switch is to limit the damage you can do with a single
command. This is an especially useful option (available for the command-
line tool only) while you are first learning to use MySQL. You might like to
use it while working your way through this book. (Specifically, this switch
stops you from updating or deleting rows unless you specify a key value
for those rows. If you don't know what this means yet, don't worry. All will
become clear in Part III, "Using MySQL.")
After you're logged in, you can see what databases exist on the system by
using the SHOW command:
show databases;
For most of you, this will be a short list at this stage. You should see the
database called mysql in the list. This is the system database that holds
information about user accounts and privileges. We'll discuss it later in the
book.
Notice that the command has a semicolon at the end of the line. Most
commands you type in the monitor need to be terminated with a
semicolon; otherwise, MySQL will not execute them. Try typing
show databases
and pressing Enter. MySQL just sits there and waits. You can now type a
semicolon and press Enter, and the command is executed. This allows you
to split complex commands over multiple lines for readability. You can
also type \g (backslash g) instead of the semicolon, but most people use
the semicolon.
You can select a database from this list and type this:
Use databasename;
Page 4 of 7
Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
(Substitute the name of the database you want to use.)
This tells MySQL that you want to work with a particular database. Pick
one and do this. (You may not have sufficient privileges to select any of
the databases. If you get a message to this effect, pick a different
database and try again.)
After you have selected a database, you can see what tables are in it by
typing
show tables;
You can get information on a particular table by typing
describe tablename;
You can log out of the monitor by typing
\q
You will notice that this command does not end with a semicolon. There is
a set of commands that begin with \ (backslash). None of them need a
terminating semicolon. You can get a list of these commands by typing
\h
(The h is for help.)
You can type commands and SQL statements directly into the monitor.
However, you can also put these commands and statements into a file
and run them all at once, like a script. We will do this later in the book,
when creating databases in Chapter 4, "Creating Databases, Tables, and
Indexes," for example.
If you are logged in to the MySQL monitor, you can run a file of commands
by typing
source filename
If you are not logged into the monitor, you can execute a file of
commands by using file redirection; for example:
mysql -u username -p < filename
You now know the basics of how to use the mysql client program.
V. Learning Activities
Activity 1. Describe the following directories in MySQL.
bin scripts data docs bench
Page 5 of 7
Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
Page 6 of 7
Tuguegarao Archdiocesan Schools System
Saint Joseph’s College of Baggao, Inc.
Baggao, Cagayan, Philippines
Transforming Lives, Shaping the Future
VI. Assessment
Quiz 1. Read and analyze the following statements carefully in column A and
match it with the items in Column B.
COLUMN A COLUMN B
1. Used to perform many A. mysqlbinlog:
administrative functions. B. phpMyAdmin
2. You can run a file of C. describe
commands by using the D. mysqladmin:
keyword? E. mysqlshow:
3. Used to check and repair F. show
damaged MyISAM tables. G. myisamchk:
4. You can see what databases H. use
exist on the system by using I. mysqldump:
what command? J. source
5. Used to back up your
databases.
6. You can get information on a
particular table using this
command
7. Used to read the contents of
the binary log, essential for
disaster recovery.
8. You can select a database
from this list using this
command.
9. Used to get information about
databases and tables.
10.It is a Web-based interface for
using MySQL.
VII. Assignment
Have an advance reading on Designing and Creating a database using
MySQL/MSSQL.
Page 7 of 7
