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