sql查询基本操作指令

1、基本查询

-- 数据的准备
    -- 创建一个数据库
    create database python_test charset=utf8;

    -- 使用一个数据库
    use python_test;

    -- 显示当前使用的数据库
    select database();

    -- 显示数据库的创建方式
    show create database python_test;

    -- 创建一个数据表
    create table students(
        id int unsigned primary key auto_increment not null,
        name varchar(20) default '',
        age tinyint unsigned default 0,
        height decimal(5,2),
        gender enum('男','女','中性','保密'), -- 1,2,3,4从1开始的
        cls_id int unsigned default 0,
        is_delete bit default 0
    );

    -- 再创建一个数据表
    create table classes(
        id int unsigned auto_increment primary key not null,
        name varchar(30) not null
    );

    -- 显示数据表
    show tables;

    -- 显示数据表的创造方式
    show create table students;
    show create table classes;

    -- InnoDB 是mysql的数据库引擎之一

    -- 向students表中插入数据
    insert into students values
    (0,'小明',18,180.00,2,1,0),
    (0,'小月月',18,180.00,2,2,1),
    (0,'彭于晏',29,185.00,1,1,0),
    (0,'刘德华',59,175.00,1,2,1),
    (0,'黄馨',38,160.00,2,1,0),
    (0,'凤姐',28,150.00,4,2,1),
    (0,'王祖贤',18,172.00,2,1,1),
    (0,'周杰伦',36,NULL,1,1,0),
    (0,'程坤',27,181.00,1,2,0),
    (0,'刘亦菲',25,166.00,2,2,0),
    (0,'金星',33,162.00,3,3,1),
    (0,'静香',12,180.00,2,4,1),
    (0,'郭靖',12,170.00,1,4,0),
    (0,'周杰',34,176.00,2,5,0);

    -- 向classes表中插入数据
    insert into classes values
    (0,'python_01期'),
    (0,'python_02期'),
    (0,'python_03期');

    -- 查看所有字段数据
        select * from students;-- 不要随便用,否则将数据库中的内容都读到内存中塞爆内存
        select * from studnets;-- 不要随便用,否则将数据库中的内容都读到内存中塞爆内存

    -- 查询指定字段
        -- select 列1,列2,...from表名;
        select name,age from students;
        select id,name from classes;

    -- 给字段起别名
        -- select 字段 as 名字,...froms 表名;
        select name as 姓名,age as 年龄 from students;
        select id as 序号,name as 班级 from classes;

    -- select 表名.字段 ... from 表名;
        select students.name,students.age from students;

    -- 给表名起别名,起了就要用
        select s.name,s.age from students as s;
        -- 否则失败，如下代码 查询失败
        -- select students.name,studnets.age from studnets as s;

    -- 去重查询
        select distinct gender from students;

2、条件查询

-- 条件查询
   -- 比较运算符查询
       -- select ... from 表名 where ...
       -- >
           -- 查询大于18岁的信息
           select * from students where age>18;
           select id,name,age from students age>18;
       -- <
           -- 查询小于18岁的信息
           select * from students where age<18;
       -- =
           -- 查询等于18岁的信息
           select * from students where age=18;
       -- != 或者 <> 前者更常用
           -- 查询不等于18岁的
           select * from students where age!=18;

   -- 逻辑运算符
       -- and
           -- 18到28之间的所有学生
           select * from students where age>18 and age<28;

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

       --or
           --18岁以上或者升高超过180(包括180)以上
           select * from students where age>18 or height>=180;

       -- not
           -- 不在  18岁以上的女性  这个范围
           select * from students where not (age>18 and gender=2);
           -- 年轻不是小于或者等于18的并且是女性
           select * from  students where not age>18 and gender=2;

   -- 模糊查询
        -- like
        -- % 替换 没有、一个、多个
        -- _ 替换一个

            -- %
                -- 查询姓名中以“小”开始的名字
                select name from students where name like '小%';
                -- like 使用的不多,因为要全部匹配,效率不是太高

                -- 查询姓名中 有“小” 的所有名字
                select name from students where name like '%小%';

            -- _
                -- 查询有两个字的名字
                select name from students where name like '__';
                -- 查询有三个字的名字
                select name from students where name like '___';
                -- 查询有至少有两个字的名字
                select name from students where name like '__%';

        -- rlike 正则
            -- 查询以 周开头的姓名
            select name from students where name rlike "^周.*";
            -- 查询以 周开头以伦结尾的姓名
            select name from students where name rlike "^周.*伦$";

   -- 范围查询：
        -- in(1,3,8)表示在一个非连续的范围内
            -- 查询 年龄为18 、34的姓名
            -- select name from students where age=18 or age=34;
            -- select name from students where age=18 or age=34 or age=12;
            select name from students where age in(18,34,12);

        -- not in 不非连续范围内
            -- 只要年龄不是18,34,12的人的姓名找出来
            select name from students where age not in(18,14,12);

        -- between ... and 表示在一个连续范围内
            -- 查询年龄范围在25到40之间的
            select name,age from students where age between 25 and 40;

        -- not between ... and 表示不在某个连续范围内
            -- 查询年龄范围不在25到40之间的
            select name,age from students where age not between 25 and 40;
            select name,age from students where not age between 25 and 50;
            -- 下面这种写法错误
            -- select * from students where age not (between 18 and 34);

   -- 判断是否为空
        -- 找到身高为NULL的
        select * from students where height is null;
        -- 找到身高不为NULL的
        select * from students where height is not null;

3、排序

-- 排序
    -- order by 字段
    -- asc 从小道大,即升序
    -- desc 从大到小,即降序

        -- 查询 年龄在18到34之间的男性，按照年龄从小到大排序
        select * from students where (age between 18 and 34) and gender = "男";

        -- 不写 order by 默认按照id排序,order by默认是从小到大的
        -- 注意 age between 18 and 34 是一个整体，所有一块括起来增加可读性
        select * from students where (age between 18 and 34) and gender=1 order by age;

        -- 年龄从大到小
        select name,age from students where age between 18 and 34 order by age desc;

        -- 查询年龄在18到34岁之间女性，并且身高从高到矮排
        select * from students where (age between 18 and 34) and gender=2 order by height desc;

    -- 如果存在相同的时候然后默认还会按照主键排序
    -- 下面使用 order by 实现按照多个字段排序,如果前面一个字段相同则按照后面的字段排
        -- 查询年龄在18到34之间的女性，身高从高到矮排序，如果身高相同则按照年龄从小到大排序
        select * from students where (age between 18 and 34) and gender=2 order by height desc,age asc;
        -- 查询年龄在18到34之间的女性，升高从高到矮排，如果身高相同则按照年龄从小到大排，若年龄也相同则按照id从大到小排
        select * from students where (age between 18 and 34) and gender=2 order by height desc,age asc,id desc;

        -- 按照年龄从小到大，升高从高到矮排
        select * from students order by age asc,height desc;

4、聚合函数

-- 聚合函数
    -- 总数
    -- count
        -- 查询男性有多少人，女性有多少人
        select * from students where gender=1;
        select count(*) from students where gender=1;
        select count(*) as 男性人数 from students where gender=1;
        select count(*) as 女性人数 from students where gender=2;

    -- 最大值
    -- max
        -- 查询最大年龄
        select age from students;

        -- 相当于去除所有的age然后扔给max函数求最大值
        select max(age) from students;

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

    -- 最小值
    -- min

    -- 最大值
    -- sum

    -- 平均值
    -- avg
        -- 计算平均年龄
        select avg(age) as 平均年龄 from students;
        -- 或者计算平均年龄还有一种方法就是
        select sum(age)/count(*) as 平均年龄 from students;

    -- 四舍五入
    -- round(123.333,1)保留1位小数
        -- 计算所有人的平均年龄保留2为小数
        select round(avg(age),2) from students;
        select round(sum(age)/count(*),2) from students;

        -- 计算男性的平均身高，保留两位小数
        select round(avg(height),2) from students where gender=1;

        -- 如下报错，不允许一起用，如果要用则需要使用分组，分组就是和聚合函数一起用的，按照某种聚合方式分成多组再操作
        -- select name,round(avg(height),2) from students where gender=1;

5、分组查询

-- 分组
-- group by 分组按照部门啊，班级啊等等
    -- 按照性别分组，查询所有性别
    select ... from students group by gender; -- 这个结果和 distinct 的结果是一样的，但是作用不一样
    -- select */name/height/age from students group by gender 都报错，因为分组按照gender分到，其他的没有区分标示
    select gender from students group by gender;



    -- 分组和聚合函数一块使用
        -- 按照性别分组，然后计算每种性别的人数
        select gender,count(*) from students group by gender;

        -- 按照性别分组，然后计算每组的最大年龄
        select gender,max(age) from students group by gender;

    -- 分组和条件判断
        -- 计算男性的人数
        select gender,count(*) from students where gender=1 group by gender;

    -- 查看分组后的内容
         -- 使用 group_concat 查看分组后组里面的的信息
        select gender,group_concat(name) from students group by gender; -- 输出按照性别分组的名字

        -- 看看分组之后的男性里面都有谁呀
        select gender,group_concat(name) from students where gender=1 group by gender;

        -- 继续看看其他信息如年龄，id
        select gender,group_concat(name,age,id) from students where gender=1 group by gender;
        -- 男     | 彭于晏293,刘德华594,周杰伦368,程坤279,郭靖1213

    -- group_concat的使用方法
        -- group_concat 写什么有什么
        select gender,group_concat(name,'_',age,'_',id) from students where gender=1 group by gender;
        -- 男     | 彭于晏_29_3,刘德华_59_4,周杰伦_36_8,程坤_27_9,郭靖_12_13

    -- having 对分组进行过滤
        -- 查看平均年龄超过30岁的性别，以及姓名 having(age)>38
        select gender,group_concat(name) from students group by gender having avg(age)>30;
        -- 下面输出每组的平均年龄看一下是否真的大于30
        select gender,group_concat(name),avg(age) from students group by gender having avg(age)>30;

        -- 查询每种性别中人数多于2个的信息
        select gender,group_concat(name) from students group by gender having count(*)>2;
        -- 查询每种性别中人数不多于2个的信息
        select gender,group_concat(name) from students group by gender having not count(*)>2;

6、分页查询

-- 分页
    -- limit start,count

    -- 限制查询出来的数据个数
        -- 查询男性学生，只显示前面两个
        select * from students where gender=1 limit 2;
    -- limit start count(length) 的使用
        -- 查询男性同学，从第1个开始找5个，这里需要主要start下面从0开始
        select * from students where gender=1 limit 0,5;

       -- 查询前五个，中间五个，第三个五个
       select * from students limit 0,5;
       select * from students limit 5,5;
       select * from students limit 10,5;

       -- 假设每页显示2个则
       select * from students limit 0,2;
       select * from students limit 2,2;
       select * from students limit 4,2;
       select * from students limit 6,2;

    -- limit (第N页-1)×每页的个数，每页的个数
        -- 每页显示三个，输出第3页的数据
        -- select * from students limit (3-1)*3,3;报错
        select * from students limit 6,3;
    -- limit 的位置永远是最后面的
        -- 排序后讲第10页的输出
        -- select * from students limit 10,2 order by age;
        select * from students order by age limit 10,2;

        -- 查询所有女性信息并且按照身高从高到矮排序只显示两个
        select * from students where gender=2 order by height desc limit 2;

7、连接查询

记住一个原则：在源数据表中过滤使用where在查询出来之后的数据表中过滤使用having，在分组那里也提到这点

-- 链接查询
    -- 内连接查询 AB的交集
    -- inner join...on on后面意味着添加条件

    -- 右链接查询 AB交集+右表数据
    -- right join...on

    -- 左链接查询 AB交集+左表数据
    -- left join...on


    -- inner join...on
        -- select ... from 表A join 表B
        select * from students join classes;

        -- 查询 能对的上的学生信息
        select * from students inner join classes on students.cls_id=classes.id;

        -- students的和classes的id 相同显示会重复
        -- 查询有能够对应班级的学生以及班级信息，显示所有学生的信息，只显示班级名称
        select students.*,classes.name 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,c.name from students as s inner join classes as c on s.cls_id=c.id;

        -- 在以上的查询中，将班级姓名显示在第一列
        select classes.name,students.* from students join classes on students.cls_id=classes.id;
        -- 按照班级排序一下
        select classes.name,students.* from students join classes on students.cls_id=classes.id order by classes.name asc;
        select classes.name,students.* from students join classes on students.cls_id=classes.id order by classes.name,students.id;

    -- left join...on
        -- 查询每位学生对应的班级信息,和 inner join 相比发现左表的信息都查找出来了，对应不上的默认为NULL
        select * from students as s left join classes as c on s.cls_id=c.id;


        -- 原则：在数据原表中过滤使用where，在查找之后得到的表中过滤使用having
        -- 查找没有对应班级的学生,在这里使用where也可以，但是推荐使用having
        select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
        select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;

    -- right join...on 用的少，可以使用left...join on 替代

8、自关联和子查询

只建立一张表使用自关联实现省、市、县的查询

需要查询一个省份的下面有多少地级市和县首先需要找到该省份的id，下面可以使用一句实现

-- 创建一个新表
    create table areas(
        aid int primary key, -- id 表示省份
        atitle varchar(20),
        pid int -- id 表示指向那个省份，没有则为null
    );
    -- 将 areas.sql添加尽量
    source areas.sql

    -- 查询山东省下有哪些城市
        -- 1、先找到山东省
        select aid from areas where attile="山东省"; --37000
        -- 2、然后在该aid下寻找
        select * from areas where aid=37000;

        -- 使用一句实现该查询,借助两张表的思路，使用join on
        select * from areas as province inner join areas as city on province.aid=city.pid having province.atitle="山东省";
        select province.atitle from areas as province inner join areas as city on province.aid=city.pid having province.atitle="山东省";

-- 子查询
    -- 查询出高于平均身高的信息
    -- 查询最高的男生信息
    select * from students where height=(select max(height) from students);
    -- 所以对于上面的查找山东省下面有多少个城市也可以使用子查询，只要先查到aid就可以了
    select * from students where pid=(select aid from areas where atitle="山东省");

9、数据哭库设计基本原则

a）满足第一第二第三范式

第一范式：原子性，不可分割

第二范式：在第一范式基础上必须有主键，并且没有包含主键的列必须完全依赖主键而不是依赖主键的一部分

第三范式：在第二范式基础上非主键列必须直接依赖与主键而不能存在传递依赖

b）考虑E-R模型

1对1：在表中新建一个字段

多对1：在多里面增加一个字段

多对多：新建一张表，聚合表