JAVA新手入门04～MYSQL基础篇


JAVA基础
作者: 双king
浏览量:2次

这篇文章主要讲解一下基础MYSQL，纯粹为新人准备的，老手可以直接略过。在真实的项目中，数据一般都是要落地到数据库进行存储的，例如你在购物网站购买了一个手机，那么购物网站需要把你购买手机的时间、价格、订单号、支付信息等存储起来，如果有积分，还会记录一下积分信息。明白了数据库的作用，我们再来看看MYSQL这个关系数据库的一些基本操作。

#### 这一篇我们将会讲解以下内容：
  • DDL：创建表、删除表、增加列等修改表的结构。
  • DML：新增、删除、修改、查询，俗称的增删改查，也被人戏称为CRUD，如果你刚开始工作，一般都是干CRUD的工作，如果你已经多年经验，绝对不要还只是CRUD。
  • INDEX：索引相关

基本概念

  • 数据库 存储数据的一个软件，mysql是其中一种，你可以把数据库想象成1个Excel。
  • 关系型数据库 数据库的数据都是规定好的格式。
  • SQL 规定好格式和语法的语句，可以从数据库中查询指定的数据，例如根据订单号查询订单信息。
  • 表 数据是存放在表中的，可以想象成1个Excel表的1个sheet。
  • 列 列是存在于表中的，可以想象成Excel中的sheet的一列，关系型数据库中每一列数据的类型都是相同的，不可以不同。

DDL

假如我们创建1个表，存储学生的基本信息，有学号、姓名、年龄3个列。

1、创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
# 创建表
CREATE TABLE `student` (
  `num` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(10) DEFAULT '' COMMENT '姓名',
  `age` smallint(11) DEFAULT 0 COMMENT '年龄',
  PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息';

#查看表的建表语句 会返回上述内容
show create table `student`;

#查看表的结构
desc `student`;
  • create table 是固定写法，记住即可。
  • student 是表的名字。
  • 左括号是固定写法，后面有匹配的右括号
  • num 是列的名字
  • NOT NULL表示这一列必须有数据，不能是空的，如果不加NOT NULL，也可以，表示数据可以是空的
  • AUTO_INCREMENT 表示这一列的值，如果插入数据时候不指定，数据库会自动生成，而且会自动创建索引，且表示这一类是主键，主键的值是不可以重复的，例如表中有一个人的学号是101，那么其他学生的学号都不能是101，不能插入第二条101的数据
  • COMMENT ‘学号’ 是这一列的备注
  • ENGINE=InnoDB 表示数据库的引擎是InnoDB，记住即可
  • DEFAULT CHARSET=utf8 表示数据的编码是utf8
  • COMMENT=’学生信息’ 是表的备注

2、删除表

1
2
3
4
# 删除表中的数据，并且删除表的结构
drop table `student`;
#只删除表中的数据，保留表的结构
truncate table `student`;

3、增加列 ，假如我们增加一列性别

1
alter table `student` add column `sex` smallint(6) NOT NULL DEFAULT 0 COMMENT '0女生 1男生';

DML 增删改查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# 增加一条记录，学生学号是数据库自动生成的，可以不设置
#这2条插入语句都可以
insert into `student` (`num`,`name`,`age`,`sex`) values (1,'小明',10,1);
insert into `student` (`name`,`age`,`sex`) values ('小明',10,1);

# 删除学号是1的学生信息
delete from `student` where num = 1;

# 修改小明的年龄为11岁
update `student` set age = 11 where name = '小明';

# 如果有2个人，名字都是小明，那么就会把2个小明的年龄都修改为11岁，所以可以根据学号来修改，
# 学号是唯一的，不会重复
update `student` set age = 11 where num = 1;

#可以同时修改2个列的值，例如把年龄修改为11岁，性别修改为男（用1表示）
update `student` set age = 11,age=1 where num = 1;

#查询学号1的学生信息，只会返回1条记录 *星号表示查询所有的字段
select * from `student` where num = 1;

#只查询名字和性别，不查询所有
select name,sex from `student` where num = 1;

#查询名字是小明的学生信息，可能会返回多条记录
select * from `student` where name = '小明';

#查询名字是小明，性别是男的学生信息
select * from `student` where name = '小明' and sex = 1;

#查询名字是小明的同学的人数，一下这2个语句都可以，推荐使用count(*)这种方式。
select count(*) from `student` where name = '小明';
select count(num) from `student` where name = '小明';

索引

索引你可以理解为书的目录，通过目录，可以快速知道内容的页数。通过索引，可以快速找到数据所在磁盘的位置。索引的结构，MYSQL用B+树来存储。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 创建索引 一般来说 索引可以在1个列上创建索引，也可以在多个列上创建索引
create index idx_name on student
(
   name
);

create index idx_name_sex on student
(
   name,
   sex
);

# 要查看索引是否生效，可以使用如下语句
explain select * from `student` where name = '小明';

explain的返回值如下：

mysql02

主要看possible_keys 和rows是否是null，这里可以看到，使用到了idx_name这个索引，我们再执行1个不会命中所以的SQL

explain select * from student where sex = 1;

explain的返回值如下：

image-20201205123342460

可以看到没有用到索引，type的值是ALL，表示这条查询语句会扫描全表，我们日常开发中，要尽量避免全表扫描，尤其是表的数据量很大的时候，例如超过了1000W条记录，100W条记录全表扫描也是很恐怖的事情。

其它总结

  • 关键字尽量大写，不大写也无所谓，mysql不区分大小写（但是可以设置区分）。
  • 每条命令最好用英文分号结尾。
  • 操作符号可以是 num = 1 或者 num <> 1(不等于1) 或者 num >= 1 或者 num <= 1或者 num between 1 and 100
  • 如果要查询名字里有明这个字的，例如，张明、李明、吴明等，可以使用like

select * from student where name like ‘%明%’，这样就可以了

  • 如果要查询学号1和2的学生，可以使用以下2种情况。

    1
    2
    select * from student where num = 1 or num = 2;
    select * from student where num in (1,2);

    这一篇只是简单的讲解了一下SQL的用法，下一篇我们会讲一些稍微有点难度的。