Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQL Articles
Page 120 of 355
What is the maximum length of each type of identifier in MySQL?
As we know that certain objects within MySQL are known as identifiers. These objects include a database, table, index, column, alias, view, stored procedure, partition, tablespace etc. Identifiers are stored using Unicode (UTF-8). The maximum length of each type of identifier is given in the following table:Sr. No.IdentifierMaximum Length (characters)1Database642Table643Column644Index645Constraint646Stored Procedure or Function647Trigger648View649Event6410Tablespace6411Log File Group6412Alias25613Compound Statement Label16
Read MoreHow can we use LPAD() or RPAD() functions with the values in the column of a MySQL table?
For using LPAD() or RPAD() functions with the column values we need to specify the column name as the first argument of these functions. Following the example from ‘Student’ table will make it clearer −Examplemysql> Select Name, LPAD(Name, 10, '*') from student; +---------+-------------------+ | Name | LPAD(Name, 10, '*') | +---------+-------------------+ | Gaurav | ****Gaurav | | Aarav | *****Aarav | | Harshit | ***Harshit | | Gaurav | ****Gaurav ...
Read MoreHow can we use the output of LTRIM() and RTRIM() functions to update MySQL table?
We can use LTRIM() and RTRIM functions with MySQL update clause so that the values, after removing space characters, in the table can be updated. Following examples will demonstrate it −ExampleSuppose we know that there can be some space characters in the values of ‘Name’ column of table ‘Student’ then with the help of following single query we can remove the space characters from that column’s value and also update the table −mysql> Update Student SET Name = LTRIM(Name); Query OK, 0 rows affected (0.07 sec) Rows matched: 5 Changed: 0 Warnings: 0 mysql> Update Student SET Name = ...
Read MoreHow can I remove the leading and trailing spaces both at once from a string without using MySQL LTRIM() and RTRIM() functions?
Other than LTRIM() and RTRIM() functions, MySQL has TRIM() function to remove leading and trailing function both at once from a string. The use of TRIM() function can be understood from the following example of a test_trim table which has a column ‘Name’ containing the names with leading and trailing spaces.Examplemysql> Select Name, TRIM(Name)AS 'Name Without Spaces' from test_trim; +---------------+---------------------+ | Name | Name Without Spaces | +---------------+---------------------+ | Gaurav | Gaurav | | Rahul | Rahul | | Aarav | Aarav | +---------------+---------------------+ 3 rows in set (0.00 sec)
Read MoreHow can we use MySQL TRIM() to remove the whitespaces from all the rows and update table?
Suppose if a table has many values having whitespaces in the columns of a table then it is wastage of space. We can use TRIM() function to remove whitespaces from all the rows and update the table too in a single query. Following the example from ‘Employee’, having whitespaces in all its rows will exhibit the concept −Examplemysql> Select * from Employee; +------+----------------------+----------------------+----------------------+ | Id | Name | Address | Department | +------+----------------------+----------------------+----------------------+ | 100 | Raman ...
Read MoreWhat is the significance of using multiple columns in MySQL GROUP BY clause?
By specifying multiple columns in GROUP BY clause we can split the result set into smaller groups. The more columns specified in GROUP BY clause, the smaller the groups will be.Examplemysql> Select designation, YEAR(Doj), count(*) from employees GROUP BY designation, YEAR(DoJ); +-------------+-----------+----------+ | designation | YEAR(Doj) | count(*) | +-------------+-----------+----------+ | Asso.Prof | 2013 | 1 | | Asst.Prof | 2015 | 1 | | Asst.Prof | 2016 | 1 | | Prof | 2009 | 2 | | Prof | 2010 | 1 | +-------------+-----------+----------+ 5 rows in set (0.00 sec)
Read MoreIn MySQL, how to remove the specific prefix from entire column's value and update them?
It can be done by applying TRIM() function on the column along with MySQL UPDATE statement. The example below will make it more clear.ExampleSuppose, we have a table ‘Employee’ which have the prefix ‘Dept.’ with all the values of Column ‘Department’ as follows −mysql> Select * from Employee; +------+----------------+------------+----------------------+ | Id | Name | Address | Department | +------+----------------+------------+----------------------+ | 100 | Raman | Delhi | IT Dept. | | 101 | Mohan ...
Read MoreWhy we cannot use comparison operator(=) for getting the rows with NULL from a table?
We cannot use = (comparison operator) because we know that NULL is not a value. If we want to get the rows with NULL from a table then we need to use IS NULL operator in MySQL query. Following example using the data from ‘employee’ table will exhibit it −Examplemysql> Select * from Employee WHERE Salary IS NULL; +----+-------+--------+ | ID | Name | Salary | +----+-------+--------+ | 7 | Aryan | NULL | | 8 | Vinay | NULL | +----+-------+--------+ 2 rows in set (0.00 sec)The query above use IS NULL operator and produces ...
Read MoreWhat happens if the position of insertion, in MySQL INSERT() function, is out of range?
MySQL INSERT() function performs no insertion if the position of insertion is out of range. The position of insertion can be out of range in the case when we pass a negative or 0(zero) value or the value goes beyond the value of a total number of characters in an original string by 2. It can be understood with the help of the following example −ExampleThe query below will perform no insertion because the position of insertion is out of range i.e. a negative value.mysql> Select INSERT('Virat', -1, 5, 'Kohli'); +-------------------------------+ | INSERT('Virat', -1, 5, 'Kohli') | +-------------------------------+ | Virat ...
Read MoreHow can I export values based on some conditions from MySQL table into a file?
We can use the conditions in WHERE clause while exporting the data from MySQL table to a file. It can be understood with the help of an example −ExampleSuppose we are having following data from table ‘Student_info’ −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 125 | Raman | Shimla | Computers | | 130 | Ram | Jhansi | ...
Read More