D16-MySQL在相遇（查询与约束）

DSQ语句

条件查询

• 想要获得满足条件的数据，
  格式：

selcet 字段名
from 表名
where 条件;
流程：取出表中的每条数据，满足条件的记录就返回，不满足条件的数据就不返回。

比较运算符

> 大于， < 小于， <=小于等于，>=  大于等于， = 等于， != ，<>不等于

例：查询math分数大于80的学生

select *
from student
where math>80;

逻辑运算符

and(&&):多个条件同时满足
or(||):多个条件中其中一个满足
not(!): 不满足

• 例
  查询age大于35且性别为男的学生（两个条件同时满足）
  select * from student where age>35 and sex='男';
  

in 关键字：
语法格式：select 字段名 from 表名 where 字段 in (数据1，数据2，数据3…)；
in里面的数据都会做为一次条件，只要满足条件的就会显示。

• 例
  查询id是1或3或5的学生

SELECT *
FROM student
WHERE id IN(1,3,5);

范围–between …and…

BETWEEN 值1 AND 值2：表示从值1到值2范围，左闭右闭，包头包尾。

• 例
  查询math成绩大于等于75，且小于等于90的学生
  SELECT * FROM student WHERE math BETWEEN 75 AND 90;

相当于 ：
SELECT *
FROM student
WHERE math>=75 AND math<=90;

LIKE

like表示模糊查询
格式:

SELECT *
FROM 表名
WHERE 字段名
LIKE ‘通配字符串’;

通配字符：（1）% 表示0个或多个字符（任意个字符）（2）_(下划线):表示一个字符
%小技巧：

1. ‘马%’ ：查询以“马”开头的
2. ‘%马’ ：查询以“马”结尾的

• ‘%马%’ ：查询包含“马”的

• 例:查询姓马的学生

SELECT *
FROM student
WHERE LIKE '马%';

• 例：查询姓名中 包含‘德’子的学生

SELECT *
FROM student
WHERE LIKE '%德%';

• 例 ：查询姓马。且姓名有三个字的学生

SELECT *
FROM student
WHERE LIKE '马__';

排序

通过ORDER BY 子句，可以将查询出的结果进行排序

SELECT  字段名
FROM 表名
WHERE 字段=值
ORDER BY 字段名[ASC| DESC];
ASC:升序
DESC:降序

单列排序

• 使用一个字段排序
• 例：查询所有数据， 使用年龄降序排序

SELECT *
FROM student
ORDER BY  age DESC;

组合排序

定义：先按第一个字段进行排序，如果第一个字段相同，才按第二个字段排序。

SELECT 字段名
FROM 表名
WHERE 字段=值 ORDER BY 字段1 [ >DESC|ASC ],字段2 [ ASC|DESC ];

• 例 查询所有数据，在年龄降序的基础上 ，如果年龄相同再以数学成绩降序排序

SELECT *
FROM student
ORDER BY age DESC, math DESC;

聚合函数（重点）

概念： 纵向查询，对一列的值进行计算，然后返回结果。 聚合函数会忽略空值 。

五个聚合函数

• count： 统计指定列记录数，记录为NULL的不统计。
• sum： 计算指定列的数值和，如果数值不是数值类型，那么计算结果为0；
• max： 计算指定列的最大值
• min： 计算指定列的最小值
• avg： 计算指定列的平均值， 如果不是数值类型，那么计算结果为0。
  使用：

写在SQL 语句SELECT后，字段名的地方
SELECT 字段名
FROM 表名;

• 例：查询学生总数

SELECT COUNT(math)
FROM student;
// 不会计算NULL值

SELECT COUNT(IFNULL(math，0))
FROM student；
// 为NULL，则给个默认值0.

• 例：查询年龄大于40的总数

SELECT COUNT(*)
FROM student
WHERE age>40;

分组–GROUP BY

• ** 定义：** 分组查询是指使用** group by**语句对查询信息进行分组，相同数据作为一组。

SELECT 字段1，字段2…
FROM 表名
GROUP BY 分组字段[HAVING 条件];

• 如何分组？
  将字段结果中相同内容作为一组。
• 例 ：查询所有数据，按性别分组，统计分组的人数

SELECT sex，COUNT(*)
FROM student
GROUP BY sex；

• 例 ：查询年龄大于25岁的人,按性别分组,统计每组的人数

SELECT sex，COUNT(*)
 FROM student
 WHERE age>25
 GROUP BY sex；

having与where的区别：

• having是在分组后对数据进行过滤
• where是在分组前对数据进行过滤
• having后面可以使用聚合函数
• where后面不可以使用聚合函数

LIMIT 语句

**作用：**限制查询的记录的条数。

SELECT *|字段列表[as 别名]
FROM 表名
[WHERE子句] [GROUP BY子句][HAVING子句] [LIMIT 子句];

思考： limit子句为什么排在最后？

• 因为前面所有限制条件都处理完了，只剩下显示多少条记录
  LIMIT语法格式：

LIMIT offset,length; 或者limit length;
offset是指偏移量，可以认为是跳过的记录数量，默认为0 , length是指需要显示的总记录数。

数据备份

source命令备份与还原

备份格式： mysqldump -u用户名 -p密码 数据库 > 文件的路径
**还原格式：**SOURCE 导入文件的路径。

注意：还原的时候的需要先登录MySQL，并选中对应的数据库。

数据库的约束

作用： 对表中的数据进行进一步的限制，保证数据的正确性、有效性、完整性。
约束种类：

• primary key: 主键
• unique：唯一
• not null ：非空
• default：默认
• foreign key：外键

主键的作用

作用： 用来唯一表示一条记录。每个表都应该有一个主键，并且每个表只能有一个主键。
** 哪个字段应该作为表的主键？**

• 通常不用业务字段作为主键， 单独

创建主键

primary key 主键的特点：

• 主键必须包含唯一的值
• 主键列不能包含NULL值
  创建主键方式：
• 在创建表的时候给字段添加主键

字段名 字段类型 primary key;

CREATE TABLE person(
	id INT PRIMARY KEY,
	`NAME`  VARCHAR(10)
);

• 在已有表中添加主键：

ALTER TABLE 表名 ADD primary key(字段名);

ALTER TABLE student 
ADD PRIMAR KEY(id);

主键的删除：
ALTER TABLE 表名 DROP PRIMARY KEY;

ALTER TABLE student DROP PRIMARY KEY(id);

联合主键：

• 多个列共同组成一个主键
  **格式：**ALTER TABLE 表名 ADD PRIMARY KEY(列名1，列名2);

unique：唯一约束

可以保证某一列的值不能重复。

#创建表的时候对某一列添加唯一约束
CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT, --主键自动增长
	`name`  VARCHAR(10) UNIQUE, --执行，使用unique进行唯一约束 );
	address VARCHAR(10)
);

特点：

• 不能添加重复数据
• 不限制多个NULL值
  删除唯一约束：
• ALTER TABLE 表名 DROP INDEX 列名;

default:默认值约束

作用： 如果添加数据的时候， 不给出该列的值，那么该列数据就会使用默认值。
格式：

字段名 数据类型（长度） DAFAULT 默认值

CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` 	VARCHAR(10) DEFAULT '佚名',
	address VARCHAR(20)
	
);

AUTO_INCREMENT：自动增长列

**注意：**自增长列一定要对整数列使用。

• 一般会对主键设置自动增长，让系统自动去维护主键的值。

补充： delete与truncate的区别
**delete删除数据：**是逐条删除所有数据，不会重置自增长序列。

DELETE FROM person;

truncate删除数据： 删除所有数据， 直接摧毁表，然后新建一张一模一样的表，会重置 自增长序列。

TRUACATE TABLE person;

NOT NULL：非空约束

作用： 可以保证某一列的数据不能为null。

• 例

CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(10) NOT NULL  --对name使用非空约束
	address VARCHAR(20)
);

删除： ALTER TABLE 表名 MODIFY 列名 数据类型（长度）；

ALTER TABLE person
MODIFY `name` VARCHAR(20);

表关系

1. 表关系

• 一对一
• 一对多
• 多对多

2. 外键约束

• ** 定义：** 一张表中的某个字段引用另一个表的主键。主表： 约束别人 副表/从表： 使用别人的数据的， 被人约束。

2.1 创建外键

1.新建表时增加外键：[constraint][外键约束名称] foreign key(外键字段)references 主表名（主键字段名）
关键字解释：

• constraint – 约束关键字
• foreign key（外键字段名） – 某个字段作为外键
• references – 主表名（主键字段名）表示参照主表中的某个字段

CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)

2.已有表增加外键： ALTER TABLE 从表 ADD [ CONSTRAINT ] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段
名);

3.删除外键：

格式： ALTER TABLE 从表 DROP foreign key 外键名称;

 
ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;