数据库基础

可爱0133

已于 2023-09-13 15:18:27 修改

阅读量163

点赞数

文章标签：数据库

于 2023-09-13 10:09:59 首次发布

本文链接：https://blog.csdn.net/2301_79423016/article/details/132844690

版权

本文详细介绍了数据库基础知识，包括事务的概念、特性、隔离级别、SQL操作（DML、DQL、DDL、DCL）、函数、约束、多表查询、并发问题以及MySQL相关知识，同时涉及Java开发所需技能。

摘要由CSDN通过智能技术生成

数据基础学习

数据库的面试提问

面试题：

什么是事务，以及事务的四大特性？

事务的隔离级别有哪些，MySQL默认是哪个？

内连接与左外连接的区别是什么？

常用的存储引擎？innoDB与MyISAM的区别？

MySQL默认innoDB引擎的索引是什么数据结构？

如何查看MySQL的执行计划？

索引失效的情况有哪些？

什么是回表查询？

什么是MVCC？

MySQL主从复制的原理是什么？

主从复制之后的读写分离如何实现？

数据库的分表如何实现？

Java开发所需要的技能

java Mysql 数据库 sql java script jQuery
Spring cloud 微服务架构
Spring. SpringMVC springboot kafka

怎么学--制定计划

分别分为三个篇章

图形化界面工具

MySQL图形化界面：

Sqlyog Navicat DataGrip

基础篇

Mysql 的基础操作

六个小结

Mysql概述
Sql
函数
约束
多表查询
事务

一、概念-数据模型

通过sql来操作数据库管理系统从而操作数据库当中的数据

关系型数据库的概念

关系型数据库（RDBMS）

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

特点：

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

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

数据模型：

数据库：在一个数据库服务器当中可以创建多个数据库的；在一个数据库当中又可以创建多个表
表

二、SQL

SQL通用语法
SQL分类
DDL
DML
DQL
DCL

1.SQL的通用语语法

1）SQL语句可以单行或多行书写，以分号结尾

2）SQL语句可以使用空格/缩进来增强语句的可读性

3）MySQL 数据库的SQL语句不区分大小写，关键字建议使用大写

4）注释：

单行注释：-- 注释内容或 # 注释内容（MySQL特有）
多行注释：/ *注释内容* /

2.SQL分类

3.SQL-DDL数据操作

1）DDL-表操作-创建&查询

查询当前数据库所有表

SHOW TABLES;

查询表结果

DESC 表名；

查询指定表的建表语句

SHOW CREATE TABLE 表名；

例子：

2)DDL-数据类型和案例

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

2.1)数值类型

Age INT或（TINYINT）UNSIGNED

Soure double(4,1)

2.2)字符串类型

Name Varchar(50)

Gender Char(1)

2.3)日期时间类型

Birthday date

2.4)案例

案例：

设计一张员工信息表：

Creat table emp(

id int comment’编号’，

workno varchar(10) comment’员工编号’，

Name varchar(10) comment’员工姓名’，

Gender char(1) comment‘性别’，

Age tinyInt unsigned comment’年龄’，

idcard varchar（18) comment’身份证号码’，

enterdate Date comment’入职时间’

）comment’员工信息表’；

3)DDL-表操作-修改&删除

3.1）增加

例子：

ALTER TABLE emp add nickname varchar(20) comment’昵称’;

3.2）修改

例子：

Alter table emp change nickname username varchar(30) comment’用户名’；

3.3）删除

例子：

Alter table emp drop username;

3.4）修改表名

例子：

Alter table emp rename to employee;

3.5）删除表

4)DDL-小结

4.SQL-DML

1)SQL-DML-插入

（添加数据）

2)DML- 数据修改

没有携带where条件则修改所有数据

3)DML-数据删除

没有携带where条件则删除所有数据

5. SQL-DQL

查询语法：

1)1)DQL-基础查询

例子：

1.---查询指定字段 name,workno,age,返回

Select name, workno, age form emp;

2.---查询所有字段返回

select * form emp;

3.---查询所有员工的地址，起别名

select workadd as ‘工作地址’ form emp;

4.---查询公司员工上班的地址（不要重复）

select woraddress distinct form emp;

2)DQL-条件查询

条件

例子：

---条件查询

---1.查询年龄等于88的员工

Select * form emp where age = 88;

---2查询年龄小于20的员工信息

Select * form emp where age < 20;

---3查询年龄小于等于20的员工信息

Select * form emp where age <= 20 ;

---4查询没有身份证号的员工信息

Select * form emp where idcard is null;

---5查询有身份证号的员工信息

Select * form emp where idcard is not null;

---6查询年龄不等于88的员工信息

Select * foem emp where age != 88;

---7查询年龄在15岁（包含）到20岁（包含）之间的员工信息

Select * form emp where age between 15 and 20 ;

Select * form emp where age >=15 && age <=20 ;

Select * form emp where age >=15 and age <=20 ;

---8查询性别为女且年龄小于25岁的员工信息

Select * form emp where age=’女’ && age<25;

---9查询年龄等于18或20或40的员工信息

Select * form emp where age =18||age=20||age=40;

Select * form emp where age =18 or age=20 or age=40;

Select * form emp where age in(18,20,40);

--10查询姓名为两个字的员工信息（用LIKE占位符查询 _ %）

Select * form emp where name like ‘_ _’;

--11查询身份证号最后一位是X的员工信息（用LIKE占位符查询 _ %）

Select * form emp where idcard like ‘%X’;

3)DQL-聚合函数

例子：

--聚合函数

--1统计该企业员工数量

Select count (*) form emp;

--2统计该企业员工的平均年龄

Select avg(age) form emp;

--统计该企业员工的最大年龄

Select max(age) form emp;

--统计该企业员工的最小年龄

Select min(age) form emp;

--统西安地区员工的年龄之和

Select sum(age) form emp where workadress = ’西安’;

4）DQL-分组查询

例子：

--分组查询

--1.根据性别分组，统计男性员工和女性员工的数量

Select gender, count(*) form emp group by gender；

--2.根据性别分组，统计男性员工和女性员工的平均年龄

Select gender, avg(age) form emp group by gender；

--3.查新年龄小于45 的员工，并根据工作地址分组，获取员工数量大于等于3 的工作地址

Select address,count(*) form emp where age<45 group by workaddress having count(*)>=3;

5）DQL-排序查询

例子：

--排序查询

--1.根据年龄对公司员工进行升序排序

Select * form emp order by age asc;

--2.根据入职时间，对员工进行降序排序

Select * form emp order by entrydate desc;

--3.根据年龄对公司的员工进行升序排序，年龄相同，在根据入职时间进行降序排序

Select * form emp order by age asc,entrydate desc;

6)DQL-分页查询

例子：

--分页查询

--1.查询第一页员工数据，每页显示十条记录

Select * form emp limit 0,10;

--2.查询第二页员工数据，每页显示十条记录

Select * form emp limit 10, 10;

7)DQL-案例练习

1.查询年龄为20，21，22，23岁的员工信息

Select * form emp where gender=’女’ and age in(20,21,22,23);

2.查询性别为男，并且年龄在20-40岁（含）以内的姓名为三个字的员工

Select * form emp where gender=’男’ and( age between 20 and 40) and name like’_ _ _’;

3.统计员工表中，年龄小于60岁的，男性员工和女性员工的人数

Select gender ,count (*) form emp where age<60 group by gender ;

4.查询所有小于与等于35岁员工的姓名和年龄，并且对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序

Select name, age form emp where age<= 35 order by age asc ,entrydate desc ;

5.查询性别为男，且年龄在20-40岁（含）以内的前5 个员工信息，对查询结果进行按年龄升序排序，年龄相同按入职时间升序排序

Select * form emp where gender=’男’ and (age between 20 and 40) order by age asc,entrydate desc limit 0,5;

8)DQL-执行顺序

6.SQL-DCL

1)DCL-用户管理

---例子：

---1.创建用户itcast,只能在当前主机localhost访问，密码123456；

Create user ‘itcast’ @ ’localhost’ identified by ‘123456’;

---2.创建用户heima，可以在任意主机访问该数据库，密码123456；

Create user ‘heima’ @ ’%’ identified by ‘123456’;

---3.修改用户heima的访问密码1234；

Alter user ‘heima’ @’%’ identified with mysql_native_password by’1234’;

---4.删除itcast@localhost用户；

Drop user ‘itcast’@’localhost’

2)DCL-权限控制

3）DCL-小结

三、函数

函数是指一段可以直接被另一段程序调用的程序或代码

函数：

字符串函数
数值函数
日期函数
流程函数

1.字符串函数

例子：

--Lpad

--rpad

---trim

--substring

小案例：

--1.由于业务需改变更，企业员工的工号，统一为五位数，目前不足5位数的全部在前面补0，例如：1号员工的工号为00001

Update emp set workno = lpad (workno, 5 , ’0’);

2.数值函数

例子：

--ceil

Select ceil(1.3);

--floor

Select floor(1.7);

--mod(取余的意思）

Select mod(4,3 );

--rand(去0-1之间的随机数)

Select rand();

0.54524854545

--round

Select round(2.345,2)

2.35

练习：

通过数据库的函数，生成一个六位的随机数验证码

Select rpad(round(rand()*1000000,0) , 6, ’0’)；

3.日期函数

例子：

查询所有员工的入职天数，并根据入职天数倒序排序

Select name,datediff(curdate(),entrydate) as ‘entrydays’ form emp order by entrydays desc;

4.流程函数

例子：

5.小练习

统计班级各个学员的成绩展示规则如下：

--->=85,展示优秀

--->=60,展示及格

---否则展示不及格

基本环境

6.小结

四、约束

1.约束概念

2.约束演示

创建表：

Creat table user(

Id int primary key auto_increment comment’主键’,

Name varchar(10) not null unique comment’姓名’,

Age int check（age >0 &&age<120） comment’年龄’,

Status char(1) default ‘1’comment’状态’,

Gender char(1) comment’性别’

)comment’用户表’；

3.外键约束

准备数据

添加外键

4.外键删除更新行为

No action和restrict是默认的参数

语法：

5.小结

1.非空约束：NOT NULL

2.唯一约束：UNIQUE

3.主键约束：PRIMARY KEY(自增：AUTO_INCREMENT)

4.默认约束：DEFAULT

5.检查约束：CHECK

6.外键约束：FOREIGN KEY

五、多表查询

多表关系
多表查询概述
内连接
外连接
自连接
子查询
多表查询案例

1.多表关系的介绍

概述：

一对多

多对多

一对一

2.多表查询概述

笛卡尔积

在多表查询时，要消除无效的笛卡尔积

使用where筛选多表字段相等的条件

3.多表查询分类

4.多表查询-内连接

例子：

5.多表查询-外连接

例子：

6.多表查询-自连接

例子：

查询员工及其所属领导的名字

效果：

7.多表查询-联合查询--union,union all

例子：

8.多表查询-子查询

1）子查询-介绍-分类

2）标量子查询

例子：

1.查询销售部所有员工信息

2.查询在方东白入职之后的员工信息

3）列子查询

例子：

列子查询

--1.查询‘销售部’和 ‘市场部’所有员工信息

A.查询销售部和市场部的id

Select id form dept where name = ‘销售部’or name = ‘市场部’;

B.查询id为销售部和市场部的所有员工信息

Select * form emp where dept_id in （Select id form dept where name = ‘销售部’or name = ‘市场部’;

）；

--2.查询比财务部所以有人工资都高的员工信息

A.查询财务部工资最高工资

1，Select id form dept where name=’财务部’；

2.select max(salary) form emp where dept_id =(Select id form dept where name=’财务部’);

3.select * form emp where salary >(select max(salary) form emp where dept_id =(Select id form dept where name=’财务部’));

或

select * form emp where salary > all (select salary form emp where dept_id =(Select id form dept where name=’财务部’));

--3.查询比研发部其中任意一人工资高的员工

A .Select id form dept wherre name=’研发部’;

B.select salary form emp where deptid =(Select id form dept wherre name=’研发部’);

C.select * form emp where salary > any (select salary form emp where deptid =(Select id form dept wherre name=’研发部’));

4)行子查询

例子：

--行子查询

--1.查询与张无忌的薪资及直属领导相同的员工信息；

A .select salary, managerid form emp where name=’张无忌’；

B .select * form emp where (salary, managerid) = (select salary, managerid form emp where name=’张无忌’);

5)表子查询

例子：

--表子查询

--1.查询与‘鹿仗客’，‘宋远桥’的职位和薪资相同的员工信息

Select salary ,job form emp where name =’鹿仗客’ or name =’宋远桥’;

Select * form emp where ( salary ,job ) in (Select salary ,job form emp where name =’鹿仗客’ or name =’宋远桥’);

--2.查询入职时间是2006-01-01之后的员工信息，及其部门信息

Select name form emp where entrydate >’2006-01-01’;

Select emp.* ,dept.* form (Select name form emp where entrydate >’2006-01-01’) e left join dept d on e.dept_id =d.id;

6）多表查询-案例练习

练习1

根据需求，完成SOL语句的编写

（1-3内连接，）

1.查询员工的姓名、年龄、职位、部门信息。

Select emp.name, emp.age, emp.jop, dept .name form emp,dept where emp.dept_id =dept.id;

2.查询年龄小于30岁的员工姓名、年龄、职位、部门信息。

Select emp.name ,emp.age, emp.jop ,dept.name form emp join dept on emp.dept_id =dept.id where emp.age< 30;

3.查询拥有员工的部门ID、部门名称。(去重)

Select distinct emp.dept_id,dept.name form emp join dept on emp.dept_id =dept.id;

4.查询所有年龄大于40岁的员工,及其归属的部门名称:如果员工没有分配部门,也需要展示出来。

Select emp.*,dept.name form emp left join dept on emp.dept_id =dept.id where emp.age>40;

5.查询所有员工的工资等级。

Select emp.*,salgrade.gade form emp ,salgrade where emp.salary between salgrade.losal and salgrade.hisal;

6.查询“研发部”所有员工的信息及工资等级。

A .select id form dept where name=’研发部’；

B .Select name form emp where dept_id=(select id form dept where name=’研发部’);

C.Select emp.*,salgrade.grade form emp,salgrade where emp.dept_id = dept.id and (emp.salary between salgrade.losal and salgrade.hisal) and dept.name=’研发部’;

7.查询“研发部”员工的平均工资

A .name=研发部

b .emp.dept_id =dept.id

c .select avg(emp.salary) form emp,dept where dept.name = ‘研发部’ and emp.dept_id=dept.id;

8.查询工资比"灭绝”高的员工信息

A.select salary form emp where name=’灭绝’；

B.select * form emp where salary > (select salary form emp where name=’灭绝’);

9.查询比平均薪资高的员工信息。

Select * form emp where salary > (select avg(salary) form emp);

10.查询低于本部门平均工资的员工信息

Select * form emp e2 where e2.salary < ( select avg(e1.salary) form emp e1 where e1.dept_id= e2.dept_id )

11.查询所有的部门信息,并统计部门的员工人数。

Select dept.*,(select count(*) form emp where emp.dept_id = dept.id ) as ‘员工人数’ form dept；

12.查询所有学生的选课情况,展示出学生名称,学号,课程名称

Select student.name,student.no ,course.name form student ,course,student_course where student.id = student_course.student.id and course.id = student_course.course.id;

数据准备