这是本文档旧的修订版!
MySQL
- Official site: mysql.com
MariaDB
- Official site: mariadb.org
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 tablename
ordesc 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:
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_name
ALTER TABLE table_name DROP INDEX index_name
ALTER 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));