【MySQL】SQL语句查询、约束、备份与恢复

1. SQL语句查询

1. 排序

通过order by语句，可以将查询出的结果排序。放置在select语句的最后。

格式:

SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;

ASC 升序 (默认)

DESC 降序

题干：

#1.使用价格排序(降序)

#2.在价格排序(升序)的基础上，以pid排序(升序)

#3.显示商品的价格(去重复)，并排序(降序)

答案

#1.使用价格排序(降序)

SELECT * FROM product ORDER BY price DESC;

#2.在价格排序(升序)的基础上，以pid排序(升序)

SELECT * FROM product ORDER BY price ASC,pid ASC

#3.显示商品的价格(去重复)，并排序(降序)

SELECT DISTINCT price FROM product ORDER BY price DESC;

1. 聚合

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

今天我们学习如下五个聚合函数：

1. count：统计指定列不为NULL的记录行数；
2. sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
3. max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
4. min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
5. avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

题干

# 需求 计算商品总条数

# 需求 计算价格大于3000的商品的总条数

#需求 计算价格小于6元的 商品的总价格

#需求 计算 价格小于6元的 商品的平均价格

#需求 查询商品的最大价格和最小价格

答案：

# 需求 计算商品总条数

SELECT COUNT(*) FROM product

# 需求 计算价格大于3000的商品的总条数

SELECT COUNT(*) FROM product WHERE price > 3000

#需求 计算价格小于6元的 商品的总价格

SELECT SUM(price) FROM product WHERE price <6

#需求 计算 价格小于6元的 商品的平均价格

SELECT AVG(price) FROM product WHERE price<6

#需求 查询商品的最大价格和最小价格

SELECT MAX(price),MIN(price) FROM product;

1. 分组

用途：统计每种类别的信息。如统计班级中每种性别各多少人，每种年龄各多少人等。

关键字：group by

1. 格式：

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

分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用有点像where条件。

示例：

数据准备

CREATE TABLE person(

pid  INT(50) AUTO_INCREMENT PRIMARY KEY ,

pname VARCHAR(100)  ,

page INT(50) ,

psex VARCHAR(50),

className varchar(50)

);

INSERT INTO person VALUES (NULL,"张三",18,"男",'J12');

INSERT INTO person VALUES (NULL,"李四",18,"男",'J12');

INSERT INTO person VALUES (NULL,"王五",20,"男",'J34');

INSERT INTO person VALUES (NULL,"小花",21,"女",'J34');

INSERT INTO person VALUES (NULL,"王大炮",19,"女",'J12');

INSERT INTO person VALUES (NULL,"王五",21,"女",'J56');

INSERT INTO person VALUES (NULL,"小六",21,null,'J12');

表数据：

 

基本需求:

-- 统计 表中 有哪几种性别。 结果显示性别值

 

-- 统计表中有哪几种年龄。 结果显示年龄

 

-- 统计表中每种性别有多少人  结果显示 性别和对应的人数

 

-- 统计表中每种年龄各有多少人 结果显示年龄和对应人数

 

-- 统计表中每种姓名有多少人 结果显示 人名和人数

 

-- 统计表中大于18岁的年龄有哪几种。 结果显示年龄

 

-- 统计表中大于18岁的年龄有几种，每种年龄有多少人，  结果显示年龄和人数

 

-- 按照姓名进行分组,并统计出 每个名字对应的人数  结果要求显示 名字和 对应的人数 ,结果只显示出名字 包含王的

 

结果：

-- 统计 表中 有哪几种性别。 结果显示性别值 select psex 性别 from person group by psex; -- 统计表中有哪几种年龄。 结果显示年龄 select page 年龄 from person group by page; -- 统计表中每种性别有多少人  结果显示 性别和对应的人数 select psex 性别, count(*) 人数 from person group by psex; -- 统计表中每种年龄各有多少人 结果显示年龄和对应人数 select page 年龄,count(*) 人数 from person group by page; -- 统计表中每种姓名有多少人 结果显示 人名和人数 select pname 姓名,count(*)人数 from person group by pname; -- 统计表中大于18岁的年龄有哪几种。 结果显示年龄 select page 年龄 from person group by page having page>18; -- 统计表中大于18岁的年龄有几种，每种年龄有多少人，  结果显示年龄和人数 select  page 年龄,count(*) 人数 from person group by page having page>18; -- 按照姓名进行分组,并统计出 每个名字对应的人数  结果要求显示 名字和 对应的人数 ,结果只显示出名字 包含王的 select pname 姓名, count(*) 人数  from person group by pname having pname like '%王%';

1. having与where的区别:

1. having是在对分组后的结果进行过滤.主要是针对列，
2. where是在分组前对数据进行过滤，主要是针对行

-- 查询年龄大于18岁的 人的全部信息

select * from person where page > 18;

 

-- 查询年龄大于18岁的人,有哪几种年龄.

select page from person group by page having page>18;

 

-- 小技巧, 统计出每种XX有多少个  查询就按照XX进行分组  ==> select XX ,count(XX) from 表名 group by XX

-- 小技巧, having 要添加的条件 必须要确保 分组查询的结果中有对应的字段才 可以

 

强化： 多个字段分组

 -- 需求1: 统计出 各个班级中存在的性别

 

-- 需求2: 统计出 各个班级中存在的性别 按照班级排序

 

-- 需求3: 各个班级中存在的年龄 显示班级和年龄

 

-- 需求4 : 统计出 各个班级中对应性别的人数.  需要显示的字段是  班级 性别 人数, 展示的结果再按照 班级名字排序

 

-- 需求5: 统计出 各个班级中存在的年龄,以及该年龄对应的人数 结果用班级名字排序

 

答案：

-- 需求1: 统计出 各个班级中存在的性别 select className 班级,psex 性别 from person group by psex, className; -- 需求2: 统计出 各个班级中存在的性别 按照班级排序 select className 班级,psex 性别 from person group by psex, className order by className; -- 需求3: 各个班级中存在的年龄 显示班级和年龄 select className 班级 , page 年龄 from person group by className, page order by className; -- 需求4 : 统计出 各个班级中对应性别的人数.  需要显示的字段是  班级 性别 人数, 展示的结果再按照 班级名字排序 select  className 班级 ,psex 性别,count(*) 人数 from person group by className, psex order by className; -- 需求5: 统计出 各个班级中存在的年龄,以及该年龄对应的人数 结果用班级名字排序 select  className 班级,page 年龄 ,count(page) 人数 from  person group by page, className order by className;

1. 分页查询

  分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。

 

例如数据共有n条，每页显示10条，第一页显示1-10条，第二页显示11-20条...

1. 格式：

SELECT 字段1，字段2... FROM 表明 LIMIT M,N

 M: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数

 N: 整数，表示查询多少条数据

-- 查询 person 表中数据 从0号索引开始显示, 每页显示2条

-- 第1页

select * from person limit 0, 2;

-- 第2页

select * from person limit 2,2;

-- 第3页

select * from person limit 4,2;

 

1. 强化练习

1. 单表练习

建表语句：

create table student(

id int,

name varchar(20),

chinese float,

english float,

math float

);

测试数据：

INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90);

INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95);

INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'王五',87,78,77);

INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,92);

INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'李来财',82,84,67);

INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'张进宝',55,85,45);

INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',99,50,100);

问题：

1)查询表中所有学生的信息。

2)查询表中所有学生的姓名和对应的英语成绩。

3)过查询表的英语成绩去掉重复数据。

4)统计每个学生的总分，显示时 有两列信息 分别是name 和总分，总分的意思是chinese english math 之和

5)统计每个学生的总分，在总分上加10分的特长分，显示时有两列信息。分别是name和特总分，总分的意思是chinese english math 之和

6)显示所有学生的chinese english math三科成绩， 显示时 分别用语文，英语，数学作为列名

7)查询姓名为李一的学生信息

8)查询英语成绩大于90分的同学信息

9)查询总分大于200分的所有同学信息

11)查询数学分数为89 90 91 的同学信息

13)查询数学分高于80并且语文分高于82的同学信息

14)查询英语80或者总分为200的同学的信息

15)对数学成绩降序输出。

16)对总分排序，降序输出，显示时只有两列，列名是姓名和总分

17)对姓李的学生信息按照总分降序的方式输出 显示姓名和总分

1. 单表练习强化

数据准备

部门表Dept

建表语句：

CREATE TABLE DEPT  

(DEPTNO FLOAT(2) PRIMARY KEY, -- 部门号

DNAME VARCHAR(14) ,  -- 部门名称

LOC VARCHAR(13) ) ;  -- 部门地址

测试数据：

INSERT INTO DEPT VALUES  (10,'ACCOUNTING','NEW YORK');  

INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');  

INSERT INTO DEPT VALUES  (30,'SALES','CHICAGO');  

INSERT INTO DEPT VALUES  (40,'OPERATIONS','BOSTON');

员工表emp

建表语句：

CREATE TABLE EMP  

(EMPNO float(4)  PRIMARY KEY,  -- 员工编号

ENAME VARCHAR(10),  -- 员工姓名

JOB VARCHAR(9),  -- 员工职位

MGR float(4),  -- 员工上级工号

HIREDATE DATE,  -- 生日

SAL float(7,2),  -- 薪水

COMM float(7,2),  -- 年终奖

DEPTNO float(2) REFERENCES DEPT);  -- 部门号

测试数据：

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7902, 'FORD', 'ANALYST', 7566,'1981-12-02', 3000, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

问题：

1. 单表级别：

1)查询没有上级的员工全部信息（也就是说 员工上级编号为 null 的 提示 IS NULL）

2)列出30号部门所有员工的姓名、薪资

4)查询员工“TURNER”的员工编号和薪资

6)-- 查询10号部门的平均薪资、最高薪资、最低薪资

1. 单表带有子查询

0)-- 查询薪资最高的员工的信息  ----子查询

1. 列出薪金比员工“TURNER”多的所有员工姓名（ename）、员工薪资（sal）
2. 列出薪金高于公司平均薪金的所有员工姓名、薪金。
3. 列出与“SCOTT”从事相同工作的所有员工姓名、工作名称
4. 列出与“SCOTT”从事相同工作的所有员工姓名、工作名称(且不展示Scott的姓名、工作)
5. 理论补充：SQL约束

1. 数据完整性

添加约束是为了让数据库中的数据 更加符合实际情况.  

   添加数据完整性=添加表约束

   分类：实体完整性，域完整性，引用完整性

行约束，主键约束，唯一约束

      域完整性：   数据类型，默认约束，非空约束

      引用完整性： 外键约束

1. 主键约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。

主键列不能包含 NULL 值。

每个表都应该有且只能有一个主键。

1. 添加主键约束

1. 方式一：创建表时，在字段描述处，声明指定字段为主键：

CREATE TABLE Persons

(

Id_P int PRIMARY KEY,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

1. 方式二：创建表时，在constraint约束区域，声明指定字段为主键：

1. 格式：[constraint 名称] primary key (字段列表)
2. 关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。
3. 字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。声明两个以上字段为主键，我们称为联合主键。

CREATE TABLE Persons

(

FirstName varchar(255),

LastName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT pk_PersonID PRIMARY KEY (FirstName,LastName)

)

或

CREATE TABLE Persons

(

FirstName varchar(255),

LastName varchar(255),

Address varchar(255),

City varchar(255),

PRIMARY KEY (FirstName)

)

1. 方式三：创建表之后，通过修改表结构，声明指定字段为主键：

ALTER TABLE Persons ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)

CREATE TABLE Persons

(

FirstName varchar(255),

LastName varchar(255),

Address varchar(255),

City varchar(255)

)

ALTER TABLE Persons ADD PRIMARY KEY (FirstName)

1. 删除主键约束

如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：

ALTER TABLE Persons DROP PRIMARY KEY

1. x自动增长列

我们通常希望在每次插入新记录时，数据库自动生成字段的值。

我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整数类型，自动增长列必须为键(一般是主键)。

1. 下列 SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键

CREATE TABLE Persons

(

P_Id int PRIMARY KEY AUTO_INCREMENT,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

1. 向persons添加数据时，可以不为P_Id字段设置值，也可以设置成null，数据库将自动维护主键值：

INSERT INTO Persons (FirstName,LastName) VALUES ('Bill','Gates')

INSERT INTO Persons (P_Id,FirstName,LastName) VALUES (NULL,'Bill','Gates')

1. 面试：delete和truncate的区别

1. Delete删除表中的数据，但不重置auto-increment记录数。
2. Truncate删除表中的数据，auto-increment记录数将重置。Truncate其实先删除表然后再创建表。

2. 扩展：默认地，AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列 SQL 语法：

ALTER TABLE Persons AUTO_INCREMENT=100

1. 非空约束

NOT NULL 约束强制列 不接受 NULL 值。

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

1. 方式一：创建表，下面的 SQL 语句强制 "Id_P" 列和 "LastName" 列不接受 NULL 值：

CREATE TABLE Persons

(

Id_P int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

1. 方式二：修改表结构

添加非空约束 ALTER TABLE student MODIFY LastName varchar(255) NOT NULL

    

    删除非空约束 ALTER TABLE student MODIFY LastName varchar(255) 

1. 唯一约束

UNIQUE 约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

1. 添加唯一约束

与主键添加方式相同，共有3种，

1. 方式1：创建表时，在字段描述处，声明唯一：

CREATE TABLE Persons

(

Id_P int UNIQUE,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

1. 方式2：创建表时，在约束区域，声明唯一：

CREATE TABLE Persons

(

Id_P int,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT 名称UNIQUE (Id_P)

)

1. 方式3：创建表后，修改表结构，声明字段唯一：

ALTER TABLE Persons ADD [CONSTRAINT 名称] UNIQUE (Id_P)

1. 删除唯一约束

1. 如需撤销 UNIQUE 约束，请使用下面的 SQL：

ALTER TABLE Persons DROP INDEX名称

1. 如果添加唯一约束时，没有设置约束名称，默认是当前字段的字段名。

唯一约束与主键约束的区别：

主键：唯一、不能为空、一个表只能有一个主键，非业务数据

唯一：唯一、可以有空值，但只能有一个空值。一个表可以有多个唯一约束。

1. 默认约束

  在添加数据中，如果该字段不指定值，采用默认值处理

1. 方式一： 创建表，字段处声明

CREATE TABLE Persons

(

Id_P int,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255) DEFAULT '北京',

City varchar(255)

)

1. 方式二： 修改表结构

   ALTER TABLE Persons MODIFY Address VARCHAR(255) DEFAULT '北京'

删除默认约束ALTER TABLE Persons MODIFY Address VARCHAR(255)

1. 理论补充：SQL备份与恢复

1. SQL备份

选中数据库，右键”备份/导出”，指定导出路径，保存成.sql文件即可。

 

1. SQL恢复

数据库列表区域右键“从SQL转储文件导入数据库”，指定要执行的SQL文件，执行即可。