14.JAVA之数据库、SQL语句、函数

一、数据库

1.1概述

1.1.1什么是数据库

简而言之，就是存储数据，管理数据的仓库。

常见的数据库分为：

1、  关系型数据库：把数据组织成行和列的类似表的结构。

      Oracle、MySQL、SQLServer、Access

2、 非关系型数据库：数据间的关系不紧密。

      MongoDB、Redis、Solr、ElasticSearch、Hive、HBase。

1.1.2关系型和非关系型

早期发展的数据库建立在数据的紧密关系基础之上（如：父子关系、师生关系），我们称其为关系型数据库，也称为传统数据库；现今数据库建立在数据的松散关系基础之上（如：中国人和美国人、中国人和印度人、视频、音频），我们称其为非关系型数据库nosql（not only sql）。业界总在争论nosql能否干掉传统数据库，很多初学者也有这个困惑。以我来看，两者没有矛盾，它们各有特点，根据业务情况互补才是真谛。但总的来说原来关系型数据库一统天下的格局早被打破，领土不断被蚕食，规模一再的缩小，虽然无法全面被替代，但却早已风光不在，沦落到一偶之地，Oracle的衰落就是最好的证明，早期只要是全球大企业无一例外都是部署Oracle，但现在都在去Oracle化，阿里就已经全面排斥Oracle。

1.1.3关系型数据库

关系型数据库有特定的组织方式，其以行和列的形式存储数据，以便于用户理解。关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据集合。

1.2Mysql数据库

1） mysql服务端，它来处理具体数据维护，保存磁盘

2） mysql客户端，CRUD新增，查询，修改(更新)，删除。

安装MySQL：设置端口号(默认是3306)+密码(root)+编码(默认是latin1->utf-8)

          Mysql数据库默认的编码是latin1等价于iso-8859-1，修改为utf-8(字符集)。

二、SQL语句

2.1概述

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

2.2分类

1、 DML（Data Manipulation Language）数据操纵语言。

如：insert，delete，update，select（插入、删除、修改、检索）简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete

2、 DDL（Data Definition Language）数据库定义语言。

如：create table之类

3、  DCL（Data Control Language）数据库控制语言。

如：grant、deny、revoke等，只有管理员才有相应的权限

4、  DQL  （DQL:Data Query Language）数据查询语言。

注意：SQL不区分大小写

2.3用法

数据库：查询、创建、删除库(谨慎)

表：查询、创建、删除、修改

记录：查询、创建、删除、修改

2.4数据库的常用操作

2.4.1       建库

Ø  创建数据库，数据库名称：byf

create database byf DEFAULT CHARACTER SET utf8;

2.4.2       删库

Ø  删除名称是byf的数据库

drop database byf;

2.4.3       查看所有数据库

Ø  查看所有数据库

show databases;

2.4.4 使用数据库

使用数据库：use  byf;

2.5表的常用操作

2.5.1    表设计

门店表：tb_door

订单详情表：tb_order_detail

2.5.2       创建表

Ø  创建tb_door表，有id,door_name,tel字段

create table tb_door(

id int primary key auto_increment,

door_name varchar(100),

tel varchar(50)

);

2.5.3       修改表

Ø  添加列

alter table tb_door add column see NUMERIC(7,2)

2.5.4       删除表

Ø  删除名称是tb_door的表

drop table tb_door;

2.5.5       查看所有表

Ø  查看所有表

show tables;

2.5.6       查看表结构/设计表

Ø  查看tb_door表结构

desc tb_door;

2.6表记录的常用操作

2.6.1       插入记录

Ø  向tb_door表中插入2条记录

insert into tb_door values(null,’永和大王1店’,666);

insert into tb_door values(null,’ 永和大王2店’,888);

或insert into tb_door(dname,col) value('财务部','上海');

2.6.2       查询记录

Ø  查询tb_door表中的所有记录

SELECT * FROM tb_door;

2.6.3       修改记录

Ø  修改tb_door表中id为1的记录

update tb_door set tel=555 where id=1;

2.6.4       删除记录

Ø  删除tb_door表中id为2的数据

Delete from tb_door where id=2;

2.6.5       排序

Ø  将tb_door表记录按照tel排序

Select * from tb_door order by tel desc;

2.6.6       记录总数

Ø  查询tb_door表中的总记录数

Select count(*) from tb_door;

2.6.7防止中文乱码解决：set names gbk;

2.7数据类型

2.7.1    命名规则

l  字段名必须以字母开头，尽量不要使用拼音

l  长度不能超过30个字符（不同数据库，不同版本会有不同）

l  不能使用SQL的保留字，如where,order,group

l  只能使用如下字符a~z、A~Z、0~9、$ 等

l  Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name

l  多个单词用下划线隔开，而非java语言的驼峰规则

2.7.2    字符

l  char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间

l  varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2

l  大文本: 大量文字（不推荐使用，尽量使用varchar替代）

以utf8编码计算的话，一个汉字在u8下占3个字节

注：不同数据库版本长度限制可能会有不同

2.7.3    数字

l  tinyint,int整数类型

l  float,double小数类型

l  numberic(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数

l  decimal和numeric表示精确的整数数字

2.7.4    日期

l  date 包含年月日

l  time时分秒

l  datetime包含年月日和时分秒

l  timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

2.7.5    图片

l  blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

2.8字段约束

2.8.1    主键约束primary key，设置主键自增策略（auto_increment）

主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。

添加主键约束，例如将id设置为主键：

主键自增策略:当主键为数值类型时，为了方便维护，可以设置主键自增策略（auto_increment），设置了主键自增策略后，数据库会在表中保存一个AUTO_INCREMENT变量值，初始值为1，当需要id值，不需要我们指定值，由数据库负责从AUTO_INCREMENT获取一个id值，作为主键值插入到表中。而且每次用完AUTO_INCREMENT值，都会自增1. AUTO_INCREMENT=1

create table abc(

    id int primary key auto_increment

);

insert into abc values(null);

insert into abc values(null);

insert into abc values(null);

select * from abc;

2.8.2    非空约束not null

非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。

添加非空约束，例如为password添加非空约束：

create table user(

    id int primary key auto_increment,

    password varchar(50) not null

);

show tables;

insert into user values(null,null);//不符合非空约束

insert into user values(null,'123');//OK

2.8.3    唯一约束unique--唯一约束

唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。

添加唯一约束，例如为username添加唯一约束及非空约束：

create table test(

    id int primary key auto_increment,

        username varchar(50) unique--唯一约束

);

show tables;

insert into test values(null,'lisi');

insert into test values(null,'lisi');--username的值要唯一,重复会报错的

select * from test;

2.9基础函数

2.9.1  lower

SELECT * FROM dept;

SELECT dname,LOWER(dname) from dept;--数据转小写

2.9.2  upper

select upper(dname) from dept --数据转大写

2.9.3  length

select length(dname) from dept --数据的长度

2.9.4  substr

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]

2.9.5  concat

select dname,concat(dname,'123') X from dept --拼接数据

2.9.6  replace

select dname,replace(dname,'a','666') X from dept --把a字符替换成666

2.9.7  ifnull

select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换

2.9.8  round & ceil & floor

round四舍五入，ceil向上取整，floor向下取整

--直接四舍五入取整

select comm,round(comm)  from emp

--四舍五入并保留一位小数

select comm,round(comm,1)  from emp

--ceil向上取整,floor向下取整

select comm,ceil(comm) ,floor(comm)  from emp

2.9.9  uuid

SELECT UUID()

返回uuid：a08528ca-741c-11ea-a9a1-005056c00001

2.9.10      now

select now() -- 年与日 时分秒

select curdate() --年与日

select curtime() --时分秒

2.9.11      year & month & day

--hour()时  minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp ;

--year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from emp ;

2.9.12      转义字符

’作为sql语句符号，内容中出现单撇就会乱套，进行转义即可

select 'xi'an'  -- 单引号是一个SQL语句的特殊字符

select 'xi\'an' --数据中有单引号时,用一个\转义变成普通字符

2.10条件查询

2.10.1  distinct

使用distinct关键字，去除重复的记录行

SELECT loc FROM dept;

SELECT DISTINCT loc FROM dept;

2.10.2  where

注意:where中不能使用列别名!!

select * from emp

select * from emp where 1=1 --类似没条件也成立

select * from emp where 1=0 --条件不成立

select * from emp where empno=100 --唯一条件

select * from emp where ename='tony'  and deptno=2 --相当于两个条件的&关系

select * from emp where ename='tony'  or deptno=1 --相当于两个条件的|关系

select name, sal from emp where sal=1400 or sal=1600 or sal=1800;

-- 或

select name, sal from emp where sal in(1400,1600,1800);

select name, sal from emp where sal not in(1400,1600,1800);

2.10.3  like

通配符%代表0到n个字符，通配符下划线_代表1个字符

select * from emp where ename like 'l%' --以l开头的

select * from emp where ename like '%a' --以a结束的

select * from emp where ename like '%a%' --中间包含a的

select * from emp where ename like 'l__'  --l后面有两个字符的 _代表一个字符位置

2.10.4  null

select * from emp where mgr is null --过滤字段值为空的

select * from emp where mgr is not null --过滤字段值不为空的

2.10.5  between and

SELECT * FROM emp

select * from emp where sal>3000 and sal<10000

select * from emp where sal>=3000 and sal<=10000--等效

select * from emp where sal between 3000 and  10000--等效

2.10.6  limit

分数最高的记录：按分数排序后，limit n，返回前n条。Oracle做的很笨，实现繁琐，后期有介绍，而mysql做的很棒，语法简洁高效。在mysql中，通过limit进行分页查询：

select * from emp limit 2 --列出前两条

select * from emp limit 1,2 --从第二条开始,展示2条记录

select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条

2.10.7  order by

SELECT * FROM emp order by sal #默认升序

SELECT * FROM emp order by sal desc #降序

SQL的执行顺序： from>where>select>order>limit

2.11聚合 aggregation

2.11.1  count(求总记录数)

select count(*) from emp --底层优化了

select count(1) from emp --效果和*一样

select count(comm) from emp  --慢,只统计非NULL的

2.11.2  max / min(求最大值/最小值)

select max(sal) from emp --求字段的最大值

select max(sal) sal,max(comm) comm from emp

select min(sal) min from emp --获取最小值

select min(sal) min,max(sal) max  from emp --最小值最大值

SELECT ename,MAX(sal) FROM emp group by ename --分组

2.11.3  sum / avg(求和/平均值)

select count(*) from emp --总记录数

select sum(sal) from emp --求和

select avg(sal) from emp --平均数

2.12分组group

用于对查询的结果进行分组统计

group by表示分组, having 子句类似where过滤返回的结果

2.12.1  group by

#每个部门每个岗位的最高薪资和平均薪资，结果中的非聚合列必须出现在分组中，否则业务意义不对

SELECT deptno,MAX(sal),AVG(sal) FROM emp

GROUP BY deptno #按照deptno分组

SELECT job,MAX(sal),AVG(sal) FROM emp

GROUP BY job #按照job分组

SELECT deptno,job,MAX(sal),AVG(sal) FROM emp

GROUP BY deptno,job #deptno和job都满足的

2.12.2  having

#平均工资小于8000的部门

select deptno, AVG(sal)  from emp 

group by deptno  #按部门分组

having AVG(sal) <8000 #查询条件,类似where,但是group by只能配合having

#deptno出现的次数

SELECT deptno,COUNT(deptno)  FROM emp

GROUP BY deptno #按deptno分组

HAVING COUNT(deptno)>1 #次数多的

三、拓展

3.1. char和varchar有什么区别

char为定长字符串，char(n)，n最大为255

varchar为不定长字符串，varchar(n)，n最大长度为65535

char(10)和varchar(10)存储abc，那它们有什么差别呢？

char保存10个字符，abc三个，其它会用空格补齐；而varchar只用abc三个位置。

3.2.datetime和timestamp有什么区别

数据库字段提供对日期类型的支持，是所有数据类型中最麻烦的一个，慢慢使用就会体会出来。

date 是 年与日

time是 时分秒

datetime年月日时分秒，存储和显示是一样的

timestamp时间戳，存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数。

3.3.中文乱码

如果在dos命令下执行insert插入中文数据，数据又乱码，那现在sqlYog客户端执行下面命令：

set names utf8;

set names gbk;

设置客户端字符集和服务器端相同。如果不知道它到底用的什么编码？怎么办呢？很简单，两个都尝试下，哪个最后操作完成，查询数据库不乱码，就用哪个。

那为何会造成乱码呢？

Mysql数据库默认字符集是lantin1，也就是以后网页中遇到的ISO8859-1，它是英文字符集，不支持存放中文。我们创建库时，可以指定字符集：

create database yhdb charset utf8;

但这样很容易造成服务器和客户端编码集不同，如服务器端utf8，客户端ISO8859-1。mysql和客户端工具都有习惯的默认编码设置，好几个地方，要都统一才可以保证不乱码。

我们只要保证创建数据库时用utf8，使用可视化工具一般就基本正确。

3.4.注释

/* 很多注释内容 */

# 一行注释内容

-- 一行注释内容，这个使用较多。

3.5.主键、外键、唯一索引的区别

1、 Primary Key   主键约束，自动创建唯一索引

2、 Foreign Key   外键约束，外键字段的内容是引用另一表的字段内容，不能瞎写

3、  Unique Index  唯一索引，唯一值但不是主键

对于约束的好处时，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。

3.6.drop、delete和truncate之间的区别

drop删除库或者表，数据和结构定义

delete和truncate只是删除表的数据

delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录

对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数。