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 George John
Page 71 of 79
How to implement WHILE LOOP with IF STATEMENT MySQL?
The following is an example to implement MySQL WHILE LOOP with IF statement. We are using in a stored procedureThe following is the query to create our stored procedure:mysql> DELIMITER // mysql> create procedure sp_getDaysDemo() -> BEGIN -> SELECT MONTH(CURDATE()) INTO @current_month; -> SELECT MONTHNAME(CURDATE()) INTO @current_monthname; -> SELECT DAY(LAST_DAY(CURDATE())) INTO @total_numberofdays; -> SELECT CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE)INTO @check_weekday; -> SELECT DAY(@check_weekday) INTO @check_day; -> SET @count_days = 0; -> SET @workdays = 0; ...
Read MoreHow can a query multiply 2 cells for each row in MySQL?
You can use multiplication operator (*) between two cells. The syntax is as followsSELECT yourColumnName1, yourColumnName2, yourColumnName1*yourColumnName2 as ‘anyVariableName’ from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table MultiplicationDemo -> ( -> FirstPrice int, -> SecondPrice int -> ); Query OK, 0 rows affected (0.63 sec)Now you can display all records from the table using insert command. The query is as followsmysql> insert into MultiplicationDemo values(10, 2); Query OK, 1 row affected (0.17 sec) mysql> insert into MultiplicationDemo values(4, 2); Query OK, ...
Read MoreMySQL convert timediff output to day, hour, minute, second format?
To understand the MySQL convert timediff output to day, hour, minute, and second format, you need to use CONCAT() from MySQL.Let us create a table. The query to create a table is as follows:mysql> create table convertTimeDifferenceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> StartDate datetime, -> EndDate datetime, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command. The query to insert record is as follows:mysql> insert into convertTimeDifferenceDemo(StartDate, ...
Read MoreHow to set sql_mode permanently in MySQL?
If you are using Windows Operating System, check your directory my.cnf or my.ini file.mysql> select @@datadir;The following is the output+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Reach the above location ‘C:\ProgramData\MySQL\MySQL Server 8.0\Data\”. The screenshot is as follows for my.cnf fileOpen the my.cnf file and write the sql_mode="TRADITIONAL". The syntax is as followssql_mode="TRADITIONAL".After that start your server once again.
Read MoreHow to remove special characters from a database field in MySQL?
You can remove special characters from a database field using REPLACE() function. The special characters are double quotes (“ “), Number sign (#), dollar sign($), percent (%) etc.The syntax is as follows to remove special characters from a database field.UPDATE yourTableName SET yourColumnName=REPLACE(yourColumnName, ’yourSpecialCharacters’, ’’);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table RemoveSpecialCharacterDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> PRIMARY Key(Id) -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using ...
Read MoreEquivalent of SQL Server IDENTITY Column in MySQL?
Equivalent of Microsoft SQL Server IDENTITY column in MySQL is AUTO_INCREMENT. The IDENTITY in SQL Server acts like AUTO_INCREMENT in MySQL.The syntax is as follows −CREATE TABLE yourTableName ( yourColumnName1 dataType NOT NULL AUTO_INCREMENT, yourColumnName2 dataType, . . . N, PRIMARY KEY(yourColumnName1) );In MySQL, you need to use primary key if your column is auto_increment otherwise MySQL will give an error. Look at the error −mysql> create table EquivalentOfIdentityInMySQL -> ( -> ProductId int NOT NULL AUTO_INCREMENT, -> ProductName varchar(30) -> ); ERROR 1075 (42000) − Incorrect table definition; ...
Read MoreRemove trailing zeros in decimal value with changing length in MySQL?
You can remove trailing zeros using TRIM() function. The syntax is as follows.SELECT TRIM(yourColumnName)+0 FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeTrailingZeroInDecimal -> ( -> Id int not null auto_increment, -> Amount decimal(5, 2), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.01 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into removeTrailingZeroInDecimal(Amount) values(405.50); Query OK, 1 row affected (0.22 sec) mysql> insert into removeTrailingZeroInDecimal(Amount) values(23.05); Query OK, ...
Read MoreHow to conduct an Accent Sensitive search in MySQL?
To conduct an Accent sensitive search in MySQL, we can use collation with utf8_bin. Here is the syntax to conduct accent sensitive search −yourColumName dataType collate utf8_bin;Apply the above syntax to conduct accent sensitive search. First, let us create a table −mysql> create table AccentSearchDemo -> ( -> Id varchar(100) collate utf8_bin -> ); Query OK, 0 rows affected (0.51 sec)Inserting three records into the table −mysql> insert into AccentSearchDemo values('John123'); Query OK, 1 row affected (0.31 sec) mysql> insert into AccentSearchDemo values('Smith123'); Query OK, 1 row affected (0.15 sec) mysql> ...
Read MoreWhat are some good tools to visualize MySQL database schema?
There are many tools to visualize MySQL database schema. Let us see some of them −SchemaSpyThis tool is based on java and can be used to analyze the metadata of MySQL database schema. Also use it to generate a visual representation of schema. A type of command line tool.The following are the featuresSupports most JDBC compliant DBMSGenerates ER diagram for foreign keysGenerates ER diagram for implied relationships (name, type) of a column matches a primary keyGenerates ER diagram for relationships based on rails naming conventionsShows column relationship and actionsShows routinesSchemaCrawlerThis is also a tool and an API that can be ...
Read MoreMySQL CAST as DATE?
There is no difference between cast as Date and date() function in MySQL.The syntax of both cast() and date() is as follows −cast(yourDateTimeColumnName as Date) date(yourDateTimeColumnName)Both functions internally call Item_date_typecast. To check both the functions, let us create a table. The query to create a table is as follows −mysql> create table CastAsDateDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> ArrivalTime datetime, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.71 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into ...
Read More