完整的查询语句

基本的查询语句
select(*|字段名|四则运算|聚合函数) from表名称)
取别名 当字段名显示较长时 可以添加别名
    select 字段名 as 别名 from 表名称;
    as可以省略
    select 字段名 别名 from 表名称

计算每个人的总分
计算每个人的各科目平均分



增
    insert into 表名称(字段...)values(值...),(值...);
        values中的值的个数和类型必须与前方声明的字段一致
    insert into 表名称 values(值...);
        values中的值的个数和类型必须与表的字段完全一致

    into 可以省略
    stu name char(20) default,age int
    #insert into table_name values(20),(30),(40);
    错误name 虽然有默认值 但是这种语法下也必须赋值

删
    delete from table_name where 条件;
    delete from table_name;
    truncate table table_name;
改
    update table_name set 字段名称=新的值 where 条件;
    update table_name set 字段名称=新的值[字段2=值2...];

查



select语句的完整语法;
    select [distinct] (*|字段|四则运算|聚合函数)from 表名
        where 查询条件
        group by  分组
        having    分组后的过滤
        order by  排序
        limit     限制获取的条数

    必须的 select 字段 from 表名
    distinct 去处重复

    强调:书写顺序必须按照上面的来

    执行顺序与书写顺序不一致

    def select():
        from()
        where()
        group by()
        having()
        order by()
        limit()
        distinct()#对完全相同的数据去重

    #从文件读取数据
    def from();
        with open('表名称')
        pass
    #过滤从文件中读取的数据
    def where():
        pass
    #分组
    def group by()
        pass
    #对分组后的数据进行过滤
    def having();
        pass
    #排序
    def order by():
        pass
    #控制获取的条数
    def limit():
        pass
    #取出重复
    def distinct():
        pass


where 子查询
    = > < >= <= !=(<>)

    in between and like
    and or not

select *from stu where not(math != 60);
#in(1,2,3,4,5)
#math  60 80 90
select *from stu where math in(60,80,90);
select *from stu where math = 60 or math = 80 or math = 90;

#英语及格并且 数学也及格
select *from stu where math >= 60 and english >= 60;


#数学 在60-80之间
select *from stu where math between 60 and 90;
select *from stu where math >= 60 and math <= 90;

 

like 长的像 模糊匹配
%任意个任意字符
_一个任意字符

select *from stu where name like "李%"; 所有姓李的
select *from stu where name like "%李%"; 名字带有李的
select *from stu where name like "%李"; 最后一个字是李的

group by分组查询
什么是分组
    把一个整体 按照某个标识分成不同的部分

分组目的
    通常分组都为了要统计数据
语法
    select * from emp group by 某个字段;

强调:
    用于分组的字段 通常应该是重复度高的 例如部门 性别
    而不应该是id name....

语法要求;
    select 后面的字段 必须是出现在group by后面的字段

select dept from emp group by dept;

一旦分组后 组内的详细数据就被隐藏了 无法直接查看但是要知道 分组不是为了查看 而是为了统计
分组后就只能看到分组的那个字段

聚合函数(统计函数)
    给他一堆数据 他统计后返回一个数据
    (1,2,3,4,5) sum=15

    sum 求和
    avg 平均数
    max 最大值
    min 最小值
    count 计数

select sum(salary) as 总月薪 from emp
select dept,max(salary) from emp group by dept


#查询每个部门有哪些人
group_concat()
select dept,group_concat(name) from emp group by dept;

#可以有多个分组依据 比如先按部门 再按工作岗位

注意注意 :聚合函数不能用在where后面

如果要对分组数据进行过滤 必须使用having
因为 where在执行时 数据还没有读完,必须等到数据读完之后再进行过滤
where 与having的区别就在于 执行时机不同

select dept,group_concat(name),count(*) from emp group by dept having count(*) < 3;


group by
    where 后不能有聚合函数
    select 后面的字段必须出现在group by后面
    通常聚合函数会与group by 连用



伪代码:
from 从文件读数据 到内存
datas = []
with open("xxxx") as f:
    while True:
        data = f.read()
        if avg(salary) > 5000;# 数据读取没有完成 不可能统计出结果
            datas.append(data)



oder by 排序 默认为升序
select * from emp order by salary;
用desc来指定为降序
select * from emp order by salary desc;
可以定义多个字段作为排序依据,并且可以单独设置顺序
select * from emp order by salary desc,id; #先按照工资降序 如果工资相同则按照id 升序


limit 控制要查询的记录数量
select * from emp limit a,b;

a表示其实位置
b要获取的条数
如果只有a则表示

#分页显示
总共为10页 每一页显示3条 页数10/3 有余数则+1=4

page=1

select * from emp limit 0,3


page=3
(page -1)*3
select * from emp limit 6,3


起始位置的计算公式
limit (页数-1) * 每页的条数,每页条数





distinct 去除重复
where  过滤条件
group by 分组
having  分组后的过滤条件
order by 排序
limit 控制获取的条数

聚合函数
    max
    min
    sum
    avg
    count(*)

name   sex
张三    男
张无忌  null
count(sex)?   结果为1
所以 count 会忽略null值   如果要统计数量用count(*) 最好


正则表达式匹配 (也用于模糊匹配)
insert into emp values(null,"laowangba","男","财务","总监",5800);
insert into emp values(null,"laoliba","男","市场","总监",5800);
insert into emp values(null,"laocheng","男","后勤","总监",5800);

语法:
 select *from emp regexp "表达式";
 select *from emp where name regexp ".*ba$";
like 只有%和_ 灵活度没有regexp高


博客:id title content submit_date author
搜索功能案例:
博客:
    create table blog
    (id int primary key auto_increment,
    title char(100),
    content varchar(10000),
    submit_date timestamp,
    author char(20));

    web log

模糊查询语句(搜索功能对应的语句)!  搜索关键字为py
select *from blog where title like "%py%" or content like "%py%" or author like "%py%";


多表查询

    例如:dept 和 emp 表
             员工表中保存dept的主键

1.笛卡尔积查询
    积表示乘积的意思
    把两个表中的所有数据 全部建立关联关系
    a表有一条 b表有三条 总数据量为1*3=3条

    可以保证肯定有一条关联关系是正确的,但是同时会产生大量的错误数据,
    我们需要加以过滤 来得到正确的数据

select * from emp,dept where dept_id =id;#两个表都有id字段 所以条件中必须指明表名称如下
select * from emp,dept where dept_id =dept.id

连接查询
join
select * from dept join emp;
on==where  只能用于连接查询
#如果是用来筛选匹配关系 建议用on


左外连接查询
    select * from dept left join emp on 条件;
    左表无论是否匹配,都全部显示 右边仅显示匹配成功的
    select * from dept left join emp on dept_id=dept.id;
    查看所有部门及部门下的所有人员信息

右外连接查询
    select * from dept right join emp on 条件;
    右表无论是否匹配,都全部显示 左边仅显示匹配成功的

全外连接查询
左右两边 无论是否匹配都要显示
mysql中不支持 全外连接 oracle 中为full join
mysql 可以通过 union 合并查询 来合并左外连接 和右外连接的查询结果

union 合并查询 合并查询结果 并且去除重复的 *******
        select *from dept left join emp on dept_id = dept.id
        union
        select *from dept right join emp on dept_id = dept.id;

union all 合并但不去除重复
        select *from dept left join emp on dept_id = dept.id
        union all
        select *from dept right join emp on dept_id = dept.id;

注意:union语句 必须使用在两个字段数量相同的情况下


# 查询1号部门的名称和其所有员工名称;
# 先将两个表所有数据 连在一起
# 通过on来筛选正确的匹配关系
# 通过where 来过滤 部门id 为 1的数据
select * from dept join emp on dept_id = dept.id where dept.id = 1;

后续无论是几个表 第一步都是先连接查询 第二步也必然是筛选正确匹配条件 最后根据实际需求添加额外条件即可

select * from 表1 join 表2 on 表1的外键id= 表2 的主键id;



如果是多对多关系 那么要获取数据 得查三个表



套路:
    1.先把三个表全都连在一起    select * from stu join tsr join tea
    2.用on来筛选正确关系       on stu.id = tsr.s_id and tea.id = tsr.t_id
    3.通过where 添加额外的条件 where tea.name = "egon"