joplin-notes/joplin notion extension/Web Development/sql/mysql.md

• MariaDB https://www.mariadbtutorial.com/getting-started/connect-to-mariadb/   After installing mariaDB use this command to setup database directory mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql do this rm -rf /var/lib/mysql/* to undo this initialization process   To start mariadbd at boot time you have to copy support-files/mariadb.service to the right place for your system U can do this in systemd init system with systemctl enable --now mariadb.service

  Two all-privilege accounts were created. One is root@localhost, it has no password, but you need to be system 'root' user to connect. Use, for example, sudo mysql The second is mysql@localhost, it has no password either, but you need to be the system 'mysql' user to connect. After connecting you can set the password, if you would need to be able to connect as any of these users with a password and without sudo   See the MariaDB Knowledgebase at https://mariadb.com/kb   You can start the MariaDB daemon with: cd '/usr' ; /usr/bin/mariadb-safe --datadir='/var/lib/mysql'   You can test the MariaDB daemon with mariadb-test-run.pl cd '/usr/mariadb-test' ; perl mariadb-test-run.pl   Please report any problems at https://mariadb.org/jira   The latest information about MariaDB is available at https://mariadb.org/.   Consider joining MariaDB's strong and vibrant community: https://mariadb.org/get-involved/

• More To load mysql shell in command prompt:

  mysql -u root -p Here root is owner_name. This will prompt for password which is ‘password’. or mysql -h localhost -P 3306 -u root -p test

  • To connect to remote mysql To connect to MariaDB on a specific host, you use the -h option:

    mysql -u [username] -p[password] -h [hostname] or to disable ssl check when connecting mysql --skip-ssl -u proj1_wishrocky -P 3306 -p94db734d1935c57ac05eab8987a959c98f074706 -h r77rn.h.filess.io proj1_wishrocky or (for newer versions) mysql --ssl-mode=DISABLED -u proj1_wishrocky -P 3306 -p94db734d1935c57ac05eab8987a959c98f074706 -h r77rn.h.filess.io proj1_wishrocky

  To get list of database:

  show databases;

  To create database

   drop database if exists database_name; 
   create database database_name; 
  

  To load database

  use hospitals;

  hospitals here is name of database

  Once database is loaded, to get list of tables:

  show tables;

  To get schema of tables:

  desc patients; or describe patients;

  where patients is name of table

  • To create table

   drop table if exists table_name; 
   create table table_name(....); 
  

  • Creating table

    create table project (
    	emp_id int not null,
        id int not null,
        name varchar(30),
        location varchar(40),
        primary key(emp_id)
    );
    

  • Adding Auto increment

    • SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

    -- To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:
    ALTER TABLE Persons AUTO_INCREMENT=100; 
    
    alter table employee 
    modify emp_id int not null auto_increment;
    

• Change mysql password if forget

  • Stop mysql service sudo systemctl stop mysql
  • start it in safe mode sudo mysqld_safe --skip-grant-tables --skip-networking
  • Then in other terminal type mysql -u root
  • when in mysql shell FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
  • close the terminal of the safe mode and start mysql service again sudo systemctl start mysql

• running sql script in mysql mysql -u username -p then SOURCE /path/to/your/script.sql; Alternatively, you can execute the script directly from the command line without entering the MySQL shell mysql -u username -p < /path/to/your/script.sql

• if else use in mysql

  SET @primaryKeyValue = 13; /* Your primary key value here */
  
    IF EXISTS (SELECT 1 FROM TableB WHERE foreignKeyColumn = @primaryKeyValue) THEN
    	-- Update the record in TableA
    	UPDATE TableA
    	SET someField = 'newValue' -- Set your field and value accordingly
    	WHERE primaryKeyColumn = @primaryKeyValue;
    ELSE
    	-- Delete the record in TableA
    	DELETE FROM TableA
    	WHERE primaryKeyColumn = @primaryKeyValue;
    END IF;
  
  

• creating and using procedures

     -- to replace semicolon with // for procedures
    DELIMITER //
  
    CREATE PROCEDURE if not EXISTS UpdateOrDeleteQuestion(IN primaryKeyValue INT)
    BEGIN
    	IF EXISTS (SELECT 1 FROM quiz_questions WHERE questionID = primaryKeyValue) THEN
    		-- Update the record in questions
    		UPDATE questions
    		SET isDeleted = TRUE
    		WHERE id = primaryKeyValue;
    	ELSE
    		-- Delete the record in questions
    		DELETE FROM questions
    		WHERE id = primaryKeyValue;
    	END IF;
    END //
  
    DELIMITER ;
  
    -- Now you can call the procedure with the primary key value
    CALL UpdateOrDeleteQuestion(2);
    -- to delete it
    drop procedure UpdateOrDeleteQuestion;
  

• to run mysql in docker first setup /var/lib/mysql by mysql_install_db --user=root --basedir=/usr --datadir=/var/lib/mysql then

    mysqld_safe --user=root  &
  
    # Wait for MySQL to initialize (this is important)
    sleep 5
  
    # other scripts to start (like backend services)
    # npm start
  

  in order to check if mysql is listening on localhost:3306 try this netstat -tuln | grep 3306

id	username	discriminator	avatar	accent_color	banner	theme_colors	pronouns	phone	desktop	mobile	premium	premium_type	bot	bio	system	nsfw_allowed	mfa_enabled	totp_secret	totp_last_ticket	created_at	premium_since	verified	disabled	deleted	email	flags	public_flags	purchased_flags	premium_usage_flags	rights	data	fingerprints	settingsIndex	webauthn_enabled	badge_ids	avatar_decoration_data	display_name_styles	collectibles	primary_guild
1471020127947448357	qwerty	3217	68d3b54bae93fdb947e64cb7df2584c5		c587b1be12f1491329d90359d89dc6bf				false	false	true	2	false		false	true	false			2026-02-11 11:18:48.735	2026-02-11 11:18:48.735	true	false	false	qwerty@email.com	0	0	0	0	875069521787904	{"hash":"$2b$12$I4fHr7GfGjbVttWsYFdZXue0uN2DMeSKbSAzu77JdZWS7fS4iJUsC","valid_tokens_since":"2026-02-11T05:48:48.735Z"}		1	false