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
Articles by AmitDiwan
Page 821 of 840
How to display only the column values whose sum is less than 150 in MySQL? Arrange the result in descending order
For this, you can use subquery. Let us first create a table −mysql> create table DemoTable844( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Amount int ); Query OK, 0 rows affected (0.95 sec)Insert some records in the table using insert command −mysql> insert into DemoTable844(Amount) values(80); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable844(Amount) values(100); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable844(Amount) values(60); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable844(Amount) values(40); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable844(Amount) values(150); Query OK, 1 row ...
Read MoreSet value only for NULL values in a MySQL table
Use IFNULL to check for NULL values and set a value using the SET command. Let us first create a table −mysql> create table DemoTable817(Value int); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable817 values(10); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable817 values(null); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable817 values(20); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable817 values(null); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from ...
Read MoreDisplay all grants for user in MySQL
Use INFORMATION_SCHEMA.SCHEMA_PRIVILEGES to display all grants for a user −select *from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES;Let us implement the above syntax to show all grants for a user −mysql> select *from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES;This will produce the following output −+-----------------------------+---------------+--------------------+-------------------------+--------------+ | GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE | +-----------------------------+---------------+--------------------+-------------------------+--------------+ | 'mysql.sys'@'localhost' | def | sys | TRIGGER | NO ...
Read MoreSet particular value of column without using update and while inserting values in MySQL
Here, we will see an example wherein we are inserting dates and updating them while using the INSERT query.Let us first create a table −mysql> create table DemoTable816(DueDate datetime); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using the insert command. Here is the query to add (minutes/hours/days/months/ years) to date when performing INSERT −mysql> insert into DemoTable816 values(date_add(now(), interval 3 minute)); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable816 values(date_add('2018-01-21 00:00:00', interval 3 Hour)); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable816 values(date_add('2016-11-11 12:40:00', interval 3 Day)); Query OK, ...
Read MoreExtract gender values as a string when it is stored in the table as a Boolean in MySQL
Let us first create a table −mysql> create table DemoTable815(Gender BOOLEAN); Query OK, 0 rows affected (0.84 sec)Insert some records in the table using insert command −mysql> insert into DemoTable815 values(true); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable815 values(false); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable815 values(false); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable815 values(true); Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −mysql> select *from DemoTable815;This will produce the following output −+--------+ | Gender | +--------+ | 1 ...
Read MoreGroup concatenate the last name from a MySQL column and set a condition to display limited records
Use GROUP_CONCAT() to perform group concatenation and set a condition. Let us first create a table −mysql> create table DemoTable813( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserFirstName varchar(100), UserLastName varchar(100) ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable813(UserFirstName, UserLastName) values('Adam', 'Smith'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable813(UserFirstName, UserLastName) values('Chris', 'Brown'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable813(UserFirstName, UserLastName) values('David', 'Miller'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable813(UserFirstName, UserLastName) values('Carol', 'Taylor'); Query ...
Read MoreHow to use MySQL LIKE to create a search system and display results on the basis of keyword?
To display results on the basis of keyword, use the LIKE operator as in the following syntax −select *from yourTableName where yourColumnName LIKE '%yourValue%';Let us first create a table −mysql> create table DemoTable812(Comments text); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable812 values('Good Looking'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable812 values('Awesome'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable812 values('Looking Gorgeous'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable812 values('Good Luck'); Query OK, 1 row affected (0.20 sec) ...
Read MoreIs INNODB enabled by default in MySQL?
Yes, it is enabled by default from MySQL version 4.0. Here, we are using MySQL version 8.0.1 −mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec)Let us now check the my.ini wherein the default engine type InnoDB is visible −Let us first create two table. One of them would be set with engine type, whereas the other one won’t be set with the engine type.The first table −mysql> create table DemoTable1(Id int NOT NULL AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.80 sec)The second table set with ENGINE ...
Read MoreCan I insert two or more rows in a MySQL table at once?
Yes, we can insert two or more rows in a table at once. Following is the syntax −insert into yourTableName(yourColumnName1, yourColumnName2) values(yourValue1, yourValue2), (yourValue1, yourValue2), .........N;Let us first create a table −mysql> create table DemoTable811( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(100), StudentAge int ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable811(StudentName, StudentAge) values('Chris', 21), ('Robert', 22), ('David', 20), ('Bob', 19), ('Carol', 23); Query OK, 5 rows affected (0.14 sec) Records: 5 Duplicates: 0 Warnings: 0Display all records from the table using select ...
Read MoreMySQL query to count rows with a specific column?
Let us first create a table −mysql> create table DemoTable841( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Value varchar(100) ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable841(Value) values('X'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable841(Value) values('Y'); Query OK, 1 row affected (0.39 sec) mysql> insert into DemoTable841(Value) values('Y'); Query OK, 1 row affected (1.62 sec) mysql> insert into DemoTable841(Value) values('Z'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable841(Value) values('X'); Query OK, 1 row affected (0.81 sec) mysql> insert into ...
Read More