这是本文档旧的修订版！
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
mysql> grant all privileges on newdb.* to newname@localhost identified by 'newpassword';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, …)
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
oakfire