增删改查

基本的查询语句
    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(*|字段名|四则运算|聚合函数)from 表名称;
        where 查询条件
        group by 分组
        having 分组后的过滤
        order by 排序
        limit 限制获取的条数
必须的 select 字段 from 表名
distinct 去除重复
强调：书写顺序必须按照上面的来
    执行顺序与书写顺序不一致

where 子查询
    =  >  <  >=  <=  !=(<>)
    in
    between and
    and or not

create table stu(id int primary key auto_increment,name char(10),math float,english float);

insert into stu values(null,"赵云",90,30);

insert into stu values(null,"小乔",90,60);

insert into stu values(null,"小乔",90,60);

insert into stu values(null,"大乔",10,70);

insert into stu values(null,"李清照",100,100);

insert into stu values(null,"铁拐李",20,55);

insert into stu values(null,"小李子",20,55);

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 某个字段;
强调：
    用于分组的字段通常应该是重复的度高的 例如 部门 性别
语法要求：
    select 后面的字段必须是出现在group by 后面的字段
一旦分组后 组内的详细数据就被隐藏了无法直接查看但是要知道 分组不是为了查看 而是为了统计
分组后就只能看到分组的那个字段

聚合函数(统计函数)
    给他一堆数据 它统计后 返回一个数据
    (1,2,3,4,5) sum =15
    sum 求和
    avg 平均数
    max 最大值
    min 最小值
    count 计数

create table emp (id int,name char(10),sex char,dept char(10),job char(10),salary double);

insert into emp values

(1,"刘备","男","市场","总监",5800),

(2,"张飞","男","市场","员工",3000),

(3,"关羽","男","市场","员工",4000),

(4,"孙权","男","行政","总监",6000),

(5,"周瑜","男","行政","员工",5000),

(6,"小乔","女","行政","员工",4000),

(7,"曹操","男","财务","总监",10000),

(8,"司马懿","男","财务","员工",6000);

#查询每个部门有哪些人
group_concat()#不常用
select dept,group_concat(name)from emp group by dept;
#可以有多个分组依据 比如先按部门 再按工作岗位
select dept,job,avg(salary)from emp group by dept,job;

注意：聚合函数不能用在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连用
    如果要对分组数据进行过滤 必须使用having
    having与where都是过滤但是执行时机不同

order by
排序 默认为升序
select * from emp order by salary;
==>>select * from emp order by salary asc;
用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 则表示获取的条数
select * from emp limit 0,3;
# 分页显示
总共为10页  每一页显示 3条   页数 10 / 3  由余数则 + 1   = 4
page = 1
select *from emp limit 0,3
page = 2
(page - 1) * 3
select *from emp limit 3,3
page = 3
(page - 1) * 3
select *from emp limit 6,3
起始位置的计算公式
limit(页数-1)*每页的条数，每页条数

补充
name   sex
张三    男
张无忌  null
count(sex)? 结果为1
所以count会忽略null值 如果要统计数量用count(*)最好
正则表达式(也用于模糊匹配)

create table emp (id int,name char(10),sex char,dept char(10),job char(10),salary double);
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高
搜索功能案例:
博客:
    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条
    可以保证 肯定有一条关联关系是正确的，但是同时会产生大量错误数据
    我们需要加以过滤来得到正确的数据

create table dept (id int,name char(10));

insert dept values(1,"市场");

insert dept values(2,"财务");

insert dept values(3,"行政");
create table emp (id int,name char(10),sex char,dept_id int);

insert emp values(1,"大黄","m",1);

insert emp values(2,"老王","m",2);

insert emp values(3,"老李","w",30);

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

内连接查询
inner(可以省略)join on
查询两边表中完全匹配条件的信息
select * from dept join emp on dept_id=dept.id;
on==where只能用于连接查询
#如果是用来筛选匹配关系 建议使用on连接查询中必须使用on
select * from emp join dept on dept_id=dept.id where dept.name='财务';
左外连接查询
    语法：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 条件;
    右外无论是否匹配，都全部显示 左边仅显示匹配成功的
    select *from dept right join emp on dept_id =dept.id;
    查询所有的员工及所在的部门信息
全外连接查询
    左右两边 无论是否匹配都要显示
    mysql中不支持全外连接 oracle中为full join
    mysql可以通过union 合并查询 来合并左外连接和右外连接的查询结果
    合并两个查询结果并且去除重复的
     select * from dept left join emp on dept_id =dept.id
     union
     select * from dept right join emp on dept_id=dept.id;
     合并但不去除重复
     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;

三表联查

create table stu(id int primary key auto_increment,name char(10));

create table tea(id int primary key auto_increment,name char(10));



create table tsr(id int primary key auto_increment,t_id int,s_id int,

foreign key(s_id) references stu(id),

foreign key(t_id) references tea(id));

insert into stu values(null,"张三"),(null,"李四");

insert into tea values(null,"egon"),(null,"wer");

insert into tsr values(null,1,1),(null,1,2),(null,2,2);

#egon老师教过哪些人？
select *from stu join tsr join tea on stu.id=tsr.s_id and tea.id =tsr.t_id where tea.name='egon';
#指定显示哪些字段
select s_id,t_id ,stu.name ,tea.name from stu join tsr join tea on stu.id=tsr.s_id and tea.id =tsr.t_id where tea.name='egon';

套路：
    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';