数据库基础笔记-CSDN博客

本文链接：https://blog.csdn.net/m0_55956064/article/details/126394633

JawaWeb

MySQL

基本语句

操作数据库

1.查询数据库

show database;

2.创建数据库

create database db1;	数据库不能够重复

3.删除数据库

drop database db1;

4.进入数据库

use db1;

select database();-- 查看当前使用的数据库

操作表(DDL)

1.查询表

show tables; 查询当前数据库下所有表名称

desc 表名称; 查询表结构

2.创建表

create table 表名(

字段名1 数据类型1,

字段名2 数据类型2,

…

);

数据类型

char为定长字符串，存储性能高，浪费空间

char(10)表示固定占10个字符空间

varchar为变长字符串，存储性能第，节约空间

varchar(10)表示最长为10个字符

3.修改表

alter table 表名 reame to 新的表名; 修改表名

alter table 表名 add 列名数据类型; 添加一列

alter table 表名 modify 列名新数据类型; 修改数据类型

alter table 表名 change 列名新列名新数据类型; 修改列名和数据类型

alter table 表名 drop 列名; 删除列

4.删除表

drop table 表名;

drop table if exists 表名;

数据操作(DML)

1.添加数据

instert into 表名(列名1，列名2,…) value(值1,值2,…);

2.修改数据

update 表名 set 列名1=值1,列名2=值2…[where 条件];

3.删除数据

delete from 表名 [where 条件];

数据查询(DQL)

1.基础查询

SELECT name,age FROM stu; 查询所有列可以用*替代，不建议使用

SELECT DISTINCT address from stu; distinct关键字可以取出重复记录

SELECT math as 数学 FROM stu; as关键字可以用来起别名

2.条件查询

SELECT * from stu WHERE age>20;

SELECT * from stu WHERE age>20 AND age<=30; 逻辑且用and表示

SELECT * from stu WHERE age =18 OR age=20 OR age=22; 逻辑或用or表示。另一种写法为age in(18,20,22)

SELECT * from stu WHERE age BETWEEN 20 AND 30; 也可用between and表示

SELECT * from stu WHERE age =18; 注意等号写法，不等号写法为!=

SELECT * from stu WHERE english is null; 注意null的判断需要用关键字is或is not

模糊查询

SELECT * FROM stu WHERE name LIKE ‘马%’; 模糊查询使用like关键字，

通配符：_代表单个任意字符，%代表任意个数字符

日期类型

SELECT * from stu WHERE hire_date BETWEEN ‘1998-09-01’ AND ‘1999-09-01’; 注意需要用单引号

3.排序查询

SELECT * FROM stu ORDER BY age ASC; 排序查询使用order by关键字

asc(默认)为升序排列，desc为降序排列全称为ascend descend

多字段排序

SELECT * FROM stu ORDER BY math DESC , english ASC;-- 按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列

4.分组查询

聚合函数

(null不参与聚合函数查询)

count(列名) 统计非空数据，取值推荐用*(只要该行有数据便会统计)或主键；max(列名)；min(列名)；sum(列名)；avg(列名)

SELECT count(id) FROM stu ;

分组查询

SELECT sex,AVG(math),COUNT(*) from stu GROUP BY sex;-- 查询男同学和女同学各自数学的平均分以及对应的人数

SELECT sex,AVG(math),COUNT(*) from stu WHERE math>70 GROUP BY sex HAVING COUNT(*)>2;	-- 查询男女同学各自的平均分以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2

注：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

where和having区别：

执行时机不一样：where时分组之前进行限定，不满足where条件则不参与分组，而having是分组之后对结果进行过滤
可判断的条件不一样：where不能对聚合函数进行判断，having可以

执行顺序：where>聚合函数>having

5.分页查询

SELECT * FROM stu LIMIT 0,3;-- limit 起始索引，查询条数 limit是MySql数据库的“方言”

– 起始索引=(当前页码-1)*每页显示的条数

数据库

约束

约束是作用于表中列上的规则，用于限制加入表的数据，以保证其正确性，有效性以及完整性

– 员工表

CREATE TABLE emp (
  id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0

);

主键约束：非空且唯一

非空约束：非空

唯一约束：唯一

默认约束：默认值；赋值null不属于默认约束

自动增长：当列是数字类型且唯一约束

外键约束

外键约束：外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性

CREATE TABLE dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)

);

-- 创建表时添加外键约束
CREATE TABLE 表名(
	 列名 数据类型,
	 …
	 [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
); 

-- 添加外键 dep_id,关联 dept 表的id主键
alter table emp add CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)

-- 删除外键
alter table emp drop FOREIGN key fk_emp_dept;

先创建主表，再创建从表才能添加外键。先添加主表数据，再添加从表数据

数据库设计

表关系

一对多

在多的一方建立外键，指向一方的主键

多对多

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

-- 订单表
CREATE TABLE tb_order(
	id int primary key auto_increment,
	payment double(10,2),
	payment_type TINYINT,
	status TINYINT
);

-- 商品表
CREATE TABLE tb_goods(
	id int primary key auto_increment,
	title varchar(100),
	price double(10,2)
);

-- 订单商品中间表
CREATE TABLE tb_order_goods(
	id int primary key auto_increment,
	order_id int,
	goods_id int,
	count int
);

-- 建完表后，添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

一对一

一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

实现方式：在任意一方加入外键，关联另一方主键，兵设置外键为唯一

多表查询

连接查询

1.内连接

隐式内连接

select * from emp,dept WHERE emp.dep_id=dept.did;-- 相当于查询A B交集数据

显式内连接

select  字段列表 FROM 表1 [INNER] JOIN表2 ON 条件;

2.外连接

左外连接：相当于查询A表所有数据和交集部分数据（本例中A在左B在右）

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

右外连接：相当于查询B表所有数据和交集部分数据

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

子查询

查询中嵌套查询，称嵌套查询为子查询

单行单列

作为条件值，使用 = 等进行条件判断

多行单列

作为条件值，使用in等关键字进行条件判断

多行多列

作为虚拟表

select * from (select * from emp where join_date > '2011-11-11' ) t1, dept where t1.dep_id = dept.did;

事务

数据库的事务是一种机制，一个操作序列，包含了一组数据库操作命令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败

事务四大特征

原子性(Atomicity)：事务是不可分割的最小操作单位，要么同时成功，要么同时失败

一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态

隔离性(Isolation)：多个事物之间，操作的可见性

持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

-- 开启事务
BEGIN
-- 回滚
ROLLBACK
-- 提交事务
COMMIT

JDBC

JDBC 指 Java 数据库连接，是一种标准Java应用编程接口(JAVA API)

主要步骤

DriverManager

1.注册驱动

2.获取连接

url：连接路径

url="jdbc:mysql://127.0.0.1:3306/db1";

Connection

1.获取执行SQL的对象

普通执行SQL对象；预编译SQL的执行SQL对象：防止SQL注入；执行存储过程的对象

2.管理事务

开启事务

conn.setAutoCommit(false);

提交事务

conn.commit();

回滚事务

conn.rollback();

Statemen

作用：执行SQL语句

executeUpdate:执行DML，DDL语句

executeQuery：执行DDL语句

ResultSet

ResultSet：封装了DQL查询语句的结果

next()：将光标从当前位置向前移动一行并判断当前行是否为有效行

getXXX(参数)：获取数据如：int getint(参数);

参数：int：列的编号，注意是从1开始

string：列的名称

PreparedStatement

PreparedStatement作用：

1.预编译SQL并执行SQL语句

//1.获取PreparedStatement对象
String sql="SELECT * FROM account WHERE name=? and password=?";  //参数值用占位符？替代。
//通过Connection对象获取，并传入sql语句
PreparedStatement pstmt = conn.prepareStatement(sql);

//2.设置参数值
pstmt.setString(1,name); //setXXX（索引，参数值），索引从1开始
//3.执行SQL
ResultSet rs = pstmt.executeQuery();  //不再传递sql

druid配置详解

属性	说明	建议值
url	数据库的jdbc连接地址。一般为连接oracle/mysql。示例如下：
	mysql : jdbc:mysql://ip:port/dbname?option1&option2&…
	oracle : jdbc:oracle:thin:@ip:port:oracle_sid

username	登录数据库的用户名
password	登录数据库的用户密码
initialSize	启动程序时，在连接池中初始化多少个连接	10-50已足够
maxActive	连接池中最多支持多少个活动会话
maxWait	程序向连接池中请求连接时,超过maxWait的值后，认为本次请求失败，即连接池	100
	没有可用连接，单位毫秒，设置-1时表示无限等待
minEvictableIdleTimeMillis	池中某个连接的空闲时长达到 N 毫秒后, 连接池在下次检查空闲连接时，将	见说明部分
	回收该连接,要小于防火墙超时设置
	net.netfilter.nf_conntrack_tcp_timeout_established的设置
timeBetweenEvictionRunsMillis	检查空闲连接的频率，单位毫秒, 非正整数时表示不进行检查
keepAlive	程序没有close连接且空闲时长超过 minEvictableIdleTimeMillis,则会执	true
	行validationQuery指定的SQL,以保证该程序连接不会池kill掉,其范围不超
	过minIdle指定的连接个数。
minIdle	回收空闲连接时，将保证至少有minIdle个连接.	与initialSize相同
removeAbandoned	要求程序从池中get到连接后, N 秒后必须close,否则druid 会强制回收该	false,当发现程序有未
	连接,不管该连接中是活动还是空闲, 以防止进程不会进行close而霸占连接。	正常close连接时设置为true
removeAbandonedTimeout	设置druid 强制回收连接的时限，当程序从池中get到连接开始算起，超过此	应大于业务运行最长时间
	值后，druid将强制回收该连接，单位秒。
logAbandoned	当druid强制回收连接后，是否将stack trace 记录到日志中	true
testWhileIdle	当程序请求连接，池在分配连接时，是否先检查该连接是否有效。(高效)	true
validationQuery	检查池中的连接是否仍可用的 SQL 语句,drui会连接到数据库执行该SQL, 如果
	正常返回，则表示连接可用，否则表示连接不可用
testOnBorrow	程序申请连接时,进行连接有效性检查（低效，影响性能）	false
testOnReturn	程序返还连接时,进行连接有效性检查（低效，影响性能）	false
poolPreparedStatements	缓存通过以下两个方法发起的SQL:	true
	public PreparedStatement prepareStatement(String sql)
	public PreparedStatement prepareStatement(String sql,
	int resultSetType, int resultSetConcurrency)
maxPoolPrepareStatementPerConnectionSize	每个连接最多缓存多少个SQL	20
filters	这里配置的是插件,常用的插件有:	stat,wall,slf4j
	监控统计: filter:stat
	日志监控: filter:log4j 或者 slf4j
	防御SQL注入: filter:wall
connectProperties	连接属性。比如设置一些连接池统计方面的配置。
	druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
	比如设置一些数据库连接属性: