楠枫渡

Thinking && coding

24
文章
13
分类
28
标签

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 NULL

1
SELECT * FROM tableName WHERE field (IS NULL|IS NOT NULL);

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

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

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

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

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

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

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

1
2
SELECT * 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
2
SELECT * FROM tableName LIMIT 5;
SELECT * FROM tableName LIMIT 0,5;

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

1
2
3
SELECT 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() – 最小值
1
SELECT AVG(field) 平均值, SUM(field) 总和, MAX(field) 最大值, MIN(field) 最小值 ,COUNT(*) 总记录数, COUNT(field) field不为空的总数 FROM tableName;

DISTINCT 去除重复

1
SELECT COUNT(DISTINCT field) alias FROM tableName;

GROUP BY分组查询，一般前面是分组名称，紧跟着是集合函数(聚集函数)

1
SELECT field1,AVG(field2) FROM tableName GROUP BY field1;

HAVING 是分组条件（使用前提是使用了GROUP BY分组查询）

1
2
SELECT 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<t2.id;