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
MySQL Articles
Page 326 of 355
What is the Maximum Value of smallint(6) unsigned in MySQL?
The maximum value of SMALLINT(6) unsigned in MySQL is 65535. The number 6 does not affect the actual range. It can only be used to display width on the command line.The Minimum Value signed is-32768The Maximum Value unsigned is65535The Maximum value signed is32767Let us understand this with zerofill and create a table using the following query.mysql> create table smallIntDemo -> ( -> FirstNumber smallint(6) zerofill -> ); Query OK, 0 rows affected (1.95 sec)Now you can insert records in the table using insert command. Whenever you insert beyond the range 65535, it will not insert in the table, since this ...
Read MoreEasy way to re-order columns in MySQL?
To re-order columns in MySQL, use the ALTER TABLE MODIFY COLUMN. The syntax is as follows -ALTER TABLE yourTableName MODIFY COLUMN yourColumnName data type after yourColumnName.To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table reOrderColumn -> ( -> ProductId int, -> DeliveryDate datetime, -> ProductName varchar(100) -> ); Query OK, 0 rows affected (0.76 sec)Now check the description of the table. The query is as follows.mysql> desc reOrderColumn;The following is the output.+--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | ...
Read MoreHow to check multiple columns for a single value in MySQL?
You can check multiple columns for one value with the help of IN operator. The syntax is as follows −select *from yourTableName where value IN(yourColumnName1, yourColumnName2, ......N);To understand the above concept, let us create a table with some columns. The query to create a table is as follows −mysql> create table OneValueFromAllColumns −> ( −> StudentId int, −> StudentFirstname varchar(200), −> StudentLastname varchar(200), −> StudentAge int −> ); Query OK, 0 rows affected (1.41 sec)Insert some records in the table with the ...
Read MoreHow to find out number of days in a month in MySQL?
To find the number of days in month, use the below syntax.select DAY(LAST_DAY(yourColumnName)) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table DaysInaGivenMonth -> ( -> MonthName datetime -> ); Query OK, 0 rows affected (1.62 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into DaysInaGivenMonth values(now()); Query OK, 1 row affected (0.24 sec) mysql> insert into DaysInaGivenMonth values(date_add(now(), interval -1 month)); Query OK, 1 row affected (0.16 sec) mysql> insert into DaysInaGivenMonth values(date_add(now(), interval ...
Read MoreHow to get an age from a D.O.B field in MySQL?
To get age from a D.O.B field in MySQL, you can use the following syntax. Here, we subtract the DOB from the current date.select yourColumnName1, yourColumnName2, ........N, year(curdate())- year(yourDOBColumnName) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table AgeDemo -> ( -> StudentId int, -> StudentName varchar(100), -> StudentDOB date -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into AgeDemo values(1, 'John', '1998-10-1'); Query OK, 1 ...
Read MoreWhat is the MySQL error: “Data too long for column”?
The “Data too long for column” error occurs when you insert more data for a column that does not have the capability to store that data.For Example - If you have data type of varchar(6) that means it stores only 6 characters. Therefore, if you will give more than 6 characters, then it will give an error.Let us create a table to understand the error. The query to create a table is as follows −mysql> create table DataToolongDemo −> ( −> Name varchar(10) −> ); Query OK, 0 rows affected (0.55 sec)Above, we have created a table ...
Read MoreCount number of rows in each table in MySQL?
To get the count of rows, you need to use information_schema.tables. The syntax is as follows.SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘yourDatabaseName’;Let us implement the above syntax for a database with the name ‘test’. The query is as follows displaying the table names with the count of rows in the table.mysql> SELECT table_name, table_rows -> FROM INFORMATION_SCHEMA.TABLES -> WHERE TABLE_SCHEMA = 'test';The following is the output.+------------------------------------+------------+ | TABLE_NAME | TABLE_ROWS | +------------------------------------+------------+ | _student_trackerdemo | 0 | | _studenttrackerdemo | 0 | | add30minutesdemo | 0 | | addcolumn | 0 | ...
Read MoreHow can I select the row with the highest ID in MySQL?
You can select the row with highest ID in MySQL with the help of ORDER BY with LIMIT OFFSETThe syntax is as follows −select *from yourTableName order by yourColumnName desc limit 1 offset 0;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table HighestIdOrderBy −> ( −> EmployeeId int, −> EmployeeName varchar(200) −> ); Query OK, 0 rows affected (0.58 sec)Insert records in the table with the help of insert command. The query is as follows −mysql> insert into HighestIdOrderBy values(200, 'David'); Query OK, ...
Read MoreHow to decrement a value in MySQL keeping it above zero?
You can decrement value in MySQL with update command. With this, you can also restrict the value to not reach below 0.The syntax is as follows −update yourTableName set yourColumnName = yourColumnName - 1 where yourColumnName > 0;To avoid the value to go below zero, you can use yourColumnName > 0.To understand the above syntax, let us create a table. The query to create a table.mysql> create table DecrementDemo −> ( −> DecrementValue int −> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table with insert statement. ...
Read MoreUsing MySQL, can I sort a column but allow 0 to come last?
You can sort a column, with 0 come last with the help of ORDER BY. The syntax is as follows −select *from yourTableName order by yourFieldName = 0, yourFieldName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table SortColumnZeroAtLastDemo −> ( −> RankNumber int −> ); Query OK, 0 rows affected (1.40 sec)Now you can insert records in the table using the following query −mysql> insert into SortColumnZeroAtLastDemo values(100); Query OK, 1 row affected (0.20 sec) mysql> insert into SortColumnZeroAtLastDemo values(0); Query OK, 1 ...
Read More