当前位置:   article > 正文

day39_mysql

day39_mysql

今日内容

0 复习昨日
1 DML
2 约束
3 DQL

0 复习昨日

1 什么是数据库(Database)? 用来组织,存储,管理数据的仓库

2 什么是数据库管理系统(Database Management System-DBMS)? 用来管理数据库的一个软件

3 数据库分类

  • 关系型数据库,Oracle,Mysql,SqlServer,DB2
  • 非关系数据库,Redis,ElasticSearch,MongoDB,HBase

4 SQL

  • DDL
  • DML
  • DQL
  • DCL

5 DDL 数据定义

  • 创建数据库 create database 库名
  • 删除库 drop database 库名
  • 创建表 …
  • 删除表 drop table 表名
  • 修改表列名 alter table 表名 change 旧列名 新列名 数据类型(长度);
  • 添加表中一列 alter table 表名 add 列名 数据类型(长度);
  • 删除表中一列 alter table 表名 drop 列名

6 DML 数据操作

  • 插入

1 DML

DML 数据操作语句

  • 插入 insert
  • 更新 update
  • 删除 delete

1.1 更新

语法

update 表名 set 字段 =[, 字段2 =2, ... ] [where 字段 =];
-- [, 字段2 = 值2, ... ] 是指,可选的,可以同时修改多个列的值
-- [where 字段 = 值] 是指,可选的,加上是指过滤,只更新符合条件的数据
--                            不加,就是更新全表数据, 不推荐!!
  • 1
  • 2
  • 3
  • 4
/*
 update 表名 set 字段 = 值;
 update 表名 set 字段 = 值 , 字段2 = 值2 ;
 update 表名 set 字段 = 值 , 字段2 = 值2 where 字段 = 值;
 推荐使用带条件的.条件有很多 = > < >= 等等
*/
-- 更新学生的id为2的成绩为100
update stu set score = 100 where sid = 2;
-- 同时更新多个字段
update stu set score = 100, birthday = '1970-01-01' where sid = 3;

-- 同时更新多条数据
update stu set score = 100 where sid >= 5;
-- 不加条件,更新全部数据
-- 不推荐使用,将来工作中数据表中的数据100w以上
update stu set score = 0;

-- 字段是可以做运算
-- 更新贝贝的年龄+2岁
update stu set age = age + 2 where sname = '贝贝';
update stu set age = age + 100;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

1.2 删除

语法

delete from 表名;  -- 删除全表数据
delete from 表名 where 字段 =; -- 只删除符合条件的数据
  • 1
  • 2
-- 删除id为8的学生数据
delete from stu where sid = 8;

delete from stu where sid >= 5;
-- 删除全部[慎用]
delete from stu;
/*
 一般实际开发中,都是逻辑删除,其实是没有进行物理删除
执行都是更新.
 在设计表时,会设计一个状态字段,例如status
  status = 1 活跃
	status = 2 不活跃
	status = 3 僵尸号
	status = 1 用户 2 vip用户 3 注销用户
*/
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

1.3 delete和truncate的区别[面试问]

-- 删除全部[慎用]
delete from stu;
-- 删除全表
truncate table stu;
  • 1
  • 2
  • 3
  • 4
deletetruncate
语句类型DMLDDL
原理逐条删除数据删除表结构和数据,重建表
空间删除数据在内存中有删除后碎片很干净,没有空间碎片
效率
恢复可以恢复数据不能恢复
自增不影响自增顺序影响,从1开始

2 约束

约束,即限制,就是通过设置约束,可以限制对数据表数据的插入,删除,更新


约束设置的语法,大部分是

create table 表名(
 字段 数据类型(长度) 约束,
 字段 数据类型(长度) 约束
);
  • 1
  • 2
  • 3
  • 4

2.1 数据类型

其实数据类型也是一种约束,例如设置id列为int类型,那就不能乱给id设置字符串或者日期等数据

2.2 主键约束

主键(primary key)约束非常重要,以后开发中基本上每张表都要有主键约束,作用是设置了主键约束的列,有以下效果

  • 不能为空
  • 不能重复

一般主键是给id设置的

设置主键方式有三种:

  • 在建表时给列直接指定
  • 在建表语句的最后指定某一列是主键
  • 给以建好的表修改设置主键
  • Navicat界面操作
-- ================= 主键 ================
-- 方案1,在建表时指定
create table t1 (
	id int(11) primary key, -- 主键
	name varchar (20)
);
-- 主键不能为空,报错 Field 'id' doesn't have a default value
insert into t1 (name) values('aaa');
-- 主键不能重复,报错Duplicate entry '1' for key 'PRIMARY' 
insert into t1 (id,name) values(1,'aaa');
insert into t1 (id,name) values(1,'aaa');
-- 方案2,在建表语句的最后指定主键
create table t2 (
 id int(11),
 name varchar(20),
 age int,
 primary key(id)  -- 指定主键
);
insert into t2(name,age) values ('cc',18);
insert into t2(id,name,age) values (1,'cc',18);
insert into t2(id,name,age) values (1,'cc',18);

-- 方案3,对已建成的表设置主键
create table t3(
 id int,
 name varchar(20)
);
insert into t3(name) values ('cc');
-- 修改表,设置主键
alter table t3 add primary key (id);
  • 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

2.3 自增约束

自增(auto_increment)约束,主要是配合主键使用,防止主键为空,重复

-- ================= 自增约束 ================
create table t4(
 id int(11) primary key auto_increment,
 name varchar(20)
);
insert into t4(id,name) values (1,'aaa');
-- 当没有给主键设置值是,自增会自动赋值
insert into t4(name) values ('bbb');
-- 还可以主动赋值
insert into t4(id,name) values (4,'ddd');
-- 当没有给主键设置值是,自增会自动赋值,自动根据上个id自增1
insert into t4(name) values ('eee');
delete from t4 where id = 5;
-- 删除数据不影响自增的顺序
insert into t4(name) values ('fff');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

在这里插入图片描述

2.4 唯一约束

唯一(unique)约束,设置了唯一约束的列,的值不能重复

-- ================= 唯一约束 ================
create table t5(
 id int,
 name varchar(20) unique -- 唯一约束
);
insert into t5(name) values('aa');
-- 报错,name列的值重复, Duplicate entry 'aa' for key 'name'
insert into t5(name) values('aa');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

2.5 非空约束

非空(not null)约束,设置了非空约束的列的值不能为空

-- ================= 非空约束 ================
create table t6(
 id int,
 name varchar(20) not null -- 非空约束
);
-- name不能没有值
insert into t6 (id) values (1);
-- 给name赋值
insert into t6 (id,name) values (1,'aaa');
-- 只是不能为空,可以重复
insert into t6 (id,name) values (2,'aaa');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

2.6 默认值

默认值(default),给列设置默认值约束后,如果该列在插入数据时没有给值,就自动赋值默认值

-- ================= 默认值约束 ================
create table t7 (
 id int,
 sex char(1) default '男' -- 默认值
);
insert into t7 (id) values (1); -- 不给值时,默认填充男
insert into t7 (id,sex) values (2,'女');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

2.7 外键约束

外键,是多表之间接的一种关联关系的一种限制.

语法

constraint 外键名 foreign key (当前表中的列名) references(主键);
  • 1

设计订单表和商品表,订单表的数据要关联商品表数据

-- 商品表
create table tb_goods(
 gid int primary key,
 gname varchar(20),
 descr varchar(20)
);

-- 订单表 (订单表关联了商品表)
create table tb_order(
 oid int primary key,
 order_time datetime,
 gid int,
 -- 设置外键
 constraint fk_order_goods foreign key(gid) references tb_goods(gid)
);
/*
 被引用的表称为父表 parent , tb_goods
 引用别人的表称为子表 child , tb_order
*/
-- 给父表随便插入数据
insert into tb_goods values (2,'键盘','敲代码没有bug');
-- 给子表随便插入数据不行!! 这个数据得是父表中有的才行
insert into tb_order values (1,'2022-11-11',1);
-- 子表可以删除数据
delete from tb_order where oid = 1;

-- 父表被引用的数据不能删除
delete from tb_goods where gid = 2;
delete from tb_goods where gid = 1;
  • 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

在这里插入图片描述

  • RESTRICT:如果想要删除父表的记录时,而在子表中有关联该父表的记录, 则不允许删除父表中的记录;
  • NO ACTION:同 RESTRICT,也是首先先检查外键;
  • CASCADE:父表 delete、update 的时候,子表会 delete、update 掉关联记录;
  • SET NULL:父表 delete、update 的时候,子表会将关联记录的外键字段所在 列设为 null,所以注意在设计子表时外键不能设为 not null;

2.8 练习

自己创建表,设计字段,把所有约束都试一遍

3 DQL

DQL 主要指查询语句,有查询单表数据,也有查多表数据表,今天主要学习单表查询

  • 基本查询
  • 条件查询
  • 模糊查询
  • 排序查询
  • 聚合查询
  • 去重查询
  • 分组查询
  • 限制查询

3.1 数据准备

将发的stu.sql导入到Navicat中

在这里插入图片描述

3.2 基本查询

select 字段1,字段2,… from 表名;


查询返回的是一张虚拟表,查询对原表数据没有任何影响,默认查询的全表数据

-- 基本查询
-- 查询所有列
select sid,sname,age,sex,score,cid groupLeaderId from stu;
-- 查询所有列,在测试,练习时可以使用*代替
select * from stu;
-- 查询指定 列
select sid,sname,sex from stu;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

3.3 条件查询

条件查询就是在基础查询基础上,再给sql设置条件,只查询部分符合条件的数据

条件语句 : select 字段1,字段2,… from 表名 where 字段 条件 值;

条件运算符

  • =
  • >
  • <
  • >=
  • <=
  • !=
  • and
  • or
  • in
  • not in
  • between…and
-- ============== 条件查询 ==============
-- 查询学号为1001的学生信息
select * from stu where sid = 1001;
-- 查询学生成绩大于60的学生id 姓名,成绩
select sid,sname,score from stu where score > 60;
-- 查询学生性别为女,并且年龄小于50的记录
select * from stu where sex = '女' and  age < 50;
-- 查询学生学号为1001,或者姓名为李四的记录
select * from stu where sid = 1001 or sname = '李四';
-- 查询学号为1001,1002,1003的记录
select * from stu where sid = 1001 or sid = 1002 or sid = 1003;
select * from stu where sid in (1001,1002,1003);
select * from stu where sid >= 1001 and sid <= 1003;
-- 查询学号不是1001,1002,1003的记录
select * from stu where sid not in (1001,1002,1003);
select * from stu where sid != 1001 and sid != 1002 and sid != 1003;
-- 查询学生年龄在20到40之间的学生记录
select * from stu where age >= 20 and age <= 40;
select * from stu where age between 20 and 40;
-- 查询性别非男的学生记录
select * from stu where sex != '男';
select * from stu where sex = '女';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

3.4 模糊查询

模糊查询其实也是条件查询

语法: select 字段1,字段2,… from 表名 where 字段 like '_值%';

  • _ 匹配任意一个字符
  • % 匹配任意多个字符
-- ============== 模糊查询 ============== 
-- 查询姓名以“张”开头的学生记录
select * from stu where sname like '张_';
select * from stu where sname like '张__';
select * from stu where sname like '张%';
-- 查询姓名中包含“三”的学生记录
select * from stu where sname like '%三%';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

3.5 排序查询

对查询后的数据按照指定字段以及指定规则排序

语法: select 字段1,字段2,… from 表名 order by 字段 [desc|asc];

  • desc 降序
  • asc 升序,默认是升序

排序查询写在最后

-- ============== 排序查询 ============== 
-- 查询所有学生记录,按年龄升序排序
select * from stu order by age asc;
select * from stu order by age;
-- 查询所有学生记录,按年龄降序排序
select * from stu order by age desc;
-- 查询所有学生记录,按年龄升序排序,如果年龄相同时,按编号降序排序
select * from stu order by age asc , sid desc;
-- 查询成绩大于60的学生id,姓名,成绩,并根据成绩降序
select sid,sname,score from stu where score > 60 order by score desc;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

3.6 聚合函数

将查询的结果,聚合运算得到一个结果值,语法特点

  • 聚合运算完,结果只有一行数据
  • 其他字段不能和聚合函数同时查询,除非有分组查询

聚合函数分类

  • count(expr) 计算指定列的不为null的行数
  • max(expr) 计算指定列的最大值
  • min(expr) 计算指定列的最小值
  • avg(expr) 计算指定列的平均数,除以不为null的条数
  • sum(expr) 计算指定列的和 ,计算不为null的数据
  • 函数中的expr,可以写列名,也可以写函数表达式

语法: select 聚合函数(字段) from 表名;

-- ============== 聚合函数  ============== 
-- 查询stu表中记录数:
select count(sid),sname from stu;
-- 查询stu表中有成绩的人数:
select count(score) from stu;
-- 查询stu表中成绩大于60的人数:
-- 先执行from获得全部数据,再通过where过滤数据,再计算select后
select count(sid) from stu where score > 60;
-- 查询所有学生成绩和
select sum(score) from stu;
-- 统计所有学生平均成绩
select avg(score) from stu;
select sum(score)/count(sid) from stu;
-- 查询最高成绩和最低成绩
-- 查询可以给列取别名,用as,as还可以省略
select max(score),min(score) from stu;
select max(score) as 最大值,min(score) as '最小值' from stu;
select max(score) 最大值,min(score) '最小值' from stu;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

3.7 去重函数

可以将某列数据去重查询,distinct,一般不单独使用,配合聚合函数使用

-- ============== 去重查询 ============== 
-- 查询年龄不重复的共有多少人		
select count(distinct age) from stu;
  • 1
  • 2
  • 3

3.8 分组查询

分组查询,就是将查询的数据分为几组.

语法: select 字段1,字段2,… from 表名 [where 字段 条件 值] group by 字段 having 字段 条件值;

  • group by 字段,根据指定字段分组
  • having 字段 值, 分组后再过滤

有个非常重要特点: SQL只要有分组,分成几组,查询结果就只有几行,所以一般配合聚合函数来使用


与聚合函数同时出现的列,必须出现在group by语句中

或者说group by后面的字段可以出现在select后

再或者说,sql语句中有group by,那么前面select后的字段,要么是group by字段,要么就是聚合函数

having和where都是过滤

  • where是分组前过滤,having是分组后过滤
  • where后不能使用聚合函数,having可以聚合函数

3.9 限制查询

就是将查询完的数据,可以限制展现条数

语法: limit n – 限制输出指定n条,从第一条开始

​ limit x,y – 限制输出,从x下标处输出y条,第一条的下标是0


常用于分页操作

-- 限制(limit)查询
select * from stu limit 3;
select * from stu limit 3,2;
  • 1
  • 2
  • 3

4 顺序

4.1 书写顺序

select * from 表名 
where 条件 
group by 字段 
having 条件 
order by 
limit x,y
------
select cid,sum(score) from stu
where sid >= 1002
group by cid
having sum(score) > 100
order by sum(score) desc
limit 1
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

4.2 执行顺序

from   获得全部数据
where  过滤一部分数据
group by 分组
having   过滤分组后的数据
select  查询展现数据,这里 有聚合的话在此时聚合
order   排序
limit   限制
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

5 作业

先总计,复盘,过电影
重复代码

空需求,完成

作业
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

下标处输出y条,第一条的下标是0


常用于分页操作

-- 限制(limit)查询
select * from stu limit 3;
select * from stu limit 3,2;
  • 1
  • 2
  • 3

4 顺序

4.1 书写顺序

select * from 表名 
where 条件 
group by 字段 
having 条件 
order by 
limit x,y
------
select cid,sum(score) from stu
where sid >= 1002
group by cid
having sum(score) > 100
order by sum(score) desc
limit 1
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

4.2 执行顺序

from   获得全部数据
where  过滤一部分数据
group by 分组
having   过滤分组后的数据
select  查询展现数据,这里 有聚合的话在此时聚合
order   排序
limit   限制
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

5 作业

先总计,复盘,过电影
重复代码

空需求,完成

作业
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/繁依Fanyi0/article/detail/65159
推荐阅读
相关标签
  

闽ICP备14008679号