USING MySQL SERVER!
Starting MySQL Client:
The standard tool for interacting with MySQL is the mysql client program. To get started, issue the following command at your prompt:
mysql -u root -p
You will be prompted to enter the root MySQL user's password. Enter the password you assigned when you installed MySQL, and you'll be presented with the MySQL monitor display:
Welcome to the MySQL monitor.
Commands end with ; or \g. Your MySQL connection id is 41 Server version: 5.1.37-1ubuntu5 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
To access a MySQL server which is running on a remote computer, use the following command:
$ mysql -h 192.168.1.8 -u root -p
In this command, use your host IP Address instead of 192.168.1.8 for remote login.
Using MySQL Client through Commands:
Let's create a database and assign a user to it. Issue the following commands at the MySQL prompt:CREATE DATABASE testdb;
CREATE USER 'testuser'@localhost IDENTIFIED BY 'CHANGEME';
GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@localhost; exit
Now let's log back into the MySQL client as testuser.
mysql -u testuser -p
Once connected, select the database using the use command, as follows:
mysql>use mydatabase
Once executed, all queries not explicitly specifying a database name will be directed towards the hypothetical mydatabase database.
Now create a sample table called "customers." Issue the following commands:
USE testdb;
CREATE TABLE customers (customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name TEXT, last_name TEXT);
This creates a table with a customer ID field of the type INT for integer (auto-incremented for new records, used as the primary key), as well as two fields for storing the customer's name. Of course, you'd probably want to store much more information than this on a customer, but it's a good example of a common case.
Working with MySQL Databases
A database in MySQL is implemented as a directory containing files that correspond to tables in the database. Because there are no tables in a database when it is initially created, theCREATE DATABASE statement creates
only a directory under the MySQL data directory and the
db.opt file.If you manually create a directory under the data directory (for example, with mkdir), the server considers it a database directory and it shows up in the output of
SHOW DATABASES.Before you can interact with your MySQL database, you need to create it! You can create a database by executing the following SQL code:
create database DBname; Use the
SHOW statement to find out
what databases currently exist on the server:
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
| tmp |
+----------+
The mysql database describes user access
privileges. The test database often is
available as a workspace for users to try things out.The list of databases displayed by the statement may be different on your machine;
SHOW DATABASES
does not show databases that you have no privileges for if you do
not have the SHOW DATABASES
privilege.Your database needs to be created only once, but you must select it for use each time you begin a mysql session. You can do this by issuing a
USE statement as shown in the
example. Alternatively, you can select the database on the
command line when you invoke mysql. Just
specify its name after any connection parameters that you might
need to provide. For example:shell>
mysql -h host -u user -p menagerieEnter password:
********Experiment with MySQL using the link given here. Click here for learning Advanced MySQL Queries...
Exercise 1: Id Name Qty Price 1 apple 10 1 2 pear 5 2 3 banana 10 1.5 6 lemon 100 0.1 5 orange 50 0.2
Write Queries for the following questions:- Question 1
- List all the items sorted alphabetically. Then list only the first 3. Then the last 3.
Answer:
select * from store order by 2;
+----+--------+-----+-------+
| Id | Name | Qty | Price |
+----+--------+-----+-------+
| 1 | apple | 10 | 1 |
| 3 | banana | 10 | 1.5 |
| 6 | lemon | 100 | 0.1 |
| 5 | orange | 50 | 0.2 |
| 2 | pear | 5 | 2 |
+----+--------+-----+-------+
select * from store order by id desc limit 3;
+----+--------+-----+-------+
| Id | Name | Qty | Price |
+----+--------+-----+-------+
| 1 | apple | 10 | 1 |
| 2 | pear | 5 | 2 |
| 3 | banana | 10 | 1.5 |
+----+--------+-----+-------+
select * from store order by id desc limit 3;
+----+--------+-----+-------+
| Id | Name | Qty | Price |
+----+--------+-----+-------+
| 6 | lemon | 100 | 0.1 |
| 5 | orange | 50 | 0.2 |
| 3 | banana | 10 | 1.5 |
+----+--------+-----+-------+ - Question 2
- list only the items that are more than $1 per unit price
Answer:
select * from store where Price >1;
+----+--------+-----+-------+
| Id | Name | Qty | Price |
+----+--------+-----+-------+
| 2 | pear | 5 | 2 |
| 3 | banana | 10 | 1.5 |
+----+--------+-----+-------+ - Question 3
- list all the items with their extended price (quantity * price)
Answer:
select *, (Qty*Price) as Total from store;
+----+--------+-----+-------+--------------------+
| Id | Name | Qty | Price | Total |
+----+--------+-----+-------+--------------------+
| 1 | apple | 10 | 1 | 10 |
| 2 | pear | 5 | 2 | 10 |
| 3 | banana | 10 | 1.5 | 15 |
| 6 | lemon | 100 | 0.1 | 10.000000149011612 |
| 5 | orange | 50 | 0.2 | 10.000000149011612 |
+----+--------+-----+-------+--------------------+ - Question 4
- list the total cost of all the items in the store
Answer:
select SUM((Qty*Price)) as Total from store;
+--------------------+
| Total |
+--------------------+
| 55.000000298023224 |
+--------------------+ - Question 5
- how many different items do we have in the store?
Answer:
select count(*) as Item_Count from store;
+------------+
| Item_Count |
+------------+
| 5 |
+------------+
Exercise 2:
Consider the following tables for manging Department and Courses details. We have a database with 3 tables:
Courses Id Name deptId 1 111 1 2 112 1 3 250 1 4 231 1 5 111 2 6 250 3 7 111 4
Dept Id Name 1 CSC 2 MTH 3 EGR 4 CHM
Write Queries for the following questions USING THE MySQL Server available @ 192.182.172.146:- Question 1
- List all the CSC Students.
Answer:
Select Course.Name from Dept, Course where Course.deptId = Dept.Id AND Dept.Name LIKE 'CSC';
- Question 2
- How many different departments are there?
Answer:
Select distinct Name from Dept;
- Question 3
- List the name of the department and students so that they are output as
"CSE 111 112 250", "MTH 111", etc... (in other words, concatenate all the students belong to the same department. The result is expected as follows:
+-----------+-----------------+
| Dept Name | Students |
+-----------+-----------------+
| CSC | 111,112,250,231 |
| MTH | 111 |
| EGR | 250 |
| CHM | 111 |
+-----------+-----------------+
Answer:
select Dept.Name as 'Dept Name', GROUP_CONCAT(Course.Name) AS Students FROM Dept, Course WHERE Course.deptId = Dept.Id GROUP BY Dept.Id;
