关于MySQL数据库的进阶操作

1、as关键字

修改别名只能在当前sql语句中使用
as 可以省略

给字段起别名

select name as n from students;

select name as '名字', age as '年龄' from students;

从students表中查询name，age字段，可以使用数据表明.字段名的方式
select students.name,students.age from students

给表起别名
用别名去取对应的字段

select 别名.字段 ... from 表名 as 别名;
select s.name,s.age from students as s

同时起别名：

select s.name '名字',s.age '年龄' from students as s

2、消除重复 --关键字 distinct

对显示结果去除重复的数据

select distinct gender from students;

3、where 语句 （筛选）

比较运算符

筛选条件为True的数据

<> 对等标号 显示除了女性 等价于 !=

select * from student where <> '女';

enum(‘男’,‘女’,‘中性’,‘保密’)

select * from student where <> 2; (效果一样)

逻辑语句

1、and

select * from student where age >= 18 and age <= 28;

错误写法：（先进行左边的小于判断，结果是0或1 然后在进行右边的判断，结果为1）

select * from student where 18 <= age <= 28;

2、or

select * from student where gender = 2 or age > 18;

4、模糊查询

只知道数据的一部分特征 来检索很多数据

1、关键字 like
% 表示 任意多个 任意字符
_ 表示 一个 任意字符

select * from students where name like '周%';
select * from students where name like '周杰_';

姓名中含有 ‘小’ 的

select * from students where name like '%小%'

2、关键字 in

select * from students where age = 18 or age = 28 or age = 38;
select * from students where age in (18,28,38);
select * from students where age not in (18,28,38);

3、关键字 between

select * from students where age in (18,19,20,21,22,23);
select * from students where age >= 18 and age <= 23;
select * from students where age between 18 and 23

4、空判断

select * from students where height IS NULL;
select * from students where height IS NOT NULL;

注： where height = null
null是一种状态，而不是一个值，所以不能这么判断

5、排序

1、关键字 order

select * from 表名 order by 字段名;

升序 ASC 默认不写 ascend 向上倾斜 从小到大

select * from students order by age ASC;

降序 DESC descend 向下倾斜 从大到小

select * from students order by age DESC;

注：分行写 没有逗号

select * from students
	where gender = 2 and age between 19 and 59
	order by age DESC;

2、order by 多个字段

年龄在19到59之间，身高由高到低排序
如果身高相同，根据年龄有小到大排序
如果年龄相同，根据id由大到小排序

select * from students
	where gender = 2 and age between 19 and 59
	order by height DESC，age ASC,id DESC;

6、分页

把许多数据分成一页一页的数据，根据用户需要将数据分为一页一页地传输给用户的技术就是分页

1、分页查询语句

select * from 表名 limit start=0,count;

start起始下标，获取count条数据

select * from students limit 0,4
如果分页的起始下标是0 则可省略0, 只写数量，即select * from students limit 4

例：
取出身高前三高

select * from students order by height DESC limit 3;

取出身高第4,5名

select * from students order by height DESC limit 4,2;

2、分页

每一页分 2 条数据
第一页数据 0 1

	select * from students limit 0,2;

第二页数据 2 3

	select * from students limit 2,2;

第三页数据 4 5

	select * from students limit 4,2;

…
…
推导公式：第n页数据，每一页显示m条数据
select * from students limit m*(n-1),m;

7、聚合函数

又称组函数
对当前所在表当做一个组进行统计

特点：
每个组函数接收一个参数（字段名或表达式）

1. 总数（count）

   统计任意一个字段的总数

    select count(*)
   

   统计符合查询条件的数据的数量

    select count(*) from students where height is null;
   

2. 最大值 max(列) 最小值 min(列)

   查询该列中的最大值

    select max(age) from students
   

3. 求和 sum(列)

    select sum(age) from students
    select sum(age)/count(age) from students
   

4. 求平均值avg(列)

    select avg(age) from students
   

5. 取整 round(数据，保留位数)

    select round(avg(age),2) from students
   

8、分组

将一个数据集 划分成若干个小区域，然后针对小区域进行数据处理
注：在聚合函数和分组一起使用的时候，聚合函数是针对每个分组进行统计的

1. group by分组

   按照性别分组（表数据已经不再是二维结构）

    select gender from students group by gender;
    select gender,count(*) from students group by gender;
   

   各组对应的平均值

    select gender,avg(age) from students group by gender;
   

2.group_concat()

把同一个分组里的每一个字段拼在一起显示

select gender,avg(age),group_concat(name) from students group by gender;

3.having 设定分组条件，只能对分组进行过滤

不能和where混淆
where只能用于记录数据的过滤

select gender,avg(age) from students group by gender having avg(age) > 30;

4.汇总显示 with rollup

把整个分组结果 新增到最后一行 汇总显示

select gender,avg(age),group_concat(name) from students group by gender with rollup;

9、连接

• 内连接查询：
  查询的结果为两个表匹配到的数据

• 外连接查询：
  在内连接的结果上还添加外部数据
  外部数据来自于右表–右连接
  外部数据来自于左表–左连接
  右（外）连接查询：
  匹配的数据和右表特有的数据
  左（外）连接查询：
  匹配的数据和左表特有的数据

  笛卡尔积运算 join 将左表和右表中的每一行分别进行拼接组合 - 交叉连接 cross join

    select * from hero cross join gongfu;
  

  • 内连接 需要在笛卡尔积的基础上 使用on 指定锅炉笛卡尔积数据的条件\

      select * from hero inner join gongfu on hero.kongfuid = gongfu.id;
    

  • 左连接
    在内连接的基础上 添加额外数据，来自于左表\

      select * from hero left （outer） join gongfu on hero.kongfuid = gongfu.id;
    

  • 右连接
    在内连接的基础上 添加额外数据，来自于右表

      select * from hero right join gongfu on hero.kongfuid = gongfu.id;
    

  标准sql语句形式：

    select gongfu.kongfu from hero join gongfu on hero.kongfuid = gongfu.id where hero.name = '妲己'
  

  mysql支持的方式：

    select gongfu.kongfu from hero,gongfu where hero.kongfuid = gongfu.di and hero.name = '妲己'
  

• 自连接：

  导入数据步骤：
  1.把areas.sql文件拷贝到Linux桌面
  2.Linux命令进入桌面 cd ~/Desktop
  3.登录mysql mysql
  4.使用数据库 use python_test_1;
  5.创建表
  6.执行命令 source areas.sql

  自连接是一种特殊的连接方式
  select * from 表 join 表 on 条件;

    select * from areas as city join areas as pro on city.pid = pro.aid;
    
    select * from areas as city join areas as pro on city.pid = pro.aid
        where pro.atitle = '河北省';
  

  select * from 表 as 别名1 join 表 as 别名2 on 条件;