数据库
学习数据库主要学习的是如何对数据进行增删改查操作.
SQL
Structured Query Language: 结构化查询语言, 通过此语言让程序员和数据库软件进行交流
DBMS
DataBaseManagementSystem: 数据库管理系统(数据库软件)
常见的几种DBMS:
MySQL: Oracle公司产品, 08年被Sun公司收购, 09年Sun公司被Oracle收购. 开源产品 , MaraDB实际上就是MySQL的一个分支使用方式和MySQL一样. 市占率排名第一
Oracle: Oracle公司产品, 闭源产品 ,性能最强 价格最贵, 市占率排名第二
SQLServer: 微软公司产品, 闭源产品 , 市占率第三
DB2: IBM公司产品
SQLite: 轻量级数据库, 安装包几十K ,只具备最基础的增删改查功能.
如何连接数据库执行SQL语句
执行SQL语句需要先和数据库软件建立链接之后
1.从开始菜单中找到MariaDB或MySQL,然后打开找到里面的MySQL Client 打开 , 然后输入
密码后回车
退出指令: exit
登录指令: mysql -uroot -p
数据库和表的概念
在MySQL数据库软件中保存数据,需要先建库,然后在库里面建表,然后把数据保存到表中
SQL语句格式:
- 以;号结尾
- 关键字不区分大小写
- 可以有空格或换行但一定要以;结尾
数据库相关的SQL语句
- 查询所有数据库
格式: show databases; - 创建数据库
格式: create database 数据库名 charset=utf8/gbk;
举例:
create database db1;
create database db2 charset=utf8;
create database db3 charset=gbk; - 查看数据库信息
格式: show create database 数据库名;
举例:
show create database db1;
- 删除数据库
格式: drop database 数据库名;
举例:
drop database db3;
drop database db2;
查询所有数据库:show databases; - 使用数据库
执行表相关和数据相关的SQL语句之前必须先使用了某个数据库
格式: use 数据库名;
举例:
use db1;
表相关的SQL语句
执行表相关的SQL语句必须已经使用了某个数据库
use db1;
- 创建表
格式: create table 表名(字段1名 类型,字段2名 类型,.......);
举例:
create table person(name varchar(50),age int);
create table student(name varchar(50),chinese int,math int,english int)charset=utf8;
创建一个员工表emp 保存名字,工资和工作
create table emp(name varchar(50),salary int,job varchar(20)); - 查询所有表
格式: show tables; - 查询表信息
格式: show create table 表名;
举例:
show create table emp; - 查询表字段
格式: desc 表名; - 修改表名
格式: rename table 原名 to 新名;
举例:
rename table student to stu; - 删除表
格式: drop table 表名; - 添加表字段
- 最后面添加格式: alter table 表名 add 字段名 类型;
- 最前面添加格式: alter table 表名 add 字段名 类型 first;
- 在xxx字段后面添加: alter table 表名 add 字段名 类型 after xxx;
- 删除表字段
格式: alter table 表名 drop 字段名; - 修改表字段
格式: alter table 表名 change 原名 新名 新类型;
UTF8字符集
目前utf8 和utf8mb3 一样 ,代表的是 用3个字节表示一个字符 (mb3=most byte 3) utf8 mb4 最多不超过4个字节表示一个字符
数据相关SQL
操作数据必须保证已经使用了某个数据库并且已经准备好了保存数据的表
create database mydb5 charset=utf8;
use mydb5;
create table person(name varchar(50),age int)charset=utf8;
- 往表中插入数据
全表插入格式: insert into 表名 values(值1,值2);
指定字段插入格式: insert into 表名(字段1名,字段2名)values(值1,值2);
举例:
insert into person values('tom',18);
insert into person(name) values('jerry');
insert into person values('aaa',10),('bbb',20),('ccc',30);
insert into person(name) values('xxx'),("yyy"),("zzz");
- 查询数据
格式: select 字段信息 from 表名 where 条件; - 修改数据
格式: update 表名 set 字段名=值 where 条件; - 删除数据
格式: delete from 表名 where 条件;
主键约束-primary key
- 主键: 表示数据唯一性的字段称为主键
- 约束: 创建表时给表字段添加的限制条件
- 主键约束: 限制主键的值 唯一且非空
- 如何使用:
create table t1 (id int primary key,name varchar(50))charset=utf8;
主键约束+自增
- 自增规则: 从历史最大值基础上+1
- 如何使用:
create table t2(id int primary key auto_increment,name varchar(50))charset=utf8;
SQL语句分类
- DDL: 数据定义语言,包括数据库相关和表相关的SQL语句
- DML: 数据操作语言, 包括增删改查
- DQL: 数据查询语言, 只包含select查询相关的SQL语句
- TCL: 事务控制语言
- DCL: 数据控制语言
数据类型
- 整数: int(m)和bigint(m) m代表显示长度, m=5 存18 查询得到00018
create table t3(age int(5) zerofill);
insert into t3 values(18);
select * from t3; - 浮点数: double(m,d) m代表总长度,d代表小数长度 , 存23.212 m=5 d=3
create table t5(price double(5,3));
insert into t5 values(23.32123);
insert into t5 values(233.32123); //报错 - 字符串:
- char(m), 固定长度, m=10 存abc 占10, 执行效率略高, 当保存数据的长度相对固定时使用, 最大值255
- varchar(m),可变长度,m=10 存abc 占3,更节省空间, 最大值65535 但推荐保存短的数据(255以内)
- text(m),可变长度, 最大值65535,建议保存长度大于255的
- 日期:
- date, 只能保存年月日
- time, 只能保存时分秒
- datetime, 保存年月日时分秒, 默认值为null , 最大值 9999-12-31
- timestamp(时间戳,距离1970年1月1日的毫秒数),保存年月日时分秒,默认值为当前系统时间,最大值 2038-1-19
- 如何使用:
create table t6(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t6 values("2022-5-15",null,null,null);
insert into t6 values(null,"14:20:25","2011-10-22 10:20:30",null);
导入*.sql批处理文件
- 从工程中得到emp.zip 从idea中复制粘贴到 某个磁盘的根目录 ,然后右键解压到当前文件夹, 在根目录下出现emp.sql文件
- 在客户端中 执行 source f:/emp.sql;
- 执行以下SQL语句 检查是否成功
show tables; //检查是否出现了 emp和dept两个表
select * from emp; //检查是否出现了数据, 如果出现乱码 执行set names utf8; 如果格式错乱 正常
去重distinct
- 查询员工表中所有不同的工作
select distinct job from emp; - 查询员工表中出现了哪几个不同的部门id?
select distinct dept_id from emp;
is null和is not null
- 查询有领导的员工姓名和领导id
select name,manager from emp where manager is not null; - 查询没有领导的员工姓名
select name from emp where manager is null;
and 和 or
- 查询1号部门工资高于2000的员工信息
select * from emp where dept_id=1 and sal>2000; - 查询3号部门或工资等于5000的员工信息
select * from emp where dept_id=3 or sal=5000; - 查询出孙悟空和猪八戒的员工信息
select * from emp where name="孙悟空" or name="猪八戒";
比较运算符 > < >= <= = !=和<>
- 查询工资大于等于3000的员工信息
select * from emp where sal>=3000; - 查询工作不是程序员的员工信息(两种写法)
select * from emp where job!="程序员";
select * from emp where job<>"程序员";
between x and y 两者之间
- 查询工资在2000到3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000;
select * from emp where sal between 2000 and 3000;
select * from emp where sal not between 2000 and 3000;
in关键字
- 查询工资等于5000,1500,3000的员工信息
select * from emp where sal=5000 or sal=1500 or sal=3000;
select * from emp where sal in(5000,1500,3000);
select * from emp where sal not in(5000,1500,3000);
模糊查询like
- %: 代表0或多个未知字符
- _:代表1个未知字符
- 举例:
- 以x开头 x%
- 以x结尾 %x
- 包含x %x%
- 第二个字符是x _x%
- 以x开头以y结尾 x%y
- 第二个是x倒数第三个是y _x%y__
- 查询名字姓孙的员工信息
select * from emp where name like "孙%"; - 查询名字以精结尾的员工姓名
select name from emp where name like "%精"; - 查询工作第二个字是售的员工姓名和工作
select name,job from emp where job like "_售%"; - 查询名字中包含僧并且工资大于2000的员工姓名和工资
select name,sal from emp where name like "%僧%" and sal>2000;
排序order by
格式: order by 字段名 asc(升序默认)/desc(降序)
description描述
descend 降序
- 查询所有员工姓名和工资并按照工资升序排序
select name,sal from emp order by sal;
select name,sal from emp order by sal asc; - 查询所有员工姓名和工资并按照工资降序排序
select name,sal from emp order by sal desc; - 查询所有员工姓名,工资和部门id并且按照部门id升序排序,如果部门id一致则按照工资降序排序
select name,sal,dept_id from emp order by dept_id,sal desc;
分页查询
- 格式: limit 跳过的条数,请求的条数(每页的条数)
- 跳过的条数=(请求的页数-1)*请求的条数(每页条数)
- 举例:
- 查询第1页的5条数据(1-5条) limit 0, 5
- 查询第2页的5条数据(6-10条) limit 5, 5
- 请求第1页的10条数据 limit 0,10
- 请求第3页的10条数据 limit 20,10
- 请求第8页的10条数据 limit 70,10
- 请求第6页的8条数数据 limit 40,8
- 查询工资最低的3个员工信息,查询按照工资升序排序的第一页的3条数据
- select * from emp order by sal limit 0,3;
- 按照入职日期(hiredate) 升序排序 查询第3页的3条数据
- select * from emp order by hiredate limit 6,3;
- 查询工资最高的员工信息
- select * from emp order by sal desc limit 0,1;
- 查询按照工资降序第2页的5条数据
- select * from emp order by sal desc limit 5,5;
别名
select name as "姓名" from emp ;
select name "姓名" from emp ;
select name 姓名 from emp ;
聚合函数
- 通过聚合函数可以对查询的多条数据进行统计查询,统计查询的方式包括:求平均值, 求最大值,求最小值,求和,计数
- 平均值avg(字段名)
- 最大值max(字段名)
- 最小值min(字段名)
- 求和sum(字段名)
- 计数count(*)
数值计算 + - * / %
- 查询每个员工的姓名,工资和年终奖(年终奖=5个月的工资)
- select name,sal,sal*5 年终奖 from emp;
- 给3号部门的员工每人涨薪5块钱
- update emp set sal=sal+5 where dept_id=3;
分组查询 group by
- 格式: group by 分组的字段名
- 查询每个部门的平均工资
- select dept_id,avg(sal) from emp group by dept_id;
- 查询每个部门的最高工资
- select dept_id,max(sal) from emp group by dept_id;
- 查询每种工作的最高工资
- select job,max(sal) from emp group by job;
- 查询每种工作的人数
- select job,count(*) from emp group by job;
- 查询每个部门工资高于2000的人数
- select dept_id,count(*) from emp where sal>2000 group by dept_id;
- 查询每个部门有领导的员工的人数
- select dept_id,count(*) from emp where manager is not null group by dept_id;
having
- where后面只能写普通字段的条件,不能包含聚合函数
- having后面可以包含聚合函数的条件,需要和group by结合使用,写在group by的后面
- 查询每个部门的平均工资要求平均工资高于2000
- select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000;
- 查询每种工作的人数,只查询人数大于1 的
-
select job,count(*) from emp group by job having count(*)>1;
-
select job,count(*) c from emp group by job having c>1;
-
- 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400
-
select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s>5400;
-
-
查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的
-
select dept_id,avg(sal) a from emp where sal between 1000 and 3000 group by dept_id having a>=2000;
-
各个关键字的书写顺序
select 查询的字段信息 from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit 跳过条数,请求条数;
子查询(嵌套查询)
- 查询工资大于2号部门平均工资的员工信息
-
select avg(sal) from emp where dept_id=2;
-
select * from emp where sal>(select avg(sal) from emp where dept_id=2);
-
- 查询工资高于程序员最高工资的员工信息
-
select max(sal) from emp where job="程序员";
-
select * from emp where sal>(select max(sal) from emp where job="程序员");
-
- 查询工资最高的员工信息
-
select max(sal) from emp;
-
select * from emp where sal=(select max(sal) from emp);
-
- 查询和孙悟空相同工作的员工信息
-
select job from emp where name="孙悟空";
-
select * from emp where job=(select job from emp where name="孙悟空") and name!="孙悟空";
-
- 查询拿最低工资员工的同事们的信息(同事指同一部门)
- 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);
关联关系
- 指创建的表和表之间存在的业务关系
- 有哪几种关系?
- 一对一: 有AB两张表,A表中的一条数据对应B表中的一条数据, 同时B表中的一条数据也对应A表中的一条数据
- 一对多:有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据对应A表中的一条数据
- 多对多:有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据也对应A表中的多条数据
- 表和表之间如何建立关系 ,通过外键字段建立关系
- 一对一: 在任意表中添加一个建立关系的字段指向另外一张表的主键 .
- 一对多: 在多的表中添加建立关系的字段(外键) 指向另外一张表的主键 .
- 多对多: 需要创建一个单独的关系表,里面至少包含两个字段分别指向另外两个表的主键.
关联查询
- 同时查询多张表数据的查询方式称为关联查询
- 关联查询包括: 等值链接, 内连接和外连接
关联查询之等值链接
- 格式: select * from A,B where 关联关系
- 查询工资高于2000的员工姓名和对应的部门名
- select e.name,d.name,sal from emp e,dept d where e.dept_id=d.id and sal>2000;
关联查询之外连接
- 等值链接和内连接查询到的都是两张表的交集数据
- 外连接查询的是一张表的全部和另外一张表的交集数据
- 格式: select * from A left/right join B on 关联关系
insert into emp(name,sal) values("灭霸",5);
- 查询所有员工姓名和对应的部门名
- select e.name,d.name from emp e left join dept d on e.dept_id=d.id;
- 查询所有部门的名称,地点和对应的员工姓名和工资
-
select d.name,loc,e.name,sal from emp e right join dept d on e.dept_id=d.id;
-
JDBC
- JavaDataBaseConnectivity: Java数据库链接
- 学习JDBC主要学习的就是如何通过Java语言和MySQL数据库进行链接并执行SQL语句.
- JDBC是Sun公司提供的一套专门用于Java语言和数据库进行链接的API(Application Programma Interface应用程序编程接口)
- 为什么使用JDBC?
- Sun公司为了避免Java程序员,每一种数据库软件都学习一套全新的方法,通过JDBC接口将方法名定义好, 让各个数据库厂商根据此接口中的方法名写各自的实现类(就是一个jar文件, 称为数据库的驱动) ,这样Java程序员只需要掌握JDBC接口中方法的调用,即可访问任何数据库软件.
- 如何通过JDBC链接数据库并执行SQL语句
- 创建Maven工程
- 在工程的pom.xml文件中 添加MySQL驱动的依赖坐标
- 创建cn.tedu.Demo01.java文件 添加以下代码
执行SQL语句的对象Statement
- execute("sql"); 此方法可以执行任意SQL语句, 但建议执行DDL(数据库相关和表相关的SQL语句)
- int row = executeUpdate("sql"); 此方法用来执行增删改相关的SQL语句, 返回值表示生效的行数
- ResultSet rs = executeQuery("sql"); 此方法用来执行查询相关的SQL语句,返回值ResultSet叫做结果集对象,查询到的数据都装在此对象中
数据库连接池DBCP
- DataBaseConnectionPool, 数据库连接池
- 作用: 将连接重用,从而提高执行效率
- 如何使用数据库连接池?
-
//创建连接池对象 DruidDataSource ds = new DruidDataSource(); //设置数据库连接信息 url 用户名 密码 ds.setUrl("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false"); ds.setUsername("root"); ds.setPassword("root"); //设置初始连接数量 ds.setInitialSize(3); //设置最大连接数量 ds.setMaxActive(5); //从连接池中获取连接 异常抛出 Connection conn = ds.getConnection(); System.out.println("连接对象:"+conn);
-
注册登录
- 创建用户表
-
use empdb;
-
create table user(id int primary key auto_increment,username varchar(50),password varchar(50),nick varchar(50))charset=utf8;
-
select count(*) from user where username='tom' and password='123456';
- SQL注入: 往本应该传值的地方,传递进来的SQL语句,导致原有SQL语句的逻辑发生改变, 这个过程称为SQL注入
-
select count(*) from user where username='abcd' and password='' or '1'='1'
-
PreparedStatement预编译的SQL执行对象
- 此对象可以将编译SQL语句的时间点提前,提前后可以将SQL语句逻辑部分提前锁死, 用户输入的内容将不能影响原有SQL语句的逻辑部分,从而解决了SQL注入的问题
- 如果SQL语句中存在变量,则必须使用PreparedStatement,解决SQL注入问题, 而且可以提高开发效率(避免了拼接字符串)
- 如果SQL语句中没有变量,可以使用Statement或PreparedStatement