Splitly - where Bills don't break Friendship | Overview of a Web App developed by me

Read More

Classification of SQL Queries

SQL (Structured Query Language) is a standard language for managing and manipulating databases. SQL is divided into different languages or subcategories based on the type of operation it performs. Here's a classification of SQL languages along with examples of queries:

1. **Data Query Language (DQL)**:

   - **Purpose**: Used to retrieve data from a database.

   - **Key Statement**: `SELECT`

Example:

   SELECT * FROM students;

   In this example, the `SELECT` statement retrieves all rows from the `students` table.

2. **Data Definition Language (DDL)**:

   - **Purpose**: Defines the structure of the database, such as creating, altering, and dropping tables and other database objects.

   - **Key Statements**: `CREATE`, `ALTER`, `DROP`, `TRUNCATE`

 Example 1: `CREATE TABLE`

   CREATE TABLE students (

      student_id INT PRIMARY KEY,

      student_name VARCHAR(100),

      age INT

   );

   This creates a `students` table with columns for `student_id`, `student_name`, and `age`.

 Example 2: `ALTER TABLE`

   ALTER TABLE students ADD COLUMN gender VARCHAR(10);

   This adds a `gender` column to the `students` table.

Example 3: `DROP TABLE`

   DROP TABLE students;

   This statement deletes the `students` table along with all its data.

---

3. **Data Manipulation Language (DML)**:

   - **Purpose**: Used to manipulate data within the database. It covers inserting, updating, and deleting records.

   - **Key Statements**: `INSERT`, `UPDATE`, `DELETE`

Example 1: `INSERT`

   INSERT INTO students (student_id, student_name, age) 

   VALUES (1, 'John Doe', 20);

   This inserts a new record into the `students` table.

Example 2: `UPDATE`

   UPDATE students 

   SET age = 21 

   WHERE student_id = 1;

   This updates the `age` of the student with `student_id` 1 to 21.

Example 3: `DELETE`

   DELETE FROM students WHERE student_id = 1;

   This deletes the record of the student with `student_id` 1 from the `students` table.

4. **Data Control Language (DCL)**:

   - **Purpose**: Used to control access to data in the database, typically through permission management.

   - **Key Statements**: `GRANT`, `REVOKE`

Example 1: `GRANT`

   GRANT SELECT, INSERT ON students TO 'username';

   This grants the user `username` permission to `SELECT` and `INSERT` records in the `students` table.

Example 2: `REVOKE`

   REVOKE INSERT ON students FROM 'username';

   This revokes the `INSERT` permission from the user `username` on the `students` table.

---

5. **Transaction Control Language (TCL)**:

   - **Purpose**: Used to manage transactions in the database. Transactions allow groups of SQL statements to be executed in a way that ensures consistency and atomicity.

   - **Key Statements**: `COMMIT`, `ROLLBACK`, `SAVEPOINT`

 Example 1: `COMMIT`

   COMMIT;

   This commits the current transaction, making all changes made permanent.

 Example 2: `ROLLBACK`

   ```sql

   ROLLBACK;

   ```

   This rolls back the current transaction, undoing all changes since the last `COMMIT`.

 Example 3: `SAVEPOINT`

   SAVEPOINT save1;

   This creates a savepoint named `save1`, which allows partial rollback to this specific point

Classification of SQL Queries:

Each of these languages plays a critical role in working with relational databases, and depending on the specific use case, you would use different combinations of them to interact with your data effectively.

Read More

Solved: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Follow the given steps to solve the issue. The commands here are for CentOS 8. The solution is same for other OS as well so modify the commands accordingly.

Step 1: After the installation of MYSQL is finished, enable and start mysqld as given below:

sudo systemctl start mysqld

sudo systemctl enable mysqld

Once, done also check the status of the mysqld to verify whether it is running or not:

sudo systemctl status mysqld

Step 2: Find the temporary password and copy it using the following command:

sudo grep 'temporary password' /var/log/mysqld.log

You will see something like this:

 A temporary password is generated for root@localhost: ;ND0#VvB0*Mt

Copy the temporary password.

Step 3: Type the following command:

mysql -u root -p

Step 4: When prompted to enter password, just paste the copied temporary password and yes you are done with your task. 

Step 5: Then you can change the password for root using the command:

sudo mysql_secure_installation

Read More

Steps to install HTTPD, PHP, MYSQL and PHPMYADMIN in CentOS 8

Steps to install httpd in CentOS 8

1. sudo yum install dnf
2. sudo dnf update
3. sudo dnf install httpd
4. sudo systemctl start httpd
5. sudo systemctl enablr httpd
6. sudo systemctl enable httpd
7. sudo firewall-cmd --add-service=http --permanent
8. sudo firewall-cmd --reload
9. sudo systemctl status httpd
10. sudo dnf update

Steps to install PHP in CentOS 8

1. sudo dnf update
2. sudo dnf install epel-release
3. sudo dnf install https://rpms.remirepo.net/enterprise/remi-release-8.rpm
4. sudo dnf module enable php:remi-8.2
5. sudo dnf module reset php
6. sudo dnf module enable php:remi-8.2
7. sudo dnf module list php
8. sudo dnf install php php-cli php-fpm php-mysqlnd php-pdo php-gd php-xml
9. php -v

Steps to install MYSQL in CentOS 8

1. sudo dnf update
2. sudo dnf install https://dev.mysql.com/get/mysql80-community-release-el8-    3.noarch.rpm
3. sudo dnf module enable mysql:8.0
4. sudo dnf update
5. sudo dnf install mysql-server
6. sudo systemctl start php-fpm
7. sudo systemctl enable php-fpm
8. sudo systemctl restart httpd
9. sudo systemctl start mysqld
10. sudo systemctl enable mysqld

Steps to install phpmyadmin in CentOS 8

1. Sudo dnf install epel-release
2. sudo dnf update
3. yum -y update
4. yum -y install phpmyadmin
5. dnf --enablerepo=remi install phpMyAdmin
6. sudo vi /etc/httpd/conf.d/phpMyAdmin.conf

Make sure the phpMyAdmin.conf file looks similar to this:

Alias /phpMyAdmin /usr/share/phpMyAdmin

Alias /phpmyadmin /usr/share/phpMyAdmin

 

<Directory /usr/share/phpMyAdmin/>

   AddDefaultCharset UTF-8

   <IfModule mod_authz_core.c>

      # Apache 2.4

      <RequireAny>

         Require all granted

      </RequireAny>

   </IfModule>

   <IfModule !mod_authz_core.c>

      # Apache 2.2

      Order Deny,Allow

      Deny from All

      Allow from 127.0.0.1

      Allow from ::1

   </IfModule>

</Directory>

 

<Directory /usr/share/phpMyAdmin/setup/>

7. sudo systemctl restart httpd

8. You can verify the phpmyadmin installation by opening URL:

http://your_server_ip/phpMyAdmin

Alternative way to install phpmyadmin on CentOS 8

1. sudo dnf update
2. sudo dnf install tar
3. sudo dnf install wget
4. wget https://files.phpmyadmin.net/phpMyAdmin/5.1.3/phpMyAdmin-5.1.3-all-languages.tar.gz
5. tar xzf phpMyAdmin-5.1.3-all-languages.tar.gz
6. sudo mv phpMyAdmin-5.1.3-all-languages /usr/share/phpMyAdmin
7. sudo mkdir /etc/phpMyAdmin
8. sudo cp /usr/share/phpMyAdmin/config.sample.inc.php /etc/phpMyAdmin/config.inc.php
9. sudo chmod 660 /etc/phpMyAdmin/config.inc.php
10. sudo chown -R apache:apache /etc/phpMyAdmin
11. sudo vi /etc/phpMyAdmin/config.inc.php
12. add to config.inc.php,

                $cfg['blowfish_secret'] = 'a1b2c3d4e5f6g7h8i9j0';

13. sudo vi /etc/httpd/conf.d/phpMyAdmin.conf

Your phpMyAdmin.conf should be similar to given below,

Alias /phpMyAdmin /usr/share/phpMyAdmin

Alias /phpmyadmin /usr/share/phpMyAdmin

 

<Directory /usr/share/phpMyAdmin/>

   AddDefaultCharset UTF-8

   <IfModule mod_authz_core.c>

      # Apache 2.4

      <RequireAny>

         Require all granted

      </RequireAny>

   </IfModule>

   <IfModule !mod_authz_core.c>

      # Apache 2.2

      Order Deny,Allow

      Deny from All

      Allow from 127.0.0.1

      Allow from ::1

   </IfModule>

</Directory>

 

<Directory /usr/share/phpMyAdmin/setup/>

14. sudo systemctl restart httpd

 

Note: "vi" command used above is an editor. I hope you know how to use it.

To insert, press i

To exit without saving, press esc then :q! and then enter

To exit along with saving, press esc then :wq! then enter

Read More

Medical store management system with visualization of data (using visual C#.NET and MYSQL)

Send your query regarding this project through email.

Read More

How to restore/import dump file in MySQL workbench ?

The following steps can be applied to import dump file :

1. Open MySQL workbench and connect to database using your password. You can see the window as given below:

2. You will be provided two ways to import the dump files.
First, you can import the dump files by selecting the option "import from dump project folder". Here you can select the folder where all your dump files are stored. It will automatically import the files in database from which it was created if the database with the same name exists. So, it is better to create database with the same name and then start the importing process.
Second, you can select the option "import from self-contained file" which will allow you to add dump files one by one. In this option before starting import process, choose the default target schema as your database in which you want to import the dump files, from the drop down list.
 Then click on "start import" button. This will import your single dump files in the selected database. Similarly, you can repeat the process to import other files as well.

3. You are done with it. If you don't see the imported table in your selected schema then close and re-open the MySQL workbench.

Read More

How to backup data in MySQL workbench ?

The following steps can be applied to backup data in MySQL using workbench :

1. Open workbench and connect to your database using your username and password.
2. Click on "Data Export" option under "Management" at the left side of the screen.

3. You will find a list of schema from where you can select which of the schema's table do you want to backup. If you want to backup all the tables of particular table then simply click on that schema but if you want to backup only few tables of a particular schema then double click on that schema and you can see the list of tables present in the selected schema on the right side of the screen where you can unselect the table you don't want to backup.

4. Select the folder where you want the backup file to be created.

5. Finally, click on the button "start export" and wait for the operations to complete.

6. You are done. Check your specified folder to get the backup file. You can again import/restore the data whenever needed.

The above process will backup each tables separately. If you desire to backup all the tables in a single file then after step 3 in above process, click on "Export to self-contained file" radio button and also tick the option "create dump in a single transaction" and select the folder where you want to save the file. Then click on export button.

Read More