Difference between revisions of "MYSQL"

From ElectroDragon
Jump to navigation Jump to search
(Redirected page to Category:MYSQL)
(Tag: New redirect)
 
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
== Install ==
+
#redirect [[category: MYSQL]]
* 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;
 
<br>
 
 
 
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;
 
<br>
 
ALTER TABLE vendors
 
ADD COLUMN last_buy VARCHAR(15);
 
<br>
 
ALTER TABLE stock
 
ADD COLUMN last_buy Datetime;
 
<br>
 
alter table sum1
 
add column last_price float(50,3);
 
<br>
 
* drop
 
alter table stock
 
drop column last_buy;
 
* Reference - http://www.mysqltutorial.org/mysql-add-column/
 
 
 
== 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
 
[[category: Web]]
 

Latest revision as of 12:45, 28 January 2020

Redirect to: