MySQL Weed

时间：2007-09-13 来源：freebendy

MySQL Weed
使用MySQL的过程中，收集的一些小知识（此文原始资料来源于老江的Blog）。(2007.09.04最后更新)

简单SQL语句
创建/删除数据库
CREATE DATABASE mydb
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

DROP DATABASE mydb

创建数据表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tblname (
colname1 coltype coloptions reference,
colname2 coltype coloptions reference, ...
[, index1, index2, ...])
[ENGINE = MyISAM | InnoDB | HEAP]
[DEFAULT CHARSET = csname [COLLATE = colname]]

CREATE TABLE titles (
titleID INT NOT NULL AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
subtitle VARCHAR(100),
edition TINYINT,
pubID INT,
catID INT,
langID INT,
year INT,
isbn VARCHAR(20),
comment VARCHAR(255),
ts TIMESTAMP,
PRIMARY KEY (titleID),
KEY pubIdIndex (pubID),
KEY langID (langID),
KEY catID (catID),
KEY title (title),
CONSTRAINT titles_ibfk_1 FOREIGN KEY (pubID)
    REFERENCES publishers (pubID),
CONSTRAINT titles_ibfk_2 FOREIGN KEY (langID)
    REFERENCES publishers (langID),
CONSTRAINT titles_ibfk_3 FOREIGN KEY (catID)
    REFERENCES categories (catID))
ENGINE = InnoDB
DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci

CREATE TABLE table2 SELECT * FROM table1 where id <= 10;

DROP TABLE mytitle

创建/删除索引
CREATE INDEX idxTitle ON titles (title)
ALTER TABLE titles ADD INDEX idxTitle (title)
SHOW INDEX FROM tablename -- 列出数据表tablename的索引

DROP INDEX indexname ON tablename

ALTER TABLE titles ADD INDEX idxTitle (title(16))

变更表结构
ALTER TABLE tablename ADD newcolumn coltype coloptions [FIRST | AFTER]

ALTER TABLE tablename CHANGE oldcolname newcolumn coltype coloptions

ALTER TABLE tablename DROP colname

ALTER TABLE tblname ADD PRIMARY KEY (indexcols ...)
ALTER TABLE tblname ADD INDEX [indexname] (indexcols ...)
ALTER TABLE tblname ADD UNIQUE [indexname] (indexcols ...)
ALTER TABLE tblname ADD FULLTEXT [indexname] (indexcols ...)

ALTER TABLE tblname ADD FOREIGN KEY [idxname]
(column1) REFERENCES table2 (column2)

ALTER TABLE tblname DROP PRIMARY KEY
ALTER TABLE tblname DROP INDEX indexname
ALTER TABLE tblname DROP FOREIGN KEY indexname

ALTER NAME tablename ENGIEN typename

MySQL伴随Linux启动
vi /etc/rc.d/rc.local
加入语句： ./mysqld_safe --user=mysql &

改变root用户密码
mysql> update mysql.user set password=password('YourPwd') where user='root';
mysql> flush privileges;

增加新用户
[1]添加一用户，使它拥有所有权限
mysql> grant all on mydb.* to myuser@"myhost" Identified by "mypassword";

[2]简单添加一用户
mysql> i nsert into mysql.user (host, user, password) values('%', 'NewUser', password('NewUserPwd'));
mysql> flush privileges;
注：最好使用grant命令。

启动/关闭MySQL服务器
启动(Linux下): mysqld_safe -u mysql &
关闭: mysqladmin -uUser -pPassword -hHost shutdown

导出/导入数据
导出：
mysqldump -uUser -pPasswd -hHost -B DB_NAME > SQL_FILE_PATH -- 将数据库DB_NAME全部导出到SQL_FILE_PATH文件中
mysqldump -uUser -pPassword -hHost DATABASE_NAME --tables TABLE_NAME > SQL_FILE_PATH -- 将数据库DB_NAME中的表TABLE_NAME导出

导入：
mysql -uUser -pPassword -hHost < SQL_FILE_PATH -- 将数据文件导入MySQL服务器中。注：该文件中必须有建数据库的语句。
mysqldump -uUser -pPassword -hHost -B DB_NAME < SQL_FILE_PATH -- 将数据文件导入MySQL服务器的DB_NAME数据库中。注：该文件中没有建数据库的语句。
注意：在导入数据库之前，需要在MySQL中新建一个名为DATABASE_NAME的空数据库。

启动日志
修改mysql.ini/mysql.cnf文件，在[mysqld]后添加如下形式的语句。
log="Absolte_Path_To_Log/sys.log"
log-update=" Absolte_Path_To_Log/ sys_update.log"

使MySQL区分大小写
修改文件my.ini/my.cnf，在选项组[mysqld]之后添加
lower_case_table_names=2

修改某用户的登录密码
mysqladmin -uUser -pOldPasswd passwd NewPasswd
mysql>update user set password=PASSWORD("NewPasswd") where user="myuser" and host="myhost";

查看表结构
mysql> describe TableName

修改表的字符集
mysql> alter table tblName convert to character set charsetName

修改表的表类型
mysql> alter table tblName engine engineType

SHOW
show character set -- 查看字符集
show character set
show collation
show columns
show create database
show create procedure and show create function
show create table
show create view
show databases
show engine
show engines
show errors
show grants
show index
show innodb status
show logs
show mutex status
show open tables
show privileges

updating...