Basics of MySql on the Commandline #1
MySQL is a multithreaded, multi-user SQL database management system (DBMS) which has more than 11 million installations. The basic program runs as a server providing multi-user access to a number of databases.
Originally financed in a similar fashion to the JBoss model, MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now a subsidiary of Sun Microsystems, which holds the copyright to most of the codebase. The project’s source code is available under terms of the GNU General Public License, as well as under a variety of proprietary agreements.
This tutorial is to get you familiar with the basics of mysql. It is not meant to be an end-all resource, but a starting point. Later we will discuss more advanced functions of mysql and how to manage databases via web interfaces such as phpMyAdmin.
(Video to Come)
First lets gain root access via su if necessary, otherwise use sudo before each of these commands.
Installing mysql is rather simple on most distributions:
- On Fedora: yum install mysql mysql-server
- On Suse: zypper in mysql mysql-client
- On Ubunt/Debian: sudo apt-get install mysql
After the install completes lets ensure mysql is running.
To Start MySql
/etc/init.d/mysql start
or
service mysqld start
To Check the Status on MySql type:
/etc/init.d/mysql status
Creating a MySQL ‘root’ Account
MySQL stores all its users and passwords in a special database called ‘mysql’. Users can be added to this database including the databases to which they have access to. To set a root password, you only have to execute a simple command for a fresh installation:
# mysqladmin -u root password enter-your-password-here
Accessing the command line
To access the MySQL command line, you have to execute the command and enter the new set root password when prompted:
# mysql -u root -p
Remember that all MySQL CLI commands end with a semi-colon (;) including the exit command. This should have taken you to a command line that begins with mysql>
To Create a Database we type:
mysql> CREATE DATABASE test_database1;
Show Created Databases, if you wish to see the created databases in a mysql system type:
mysql> SHOW DATABASES;
You will notice there’s a database called “mysql” The mysql database is used by the MySQL server to store information about users, permissions, etc. There’s also a database simply called “test”. The test database is often used as a workplace for MySQL users to test and try things—this is useful in a work environment where many people are working with critical information.
Create a table
USE test_database1;
then press ENTER. The terminal should state “Datebase Changed” and take you back to the mysql prompt. The USE command allows you to start using the database test_database1.
Type: (as one line)
CREATE TABLE name (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first CHAR(25), last CHAR(25));
then press ENTER.
This string of commands is used to CREATE a TABLE called name with three fields: id, first, and last.
Here are the datatypes and properties for these fields:
- INT
CREATE TABLE name (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first CHAR(25), last CHAR(25) );
The INT datatype for the id field ensures it will contain only integers—numbers, not text.
- NOT NULL
CREATE TABLE name (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first CHAR(25), last CHAR(25) );
The NOT NULL property ensures the id field cannot be left blank.
- PRIMARY KEY
CREATE TABLE name (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first CHAR(25), last CHAR(25) );
The PRIMARY KEY property makes id the key field in the table.
In any database table, one field should be the key field—a field that can contain no duplicates. In this table, name, the id field is the key field because it contains the PRIMARY KEY property. This means the name table can’t have two records with an id of 35.
-
AUTO_INCREMENT
CREATE TABLE name (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first CHAR(25), last CHAR(25) );
The AUTO_INCREMENT property automatically assigns a value to the id field, increasing the previous id number by one for each new field.This ensures that the NOT NULL (can’t be blank) and the PRIMARY KEY (can’t have duplicates) properties of the id field are both satisfied.
-
CHAR
CREATE TABLE name (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first CHAR(25), last CHAR(25) );
The CHAR datatype for the first and last fields limits the length of entries to 25 characters each.
In the test_database1 database, you’ve created a table called name that’s organized like this:
|
Field |
Datatype |
Properties |
|
id |
INT |
primary key, not null, auto increment |
|
first |
CHAR(25) |
|
|
last |
CHAR(25) |
Create a record
Type:
INSERT INTO name (id, first, last) VALUES (NULL, ‘Linux’, ‘Torvalds’);
then press ENTER.
This command string creates the first record in the table name. It reads much like a sentence:INSERT INTO the table name (which has the fields id, first, and last) the corresponding VALUES NULL, Linux, and Torvalds.
Since the id field can’t be blank (it has a NOT NULL property), putting a NULL value in it forces MySQL to automatically number the record (because the id field also has the property AUTO_INCREMENT).
The data in the table name is now organized like this:
|
Fields: |
id |
first |
last |
|
Record: |
1 |
Linus |
Torvalds |
Note: Text is enclosed within single quotes to let MySQL know that it’s just text, not a command. If the phrase ‘Who created linux?’ was not enclosed in single quotes, MySQL might interpret the words name and values as commands, and get confused. In these examples, single-quotes are used. Double-quotes perform the same function.
Type: INSERT INTO name (id, first, last) VALUES (NULL, ‘Richard’, ‘Stallamn’), (NULL, ‘Alan’, ‘Cox’), (NULL, ‘Mark’, ‘Shuttleworth’);
then press ENTER.
This adds three records to the table name: one record each for person Richard Stallamn, Alan Cox, and Mark Shuttleworth. The data in the table name are now organized like this:
|
Fields: |
id |
first |
last |
|
Records: |
1 |
Linus |
Torvalds |
|
2 |
Richard |
Stallman |
|
|
3 |
Alan |
Cox |
|
|
4 |
Mark |
Shuttleworth |
Run a Query
Type:
SELECT * FROM name; then press ENTER. Thefollowing should be displayed
|
|
id |
first |
last |
|
1 |
Linus |
Torvalds |
|
|
2 |
Richard |
Stallman |
|
|
3 |
Alan |
Cox |
|
|
4 |
Mark |
Shuttleworth |
The SELECT command tells MySQL to perform a query. The asterisk (*) command tells MySQL to return everything (the asterisk means “everything” or “all”) that’s in the table name.
Type:
SELECT first, last FROM name ORDER BY last; then press ENTER.
Now only the first name column and the last name column should appear. This query is more precise than the previous one: it selects the fields first and last from the table name.ORDER BY puts the records in alphabetical order, based on the field last. In other words, it puts the presidents’ last names in alphabetical order.
Type: SELECT id, first, last FROM name ORDER BY id;
then press ENTER.
In this query, ORDER BY id places the records in numeric order, based on their id numbers. IP: To arrange records in reverse numeric or reverse alphabetical order, add DESC on the end. For instance, type:
SELECT first, last FROM name ORDER BY last DESC;
The DESC option refers to the word “descending.” It tells MySQL to order things descending from high to low instead of the default: low to high.
Type:
\q;
then press ENTER.
This closes your MySQL database connection. You are now logged out of the MySQL server: the mysql> prompt is gone and you should be returned to your linux shell prompt.
That’s it for this tutorial. We will cover more with MySql at a later date.