JavaEE从入门到起飞(六) ~ mysql

数据库理论

数据库介绍

数据库就是一个存储数据的文件系统，它会按照特定的格式对数据进行存储，用户可以对数据库中的数据进行增加，修改，删除及查询操作(CRUD)

数据库全称叫DBMS（数据库管理系统），它的层次是：软件–>数据库–>数据表–>数据记录

业界的数据库种类有很多，比较知名的是下面这些

• MySQL： 开源免费的数据库，已经被Oracle公司收购了，MySQL6.x版本（商业、社区）也开始收费

• Oracle：收费的大型数据库，Oracle公司的产品

• DB2： IBM公司的数据库产品，收费的

• SQLServer：MicroSoft公司收费的中型的数据库，C#、.net等语言常使用

MySQL安装

见官网文档

MySQL连接

-- 语法
mysql [-h 服务器地址  -P 端口号] -u用户名 -p密码

--实例
mysql -uroot -p

经典错误1: ERROR 2003 (HY000): Can’t connect to MySQL server on ‘localhost’ (10061) 表示数据库服务停止了

经典错误2: ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES) 表示你的用户名和密码不对

① 打开连接窗口

点击idea左下角的图标,在弹出框中选择Database，在idea右边侧边栏中选中Database，点击+号,然后选择Data Source中的MySQL

② 选择驱动版本，版本号选择8.0.13，Class选择com.mysql.cj.jdbc.Driver

③ 填写数据库服务地址、端口、账号、密码，点击测试，通过之后，点击ok保存连接

SQL介绍

SQL是用来操作数据库的一种语言，通过sql可以实现数据库、数据表、数据记录的增删改查

我们一般把增删改查称为CRUD：create创建、retrieve 检索、update 修改、delete删除

SQL的书写标准如下：

1. SQL可以单行书写，也可以多行书写, 它以；结束一条SQL语句
2. 在windows环境中SQL语句是不区分大小写的
3. 在SQL中可以使用注释，一般有两种：单行注释 -- 注释内容 和 多行注释 /* 注释内容 */

数据库操作

-- 创建数据库
	create database [IF NOT EXISTS] 数据库名;

-- 查询数据库
	show databases;

-- 删除数据库
	drop database [IF EXISTS] 数据库名;

-- 查看正在使用的数据库
	select database();

-- 切换数据库
	use 数据库名;

-- 创建数据库: 创建一个名为db1的数据库
create database db1;
create database db1;  -- 创建的库如果是存在的, 会报错 Can't create database 'db1'; database exists
create database if not exists db1; -- 使用if not exists  创建的库如果是存在的,不会报错

-- 查询数据库：查看所有数据数据库
show databases;

-- 删除数据库: 删除名为db1的数据库
drop database db1;
drop database db1; -- 如果库不存在, 直接删 会报错 Can't drop database 'db1'; database doesn't exist
drop database if exists db1; -- 如果存在就删除  不存在就算了

-- 切换数据库：1) 查看正在使用的数据库
select database();

-- 切换数据库：2) 切换正在使用的数据库为db1(db1必须存在)
create database db1;
use db1; -- Unknown database 'db1'

数据表操作

创建数据表

基本语法

-- 语法
    create table 表名(    
        字段名1  字段类型1(字段长度) [ comment  字段1注释 ],  -- 不是;号    
        字段名2  字段类型2(字段长度) [ comment  字段2注释 ],	
         ....	
        字段名n  字段类型n(字段长度) [ comment  字段n注释 ]-- 最后一列后面是没有,的
    ) [ comment  表注释 ];

/**
创建数据表  1) 在db1中创建一张名为student1的数据表，表中字段的要求如下
    id：标识，数字类型
    name：姓名，字符串类型，长度限制最多30个字符
    gender：性别，字符串类型，长度限制为1个字符
    age：年龄，数值类型
    birthday：生日，日期类型，格式YYYY-mm-dd
*/
create table student1(
    id int comment '标识',
    name varchar(30) comment '姓名',
    gender varchar(1) comment '性别',
    age int comment '年龄',
    birthday date comment '生日'
) comment '学生表';

数据类型

MySQL中主要有三大类数据类型： 分别是数值、字符、日期

大分类	类型	描述
数值类型	tinyint	小整数
	int	整数
	bigint	大整数
	float	浮点数类型
	double	浮点数类型
字符类型	varchar(30)	可变长度字符串，如果插入的长度小于定义长度时，插入多长就存多长
	char(11)	固定长度字符串，如果插入的长度小于定义长度，则可以用空格进行填充
日期类型	date	日期，格式：yyyy-MM-dd
	datetime	日期时间，格式：yyyy-MM-dd HH:mm:ss

/**
	创建数据表  2) 
	在db1中创建一张名为student2的数据表，要求跟student1一致，数据类型要更符合设计原则
*/
create table student2(
     id int comment '标识',
     name varchar(30) comment '姓名',
     gender char(1) comment '性别',
     age tinyint unsigned comment '年龄', -- 有符号(-128-127)  无符号(0-255)
     birthday date comment '生日'
) comment '学生表';

数据约束

约束用于对表中的数据进行进一步的限制，一般作用在表中的字段上，用于保证数据的正确性。

约束	描述	关键字
主键约束	主键是一行数据的唯一标识，要求非空且唯一；一张表只能有一个主键列	primary key(auto increment自增)
非空约束	限制该字段值不能为null	not null
唯一约束	保证字段的所有数据都是唯一、不重复的; 可以为null,多列都可以为null	unique
默认约束	保存数据时，如果未指定该字段值，则采用默认值	default
外键约束	让两张表的数据建立连接，保证数据的一致性和完整性	foreign key

添加完约束之后，可以查询添加一些违法数据，观察报错

• Duplicate entry ‘1’ for key ‘PRIMARY’ id重复会报错
• Duplicate entry ‘黑张三’ for key ‘name’ 姓名重复会报错
• Column ‘age’ cannot be null age为null，会报错

/**
	创建数据表  3) 在db1中创建一张名为student3的数据表，表中字段的要求如下
    id：数字类型，`主键字段，并且数值自动增长`
    name：字符串类型，长度限制最多30个字符，`要求姓名不能出现重复`
    gender：字符串类型，长度限制为1个字符，`如果不插入，默认为男`
    age：数值类型，`不能为空`
    birthday：日期类型，格式YYYY-mm-dd
*/
create table student3(
                         id int primary key auto_increment comment '标识',
                         name varchar(30) unique comment '姓名',
                         gender char(1)  default '男'  comment '性别',
                         age tinyint unsigned not null comment '年龄', -- 有符号(-128-127)  无符号(0-255)
                         birthday date comment '生日'
) comment '学生表';
-- ------------------------添加完约束之后,可以查询添加一些违法数据,观察报错-------------------------------------- --
-- id重复会报错   Duplicate entry '1' for key 'PRIMARY'
-- 姓名重复会报错 Duplicate entry '黑张三' for key 'name'
-- age为null,会报错  Column 'age' cannot be null

查询数据表

-- 查看库中所有数据表
	show tables;

-- 查看表结构
	desc 数据表名;

-- 查看表的建表语句
	show create table 数据表名;

-- 查询数据表 1) 查看当前库中的所有数据表
show tables;

-- 查询数据表 2) 查看student3表的表结构
desc student3;

-- 查询数据表 3) 查看student3表的建表语句
show create table student3;

修改数据表

-- 添加字段
	alter table 表名 add  字段名 类型(长度) 

-- 修改字段类型
	alter table 表名 modify 字段名  新数据类型(长度)

-- 修改字段名和字段类型
	alter table 表名 change 旧字段名 新字段名 类型 (长度) 

-- 删除字段
	alter table 表名 drop column 字段名

-- 修改表名
	rename table 表名 to 新表名

-- 修改数据表 1) 修改student3表, 添加一列description 变长字符串类型，长度30
alter table student3 add description varchar(30);

-- 修改数据表 2) 修改student3表 description列为定长字符串类型，长度40
alter table student3 modify description char(40);

-- 修改数据表 3) 修改student3表 description列名为descr，变长字符串类型，长度20
alter table student3 change description descr varchar(20);

-- 修改数据表 4) 删除student3表的descr列
alter table student3 drop column descr;

-- 修改数据表 5) 修改student3表的名称为stu
rename table student3 to stu;

删除数据表

-- 删除数据表
	drop table [ if exists ] 表名

-- 删除stu表
	drop table if exists stu;

单表增删改查（重点）

增加数据

-- 指定字段添加数据 
	insert into 表名 (字段名1, 字段名2)  values (值1, 值2)

-- 全部字段添加数据 
	insert into 表名 values (值1, 值2, ...)

-- 批量添加数据（指定字段）
	insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2)

-- 批量添加数据（全部字段）
	insert into 表名 values (值1, 值2, ...), (值1, 值2, ...)

注意：

1. 插入数据时，指定的字段顺序需要与值的顺序是一一对应的
2. 字符串和日期型数据应该包含在引号中
3. 插入的数据大小，应该在字段的规定范围内

-- 准备数据表
drop table if exists tb_emp;
create table tb_emp(
       id int primary key auto_increment comment '主键ID',
       username    varchar(20)                  unique not null comment '用户名',
       password    varchar(32) default '123456' null comment '密码',
       name        varchar(10)                  not null comment '姓名',
       gender      tinyint unsigned             not null comment '性别, 1 男, 2 女',
       image       varchar(300)                 null comment '图像url',
       job         tinyint unsigned             null comment '职位, 1 班主任 , 2 讲师 , 3 学工主管, 4 教研主管',
       entrydate   date                         null comment '入职日期',
       create_time datetime                     not null comment '创建时间',
       update_time datetime                     not null comment '修改时间'
) comment '员工表';



-- 准备数据表
drop table if exists tb_emp;
create table tb_emp(
                       id int primary key auto_increment comment '主键ID',
                       username    varchar(20)                  unique not null comment '用户名',
                       password    varchar(32) default '123456' null comment '密码',
                       name        varchar(10)                  not null comment '姓名',
                       gender      tinyint unsigned             not null comment '性别, 1 男, 2 女',
                       image       varchar(300)                 null comment '图像url',
                       job         tinyint unsigned             null comment '职位, 1 班主任 , 2 讲师 , 3 学工主管, 4 教研主管',
                       entrydate   date                         null comment '入职日期',
                       create_time datetime                     not null comment '创建时间',
                       update_time datetime                     not null comment '修改时间'
) comment '员工表';



-- 插入数据 1) 为 tb_emp 表的 username, name, gender, create_time, update_time 字段插入值
insert into tb_emp(username, name, gender, create_time, update_time) values ('zhangsan','张三',1,'2024-08-03 10:10:10',now());

-- 插入数据 2) 为 tb_emp 表的 所有字段插入值
insert into tb_emp(id, username, password, name, gender, image, job, entrydate, create_time, update_time)
                    values (null,'lisi',null,'李四',1,'1.jpg',1,'2019-01-01',now(),now());
insert into tb_emp values (null,'wangwu',null,'王五',1,'1.jpg',1,'2019-01-01',now(),now());

-- 插入数据 3) 批量为 为 tb_emp 表的 username , name , gender, create_time, update_time 字段插入数据
insert into tb_emp(username, name, gender, create_time, update_time) values
        ('zhangsan1','张三1',1,'2024-08-03 10:10:10',now()),
        ('lisi1','李四1',1,'2024-08-03 10:10:10',now()),
        ('wangwu1','王五1',1,'2024-08-03 10:10:10',now());

修改数据

-- 修改数据
	update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [ where  条件 ]

注意：修改语句如果没有where条件，则会修改整张表的所有数据

-- 修改数据 1) 将 tb_emp 表的所有员工的入职日期更新为'2010-01-01'
update tb_emp set entrydate = '2010-01-01';

-- 修改数据 2) 将 tb_emp 表的ID为1员工 姓名name字段更新为'黑马'
update tb_emp set name = '黑马' where id = 1;

-- 修改数据 3) 将 tb_emp 表的ID为1员工 姓名name字段更新为'传智',入职日期更新为'2022-01-01'
update  tb_emp set name = '传智', entrydate = '2022-01-01' where  id = 1;

删除数据

-- 删除数据
	delete  from  表名  [ where  条件 ]

注意：删除语句中如果不加条件，则将所有数据都会被删除！

-- 删除数据 1) 删除 tb_emp 表中ID为1的员工
delete from tb_emp where id = 1;

-- 删除数据 2) 删除 tb_emp 表中的所有员工
delete from tb_emp;

查询数据

-- 员工管理(带约束)
drop table if exists tb_emp;
create table tb_emp (
    id int unsigned primary key auto_increment comment 'ID',
    username varchar(20) not null unique comment '用户名',
    password varchar(32) default '123456' comment '密码',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
    image varchar(300) comment '图像',
    job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
    entrydate date comment '入职时间',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
) comment '员工表';

-- 准备测试数据
INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate, create_time, update_time) VALUES
    (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:37'),
    (3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', '2022-10-27 16:35:33', '2022-10-27 16:35:39'),
    (4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:41'),
    (5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', '2022-10-27 16:35:33', '2022-10-27 16:35:43'),
    (6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:45'),
    (7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', '2022-10-27 16:35:33', '2022-10-27 16:35:47'),
    (8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', '2022-10-27 16:35:33', '2022-10-27 16:35:49'),
    (9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', '2022-10-27 16:35:33', '2022-10-27 16:35:51'),
    (10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:53'),
    (11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 2, '2007-02-01', '2022-10-27 16:35:33', '2022-10-27 16:35:55'),
    (12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 2, '2008-08-18', '2022-10-27 16:35:33', '2022-10-27 16:35:57'),
    (13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 1, '2012-11-01', '2022-10-27 16:35:33', '2022-10-27 16:35:59'),
    (14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', '2022-10-27 16:35:33', '2022-10-27 16:36:01'),
    (15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', '2022-10-27 16:35:33', '2022-10-27 16:36:03'),
    (16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:05'),
    (17, 'chenyouliang', '12345678', '陈友谅', 1, '17.jpg', null, '2015-03-21', '2022-10-27 16:35:33', '2022-10-27 16:36:07'),
    (18, 'zhang1', '123456', '张一', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:09'),
    (19, 'zhang2', '123456', '张二', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:11'),
    (20, 'zhang3', '123456', '张三', 1, '2.jpg', 2, '2018-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:13'),
    (21, 'zhang4', '123456', '张四', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:15'),
    (22, 'zhang5', '123456', '张五', 1, '2.jpg', 2, '2016-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:17'),
    (23, 'zhang6', '123456', '张六', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:19'),
    (24, 'zhang7', '123456', '张七', 1, '2.jpg', 2, '2006-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:21'),
    (25, 'zhang8', '123456', '张八', 1, '2.jpg', 2, '2002-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:23'),
    (26, 'zhang9', '123456', '张九', 1, '2.jpg', 2, '2011-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:25'),
    (27, 'zhang10', '123456', '张十', 1, '2.jpg', 2, '2004-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:27'),
    (28, 'zhang11', '123456', '张十一', 1, '2.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:29'),
    (29, 'zhang12', '123456', '张十二', 1, '2.jpg', 2, '2020-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:31');

基本查询

-- 查询指定字段
	select 字段1, 字段2, 字段3  from 表名

-- 查询所有字段
	select  *  from 表名

-- 设置别名
	select  字段1  [ as ] 别名1  , 字段2  [ as ] 别名2    from   表名

-- 去除重复记录
	select  distinct  字段列表  from  表名

-- ---------------------------------------基本查询----------------------------------------------
-- 1. 查询指定字段 name,entrydate 并返回
select name,entrydate from tb_emp;

-- 2. 查询返回所有字段
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp;
select * from tb_emp;

-- 3. 查询所有员工的 name,entrydate, 并起别名(姓名、入职日期)  --- as 关键字可以省略
select name as '姓名',entrydate as '入职日期' from tb_emp;
select name '姓名',entrydate '入职日期' from tb_emp;

-- 4. 查询员工有哪几种职位(不要重复)
select distinct job from tb_emp;

条件查询

条件查询：select 字段列表 from 表名 where 条件列表

比较运算符	功能		逻辑运算符	功能
>	大于		and	并且 (多个条件同时成立)
>=	大于等于		or	或者 (多个条件任意一个成立)
<	小于		!	非 , 不是
<=	小于等于
=	等于
<> 或 !=	不等于
is null	是null
between … and …	在某个范围之内(含最小、最大值)
in(…)	在in之后的列表中的值，多选一
like 占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)

-- ----------------------------------------条件查询--------------------------------------------
-- 1-1. 查询 姓名 为 杨逍 的员工
select * from tb_emp where name = '杨逍';

-- 1-2. 查询在 id小于等于5 的员工信息
select * from tb_emp where id <= 5;

-- 1-3. 查询 密码不等于 '123456' 的员工信息
select * from tb_emp where password != '123456';

-- 1-4. 查询 没有分配职位 的员工信息  null 六亲不认
select * from tb_emp where job is null;

-- 1-5. 查询 有职位 的员工信息
select * from tb_emp where job is not null;

-- 2-1. 查询 id<=5 并且 job=2  的员工信息
select * from tb_emp where id <=5  and job = 2;

-- 2-1. 查询 id<=5 或者 job=2  的员工信息
select * from tb_emp where id <=5  or job = 2;


-- 3-1. 查询入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01';

-- 3-2. 查询职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
select * from tb_emp where job in (2,3,4);


-- 4-1. 查询姓 '张' 的员工信息
select * from tb_emp where name like '张%';

-- 4-2. 查询姓名中包含 '三' 的员工信息
select * from tb_emp where name like '%三%';


-- 4-3. 查询姓'张'，并且姓名为三个字的员工信息
select * from tb_emp where name like '张__';

聚合函数

聚合函数: 将一列数据作为一个整体，进行纵向计算，语法为: select 聚合函数(字段名) from 表名

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

-- ----------------------------------聚合函数-----------------------------------------------
-- null不参与任何聚合运算

-- 1. 统计该企业员工数量
select count(id) from tb_emp;
select count(*) from tb_emp;
select count(1) from tb_emp;
select count(job) from tb_emp; -- 数量的时候避开有null的列

-- 2. 统计该企业最早入职的员工的入职日期
select min(entrydate) from tb_emp;

-- 3. 统计该企业最迟入职的员工的入职日期
select max(entrydate) from tb_emp;

-- 4. 统计该企业员工ID的平均值
select avg(id) from tb_emp;

-- 5. 统计该企业员工的ID之和
select sum(id) from tb_emp;

分组过滤

-- 分组
	select 分组字段，聚合函数() from 表名 group by 分组字段名

-- 分组后过滤
	select 分组字段，聚合函数() from 表名 group by 分组字段名 having 分组后过滤条件

-- -------------------------------------分组过滤-----------------------------------------
-- 一般情况下, 分组会跟聚合函数一起出现
-- 如果没有分组或者在分组之前来筛选条件,使用where; 如果是分组之后的结果再进行筛选,用having (having只要出现, group by 就一定会出现)

-- 1. 根据性别分组， 统计男性和女性员工的数量
select gender, count(1) from tb_emp group by gender;

-- 2. 先查询入职时间在 '2015-01-01' (包含) 以前的员工
select * from tb_emp where entrydate <= '2015-01-01';

-- 3. 先查询入职时间在 '2015-01-01' (包含) 以前的员工，并对结果根据职位分组
select job,count(1) from tb_emp where entrydate <= '2015-01-01' group by job;

-- 4. 先查询入职时间在 '2015-01-01' (包含) 以前的员工，并对结果根据职位分组，获取员工数量大于等于2的职位
select job,count(1) from tb_emp where entrydate <= '2015-01-01' group by job having count(1) >= 2;
select job,count(1) as num from tb_emp where entrydate <= '2015-01-01' group by job having num >= 2;

补充：

*聚合函数将一列数据作为整体纵向计算，因此不涉及其他列，聚合函数只能查询出一个列，但分组的话，可以显示按照分组的列。
如果你使用 GROUP BY 子句来对 job 列进行分组，那么 SELECT 中应该要么选择被分组的列（job），要么选择聚合函数应用于其他列（如 COUNT(*), SUM(salary) 等）。在这种情况下，如果你使用 SELECT ，它会选择所有列，但这些列中可能有多个不同的值，这与 GROUP BY 的语义相违背。

排序

-- 排序
	select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2
	
-- 注意
    ASC：升序（默认值）
    DESC：降序

-- -------------------------------------排序-----------------------------------------
-- 1. 根据入职时间，对员工进行降序排序
select * from tb_emp order by entrydate desc;

-- 2. 根据入职时间，对员工进行升序排序
select * from tb_emp order by entrydate asc;
select * from tb_emp order by entrydate; -- 省略排序方式的话,默认就是升序

-- 3. 根据入职时间对公司的员工进行升序排序，入职时间相同再按照ID进行降序排序
select * from tb_emp order by entrydate asc, id desc;

分页

-- 分页
	select 字段列表  from 表名 limit 起始索引, 查询记录数

-- -------------------------------------分页--------------------------------------------
-- 1. 查询第1页员工数据, 每页展示5条记录 0,5
select * from tb_emp limit 0,5;

-- 2. 查询第2页员工数据, 每页展示5条记录 5,5
select * from tb_emp limit 5,5;

-- 3. 查询第3页员工数据, 每页展示5条记录
select * from tb_emp limit 10,5;

-- 4. 查询第4页员工数据, 每页展示5条记录
select * from tb_emp limit 15,5;

-- 4. 查询第pageNum页员工数据, 每页展示pageSize条记录
select * from tb_emp limit (pageNum - 1)* pageSize,pageSize;

小结

1. 排序: order by 列 desc
2. 截取: limit 开始,几个
3. 聚合函数: count(列)
4. 分组: group by 列
5. 过滤: having 过滤条件
6. 普通条件: where

select 集合函数() from 表 [where 条件]  [group by 分组]  [having 过滤] [order by 排序] [limit 截取]

代码的执行顺序。

多表查询（重点）

表间关系

项目开发中，因为业务之间相互关联，所以实体与实体之间存在联系，故而表跟表之间也存在着各种联系

我们把这种能够表示表间关系的数据库称为关系型数据库（RDBMS），在数据库设计上，表关系分为三种：

• 一对多

  场景：班级和学生 部门和员工

  说明：一个班级对应多个学生，一个学生只能对应一个班级

• 多对多

  场景：学生和课程 学生和老师

  说明：一个学生对应多个课程，一个课程对应多个学生

• 一对一

  场景：丈夫和妻子 人和身份证号

  说明：一个丈夫只有一个妻子，一个妻子也只有一个丈夫

一对多: (最多) 在从表中添加外键列指向主表的主键

多对多: (最多) 添加一张中间表, 在中间表中一定会有两个外键列,分别指向两张主表的主键

一对一: (比较少) 在从表中添加外键,指向主表的主键; 外键列上还要有唯一约束

一对多

例子: 班级和学生

建表原则:

• 在表设计时，我们把一的一方称为主表，多的一方称为从表
• 在从表中添加一列指向主表的主键列，新加列名字一般是（主表名_主键名）
• 将新建的这一列称为外键列，它的作用是为了建立当前表和其他表的关联关系，一张表中，允许有多个外键列

-- 创建班级表
create table class(
    id int primary key,
    name varchar(30)
);

-- 创建学生表
create table student(
    id int primary key,
    name varchar(30),
    class_id int
);

-- 插入数据
insert into class values (1,'JAVAEE166'),(2,'JAVAEE167');
insert into student values (1,'张三',1),(2,'李四',2),(3,'王五',2);

外键约束

作用：限定两张表有关系的数据，保证数据的正确性、有效性和完整性。

-- 创建表的时候添加
	create table 表名(
		列名  列类型,
                [constraint 约束名: 外键列_fk]  foreign key (列名)  references  主表(主键)
            )
-- 创建表之后单独添加
	alter table 表名 add [constraint 约束名]  foreign key (列名)  references  主表(主键)

-- 添加外键约束
alter table student add constraint class_id_fk foreign key(class_id) references class(id);

现在的企业表设计中,不推荐使用物理外键; 推荐使用逻辑外键

多对多

例子: 学生和课程

建表原则:

• 新建一张中间表，作为两张主表的从表存在
• 在中间表中创建两个外键，分别指向两张主表的主键
• 多对多一个关系本质上是由两个一对多拼接而成

-- 创建学生表
create table student(
        id int primary key,
        name varchar(30)
);

-- 创建课程表
create table course(
       id int primary key,
       name varchar(30)
);

-- 建立中间表
create  table student_course(
        id int primary key auto_increment,
        student_id int,
        course_id int
);

一对一

例子: 用户和身份证

建表原则

• 关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率
• 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

-- 创建用户表
create table tb_user(
        id int unsigned  primary key auto_increment comment 'ID',
        name varchar(10) not null comment '姓名',
        gender tinyint unsigned not null comment '性别, 1 男  2 女',
        phone char(11) comment '手机号',
        degree varchar(10) comment '学历'
) comment '用户基本信息表';

-- 用户身份信息表
create table tb_user_card(
     id int unsigned  primary key auto_increment comment 'ID',
     nationality varchar(10) not null comment '民族',
     birthday date not null comment '生日',
     idcard char(18) not null comment '身份证号',
     issued varchar(20) not null comment '签发机关',
     expire_begin date not null comment '有效期限-开始',
     expire_end date comment '有效期限-结束',
     user_id int unsigned not null unique comment '用户ID'-- 外键,指向用户表主键, 必须有唯一约束,才能保证1对1
) comment '用户身份信息表';

多表查询

在企业开发中，我们一个业务需要的数据往往是来自多张表的，所以这时候就需要多表联合查询。

所谓的多表联合查询就是使用一条SQL语句将多张表的数据一起查询展示出来。

-- 创建部门表（主表）
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),  -- 性别（sex）
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT -- 外键字段
);
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',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',6666,'2011-03-14',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('沙僧','男',4500,'2017-03-04',null);

交叉连接（了解）

查询员工表和部门表中所有信息

-- 解释
	使用左表中的每一条数据分别去连接右表中的每一条数据, 将所有的连接结果都展示出来
	
-- 语法
 	select * from 左表,右表

内连接

查询员工表和部门表中的信息, 仅显示两表中全部符合的数据

-- 解释 
	使用左表中的每一条数据分别去连接右表中的每一条数据, 仅仅显示出匹配成功的那部分
	
-- 语法
	隐式内连接: select * from  左表,右表 where 连接条件
	显示内连接: select * from 左表 [inner] join 右表 on 连接条件

左外连接

查询所有员工和对应的部门信息, 不存在部门的使用null补齐

-- 解释
	首先要显示出左表的全部, 然后使用连接条件匹配右表,能匹配中的就显示,匹配不中的显示为null

-- 语法
	select * from 左表 left [outer] join 右表 on 连接条件

右外连接（了解）

查询所有员工和对应的部门信息, 不存在部门的使用null补齐

-- 解释
	首先要显示出右表的全部, 然后使用连接条件匹配左表,能匹配中的就显示,匹配不中的显示为null
	
-- 语法
	select * from 左表 right outer join 右表 on 连接条件

子查询

-- 解释:
	一个查询使用了另一个查询的结果
	
-- 子查询根据查询结果不同，作用不同
	单个值，使用 = > <  等进行条件判断 :    SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);
	多个值，使用in、not in进行条件判断 :    SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);
	一张表，将此结果声明为临时表来使用 :    SELECT * FROM (子查询) AS 表 JOIN 表 ON 条件;

-- ---------------------------------------连接查询--------------------------------------
-- 1. 查询员工表和部门表中所有信息(交叉连接)
select * from emp,dept;

-- 2. 查询员工和部门信息, 仅显示两表中全部符合的数据(内连接)
-- 隐式内连接
select * from emp,dept where emp.dept_id = dept.id;
select * from emp e , dept d where e.dept_id = d.id;  -- 推荐

-- 显示内连接
select * from emp e inner join dept d on e.dept_id = d.id;  -- 效率高


-- 3. 查询员工和部门信息, 显示所有员工, 员工部门不存在的使用null补齐(左外连接)
select * from emp e left outer join dept d on e.dept_id = d.id;


-- 4. 查询员工和部门信息, 显示所有部门, 部门中没有员工的使用null补齐(右外连接)
select * from emp e right outer join dept d on e.dept_id = d.id;
select * from  dept d left outer join emp e on e.dept_id = d.id;


-- -----------------------------------------子查询--------------------------------------
-- 1: 查询工资小于平均工资的员工有哪些？(子查询结果为一个值  标量子查询)
-- 1-1 求所有员工的平均工资
select avg(salary) from emp;  -- 5491.5
-- 1-2 谁的工资小于上面的结果
select * from emp where salary < 5491.5;
-- 1-3 拼
select * from emp where salary < (select avg(salary) from emp);

-- 2: 查询工资大于5000的员工，所在部门的名字 (子查询结果为多个值  列子查询)
-- 2-1 工资大于5000的员工的部门的id
select dept_id from emp where salary > 5000; -- 1,2
-- 2-2 从部门表中根据id查询名字
select * from dept where id in (1,2);
-- 2-3 拼
select * from dept where id in (select dept_id from emp where salary > 5000);


-- 3: 查询出2011年以后入职的员工信息，包括部门信息 (子查询结果为一张表   表子查询)
select * from emp where join_date >= '2011-01-01';
select * from (select * from emp where join_date >= '2011-01-01') as a;
select * from (select * from emp where join_date >= '2011-01-01') as a left join dept d on a.dept_id = d.id

多表案例

我们在企业开发时，根据不同的业务需求往往需要通过2张及以上的表中去查询需要的数据。其实不管是几张表的查询，都是有规律可循的。

-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY auto_increment, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门位置
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 职务表
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20), -- 职务名称
  description VARCHAR(50) -- 职务描述
);
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id  外键
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资 99999.99
  bonus DECIMAL(7,2), -- 奖金 99999.99
  dept_id INT, -- 所在部门编号  外键
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,  -- 等级
  losalary INT, -- 最低工资
  hisalary INT -- 最高工资
);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

-- 1 查询所有员工信息。显示员工姓名，工资，职务名称
-- 1. 确定本次查询涉及哪几张表   emp   job
-- 2. 确定表之间的连接条件
select * from emp e left join job j on e.job_id = j.id;
-- 3. 确定有无业务逻辑 where
-- 4. 确定显示的字段
select e.ename,e.salary,j.jname from emp e left join job j on e.job_id = j.id;

-- 2 查询所有员工信息。显示员工姓名，工资，职务名称，部门名称
-- 1. 确定本次查询涉及哪几张表 emp job dept
-- 2. 确定表之间的连接条件
select * from emp e
    left join job j on e.job_id = j.id
    left join dept d on e.dept_id = d.id;
-- 3. 确定有无业务逻辑
-- 4. 确定显示的字段
select e.ename,e.salary,j.jname,d.dname from emp e
                  left join job j on e.job_id = j.id
                  left join dept d on e.dept_id = d.id;


-- 3 查询所有员工信息。显示员工姓名，工资，职务名称，部门名称，工资等级
-- 1. 确定本次查询涉及哪几张表 emp job dept salarygrade
-- 2. 确定表之间的连接条件
select * from emp e
                  left join job j on e.job_id = j.id
                  left join dept d on e.dept_id = d.id
                  left join salarygrade s on e.salary between s.losalary and s.hisalary;
-- 3. 确定有无业务逻辑
-- 4. 确定显示的字段
select e.ename,e.salary,j.jname,d.dname,s.grade from emp e
                  left join job j on e.job_id = j.id
                  left join dept d on e.dept_id = d.id
                  left join salarygrade s on e.salary between s.losalary and s.hisalary;

-- 4 查询经理的信息。  显示员工姓名，工资，职务名称，部门名称，工资等级
select e.ename,e.salary,j.jname,d.dname,s.grade from emp e
         left join job j on e.job_id = j.id
         left join dept d on e.dept_id = d.id
         left join salarygrade s on e.salary between s.losalary and s.hisalary
         where j.jname = '经理';

-- 5 查询所有员工的姓名和其经理的姓名  (自关联查询)
select e.ename,m.ename from emp e left join emp m on e.mgr =m.id;

一般规律

-- 1. 确定本次查询涉及哪几张表
-- 2. 确定表之间的连接条件
-- 3. 确定有无业务逻辑
-- 4. 确定显示的字段

事务

事务引入

场景：目前有一张账户表，表中有两个账户

-- 创建账户表,并且添加两条测试数据
CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(32),
	money DOUBLE 
);
INSERT INTO account (NAME, money) VALUES ('A', 1000), ('B', 1000);

需求: 实现这样一个业务, 从A向B账户转10元钱

-- 将A账户减去10元
update account set money=money-10 where name = 'A';  --执行成功

-- 将B账户加上10元
update account set money=money+10 where name = 'B';  --遇到问题,执行失败了

问题：如果上面的两条sql，一条执行成功了，但是另一条执行失败了，此时就造成了数据的不一致

要解决上述的问题，就需要通过数据库中的事务来解决。

事务解释

一个业务操作包含多个子操作，如果这个业务操作被事务管理了，那么这些子操作要么同时成功，要么同时失败。

在MySQL数据库中，默认情况下，一条DML语句就是一个独立的事务。

SQL语法	描述
begin; (start transaction)	开启手动控制事务
commit;	提交事务
rollback;	回滚事务

begin; --开启事务
update account set money=money-10 where name = 'AA';
update account set money=money+10 where name = 'BB';
commit; -- 提交事务


begin; --开启事务
update account set money=money-10 where name = 'AA';
update account set money=money+10 where name = 'BB';
rollback; -- 回滚事务

总结

1. 看的懂对应数据库 数据表 增删改查

2. 会有视图工具操作数据库 数据表

3. 数据记录增删改 查 重点练习

4. 表间关系分得清 创建表的时候 会设计表间关系

5. 多表查询步骤+自查询

6. 事务理解