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
MySQLi Articles
Page 262 of 341
Searching BETWEEN dates stored as varchar in MySQL?
You can search between dates stored as varchar using STR_TO_DATE(). The syntax is as follows −select *from yourTableName where STR_TO_DATE(LEFT(yourColumnName, LOCATE('', yourColumnName)), '%m/%d/%Y') BETWEEN 'yourDateValue1' AND 'yourDateValue2’;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SearchDateAsVarchar -> ( -> Id int NOT NULL AUTO_INCREMENT, -> ShippingDate varchar(100), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.99 sec)Insert some records in the table using INSERT command. The query is as follows −mysql> insert into SearchDateAsVarchar(ShippingDate) values('6/28/2011 9:58 AM'); Query OK, 1 ...
Read MoreSet the MySQL primary keys auto increment to be unlimited (or incredibly huge)?
You can use BIGINT but this is not unlimited but you can use large number of primary keys auto increment using it. The syntax is as follows −yourColumnName BIGINT NOT NULL AUTO_INCREMENT;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table LargeAutoIncrement -> ( -> Id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY -> ); Query OK, 0 rows affected (0.78 sec)Now in this table you can store large number like 9223372036854775807 i.e. for primary key auto increment.Let us insert ...
Read MoreCreate a column on my table that allows null but is set by default to empty (not null)?
You need to use default keyword for this. The syntax is as follows −alter table yourTableName add yourColumnName yourDataType NULL Default '';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table AllowNullDefaulNotNullDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserName varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.65 sec)Let us add a new column that allow NULL value but default value set to NOT NULL. The query is as follows −mysql> alter table AllowNullDefaulNotNullDemo add UserAddress varchar(20) ...
Read MoreGet the strings in the table records that ends with numbers?
You need to use REGEXP for this. The syntax is as follows −select *from yourTableName where yourColumnName REGEXP '[[:digit:]]$';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table StringEndsWithNumber -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserId varchar(20), -> UserName varchar(20) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into StringEndsWithNumber(UserId, UserName) values('123User', 'John'); Query OK, 1 row affected (0.18 sec) mysql> insert ...
Read MoreGet the date/time of the last change to a MySQL database?
You can get the date/time of the last change to a MySQL database with the help of INFORMATION_SCHEMA.TABLES. The syntax is as follows −SELECT update_time FROM information_schema.tables WHERE table_schema = 'yourDatabaseName’' AND table_name = 'yourTableName’;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table TblUpdate -> ( -> Id int not null auto_increment primary key, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into TblUpdate(Name) ...
Read MoreWhat are the different quote marks of MySQL?
You can use backticks and single quotes in MySQL. The backtick can be used around the column name and table name while single quotes can be used for the column name values.Let us take an example for both the quote marks. To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table QuotesDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(20), -> UserAge int -> ); Query OK, 0 rows affected (2.53 sec)Insert some records in the table using ...
Read MoreMySQL DateTime Now()+5 days/hours/minutes/seconds?
To update the current date and time to 5 days, you need to use the Now() + 5. That would update the entire date-time i.e. days, hour, minutes and seconds. To understand this, let us create a table. The query to create a table is as follows −mysql> create table UserInformationExpire -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(10), -> UserInformationExpireDateTime datetime not null -> ); Query OK, 0 rows affected (0.83 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> ...
Read MoreMySQL If statement with multiple conditions?
You can use if statement in a stored procedure with multiple conditions with the help of AND or OR operator. The syntax is as follows −DECLARE X int; DECLARE Y int; SET X = value1; SET Y = value2; IF ( (X < Y AND X > value1 AND Y >value2) OR X! = anyValueToCompare) THEN yourStatement; ELSE yourStatement; END IFNow to understand the above syntax, let us create a stored procedure. The query to create a stored procedure is as follows −mysql> create procedure SP_IFELSEDEMO() -> BEGIN -> DECLARE X int; -> DECLARE Y ...
Read MoreSum if all rows are not null else return null in MySQL?
You can achieve this with the help of GROUP BY HAVING clause. The syntax is as follows −SELECT yourColumnName1, SUM(yourCoumnName2) from yourTableName GROUP BY yourColumnName1 HAVING COUNT(yourCoumnName2) = COUNT(*);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SumDemo -> ( -> Id int, -> Amount int -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SumDemo values(1, 200); Query OK, 1 row affected (0.22 ...
Read MoreMySQL "order by" inside of "group by"? Is it possible?
Instead of using ORDER BY inside GROUP BY, you can use the MAX() aggregate function.The syntax is as follows −SELECT yourNameColumnName, MAX(yourRankColumnName) FROM yourTableName GROUP BY yourNameColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table MaxDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(20), -> UserRank int -> ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into MaxDemo(UserName, UserRank) values('Larry', 2); Query ...
Read More