Java 数据库

Java 数据库

数据库

一 概念

数据库：数据存储的仓库，用于存储和管理数据

常见的数据库产品：

Oracle：oracle公司 大型的关系型数据库

DB2：IBM公司 大型的关系型数据库

Mysql：mysql公司 中小型关系数据库

Sql Sever：微软公司 中小型关系型数据库

Sqllite：安卓系统内置数据库 微型关系型数据库

NoSql系列数据库：not only sql

二 SQL结构化查询语言

概念：通过sql语言可以操作所有的关系型数据库。每种数据库之间会存在差异，称为 “方言”

SQL分类：

DDL(Data Definition Language)：数据定义语言，用来定义数据库对象

DML（Date Manipulation Langugae）:数据操作语言，用来定义数据库记录

DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别

DQL（Data Query Language）：数据查询语言，用来查询记录。

2.1 DDL操作数据库和表

操作数据库：

• 创建：create database 数据库名称;
• 删除：drop database 数据库名称;
• 修改：alte datebase …chaeacter set 要修改的字符集名称
• 查询：show databases; 查询所有数据库的名称
  show create database 数据库名称 查询创建数据库语句

操作表：

• 创建

  列的数据类型：

  int：整型 id int；

  double：浮点型 double（6,2）表示最多6位，其中必须有2位是小数，即最大值为9999.99

  char：固定长度字符串类型

  varchar：可变长度字符串类型

  text：字符串类型，存大格式的文本

  blob：字节类型，存字节类型的数据 比如电影字节，图片字节

  date：日期类型 格式为：yyyy—MM—dd

  time:时间类型 格式为：yyyy-MM-dd hh:mm:ss

  datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

  timestamp:时间戳类型 yyyy-MM-dd hh:mm:ss 如果该类型的字段不给赋值，则默认当前时间

​ 语法：

​ create table 表名(
​ 列名1 类型1,
​ 列名2 类型2,
​ …
​ 列名n 类型n

​ ）；

• 删除：drop table 表名

• 修改

  1.添加列：给stu表添加name列

  alter table stu add （name varchar（10））；

  2.修改列类型：修改stu表的gender列类型为char（2）

  alter table stu modify gender char（2）

  3.修改列名：修改stu表的gender列名为sex

  alter table stu change gender sex char（2）

  4.删除列：删除stu表的name列

  alter table stu drop name；

  5.修改表名称：修改stu表名称为studen

  alter table stu rename to studen

• 查询

  show tables；查询该数据库下所有表的名称

  desc 表名；查询表结构

  （注意：操作表之前，应该先选择一个数据库使用：use 数据库名称）

  ​

2.2DML操作表中的数据

• 增

  语法：

  insert into 表名（列名1.列名2，…）values(值1，值2，…);

  (注意：如果表得所有列都有添加值，则列名可以省略)

  insert into stu（name，age）values（‘zhangsan’,23）;

  insert into stu (name,age,sex)values(‘zhangsan’,23,‘1’);

  insert into stu values(‘zhangsan’,23,‘1’);

• 删

  语法：

  delete from 表名；无条件的删除，删除表中所有数据

  truncate table 表名 删除所有记录，先删表，再创建表

  delete from 表名 条件删除 where = > < >= <= and(且) or(或)

  delete from student where name=‘zhangsan’;

  delete fromstudent where name=‘zhangsan’ or name=‘wangwu’ or name=‘lisi’;

• 改

  语法：

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

  update stu set sex =’1‘where name=’lisi‘；

  update customer set money=5000 where id=xxx；

2.3DQL查询

语法：

select*from 表名 查询表中的所有信息

select… … from 表名 查询个别字段

条件查询

= != <>(不等于) < <= > >=

between…and 在什么范围之间

in（set）

is null 为空

is not null 不为空

and 并且

or 或者

not非

– 等值 条件
– 查询工资等于 800的员工
SELECT * FROM emp WHERE sal=800;

– 查询工资大于1500的员工

SELECT * FROM emp WHERE sal>=1500;

– 查询工资大于1000 奖金大于1000的员工

SELECT * FROM emp WHERE sal>1000 AND comm>1000;

– 查询工资大于1000 或 大于1000;

SELECT * FROM emp WHERE sal>1000 OR comm>1000;

– 查询工资 大于等于2000 小于等于 3000 的员工

SELECT * FROM emp WHERE sal>=2000 AND sal<=3000;

– 上面这个可以 BETWEEN 2000 AND 5000; 在什么 之间

SELECT * FROM emp WHERE sal BETWEEN 2000 AND 5000;

– 我要查询奖金是null的员工
– null 值比较特殊，你要判断是不是null 用 = 或 != 判别不出来

SELECT * FROM emp WHERE comm IS NULL;

SELECT * FROM emp WHERE comm IS NOT NULL;

– 字段运算

– 查询这个员工的年薪是多少

SELECT ename,sal,sal*12 FROM emp;

– 可以给字段起别名，用于展示好看一些 AS 可以省略不写

SELECT ename 姓名,sal 月薪,sal*12 AS 年薪 FROM emp;

– 查询这个员工的年薪是多少 并且把奖金加上
– null值参与运算 结果就是null了

SELECT ename AS 姓名,sal AS 月薪,comm AS 奖金,sal*12+comm AS 年薪 FROM emp;

– ifnull 来处理null 值 ifnull(0,comm)

– ifnull()
– NULLIF（参数一，参数二) 如果参数一等于参数二，返回值为NULL，否则返回参数一

SELECT ename AS 姓名,sal AS 月薪,comm AS 奖金,(sal*12)+IFNULL(0,comm) AS 年薪 FROM emp;

– 查询工资是3000的 DISTINCT 对结果去重
SELECT DISTINCT sal FROM emp WHERE sal=3000;

– 我要查询这个奖金是 500 300 1400 的员工

SELECT * FROM emp WHERE comm=300 OR comm=500 OR comm=1400;

– in()
SELECT * FROM emp WHERE comm IN(300,500,1400) OR sal IN(800);

– as 可以给字段起别名，也可以给表起别名

SELECT e.ename,e.job,e.sal FROM emp AS e WHERE e.comm IS NOT NULL;

– 模糊查询 like
– %通配多个任意字符
– _ 统配单个任意字符

– 查询 姓名包含S的员工

SELECT * FROM emp WHERE ename LIKE ‘%S%’

– 查询 姓名是以S开头的

SELECT * FROM emp WHERE ename LIKE ‘S%’

– 查询 姓名是以T结尾的

SELECT * FROM emp WHERE ename LIKE ‘%T’

– 查询 姓名第二个字符是C的员工

SELECT * FROM emp WHERE ename LIKE ‘__O%’

– 查询 姓名是5个字符组成的

SELECT * FROM emp WHERE ename LIKE ‘_____’

– 排序 order by
– 查询工资 从小到大排列

SELECT * FROM emp ORDER BY sal ASC; – 默认升序排列

SELECT * FROM emp ORDER BY sal DESC; – 降序排列

SELECT * FROM emp ORDER BY sal ASC,comm DESC; – 可以排序多个字段

• 聚合函数：
• 聚合函数是用来做纵向运算的函数：

– 我要知道有多少个员工 null值不参与统计

COUNT(empno) 统计一列由多少个

SELECT COUNT(empno)AS 总人数 FROM emp;

SELECT COUNT(*)AS 总人数 FROM emp;

– 统计工资发了多少

SELECT SUM(sal) AS 总支出 FROM emp;

– 统计最高工资 最小工资 平均工资

SELECT MAX(sal) AS 最高工资 FROM emp;

SELECT MIN(sal) AS 最小工资 FROM emp;

SELECT AVG(sal) AS 平均工资 FROM emp;

– 比如我按部分编号分组 比如有三个部门 然后我求每个部门的平均工资 那展示出来的数据应该有三条

– group by

SELECT deptno AS 部门号, AVG(sal) AS 部门平均工资 FROM emp GROUP BY deptno;

– 比如我按部分编号分组 比如有三个部门 然后我求每个部门的总工资 那展示出来的数据应该有三条

SELECT deptno AS 部门号, SUM(sal) AS 部门总工资 FROM emp GROUP BY deptno;

– 1.例如查询 每个部门的部门编号 已经每个部门工资大于1500的人数
– 分组之前可以通过where 做条件筛选

SELECT deptno AS 部门号, COUNT(*) AS 部门人数 FROM emp WHERE sal>1500 GROUP BY deptno;

– 2.例如 我要查询 各个部门平均工资 大于2000 的部门

– having 对分组之后的结果，进行再次筛选
SELECT deptno AS 部门号, AVG(sal) AS 部门平均工资 FROM emp GROUP BY deptno HAVING AVG(sal)>2000

SELECT deptno AS 部门号, AVG(sal) AS 部门平均工资 FROM emp GROUP BY deptno HAVING 部门平均工资>2000

– 3. 例如我要查询 各个部门 员工工资大于1500 的平均工资 并且平均工资 大于3000的部门

– where 和 having 的区别，where 是分组之前，进行条件筛选，不符合条件的不参与分组，having 是对分组之后产生的结果集，进行再次筛选

SELECT deptno AS 部门号, AVG(sal) AS 部门平均工资 FROM emp WHERE sal>1500 GROUP BY deptno HAVING 部门平均工资>3000

– 分页查询 limit

SELECT * FROM emp;

– 我要查询第一页，每页展示4条

SELECT * FROM emp LIMIT 0,4; – 第一页

SELECT * FROM emp LIMIT 4,4; – 第二页

SELECT * FROM emp LIMIT 8,4; – 第三页

SELECT * FROM emp LIMIT 12,4; – 第四页

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