MySQL：Useful Commands

浏览数：23 / 时间：2015年06月12日

Start/Stop/Restart MySQL:
===========================
On Linux start/stop/restart from the command line:
/etc/init.d/mysqld start
/etc/init.d/mysqld stop
/etc/init.d/mysqld restart

Some Linux flavours offer the service command too
service mysqld start
service mysqld stop
service mysqld restart

or
service mysql start
service mysql stop
service mysql restart

On OS X to start/stop/restart MySQL from the command line:
sudo /usr/local/mysql/support-files/mysql.server start
sudo /usr/local/mysql/support-files/mysql.server stop
sudo /usr/local/mysql/support-files/mysql.server restart

Connect:
===========================
mysql -h localhost -u myname -pMyPassword

Set Password:
===========================
First set: mysqladmin -u root -password root
then:
mysql> UPDATE mysql.user SET password=PASSWORD(’新密码’) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;

Show current user:
===========================
mysql> select user();

Create Database:
===========================
mysql> create database openfire character set ‘utf8‘;

Show Databases:
===========================
mysql> show databases;

Use Database:
===========================
mysql> use database;

Show Tables:
===========================
mysql> show tables;

Empty database with root user
===========================
mysql> DROP DATABASE atomstore;
mysql> CREATE DATABASE atomstore;

Grant Permission
===========================
GRANT ALL PRIVILEGES ON * . * TO ‘newuser‘@‘localhost‘;
FLUSH PRIVILEGES;

Show table structure
===========================
SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]
    [LIKE ‘pattern‘ | WHERE expr]

Drop database
===========================
DROP DATABASE atomstore;

Drop table
===========================
DROP TABLE xxxxx;

Create table samples
===========================
create table if not exists `fchat`.`user` (
`userId` int(7) not null auto_increment,
   `username` varchar(64) not null,
   `plainPassword` varchar(32) null,
   `encryptedPassword` varchar(255) null,
   `name` varchar(100) null,
   `email` varchar(100) null,
   `creationDate` datetime not null,
   `modificationDate` datetime not null,
   primary key(`userId`)
);

create table if not exists `fchat`.`tag` (
`tagId` int(5) not null auto_increment,
`tagName` varchar(64) not null,
`userId` int(7) not null,
primary key(`tagId`),
foreign key (userId)
references user(userId)
    on delete cascade
);