JAVA学习之路——数据库

数据库

什么是数据库
—DBMS:DataBaseManagementSystem，数据库管理系统（数据库软件）
常见的DBMS：Oracle 、MySQL 、DB2SQLserver 、sqlite等

数据库分类

1.关系型数据库：经过数学理论验证可以保存现实生活中的各种关系，以表为单位保存数据
2.非关系型数据库：主要解决特定的场景，比如数据缓存，以键值对的形式在内存中保存数据

开源和闭源

1.开放源代码，免费，靠服务盈利，有大拿无偿维护升级
2.不开放源代码，收费，靠买产品和服务盈利，有大拿攻击找漏洞

常见关系型数据库介绍

1.MYSQL：Oracle，开源产品，08年被Sun公司收购，09年Sun被Oracle收购，传言要闭源，MYSQL创始人辞职创办MariaDB，市场占有率第一
2.Oracle：Oracle公司产品，闭源，性能和价格都是最高，市场占有率第二
3.SQLserver：微软产品，主要应用在微软整体解决方案当中，市场占有率第三
4.DB2：IBM公司产品，闭源，主要应用在微软IBM解决方案中
4.SQLite：轻量级数据库，只具备最基础的增删改查操作，主要应用在嵌入式设备和移动设备当中

网站整体解决方案：编程语言（Java、net、PHP），WebServer（web服务软件），操作系统（Window、Linux），数据库软件

SQL

什么是SQL：Structured Query Language结构化查询语言，用于程序员和数据进行沟通

连接数据库

Linux：任意位置右键打开终端，执行：
mysql -uroot -p 回车 填写密码 回车
window：开始菜单-所有程序-找到MySQL-MySQL Clicet-输入密码 回车
退出命令：exit

数据库相关SQL

1.查看所有数据库
格式：show databases;
2.创建数据库
格式：create database 数据库名 character set utf8/gbk;
exp:creat database db1 character set utf8;
3.查看数据库详情：
格式：show create database 数据库名；
exp:shoe create database db1;
4.删除数据库
格式：drop database 数据库名;
exp:drop database db1;
5.使用数据库
格式：use 数据库名;
exp:use db1;

表相关SQL

前提一定使用了数据库
1.创建表
格式：
create table 表名(字段名1 字段类型1,字段名2 字段类型2,…);
create table person(name varchar(10),age int);
2.查询表详情
格式：
show tables; //查看所有表
show create table 表名; //查看指定表
show create table person;
表引擎：
1).innodb：默认，支持数据库的高级操作。如：外键、事物等
2).myisam：只支持基础的增删改查操作
3.创建表时指定引擎字符集
格式：
create table 表名(字段名1 字段类型1,字段名2 字段类型2,…)
engine= innodb/myisam charset= utf8/gbk;
create table t1(name varchar(10),age int) engine=myisam charset=gbk;
4.查看表字段
格式：
desc 表名;
desc person;
5.删除表
格式：
drop table 表名;
drop table t1;
6.查询所有表
格式：
show tables;
7.修改表名
格式：
rename table 原名 to 新名;
8.修改引擎和字符集
格式：
alter table 表名 engine=myisam/innodb charset=utf8/gbk;
alter table student engine=myisam charset=gbk;
9.添加表字段
最后添加格式：alter table 表名 add 字段名 字段类型;
最前添加格式：alter table 表名 add 字段名 字段类型 first;
在xx后添加格式：alter table 表名 add 字段名 字段类型 after xxx;
10.删除表字段
格式：
alter table 表名 drop 字段名;
alter person drop salary;
11.修改字段名和类型
格式：
alter table 表名 change 原名 新名 新类型;
alter table student change gender salary int;

数据相关SQL

1.新增数据（插入数据）
全表插入格式：
insert into 表名 values(值1，值2，值3···);
指定字段插入格式：
insert into 表名 (字段1，字段2) values (值1，值2);
批量插入：在values后面通过逗号将多组数据写在一起
insert into t1 values (3,‘aaa’,10),(4,‘bbb’,11),(5,‘ccc’,12)
中文问题：
insert into t1 values (6,‘刘德华’,30);//如果报错执行下面命令
set names gbk; //让MySQL软件接收数据时的解码字符集为gbk;
2.查询数据
格式：
select 字段信息 from 表名 where 条件;
select name from ti;//查询所有name
select name,age from t1;
*selsec from t1; //查询所有信息
*select from t1 where id<3; //查询id小于3的所有信息
3.修改数据
格式：
updata 表名 set 字段名=值 where 条件;
update t1 set age=40 where name=‘刘德华’;
update t1 set age=18; //所有数据都改成18
4.删除数据
格式：
delete from 表名 where 条件;
delete from t1 where id=1;
delete from t1;//删除全部

SQL数据类型

1.整数：int和bigint(等效long)
2.浮点数：double(m,d) m代表总长度，d代表小数长度
52.342 m=5，d=3
decimal(m,d)：超高精度的浮点数
3.字符串：char(m) m代表字符串长度 固定长度 最大长度255
varchar(m) 可变长度 最大长度65535 但是超过255建议使用text
text(m) 可变长度 最大长度6535
4.日期：date：只能保存年月日
time：只能保存时分秒
datetime：保存年月日时分秒，默认值为null，最大值9999-12-31
timestamp(时间戳)：保存年月日时分秒，more哪位当前系统时间，最大值2038-1-19

主键约束 primary key

什么是主键：表示数据唯一性的字段成为主键
什么是约束：创建表时给表字段你添加的限制条件
什么是主键约束：限制主键的值唯一且非空
create table t2(id int primary key,name varchar(10),age int);
主键约束加自增 primary key auto_increment
格式：
creat table t3(id primary key auto_increment,name varchar(10));
insert into t3values(null,‘bbb’); //id可以赋空
从历史最大值基础+1 只增不减(若历史最大为12，删除12，再次用null赋值，结果为13)

导入(*.sql)文件到数据库

windows：把文件放到d盘根目录执行以下命令
source d:/tables.sql;
linux：把文件解压放到桌面
source/home/soft01/桌面/tables.sql

is null 和is not null

1.查询没有领导的员工姓名
select ename from emp where mgr is null; //不能是mgr=null
2.查询有上级领导的员工信息
select * from emp where mgr is not null;

去重 distinct

1.查询员工表中有哪些部门编号
select distinct deptno from emp;
2.查询员工表中员工从事的工作有哪些
select distinct job from emp;

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

1.查询工资在2000以下的员工姓名、工资
select ename,sal from emp where sal<2000;
2.查询职位是manager的员工姓名、工资、职位
select ename,sal,job from emp where job=‘manager’;
3.查询工资小于等于1600的员工姓名、职位、工资
select ename,job,sal from emp where sal<=1600;
4.查询不是10号部门的员工姓名和部门编号
select ename,deptno from emp where deptno!=10;
select ename,deptno from emp where deptno<>10;

and和or

如果查询数据需要同时满足多个条件用and
如果查询数据只需要满足一个条件就可以使用or
1.查询10号部门中工资低于2000的员工姓名、工资和部门编号
select ename,sal,deptno from emp where deptno=10 and sal<2000;
2.查询部门是30号或者工资高于3000的员工信息
select * from emp where deptno=30 or sal>3000;

模糊查询like

%代表0或多个未知字符
下划线代表1个未知字符
例：
1.查询第一个字符是x x%
2.查询最后一个字符是y %y
3.倒数第二个字符是x %x下划线
4.以x开头以y结尾 x%y
5.包含x %x%
6.第二个字符是x，倒数第三个字符是y 下划线x%y下划线下划线
1.查询名字以j开头的员工姓名
select ename from emp where ename like ‘j%’;
2.查询名字第二个字符是l的员工姓名和工资
select ename,sal feom emp where ename like ‘_l%’;
3.查询包含a的员工姓名
select ename from emp where ename like ‘%a%’;

between x and y 包含x和y

1.查询工资在2000到3000的员工姓名和工资
select ename,sal from emp where sal>=2000 and sal<=3000;
select ename,sal from emp where sal between 2000 and 3000;

in

1.查询工资为800,1300,1500的员工信息
select * from emp where sal=800 or sal=1300 or sal=1500;
select * from emp where sal in (800,1300,1500);

排序 order by

order by 字段名 asc 升序(默认)/desc降序
1.查询每个员工的姓名和工资，按照工资升序排序
select ename,sal from emp order by sal;
2.查询每个员工的姓名和奖金，按照奖金降序排序
select ename,comm from emp where deptno=30 order by comm desc;

练习
1.查询有奖金并且有上级领导的员工姓名、奖金、领导编号
select ename,comm,mgr from emp where comm>0 and mgr is not null;
2.查询名字中包含a，并且工资在3000以内的员工姓名和工资
select ename,sal from emp where ename like ‘%a%’ and sal<=3000;
3.查询20号和30号员工姓名和工资，按照降序排序
select ename,sal from emp where deptno in (20,30) order by sal desc;
4.查询工资不等于3000,5000,1500的员工姓名和工资
select ename,sal from emp where sal not in(3000,5000,1500);
5.查询工资在1000到2000之外的员工信息，按照工资升序排序
select * from emp where sal order between 1000 and 2000 order by sal;

分页查询 limit

limit 跳过条数，请求条数(每页条数)
1.查询员工表信息工资降序，第三页，每页两条数据
select * from emp order by sal desc limit 4,2;
2.查询员工表工资前三名的员工姓名和工资
select *from emp order by sal desc limit 0,3;
3.查询工资最低的员工信息
select * from emp order by sal 0,1;

数值计算 + - * / %

1.查询每个员工的姓名、工资和年终奖(工资5)
select ename,sal,sal5 from emp;
2.查询员工表中每个员工涨薪1000块之后的姓名、涨薪前工资和涨薪后工资
select ename,sal,sal+1000 from emp;

聚合函数

对查询的多条数据进行统计查询：平均值、最大值、最小值、求和、计数
平均值 avg：
1.查询30号部门的平均工资
select avg(sal) from emp where deptno=30;
2.查询工作是manager的员工的平均工资
select avg(sal) from emp where job=‘manager’;
最大值和最小值max,min(字段名)：
1.查询10号部门工资的最高值
select max(sal) from emp where deptno=10;
2.查询20号部门工资的最低值
select min(sal) from emp where deptno=20;
求和sum(字段名)：
1.查询员工表中30号部门的工资总和
select sum(sal) from emp where deptno=30;
计数 count(字段名 或)：*
如果指定字段有null时，不计入数值
1.查询20号部门工资大于2000的员工人数
select count(*) from emp deptno=10 and sal<2000;

分组查询 group by

格式：group by 字段
题目需求中出现每个，每种时使用分组查询，每个什么就什么作为分组的字段
1.查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptni;
2.查询每种工作的最高工资
select job,max(sal) from emp group by job;
3.查询每个部门的工资总和
select deptno,sum(sal) from emp group by deptno;
4.查询每个领导mgr的下属个数
select mgr,count(*) from emp where mgr is not null group by mgr;

having

from后面只能跟普通字段的条件，如果条件是聚合函数的条件则需要使用having
having一定要和group by结合使用
格式：select … from 表名 where …group by … having … order by …limit
1.查询每个部门的平均工资，要求查询平均工资大于2000的
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
别名：
select deptno 部门编号,avg(sal) a from emp group by deptno having a>2000;
2.查询emp表中工资在1000到3000的员工，每个部门的编号、工资总和、平均工资，过滤掉平均工资低于2000的部门，最后按照平均工资进行升序排序
select deptno,sum(sal),avg(sal)a from emp where sal between 1000 and 3000 group by deptno having a>=2000 order by a;

总结练习

1.查询没有上级领导的员工的编号、姓名、工资
select empno,ename,sal from emp where mgr is null;
2.查询emp表中没有奖金的员工的姓名、职位、工资和奖金
select ename,job,sal,comm from emp where comm is null or comm=0;
3.查询emp表中含有奖金的员工的编号、姓名、职位和奖金
select empno,ename,job,comm from emp where mgr is not null;
4.查询含有上级领导的员工的姓名，工资以及上级领导编号
select ename,sal,mgr from emp where mgr is not null;
5.查询emp表中名字以"S"开头的所有员工的姓名
select ename from emp where ename like ‘S%’;
6.查询emp表中名字的最后一个字符是"S"的员工的姓名
select ename from emp where ename like ‘%S’;
7.查询emp表中倒数第二个字符是"E"的员工的姓名
select ename from emp where ename like ‘%e_’;
8.查询emp表中员工的倒数第三个字符是"N"的员工姓名
select ename from emp where ename like ‘%N__’;
9.查询emp表中员工的名字中包含"A"的员工的姓名
select ename from emp where ename like ‘%A%’;
10.查询emp表中名字不是以"K"开头的员工的所有信息
select *from emp where ename not like ‘k%’;
11.查询emp表中名字不包含’A’的所有员工的信息
select *from emp where ename not like ‘%A%’;
12.做文员的员工人数(job中含有CLERK的)
select count(星) from emp where job=‘clerk’;
13.销售人员job:salseman的最高薪水
select max(sal) from emp where job=‘SALSEMAN’;
14.查询emp表中员工的编号、姓名、职位、工资，并且工资在1000-2000之间
select empno,ename,job,sal from emp where sal between 1000and 2000;
15.查询emp表中员工在10号部门，并且含有上级领导的员工的姓名、职位、上级领导编号以及所属部门的编号
select ename,job,mgr,deptno from emp where deptno=10 and mgr is not null;

分组查询练习

1.查询除了10号部门，每个部门的平均工资
select deptno,avg(sal) from emp where deptno!=10 group by deptno;
2.查询每个职业里面的员工人数
select job,count(*) from emp group by job;
3.查询每个部门的平均工资然后以平均工资降序排序
select deptno,avg(sal) a from emp group by deptno order by a desc;
4.查询每个月发工资最多的部门编号
select deptno,from emp group by deptno order by sum(sal) desc limit 0,1;
5.查询只有三个员工的部门编号
select deptno from emp group by deptno having count(星)=3;

子查询（嵌套查询）

1.查询工资高于平均工资的员工信息
select avg(sal) from emp ;
select 星 from emp where sal>(select avg(sal) from emp );
2.查询员工表中工资最高的员工信息
select max(sal) from emp;
select 星 from emp where sal=(select max(sal) from emp);
3.查询工资高于20号部门最高工资的员工信息
select 星 from emp where sal>(select max(sal) from emp where deptno=20);
4.查询和jnoes相同工作的其他员工信息
select 星 from emp where job=(select job from emp where ename=‘jnoes’) and ename!=jnoes;
5.查询最低员工的同事们的信息
select min(sal) from emp;
select deptno from emp where sal=(select min(sal) from emp);
select 星 from emp where deptno =(select deptno from emp where sal=(select min(sal) from emp)😉 and sal!=(select min(sal) from emp);
6.查询King的部门信息（用到dept表）
select depyno from emp where ename=‘King’;
select 星from dept where deptno=(select depyno from emp where ename=‘King’);

关联查询

同时查询多张表的查询方式称为关联查询
关联查询必须写关联关系，如果不写，会得到两张表数据的成绩
1.查询每个员工的姓名和对应的部门
select e.ename,d.deptno from emp e,dept d where e.deptno=e.deptno;
关联查询查询方式
1.等值连接：select 星 from A,B where A.x=B.x and A.age=18;
2.内连接：select 星 from A join B on A.x=B.x where A.age=18;
等值连接和内连接查询到的数据是两张表的交集数据
3.外连接：select 星 from A left/right join B on A.x=B.x where A.age=18;
外连接查询到的是一张表的全部和另一张表的交集
查询所有部门的名称和对应的员工的姓名
select d.dname,e.ename from emp e right join dept d on e.deptno=d.deptno;
总结：如果查询的是两张表的交集数据则使用等值连接或内连接（推荐），如果查询的是一张表的全部和另一张表的交集则只用外连接

关联关系

关联关系指创建表时两张表之间存在的业务关系
外键：用与建立关系的字段称为外键，一个表只有一个主键，但是可以有多个外键

1.一对一：
如何建立关系：从表中添加外键指向表的主键
2.一对多：
如何建立关系：在多的表中添加外键指向另一张表的主键
3.多对多：
如何建立关系：创建一个单独的关系表（用户表和角色表），表中的两个外键字段指向其他两个表的主键

添加注释

添加注释可以方便对数据库的创建

create table test1 ( 
    field_name int comment '字段的注释' 
)comment='表的注释'; 

查看注释：
show create table 表名;
（如果出现乱码：set names jbk）