19.1 操作数据表中的数据记录(DML)
19.1.1 使用INSERT语句想数据表中添加数据
插入数据是向已经存在的数据表中添加一条新的纪录,应该使用INSERT INTO语句。
INSERT INTO 表名 [(字段名1,字段名2,...,字段名n)]VALUES('值1','值2',...,'值n');
在表名后面的括号中是该表中定义的字段名称列表,它们与VALUES子句后面的表达式列表的值是一一对应的,个数也要相等,并且表达式值得类型必须与字段的类型一致,INSERT语句也可以省略字段列表,但必须插入一行完整的数据,而且必须按照表中定义的字段顺序为全部字段提供值。
在插入数据时,对于存在主键,或者定义为唯一约束的数据表,应确保不能插入重复数据,否则会使数据库发生错误。
19.1.2 使用UPDATE语句更新数据表中已经存在的数据
UPDATE 表名 #需要给出被修改的表名
SET 字段名=表达式 [,......] #可以对表中的一列或多列数据进行修改
[WHERE 条件] #给出必要的WHERE子名指定要更新的数据行
[ORDER BY 字段] #按照被指定的顺序对行进行更新
[LIMIT 行数] #限制可以被更新的行的数目
这里详细解释下操作步骤:
1.在EXCEL下填写数据表,并在MySQL中创建对应的数据表
2.将EXCEL表格保存格式为.CSV(逗号分隔)格式,然后再用记事本转存为.txt格式,用UTF-8编码保存。
3.在PHPmyAdmin下执行SQL语句(这里注意要选中TABLE后进入SQL,不然会报错,这里我是放在了bookstore中)
LOAD DATA INFILE 'C:/Users/llc_1/Desktop/test.txt' INTO TABLE `book` fields terminated by ',' lines terminated by '\n'
这里注意文件路径使用“/”而不是“\”(我去……坑爹的吧)Table 后的值是你的表名;fields terminated by后的值是列与列间的分割标识(个人感觉“,”比较靠谱,不易出错。而tab可能在导入的时候出现问题);lines terminated by后面的值是行与行之间的分割标识,这个没的说,一般都是“\n”。
然后检查下导入都成功了(这里我显示中文都是乱码,但是在创建中添加以UTF8编码的语句通不过……,目前我还没解决这个问题,在这里留个空,等以后补充)
UPDATE 语句虽然一次只能修改一个数据表,但是可以同时对同一表中多个字段进行修改。
由于表的集合操作特点,还可以使用UPDATE语句修改多条记录中的某一列的值,或者赋值给另一个列。
19.1.3 使用DELETE语句删除数据表中不需要的数据结构
DELETE FROM 表名 #删除表中记录行的DELETE语法格式
[WHERE 条件] #给出必要的WHERE子名指定要删除的数据行
[ORDER BY 字段] #按照被指定的顺序对行进行删除
[LIMIT 行数] #限制可以被删除的行的数目
如果没有指定WHERE子句的检索条件,DELETE语句将会删除数据表中的全部数据记录,是数据库中只剩下数据表结构。但是如果需要清空表,不需要使用DELETE语句,使用TRUNCATE方法会更加效率,因为TRUNCATE语句用于清空表中所有数据,只保留一个数据表的定义。
19.2 通过DQL命令查询数据表中的数据
SELECT [ALL|DISTINCT] #使用SELECT语句查询检索
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,...]]} #选择哪些数据列
FROM tableexpression[,...][IN externaldatabase] #指定SELECT语句中字段的来源
[WHERE...] #数据行必须满足哪些检索条件
[GROUP BY...] #指明按照哪几个字段来分组
[HAVING...] #过滤分组的记录,必须满足的次要条件
[ORDER BY...] #按一个或多个字段排序查询结果
[LIMIT count]; #对结果个数的限制
其中“[]”为可选,“{}”中为必选一个。FROM子句指定SELECT来源,FROM后main还有多个表达式(用逗号分开)。
19.2.1 选择特定的字段
另外可以使用“*”从表中检索出所有字段。
19.2.2 使用AS子句为字段取别名
定义别名时一定要使用单引号引起来,其中AS关键字是可选的,在原字段名和别名之间使用一个空格即可。
19.2.3 DISTINCT关键字的使用
如果在使用SELECT语句返回的记录结果中包含重复的记录,可以使用DISTINCT关键字取消重复的数据,只返回一个。另外要注意DISTINCT关键字的作用范围是整个查询列表。
与DISTINCT相对应的是ALL关键字,用于返回满足SELECT语句条件的所有记录,不用刻意地添加ALL关键字。
19.2.4 在SELECT语句中使用表达式的列
如果将这种SQL语句嵌入到PHP语言中使用,会和PHP运算符号混淆,极易产生错误。因此可以使用前面介绍的附加字段别名来解决。
在SELECT语句中使用表达式重新对数据列进行计算是比较常见的。
19.2.5 使用WHERE子句按条件检索
19.2.6 根据空值(NULL)确定检索条件
如果要检索NULL值,必须使用IS NULL 和IS NOT NULL关键字。
19.2.7 使用BETWEEN AND进行范围比较查询
如果需要对某个字段通过范围的值进行查询,可以使用BETWEEN AND关键字实现,也可以使用“>=”和“<=”完成相同的功能。
19.2.8 使用IN进行范围比对查询
在WHERE子句中,使用IN关键字并在后面的括号“()”提供一个值的列表,以供该字段进行比较。该列表中至少应该存在一个值,如果有多个值,可以使用逗号“,”分隔。
19.2.9 使用LIKE进行模糊查询
在查询条件中通常会与“_”和“%”两个通配符一起使用,可以实现复杂的检索查询:
- 百分号“%”:表示0个或任意多个字符
- 下划线“_”:表示单个的任意一个字符
如果是哟那个一个完整的字符串作为精确的查询条件,最好不要使用LIKE进行模糊查询,应该直接使用“=”的功能。
19.2.10 多表查询(连接查询)
- 非等值和等值的多表查询
- 自身连接查询
- 复合连接查询
19.2.11 嵌套查询(子查询)
但要明确子查询中返回的不能是集合,而只能返回一行结果,否则会发生错误。
19.2.12 使用ORDER BY 对查询结果排序
可以后接DESC或ASC关键字进行排序。(默认为ASC升序)
19.2.13 使用LIMIT限定结果行数
如果在数据表中的记录数非常多,可以通过LIMIT子句一次取少量的记录,用分页的方式继续浏览后面的数据。
19.2.14 使用统计函数
COUNT():返回满足SELECT语句中指定条件的记录数
SUM():通常为数值字段或表达列作统计,返回一列的和
AVG():通常为数值字段或表达列统计,返回一列的平均值
MAX():可以为数值字段、字符字段或表达列作统计,返回一列最大的值
MIN():可以为数值字段、字符字段或表达列作统计,返回一列最大的值
语法为:
SELECT 函数名 (列名1或*),...函数名(列名n) FROM 表名 #使用统计函数
19.2.15 使用GROUP BY对查询结果分组
需要注意的是,在GROUP BY子句中不支持对字段分配别名,也不支持任何使用了统计函数的集合列。
完成分组查询和统计后,还可以使用HAVING子句对查询结果进一步筛选。
19.3 查询优化
开发者需要牢记的规则:
- 最先出现的条件,一定是过滤和排除掉最多结果的条件
- 第二出线的次之,以此类推
因而,表中不同字段的值分布,对查询速度有着很大影响,而ORDER BY中的条件,只与索引有关,而与条件顺序无关。
在大多数情况下,根据WHERE条件的先后顺序和ORDER BY排序字段的先后顺序而建立的联合索引,就是与这条SQL语句匹配的最有索引结构。尽管事实的产品中不能只考虑一条SQL语句,也不能考虑空间占用而建立太多索引。
EXPLAIN语句是检测索引和查询能否良好匹配的简便方法。