第11天 MySQL-SQL普通增删改查

一、如何设计表****关系

自然界中的关系通过数据对应的模式进行分析之后,得出结果:可以大致分为三种,一对一,一对多(多对一),多对多

一对多

一对多与多对一是一个概念,指的是一个实体的某个数据与另外一个实体的多个数据有关联关系。

班级表(一表)

名称 教室 总人数 学科
PHP141115 A814 53 PHP
PHP140925 A806 55 PHP

学生表(多表)

学号 姓名 性别 年龄
0001 何权森 22
0002 朱红林 保密
0003 詹曼雪 保密
0004 刘星 28

将实体间的多对一的关系进行维护:在“”表中,增加一个字段能够指向“一”表中,唯一字段。

学号 姓名 性别 年龄 班级名称
0001 何权森 22 PHP141115
0002 朱红林 保密 PHP141115
0003 詹曼雪 保密 PHP140925
0004 刘星 28 PHP140925

多对多

一个实体的数据对应另外一个实体的多个数据,另外实体的数据也同样对应当前实体的多个数据。

教师表(多)

姓名 性别 年龄
犬夜叉 300
戈薇 18

班级表(多)

名称 教室 总人数 学科
PHP141115 A814 53 PHP
PHP140925 A806 55 PHP

犬夜叉:教过PHP141115和PHP140925
PHP141115:被犬夜叉和戈薇教过
解决方案:创建一个中间表,专门用来维护多表之间的对应关系,通常是能够唯一标识出数据的字段(主键)


image.png

一对一

一个实体的数据对应另外一个实体的数据,一条对一条

学生表(内部关系)

姓名 性别 年龄 身高 政治面貌 家庭住址 紧急联系人 紧急联系人方式 婚姻情况
张无忌 20 175 党员 冰火岛 金毛狮王 未婚
周芷若 18 168 共产党 峨眉山 灭绝师太 飞鸽传书 未婚
赵敏 18 168 国名党 蒙古 王爷 号角 未婚
宋青书 22 178 党员 武当山 周芷若 未婚

为了提升操作的效率会主动的将一张表进行拆分,拆分成两张表:一张经常用于操作,另外一张很少操作。

常用信息

姓名 性别 年龄 身高
张无忌 20 175
周芷若 18 168
赵敏 18 168
宋青书 22 178

不常用信息

姓名 政治面貌 家庭住址 紧急联系人 紧急联系人方式 婚姻情况
张无忌 党员 冰火岛 金毛狮王 未婚
周芷若 共产党 峨眉山 灭绝师太 飞鸽传书 未婚
赵敏 国名党 蒙古 王爷 号角 未婚
宋青书 党员 武当山 周芷若 未婚

二、****查询数据

基本语法
select 字段列表/* from 表名 where条件;
完整语法
select 选择模式 表达式[字段列表(别名)/*] [from 子句] [where子句] [group by子句] [having子句] [order by子句] [limit子句]
建立班级表与学生表
班级表:

 create table  if not exists class(
  c_id smallint unsigned  primary key auto_increment,
  class_name varchar(30)
);

学生表:

create table  if not exists student1(
  stu_id smallint unsigned primary key auto_increment,
  stu_name varchar(30),
  age tinyint,
  class_id smallint  
);
create table  if not exists student(
  id smallint unsigned primary key auto_increment,
  name varchar(30),
  age tinyint,
  c_id smallint  
);

分别插入数据:

 insert into class values(null,'PHP');     1
 insert into class values(null,'JAVE');     2
 insert into class values(null,'HTML5');  3
 insert into class values(null,'python');  4
 insert into student1 values(null,'张三',28,1);
 insert into student1 values(null,'李四',20,1);
 insert into student1 values(null,'王五',38,2);
 insert into student1 values(null,'张三丰',98,2);
 insert into student1 values(null,'张无忌',18,2);
 insert into student1 values(null,'张国荣',28,3);
 insert into student1 values(null,'谭咏麟',28,3);
 insert into student1 values(null,'张学友',28,4);
 insert into student1 values(null,'刘德华',28,4);
insert into student values(null,'张三',28,1);
 insert into student values(null,'李四',20,1);
 insert into student values(null,'王五',38,2);
 insert into student values(null,'张三丰',98,2);
 insert into student values(null,'张无忌',18,2);

选择模式

选择模式指的是当select查到数据之后,对数据的处理方式:查询全部(默认的all),去重(去掉重复的:记录重复,distinct)


image.png

表达式

select指定的要返回数据形式
通常要查询的数据,都是字段列表或者*(全部字段),有时候会因为同时查询多个表而出现字段重名的问题。需要使用字段别名来决绝问题。
语法:字段 [as] 别名


image.png

from子句

from子句指 的是数据源
from后面必须接数据源,数据源不一定是一张表

  1. from后面可以跟多张表,使用逗号分隔


    image.png

    形成笛卡尔积,特点:字段上进行数目相加,数据形成数目的相成,尽量避免

  2. 表别名


    image.png
  3. 数据源不一定是一张已经存在的表,需要的只是一个二维表的结构。from后面可以跟一个子查询。


    image.png

where子句

where子句用于筛选条件,对数据(记录)进行逐行判断,返回是一个类似布尔的结果。
Where子句在数据加入到内存中之前就进行了数据的筛选
where判断:返回的结果是布尔类型
比较运算:=,>,>=,<,<=,between 左边较小 and 较大,in,is null/is not null
逻辑运算:&&and和||or,用于多条件联合判断
模糊匹配:like ‘patter’,匹配符:%和_
% 匹配所有
_ 匹配单个,如果要匹配多个的话,使用多个’_’

order by子句

显示数据的一种样式(没有对数据进行筛选)
排序子句,对对应的字段进行排序。
语法
order by 字段 [asc|desc];

limit子句

limit的基本使用用于限制数据的访问量。
limit标准使用语法
limit offset,length;
从指定位置(offset)开始,获取对应长度(length)条记录
limit的应用:分页
分页必须知道的条件:页码,每页显示的数据长度
顺序: where子句 => order by 子句=> limit 子句

image.png

group by分组查询(重点)

1、使用group by对class_id进行分组
select * from student group by class_id;


image.png

2、group by应用场景
特别说明:在SQL语句中,我们并不会直接对某个字段进行分组操作,这样是没有意义的。在实际项目开发中,我们的group by分组主要是结合统计函数进行使用的。
常用统计函数:
count(字段) :统计总记录数
max() :统计最大值
min() :统计最小值
sum() :求和
avg() :求平均值
例如:统计每个班级分别有多少个学生(按班级进行统计group by class_id)


image.png

统计每个班级中年龄的最大值和最小值分别是多少
image.png

求每个班级的年龄总和、平均年龄
image.png

having****子句

在SQL语句中的,我们可以使用where子句进行条件判断。
① 其实在SQL语句还有一个having子句,其也可以进行条件判断,几乎所有使用where语句的地方都可以使用having进行替换。
1)使用where子句获取年龄大于18岁的学生信息


image.png

2)使用having子句获取年龄大于18岁的学生信息


image.png

② 虽然where子句与having子句都是用于进行条件判断,但是where是发生数据查询在进入内存之前,而having子句是发生在进入内存之后执行的。所以两者的作用点不同。
在实际项目开发中,where子句主要用于进行条件判断,而having子句主要用于对数据进行筛选操作。
例如:统计班级总人数大于2的班级信息
① 对所有班级进行分组进行人数的统计
image.png

② 对分组后的数据进行筛选(筛选总人数大于2的班级信息)
where子句(错误演示):


image.png

having子句(正确演示):
image.png

三****、****多表****联合查询

1、联合****查询****(union)

所谓的联合查询就是把两个或多个表的查询结果进行合并操作。基本语法:
select */字段 from 数据表
union [all | distinct]
select */字段 from 数据表
union all :对查询结果进行合并,保留所有的查询结果,不去重
union distinct :对查询结果进行合并,去除重复的查询结果(去重),默认去重
示例代码:


image.png

image.png

特别说明:使用union联合查询必须有一个前提:查询的结果的字段数量必须一致,否则会产生以上错误。

2****、union联合****查询的应用场景

1)如果在select查询语句中,需要分条件查询,但是其如果写在一条select语句中会出现语句冲突,这个时候就可以使用union进行合并操作。
例如:编写SQL语句,要求:
对于class_id = 1中的学员信息对age进行升序排列
且对于class_id = 4中的学员信息对age进行降序排列


image.png

使用union进行合并操作
默认情况下union不能结合order进行使用。
解决方案:如果在union合并查询时出现order by,必须对每一个select语句用圆括号括起来。通过圆括号可以解决union与order by的结合问题。但是运行后发现,order by失效了。这是因为,默认情况下,union与order by结合,其order by会失效。如果想让order by生效必须结合limit使用,否则order by不会生效。


image.png

image.png

2)使用union合并解决分表后获取所有数据
在实际项目开发中,我们的数据表所存储的记录总数可能在千万级以上(1个亿)。如果这些数据都放在一个表中,其数据查询时要查询1亿次。为了达到优化的问题,我们可以创建多个结构相同的表,把1亿条假设分为10个表,每个表只需要存储1000万条数据。这样在数据检索时,更加高效。我们把这种就称之为“物理分表”。在读取所有表信息时,我们还可以通过union进行合并操作。

四****、SQL中的多表****(有关系两者,主要是多对一)****连接****查询

1、什么****是多表连接查询

所谓的多表连接查询,就是拿一个表中的数据与另外一个或多个表中的数据进行匹配,如果有与之匹配的结果,则返回。反之,则自动忽略。
在SQL语句中,查询一共分为三大类:内连接查询、外连接查询、自然连接查询

3****、为****数据表其别名

基本语法:
select 别名.* from 数据表1 (空格+别名 | as别名)

4****、****内****连接查询(内连接)

基本语法:
select 数据源1.字段,数据源2.字段 from 数据源1 【inner】 join 数据源2 on 查询条件;
主要功能:从其中一个表中读取数据,然后到另外一个表中进行匹配,如果条件成立,则返回该条记录。不匹配数据不返回


image.png

5****、左右****外链接查询(外链接查询)

有两张表:左表与右表,基本语法:
左外链接查询:select 左表.字段,右表.字段 from 左表 left join 右表 on 匹配条件;
主要功能:
① 以左表作为主表,读取所有记录信息
② 拿主表中的每一条记录与右表中的记录进行匹配,如果匹配成功,则返回关联信息,反之则返回null。


image.png

右外链接查询:select 左表.字段,右表.字段 from 左表 right join 右表 on 匹配条件;
主要功能:
① 以右表作为主表,读取所有记录信息
② 拿主表中的每一条记录与左表中的记录进行匹配,如果匹配成功,则返回关联信息,反之则返回null。


image.png

五、备份恢复数据库(命令的方式)

备份数据库:

mysqldump ****-h服务器地址 -u登录名 -p**** 要备份的数据库名 > 要保存为的文件(带有路径)

image.png

备份的结果:
image.png

说明:数据库需要每天利用定时脚本进行定时定点的进行数据备份,一般选择凌晨访问量少的时候进行

恢复数据库:

将刚才的数据恢复到另一个数据库中:

mysql -h服务器地址 -u登录名 -P端口号 -p 数据库名****【****事先去创建****】****<**** 带有路径的****文件名****(****已经存在****)

image.png

结果:
image.png

扩展:仅适用于学习阶段
数据库没有及时备份,原数据库故障,需要进行数据迁移时。
image.png

1、把要迁移的数据的存储引擎改为myisam,使结构和数据实现分离,这样我们进行迁移时,结构和数据都和ibdata1进行了分离
2、把迁移的文件目录放入到新的数据库的data目录下即可

六、数据控制语言(DCL)

问题描述:
在实际开发中,有多人进行开发,此时每个开发者都要连接数据库,每个开发者都是在自己的电脑上开发的,即拥有不同的IP地址,按照实际,是无法连接到数据库服务器


image.png

解决方案:
创建多个用户,允许特定的IP地址进行访问,并且要进行权限控制。
root用户名是超级管理员,所以一般不会给开发超级管理员的权限
数据控制,其实就是“分配权限”——就涉及到用户。
则主要就是2个问题:
用户管理: 创建一个用户名和一个密码
权限分配:
在mysql中,权限是系统内定的一些“名词”(单词),大约30个,每个权限表示“可以做什么工作”。 
则分配权限就是相当于让某个用户可以做哪些工作。
主要权限如下:


image.png

MySQL中的用户

mysql中的用户信息都存储在系统数据库mysql的user表中:

创建用户

语法形式:
create user ‘用户名’@’允许其登录的地址’ identified by ‘密码’;
说明:
1,创建的用户需同时指定该用户可以在哪个地址进行登录。
其中“%”代表“任何地址”。
2,用户创建之后,自动在mysql的user表中添加了一条记录,但该用户还没有权限。


image.png

删除用户

drop user ‘用户名’@’允许其登录的地址’;
没有权限:


image.png

有权限:


image.png

权限分配

增加权限:

简短语法:
用户已经创建好了,直接分配权限
grant show databases,select,update,delete,insert on . to ‘lau’@’localhost’
使用root账户进行权限分配:

image.png

image.png

image.png

登陆lau用户:
image.png

完整语法:
分配权限的同时也创建了用户
grant 权限名1,权限名2, .... on 数据库名.对象名 to 用户名@允许其登录的地址 identified by 密码;
给特定的权限grant select,delete on school.student to ‘admin’@’192.168.2.24’ indentified by ‘123’

image.png

** 给所有的权限(超级管理员的权限)**

grant all privileges on . to ‘root’@’%’ indentified by ‘123’

image.png

说明:
1权限名就是:’select’, ‘update’, ‘delete’,等等。其中ALL 表示“所有权限”,或all privileges也一样
2对象名:就是一个数据库中“装”的东西,表是最常见的,也可以是视图,存储过程,存储函数等。
其中:表示所有数据中的所有对象
某数据库名.*表示该数据库中的所有对象——这个商业上常用。
3,identified by ‘密码’用于给一个用户在此时修改密码,不写也可以,那就不修改密码。
4,但同时该语句也可以创建用户(如果不存在),但此时identified by ‘密码’必须写。
强调:1、在实际开发中,尽量不要使用root账号进行操作
2、分配用户权限时尽量限制在特定的库、特定的表
3、权限尽量分配合理,不要分配其结构方面权限。
经常犯的错误:买了服务器之后,如果你的项目的服务器和数据库服务器不一致,如果你想项目服务器连接数据库服务器,请记住一定要给项目服务器分配特定的用户并且授权。
删除权限
remove 权限名1,权限名2, .... on 数据库名.表名 from ‘用户名’@’允许其登录的地址’ ;
表示从某个用户身上“取消”某些权限(也许还保留了其他权限)。

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 201,552评论 5 474
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 84,666评论 2 377
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 148,519评论 0 334
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,180评论 1 272
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,205评论 5 363
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,344评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,781评论 3 393
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,449评论 0 256
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,635评论 1 295
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,467评论 2 317
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,515评论 1 329
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,217评论 3 318
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,775评论 3 303
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,851评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,084评论 1 258
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 42,637评论 2 348
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,204评论 2 341

推荐阅读更多精彩内容