Linux数据库：【表的约束】【表的基本查询】

一.表的约束

1.1空属性 not null

1.2默认值 default

空属性和默认值一起使用？

1.3列描述 comment

1.4 zerofill

1.5 主键

1.6 自增长

1.7 唯一键

1.8 外键

二. 表的基本查询

2.1 Create

2.1.1单行数据 + 全列插入

2.1.2多行数据 + 指定列插入

2.1.3 插入否则更新

2.1.4 替换

2.2 Retrieve

2.2.1 select 列

2.2.1.1 全列查询

2.2.1.2指定列查询

2.2.1.3 查询字段为表达式

2.2.1.4为查询结果指定别名

2.2.1.5结果去重 distinct

2.2.2where条件

2.2.2.1英语不及格的同学及英语成绩 ( < 60 )

2.2.2.2语文成绩在 [80, 90] 分的同学及语文成绩

2.2.2.3 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

2.2.2.4 姓孙的同学及孙某同学

2.2.2.5语文成绩好于英语成绩的同学

2.2.2.6 总分在 200 分以下的同学

2.2.2.7 语文成绩 > 80 并且不姓孙的同学

2.2.2.8孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

2.2.2.9 NULL 的查询

2.2.3结果排序 order by

2.2.3.1 同学及数学成绩，按数学成绩升序显示

2.2.3.2同学及 qq 号，按 qq 号排序显示

2.2.3.3查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

2.2.3.4查询同学及总分，由高到低

2.2.4 筛选分页结果 limit

2.3 Update

2.3.1将孙悟空同学的数学成绩变更为 80 分

2.3.2将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

2.3.3将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

2.3.4将所有同学的语文成绩更新为原来的 2 倍

2.4 Delete

2.4.1 删除数据 delete

2.4.1.1 删除孙悟空

2.4.1.2 删除整张表

2.4.2截断表 truncate

2.5插入查询结果 rename

2.6 聚合函数

2.6.1 统计班级共有多少同学 count

2.6.2 统计qq号有多少个

2.6.3统计本次考试的数学成绩分数个数

2.6.4统计总分 sum

2.6.5统计平均总分

2.6.6返回英语最高分

2.6.7返回 > 70 分以上的数学最低分

2.7group by子句的使用

一.表的约束

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。

1.1空属性 not null

两个值：null（默认的）和not null(不为空)

数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办

法参与运算。

比如我在创建一个班级表的时候就可以，设置一下不为空的属性（not null）：

表格里的Null的那一列就变成了NO。

当我们在插入数据的时候这两行数据就不可以为null。如下图：

下面的那个我们就不可以进行插入。

1.2默认值 default

默认值实际上就是缺省值，当我们不添加某一个值的时候，mysql会使用默认值。如果我们主动的添加了值就用我们自己的值。

这里面我设置了age的默认值和sex的默认值：

空属性和默认值一起使用？

这里还是上面的表，我修改了t1的sex：

一种是我不传sex，会使用默认值。另一种是我传null，这里会报错。看现象：

1.3列描述 comment

这个很简单，就充当备注的意思：

使用desc无法看到

show查看详细信息：

1.4 zerofill

刚开始学习数据库时，很多人对数字类型后面的长度很迷茫。

可以看到int(11),这个代表什么意思呢？整型不是4字节码？这个10又代表什么呢？其实没有zerofill这个属性，括号内的数字是毫无意义的。

但是对列添加了zerofill属性后，显示的结果就有所不同了。修改表的属性：

可以发现，除了10剩下的全都补0了。这就是zerofill属性的作用，如果宽度小于设定的宽度（这里设置的是5），自动填充0。要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1。为什么是这样呢？我们可以用hex函数来证明。

可以看出数据库内部存储的还是A,00010只是设置了zerofill属性后的一种格式化输出而已。

1.5 主键

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。

主键约束：主键对应的字段中不能重复，一旦重复，操作失败。

当表创建好以后但是没有主键的时候，可以再次追加主键

alter table 表名 add primary key(字段列表)

删除主键

alter table 表名 drop primary key;

复合主键

在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。

这个是直接添加：

插入两次的话就会失败：

这里还可以创建表成功后，在去添加主键，运用一下上面的命令：

1.6 自增长

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值 +1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。

自增长的特点:

任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）

自增长字段必须是整数

一张表最多只能有一个自增长

此时再去添加字段的话：

索引：

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

1.7 唯一键

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

关于唯一键和主键的区别：我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。乍一听好像没啥区别，我们举一个例子

假设一个场景(当然，具体可能并不是这样，仅仅为了帮助大家理解) 比如在公司，我们需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息，一个身份证号码，一个是员工工号，我们可以选择身份号码作为主键。而我们设计员工工号的时候，需要一种约束：而所有的员工工号都不能重复。具体指的是在公司的业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯一键。一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整

1.8 外键

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique 约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。

外键：

foreign key (字段名) references 主表(列)

先创建主表，再创建从表：

给主表插入数据：

给从表插入数据：

插入班级id为null，比如来了一个学生，目前还没有分配班级

理解外键约束

首先我们承认，这个世界是数据很多都是相关性的。

理论上，上面的例子，我们不创建外键约束，就正常建立学生表，以及班级表，该有的字段我们都有。此时，在实际使用的时候，可能会出现什么问题？有没有可能插入的学生信息中有具体的班级，但是该班级却没有在班级表中？这很明显是有问题的。因为此时两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题。解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给mysql去审核了，提前告诉mysql 表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql不允许你插入。

插入一个班级号为30的学生，因为没有这个班级，所以插入不成功，这就是外键约束

二. 表的基本查询

2.1 Create

-- 创建一张学生表
CREATE TABLE students (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,sn INT NOT NULL UNIQUE COMMENT '学号',name VARCHAR(20) NOT NULL,qq VARCHAR(20)
);

2.1.1单行数据 + 全列插入

下面我插入三条数据：

2.1.2多行数据 + 指定列插入

加上逗号就行了：

2.1.3 插入否则更新

由于主键或者唯一键对应的值已经存在而导致插入失败

-- 主键冲突
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师');
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
-- 唯一键冲突
INSERT INTO students (sn, name) VALUES (20001, '曹阿瞒');
ERROR 1062 (23000): Duplicate entry '20001' for key 'sn'

可以选择性的进行同步更新操作语法：

INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...

这里我就故意写一个与主键重复的人，并且修改。

相当于是如果主键冲突了，就把插入操作变为update。这里是2 row affected。

2.1.4 替换

-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
Query OK, 2 rows affected (0.00 sec)
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

下面就可以看出，一行被影响和两行被影响，且成功把nihao替换为ok：

2.2 Retrieve

先准备好表：

-- 创建表结构 
CREATE TABLE exam_result ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL COMMENT '同学姓名', chinese float DEFAULT 0.0 COMMENT '语文成绩', math float DEFAULT 0.0 COMMENT '数学成绩', english float DEFAULT 0.0 COMMENT '英语成绩' 
); -- 插入测试数据 
INSERT INTO exam_result (name, chinese, math, english) VALUES ('唐三藏', 67, 98, 56), ('孙悟空', 87, 78, 77), ('猪悟能', 88, 98, 90), ('曹孟德', 82, 84, 67), ('刘玄德', 55, 85, 45), ('孙权', 70, 73, 78), ('宋公明', 75, 65, 30); 
Query OK, 7 rows affected (0.00 sec) 
Records: 7 Duplicates: 0 Warnings: 0

2.2.1 select 列

2.2.1.1 全列查询

- - 通常情况下不建议使用 * 进行全列查询

- - 1 . 查询的列越多，意味着需要传输的数据量越大；

- - 2 . 可能会影响到索引的使用。

2.2.1.2指定列查询

2.2.1.3 查询字段为表达式

甚至是：

2.2.1.4为查询结果指定别名

as去掉也可以：

2.2.1.5结果去重 distinct

去重后：

2.2.2where条件

比较运算符：

逻辑运算符：

2.2.2.1英语不及格的同学及英语成绩 ( < 60 )

2.2.2.2语文成绩在 [80, 90] 分的同学及语文成绩

或者不使用比较运算符，使用between and：

2.2.2.3 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

或者是使用in加上一段：

2.2.2.4 姓孙的同学及孙某同学

-- % 匹配任意多个（包括 0 个）任意字符 
-- _ 匹配严格的一个任意字符

2.2.2.5语文成绩好于英语成绩的同学

2.2.2.6 总分在 200 分以下的同学

-- WHERE 条件中使用表达式

-- 别名不能用在 WHERE 条件中

2.2.2.7 语文成绩 > 80 并且不姓孙的同学

2.2.2.8孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

2.2.2.9 NULL 的查询

查询class_id 不为null的

NULL 和 NULL 的比较，= 和 <=>的区别

2.2.3结果排序 order by

-- ASC 为升序（从小到大） 
-- DESC 为降序（从大到小） 
-- 默认为 ASC SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];

注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

2.2.3.1 同学及数学成绩，按数学成绩升序显示

2.2.3.2同学及 qq 号，按 qq 号排序显示

2.2.3.3查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

2.2.3.4查询同学及总分，由高到低

2.2.4 筛选分页结果 limit

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死按 id 进行分页，每页 3 条记录，分别显示第 1、2、3 页

例如：

2.3 Update

UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]

2.3.1将孙悟空同学的数学成绩变更为 80 分

2.3.2将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

2.3.3将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

2.3.4将所有同学的语文成绩更新为原来的 2 倍

不用where就是更新全部的：

2.4 Delete

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

2.4.1 删除数据 delete

2.4.1.1 删除孙悟空

2.4.1.2 删除整张表

这是很危险的操作！

2.4.2截断表 truncate

TRUNCATE [TABLE] table_name

注意：这个操作慎用

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；

2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚

3. 会重置 AUTO_INCREMENT 项

删除之后，重新查看表里面的数据：

2.5插入查询结果 rename

INSERT INTO table_name [(column [, column ...])] SELECT ...

先创建原始数据表：

-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样

-- 将 duplicate_table 的去重数据插入到 no_duplicate_table

-- 通过重命名表，实现原子的去重操作

2.6 聚合函数

2.6.1 统计班级共有多少同学 count

使用 * 做统计，不受 NULL 影响

当然重命名也是可以的：

使用表达式可以：

2.6.2 统计qq号有多少个

注意null不作数

2.6.3统计本次考试的数学成绩分数个数

2.6.4统计总分 sum

2.6.5统计平均总分

2.6.6返回英语最高分

运用之前的也可以找：

2.6.7返回 > 70 分以上的数学最低分

2.7group by子句的使用

在select中使用group by 子句可以对指定列进行分组查询

select column1, column2, .. from table group by column;

准备工作，

创建一个雇员信息表（来自oracle 9i的经典测试表）

EMP员工表

DEPT部门表

SALGRADE工资等级表

随后插入数据：

如何显示每个部门的平均工资和最高工资，总共三个组，group by指定列名，实际分组，是用该列的不同行数据来进行分组的，分组的条件deptno，组内一定是相同的，也就是说，可以被聚合压缩：

显示每个部门的每种岗位的平均工资和最低工资

如果我在加一个ename呢？

名字人人都有，无法分组，分组其实就是把相同的东西放到一起。

显示平均工资低于2000的部门和它的平均工资

统计各个部门的平均工资

having和group by配合使用，对group by结果进行过滤

--having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。

where（纯粹的对表筛选）与having（分组之后的筛选）对比：