1.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保存连接
2.SQL介绍
SQL是用来操作数据库的一种语言，通过sql可以实现数据库、数据表、数据记录的增删改查
我们一般把增删改查称为CRUD：create创建、retrieve 检索、update 修改、delete删除
SQL的书写标准如下：
SQL可以单行书写，也可以多行书写, 它以；结束一条SQL语句
在windows环境中SQL语句是不区分大小写的
在SQL中可以使用注释，一般有两种：单行注释
-- 注释内容和 多行注释/* 注释内容 */
3.数据库操作
-- 创建数据库
create database [IF NOT EXISTS] 数据库名;-- 查询数据库
show databases;-- 删除数据库
drop database [IF EXISTS] 数据库名;-- 查看正在使用的数据库
select database();-- 切换数据库
use 数据库名;
4.
-- 创建数据库: 创建一个名为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'
4.数据表操作
-- 语法
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 '学生表';
4.1数据类型
MySQL中主要有三大类数据类型： 分别是数值、字符、日期
/**
创建数据表 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 '学生表';
4.2数据约束
约束用于对表中的数据进行进一步的限制，一般作用在表中的字段上，用于保证数据的正确性。
添加完约束之后，可以查询添加一些违法数据，观察报错
-
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
4.2查看数据表
-- 查看库中所有数据表
show tables;-- 查看表结构
desc 数据表名;-- 查看表的建表语句
show create table 数据表名;
-- 查询数据表 1) 查看当前库中的所有数据表show tables;-- 查询数据表 2) 查看student3表的表结构desc student3;-- 查询数据表 3) 查看student3表的建表语句show create table student3;
4.3修改数据表
-- 添加字段
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;
4.3删除数据表
5-- 删除stu表
drop table if exists stu;
5.单表增删改查（重点）
5.1增加数据
-- 指定字段添加数据
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, ...)
注意：
-
插入数据时，指定的字段顺序需要与值的顺序是一一对应的
-
字符串和日期型数据应该包含在引号中
-
插入的数据大小，应该在字段的规定范围内
-- 准备数据表
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());
5.2修改数据
-- 修改数据
update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [ where 条件 ]
-- 修改数据 1) 将 tb_emp 表的所有员工的入职日期更新为'2010-01-01'
update tb_emp set entrydate = '2010-01-01';
5.3删除数据
注意：删除语句中如果不加条件，则将所有数据都会被删除！
-- 删除数据 1) 删除 tb_emp 表中ID为1的员工
delete from tb_emp where id = 1;-- 删除数据 2) 删除 tb_emp 表中的所有员工
delete from tb_emp;
5.4查询数据
-- 员工管理(带约束)
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');5.4.1基本查询
-- 查询指定字段
select 字段1, 字段2, 字段3 from 表名-- 查询所有字段
select * from 表名-- 设置别名
select 字段1 [ as ] 别名1 , 字段2 [ as ] 别名2 from 表名-- 去除重复记录
select distinct 字段列表 from 表名
5.4.2条件查询
条件查询：select 字段列表 from 表名 where 条件列表
-- 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 '张__';
5.4.2聚合函数
聚合函数: 将一列数据作为一个整体，进行纵向计算，语法为: select 聚合函数(字段名) from 表名
-- ----------------------------------聚合函数-----------------------------------------------
-- 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;
5.4.3分组过滤
-- 分组
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;5.4.4排序
-- 排序
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;5.4.5分页
-- 分页
select 字段列表 from 表名 limit 起始索引, 查询记录数
6.多表查询（重点）
6.1内连接
查询员工表和部门表中的信息, 仅显示两表中全部符合的数据
-- 解释
使用左表中的每一条数据分别去连接右表中的每一条数据, 仅仅显示出匹配成功的那部分
-- 语法
隐式内连接: select * from 左表,右表 where 连接条件
显示内连接: select * from 左表 [inner] join 右表 on 连接条件
6.2左外连接
查询所有员工和对应的部门信息, 不存在部门的使用null补齐
-- 解释
首先要显示出左表的全部, 然后使用连接条件匹配右表,能匹配中的就显示,匹配不中的显示为null-- 语法
select * from 左表 left [outer] join 右表 on 连接条件
6.3子查询
-- 解释:
一个查询使用了另一个查询的结果
-- 子查询根据查询结果不同，作用不同
单个值，使用 = > < 等进行条件判断 : 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.id7.事务
7.1事务引入
场景：目前有一张账户表，表中有两个账户
-- 创建账户表,并且添加两条测试数据
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，一条执行成功了，但是另一条执行失败了，此时就造成了数据的不一致
要解决上述的问题，就需要通过数据库中的事务来解决。
7.2事务解释
一个业务操作包含多个子操作，如果这个业务操作被事务管理了，那么这些子操作要么同时成功，要么同时失败。
在MySQL数据库中，默认情况下，一条DML语句就是一个独立的事务。
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; -- 回滚事务
43万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
