====== MySQL ======
* Official site: [[http://mysql.com|mysql.com]]
===== MariaDB =====
* Official site: [[https://mariadb.org/|mariadb.org]]
* 与 mysql 的差异: https://www.skeema.io/blog/2023/05/10/mysql-vs-mariadb-schema/
* 在root下刚安装完时,即可直接用 ''mysql'' 命令进入, mariadb 会自动检查当前系统用户是否为root.
* 执行 ''sudo mysql_secure_installation'' 脚本来进行安全设置
* Ubuntu20.04 后 MariaDB 默认使用 ''unix_socket'' 插件来认证root用户,改回 ''mysql_native_password'' 插件的方式:
sudo su # get root access
mysql # connect db
update mysql.user set plugin = 'mysql_native_password' where User = 'root';
flush privileges;
===== Tools =====
* Mac 下好用的工具:[[https://www.sequelpro.com/|Sequal Pro]]
* [[https://github.com/slashbaseide/slashbase|slashbase]]: 一个基于浏览器的数据库操作 IDE,有点类似 PHPMyAdmin,但使用 Go 语言编写,并且支持 PostgreSQL 和 MongoDB。
===== Manual =====
* [[https://dev.mysql.com/doc/refman/5.7/en/sql-statements.html|sql syntax]]
==== Connect ====
* ''mysql -h host -u username -p password -P port''
* default port,no password: ''mysql -h host -u username -p''
* disconnect: ''mysql> quit''
==== Create User ====
* create user:
mysql -h host -u username -p
mysql> use mysql;
mysql> insert into user(Host,User,Password) values("localhost","newname",password("newpassword"));
mysql> flush privileges;
==== Database ====
* Create: ''create database newdb;''
* List databases: ''show databases;''
* Use databases: '' use newdb;''
* List tables: ''show tables''
* show table : ''describe tablename'' or ''desc tablename''
* change table: ''ALTER TABLE''
* change table name: ''ALTER TABLE tablename RENAME TO new_tablename''
* remove column: ''ALTER TABLE tablename DROP COLUMN columnname''
* add column: ''ALTER TABLE tablename ADD COLUMN columnname type''
* modify column: ''ALTER TABLE tablename CHANGE columnname new_columnname new_type'';
==== Add Privages ====
* 给 newname 用户添加 newdb 所有权限:
grant all privileges on newdb.* to newname@localhost identified by 'newpassword';
flush privileges;
* 不限数据库不限来源ip不指定密码的写法:
grant all privileges on *.* to username@'%';
flush privileges;
==== Check Connections ====
* Connection history: ''show status like 'Conn%'; ''
* Current connections: ''show status like '%onn%'; ''
* Threads connected: ''show processlist;''
==== Query ====
* [[http://dev.mysql.com/doc/refman/5.7/en/examples.html|select examples]], ''select a,b from tablename where [conditions] order by a''
* [[http://dev.mysql.com/doc/refman/5.7/en/delete.html|delete syntax]]; '' delete from tablename where conditions ''
* [[http://dev.mysql.com/doc/refman/5.7/en/update.html|update syntax]]; '' update tablename set a=1 b=2 where conditions''
* [[http://dev.mysql.com/doc/refman/5.7/en/insert.html|insert syntax]]; '' insert into tablename(colname1, colname2, ...) values(value1, value2, ...)''
* The query mixes ASC and DESC:SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
ASC 可省略,默认正序。
==== Index ====
* create index:
* ''ALTER TABLE table_name ADD INDEX index_name (column_list)''
* ''ALTER TABLE table_name ADD UNIQUE (column_list)''
* ''ALTER TABLE table_name ADD PRIMARY KEY (column_list)''
* remove index:
* ''DROP INDEX index_name ON talbe_name''
* ''ALTER TABLE table_name DROP INDEX index_name''
* ''ALTER TABLE table_name DROP PRIMARY KEY''
* show index:''SHOW INDEX FROM table_name''
==== ORDER BY ====
===== Tips =====
* 记录 mysql 命令行结果到文件mysql> tee output.txt
Logging to file 'output.txt'
mysql> notee
Outfile disabled.
#或者
mysql> \T output.txt
Logging to file 'output.txt'
mysql> \t
Outfile disabled.
* 导入导出数据: mysqldump, 查看 ''man mysqldump''
* ''mysqldump --databases [dbname1] [dbname2] > output.sql''
* ''mysql < output.sql''
* 获取时区设置 ''SHOW VARIABLES LIKE 'system_time_zone' ''
* Timestamp 类型赋值给 Datetime 类型update sometable set dtime=FROM_UNIXTIME(UNIX_TIMESTAMP(ttime));
* 查看表大小
select TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH,INDEX_LENGTH from information_schema.TABLES;
* [[http://coolnull.com/154.html|mysql binlog日志自动清理及手动删除]]
* 关闭mysql主从,关闭binlog;
* 开启mysql主从,设置expire_logs_days;
* 手动清除binlog文件,''PURGE MASTER LOGS ''
* DNS解析如果有问题的话会影响 mysql 连接速度,可在 ''my.cnf'' 中 ''[mysqld]'' 下添加 ''skip-name-resolve'' 来忽略dns解析与IP反查。忽略后只能使用IP地址来连接数据库。