mysql表结构命令

• show columns from 表名;
• describe 表名;
• show create table 表名;
• use information_schema
   select * from columns where table_name='表名';

顺便记下:

show databases;
use 数据库名;
show tables;

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD PRIMARY KEY (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
  or    MODIFY [COLUMN] create_definition
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX index_name
  or    RENAME [AS] new_tbl_name
  or    table_options

Refresh Database:
Backup: mysqldump -h offline-prod-mysql-db1 -P 3306 -u username -p --opt databaseName > databaseName.sql

Refresh: mysql -h offline-dev-mysql-db1 -u username -P 3306 -p databaseName < databaseName .sql

mysqldump -h 10.19.8.167 -P 3306 -u username -p --no_data DATABASE_NAME > output_filename_schema.sql

Connect to Database:
mysql -h 10.19.90.52-u username -p
mysql create database databaseName;
grant all privileges on *.* to 'root'@'%' identified by 'passwd';

Privillege :
show grants for 'username'@'%';
set password for 'username'@'%' = PASSWORD('newpwd');

Create User :
GRANT ALL PRIVILEGES ON . TO 'uesrname'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSHPRIVILEGES;

Others:
Quit
select version(),current_date();
select (20+5)*4 AS Result,sin(pi()/3);
SELECT USER(); SELECT NOW();