数据库-基础

最新推荐文章于 2024-10-02 00:27:17 发布

YY不爱吃烤鸭

最新推荐文章于 2024-10-02 00:27:17 发布

阅读量164

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/m0_67100317/article/details/132432165

版权

锲而舍之，朽木不折；坚持不懈，金石可镂。

基础

数据库相关概念

1.数据库：存储数据的仓库，数据是有组织的进行存储。DataBase(DB）。

2.数据库管理系统：操纵和管理数据库的大型软件。DataBase Managment System(DBSM)

3.SQL:操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。Structured QueryLanhuage(SQL)。

MySQL下载及安装

MySQL社区版

MySQL启动与停止

方式一：

在windows命令行输入services.msc进入Windows的系统中找到MySQL80,右键点击后选启动或停止。

方式二：

进入cmd，以管理员身份运行，输入net start mysql80启动，输入net stop mysql80停止。

MySQL客户端连接

方式一：MySQL自带的客户端命令行

开始菜单找到mysql，选择mysql客户端--，后输入密码，就连接上了mysql。

方式二：系统自带的命令行工具执行指令

mysql [-h 127.0.0.1 ] [-P 3306] -u root -p

-h指定连接的哪个ip，-P指定连接端口，这两个参数可以省略，默认本地3306.

-u指定root用root用户进行连接，-p输入密码

打开cmd进入后输入mysql -u root -p,后输入密码，就连接上了mysql。

注意：使用这种方式，需要配置PATH环境变量。

MySQL数据模型

DBMS是一个软件，用来创建、操作数据库。一个数据库中可以包含多张表结构。表才是最终存储数据的。

关系型数据库（RDBMS）

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点：

1.使用表存储数据，格式统一，便于维护

2.使用SQL语言操作，标准统一，使用方便

通过表来存储数据的数据库就是关系型数据库，不通过表来存储数据的数据库就是非关系型数据库

SQL

SQL通用语法
- 1. SQL语句可以单行或多行书写，以分号结尾
- 2. SQL语句可以使用空格/缩进来增强语句的可读性。
- 3,MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
- 4,注释：
  - 单行注释：－－注释内容或#注释内容 (MySQL特有）
  - 多行注释：／＊注释内容＊／
SQL分类
- DDL-数据库操作

可以按键盘上的上下键来查询之前执行过的语句

DDL-表操作

DDL-表操作-查询

要先进入表中

DDL-表操作-创建

要先想现在在哪个表中

DDL-表操作-数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

DDL-表操作-修改

DDL-表操作-删除

在删除表时，表中的数据也会被删除

小结

MySQL图形化界面

DML
- DML-介绍
  - DML英文全称是 Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改操作
- DML-添加数据
- DML-修改数据
- DML-删除数据
- 小结
DQL
- DQL-介绍
  - DQL英文全称是 Data Query Language（数据查询语言），数据查询语言，用来查询数据库中表的记录。
  - 查询关键字：SELECT
  - DQL-语法
- DQL-基本查询

as可以省略

去除重复

DQL-条件查询
DQL-聚合函数
- 介绍
  - 将一列数据作为一个整体，进行纵向计算。
- 常见的聚合函数
- 语法
DQL-分组查询
- 语法
- where与having区别
  - 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
  - 判断条件不同：where不能对聚合函数进行判读，而having可以。
- 注意
  - 执行顺序：where>聚合函数>having
  - 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
DQL-排序查询
- 语法
- 排序方式
  - asc :升序（默认）
  - desc:：降序
- 注意：
  - 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
DQL-分页查询
- 语法
- 注意：
  - 起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。
  - 分页查询是数据库的方言，不同的数据库有不同的事项，MySQL中是LIMIT
  - 如果查询的时第一页数据，起始索引可以省略，直接简写为 limit10.

DQL语句编写顺序和执行顺序
- 编写顺序
执行顺序
总结

DCL
- DCL-介绍
  - DCL英文全称是（Data Control Language）（数据控制语言），用来管理数据库用户。控制数据库的访问权限
- DCL-管理用户
  - 查询用户可以直接查询user表
  - localhost 代表当前主机可以访问，%代表任意主机可以访问
  - 注意：
    - 主机名可以使用%统配
    - 这类SQL开发人员操作的比较少，主要是DBA（Database Administratorv数据库管理员）操作。
- DCL-权限控制
  - MySQLz中定义了很多种权限，但是常用的就以下几种
  - 注意：
    - 多个权限之间，使用逗号分隔
    - 授权时，数据库名和表名可以使用*进行通配
- 小结

函数
- 函数-介绍
  - 函数是指一段可以直接被另一段程序调用的程序或代码
- 字符串函数
  - MySQL中内置了很多字符串函数，常用的几个如下：
- 数值函数
  - 常见的数值函数如下：
- 日期函数
  - 常见的日期函数如下:
    - datediff 是用第一个时间减去第二个时间
流程控制函数
- 流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

小结
约束
- 介绍
  - 概述：约束是作用于表中资源上的规则，用于闲置存储在表中的数据。
  - 目的：保证数据库中数据的正确。有效性和完整性。
  - 分类：

外键约束
- 概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
- eg:
- 语法：
外键删除更新行为
- 前两个为默认行为

eg:cascade

小结

多表查询

多表关系
概述：项目开发中，在进行数据库表结构设计时，会根据业务需求即业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多（多对一）
- 多对多
- 一对一
多表查询概述
- 概述：指从多张表中查询数据
- 笛卡尔积：在数学中，两个集合A集合和B集合的所有组合情况（在多表查询中，需要消除无效的笛卡尔积）

多表查询分类

连接查询

内连接：

相当于查询A、B交集部分数据

-- 内连接演示...注：如果连接外键de指为null,则查询不出来
-- 1.查询每个员工的姓名，及关联的部门的名称（隐式内连接实现）
select emp.name , dept.name from dept,emp where emp.dept_id=dept.id;
-- 如果表名长的话，可以起别名,起别名后，不能再使用表名
select e.name , d.name from dept d,emp e where e.dept_id=d.id;

-- 显示内连接
-- 2.查询每个员工的姓名，及关联的部门的名称
select e.name , d.name from emp e inner join dept d on e.dept_id = d.id;
select e.name , d.name from emp e join dept d on e.dept_id = d.id;

外连接：

左外连接：查询左表所有数据，以及两张表交集部分数据
右外连接：查询右表所有数据，以及两张表交集部分数据

-- 外连接演示。。注：如果连接外键的指为null,可以查询出来
-- 1.查询emp表的所有数据，和对应的部门信息（左外连接）
select e.* , d.name from emp e left outer join dept d on e.dept_id=d.id;

select e.* , d.name from emp e left join dept d on e.dept_id=d.id;

-- 2.查询dept表的所有数据，和对应的员工信息（右外连接）
select d.name ,e.* from dept d right join emp e on d.id = e.dept_id;

select d.name ,e.* from dept d join emp e on d.id = e.dept_id;

自连接：

当前表与自身的连接查询，自连接必须使用表别名

-- 自连接
-- 1.查询员工及其领导
select a.name,boss.name from emp a,emp boss where a.managerid=boss.id;

-- 2.查询所有员工emp及其领导的名字emp，如果员工没有领导，也需要查询出来
select  a.name,boss.name from  emp a left join emp boss on a.managerid=boss.id;

联合查询-union,union all
- 对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
子查询
- 概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称为子查询。
- 子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。
- 根据子查询的结果不同，分为：
  - 标量子查询（子查询结果为单个值）
  - 列子查询（子查询结果为一列）
  - 行子查询（子查询结果为一行）
  - 表子查询（子查询结果为多行多列）
- 根据子查询位置，分为LWHERE之后、FROM之后、SELECT之后。

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

常用的操作符：= <> > >= < <=

-- 标量子查询
-- 1.查询“ 研发部”的所有员工信息
-- a.查询“研发部”部门ID
select id from dept where name='研发部';
-- b.根据研发部门ID，查询员工信息
select * from emp where dept_id=1;
-- 合并后：
select * from emp where dept_id=(select id from dept where name='研发部');


-- 2.查询在“杨逍”入职之后的员工信息
-- a.查询“杨逍”入职年龄
select age from emp where name='杨逍';

-- b.查询年龄比杨逍小的员工信息
select * from emp where age<33;
-- 合并后：
select * from emp where age<(select age from emp where name='杨逍');

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：IN 、NOT IN 、ANY 、SOME 、ALL

-- 列子查询
-- 1.查询”销售部“和”研发部“的所有员工信息
-- a.查询"销售部"和“研发部”的部门ID
select id from dept where name='销售部' || name='研发部';
-- b.查询id=4,1的员工的信息
select * from emp where dept_id=1 ||emp.dept_id=4;
-- 总和
select * from emp where dept_id in(select id from dept where name='销售部' || name='研发部');

-- 2.查询比研发部所有年龄大的员工的信息
-- a.查询研发部员工的年龄
select age from emp where dept_id=(select id from dept where name='研发部');
-- b.查询比研发部年龄大的员工信息
select * from emp where  age> all(select age from emp where dept_id=(select id from dept where name='研发部'));

-- 3.查询比研发部其中一人年龄大的员工信息
-- a.查询研发部年龄
select age from emp where dept_id=(select id from dept where name='研发部');
select * from emp where age>any(select age from emp where dept_id=(select id from dept where name='研发部'));

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：=、<>、IN、NOT IN

-- 行子查询
-- 1.查询年龄与“张无忌”相同且直属领导也相同的员工信息
-- a.查询“张无忌”的年龄及直属领导
select age,managerid from emp where name='张无忌';
-- b.查询信息
select * from emp where (age,managerid)=(20,1);
-- 总和
select * from emp where (age,managerid)=(select age,managerid from emp where name='张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询

常用操作符：IN

-- 表子查询
-- 1.查询与“张无忌”或“常遇春”的年龄和职位相同的员工信息
-- a.查询职位和年龄
select job,age from emp where name='张无忌'or name='常遇春';
-- b.查询相同的
select * from emp where (job,age)in(select job,age from emp where name='张无忌'or name='常遇春');
-- 2.查询入职年龄22之后的员工信息，及部门信息
select * from emp where age>22;
select e.*,d.* from (select * from emp where age>22) e left join dept d on e.dept_id=d.id;

多表查询案例：

-- ----------------多表查询案例
create table salgrade(
    grade int,
    losal int ,
    hisal int
)comment '薪资等级表';
insert into salgrade values (1,0,3000),(2,3001,5000) ,
                           (3,5001,8000),(4,8001,10000),(5,10001,15000),
                           (6,15001,20000),(7,20001,25000),(8,25001,30000);


create  table dept(
    id int auto_increment primary key  comment 'ID',
    name varchar(50) not null comment '部门名称'
)comment '部门表';
insert into dept (id, name) values (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总裁办');


create table emp(
    id int primary key auto_increment comment 'id',
    name varchar(50) not null comment '姓名',
    age int check( age>0 && age<=120 ) comment '年龄',
    job varchar(50) comment '职位',
    salary int comment'薪资',
    entrydate  date comment '入职时间',
    managerid int  comment '直属领导ID',
    dept_id int  comment '部门id'
)comment '员工表';


insert into emp(id,name,age,job,salary,entrydate,managerid,dept_id)values(1,'金庸',66,'总裁',20000,'2000-01-01',null,5),
                                                        (2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
                                                        (3,'杨逍',33,'开发',8400,'2000-11-03',2,1),
                                                        (4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),
                                                        (5,'常遇春',43,'开发',10500,'2004-09-07',3,1),
                                                       (6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1),
                                                       (7,'灭绝',60,'财务总监',8500,'2002-09-12',1,3),
                                                       (8,'周芷若',19,'会计',4800,'2006-06-02',7,3),
                                                       (9,'丁敏君',23,'出纳',5250,'2009-05-13',7,3),
                                                       (10,'赵敏',20,'市场部总监',12500,'2004-10-12',1,2);

alter table emp add constraint fk_emp_dept_id foreign key emp(dept_id) references dept(id);

-- 1.查询员工的姓名、年龄、职位、部门信息（隐式内连接）
select emp.name,emp.age,emp.job,dept.name from emp,dept where emp.dept_id=dept.id;

-- 2.查询年龄小于30岁的员工的姓名、年龄、只为、部门信息（显式内连接）
select e.name,e.age,e.job,d.name from emp e join dept d on e.dept_id = d.id&&e.age<30;

-- 3.查询拥有员工的部门ID、部门名称
select id,name from dept where dept.id in (select dept_id from emp);

insert into emp values (11,'陈友量',42,NULL,2000,'2011-10-12',1,NULL);
-- 4.查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来
select ee.*,d.name from (select * from emp where age>40) ee left join dept d on d.id =ee.dept_id;
-- 5.查询所有员工的工资等级--隐式内连接
select e.name,s.grade from emp e,salgrade s  where e.salary>=s.losal && e.salary<=s.hisal;
select e.name,s.grade from emp e,salgrade s  where e.salary between s.losal and s.hisal;
-- 6.查询研发部所有员工的信息及工资等级
select e.*,s.grade from emp e,salgrade s  where e.dept_id=(select id from dept where dept.name='研发部') && e.salary between s.losal and s.hisal;
select e.name,s.grade from  emp e,salgrade s ,dept d  where d.name='研发部' and e.dept_id=d.id and  e.salary between s.losal and s.hisal;

-- 7.查询研发部的平均工资
select avg(e.salary) from emp e, dept d where d.name='研发部'and e.dept_id=d.id ;

-- 8.查询工资比灭绝高的员工信息
select e.salary from emp e where e.name='灭绝';
select a.* from emp a where a.salary>(select e.salary from emp e where e.name='灭绝');

-- 9.查询比平均薪资高的员工信息
select avg(e.salary) from emp e;
select * from emp a where a.salary>(select avg(e.salary) from emp e);

-- 10.查询低于1部门平均工资的员工信息
select avg(e.salary) from emp e where e.dept_id=1;
select * from emp e where e.dept_id=1 and e.salary<(select avg(e.salary) from emp e where e.dept_id=1);

-- 11. 查询所有的部门信息，并统计部门的员工人数
-- a.查询所有部门信息
select d.id,d.name,d.id '人数'from dept d;
-- b.统计某一部门的员工人数
select count(*) from emp e where e.dept_id=1;
select d.id,d.name,(select count(*) from emp e where e.dept_id=d.id) '人数'from dept d;

小结：

事务

事务简介

事务是一组操作的集合，它是不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

事务操作

-- ------------------事务
-- 更新数据
update account set money=2000 where name='李四'or name='王五';

-- 转账操作
select @@autocommit;

set @@autocommit=0;-- 手动操作

-- 1.查询李四余额
select * from account where name='李四';

-- 2.将李四余额-1000
update account set money=money-1000 where name ='李四';

-- 3.将王五余额+1000
update account set money=money+1000 where name ='王五';

-- 提交事务  程序执行成功执行
commit ;
-- 回滚事务 程序执行失败
rollback ;

-- 方式二
-- 开启事务
start transaction ;

-- 1.查询李四余额
select * from account where name='李四';

-- 2.将李四余额-1000
update account set money=money-1000 where name ='李四';
程序报错。。
-- 3.将王五余额+1000
update account set money=money+1000 where name ='王五';

-- 提交事务
commit ;
-- 回滚事务
rollback ;