mysql学习-mysql进阶

一.DDL操作

1.有关表的一些操作

更改表名

alter table 表名 rename 新表名;

更改字段名

alter table 表名 change 列名 新列名 数据类型;

添加字段

alter table 表名add 列名类型;

删除字段

alter table 表名 drop 列名;

更改字段类型(尽量不要更改)

alter table 表名 modify 列名 新数据类型;

查看建表语句

show create table 表名;

二.约束

三.条件判断

1.and

多个条件之间可以使用and进行连接

例子:

select * from student where name='张三' and score > 90;

2.or

或

例子:

select * from student where name='张三' or score > 90;

3.关系表达式

4.between and

在两者之间

select * from student where score >= 98 and score<=100;
等价于
select * from student where score between 98 and 100;

5.in

条件是一个范围的时候用in

例子:

select 列限定 from 表限定 where 列名 in(值1,值2....);
如 : 给出一个数据集合(1,3,10,20),获取学生id在这个数据集合中的学生信息
select * from student where id in (1,3,10,20);

6.模糊查询

使用关键字like

%的含义:0-n个任意字符

-的含义:匹配任意单个字符

例子:

如 : 把name中,把姓张的查询出来
select * from student where  name like '张%';
如 : 把 name中,姓名有两个字的查询出来
select * from student where  name like '__';

注意:如果想要查询 _ 或者 % 需要转义

7.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;

8.limit

限制查询出来的条数

例子:

语法 : 

select 列限定 from 表限定 limit 条数;
select 列限定 from 表限定 limit 开始值(下标从0开始) ,条数;
如 : 查询学生表,分数前三名的信息
select * from  student order by score desc limit 3;
如 : 查询学生表,分数第二名和第三名
select * from  student order by score desc limit 1,2;

四.单表查询

1.组函数

常用组函数

2.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;

3.Having

作用:对查询之后的结果进行过滤,where是在查询的时候就进了过滤

例子:

有时候我们也需要做一些判断,比如求出平均值了,我只想要平均值 大于60分的平均分数,这时候用where就不行了
select teacher_id, avg(score) as avg from student  where avg > 60  group by teacher_id;
这个时候就需要使用having进行过滤
select teacher_id, avg(score) as avg from student group by teacher_id having avg > 60;

五.子查询

子查询又叫嵌套查询。它通常可以位于SELECT后面 FROM后面 WHERE后面,共三种使用场景。

1.在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.子查询中只能有一个字段（子查询的结果必须是一行一列）

使用子查询的时候，建议大家养成使用别名的好习惯，这样可以让我们的查询语句更加清晰。别名可以用来命令新字段，也可以用来命名新表.

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.必须要有别名,因为子查询优先被执行,子查询的别名,可以让别的查询当做表或者列去操作

3.where后面

如 : 在不知道teacher_id 和 老师名字的对应关系的情况下,想查询出张老师下面的所有学生信息
select * from student where teacher_id in (
select id from teacher where name='张老师'
);

注意:

1.多条数据要用in而不要用=,如果确定子查询的结果为一行一列的话,就可以用 = 等于

2.如果返回结果为多行一列的话 要用 in , 一列是必须的,必须是一列

3.子查询中的SELECT后面只能有一个字段（多个字段的话会报错）

六.union和union all

联合查询,合并查询的结果

union:会去重,会将重复的数据去除

union all :不会去重,而是将两个表查出来的数据合并到一起

七.常用函数