数据库基础（单表查询和多表查询）

目录

单表查询

重要语句关键字

测试脚本

多表查询

创建测试数据

交叉连接

内连接

子查询

添加外键约束

测试外键约束

添加联级删除和连级修改

测试联级删除和连级修改

---

单表查询

重要语句关键字

关键字/语句	含义	范例	备注
select 字段名 from 表名	对指定字段名进行查询	select ename, job, sal, entryTime from emp;	很常用
select * from 表名	匹配所有字段进行查询	select * from emp;	不常用
select distin 字段名，... from 表名	查询去重数据	select distinct ename, job from emp; select distinct job from emp;	几个字段值完全相同才算重复
比较运算符 —— “=”	比较两数是否相等	select ename, job, mgr, deptno from emp where ename = '张三'; select ename, job, mgr, deptno from emp where date (entryTime) = '2022-11-5';	Null 不能使用
比较运算符 —— “< >” / “!=”	比较两数不相等	select ename, job, mgr, deptnob from emp where mgr > 5000 and job != '董事长';	Null 不能使用
比较运算符 —— “>” / “<” / “>=” / “<=”	判断左右两侧是否大于/小于/大于等于/小于等于	select ename, job, mgr, deptno from emp where mgr >= 5000;
between...and...	数据是否在指定范围内	select ename, job, sal from emp where sal between 1500 and 4500;等同于select ename, job, sal from emp where sal >= 1500 and sal <= 4500;
in	数据是否存在指定集合内	select ename, job, mgr, deptno from emp where deptno in (10, 20); select ename, job, mgr, deptno,entryTimefrom emp where entryTime in ('2018-3-8', '2021-10-7');
is null / is not null	数据是否为/不为 null	from emp where comm is not null; select ename, job, sal,comm from emp where comm is null;	comm is noll 不等于 comm = 0
like %通配符	模糊查询	select ename, job, sal from emp where ename like '%一';	%任意长度字符串
like _通配符	模糊查询	select ename, job, sal from emp where ename like '_ _一';	—单个字符
not / ！	非，返回相反结果	update emp set sal=sal + 200 where (sal >= 4000 or comm < 200) and job != '董事长';
and / &&	与，需全部满足	select empno, ename, job, mgr, sal, comm, deptno from emp where deptno = 20 and sal > 1000;
or / ||	或，满足其一即可	select empno, ename, job, mgr, sal, comm, deptno from emp where deptno = 20 or sal > 1000;
count（e）	返回查询记录总数	select count(*) from emp	参数可为字段名或 *
sum（e）	返回字段值总和	select sum(sal) from emp; select sum(sal) from emp where mgr > 1000;	只能为数值型数据，数据累加时忽略字段中的NULL值
avg（e）	返回字段值平均数	select avg(mgr) from emp;	只能为数值型数据
max（e）	返回字段值最大值	select max(sal) from emp;	只能为数值型数据
min（e）	返回字段值最小值	select min(sal) from emp where job in ('分析员');	只能为数值型数据
group by	分组查询	单独使用select deptno from emp group by deptno; 与聚合函数一起使用select deptno, sum(sal), avg(sal) from emp group by deptno; HAVING关键字一起使用select deptno, avg(sal)from emp group by deptno having avg(sal) < 3000;	HAVING关键字：对结果进行条件过滤
order by	排序查询	select empno, ename, job, mgr, sal, comm, deptno from emp order by sal desc;	asc：升序（默认）desc：降序
limit	限量查询	select empno, ename, job, mgr, sal, comm, deptno from emp order by sal desc limit 5; select empno, ename, job, mgr, sal, comm, deptno from emp limit 1,4;	下标都是从0开始 第二条记录下标为1

注：以上语句关键字都可在删除、查看、修改时使用

测试脚本

create database if not exists emp;
use emp;

CREATE TABLE emp
(
    empno     INT PRIMARY KEY comment '员工编号',
    ename     VARCHAR(20) UNIQUE NOT NULL comment '员工姓名',
    job       VARCHAR(20)        NOT NULL comment '员工职位',
    entryTime date               not null comment '入职时间',
    mgr       INT comment '直属上级编号',
    sal       DECIMAL(7, 2) comment '基本工资',
    comm      DECIMAL(7, 2) comment '奖金',
    deptno    INT comment '所属部门编号'
);

INSERT INTO emp
VALUES (9839, '刘一', '董事长', date('2018-2-5'), NULL, 6000, NULL, 10),
       (9982, '陈二', '经理', date('2018-3-8'), 9839, 3450, NULL, 10),
       (9369, '张三', '保洁', date('2018-8-5'), 9902, 900, NULL, 20),
       (9566, '李四', '经理', date('2019-3-7'), 9839, 3995, NULL, 20),
       (9988, '王五', '分析员', date('2021-9-10'), 9566, 4000, NULL, 20),
       (9902, '赵六', '分析员', date('2015-5-3'), 9566, 4000, NULL, 20),
       (9499, '孙七', '销售', date('2022-11-5'), 9698, 2600, 300, 30),
       (9521, '周八', '销售', date('2019-5-15'), 9698, 2250, 500, 30),
       (9654, '吴九', '销售', date('2019-11-21'), 9698, 2250, 1400, 30),
       (9844, '郑十', '销售', date('2019-9-6'), 9698, 2500, 0, 30),
       (9900, '萧十一', '保洁', date('2021-10-7'), 9698, 1050, NULL, 30);
      
#查询字段
select ename, job, sal, entryTime
from emp;

#去重查询
select distinct ename, job
from emp;

select distinct job
from emp;

select distinct ename
from emp;

#条件查询  "="
select ename, job, mgr, deptno
from emp
where ename = '张三';
select ename, job, mgr, deptno
from emp
where date(entryTime) = '2022-11-5';

#  "<>、!="
select ename, job, mgr, deptno
from emp
where mgr >= 5000;
select ename, job, mgr, deptno
from emp
where mgr > 5000
  and job != '董事长';
 
#  "in"
select ename, job, mgr, deptno
from emp
where deptno in (10, 20);

select ename, job, mgr, deptno
from emp
where deptno = 10
   or deptno = 20;

select ename, job, mgr, deptno,entryTime
from emp
where entryTime in ('2018-3-8', '2021-10-7');

update emp
set comm=comm * 0.3
where deptno in (10, 30);

update emp
set comm=comm * 0.5
where entryTime in (date('2018-1-1'), date('2023-1-1'));

# between... and...
select ename, job, sal
from emp
where sal between 1500 and 4500;

select ename, job, sal
from emp
where sal >= 1500
  and sal <= 4500;

select ename, job, sal, entryTime
from emp
where entryTime between date('2018-11-3') and date('2023-5-15');

#数据是否不为空
select ename, job, sal,comm
from emp
where comm is not null;

#数据是否为空
select ename, job, sal,comm
from emp
where comm is null;

select *
from emp;

# like
# %通配符
select ename, job, sal
from emp
where ename like '%一';

#  _通配符
select ename, job, sal
from emp
where ename like '__一';

# is null
select ename, job, sal
from emp
where comm is null;

update emp
set comm=comm + 1000
where comm is not null;

delete
from emp
where sal < 1000;

delete
from emp
where empno like '%4%';

update emp
set deptno=20
where deptno = 10;

select ename, job, sal
from emp
where comm is null;

update emp
set sal=sal + 200
where (sal >= 4000 or comm < 200)
  and job != '董事长';

#聚合函数
select count(*)
from emp;

select count(*)
from emp

where sal;
# 如果字段中存放的是数值型数据，需要统计该字段中所有值的总数，
# 可以使用SUM()函数。SUM()函数会对指定字段中的值进行累加，并且在数据累加时忽略字段中的NULL值。
select sum(sal)
from emp;

select sum(sal)
from emp
where mgr > 1000;

# 如果字段中存放的是数值型数据，需要统计该字段中所有值的平均值，
# 可以使用AVG()函数。AVG()函数会计算指定字段值的平均值，并且计算时会忽略字段中的NULL值。
select avg(sal)
from emp
where job in ('销售', '保洁');

select avg(mgr)
from emp;

select max(sal)
from emp;

select min(sal)
from emp
where job in ('分析员');

#逻辑运算
select empno, ename, job, mgr, sal, comm, deptno
from emp
where deptno = 20
  and sal > 1000;
 
select empno, ename, job, mgr, sal, comm, deptno
from emp
where deptno = 20
  or sal > 1000;

#分组查询
# 单独使用GROUP BY进行分组，将根据指定的字段合并数据行
select deptno
from emp
group by deptno;

select deptno, sum(sal), avg(sal)
from emp
group by deptno;

# 通常情况下GROUP BY和HAVING关键字一起使用，用于对分组后的结果进行条件过滤
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) < 3000;

#排序查询
select empno, ename, job, mgr, sal, comm, deptno
from emp
order by sal desc;

#限量查询
# limit 1,4;  limit offset，step   offset   ，step 查询记录数
select empno, ename, job, mgr, sal, comm, deptno
from emp
order by sal desc
limit 5;

#下标都是从0开始 下标记录号 从第几条开始
select empno, ename, job, mgr, sal, comm, deptno
from emp
limit 1,4;

多表查询

创建测试数据

create database if not exists bms;
use bms;
#用户表
create table if not exists user
(
    # 用户编号  主键且自增
    id   int primary key auto_increment comment '用户编号',
    # 用户名 非空、唯一
    name varchar(12) not null unique comment '用户名'
);
#图书表
create table if not exists book
(
    # 图书编号  主键且自增
    id          int primary key auto_increment comment '图书编号',
    #书名  非空、唯一
    name        varchar(20)   not null unique comment '书名',
    #价格  非空、唯一
    price       decimal(6, 2) not null comment '价格',
    #上架日期 非空
    upload_time date          not null comment '上架日期',
    #图书状态  默认“归还”
    state       varchar(10)   not null default '归还' comment '图书状态'
);
#借阅表
create table record
(
    id          int primary key auto_increment,
    book_id     int  not null,
    borrower_id int  not null,
    #借阅时间
    borrow_time date not null comment '借阅时间',
    #归还时间
    remand_time date comment '归还时间'
);

#添加用户数据
insert into user
values (0, '张三'),
       (0, '李四'),
       (0, '王五'),
       (0, '赵四'),
       (0, '小红'),
       (0, '小明');

insert into book
values (0, '哈利波特', 32.00, date('2022-5-5'), '归还'),
       (0, '记住乡愁', 21.11, date('2022-5-6'), '归还'),
       (0, 'Java程序设计', 59.80, date('2022-5-7'), '未归还'),
       (0, '明朝那些事', 17.50, date('2022-5-8'),'未归还'),
       (0, '西游记', 25.73, date('2022-5-9'),'归还');

insert into book (name, price, upload_time, state)
values ('红楼梦', 21.58, date('2022-5-10'), '未借出'),
       ('三国演义', 21.58, date('2022-5-11'), '未借出'),
       ('水浒传', 21.58, date('2022-5-12'), '未借出');

insert into record
values (0, 001, 01, date('2023-10-5'), date('2023-11-5')),
       (0, 001, 03, date('2023-2-8'), date('2023-3-1')),
       (0, 002, 02, date('2023-3-6'), date('2023-5-6'));
insert into record(book_id, borrower_id, borrow_time)
values (03, 03, date('2023-3-5')),
       (04, 01, date('2023-10-1'));
insert into record
values (0, 005, 04, date('2023-5-21'), date('2023-6-3'));

交叉连接

#比西游记更贵的书
select b.name, b.price
from book b
where price > (select price from book where name = '西游记');

#查询图书状态和《三国演义》一样的图书
select name, state
from book
where state = (select state from book where name = '三国演义');

#查询价格比所有图书平均价还低的图书
select name, price
from book
where price < (select avg(price) from book);

#查询未归还的图书比归还的图书还低的图书
select name, price, state
from book
where price < (select avg(price) from book where state = '未归还')
  and state = '归还';

#查询图书借阅记录
select r.id, b.name, u.name, r.borrow_time, r.remand_time
from book b
         inner join record r on r.book_id = b.id
         inner join user u on r.borrower_id = u.id;

内连接

#查看所有借书人员记录
#左连接
select u.name, b.name, r.borrow_time, r.remand_time
from user u
         left join record r on r.borrower_id = u.id
         left join book b on b.id = r.book_id;

#右连接
select u.name, b.name, r.borrow_time, r.remand_time
from book b
         right join record r on b.id = r.book_id
         right join user u on r.borrower_id = u.id;

#查看所有图书借阅记录
#左连接
select b.name, b.price, b.upload_time, u.name, b.state, r.borrow_time, r.remand_time
from book b
         left join record r on b.id = r.book_id
         left join user u on r.borrower_id = u.id;

#右连接
select b.name, b.price, b.upload_time, u.name, b.state, r.borrow_time, r.remand_time
from user u
         right join record r on r.borrower_id = u.id
         right join book b on b.id = r.book_id;

子查询

#未归还的书中价格大于30的书名
select name, price
from book
where price in (select price from book b where price > 30)
  and state = '未归还';

添加外键约束

#对图书表中的借阅者添加外键约束
alter table record #从表
    add constraint yk_book_id foreign key (book_id) references book (id),#主表
    add constraint yk_borrow_id foreign key (borrower_id) references user (id);

测试外键约束

#添加脏数据
insert into record
values (0,8,22,date ('2021_5_20'),null);

#删除主表数据
delete from user where id=4;

#修改主表外键(错误)
update user set id=9 where id=3;

添加级联删除和级联修改

#对借书表中的图书编号和姓名编号添加外键约束、级联删除和级联修改
alter table record
    add constraint yk_book_id foreign key (book_id) references book (id) on delete cascade on update cascade,
    add constraint yk_borrow_id foreign key (borrower_id) references user (id) on delete cascade on update cascade;

测试级联删除和级联修改

#添加脏数据
insert into record
values (0,8,22,date ('2021_5_20'),null);

#删除主表数据
delete from user where id=4;
select id, name from user;
select id, book_id, borrower_id, borrow_time, remand_time from record;

#修改主表外键
update user set id=9 where id=3;
select id, name from user;
select id, book_id, borrower_id, borrow_time, remand_time from record;