MYSQL Use

Operations

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

Database

• CREATE DATABASE demodb;

Tables

create tables

• CREATE TABLE table_name (column_name column_type);
• Example 1

CREATE TABLE test1 (
test VARCHAR(100)
);

• 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));

Data Type column_type

• VARCHAR(100) - 0-65535 字节 变长字符串
• DATE - 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
• INT - 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
• 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;

Column

Add

• ALTER TABLE `test` ADD COLUMN `type` VARCHAR(10) NOT NULL DEFAULT '0' COMMENT '0为添加1为编辑'
• ALTER TABLE `test` ADD COLUMN `type` VARCHAR(10)
• After certain column

ALTER TABLE `sum1`
ADD COLUMN `type` VARCHAR(100) NOT NULL DEFAULT '0' COMMENT '0为添加1为编辑' AFTER `column_name`;

• Simple after certain column

ALTER TABLE `sum1`
ADD COLUMN `type` VARCHAR(100) AFTER `name`;

Check

• show fields from sum1;

Change

• alter table sum1 change avg_buy avg_buy float(50,3);

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;

• Reference - http://www.mysqltutorial.org/mysql-add-column/

Cell

Content Update

Simple Update

• update sum1 SET type='D-B-' where name='BLV';
• update stock set date2='20190401' where id=29;

SUM

• 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);

Conditional Update by IF

• update sum1 set sum1.avg_buy= (select round(SUM(if(share>=1, price * share, 0)),2) / SUM(if(share>=1, share, 0)) from stock where sum1.name=stock.name);

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:

• mysql -u root -p
• show databases;
• use edwik;
• show tables;
• 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