help

1. mysql --verbose --help
2. 进入 mysql shell 中, 通过 help 查看帮助命令, For server side help, type help contents.

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...) for the next query to pick up.
ssl_session_data_print Serializes the current SSL session data to stdout or file

For server side help, type 'help contents'

common

• login
  • mysql -u [username] -p[password] -h [host] -P [port] [database]: 登陆. 注意 -p后面没有空格～
• enter
  • mysql -h 127.0.0.1 -u root -p: 使用密码登录 MySQL 服务器
  • EXIT;: 退出 MySQL 数据库服务器
• database
  • SHOW DATABASES;: 显示所有数据库
  • CREATE DATABASE <datebase>;: 创建数据库
  • DROP DATABASE <datebase>;: 删除数据库, 如果 database 名称包含了 -, 需要使用反引号 "`" 包围数据库名称
  • USE <datebase>;: 切换数据库
• tables
  • SHOW TABLES;: 显示数据库中的所有表
  • CREATE TABLE <tablename>: create table 表名(列名 1: 列的类型 列名 2: 列的类型 列名 3: 列的类型 )
  • DESC <tablename>: 显示表结构
  • SHOW CREATE TABLE <tablename>;: 显示创建表的语法
  • alert
    • ALTER TABLE <tablename> ADD COLUMN 列名 类型;: 添加列
    • ALTER TABLE <tablename> MODIFY COLUMN name VARCHAR(255);
• other
  • ROLLBACK: 执行回滚. 在开启非自动提交的时候有作用, 如果已经 commit 的事物, rollback 是没有效果的

SELECT

SELECT * FROM <tablename>;
SELECT * FROM <tablename> WHERE id = 1;
SELECT name FROM author WHERE name LIKE '%曾%';
# 查询近10天的交易数据
SELECT * FROM t_daily WHERE trade_date >= CURDATE() - INTERVAL 10 DAY AND trade_date <= CURDATE();
#
SELECT * FROM t_daily WHERE trade_date BETWEEN '2024-01-02' AND '2024-01-04';

#   查询, 排序
SELECT * FROM your_table ORDER BY column_to_sort DESC LIMIT 10;

key

• LIKE
• LIMIT
• DESC
• ASC: 升序

DELETE

• DELETE FROM <tablename> where name = 'squirrel';
• TRUNCATE TABLE <tablename>; TRUNCATE 是一个 DDL 操作,比 DELETE 更高效,因为它不记录每一行的删除操作
• 删除数据库内数据-保留表结构

UPDATE

UPDATE <tablename> SET name = 'squirrel' where owner = 'Diane';

INSERT

INSERT INTO `test`.`orm_user`(`name`,`password`,`salt`,`email`,`phone_number`) VALUES ('{name}', '{password}', '{salt}', '{email}', '{phone_number}');

# key 冲突
ON DUPLICATE KEY UPDATE:
  INSERT INTO t_daily (id, ts_code, trade_date, open, high, low, close, pre_close, changed, pct_chg, vol, amount) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE ts_code = VALUES(ts_code), trade_date = VALUES(trade_date), open = VALUES(open), high = VALUES(high), low = VALUES(low), close = VALUES(close), pre_close = VALUES(pre_close), changed = VALUES(changed), pct_chg = VALUES(pct_chg), vol = VALUES(vol), amount = VALUES(amount)