Page Comparison

...

Key Points

References

...

https://tableplus.com/blog/2018/11/what-is-the-default-username-password-in-mysql.html

Access server environment to reset

You must have root access on the server to reset MySQL's root password.

if remote, login with ssh

You must run the commands in the following steps as the root user. Therefore, you can either log in directly as the root user (which is not recommended for security reasons), or use the su or sudo commands to run the commands as the root user.

Stop the MySQL service or daemon first

service mysql stop
or 
mysqld stop

...

MariaDB vs MySQL Key Differences Comparing MySQL 80 with MariaDB 105.pdf gdrv

The goal of this article is to evaluate and highlight the main similarities and differences between the MySQL Server Database and the MariaDB Server Database. We’ll look into performance aspects, security, main features, and list all aspects which need to be considered before choosing the right database for your requirements.

For MySQL, we can see names such as Facebook, Github, YouTube, Twitter, PayPal, Nokia, Spotify, Netflix and more.

For MariaDB, we can see names such as Redhat, DBS, Suse, Ubuntu, 1&1, Ingenico, Gaming Innovation Group, BlaBla Cars and more.

Oracle DB compare to MySQL DB 

...

The conditional logic of the CASE expression works hand in hand with the aggregate function to only get a total of the prod_name that you want in each column. Since we have 3 products, then you’d write 3 sum(case... expressions for each column. Here is a demo on SQL Fiddle. This query will give a result of:

MySQL procedure example

...

MySQL procedure example

Create a procedure with a parm and a result set

-- prepared stmt example 
set @aproduct = 'Pants';
prepare stmt1 from '
select p.* from products as p
where p.prod_name like ?';
execute stmt1 using @aproduct;

deallocate prepare stmt1;

-- procedure example - normally use create definer for security by roles
delimiter ;;
CREATE PROCEDURE `zListProducts`(IN increment INT, OUT acounter INT)
DETERMINISTIC
-- SQL SECURITY INVOKER
BEGIN
DECLARE counter INT DEFAULT 0;
WHILE counter < 10 DO
SET counter = counter + increment;
END WHILE;
SET acounter = counter;
END;;
delimiter ;

Call a procedure with a parm and a result set

...

Call a procedure with a parm and a result set

...

SET @increment = 2;
SET @acounter = 22;
select @increment;

CALL `zListProducts`(@increment, @acounter);

select @acounter;

MySQL dynamic statements using SQL varialbles @myvar etc

Temporal DB Tables in MySQL

https://en.wikipedia.org/wiki/Temporal_database

A temporal database stores data relating to time instances. It offers temporal data types and stores information relating to past, present and future time. Temporal databases could be uni-temporal, bi-temporal or tri-temporal.

More specifically the temporal aspects usually include valid time, transaction time or decision time.

• Valid time is the time period during which a fact is true in the real world.
• Transaction time is the time at which a fact was recorded in the database.
• Decision time is the time at which the decision was made about the fact.

MariaDB of a temporal table with application and system time

If your application needs to utilize both system-versioning and application-time periods, you can combine them in bitemporal tables. The earlier instructions for maintaining each type of temporal data applies.

CREATE TABLE coupons_new (
   id INT UNSIGNED,
   name VARCHAR(255),
   date_start DATE,
   date_end DATE,
   PERIOD FOR valid_period(date_start, date_end)
) WITH SYSTEM VERSIONING;

This creates a table with two time periods, the arbitrary one based on the two DATE columns for application-time periods, and the internal SYSTEM_TIME for system-versioning.

You may find this useful in applications where you need an audit trail for data change while still needing to perform operations dependent on application-time periods.

Step-by-step guide for Example

...