- SQL语句查询
- 排序
通过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;
-
- 聚合
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个单一的值;另外聚合函数会忽略空值。
今天我们学习如下五个聚合函数:
- count:统计指定列不为NULL的记录行数;
- sum:计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
- max:计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
- min:计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
- 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;
-
- 分组
用途:统计每种类别的信息。如统计班级中每种性别各多少人,每种年龄各多少人等。
关键字:group by
- 格式:
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 '%王%'; |
- having与where的区别:
- having是在对分组后的结果进行过滤.主要是针对列,
- 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; |
-
- 分页查询
分页查询在项目开发中常见,由于数据量很大,显示屏长度有限,因此对数据需要采取分页显示方式。
例如数据共有n条,每页显示10条,第一页显示1-10条,第二页显示11-20条...
- 格式:
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;
- 强化练习
- 单表练习
建表语句:
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)对姓李的学生信息按照总分降序的方式输出 显示姓名和总分
-
- 单表练习强化
数据准备
部门表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)查询没有上级的员工全部信息(也就是说 员工上级编号为 null 的 提示 IS NULL)
2)列出30号部门所有员工的姓名、薪资
4)查询员工“TURNER”的员工编号和薪资
6)-- 查询10号部门的平均薪资、最高薪资、最低薪资
- 单表带有子查询
0)-- 查询薪资最高的员工的信息 ----子查询
- 列出薪金比员工“TURNER”多的所有员工姓名(ename)、员工薪资(sal)
- 列出薪金高于公司平均薪金的所有员工姓名、薪金。
- 列出与“SCOTT”从事相同工作的所有员工姓名、工作名称
- 列出与“SCOTT”从事相同工作的所有员工姓名、工作名称(且不展示Scott的姓名、工作)
- 理论补充:SQL约束
- 数据完整性
添加约束是为了让数据库中的数据 更加符合实际情况.
添加数据完整性=添加表约束
分类:实体完整性,域完整性,引用完整性
实体完整性: 数据行约束,主键约束,唯一约束
域完整性: 数据类型,默认约束,非空约束
引用完整性: 外键约束
-
- 主键约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有且只能有一个主键。
-
-
- 添加主键约束
-
- 方式一:创建表时,在字段描述处,声明指定字段为主键:
CREATE TABLE Persons
(
Id_P int PRIMARY KEY,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
- 方式二:创建表时,在constraint约束区域,声明指定字段为主键:
- 格式:[constraint 名称] primary key (字段列表)
- 关键字constraint可以省略,如果需要为主键命名,constraint不能省略,主键名称一般没用。
- 字段列表需要使用小括号括住,如果有多字段需要使用逗号分隔。声明两个以上字段为主键,我们称为联合主键。
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)
)
- 方式三:创建表之后,通过修改表结构,声明指定字段为主键:
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)
-
-
- 删除主键约束
-
如需撤销 PRIMARY KEY 约束,请使用下面的 SQL:
ALTER TABLE Persons DROP PRIMARY KEY
-
- x自动增长列
我们通常希望在每次插入新记录时,数据库自动生成字段的值。
我们可以在表中使用 auto_increment(自动增长列)关键字,自动增长列类型必须是整数类型,自动增长列必须为键(一般是主键)。
- 下列 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)
)
- 向persons添加数据时,可以不为P_Id字段设置值,也可以设置成null,数据库将自动维护主键值:
INSERT INTO Persons (FirstName,LastName) VALUES ('Bill','Gates')
INSERT INTO Persons (P_Id,FirstName,LastName) VALUES (NULL,'Bill','Gates')
- 面试:delete和truncate的区别
- Delete删除表中的数据,但不重置auto-increment记录数。
- Truncate删除表中的数据,auto-increment记录数将重置。Truncate其实先删除表然后再创建表。
- 扩展:默认地,AUTO_INCREMENT 的开始值是 1,如果希望修改起始值,请使用下列 SQL 语法:
ALTER TABLE Persons AUTO_INCREMENT=100
-
- 非空约束
NOT NULL 约束强制列 不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着,如果不向字段添加值,就无法插入新记录或者更新记录。
- 方式一:创建表,下面的 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)
)
- 方式二:修改表结构
添加非空约束 ALTER TABLE student MODIFY LastName varchar(255) NOT NULL
删除非空约束 ALTER TABLE student MODIFY LastName varchar(255)
-
- 唯一约束
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
-
-
- 添加唯一约束
-
与主键添加方式相同,共有3种,
- 方式1:创建表时,在字段描述处,声明唯一:
CREATE TABLE Persons
(
Id_P int UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
- 方式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)
)
- 方式3:创建表后,修改表结构,声明字段唯一:
ALTER TABLE Persons ADD [CONSTRAINT 名称] UNIQUE (Id_P)
-
-
- 删除唯一约束
-
- 如需撤销 UNIQUE 约束,请使用下面的 SQL:
ALTER TABLE Persons DROP INDEX名称
- 如果添加唯一约束时,没有设置约束名称,默认是当前字段的字段名。
唯一约束与主键约束的区别:
主键:唯一、不能为空、一个表只能有一个主键,非业务数据
唯一:唯一、可以有空值,但只能有一个空值。一个表可以有多个唯一约束。
-
- 默认约束
在添加数据中,如果该字段不指定值,采用默认值处理
- 方式一: 创建表,字段处声明
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255) DEFAULT '北京',
City varchar(255)
)
- 方式二: 修改表结构
ALTER TABLE Persons MODIFY Address VARCHAR(255) DEFAULT '北京'
删除默认约束ALTER TABLE Persons MODIFY Address VARCHAR(255)
- 理论补充:SQL备份与恢复
- SQL备份
选中数据库,右键”备份/导出”,指定导出路径,保存成.sql文件即可。
-
- SQL恢复
数据库列表区域右键“从SQL转储文件导入数据库”,指定要执行的SQL文件,执行即可。