DQL数据库其他查询语句与备份还原

目录

一、其他查询语句

1.分组查询

2.筛选查询

3.分页查询

4.数据库的约束 

---

创建库

-- 创建库
CREATE DATABASE mysql_02;

选择库

-- 选择库
USE mysql_02;

创建表

--创建表
CREATE TABLE student(
	id INT,   -- id编号
	NAME VARCHAR(20), -- 姓名
	sex VARCHAR(2), -- 性别
	chinese INT,  -- 语文
	english INT, -- 英语
	math INT -- 数学
);

插入数据

-- 插入数据
INSERT INTO student(id,NAME,sex,chinese,english,math) VALUES
(1,'武松','男',89,78,90),(2,'潘金莲','女',67,53,95),
(3,'扈三娘','女',87,78,77),(4,'武大郎','男',88,98,92),
(5,'西门庆','男',82,84,67),(6,'玉面狐','女',55,85,45),
(7,'牛魔王','男',75,65,30),(8,'孙悟空','男',90,100,91),
(9,'唐三','男',99,99,99),(10,'小舞','女',60,95,85),
(11,'石昊','男',96,68,40),(12,'狠人','女',98,90,20);

查询表数据

-- 查询表数据
SELECT	* FROM student;

最终的表

  

 本文是以上述创建的表来进行操作的

一、其他查询语句

1.分组查询

分组查询使用关键字group by

注意事项1:gruop by 的后面不能使用聚合函数

语句:

SELECT 
    字段列表(可以包含查询的分组字段) 
FROM
    表名 
GROUP BY 
    分组字段;

需求:按照性别进行分组,查询分组信息和总人数(总人数用关键字count

SELECT 
     sex '性别', -- 查询分组字段
     COUNT(id) '总人数'
FROM
     student
GROUP BY sex ;

如果现在有where条件,并且还有分组的查询

需求:要按性别分组,查询性别以及数学的平均分

条件:数学成绩不小于70分的人参与分组

select
     sex,
     avg(math)
from  
    student
group by 
	sex
where 
	math > 70 ;

 

运行后,出现错误代码

正确的应该是

SELECT
    sex '性别',
    AVG(math) '数学平均分' 
FROM
    student

WHERE 
	math > 70 
	
GROUP BY sex ;

注意事项: group by 后面不能使用where条件,应该先满足这个条件,才能进行分组

2.筛选查询

筛选查询使用关键字having

having后面是可以使用聚合函数的,且having必须置于group by之后

需求:按性别进行分组,查询分组信息以及数学平均分和总人数
       条件:数学成绩不小于70分的人参与分组
              筛选:总人数大于2的那一组

SELECT
	sex '学生性别',
	AVG(math) '数学平均分',
	COUNT(id) '总人数'
FROM

	student
    
 WHERE 
	math > 70 
 GROUP BY 
	sex 
 
 HAVING  
	COUNT(id) > 2 ;

注意事项:
    关于group by和having使用的先后顺序,而且有where条件
    where在group by 之前,having在group by 之后
    group by不能使用聚合函数,having是可以使用聚合函数查询的 

3.分页查询

筛选查询使用关键字limit

语法格式:
    select
        字段列表
    from
        表名
    limit
        起始行数,每页显示的条数;

注意:起始行数从0开始算的

算法:(当前页码-1)*每页显示的条数

需求:每页显示4个学生数据,求每页的分页数据

第一页:

SELECT
	*
FROM
	student
LIMIT
	0,4;

 第二页:

SELECT
	*
FROM
	student
LIMIT
	4,4;

 第三页:

SELECT
	*
FROM
	student
LIMIT
	8,4;

 4.数据库的约束 

约束:在操作数据库的时候,约束管理人员使用数据的时候行为;

举例: 有一张员工表,插入一行数据,没有给员工给性别值,默认值就是null,这毫无意义

默认约束 default :当在给某个字段没有插入值的时候,这个默认约束就可以作用,给字段设置默认值

创建一张员工表

CREATE TABLE emp(
	id INT, -- 员工编号
	emp_name VARCHAR(10),-- 员工姓名
	gender VARCHAR(5) -- 员工性别  
);

插入正常数据

INSERT INTO emp VALUES(1,'孙悟空','男') ;

 插入部分字段

INSERT INTO emp(id,emp_name) VALUES(2,'猪八戒') ; 

 可以发现猪八戒的性别是null,没有任何意义

创建新表加入默认约束

CREATE TABLE emp(
	id INT, -- 员工编号
	emp_name VARCHAR(10),-- 员工姓名
	gender VARCHAR(5)  DEFAULT '男' -- 员工性别   ,加入默认约束
);

再插入部分字段

INSERT INTO emp(id,emp_name) VALUES(2,'猪八戒') ; 

 可以发现没有给猪八戒添加性别,但是表自动添加了

也可以更改字段类型加入默认约束

ALTER TABLE emp	MODIFY gender VARCHAR(5) DEFAULT '男' ;