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
Articles by AmitDiwan
Page 723 of 839
MySQL query to fetch date more recent than 14 days?
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 MoreFix error in MySQL “select ClientId,ClientName,ClientAge, from tablename”
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 MoreMySQL automatic string to integer casting in WHERE clause to fetch a specific id
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 MoreMake all column names lower case in MySQL with a single query
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 MoreHow to select multiple max values which would be duplicate values as well in MYSQL?
For this, use the join concept. Let us first create a −mysql> create table DemoTable1389 -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentMarks int -> ); Query OK, 0 rows affected (2.73 sec)Insert some records in the table using insert command. Here, we have inserted duplicate values as well −mysql> insert into DemoTable1389(StudentMarks) values(40); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable1389(StudentMarks) values(40); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1389(StudentMarks) values(68); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable1389(StudentMarks) values(78); Query OK, ...
Read MoreDisplay the contents of a VIEW in MySQL?
Following is the syntax −select * from yourViewName;Let us first create a table −mysql> create table DemoTable1388 -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(40) -> ); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1388(StudentName) values('Chris'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable1388(StudentName) values('Bob'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1388(StudentName) values('David'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1388(StudentName) values('Mike'); Query OK, 1 row affected (0.29 sec)Display all ...
Read MoreHow to obtain multiple rows in a single MySQL query?
To obtain multiple rows in a single MySQL query, use LIKE operator. Let us first create a table −mysql> create table DemoTable1385 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.90 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1385(Name) values('Chris Brown'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1385(Name) values('Adam Smith'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable1385(Name) values('Carol Taylor'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1385(Name) values('John ...
Read MoreInsert multiple rows in a single MySQL query
Let us first create a table −mysql> create table DemoTable1384 -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(20), -> StudentAge int -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command. Here, we are inserting multiple rows in a single query −mysql> insert into DemoTable1384(StudentName, StudentAge) values('Chris Brown', 21), ('David Miller', 22), -> ('Carol Taylor', 19), ('Adam Smith', 23); Query OK, 4 rows affected (0.11 sec) Records: 4 Duplicates: 0 Warnings: 0Display all records from the table using select statement −mysql> select * ...
Read MoreMySQL query to find the top two highest scores
For this, use aggregate function MAX(). Let us first create a table −mysql> create table DemoTable1383 -> ( -> Id int, -> PlayerScore int -> ); Query OK, 0 rows affected (0.90 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1383 values(200, 78); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1383 values(200, 89); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1383 values(200, 89); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1383 values(200, 87); Query OK, 1 row affected (0.29 sec) mysql> insert into ...
Read MoreHow to update a MySQL table by swapping two column values?
To swap two values in a column, use CASE WHEN statement. Let us first create a table −mysql> create table DemoTable1382 -> ( -> StudentName varchar(20) -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1382 values('John'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1382 values('Chris'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1382 values('Adam'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1382 values('Bob'); Query OK, 1 row affected (0.17 sec)Display all records from the table using ...
Read More