Aqsa Yasin – Linux Hint https://linuxhint.com Exploring and Master Linux Ecosystem Wed, 10 Mar 2021 03:38:04 +0000 en-US hourly 1 https://wordpress.org/?v=5.6.2 Using MySQL Unique Constraints https://linuxhint.com/using-mysql-unique-constraints/ Fri, 05 Mar 2021 11:05:13 +0000 https://linuxhint.com/?p=92926 MySQL CONSTRAINT is being cast off to describe the rules that permit or restrict the values in fields that may hold or limit the data that can be entered into the rows. The aim of imposing restrictions is to maintain a database’s credibility. One of them is the UNIQUE constraint.

The UNIQUE restriction guarantees that almost all elements in a field are distinct from each other. For a field or group of fields, the UNIQUE and PRIMARY KEY limits mutually have an assurance of individuality. We may, nevertheless, have several UNIQUE restraints in each table, though one PRIMARY KEY limit for each table instead. Let’s learn it by trying some examples.

Unique Constraint via Workbench:

First of all, we have to learn about how to add Unique constraints to the table while using MySQL Workbench 8.0. Open your newly installed MySQL Workbench 8.0 and connect it to the database.

In the query area, you have to write the below command to create a table ‘Person’. This table has 4 columns with one primary key. We have to specify one unique column. As you can see, we have cleared the column ‘Id’ as the ‘UNIQUE’ column:

>> CREATE TABLE Person (ID int PRIMARY KEY NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, UNIQUE(ID));

Now the table “Person” has been created with its “UNIQUE” column “ID”. You can find the table under the “Navigator” and “Schemas” while listed in the “Tables” option.

While inserting the records, when you tap on the “Apply” button, it will review the inserted records as shown below. You can see we have one duplicated record at lines 3, and 4 that has the same “ID”. Tap the “Apply” button to apply changes.

In the below image, it’s generating an error that the column “ID” has duplicated, which is the value “13”.

After correcting the record, when you apply the changes, it will work correctly.

Unique Constraint via Command-Line Shell:

In the MySQL command-line shell, we will be adding UNIQUE keys to one or several columns. Open your command-line first to have a glimpse of each in the below examples. Type your password to use the command shell.

Example 01: On Single Column

Within this syntax, as in column specification, we use the UNIQUE term that you’d like to apply the uniqueness law. Once we insert or alter a value that creates duplicates in the particular column, the modification will be refused by MySQL and an exception will be given as well. There will be a column restriction in this Particular limit. And you could use that to implement one field’s unique rule too. Here is a syntax for a single-column UNIQUE key:

>> CREATE TABLE table_name( col datatype UNIQUE, col datatype);

Let’s create a table “supplier” in the database “data” with three columns in it. The column “ID” is defined as “UNIQUE”.

>> CREATE TABLE data.supplier( id INT AUTO_INCREMENT NOT NULL UNIQUE, Name VARCHAR(50) NOT NULL, Area VARCHAR(50));

On checking, you can see the table has no records yet.

>> SELECT * FROM data.supplier;

Let’s insert the records into the table. The first record will be inserted into the table smoothly, as shown below.

The second record will be inserted smoothly again since it has no duplicate values at the column “ID”. On the other hand, it is taking the same value as it uses in the first query at column “Area”.

On inserting the third record, we have provided the duplicate value “1” as we have provided in the first insert statement. It will generate an error that the “ID” column is getting a duplicate value, as shown in the image below.

While checking again, you can see that the table has only the record of the first two insert statements. While there is no record from the third insert statement.

>> SELECT * FROM data.supplier;

Example 02: On Multiple Columns

With this format, after the UNIQUE term, we apply a comma-separated set of columns in brackets. The composition of values in field col1 and col2 is used by MySQL to determine the uniqueness.

>> CREATE TABLE table_name( col1 datatype, col2 datatype, UNIQUE(col1,col2));

We have been creating a table “minister” in the database “data” with five columns. The column “ID” is defined as “UNIQUE” and “PRIMARY”. The keyword “CONSTRAINT” is used to name a unique key constraint as “uc_add_sal”. The “UNIQUE” keyword is used to define a UNIQUE constraint on the columns specified in the brackets, e.g., Address and “Salary”. Now we have a total of three columns having “UNIQUE” constraint on them.

>> CREATE TABLE data.minister( Mid INT AUTO_INCREMENT PRIMARY KEY NOT NULL UNIQUE, Name VARCHAR(50) NOT NULL, Address VARCHAR(50), Job VARCHAR(50), Salary VARCHAR(50), CONSTRAINT uc_add_sal UNIQUE (Address, Salary));

On checking the table, you can see the table is empty right now.

>> SELECT * FROM data.minister;

Let’s insert some records into it. The first record will be added to the table successfully because it’s the first line and there is no row to be matched with.

Enter another unique record without any duplicate values at any column, as shown below.

It doesn’t affect when we enter the duplicate values for the columns that have no “UNIQUE” constraint on them. Have a look at the below query. It has a duplicate value at the column “Name” and “Job”. It works properly because these two columns have no “UNIQUE” constraint defined on them.

On the other hand, when we insert the duplicate value, e.g., “13” and “Rawalpindi”, it will generate an error, as shown below. This is because “13” and “Rawalpindi” have been specified earlier.

On checking, we have only three records in the table, inserted by the first three queries.

>> SELECT * FROM data.minister;

Conclusion:

We have gracefully done all the examples of defining UNIQUE constraints on the single and multiple columns while using the MySQL Workbench 8.0 and MySQL command-line client shell. Hopefully, you will get no problems while solving problems relating to UNIQUE keys.

]]>
MySQL If-Then Statements https://linuxhint.com/mysql-if-then-statements/ Thu, 04 Mar 2021 04:59:21 +0000 https://linuxhint.com/?p=92788 The ‘IF’ declaration is often used in preserved programs in MySQL that enforce the simple conditional construct. The IF-THEN statement allows a series of SQL statements to be performed based on a condition specified. It yields True, False, or NULL as one of the three values. Throughout this guide, we will understand how to process a piece of SQL command regarding a given condition by using the MySQL IF-then statement.

Syntax:

>> IF condition THEN statements END IF;

In the following syntax:

  • IF: It’s the keyword clause for the condition to be started.
  • Condition: After the ‘IF’ clause, It’s the constraint to be fulfilled.
  • Statements: Can be any piece of code, e.g., select, retrieve, update, delete. If a condition is evaluated as TRUE, then the statements after the ‘THEN’ clause will be executed.
  • ENDIF: It’s the end of the ‘IF’ clause. After it, the power is moved to the next clause.

Let’s start understanding If-then by launching the MySQL command shell. By typing the password, we are ready to go.

Example 01: MySQL IF() Function:

To gain knowledge about the If statement, we have first to test the IF() function. In the below example, we have defined the IF() function in the SELECT query and give it a condition to check whether 2 is greater than 9 or not. If the condition is valid, it will return the first value after the condition; otherwise, second. As our condition is not valid, that’s why it returns ‘false’.

Let’s assume a table ‘order’ as shown in the appended image.

>> SELECT * FROM data.order;

Let’s have a look at the IF() function performed on this table. We have been selecting the three columns. If the column ‘Status’ has the value ‘Paid’ then the IF() method will return ‘Excellent’ otherwise ‘Bad’. The IF() function’s returned value will be saved into a newly run-time-created column ‘Remarks’. Now we can see the output as appended below.

Example 02: MySQL IF-THEN Statement

We have tried an IF() function on the MySQL command line. Let’s try a new example of the IF-Then statement in a MySQL GUI while using procedures. Open the MySQL Workbench 8.0 and connect to the Database first.

We have been working on the database ‘data’; then, you have to state the ‘use data’ command to use it in Workbench. Press the flash icon to execute it. You have to know that the If-then statements work with store procedures, as shown below. We have declared a keyword delimiter to start the store procedure. The procedure ‘myResult’ has been taking two arguments. After the BEGIN statement, we have an IF statement that is checking the condition. If the condition satisfies, then the ‘THEN’ command and its following statement will be executed. If the condition gets false, then the statements after ‘END IF’ will be implemented.

As the procedure ‘myResult’ has been taking two arguments, we have to pass two values into it.

After passing values to the store procedure, we have to Call the procedure to see the results of the If-then statement.

And the result is given below. It has calculated the discount_rate via the If-then statement.

If you want to use the same stored procedure again, you have to drop this procedure first using the DROP command below and then execute it again.

Example 03: MySQL IF-THEN-ELSE Statement

Let’s go to some broad level. We will be looking at the IF-Then-Else statement this time by using the stored procedure in our example. Have a look at the below table, ‘student’ with some fields in it.

>> SELECT * FROM data.student;

First of all, you have to use the database ‘data’ to use the table ‘student’ in our store procedure. For that, type the below command in your MySQL command-line terminal.

>> use data;

Now declare a delimiter, and then start writing the store procedure. The ‘CREATE’ command will be used to declare or create a procedure as always. The procedure ‘details’ has been taking two arguments. After that, the store procedure starts with the ‘BEGIN’ keyword. The word ‘DECLARE’ has been used to define a variable ‘Sub’ for subjects. The ‘SELECT’ query has been used to select the values of column ‘Subject’ from the table ‘student’ and save it into the newly declared variable ‘Sub’. The user-provided value ‘S_Subject’ will be compared to the column ‘Subject’ value. In the ‘IF’ statement, If the user-provided value ‘S_Subject’ matches with the column ‘Subject’ value, then the relative ‘THEN’ statement will be executed along with its statements within. This procedure will be processed from the first ‘IF’ statement to the second, then the third ‘ELSEIF’ statement. If the last ‘ELSEIF’ part doesn’t match with the value provided by a user, then control will be given to the ‘END IF’ statement.

Let’s end the delimiter with the below command.

We have to call the store procedure by executing it with a ‘CALL’ query and provided arguments in the parenthesis. As we have given ‘Maths’ as its value, then a new column will be generated to output the ‘THEN’ and ‘SET’ statement.

Let’s check the newly created column ‘S_Cource’ by using the below command. You can see we have a relevant result according to the course ‘Maths’.

Call the procedure again by a change at the first argument, i.e., subject. We have provided the subject ‘Computer’ this time.

While selecting the value of the ‘S_Cource’ column, you can see we have a value relating to the subject ‘Computer’, which is ‘IT’.

Once more, we have been calling the procedure as below by a modification at the first argument. We have delivered the subject ‘History’ this time.

By calling the column ‘S_Cource’ again, you can see how we have a result ‘Masters in History’ regarding the recently provided subject, e.g., History.

Conclusion:

We have done will all the examples required for If-then statements elaboration, e.g., IF() function, If-then simple statement, If-then-else statement.

]]>
MySQL Transactions https://linuxhint.com/mysql-transactions/ Thu, 04 Mar 2021 04:54:53 +0000 https://linuxhint.com/?p=92807 A transaction is a simultaneous collection of functions for manipulating datasets and is carried out as though it was a single entity of work. In other terms, a transaction can never be accomplished until each particular process is successful inside the unit. The whole process will collapse if any transaction inside the process crashes. Several SQL queries are clubbed into a unit, and all of them will be executed together as a portion of its transaction. Whenever a transaction incorporates several updates to a database, and the transaction is committed, all the modifications work, or all the updates are reversed whenever the operation is rolled back.

Transaction Properties

Transactions, frequently known by the term ACID, have four main general properties.

  • Atomicity: This guarantees that all tasks inside the work unit become completed successfully; else, just at the failure point, the process is terminated, and prior processes are restored to their old state.
  • Consistency: This means that upon a sufficiently committed process, the database updates state appropriately.
  • Isolation: It helps transactions to work with one another and individually and transparently.
  • Durability: This makes sure that in the event of a system breakdown, the outcome or consequence of a committed transaction retains.

MySQL Transactions Working:

Within MySQL, the two terms, Commit and Rollback are primarily used only for MySQL transactions. The transactions start only with BEGIN WORK declaration and finish either by a COMMIT declaration or a ROLLBACK declaration. The SQL instructions comprise the majority of the transaction amongst both the starting and stopping statements. Such event series is irrespective of the specific programming language utilized. You will make a suitable path in whatever language you are using to build the application. The below SQL statements can be implemented using the mysql query() feature.

  • BEGIN: Start your process or transaction by providing the BEGIN WORK SQL instruction.
  • Add SQL COMMAND: one or even more SQL statements such as SELECT, INSERT, UPDATE, and DELETE, respectively. Confirm even if there’s no mistake and everything is in compliance with your expectations.
  • COMMIT: The COMMIT instruction must be imposed after a successful transaction is done, such that the modifications to all of the associated tables can take full effect.
  • ROLLBACK: If a malfunction occurs, it’s indeed appropriate to send a ROLLBACK instruction to revert each table specified in the transaction towards its former condition.
  • AUTOCOMMIT: By default, MySQL applies the modifications permanently to a database. If AUTOCOMMIT is set to 1 (standard), then each SQL query (whether or not inside a transaction) is assumed to be a completed transaction and committed until it is completed by default. To avoid automatic commit, set AUTOCOMMIT to 0.

Example 01: AutoCommit Mode On:

MySQL operates with the Autocommit phase allowed through default. It ensures that MySQL saves the changes on the disk to create it perpetually as long as we run a query that adjusts (alters) a table. It is not necessary to turn back the move. Let’s try with AUTOCOMMIT on mode. Open your MySQL command-line shell and type your password to begin.

Take an example of a table ‘book’ that has been created in a database ‘data’. Right now, we haven’t performed any query on it yet.

>> SELECT * FROM data.book;

Step 2: This process is to update the table ‘book’. Let’s update the value of column ‘Author’ where the ‘Name’ of a book is ‘Home’. You can see changes have been made successfully.

>> UPDATE data.book SET Author = ‘Cristian Steward’ WHERE Name = ‘Home’;

By having a glimpse of the updated table, we have a modified value of the author where ‘name’ is ‘Home’.

>> SELECT * FROM data.book;

Let’s use the ROLLBACK command to revert the changes by simply adding the below query. You can see that the ROLLBACK query doesn’t work here as it shows that ‘0 rows affected’.

You can even see the table as well. The table has got no changes after the ROLLBACK statement execution so far. This means that the ROLLBACK doesn’t work when we have AUTOCOMMIT on by default.

>> SELECT * FROM data.book;

Example 02: AutoCommit Mode Off:

To revert the changes made, let’s try with AUTOCOMMIT off mode. Using the same example of table ‘book’, we will perform some changes on it. We will be using the START TRANSACTION declaration to deactivate its auto-commit phase or simply type the below command to set AUTOCOMMIT off.

>> SET AUTOCOMMIT = 0;

Let’s say we have the same table ‘book’ in our database, and we have to make changes to it. Then revert those changes to the old ones again.

>> SELECT * FROM data.book;

If you haven’t turned off the AUTOCOMMIT mode, then make a start with a START TRANSACTION query in the command line shell as below.

We will be updating the same table using the UPDATE command by setting the ‘Author’ as ‘Aliana’ where the ‘Name’ of a book is ‘Dream’. Do it by using the below command. You will see that the changes will be made successfully and effectively.

>> UPDATE data.book SET Autho = ‘Aliana’ WHERE Name = ‘Dream’;

Let’s check whether the above query has worked perfectly and made changes to the table or not. You can check the updated table by using the below SELECT command as always.

>> SELECT * FROM data.book;

You can see that the query has worked great, as shown below.

Now, it’s a turn of the ROLLBACK command to perform its function. Try the ROLLBACK command in your command line to roll back the recent update to the table.

Let’s check whether the ROLLBACK query has been worked as it should work or not. For this, you have to check the table ‘book’ again by using the ‘SELECT’ command as always.

>> SELECT * FROM data.book;

You can see from the below output that ROLLBACK has finally worked. It has reverted the changes made by the UPDATE query on this table.

Conclusion:

That’s all for MySQL transactions. I hope this guide will help you to perform MySQL transactions conveniently.

]]>
MySQL Find Duplicate Values in Table https://linuxhint.com/find-duplicate-table-values-mysql/ Tue, 02 Mar 2021 15:51:30 +0000 https://linuxhint.com/?p=92747 Redundant data can be kept in the table by the database program, influencing the database’s output in MySQL. Data replication, though, happens for different purposes, and it is an important job to identify the duplicate values in the table when dealing with a MySQL database. Broadly speaking, it is smart to use clear restrictions on a table often to store information that prevents redundant rows. Sometimes, in a MySQL database, you might like to calculate the number of repeated values. We addressed this question in this topic, in which you will learn about how to locate duplicate values through different ways and how to count duplicate values.

To get started, you must have MySQL installed on your system with its utilities: MySQL workbench and command-line client shell. After that, you should have some data or values in your database tables as duplicates. Let’s explore this with some examples. First of all, open your command-line client shell from your desktop taskbar and type your MySQL password upon asked.

We have found different methods to find duplicated in a table. Have a look at them one by one.

Search Duplicates in a Single Column

First, you must know about the syntax of the query used to check and count duplicates for a single column.

>> SELECT col COUNT(col) FROM table GROUP BY col HAVING COUNT(col) > 1;

Here is the explanation of the above query:

  • Column: Name of the column to be checked.
  • COUNT(): the function used to count many duplicate values.
  • GROUP BY: the clause used to group all rows according to that particular column.

We have created a new table called ‘animals’ in our MySQL database ‘data’ having duplicate values. It has six columns with different values in it, e.g., id, Name, Species, Gender, Age, and Price providing information regarding different pets. Upon calling this table using the SELECT query, we get the below output on our MySQL command-line client shell.

>> SELECT * FROM data.animals;

Now, we will try to find the redundant and repeated values from the above table by using the COUNT and GROUP BY clause in the SELECT query. This query will count the Names of pets which are located less than 3 times in the table. After that, it will display those Names as below.

>> SELECT Name COUNT(Name) FROM data.animals GROUP BY Name HAVING COUNT(Name) < 3;

Using the same query to get different results while changing the COUNT number for Names of pets as shown below.

>> SELECT Name COUNT(Name) FROM data.animals GROUP BY Name HAVING COUNT(Name) > 3;

To get results for a total of 3 duplicate values for Names of pets as shown below.

>> SELECT Name COUNT(Name) FROM data.animals GROUP BY Name HAVING COUNT(Name) = 3;

Search Duplicates in Multiple Columns

The syntax of the query to check or count duplicates for multiple columns is as follows:

>> SELECT col1, COUNT(col1), col2, COUNT(col2) FROM table GROUP BY col1, col2 HAVING COUNT(col1) > 1 AND  COUNT(col2) > 1;

Here is the explanation of the above query:

  • col1, col2: name of the columns to be checked.
  • COUNT(): the function used to count several duplicate values.
  • GROUP BY: the clause used to group all rows according to that specific column.

We have been using the same table called ‘animals’ having duplicate values. We got the below output while utilizing the above query for checking the duplicate values in multiple columns. We have been checking and counting the duplicate values for columns Gender and Price while grouped by the column Price. It will show the pet genders and their prices which are residing in the table as duplicates not more than 5.

>> SELECT Gender, COUNT(Gender), Price, COUNT(Price) FROM data.animals GROUP BY Price HAVING COUNT(Price) < 5 AND  COUNT(Gender) < 5;

Search Duplicates in Single Table Using INNER JOIN

Here is the basic syntax for finding duplicates in a single table:

>> SELECT col1, col2, table.col FROM table INNER JOIN(SELECT col FROM table GROUP BY col HAVING COUNT(col1) > 1) temp ON table.col= temp.col;

Here is the narrative of the overhead query:

  • Col: the name of the column to be checked and selected for duplicates.
  • Temp: keyword to apply inner join on a column.
  • Table: name of the table to be checked.

We have a new table, ‘order2’ with duplicate values in the column OrderNo as shown below.

>> SELECT * FROM data.order2;

We are selecting three columns: Item, Sales, OrderNo to be shown in the output. While the column OrderNo is used to check duplicates. The inner join will select the values or rows having the values of Items more than one in a table. Upon executing, we will get the results below.

>> SELECT Item, Sales, order2.OrderNo FROM data.order2 INNER JOIN(SELECT OrderNo FROM data.order2 GROUP BY OrderNo HAVING COUNT(Item) > 1) temp ON order2.OrderNo= temp.OrderNo;

Search Duplicates in Multiple Tables Using INNER JOIN

Here is the simplified syntax for finding duplicates in multiple tables:

>> SELECT col FROM table1 INNER JOIN table2 ON table1.col = table2.col;

Here is the description of the overhead query:

  • col: name of the columns to be checked and selected.
  • INNER JOIN: the function used to Join two tables.
  • ON: used to join two tables according to provided columns.

We have two tables, ‘ order1’ and ‘order2’, in our database having the ‘OrderNo’ column in both as displayed below.

We will be using the INNER join to combine the duplicates of two tables according to a specified column. The INNER JOIN clause will get all the data from both the tables by joining them, and the ON clause will relate the same name columns from both tables, e.g., OrderNo.

>> SELECT * FROM data.order1 INNER JOIN data.order2 ON order1.OrderNo = order2.OrderNO;

To get the particular columns in an output, try the below command:

>> SELECT Region, Status, Item, Sales FROM data.order1 INNER JOIN data.order2 ON order1.OrderNo = order2.OrderNO;

Conclusion

We could now search for multiple copies in one or several tables of MySQL information and recognize the GROUP BY, COUNT, and INNER JOIN function. Make sure that you have built the tables properly and also that the right columns are chosen.

]]>
MySQL Drop a Column From Existing Table https://linuxhint.com/drop-existing-table-column-mysql/ Tue, 02 Mar 2021 14:51:51 +0000 https://linuxhint.com/?p=92456 MySQL Database Infrastructure is indeed a completely managed database service to build cloud-native apps. There are different cases where we perform different queries or commands to alter the database. Depending on the case, the ALTER expression is often included for the ‘ADD’, ‘Delete/DROP’ and ‘MODIFY’ commands. This tutorial guide will learn precisely how to remove a column from an existing table utilizing the MySQL DROP COLUMN clause.

Syntax

>> ALTER TABLE table_name DROP COLUMN exisiting_column_name;

Let’s examine the above syntax for this query:

  • Table_name: is the title of an existing table you want to modify.
  • existing_column_name: is the name of a column to be deleted.

Note: You can have more than one columns to be deleted. For that, you have to use more than one DROP COlUMN clause in your query.

Drop Column via MySQL Workbench

Make sure you have MySQL installed on your windows system. You have to open the newly installed MySQL workbench from the start button of your desktop. We have to make sure to connect our  MySQL workbench with the database from the main menu of the workbench under the ‘Database’ tab.

Under the workbench’s Navigation bar, we have a list of different databases that we have created already. Within the database ‘data’, we have added a table ‘student’. The table ‘student’ has the following records in it as below.

If you want to drop a column from an existing table ‘student’, you have to sail across in the direction of the Schemas beneath the Navigator. Inside the database ‘data’, we have a list of tables, e.g., student and teacher. We will expand the table ‘student’. While hovering over it, you will discover a representation of the setting icon, as shown below. Hit it off to carry on.

A new window will be opened in the workbench as below. We might see a list of columns and their definitions. To drop a column from the table, you have to select that column, right-click on it and press the ‘Delete Selected’ option.

A new window will be popped up, having a query written on it to drop a column. Hit on Apply button to proceed with the update.

Another below window will be opened. Tap on a Finish button to reflect changes at the table ‘student’.

You can see a column ‘age’ has been removed from the table ‘student’ as we couldn’t find it here.

Try the below query in a workbench query place below the navigator to drop a column from a table. Tap on the flash icon under the navigator bar as highlighted in the image below to reflect the query’s changes.

>> ALTER TABLE data.student DROP COLUMN age;

The new altered table without a column ‘age’ is shown below.

Drop a Column via Command-Line Shell

Make sure you have a command-line client shell utility of MySQL has been installed on your current system. To remove a column from a table while using the command-line, open the MySQL command-line client from the taskbar. Type your MySQL password while asked in the shell to continue working.

Suppose we have a table ‘student’ with some record in it residing in the schema ‘data’. While checking, we have found a given-below record in the table ‘student’. Right now, this table has probably 9 columns in it.

>> SELECT * FROM data.student ORDER BY id;

Example 01: Drop a Single Column

If you are looking for an example to delete a single column from an existing table, then this example is indeed for you. Considering the same above table, let’s delete the column named ‘lastname’ from it. After that, we must have 8 columns left. Try the below query in the MySQL command-line client shell. If the query works properly, it will display a message that the query is ‘OK’.

>> ALTER TABLE data.student DROP COLUMN lastname;

The above image shows that the query works properly, and the column ‘lastname’ has been removed from the table ‘student’. Let us check it and use the same SELECT query to call the table ‘student’.

>> SELECT * FROM data.student ORDER BY id;

The output below shows that we have left with only 8 columns, and the column ‘lastname’ and its values have been deleted from the table ‘student’ successfully.

You can delete columns from the start, last, middle, and from any position of the table.

Example 02: Drop More than One Columns

You’re also able to drop more than one column from any table in MySQL using the ALTER query. You just need to add more than one DROP clause in the ALTER query. Let’s take the same above updated table ‘student’ having 8 columns. We have to delete the two columns, e.g., gender and reg_date, from it. For that, we have to use two DROP Column clauses in our query. Let us execute the below ALTER query followed by the DROP clauses in the MySQL command-line client shell.

>> ALTER TABLE data.student DROP COLUMN gender, DROP COLUMN reg_date;

As you can see from the above query message that the query worked perfectly. Upon checking the table ‘student’, we have got an updated table having 5 columns left in it. The column named ‘gender’ and ‘reg_date’ has been removed from it.

>> SELECT * FROM data.student ORDER BY id;

Point to be noted that we have deleted the columns reg_date and gender from two different locations of a table. This means you can delete any column from any location of a table. It is not necessary to delete columns from the last place of the table.

Conclusion

You have proficiently tried all the inquiries to delete, remove or drop a single column or more than one column from an already defined table in a database while working in MySQL workbench and Command-line client shell. We hope you have got no issues while trying all the above methods.

]]>
MySQL Delete Row or Rows https://linuxhint.com/delete-row-mysql/ Mon, 01 Mar 2021 16:32:51 +0000 https://linuxhint.com/?p=92243 MySQL is a free, open-source management framework for relational databases. To work on it, you have to install it on your system first with all the required utilities, e.g., workbench and command-line client. Open the newly installed MySQL workbench as below. You must have to connect your workbench with the database to start working on it properly. After that, you have to create a new schema to perform different queries on the data.

First of all, you must have some data in your database schema to perform queries on it. Let’s make a table named ‘student’ in the database ‘data’ using a CREATE query in MYSQL Workbench or Command-Line Client. The table ‘student’ has six columns: ‘id’, ‘firstname’, ’lastname’, ‘email’, ‘reg_date’, and ‘class’. We will be adding values to its columns using its grid view as below and click on the ‘Apply’ button to save changes. Now you can perform any update on these records.

Delete via Workbench Interface

A very simple method to delete row/rows from the MySQL table is via the workbench grid view as we have a table ‘student’ with ten records in it. To delete a single row from a table, you have to select the particular row and press the delete-row icon from the grid window as we have selected the 10th row and pressed the highlighted icon below.

After tapping on the delete icon, you can see that the 10th row and its record have been deleted from the table ‘student’. If you want to delete more than one row, you have to select more than one row consecutively.

Delete Single Row via Command-Line

Another simple method to delete a row from the MySQL schema is through the command-line client. Open the MySQL command-line client under the newly installed ‘MySql’ via the ‘window’ button. First of all, check and display all the records of table ‘student’ using the ‘SELECT’ command as below.

>> SELECT * FROM data.student ORDER BY id;

Example 01: Using One Condition in WHERE Clause
Let’s delete a single row using the ‘WHERE’ clause in the ‘DELETE’ query. We are deleting the row where the ‘lastname = Waleed’, which is row number 10 as above. Let’s try it as:

>> DELETE FROM data.student WHERE lastname=’Waleed’;

It has been deleted successfully as it displays that ‘Query OK, 1 row affected’.

On display all the rows of table ‘student’, we can see that the record of the 10th row has been deleted from the table.

Use the same ‘DELETE’ query in the navigator of the workbench to delete a record as shown.

Example 02: Using More than One Condition in WHERE Clause
You can also delete the single row from the table, using more than one condition in the ‘DELETE’ query of MySQL. We are using two conditions in the ‘WHERE’ clause, e.g., ‘lastname = khursheed’ and ‘id > 7’. This query will only delete the row which has an id greater than ‘7’, and its lastname is ‘khursheed’. In our case, it is the 9th row.

>> DELETE FROM data.student WHERE lastname=’khursheed’ AND id > 7;

The 9th row has been deleted successfully as it says that ‘Query OK, 1 row affected.’

On checking, we have only 8 rows left within the table. The 9th row has been wiped away from the table, as shown below.

Example 03: Using LIMIT Condition in WHERE Clause
We can also delete a single row via the ‘LIMIT’ clause in the ‘DELETE’ query. In this query, we have to define a limit as ‘1’ for a single row to be deleted. We have defined a limit value as ‘1’ in the ‘WHERE’ clause of the ‘DELETE’ query. It will only delete the first row from all the records having ‘lastname = Awan’, which is row number 2.

>> DELETE FROM data.student WHERE lastname = ’Awan’ ORDER BY id LIMIT 1;

Use the ‘SELECT’ query to check the updated table.  You can see that the 2nd row is nowhere in the table as displayed below, and we have only 7 rows left.

Delete Multiple Rows via Command-Line

Let us update the table ‘student’ first by adding some records to it so we can delete multiple rows. Let’s display the records of a table where the lastname is ‘Awan’, using the ‘SELECT’ query with the only WHERE clause. This query will display only 4 rows, as we have only 4 records for the column ‘lastname = Awan’.

>> SELECT * FROM data.student WHERE lastname = ‘Awan’;

Example 01: Using LIMIT Condition in WHERE Clause
To delete multiple rows from a table, we can be using the ‘LIMIT’ condition in the ‘WHERE’ clause of the ‘DELETE’ query. We just have to define the ’LIMIT’ other than 1 or any negative number. So, we have been defining ‘LIMIT’ as ‘3’, to delete 3 rows from the table. It will delete the first three rows of the record having the ‘lastname’ as ‘Awan’.

>> DELETE FROM data.student WHERE lastname = ’Awan’ ORDER BY id LIMIT 3;

Display the remaining records of the table using the ‘SELECT’ query. You will see, there is only 1 record left for ‘lastname’ having the value ‘Awan’, and three rows have been deleted.

Example 02: Using More than One Conditions in WHERE Clause
We are using the same above the table and defined two conditions in the ‘WHERE’ clause to delete rows having ‘id’ greater than 2 and less than 9 as follows:

>> DELETE FROM data.student WHERE id > 2 AND id < 9;

We have only 2 rows left in the table while checking the records.

Example 03: Delete All Rows
You can delete all the rows from the table ‘student’ using the below simple query in the command line as:

>> DELETE FROM data.student;

While trying to display the records, you will get an empty set of tables.

Conclusion

We have taken a glimpse of different ways to delete single and multiple rows from a table while working in MySQL via the workbench and command-line client interface.

]]>
MySQL Subqueries https://linuxhint.com/mysql-subqueries/ Sat, 27 Feb 2021 09:36:39 +0000 https://linuxhint.com/?p=92153 A subquery is a SQL query within a greater query that is recursive, or a subquery is considered an internal query. In contrast, an outer query is termed as the query that includes the subquery. A MySQL subquery can be embedded in the queries, including SELECT, INSERT, UPDATE, or DELETE. Furthermore, within another subquery, a subquery may be nestled. The phrase subquery should be closed in brackets wherever it is used. We’ll teach you how and when to use MySQL subquery to compose complicated queries and describe the idea of the associated subquery. Open the command-line shell from your desktop and write your password to start using it. Press Enter and continue.

Subquery within Single Table Records:

Create a table named ‘animals’ in the database ‘data.’ Add the below following record of different animals with different properties as displayed. Fetch this record using the SELECT query as follows:

>> SELECT * FROM data.animals;

Example 01:

Let’s retrieve the limited records of this table using the subqueries. Using the below query, we know that subquery will be executed first, and its output will be used in the main query as input. A subquery is simply fetching the age where the animal price is 2500. The age of an animal whose price is 2500 is 4 in the table. The main query will select all the table records where the age is greater than 4, and the output is given below.

>> SELECT * FROM data.animals WHERE Age > ( SELECT Age FROM data.animals WHERE Price=2500);

Example 02:

Let’s use the same table in different situations. In this example, we will be using some Function instead of WHERE clause in the subquery. We have been taking the average of all the prices given for animals. The average price will be 3189. The main query will select all the records of animals having a price of more than 3189. You will get the below output.

>> SELECT * FROM data.animals WHERE Price > ( SELECT AVG(Price) FROM data.animals);

Example 03:

Let’s use the IN clause in the main SELECT query. First of all, the subquery will fetch prices greater than 2500. After that, the main query will select all the records of table ‘animals’ where the price lies in the subquery result.

>> SELECT * FROM data.animals WHERE Price IN ( SELECT Price FROM data.animals WHERE Price > 2500 );

Example 04:

We have been using the subquery to fetch the name of the animal where the price is 7000. As that animal is a cow, that’s why the name ‘cow’ will be returned to the main query. In the main query, all the records will be retrieved from the table where the animal name is ‘cow.’ As we have only two records for animal ‘cow,’ that’s why we have the below output.

>> SELECT * FROM data.animals WHERE Name = ( SELECT Name FROM data.animals WHERE Price=7000);

Subquery within Multiple Table Records:

Assume the below two tables, ‘ student’ and ‘teacher,’ in your database. Let’s try some examples of subqueries using these two tables.

>> SELECT * FROM data.student;
>> SELECT * FROM data.teacher;

Example 01:

We will fetch data from one table using the subquery and use it as an input for the main query. This means that these two tables can relate in some manner. In the below example, we have been using the subquery to fetch the student’s name from the table ‘student’ where the teacher name is ‘Samina.’ This query will return ‘Samina’ to the main query table ‘teacher.’ The main query will then select all the records related to the teacher name ‘Samina.’ As we have two records for this name, therefore we have got this result.

>> SELECT * FROM data.teacher WHERE TeachName = ( SELECT TeachName FROM data.student WHERE TeachName = ‘Samina’ );

Example 02:

To elaborate the subquery in the case of different tables, try this example. We have a subquery that is fetching the teacher’s name from the table student. The name should have ‘i’ at any position in its value. This means, all the names in the column TeachName having ‘i’ in their value will be selected and returned to the main query. The main query will select all the records from the ‘teacher’ table where the teacher name is in the output returned by the subquery. As subquery returned 4 names of teachers, that’s why we will be having a record of all these names residing in the table ‘teacher.’

>> SELECT * FROM data.teacher WHERE TeachName IN ( SELECT TeachName FROM data.student WHERE TeachName LIKE%i%);

Example 03:

Consider the below two tables, ‘order’ and ‘order1’.

>> SELECT * FROM data.order;
>> SELECT * FROM data.order1;

Let’s try an ANY clause in this example to elaborate subquery. The subquery will select the ‘id’ from the table ‘order1’, where the column ‘Status’ has a value of ‘Unpaid.’ The ‘id’ can be more than 1. This means that more than 1 value would be returned to the main query to get the table ‘order’ results. In this case, any ‘id’ could be used. We have got the below output for this query.

>> SELECT Item, Sales, id FROM data.order WHERE id= ANY ( SELECT id FROM data.order1 WHERE Status= ’Unpaid’ );

Example 04:

Assume you have the below data in the table ‘order1’ before applying any query.

>> SELECT * FROM data.order1;

Let’s apply the query within a query to delete some records from the table ‘order1’. Firstly, the subquery will select the ‘Status’ value from the table ‘order’ where the Item is ‘Book.’ The subquery returns ‘Paid’ as the value. Now the main query will delete the rows from the table ‘order1’ where the ‘Status’ column value is ‘Paid.’

>> DELETE FROM data.order1 WHERE Status= ( SELECT Status FROM data.order WHERE Item = ’Book’ );

Upon checking, we have now the below records remained in the table ‘order1’ after the execution of the query.

>> SELECT * FROM data.order1;

Conclusion:

You have efficiently worked with a lot of subqueries in all the above examples. We hope everything is clear and clean now.

]]>
MySQL Sort Results with ORDER BY Statement https://linuxhint.com/sort-results-order-by-statement-mysql/ Sat, 27 Feb 2021 09:29:46 +0000 https://linuxhint.com/?p=92124 While working with MySQL queries, the results are obtained in the same sequence as the records inserted into the schema utilizing the SELECT command. It’s the standard order for sorting. You would be aiming at how we might arrange our query result. Sorting is re-arranging the outputs of our query in a defined manner. Sorting may be done on one field or more than one field. The ORDER BY statement is being used to arrange the query results in an ascending or descending order in MySQL. The ORDER BY statement organizes data by default in go-up order if ASC or DESC is not specified. The DESC term is being used to organize the data in descending way.

Syntax:

>> SELECT * FROM table_name ORDER BY expression ASC|DESC
>>SELECT expression FROM table_name ORDER BY expression ASC|DESC
>> SELECT expression FROM table_name WHERE condition ORDER BY expression ASC|DESC

Let’s have a glimpse at the explanation of a query.

  • Table_name: Name of a table to get data from
  • Expression: Name of a column to be retrieved or name of a column used to arrange data.
  • ASC: Used to categorize data in ascending order. It is optional.
  • DESC: Used to arrange data in descending order. It is optional
  • WHERE condition: It is an optional constraint to be used.

Get started with opening the MySQL command-line client shell to start working on sorting. It may ask for your MySQL password. Type your password and tap Enter to continue.

Example: Sort without ORDER BY (ASC or DESC) clause:

To elaborate sorting with the ORDER BY clause, we have been starting our first example without using the ORDER BY clause. We have a table ‘teacher’ in the schema ‘data’ of MySQL with some records in it. When you want to fetch the data from this table, you will get it as it is, as it was inserted in the table without performing extra sorting, as presented below.

>> SELECT * FROM data.teacher;

Example: Sort with ORDER BY Column Name without ASC|DESC:

Taking the same table with a little change in the SELECT query. We have specified the name of a column according to which the whole table will get sorted. We have been using the column ‘id’ to sort the table. As we haven’t defined the sort type, e.g., Ascending or descending, that’s why it will be automatically sorted in ascending order of ‘id’.

>> SELECT * FROM data.teacher ORDER BY id;

Let’s sort the same table without using ASC or DESC expression in the SELECT statement while using another column. We will be sorting this table ORDER BY the column ‘subject’. All the data in the column ‘subject’ will get sorted alphabetically first; then, the whole table will be sorted according to it.

>> SELECT * FROM data.teacher ORDER BY subject;

Now. We will be sorting the table ‘teacher’, according to the column ‘qualification’. This query will sort the column ‘qualification’ alphabetically first. After that, all the records get sorted by this column as below.

>> SELECT * FROM data.teacher ORDER BY qualification;

You may also fetch the specified column data from the table with the ORDER BY clause. Let’s display the three-column data from the table ‘teacher’ and sort this data according to the column ‘firstname’. We will be getting three columns sorted record as shown.

>> SELECT firstname, subject, qualification FROM data.teacher ORDER BY firstname;

Example: Sort with ORDER BY Single Column Name with ASC|DESC:

Now, we will be performing the same query with a little change in its syntax. We will specify the sorting type while defining the column name in the query. Let us fetch the record of four columns: firstname, lastname, subject, and qualification from a table ‘teacher’ while sorting this record according to the column ‘firstname’ in ascending order. This means that the column ‘firstname’ will be sorted in ascending order first then all the data regarding it will get sorted.

>> SELECT firstname, lastname, subject, qualification FROM data.teacher ORDER BY firstname ASC;

According to the descending order of column ‘firstname’, Sorting the same record of four columns’ is as follows.

>> SELECT firstname, lastname, subject, qualification FROM data.teacher ORDER BY subject DESC;

Fetching the fully-fledge table ‘teacher’ while using the column ‘firstname’ in ascending order is as follows.

>> SELECT * FROM data.teacher ORDER BY firstname ASC;

Let’s retrieve the whole table by the descending order of column ‘id’ as below.

>> SELECT * FROM data.teacher ORDER BY id DESC;

Example: Sort with ORDER BY Multiple Column Name with ASC|DESC:

Yes! You can easily sort your table with multiple columns. You just need to specify the ORDER BY clause while a comma separates each column name with its sorting type. Let’ take a glimpse of a simple example. We have been selecting four-column data from a table. Firstly, this data will be sorted according to the column ‘id’ in descending order then into descending order by the column ‘firstname’.

>> SELECT id, firstname, subject, qualification FROM data.teacher ORDER BY id DESC, firstname ASC;

Example: Sort with ORDER BY with WHERE clause:

As we know that the WHERE clause is being used for performing some conditions on data. We can easily sort our data while using the WHERE clause and fetch it according to it. We have performed a simple query in which we have fetched all records from the table ‘teacher’ where the ‘id’ is greater than 4 and the ‘qualification’ of a teacher is ‘Mphil’. This query will fetch the records of teachers whom qualification is equaled to MPhil, and their ‘id’ is not less than 5. After that, this data will b sorted in descending order of the ‘ids’ of teachers as shown in the image.

>> SELECT * FROM data.teacher WHERE id > 4 AND qualification = ‘MPhil’ ORDER BY id DESC;

If you want to fetch the specified columns from a table, you can also do that. Let’s do that as below.

>> SELECT id, firstname, subject, qualification FROM data.teacher WHERE id > 2 AND id < 11 AND subject = ‘Math’ ORDER BY qualification DESC;

Conclusion:

We have done almost all the examples to learn the ORDER BY clause for sorting the records. I hope this article will help you to sort results in MySQL.

]]>
MySQL Limit Results Returned With LIMIT https://linuxhint.com/use-mysql-limit-clause/ Sat, 27 Feb 2021 09:25:21 +0000 https://linuxhint.com/?p=92122 You eventually hit the stage where data volume greatly increases when we start to deal with DBMS like MySQL. It is difficult for us to manage and use. MySQL has built-in capabilities that make it easy to handle. In MySQL, the LIMIT clause is being used to cut down the number of rows throughout the result set using the SELECT expression. We will discover how to use the MySQL LIMIT clause in this guide to restrict the number of rows that a query returns.

LIMIT Syntax:

>> SELECT Expression FROM table LIMIT Offset, Rows;

Here is the explanation of the LIMIT query:

  • Expression: Can be a column name or steric ‘*’.
  • Offset: The offset determines the offset to be returned from the first row. If you use the 0 as offset, it will return the row 1 and vice versa.
  • Rows: The total rows to be returned.

Open the command-line shell of MySQL and type the password. Press Enter to continue.

Assume you have a table called ‘teacher’ in your MySQL database, as shown below. If you want to fetch all the records or rows of this table without any limit define, you will do it using the simple SELECT query as follows:

>> SELECT * FROM data.teacher;

Example 01: LIMIT with Row Number Only:

If a user wants to fetch some records while limiting the number of rows, he/she can do it by using the simple LIMIT clause in the SELECT statement. Let’s try an example while utilizing the above table. Suppose you want to display only 6 rows from the above table while displaying the rows following the descending order of a column TeachName. Try the following query:

>> SELECT * FROM data.teacher ORDER BY TeachName DESC LIMIT 6;

Example 02: LIMIT With OFFSET and Row Number:

Let’s try the same table to define Offset alongside the row number. Suppose you need to fetch only 6 records from the table while the record returned must be starting from the 6th row of a table. Try the succeeding query:

>> SELECT * FROM data.teacher DESC LIMIT 6, 6;

Example 03: LIMIT with WHERE Clause:

Assume the table ‘same’ is located in the MySQL database. Fetch the whole table while sorting it in ascending order of column ‘id’ using the SELECT command along with ORDER BY as follows:

>> SELECT * FROM data.same ORDER BY id ASC;

Trying the stated query, we will have random three records of the age between 12 and 34 from any random location of the table. While in the above table, we have more than 6 records of age group between 12 and 34.

>> SELECT * FROM data.same WHERE age >12AND age <34LIMIT 3;

When we use the ORDER BY clause in the query without stating the order type, it will automatically fetch the record in ascending order as below.

>> SELECT * FROM data.same WHERE age >12AND age <34ORDER BY age LIMIT 3;

To fetch a limited record in another sort order, you have to define the sort order. As we are fetching 8-row data using the descending order of column ‘age’.

>> SELECT * FROM data.same WHERE age >12AND age <34ORDER BY age DESC LIMIT 8;

Let’s fetch only 2 rows where the pet name is ‘dog’. On execution, we have only 1 result because the ‘=’ sign searched for the exact pattern, and we have only 1 record of its pattern.

>> SELECT * FROM data.same WHERE pet = ‘dog’ ORDER BY id LIMIT 2;

Example 04: LIMIT with LIKE Clause and It’s Wildcards:

We searched for the pattern ‘dog’ and got only 1 result. While we have more than 1 record of ‘dog’ in the table. Now we will fetch those records using the LIKE clause along with the LIMIT clause. To display the only 5 records of the table where the pet name starts from ‘d’, try the below query. As we have only 2 records for pattern ‘dog’, that’s why we have got only 2.

>> SELECT * FROM data.same WHERE pet LIKE ‘d%ORDER BY id LIMIT 5;

Let’s retrieve only 10 records from the table, where the pet must have ‘r’ at any middle location of its name. As we have horse, parrot, and rabbit in the pets having ‘r’ in their name, that’s why we have got only 4 records from this query.

>> SELECT * FROM data.same WHERE pet LIKE%r%ORDER BY id LIMIT 10;

To get 6 records of the table, where the pet name must have ‘t’ at the end, run the stated query in the SQL command-line shell. Here we have 4 records from this query.

>> SELECT * FROM data.same WHERE pet LIKE%t’ ORDER BY id LIMIT 6;

To get 8 records of the table, where a person’s job must have ‘er’ at the end, run the below query in the shell. Here we have got 6 records from this query.

>> SELECT * FROM data.same WHERE job LIKE%er’ ORDER BY age ASC LIMIT 8;

Let’s change the WHERE clause along with the LIKE statement in the SELECT query. Suppose you want to fetch a 6-row record from the table ‘same’. You have tried a condition to fetch the only records where the ‘fname’ must have ‘a’ at the last of its value, and ‘lname’ must have ‘a’ at any middle location of its value. On the other hand, the data must be sorted following the column ‘age’ descending order. To get these 6 records, run the below-stated query in the SQL command-line shell. We have got 5 records for this condition, and the result is shown below.

>> SELECT * FROM data.same WHERE fname LIKE%a’ AND lname LIKE%a%ORDER BY age DESC LIMIT 5;

Conclusion:

I hope you are fully prepared about the LIMT topic after trying almost all the examples for the LIMIT clause along with its members, e.g., Offset and row number.

]]>
MYSQL Find Matching Records with LIKE https://linuxhint.com/find-matching-records-like-mysql/ Sat, 27 Feb 2021 06:40:06 +0000 https://linuxhint.com/?p=91960 The MySQL LIKE operator tests if a particular character string resembles the pattern mentioned. We will match a portion of the overall data present in a segment that doesn’t need to match precisely. We will cup tie our keyword with the sequence of the information available in columns by using wildcard query in various combinations. MySQL Wildcards are symbols that help match difficult criteria with search results and have been used in combination with a compare operator called LIKE or a contrast operator called NOT LIKE.

MySQL provides these two wildcards for constructing patterns.

  • The percentage ‘%’
  • The underscore ‘_’

Open your newly installed command-line client shell of MySQL and type your MySQL password to work on it.

We have created a new table called ‘teacher’ in our database having different records in it, as shown below.

>> SELECT * FROM data.teacher;

MySQL LIKE with Percentage % Wildcard:

Percentage sign works differently while using at different locations with alphabets. In the first example, the percentage sign has been used at the last location of the pattern to fetch a record of two columns, ‘TeachName’ and ‘subject’, where the subject name starts with ‘C’. Upon trying the below LIKE query, we have got the below result.

>> SELECT TeachName, subject FROM data.teacher WHERE subject LIKE ‘C%;

Use of the percentage sign before the pattern means that the pattern will match the last location of a value. So we have been looking for the records of columns ‘TeachName’ and ‘subject’ where the teacher name containing the alphabet ‘a’ at the last location. We have found the below output.

>> SELECT TeachName, subject FROM data.teacher WHERE TeachName LIKE%a’;

If you want to search the string pattern at the middle of the value, you have to place the percentage sign at both the start and end of the pattern. We have searched for the ‘am’ pattern lies between teachers’ names using the below-stated query.

>> SELECT TeachName, subject FROM data.teacher WHERE TeachName LIKE%am%;

Use the percentage sign in the middle of the pattern to search for a matching value without knowing what comes in the middle of it. We have displayed all the data related to the teacher name starting with ‘S’ and ends with ‘a’.

>> SELECT TeachName, subject FROM data.teacher WHERE TeachName LIKE ‘S%a’;

MySQL LIKE with Underscore ‘_’ Wildcard:

We will be using a new table called ‘record’ for understanding the underscore wildcard operator. The wildcard underscore ‘_’ works as one character when placed at some location; that’s why it couldn’t work for more than two characters as a percentage operator does.

>> SELECT * FROM data.record;

Let’s fetch matching values while placing the underscore at the last of the pattern location. You have to define the exact number of characters in that particular name. Otherwise, your query won’t work. We want to display the records of the ‘Name’ starts with ‘Za’, where the three underscores mean that the last three characters of this name can be anything, and the name should consist of only 5 characters.

>> SELECT * FROM data.record WHERE Name LIKE ‘Za___’;

Place the underscore operator at the start of the pattern to search for the value. This implies that the starting characters of a string value can be anything. After the specified character has been used, the percentage sign means that the string value can be of any length. So, when we execute this query, it will return the values with different lengths.

>> SELECT * FROM data.record WHERE Name LIKE ‘___a%;

In the below query, we have been using the wildcard underscore in the middle of the pattern. This means that the character before the last alphabet can be anything but the last alphabet must be ‘a’. The percentage sign shows that the string can be of any length.

>> SELECT * FROM data.record WHERE Name LIKE%_a’;

We will be using the underscore at the start and any point of the pattern while searching in the column ‘Country’. This shows that the second character of a pattern must be ‘u’.

>> SELECT * FROM data.record WHERE Country LIKE ‘_u_%;

MySQL LIKE with NOT Operator:

MySQL helps you merge the NOT operator with the LIKE operator to identify a string that doesn’t even match a particular sequence. We have been searching for the records of columns: ‘Name’, ‘City’ and ‘Country’, where the Country name must have the ‘i’ alphabet in its string at any of the middle locations. We have got three results for this particular query.

>> SELECT * FROM data.record WHERE Country NOT LIKE%i%;

MySQL LIKE with Escape Characters:

The sequence you would like to match often includes wildcard characters, e.g., %10, 20, etc. In this scenario, we could use the ESCAPE clause to define an escape symbol such that the wildcard symbol is treated as a literal character by MySQL. When you do not specifically mention an escape character, the standard escape operator is backslash ‘\’. Let’s search for the values in the table having ‘_20’ at the end of city names. You have to add ‘\’ as a wildcard escape character before the ‘_20’ because the underscore is itself a wildcard. It shows the data of cities having ‘_20’ at the end of their names. The percentage sign means the start of the name can be of any length and can have any character.

>> SELECT * FROM data.record WHERE CITY LIKE%\_20’;

In the below example, the ‘%’ sign at start and end is used as a wildcard operator as used before. The second last ‘%’ sign is a pattern to be searched, and ‘\’ is an escape character here.

>> SELECT * FROM data.record WHERE CITY LIKE%\%%;

The ‘%’ sign is the wildcard operator, ‘\’ is the escape character and the last ‘/’ sign is a pattern to be searched at the last position of names here.

>> SELECT * FROM data.record WHERE CITY LIKE%\/;

Conclusion:

We have done with Like Clause & Wildcards, which are important instruments that help hunt for information that matches complicated patterns. I hope this guide has helped you reach your actual goal of learning LIKE operators and wildcard operators.

]]>
MYSQL Import Data from CSV File https://linuxhint.com/import-data-csv-file-mysql/ Fri, 26 Feb 2021 18:23:21 +0000 https://linuxhint.com/?p=91878

A CSV or comma-separated value document is a delineated text document that distinguishes values from a comma. Every line is its information record. Each data, parted by commas, comprises one or extra fields. The origin of the title for this document layout is the usage of the comma as a field divider. For sharing information between various programs, such documents are used. For instance, Database and contact administrators also endorse CSV files. The theory is that from one program to a CSV document, you may transfer complex information and afterward import the information in that CSV document to some other program. In this tutorial, we will learn how to import data from a CSV file into MySQL workbench. Let’s get started.

Step 01: Create a CSV File

To create a CSV file, you have to open Microsoft Excel in your system and add some data as we have created a file and added the below record into it. Make sure to create field names easy to make it convenient for yourself. Now, save this file with the ‘.csv’ extension. You can change its file format while saving it to any folder. Go to ‘File,’ and hit on ‘Save As.’ Select the location, name the document and below the name field, select the file format. We have named our file as ‘book.’

Step 02: Connect Database

To import your CSV file data into MySQL, you must first connect your Workbench with the Database. For this purpose, open the newly install MySQL Workbench 8.0 and go to the ‘Database’ option. Hit on the ‘Connect to Database’ option from the listed options.

The below new window will be opened. Select your Database, e.g., ‘Stored Connection.’ After that, set the ‘Parameters’ and then hit the ‘OK’ button to link to the Database. You will be heading to the MySQL Workbench Schema’s Graphical User Interface window.

Step 03: Create Schema

In the Local instance or database window, you will see a list of schemas already created by MySQL. You have to generate a new database or use the already created Database as we have a database ‘data’ as our default database. We will utilize it to import data.

Step 04: Create Table

In the query area of schema’ data,’ create a table ‘book’ using the schema by ‘use’ command. Make sure to use the same names for the file and its fields as used in the CSV file. After that, select the create query to be executed and tap on the flash button. The new table ‘book’ will be created in the database’ data.’

You can see that the table ‘book’ has been created, and you can change its constraints.

The table is currently empty as below.

Step 05: Import CSV File Data into Table

Let’s start importing data from the CSV file into the newly created table. Navigate the Schemas and click on the ‘data’ database. There will be a newly created list of tables under the table option of database’ data.’ Hit on the just created table ‘book’ and right-click on it. Now click on the option, i.e. ‘Table Data Import Wizard’ option.

You will see, the window will be opened as shown. Select the file ‘book’ by adding its location. Tap the ‘Browse’ button to pick the file path.

As you can have a glimpse that we have saved a file ‘book’ into some directory. Click on it, and tap on the ‘Open’ button to continue.

We can view that the file path has been selected. Hit on the ‘Next’ button to proceed.

We have the following window now. If you want to add your CSV file data into an already created table, you have to opt for, ‘Use the existing table’ radio button. On the other hand, if you want to import your CSV file data into the new table, you have to create a new table by choosing the’ Create new table’ by choosing the radio button. In this option, you have to select the database name that is already located in your server and give a new table. If you have selected the ‘Use existing table’ option, you have to checkmark the checkbox ‘Truncate table before import.’ Then, click on the ‘Next’ button to go on to the next step.

Now we are on the ‘Configure Import Settings’ window. In this window, you have to select Encoding, e.g., we have selected ‘utf-8’, which is standard. Make sure the Source and Destination columns have similar names. If not, you can select the destination column names by tapping on the column’s name and checking the list of column names. If there are any extra columns generated, you can unselect them here to avoid the creation of extra fields. If everything is set, then you are good to go. Hit on the ‘Next’ button to get closer to import data from the CSV file.

Now, it has two steps listed to import the file data. Firstly, it will prepare the import, and after that, it will import the data file. We have to just tap on the ‘Next’ button to initiate the import process.

After tapping the ‘Next’ button, it will start importing. It will hardly take 2 minutes to import all the data from the file ‘book.’ If you have too much data in the file, it may take a little longer.

After the import has been completed, hit on the ‘Next’ button again.

Finally, the import process has been completed. Tap on the ‘Finish’ button to end it efficiently.

Step 06: Check the Table

We can now refresh the table ‘book to see if it has been updated or not. See! The CSV file data has been magnificently imported into a table ‘book’ without any error or change.

Conclusion

Finally, we have done all the necessary steps to import data from a CSV file to a MySQL Server Workbench 8.0.

]]>
MySQL Count Matching Records With COUNT https://linuxhint.com/identify-duplicate-values-mysql/ Fri, 26 Feb 2021 18:17:57 +0000 https://linuxhint.com/?p=91861

Data redundancy occurs for a lot of reasons. Several of the complicated duties you should cope with while working with database systems is trying to discover duplicate values. For this purpose, We will be using the COUNT() aggregate method. The COUNT() method returns the sum of rows residing in a specific table. The COUNT() function permits you to sum all rows or only rows matching the condition defined. In this guide, You’ll get to know how to identify duplicate values for one or maybe more MySQL columns using COUNT(). The COUNT() method has the following three types:

  • COUNT(*)
  • COUNT(expression)
  • COUNT(DISTINCT expression)

Make definite that you have MySQL installed on your system. Open the MySQL command-line client shell and enter your password to continue. We will be looking at some examples for counting the matching values using the COUNT() method.

We have a table ‘social’ in our schema ‘data’. Let’s check its record via the following query.

>> SELECT * FROM data.social;

MySQL COUNT(*)

The COUNT(*) method is used to count the number of rows residing in the table or count the number of rows according to the given condition. To check the total number of rows in a table, ‘social’ try the below query. We have a total of 15 rows in the table as per the result.

>> SELECT COUNT(*) FROM data.social;

Take a glimpse of the COUNT(*) method while defining some conditions. We have to fetch the number of rows where the username is the same as ‘Mustafa’. You can see we have only 4 records for this particular name.

>> SELECT COUNT(*) FROM data.social WHERE User = ‘Mustafa’;

To fetch the total sum of rows where users’ website is ‘Instagram’, try the below-stated query. The table ‘social’ has only 4 records for the website ‘Instagram’.

>> SELECT COUNT(*) FROM data.social WHERE Website = ‘Instagram’;

To retrieve the total number of rows where the ‘Age’ is greater than 18 is as follows:

>> SELECT COUNT(*) FROM data.social WHERE Age > 18;

Let’s fetch the data of columns ‘User’ and ‘Website’ from a table, where the User name starts with the alphabet ‘M’. Try the below instruction on the shell.

>> SELECT User, Website FROM data.social WHERE User like ‘M%;

MySQL COUNT(expression)

In MySQL, the COUNT(expression) method is only used when you want to count non-Null values of the column ‘expression’. The ‘expression’ would be the name of any column. Let us take a simple example of it. We have been only counting the non-null values of a column ‘Website’, which is related to the column ‘Age’ having a value that equals ‘25’. See! We have only 4 non-null records for the users having age ‘25’, who are using websites.

>> SELECT COUNT(Website) FROM data.social WHERE Age = 25;

MySQL COUNT(DISTNCT expression)

In MySQL, the COUNT(DISTINCT expression) method is used to sum non-Null values and distinct values of the column ‘expression’. To count a distinct number of non-null values in the column ‘Age’ we have been using the below query. You will find 6 non-null and distinct records of column ‘Age’ from the table ‘social’. This means we have a total of 6 people having different ages.

>> SELECT COUNT(DISTINCT Age) FROM data.social;

MySQL COUNT(IF(expression))

For large emphasis, you should merge COUNT() with flow control functions. For starters, for a portion of the expression being used in the COUNT() method, you might use the IF() function. It may be very useful to do this to provide a fast breakdown of the information inside a database. We will be counting the number of rows with different age conditions and dividing them into three different columns, which can be said as categories. First, COUNT(IF) will be counting the rows having age less than 20 and save this count into a new column named ‘Teenage’. Second COUNT(IF) is counting the rows having ages between 20 and 30 while saving it to a column ‘Young’. Third, the last counts the rows having ages greater than 30 and saved into a column ‘Mature’. We have 5 teenagers, 9 young and only 1 mature person in our record.

 >> SELECT COUNT(IF(Age < 20,1,NULL)) ‘Teenage’, COUNT(IF(Age BETWEEN 20 AND  30,1,NULL)) ‘Young’, COUNT(IF(Age > 30,1,NULL)) ‘Mature’ FROM data.social;

MySQL COUNT(*) with GROUP BY Clause

The GROUP BY statement is a SQL instruction using for group rows with the same values. It returns the total number of values residing in each group. For instance, if you want to check each user’s number separately, you have to define the column ‘User’ with the GROUP BY clause while counting records for each user with COUNT(*).

You can either select more than two columns while performing the counting of rows along with the GROUP BY clause, as follows.

>> SELECT User, Age, Website, COUNT(*) FROM data.social  GROUP BY Website;

If we want to count rows while using the WHERE clause having some conditions in it alongside the GROUP BY and COUNT(*), you can also do that. The below query will fetch and count the records of columns: ‘User’, ‘Website’, and ‘Age’ where the website value is ‘Instagram’ and ‘Snapchat’ only. You can see we have only 1 record for both websites for different users.

>> SELECT User, Website, Age, COUNT(*) FROM data.social  WHERE Website = ‘Instagram’ Or Website = ‘Snapchat’ GROUP BY Website, Age;

MySQL COUNT(*) with GROUP BY and ORDER BY Clause

Let’s try the GROUP BY and ORDER BY clauses jointly with COUNT() method. Let’s fetch and count the rows of table ‘social’ while arranging the data in descending order using this query:

>>  SELECT User, Website, Age, COUNT(*) FROM data.social  GROUP BY Age ORDER BY COUNT(*) DESC;

The below-stated query will first count the rows, then display the only records having COUNT greater than 2 in ascending order.

>> SELECT User, Age, COUNT(*) FROM data.social  GROUP BY Age HAVING COUNT(*) > 2 ORDER BY COUNT(*) ASC;

Conclusion

We have gone through all the possible methods to count the matching or duplicate records using the COUNT() method with different other clauses.

]]>
MySQL Add a Column to Existing Table https://linuxhint.com/add-column-to-existing-table-mysql/ Fri, 26 Feb 2021 18:14:19 +0000 https://linuxhint.com/?p=91835

MySQL Database System is a highly scalable database service for creating cloud-native applications. Therefore we have to perform different operations while working on it. The ALTER TABLE declaration is being cast-off to add, remove, or alter columns while working on an already existing table in any schema of MySQL. We’ll teach you exactly how to declare a column to an existing table utilizing the MySQL ADD COLUMN expression in this guide.

Syntax:

>> ALTER TABLE table_name ADD new_column_name column_definition [FIRST | AFTER column_name ];

Here is the detail of this query:

  • Table_name: is the existing table you wish to amend or add a new column.
  • New_column_name: is the title for a new column to be added.
  • Column_definition: It is the data type of a new column and its definition, e.g., Null, Not Null.
  • FIRST | AFTER column_name: This clause specifies the location of a new column in the table. It is optional; that’s why if not used, the column will be implanted at the last of a table.

Add Column via MySQL Workbench

Open your newly installed MySQL workbench from the start button of your desktop. Make sure to connect your workbench with the database.

In the Navigation bar of the workbench under the schema, you can find the already created databases. We have created a database ‘data’ and added a table ‘student’ into it. The table ‘student’ has the following columns and records in it.

To add a new column in the existing table ‘student’, you have to navigate towards the Schemas under the Navigator. Within the database ‘data’, we have a list of tables, e.g., student and teacher. You have to expand the table, student. While hovering over it, you will find an icon of the setting, as highlighted below. Click on it to continue.

The below window will be opened in MySQL workbench. You can view a list of columns and their definitions. You can add a new column at the last of all the columns by double tapping at the last space and, after that, writing a column name on it.


It is clear from the below image that we have added the new column ‘age’ at the last of all columns, with its definition defined.

You will find a new window having a query listed to add a new column as below.  Click on Apply button to proceed.

The window will be opened. Hit on Finish to see changes.

Now, the restructured table is appended below.

After adding values, it will look like as underneath. You can also add the Alter query in the space above this table and under the navigation bar to add a column in a table.

Add Column via Command-Line Shell

To add a new column in an existing table while using the command-line, you have to open the MySQL command-line client from the taskbar. Enter your MySQL password when asked and press Enter.

Upon checking, we have found the below-given record in the table ‘student’. Let’s add a new column, ‘age’, at the end of the table.

>> SELECT * FROM data.student ORDER BY id;

Example 01: Add Single Column

If you want to add a single column to an existing table, then this example is meant for you. Now, we will add a new column named ‘age’ at the last place of the table, ‘student’. To add a new column ‘age’ after the column ‘class’, try the below query in the MySQL command-line shell.

>> ALTER TABLE data.student ADD age VARCHAR(20) NOT NULL AFTER class;

On checking the table, you will see that the table has created a new empty column ‘age’ at the last place, as shown in the image.

>> SELECT * FROM data.student ORDER BY id;

We will be updating a table while adding values to a newly created column ‘age’. We have tried the below three UPDATE queries to add values into a column ‘age’.

>> UPDATE data.student SET age=25WHERE id > 0 and id < 3;

>> UPDATE data.student SET age=17WHERE id > 3;

>> UPDATE data.student SET age=18WHERE id = 3;

Let’s check the updated table ‘student’ using the below SELECT query in the shell as:

>> SELECT * FROM data.student ORDER BY id;

Now we have a full-fledge newly updated table as given below.

Example 02: Add More than One Columns

You can also add more than one column at different places of an existing table using ALTER query. Let’s try the below query to add two new columns, e.g., gender and city, at the last of all the table ‘student’ columns. We have used two ADD clauses in this query to add two columns.

>> ALTER TABLE data.student ADD COLUMN gender VARCHAR(20) NOT NULL AFTER age, ADD COLUMN city VARCHAR(20) NOT NULL AFTER gender;

You will find the below-updated table upon checking it with the SELECT query in the shell. You will find that the table has created two new columns with no values in them at all.

>> SELECT * FROM data.student ORDER BY id;

To avoid the emptiness of newly created columns, we will be adding values to new columns, e.g., gender and city. We have tried the below three UPDATE queries to add values into the columns’ gender’ and ‘city’. First of all, we have updated the column ‘gender’ using the below query as:

>> UPDATE data.student SET gender = ‘Female’ WHERE id < 6;

After that, we have updated the column ‘city’ by using the below two UPDATE commands:

>> UPDATE data.student SET city = ‘Islamabad’ WHERE id < 3;
>> UPDATE data.student SET city = ‘Rawalpindi’ WHERE id > 2;

Let’s check the updated table ‘student’ using the beneath SELECT query in the command line shell as:

>> SELECT * FROM data.student ORDER BY id;

Finally, we have got a newly restructured table as given below.

Conclusion

Perfect! We have efficiently tried all the queries to add a single column or more than one column in an existing table while working in MySQL workbench and Command-line client shell.

]]>
How to Run the Same Command Multiple Times in Linux https://linuxhint.com/run-same-command-multiple-times-linux/ Tue, 23 Feb 2021 17:31:53 +0000 https://linuxhint.com/?p=91069 When programming, you may encounter a situation in which you need to perform the same task multiple times. A simple solution is to manually repeat the process as many times as it is needed; however, it is not productive to do so. That is why the concept of loops was introduced to programming. The basic goal of a loop is to repeat a task several times, depending upon the value provided for the iterator and the termination condition of the loop. Loops allow programmers to avoid the hassle of repeating processes manually.

Suppose that there is a command that you wish to run multiple times. There are several important reasons that you might need to run a command repeatedly, so you want to be sure that a certain command produces the correct output every time it is executed. The more you run a command manually, the more certainty you will gain each time you run the command.

But how do you do this programmatically? Well, there are several methods that can be used to run the same command multiple times, as well as for verifying the output of the repeated command. This article shows you how to create a repeatable command using Linux Mint 20 as the host operating system.

Methods for Reiterating Commands in Linux Mint 20

The following sections show you two methods that you can use to run the same command multiple times using a Bash script in Linux Mint 20.

Method 1: Reiterating a Command Using the “for” Loop in Bash

The first method of running the same command multiple times using a Bash script in Linux Mint 20 is implemented using the “for” loop. The sample script is shown in the following image. You can copy this script into any Bash file of your choice.

In this script, we have created a simple “for” loop that iterates through a list containing items from 1 to 5, meaning that the “for” loop will have a total of five iterations. In these five iterations, our desired command will be executed with each iteration, or five times.

Here, we specified for the “date” command to run “5” times. This command displays the current system date and time in the terminal. You can use any other command of your choice in place of the “date” command.

We also wanted our output to be displayed after every “1” second. To serve this purpose, we used the “sleep” command with a sleep interval of “1,” though you may increase the sleep interval according to your preferences. You can even increase or decrease the number of iterations of the “for” loop, depending upon the number of times you want to execute the command.

Execute this Bash script with the following command:

$ bash Multiple.sh

The result of the above Bash script is shown in the following image. The output contains the current system date and time at “5” different intervals, which means that our “date” command has been successfully executed for the specified number of iterations.

Method 2: Reiterating a Command Using the “while” Loop in Bash

The second method of reiterating a command multiple times using a Bash script in Linux Mint 20 is implemented using the “while” loop. The sample script is shown in the following image. You can copy this script into any Bash file of your choice.

In this script, the variable “i” is initialized with the value “0.” This variable will act as the iterator for the “while” loop. Then, the iterating condition of the “while” loop is that the value of the variable “i” is less than “5.” Within this loop, we have a “date” command that will serve the same purpose as the command used in Method 1.

This command is followed by the “sleep” command. This command prints the output after some specified interval, which is “1” second, in this case. Finally, we will increment the value of the iterator “i” using the “+1” incrementing notation.

The result of our Bash script is depicted in the following image. This output contains the current system date and time at five different intervals, meaning that the “date” command has been executed successfully for the specified number of iterations. This time, we have achieved this goal by implementing the “while” loop instead of the “for” loop.

Conclusion

Today, you learned that instead of manually running a command multiple times, you can write a Bash script to simplify this task. Using the “for” or “while” loops in a Bash script, you can easily achieve the functionality of running a command multiple times. This article showed you how to reiterate commands using both methods while running the “date” command repetitively. We also used the “sleep” command in both of our example scripts so that it was easier to visualize the effect of the repetitive “date” command since the value of “seconds” will change every time this command is executed.

In the same manner, you can run any command of your choice multiple times using either of the Bash scripts that we shared with you today while operating in Linux Mint 20. I hope this guide will help you to run the same command multiple times in Linux.

]]>
What Is BC in a Bash Script? https://linuxhint.com/what-is-bc-bash-script/ Tue, 23 Feb 2021 17:25:29 +0000 https://linuxhint.com/?p=91052 BC, which stands for Basic Calculator, is a command in Bash that is used to provide the functionality of a scientific calculator within a Bash script. This can be useful for scripting with various arithmentic use cases and scenarios. This article shows you how to use BC in a Bash script.

Examples of using the BC Command in a Bash Script

To learn more about using the BC command in a Bash script in Linux Mint 20, take a look at the examples provided in the following sections:

Example 1: Calculating the Power of a Number

Bash programming only allows us to perform mathematical operations on integers, i.e., you cannot perform calculations with decimals or floating-point numbers in Bash. To calculate the power of a decimal with an integer exponent, we will write the following Bash script:

In this Bash script, a one-liner echo command calculates the second power of “2.5.” The result is piped to the BC command, which will perform this calculation.

After saving this script, we can execute the following command:

$ bash BC.sh

The output of our Bash script results in a decimal number, as shown in the image below. This operation would not have been possible without the BC command.

Example 2: Checking Whether a Number is Less than Another Number

The BC command can also be used to compare if a number is greater or less than another. To draw such a comparison, we will write the following Bash script:

In this Bash script, again, a one-liner echo command is used. The command checks whether one number is less than another number. The result is piped to the BC command, which will perform this comparison. The output of this contrast will be “1” if the given statement is true; otherwise, the output will be “0.”

The output of the above Bash script is “0” since 10 is greater than 5, which makes our statement false. This output can be seen in the image below:

Example 3: Checking Whether a Number Is Equal to Another Number

As in Example 2, the BC command is used again in this example; however, this time, the command will check whether one number is equal to another number. To draw such a comparison, we will write the following Bash script:

In this Bash script, a one-liner echo command checks whether the first number is equal to the other number. The result is piped to the BC command, which will perform this comparison. The output of this script will be “1” if the given statement is true; otherwise, the output will be “0” if the statement is false.

The output of our Bash script is “1” since 2 is equal to 2, which makes our statement true. This output can be seen in the image below:

Example 4: Using the BC Command with the && Logical Operator

The BC command can also be paired up with logical operators in Bash, including && and ||, which correspond to logical multiplication and logical addition, respectively. The outcome of the && logical operator is true, or “1,” when all the provided inputs are non-zero. Otherwise, the result will be false, or “0.” To use the && operator with the BC command in Bash, we will write the following Bash script:

In this Bash script, a simple one-liner echo command performs the logical operation && between the numbers “10” and “0.” The result is piped to the BC command that will perform this operation.

The output of our Bash script is “0” since at least one of our provided values is not non-zero, which makes our operation false. This output can be seen in the image below:

Example 5: Using the BC Command with the || Logical Operator

The result of the || logical operator is true, or “1,” when one of the provided inputs is non-zero. Otherwise, the result will be false, or “0.” To use the || operator with the BC command in Bash, we will write the following Bash script:

In this Bash script, a simple one-liner echo command performs the logical operation || between two numbers, “10” and “0.” The result is piped to the BC command that will perform this operation.

The output of our Bash script is “1” since one of our provided values is non-zero, which makes our operation true. This output can be seen in the image below:

Example 6: Dividing Decimal Numbers with the Result in Decimal

We can also use the BC command to divide decimal numbers and return the result in decimal form, up to a specific decimal place. To obtain this decimal precision, we will write the following Bash script:

In this Bash script, a one-liner echo command divides two numbers, “6.5” and “2.7.” We want the result to be accurate to “3” decimal places, and we have used the “scale” flag for this purpose. The result is piped to the BC command that will perform this calculation.

The output of our Bash script results in a decimal number that is correct up to 3 decimal places, as shown in the image below. This would not have been possible without using the “scale flag” with the BC command. The output can be seen in the image below:

Conclusion

In this article, we provided several examples of using the BC command in Bash script in Linux Mint 20. However, there is still a lot more that you can do with this powerful command that you can explore on your own and add more math and calculations to your bash scripts.

]]>
How to Break from a Bash While Loop? https://linuxhint.com/break-from-a-bash-while-loop/ Mon, 22 Feb 2021 11:49:33 +0000 https://linuxhint.com/?p=90716

Loops are an extremely useful means of performing repetitive tasks not only in Bash scripting but also in all other programming languages. It enables us to write a task (that is supposed to occur multiple times) at once and enclose it within any desired loop so that the said task can be performed repeatedly. Different loops are used in every programming language, i.e., multiple types of loops can be used with every programming language. Amongst all types, the most frequently used loops are the “for” loop and the “while” loop.

A major difference between the execution of the “for” loop and the “while” loop is that in the former one, the incrementing or decrementing variable is specified with the loop whereas, in the latter, that variable is specified after the task that is supposed to be performed repeatedly is stated. The “while” loops appear to be more convenient for the programmers syntactically.

The concept of infinite loops in every programming language is also very common, i.e., a loop that never terminates and its condition assesses to be always “true”. At times, these loops are written accidentally by the programmers, however, there are situations in which such loops are written deliberately. Either way, there can be certain conditions in which we want that infinite loop to break.

Apart from the scenario that we have discussed above, there are times that we purposely create finite loops that we want to run based on a specific condition, wherein we want the normal flow of that loop to break. For both scenarios, there should be a proper mechanism in which we can break a loop whenever a certain specified condition is met.

We can achieve this objective using the “break” statement with our loops regardless of whether they are finite or infinite. Since the “while” loop is one of the most commonly used loops in any programming language, therefore, we will see how we can break from the a “while” loop in Bash in Linux Mint 20 by sharing an example of Bash script with you.

Example Script for Breaking from a Bash While Loop in Linux Mint 20

For demonstrating the usage of the “break” command in Bash, you have to create a Bash file in your Home directory. In our case, we have entitled it as “BreakWhile.sh”. You can also have any other name for this Bash file. Once this file is created, you have to open it up with any text editor and then write down the script shown in the following image:

In this script, we have defined a variable named “number” and initialized it with the value “1”. Then we have a “while” loop whose iterating condition is that the value of the variable “number” should be less than 10, i.e., this loop will keep iterating until the value of the “number” variable is less than 10. Then in the do-done block, we have an “if” statement whose condition is that whenever the value of the “number” variable will be equal to “9”, our “while” loop will break. Otherwise, it will keep running. Then we have simply printed the value of the “number” variable for each iteration of our “while” loop. Finally, we have incremented the value of our “number” variable, i.e., the value of our “number” variable will exceed one after every iteration of our “while” loop. The above script will result in a situation in which the number “9” will never be printed since when the value of our “number” variable will be incremented to “9” our “while” loop will simply terminate without printing anything on the terminal.

To verify this situation, we have to execute the Bash script that we have just created using the command shown below. However, before executing this command, you should ensure that you have saved your Bash script file.

$ bash BreakWhile.sh

The output of this script is shown in the following image. You can easily see that the numbers printed on the terminal are from 1 to 8 and the number “9” is not printed which means that our “while” loop has terminated successfully by using the “break” command.

Conclusion

This article demonstrated a quite simple example of breaking from a “while” loop in Bash in Linux Mint 20. The very same Bash script can be executed in any other Linux distribution of your choice, and it will render the very same results. Also, you can even use this “break” statement with the “for” loop or any other loop in Bash to break its normal flow. This statement is extremely useful especially if you have a certain special case within your program for which you do not want your program to continue its normal execution or you may even want the control of your program to take an altogether different path of execution.

However, an important thing to consider over here is that we only intended to give you a head start with using the “break” statement with the “while” loop in Bash in Linux Mint 20. That is why we have just created a simple Bash script for printing some sample numbers on the terminal, which are less than 10 except for the number “9”. But it does not mean that the break statement is only used with such simple scenarios. You can create even more complex programs to test the effectiveness of the “break” statement with the “while” loop in Bash in Linux Mint 20. Hopefully by going through this tutorial, you can easily create any bash script of your choice using the break statement.

]]>
How do I Prompt for Input in Bash? https://linuxhint.com/prompt-for-input-bash/ Mon, 22 Feb 2021 09:53:15 +0000 https://linuxhint.com/?p=90250

It is often necessary to perform calculations depending on user inputs when creating a Bash script. Throughout this guide, before carrying out a basic operation with a shell script, first, let’s take a glance at interpreting and verifying user inputs. We will be using the integrated Bash read command to interpret the Bash user input. The read command takes some value as input via the user and allocates it to the variable. It read out only a solitary line through the Bash command terminal. In this guide, you would learn how to interpret the user input from the terminal and the script.

To implement the read command, here is the syntax:

read <variable name>

Example 01:

Let’s take a simple example of reading input from the user in bash when prompt. Open a terminal and create a new file “input.sh”.

Open the file and add a little code to it as below. Firstly, the echo statement is asking the user to add input value. The read statement is used to input user value, which will be saved to the variable “NAME”. The last echo statement is used to print the answer with inputted value as “$NAME” variable.

Execute the file using the bash command. Firstly it will prompt for the name. When the user inputs the value “Aqsa”, it will print the message with the value within it.

Example 02:

Open the old file and write the below code into it. We may be using the integrated read command; to a user with a query, using that -p option. You have to summon up to use the “readline” keyword -e to let line editing with arrow keys. After that, use the variable to print the path of a file entered by a user at the command shell.

Let’s check the output of this code using the bash command in the terminal. You will see it will ask for the file path to be entered in the shell. When you enter the required value and press Enter, it will print it out.

$ bash input.sh

If you want to suggest an answer, you have to use the keyword “–i” after the string with the path in inverted commas.

Let’s check the output for updated code using the bash command. You will perceive that it will suggest you a file path as below.

Example 03:

Let’s have another example of prompting for input in a shell. Write down the below code in the file “input.sh”. We have two read commands to add login credentials by a user on the shell. The keyword “-sp” is used to hide the credential “Password” while entering the shell.

On execution, you can see that the user has entered its credentials while the password is hidden while entering. In the end, it has displayed the message to pay thanks to the user.

Example 04:

We have another example to read multiple car names as values from the user’s bash shell. For this purpose, we required three variables as “car1”, “car2”, and “car3”. We have an echo comment that is asking for the names of cars. Read command is used to read inputted values (names of cars) by a user in a shell. The next three echo statements will print the messages with the names of the car consecutively.

Using the bash command, execute the file. It will ask for the names of cars you like. When the user entered the names of cars consecutively, it will save them into variables of a read command and print them out by using the next three echo statements one by one.

Example 05:

In the above example, we have seen how to prompt user input while saving the input into three variables. In this example, we will learn about how to read user inputted values in one variable as array members using the keyword “-a”. So, open the same file and write the below code in it. The echo statement will ask you to enter the input required. The read statement has the keyword “-a” to take multiple values from the user and save them to the one variable array “NAMES”. At the last echo statement, all the inputted values are printed as array members within the string text.

While running the file “input.sh”, the user has been prompted to enter the values. When the user enters the values, these values have been saved into the array variable “NAMES”. After saving these values, the echo statement will be executed and print out the inputted names as array values within itself as shown.

Example 06:

Let’s take the same file with little change in the script. We have written two read commands to take value from a user as input in a shell and saved them in two variables, “NUM1” and “NUM2”. After that, both the variables have been printed out.

In the command terminal, write the Chmod command to assign execution privileges to this file.

$ chmod +x input.sh

When you execute this file using bash, you will see it will prompt you to add numbers consecutively. When you enter the required values, it will print out both numbers separately.

If you want to validate that any field or variable is left blank by the user, you can update the previous code as below. The keyword “-z” is used to check both variables, if they have any blank spaces in them.

While the execution, the user has added space as a number. The user got a message to try again because the variable entered by the user got spaces.

Let’s try the same code with some conditions over the numbers inputted by the user. If the user input’s any character other than those mentioned in the below code, it will pass a message.

While trying this code, a user has added one special character, which generates a prompt message.

Conclusion

In this guide, you have successfully learned how to prompt user input in the shell and interpret the user input using some very simple examples.

]]>
How to Write Bash If/Else Statements in One Line https://linuxhint.com/write-bash-if-else-statements-one-line/ Sun, 21 Feb 2021 19:34:03 +0000 https://linuxhint.com/?p=90692 Bash is a flexible programming language that allows you to write programs just the way you like. Before getting into the article, we would first like to share with you a good programming practice. Whenever you write a program in any programming language, the readability of the code should always be your priority. This is because your code is not only used by yourself but there will be many other programmers who will be using and reading your code. Therefore, your code should be readable enough to be understood by everyone.

Today’s article introduces you to the concept of one-line programming. Bash allows you to write components, such as loops or conditional statements, in one line. You might wonder why we should consider writing these components in one line when we have just explained to you the concept of readability. To understand this, consider the following scenario: you have a program spanning a thousand lines. Such a lengthy code would be difficult to visualize, as well as to debug. In this situation, if your code contains many different loops and conditional statements, then it would improve the readability of the code to write several statements in one line to make your code look more compact.

The following tutorial shows you how to write Bash if/else statements in one line in Linux Mint 20 by providing several examples of these statements.

Methods for Writing Bash If/Else Statements in One Line

To learn more about writing Bash if/else statements in one line in Linux Mint 20, examine the following example Bash scripts:

Example 1: Comparing Two Strings in One Line

In the first example, we will write a Bash script that will compare two strings in one line. To achieve this functionality, write the Bash script shown in the image below in a Bash file:

We will compare two pre-defined strings in the “if” part of the statement, and a message will be displayed if this part is executed. Then, the “else” part of the statement will also display a message if it is executed.

To run this Bash script, we will use the following command:

$ bash OneLiner.sh

Since both strings were equal, the “if” statement will be executed. We will obtain the message shown below upon execution of this script:

Now, we will tweak our Bash script a bit by changing one of the strings, as shown in the following image:

After making this change, when we execute our Bash script again, the “else” statement will be executed, and we will get the message shown in the image below:

Example 2: Comparing a Variable with an Integer in One Line

Now, we will write a Bash script that will compare a variable with an integer in one line. To achieve this functionality, write the Bash script shown in the image below in a Bash file:

We will create a variable named “var” and assign it the value “20.” Then, we will compare the value of this variable with an integer “25” for equality in the “if” part of the statement. A message will be displayed if this part is executed. Then, the “else” part of the statement will also display a message if it is executed.

Since the value of the variable “var” was not equal to “25,” the “else” statement will be executed. We will obtain the message shown below upon execution of this script:

Now, we will tweak our Bash script a bit by changing the value of our variable “var” and setting it to “25,” as shown in the following image:

After making this change, when we execute our Bash script again, the “if” statement will be executed. We will obtain the following message upon execution of this script:

Example 3: Comparing Two Variables in One Line

Finally, we will write a Bash script that will compare two integer variables in one line. To achieve this functionality, write the Bash script shown in the image below in a Bash file:

We have created the two variables “var1” and “var2” and assigned them the values “25” and “20,” respectively. Then, the values of these variables will be compared for equality in the “if” part of the statement, and a message will be displayed if this part is executed. Then, the “else” part of the statement will also display a message if it is executed.

Since the value of “var1” was not equal to the value of “var2,” the “else” statement will be executed. We will obtain the following message upon execution of this script:

Now, we will tweak our Bash script a bit by changing the value of our “var2” to “25” so that the values of both the variables become equal, as shown in the following image:

After making this change, when we execute our Bash script again, the “if” statement will be executed. We will obtain the following message upon execution of this script:

Conclusion

This article provided three different examples and their slight variations for writing if/else statement in Bash in Linux. This tutorial showed you how to use conditional statements in Bash all contained within a single line, making your code look more compact and readable.

]]>
How to Obtain a Bash Substring After a Specified Character https://linuxhint.com/bash-substring-after-character/ Sun, 21 Feb 2021 13:12:03 +0000 https://linuxhint.com/?p=90621 In programming, a string is a series of characters, whether as a precise constant or some sort of variable. The characters contained within a string can be any number, digit, or special character. Keywords can be used to obtain a substring after certain characters, and all keywords work the same. Some examples of keywords include the following:

  • Cut
  • Awk
  • Sed

This article provides several examples to improve your understanding of the concept of obtaining a substring after a certain character.

To follow the examples provided in this tutorial, first, log into your Linux system and open the terminal. Next, create a new file with the name “input.sh.”

Example 1: Using the Cut Keyword

Open the file “input.sh” from the home directory, and write the following code in the file. In our first example, we will define a string named “string” with some value in it having some characters and numbers. We will use the keyword “cut” in this code, followed by “-d,” to obtain the substring of the particular string. After that, we will provide the exact character in inverted commas as “-“ so that the substring will be searched after this special character.

Here, it is important to understand the main idea of substring formation. You must remember how to include the keyword “f” when using the “cut” command. The keyword “f” can be used in various ways to create a substring, so let us look at this particular method.

-f2: text after the first special character “-“ and before the next “-“

This means that it should display the substring “bar” because it is located after the first “-“ character and before the next “-“ character.

After running this bash file, we obtained the substring “bar,” as we expected.

Now, we will check the same example for the “-f2-“ keyword. Update the file, as below.

-f2-: the text following the first special character “-“ regardless of whether there are numerous “-“ characters.

This implies that it will display the substring “bar-123” because it is located after the first “-“ character, regardless of whether any “-“ characters exist.

After executing this bash file, we obtained the substring “bar-123,” as it is located after the first “-“ character.

We will now take the same condition, with little changes to the string and characters. We have defined the new string “str” and assigned it a different value. In this example, “i” is the special character to be searched from the original string, and from this character onward, we will create a substring. In this case, we have used:

-f2: to create a substring following the first special character “i” and before the next character “i.”

This implies that it should display the substring “ltEff=str” because it is located after the first “i“ character.

When the file runs, a substring will be obtained before the next “i” and after the first “i.”

You can try this method with the same line of string, as shown in the image below:

It will display the same result as above.

Now, we will use the “cut” keyword with little change to the “f” condition in a single line. We will use “f1” in this case to change the outcome of the substring. We are using:

-f1: to create a substring before the first special character “i.”

This infers that it should display the substring “GenF” because it is located before the special character “i.”

The output below is as expected.

Here, we are using the same example with little change. We have been using the old method for it.

The result of this script is the same as above.

Next, taking the same previous example, we use the “cut” keyword, while changing the keyword “f.” Here, we will use “f3” to change the outcome of the substring, and we are using:

-f3: to create a substring after the next special character “i.”

This indicates that it should show the substring “ng.-01234” because it is located after the next special character “i.”

We will run the same code using the Bash command. You can see the new result below:

Example 2: Using the Awk Keyword

Open the file “input.sh” and write the appended code in the file. Here, we have declared an echo statement with the string “foo-bar-123” using the “awk” keyword. The print term is followed by the “-F-“ keyword. This will create a substring after the next special character, which is “123,” and print it. There is no need to define the special character in this case.

The following is the output “123” that was mentioned above.

Example 3: Using the Sed Keyword

In this example, we will update the same file with the code provided below. In this case, the keyword “sed” is used instead of “cut” or “awk.”

This code will display a similar output to that of the previous example.

Example 4: Using Special Characters

In the next example, we will update the same file with the code provided below. Here, we will define three strings: “string,” “searchstr,” and “temp.” We have “${string%$searchstr*}” in the code. The “%” will search for the value of the variable “searchstr,” which is “and,” and will remove everything after this special variable from the original string. The remaining text will be saved in the variable “temp.” Now, the variable “temp” will be printed, along with the text “This is a new string.”

If we execute the above code, the original string will be printed first; then, the new substring will be printed.

Taking the same example with a small update, we will use the “#*” string so that everything following the value of “searchstr,” which is “and,” will be inserted into variable “temp.”

When you check it in Bash, you will see that the old string will print first. After that, since “it will be removed” is a new value of variable “temp,” that is why it will be printed at the next line first, along with the text “This is a new string.”

Conclusion

If you want to obtain a substring from any string using some special character in it, you can do so by utilizing the methods provided above.

]]>
How to Create a Bash Function that Returns an Array https://linuxhint.com/create-bash-function-return-array/ Sun, 21 Feb 2021 12:49:46 +0000 https://linuxhint.com/?p=90599 It may appear at first glimpse that returning an array from a Bash function is not realistic. Considering all the benefits, it can be useful to call multiple methods to construct arrays to split up the process of gathering all the appropriate parameters for a YAD call.

There are many reasons why one might want to restore a BASH array:

  • Managing the lifespan of arrays is simpler because they are assigned locally.
  • For just-in-time attainment, obtaining arrays from methods may help.
  • To support log algorithm techniques, the names of methods that return arrays may be cast off.

You may believe that Bash loses the capability to return function arrays. However, that is not exactly correct. It is possible to move the resultant array to a method by reference, taking cues from C/C++ developers. Such a strategy allows the method to continue to be free from references towards a global variable. The following article shows clear instances of this case.

Example 1: Returning an Array

Log into your Linux system and open the command terminal to proceed. We will create a Bash file named “script.sh” using the touch command to return the array using the Bash function. The script is as follows:

$ touch script.sh

You can view the newly created file in the Home directory. Open this file and write down the appended code into it as-is. Here, we are attempting to create an associative array through an argument in a method from either a list pass.

Initially, we have created the function foo().

Inside this program, we have removed the “declare” term, which is a Bash pre-configured command that allows us to change or customize the attributes, the methods of the shell smeared to the variables, and demonstrate the values of these attributes inside the span of our shell command terminal. Moreover, it can be used to define a lengthy variable. Lastly, it is used to define the variables.

We have added the “fooval” value to the “arr” array.

The keyword “–A “is used to create the NAMEs associative array if supported. We must use the list/array as a global variable, which implies that only a method, not a script, can perform this action.

We have also created another array, named “myarr,” for use as a reference. Bash allows the name of a relative variable arr to be dissimilar to the name of the relative variable myarr.

After that, in the eighth line, we have passed the “myarr” array to the Bash foo() function as a reference.

In the past, we used the “for” loop to print both the “arr” and “myarr” arrays to the foo() function.

We will now check the result of this code. Execute the Bash command to run the above code. You can see that the array has been returned to the function and then printed.

$ bash script.sh

Example 2: Returning another Array

Let us look at another example of returning arrays to a function. Open your command terminal and create a new file named “openvpn.log” using the touch command, as follows:

$ touch openvpn.log

Now, open the “openvpn.log” file, and write the following text into this file, as shown. Save this file and close it.

Again, open the command shell and create another file named “script.sh,” using the touch command to add the Bash script to the file.

$ touch script.sh

Next, open the “script.sh” file and append the following code into this file as-is. Save and close this file. This script will use a method that reads values/strings from a document and returns an array:

  • Declaring the array: clients
  • Allocate the returned array of the method to array clients
  • Show array: clients

Let us now have a detailed look at the working of this script.

  • We have declared a global array of “clients” using the “declare” keyword, followed by “-A.”
  • The readArray() function has been defined. In this function, we have declared some local variables. The local variable “array” is empty, but “i” and “j” have been defined with the 0 value to be used as iterators.
  • Using the read mode, we will read the text from the file using iterators to increment the indexes.
  • The line “clients[$((i++))]+=${LINE};” is used to append the text lines to the globally defined “clients” array variable.
  • After that, “j++” is jumping on the next index.
  • The variable “$1” is used to save and return the array that was just created from the “openvpn.log” text file.
  • On the outside of the function, the string has been declared as “$string” and has been given a file path as a value.
  • This string has been passed to the readArray function as a reference to read text from this file.
  • After that, the “clients” array has been printed, and the entire text within it has been displayed in one line.
  • Now, we will display a message that the array is no longer empty.
  • The “for” loop has been used to convert the contents of the “clients” array into the array type and declare an index for the contents using the statement “echo “$i: ${clients[$i]}.”
  • Finally, we displayed a message and printed some converted array values separately as a single indexed position of an array.

Let us now check the output of this Bash script. Run the Bash command to execute the “script.sh” file. As you can see, the first echo statement will print all the text from the “openvpn.log” file, which has been saved in the “clients” array as one line. The second echo statement will display the string message. The third echo statement will display the “clients” array in indexed form, as it has just been converted. The fourth one will display a message again. The final one will display the contents of the “clients” array individually.

Conclusion

This article showed you how to return arrays (especially associative arrays) to a function using the “declare” built-in command with two examples. I hope that this article helped you to better understand this topic.

]]>
Create Bash Functions with Arguments https://linuxhint.com/create-bash-functions-arguments/ Sun, 21 Feb 2021 12:46:17 +0000 https://linuxhint.com/?p=90600 In programming, a function is an entity that performs an activity when it is called. This function may or may not accept arguments, which are the parameters that determine the activity that a function performs. Many of those who are new to programming might wonder why we even need to create functions when we can simply write a program as-is without breaking it into different parts.

This is where the concepts of Modularity and Code Reusability come into play. Modularity, or modular programming, is a highly recommended programming approach that breaks code into chunks to enhance readability, which also results in Code Reusability. Code Reusability refers to the ability to reuse a certain piece of code repeatedly, thus avoiding the task of rewriting the code every time it is used.

Modularity and Code Reusability are why functions are so extensively used in all programming languages, regardless of whether they are high-level or low-level. However, it can be quite tricky to create functions that work with the correct arguments or that accept certain arguments. This article uses several examples to show you how to create Bash functions with arguments in Linux Mint 20.

Examples of Creating Bash Functions with Arguments in Linux Mint 20

Functions with arguments in Bash can be created very conveniently. The following examples demonstrate how to create various Bash functions with arguments.

Example 1: Passing a String Argument to a Function

In this example, we will write a Bash script that will define a function to take a string as an argument. This can be done by copying the script shown in the image below in a Bash file. You can name your Bash filename according to your preferences:

In this Bash script, we created a function named “Hello.” Inside the body of the function, we will print a message, followed by “$1,” which represents the value of the string argument that will be passed to this function. Then, outside the body of this function, we called this function with its name while specifying the string argument to be passed to the function inside of double-quotes.

After creating this Bash script, we will execute the script with the following command:

$ bash Function.sh

The output of this script is shown in the image below:

Example 2: Passing More than One String Argument to a Function

In the next example, we will write a Bash script that will define a function to take two string arguments. This can be done by copying the script shown in the image below in a Bash file:

The script used in this example is roughly the same as the one that we wrote in our first example. The only variation is that, in this script, we used two placeholders (i.e., “$1” and “$2”) for our arguments, since we are passing two string arguments to this function. Then, in the same manner, we called this function with its name, followed by two string arguments enclosed in double-quotes.

After executing this modified script, you will obtain the following result:

Example 3: Passing Integer Arguments to a Function for Addition

To add two integers in Bash, we will write a Bash script that will define a function to take two integer arguments. This can be done by copying the script shown in the image below in a Bash file:

In this Bash script, we defined a function named “Sum.” Inside the body of this function, we created an expression to add the values of the integer arguments “$1” and “$2” and store the result of the sum in the variable “add.”

We will display the result of this calculation using the “echo” command. Outside the body of this function, we called it with its name, followed by the two integer parameters, “22” and “27.”

When we execute this Bash script, we will obtain a message in our terminal, followed by the result of our addition, which will be “49.” The output of this script is shown in the following image:

Example 4: Passing Integer Arguments to a Function for Multiplication

To multiply two integers in Bash, we will write a Bash script that will define a function to take two integer arguments. This can be done by copying the script shown in the image below in a Bash file:

In this Bash script, we defined a function named “Product.” Inside the body of this function, we created an expression to multiply the values of the integer arguments “$1” and “$2” and store the product in the variable “mul.”

Then, we will display the result of this calculation with the “echo” command. Outside the body of this function, we called it with its name, followed by two integer parameters “2” and “3.”

When we execute this Bash script, we will obtain a message in our terminal, followed by the result of our multiplication, which will be “6.” This output is shown in the following image:

Conclusion

This tutorial showed you four different examples of creating Bash functions with arguments. These examples were based on the functions that display messages, as well as those that perform some basic calculations. With these examples, you should now have a basic idea of passing arguments to functions in Bash in Linux Mint 20. However, the complexity of these functions can vary according to the requirements of your program.

]]>
Bash script to While Loop while Reading Stdin https://linuxhint.com/while-loop-bash-script-reading-stdin/ Sun, 21 Feb 2021 12:11:00 +0000 https://linuxhint.com/?p=90468 The concept “stream” in a computer applies to something that might move data. Any instruction you are executing in the terminal would be at any position of the flow. These positions can be an origin or an outflow. Let’s get a quick overview of the specific Stdin stream. In Linux, stdin refers to the default or standard input. The input it requires must be a text. To acquire data or information from you, it’s the file handler that your procedure readout. Almost all flows are viewed in Linux as if they are directories. We may read/write information from all of these streams, exactly as you can read/write a document. By using a special file descriptor number related to it provides a great approach to access a document. There have been special values allocated to every one of these throughout the situation of such streams. Stdin has a value of 1.

Stdin: 1

Let’s begin by understanding through practice about Stdin Stream using while loops. At very first, we will be having a basic example of the stdin as read. Execute the instruction below. The instruction would demand keyboard input. In this, through stdin, the reading tool gets the text.

$ read

Example 01:

Create a new file, “input.sh” and add the appended script to it. We have been using the while loop to read the text by a user from the terminal and print it. The script is named with a “/dev/stdin” as the very first $1 parameter, in which the corresponding approach reads the regular input from the console. Save this file and close it.

Open the terminal, and run the newly updated file “input.sh” as:

$ bash input.sh

When you execute the file using the bash command, you will be jumped to the next line to write something. As you can see below, the user has written a one-line text and press Enter.

The text written by a user will be read out first and printed out on the next line as below.

You can even provide one space between your text input as below.

Example 02:

Now we will read the text from the file. Update the same file “input.sh” by providing the filename “script.sh” as the very first $1 parameter. The corresponding approach reads from this document.

We have the following text information in the file “script.sh” as below. Let’s check how it works.

Execute the file “input.sh” using the bash command. You will see that the read stream reads out from the provided file “script.sh” and print it out in the terminal as below.

$ bash input.sh

Example 03:

Let’s have an example to read each directory one by one using stdin. You have to consider the parameter -u with the read. In this, “-u 1” implies “read from stdin.” In this code, “line” represents the filename, and the increment “i++” is used to jump over to the next directory or file. It will also count the file number that has been read as well. Let’s run this code to check what happens next.

Execute the bash file “input.sh”. It will prompt you to enter some text to jump over to the next file. Here “comm” represents the name of the first file.

$ bash input.sh

While continuing this, you can see we have a list of files that we have gone through.

Example 04:

In this example, we have two related files to read from. Assign the required privileges to both files using the “Chmod” command as below.

chmod u+x filename

Write the below code in the file “input.sh”. Until the “while” loop is getting lines, it will print those lines. While the “line” refers to another file “script.sh”.

We have the below code in the file “script.sh”. While the loop is running, it is printing the line number.

Execute both files using “”./” at the start of the filename and separating using “”|” in the shell. You will see that it is printing the line numbers while printing the text from the files as well. It’s a very simple method to correlate two files or their contents.

$ ./script.sh | ./input.sh

Example 05:

Let’s end this topic by having this simple and efficient example. We have a file “script.sh” with the below contents or names of persons. We will be reading these names one by one from another file.

Update the file “input.sh: with the below script. In this script, we have a while loop to elaborate “stdin” working. We have been using read “read –r” while reading from another file as other than standard input. On the other hand, using “-u” as bash-specific, the standard output from the user in the terminal. Here, the “name” is the text or content of the file “script.sh”. The option “-p” is used to “read”. The read statement will read the “name” from another file and ask if you want to delete it or not. The keyword “ip” is used for user response to affirm the action of deletion. Whatever the user response is, it will print it out. In the “if” statement, it will check if the standard input from the user is same as “y”, then it will print out some message as mentioning that it has been deleting the “name”. This process will be reiterated until the last content of the file “script.sh”.

Let’s have a look at the output of the above code. Execute the file using the bash command. The system will ask you if you want to delete this “name” or not. Enter “y” and tap “Enter”.

$ bash input.sh

Here on pressing “y”, it will print “y” and show a message that it has been deleting the particular “name”. After that, it will switch to another “name”.

It will ask you to delete the names until all the names or contents of file “script.sh” have been lopped over as below.

Conclusion:

We have magnificently gone through all the simple examples of standard input while using the “while” loop in the bash script.

]]>
How do I Create an Alias in Bash? https://linuxhint.com/create-bash-alias/ Sat, 20 Feb 2021 22:07:56 +0000 https://linuxhint.com/?p=90339 Bash alias is a command-based shortcut title. Every alias comprises a single word (or maybe even a single letter), which can be used rather than a relatively long command. In the Linux system, there have been several instructions that we’ll need to utilize daily. If we can run some popular instructions by typing quick instructions, it would be very beneficial for all of us. Via bash aliases, Linux users can conveniently build commonly used shortcut commands for big commands. Bash aliases are not just used to ease the job and thus save users’ time.

Create Alias in Bash:

Most people prefer to execute commands using shortcuts. You could find yourself, for instance, using the “ls –F” instruction many times. You could even create a detour for this instruction conveniently: for example, “lf”. So when you have to use “lf” in which the shell expects a command, the shell will replace “ls –F”. The alias definition begins with the word “alias”, preceded by a title of the alias, the equivalent symbol, as well as the instruction we intend to execute as we enter the alias. It is appropriate to encapsulate the instruction in quotations and without any spacing all over the equal sign. There is a need to announce each alias on even a new line. It is really easy to construct aliases within Bash. The following is the alias syntax:

$ alias=alias_name=”command_to_run”

Alias Types:

A user may temporarily or permanently claim an alias. It is possible to be using temporary aliases as soon as the user’s access persists. Hence there are two types of alias, temporary and permanent. We’re going to have a look at and analyze both types. Firstly, login from your Linux system and open your command terminal. You will be able to see the already defined default aliases of your Linux system using the simple “alias” command in the terminal, and the list will be displayed as shown below.

$ alias

All of these mentioned techniques are carried out on Ubuntu. Conversely, so far, since you’re dealing with Bash, they can function on every Linux distribution.

Temporary Aliases:

So far, because the console session is operating, such a kind of alias persists. It would lose the alias once the shell is ended. Let’s have a look at the temporary alias. Open your command terminal and navigate to the Desktop directory using the below command:

$ cd ~/Desktop

Perhaps one of the utmost popular instructions on the Linux terminal is the “ls” instruction. Typically, with either the “-la” option, we use this command to display all files and folders, plus secret ones, as in the large list layout.

Now using the “ls” command, we will create the alias.

$ alias L=" ls –la"

The performance of the “L” & “ls -la” instructions may be the same upon constructing aliases.

$ L

If the window is closed and the consumer begins a new session again, the alias instruction would not operate.

$ L

Permanent Aliases:

Bash may recall the formation of both the alias as well as its purpose when it is formed. You have to announce it in the .bashrc document to create permanent aliases. The document .bashrc has a bash script that is run each moment a bash process begins. The position is “~/.bashrc”. For every single person in the process, it is special. Let’s have an example of permanent aliases. You can update your system without using the aliases using the update and upgrade command as below.

$ sudo apt update && sudo apt upgrade -y

For making your preferred aliases, .bashrc is indeed a popular approach. Within your setup, .bashrc might not have been active. Create and launch the .bashrc using the nano command. If it is not available, an empty document would be opened.

$ nano ~/.bashrc

File .bashrc will be opened. Add the below line to the file to make aliases for an update of the system.

alias update=" sudo apt update && sudo apt upgrade –y"

Save the file and close it. After that, run the source instruction in the terminal to replenish the file.

$ source ~/.bashrc

This is the moment to verify whether the alias is working or not. Restart the Linux system, get yourself logged in to your Linux system, and execute the alias “update” command that we have just formed. You can see that the alias has been successfully working as it should be and updating the system.

Remove Bash Alias:

To remove the formerly formed command aliases, the term unalias is being used. That alias would not function while using this instruction. Well, you may use the unalias instruction to completely disable it if you find that you no longer want to have the shortcut command. Firstly check the already formed aliases in your system using the alias command.

$ alias

You can see a newly formed alias command “update” is listed in the list below.

Now execute the “unalias” command to delete the previously made shortcut command.

$ unalias update

While checking again in the list of aliases, you can see that the “update” alias has been removed completely.

You can also erase the aliases from the .bashrc file by opening it using the nano command and deleting it from the file. You can simply comment on the alias line or just remove it completely. After that, run the source command to reflect the changes. Save the updated file and restart your system to check the changes. When you again try the “update” alias command, it will not work.

Conclusion:

In this guide, we have studied alias and their two different types. This article is a simple illustration of how to generate an alias as well as execute the commands that are quite often used without typing each instruction over and over yet again. One could now ruminate more about instructions to use far more and generate shortcuts in one’s command shell for them.

]]>
How to Simulate an Array of Arrays in Bash https://linuxhint.com/simulate-bash-array-of-arrays/ Sat, 20 Feb 2021 19:13:42 +0000 https://linuxhint.com/?p=90321 Bash is indeed an interpreted, interactive language, and how much space to reserve in advance does not have to be known. It is also possible to make ready a new array dynamically without declaring it or extending a previously defined array to include further entries. Still, multidimensional arrays aren’t supported by bash, and we can’t get array components that are also arrays. Fortunately, multidimensional arrays can be simulated. This article will provide some illustrations of the simulation of an array of arrays in a bash script.

Example 01: Using Simple “For” Loops

We have an example of simulating an array of arrays using the simple method. Let’s start demonstrating how to load a user-defined m x n table with random numbers (that aren’t random, because each column will at all times have a similar number in each run on most of its rows, but that does not apply to the question), and print it. When we work on either a bash that you do have, bash version 4, the below script would certainly work efficiently. We should not solitary declare 0; that is more like a perfect solution to values being accepted vigorously. We have declared an array with the “-A” keyword.  If we don’t define the associative array using -A, the code may not work for us. The read keyword is then used to read the user’s input, which is rows and columns of a table. Then we have used two “for” loops for the incrementation of rows and columns of a table. In for loop, we have been making a two-dimensional array. In the next for loop, all the values of an array have been displayed.

When you run the bash file, it will ask a user to enter rows and columns as “m” and “n”. After that, for loops will generate a two-dimensional table as below.

Example 02: Using Hashes

Taking the same instance, we can emulate the arrays using hashes. However, we have to be more careful about leading zeros and several other stuff. The next explanation is working. However, the way out is very far from ideal. We have been taking rows and columns manually. For loop is used to make a matrix. Then we have been using hashes to emulate the two-dimensional array. At last, the array will be printed out as below.

Execute the file “input.sh” in the bash shell using the bash command. You will find a table with rows and columns number mentioned.

Example 03: Using Associative Arrays

Let’s have an example of simulation having a somewhat similar effect using the associative arrays used as an array of arrays as below. After the declaration of the associative array, we have defined values for arrays separately. After that, we have made it to print out the values in two dimensional way.

You can see the output as a two-dimensional array while running the file. If we ignore the “declare -A arr” line, the echo statement may display (2 3) rather than (0 1), since (0,0), (1,0), and others may have been used as a mathematical expression and calculated to 0 (the value at the right side of a comma).

Example 04: Using Name-references

In bash, it is a frequent issue with referencing arrays inside arrays that you’ll have to construct name-references using declare -n. That name afterward -n serves as a name ref for the value allocated (after =). Currently, we handle this variable only with attribute name ref to extend as though it was an array and extend the appropriately cited array as beforehand. Let’s have an example of name refs. We have successfully declared two arrays. After that, we have assigned both the arrays to another array as a member. We have used for loop to make a two-dimensional array. We have made another variable to add the one-by-one values of the array “group” into it for comparison. Deep down, it will go to members of inner arrays “bar” and “foo” to take values and compare them while printing the message.

When we execute the file “input.sh”, you will see the below output. The variable “lst” has values of inner arrays within the array “groups”.

Example 05:  Using Cut Keyword

Only now, I’ve stumbled into it. There had been a fairly straightforward approach that worked for everyone. To show a main map for the system, I decided to use an array containing a device name and a screen location. We have to concatenate the title of the unit and the corresponding location of a display into some single string, using only a delimiter, which we assumed will not occur in either of our values (in my case, I used .). And I used a “cut” keyword to split the concrete values into their components if necessary. There may be a clearer and easier approach to do it, though, and this is only to illustrate that in a sense, in bash, we can build a multidimensional array, although it does not help it. After that, you have to print both the device name and its location separately after creating the substring.

Let’s run the bash “input.sh” file. You will see the separated device and its location in the shell prompt as while execution. The solution works using the cut command.

Example 06

Let’s take a little longer example to emulate a multidimensional array. In the load_alpha() function, all the alphabets will be loaded into the array. After that, the print_Alpha() function is declared and used to print out all the alphabets in the row-major order as a matrix or two-dimensional format. On the other hand, we have been using the rotate() function to rotate the array. Let’s try this example in the bash shell to see results.

While execution, we have found a very beautiful structure of multidimensional array in the bash shell as below

Conclusion

We have successfully tried some examples for simulating arrays of arrays in bash. I hope it works!

]]>