Difference between revisions of "MYSQL"

From ElectroDragon
Jump to navigation Jump to search
(Workflow)
Line 111: Line 111:
 
import sql
 
import sql
 
* docker exec -i some-mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /web/import/blogdb.sql
 
* docker exec -i some-mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /web/import/blogdb.sql
[[category: Website]]
+
[[category: Web]]

Revision as of 09:46, 1 December 2019

Install

  • apt-get install mysql-server
  • connector: python -m pip install mysql-connector
  • enter into mysql: mysql -u root -p

Setup

  • Allow remote access: nano /etc/mysql/my.cnf: bind-address = 127.0.0.1 change to bind-address = 0.0.0.0
  • Or just comment is # bind-address = 0.0.0.0
  • sudo ufw allow mysql
  • create new remote user:
CREATE USER 'hechao'@'localhost' IDENTIFIED BY 'password_here';
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;


CREATE USER 'hechao'@'%' IDENTIFIED BY 'password_here';
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'WITH GRANT OPTION;
  • OR
grant all privileges on *.* to 'root'@'%' identified by 'password';
flush privileges;
  • Where first * is database, second * is tables, monty is login name, % for any ip address

Operations

  • Update: UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';

Database

  • CREATE DATABASE demodb;

create tables

  • normal: CREATE TABLE stock (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), date DATE(255), share INT(100));
  • datetime, varchar, float, int: CREATE TABLE stock (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), date2 Datetime, price FLOAT(50,3), share INT(100));
  • CREATE TABLE sum1 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), total_share INT(100), total_cost FLOAT(50,3), avg_price FLOAT(50,3));

show tables

  • show tables;

table insert

  • insert into stock(name, date2, share) values ('DBC', '19600101', 1);
  • insert into stock(name, date2, share) values ('DBC', '1960-01-01', 2);

example data

  • insert into stock(name, date2, share, price) values ('DBC', now(), 1, 14.3);
  • insert into stock(name, date2, share, price) values ('DBC', now(), 2, 14.87);

ignore

  • insert ignore into sum1 (name) values ('test2')

table drop

  • drop table stock;

table select

  • SELECT * FROM stock.stock;
  • SELECT User, Host, authentication_string FROM mysql.user;
INSERT INTO sum2(name, total_share, total_cost, avg_price)
SELECT name, 
SUM(share) 'total_share', 
round(SUM(price * share),2) 'total_cost', 
round(SUM(price * share)/SUM(share),2) 'avg_price' 
FROM stock 
GROUP BY name;

table delete

  • DELETE FROM stock WHERE name = 'DBC';
  • DELETE FROM stock WHERE ID = 3;

Cell

Content Update

  • update stock set date2='20190401' where id=29;
  • update stock, sum1 set sum1.total_share= (select SUM(share) from stock where sum1.name=stock.name);
  • update stock, sum1 set sum1.total_cost= (select round(SUM(price * share),2) from stock where sum1.name=stock.name);
  • update stock, sum1 set sum1.avg_price= (select round(SUM(price * share)/SUM(share),2) from stock where sum1.name=stock.name);

Column Update

  • Add column
ALTER TABLE vendors
ADD COLUMN phone VARCHAR(15) AFTER name;


ALTER TABLE vendors
ADD COLUMN last_buy VARCHAR(15);


ALTER TABLE stock 
ADD COLUMN last_buy Datetime;


alter table sum1 
add column last_price float(50,3);


  • drop
alter table stock 
drop column last_buy;

Other operations

  • Make change: FLUSH PRIVILEGES;
  • service mysql restart
  • systemctl start mysql
  • systemctl enable mysql


Output - dump

  • mysqldump -u root -p test>test.sql

Input

  • mysql -u root -p
  • create database stock
  • exit
  • mysql -u root -p stock < stock.sql

Workflow

Example for mediawiki:

  • show databases;
  • use edwik;
  • SHOW COLUMNS FROM page;
  • select * from page where page_title='RISC-V';
  • delete from page where page_id=4975;

Docker

dump sql

  • docker exec db sh -c 'exec mysqldump -uroot -ppassword wordpress' > /web/import/test2.sql

import sql

  • docker exec -i some-mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /web/import/blogdb.sql