MySQL数据库的基本操作

字段的数据类型

• 第一种整数类型，如下所示：
  image.png
• 注意⚠️：MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度，例如int(4)；
• 第二种浮点数类型，如下所示：

image.png

• 第三种字符串类型，如下所示：

image.png

• char与varchar类型相似，均用于存储较短类型的字符串，主要的不同之处在于存储方式，char类型长度固定，varchar类型的长度可变；

• 第四种日期和时间类型，如下所示：

image.png

• 时间戳的取值范围可以更小；
• 时间戳的时间可以根据时区来显示；
• 时间戳类型可使用current_timestamp来获取系统的当前时间；
• 当时间戳类型的字段没有赋值，或者被赋值为null，MySQL会自动将字段赋值为系统当前的日期时间；

创建数据库表

• 利用Navcat这款工具软件，通过写SQL语句的方式，进行常见的数据库表的相关操作；

-- 创建数据库表
create table t_student(
 sno int(6),
 sname varchar(10),
 sex char(1),
 age int(3),
 enterdate varchar(10),
 email varchar(15)
);

-- 查看表结构
desc t_student -- describe

-- 查看表数据
select * from t_student

-- 查看建表语句
show create table t_student 

• 在Navcat中的操作如下所示：

image.png

数据的增删改查

-- DML语言
-- 查询表数据
select * from t_student

-- insert
-- 1406 - Data too long for column 'email' at row 1 数据宽度越界了 所以报错
insert into t_student values(1,"zhangsan","男",25,'1999-08-12',"zhangsan@qq.com");

-- 可以加入相同的数据 需要设置一定的约束 才能避免插入相同的数据
insert into t_student values(1,"zhangsan","男",25,'1999-08-12',"zhangsan@qq.com");

-- 不区分双引号和单引号 
insert into t_student values(2,'lisi','男',34,"1990-09-23",'li@qq.com');

-- 日期有多种写法
insert into t_student values(3,'wangwu','男',34,"1990/09/23",'wangwu@qq.com');

-- 1136 - Column count doesn't match value count at row 1 数据与表字段不匹配
insert into t_student values(4,"zhaoliu","女");

-- 添加记录时 可给指定的字段赋值 
insert into t_student (sno,sname,sex,email) values (5,"wangba","男","wangba@qq.com");

-- update
-- 修改一个字段
update t_student set sex = '女' where sno = 2;

-- 同时修改多个字段
update t_student set age = 30,sname = "wangwu2",sex = '女' where sno = 3;

-- delete
-- 关键字,表名,字段名是不区分大小写的
delete from t_student where age = 34;

• 在Navcat中的操作如下所示：

image.png

表结构的操作

-- DDL语句 create alter drop
-- DML语句 insert update delete 

-- 查询数据
select * from t_student

-- 查询表结构
desc t_student

-- 修改表的结构
-- 增加字段 
-- 新增的字段 默认在最后 
alter table t_student add classname varchar(10);

-- 可设置新增字段 在第一列
alter table t_student add score double(5,2) first

-- 可设置新增字段 在email字段的后面
alter table t_student add score double(5,2) after email;

-- 1264 - Out of range value for column 'score' at row 3 score数值越界
update t_student set score = 1234.3333 where sno = 3;

-- 添加成功 double(5,2) 5表示数的总位数 2表示保留的小数位
update t_student set score = 123.3333 where sno = 3;

update t_student set classname = "java001";

-- 修改字段
-- 修改字段的取值规则
alter table t_student modify score float(4,1);

-- 修改字段名 score --> score1
alter table t_student change score score1 double(5,2);

-- 删除字段
alter table t_student drop score;

-- 删除表
drop table t_student

• 新增字段，修改字段，删除字段，删除表均是表结构的操作；

表的完整性约束

• 为了防止不符合规范的数据存入数据库表中，在用户对数据进行插入，修改，删除等操作时，MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是完整性约束；
• MySQL中主要支持六种完整性约束，其中check约束是MySQL8中提供的支持，如下所示：

image.png

• 其中not null与default这两种约束是列级约束，也就是说只能加载字段(列)的后面；

• 添加约束的第一种方式：在字段后面直接加约束，如下所示：

/*
创建学生表
  字段包含学号，姓名，性别，年龄，入学日期，班级，邮箱
    学号是主键 等价于(不能为空 + 唯一)
    姓名不能为空
    性别默认值为男 只能是男或者女
    邮箱唯一
*/
create table t_student(
    sno int(6) primary key auto_increment,
    sname varchar(10) not null,
    sex char(1) default "男" check(sex = '男' || sex = '女'),
    age int(3) check(age >= 18 and age <= 50),
    enterdate date,
    classname varchar(10),
    email varchar(15) unique
);

select * from t_student

-- 1406 - Data too long for column 'sex' at row 1 sex值不满足约束
insert into t_student values(1,'zhangsan',"男1",34,"1999-10-23","Java001","zhangsan@qq.com");

-- 3819 - Check constraint 't_student_chk_2' is violated 年龄范围不满足 
insert into t_student values(1,'zhangsan',"男",12,"1999-10-23","Java001","zhangsan@qq.com");

-- 1048 - Column 'sname' cannot be null 姓名不能为空
insert into t_student values(1,null,"男",12,"1999-10-23","Java001","zhangsan@qq.com");

insert into t_student values(1,"zhangsan","男",25,"1999-10-23","Java001","zhangsan@qq.com");

-- 1062 - Duplicate entry '1' for key 't_student.PRIMARY', 主键重复 
insert into t_student values(1,"zhangsan","男",25,"1999-10-23","Java001","zhangsan@qq.com");

-- 主键可使用default或者null进行占位
insert into t_student values(default,"lisi","男",25,"1999-10-23","Java001","lisi@qq.com");
insert into t_student values(null,"wangwu","女",25,"1999-10-23","Java002","wangwu@qq.com");
insert into t_student (sname,enterdate)values("zhaoliu","1998-12-25");
insert into t_student (sname,enterdate)values("zhaoliu2","1998-12-25");

• • 添加约束的第二种方式：通过constraint；

/*
创建学生表
  字段包含学号，姓名，性别，年龄，入学日期，班级，邮箱
    学号是主键 等价于(不能为空 + 唯一)
    姓名不能为空
    性别默认值为男 只能是男或者女
    邮箱唯一
*/
-- 添加约束2
create table t_student(
  sno int(6) auto_increment,
    sname varchar(10) not null,
    sex char(1) default '男',
    age int(3),
    enterdate date,
    classname varchar(10),
    email varchar(15),
    constraint pk_stu primary key(sno),
    constraint ck_stu_sex check(sex = '男' || sex = '女'),
    constraint ck_stu_age check(age >= 18 and age <= 50),
    constraint uk_stu_email unique(email)
);

select * from t_student

insert into t_student values(1,"zhangsan","男",25,'1999-08-12',"Java001","zhangsan@qq.com");
insert into t_student values(null,"lisi","男",26,'1989-08-12',"Java001","lisi@qq.com");

insert into t_student (sno,sname,sex)values(default,"zhuliu","女");

• 添加约束的第三种方式：add constraint；

-- 添加约束3
create table t_student(
  sno int(6),
    sname varchar(10) not null,
    sex char(1) default '男',
    age int(3),
    enterdate date,
    classname varchar(10),
    email varchar(15)
);

alter table t_student add constraint pk_stu primary key(sno);
alter table t_student add constraint ck_stu_sex check(sex = '男' || sex = '女');
alter table t_student add constraint ck_stu_age check(age >= 18 and age <= 50);
alter table t_student add constraint uk_stu_email unique(email);
alter table t_student modify sno int(6) auto_increment;

• not null与default只能写在字段列的后面，不能单独拿出来书写；

外键约束

• 外键约束(foreign key)是用来实现数据库表的参照完整性的，外键约束可以使两张表紧密结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性；
• 外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或唯一约束，被依赖的表我们通常称之为父表或者主表，设置外键约束的表称之为子表或者从表；
• 例如学生表中字段class sno表示班级的编号，是班级表的主键，可称为学生表的外键，班级表是主表，学生表是从表；

-- 外键关联
-- 创建班级表
create table t_class(
  cno int(4) primary key auto_increment,
    cname varchar(10) not null,
    romm char(4)
);

select * from t_class;

-- 添加数据
insert into t_class values(null,'Java001','r301');
insert into t_class values(null,'Java002','r302');
insert into t_class values(null,'大数据001','r303');
-- 等价写法
insert into t_class values(null,'Java001','r301'),(null,'Java002','r302'),(null,'大数据001','r303');

create table t_student(
  sno int(6) primary key auto_increment,
    sname varchar(10) not null,
    age int(3),
    score double(4,1),
    classno int(4) -- 取值要参考t_class表的主键 
);

select * from t_student

insert into t_student values(null,'zhangsan',30,56.3,1);
insert into t_student values(null,'lisi',23,34.3,2);
insert into t_student values(null,'zhaoliu',23,34.3,2);
insert into t_student values(null,'wangwu',23,34.3,3);
-- 问题一：学生可以加入不存在的班级
insert into t_student values(null,'zhuliu',34,67.3,4);
-- 问题二：将班级2删除 但学生的班级信息依然存在
delete from t_class where cno = 2;

• 针对上面的问题，我们使用外键约束来解决；
• 给学生表添加外界约束，即学生表的classno字段的取值要参考班级表的主键，学生表的classno字段就是学生表的一个外键；
• 添加外键约束的第一种方案：

create table t_student(
    sno int(6) primary key auto_increment,
    sname varchar(10) not null,
    age int(3),
    score double(4,1),
    classno int(4), -- 取值要参考t_class表的主键 
    constraint fk_stu_classno foreign key(classno) references t_class(cno) -- 学生表的外键classno 是班级表的主键 外键约束
);

• 添加外键约束的第二种方案：

-- 解决方案：设置外键关联的第二种方式
create table t_student(
  sno int(6) primary key auto_increment,
    sname varchar(10) not null,
    age int(3),
    score double(4,1),
    classno int(4), -- 取值要参考t_class表的主键 
);
-- 添加外键约束 其值参考于班级表的主键
alter table t_student add constraint fk_stu_classno foreign key(classno) references t_class(cno);

• 现在添加了学生表的外键约束，那么现在：
  • 不可以添加一个学生到一个不存在的班级；
  • 不可以删除一个存在学生的班级；
• 现在有一个需求就是：如何删除一个存在学生的班级，例如删除2班，而2班中还存在学生，这就要使用到外键策略；
• -- 外键策略一：NO Action，必须先保证2班没有学生，才能删除2班 (删除学生 或者 将学生的班级编号classno设置为null)

-- 将学生的班级编号classno设置为null
update t_student set classno = null where classno = 2;
-- 删除2班学生
delete from t_student where classno = 2;
-- 最后才能成功删除2班
delete from t_class where cno = 2;

• 外键策略二：cascade级联

-- 首先删除外键
alter table t_student drop foreign key fk_stu_classno;

-- 设置外键 且设置级联操作 更新或者删除的时候 级联
alter table t_student add constraint fk_stu_classno foreign key(classno) references t_class(cno) on update cascade on delete cascade; 

• -- 外键策略三：set null 更新或者删除的时候 置空；

alter table t_student add constraint fk_stu_classno foreign key(classno) references t_class(cno) no update set null on delete set null;

• 外键策略的总结：
  • 采用哪种外键策略需视具体情况而定；
  • 朋友圈发一条动态，有点赞，留言，若动态删除了，那么点赞，留言也应被删除，那么采用级联cascade策略；
  • 班级与学生，如班级取消了，学生应该还在 指示学生所在的班级要置空 那么采用set null 策略；
  • 如果业务不清晰 可采用no action策略；

DDL与DML的补充

select * from t_student

-- 快速创建表
-- 有表结构 也有数据
create table t_student2
as
select * from t_student

create table t_student3 
as 
select * from t_student where 1=2

-- 只有表的结构
select * from t_student3

-- 部分表结构和数据
create table t_student4
as
select sno,sname,age from t_student where age = 23

select * from t_student4

-- 添加
insert into t_student4 values(3,'nima',45);
insert into t_student4 set sno = 22,sname = 'rensheng';
insert into t_student4 (sno,sname)values(33,'caonimei');

-- 删除
delete from t_student4; -- 可回滚
truncate table t_student4; -- 速度快，无法回滚

• 快速创建表，即通过一个已经创建的表，快速创建一个新表；
• delete与truncate都可删除表中的所有记录，但delete删除之后可以回滚，但truncate删除之后，不能回滚；

DQL单表查询

• 首先创建了四张表分别为t_emp，t_dept，t_salgrade，t_bonus；
• t_emp表示员工表，其结构如下：

image.png

• 其中字段deptno表示部门编号，为外键，其值参考部门表t_dept的主键值；
• t_dept表示部门表，其结构如下：

image.png

• t_salgrade表示工资等级表，其结构如下：

image.png

• t_bonus，其结构如下：

image.png

• 简单的查询语句如下所示：

-- 查询语句
-- 查询所有列的数据
select * from t_dept
select * from t_emp
-- 查询部分列的数据
select empno,ename,sal,comm,deptno from t_emp
-- 查询部分行的数据
select empno,ename,sal,comm,deptno from t_emp where sal < 2500

-- 别名
select empno 编号,ename 姓名,sal 工资,comm 补助,deptno 部门编号 from t_emp where sal < 2500
-- as可以省略
select empno as 编号,ename as 姓名,sal as 工资,comm as 补助,deptno as 部门编号 from t_emp where sal < 2500
-- 出现空格时 用单引号或双引号 引上
select empno '编 号',ename "姓 名",sal Salary,comm 补助,deptno 部门编号 from t_emp where sal < 2500

-- 算术运算
select empno,ename,sal,comm,sal*2,deptno from t_emp where sal < 2500
select empno,ename,sal,comm,sal*2,sal+comm,deptno from t_emp where sal < 2500

-- 去重
select job from t_emp
select distinct job from t_emp
select job,deptno from t_emp
-- 对所有列进行去重
select distinct job,deptno from t_emp

-- 排序 order by
select *from t_emp
-- 按主键进行排序
select * from t_emp order by empno
-- 按工资降序排列
select * from t_emp order by sal desc
-- 按工资升序排列 默认升序
select * from t_emp order by sal asc
-- 按工资升序排列 若工资相同时按入职时间 降序排列 日期时间是大小指的是数值不是早晚；
select * from t_emp order by sal desc,hiredate desc

• distinct关键字 表示去重；
• order by asc 升序，order by desc 降序；
• where语句的基本使用如下：

-- where语句
-- 关系运算符
select * from t_emp
select * from t_emp where deptno = 10
select * from t_emp where deptno < 10
select * from t_emp where deptno <= 10
-- 不等于 <>或者!=
select * from t_emp where deptno <> 10
select * from t_emp where deptno != 10
select * from t_emp where job = "clerk"
select * from t_emp where job = "CLERK"
-- binary 表示要区分大小写
select * from t_emp where binary job = "CLERK"
select * from t_emp where hiredate < "1981-12-25"

-- 逻辑运算符 and
select * from t_emp where sal > 1500 and sal < 3000
select * from t_emp where sal > 1500 and sal < 3000 order by sal
select * from t_emp where sal >= 1500 and sal <= 3000 
-- [1500,3000] 闭区间
select * from t_emp where sal between 1500 and 3000
-- and 与 && 等价
select * from t_emp where sal between 1500 && 3000

select * from t_emp where job = "salesman" and deptno = 30 order by sal

-- 逻辑运算符 or
select * from t_emp where deptno = 10 or deptno = 20
-- or 与 || 等价
select * from t_emp where deptno = 10 || deptno = 20
select * from t_emp where deptno in(10,20)
select * from t_emp where job in('clerk','manager','analyst') order by job
select * from t_emp where job = 'manager' or deptno = 30

-- 模糊匹配 % 或者 _
-- %代表 任意多个字符 
-- 关键字like 姓名包含m的
select * from t_emp where ename like '%m%'
-- _代表任意一个字符
-- 姓名第二个字母是a
select * from t_emp where ename like '_A%'
-- 姓名第三个字母是a
select * from t_emp where ename like '__A%'
-- 姓名第三个字母不是a
select * from t_emp where ename not like '__A%'

-- null
select * from t_emp where comm is null
select * from t_emp where comm is not null

-- 小括号()
select * from t_emp where job = "salesman" or (job = "clerk" and sal >= 1280)

• 涉及between，like，binary关键字；

Navicat调试移动端建立的SQLite数据库

• 在移动端不论iOS或者Android，建立的数据库都是SQLite数据库，所以利用Navicat建立连接的都是属于建立SQLite连接；

iOS端利用Navicat打开SQLite数据库

• 主要操作步骤如下：

image.png Snip20220309_11.png Snip20220309_12.png

Android端利用Navicat打开SQLite数据库

• 首先在Device File Explore工具中，在路径/data/data/你的App包名/databases/ 找到建立的SQLite数据库，如下所示：

image.png

• 右击保存，保存位置默认(/Users/liyanyan33/Documents/AndroidStudio/DeviceExplorer/Pixel_3a_API_30_x86\ \[emulator-5554\]/data/data/com.example.yyshop/databases)，注意要记住保存的位置，保存成功后，再次打开路径，然后选中SQLite数据库，右击利用Navicat直接打开即可，如下所示：

image.png image.png