typora-copy-images-to: imgs

1)分组
  group by 字段;
  select 分组字段,聚合函数(字段) from t where条件  group by 分组字段 having 条件;
  过程:
  1)where过滤整张表获取一个结果集;
  2)在where过滤结果集的基础上进行分组运算,形成分组的结果集;
  3)having在group by分组结果集的基础上进行条件过滤;

  where与having区别:
    1)where用于分组之前,不能与聚合函数结合使用(where其实就是一个条件过滤声明);
    2)having用于分组之后(已经被group by分组形成分组结果集),可以与聚合函数结合使用;
 2)order by 字段1,字段2,...;
   用于排序
   排序规则:首先根据第一个字段进行排序,如果第一个字段都不相同,其他排序字段就不会参与排序运算,如果出现相同的值,第二个字段参与排序运算,如果第二个也存在相同的值,以此类推;
 3)模糊搜索: like '通配符字符串';
   通配符:%--0到多个,_----一个占位符
   select * from t where name like '%通%';
 4)5大聚合函数
   使用格式:
   select 聚合函数(字段),name from t group by name;
   统计条数:count(*)
   求和:sum(字段)
   最大值:max(字段)
   最小值:min(字段)
   平均数:avg(字段)
  5)其他函数
     ifnull(v1,v2)---->如果v1不为null,函数返回v1,否则返回v2;
     isnull(v1)----->判断v1是否为null,如果v1为null,那么返回1,否则返回0;
   6)DML:
     插入:insert into
         eg:insert into t(字段1,字段2,...) values(v1,v2,...),(v1,v2,...),....;
     更新:update  set
         eg:update t set 字段1=v1,....字段n=vn where 条件;
     删除:delete from
          eg:delete from t where 条件;
             truncate table t;
    7)比较运算符: > < <= >= = != <>
      逻辑运算符:and, or,not

1. 1、DQL查询语句-limit语句(掌握)

在我们使用查询语句的时候,经常需要返回前几行或者中间某几行数据 ,这个时候可以使用limit实现限制查询记录的条数;

格式:

select * from 表名 where 条件 limit offset ,row_count;
-- 说明:offset表示索引偏移量,索引位从0开始;row_count:返回的条数,从索引偏移量开始(包含偏移量对应的行记录)
limit 0 ,3;-- id=1,2,3
limit 4,2;-- id=5,6
/*
 offset :表示查询结果的索引值,从0开始表示第一行数据记录,依次类推,当offset为0时,可以省略不写,如 limit 8 表示返回前8条数据;
 row_count:表示要查询的记录数
*/
--  举例:
 select * from 表名 limit 4,5 ; -- 表示从索引值4开始查询,最多返回5条数据;

image-20201015194238130

需求:蠕虫复制student表数据到student3,然后查询学生student表信息,按照每页3条记录查询,查询前4条数据;

create table student like day01.student3;
insert into student select * from day01.student3;
-- 第一页:
select * from student limit 0,3;-- id=1,2,3
select * from student limit 3,3;-- id=4,5,6
select * from student limit 6,3;-- id=7,8 最后一页返回的记录数<=row_count
-- 规律:页数与偏移量关系--->偏移量=(页数-1)*row_count;
-- 最终说明:1)limit offset,pageSize; 
-- offset:表示位移偏移量,从0开始,包含边界值;
-- pageSize:每页大小,也就是返回的行数

小结:

1.limit查询语法格式?

select * from t limit offset,rowCount;

2.使用条件,分组,排序,分页查询的执行顺序?

select * from t where 条件 1 group by 字段 2 having 条件 3 order by 字段 4 limit offset,rowCount 5;

2. 2、DCL创建用户,给用户授权,撤销授权(了解)

在企业生产环境中数据库一般由专业的DBA运营维护,一般不同的业务(项目)的数据存放在不同的数据库下,且由不同的人员去维护,并根据职责分配不同的权限;

生产环境中合理的分配数据库权限,对系统的正常运转起到非常好的促进作用!

​ 我们现在使用的是root超级管理员,拥有全部权限,一般在公司中,针对不同的职责人员,需要创建不同的用户并且分配不同的权限;

2.1.1. 1.创建普通用户指令

指令格式:

create  user '用户名'@'主机名称' identified by '密码';
-- 主机名称为当前用户的ip或者主机名,也可使用%匹配所有地址

需求1:创建u1用户,只能在localhost这个服务器登录mysql服务器,密码为1234.

需求2:创建u2用户可以在任何电脑上登录mysql服务器,密码为1234

2.1.2. 2.给用户授权

命令格式:

-- 给用户授权格式:
grant 权限1,权限2,.... on 数据库.表 to '用户名'@'主机名';

需求3:给u1用户分配对heima01这个数据库操作的权限:创建表,修改表,插入记录,更新记录,查询.

2.1.3. 3.撤销用户权限

命令格式:

-- 撤销权限,格式:
revoke权限1,权限2,.... on 数据库.表名  from '用户名'@'主机名称';

需求4:撤销u1用户对heima01数据库所有(all)表的操作的权限;

3. 3、DCL删除用户,修改用户密码(了解)

3.1.1. 1.删除普通用户命令

命令格式:

-- 删除用户格式
drop user '用户名'@'主机名称';

需求1:删除u2

3.1.2. 2.修改超级管理员密码

命令格式:

msyqladmin -u root -p  password 新密码;

需求2:修改管理员密码为123456

3.1.3. 3.修改普通用户密码

-- 修改普通用户密码格式:
set password for '用户名'@'主机名' =password('新密码');

需求3:将'u1'@'localhost'的密码改成'666'

4. 4、数据库备份和还原(理解)

4.1.1. 1.应用场景

1)防止磁盘损坏,造成数据库中的数据丢失;

2)业务需要,做数据库中数据迁移(从B库迁移到不同机器下的B库);

4.1.2. 2.命令行备份与还原

-- 备份,导出的文件后缀名必须为.sql
mysqldump -u 用户名 -p  需要备份的数据库 > 数据存放的文件路径(sql后缀名)
-- 还原,数据导入格式
mysql -u root -p 数据库名称 < 数据存放的文件路径

需求1:将day02数据库导出到硬盘文件e:\day02.sql 中

image-20201031094241742

说明:使用mysqldump命令备份数据,不会备份库的创建语句,那么在数据还原的时候,需要自己创建库;

需求2:重新开启一个新的dos窗口,将day02备份的数据表和表数据 恢复到heima库中

image-20201031094720935

4.1.3. 3.图形化界面备份与还原

1.可视化工具实现数据备份

image-20201031094838822

image-20201031095028538

2 使用可视化工具将之前备份的数据导入

image-20201031095237213

image-20201031095259174

5. 5、数据库约束的概述(掌握)

1.数据库约束的作用

​ 约束是对表中的数据进一步限制,保证数据正确,有效和完整;

2.约束分类

约束名称 关键字 作用
主键约束 primary key 唯一+非空
唯一约束 unique 唯一
非空约束 not null 非空
默认值约束 default 默认的值 指定字段的默认值
外键约束 foreign key 用于多表之间的关系约束

6. 6、主键约束(掌握)

1.主键约束作用

​ 首先,主键能够唯一的标识表中的每一行数据,使用主键约束后,可是数据满足唯一且非空,方便数据管理;

2.创建表时创建主键

字段名 类型 primary key

3.在已有表中添加主键

alter table 表名 add primary key(字段);

需求:创建学生表st5, 包含字段(id, name, age)将id做为主键,并测试唯一和非空

create table st5(
 id int primary key,
 name varchar(20),
  age int  
);

小结:

1.主键特点?

唯一+非空

2.创建主键的格式?

create table st5(
 id int primary key,
 name varchar(20),
  age int  
);
-- alter语句
alter table 表名 add primary key(字段);

7. 7、主键自增

1.主键自增介绍及语法格式

数据库自己维护主键,且实现主键的递增;

-- 格式
字段名 字段类型 primary key auto_increment;

需求:创建学生表st6, 包含字段(id, name, age)将id做为主键并自动增长并测试

create table st6(
 id int primary key auto_increment,
 name varchar(20),
 age int   
);

8. 8、唯一约束(掌握)

1.唯一约束介绍及语法格式

​ 字段值唯一,不可重复

-- DDL修改 用的不多
alter table 表名 add unique(字段);
-- 创建表时指定
create table  表名(
    字段名 字段类型 unique,
    ......
);

需求1:创建学生表st7, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生并唯一性和null重复

create table st7(
id int primary key auto_increment,
name varchar(20)  unique   
);

说明:如果插入相同的数据会报:Duplicate entry 'laoliu' for key 'name'

​ 同时,唯一约束可以出现多个null;

9. 9、非空约束(掌握)

1.介绍及语法

​ 约束某个字段不能为空;

-- 修改字段非空约束,不常用
alter table 表名 alter 字段 set  not null;
-- 创建表时指定
字段名  类型  not  null

需求:创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL,并测试

create table st8(
id int,
name varchar(20) not null,
gender char(2)    
);

说明:如果为null,则报:Column 'name' cannot be null

小结:

1.非空约束格式?

-- 创建表
字段名称 字段类型 not null

10. 10、默认值(了解)

1.介绍及语法格式

​ 给字段添加默认值,如果字段没有被赋值,则使用默认值;

-- 格式1:修改表字段默认值
alter  table 表名 alter 字段名 set default 默认值;
-- 创建表是指定默认值
字段名  字段类型  default 默认值;

需求:创建一个学生表 st9,包含字段(id,name,address), 地址默认值是广州,并测试(null)

create table st9(
id int,
name varchar(20),
address varchar(50) default '广州'     
);

2.面试题:非空+唯一约束与主键约束区别?

1)主键可以添加自增,非空+唯一的组合约束不能添加自增;
2)主键约束在表中只有一个,但是非空+唯一的约束可以配置多个;
3)主键约束底层维护了一个主键索引,而唯一约束底层维护了一个唯一索引;

11. 11、表关系的概念和外键约束

在一些外包公司中,程序员经常需要维护多个项目,同时一个项目也可能被多个程序员维护,所以程序员与项目之间是多对多个关系;

常见表结构关系:

多对多: 程序员与项目\学生与选修课程\玩家与装备等;

一对多:买家与订单\班级与学生\部门与员工等;

一对一:用户与身份证\玩家信息与微信信息

11.1. 1.多对多(掌握)

以程序员和项目为例:

业务分析:

一个程序员可以维护多个项目;

一个项目也可被多个程序员维护;

程序员与项目之间是多对多的关系;

ER分析:

image-20201015204147130

说明:通常在多对多的业务场景中共会通过设计一张中间表将多对多转换成一对多,简化了业务关系;

表结构组成:

coder表:id int ,name varchar,salary double;

project表:id int ,name varchar;

coder_project表:coder_id int,project_id int;

-- 程序员表
create table coder(
id int primary key auto_increment,
name varchar(20),
salary double
);
-- 项目表
create table project(
id int primary key auto_increment,
name varchar(10)
);
-- 中间表
create table coder_project(
coder_id int,
project_id int
);
-- 添加测试数据
insert into coder values(1,'张三',12000);
insert into coder values(2,'李四',15000);
insert into coder values(3,'王五',18000);

insert into project values(1,'QQ项目');
insert into project values(2,'微信项目');

insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,2);

11.2. 2.外键约束(掌握)

1.表结构设计存在的问题

上述,中间表的作用是为了维护多对多的关系,但是如果不加相关的约束,会出现以下问题:

1.中间表中可以插入不存在的程序员id或者项目id信息;

2.主表删除信息后,中间表信息依旧保存,不能统一;

3.主表主键更新后,对应的中间表数据没有同步;

2.添加外键约束格式

-- 方式1:修改字段为外键
alter table 当前表 add constraint 外键名称 foreign key(当前表中的字段) references 主表(主表主键);
/*
  说明:
  constraint 外键名称 :表示约束,同时给约束取名称
   foreign key(当前表中的字段):表示给当前表字段添加外键
   references 主表(主表主键):表示关联主表中的主键

*/

-- 方式2:创建表时创建外键
create table coder_project(
    coder_id int,
    project_id int,
    constraint c_id_fk foreign key(coder_id) references coder(id),
    constraint p_id_fk foreign key(project_id) references porject(id)
);

3.需求:为中间表添加外键

说明:添加外键前,可将3张表中数据truncate掉,然后中间表通过alter添加外键关联;

-- 添加外键
alter table coder_project add constraint fk_coder_id foreign key(coder_id)
 references coder(id);
alter table coder_project add constraint fk_project_id foreign key(project_id)
 references project(id);

4.测试功能

​ 中间表插入数据,主表主键删除+修改测试

/*
Error Code : 1452
Cannot add or update a child row: a foreign key constraint 
fails (`day02_2`.`coder_project`, CONSTRAINT `fk_coder_id` FOREIGN KEY (`coder_id`) 
REFERENCES `coder` (`id`))
*/
insert into coder_project values(100,50);
delete from coder where id=2;
-- 说明修改非主键字段,不影响
update coder set name ='lisi' where id=2;
/*
Error Code : 1451
Cannot delete or update a parent row: a 
foreign key constraint fails (`day02_2`.`coder_project`, CONSTRAINT `fk_coder_id` FOREIGN KEY 
(`coder_id`) REFERENCES `coder` (`id`))
*/
update coder set id =7 where id=2;

11.3. 3.外键的级联(掌握)

1.介绍

级联操作就是在修改或者删除主键时可以同时对从表的外键进行修改删除;

on udpate cascade :代表主表进行更新操作后,从表对应的也要进行更新操作;
on delete cascade:代表主表进行主键删除后,从表外键对应的数据也要删除;

2.为中间表添加级联操作

演示创建表过程条件外键和级联操作;

步骤:1.先drop删除中间表,然后在drop两张主表;

​ 2.重新创建主表及中间表,并进行外键关联和级联设置;

-- truncate清空表,重新创建表
drop table coder_project;
truncate table project;
truncate table coder;

create table coder_project(
coder_id int,
project_id int,
constraint fk_coder_id foreign key(coder_id) references coder(id) on update cascade on delete cascade,
constraint fk_project_id foreign key(project_id) references project(id)  on update cascade on delete cascade
);

insert into coder values(1,'张三',12000);
insert into coder values(2,'李四',15000);
insert into coder values(3,'王五',18000);

insert into project values(1,'QQ项目');
insert into project values(2,'微信项目');

insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,2);

image-20201031141412193

11.4. 4.一对多(掌握)

一对多:包含两个实体,其实A实体中的数据对应多个B实体中的数据,相反,B中一条数据真能对应A中一条.

举例:

作者--小说, 教室与班级 ,部门与员工;

以作者--小说为例分析:

image-20201015204310391

image-20201015204421210

说明:在表结构设计中,针对一对多的业务场景,一般交给多个一方去维护;

image-20201031141332593

11.5. 4.一对一

一对一关系表在实际开发中使用的并不多,其中也是2个实体,其中A实体中的数据只能对应B实体中的一个数据,同时B实体中的数据也只能对应A实体中的一个数据。

举例:一个玩家绑定一个微信号\一个老司机只能有一个一张汽车驾照等等;

image-20201015204524227

12. 表设计案例(作业练习)

1.分析需求:

1、每个教师teacher可以教多门课程

2、每个课程course由一个老师负责

3、每门课程可以由多个学生选修

4、每个学生student可以选修多门课程

5、学生选修课程要有成绩

流程:

1.ER图分析

2.SQL设计;

results matching ""

    No results matching ""