How To Run Basic Queries of MySQL / MariaDB on Fedora 20 For Newbies !!

Following Article Describes How To Perform Most Basic MYSQL / MariaDB Queries on Fedora 20? or How to Run MySQL on Fedora 20 ? Which is Latest Version of Linux Based Operating System Project. Last Operation of This Assignment also describes How to change MySQL 'Root' User password on Linux.

It is also a part of 3rd Year, 5th sem Computer Engineering Academic Curriculum of Pune University. As PL 1 Subject. Group A, Assignment 1. It's Problem Statement And Solution is Given Below. You may also Checkout Database Management System complete syllabus & Tutorial of 5th sem subject called Database Management systems application.

Problem Statement :

DBMS using connections(Client-Data server, two tier) Oracle/MySQL (ODBC/JDBC), SQL prompt to create data base tables insert, update data values, delete table, use table, select queries with/without where clause. ,demonstrate use of stored procedure / function (create procedure at the data side and make use of it on the client side).

================== MySQL Queries ================

[hnpandya@localhost ~]$ su //To Grant Super User Access.

Password: 
[root@localhost hnpandya]# service mysqld start //To Start MYSQL Service.

Redirecting to /bin/systemctl start  mysqld.service

[root@localhost hnpandya]# mysql -u root -p  //To Open MYSQL Prompt.

Enter password: 

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 4

Server version: 5.5.33a-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;         //To Show All Available Databases.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> create database student;      //To Create New Database

Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use student; //To Change Database, (By default in test.)

Database changed

MariaDB [student]> create table stud (Rollno int, Name varchar(20), Branch varchar(20), Percent int);                            // To create Table In Database.

Query OK, 0 rows affected (0.01 sec)

MariaDB [student]> insert into stud (Rollno,Name,Branch,Percent) values (1,'Hardik','Computer',75);               // To Insert Data To Table.

Query OK, 1 row affected (0.01 sec)

MariaDB [student]> insert into stud (Rollno,Name,Branch,Percent) values (2,'Pandya','Computer',85);

Query OK, 1 row affected (0.00 sec)

MariaDB [student]> insert into stud (Rollno,Name,Branch,Percent) values (3,'XYZ','Computer',65);

Query OK, 1 row affected (0.00 sec)

MariaDB [student]> insert into stud (Rollno,Name,Branch,Percent) values (4,'ABC','Computer',70);

Query OK, 1 row affected (0.00 sec)

MariaDB [student]> select * from stud; // To Show All content of Table.

+--------+--------+----------+---------+
| Rollno | Name   | Branch   | Percent |
+--------+--------+----------+---------+
|      1 | Hardik | Computer |      75 |
|      2 | Pandya | Computer |      85 |
|      3 | XYZ    | Computer |      65 |
|      4 | ABC    | Computer |      70 |
+--------+--------+----------+---------+
4 rows in set (0.00 sec)


MariaDB [student]> update stud set Branch='IT' where Rollno=3; 

Query OK, 1 row affected (0.03 sec) // To Update Particular Table Record using Where Clause.

Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [student]> select * from stud;

+--------+--------+----------+---------+
| Rollno | Name   | Branch   | Percent |
+--------+--------+----------+---------+
|      1 | Hardik | Computer |      75 |
|      2 | Pandya | Computer |      85 |
|      3 | XYZ    | IT       |      65 |
|      4 | ABC    | Computer |      70 |
+--------+--------+----------+---------+
4 rows in set (0.00 sec)

MariaDB [student]> delete from stud where Rollno=4;  // To Delete Particular Record From Table.

Query OK, 1 row affected (0.01 sec)

MariaDB [student]> select * from stud;

+--------+--------+----------+---------+
| Rollno | Name   | Branch   | Percent |
+--------+--------+----------+---------+
|      1 | Hardik | Computer |      75 |
|      2 | Pandya | Computer |      85 |
|      3 | XYZ    | IT       |      65 |
+--------+--------+----------+---------+
3 rows in set (0.00 sec)
MariaDB [student]> grant all on*.* to 'userall'@'%' identified by '';

Query OK, 0 rows affected (0.01 sec)  //To give access to all(%) clients through server

MariaDB [student]> select user, password ,host from mysql.user;     //To display whether user created 

+---------+----------+-----------------------+
| user    | password | host                  |
+---------+----------+-----------------------+
| root    |          | localhost             |
| root    |          | localhost.localdomain |
| root    |          | 127.0.0.1             |
| root    |          | ::1                   |
|         |          | localhost             |
|         |          | localhost.localdomain |
| userall |          | %                     |
+---------+----------+-----------------------+
7 rows in set (0.00 sec)

MariaDB [student]> update mysql.user set password='sql' where user='root';

Query OK, 4 rows affected (0.00 sec)   // To Change MySQL User Access Password.

Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [student]> select user, password ,host from mysql.user;

//To display whether User Created.

+---------+----------+-----------------------+
| user    | password | host                  |
+---------+----------+-----------------------+
| root    |   sql    | localhost             |
| root    |   sql    | localhost.localdomain |
| root    |   sql    | 127.0.0.1             |
| root    |   sql    | ::1                   |
|         |          | localhost             |
|         |          | localhost.localdomain |
| userall |          | %                     |
+---------+----------+-----------------------+
7 rows in set (0.00 sec)

MariaDB [student]> update mysql.user set password='' where user='root';

Query OK, 4 rows affected (0.01 sec)   // To Reset NULL MySQL User Access Password.

Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [student]> select user, password ,host from mysql.user;

+---------+----------+-----------------------+
| user    | password | host                  |
+---------+----------+-----------------------+
| root    |          | localhost             |
| root    |          | localhost.localdomain |
| root    |          | 127.0.0.1             |
| root    |          | ::1                   |
|         |          | localhost             |
|         |          | localhost.localdomain |
| userall |          | %                     |
+---------+----------+-----------------------+
7 rows in set (0.00 sec)


========================================================