sql-4

order by 排序子句

对结果进行排序的语句！

语法：

order by 字段名 [asc|desc], [字段名 [asc|desc],]

asc  升序   desc 降序

 

 

可按照多个字段进行排序

 

 

 

 

limit 子句

限制 结果记录数的子句！

从 所有的结果中 ，选择部分结果的 子句！

 

 

可以从 某个位置开始，取得多少条！

语法：

limit start, size;

start:起始位置

size，取得的记录数

 

 

 

注意，第二个参数是，长度，而不是 终止位置！

 

limit使用的最多的地方就是用在分页！

子查询，sub-query

概念

简单来说，所谓的子查询，就是一条select语句中又出现了其他的select语句！

要求：子查询的语句必须用一对括号括起来！

 

 

需求：

找出学生表中身高最高的学生！

 

 

 

如果说表中出现了多条记录中的身高是一样的 上面的SQL语句就满足不了我们的需求！

我们可以先取得到

 

 

 

 

子查询的分类

分类的依据！

两种分类依据：

1， 依据 子查询出现的位置！

where型子查询， 出现在where子句内！

from 型子查询， 出现在from子句内！

 

2， 依据 子查询的返回数据的格式！

标量子查询，返回值是一个数据，称之为标量子查询！   一行一列的二维表

列子查询，返回一个列，多行一列的二维表

行子查询，返回一个行，一行多列的二维

表子查询，返回的是一个二维表

 

 

 

from型  表子查询

from型子查询一般就是当成一张表来使用，而且大部分出现在from之后，作为数据源！

需求：

查询，每个班级之内，最高的学生

先要让班级进行分组  然后再对身高进行排序

 

先对身高进行降序

 

 

 

 

列子查询

我们通常把列子查询的结果当成一个集合来使用！

所以，通常就是配合in或not in来使用列子查询！

 

需求：

查询班级有女学生的男同学的信息

 

先应该把有女学生的班级ID找出来

 

 

 

 

 

查询有学生的班级

 

 

行子查询

一次性查出来一个行（多个行）使用 行进行匹配   

需求：

查找班级中最高的同时最有钱的学生   

 

 

 

 

 

 

 

exists型子查询

exists子查询的目的不是为了产生结果集，只是用来判断某个子查询是否查询到了数据！

exists子查询返回的是一个布尔值！

如果查询到了结果，就返回true（1），如果没有查询到数据，就返回false（0）

exists出现在where条件里面

查询有学生的班级

 

 

 

连接查询，join

连接，多个表记录之间的连接！

 

 

场景：

需要得到一个学生列表，要求是，展示：

学生姓名，性别，班级名字

 

 

此时 需要 不单从 学生表获取数据，还需要从 班级表获得数据！

 

语法：

from 表名1  join  表名2 on 连接条件

 

两个特殊的地方：

join ，连接

on ，连接条件

 

 

join 连接语法

内连接

外连接，左外连接，右外连接，

交叉连接

自然连接

 

 

内连接，inner join

记录与真实的记录连接，称之为内连接！（两条真实存在的记录的连接）

mysql默认的连接就是 inner join

 

 

 

 

内连接，可以连接省略条件！

on可以省略：相当于连接条件永远成立！

返回值是一个 笛卡尔积！

 

 

 

外连接，left join，right join

分成：左外连接left join，右外连接right join！

 

连接的记录，可能是一方不存在的！（两条记录中，可能某条不存在）

 

 

总结：内连接，外连接差别不大，只是外连接会将没有连接成功的记录，也出现最终的连接的结果内，而内连接，连接的结果只有连接成功的（两条记录都存在的）

 

 

左外连接

 

 

 

右外连接

 

 

 

注意左外与右外的区别：

区别在于，那个表的记录（指的是连接失败的记录），会最终出现在连接结果内？

 

什么是左表 和 右表？

join关键字前面的（左边的）左表，join关键字后边的（右边的）右表！

 

左外：如果出现左表记录连接不上右表记录的，左表记录会出现正在最终的连接结果内！而右表记录相应设置成NULL。

右外：如果出现右表记录连接不上左表记录的，右表记录会出现正在最终的连接结果内！而左表记录相应设置成NULL。

 

总结：左外连接它是以左表为主   右外连接它是以右表为主

左右外连接其实它们可以是一样的 ，只需要换表所在的位置  

 

工作中使用最多就是左外连接

 

 

 

问题：

统计 每个班级内，学生的数量！，在班级列表内：

班级名，教室，学生数量

 

 

外连接一定要有连接的条件：

 

 

 

 

交叉连接，cross join

 

结果与 内连接一致！

内连接：

 

 

交叉连接：

 

 

 

有时，在获得笛卡尔积时，显式的使用 交叉连接！

 

交叉连接 相当于 是 没有条件的内连接！

 

 

 

自然连接，natural  join

自然连接它是拿多个表中相同的字段名来作为连接的条件

 

 

mysql，自动判断连接条件，帮助我们完成连接！

典型的条件就是，表中的同名字段！

 

 

 

连接条件，on，using

on，后面使用一个连接条件表达式！

 

 

using(连接字段)，要求使用同名字段进行连接！

 

 

 

using 的特别地方：

会对字段列表做一次整理！将连接字段作为一次显示！

 

 

 

 

 

union查询，联合查询

将 多个查询的结果，并列到一个结果集合内！

将班级表中的男生与女生的数量联合起来！

 

 

 

 

先获取到班级中所有的男生 按身高降序  

然后获取到班级中所有的女生 按身高升序

把两个结果联合到一起   

 

 

 

 

 

 

union要求两个表中的字段的数量要一样

 

 

 

 

 

select语句的选项

distinct，取消相同的记录  去重

 

 

默认是 all，可以不写，表示所有的记录都出现！

 

 

删除数据，delete，truncate

delete时，支持 order by 和 limit 来限制删除的数据记录!

 

 

 

如果清空表，此时可以独立使用 truncate 语句，完成清空表

 

 

 

 

 

truncate 表名 先将表给删除  然后再重新创建一张表

 

 

更新，update

类似于 删除，也可以使用 order by 和 limit 确定更新的记录！

 

 

数据的备份

属于DCL：数据控制语言！

备份：就是将已有的数据复制一份，存放到其他的存储单元！

常见的有：文本备份，数据备份和sql备份

文本备份

文本备份是最简单的，复制——粘贴！

一般认为，只适合Myisam存储引擎的表！

因为Myisam引擎的表它会将表分为三个部分来进行存储

 

 

 

文本备份的缺点：

每次都需要备份整个全部文件，非常浪费磁盘空间！

 

数据备份

就是只备份一张表的数据部分！

采用这种备份方式，如果误删了表的全部记录，还可以还原（也就是误使用delete语句），但是如果将整个表都删除了（误使用drop），不能还原！

备份语法

select  *|字段列表  into  outfile 文件地址  from 表名;

 

 

 

 

 

还原语法

load data infile 文件路径  into table 表名[字段列表];

 

 

 

 

数据的导出工具，mysqldump

将表的结构和数据通过sql语句的形式进行备份！

 

 

 

mysql中有一个专门用来sql备份的客户端！

 

备份语法

备份一个库

 

mysqldump  –hPup  -B  需要备份的数据库 > 指定的文件中  

 

 

 

注意，上面 > 的语法，指的是 输出重定向！

 

备份表

与 备份库相比，多出了一个 表名的值：

库名 表名

一次备份一个表

mysqldump  –hPup   数据库 表名 > 指定的文件中  

 

 

一次备份多个表

mysqldump  –hPup   数据库  表名1  表名2  表名n > 指定的文件中  

说明：

表名之间是使用空格分隔

 

 

还原语法

在CMD窗口中 没有登录mysql客户端时

mysql –hPup 数据库名 < 备份路径

 

 

 

将select_stu.sql这个文件中内容导入到xyz数据库中

 

 

还原还有一个语法：

可以在mysql客户端登陆后，使用 source 指令，来强制执行一个文件内的sql语句！

 

 

source 备份路径;  

 

使用source这个命令导入文件时 文件名不需要加引号

用户权限管理

用户权限管理：在不同的项目中给不同的角色（开发者）不同的操作权限，为了保证数据库数据的安全。

 

通常，一个用户的密码不会长期不变，所以需要经常性的变更数据库用户密码来确保用户本身安全（mysql客户端用户）

 

用户管理

Mysql需要客户端进行连接认证才能进行服务器操作：需要用户信息。Mysql中所有的用户信息都是保存在mysql数据库下的user表中。

 

 

 

 

默认的，在安装Mysql的时候，如果不选择创建匿名用户，那么意味着所有的用户只有一个：root超级用户

 

 

在mysql中，对用户管理中，是由对应的Host和User共同组成主键来区分用户。

User：代表用户的用户名

Host：代表本质是允许访问的客户端（IP或者主机地址）。如果host使用%代表所有的用户（客户端）都可以访问

 

 

创建用户

基本语法：create user 用户名 identified by ‘明文密码’;

用户：用户名@主机地址

主机地址：’’ | ‘%’

 

 

 

删除用户

注意：mysql中user是带着host本身的（具有唯一性）

基本语法：drop user 用户名@host;

 

 

修改用户密码

Mysql中提供了多种修改的方式：基本上都必须使用对应提供的一个系统函数：password()，需要靠该函数对密码进行加密处理。

 

 

1、 使用专门的修改密码的指令

基本语法：set password for 用户 = password(‘新的明文密码’);

 

2、 使用更新语句update来修改表

基本语法：update mysql.user set password = password(‘新的明文密码’) where user = ‘’ and host= ‘’;

 

 

权限管理

在mysql中将权限管理分为三类：

1、 数据权限：增删改查（select|update|delete|insert）

2、 结构权限：结构操作（create|drop）

3、 管理权限：权限管理（create user|grant|revoke）：通常只给管理员如此权限

授予权限：grant

将权限分配给指定的用户

基本语法：grant 权限列表 on库名.表名 to 用户;

权限列表：使用逗号分隔，但是可以使用all privileges代表全部权限

数据库.表名：可以是单表（数据库名字.表名），可以是具体某个数据库（数据库.*），也可以整库（*.*）

 

 

取消权限：revoke

权限回收：将权限从用户手中收回

revoke 权限列表 on 库名.表名 from ‘用户名’ [@主机地址];

 

 

刷新权限：flush

Flush：刷新，将当前对用户的权限操作，进行一个刷新，将操作的具体内容同步到对应的表中。

基本语法：flush privileges;

密码丢失的解决方案

如果忘记了root用户密码，就需要去找回或者重置root用户密码

 

1、 停止mysql服务  

 

 

2、 重新启动服务：mysqld.exe  –skip-grant-tables //启动服务器但是跳过权限

 

 

3、 当前启动的服务器没有权限概念：非常危险，任何客户端，不需要任何用户信息都可以直接登录，而且是root权限：新开客户端，使用mysql.exe登录即可

 

 

4、 修改root用户的密码：指定 用户名@host

 

5、 赶紧关闭服务器，重启服务

 

 

 

视图，view

视图，是一张表，但是虚拟表！

是通过 一条 查询语句 得到一个张虚拟表！

因此，认为视图，就是 select语句的结果！

 

创建视图

视图的本质是SQL指令（select语句）

基本语法：create  view 视图名字 as  select指令; //可以是单表数据，也可以是连接查询，联合查询或者子查询

 

 

 

 

 

 

 

查看视图结构：视图本身是虚拟表，所以关于表的一些操作都适用于视图

 

比如有一个用户是超级管理员  我们可以给其看到所有的数据  

 

普通用户我只想给其一个查看 学生姓名 、学生的所在班级  

 

 

使用视图

视图是一张虚拟表：可以直接把视图当做“表”操作，但是视图本身没有数据，是临时执行select语句得到对应的结果。视图主要用户查询操作。

 

 

基本语法：select 字段列表 from 视图名字 [字句];

 

修改视图

修改视图：本质是修改视图对应的查询语句

基本语法：alter view 视图名字 as 新select指令;

 

删除视图

基本语法：drop view 视图名字;

 

 

 

 

视图算法，执行方式 algorithm

场景

取得每个班级最高的学生信息

使用 from型子查询，可以，但是使用同样逻辑的视图不行！

 

 

 

视图 其实一共有三种执行方式：

merge，合并

temptable，临时表

undefined，未定义，就是默认的，mysql自己决定算法（从 merge，和temptable内选择）！

 

在创建视图的时候可以指定视图的算法  

 

 

 

视图用的非常少