SQL子查询、外键、权限、视图

1、子查询

概念：就是查询语句里面又有一个查询语句
基本语法：select * from 数据源 where 条件判断 =/<> (select 字段名 from 数据源 where 条件判断);

• 分类：
  1）标量子查询：子查询返回结果是一个数据（一行一列）
  2）列子查询： 返回结果是一行多列
  3）行子查询：返回结果是一列多行
  4）表子查询：返回结果是多行多列
  5）exists子查询：返回0或者1

1.1标量子查询

grouptest：class2：

• 比如想知道一个学生名字，想查询所在的班级ID

select * from class2 where class_name =(
select class from grouptest where name ='欧阳翠花'
);

1.2、列子查询

基本语法：主查询 where 条件 in (列子查询);
从上面的两个表可以知道，物联网5班是没有人的

• 查询有学生的班级：

select class_id,class_name from class2 where class_name in(
select class from grouptest
);

1.3、行子查询

基本语法：主查询 where 条件[（构造一个行元素）] = (行子查询);
查询年龄最大且身高最高的学生

select * from grouptest having (age,height) = (select max(age),max(height) from grouptest);

如果没有年龄最大且身高是最高的，返回值为NULL

1.4、表子查询

暂时没找到例子emmmm

1.5、exists子查询

也没找到例子emmm

1.6、关键词的使用

• in
  主查询 where 条件 in(列子查询);
• any
  = any(列子查询 )：条件在查询结果中有任意一个匹配即可
  <>any(列子查询)：条件在查询结果中不等于任意一个
  1 =any(1,2,3) ===== true
  1 <>any(1,2,3) ===== true
• some
  与any完全一模一样
• all
  = all(列子查询)：等于里面所有
  <>all(列子查询)：不等于其中所有
  ps:如果有匹配字段为NULL，那么其不参与匹配

2、数据库的备份与还原（失败）

基本语法：mysqldump -h地址 -P端口 -u用户名 -p密码 数据库名字 [表1 [表2…]] > 备份文件地址

• 整库备份（只需要提供数据库名字）
• 单表备份：数据库后面跟一张表
• 多表备份：数据库后跟多张表

mysqldump -hlocalhost -P3306 -uroot -proot mydatabase2 > e:/temp/mydatabase.sql;

3、用户权限管理

3.1、用户管理

select * from mysql.user;

3.2、创建用户

基本语法：create user 用户名@主机地址 identified by ‘明文密码’;

--用户名user1，密码123456
create user 'user1'@'%' identified by '123456';

3.3、删除用户

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

drop user 'user1'@'%';

3.3、修改密码

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

• 使用基础语法修改密码

set password for 'user1'@'%'=password('654321');

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

update mysql user set password=password('123456789') where user='user1' and host='%';

4、权限管理

4.1、授予权限

基本语法：grant 权限列表 on 数据库 / * . 表 名 / * to 用户;
权限列表：使用逗号分隔，但是可以使用all privileges代表全部权限
数据库.表名：可以是单表（数据库名字.表名），可以是具体某个数据库（数据库 . * ），也可以整库 （ * . * ）

grant select on mydatabase2.grouptest to 'user1'@'%';
show databases;

4.2、取消权限

基本语法：revoke 权限列表/all privileges on 数据库 / * . 表 / * from 用户;

revoke all privileges on mydatabase2.grouptest from 'user1'@'%';

4.3、刷新权限

基本语法：flush privileges;

flush privileges;

4.4、找回密码

如果忘了root的密码，就需要重置密码

• 1）停止服务
  
• 2）重新启动服务：mysqld.exe –skip-grant-tables //启动服务器但是跳过权限
  
• 3）新开客户端，使用mysql.exe登录即可
  这里非常危险，任何客户端，不需要任何用户信息都可以直接登录，而且是root权限
  
• 4）修改密码
  
• 5）重启服务
  

5、外键

表B中字段1是主键，但在表A中字段1不是主键，这个时候，在表A中，字段1就是外键。简单的说，在主场是主键，客场是外键

5.1、增加外键

两种方法创建外键

• 1）在创建表的同时增加外键
  基本语法：在字段之后增加一条语句
  
  首先我们先创建两个表

--班级表，class_id在班级表是主键
create table class (
class_id varchar(10) primary key,
class_name varchar(10),
class_num tinyint
);

--学生表,Sid是主键，class_id是外键，但学生表的外键是班级表的主键
create table student(
Sid varchar(10) primary key,
Snamee varchar(10),
Sage tinyint,
Sheight int,
class_id varchar(10),
foreign key (class_id) references  class(class_id)
);

• 2）方案2：在创建表后增加外键
  

--老师表,Tid是主键，class_id是外键，但老师表的外键是班级表的主键
create table teacher(
Tid varchar(10) primary key,
Tnamee varchar(10),
Tage tinyint,
Theight int,
class_id varchar(10)
);

--增加外键
alter table teacher add foreign key(class_id) references class(class_id);

5.2、删除主键（失败）

基本语法：alter table 从表 drop foreign key 外键名字;

5.3、外键约束

作用：当一个表产生变化时，另外一张表也能随之变化，可以保证数据的统一性与正确型
基本语法： add foreign key(外键字段) references 主表(主键) on 约束模式;

• 约束模式：
  1）district：严格模式，默认的，不允许操作
  2）cascade：级联模式，一起操作，主表变化，从表数据跟着变化
  3）set null：置空模式，主表变化（删除），从表对应记录设置为空：前提是从表中对应的外键字段允许为空

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

alter table student add foreign key(class_id) 
references class(class_id) 
on update cascade
on delete set null;

--删除C004班
delete from class where class_id='C004';

班级表删除了C004班，学生表也会自动一起变为NULL的

6、视图

6.1、创建视图

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

create view SC_view as
select s.Sid,s.Snamee,c.class_id,c.class_name from student as s 
left join class as c on s.class_id=c.class_id;

6.2、使用视图

基本语法：select 字段列表 from 视图名字 [子句];
就像平时使用数据表一样就行了

6.3、修改视图

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

6.4、删除视图

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