MySQL

这是本文档旧的修订版！

Offcial site: mysql.com

sql syntax

mysql -h host -u username -p password -P port
default port,no password: mysql -h host -u username -p
disconnect: mysql> quit

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;

Create: mysql> create database newdb;
List databases: mysql> show databases;
Use databases: mysql> use newdb;
List tables: mysql> show tables
show table : mysql> 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;

给 newname 用户添加 newdb 所有权限：

mysql> grant all privileges on newdb.* to newname@localhost identified by 'newpassword';
mysql> flush privileges;

不限数据库不限来源ip不指定密码的写法：

mysql> grant all privileges on *.* to username@'%';
mysql> flush privileges;

Connection history mysql> show status like 'Conn%'
Current connections mysql> show status like '%onn%' “Threads_connected”
mysql> show processlist

select examples, select a,b from tablename where [conditions] order by a
delete syntax; delete from tablename where conditions
update syntax; update tablename set a=1 b=2 where conditions
insert syntax; insert into tablename(colname1, colname2, …) values(value1, value2, …)

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

记录 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
获取时区设置 SHOW VARIABLES LIKE 'system_time_zone'