mysql锿法_mysql各种方法（ALL)

小知识：

\G 放到sql语句后,可以使每个字段打印到单独的行，如：

mysql> select * from user_msg limit 2 \G;

一、增加删除表字段的ALTER命令：

ALTER TABLE tbl DROPname; #删除name字段ALTER TABLE tbl ADD name INTFIRST; #首部添加name字段ALTER TABLE tbl DROPname;ALTER TABLE tbl ADD name INT AFTER age; #age的后面添加name字段

修改字段类型及名称:

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

ALTER TABLE testalter_tbl MODIFY c CHAR(10);

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

ALTER TABLE testalter_tbl CHANGE i j BIGINT;ALTER TABLE testalter_tbl CHANGE j j INT;

修改字段默认值:

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

修改表名

如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。

尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl：

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

二、MySQL 索引

索引分单列索引和组合索引。

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。

因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。 建立索引会占用磁盘空间的索引文件。

普通索引

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

创建表的时候直接指定

CREATE TABLEmytable(

IDINT NOT NULL,

usernameVARCHAR(16) NOT NULL,INDEX [indexName](username(length))

);

删除索引的语法

DROP INDEX [indexName] ON mytable;

唯一索引：

UNIQUE关键字修饰。

三、MySQL 临时表

使用TEMPORARY修饰表名；

当你使用 SHOW TABLES命令显示数据表列表时，你将无法看到 临时表。

如果你退出当前MySQL会话，再使用 SELECT命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。

使用DROP TABLE命令可以手动删除临时表。

四、MySQL 复制表(重要)

SHOW CREATE TABLE tbl \G; #打印创建表tb1的全过程

//手动复制一遍，改个表名，tb2，就复制了tb1的数据结构；(理解错误。。。mark一下)

//已经显示出创建tb1的代码过程了，将表明由tb1改为tb2就可以了。。。然后如下复制数据：

复制数据：

INSERT INTOtb2 (runoob_id,

runoob_title,

runoob_author,

submission_date)SELECTrunoob_id,runoob_title,

runoob_author,submission_dateFROM tbl;

五、MySQL 序列

CREATE TABLEinsect

(

idINT UNSIGNED NOT NULL AUTO_INCREMENT = 100, #默认从1开始PRIMARY KEY(id),

nameVARCHAR(30) NOT NULL, # type ofinsect

date DATENOT NULL, # date collected

originVARCHAR(30) NOT NULL # wherecollected

);

ALTER TABLE insect AUTO_INCREMENT = 100;

重置序列

如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示：

ALTER TABLE insect DROPid;ALTER TABLEinsectADD id INT UNSIGNED NOT NULLAUTO_INCREMENT FIRST,ADD PRIMARY KEY (id);

六、MySQL 处理重复数据

主键不能为空，NOT NULL限制；通过主键的唯一性，在创建表时进行控制；

CREATE TABLEperson_tbl

(

first_nameCHAR(20) NOT NULL,

last_nameCHAR(20) NOT NULL,

sexCHAR(10),PRIMARY KEY(last_name, first_name)

);

插入时控制：

INSERT IGNORE INTO与INSERT INTO的区别：

INSERT IGNORE INTO：如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。

统计重复数据

以下我们将统计表中 first_name 和 last_name的重复记录数：

SELECT COUNT(*) asrepetitions, last_name, first_nameFROMperson_tblGROUP BYlast_name, first_nameHAVING repetitions > 1;

过滤重复数据

如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

SELECT DISTINCTlast_name, first_name #注意：last_name和first_name作为一个整体使用的DISTINCT。效果同下GROUP BY：FROMperson_tblORDER BY last_name;

你也可以使用 GROUP BY 来读取数据表中不重复的数据：

SELECTlast_name, first_nameFROMperson_tblGROUP BY (last_name, first_name);

删除重复数据

如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：

CREATE TABLE tmp SELECTlast_name, first_name, sex #间接创建表的方法FROMperson_tbl;GROUP BY(last_name, first_name);DROP TABLEperson_tbl;ALTER TABLE tmp RENAME TO person_tbl;

七、MySQL 导出(入)数据

为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE

SELECT * FROM tb1 INTO OUTFILE '/tem/tutorials.txt';

你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：

SELECT * FROM tb1 INTO OUTFILE '/tmp/tutorials.txt'FIELDS TERMINATEDBY ',' ENCLOSED BY '"'LINES TERMINATEDBY '\r\n';

#每项用 , 分割   用 " 包裹   行结束符为：'\r\n' ；

#注意：

SELECT ... INTO OUTFILE 语句有以下属性:

LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。

SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。

输出不能是一个已存在的文件。防止文件数据被篡改。

你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。

在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。

导出SQL格式的数据

导出SQL格式的数据到指定文件，如下所示：

mysqldump -u root -p database1 tbl > dump.txt

password******

#以上为从数据库database1的表tb1导出到dump.txt；

#导出整个数据库，不加tb1即可；

#备份所有数据库：

mysqldump -u root -p --all-databases > database_dump.txt

password ******

使用 LOAD DATA 导入数据

MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。

如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：

LOAD DATA LOCAL INFILE 'dump.txt'

INTO TABLE mytbl (b, c, a);

#如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。

mysqlimport命令可以指定选项来设置指定格式,命令语句格式如下：

mysqlimport -u root -p --local --columns=b,c,a \ --fields-terminated-by=":" \ --lines-terminated-by="\r\n" database_name dump.txt

password *****