Skip to content

mysql

June 1, 2012

Onward on my LAMP journey to Mysql (http://dev.mysql.com/)

You can download mysql from the following website (I downloaded the Mysql Community Server): –

http://dev.mysql.com/downloads/mysql/

I will download and install the following to install mysql : –

prompt> wget -v http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/Downloads/MySQL-5.5/MySQL-devel-5.5.25-1.linux2.6.i386.rpm     # This is the development libraries for mysql

prompt> wget -v http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/Downloads/MySQL-5.5/MySQL-client-5.5.25-1.linux2.6.i386.rpm    # This is the mysql client

prompt> wget -v http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/Downloads/MySQL-5.5/MySQL-server-5.5.25-1.linux2.6.i386.rpm      # This is the mysql server

I then proceeded to install : –

prompt> rpm -ivh MySQL-devel-5.5.25-1.linux2.6.i386.rpm

prompt> rpm -ivh MySQL-client-5.5.25-1.linux2.6.i386.rpm

When i was trying to install the server i received conflict errors saying that it will not be installed as mysql-libs-5.1.61-1.el6_2.1.i686 is installed. So what i did was remove this old package by: –

prompt> rpm -ev –nodeps mysql-libs-5.1.61-1.el6_2.1.i686

And then proceeded to install the mysql server: –

prompt> rpm -ivh MySQL-server-5.5.25-1.linux2.6.i386.rpm

I started mysql by: –

prompt> service mysql start

Did a quick check to make sure that it will start at boot too: –

prompt> chkconfig –list mysql

Did the following to confirm what version of mysql was installed by asking the mysqladmin application: –

prompt> mysqladmin version

prompt> mysql

This immediately logged me into the mysql database as a unprivileged user (without the access to create or modify records) without needing to give a password.

Notice how the prompt has changed to mysql> this tells us that we are in the mysql database. Doing the following command will give more detailed information about who is logged into the database amongst other information: –

prompt> status

Now exit the database as we have verified it is working correctly

prompt> exit

Now as I can logon to mysql without any passwords this likely means that i can logon as the user root that will have all privledges available to create, delete and modify any records on mysql. For this reason I will login as the user root and change the password so that this user account is sercure: –

prompt> mysql -u root

now that i am logged in as root on mysql, i will change the password for root: –

mysql> SET PASSWORD FOR
-> ‘root’@’localhost’ = PASSWORD(‘type_password_here’);

After I hit return i got confirmation that my commands were correctly carried out by feedback from mysql: –

Query OK, 0 rows affected (0.00 sec)

Now if i try to login as root without a password mysql will have none of it and will not let me in as there is now a password assigned to the root user. So i have to login as follows: –

prompt>mysql -u root -p # Hit return and then prompt will ask for a password, type it in, hit return and you are in!

Next thing i will do is create an account called bob that i will use from here on out to create, delete and modify mysql, whilst still logged in as root : –

mysql> CREATE USER'bob'@'localhost'IDENTIFIED BY'type_password_here';

mysql> GRANT ALL PRIVILEGES ON*.*TO'bob'@'localhost'
-> WITH GRANT
OPTION;

# This grants bob access to add, remove and modify all databases

Again exit mysql and now login as bob with the new password to make sure bob account can be accessed: –

prompt> mysql -u bob -p

Now typing the following will show us what databases the account bob has access to: –

mysql> SHOW DATABASES;

You should see the test database available, lets use that database: –

mysql> USE test # the prompt should come back with Database changed, this acts as acknowledgement that we have changed to the test database

As an example we will create a database called shopping_trolley and build the database up with records: –

mysql> CREATE DATABASE shopping_trolley;     # This creates the database

mysql> USE shopping_trolley #  This selects the database so that we can add records specifically to this database

If you ever want to make sure what database you have selected and are working on then all you need to do is type: –

mysql> SELECT DATABASE();

From here on out it might be worth me telling you what the structure of the record is. In each Database we have many Tables and in those Tables are loads of Columns that contain Data. To be honest keep it simple like this and it makes it more easier to understand!

So far we have created our database, but we have no tables in it yet, we can confirm this by: –

mysql> SHOW TABLES;

What we’ll do is create a table that we intent to keep a shopping list for certain vegetables to buy, we will include the following columns in the vegetable table: –

Item

Quantity

Price

Required

We will create the vegetables table and the above columns in it by: –

mysql> CREATE TABLE vegetables (Item VARCHAR(20), Quantity VARCHAR(2), Price VARCHAR (5), Required DATE);

As you can see above i have limited Item, Quantity and Price to data type VARCHAR and set Required as data type DATE. Furthermore you can see i have limited the amount of characters by specifying it in the brackets. To find out more about what kind of data types you can use visit http://dev.mysql.com/doc/refman/5.5/en/data-types.html

Now if you do a show tables this should now show vegetables table: –

mysql>SHOW TABLES

Now to see all the columns in your table: –

mysql> DESCRIBE vegetables;

Now that we have our table and columns created we can now add data to the columns, there are two ways to do this, and they are with either or both: –

  1. LOAD DATA
  2. INSERT

To insert the data from a file you will need to create a text document like vegetables.txt and populate it with the data that you want to add to the table and columns: –

Tomato  1       40p     2012-01-26
Cucomber        44      100p    2012-02-15
Cabbage 4       20p     2012-01-01
Potatoe 15      76p     2012-03-18

As you can see above i have typed the data and saved it to a text file called vegetables.txt. Notice how each column is seperated by a tab, this helps to distinguish each column from another. The columns represents data that will cover the table’s columns of Item, Quantity, Price and Required.

Now to put this data information into the vegetables table and columns we will have to do the following: –

mysql> LOAD DATA LOCAL INFILE ‘/home/main/vegetables.txt’ INTO TABLE vegetables;

Now the otherway to insert data into your tables and columns is by adding data information one at a time, for example let’s say we want to add the following data into the vegetables table and columns: –

sprouts    6        23p          2012-05-29

To add the above data information to the vegetables table and columns we do: –

mysql> INSERT INTO vegetables
-> VALUES (‘sprouts’,’6′,’23p’,’2012-05-29′);

Now to make sure all of this data information has been added to the tables and its columns we need to select to view either a specific column like ITEM: –

mysql> SELECT item FROM vegetables;

Or we can view all of the data information that we have put in the table by using * : –

mysql> SELECT * FROM vegetables;

So lets say that you have done the above command and found that one record is incorrect, lets say you want the sprouts to be 25p, again there are two ways to make this amendment. You can clear the table and columns of any data information and then load in the corrected information as well as all of the other data again from the vegetables.txt file: –

mysql> DELETE FROM vegetables;
mysql> LOAD DATA LOCAL INFILE  ‘/home/main/vegetables.txt’ INTO TABLE vegetables;

Or you can update the data row for the sprouts by: –

mysql> UPDATE vegetables SET Price = ’25p’ WHERE Item = ‘sprouts’;

Now let’s say we want to get a whole row of data for item Cabbage, this is how we do it: –

mysql> SELECT * FROM vegetables WHERE Item = ‘Cabbage’;

Similarly if we want to query all 2012-01-26 data information in the Required Table Column: –

mysql> SELECT * FROM vegetables WHERE Required = ‘2012-01-26’;

If you want to see whole table columns then you can specify which ones you want to see, for example i have specified to see Item and Price: –

mysql> SELECT Item, Price FROM vegetables;

We can order by the price too: –

mysql> SELECT Item, Price FROM vegetables ORDER BY Price;

If you want to order by Price going the opposite way then just replace Price with ‘Price’

Just to recap…

CREATE DATABASE database_name;

USE database_name

CREATE TABLE table_name (data_type (number_of_characters), data_type2 (number_of_characters));

LOAD DATA LOCAL INFILE ‘location_of_data_to_be_inserted_from_text_file’ INTO TABLE table_name;

This should be enough for a basic understanding of how to create information in mysql. The next thing to do would be to display this data int a dynamic loading webpage so instead if going to the command line and typing commands to view specific data we can instead use a webpage to do the same stuff.

Advertisements

From → Linux

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: