数据库多表和查询

多表和查询

1 DQL语句

高级查询

准备数据

-- 创建表
CREATE TABLE student3 (
 id int,
 name varchar(20),
 age int,
 sex varchar(5),
 address varchar(100),
 math int,
 english int
);
-- 插入记录
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);

1.1 条件查询

select ... from 表名 where 条件

比较运算符 (关系运算符)

> < >= <= = != <>

-- 例如
字段 = 字段值
# 关系运算符 mysql 特有的
-- 查询math分数大于80分的学生
SELECT * FROM student3 WHERE math > 80;
-- 查询english分数小于或等于80分的学生
SELECT * FROM student3 WHERE english <=80;
-- 查询age等于20岁的学生
SELECT * FROM student3 WHERE age =20;
-- 查询age不等于20岁的学生
SELECT * FROM student3 WHERE age != 20;
SELECT * FROM student3 WHERE age <> 20;

逻辑运算符

and && 条件同时满足
or || 条件满足一个
not ! 不满足(条件取反)
# 逻辑运算符
-- 查询age大于35且性别为男的学生(两个条件同时满足)
SELECT * FROM student3 WHERE age >35 AND sex ='男';
-- 查询age大于35或性别为男的学生(两个条件其中一个满足)
SELECT * FROM student3 WHERE age >35 OR sex ='男';
-- 查询id是1或3或5的学生
SELECT * FROM student3 WHERE id =1 OR id =3 OR id = 5;
-- in关键字
-- 再次查询id是1或3或5的学生
SELECT * FROM student3 WHERE id IN(1,3,5);
-- 查询id不是1或3或5的学生
SELECT * FROM student3 WHERE id NOT IN(1,3,5);

in关键字

字段 in(值1,值2,值3...) -- 使用or拼接值, 满足条件显示

范围查询

-- 格式
字段 between 较小的值 and 较大的值  包头包尾
-- 查询english成绩大于等于77,且小于等于87的学生
SELECT * FROM student3 WHERE english >=77 AND english <=87;
-- between
SELECT * FROM student3 WHERE english BETWEEN 77 AND 87;

模糊查询

-- 格式
字段 like '通配符字符串';
% 任意字符 0个或多个
_ 一个字符
# like模糊匹配
-- 查询姓马的学生
-- = 不可以用了
SELECT * FROM student3 WHERE NAME ='马';
-- like
SELECT * FROM student3 WHERE NAME LIKE '马%';
-- 查询姓名中包含'德'字的学生
SELECT * FROM student3 WHERE NAME LIKE '%德%';
-- 查询姓马,且姓名有三个字的学生
SELECT * FROM `student3` WHERE `name` LIKE '马__';

1.2 排序

多字段排序,在上一个基础上在进行排序

-- 格式
select ... from 表名 order by 排序字段 [asc | desc], 排序字段 [asc | desc]
asc 升序 默认值
desc 降序

# 排序
-- 查询所有数据,使用年龄升序排序
SELECT * FROM student3 ORDER BY age ASC;
SELECT * FROM student3 ORDER BY age ;
-- 查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩降序排序
SELECT * FROM student3 ORDER BY age DESC;
SELECT * FROM student3 ORDER BY age DESC ,math DESC;

1.3 聚合函数

对一列数据进行计算,返回的是一个结果值,忽略空值(null)

count(字段名) -- 统计
max(字段名) -- 最大值
min(字段名)  -- 最小值
sum(字段名) --求和
avg(字段名)  --平均值
# 聚合函数
-- 查询学生总数(null值处理)
SELECT COUNT(id) FROM student3;
SELECT COUNT(english) FROM student3;
SELECT english FROM student3;
SELECT IFNULL(english,0) FROM student3;
SELECT COUNT(IFNULL(english,0)) FROM student3;
-- 查询年龄大于40的总数
SELECT * FROM student3 WHERE age >40;
SELECT COUNT(*) FROM student3 WHERE age >40;
-- count(1 位置) --> id
SELECT COUNT(1) FROM student3 WHERE age >40;
-- 查询数学成绩总分
SELECT SUM(math) FROM student3;
-- 查询数学成绩平均分
SELECT AVG(math) FROM student3;
-- 查询数学成绩最高分
SELECT MAX(math) FROM student3;
-- 查询数学成绩最低分
SELECT MIN(math) FROM student3;

ifnull()函数

-- 格式
ifnull(字段名,指定值) -- 如果字段有值,直接返;若果字段没有之,返回指定的值

1.4 分组

对查询结果进行分组,相同内容分为一组 ,通常与聚合函数一起使用

-- 格式
select 分组字段,聚合函数(分组字段) from 表名 group by 分组字段 having 条件;
# 分组
-- 按性别分组
SELECT * FROM student3 GROUP BY sex;
-- 查询男女各多少人
SELECT sex,COUNT(sex) FROM student3 GROUP BY sex;
-- 男生最高分和女生最高分别是多少
SELECT sex,MAX(math) FROM student3 GROUP BY sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
SELECT * FROM student3 WHERE age >25;
SELECT sex,COUNT(*) FROM student3 WHERE age >25 GROUP BY sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
-- where 条件
-- Invalid use of group function
SELECT sex,COUNT(*) FROM student3 WHERE age >25 AND COUNT(*)>2 GROUP BY sex;
SELECT sex,COUNT(*) FROM student3 WHERE age >25 GROUP BY sex HAVING COUNT(*) >2;

总结:where条件是在分组前过滤,不能使用聚合函数

having条件是在分组后过滤,可以使用聚合函数

1.6 limit分页

-- 格式
select ... from 表名 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);
-- 查询学生表中数据,从第三条开始显示,显示6条
SELECT * FROM student3 LIMIT 2,6;
-- 查询学生表中数据,显示前6条
SELECT * FROM student3 LIMIT 0,6;
SELECT * FROM student3 LIMIT 6;
-- 模拟百度分页,一页显示5条

-- 第一页
SELECT * FROM student3 LIMIT 0,5;
-- 第二页
SELECT * FROM student3 LIMIT 5,5;
-- 第三页
SELECT * FROM student3 LIMIT 10,5;
-- 第N页 后期 java代码实现分页功能
SELECT * FROM student3 LIMIT (n-1)*显示个数,显示个数;

高级查询格式

select ... from 表名 [where 子句] [group by 子句] [having 子句] [order by 子句] [limit 子句]

2 数据库备份和还原

数据迁移 : 备份、还原

dos命令窗口  

-- 备份 在dos命令窗口下
mysqldump -u用户名 -p密码 数据库名 > 导出文件路径
-- 还原 登录mysql 选择库
source 导入文件路径

图形化工具

3 数据库约束

对数据进一步限制,保证数据的正确性、有效性和完整性

3.1 约束分类 (ctrl+t 提示表格)

约束 描述
primary key 主键
unique 唯一
not null 非空
default 默认值
foreign key 外键

3.2 主键

使用标识唯一的一条记录,通常使用 id作为主键

添加主键

-- 创建表
create table 表名(
字段名 字段类型 primary key,
   ...
);
-- 已有表
alter table 表名 add primary key(字段名);

总结主键是要求唯一和非空一张表只能有一个主键

-- 主键
-- 创建表
CREATE TABLE stu1(
id INT PRIMARY KEY,
NAME VARCHAR(32)
);
-- 插入记录
INSERT INTO stu1 VALUES(1,'tom');
-- 错误 Duplicate entry '1' for key 'PRIMARY'
INSERT INTO stu1 VALUES(1,'tom');
-- 错误 Column 'id' cannot be null
INSERT INTO stu1 VALUES(NULL,'jack');

-- 已有表 要求表中主键字段是正确的 唯一和非空
ALTER TABLE student3 ADD PRIMARY KEY(id);
-- name 字段也设为主键
-- 错误 Multiple primary key defined
ALTER TABLE student3 ADD PRIMARY KEY (NAME);

-- 联合主键  把多个字段当作一个整体作为主键来判断
CREATE TABLE stu2 (
id INT ,
`name` VARCHAR(32),
PRIMARY KEY (id,`name`)
);
-- 插入记录
INSERT INTO stu2 VALUES(1,'jack');
INSERT INTO stu2 VALUES(1,'tom');
-- 错误 Duplicate entry '1-jack' for key 'PRIMARY'
INSERT INTO stu2 VALUES(1,'jack');

主键自增

-- 格式
create table 表名(
字段名 字段类型 primary key auto_increment,
   ...
);

-- 主键自增 默认从1开始
-- 设置主键自增起始值
alter table 表名 auto_increment = 起始值;
-- 主键自增  tab 提示
CREATE TABLE stu3 (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32)
);
-- 插入记录
INSERT INTO stu3 VALUES(1,'tom');
INSERT INTO stu3 VALUES(NULL,'tom');
INSERT INTO stu3 VALUES(NULL,'jerry');

-- 自增起始值
ALTER TABLE stu3 AUTO_INCREMENT=10000;
INSERT INTO stu3 VALUES(NULL,'jerry');

自增要求 主键类型必须为 数值类型

truncate与delete区别

-- truncate 与 delete 区别
DELETE FROM stu3;
INSERT INTO stu3 VALUES(NULL,'jerry');

TRUNCATE TABLE stu3;
INSERT INTO stu3 VALUES(NULL,'jerry');

truncate 属于 DDL语句,暴力delete属于DML语句,温柔一点

删除主键

-- 格式
alter table 表名 drop primary key;
-- 删除主键 stu1
ALTER TABLE stu1 DROP PRIMARY KEY;
-- 删除主键 stu3
-- 错误 Incorrect table definition; there can be only one auto column and it must be defined as a key
ALTER TABLE stu3 DROP PRIMARY KEY;
-- 先删除自增
ALTER TABLE stu3 MODIFY id INT ;

3.3 唯一

不会拦截空值重复

-- 创建表
create table 表名(
字段名 字段类型 unique,
   ....
);
-- 已有表
alter table 表名 add unique(字段名);
-- 唯一
CREATE TABLE stu4 (
id INT,
NAME VARCHAR(32) UNIQUE
);
-- 插入记录
INSERT INTO stu4 VALUES(1,'tom');
-- 错误Duplicate entry 'tom' for key 'name'
INSERT INTO stu4 VALUES(2,'tom');
INSERT INTO stu4 VALUES(3,NULL);
INSERT INTO stu4 VALUES(4,NULL);

3.4 非空

-- 创建表
create table 表名(
字段名 字段类型 not null,
   ....
);
-- 已有表
alter table 表名 modify 字段名 字段类型 not null;
-- 非空
CREATE TABLE stu5(
id INT,
NAME VARCHAR(32) NOT NULL
);
-- 插入记录
INSERT INTO stu5 VALUES(1,'jack');
-- 错误 Column 'name' cannot be null
INSERT INTO stu5 VALUES(1,NULL);
INSERT INTO stu5 VALUES(1,'');

如果字段设置为唯一和非空 -跟主键的区别是 主键支持自增

3.5 默认值

所有字段设置完毕后默认值为null

-- 创建表
create table 表名(
字段名 字段类型 default null,
   字段名 字段类型 default 默认值,
   ...
);
-- 已有表
alter table 表名 modify 字段名 字段类型 default 默认值;
-- 默认值
CREATE TABLE stu6(
NAME VARCHAR(32),
sex VARCHAR(5) DEFAULT '男'
);
-- 插入记录
INSERT INTO stu6 VALUES('佳佳','女');
INSERT INTO stu6(NAME) VALUES('田伟');
INSERT INTO stu6 VALUES('佳佳',NULL);

4 表关系

在现实生活中,实体与实体之间是存在关系的,我们在设计数据库表的时候也应该体现出这种关系;

一对多

例如:班级和学生、部门和员工

多对多

例如:学生和课程、用户和角色

一对一

例如:公司和注册地、老公和老婆

4.1 一对多

班级和学生

-- 一对多
CREATE DATABASE ee_day03_1;
USE ee_day03_1;
-- 班级表
CREATE TABLE class(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32)
);
-- 记录
INSERT INTO class VALUES(NULL,'黑马69期');
INSERT INTO class VALUES(NULL,'黑马70期');
-- 学生表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32),
class_id INT
);
-- 记录
INSERT INTO student VALUES(NULL,'曼曼',1);
INSERT INTO student VALUES(NULL,'佳佳',1);
INSERT INTO student VALUES(NULL,'欢欢',2);

4.2 多对多

学生和课程

-- 多对多
-- 课程表
CREATE TABLE course (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32)
);
-- 记录
INSERT INTO course VALUES(NULL,'ui');
INSERT INTO course VALUES(NULL,'java');
INSERT INTO course VALUES(NULL,'python');
-- 中间sc
CREATE TABLE sc(
sid INT,
cid INT,
PRIMARY KEY (sid,cid),
CONSTRAINT sid_student_fk FOREIGN KEY (sid) REFERENCES student(id),
CONSTRAINT cid_course_fk FOREIGN KEY (cid) REFERENCES course(id)
);
-- 记录
INSERT INTO sc VALUES(1,1);
INSERT INTO sc VALUES(1,2);
INSERT INTO sc VALUES(1,3);
INSERT INTO sc VALUES(2,1);
INSERT INTO sc VALUES(2,3);
INSERT INTO sc VALUES(3,2);

-- 错误记录
INSERT INTO sc VALUES(1,5);

4.3 一对一

公司和注册地

我们在设计表的时候,可以合并到一起

建表原则:

外键是唯一

主键是外键

-- 一对一
-- 公司表
CREATE TABLE company (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32)
);
-- 记录
INSERT INTO company VALUES(1,'拼多多');
INSERT INTO company VALUES(2,'红心浏览器');
-- 地址表
CREATE TABLE address(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32),
FOREIGN KEY (id) REFERENCES company(id)
);
-- 记录
INSERT INTO address VALUES(1,'上海滩');
INSERT INTO address VALUES(2,'北京');

4.4 外键约束

多表有关系的数据进一步限制,保证数据的有效性、正确性和完整性

添加外键约束

-- 创建表
create table 表名(
字段名 字段类型 [约束],
   [constraint] [约束名] foreign key(字段名) references 主表(主键字段)
);
-- 已有表
alter table 表名 add  [constraint] [约束名] foreign key(字段名) references 主表(主键字段);

外键约束特点

  1. 主表不能删除从表已引用的数据

  2. 从表不能添加主表未拥有的数据

  3. 先添加主表数据再添加从表数据

  4. 先删除从表数据再删除主表数据

删除外键约束

-- 格式
alter table 表名 drop foreign key 约束名;
-- 给学生表增加外键约束
-- 错误 Cannot add or update a child row
ALTER TABLE student ADD CONSTRAINT  class_id_class_fk FOREIGN KEY (class_id) REFERENCES class(id);

-- 插入一条学生信息  班级71期 id3
-- 错误 Cannot add or update a child row
INSERT INTO student VALUES(NULL,'师哲',3);

-- 删除外键
ALTER TABLE student DROP FOREIGN KEY class_id_class_fk;

发表评论