Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
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 More
Let us first create a −mysql> create table DemoTable1396 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(40), -> Age int -> ); Query OK, 0 rows affected (0.93 sec)Insert some records in the table using insert −mysql> insert into DemoTable1396(Name, Age) values('Chris', 21); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1396(Name, Age) values('David', 24); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable1396(Name, Age) values('Bob', 26); Query OK, 1 row affected (0.40 sec)Display all records from the table using select −mysql> select * from DemoTable1396;This ... Read More
Let us first create a −mysql> create table DemoTable1 -> ( -> Id int -> ); Query OK, 0 rows affected (1.06 sec)Insert some records in the table using insert −mysql> insert into DemoTable1 values(1); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1 values(NULL); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1 values(2); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable1 values(3); Query OK, 1 row affected (0.13 sec)Display all records from the table using select −mysql> select * from DemoTable1;This will produce the following output −+------+ | ... Read More
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
For thousands number, use MySQL FORMAT(). Let us first create a −mysql> create table DemoTable1394 -> ( -> Amount decimal(7, 3) -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert −mysql> insert into DemoTable1394 values(60); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1394 values(2355.4); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable1394 values(456); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1394 values(8769); Query OK, 1 row affected (0.13 sec)Display all records from the table using select −mysql> select * from DemoTable1394;This ... Read More
Following is the syntax −select * from yourTableName where yourColumnName like '%a%a%a%';Let us first create a −mysql> create table DemoTable1393 -> ( -> CountryName varchar(40) -> ); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert −mysql> insert into DemoTable1393 values('andorra'); Query OK, 1 row affected (0.50 sec) mysql> insert into DemoTable1393 values('australia'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1393 values('argentina'); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable1393 values('austria'); Query OK, 1 row affected (0.26 sec)Display all records from the table using select −mysql> ... Read More
Let us first create a −mysql> create table DemoTable1392 -> ( -> ArrivalDate date -> ); Query OK, 0 rows affected (0.43 sec)Insert some records in the table using insert −mysql> insert into DemoTable1392 values('2019-09-10'); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable1392 values('2019-09-26'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1392 values('2019-09-12'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1392 values('2018-09-20'); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable1392 values('2019-10-11'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select ... Read More
The error occurs because we have a comma at the end of the column names, just before “from tablename’. Here is the error you may have got −mysql> select ClientId, ClientName, ClientAge, from DemoTable1391; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from DemoTable1391' at line 1To remove the error, as discussed above, you need to remove the last comma from the table. Let us first create a −mysql> create table DemoTable1391 -> ( -> ClientId int NOT ... Read More
If the string begins with integer then it converts the string to integer, otherwise it won’t. Let us first create a −mysql> create table DemoTable1390 -> ( -> StudentId varchar(20) -> ); Query OK, 0 rows affected (0.93 sec)Insert some records in the table using insert −mysql> insert into DemoTable1390 values('563_John'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1390 values('1001_Carol_Taylor'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable1390 values('David_Miller_789'); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable1390 values('456_AdamSmith'); Query OK, 1 row affected (0.11 sec)Display all records from ... Read More
Let us first create a −mysql> create table DemoTable1 -> ( -> StudentFirstName varchar(20), -> StudentLastName varchar(20), -> StudentAge int, -> StudentCountryName varchar(20) -> ); Query OK, 0 rows affected (4.20 sec)Let us now make all column names lower case in MySQL −mysql> select concat('alter table ', table_name, ' change `', column_name, '` `', -> lower(column_name), '` ', column_type, ';') -> from information_schema.columns where table_schema = 'demo';This will produce the following output −+-----------------------------------------------------------------------------------------------------------------+ | concat('alter table ', table_name, ' change `', column_name, '` `', lower(column_name), '` ', column_type, ';') | +-----------------------------------------------------------------------------------------------------------------+ | ... Read More