Coding

## Install MySQL Linux $ sudo apt install mysql-server $ sudo service mysql status $ sudo service mysql start $ sudo service mysql stop $ sudo mysql_secure_installation # password $ sudo mysql -u root $ sudo mysql -u root -p $ mysql -u user -p ## Install MySQL macOS $ /usr/local/mysql/bin/mysql -u root # access mysql without root password $ /usr/local/mysql/bin/mysql -u root -p # access mysql with root password $ sudo /usr/local/mysql/support-files/mysql.server start $ sudo /usr/local/mysql/support-files/mysql.server stop $ sudo /usr/local/mysql/support-files/mysql.server status $ sudo /usr/local/mysql/support-files/mysql.server start --skip-grant-tables # initiate safe mode to change root password

>SHOW DATABASES; >SELECT user FROM mysql.user; -- show users >DROP USER 'jeffrey'@'localhost'; -- delete user >ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass'; -- change password >CREATE DATABASE wordpress; >CREATE USER 'wpuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; >GRANT ALL ON wordpress.* TO 'wpuser'@'localhost'; >FLUSH PRIVILEGES; >SHOW CREATE DATABASE wordpress; -- returns the database name and the character set >DESCRIBE wordpress; -- describe table structure >EXPLAIN wordpress; -- same as describe >USE database_name; >SHOW TABLES; >SHOW TABLES FROM database_name; >SHOW COLUMNS FROM database_name; >CREATE TABLE table_name ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, tipo VARCHAR(255), estado VARCHAR(255), PRIMARY KEY (id) ); >CREATE TABLE products ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, created_by INT NOT NULL, brand VARCHAR(50) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (created_by) REFERENCES users(id) ); >CREATE TABLE productos ( cod_producto INT (15) PRIMARY KEY AUTO_INCREMENT, nombre VARCHAR (22), precio INT (100) ) AUTO_INCREMENT = 100; -- contador inicia en 100 >ALTER TABLE products ADD CONSTRAINT fk_id_categories FOREIGN KEY (id_categories) REFERENCES categories (id) ON DELETE CASCADE/RESTRICT ON UPDATE CASCADE/RESTRICT; >ALTER TABLE account MODIFY COLUMN id int AUTO_INCREMENT; >ALTER TABLE account ADD `lastUpdated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP; >ALTER TABLE notas ADD FOREIGN KEY (id_estudiante) REFERENCES alumnos (id_estudiante); -- add column >ALTER TABLE alumno ADD COLUMN id_salon INT NOT NULL; -- delete column >ALTER TABLE alumno DROP COLUMN grado; -- rename table >RENAME TABLE product TO products; -- rename column >ALTER TABLE concepts RENAME COLUMN income TO income_outcome; -- move columns order ALTER TABLE table_name MODIFY password varchar(20) AFTER id; >ALTER TABLE table_name ADD COLUMN create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ADD COLUMN update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; >INSERT INTO table1 (col1, col2) VALUES ('val1', 'val2'); >SELECT * FROM table1 WHERE col1 = 'val1' AND col2 = 'val2'; >UPDATE table1 SET col1 = 'val1', col2 = 'val2' WHERE col3 = 'val1'; >DELETE FROM table1 WHERE col1 = 'val1'; >CREATE INDEX email_index ON users(email); >SELECT email FROM users; >SELECT email FROM users WHERE id = 2; >SELECT email FROM users LIMIT 100; >SELECT email, id FROM users ORDER BY id ASC LIMIT 100; >SELECT email, id, country FROM users WHERE country = 'US' ORDER BY id DESC LIMIT 2; >SELECT * FROM account; >SELECT * FROM account WHERE id = 1; >SELECT * FROM account WHERE account = 'Visa'; >SELECT * FROM account WHERE id = 1 AND account = 'Chequing'; >SELECT * FROM account LIMIT 1; >SELECT * FROM account WHERE edad > 15; >SELECT * FROM account WHERE edad <= 15 AND email = 'correo@gmail.com'; >SELECT * FROM account WHERE edad = 20 OR email = 'correo@gmail.com'; >SELECT * FROM account WHERE email != 'correo@gmail.com'; >SELECT * FROM account WHERE edad BETWEEN 15 AND 30; >SELECT * FROM account WHERE email LIKE '%gmail%'; >SELECT * FROM account WHERE email LIKE '%gmail.com'; >SELECT * FROM account WHERE email LIKE 'usersomething%'; >SELECT * FROM account ORDER BY edad ASC; >SELECT * FROM account ORDER BY edad DESC; >SELECT MAX(edad) AS mayor FROM account; >SELECT MIN(edad) AS menor FROM account; >SELECT count(id), brand FROM products GROUP BY brand; >UPDATE account SET account = 'Ethereum' WHERE account = 'Chequing'; >UPDATE account SET column1 = valor1, column2 = valor2 WHERE id = 5; >DELETE FROM account_table WHERE id = 2; >INSERT INTO account_table (account_column) VALUES ('Chequing'); >INSERT INTO products (name, created_by, brand) VALUES ('ipad', 1, 'apple'), ('iphone', 1, 'apple'), ('apple watch', 2, 'apple'), ('monitor', 3, 'acer'), ('think centre', 2, 'lenovo'); -- delete table >DROP TABLE products; -- show globals >SHOW GLOBAL VARIABLES LIKE 'PORT'; >SHOW WARNINGS; >SHOW NOW(); >EXIT; -- Index (for search) -- left, right, inner, and cross join ??? -- group by, having, etc -- MySQL Script generated by MySQL Workbench CREATE SCHEMA IF NOT EXISTS `finances_eer`; USE `finances_eer`; CREATE TABLE IF NOT EXISTS `finances_eer`.`concepts` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NOT NULL, `income_outcome` VARCHAR(1) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `finances_eer`.`accounts` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NOT NULL, `balance` FLOAT NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `finances_eer`.`transactions` ( `id` INT NOT NULL AUTO_INCREMENT, `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `id_account` INT NOT NULL, `id_concept` INT NOT NULL, `description` VARCHAR(100) NOT NULL, `outcome` FLOAT NULL, `income` FLOAT NULL, PRIMARY KEY (`id`), INDEX `id_account_idx` (`id_account` ASC) VISIBLE, INDEX `id_concept_idx` (`id_concept` ASC) VISIBLE, CONSTRAINT `id_account` FOREIGN KEY (`id_account`) REFERENCES `finances_eer`.`accounts` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `id_concept` FOREIGN KEY (`id_concept`) REFERENCES `finances_eer`.`concepts` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

Numeric: INT //enteros FLOAT //decimales Alfanumerics: CHAR //255 characteres VARCHAR (longitud caracteres) //65,535 characteres Fecha y Hora: DATE = AA-MM-DD TIME = HH:MM:SS DATATIME = AAAA-MM-DD HH:MM:SS TIMESTAMP = AA-MM-DD HH:MM:SS

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf bind-address = * #replace local host with wildcard > RENAME USER 'sammy'@'localhost' TO 'sammy'@'remote_server_ip';