MySQL Row

Introduction to MySQL Row

Mysql Row function is used to extract only a single row from a table. So, whenever we want to select only one single row values then we use row subquery function in our main query statement. Along with a single row values it also returns values of one or more than one column value.

Since, we use row function in a subquery, therefore logical operators are required to make comparison between the statements and find a matched output as a result. Below are the logical operators that are used to make comparison:

=,>,<,>=,<=,<>,!=,<=>

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 the above queries, if table table2 contains a single row with only one column i.e. id = 5, the subquery will returns 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 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 rows because a row function in the subquery can return maximum one row or no row i.e. almost one row.

Row constructor:  A row constructor is used to make comparisons with subqueries that output two or more columns as result. When any subquery output a single column, it is considered as as a scalar value and is not considered as 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

Below are the case study we will learn:

Case #1 – Insert a single row in table

Insert command is used to insert new row in the table

Syntax:

Insert into <table_name> values (<value1>,<value2>,<value3>…..<valuen>);

• Where table_name is the name of the table in which a row is needed 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

Delete command is used to delete rows from a mysql table. It is used to delete temporary data from out database. Delete clause can delete more than 1 row from a table. Once a row is deleted by delete clause from table it cannot be recovered.

Syntax:

Delete from <table_name> where <condition>;

Where,

• table_name is the name of the table from where the row needs to 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 datas that are present in our table. For that, we will use a select query.

select * from students;

Output:

Now, we will delete detail of 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 form table

Select clause is used to select data from 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 name of the table 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 are used to define rules to be applied to store values in the table.

They are:

• Not null: It is used to specify that the column cannot contain null values.
• Unique: It is used to for not inserting duplicate values in the column.
• Primary key: It is used to accept only unique values for each row.
• Foreign key: It is used to link two tables by a common column of both tables.
• Default: If no values are inserted in the table then the default set values will be inserted at 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 MySQL row subquery function, which is used to select only one row at a time with one or more columns. In this article, we have also about simple manipulations to be done in the row of the table like insertion, deletion, updation. We have also learned about the constraints that can be applied in the table. In this article, all queries have been explained by a simple example and also are provided with the screenshots of the output to provide a better understanding to the reader.

Recommended Articles

This is a guide to MySQL Row. Here we discuss an introduction to MySQL Row, syntax, how does it work with the different case study in detail. You can also go through our other related articles to learn more –

1. MySQL CHECK Constraint
2. ROLLUP In MySQL
3. MySQL Root
4. MySQL Commands