SQL (二)

一、数据场景查询

1.1 排序

通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示方式,不会影响数据库中数据的顺序)
SELECT 字段名 FROM 表名 WHERE 字段=ORDER BY 字段名 

[ASC|DESC];
ASC: 升序,默认值
DESC: 降序

单列排序, 什么是单列排序:只按某一个字段进行排序,单列排序。

  练习:使用年龄降序排序

组合排序,同时对多个字段进行排序,如果第 1 个字段相等,则按第 2 个字段排序,依次类推。

-- 组合排序的语法:
SELECT 字段名 FROM 表名 WHERE 字段=ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];

  练习:查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩升序排序

 

1.2 聚合函数 

之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个结果值。聚合函数会忽略空值 NULL。
 
SQL (二)
-- 语法
SELECT 聚合函数(列名) FROM 表名;

练习:查询学生总数

我们发现对于 NULL 的记录不会统计,建议如果统计个数则不要使用有可能为 null 的列,但如果需要把 NULL也统计进去呢?
IFNULL(列名,默认值) 如果列名不为空,返回这列的值。如果为 NULL,则返回默认值。

练习:查询 id 字段,如果为 null,则使用 0 代替

我们可以利用 IFNULL()函数,如果记录为 NULL,给个默认值,这样统计的数据就不会遗漏
练习:
1.查询年龄大于 20 的总数
2.查询数学成绩总分
3.查询数学成绩平均分
4.查询数学成绩最高分
5.查询数学成绩最低分
 
1.3 分组
分组查询是指使用 GROUP BY 语句对查询信息进行分组,相同数据作为一组
SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
GROUP BY 怎么分组的?
将分组字段结果中相同内容作为一组,如按性别将学生分成 2 组。
SQL (二)
GROUP BY 将分组字段结果中相同内容作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。分组的目的就是为了统计,一般分组会跟聚合函数一起使用。
注意:当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的

练习:
1.按性别进行分组,求男生和女生数学的平均分
2.查询男女各多少人
3.查询年龄大于 25 岁的人,按性别分组,统计每组的人数
4.查询年龄大于 25 岁的人,按性别分组,统计每组的人数,并只显示性别人数大于 2 的数据
 
having 与 where 的区别
SQL (二)

 

 练习:Orders 表数据如下所示,执行如下 SQL 语句,运行结果是?

SQL (二)

select product,sum(price) from orders group by product where sum(price) > 30; 
 
1.4 limit的作用
准备数据
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
(9,唐僧,25,,长安,87,78),
(10,孙悟空,18,,花果山,100,66),
(11,猪八戒,22,,高老庄,58,78),
(12,沙僧,50,,流沙河,77,88),
(13,白骨精,22,,白虎岭,66,66),
(14,蜘蛛精,23,,盘丝洞,88,88);
 
limit 的作用:LIMIT 是限制的意思,所以 LIMIT 的作用就是限制查询记录的条数。
 
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子 句][LIMIT 子句];

SQL (二)

 练习:查询学生表中数据,从第 3 条开始显示,显示 6 条。

 

LIMIT 的使用场景:
分页:比如我们登录京东,淘宝,返回的商品信息可能有几万条,不是一次全部显示出来。是一页显示固定的条数。 假设我们每页显示 5 条记录的方式来分页。
SQL (二)

 

 

二、数据库表的约束

对表中的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。
SQL (二)

 

 

2.1 主键

用来唯一标识数据库中的每一条记录,通常不用业务字段作为主键,单独给每张表设计一个 id 的字段,把 id 作为主键。主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复唯一非空就行。
如:身份证,学号不建议做成主键
-- 在创建表的时候给字段添加主键
字段名 字段类型 PRIMARY KEY

-- 在已有表中添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

-- 删除主键
alter table 表名 drop primary key;

练习:

1.创建一个学生st1表 字段id,name,age,把id设置为主键

2.添加相同主键看是否插入

3.删除主键

注:主键数在一个表中,只能有一个。不能出现多个主键。

 

2.2 主键自增

主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
默认地 AUTO_INCREMENT 的开始值是 1,如果希望修改起始值,请使用下列 SQL 语法
-- 创建表时指定起始值
CREATE TABLE 表名(
列名 int primary key AUTO_INCREMENT
) AUTO_INCREMENT=起始值;
-- 指定起始值为 1000

-- 创建好以后修改起始值
ALTER TABLE 表名 AUTO_INCREMENT=起始值;

注:DELETE:删除所有的记录之后,自增长没有影响。TRUNCATE:删除以后,自增长又重新开始。

 

2.3 唯一约束

什么是唯一约束: 表中某一列不能出现重复的值
-- 唯一约束的基本格式 建表
字段名 字段类型 UNIQUE

-- 建完表添加
alter table 表名 add unique(字段名)

注:null不存在数据,不属于重复问题

 

2.4 非空约束

什么是非空约束:某一列不能为 null
-- 非空约束的基本语法格式
字段名 字段类型 NOT NULL

2.5 默认值

在表中插入一条新记录时,如果没有为某个字段赋值,系统就会自动为这个字段插入默认值。

字段名 字段类型 DEFAULT 默认值

 

 2.6 外键约束

单表的缺点:创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id 主键并自动增长,添加 5 条数据
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);

-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (张三, 20, 研发部, 广州);
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (李四, 21, 研发部, 广州);
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (王五, 20, 研发部, 广州);
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (老王, 20, 销售部, 深圳);
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (大王, 22, 销售部, 深圳);
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (小王, 18, 销售部, 深圳);
可以看到 以上数据表的缺点:
1) 数据冗余
2)后期还会出现增删改的问题
 
解决方案: 分表
SQL (二)
-- 解决方案:分成 2 张表
-- 创建部门表(id,dep_name,dep_location)
-- 一方,主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方,从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int -- 外键对应主表的主键
)
-- 添加 2 个部门
insert into department values(null, 研发部,广州),(null, 销售部, 深圳);
select * from department;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES (张三, 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (李四, 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (王五, 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (老王, 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES (大王, 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES (小王, 18, 2);
select * from employee;

 

当我们在 employee 的 dep_id 里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门,实际应用中不能出现这种情况。employee 的 dep_id 中的数据只能是 department 表中存在的 id 
 
SQL (二)
 
需要约束 dep_id 只能是 department 表中已经存在 id 
解决方式: 使用外键约束
什么是外键:在从表中与主表主键对应的那一列,如:员工表中的 dep_id
主表: 一方,用来约束别人的表
从表: 多方,被别人约束的表 
 
SQL (二)

 

 

-- 创建约束的语法
-- 新建表时增加外键:
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

-- 已有表增加外键:
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
-- 2) 创建从表 employee 并添加外键约束 emp_depid_fk
-- 多方,从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references department(id)
)
-- 3) 正常添加数据
INSERT INTO employee (NAME, age, dep_id) VALUES (张三, 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (李四, 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (王五, 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (老王, 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES (大王, 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES (小王, 18, 2);
select * from employee;
-- 4) 部门错误的数据添加失败
-- 插入不存在的部门
-- Cannot add or update a child row: a foreign key constraint fails
INSERT INTO employee (NAME, age, dep_id) VALUES (老张, 18, 6);

 

删除外键

ALTER TABLE 从表 drop foreign key 外键名称;

然后在alter添加.....

 

外键的级联

在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作
SQL (二)
 
要把部门表中的 id 值 2,改成 5,能不能直接更新呢? 
要删除部门 id 等于 1 的部门, 能不能直接删除呢? 
 
-- 删除 employee 表,重新创建 employee 表,添加级联更新和级联删除
drop table employee;

create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references
 department(id) on update cascade on delete cascade
)

-- 再次添加数据到员工表和部门表
INSERT INTO employee (NAME, age, dep_id) VALUES (张三, 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (李四, 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (王五, 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (老王, 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES (大王, 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES (小王, 18, 2);

-- 删除部门表?能不能直接删除?
drop table department;
-- 把部门表中 id 等于 1 的部门改成 id 等于 10
update department set id=10 where id=1;
select * from employee;
select * from department;
-- 删除部门号是 2 的部门
delete from department where id=2;

 

 

三 、多表查询

数据准备

-- 创建部门表
create table dept(
 id int primary key auto_increment,
 name varchar(20)
)
insert into dept (name) values (开发部),(市场部),(财务部); 

-- 创建员工表
create table emp (
 id int primary key auto_increment,
 name varchar(10),
 gender char(1), -- 性别
 salary double, -- 工资
 join_date date, -- 入职日期
 dept_id int,
 foreign key (dept_id) references dept(id) -- 外键,关联部门表(部门表的主键) ) 
insert into emp(name,gender,salary,join_date,dept_id) values(孙悟空,,7200,2013-02-24,1);
insert into emp(name,gender,salary,join_date,dept_id) values(猪八戒,,3600,2010-12-02,2);
insert into emp(name,gender,salary,join_date,dept_id) values(唐僧,,9000,2008-08-08,2);
insert into emp(name,gender,salary,join_date,dept_id) values(白骨精,,5000,2015-10-07,3);
insert into emp(name,gender,salary,join_date,dept_id) values(蜘蛛精,,4500,2011-03-14,1);

 

多表查询的作用:
比如:我们想查询孙悟空的名字和他所在的部门的名字,则需要使用多表查询。
如果一条 SQL 语句查询多张表,因为查询结果在多张不同的表中。每张表取 1 列或多列。
SQL (二)

 

 

笛卡尔积现象 
-- 需求:查询所有的员工和所有的部门
select * from emp,dept;

SQL (二)

我们发现不是所有的数据组合都是有用的,只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。
-- 设置过滤条件 Column ‘id‘ in where clause is ambiguous
select * from emp,dept where id=5;
select * from emp,dept where emp.‘dept_id‘ = dept.‘id‘;
-- 查询员工和部门的名字 select emp.`name`, dept.`name` from emp,dept where emp.‘dept_id‘ = dept.‘id‘;

 

3.1 内连接

用左边表的记录去匹配右边表的记录,如果符合条件的则显示。如:从表.外键=主表.主键
连接结果仅包含符合连接条件的行,参与连接的两个表都应该符合连接条件
-- 隐式内连接:看不到 JOIN 关键字,条件使用 WHERE 指定
SELECT 字段名 FROM 左表, 右表 WHERE 条件

-- 显示内连接:使用 INNER JOIN ... ON 语句, 可以省略 INNER
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件 
需要联合 2 张表同时才能查询出需要的数据,使用内连接
SQL (二)

 

总结内连接查询步骤:
1)确定查询哪些表
2)确定表连接的条件
3)确定查询的条件
4)确定查询的字段

 

3.2 外连接

-- 左外连接:使用 LEFT OUTER JOIN ... ON,OUTER 可以省略
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
用左边表的记录去匹配右边表的记录,如果符合条件的则显示;否则,显示 NULL
可以理解为:在内连接的基础上保证左表的数据全部显示
-- 右外连接:使用 RIGHT OUTER JOIN ... ON,OUTER 可以省略
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
用右边表的记录去匹配左边表的记录,如果符合条件的则显示;否则,显示 NULL
可以理解为:在内连接的基础上保证右表的数据全部显示
 
注:工作中优先使用左连接
 
 
3.3 子查询
 
1) 一个查询的结果做为另一个查询的条件
2) 有查询的嵌套,内部的查询称为子查询
3) 子查询要使用括号
 
需求:查询开发部中有哪些员工?
 
子查询结果的三种情况: 
1) 子查询的结果是单行单列 
SQL (二)
2) 子查询的结果是多行单列 

 SQL (二)

3) 子查询的结果是多行多列 
SQL (二)

 

 

3.3.1 子查询的结果是一个值的时候
子查询结果只要是单行单列,肯定在 WHERE 后面作为条件,父查询使用:比较运算符,如:> 、<、<>、= 等
SELECT 查询字段 FROMWHERE 字段=(子查询);

练习:

1.查询工资最高的员工是谁?

2.查询工资小于平均工资的员工有哪些?

 

3.3.2 子查询结果是多行单列的时候

子查询结果是单例多行,结果集类似于一个数组,父查询使用 IN 运算符
SELECT 查询字段 FROMWHERE 字段 IN (子查询);

练习:

1.查询工资大于 5000 的员工,来自于哪些部门的名字?

2.查询开发部与财务部所有的员工信息

 

3.3.3 子查询的结果是多行多列

子查询结果只要是多列,肯定在 FROM 后面作为表 
SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件;

注:子查询作为表需要取别名,否则这张表没有名称则无法访问表中的字段

练习:

1.查询出 2011 年以后入职的员工信息,包括部门名称 

SQL (二)

上一篇:建议收藏备查!MySQL 常见错误代码说明


下一篇:sql-02.结构化查询语言