mysql 常用语句

以下是mysql 常用语句：

创建数据库

CREATE DATABASE databasename;

创建表

CREATE TABLE tablename (
  column1 datatype,
  column2 datatype,
  column3 datatype
);

删除数据库

DROP DATABASE databasename;

删除表

DROP TABLE tablename;

插入数据

INSERT INTO tablename (column1, column2, column3) VALUES (value1, value2, value3);

查询数据

SELECT column1, column2, column3 FROM tablename;

更新数据

UPDATE tablename SET column1 = value1, column2 = value2 WHERE condition;

删除数据

DELETE FROM tablename WHERE condition;

查询所有字段

SELECT * FROM tablename;

查询不重复的数据

SELECT DISTINCT column1, column2, ... FROM tablename;

排序

SELECT column1, column2, ... FROM tablename ORDER BY column1 ASC/DESC, column2 ASC/DESC, ...;

按条件分组

SELECT column1, COUNT(column2) FROM tablename GROUP BY column1;

连接两个表查询数据

SELECT column1, column2, ... FROM tablename1 JOIN tablename2 ON tablename1.column = tablename2.column;

对查询结果进行数学运算

SELECT column1 + column2 FROM tablename;

统计数量

SELECT COUNT(column1) FROM tablename;

统计平均数

SELECT AVG(column1) FROM tablename;

统计最大值

SELECT MAX(column1) FROM tablename;

统计最小值

SELECT MIN(column1) FROM tablename;

更新表结构

ALTER TABLE tablename ADD columnname datatype;

给表添加索引

ALTER TABLE tablename ADD INDEX indexname (columnname);