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
MySQL Articles
Page 301 of 355
MySQL query to order rows with value greater than zero?
Let us first create a table. Following is the query −mysql> create table gettingAndOrderingRowsDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Value int -> ); Query OK, 0 rows affected (1.35 sec)Following is the query to insert some records in the table using insert command −mysql> insert into gettingAndOrderingRowsDemo(Value) values(10); Query OK, 1 row affected (0.33 sec) mysql> insert into gettingAndOrderingRowsDemo(Value) values(13); Query OK, 1 row affected (0.32 sec) mysql> insert into gettingAndOrderingRowsDemo(Value) values(0); Query OK, 1 row affected (0.17 sec) mysql> insert into gettingAndOrderingRowsDemo(Value) values(20); Query OK, 1 ...
Read MoreHow to extract part of a URL in MySQL?
You need to use SUBSTRING_INDEX() function from MySQL to extract part of a URL. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, URL text ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(URL) values('https:\www.example.com\homepage'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable(URL) values('https:\www.onlinetest.com\welcome\indexpage'); Query OK, 1 row affected (0.12 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. Here, we can ...
Read MoreMySQL query to display databases sorted by creation date?
You can display databases sorted by creation date with ORDER BY clause. Following is the query to display all databases −mysql> show databases;This will produce the following output −+---------------------------+ | Database | +---------------------------+ | bothinnodbandmyisam | | business | | commandline | | customer_tracker_database | | customertracker | | database1 | | databasesample | | demo ...
Read MoreHow to sum the score of students with the same name in MySQL with ORDER BY?
For this, use ORDER BY along with GROUP BY clause. Let us first create a table with Student Name and Score −mysql> create table countRowValueDemo -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(20), -> StudentMathScore int -> ); Query OK, 0 rows affected (0.71 sec)Following is the query to insert records in the table using insert command −mysql> insert into countRowValueDemo(StudentName, StudentMathScore) values('Larry', 45); Query OK, 1 row affected (0.19 sec) mysql> insert into countRowValueDemo(StudentName, StudentMathScore) values('Mike', 56); Query OK, 1 row affected (0.16 sec) mysql> insert into ...
Read MoreHow to remove partial text from value in MySQL?
In order to remove partial text from value, you can use REPLACE() from MySQL. Following is the syntax −update yourTableName set yourColumnName = replace(yourColumnName ,'yourValue ', '' );Let us first create a table −mysql> create table removePartialTextDemo -> ( -> JavaVersionDetails varchar(100) -> ); Query OK, 0 rows affected (0.51 sec)Following is the query to insert some records in the table using insert command −mysql> insert into removePartialTextDemo values('Java Version 1.0'); Query OK, 1 row affected (0.50 sec) mysql> insert into removePartialTextDemo values('Java Version 1.1'); Query OK, 1 row affected (0.23 sec) mysql> insert into ...
Read MoreDisplay 3 decimal places in MySQL?
To display 3 digits after decimal, use TRUNCATE(). Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Value DECIMAL(10, 5) ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value) values(109.4567); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable(Value) values(15.9875); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable(Value) values(1234.2346789); Query OK, 1 row affected, 1 warning (0.14 sec)Following is the query to display all records from the table using select statement −mysql> select ...
Read MoreHow do I insert a NULL value in MySQL?
To insert a NULL value, you can use UPDATE command. Following is the syntax −UPDATE yourTableName SET yourColumnName=NULL;Let us first create a table −mysql> create table insertNullValue -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ClientName varchar(100), -> ClientCountryName varchar(20) -> ); Query OK, 0 rows affected (0.54 sec)Following is the query to insert some records in the table using insert command −mysql> insert into insertNullValue(ClientName, ClientCountryName) values('Larry', 'US'); Query OK, 1 row affected (0.19 sec) mysql> insert into insertNullValue(ClientName, ClientCountryName) values('David', 'AUS'); Query OK, 1 row affected (0.09 sec) ...
Read MoreHow to select the table with the greatest number of columns in MySQL?
You can use INFORMATION_SCHEMA.COLUMNS to get the table with the greatest number of columns. The syntax is as follows −SELECT TABLE_NAME, COUNT(*) AS anyAliasName FROM INFORMATION_SCHEMA.COLUMNS GROUP BY TABLE_NAME ORDER BY yourAliasName DESC LIMIT 1;Following is the query to select the table that has the greatest number of columns. We are getting this result because we have set the count to DESC and used GROUP BY TABLE_NAME −mysql> SELECT TABLE_NAME, COUNT(*) as TOTAL_COUNT FROM INFORMATION_SCHEMA.COLUMNS GROUP BY TABLE_NAME ORDER BY TOTAL_COUNT DESC LIMIT 1;This will produce the following output −+-----------------------------------+-------------+ | TABLE_NAME ...
Read MoreHow to delete all rows except some in MySQL?
You can use NOT IN operator for the rows you do not want to delete. Following is the syntax −delete from yourTableName where yourColumnName NOT IN(‘yourValue1’, ‘yourValue2’, ‘yourValue3’, .........N);Let us first create a table −mysql> create table deleteAllRowsWithCondition -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.84 sec)Following is the query to insert some records in the table using insert command −mysql> insert into deleteAllRowsWithCondition(Name) values('Larry'); Query OK, 1 row affected (0.14 sec) mysql> insert into deleteAllRowsWithCondition(Name) values('John'); Query OK, 1 row affected ...
Read MorePerform complex MySQL insert by using CONCAT()?
To perform complex MySQL insert, you can use CONCAT() function. Let us see an example and create a table with StudentId and StudentFirstName.After that, complex MySQL insert will be performed and 'Web Student’ text will get inserted for every value and unique StudentId will get concatenated.The query to create first table is as follows −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentFirstName varchar(20) ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentFirstName) values('John'); Query OK, 1 row affected (0.16 sec) mysql> insert ...
Read More