（小白）MySQL基础学习005 20201010

（小白）MySQL基础学习005 20201010

历程

1. 子查询
   子查询是一种常用计算机语言SELECT-SQL语言中嵌套查询下层的程序模块，当一个查询是另一个查询的条件时，称之为子查询

在一条select语句中，嵌入了另一条select语句，那么被嵌入的select语句称为子查询语句

2. 主查询
   主要的查询对象，第一条select语句，确定的用户所有获取的数据目标（数据源），已经要具体得到的字段信息

3. 子查询与主查询的关系
   子查询是辅助，嵌入到主查询中，子查询可以独立存在

4. 子查询分类
   标量子查询：结果是一个数据，一行一列
   列子查询：返回一列
   行子查询：返回一行
   表子查询返回多行多列
   Exists子查询：返回结果1或0

5. 按位置分
   where子查询：子查询出现的位置在where条件中
   from子查询：子查询出现的位置在from数据中（做数据源）

6. 标量子查询
   select * from 数据源 where 条件判断 =/<>(select 字段名 from 数据源 where 条件判断) //子查询得到的结果只有一个值

知道一个学生的名字，想知道他在哪个班级（班级名字）
1、通过学生找到班级ID
2、通过班级ID获取班级名称
select * from my_class where class_id=(select class_id from my_student where stu_name=“小江”);

需求决定主查询，条件决定子查询

7. 列子查询
   select * from 数据源 where 条件判断in(列子查询)
   想获取已经有学生在班的所有班级名字
   1、找出学生表中所有的班级ID
   2、找出班级表中对应的名字

列子查询实现
select stu_name from my_class where class_id in (select class_id from my_student);

8. 行子查询
   返回结果为一行多列
   行元素：字段元素是指一个字段所对应的值，行元素对应的就是多个字段，多个字段合起来作为一个元素参与运算，把这种情况称之为元素
   主查询 where 条件[(构造一个行元素)=(行子查询)]
   获取班级年龄最大，且身高最高的学生
   1/求出班级年龄最大的值
   2、求出班级身高最高的值
   3、求出对应的学生

select * from my_student where (stu_age,stu_height)=(select max(stu_age),max(stu_height) from my_student);

9. 表子查询，与行子查询非常相似，只是行子查询需要产生行元素，而表子查询没有
   行子查询where条件判断，where子查询
   表子查询 from 数据源，from 子查询

select 字段表 from (表子查询)as 别名[group by][having][order by][limit]
获取每个班上最高身高的学生（一个）
select * from my_student group by class_id having stu_height =max (stu_height);
这样是没办法得到的
正确的方法是：
1、将每个班最高的学生排在最前面：order by
2、再针对结果进行group by:保留每组第一个
select * from (select * from my_student order by stu_height desc) as temp group by class_id;

10. exists子查询
    查询返回的结果只有1或0
    where exists(查询语句) 就是看结果是否存在，存在返回1，不存在返回0
    求出，有学生在的所有班级
    select * from my_class as c where exists(select stu_id from my_student as s where s.class_id = c.class_id);
    

11. 特定关键字
    In:
    主查询where 条件 in(列子查询)
    any
    any(列子查询)，条件在查询结果中有任意一个匹配即可，等价于in
    <>any：条件不等于任何一个

some
与any完全一样，在国外some和any意思相同，但不定就完全不同了，not any 一点也不 not some一点点

all
all(列子查询),等于里面所有
<>all,不等于里面所有
select * from my_student where class_id in (select class_id from my_class);

select * from my_student where class_id =any (select class_id from my_class);

select * from my_student where class_id <>any (select class_id from my_class);

这里虽然是不等于，但相当于每一条数据只要有一个不符合，就输出，所以一样的。
select * from my_student where class_id =all (select class_id from my_class);
select * from my_student where class_id <>all (select class_id from my_class);

查询时如果数据为NULL，那么此数据不会进行匹配

12. 数据备份与还原
    mysql提供了一个专门用于备份sql的客户端，mysqldump.exe
    
    就在bin目录下
    sql备份是一种mysql非常常见的备份与还原方式，sql备份不只是备份数据，还备份对应的sql指令（表结构），即便数据遭到毁灭性的破坏（数据库被删），那么利用sql备份依然可以实现数据还原

sql备份因为需要备份结构，因此产生的备份文件特别大，因此不适合特大型数据备份，也不适合数据变换频繁型数据库备份。

因为用到专门的客户端，所以还没与数据服务器进行连接
mysqldump/mysqldump.exe -hPup 数据库名字[表1【表2。。。】] >备份文件地址
备份有三种形式：
1、整库备份
mysqldump.exe -hlocalhost -P3306 -uroot -proot mydatabase2>c:/server/temp/mydatabase2.sql
我测试报错，具体原因不知道

2、单表备份

3、多表备份
mysqldump -uroot -proot mydatabase2 my_student my_int >c:/server/temp/mydatabase2.sql

还是报错！

13. 数据还原
    1、mysql.exe-hPup 数据库 <文件位置
    mysql -uroot -proot mydb < c:/server/temp/mydatabase2.sql
    2、source SQL 文件位置//必须先进入到对应的数据库
    source c:server/temp/mydatabase2.sql
    3、人为操作：打开备份文件，复制所有SQL指令，然后到mysql.exe客户端中去粘贴执行（不推荐）

14. 用户管理
    用户权限管理，在不同的项目中给不同的角色，不同的操作权限，为了保证数据库数据的安全
    通常一个用户的密码不会长期不变，所以需要经常变更数据密码来确保用户本身安全（mysql客户端用户）

mysql用户的数据，都是放在mysql中的user表中
select * from mysql.user\G

15. 创建用户
    直接在mysql.user表中插入记录，但不推荐
    专门创建用户的指令：
    create user 用户名 identified by 密码;
    用户：用户@主机地址(host)
    主机地址：除了host，也可以是“”或%
    create user “user1”@"%" identified by “123456”;

select * from mysql.user\G;

发现user1已经创建成功了。

简化创建：不限定客户端ID，也没有密码
但这不安全，谁都可以访问
create user user2;

尝试登录：
mysql -uuser2;

16. 删除用户
    注意：mysql中user是带着host本身的（具有唯一性）
    drop user 用户名@host;
    

17. 修改用户密码
    提供了多种方式，但都需要系统函数password()
    需要靠该函数对密码进行加密处理
    1.使用专门的修改密码的指令
    set password for 用户=password(“新的明文密码”)
    2、更新语句修改
    update mysql.user set password=password(“新的明文密码”) where user =" and host="
    set password for ‘user1’@’%’ =password(‘654321’);
    
    
    123456无法登录，但654321可以登录，说明成功了

18. 权限管理
    mysql中将权限管理分为三类：
    1、数据权限：增删改查（select\update\delete\insert）一般只给数据权限的更多
    2、结构权限：结构操作（create\drop）
    3、管理权限：权限管理（create user\grant\revoke）：通常只给管理员如此权限

19. 授予权限：grant
    将权限分配给指定用户
    grant 权限列表on 数据库/表名/to用户
    权限列表：使用逗号分隔，但可以使用all privileges代表全部权限
    数据库表名：可以是单表（数据库名字.表名），可以是具体某个数据库（数据库）也可以整库（.*）
    grant select on mydatabase2.my_student to ‘user1’@’%’;
    
    
    用user1登陆，发现可以查看对应的数据库
    用户被授权后，不需要退出，就可以查看

20. 取消权限revoke
    权限回收：将权限从用户手中收回
    revoke权限列表/allprivileges on 数据库/*.表/*from 用户

revoke select on mydatabase2.my_student from ‘user1’@’%’;

取消所有权限
revoke all privileges on mydb.my_student from ‘user1’@’%’;
回收权限同样不需要刷新或者重新登录。

21. 刷新权限
    Flush:刷新，将当前用户的权限操作，进行一个刷新，将操作的具体内容同步到对应的表中。
    flush privileges;
    
    相当于把当前权限放到脚本里去，虽然之前设置的时候就已经完成了权限更改，但刷新相当于把权限设置写进了脚本。

22. 密码丢失的解决方案
    1、停止服务
    net stop mysql;
    我的电脑报错，之前net就没成功
    
    2、重新启动服务：mysqld.exe-skip-grant-talbes//启动服务器便是路过权限
    3、当前启动的服务器没有权限概念，非常危险，任何客户端，不需要任何用户信息都可以直接登录，而且是root权限：新开客户端，使mysql登录
    4、修改root用户密码：

update mysql.user set password=password(“root”) where user=“root” and host=“localhost”;

23. 外键
    外键：froeign key
    一张表（A）中有一个字段，保存的值指向另一张表（B）的主键
    B：主表
    A：从表
    增加外键：
    1:创建表的时候增加外键，类似主键
    字段后增加一条语句：
    constraint 外键名 froeign key[外键字段]references 主表（主键）
    这里的 外键名 用的是反引号在esc下面的~这个按键

2：创建表后增加外键
alter table 从表 and [constraint外键名] froeign key[外键字段]references 主表（主键）

增加外键
create table my_foreign(
id int primary key auto_increment,
name varchar(10) not null,
class_id int,
foreign key(class_id) references my_class(class_id)
)charset utf8;


这里的class_id 的KEY为MUL，为多索引的意思
多索引：外键本身是一个索引，外键还要求字段本身也是一个普通索引。

desc my_student;
desc my_class;

修改my_student表，将class_id设为外键字段
alter table my_student add constraint student_class_ibfk_1 foreign key (class_id) references my_class(class_id);

show create table my_student;

外键名字可以指定

23. 修改&删除外键
    外键不允许修改，只能先删除后增加
    alter table 从表 drop foreign key 外键名字
    alter table my_student drop foreign key student_class_ibfk_1;
    
    这里发现，虽然删除了外键，但my_student中class_id依然是MUL，外键创建会自动增加一个索引，但外键删除只会删除自己，不能删除生成的普通索引。

如果想删除对应的索引：alter table 表名drop index 索引名字
外键基本要求：
1、外键字段需要保证与关联的主表的主键字段类型完全一致
2、基本属性也要相同
3、如果在表后增加外键，对数据还要有一定的要求（从表数据与主表的关联关系）
4、外键只能使用innodb存储引擎，myisam不支持

24. 外键——约束
    通过建立外键关系之后，对主表和从表都会有一定的数据约束效率

从表不能插入外键所在字段，主表不存在的数据
主表不能删除一个被引入的数据
insert into my_foreign values(null,“小明”,1);
insert into my_foreign values(null,“小李”,5);

这里发现我两条都加进去了，关键问题应该是我的引擎是myisam
delete from my_foreign where name=“小李”;

修改引擎：
alter table my_foreign ENGINE=innodb;

alter table my_class ENGINE=innodb;

alter table my_foreign add constraint foreign_class_ibfk_1 foreign key (class_id) references my_class(class_id);

insert into my_foreign values(null,“小李”,5);

这时候就报了班级的错误！

25. 可以在创建外键的时候，对外键约束进行选择性的操作
    add froeign key(外键字段)references 主表（主键）on约束模式
    约束模式：
    1:district严格模式，默认的，不允许操作
    2:：cascade:级联模式，一起操作，主表变化，从表数据跟着变化
    3:：set null置空模式，主表变化（删除），从表对应记录设置为null，前提是从表中对应的外键字段允许为空

外键主要约束的是主表，从表主要不能插入主表不存在的数据

通常在进行约束的时候，需要指定操作：update和delete
常用的约束模式：on update ascade,on delete set null,更新级联，删除置空

alter table my_student add foreign key(class_id) references my_class(class_id);

——约束
on update cascade,
on delete set null;

外键比较少使用

26. 视图基本操作
    视图的本质是SQL指令（select语句）
    基本语法：createview 视图名字 as select 指令；
    //可以是意表数据，也可以是连接查询，联合查询或子查询
    create view student_class_v as select s.*,c.name from my_student as s left join my_class as c on s.class_id=c.class_id;
    报错：

视图本身是虚表，所以表的操作都适合于视图

27. 使用视图
    select 字段列表 from 视图名字【子句】

28. 事务安全
    mysql允许统计事物管理，但只能innodb引擎
    将用户所做的操作，暂时保存起来，直到最后确定才真正保存
    通常事物是自动提交的，但也可以使用手动事物

29. 自动事务，用户将一条SQL指令发到服务器的时候，服务器在执行之后，不用等待用户反馈结果，会自动将结果同步到数据表。
    证明：两个客户端，一个执行SQL，另一个查看执行结果

30. 查看是否自动事务
    autocommit:
    show variables like “autocommit”;
    
    这是可以更改的！

31. 关闭自动事物；关闭之后系统就不在帮助用户自动提交结果了
    set autocommit =off;
    
    这时候，如果我们添加数据，本地能看到本地的变化，但并没有存储到数据中，另外的客户端是看不到数据更新的，一旦事物关闭，那么需要用户提供是否同步的命令
    commit:提交：同步到数据表，事务也会被清空
    rollback:回滚（清空之前的操作，不要了）

一般不会关闭自动事物，只会在需要事务处理的时候，才会进行手动事务

32. 手动事务：
    不管开启、过程还是结束，都需要用户（程序员），手动的发送事务操作指令来实现
    开启事务：start transaction
    
    事务处理：
    执行事物：
    将多个连接，但是一个整体的SQL指令，逐一执行
    1、事务操作
    2、新增数据
    
    
    
    把小江的班级设为6
    

事务提交：commit

回滚：rollback

回滚点：
当一系列步骤中，如果后面失效了，但前面不需要回滚的话，可以设置一个回滚点。
增加回滚点：
savepoint 回滚点名字
回滚到回滚点：
rollback to回滚点名字

在一个事务中，可以设置多个回滚点，但是如果回到了前面的回滚点，那么 后面的回滚点就失效了，相当于已经被清除了。
savepoint sp1;

update my_student set class_id =3 where stu_id=“stu001”;
此处我们假设要修改的是山姆，但我不小心写成了夏洛的编号：

此时我们回到回滚点：
rollback to sp1;

33. 事务特点：
    原子性、一致性、带离性、持久性
    
    隔离性，指的是当前用户操作的数据，其它人是无法操作的

34. 系统变量
    mysql本质是一门编程语言，需要变量来保存数据
    系统变量 ：
    系统变量有很多
    选择系统变量的方法：
    select @@变量 @@变量称为全局变量
    select @@autocommit
    
    修改系统变量：
    局部修改（会话级别）只针对当前客户端当次连接有效；

基本语法：set 变量名=新值
全局修改：针对所有的客户，“所有时刻”都有效
基本语法：
set global变量名=值；
set @@global.变量名=值；

会话变量:
只针对当前使用的客户端有效
set@变量名=值
set @name=“hello world”;

在mysql中没有==来判断是否等于，所以=号会有判断和赋值两个功能，有时候就会在=赋值的时候报错，所以mysql提供了一个客户的赋值号：:=
set @age:=1;

mysql允许将查询到的数据（只能一行），放到变量中，一个变量对应一个字段值，mysql没有数组
赋值且查看赋值流程，select@变量1=字段1，@变量2=字段2from数据表where 条件
select @name

select @name=stu_name,@age=stu_age from my_student limit 1;
以上是错误语法：
就是因为使用了=，系统当作了比较符号
select @name:=stu_name,@age:=stu_age from my_student limit 1;
只赋值，不看过程select字段1，字段2……from数据源where条件into@变量1@变量2

局部变量：
作用范围在begin和end之间，
是用declare 生成的
语法：declare 变量名 数据类型[属性]

35. if分支
    if在mysql中有两种语法：
    1:在select查询当中，当作一种查询来进行判断
    if(条件，为真结果，为假结果)
    mysql> select *,if(stu_age>17,“符合”,“不符合”) as judge from my_student where stu_age>17;
    
    select *,if(stu_age>17,“符合”,“不符合”) as judge from my_student;
    

2:用在复杂的语句块中（函数/存储过程/触发器）

if 条件表达式 then
满足条件要执行的语句
end if;

3：复合语法
if 条件表达式 then
满足条件要执行的语句
else
不满足条件要执行的语句
end if;

36. while循环
    while 条件 do
    循环执行的代码
    End while;
    都需要在代码块里执行

37. 结构标识符

结构标识符，为某些特定的结构进行命名，然后为的是在某些地方使用名字

基本语法：
标识符名：while 条件
循环
End while[标识符名];

标识符存在，主要是为了循环体中使用循环控制，在mysql中没有continue和break,但有自己的关键字替代：
iterate迭代，就是以下的代码不执行，重新开始循环，相当于continue
leave离开，整个循环终止，相当于break

标识名称：while 条件 do
if 条件判断 then
循环执行的代码
iterate/continue 标识名称
循环执行的代码
End while【标识名称】;

38. 函数
    系统函数/内置函数
    自字义函数
    无论是哪种函数，都是通过select 函数名(参数) 实现

39. 内置函数
    
    mysql> select char_length(“你好中国”),length(“你好中国”);
    
    select concat(“你好”,“中国”),instr(“你好中国”,“你”),instr(“你好中国”,“我”);

mysql> select lcase(“aBcD”),left(“你好中国”,2);

mysql> select ltrim(" a dbc "),mid(“你好中国”,2);

40. 时间函数
    
    mysql> select now(),curdate(),curtime();
    
    mysql> select datediff(“2010-10-10”,“1990-10-10”);
    
    mysql> select date_add(“2000-10-10”,interval 10 second);
    
    day/hour/minute/second

unix_timestamp:获取时间戳

mysql> select from_unixtime(1234567890);

41. 数学函数

select abs(-1),ceiling(1.1),floor(1.1),pow(2,4),rand(),round(1.5);

42. 其它函数

select md5(“a”),version(),database(),uuid();