这是本文档旧的修订版！
MySQL
- Offcial site: mysql.com
Manual
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:
mysql> create database newdb; - List databases:
mysql> show databases; - Use databases:
mysql> use newdb; - List tables:
mysql> show tables - show table :
mysql> describe tablenameordesc 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 所有权限：
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;
Check Connections
- Connection history:
mysql> show status like 'Conn%'; - Current connections:
mysql> show status like '%onn%'; - Threads connected:
mysql> show processlist;
Query
- 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, …)
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_nameALTER TABLE table_name DROP INDEX index_nameALTER TABLE table_name DROP PRIMARY KEY
- show index:
SHOW INDEX FROM table_name
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 - 获取时区设置
SHOW VARIABLES LIKE 'system_time_zone' - Timestamp 类型赋值给 Datetime 类型
UPDATE sometable SET dtime=FROM_UNIXTIME(UNIX_TIMESTAMP(ttime));
oakfire