必备sql和表关系及授权

graph LR
执行1[必备sql和授权]
执行2[SQL强化和实践]
执行3[索引和函数以及存储过程]
执行4[Python操作mysql和应用]
执行5[常见SQL语句]
执行6[表关系]
执行7[授权管理]
执行8[一对多]
执行9[多对多]
Mysql --> 执行1
Mysql --> 执行2
Mysql --> 执行3
Mysql --> 执行4
执行1 --> 执行5
执行1 --> 执行6
执行1 --> 执行7
执行6 --> 执行8
执行6 --> 执行9

简要：

必备SQL（八个必备）
表关系
授权

1.必备SQL语句

通过下面这两张表进行SQL语句延申测试

depart表跟info表

create database test default charset utf8 collate utf8_general_ci;

在test数据库创建两个需要关联的表

use test;

create table depart (

    id int not null  auto_increment primary key,

    title varchar(16) not null

)default  charset utf8;

create table info (

    id int not null auto_increment key,

    name varchar(16) not null,

    email varbinary(32) not null,

    age int,

    depart_id int

);

插入数据

use test;

insert into depart(title) value ("开发"),("运营"),("销售");

insert into info(name,email,age,depart_id) value("吴佩琦","wupeiqi@live.com",19,1);

insert into info(name,email,age,depart_id) value("张三","zhangsan@live.com",49,1);

insert into info(name,email,age,depart_id) value("李氏","lishi@live.com",9,2);

insert into info(name,email,age,depart_id) value("王五","wangwu@live.com",29,1);

insert into info(name,email,age,depart_id) value("李杰","lijie@live.com",39,3);

insert into info(name,email,age,depart_id) value("超","cao@live.com",49,1);

insert into info(name,email,age,depart_id) value("关羽","guanyu@live.com",49,1);

1.1.条件

根据条件搜索结果

select * from info where id > 1;

select * from info where id = 1;

select * from info where id >= 1;

select * from info where id != 1;

select * from info where id between 2 and 4;  -- id 大于2且小于4

select * from info where name = "张三" and age = "49";

select * from info where name = "张三" or age = "49";

select * from info where (name = "张三" or email = "zhangsan@live.com") and age = "49";

select * from info where id in (1,4,6); -- 多个取值

select * from info where name in ('吴佩琦','王五','超');   -- 或者这样除数字外条件需要单引号

select * from info where id in (select id from depart);  -- 联合查询info表的id字段跟depart表的所有id字段匹配

select * from info where exists (select * from depart where id = 5); -- exists判断相当于if,如果depart表有id=5的字段成立进行查询，如果depart表有id=5的字段不成立不进行查询

select * from info where not exists (select * from depart where id = 5); -- exists判断相当于取反

-- 不直接查莫一张表，先通过SQL查询到这张表的数据然后再做筛选

select * from (select * from info where id > 5) as T where T.age > 10;  -- as T 这个T相当于括号里面查询出来后重新定义出来的临时表

select  * from info where info.id > 5; -- where 条件的时候用表名加字段,一般用于多表联合查询时区分info.id

1.2.通配符

如果数据量小可以使用下面的通配符方法的sql进行查询，如果数据量大一般需要工具进行查询，如使用es

select * from info where name like "%王%";  -- %取任意长度的任意字符

select * from info where name like "王%";   -- 匹配后面任意长度的任意字符，如果%在前面的任意字符就不存在

select * from info where name like "%佩琦";

select * from info where name like "吴%琦";

select * from info where email like "__peiqi@live.com";  -- 一个下划线代表一个字符

select * from info where email like "__peiqi_live.com";

1.3.映射

-- max/min/sum

-- 注意少用select * 这种方法在程序里面去查数据，根据需求几列获取几列

select

       id,

       name,

       666 as num,                          -- 添加一个字段数据，并定义字段名为num

       (select max(id) from depart) as mid, -- 定义depart表的id最大值为mid字段数据

       (select min(id) from depart) as nid, -- 定义depart表的id最小值为nid字段数据

       (select sum(id) from depart) as sid, -- 定义depart表的id的和为sid字段数据

       age

from info;

-- 下面这两种查询方式效率比较低程序用的比较少

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 else的使用方法，相当于if,then(如果成立就是什么)

select

       id,

       name,

       case depart_id when 1 then "第一部门" end v1

from info;

select

       id,

       name,

       case depart_id when 1 then "第一部门" end v1,

       case depart_id when 1 then "第一部门" else "其它" end v2,

       case depart_id when 1 then "第一部门" when 2 then "第二部门" 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 < 39 then "青年" else "油腻男" end v6

from info;

1.4.排序

-- order by (先查询再进行排序)

select * from info order by age desc;      -- 倒序排列

select * from info order by age asc;       -- 顺序排列(默认顺序)

select * from info order by age asc,id desc;  -- 有限安装age进行从小到大，如果age相同则按照id从大到小

select * from info where id > 5 order by age asc,id desc;  -- 添加个筛选条件在进行排序

select * from info where id > 10 or info.email like '%guan%' order by age asc,id desc;

1.5.取部分

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

select * from info order by id desc limit 3;      -- 先排序再取前三行

select * from info where id >5 order by id desc limit 3;

select * from info limit 3 offset 2;              -- 从位置2开始，向后获取前3条

1.6.分组

-- group by

select age,max(id),min(id),sum(id) from info group by age;

巴特西

MySQL数据库SQL语法常规操作