数据库知识点总结

绝不当程序猿

已于 2022-08-27 16:22:53 修改

阅读量418

点赞数 1

文章标签： java 数据库 sql mysql

于 2022-08-27 16:17:29 首次发布

本文链接：https://blog.csdn.net/weixin_43861382/article/details/126558226

版权

SQL

Structured Query Language 结构化查询语言, 此语言是用于程序员和数据库软件进行交流的语言

DBMS

DataBaseManagementSystem数据库管理系统(俗称数据库软件)

常见的DBMS包括:

MySQL/MariaDB: 开源数据库, 是Oracle公司的产品, 08年被Sun公司收购,09年Sun公司被Oracle收购, 市占率排名第一

Oracle:是Oracle公司的产品, 闭源数据库, 性能最高价格最贵, 市占率排名第二

SQLServer: 微软公司产品, 闭源数据库

DB2: IBM公司产品, 闭源数据库

SQLite: 轻量级数据库,安装包几十k

如何连接数据库软件

连接数据库软件的几种方式:

通过命令行连接本机上的数据库软件

通过三方的可视化软件连接数据库软件

通过Java代码连接数据库软件

通过命令行连接数据库

从开始菜单中找到MariaDB或MySQL文件夹然后找到里面的MySQL Client 打开后输入密码然后回车显示 Welcome....说明连接成功

如果是Linux或mac OS 打开终端输入: mysql -uroot -p 回车后再输入密码回车

退出连接指令: exit 回车

数据库中如何保存数据

要想在数据库软件中保存数据需要先建库再建表

数据库相关的SQL语句

1.查询所有数据库

格式: show databases;

2.创建数据库

默认字符集格式: create database 数据库名;

指定字符集格式: create database 数据库名 charset=utf8/gbk;

举例:

create database db1;

create database db2 charset=utf8;

create database db3 charset=gbk;

show databases;

3.查询数据库信息

格式: show create database 数据库名;

举例:

show create database db1;

show create database db2;

show create database db3;

4.删除数据库

格式: drop database 数据库名;

举例:

drop database db3;

drop database db2;

show databases;

5.使用数据库

对表和数据操作之前必须使用了某一个数据库否则会报错

格式: use 数据库名;

举例:

use db1;

表相关的SQL语句

执行表相关的SQL语句之前必须使用了某个数据库否则会报以下错误

use db1;

1.创建表

格式: create table 表名(字段1名类型, 字段2名类型,.......) charset=utf8/gbk;

举例:

create table person(name varchar(50), age int);

create table student(name varchar(30),chineses int,

english int )charset=utf8;

create table car(name varchar(20),type varchar(20))charset=gbk;

2.查询所有表

格式: show tables;

查看表信息

格式: show create table 表名;

举例:

show create table person;

show create table student;

show create table car;

3.查看表字段

格式:desc 表名;

举例:

desc person;

desc student;

4.删除表

格式: drop table 表名;

举例:

drop table student;

show tables;

5.修改表名

格式: rename table 原名 to 新名;

举例:

rename table person to p;

rename table car to c;

6.添加表字段

最后面添加格式: alter table 表名 add 字段名类型;

最前面添加格式: alter table 表名 add 字段名类型 first;

在xxx字段后面添加: alter table 表名 add 字段名类型 after xxx;

举例:

create table hero(name varchar(20));

alter table hero add age int;

alter table hero add id int first;

alter table hero add gender varchar(2) after name;

7.删除表字段

格式: alter table 表名 drop 字段名;

举例:

alter table hero drop age;

8.修改表字段

格式: alter table 表名 change 原名新名新类型;
举例:

alter table hero change gender age int;

数据相关SQL

执行数据相关的SQL必须使用了某个数据库并且在这个数据库里面创建了某张表,才能往这个表里面添加数据

create database newdb1 charset=utf8;

use newdb1;

create table person(name varchar(10),age int)charset=utf8;

1.插入数据(增)

全表插入格式: insert into 表名 values(值1,值2,......);

指定字段插入格式: insert into 表名(字段1名,字段2名) values(值1,值2);

批量插入:在values后面写多组值通过逗号分隔

举例:

insert into person values("张三",30);

insert into person(name) values('李四');

insert into person values("刘备",50),("关羽",40),("张飞",30);

insert into person(name) values('诸葛亮'),('司马懿');

2.查询数据

格式: select 字段信息 from 表名 where 条件;

举例:

select name from person;

select name,age from person;

select * from person;

select name from person where age>30;

select * from person where name="刘备";

3.修改数据

格式: update 表名 set 字段名=值,字段名=值 where 条件;

举例:

update person set age=88 where name="诸葛亮";

update person set name="孙尚香" where name="刘备";

update person set name="关二爷",age=100 where name="关羽";

4.删除数据

格式: delete from 表名 where 条件;

举例:

delete from person where name="张三";

delete from person where age is null;

delete from person where age>50;

delete from person;

数据类型

整数: int(m)和bigint(m) , m代表显示长度 m=5 存18 查询到的是00018 , 是用来补零的不是代表存储数据的长度, 而且需要和zerofill关键字结合使用

create database newdb2 charset=utf8;

use newdb2;

create table t1(age int(5) zerofill);

insert into t1 values(18);

select * from t1;

浮点数:double(m,d) m代表总长度,d代表小数长度 58.123 m=5 d=3

create table t2(price double(5,3));

insert into t2 values(56.231);

insert into t2 values(23.456789); //不报错四舍五入

insert into t2 values(123.456789); //报错

字符串

char(m): 固定长度 m=5 存"abc" 占5, 在长度固定时执行效率略高, 最大值255

varchar(m): 可变长度, m=5 存"abc" 占3, 更节省空间, 最大值65535 ,建议保存255以内的数据

text(m): 可变长度, 最大值65535, 建议保存长度较大的数据时使用

时间和日期相关

date: 只能保存年月日

time: 只能保存时分秒

datetime: 保存年月日时分秒, 默认值为null, 最大值9999-12-31

timestamp:时间戳(指距离1970年1月1日的毫秒数),保存年月日时分秒, 默认值为当前系统时间(极个别的同学会出现默认值为null), 最大值2038-1-19

举例:

create table t3(t1 date,t2 time,t3 datetime,t4 timestamp);

insert into t3 values("2022-10-11",null,null,null);

select * from t3;

insert into t3 values(null,"16:20:30","2011-10-22 11:22:33",null);

select * from t3;

主键约束 primary key

约束: 创建表时给表字段添加的限制条件

主键: 表示数据唯一性的字段称为主键

主键约束: 限制主键字段的值唯一且非空

举例:

create table t4(id int primary key,name varchar(20));

insert into t4 values(1,'aaa');

insert into t4 values(2,'bbb');

insert into t4 values(1,'ccc'); //报错 提示以下信息表示数据重复

 insert into t4 values(null,'ddd'); //报错 提示不能为null

自增 auto_increment

自增规则: 从历史最大值+1

create table t5(id int primary key auto_increment,name varchar(20));

insert into t5 values(null,'aaa');

insert into t5 values(null,'bbb');

insert into t5 values(10,'ccc');

insert into t5 values(null,'ddd');

delete from t5 where id>=10;

insert into t5 values(null,'eee');

导入*.sql 批处理文件

此文件是通过三方工具从MySQL数据库里面导出的数据文件

通过在命令行中执行以下指令将此文件的数据导入到自己数据库软件中

source 路径;

source d:/emp.sql;

比较运算符 > < >= <= = !=和<>

1.查询工资小于等于3000的员工姓名和工资

select name,sal from emp where sal<=3000;

2.查询程序员的名字

select name from emp where job='程序员';

3.查询2号部门的员工姓名,工资和工作

select name,sal,job from emp where dept_id=2;

4.查询不是人事的员工姓名和工作(两种写法)

select name,job from emp where job!='人事';

select name,job from emp where job<>'人事';

and,or,not

and:当查询多个条件同时满足时使用

or:当多个条件只需要满足某一个时使用

not 取反

1.查询1号部门工资高于2000的员工信息

select * from emp where dept_id=1 and sal>2000;

between x and y 两者之间包含x和y

1.查询工资在2000到3000之间的员工信息

select * from emp where sal>=2000 and sal<=3000;

select * from emp where sal between 2000 and 3000;

2.查询工资在2000到3000以外的员工信息

select * from emp where sal not between 2000 and 3000;

in(x,y,z)

当查询某个字段的值为多个值的时候使用in关键字

1.查询工资等于1500,3000和5000的员工信息

select * from emp where sal=1500 or sal=3000 or sal=5000;

select * from emp where sal in(1500,3000,5000);

2.查询工作不是销售和程序员的信息

select * from emp where job not in('销售','程序员');

去重distinct

1.查询员工表中出现了哪几种不同的工作

select distinct job from emp;

2.查询员工表中有哪几种不同的部门id

select distinct dept_id from emp;

模糊查询like

%: 代表0或多个未知字符

_: 代表1个未知字符

举例:

x开头 x%

x结尾 %x

包含x %x%

第二个字符是x _x%

x开头y结尾 x%y

第二个是x倒数第三个是y _x%y__

1.查询名字姓孙的员工姓名

select name from emp where name like "孙%";

查询名字中包含僧的员工信息

查询名字以精结尾的员工姓名

查询工作中包含销售并且工资大于1500的员工信息

2.查询1号和2号部门中工作以市开头的员工信息

select * from emp where dept_id in(1,2) and job like "市%";

排序 order by

格式: order by 排序的字段名 asc升序(默认)/desc降序;

1.查询每个员工姓名和工资,按照工资升序排序

select name, sal from emp order by sal;

select name, sal from emp order by sal asc;

2.查询每个员工的姓名,工资和部门id 按照部门id升序排序,如果部门id一致则按照工资降序排序

select name,sal,dept_id from emp order by dept_id,sal desc;

分页查询limit

格式: limit 跳过的条数,请求的条数(每页的条数)

跳过的条数=(请求的页数-1)*每页的条数

举例:

第1页的5条数据 limit 0,5

第2页的5条数据 limit 5,5

第5页的10条数据 limit 40 ,10

第10页的10条数 limit 90,10

第8页的6条数据 limit 42,6

1.按照工资升序排序查询第3页的2条数据

select * from emp order by sal limit 4,2;

别名

select name as "名字" from emp;

select name "名字" from emp;

select name 名字 from emp;

聚合函数

将查询到的多条数据进行统计查询

统计方式包括:

求平均值

最大值

最小值

求和

计数

平均值:avg(字段名)

查询1号部门的平均工资

select avg(sal) from emp where dept_id=1;

最大值:max(字段名)

查询1号部门的最高工资

select max(sal) from emp where dept_id=1;

最小值:min(字段名)

查询1号部门的最低工资

select min(sal) from emp where dept_id=1;

求和: sum(字段名)

查询1号部门的工资总和

select sum(sal) from emp where dept_id=1;

计数: count(*)

查询程序员的数量

select count(*) from emp where job='程序员';

分组查询group by

分组查询可以将某个字段相同值的数据划分为一组,然后以组为单位进行统计

1.查询查询每个部门的平均工资

select dept_id,avg(sal) from emp group by dept_id;

2.查询平均工资最高的部门id和平均工资

select dept_id,avg(sal) from emp group by dept_id

order by avg(sal) desc limit 0,1;

通过别名将重复出现的内容复用

select dept_id,avg(sal) a from emp group by dept_id

order by a desc limit 0,1;

having

where后面只能写普通字段条件,不能写聚合函数条件

having后面是专门用来写聚合函数条件, having要和分组查询结合使用不要单独使用,having写在group by的后面

1.查询每个部门的平均工资,只查询出平均工资大于2000

select dept_id,avg(sal) from emp

group by dept_id having avg(sal)>2000 ;

2.查询每种工作的人数,只查询人数大于1的

select job,count(*) c from emp group by job having c>1;

3.查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400.

select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s>5400;

4.查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的

select dept_id,avg(sal) a from emp where sal between 1000 and 3000 group by dept_id having a>=2000;

子查询

1.查询工资高于2号部门平均工资的员工信息

select avg(sal) from emp where dept_id=2;

select * from emp where sal>(select avg(sal) from emp where dept_id=2

2.查询拿最低工资员工的同事们的信息(同事指同一部门)

select min(sal) from emp;

select dept_id from emp where sal=(select min(sal) from emp);



select * from emp where dept_id=(select dept_id from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);

数值计算+ - * / %

1.查询每个员工的姓名,工资和年终奖(5个月的工资)

select name,sal,5*sal 年终奖 from emp;

2.给每个3号部门的员工涨薪5块钱

update emp set sal=sal+5 where dept_id=3;

关联关系

指创建表时,表和表之间存在的业务关系.

包含以下几种关系:

一对一: 有AB两张表, A表中的一条数据对应B表中的一条数据,同时B表中的一条数据也是对应A表中的一条数据

一对多:有AB两张表, A表中的一条数据对应B表中的多条数据,同时B表中的一条数据对应A表中的一条数据

多对多:有AB两张表, A表中的一条数据对应B表中的多条数据,同时B表中的一条数据也对应A表中的多条数据

如果两张表之间存在业务关系,如何建立关系?

一对一: 在两张表中的任意一张表里面添加建立关系的字段,指向另外一张表的主键

一对多: 一对多的两张表, 在"多"的表里面添加建立关系的字段,指向另外一张表的主键

多对多: 需要创建一个单独的关系表,表里面至少两个字段指向另外两张表的主键 .

关联查询

同时查询存在管理关系的多张表的数据时使用的查询方式

包含三种查询方式:

等值连接

内连接

外连接

关联查询之等值连接

格式: select * from A,B where A.x=B.x

1.查询每个员工的姓名和对应的部门名

select e.name,d.name

from emp e,dept d where e.dept_id=d.id;

2.查询工资高于2000的员工姓名,工资和对应的部门名和地址

select e.name,sal,d.name,loc

from emp e,dept d where e.dept_id=d.id and sal>2000;

关联查询之内连接

等值连接和内连接查询到的数据是一样的都是两个表的交集数据,只是书写格式不一样

格式: select * from A join B on A.x=B.x

1.查询每个员工的姓名和对应的部门名

select e.name,d.name

from emp e join dept d on e.dept_id=d.id;

2.查询工资高于2000的员工姓名,工资和对应的部门名和地址

select e.name,sal,d.name,loc

from emp e join dept d on e.dept_id=d.id where sal>2000;

关联查询之外连接

外连接查询到的是一张表的全部和另外一张表的交集

格式: select * from A left/right join B on A.x=B.x

1.查询所有员工姓名和对应的部门信息

select e.name,d.*

from emp e left join dept d on e.dept_id=d.id;

2.查询所有部门名,部门地址和对应的员工姓名

select d.name,loc,e.name

from emp e right join dept d on e.dept_id=d.id;

关联查询总结:

如果查询的数据是来自多张表,则需要使用关联查询

如果查询的是两个表的交集数据则使用等值连接或内连接(推荐)

如果查询的是一张表的全部和另外一张表的交集则使用外连接

JDBC

作用: 通过Java代码执行SQL语句

JavaDataBaseConnectivity: Java数据库链接,

JDBC是Sun公司提供的一套用于Java语言和数据库软件进行连接的API(Application Programma Interface应用程序编程接口)

Sun公司为了避免Java程序员每一种数据库软件都学习一套新的方法, 通过JDBC接口将方法名定义好, 各个数据库厂商根据JDBC里面的方法名写各自的实现类(jar包) , 这样Java程序员只需要掌握JDBC接口中方法的调用,将来就算是换了数据库软件, 代码是不需要改变的,这样的话就降低了Java程序员的学习成本

如何通过JDBC和数据库软件进行连接并执行SQL语句

1.创建maven工程

2.在工程的pom.xml文件中添加以下内容然后刷新maven

<!--依赖标签-->

<dependencies>

<!-- 连接MySQL数据库的依赖 -->

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<version>8.0.15</version>

</dependency>

</dependencies>

3.检查是否出现了和mysql相关的jar包文件

4.如果没有出现 ,检查pom.xml里面是否书写正确, 如果确定书写没问题,检查maven配置是否正确, 如果正确则删除.m2文件夹下的repository文件夹然后回到工程中再次刷新maven

5.在main文件夹中添加cn.tedu.Demo01.java文件

6.添加main方法, 并在里面添加如下代码:

Java

//1.获取数据库链接对象 异常抛出

Connection conn = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false",

"root","root");

System.out.println("链接对象:"+conn);

//2.创建执行SQL语句的对象

Statement s = conn.createStatement();

//3.执行SQL语句

s.execute("create table jdbct1(id int)");

//4.关闭资源

conn.close();

System.out.println("执行完成!");

SQL语句分类

DDL:数据定义语言,包含数据库相关和表相关的SQL语句

DML:数据操作语言,包括增删改查

DQL:数据查询语言,只包含查询

TCL:事务控制语言, 包含和事务相关

DCL:数据控制语言, 包含和用户管理和权限分配相关的SQL语句

Statement执行SQL语句的对象

execute("sql"); 此方法可以执行任意SQL语句,推荐执行数据库相关和表相关的SQL语句(DDL数据定义语言)

int row = executeUpdate("sql"); 此方法执行增删改相关SQL语句,返回值表示生效的行数

ResultSet rs = executeQuery("sql"); 此方法执行查询相关SQL语句,返回值里面装着查询回来的数据

数据库连接池DBCP

DataBaseConnectionPool: 数据库连接池

作用: 将连接重用,从而提高执行效率

如何使用连接池?

在pom.xml文件中再添加一个和连接池相关的依赖

相关代码:

Plain Text

//创建连接池对象

DruidDataSource dds = new DruidDataSource();

//设置连接数据库的信息

dds.setUsername("root");

dds.setPassword("root");

dds.setUrl("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false");

//设置初始连接数量 和 最大连接数量

dds.setInitialSize(3);

dds.setMaxActive(5);

//从连接池中获取连接 异常抛出

Connection conn = dds.getConnection();

System.out.println("连接对象:"+conn);

SQL注入

以SQL语句select count(*) from user where username='tom' and password='asdf'举例,用户传参两个，分别是username和password