day 29 数据库

1. SQL
    1.1 SQL介绍
        SQL，它的全称叫Structured Query Language，结构化的查询语言。之所以出现这个东西，是为了统一/屏蔽不同数据库厂商生产的数据库产品之间的差异。
        SQL定义了一系列标准和规范,数据库厂商也需要按照这个规范来,当然会有一些细小的差别,相比没有规范来说,要好很多。
        比如 项目后期由于某些原因,需要进行数据库更换,那么操作数据库的语句就都需要更改,而SQL出现,可以避免这个问题。
    1.2 简单的DDL
        DDL : Data Definition Language 数据库定义语言
            涉及的关键字 : create drop alter
        比如更改表名
            alter table 表名 rename 新表名;
            如 alter table teacher rename t_teacher;
        更改字段名
            alter table 表名 change 列名 新列名 数据类型;
        更改表的列名 和 数据类型 当然数据类型可以不改,但是必须得写,
            如 alter table t_teacher change name teacher_name varchar(20);
        添加字段
            alter table 表名add 列名类型;
            如 alter table t_teacher add birthday datetime; 默认添加到尾部
            alter table t_teacher add birthday datetime after teacher_name; 把列添加到指定列的后面
            alter table t_teacher add sex2 char(2) first; 添加到第一列,需要把表关掉,刷新一下,再打开才能显示出来
        删除字段
            alter table 表名 drop 列名;
            如 alter table t_teacher drop birthday;
        更改字段类型(尽量不要更改)
            alter table 表名 modify 列名 新数据类型;
            如 alter table t_teacher modify sex2 varchar(20);
        alter table 表名 modify 列名 数据类型 comment '该列的注释说明';  更改类型的同时,还能添加注释说明
        查看建表语句
        show create table 表名;
    1.3 DDL增强
        实体完整性（主键）
            主键通常用于唯一确定表中的一条记录，设置为主键的字段是不能为NULL并且不能重复的。
        主键可以设置在一个字段上，也可以设置在多个字段上。（但大多数场景都是设置在一个字段上，这个字段通常是业务主键或者流水号）
        主键设置可以划分为两种
            第一种 : 创建表语句时,添加主键约束
                CREATE TABLE teacher(
                    id INT,
                    `name` VARCHAR(10),
                    PRIMARY KEY(id)
                )
            第二种 : 创建表完成之后,通过alter添加主键约束
                语法 :  alter table 表名 add primary key(列名,列名...);
                ALTER TABLE student ADD PRIMARY KEY(name);
        主键自增
            但是自增的列,必须为主键列,关键字 auto_increment
            设置自增的两种方式 :
                第一种 : 建表时,添加自增
                    CREATE TABLE teacher(
                    id INT auto_increment,
                    `name` VARCHAR(10),
                    PRIMARY KEY(id)
                )
                    
                第二种 : 创建表之后,添加自增
                    语法 : alter table 表名modify 主键列名 类型 auto_increment;
                    ALTER TABLE student MODIFY id int  auto_increment;
            设置自增的起始值
                语法 : alter table 表名auto_increment=值;
                ALTER TABLE student auto_increment = 5;
        关联完整性（外键）
            对应java代码来说,外键就是类的关联关系(一个类的成员变量是另外一个类的对象引用)
            外键列的值,必须是关联表中的已有主键值,也可以为空
            设置外键有两种方式 :
            第一种 : 创建表时添加外键约束
                CREATE TABLE `student` (
                  `id` int(11) ,
                  `name` varchar(10) ,
                  `sex` char(1),
                  `kecheng` varchar(10) ,
                  `score` int(11) ,
                    teacher_id int,
                    PRIMARY KEY(id),
                    FOREIGN KEY(teacher_id) REFERENCES teacher(id)
                )
            第二种 : 创建完表之后,添加外键约束
                alter table 表名 add foreign key (外键列列名) references 指向的表名 (主键列列名);
                ALTER TABLE student ADD FOREIGN KEY(teacher_id) REFERENCES teacher(id);
        唯一约束 unique
            唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。
            唯一约束不允许出现重复的值，但是可以为多个null.
                设置unique约束有两种方式 :
                第一种 : 创建表时,添加unique约束
                    CREATE TABLE `student` (
                      `id` int(11) ,
                      `name` varchar(10) ,
                      `sex` char(1),
                      `kecheng` varchar(10) ,
                      `score` int(11) ,
                        teacher_id int UNIQUE,
                        PRIMARY KEY(id),
                        FOREIGN KEY(teacher_id) REFERENCES teacher(id)
                    )
                第二种 : 创建表之后,添加unique约束
                    ALTER TABLE student ADD UNIQUE(teacher_id);
        非空约束 not null与 默认值 default    
            所有的类型的值都可以是null，包括int、float 等数据类型,设置为not null的字段,必须填入数据
            经常和default一起使用,当不填写数据的时候,把默认值设置成指定的值
                设置not null 与 default有两种方式 :
                    第一种 : 创建表时,添加约束
                        CREATE TABLE `student` (
                          `id` int auto_increment,
                          `name` varchar(10) not null DEFAULT '无名氏',
                          `sex` char(1),
                          `kecheng` varchar(10) ,
                          `score` int(11) ,
                            teacher_id int,
                            PRIMARY KEY(id)
                        )
                    第二种 : 创建表之后,添加约束
                        语法 : alter table 表名 modify 列名 数据类型 not null  default 默认值;
                            ALTER TABLE student MODIFY sex char(1) not null DEFAULT'男';
    1.4 基础DQL    
        语法 :
            select 列限定 from 表限定 where 行限定;
        and
                且,和,的意思,一般用于 必须符合两个添加的判断,等同于java中的 &&
            语法 :
                select 列限定 from 表限定 where A表达式 and B表达式;
                如 : 查询学生表中,name是张三且成绩大于90分
            select * from student where name='张三' and score > 90;
        or
                或的意思,一般用于 符合一个添加判断的情况下,等同于java中的 ||
            语法 :
                select 列限定 from 表限定 where A表达式 or B表达式;
                如 : 查询学生表中,name是张三 或 成绩大于90分
            select * from student where name='张三' or score > 90;
                只要符合两个条件中的任何一个条件,就可以
        关系表达式
            > , >= , <  , <= ,<>,=
            > : 大于
            < : 小于
            >= : 大于等于
            <= : 小于等于
            = : 相等
            <> : 不等于
        错误 判断为空不能使用 = null ,应该使用 is null
            select * from student where  score is null;
        错误 判断不为空 不能使用 <>null,应该使用 is not null
            select * from student where  score is not  null;
        between  and
                在...之间
            语法 :
                select 列限定 from 表限定 where 列名 between 值1 and 值1;
            如 : 查询学生表中 成绩在98到100之间 (包含98和100)
                select * from student where score >= 98 and score<=100;  
                等价于
                select * from student where score between 98 and 100;
        In
                在指定数据中
            语法 :
                select 列限定 from 表限定 where 列名 in(值1,值2....);
            如 : 给出一个数据集合(1,3,10,20),获取学生id在这个数据集合中的学生信息
                select * from student where id in (1,3,10,20);
        模糊查询like
            我们经常会用到搜索功能,比如百度,搜索功能实现,就是使用like模糊查询技术点
                其中 % 匹配任意个数的任意字符
                _ 匹配单个任意字符
            语法 :
                select 列限定 from 表限定 where 列名 like  '值' ;
            如 : 把name中,把姓张的查询出来
                select * from student where  name like '张%';
            如 : 把 name中,姓名有两个字的查询出来
                select * from student where  name like '__';
        Order by 排序
            排序,望文知意,能够让我们查询的数据进行排序展示
            语法 :
                select 列限定 from 表限定 order by 列名 asc/desc;
            Asc : 升序
            Desc : 降序
            如 : 查询所有学生信息,以成绩降序
                select * from student order by score desc;
            如 : 查询所有学生信息,按成绩降序,如果成绩相同,按照id升序
                select * from  student order by score desc , id asc;
        Limit
            限制条数,通常和order by一起使用,因为我们使用排序之后,再去获取前几条数据,比较有价值,比如成绩前三名
            语法 :
                select 列限定 from 表限定 limit 条数;
                select 列限定 from 表限定 limit 开始值(不包含) ,条数;
            如 : 查询学生表,分数前三名的信息
                select * from  student order by score desc limit 3;
            如 : 查询学生表,分数第二名和第三名
                select * from  student order by score desc limit 1,2;
2. 单表查询(组函数)
            MYSQL中有一类特殊的函数，用于统计，或者分组统计,
                分组关键字使用 group by
            常用组函数有 :
                count(*) : 总条数
                max(字段名) : 最大值
                min(字段名) : 最小值
                avg(字段名) : 平均值
                sum(字段名) : 总和
        Group by
            如 : 查询每个老师分别带了多少学生(显示老师id即可)
                select teacher_id, count(*) as stu_count  from student group by teacher_id;
            如 : 查询每个老师带的学生中的最高分数
                select teacher_id, count(*) as stu_count,max(score) as stu_max_score from student group by teacher_id;
            如 : 查询每个老师所带学生的总成绩与平均分
                select teacher_id, sum(score) as sum,avg(score) as avg from student group by teacher_id;
        Having
            刚才我们使用group by 和 组函数,可以对数据进行分组查询,并且也可以查询到平均值等数据
            但是有时候我们也需要做一些判断,比如求出平均值了,我只想要平均值 大于60分的平均分数,这时候用where就不行了
            这个时候就需要使用having进行过滤
                select teacher_id, avg(score) as avg from student group by teacher_id having avg > 60;
3. 子查询
        子查询又叫嵌套查询。它通常可以位于SELECT后面 FROM后面 WHERE后面,共三种使用场景。当我们查询一个表没有办法实现功能的时候,就需要使用子查询
            上面我们讲到了分组查询，可以查询每个老师所带学生的最低分，
            但是我们刚才查出来之后,我们只能看到teacher_id,但是我们并不知道teacher_id对应的是那个老师,这个时候我们最好是显示老师的名字是比较好的,
        可以用子查询实现
        场景一 : select后面
            语法 :
            select 字段名,(查询语句) from 表名;
            如  : 查询所有学生的信息并显示老师的名字
            select *,(
                            select name from teacher where id=teacher_id   
                        ) as teacher_name from student ;
            如 : 查询每个老师的学生的 最大分数,最小分数,平均分数,分数总和,学生人数,老师名字
            select max(score),min(score),sum(score),avg(score),count(*),(
                    select name from teacher where id=teacher_id
                    ) as teacher_name from student group by teacher_id ;
            注意 :
            当位于SELECT后面时，要注意
            1.一定要在两个表之间找好对应关系（teacher.id必须是主键或者必须保证teacher.id在teacher表中是唯一的）
            2.子查询中只能有一个字段（子查询的结果必须是一行一列）
            使用子查询的时候，建议大家养成使用别名的好习惯，这样可以让我们的查询语句更加清晰。别名可以用来命令新字段，也可以用来命名新表.
        场景二 : from后面
            还是学生表student，我们要将成绩进行分级，并且显示汉字的分级与字母的分级。这里可以使用子查询。相当于给student“新增”了2个字段
            如 : 使用子查询 对成绩划分等级, score<60 ,评级C 并且是差,score>=60 且 score<80 评级B并且是良,score>=80 评级是A并且是优
            select *,
            case rank
            when 'A' then '优'
            when 'B' then '良'
            when 'C' then '差'
            end rank_ch
            from (
            select *,
            case
            when score < 60 then 'C'
            when score >=60 and score <80 then 'B'
            when score >=80 then 'A'
            end as rank
            from student
            ) a;

            注意 :
            当位于FROM后面时，要注意
            1.我们可以把子查询当成一张表
            2.必须要有别名,因为子查询优先被执行,子查询的别名,可以让别的查询当做表或者列去操作
        场景三 : where后面
            如 : 在不知道teacher_id 和 老师名字的对应关系的情况下,想查询出张老师下面的所有学生信息
            select * from student where teacher_id in (
            select id from teacher where name='张老师'
            );
            注意 :
            当位于WHERE后面时，要注意
            1.    多条数据要用in而不要用=,如果确定子查询的结果为一行一列的话,就可以用 = 等于
            2.    如果返回结果为多行一列的话 要用 in , 一列是必须的,必须是一列
            3.子查询中的SELECT后面只能有一个字段（多个字段的话会报错）