mysql常用语句列表

增加一张表

CREATE TABLE `table_name`(

  ...

  )ENGINE=InnoDB DEFAULT CHARSET=utf8;

增加记录

INSERT INTO `your_table_name`(`column_name_one`,`column_name_two`)

VALUES

('your_value_one','your_value_two');

增加字段

ALTER TABLE `your_table_name`

ADD `your_column_name` ...

AFTER `column_name`;

增加索引

主键

ALTER TABLE `your_table_name`

ADD PRIMARY KEY your_index_name(your_column_name);

唯一索引

ALTER TABLE `your_table_name`

ADD UNIQUE your_index_name(your_column_name);

普通索引

ALTER TABLE `your_table_name`

ADD INDEX your_index_name(your_column_name);

全文索引

ALTER TABLE `your_table_name`

ADD FULLTEXT your_index_name(your_column_name);

逐行删除

DELETE FORM `table_name`

WHERE ...;

清空整张表

TRUNCATE TABLE `your_table_name`;

删除表

DROP  TABLE `your_table_name`;

删除字段

ALTER TABLE `your_table_name`

DROP `column_name`;

删除索引

ALTER TABLE `your_table_name`

DROP INDEX your_index_name;

变更数据

UPDATE `table_name`

SET column_name=your_value

WHERE ...;

变更字段

ALTER TABLE `your_table_name`

CHANGE `your_column_name` `your_column_name` ;

普通查询

SELECT `column_name_one`, `column_name_two`

FROM `table_name`;

普通查询

SELECT *

FROM `your_table_name`

AS a

JOIN `your_anther_table_name`

AS b

WHERE a.column_name = b.column_name...;

合计函数条件查询：WHERE 关键字无法与合计函数一起使用

SELECT aggregate_function(column_name)

FROM your_table_name

GROUP BY column_name

HAVING aggregate_function(column_name)...;

完全复制一张表：表结构+全部数据

CREATE TABLE `your_table_name` LIKE `destination_table_name`;

INSERT INTO `your_table_name`

SELECT *

FROM `destination_table_name`;

巴特西