MySQL User guide

  1. How to install Mysql?

    • With internet connection:

      1. Using terminal:

        To install MySQL, run the following command from a terminal prompt:

        sudo apt-get install mysql-server

        During the installation process you will be prompted to enter a password for the MySQL root user. Once the installation is complete, the MySQL server should be started automatically. You can run the following command from a terminal prompt to check whether the MySQL server is running:

        sudo netstat -tap | grep mysql

        When you run this command, you should see the following line or something similar: 

        tcp   0   0 localhost:mysql       *:*        LISTEN 2556/mysqld
        If the server is not running correctly, you can type the following command to start it:

        sudo /etc/init.d/mysql restart

      2. Using Ubuntu software Center:

        Open ubuntu softaware centre, type Mysql Server in search, Click Install.

    • Offline installation:

      1. Using terminal:

        a. Download or acquire the deb packages of mysql. http://dev.mysql.com/downloads/mysql/5.6.html http://packages.ubuntu.com/quantal/multiarch-support

        b. Copy it in a empty folder.

        c. Go to to the empty folder path through terminal and type

        sudo dpkg -i .deb*

      2. Using Ubuntu software Center:

        a. Right click the deb package and click open through ubuntu software centre.

  2. To get Mysql Prompt in Terminal

    Type the following command to get MySQL prompt :

    mysql -u root -p

    Enter the Mysql password

    root123

    NOTE: mysql refers to command

    -u root -p refers to user root password

        >>login to MySQL as root user(u) with mysql password(p).

  3. Creation of Database and query execution.

    1. You will get the MySQL prompt as shown below:

      Alt text

    2. Database Object Creation:

      • To create Database

        CREATE DATABASE Student;

      • To Use Database

        USE Student;

      • To view the databases

        SHOW DATABASES;

      Alt text

    3. Table Creation: 

      CREATE TABLE student(
snum INT(4) PRIMARY KEY,
sname varchar(10),
major VARCHAR(10),
lev VARCHAR(2),
age INT(2));

CREATE TABLE faculty(
fid INT(4) PRIMARY KEY,
fname VARCHAR(10),
deptid INT(2));

CREATE TABLE class(
cname VARCHAR(10) PRIMARY KEY,
meetat varchar(10),
room VARCHAR(4),
fid INT(4) REFERENCES faculty(fid));

CREATE TABLE enrolled(
snum INT(4) REFERENCES student(snum),
cname VARCHAR(10) REFERENCES class(cname));

      Alt text

    4. Inserting values into Table:

      INSERT INTO student (snum,sname,major,lev,age) values(121,'Agrawal','CSE','SR',21);

INSERT INTO student (snum,sname,major,lev,age) values(119, 'Tony ', 'CSE', 'SR',21);

INSERT INTO student (snum,sname,major,lev,age) values(122, ' Krishna ', 'CSE', 'JR',20);

      Alt text

    5. to execute Query;

      To find the author1 of the book which has maximum sales:

SELECT author1_name
FROM author1 a,catalogue1 c
WHERE a.author1_id=c.author1_id AND book_id IN (
    SELECT book_id
    FROM orderdetails1
    WHERE quantity= (
        SELECT MAX(quantity) FROM orderdetails1)
        );

      Alt text

    6. To get the tables information use DESC command.

      Alt text

    7. Contents of the table can be viewed by using the command SELECT as follows:

      Alt text

    8. In between if user wants to clear the screen of the terminal then press: ctrl+l.

    9. To get any information about the commands used in the mysql use "help" command. Eg: HELP INSERT; HELP CREATE;

    10. Type exit to exit from the MySQL prompt.

      Note 1 : MYSQL is a a case sensitive. Ex: "DESC MEMBERS" is differ from "DESC members".

      Alt text

      Note 2:

Handy MySQL Commands
Description Command
To login (from unix shell) use -h only if needed. [mysql dir]/bin/mysql -h hostname -u root -p
Create a database on the sql server. CREATE DATABASE [databasename];
List all databases on the sql server. SHOW DATABASES;
Switch to a database. USE [db name];USE [db name];USE [db name];
To see all the tables in the db. SHOW TABLES;
To see database's field formats. DESCRIBE [tadescribe [table name];
To delete a db. DROP TABLE [database name];
To delete a table. DROP TABLE [table name];
Show all data in a table. SELECT * FROM [table name];
Show only N number of rows from table. SELECT * FROM [table name] LIMIT N;
Show fix N of rows from table starting from Mth record SELECT * FROM [table name] LIMIT N OFFSET M;
Returns the columns and column information pertaining to the designated table. SHOW COLUMNS FROM [table name];
Show certain selected rows with the value "whatever". SELECT * FROM [table name] WHERE [field name] = "whatever";
Show all records containing the name "Bob" AND the phone number '3444444'. SELECT * FROM [table name] WHERE name = "Bob" AND phonenumber = '3444444';
Show all records not containing the name "Bob" AND the phone number '3444444' order by the phonenumber field. SELECT * FROM [table name] WHERE name != "Bob" AND phonenumber = '3444444' order by phone number;
Show all records starting with the letters 'bob' AND the phone number '3444444'. SELECT * FROM [table name] WHERE name like "Bob%" AND phonenumber = '3444444';
Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a. SELECT * FROM [table name] WHERE rec RLIKE "^a$";
Show unique records. SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc). SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Count number of rows. SELECT COUNT(*) FROM [table name];
Join tables on common columns. SELECT lookup.illustrationid, lookup.personid, person.birthday FROM lookup
LEFT JOIN person ON lookup.personid=person.personid=statement TO JOIN birthday IN person table with primary illustration id;
Switch to the mysql db. Create a new user. INSERT INTO [table name] (Host,User,Password) VALUES('%','user',PASSWORD('password'));
Change a users password.(from unix shell). [mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password 'new-password'
Change a users password.(from MySQL prompt). SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
Switch to mysql db.Give user privilages for a db. INSERT INTO [table name] (Host,Db,User,Selectpriv,Insertpriv,Updatepriv,Deletepriv,Createpriv,Droppriv) VALUES ('%','db','user','Y','Y','Y','Y','Y','N');
To update info already in a table. UPDATE [table name] SET [column name]=[new value] WHERE [column name]=[value]
Delete a row(s) from a table. DELETE from [table name] where [field name] = 'whatever';
Update database permissions/privilages. FLUSH PRIVILEGES;
Delete a column. ALTER TABLE [table name] DROP COLUMN [column name];
Add a new column to db. ALTER TABLE [table name] ADD COLUMN [new column name] varchar (20);
Change column name. ALTER TABLE [table name] CHANGE [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes. ALTER TABLE [table name] ADD UNIQUE ([column name]);
Make a column bigger. ALTER TABLE [table name] MODIFY [column name] VARCHAR(3);
Delete unique from table. ALTER TABLE [table name] DROP INDEX [colmn name];
Load a CSV file into a table. LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
Dump all databases for backup. Backup file is sql commands to recreate all db's. [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
Dump one database for backup. [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
Dump a table from a database. [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (or database table) from backup. [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
Create Table Example 1. CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),
officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups
VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create Table Example 2. CREATE TABLE [table name] (personid INT(50) NOT NULL AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(35), middlename VARCHAR(50), lastname VARCHAR(50) DEFAULT 'bato');

Advantages of MySQL:

  1. MySQL is the widely used open source database. MySQL is the backend database of most of the websites.
  2. As a Free Software(Free as in freedom), MySQL can be downloaded and used by the developer for free.
  3. MySQL is robust and it provides excellent performance due to usage of MyISAM.
  4. MySQL occupies very less disk space.
  5. MySQL can be easily installed in all major operating systems like Microsoft Windows, Linux, UNIX.
  6. MySQL can be easily learnt using the tutorials that are available on internet. We would recommend users to go through the Spoken Tutorial videos given below to get more information on MySQL.
  7. MySQL is best suited for small and medium applications.

Resources

  • Please go through the video tutorials on MySQL developed and released by Spoken Tutorial Project, an initiative of National Mission on Education through ICT, Government of India, to promote IT literacy through Open Source Software. Students can go through these video tutorials to get better understanding of the subject. The tutorials for MySQL can be downloaded from here. More info about the project can be found here.

  • The MySQL Handbook is also available in the mysql-doc-5.0 package. To install the package enter the following in a terminal: sudo apt-get install mysql-doc-5.0

  • The documentation is in HTML format, to view them enter file:///usr/share/doc/mysql-doc-5.0/refman-5.0-en.html-chapter/index.html in your browser's address bar.