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 343 of 355
MySQL GROUP BY with WHERE clause and condition count greater than 1?
To understand the group by with where clause, let us create a table. The query to create a table is as follows −mysql> create table GroupByWithWhereClause -> ( -> Id int NOT NULL AUTO_INCREMENT, -> IsDeleted tinyint(1), -> MoneyStatus varchar(20), -> UserId int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.57 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into GroupByWithWhereClause(IsDeleted, MoneyStatus, UserId) values(0, 'Undone', 101); Query OK, 1 row affected (0.17 sec) mysql> insert into GroupByWithWhereClause(IsDeleted, MoneyStatus, UserId) ...
Read MoreUsing “WHERE binary” in SQL?
The binary keyword can be used after WHERE clause to compare a value with exact case sensitive match.The following is an example −Case 1 − Case insensitive matchThe query is as follows −mysql> select 'joHN'='JOHN' as Result;The following is the output −+--------+ | Result | +--------+ | 1 | +--------+ 1 row in set (0.00 sec)In the above sample output, the result is true while we know joHN and JOHN are two different words. This is not a case sensitive match.Case 2 − If you want case sensitive match, use the binary keyword.The query is ...
Read MoreORDER BY alphabet first then follow by number in MySQL?
You need to use regular expression with ORDER BY clause. The syntax is as follows:SELECT *FROM yourTableName ORDER BY IF(yourColumnName RLIKE '^[a-z]', 1, 2), yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table AlphabetFirstThenNumberDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.95 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into AlphabetFirstThenNumberDemo(Name) values('John'); Query OK, 1 row affected (0.37 sec) mysql> insert ...
Read MoreHow to select max of mixed string/int column in MySQL?
To select max of mixed string/int column, you need to use substring() function. The syntax is as follows:SELECT MAX(CAST(SUBSTRING(yourColumnName, 4, length(yourColumnName)-3) AS UNSIGNED)) AS anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table StringIntMixHighestDemo -> ( -> InvoiceId int NOT NULL AUTO_INCREMENT, -> InvoiceNumber varchar(20), -> PRIMARY KEY(InvoiceId) -> ); Query OK, 0 rows affected (0.65 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV129'); Query OK, 1 row ...
Read MoreSelect multiple sums with MySQL query and display them in separate columns?
To select multiple sum columns with MySQL query and display them in separate columns, you need to use CASE statement. The syntax is as follows:SELECT SUM( CASE WHEN yourColumnName1=’yourValue1’ THEN yourColumnName2 END ) AS yourSeparateColumnName1, SUM( CASE WHEN yourColumnName1=’yourValue2’ THEN yourColumnName2 END ) AS yourSeparateColumnName2, SUM( CASE WHEN yourColumnName1=’yourValue3’ THEN yourColumnName2 END ) AS yourSeparateColumnName3, . . . N FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table selectMultipleSumDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> PlayerName varchar(20), -> PlayerScore int, ...
Read MoreHow to delete last record (on condition) from a table in MySQL?
To delete last record (on condition) from a table, you need to use ORDER BY DESC with LIMIT 1. The syntax is as follows:DELETE FROM yourTableName WHERE yourColumnName1=yourValue ORDER BY yourColumnName2 DESC LIMIT 1;The above syntax will delete last record (on condition) from a table. It sorts the column in descending order and choose the first element to delete.To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table UserLoginTable -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserId int, -> UserLoginDateTime datetime, -> PRIMARY ...
Read MorePopulating a table from query results in MySQL?
To populate a table from query results, use the following syntax:INSERT yourTableName(yourColumnName1, yourColumnName2, yourColumnName3, ..........N) SELECT yourColumnName1, yourColumnName2, yourColumnName3, ..........N FROM yourAnotherTableName;To understand the above syntax, let us create a table. The first table is as follows with some records. The query to create a table is as follows:mysql> create table PopulateTableDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> Amount int, -> ArrivalDateTime datetime, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.68 sec)Now you can insert some records in the table using insert command. The ...
Read MoreShortcomings of mysql_real_escape_string?
The shortcoming of mysql_real_escape_string is as follows − It has main shortcoming in the modern API while we are making prepared statement. This has another shortcoming for every possible attack when you will typecast. Let us see the typecast syntax in MySQL − (TypeCast)mysql_real_escape_string($_REQUEST['anyColumnName'])); In the above syntax, we are typecasting, but in this case, it is not safer for every possible attack. The other cases include the following − It is not type safe. It is not for injection attack.
Read MoreHow to add super privileges to MySQL database?
Firstly, check the MySQL database with DESC command. mysql>DESC mysql.db; The following is the output. +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | ...
Read MoreIs the primary key automatically indexed in MySQL?
Yes, primary key is automatically indexed in MySQL because primary key, index, etc gets stored into B-trees. All engines including InnoDB as well as MyISAM automatically supports the primary key to be indexed. The primary key is implicitly indexed in InnoDB, MyISAM, and other engines. Let us create a table with primary key − mysql> create table DemoIndex -> ( -> Id int not null, -> primary key(Id) -> ); Query OK, 0 rows affected (1.21 sec) In the above table, Id is implicitly indexed.
Read More