数据库SQL语句

Day2 数据库SQL语句

一、查询

1、条件查询

# 查询所有学生信息
SELECT
	* 
FROM
	student;
# 查询学号、姓名、身份证号
SELECT
	StudentNo AS 学号,
	StudentName AS 姓名,
	IdentityCard AS 身份证号 
FROM
	student;# AS可省略
# 查询年级为2的学生信息
SELECT
	* 
FROM
	student 
WHERE
	GradeId = 2;
# 查询年级为2和3的学生信息
SELECT
	* 
FROM
	student 
WHERE
	GradeId = 2 
	OR GradeId = 3;# 方法1
SELECT
	* 
FROM
	student 
WHERE
	GradeId IN ( 2, 3 );# 方法2
# 查询年级为3的女学生（1,男 2,女）
SELECT
	* 
FROM
	student 
WHERE
	GradeId = 3 
	AND Sex = 2;
# 查询出生日期在1985-2024年的学生信息
SELECT
	* 
FROM
	student 
WHERE
	BornDate > '1985-01-01' 
	AND BornDate < '2024-12-31';# 方法1
SELECT
	* 
FROM
	student 
WHERE
	BornDate BETWEEN '1985-01-01' 
	AND '2024-12-31';# 方法2 (数值/时间)

2、模糊匹配

# like模糊查询
# %前后匹配所有  _一个字符
# 查询姓李的学生信息
SELECT
	* 
FROM
	student 
WHERE
	StudentName LIKE '李%';
# 查询籍贯在中关村的学生信息
SELECT
	* 
FROM
	student 
WHERE
	Address LIKE '%中关村%';
# 查询姓名为俩字的学生信息
SELECT
	* 
FROM
	student 
WHERE
	StudentName LIKE '__';

3、聚合查询

# 聚合查询
# 总记录数COUNT()、求和SUM()、平均分AVG()、最大值MAX()、最小值MIN()
# 查询学生总数
SELECT
	COUNT(*) 
FROM
	student;
# 查询学生成绩总和
SELECT
	SUM( StudentResult ) 
FROM
	result;
# 查询学生成绩的平均分
SELECT
	AVG( StudentResult ) 
FROM
	result;
# 查询学生成绩的最大值
SELECT
	MAX( StudentResult ) 
FROM
	result;
# 查询学生成绩的最小值
SELECT
	MIN( StudentResult ) 
FROM
	result;

4、分组聚合

# 分组聚合
# 分组后只能查询分组字段和聚合函数
# 查询男女学生人数
SELECT
	Sex 性别,
	COUNT(*) 总人数 
FROM
	student 
GROUP BY
	Sex;
# 查询每个年级男女学生人数
SELECT
	GradeId 年级,
	Sex 性别,
	COUNT(*) 总人数 
FROM
	student 
GROUP BY
	GradeId,
	Sex 
ORDER BY
	GradeId,
	Sex;
# 分组聚合后的聚合筛选用HAVING
# 查询每个年级男女学生人数大于8人
SELECT
	GradeId 年级,
	Sex 性别,
	COUNT(*) 总人数 
FROM
	student 
GROUP BY
	GradeId,
	Sex 
HAVING
	COUNT(*) > 5 
ORDER BY
	GradeId,
	Sex;
# 查询每个科目最高与最低的成绩信息
SELECT
	SubjectNo 科目,
	MAX( StudentResult ) 最高成绩,
	MIN( StudentResult ) 最低成绩 
FROM
	result 
GROUP BY
	SubjectNo;

5、排序

# 排序 ORDER BY
# 升序(默认): ASC 降序: DESC
# 对成绩表中的成绩升序/降序排序
SELECT
	* 
FROM
	result 
ORDER BY
	StudentResult DESC;
# 多列排序: 优先按照第一个排序,第一个相同则按照第二个排序
SELECT
	* 
FROM
	student 
ORDER BY
	GradeId,
	BornDate DESC;
# NULL值判断
# IS NULL 或 IS NOT NULL
SELECT
	* 
FROM
	student 
WHERE
	LoginPwd IS NOT NULL;

6、子查询

# 子查询
# 建议用IN
# 查询'大一'学生的信息
SELECT
	* 
FROM
	student 
WHERE
	GradeId = ( SELECT GradeID FROM grade WHERE GradeName = '大一' );
# 查询'大一''大二'学生的信息
SELECT
	*
FROM
	student
WHERE
	GradeId IN ( SELECT GradeID FROM grade WHERE GradeName IN ('大一','大二') );

# 查询学生表前3条记录
SELECT
	* 
FROM
	student 
	LIMIT 3;

# 分页
# LIMIT 从第几页开始查((当前页-1)*每页显示的记录数), 每页显示的记录数
SELECT
	* 
FROM
	student 
	LIMIT 6,
	3;

# 常用数据类型
# SQL: INT BIGINT CHAR(指定长度) VARCHAR(指定长度) TEXT DATE TIME DATETIME DOUBLE

# 主表：理论上不可随便删除
# 子表：理论上可以随便删除
# 主表的主键 = 子表的外键
# 表与表的关联是通过子表的外键

# 一对一
# 一对多
# 多对多 额外的关联表

7、连接

# 连接查询
# INNER JOIN内连接: 返回有关系的数据
# 查看学生信息与年级信息

SELECT
	* 
FROM
	student
	INNER JOIN grade ON student.GradeId = grade.GradeID;

SELECT
	stu.StudentNo,
	stu.StudentName,
	stu.Sex,
	stu.GradeId,
	gra.GradeName 
FROM
	student AS stu
	INNER JOIN grade AS gra ON stu.GradeId = gra.GradeID;
	
SELECT
	stu.StudentName,
	sub.SubjectName,
	res.StudentResult 
FROM
	student AS stu
	INNER JOIN result AS res ON stu.StudentNo = res.StudentNo
	INNER JOIN `subject` AS sub ON res.SubjectNo = sub.SubjectNo 
ORDER BY
	stu.StudentName;
	
# 外连接
# 查询指定表的所有记录，未关联的字段以NULL填充
# LEFT JOIN:左外连接
# RIGHT JOIN:右外连接
# 查询学生所有记录及连接信息
SELECT
	stu.StudentNo,
	stu.StudentName,
	stu.Sex,
	stu.GradeId,
	gra.GradeName 
FROM
	student AS stu
	LEFT JOIN grade AS gra ON stu.GradeId = gra.GradeID;

# 书写顺序 select  from  inner join  where  group by  having  order by  limit
# 执行顺序 from  inner join  where  group by  having  select  order by  limit

# 自连接查询
# 主
SELECT
	id, name
FROM
	my_department;
# 从
SELECT
	id, name, fu_id
FROM
	my_department;

SELECT
	zhu.name, 
	cong.name
FROM
	my_department zhu
	INNER JOIN my_department cong ON zhu.id = cong.fu_id;

二、增删改

1、新增

# 增删改
# 新增
INSERT INTO SUBJECT
VALUES
	( NULL, '玄学', 50, 1 );
# 插入部分列需要注意，主键是否自增，是否必填字段。
INSERT INTO SUBJECT ( SubjectName )
VALUES
	( '神学' );
# 批量新增
INSERT INTO SUBJECT ( SubjectName )
VALUES
	( '政治' ),
	( '哲学' ),
	( '文学' );

2、删除

# 删除
# 全表数据删除
DELETE 
FROM
	my_department;
# 根据主键删除
DELETE 
FROM
SUBJECT 
WHERE
	SubjectNo = 18;
# 批量删除
DELETE 
FROM
SUBJECT 
WHERE
	SubjectNo IN ( 19, 20 );
	
DELETE 
FROM
SUBJECT 
WHERE
	SubjectName = '哲学' 
	OR SubjectName = '文学';

3、修改

# 修改
# 全表数据修改
# 将科目表中的课时全部改成120，年级改成2
UPDATE
	`subject`
SET
	ClassHour = 120,
	GradeID = 2;

# 根据主键修改
# 修改学号为10067 的名字为吴迪，生日为1986-12-31 08:08:08
UPDATE
	student
SET
	StudentName = '吴迪',
	BornDate = '1986-12-31 08:08:08'
WHERE StudentNo = 10066;
# 批量修改
UPDATE
	student
SET
	StudentName = '小刚',
	BornDate = '2006-12-31 08:08:08'
WHERE StudentNo IN (1001,1002);
# 修改所有北京与上海的学生信息
UPDATE
	student
SET
	StudentName = '小白',
	BornDate = '2026-12-31 08:08:08'
WHERE
	Address LIKE '北京%' OR Address LIKE '上海%';