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 311 of 355
How to get the greatest of two columns values in MySQL?
In order to get the greatest of two columns values in MySQL, you need to use GREATEST() function. Following is the syntax:select greatest(yourColumnName1, yourColumnName2) AS anyAliasName from yourTableName; Let us first create a table:mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Number1 int, Number2 int ); Query OK, 0 rows affected (0.63 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable(Number1, Number2) values(1000, 10000); Query OK, 1 row affected (0.49 sec) mysql> insert into DemoTable(Number1, Number2) values(600, 900); Query OK, 1 row affected (0.12 sec) mysql> ...
Read MoreDisplay all records except one in MySQL
You can use IN() to display all records except one in MySQL. Let us first create a table −mysql> create table DemoTable ( Id int, FirstName varchar(20) ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 'Larry'); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable values(10, 'Chris'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(110, 'Robert'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(90, 'David'); Query OK, 1 row affected (0.20 sec)Following is the query ...
Read MoreHow to convert date YYYYMMDD to YY-MM-DD in MySQL query?
To convert date YYYYMMDD to YY-MM-DD in MySQL, use the below syntax −select date_format(str_to_date(yourColumnName, '%Y%m%d'), '%Y-%m-%d') from yourTableName;Let us first create a table −mysql> create table DemoTable ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientProjectDeadline varchar(200) ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command. We have inserted dates in the YYYYMMDD format −mysql> insert into DemoTable(ClientProjectDeadline) values('20121221'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable(ClientProjectDeadline) values('20190416'); Query OK, 1 row affected (0.53 sec) mysql> insert into DemoTable(ClientProjectDeadline) values('20160624'); Query OK, 1 row affected (0.20 sec) mysql> ...
Read MoreSort in MySQL and increment value?
You can use update command along with a user-defined variable. Let us first create a table −mysql> create table DemoTable ( FirstName varchar(20), Position int ); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', 100); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Robert', 120); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values('David', 130); Query OK, 1 row affected (0.16 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;This will produce ...
Read MoreHow to count number of specific symbols in a row in MySQL?
You can use LENGTH() to count number of specific symbols in a row. Let us first create a table −mysql> create table DemoTable ( Value varchar(200) ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('?1234?6789?5656?324?'); Query OK, 1 row affected (0.17 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+----------------------+ | Value | +----------------------+ | ?1234?6789?5656?324? | ...
Read MoreHow to display the bit(1) fields in MySQL?
Let us first create a table. Here, our columns is of type bit(1) −mysql> create table DemoTable ( isCaptured bit(1) ); Query OK, 0 rows affected (1.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.47 sec) mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.29 sec)Following is the query to display all records from the table using select statement −mysql> select ...
Read MoreHow to use TIME type in MySQL?
Let us first create a table. Within that we have set a column with type TIME to get the login time −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, LoginTime TIME NULL ); Query OK, 0 rows affected (0.69 sec)Insert records in the table using insert command −mysql> insert into DemoTable(LoginTime) values('12:34:45'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(LoginTime) values('13:56:01'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(LoginTime) values('04:12:23'); Query OK, 1 row affected (0.13 sec)Following is the query to display all records from the table using ...
Read MoreHow to get the maximum value from strings with integers in MySQL?
You can use CAST() with MAX() for this. Since the string is filled with string and integer, fir example, “STU201”, therefore we need to use CAST().Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentBookCode varchar(200) ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentBookCode) values('STU201'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(StudentBookCode) values('STU202'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(StudentBookCode) values('STU203'); Query OK, 1 row affected (0.20 sec) mysql> ...
Read MoreHow to change a specific char in a MySQL string?
To change a specific char in a MySQL string, you can use CONCAT() along with SUBSTRING().Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Value varchar(200) ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value) values('98764'); Query OK, 1 row affected (0.17 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+----+-------+ | Id | Value | +----+-------+ | 1 | 98764 | +----+-------+ ...
Read MoreExtract the user ID from the username only in MySQL?
To extract the User ID only from MySQL, you can use SUBSTRING_INDEX(), which extracts the part of a string from the Username to get the User ID. Let us first display the user −mysql> SELECT USER();This will produce the following output −+----------------+ | USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)Let us now extract the UserID only −mysql> SELECT SUBSTRING_INDEX(USER(), '@', 1);This will produce the following output −+-------------------------------+ | SUBSTRING_INDEX(USER(), '@', 1) | +-------------------------------+ | root ...
Read More