mysql——第二课

学生表

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `sex` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `c_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1574588396 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

班级表

CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

查询：

询是数据库操作的核心，用于从数据库中检索、插入、更新和删除数据。

-- 从表中选取所有列的数据
SELECT * from student
-- 从表中选取指定列的数据
select name,age,sex FROM student;

between:

在 SQL 里，BETWEEN 操作符是一个很实用的工具，主要用于筛选出指定范围内的数据。此范围涵盖数值、日期或者文本等不同类型的数据。

SELECT * from student where age BETWEEN 20 and 100;

in:

在 SQL 中，IN 操作符是一个常用的条件筛选工具，它允许你在 WHERE 子句里指定多个值，用于判断某列的值是否包含在给定的值列表中。

select * from student where age in(45,21);
select * from student where id in(1,3);

 与或非：

1. AND 运算符

AND 运算符用于在 WHERE 子句中连接多个条件，只有当所有条件都为真时，对应的记录才会被返回。

SELECT *
FROM employees
WHERE department = 'IT' AND salary > 5000;

此查询仅会返回 department 是 IT 并且 salary 超过 5000 的员工记录，也就是 Bob 和 David 的信息。

2. OR 运算符

OR 运算符同样用于在 WHERE 子句中连接多个条件，只要其中任何一个条件为真，对应的记录就会被返回。

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR ...;

该查询会返回 department 为 IT 或者 salary 大于 6000 的员工记录，即 Bob、Charlie 和 David 的信息。

3. NOT 运算符

NOT 运算符用于对一个条件取反，也就是当条件为假时，对应的记录才会被返回。

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

通过灵活运用 AND、OR 和 NOT 运算符，你能够构建出满足各种需求的复杂查询条件。

ISNULL()函数的使用

ISNULL(expr)，如果expr的值为null，则返回1，如果expr1的值不为null，则返回0。

SELECT * from student where age is not null;

模糊查找

在 SQL 中，模糊查找是一种强大的功能，允许你在数据库中查找与特定模式匹配的数据，而不是精确匹配。这在处理文本数据时特别有用，比如在搜索功能中。主要通过 LIKE 操作符结合通配符来实现。

通配符

• 百分号（%）：代表零个、一个或多个任意字符。
• 下划线（_）：代表单个任意字符。
• 方括号（[]）：用于指定一个字符范围，只适用于部分数据库系统（如 SQL Server）。
• 脱字符（[^ ]）：用于排除指定字符范围，同样只适用于部分数据库系统（如 SQL Server）

SELECT * from student where name like"吕_";
SELECT * from student where name like"吕__";
SELECT * from student where name like"吕%";
SELECT * from student where name like"%吕"

分页查询

在 SQL 里，LIMIT 是进行分页查询的常用关键字，它能限定查询结果返回的行数，借助 LIMIT 与偏移量，就能实现分页查询。不过不同数据库系统对 LIMIT 的使用方式稍有差异。

-- limit a,b a表示起始索引值 b表示查询个数
-- limt b offset a
SELECT * from student limit 0,5;
SELECT * from student limit 5,5;

排序子语句

在 SQL 中，排序子语句通常指的是 ORDER BY 子句，它用于对查询结果集按照一个或多个列进行排序。

-- --  排序子语句
-- order by 列名 desc 降序 asc升序
select * from student order by age desc;
--  where 排序 限制
SELECT *from student where sex="男" ORDER BY age asc limit 0,5

聚合函数 分组函数

在 SQL 中，聚合函数和分组函数（通常借助 GROUP BY 子句与聚合函数配合使用）是用于对数据进行汇总和分析的重要工具，

 COUNT()

• 功能：用于统计记录的数量。
• 示例：假设有一个 employees 表，要统计员工的数量。

SELECT COUNT(*) FROM employees;

SUM()

• 功能：用于计算某列数值的总和。
• 示例：计算 employees 表中所有员工的工资总和。

SELECT SUM(salary) FROM employees;

AVG()

• 功能：用于计算某列数值的平均值。
• 示例：计算 employees 表中员工的平均工资。

SELECT AVG(salary) FROM employees;

分组函数（GROUP BY 子句）

GROUP BY 子句用于将查询结果按照一个或多个列进行分组，然后可以对每个组应用聚合函数。

-- 聚合函数 分组函数
-- min() 最小 max()最大 sum()求和 avg() 求平均 count()数量-->count不统计null字段
--  HAVING 子句：如果你需要对分组后的结果进行过滤，应该使用 HAVING 子句而不是 WHERE 子句。WHERE子句在数据分组前进行过滤，而 HAVING 子句在数据分组后对分组的结果进行过滤。
SELECT avg(age),c_id from student  where sex="男" GROUP BY c_id having c_id=1;

添加：

insert into student(name,age,sex) values("赵云",18,"男"); 

修改:

UPDATE student set name="诸葛亮",age=100,sex="男" where id=14;

删除:

DELETE from student where id=1;
delete from student where id in (5,12,13,4);

多表查询：

-- 重命名 as 新名字 as可省
select s1.*,c1.c_name 班级名称 from student as s1,class c1 where s1.c_id=c1.id

连表查询:

连表查询（也称为连接查询）是 SQL 中一种强大的功能，用于从多个表中检索相关的数据。当数据分散在不同的表中时，通过连表查询可以将这些表中的数据组合起来，以满足特定的查询需求。

1. 内连接（INNER JOIN）

• 原理：内连接只返回两个表中满足连接条件的行，即只返回两个表中匹配的记录。

2. 左连接（LEFT JOIN 或 LEFT OUTER JOIN）

• 原理：左连接返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则右表的列值将显示为 NULL。

3. 右连接（RIGHT JOIN 或 RIGHT OUTER JOIN）

• 原理：右连接与左连接相反，它返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则左表的列值将显示为 NULL。

4. 全连接（FULL JOIN 或 FULL OUTER JOIN）

• 原理：全连接返回两个表中的所有行，无论是否有匹配的行。如果某一行在另一个表中没有匹配的行，则对应的列值将显示为 NULL

SELECT * FROM STUDENT JOIN CLASS ON STUDENT.C_ID=CLASS.ID;
SELECT * FROM STUDENT LEFT JOIN CLASS ON STUDENT.C_ID=CLASS.ID;