数据库中常见的操作和函数

这篇博客主要介绍一下数据库中常见的操作和函数；注意，是常见的，也就是说很多不常见的操作和函数这里不会介绍，毕竟零碎的操作和函数过多，又因为本人主学Java语言，因此只掌握了其中一部分常用的，使我Java的学习不受阻碍即可。

这里以MySQL数据库为例进行介绍；

声明：因为内容较多，每行代码都要截图的话会十分麻烦，因此我仅对一些感觉难以理解的地方进行了截图，但是，请放心，所有的代码我都用Navicat执行过，应该是没问题的，如果还有些地方不理解则可以自行使用相应软件执行代码；如有错误，欢迎指正。（MySQL数据库代码）

数据库

SQL语句中的注释是###，这里的//是Java中的注释，习惯了，理解一下；

创建数据库： create database 数据库名 【character set 字符集】
  create database keeper  //创建名字为keeper的数据库，character set 字符集 是规定该数据库的编码
类型，因为MySQL数据库默认都是utf8，因此后面中括号里面的内容直接可以忽略
删除数据库： drop database 数据库名
  drop database keeper //删除名字为keeper的数据库
  use keeper //使用数据库

因为MySQL统一确定编码是utf8，因此修改数据库编码不再介绍；

表

我们先看数据类型和创建表；

create table student(  //创建一个名字为student的表
name varchar(15),  //name类型为varchar型，可储存15个字节；注意在utf8中一个汉字占3个字节
id char(18)， //id类型为char型，可储存18个字节
age int(3),  //age类型为char型，可储存长度为3个字节的整数
height double(4,1)  //height为double型，可储存长度为4个字节且带一位小数的数
time time,  //时间，格式为‘时：分：秒’
date date,  //时间，格式为‘年-月-日’
datetime datetime,  //时间，格式为‘年-月-日 时：分：秒’
timestamp timestamp  //时间，格式为‘年-月-日 时：分：秒’
)

然后我们给表添加一条数据：刘明，411257199108052754，28，78.5，now()，now()，now()，now() 其中now()是自动获取当前时间，精确到秒，格式为‘年-月-日 时：分：秒’；然后查看该数据我们可以得到下表：

char和varchar的区别是：前者一般字节长度固定时使用，比如身份证号，全国都是18位；手机号，11位；后者是字节长度不定时使用，比如：姓名、住址等；另外，填入varchar和char的字段可以不够要求的字节数，但不可超过；int和double也是；

datetime和timestamp的区别，其实就是范围的不同，datetime时间范围较大，大致从1000年到9999年之间的时间都能表示，而timestamp的时间范围较小，只能大致表示1970年到2038年之间的时间；

数据类型常用的还有longblob和longtext，前者是可储存二进制的数据类型，音频、视频、图形、图像都可；后者是储存大文本的数据类型，自述、补充说明等等；注意：longblob最大储存为4G，因此基本上不会用来存储视频和音频等；

其次修改表和一些小约束条件；

create table stu(    
name varchar(15) unique,  //unique 内容不允许重复，即不同行里的name属性（值）不能相同
id char(18) primary key,  //primary key 定义为主键，不允许重复，不允许为null
age int(3) default 0,   //default 0 默认值为0，即输入数据时该项不输入会默认此值为0
height double(4,1) not null    //not null 即不允许该值为null
)

unique 不允许重复；primary key 主键，不允许为null和重复；default '默认值' 即没有输入的情况下默认该值为'默认值'；not null 不允许值为null；

但是，注意：unique 约束虽然不允许重复，但是若此值为null，则满足其他约束条件的话允许有多个null存在；以上表stu为例输入数据：

insert into stu (name,id,age,height) value ('Tom','111','11','111.1')
insert into stu (name,id,age,height) value (null,'112','11','111.1');
insert into stu (name,id,age,height) value (null,'113','11','111.1');
insert into stu (name,id,age,height) value (null,'114','11','111.1')

插入数据后查询后查询可得：

我们发现三个name为null的数据都插入进去了；

alter table stu add sex int(1) default 0 after id  //添加字段，在字段id之后插入int类型且默认值为0的sex项
alter table stu modify name varchar(18)  //修改字段，修改字段name的字段长度，使其原来的长度变大，由15变成18
alter table stu drop sex  //删除字段 sex
rename table stu to stud //重命名
drop table stu  //删除stu表
truncate table stu //删除stu表

注意：

• 修改字段只能修改字段的数据类型（只有对应列为空值才能修改）、大小（一般是加长字段）和默认值（修改默认值只会影响后来插入表的数据，对已存在的数据没有影响）；
• 删除字段一次只能删除一个字段；一个表中最少有一个字段；不能删除作为另外一个表的外键的字段；

修改数据

插入字段
insert into stu (name,height,id,age) value ('Tom','111.1','111','11')
insert into stu value ('Jerry','222','11','1.1')   //可以省略列名，但数据输入顺序要与表中字段的默认顺序相同
insert into stu (name,id,age,height) select name,id,age,height from stud  //从stud表中选取数据插入到stu表中
delete from stu where id='111'   //删除stu表中id为‘111’的那行数据
delete from stu  //删除stu表中所有数据
update stu set id='222' where name='Tom'   //将stu表中name为‘Tom’的那行数据的id改为‘222’

注意：第三种插入方式要求被插入表的字段名列表的数目与数据类型要与select字段中选择的字段列表的数目与数据类型相匹配；数据类型不考虑长度，但是需要比被选择的字段长度长或相等；

where后面是该操作执行哪行或哪些数据的判断条件，即数据查询的过滤条件；

数据查询

select * from stu  //查询stu表中所有数据
select name,id from stu  //查询stu表中列名为name和id的数据
select name as n,id from stu  //查询stu表中列名为name和id的数据并给name取别名n

下图为第三个查询语句的查询结果；其中as可以使用空格键代替即省略

create table student(
	id char(36) primary key,
	name varchar(8) not null,
	age int(3) default 0,
	mobile char(11),
	address varchar(150)
)
insert into student 
values ('9b4435ec-372c-456a-b287-e3c5aa23dff4','张三',24,'12345678901','北京海淀');
insert into student 
values ('a273ea66-0a42-48d2-a17b-388a2feea244','李%四',10,'98765432130',null);
insert into student 
values ('eb0a220a-60ae-47b6-9e6d-a901da9fe355','张李三',11,'18338945560','安徽六安');
insert into student 
values ('6ab71673-9502-44ba-8db0-7f625f17a67d','王_五',28,'98765432130','北京朝阳区');
insert into student 
values ('0055d61c-eb51-4696-b2da-506e81c3f566','王_五%%',11,'13856901237','吉林省长春市宽平区');

为了便于理解下文中的内容，我们创建一个student表，插入5条数据；

           select * from student where name like'%三' 

%：匹配多次或0次；即我们不确定的一段字符或字节都可以使用%表示；

        select * from student where name like'_三' 

_：仅匹配一次；即可以充当一个我们模糊查询的字符或字节

问题来了，当我们需要查询的字段中还有%或者_怎么办，这时候就需要使用escape取消%和_字符的通配符特性了；

select * from student where name like'%A%%' escape 'A'

_取消通配符格式同%；注意：escape后面只能是单个字母；escape后面可跟字母、@、$、#等特殊字符；

select * from student where age='11' and address like '吉林%'   //逻辑与查询
select * from student where age='11'or address like '吉林%'  //逻辑或查询
select * from student where age between 11 and 24  //and范围查询；包括11和24
select * from student where age in(11,10,24)  //逐一匹配括号中的数值并查找符合匹配条件的数据
select * from student where address is null  //查询address值为null的数据
select * from student where address is not null  //查询address值不为null的数据

注意：

• between and 查询小值必须放在前面，大值放在后面，不然查询没有结果；

• in 是符合括号中的条件逐一匹配，不是符合每个条件，相当于 age=11 or age=10 or age=24

• 判断是否为null不能用=，即 is null 不能写成 =null；is not null 不能写成 ！=null

• 范围查询还有>、>=、<、<=、=、！=

order by 对查询结果进行排序：select * from student where 1=1 order by age desc,mobile asc

order by 关键字需要放在最后，desc 后缀降序排序，asc（也是默认即不写）升序排序；多个排序依据同时出现时须使用，隔开，并且排序是在前一个依据相同的时候后一个依据才会发挥作用；

select distinct(age) from student  //对age进行去重并查询
select distinct(age),name from student  //对age和name同时去重并查询

distinct 是去重查询函数，括号中只能有一项属性，若括号后还有其他属性，则要求括号后的列属性也相同才能实现去重；

算数表达式查询，支持+、-、*、/，不支持取余%（因为作为通配符使用了），取余使用mod（a,b）

select age+1 as age from student  //as取别名，可省略
select mod(1,3) from dual
select mod(1,3)

其中表dual不存在，是MySQL数据库中为使语句完整的伪表，可以省略，因为是默认的，下文中很多代码都省略了from dual；

为了下文的介绍便于理解我们将上个student表删除，再创建一个student表并为其添加4条数据；

create table student(
id char(1) primary key,
name varchar(8),
sex char(2) default '男' ,
age int(3) default 0
)
 
insert into student values ('1','王明','男',18);
insert into student values ('2','孙丽','女',17);
insert into student values ('3','王明','男',27);
insert into student (id,sex,age) values ('4','男',27);

select length('我喜欢一个姑娘')   //值为21
select char_length('我喜欢一个姑娘')  //值为7

length返回的是字节长度，char_length返回的是字符长度，在utf8编码中一个汉字等于三个字节；

select concat(id,'-',name,'-',sex,'-',age) from student //不显示列属性有为null的数据
select concat_ws('-',name,id,sex,age) from student   //显示列属性有为null的数据

除所说的两个区别外，这两个查询语句返回的结果完全相同；

select trim('   hello world   ')  //trim去除字符两端的空格，但不去除字符中间的空格
select substring('hello world',7)  //从第7位开始截取字段
select substr('hello world',7)  //从第7位开始截取字段
select substring('hello world',7,4)  //从第7位开始截取字段，连续截取4位
select substr('hello world',7,4)  //从第7位开始截取字段，连续截取4位
select replace('hello world','o','A')  //替换字段，将字段中的o全部替换成A
select reverse('hello world') //反转字段
select strcmp('a','d')  //两个字符串相同返回0，前者大返回1，后者大返回-1
select round(1.58,0),round(1.58),round(1.298,1)  //四舍五入保留0，0，1位小数
select truncate(1.58,0),truncate(1.298,1)  //截取字段，要求截取位之后的数全部舍去

注意：substring和substr作用完全相同；数据库和Java字段位数下标不同，数据库从1开始，Java一般从0开始；

select now()  //获取当前时间
select date_format(now(),'%Y%m%d%h%i%s')   //获取当前时间并转换成指定格式，这里结果是：20191118081556

select convert(now(),char(10))//convert(value,type)将获取的值转换成指定格式（类型），这里是将获取的时间转成10个字符：2019-11-18

if(expr1,expr2,expr3)； select if(name is NULL,'未知',name) from student 查询name项属性，不为null则返回name值，为null返回‘未知’；

•

ifnull(expr1,expr2)；  select ifnull(name,'未知') from student 同上，查询结果同上

select avg(age) from student  //求age列表的平均值
select max(age) from student  //求age列表的最大值
select min(age) from student  //求age列表的最小值
select sum(age) from student  //求age列表的总值
select count(age) from student  //求age列表的行数

注意：count、max、min函数操作的数据的数据类型可以是char、varchar、int和date，但不能为clob；avg、sum仅能用在数字类型的数据上；

group by 用于数据分组，后面跟分组的依据；

          select sex,count(id) from student where 1=1 group by sex   查询student表中男女的数量

select sex from student where 1=1 group by sex  查询student表中的性别

       select sex,count(id) from student where 1=1 group by sex order by sex  查询男女的数量并按性别升序排序

该查询执行的顺序：查询符合where条件的数据->按照sex分组->统计每组的数据->按照sex升序排序

注意：使用group by，查询结果列要么是分组依据列，要么是聚合函数列（多行操作的函数，上例中的count函数就是），不允许有其他列，否则查询无果或查询结果无意义；

where（条件查询的关键字）后面不能跟多行函数（聚合函数），有时我们又需要，因此有了having关键字；

      select name from student where 1=1 having count(name)>1   查询重复的名字并返回人数