Chao: /* Content Update */

2020-01-29T05:47:53Z

Content Update

New page

== 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;
<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/

== 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
[[category: MYSQL]]

MYSQL Use - Revision history

Chao: /* Content Update */