Introduction to MySQL Row
The Mysql Row function extracts only a single row from a table. So, whenever we want to select only one row value, we use the row subquery function in our main query statement. It also returns values of one or more than one column value along with a single row value.
Since we use the row function in a subquery, logical operators must compare the statements and find a matched output. The logical operators used for making comparisons are as follows:
=,>,<,>=,<=,<>,!=,<=>
Code #1
select * from table1 where (column_1, column_2) = (select column_3, column_4 from table2 where id=5);
select * from table1 where ROW (column_1, column_2) = (select column_3, column_4 from table2 where id=5);For both queries above, if table2 contains a single row with only one column, i.e., id = 5, the subquery will return a single row. If the table contains a single row but has more than 1 column, i.e., column_3 and column_4 values equal to the column_1 and column_2 values of any rows in table1, the where expression is true, and each query returns those table1 rows. If the table2 row column_3 and column_4 values are not equal to the column_1 and column_2 values of any table1 row, the expression is false, and the query will return an empty result set. An error will occur if the subquery returns more than one row because a row function can produce a maximum of one or no row, i.e., almost one row.
Row constructor: A row constructor compares subqueries that output two or more columns. A subquery output of a single column is considered a scalar value, not a row value.
Code #2
select * from table1 where row (1) = (select column_1 from table2);
select * from table1 where (column_1, column_2) = (1, 2);
select * from table2 where column_1 = 1 and column_2 =1;Both the above queries are equivalent
Case Study of MySQL Row
Below is the case study we will learn:
Case #1 – Insert a single row in the table
We use the INSERT command to insert a new row into the table.
Syntax:
Insert into <table_name> values (<value1>,<value2>,<value3>…..<valuen>);- Where table_name is the table name in which a row needs to be inserted.
- Values=> values for each column of the table.
Code:
Insert into students values(1,'ashish','java');
Insert into students values(2,'rahul','C++');To check the content of the table, we will use a select query.
select * from students,Below is the output of the query
Case #2 – Delete a single row from a table
We use the DELETE command to delete rows from a MySQL table. It serves the purpose of deleting temporary data from our database. The DELETE clause can delete multiple rows from a table. Once the delete clause removes a row from the table, it cannot be recovered.
Syntax:
Delete from <table_name> where <condition>;Where,
- table_name is the table’s name from where the row must be deleted.
- Where clause is optional and is used to restrict the numbers of rows we need to delete.
Firstly, we need to check what are all the data that are present in our table. For that, we will use a select query.
select * from students;Output:
Now, we will delete the detail of the student whose roll_no is 11
delete from students where roll_no=11;
select* from students;Output:
Case #3 – How to select a single row from the table
A select clause is used to select data from a table.
Syntax:
select column 1, column 2,….column n from table_name where [condition]
select roll_no, student_name, course from students where roll_no=3;Output:
Case #4 – How to alter a row
Syntax:
update<table_name> set <column_name>=value where <condition>;- Table_name => The table name in which the value is to be changed.
- condition => condition to get specified row
Code:
update students set roll_no=roll_no+10 where student_name='ashish';
select * from students;Output:
Case #5 – MYsql constraints
MySQL constraints apply rules to store values in the table.
They are:
- Not null: It specifies that the column cannot contain null values.
- Unique: It prevents the insertion of duplicate values in the column.
- Primary key: It accepts unique values for each row.
- Foreign key: Links two tables by a common column of both tables.
- Default: If the table does not receive any values during insertion, the system automatically inserts default values in the blank place.
Syntax:
create table<table_name>([column_name] [datatype] ([size])[column constraint]---[table constraint] ([column_name]))Code:
create table students (roll_no int PRIMARY KEY, student_name varchar(150) NOT NULL,course varchar(100) NOT NULL UNIQUE);Conclusion
In this article, we have learned about the MySQL row subquery function, which selects only one row at a time with one or more columns. This article also explains how to perform simple manipulations on the rows of a table, such as insertion, deletion, and updating. It also teaches us about the constraints we can apply to the table. The article clearly understands all queries through a simple example and includes screenshots of the output for better reader comprehension.
Recommended Articles
We hope that this EDUCBA information on “MySQL Row” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
