第四章 操作数据表中的记录
回顾和概述
约束
按约束所针对列的数目划分：
表级约束：针对两个或两个以上的字段使用
列级约束：只针对某一个字段
按功能划分：
NOU NULL（非空约束）不存在表级约束
PRIMARY KEY（主键约束）
UNIQUE KEY（唯一约束）
DEFAULT（默认约束）不存在表级约束
FOREIGN KEY（外键约束）
修改数据表
针对字段的操作：添加/删除字段、修改列定义，修改列名称等
针对约束的操作：添加/删除各种约束
针对数据表操作：数据表更名（两种方式）
插入记录INSERT
INSERT：
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
INTO 可以省略，col_name列名称如果省略默认为所有咧赋值，如果存在多个字段，字段之间用逗号分隔
创建数据表：
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
age TINYINT UNSIGNED NOT NULL DEFAULT 10,
sex BOOLEAN
);
插入记录：
如果省略字段名，默认为所有字段赋值，但是为id自动增长的字段赋值时，可以采用两个特殊的值，依次让它采用默认的自动编号的形式（NULL，DEFAULT）
INSERT users VALUES(NULL,'Tom','123',25,1);#插入一条记录id为NULL（默认的自动编号）
使用SELECT * FROM users;#id为1，按照默认的自动增长插入
INSERT users VALUES(NULL,'John','456',25,1);
使用SELECT * FROM users;#id依次递增，按照默认的自动增长插入
INSERT users VALUES(NULL,'Tom','123',25,1);#使用DEFAULT同样也可以自动增长
如果省略列名，所有的列必须依次赋值，否则会报错
列的值也可以是一个数学表达式：
INSERT users VALUES(DEFAULT,'Tom','456',3*7-5,1);#插入成功
除此之外，还可以写成字符表达式或函数
为有默认值的DEFAULT字段可以用DEFAULT设置成默认值：
INSERT users VALUES(DEFAULT,'Tom','123',DEFAULT,1);#为age字段设置为默认的值
使用SELECT * FROM users;查看记录，发现刚刚插入的记录被赋予了默认值
一次插入多条记录
INSERT users VALUES(DEFAULT,'Tom','123',3*7-5,1),(NULL,'Rose',md5('123'),DEFAULT,0);#一次插入多条记录，列值可以为表达式或函数（md5()为计算字符串md5的哈希）DEFAULT为默认值
插入记录INSERT SET-SELECT
插入记录的第二种方法：
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},...
说明：与第一种方式的区别在于，此方法可以使用子查询（SubQuery）
这种方法用的较少，还以一个区别，刚才的那个方法一次可以插入多条记录，此方法一次只能插入一条记录
INSERT users SET username='Ben',password='456';
第三种方法：
INSERT [INTO] tbl_name [(col_name,...)] SELECT...
说明：此方法可以将查询结果插入到指定数据表中。
单表更新记录UPDATE
UPDATE：
更新记录（单表更新）
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1 | DEFAULT} [,col_name2={expr2 | DEFAULT}]... [WHERE where_condition]
更新记录：省略条件将会更新所有记录
UPDATE users set age=age+5;#把所有记录的age增加5
更新记录：多列
UPDATE users SET age=age-id,sex=0;#age=age-id没有实际意义，只是为了演示
更新记录：加条件
UPDATE users SET age=age+10 WHERE id%2=0;#id为偶数的年龄增加10
单表删除记录
单表删除：
DELETE FROM tbl_name [WHERE where_condition]
如果省略WHERE条件，将删除所有记录
DELETE FROM users WHERE id=6；#id=6的记录被删除
此时再插入一条记录：
INSERT users VALUES(NULL,'111','222',33,NULL);#id为9不是6
*把记录删除后，即使id不连续，新插入的id仍然是已有的最大的id号加1而不是被删除的id加1
查询表达式解析
SELECT:
SELECT select_expr [,select_expr...]
[
FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | position} [ASC | DESC],...]
[HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC | DESC],...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
]
通常SELECT查询在数据库操作中占80%以上
SELECT VERSION();#查找某一个函数（版本）
SELECT NOW();#当前时间
SELECT 3+5;#查找表达式结果
以上为省略数据表，这些情况也必须省略数据表，因为它只是计算表达式或函数的结果，不依赖任何表
查询表达式select_expr
- 每一个表达式表示想要的一列，必须有至少一个。
- 多个列之间以英文逗号分隔。
- 星号（）表示所有列。tbl_name.可以表示命名表的所有列。
- 查询表达式可以使用[AS] alias_name为其赋予别名。
- 别名可以用于GROUP BY,ORDER BY或HAVING子句。
SELECT id,username FROM users;#选择某一列或几列查询
SELECT username,id FROM users;#顺序可以和原表中不同，SELECT的顺序影响查询结果中的顺序
*表示所有列
tbl_name.*可以表示命名表的所有列，如：
SELECT users.id,users.username FROM users;#选择users表中的id和users表中的username
对于单表查询，没有必要再字段前加上表名，如果以后使用到多表连接就会使用到，若果两张表存在相同的字段就不可以只写字段名了，这时就要使用表名.列名查询
如果某些字段比较长，可以为其赋予别名
SELECT id AS userid,username AS uname FROM users;#为id赋予别名userid,为username赋予别名uname
字段别名也会影响到查询结果集
别名中的AS可以使用，也可以不使用，建议使用SELECT查询时都加上AS，避免发生以下错误：
SELECT id username FROM users;#username别名和字段名相同，系统会认为是别名，结果只会产生一列id而且username是其别名
where语句进行条件查询
条件表达式WHERE
- 对记录进行过滤，如果没有指定WHERE子句，则显示所有记录。
- 在WHERE表达式中，可以使用MySQL支持的函数或运算符。
group by语句对查询结果分组
查询结果分组GROUP BY
[GROUP BY {col_name | position} [ASC | DESC],...]
ASC升序（默认），DESC降序，如果存在多个分组条件，之间用逗号分隔
按照性别分组
SELECT sex FROM users GROUP BY sex;#结果只有两个0和NULL
Paste_Image.png
分组除了写列名字还可以写位置
SELECT sex FROM users GROUP BY 1;#1是指SELECT中第一个出现的字段一般建议指定列名称而不是位置，位置比较麻烦
having语句设置分组条件
分组条件：对部分记录进行分组
分组条件HAVING
[HAVING where_condition]
SELECT sex FROM users GROUP BY 1 HAVING age>35；
系统提示错误，因为在使用HAVING进行分组条件的指定时，一定要保证分组条件要么为一个聚合函数要么要保证HAVING 后的字段必须出现在SELECT所选择的列中，否则会出现错误
SELECT sex,age FROM users GROUP BY 1 HAVING age>35；#HAVING 后的字段出现在SELECT所选择的列中了，没有任何结果，但是语法是正确的
``SELECT sex FROM users GROUP BY 1 HAVING count(id)>=2；#分组条件为聚合函数（只有一个返回结果的函数，如sum(),avg(),max(),min(),count()）。结果就一个（sex分组中计算数目大于2的）`
Paste_Image.png
order by语句对插询结果排序
对查询结果进行排序
[ORDER BY {col_name | expr | position} [ASC | DESC],...]
SELECT * FROM users ORDER BY id DESC;#按照id降序排列
如果第一个字段的排序不能得出想要的结果，还可以再按照第二个字段排序：
SELECT * FROM users OPDER BY age,id DESC;#先按照age降序排列，如果有相同的值再按id降序排列
limit语句限制插询数量
有两种语法结构限制返回数量，第一个比较常用
第一种：
限制查询结果返回的数量LIMIT
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
SELECT * FROM users LIMIT 2;#从第一条开始返回，一共返回2条记录
SELECT * FROM users LIMIT 3,2;#返回第4条和第5条记录，因为SELECT是从0开始编号的
id的编号和SELECT所选择的记录的编号没有关系，如
SELECT * FROM user ORDER BY id DESC LIMIT 2,2;#返回第7和第5条记录，而不是id为3和4 的记录
再看INSERT SET语句：把选择的结果存储到指定的数据表
插入记录INSERT
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},...
说明：与第一种凡是的区别在于，此方法可以使用子查询（SubQuery）
CREATE TABLE test(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20)
);
从users表中选择数据插入到test表中：
INSERT test SELECT username FROM users WHERE age>=30;#错误，因为test表中有两列，而选择出的结果只有一列
应该修改为：
INSERT test(username) SELECT username FROM users WHERE age>=30;#插入成功
offset通常用于分页
会使用这个语句进行分页：
限制查询结果返回的数量LIMIT
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
偏移值=(当前页码-1)每页所显示的记录数*
小结
Paste_Image.png
Paste_Image.png
Paste_Image.png
Paste_Image.png
Paste_Image.png
