MySQL is preinstalled within our provided container and automatically
starts when you start the container. Once you are inside
the container, you can start the MySQL command-line interface by typing
mysql:
$ mysql
Then you should receive the following prompt
MariaDB [(none)]>
and be inside the MySQL command-line interface. All commands in this tutorial should be issued inside the MySQL command-line unless noted otherwise.
MySQL allows users to create multiple databases, so that a single
MySQL server can host databases for many independent applications.
Before you start issuing SQL commands to mysql, you first have to
select the database that you will be using. In order to see what
databases currently exist, run
SHOW DATABASES;
You will see an output like
+--------------------+
| Database |
+--------------------+
| class_db |
| information_schema |
+--------------------+
information_schema is a database that MySQL creates automatically and
uses to maintain some internal statistics on datbases and tables. The
other database, class_db, is what we created for the class (note
database names are case-sensitive in MySQL). Select the class_db
database for the rest of this tutorial by issuing the command
USE class_db;
It is also possible to specify a database as a command line parameter to
the mysql command:
$ mysql class_db
Once you select a database, you can execute any SQL command. For
example, you can create a table using the CREATE TABLE command:
CREATE TABLE <tableName> (
<list of attributes and their types>
);
Note that all reserved keywords (like CREATE and TABLE) are
case-insensitive and identifiers (like table names and attribute
names) are case-sensitive in MySQL by default. That is, a table named
STUDENT is different from the student table.
You may enter a command on one line or on several lines. If your command
runs over several lines, you will be prompted with " -> " until you
type the semicolon that ends any command. An example table-creation
command is:
CREATE TABLE tbl(a int, b char(20));
This command creates a table named tbl with two columns. The first,
named a, is an integer, and the second, named b, is a character
string of length (up to) 20.
When you create a table, you can declare a (set of) column(s) to be the primary key like:
CREATE TABLE <tableName> (..., a <type> PRIMARY KEY, b, ...);
or
CREATE TABLE <tableName> (<attrs and their types>, PRIMARY KEY(a,b,c));
Having created a table, we can insert tuples into it. The simplest way
to insert is with the INSERT command:
INSERT INTO <tableName> VALUES ( <list of values for attributes, in order> );
For instance, we can insert the tuple (10, 'foobar') into relation
tbl by
INSERT INTO tbl VALUES (10, 'foobar');
Once tuples are inserted, we can see the tuples in a relation with the command:
SELECT * FROM <tableName>;
For instance, after the above create and insert statements, the command
SELECT * FROM tbl;
produces the result
+------+--------+
| a | b |
+------+--------+
| 10 | foobar |
+------+--------+
Instead of inserting tuples one at a time, it is possible to create a file that contains all tuples that you want to load in batch. The command for bulking loading tuples from a file is the following:
LOAD DATA LOCAL INFILE <dataFile> INTO TABLE <tableName>;
where <dataFile> is the name of the file that contains the tuples.
For example, the command
LOAD DATA LOCAL INFILE 'movie.del' INTO TABLE Movie;
will load all tuples
in the movie.del file of the current directory into the table Movie.
Each line in the data file corresponds to one tuple and columns are
separated by a tab character (\t). You can specify a NULL value in the
data file using \N. For example, the following data file
1 first
2 second
3 \N
will insert three tuples, (1, ‘first’), (2, ‘second’), and (3, NULL) to
a table. If you want to use, say, commas to separate columns, not tabs,
add FIELDS TERMINATED BY ',' to the LOAD command as follows:
LOAD DATA LOCAL INFILE <dataFile> INTO TABLE <tableName> FIELDS TERMINATED BY ',';
If some columns in the data file is enclosed with, say, double quotes,
you need to add OPTIONALLY ENCLOED BY '"' as well:
LOAD DATA LOCAL INFILE <dataFile> INTO TABLE <tableName>
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
Notes on CR/LF issue: If your host OS is Windows, you need to pay
particular attention to how each line of a text file ends. By
convention, Windows uses a pair of CR (carriage return) and LF (line
feed) characters to terminate lines. On the other hand, Unix (including
Linux and Mac OS X) use only a LF character. Therefore, problems arise
when you are feeding a text file generated from a Windows program to a
program running in Unix (such as mysql in Docker container). Since the end of the
line of the input file is different from what the tools expect, you may
encounter unexpected behavior from these tools. If you encounter this
problem, you may want to run dos2unix command from Docker container on your
Windows-generated text file. This command converts CR and LF at the end
of each line in the input file to just LF. Type dos2unix --help to
learn how to use this command.
To remove a table from your database, execute
DROP TABLE <tableName>;
We suggest you execute
DROP TABLE tbl;
after trying out the sequence of commands in this tutorial to avoid leaving a lot of garbage tables around.
You can get the set of all tables within the current database by the following command:
SHOW TABLES;
Once you know the list of tables, it is also possible to learn more about the table by issuing the command:
DESCRIBE <tableName>;
Instead of typing and running SQL commands at a terminal, it is often more convenient to type the SQL command(s) into a file and cause the file to be executed.
To run the commands in foo.sql (in the current working directory),
type:
SOURCE foo.sql;
in mysql. Files like foo.sql that have SQL commands to be executed
are often referred to as a (batch) script file. You can also execute the
script file directly from the Unix shell by redirecting the input to
mysql like the following:
$ mysql class_db < foo.sql
Again, pay attention to the CR/LF issue if your host OS is windows and
if you create your SQL batch script file from Windows. Run dos2unix on
the file if necessary.
mysql provides the command TEE to save the queries that you executed
and their results to a file. At the MariaDB> prompt, you say:
TEE foo.txt;
and a file called foo.txt will appear in your current directory and
will record all user input and system output, until you exit mysql or
type:
NOTEE;
Note that if the file foo.txt existed previously, new output will be
appended to the file.
mysqlTo leave mysql, type
QUIT;
By default, when you run mysql, you connect to MySQL as a user with
the same name of your Unix account: cs143.
It is possible to run mysql command under the “root” user
using the sudo command like the following:
$ sudo mysql
[sudo] password for cs143:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.3.25-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
When prompted for a password, type your password (which is password
in our container). You are now connected to the
class_db database as the user root.
The user root is the “superuser” or the “database administrator” of
our MySQL installation and has full access to all databases and tables,
including the ability to create new users, change user privileges, and
so on. Because the root user has unrestricted access, for security
purposes, it is best to connect to MySQL as root only when you need to
perform one of these administrative tasks. For your project work, the
default user account cs143 will be sufficient, which
has full access to the class_db database but nothing else.
As a final note, keep in mind that MySQL maintains its own username and
password pairs independently of the underlying operating system (OS). Thus, it is possible
to create a MySQL user that does not exist in the underlying OS and vice
versa. This means that your default MySQL user cs143 is not related
to your Linux account cs143 except the shared name. In fact,
cs143 in MySQL has a different password (an empty password)
from that of cs143 of the Linux account (“password”).
mysql provides internal help facilities for MySQL commands. To see a
list of commands for which help is available, type help or
help contents in mysql. To look up help for a particular topic
(listed in the contents), type help followed by the topic.
This document was written by Junghoo “John” Cho for classes that he teaches at UCLA.