mysql 大表增删查改_MySQL数据表的增删改查操作

mysql表的常用操作，即INSERT插入数据操作、DELETE删除数据操作、UPDATE修改数据操作、SELECT查询数据操作，及常见的表查询条件使用。

INSERT插入数据操作

INSERT INTO tableName (field1,field2,…) values (value1,value2,…);

DELETE删除数据操作

DELETE FROM tableName WHERE conditions;

UPDATE修改数据操作

UPDATE tableName SET field1 = value1,field2 = value2, … WHERE conditions;

SELECT查询数据操作

SELECT field1,field2,… FROM tableName WHERE conditions;

下面主要讲解mysql的查询操作的条件：

IS NULL 或 IS NOT NULL1SELECT * FROM tableName WHERE field (IS NULL|IS NOT NULL);

IN() 在…范围内 或 NOT IN()不在…范围内1

2SELECT * FROM tableName WHERE field IN(value1,value2,...);

SELECT * FROM tableName WHERE field NOT IN(value1,value2,...);

BETWEEN AND 在…之间 或 NOT BETWEEN AND 不在…之间1

2SELECT * FROM tableName WHERE field BETWEEN value1 AND value2;

SELECT * FROM tableName WHERE field NOT BETWEEN value1 AND value2;

NOT 取反运算符 ADN 与运算符 OR 或运算符1

2SELECT * FROM tableName WHERE field1 = value1 OR field2 = value2;

SELECT * FROM tableName WHERE field1 = value1 AND field2 = value2;

排序操作 DESC代表降序排序，ASC代表升序排序，默认升序排序1

2SELECT * FROM tableName ORDER BY field DESC;

SELECT * FROM tableName ORDER BY field ASC;

LIMIT 限制结果显示行数(用法：LIMIT 5 显示5行 或 LIMIT 0,5 从0位置开始显示5行,0代表数据库内起始行数),LIMIT 在SQL语句最后面1

2SELECT * FROM tableName LIMIT 5;

SELECT * FROM tableName LIMIT 0,5;

IFNULL() 与 IF()两个函数的使用及区别:IFNULL(exp1,exp2) = IF(exp1 IS NULL,exp2,exp3)1

2

3SELECT field1 AS alias1, field2 AS alias2, IFNULL(field3 , value3) AS alias3 FROM tableName;

SELECT field1 AS alias1, field2 AS alias2, IF(field3 IS NULL, value3, field3) AS alias3 FROM

tableName;

集合函数(聚集函数)AVG() – 求平均值 (计算平均时，不包含空值)

SUM() – 求和

COUNT() – 求总记录数

MAX() – 最大值

MIN() – 最小值1SELECT AVG(field) 平均值, SUM(field) 总和, MAX(field) 最大值, MIN(field) 最小值 ,COUNT(*) 总记录数, COUNT(field) field不为空的总数 FROM tableName;

DISTINCT 去除重复1SELECT COUNT(DISTINCT field) alias FROM tableName;

GROUP BY分组查询，一般前面是分组名称，紧跟着是集合函数(聚集函数)1SELECT field1,AVG(field2) FROM tableName GROUP BY field1;

HAVING 是分组条件(使用前提是使用了GROUP BY分组查询)1

2SELECT field1,AVG(field2) FROM tableName GROUP BY field1 HAVING AVG(field2)>=60 ORDER BY

AVG(field2) DESC LIMIT 2;

多表连接查询 查询结果是多个表中的信息1

2

3

4

5

6

7

8

9

10

11

12

13

14--等值链接查询

SELECT tableName1.field1,tableName1.field2,tableName2.field1,tableName2.field2 FROM tableName1,

tableName2 WHERE tableName1.fid=tableName2.id;

--左链接查询

SELECT t1.field1,t1.field2,t2.field1,t2.field2 FROM tableName1 t1 LEFT JOIN tableName2 t2 ON

t1.fid=t2.id;

--右链接查询

SELECT t1.field1,t1.field2,t2.field1,t2.field2 FROM tableName1 t1 RIGHT JOIN tableName2 t2 ON

t1.fid=t2.id;

--表的自身链接查询

SELECT t1.field1,t1.field2,... FROM tableName t1,tableName t2 WHERE t1.id