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 178 of 355
Display month names and year from a column with date records with MySQL
Let us first create a −mysql> create table DemoTable1619 -> ( -> ArrivalTime datetime -> ); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using insert −mysql> insert into DemoTable1619 values(now()); Query OK, 1 row affected (0.40 sec) mysql> insert into DemoTable1619 values(curdate()); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1619 values('2019-12-31'); Query OK, 1 row affected (0.17 sec)Display all records from the table using select −mysql> select * from DemoTable1619;This will produce the following output −+---------------------+ | ArrivalTime | +---------------------+ | 2019-10-20 15:02:12 | | ...
Read MoreMySQL query to copy IP address from varchar column to integer in the same table?
For this, you can use INET_ATON(). Let us first create a −mysql> create table DemoTable1404 -> ( -> IpAddress varchar(40) -> ); Query OK, 0 rows affected (1.02 sec)Insert some records in the table using insert −mysql> insert into DemoTable1404 values('192.168.120.0'); Query OK, 1 row affected (0.43 sec) mysql> insert into DemoTable1404 values('192.168.120.20'); Query OK, 1 row affected (0.60 sec) mysql> insert into DemoTable1404 values('224.0.0.0'); Query OK, 1 row affected (0.42 sec)Display all records from the table using select −mysql> select * from DemoTable1404;This will produce the following output −+----------------+ | IpAddress | +----------------+ | ...
Read MoreUpdate table and order dates in MySQL
You cannot use UPDATE command with ORDER BY clause, but you can use SELECT statement with ORDER BY DESC.Let us first create a −mysql> create table DemoTable1403 -> ( -> DueDate timestamp -> ); Query OK, 0 rows affected (1.26 sec)Insert some records in the table using insert −mysql> insert into DemoTable1403 values('2019-09-29'); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable1403 values('2016-02-21'); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable1403 values('2018-01-31'); Query OK, 1 row affected (0.65 sec) mysql> insert into DemoTable1403 values('2017-12-01'); Query OK, 1 row affected (0.27 sec)Display all ...
Read MoreDisplay substring in MySQL if the string is less than a specific length or display a custom message if it is more?
For this, you can use substring() function in MySQL. For conditions, use MySQL CASE statement. Let us first create a −mysql> create table DemoTable1402 -> ( -> EmployeeName varchar(40) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert −mysql> insert into DemoTable1402 values('Adam Smith'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1402 values('Chris Brown'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1402 values('David Miller'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1402 values('Carol Taylor'); Query OK, 1 row affected (0.10 sec)Display ...
Read MoreWhy do backticks won't work in the SET part of an UPDATE query (for MySQL)?
Backticks would work if written correctly as in the below syntax −update `yourTableName` set `yourTableName`.`yourColumnName`='yourNewValue' where yourCondition;Let us first create a −mysql> create table `DemoTable_1401` -> ( -> Name varchar(20) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert −mysql> insert into `DemoTable_1401` values('Chris'); Query OK, 1 row affected (0.16 sec) mysql> insert into `DemoTable_1401` values('David'); Query OK, 1 row affected (0.10 sec) mysql> insert into `DemoTable_1401` values('Bob'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select −mysql> select * from `DemoTable_1401`;This will produce the ...
Read MoreHow to implement CANDIDATE key in any MySQL table?
Each relation may have one or more candidate key. One of these candidate keys is called Primary Key. Each candidate key qualifies for Primary Key. Therefore, candidates for Primary Key is called Candidate Key. To implement candidate key in MySQL, set more than one column as unique key. These keys would qualify for candidate key as in the below syntax −alter table yourTableName add unique key anyName(yourColumnName1, yourColumnName2);Let us first create a −mysql> create table DemoTable1400 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(40), -> Age int -> ); Query ...
Read MoreGetting the next primary key without adding a new record is impossible, isn't it in MYSQL
No, it is possible to get the next primary key without adding a new record. Let us first create a −mysql> create table DemoTable1399 -> ( -> StudentId int NOT NULL AUTO_INCREMENT, -> PRIMARY KEY(StudentId) -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert −mysql> insert into DemoTable1399 values(); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1399 values(); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1399 values(); Query OK, 1 row affected (0.07 sec)Display all records from the table using select −mysql> select ...
Read MoreFind percentage from marks in MySQL
Let us first create a −mysql> create table DemoTable1398 -> ( -> Marks int -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert −mysql> insert into DemoTable1398 values(78); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1398 values(82); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1398 values(90); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1398 values(98); Query OK, 1 row affected (0.12 sec)Display all records from the table using select −mysql> select * from DemoTable1398;This will produce the following output −+-------+ | ...
Read MoreComparison of varchar date records from the current date in MySQL
For date comparison, you can use STR_TO_DATE(). Following is the syntax −select * from yourTableName where str_to_date(yourColumnName, 'yourFormatSpecifier') > curdate();Let us first create a −mysql> create table DemoTable1397 -> ( -> AdmissionDate varchar(40) -> );s Query OK, 0 rows affected (0.97 sec)Insert some records in the table using insert −mysql> insert into DemoTable1397 values('01/04/2019'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1397 values('27/09/2019'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1397 values('29/09/2018'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1397 values('29/09/2019'); Query OK, 1 row affected (0.08 sec)Display ...
Read MoreMySQL query to sort multiple columns together in a single query
To sort multiple columns, use ORDER BY GREATEST(). Let us first create a −mysql> create table DemoTable1395 -> ( -> Value1 int, -> Value2 int, -> Value3 int -> ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert −mysql> insert into DemoTable1395 values(40, 50, 60); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1395 values(90, 56, 80); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1395 values(10, 20, 30); Query OK, 1 row affected (0.11 sec)Display all records from the table using select −mysql> select ...
Read More