MySQL数据库SQL命令使用（表数据操作）

-- curd的解释: 代表创建（Create）、更新（Update）、读取（Retrieve/Read）和删除（Delete）
   -- 增加
    -- 全列插入
    -- insert [into] 表名 values(...)
    -- 主键字段 可以用 0  null   default 来占位
    -- 向classes表中插入 一个班级
       insert into t_classes values(0,'python6期');
    -- 向students表插入 一个学生信息
      -- 表(id、name、age、high (decimal)、gender (enum)、cls_id)
      insert t_students values(default,'小王1',20,168.88,'女',1);
    -- 部分插入
    -- insert into 表名(列1,...) values(值1,...)
    --insert into students(name,gender) values("林老师",1);
      insert into t_students(name,gender) values('林老师','男');

    -- 多行插入
    --insert into students values(0,"laowang",18,166.66,1,111),(0,"laoli",18,166.66,1,111);

    insert into t_students values(0,"laowang",18,166.66,1,111),(0,"laoli",18,166.66,1,111);

    --insert into t_students(name,gender) values("小张1",1),("小张2",2);
    insert into students(name,gender) values("小张1",1),("小张2",2);

-- 修改
    -- update 表名 set 列1=值1,列2=值2... where 条件;  
    -- 全部修改
      update t_classes set name='p+智能6期';
    -- 按条件修改
      update t_classes set name='python6期' where id=1;
    -- 按条件修改多个值
    -- update students set gender ="",name = "xxx" ;
    update t_students set age=39,height=170.8 where name='laoli';

    -- 查询基本使用
    -- 查询所有列
    -- select * from 表名;
      select * from t_classes;
    ---定条件查询
       select * from t_students where gender='男';

        -- 查询指定列
    -- select 列1,列2,... from 表名;
      select name, gender from t_students;

    -- 字段的显示顺序
        select gender,name from t_students;

-- 删除
    -- 物理删除
    -- delete from 表名 where 条件
       delete from t_students where gender='男';
    -- 逻辑删除(本质就是修改操作)
    --update students set isdelete=1 where id=1;
--02查询练习
-- 2.1 查询所有字段
-- select * from 表名;
  select * from students;
  select id,name from classes;
-- 2.2 查询指定字段
-- select 列1,列2,... from 表名;
select name,gender from students;

-- 2.3 使用 as 给字段起别名
-- select 字段 as 名字.... from 表名;
  select name as '姓名' ,gender as '性别' from students;

-- sql语句查询字段的完全形式
-- select 表名.字段 .... from 表名;   
  select students.name,students.gender from students;
# 只有正在使用当前数据库, 并且查找的数据表就在当前数据库中,才可以省略数据库名
# 数据表名什么时候不可以省略: 当一个sql语句中出现了多个数据表, 并且查询的字段在多个表中并且字段名相同, 此时表名不能够省略
--失败的: select name,name from students,classes;
 select students.name,classes.name from students,classes;

-- 2.4 可以通过 as 给表起别名
-- select 别名.字段 .... from 表名 as 别名;
 select s.name,c.name from students as s,classes as c;

--失败的: select students.id,students.age from students as s;


-- 2.5 消除重复行(查性别)
-- 在select后面,列的前面使用distinct可以消除重复的行
 select distinct gender from students;

 select distinct id from students;
 select distinct * from students;

--03条件查询
-- select .... from 表名 where 条件
-- 使用where子句对表中的数据筛选，结果为true的行会出现在结果集中

-- 3.1 比较运算符 > 、< 、>=、<=、=、!= 
    -- >
    -- 查询大于18岁的学生信息
        select * from students where age>18;
    -- <
    -- 查询小于18岁的学生信息
       select * from students where age<18;
    -- >=
    -- <=
    -- 查询小于或者等于18岁的学生信息
       select * from students where age<=18;

    -- =
    -- 查询年龄为18岁的所有学生的名字
      select * from students where age=18;

    -- != 或者 <>
       select * from students where age!=18;

-- 3.2 逻辑运算符 and、or、not
    -- and  (并且) 年龄在18和28之间的所有学生信息
         select * from students where age>18 and age<28;

    --失败的 select * from students where age>18 and <28;


    -- 18岁以上的女性
       select * from students where age>18 and gender='女';

    -- or (或者)
    -- 18以上或者身高高过180(包含)以上的学生信息
      select * from students where age>18 or height>=180;

    -- not (取反)
    -- 不在 18岁以上的女性 这个范围内的信息
     select * from students where not (age>18 and gender='女');

-- 3.3 模糊查询(where 列名 like 要查询的数据)
    1.-- like 
    -- % 替换任意个  
    -- _ 替换1个   
    -- like 是匹配以xxx开头的字符串

    -- 查询姓周的学生
       select * from students where name like '周%';

    -- 查询姓名中 包含 "杰" 字的所有名字
       select * from students where name like '%杰%';


    -- 查询姓周并且“名”是一个字的学生
       select * from students where name like '周_';
    -- 查询姓黄或叫靖的学生
        select * from students where name like '黄%' or  name like '%靖%';

    2.rlike和REGEXP :
        常用通配符：. 、* 、 [] 、 ^ 、 $ 、{n}
        . : 匹配任意单个字符


                * ： 匹配0个或多个前一个得到的字符

        [] : 匹配任意一个[]内的字符，[ab]*可匹配空串、a、b、或者由任意个a和b组成的字符串。

        ^ : 匹配开头，如^s匹配以s或者S开头的字符串。

        $ : 匹配结尾，如s$匹配以s结尾的字符串。

        {n} : 匹配前一个字符反复n次。

        注意： 
        1. 在MySQL中，like、rlike和regexp都不区分大小写，如果需要区分，可以在WHERE后添加关键字段binary。 
        2. like是完全匹配。rlike和regexp是不完全匹配，只要不同时匹配^和 $， 其他的包含即可。如 ^ba可以匹配baaa和baab，a也可以匹配baaa和baab，但是^bab$不能匹配baab。

-- 3.4 范围查询
    -- in 表示在一个非连续的范围内
    -- 查询编号是1或3或8的学生
        select * from students where id in(1,3,8);
    -- between ... and ...表示在一个连续的范围内
    -- 查询 年龄在18到34之间的男同学信息      
        select * from students where age between 18 and 34;
    -- 查询 年龄不在在18到34之间的的信息
       select * from students where not (age between 18 and 34);
     --失败的: select * from students where age not (between 18 and 34);

-- 3.5 空判断
    -- is null (判空)
    -- 查询身高为空的学生信息
       select * from students where height is null;

    -- 失败的:select * from students where height=null;


    -- is not null(判非空)
    -- 查询身高不为空的学生信息
    select * from students where height is not null;
    select * from students where not (height is null);

-- 3.6 优先级
    --优先级由高到低的顺序为：小括号，算术运算符,比较运算符，逻辑运算符
    --not>and>or，如果同时出现并希望先算or，需要结合()使用
    --分析以下sql语句:
     5+5*6
    select * from students where  not (age>=18) or height>=180 and gender="男";

--04排序 
-- select * from 表名 [where ...] order by 列1 asc|desc [,列2 asc|desc,...]
-- 有where条件时，order by需放到where条件的后面
-- asc从小到大排列，即升序，默认是升序
-- desc从大到小排序，即降序
-- 4.1 order by 字段

-- 查询未删除学生的信息，按年龄降序排序
 select * from students where is_delete=0  order by age desc;

-- 4.2 order by 多个字段

-- 查询所有学生信息，按照年龄从小到大、当年龄相同，则身高从高到矮的排序
  select * from students order by age asc ,height desc;
  select * from students order by age  ,height desc;

--05分页
-- 当数据量过大时，通过分批、分页加载数据既能提升加载速度，也可更好显示查询结果
-- select * from 表名 limit [start,] count
-- 限制查询，start为查询的起始下标，count为限制查询的数量
-- 注意: limit 放在查询语句的最后面

-- 查询5个数据
   select * from students limit 5;
   select * from students limit 0,5;
   select * from students limit 6,5;

-- 分页查询
-- 每页显示2个，第1个页面
   select *from students limit 0,2;
-- 每页显示2个，第2个页面
    select *from students limit 2,2;

-- 每页显示2个，第3个页面
  select *from students limit 4,2;

-- 每页显示2个，第4个页面
  select *from students limit 6,2;

-- 每页显示2个，显示第6页的信息, 按照年龄从小到大排序
--公式: start=(page-1)*count
   select * from students order by age asc limit 10,2;


--错误1 select * from students limit 10,2 order by age asc;
--错误2 select * from students order by age limit 2*(6-1),2;

--06聚合函数(对查询的结果集进行统计分析)
-- 聚合函数aggregation function又称为组函数，是把结果集当做一个组进行统计
-- 6.1 求总数
-- count(*)表示计算总行数
-- count(列)表示计算某列的总行数，假如该列某个值为null则不会统计
-- count
-- 统计学生表中总人数有多少人
  select count(*) from students;
-- 统计学生表中，女性有多少人
  select count(*) from students where gender='女';

-- 统计学生表中，height 字段不为空的总人数
 select count(height) from students;
 select count(id) from students;

-- 6.2 求最大值
-- max(列) 求此列的最大值
-- 查询最大的年龄
 select max(age) from students ;


-- 查询女性的最高身高
 select max(height) from students where gender='女';


-- 6.3 求最小值
-- min(列)表示求此列的最小值
-- 查询最小身高
 select min(height) from students where gender='女';

-- 6.4 求和
-- sum(列)表示求此列的和
-- 计算所有学生的年龄总和
   select sum(age) from students;
-- 6.5 求平均值
-- avg(列)表示求此列的平均值
-- 计算未删除女生的总人数及平均年龄
  select count(*),avg(age) from students where is_delete=0 and gender='女';

  -- 错误的写法： select name,count(*),avg(age) from students where is_delete=0 and gender='女';

  提示：目前 聚合函数不能与字段名混合使用


--07分组(难点)
-- 在实际业务中，经常会对数据进行分类统计操作，通过 group by 可实现分组,做更精细的聚合统计操作
-- select * from tbname where 条件 group by...having 条件...

-- 7.1 group by
-- 将查询结果按照1个或多个字段进行分组，字段值相同的为一组
--注意: select 列 中只能存放分组函数(比如聚合函数)，或是出现在group by子句中的分组标签

-- 按照性别分组,查询所有的性别
  select gender from students group by gender;

--失败 select name,gender from students group by gender;
--失败 select * from students group by gender;


-- 7.2 group by + 聚合函数
-- 查询每组性别的平均年龄
   select gender,avg(age) from students group by gender;

-- 查询每种性别中的平均年龄avg(age), 最大年龄,平均身高,最高身高
   select gender,avg(age),max(age),avg(height),max(height) from students group by gender;


-- 7.3 group_concat(列)
-- group_concat(字段名) 拼接字符， 可以作为一个输出字段的值来使用
-- 表示分组之后，根据分组结果，使用group_concat()来拼接每一组的某字段的值

-- 查询同种性别中的姓名
   select gender,max(age),group_concat(name) from students group by gender;


-- 7.4 having  
-- having 条件表达式：对分组后的数据进一步筛选
--注意：having与where相似，但having只能与group by 连用，且 having后可接select 列 出现的元素或者聚合函数,where 不能接聚合函数

-- 计算男性的人数
   select count(*) from students where gender='男';

-- 通过分组来实现
   select gender,count(*) from students group by gender having gender='男';

-- 除了男性以外其他性别的人数
    select gender,count(*) from students group by gender having gender!='男';
-- 查询每种性别中的平均年龄avg(age)
   select gender,avg(age) from students group by gender  ;

-- 按性别分组，分别统计出平均年龄超过30岁的组的性别以及姓名 having avg(age) > 30
   select gender,group_concat(name),avg(age) from students group by gender having avg(age)>30;
-- having 和 where 的区别
where: 对源数据做条件筛选，where 条件中 不能接聚合函数
having: 是对分组之后的数据做进一步的筛选操作, 有having就一定有group by, 有 group by 不一定有having，having条件中 可以接聚合函数


--08连接查询
-- 8.1 inner join ... on   (内连接)
-- 结果仅包含符合连接条件的两表中的行
-- select ... from 表A inner join 表B  on  条件;

 # select * from students,classes where students.cls_id=classes.id;
 select * from students inner join classes on students.cls_id=classes.id; 


-- 查询有能够对应班级的学生以及班级信息   
 select * from students inner join classes on students.cls_id=classes.id; 


-- 只显示姓名、班级
   select students.name,classes.name from students inner join classes on students.cls_id=classes.id; 


-- 给数据表起别名
select s.name as '姓名',c.name as '班名' from students as s inner join classes as c on s.cls_id=c.id; 


-- 8.2 left join ...  on (左连接)
-- select ... from 表A left join 表B  on  条件;
-- 左表全部行+右表匹配的行，如果左表中某行 在右表中没有匹配的行，则右表该行显示NULL

-- 查询每位学生对应的班级信息
  select * from students as s left join classes as c on s.cls_id=c.id;


-- 查询没有对应班级信息的学生
-- select ... from xxx as s left join xxx as c on..... where .....
  select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;


-- 8.3 right join ...  on (右连接)
-- select ... from 表A right join 表B  on  条件;
-- 右表全部行+左表匹配的行，如果右表中某行 在左表中没有匹配的行，则左表该行显示NULL
 select * from classes as c right join students as s on s.cls_id=c.id;


--09子查询 : 查询的嵌套  select select
-- 标量子查询: 子查询返回的结果是一个数据(一行一列)
-- 列子查询: 返回的结果是一列(一列多行)
-- 行子查询： 返回的结果是一行(一行多列)
-- 表子查询: 返回的结果是一个临时表(多行多列)

-- 9.1 标量子查询
-- 查询出高于平均身高的信息(height)
-- 1 查出平均身高
   select avg(height) from students;  # 172

-- 2 查出高于平均身高的信息
   select * from students where height>(select avg(height) from students);


-- 9.2 列子查询
-- 查询有班级的学生信息
-- select name from students where cls_id in (select id from classes);
-- 1 查出所有的班级id
  select id from classes;  {1,2}
-- 2 查出能够对应上班级号的学生信息
   select * from students where cls_id in(select id from classes);

--9.3 表子查询
-- 查询编号小于6的男性同学的姓名
 select * from students where id<6;
 select name from (select * from students where id<6) as s  where s.gender='男';

–4. 数据表设计与创建

-- 4.1 创建"商品分类"表

    第一步 创建表 (商品种类表 goods_cates )
        create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null

);

    第二步 同步 商品分类表 数据 将商品表的所有 (种类信息) 写入到 (商品种类表) 中
       1. 直接手动添加
       2. 通过查询结果来添加到商品列表表中

    -- 查询 goods 表中的所有 种类(cate_name)
        -- 去重(distinct)
         select distinct cate_name from goods ;
    -- 通过sql语句取得要插入的数据
        insert into goods_cates(name) select distinct cate_name from goods;

    --(注意) 把查询出来的 结果 写入 goods_cates 表里去 ( insert into ) 只插入name ,且不需要加values 


    第三部 同步 商品表 数据 通过 goods_cates 数据表来更新 goods 表

    -- 因为要通过 goods_cates表 更新 goods 表 所以要把两个表连接起来
      select * from goods as g inner join goods_cates as c on   g.cate_name=c.name;

    -- 把 商品表 goods 中的 cate_name 全部替换成 商品分类表中的 商品id ( update ... set )
       update goods as g inner join goods_cates as c  on  g.cate_name=c.name set g.cate_name=c.id;

    第四部 修改表结构

    -- 查看表结构(注意 goods表的cate_id 与 goods_cates表的 id 字段 类型需要一致)
      desc goods;

    -- 修改表结构 alter table 字段名字不同 change,把 cate_name 改成 cate_id int unsigned not null
     alter table goods change cate_name  cate_id  int unsigned not null;


-- 4.2 创建 商品品牌表 goods_brands

    第一步 创建 "商品品牌表" 表
    -- 创建goods_brands表
    create table if not exists goods_brands(
id int unsigned primary key auto_increment,
name varchar(40) not null

);

    -- 插入数据 ，数据来自goods表
    -- 按照 分组 的方式查询 goods 表中的所有 种类(brand_name)
      1. select brand_name from goods group by brand_name;
      2. insert into goods_brands(name) select brand_name from goods group by brand_name;

    --(注意) 把查询出来的 结果 写入 goods_brands 表里去 ( insert into ) 只插入name

    第二步 同步数据
    -- 通过goods_brands数据表来更新goods数据表 g.brand_name=b.id
       1. select * from goods as g  inner join goods_brands as b on g.brand_name=b.name;
       2. update goods as g  inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;

    第三部 修改表结构
    -- 通过alter table语句修改表结构 brand_id int unsigned not null
      alter table goods change brand_name brand_id int unsigned not null;