MySQL Basics

I.1

Overview

I.1.1 Introduction

MySQL is an Open Source Structured Query Language (SQL) database management system. MySQL is a trademark of Oracle Corporation which also distributes and supports MySQL. This document will discuss the basics of using MySQL once it is installed. These include basic administrative commands such as setting passwords and creating new users. Very basic database management is also covered. The operations in database management of interest including creating and editing databases, tables in databases and structures in tables.

I.1.2 Prerequisites

If MySQL has not been installed, installation is covered by: "Setting up Localhost".

I.1.3 Contents

I.1 Overview

I.1.1 Introduction
I.1.2 Prerequisites
I.1.3 Contents

I.2 MySQL Monitor Basics

I.2.1 Start and Stop
I.2.2 Non-root Login

I.3 User Management

I.3.1 Intro
I.3.2 Change Password for root
I.3.3 Create and Delete Users
I.3.4 View Users

I.4 Database Basics

I.4.1 Intro
I.4.2 Creation and Removal
I.4.3 Inspection
I.4.4 Managing User Privlieges

I.5 Tables

I.5.1 Intro
I.5.2 Table Creation and Removal
I.5.3 Viewing the Tables
I.5.4 Add a Row
I.5.5 Delete a Row
I.5.6 Edit an Existing Row
I.5.7 An Example Table
I.5.8 Searching Tables

I.2

MySQL Monitor Basics

I.2.1 Start and Stop

MySQL provides its own built in tool to administer the databases, namely the MySQL Monitor. This section will cover how to enter and exit the monitor

The following starts up MySQL:

$ su # cd /usr/local/mysql # ./bin/mysqld_safe --user=mysqluser

To start the MySQL monitor and use it with root privileges, log into the MySQL monitor as root:

# ./bin/mysql -u root -p

To stop the monitor and MySQL itself the following sequence of commands can be used. To exit the MySQL monitor enter the command \q. To stop MySQL:

# cd /usr/local/mysql # ./bin/mysqladmin -u root -p shutdown

I.2.2 Non-root Login

For security reasons, and to prevent accendential changes it can be of utility to log into either MySQL or the MySQL Monitor as a user other than "root". .

To switch to using the monitor as the user "arbprimary" instead of root, quit the monitor if necessary and log in again using the command:

# ./bin/mysql -u arbprimary -p

This will permit the use of the monitor with the privlieges associated with the user "arbprimary" instead of "root". Creating new users such as "arbprimary" is discussed in the next section, "I.3 User Management".

I.3

User Management

I.3.1 Intro

Basic user management includes displaying users, creating users, deleting users, changing their passwords, and granting them privileges. This section will briefly cover the basics of these tasks.

I.3.2 Change Password for root

When MySQL was installed the root user was given a randomly generated password. To change the password for root from the random password originally generated during the installation of MySQL to a chosen password, enter the MySQL Monitor. Then use the following command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

and follow the prompts.

This command can also be used to change the password for users other than root; the string, "'root'@'localhost'" must be replaced by a string appropriate to the user for whom the password is being changed.

I.3.3 Create and Delete Users

To create a new user for MySQL use the command:

mysql> CREATE USER 'arbprimary'@'%' IDENTIFIED BY 'new_user_password';

This creates a new user named “arbprimary” with login rights from any host. The field that comes after the “@” symbol is the host allowed for the user to log in from and the “%” symbol is a wild card which specifies any.

Users can be deleted with the following command:

mysql> DROP USER 'arbprimary'@'%';

This line drops, or deletes, the user that the previous chapter created.

I.3.4 View Users

To view the current users use the command:

mysql> SELECT user FROM mysql.user;

The ouput from this should show several users including "root" and the new user, "arbprimary".

To show the permissions for the user "arbprimary" use the following command:

mysql> SHOW GRANTS FOR arbprimary;

This will show that "arbprimary" is granted usage on "*.*". However currently "arbprimary" does not have any privileges on any databases. Adding privileges for the new user will be covered in "I.4 Database Basics", the next section.

I.4

Database Basics

I.4.1 Intro

This section will covers the basics of database management.This includes, creating databases, deleting databases, granting user permissions on databases and viewing the results.

I.4.2 Creation and Removal

If not already logged in, log into the MySQL monitor as root. Create a new database:

mysql> CREATE DATABASE arbordb;

This command creates a new database, "arbordb".

A database can be deleted with the command “DROP DATABASE”. For example to delete the newly created “arbordb”:

mysql> DROP DATABASE arbordb

I.4.3 Inspection

List the current databases for the system:

mysql> SHOW DATABASES;

This should show the newly created “arbordb” along with some system databases that are automatically created when MySQL is installed.

I.4.4 Managing User Privlieges

Grant the user “arbprimary” full privileges for the newly created database “arbordb”.

mysql> GRANT ALL ON arbordb.* TO 'arbprimary'@'%';

This grants all privileges including modification privileges to the user “arbprimary” for the new database. To check that the privileges were granted:

mysql> show grants for arbprimary; +---------------------------------------------------------+ | Grants for arbprimary@% | +---------------------------------------------------------+ | GRANT USAGE ON *.* TO 'arbprimary'@'%' | | GRANT ALL PRIVILEGES ON `arbordb`.* TO 'arbprimary'@'%' | +---------------------------------------------------------+

The line “GRANT ALL PRIVILEGES...” now appears in the grants for “arbprimary”.

The following line removes the privileges to operate on the database "arbdb" from the user "arbprimary":

mysql> REVOKE ALL ON arbordb.* FROM 'arbprimary'@'%';

I.5

Tables

I.5.1 Intro

The data storage units within a MySQL database are called "tables". These tables each have a name and a format. They store sequences of data, as records, organized in a series of fields. Typically when the table is visualized the series of fields correspond to the columns of the table, and the series of data values stored for each column for a given record corresponds to a row in the table.

This section will cover creating, deleting and displaying tables. The commands necessary to edit and search tables will also be covered briefly.

The MySQL monitor will be used at this stage to operate on the database and manipulate the tables. For the commands in this section to work a user named "arbprimary" with privileges for the database "arbdb" must be created. This is covered in the previous sections; "User Management" covers creating new users, and "Database Basics" covers creating databases and granting user permissions for them. After the user "arbprimary" has been created, open the "MySQL Monitor" as this user:

# ./bin/mysql -u arbprimary -p

This will allow the database "arbdb" to be manipulated in the monitor.

I.5.2 Table Creation and Removal

To begin with a database in which the tables will be manipulated must be chosen. As "arbprimary" choose the example database, "arbdb":

mysql> CREATE TABLE trees ( id INT NOT NULL AUTO_INCREMENT, species VARCHAR(40), name VARCHAR(40), evergreen INT(1), form INT, branch INT, height10 DECIMAL, height30 DECIMAL, height DECIMAL, PRIMARY KEY(id));

This creates a new table, "trees" in the current database, "arbordb". This table has two string columns (VARCHAR) which accept strings of variable length up to 40 chars: species and name. It has three columns that accept integer data (INT) and three columns that accept decimal data (DECIMAL). The number in parentheses behind the type specifies the length of the field. For instance, "evergreen" is an "INT" of length 1.The "id" column which is an integer data type is set to be an automatically incrementing primary key. The "id" does not need to be set it will increment with each new record added; it is never null.

The table can be deleted with the command:

mysql> DROP TABLE trees;.

I.5.3 Viewing the Tables

To view which tables exist in a database use the following command:

mysql> SHOW TABLES; +-------------------+ | Tables_in_arbordb | +-------------------+ | trees | +-------------------+

To show the rows and the columns of the table "trees" use the command:

mysql> SELECT * FROM trees;

Since there are currently no rows in the table "trees" this command will only show the the column names.

I.5.4 Add a Row

To fill one row in the table, “trees” use the command:

mysql> INSERT INTO trees (species, name, evergreen, form, branch, height10, height30, height) VALUES('picea pungens','colorado blue spruce',1,1,1,15,30,70);

If the resulting table is displayed now, it is printed as:

mysql> SELECT * FROM trees; +----+---------------+----------------------+-----------+------+--------+----------+----------+--------+ | id | species | name | evergreen | form | branch | height10 | height30 | height | +----+---------------+----------------------+-----------+------+--------+----------+----------+--------+ | 1 | picea pungens | colorado blue spruce | 1 | 1 | 1 | 15 | 30 | 70 | +----+---------------+----------------------+-----------+------+--------+----------+----------+--------+

To add another line execute:

mysql> INSERT INTO trees (species, name, evergreen, form, branch, height10, height30, height) VALUES('pica pung','blue spruce',1,1,1,10,20,70);

The command "SELECT * FROM trees;" should now display both lines:

+----+---------------+----------------------+-----------+------+--------+----------+----------+--------+ | id | species | name | evergreen | form | branch | height10 | height30 | height | +----+---------------+----------------------+-----------+------+--------+----------+----------+--------+ | 1 | picea pungens | colorado blue spruce | 1 | 1 | 1 | 15 | 30 | 70 | | 2 | pica pung | blue spruce | 1 | 1 | 1 | 10 | 20 | 70 | +----+---------------+----------------------+-----------+------+--------+----------+----------+--------+

I.5.5 Delete a Row

To delete the second line added to the table trees use the command:

mysql> DELETE FROM trees WHERE id=2;

Displaying the table again should show that the second row has in fact been deleted.

I.5.6 Edit an Existing Row

To change an existing line in a table use the command:

mysql> UPDATE trees SET height=65 WHERE id=1;

This changes the height column from 70 to 65. To alter the format of the table itself see: "alter-table" from mysql.com.

I.5.7 An Example Table

An example table will be needed for later exercises. For instance, the following example table can be created using the commands covered in this section:

+----+-------------------+----------------------+-----------+------+--------+----------+----------+--------+ | id | species | name | evergreen | form | branch | height10 | height30 | height | +----+-------------------+----------------------+-----------+------+--------+----------+----------+--------+ | 1 | picea pungens | colorado blue spruce | 1 | 1 | 1 | 15 | 30 | 65 | | 3 | malus x domestica | red delicious apple | 0 | 1 | 1 | 20 | 20 | 20 | | 4 | picea omorika | serbian spruce | 1 | 1 | 2 | 15 | 30 | 50 | +----+-------------------+----------------------+-----------+------+--------+----------+----------+--------+

I.5.8 Searching Tables

Search queries operating on tables is of great utility in retrieving records from the database. To search the table returning those entries that match a wild card use the command:

mysql> SELECT * FROM trees WHERE species LIKE 'picea%';

This command uses the wildcard "%" to return from the table "trees" those rows which have a species name which begins with "picea". For a more complete description on using wildcards to search for table entries see: "pattern-matching" from mysql.com.

To search the table and only see certain columns returned use the command:

mysql> SELECT species, name, height FROM trees WHERE name LIKE '%spruce';

If this command is used on the example tables described in section I.5.7, then the result is:

+---------------+----------------------+--------+ | species | name | height | +---------------+----------------------+--------+ | picea pungens | colorado blue spruce | 65 | | picea omorika | serbian spruce | 50 | +---------------+----------------------+--------+

This search command returns the values in the columns: species, name and height; for all rows that have a name ending in "spruce".