学习记录之数据库（SQL）

2022-07-26 21:08 作者:星月袭空 0人读过 | 我要投稿

数据库:

学习如何对数据进行增删改查操作

SQL:

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

DBMS:

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

常见的数据库管理系统(DBMS)

MySQL: Oracle公司产品, MySQL08年被Sun公司收购, Sun公司09年被Oracle收购. 开源产品市占率第一

Oracle: Oracle公司产品, 闭源产品, 性能最强价格最贵市占率第二

SQLServer: 微软公司产品, 闭源产品, 市占率第三

DB2: IBM公司产品 ,闭源产品

SQLite: 轻量级数据, 安装包只有几十k ,只具备基础的增删改查功能

如何连接数据库执行SQL语句:

执行SQL语句之前需要先和数据库软件进行链接

1.从开始菜单中找到MariaDB或MySQL文件夹展开后找到 MySQL Client 然后输入密码回车

2.如果使用的Linux或苹果系统打开终端然后输入 mysql -uroot -p 回车然后输入密码回车

3.显示下图内容代表成功

4.退出命令 exit;

数据库中如何保存数据:

数据库中以表为单位保存数据,表是存在于数据库之中, 保存数据则需要先建库再建表

SQL语句格式要求:

1.以;结尾

2.不区分大小写

3.可以包含空格和换行

数据库相关SQL:

查询所有数据库: show databases;

创建数据库:create database 数据库名 charset=utf8/gbk;

举例:

create database db1;

create database db2 charset=utf8;

查看数据库信息: show create database 数据库名;

举例:

show create database db1;

删除数据库: drop database 数据库名;

举例:

drop database db2;

使用数据库:执行表相关和数据相关的SQL语句之前必须使用了某一个数据库否则会报错,格式: use 数据库名;

举例:

use db1;

表相关的SQL语句:执行表相关的SQL语句必须提前使用了某个数据库,否则会报错

查询所有表:show tables;

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

举例:

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

create table hero(name varchar(50),money int,type varchar(10))charset=utf8;

查看表信息:show create table 表名; show create table student;

删除表:drop table 表名; drop table student;

修改表名:rename table 原名 to 新名; rename table person to p;

查看表字段: desc 表名;desc = description 描述 desc p;

添加表字段:

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

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

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

举例:

alter table emp add age int;

alter table emp add id int first;

alter table emp add gender varchar(5) after name;

删除表字段:alter table 表名 drop 字段名;

举例:

alter table emp drop gender;

修改表字段:alter table 表名 change 原名新名新类型;

举例:

alter table emp change name age int;

数据相关SQL

执行数据相关的SQL 必须已经使用了某一个数据库并且已经创建了保存数据的表

往表中插入数据(增):

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

insert into person values("Tom",18);

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

insert into person(name) values("Jerry");

批量插入数据:

insert into person values("lilei",28),("hanmeimei",28);

insert into person(name) values("aa"),("bb"),("cc");

插入中文数据:

insert into person values("刘德华",50);

如果执行上面SQL语句报如下错误:

执行 set names gbk; 可以解决

查询数据:select 字段信息 from 表名 where 条件;

举例:

select name from person;

select name,age from person;

select * from person;

select name from person where age=28;

select * from person where name='刘德华';

修改数据:update 表名 set 字段1名=值,字段2名=值 where 条件;

举例:

update person set name="杰瑞" where name='Jerry';

update person set name="张学友",age=30 where name="刘德华";

update person set age=50 where name="aa";

update person set age=88 where age=28;

update person set age=10 where age is null;

删除数据:delete from 表名 where 条件;

举例:

delete from person where age=88;

delete from person where name="张学友";

delete from person where age>10;

delete from person;

数据类型:

整数: int(m) 和 bigint(m) , bigint等效java中的long, m代表显示长度,举例m=5,存18 查询到 00018 , 需要补零的话必须使用zerofill关键字

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

insert into t1 values(18);

select * from t1;

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

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

insert into t2 values(45.2312312123);

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

字符串

char(m): 固定长度字符串 , m=5 存abc 占5个字符长度 , 应用场景: 当存储长度固定时,比如存储性别char(1) , 最大字符长度255

varchar(m):可变长度字符串, m=5 存abc 占3个字符长度, 最大值65535但是建议保存255以内长度的数据

text(m):可变长度字符串, 最大值65535 建议保存长度大于255的数据

日期

date: 只能保存年月日

time: 只能保存时分秒

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

timestamp(时间戳:保存1970年1月1日到现在的毫秒数):保存年月日时分秒, 最大值2038-1-19 , 默认值为当前系统时间(当赋值为null时触发默认值)

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

insert into t_date values("2022-06-09",null,null,null);

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

select * from t_date;

主键约束 primary key

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

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

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

举例:

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

insert into t5 values(1,"a");

insert into t5 values(2,"b");

insert into t5 values(2,"c"); //重复报错

insert into t5 values(null,"d"); //不能为null 报错

主键约束+自增 primary key auto_increment

当字段设置为自增后插入null值时会触发自增

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

举例:

create table t6(id int primary key auto_increment,name varchar(50));

insert into t6 values(null,"a");

insert into t6 values(null,"b");

insert into t6 values(10,"c");

insert into t6 values(null,"d");

delete from t6 where id>=10;

insert into t6 values(null,"e");

数据库SQL语句的分类

DDL: 数据定义语言, 包括数据库相关和表相关的SQL,truncate table 表名; 删除表并创建新表

DML: 数据操作语言,包括:增删改查相关的SQL语句

DQL: 数据查询语言,包括:select查询相关

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

DCL: 数据控制语言, 包含创建用户以及用户权限分配相关SQL

导入*.sql批处理文件

1.压缩文件复制到某个盘的根目录下面并解压到当前文件夹

2.在命令行客户端中执行source F:/emp.sql;

执行show tables;

如果经过测试出现中文乱码问题执行 set names utf8; 或 set names gbk;

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

1.查询工资大于等于3000的员工姓名,工资和工作

select name,sal,job from emp where sal>=3000;

2.查询1号部门的员工姓名,工资,部门id

select name,sal,dept_id from emp where dept_id=1;

3.查询程序员的名字和工资

select name,sal from emp where job="程序员";

4.查询员工表中不是销售的员工姓名和工作(两种写法)

select name,job from emp where job!="销售";

select name,job from emp where job<>"销售";

and 和 or 和 not

and: 等效java中的&&, 需要多个条件同时满足时使用

or: 等效java中的|| , 多个条件满足一个时使用

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

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

2.查询3号部门或工资等于5000的员工信息

select * from emp where dept_id=3 or sal=5000;

3.查询出CEO和项目经理的名字

select name from emp where job="CEO" or job="项目经理";

4.查询有奖金的销售名字和奖金

select name,comm from emp where comm>0 and job="销售";

is null 和 is not null

1.查询有上级领导的员工姓名和领导id

select name,manager from emp where manager is not null;

2.查询没有上级领导的员工姓名和领导id

select name,manager from emp where manager is null;

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)

当查询某个字段的值等于多个值的时候使用

1.查询工资等于1500,3000和5000的员工姓名和工资

select name,sal from emp where sal=1500 or sal=3000 or sal=5000;

select name,sal 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_

排序 order by

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

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

select name,sal from emp order by sal;

select name,sal from emp order by sal desc;

2.查询工资高于2000的员工姓名和工资, 按照工资降序排序

select name,sal from emp where sal>2000 order by sal desc;

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

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

分页查询

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

举例:

查询第一页的5条数据(1-5) limit 0,5

查询第2页的5条数据(6-10) limit 5,5

查询第5页的5条数据 limit 20,5

查询第8页的10条数据 limit 70,10

查询第7页的8条数据 limit 48,8

别名

select name as "名字" from emp;

select name "名字" from emp;

select name 名字 from emp;

聚合函数

通过聚合函数可以对查询的多条数据进行统计查询

有哪些统计方式?

平均值avg

最大值max

最小值min

求和sum

计数count

平均值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()

查询程序员的工资总和

select sum(sal) from emp where job="程序员";

计数count(*)

查询员工表的人数

select count(*) from emp;

分组查询group by

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

如果题目中出现每个或每种这样的关键字并且查询的内容为聚合函数的结果

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

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

2.查询每个部门的最高工资

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

3.查询每种工作的平均工资

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

4.查询每个部门工资高于2000的人数

select dept_id,count(*) from emp where sal>2000 group by dept_id;

5.查询每种工作的最低工资

select job,min(sal) from emp group by job;

6.查询1号部门和2号部门的人数

select dept_id,count(*) from emp where dept_id in(1,2) group by dept_id;

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

select dept_id,avg(sal) from emp group by dept_id order by avg(sal) desc limit 0,1;

having关键字:

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

having后面专门写聚合函数条件,而且需要和group by分组查询结合使用,写在group by 的后面

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

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

select dept_id,avg(sal) a from emp group by dept_id having a>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;

各种查询相关的关键字顺序：select 字段信息 from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit 跳过条数,请求条数.

子查询(嵌套查询)

可以将一条SQL语句查询的结果当做另外一条SQL语句条件的值

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 max(sal) from emp where job="程序员";

select * from emp where sal>(select max(sal) from emp where job="程序员");

3.查询工资最高的员工信息

select * from emp where sal=(select max(sal) from emp);

4.查询和孙悟空相同工作的员工信息

select * from emp where job=(select job from emp where name="孙悟空") and name!="孙悟空";

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

得到最低工资

select min(sal) from emp;

通过最低工资得到拿最低工资人的部门id

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

通过部门id查询员工

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,sal*5 年终奖 from emp;

2.给3号部门所有员工涨薪5块钱

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

3.查询孙悟空的部门信息(用到dept部门表)

select dept_id from emp where name="孙悟空";

select * from dept where id=(select dept_id from emp where name="孙悟空");

关联关系：

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

有哪几种关系:

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

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

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

如果两张表之间存在关联关系,如何建立关系：

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

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

多对多:需要创建一个单独的关系表,关系表中两个字段分别指向另外两张表的主键

关联查询：

同时查询多张表数据的查询方式称为关联查询

关联查询包括: 等值链接, 内连接和外连接

关联查询之等值链接：

格式: select * from A,B where 关联关系 and 其它条件

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

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

查询工资高于2000的员工姓名,工资和部门信息

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

关联查询之内连接：

内连接和等值链接作用一样,查询到的都是两个表的交集数据,查询不到交集以外的数据

格式: select * from A join B on 关联关系 where 条件

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.* from emp e join dept d on e.dept_id=d.id where sal>2000;

外连接：

作用:查询一张表的全部和另外一张表的交集数据

格式:select * from A left/right join B on 关联关系 where 条件;

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

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

2.查询所有部门名称,地点和对应的员工姓名和工资,只查询有领导的员工

select d.name,loc,e.name,sal from emp e right join dept d on dept_id=d.id where e.manager is not null;

关联查询总结:

如果查询的是多张表中的数据则使用关联查询:(等值链接,内连接和外连接)

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

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

JDBC：

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

JavaDataBaseConnectivity: Java数据库链接

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

Sun公司为了避免Java程序员每一种数据库软件都学习一套新的方法, 通过JDBC接口将方法名定义好,各个数据库厂商根据此接口中的方法名写各自的实现类(jar包(驱动)), 这样Java程序员只需要掌握JDBC接口中方法的调用即可访问任何数据库, 即使在工作中换了数据库软件,代码也不需要发生改变.

Statement执行SQL语句的对象：

execute("sql"); 可以执行任意sql语句,但是推荐执行DDL(数据定义语言,包括数据库相关和表相关)

int row = executeUpdate("sql"); 执行增删改, 此方法的返回值为生效的行数

ResultSet rs = executeQuery("sql"); 执行查询相关的SQL语句, ResultSet对象里面装着查询回来的结果.

DBCP数据库连接池：

DataBaseConnectionPool: 数据库连接池

作用: 将链接重用,避免频繁的开关链接所带来的资源浪费.

SQL注入：

SQL注入是往本应该传值的地方,传递进去了SQL语句导致原有SQL语句的逻辑发生改变,这个过程称为SQL注入

where username='xxx' and password='' or '1'='1'

SQL注入漏洞是网站的低级漏洞,但是危害比较大, 黑客可以利用此漏洞对网站进行攻击

PreparedStatement：

通过PreparedStatement执行SQL语句可以避免出现SQL注入漏洞

内部原理: 使用PreparedStatement 可以起到预编译的作用, 可以将编译SQL语句的时间点提前(从之前执行SQL语句时编译提前到创建时), 编译时间点提前可以将SQL语句中业务逻辑部分编译好(将业务部分锁死),之后将用户输入的内容替换到SQL语句时,用户输入的内容只能以值的形式添加到SQL语句中,不会影响原有SQL语句的判断逻辑,所以避免了SQL注入的漏洞

执行setString()方法替换?时会自动给字符串添加引号,并且用户输入的字符串中出现了引号关键字时会进行转义操作 password=''' or ''1''=''1'

数据库常见错误列表：

1.未开启数据库服务