EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Row
Secondary Sidebar
MySQL Tutorial
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL?Table?Size
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

MySQL Row

By Priya PedamkarPriya Pedamkar

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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:

MySQL Row - 1.

Now, we will delete detail of student whose roll_no is 11

delete from students where roll_no=11;
select* from students;

Output:

2 set

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:

1 in set

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:

alter

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
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (17 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more