mysql 常用命令：

查看所有的数据库　 show databases; 定义当前数据库: use 'databasename'

show tables; grant insert,update, delete, create on 'databasebmae' to 'username' @'domain' IDENTIFIED by 'password';

使用脚本创建数据库　： mysql > . db-setup.mysql

-- This is the required schema for MySQL. Load this into the database -- using the mysql interactive terminal:

-- mysql> . db-setup.mysql

CREATE DATABASE jabberd2; USE jabberd2;

-- -- c2s authentication/registration table

CREATE TABLE authreg ( username TEXT, KEY username (username(255)), realm TINYTEXT, KEY realm (realm(255)), password TINYTEXT ) DEFAULT CHARSET=UTF8;

-- -- Session manager tables

-- -- Active (seen) users -- Used by: core

CREATE TABLE active ( collection-owner TEXT NOT NULL, KEY(collection-owner(255)), object-sequence BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(object-sequence), time INT ) DEFAULT CHARSET=UTF8;

-- -- Logout times -- Used by: mod_iq_last

CREATE TABLE logout ( collection-owner TEXT NOT NULL, KEY(collection-owner(255)), object-sequence BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(object-sequence), time INT ) DEFAULT CHARSET=UTF8;

-- -- Roster items -- Used by: mod_roster

CREATE TABLE roster-items ( collection-owner TEXT NOT NULL, KEY(collection-owner(255)), object-sequence BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(object-sequence), jid TEXT, name TEXT, to TINYINT, from TINYINT, ask INT ) DEFAULT CHARSET=UTF8;

-- -- Roster groups -- Used by: mod_roster

CREATE TABLE roster-groups ( collection-owner TEXT NOT NULL, KEY(collection-owner(255)), object-sequence BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(object-sequence), jid TEXT, group TEXT ) DEFAULT CHARSET=UTF8;

-- -- vCard (user profile information) -- Used by: mod_iq_vcard

CREATE TABLE vcard ( collection-owner VARCHAR(255) NOT NULL, KEY(collection-owner(255)), object-sequence BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(object-sequence), fn VARCHAR(255), nickname VARCHAR(255), url VARCHAR(255), tel VARCHAR(255), email VARCHAR(255), jabberid VARCHAR(3071), mailer VARCHAR(1023), title VARCHAR(255), role VARCHAR(255), bday VARCHAR(255), # Shouldn't this be a DATE? tz VARCHAR(7), n-family VARCHAR(255), n-given VARCHAR(255), n-middle VARCHAR(255), n-prefix VARCHAR(255), n-suffix VARCHAR(255), adr-street VARCHAR(255), adr-extadd VARCHAR(255), adr-pobox VARCHAR(15), adr-locality VARCHAR(255), adr-region VARCHAR(255), adr-pcode VARCHAR(31), adr-country VARCHAR(63), geo-lat VARCHAR(255), geo-lon VARCHAR(255), org-orgname VARCHAR(255), org-orgunit VARCHAR(255), agent-extval VARCHAR(255), sort-string VARCHAR(255), desc TEXT, note TEXT, uid VARCHAR(255),

`photo-type` VARCHAR(127),
`photo-binval` TEXT,
`photo-extval` VARCHAR(255),

`logo-type` VARCHAR(127),
`logo-binval` TEXT,
`logo-extval` VARCHAR(255),

`sound-phonetic` VARCHAR(255),
`sound-binval` TEXT,
`sound-extval` VARCHAR(255),

`key-type` VARCHAR(127),
`key-cred` TEXT,

`rev` VARCHAR(255)
) DEFAULT CHARSET=UTF8;

-- -- Offline message queue -- Used by: mod_offline

CREATE TABLE queue ( collection-owner TEXT NOT NULL, KEY(collection-owner(255)), object-sequence BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(object-sequence), xml MEDIUMTEXT ) DEFAULT CHARSET=UTF8;

-- -- Private XML storage -- Used by: mod_iq_private

CREATE TABLE private ( collection-owner TEXT NOT NULL, KEY(collection-owner(255)), object-sequence BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(object-sequence), ns TEXT, xml MEDIUMTEXT ) DEFAULT CHARSET=UTF8;

-- -- Message Of The Day (MOTD) messages (announcements) -- Used by: mod_announce

CREATE TABLE motd-message ( collection-owner TEXT NOT NULL, KEY(collection-owner(255)), object-sequence BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(object-sequence), xml TEXT ) DEFAULT CHARSET=UTF8;

-- -- Times of last MOTD message for each user -- Used by: mod_announce

CREATE TABLE motd-times ( collection-owner TEXT NOT NULL, KEY(collection-owner(255)), object-sequence BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(object-sequence), time INT ) DEFAULT CHARSET=UTF8;

-- -- Default privacy list -- Used by: mod_privacy

CREATE TABLE privacy-default ( collection-owner TEXT NOT NULL, KEY(collection-owner(255)), object-sequence BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(object-sequence), default text ) DEFAULT CHARSET=UTF8;

-- -- Privacy lists -- Used by: mod_privacy

CREATE TABLE privacy-items ( collection-owner TEXT NOT NULL, KEY(collection-owner(255)), object-sequence BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(object-sequence), list TEXT, type TEXT, value TEXT, deny TINYINT, order INT, block INT ) DEFAULT CHARSET=UTF8;

-- -- Vacation settings -- Used by: mod_vacation

CREATE TABLE vacation-settings ( collection-owner TEXT NOT NULL, KEY(collection-owner(255)), object-sequence BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(object-sequence), start INT, end INT, message TEXT ) DEFAULT CHARSET=UTF8;

-- -- Users statuses -- Used by: mod_status

CREATE TABLE status ( collection-owner TEXT NOT NULL, KEY(collection-owner(255)), object-sequence BIGINT NOT NULL AUTO_INCREMENT, KEY(object-sequence), status TEXT NOT NULL, show TEXT NOT NULL, last-login INT DEFAULT '0', last-logout INT DEFAULT '0', xml TEXT ) DEFAULT CHARSET=UTF8;

转载于:https://my.oschina.net/wansheg/blog/901983