1 月月更|盘点 2021|推荐学 Java——数据表操作

作者：逆锋起笔

2022 年 1 月 10 日
本文字数：5370 字
阅读完需：约 18 分钟

说明

上节内容学习了数据库 MySQL 的安装、验证、数据库管理工具、数据库的基本操作命令，还没有学习的同学可以从主页去看上一篇推送内容。

本节内容就学习有关数据库中表的操作，这其中包括 表内 和 表关联 的：创建、列数据类型、数据查询、筛选、删除、添加、修改等等操作，这块内容极为重要，重点不但需要掌握基本的 SQL 使用规则，还要掌握一些系统提供的 SQL 函数。

提示：你可以将 sql 理解为一种编程语言格式，那么这其中用到的所有标点符号都是英文的，括号都是成对出现的，这种低级错误千万别给自己养成"习惯"。

表（CRUD）

创建表语法

create table 表名(列名 列的类型（长度） 列的约束,列名2 列的类型（长度） 列的约束);

复制代码

MySQL 中列的数据类型

int：和 Java中相同

bigint ：等同于 Java 中的 Long.

char：固定长度（长度指的是字符个数），使用不当会导致空间的浪费。

varchar：可变长度（长度指的是字符个数），会根据实际的数据长度动态分配空间。

double：双精度浮点型。

float：单精度浮点型。

boolean：

date：短日期，格式：%Y-%m-%d

time：hh:mm:ss

datetime：长日期，格式：%Y-%m-%d %h:%i:%s，默认值是 null. 与之对应的获取时间函数是：now()

timestamp：YYYY-MM-DD hh:mm:ss，默认使用当前时间

text：主要用来存放文本。

blob：全称Binary Large OBject ，二进制大对象，专门用来存储图片、视频、音频等流媒体数据。

clob：全称Character Large OBject ，字符大对象，最多可存储 4G 的的字符串。比如存储一篇文章、一个说明。超过 255 个字符的都要采用 clob .

约束

约束的作用

对字段的约束，为了保证表中数据的有效性。

约束分类

主键约束：primary key
单一主键：一个字段做主键
复合主键：多个字段联合做主键（不建议使用）
主键值类型建议
int
bigint
char
不建议使用 varchar ，主键一般都是定长的，整数居多。
自然主键（推荐使用，主键尽量不要和业务数据挂钩）
业务主键（比如用学号做主键，一单业务有变化，主键相关表可能就会有问题）
外键约束：foreign key
一张表中的某个字段在另外一张表中被标记为外键约束。
被标记字段的表称为子表，另外一张表叫父表
删除数据，先删除子表，再删除父表
插入数据，先插入父表，再插入子表
语法格式：

    foreign key(列名) references 父表表名(父表中要被添加的列名);

复制代码

外键（父表中的列）不一定要是主键，但至少具有 unique 约束；外键可以为 Null .
唯一约束：unique 约束的字段不能重复，但可以为 NULL .

  -- 多个列联合约束 这种约束叫做 表级约束  create table 表名(  id int not null unique, -- 非空唯一联合约束  name varchar(50),  address varchar(255),  unique(name,address) -- 表级约束  );

复制代码

当一列同时使用 not null 和 unique 约束时，该字段自动成为主键字段。（Oracle 不是这样的）

非空约束：not null
检查约束：check（Mysql 不支持，oracle 支持）

举例，创建学生表

   mysql -uroot -p密码

复制代码

进入指定数据库（如果没有数据库，则利用前面的创建数据库语句进行创建）

   use 数据库名字;

复制代码

这两步是必须的，我们的表都是在库的基础上才有的，所以在创建表之前我们需要选对要使用的数据库。

创建表

通用语法格式：

create table t_student(  sid int primary key,  sname varchar(30),  ssex int default 0, -- default 指定默认值  sage int);

复制代码

表名建议以 t_ 或 tbl_ 开头，可读性强。
表名和列名（字段名）都属于标识符。

复制表（了解即可）

create table 表名 as select * from 已存在的表名;

复制代码

这样原表中的数据也会保留。

查看表

查看当前数据库下所有表：

show tables;

复制代码

查看表的创建过程

show create table 表名;

复制代码

查看表结构

desc 表名;

复制代码

插入数据

通用语法格式：

-- 插入一条记录insert into 表名(列名1,列名2...) values (对应列名值1,对应列名值2...);-- 一次插入多条记录insert into 表名(列名1,列名2...) values(对应列名值1,对应列名值2...),(对应列名值1,对应列名值2...);

复制代码

列名要一一对应
列的数据类型也要一一对应
列的顺序可以调整
如果插入的某列值为空，则默认给 null 值（创建表设置了默认值的列除外）

插入日期

mySQL 的日期格式：
%Y年
%m 月
%d 日
%h 时
%m 分
%s 秒
str_to_date('日期字符串','日期格式')
比如数据表中员工的入职日期字段 hiredate 的类型是 date ，现在要新增一名员工，sql 如下：

  insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno,`language`)  values  (7021,'耶律小乔','前台',7876,str_to_date('2021-12-01','%Y-%m-%d'),3566,0,40,'English');

复制代码

如果正好你传入的日期字符串满足格式 %Y-%m-%d 那么可以不用显示地写该函数，MySQL 会自动转换。

该函数的参数日期和格式，只需要满足对应格式就行，如下也是可以成功执行的：

  insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno,`language`)  values  (7022,'小乔','客服',7876,str_to_date('12-02-2021','%m-%d-%Y'),2566,50,40,'普通话');

复制代码

date_format(date 类型的列名,'日期格式')
多用在查询时，将 date 类型的结果按照给定的格式展示出来。

  select e.ename, date_format(e.hiredate,'%m-%d-%Y') as hiredate from emp e;

复制代码

结果：

同样，如果你的日期都是按照 mysql 的标准日期格式存入的，那么查询的时候如果没有特殊要求，可以直接查询。

更新记录

// 更新记录UPDATE 表名SET 列名1=列1对应的值,列名2=列2对应的值WHERE 更新的条件;

复制代码

修改表中的列、列名、表名

添加列（add）

  alter table 表名 add 列名 列的类型 列的约束  alter table student add chengji int not null;

复制代码

修改列（modify）

  alter table 表名 modify 列名 列的类型（长度）;

复制代码

修改列名（change)

  alter table 表名 change 原列名 新列名 列名的类型（长度）;

复制代码

删除列（drop）

  alter table 表名 drop chengji;

复制代码

修改表名（rename）

  rename table 原表名 to 新表名;

复制代码

修改表的字符集

  alter table 表名 character set 字符集名称;

复制代码

删除表中数据

delete from 表名;

复制代码

表还在，只是数据被清空了。缺点 占用的硬盘空间不会释放；优点 这种删除方式支持回滚。

truncate table 表名;

复制代码

这种方式适合删除表中的数据比较多（比如上万条或更多）的情况，速度快，不支持回滚，表还在，但无任何数据。

删除表

drop table if exists 表名; -- 如果存在就删除，否则删除一张不存在的表会报错

复制代码

不会删除表中的数据，只是把表删除了。

表内常用 SQL

1，插入数据

insert into 表名(列名1，列名2，列名3...) values (列名1对应的值，列名2对应的值，列名3对应的值);

复制代码

批量插入

insert into student values(103,'lunzima',1,18),(104,'houyi',0,20),(105,'hanbin',1,22);

复制代码

批量插入效率高于单条插入，但是批量插入其中一条如果出错，可能引起同批插入的其他条数据错误。

注：

如果是全列名插入，则可以省略表名后的列名不写，例如下面这样：

insert into student values(100,'yasuo',0,25);

复制代码

设置了主键的列，插入时要保证主键不重复。
插入中文乱码解决方法
在 MySql 的安装目录（比如我的是：C:\Program Files\MySQL\MySQL Server 5.5）下，找到my.ini文件，打开后编辑default-character-set=gbk这句代码即可，默认是utf8
然后在命令行重新登录账户，操作即可。
部分列插入时，列名不能省略。

  insert into 表名(要插入的列1，列2...) values(对应列的值...);

复制代码

2，查询表中记录

select * from 表名;

复制代码

查看表中指定列的数据

select 列名1,列名2 from 表名;

复制代码

别名as查询

  select 表名的别名.列名1,表名的别名.列名2 from 表名 as 表名的别名;

复制代码

别名as是可以省略的。也可以给列名加别名，像下面这样

  select 表名的别名.列名1 列名1的别名,表名的别名.列名2 列名2的别名 from 表名 表名的别名;

复制代码

去重查询

  // 查询表中某列数据，并去掉重复值  select distinct 列名 from 表名;  // distinct 只能出现在多列的最前面，表示这几列联合去重  select distinct 列名1, 列名2 from 表名;

复制代码

select 运算查询

  select *,列名*0.85 from 表名;

复制代码

这里的运算符可以是+,-,*,/。增加的列也可以添加别名；增加的列仅仅是在查询结果上显示，不会真正改变表中的结构。

where后的条件写法
关系运算符：>,>=,<,<=,!=,<>

  select * from 表名 where 列名 关系运算符 限定条件的值;  // 比如这样  select * from product where price <> 500;

复制代码

其中，!=不是标准的 SQL 语法，<>才是标准的不等于。

sql 中要查询某个 null 值，要使用 is null ，而不是= null ；相反，要查询不为 null，那么使用is not null .

逻辑运算符：and,or,no

  // 举例  select * from product where price > 10 and price < 2000;  // 上面这句和下面这句等价  select * from product where price between 11 and 1999;    select * from product where price = 88 or price = 99;

复制代码

between...and... 语句包含左右两边的值，且只能是左小右大。
and 和 or 同时出现，前者优先级高，如果要确定优先级，使用 () 即可。

模糊查询:like

_：代表一个字符。
%：代表多个字符。

  // 查询商品名字中带有'代码'两个字的所有商品  select * from product where pname like '%代码%';  // 查询商品名字中第二个字符是'一'的所有商品  select * from product where pname like '_一%';  // 查询商品名字以'电'开头的所有商品  select * from product where pname like '电%';  // 查询商品名字中带有下划线的所有商品  select * from product where pname like '%\_%';

复制代码

如果要查询的列名中本身有_，那么要把列名中带有_的所有记录查出来，这个时候需要用到转义符号 \

在某个范围获得值：in

  // 查出商品编号为 3 和 5 的所有商品  select * from product where cno in (3,5);  // 查询商品编号不为 3 和 5 的所有商品  select * from product where cno not in(3,5);

复制代码

in 相当于多个 or ，并不是区间的意思。

排序查询：order by
最后执行，对select的结果进行操作。
asc：升序（默认排序方式）
desc：降序

  // 按照某列进行排序  select * from 表名 order by 列名 desc/asc;  // 结合 where 条件的结果，在进行排序  select * from 表名 where 列名 条件运算符 条件值 order by desc/asc;

复制代码

如果排序列有相同的值，那么可以指定按照另外列排序，语法格式如下：

  select * from 表名 order by 列名 desc,列名 asc;

复制代码

聚合函数
sum()：求和
avg()：求平均值
max()：最大值
min()：最小值
count()：统计数量

  // 求和  select sum(要求和的列名) from 表名;  // 求平均值  select avg(要求平均值的列名) from 表名;  // 求最大值  select max(要求最大值的列名) from 表名;  // 求最小值  select min(要求最小值的列名) from 列名;  // 统计数量 只统计不为null的记录条数  select count(表中任意不存在Null值的列或者直接写`*`) from 表名;

复制代码

注意：聚合函数不能直接跟在where后面。

  // 比如：查出价格大于平均值的所有商品  select * from product where price > (select avg(price) from product);

复制代码

分组查询：group by
将表中某列值相同的记录放在一起，称为一组。
sql 语句的执行顺序：

  select ... from ... where ... group by ... order by ...

复制代码

顺序：from -> where -> group by -> select -> order by

  // 按照某列去分组，对于列名相同的记录默认会显示排在前面的  select * from 表名 group by 列名;  // 按照某列排序，并统计该列的数量  select 列名,count(列名) from 表名 group by 列名;    // 两个字段联合一起查询，示例：查找各部分，各个岗位工资最高的记录  select deptno,job,max(sal) from emp group by deptno,job;

复制代码

having
条件筛选。出现在分组之后，其后可以接聚合函数。where关键字出现在分组之前，其后不可接聚合函数。

  // 比如：查询商品表中，按照商品编号分组显示每组的平均价格，并查询平均价格大于 60元的所有商品。  select cno,avg(price) from product group by cno having avg(price) > 60;    // 以下两句sql作用相同，其中后者效率更高。找出各部门 最高薪资大于3000的  select deptno,max(sal) from emp group by deptno having max(sal)>3000;  select deptno ,max(sal) from emp where sal > 3000 group by deptno;

复制代码

3，删除表中记录

删除指定某条记录

  delete from 表名 where 条件;  // 例如  delete from student where sid=105;

复制代码

注：如果不指定条件，则会将表中的数据一条一条全部删除。

truncate 和 delete from 表名; 删除表中数据有何区别？
前者是将表直接删除，然后重新创建表，表中无数据。后者是一条一条删除表中所有数据。
在数据量较少的情况下，后者效率高；反之，前者高。

4，更新表中记录

更新某条记录

  update 表名 set 要更新的列名1=列的值1,要更新的列名2=列的值2 where 条件;

复制代码

比如这样：

  update student set sname='寒冰',sage=23 where sid=105;

复制代码

更新所有记录的某些列

  update 表名 set 要更新的列1=值1,要更新的列2=值2;

复制代码

总结

表的操作，根据个人职位和功能需求来定，一个完整体系表的构建是一个具体业务的逻辑体现。
大多开发者在表内和表与表之间进行操作的时候多，其中最主要也是用的做多的操作是查询和筛选。

小编特意创建了一个公众号：推荐学java，分享与java相关的内容，并以原创为主，欢迎大家微信搜索 javaFirst 关注（关注即送精品视频教程），一起学 Java！

发布于: 刚刚阅读数: 2

原文链接:【http://xie.infoq.cn/article/df9fc1e7c4218d5d8f972f0c3】。

逆锋起笔

关注

公众号「逆锋起笔」主理人 2018.07.31 加入

程序视角，转射人生！

发布

暂无评论

创作场景

1 月月更|盘点 2021|推荐学 Java——数据表操作

说明

表（CRUD）

创建表语法

MySQL 中列的数据类型

约束

约束的作用

约束分类

创建表

查看表

查看当前数据库下所有表：

查看表的创建过程

查看表结构

插入数据

插入日期

更新记录

修改表中的列、列名、表名

删除表中数据

删除表

表内常用 SQL

1，插入数据

2，查询表中记录

3，删除表中记录

4，更新表中记录

总结

逆锋起笔

评论