sql语句和表关系及授权

1.数据库与表的创建

1.1.创建数据库

create database day22db default charset utf8 collate utf8_general_ci;

1.2.创建表

create table depart(
	id int not null primary key auto_increment,
	title varchar(16) not null
)default charset=utf8;

create table info(
    id int not null primary key auto_increment,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int
)default charset=utf8;

1.3.插入数据

mysql> insert into depart(title) values("开发"),("运营"),("销售");
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

2.表查询相关操作

2.1运算符

根据条件搜索结果where

• >

  -- 查找年龄大于30
  select * from info where age>30;
  -- 查找id大于1
  select * from info where id>1;
  

• =

  -- 查找id等于1
  select * from info where id=1;
  

• >=

  -- 查找id大于等于1
  select * from info where id>=1;
  

• !=

  -- 查找id不等于1
  select * from info where id!=1;
  

• between and

  -- 查找2<=id<=4
  select * from info where id between 2 and 4;
  

• and

  -- 查找name="wxy" 并且 age=19
  select * from info where name="wxy" and age=19;
  

• or

  -- 查找name="wxy" 或者 age=49
  select * from info where name="wxy" or age=49;
  -- 查找name="wxy" 或者 email="123@qq.com" 里面 age=49
  select * from info where (name="wxy" or email="123@qq.com") and age=49;
  

• in

  -- 查找id 等于1或4或6
  select * from info where id in (1,4,6);
  -- 查找id 在depart表中存在的id
  select * from info where id in (select id from depart);
  

• not in

  -- 查找id不等于1或4或6
  select * from info where id not in (1,4,6);
  

• exists

  -- 查找是否有select * from depart where id=5，如果有，就查询select * from info
  select * from info where exists(select * from depart where id=5);
  

• not exists

  -- 查找是否有select * from depart where id=5，如果没有，就查询select * from info
  select * from info where not exists(select * from depart where id=5);
  

• 其他

  -- 查找id>2 里面的age>10的信息
  select * from (select * from info where id>2) as T where age>10;
  

  -- 一张表查询2种表达方式都可以，多个表查询有相同的列就用第一种表达方式
  -- 查找info中id>10的
  select * from info where info.id>10;
  -- 查找表中id>10的
  select * from info where id>10;
  

2.2通配符

用于模糊查询

• %【匹配表示0到多个】

  select * from info where name like '%wxy';
  

• _【匹配一个】

  select * from info where name like "_wxy";
  

2.3映射【取别名】

• 获取自己想要的列

    -- 只获取id,name两列
    select id,name from info;
    -- 将name列起别名为n
    select id,name as n from info;
    -- 增加一列123,所有值都为123
    select id,name as n,123 from info;
  

  -- 查找id,name,添加num列值为666，
  -- 添加mid列值为depart表中id的最大值，
  -- 添加nid列值为depart表中id的最小值
  select id,
  name,
  666 as num,
  (select max(id) from depart) as mid,
  (select min(id) from depart) as nid,
  age 
  from info;
  

  -- 查找id,name，添加一列x1,info中的depart_id=depart中的id为条件，找depart表中的title作为x1的值
  
  select id ,
  name,
  (select title from depart where depart.id=info.depart_id) as x1
  from info;
  

  select 
  id ,
  name,
  (select title from depart where depart.id=info.depart_id) as x1,
  (select title from depart where depart.id=info.id) as x2 
  from info;
  

• case when then end

  -- 添加一列为v1,当depart_id=1时，将v1的值赋值为 "第1部门" 
  
  select id,
  name,
  case depart_id when 1 then "第1部门" end v1
  from info;
  

• case when then else end

  -- 添加一列为v2,当depart_id=1时，将v2的值赋值为 "第1部门" ,否则赋值为 "其他"
  select 
  id,
  name,
  case depart_id when 1 then "第1部门" else "其他" end v2 
  from info;
  

• case when then ... when then ... when then ... else... end

  -- case when then ... when then ...  when then ... else... end 
  
  select id,
  name,
  case depart_id when 1 then "第一部门" end v1,
  case depart_id when 2 then "第1部门" else "其他" end v2,
  case depart_id when 1 then "第一部门" when 2 then "第2部门" else "其他" end v3,
  case when age<18 then "少年" end v4,
  case when age<18 then "少年" else "油腻男" end v5,
  case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
  from info;
  

2.4排序(order by)

• desc【降序】

  select * from info order by age desc;
  

• asc【升序】

  select * from info order by age asc;
  

• 多条件排序

  select * from info order by age asc,id desc;
  select * from info where id>6 or name like "%y" order by age asc,id desc;
  

2.5 取部分(limit)

一般用于获取部分数据

• limit

  -- 取前五条
  select * from info limit 5;
  

• limit offset

  -- 从第二个位置开始取，取三个，offset从0开始
  select * from info limit 3 offset 2;
  

2.6分组

• group by

  select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;
  select age,count(1) from info group by age;
  select depart_id,count(id) from info group by depart_id;
  

• having【先执行分组再判断条件】

  select depart_id,count(id) from info group by depart_id having count(id)>2;
  

  select age,max(id) from info group by age;
  -- 只获取根据年龄分组的获取最大的id
  select * from info where id in (select max(id) from info group by age);
  

  -- 根据age分组，再查找count(id)>2 
  select age,count(id) from info group by age having count(id)>2;
  -- 聚合条件，having放后面
  select age,count(id) from info where id>4 group by age having count(id)>2;
  

  select age,
  count(id)
  from info where id>2 group by age having count(id)>1 order by age desc limit 1;
  - 要查询的表info
  - 条件 id>2
  - 根据age分组
  - 对分组后的数据再根据聚合条件过滤 count(id)>1
  - 根据age从大到小排序
  - 获取第1条
  

2.7左右连表

多表查询

• 主表

  主表 left outer join 从表 on 主表.x=从表.id
  

  -- 左表连接，左边是主表
  -- 右表连接，右边是主表
  select * from info left outer join depart on info.depart_id=depart.id;
  select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id=depart.id;
  

• 从表

  从表 right outer join 主表 on 主表.x = 从表.id
  select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id=depart.id;
  

2.8联合

• 列数需相同【union】

   select id,title from depart 
   union 
   select id,name from info;
  

• 【union】自动去重

   select id,title from depart 
   union 
   select email,name from info;
  

• 获取所有【union all不去重】

  select id from depart
  union all 
  select id from info;
  

3.表关系

3.1 一对多

• 需要两张表来存储信息，且两张表存在一对多或多对一关系
  • constraint fk_info1_depart1 foreign key (depart_id) references depart1(id)

  create table depart1(
      id int not null auto_increment primary key,
      title varchar(16) not null
  )default charset=utf8;
  
  create table info1(
      id int not null auto_increment primary key,
      name varchar(16) not null,
      email varchar(32) not null,
      age int,
      depart_id int not null,
      constraint fk_info1_depart1 foreign key (depart_id) references depart1(id)
  ) default charset=utf8;
  -- 如果表结构创建好了，额外添加外键
  alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);
  -- 删除外键
  alter table info drop foreign key fk_info_depart;
  

3.2 多对多

• 需要三张表来存储信息，两张单表+关系表，创造出两张单表之间多对多关系

  create table boy(
      id int not null auto_increment primary key,
      name varchar(16) not null
  )default charset=utf8;
  
  create table girl(
      id int not null auto_increment primary key,
      name varchar(16) not null
  )default charset=utf8;
  
  create table boy_girl(
      id int not null auto_increment primary key,
      boy_id int not null,
      girl_id int not null,
      constraint fk_boy foreign key boy_girl(boy_id) references boy(id),
      constraint fk_girl foreign key boy_gril(girl_id) references girl(id)
  )default charset=utf8;
  -- 创建表之后添加外键
  alter table boy_girl add constraint fk_boy foreign key boy_girl(boy_id) references boy(id);
  alter table boy_girl add constraint fk_girl foreign key boy_girl(girl_id) references girl(id);
  -- 删除外键
  alter table boy_girl drop foreign key fk_boy;
  alter table boy_girl drop foreign key fk_girl;
  

4.授权

在mysql中支持创建账户，并给账户分配权限
例如：只拥有数据库A操作的权限,只能操作数据库A
只拥有数据库B中某些表的权限，只拥有数据库B中某些表读的权限等

4.1用户管理

user表中存储着所有的账户信息(含账户，权限等)

show databases;
select user,authentication_string,host from mysql.user;

• 创建和删除用户

  -- create user '用户名'@'连接者的ip地址' identified by '密码';
  -- drop user '用户名'@'连接者的ip地址';
  create user wxy@127.0.0.1 identified by '123456';
   drop user wxy@127.0.0.1;
  

• 修改用户

  -- rename user '用户名'@'ip地址' to '新用户名'@'ip地址';
  rename user wxy1@127.0.0.1 to wxy@127.0.0.1;
  

• 修改密码

  -- set password for '用户名'@'ip地址'=Password("新密码");
    -- 8.0.23版本
  ALTER USER 'wxy'@'127.0.0.1' identified with mysql_native_password by '123456';
  

4.2授权

• grant 权限 数据库.表 '用户名'@'ip地址';

  -- 所有数据库权限
  grant all privileges on *.* to 'wxy'@'localhost'
  -- 数据库day26所有权限
  grant all privileges on day26.* to 'wxy'@'localhost'
  -- 数据库day26中info表所有权限
  grant all privileges on day26.info to 'wxy'@'localhost'
  
  
  -- 数据库day26中info表查询权限
  grant select on day26.info to 'wxy'@'localhost'
  -- 数据库day26中info表查询和插入权限
  grant select,insert on day26.info to 'wxy'@'localhost'
  
  注意：flush privileges;-- 将数据库读取到内存中，从而立即生效
  

• 对于权限

  all privileges  除grant外的所有权限
  select          仅查权限
  select,insert   查和插入权限
  ...
  usage                   无访问权限
  alter                   使用alter table
  alter routine           使用alter procedure和drop procedure
  create                  使用create table
  create routine          使用create procedure
  create temporary tables 使用create temporary tables
  create user             使用create user、drop user、rename user和revoke  all privileges
  create view             使用create view
  delete                  使用delete
  drop                    使用drop table
  execute                 使用call和存储过程
  file                    使用select into outfile 和 load data infile
  grant option            使用grant 和 revoke
  index                   使用index
  insert                  使用insert
  lock tables             使用lock table
  process                 使用show full processlist
  select                  使用select
  show databases          使用show databases
  show view               使用show view
  update                  使用update
  reload                  使用flush
  shutdown                使用mysqladmin shutdown(关闭MySQL)
  super                   使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆
  replication client      服务器位置的访问
  replication slave       由复制从属使用
  

• 对于数据库和表

  数据库名.*            数据库中的所有
  数据库名.表名          指定数据库中的某张表
  数据库名.存储过程名     指定数据库中的存储过程
  *.*                  所有数据库
  

• 查看授权

  show grants for '用户名'@'ip地址'
  
  show grants for 'wxy'@'localhost'
  

• 取消授权

  revoke 权限 on 数据库.表 from '用户名'@'ip地址'
  
  revoke ALL PRIVILEGES on day26.* from 'wxy'@'localhost';
  
  revoke ALL PRIVILEGES on day26db.* from 'wxy'@'%';
  注意：flush privileges;   -- 将数据读取到内存中，从而立即生效。
  

5 mysql执行顺序

到目前为止SQL执行顺序：

• join
• on
• where
• group by
• having
• order by
• limit