MySQL更改表结构,约束,DQL,组查询子查询

ddl高级操作

改表结构

改表名

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 after teacher_name;添加到指定列后面
alter table t_teacher add sex char(2) first;添加到前面第一列,需要把表关掉,刷新一下,再打开才能显示出来

删除字段

alter table 表名 drop 列名;
例
alter table t_teacher drop birthday;

更改字段类型

alter table 表名 modify 列名 新数据类型;
alter table 表名 modify 列名 数据类型 comment ‘该列的注释说明’; 更改类型的同时,还能添加注释说明
alter table t_teacher modify sex varchar(20);

查看建表语句

show create table 表名;

约束

约束类型	关键字
主键	primary key
外键	foreign key
唯一	unique
非空	not null
自增	auto_increment

主键

主键通常用于唯一确定表中的一条记录，设置为主键的字段是不能为NULL并且不能重复的。主键可以设置在一个字段上，也可以设置在多个字段上(只要多个字段组合在一起不重复就行)。（但大多数场景都是设置在一个字段上，这个字段通常是业务主键或者流水号）

创建表语句时,添加主键约束

单个主键
create table person(
id int,
name varchar(100),
income decimal(18,2),
primary key(id)
这种方式会有默认值0
);
多主键
create table person(
id int,
name varchar(100),
income decimal(18,2),
primary key(id,name)
);
如果只有一列主键,也可以直接写在字段后面

create table person(
    id int primary key,
    name varchar(20),
    income decimal(18,2)
);

创建表完成之后,通过alter添加主键约束

alter table 表名 add primary key(列名1,列名2…);

alter table person3 add primary key(id);

主键自增

auto_increment

建表时,添加自增

create table person4(
id int auto_increment ,
name varchar(200),
primary key(id)
);

创建表之后,添加自增

alter table 表名modify 主键列名 类型 auto_increment;
例
alter table person modify id int auto_increment;

设置自增的起始值

alter table 表名auto_increment=值;

create table person6(
 id int auto_increment ,
 name varchar(200),
  primary key(id)
);
alter table person6 auto_increment=10000; 

外键

一个表中的外键列,需要参照另一个表的主键值生成,并且一个表的外键列的值和另一个表的主键值的数据类型必须一致,
然后就可以通过这个表中的外键 去找另一个表的主键,能找到主键就能根据主键找到对应的一行数据常用于有关联关系的两个表中
外键列的值,必须是关联表中的已有主键值,也可以为空

创建表时添加外键约束

create table teacher(
    id int ,
    name varchar(20),
    primary key (id)
);
create table student (
    id int ,
    name varchar(20),
    teacher_id int ,
    primary key (id),
    foreign key (teacher_id) references teacher(id)
);

引用student中添加外键列,指向teacher表,所以必须先创建teacher表才行

创建完表之后,添加外键约束

alter table student1 add foreign key (teacher_id) references teacher1 (id);

唯一约束 unique

唯一约束不允许出现重复的值，但是可以为多个null.

创建表时,添加unique约束

create table temp (
    id int ,
    `name` varchar(20),
    unique(id)
);
--或
   create table temp (
    id int unique ,
    `name` varchar(20)
);

创建表之后,添加unique约束

alter table temp1 add unique (id);

非空约束 not null 与默认值 default

create table temp2(
    id int not null,
    `name` varchar(30) default  'abc',
 sex varchar(10) not null default'男' 
);
--如果设置自增了不写id也可以

检查约束(MySQL不支持)

检查字段的值是否为指定的值
一般用于性别等,对列的值做一些限制,比如 性别列的值只能是男或者女但是MySQL这里给舍弃了,不再支持,所以六大约束就成了五大约束

DQL

条件判断

and 且

select 列限定 from 表限定 where A表达式 and B表达式;
如 : 查询学生表中,name是张三且成绩大于90分
select * from student where name='张三' and score > 90;

or 或

select 列限定 from 表限定 where A表达式 or B表达式;
如 : 查询学生表中,name是张三 或 成绩大于90分
select * from student where name='张三' or score > 90;
只要符合两个条件中的任何一个条件,就可以

关系表达式

符号	含义
>	大于
<	小于
>=	大于等于
<=	小于等于
=	相等
<>	不等于

注意 : 判断是否为空,应该使用is null,而不是 = null , 同理,判断不为空应该使用 is not null ,而不是 <>null,并且and和or同时出现的话,and优先级比or要高

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);
常用来批量删除
DELETE from student1 WHERE id in(1,2,6);

模糊查询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

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;

单表查询(组函数)

常用的组函数
count(*): 数据一共有多少条数
max(字段名):最大值
min(字段名):最小值
avg(字段名):平均值
sum(字段名):总和

select count(*),max(字段名),min(字段名)... from 表名 group by 字段名;
如 : 查看学生表共有多少学生
select count(*) from student;
如 : 查看学生表中分数大于90分的有多少学生
select count(*) from student where score > 90;

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

用于分组查询或者函数操作后类似where(用不了)的条件
例如:要平均值 大于60分的平均分数where错误
select teacher_id, avg(score) as avg from student where avg > 60 group by teacher_id;
这个时候就需要使用having进行过滤having正解
select teacher_id, avg(score) as avg from student group by teacher_id having avg > 60;

子查询

又称嵌套查询查询里面套查询,上面的分组查询，可以查询每个老师所带学生的最低分，但是我们刚才查出来之后,我们只能看到teacher_id,但是我们并不知道teacher_id对应的是那个老师,这个时候我们最好是显示老师的名字是比较好的,可以用子查询实现

select后面

select 字段名,(查询语句) from 表名;
如 : 查询所有学生的信息并显示老师的名字

select *,(
          select name from teacher where id=teacher_id   
      ) as teacher_name from student ;
--这里面 teacher_id 是student表的外键,它连接的teacher表的主键id,括号的位置是是student的列,它的条件是学生的teacher_id结果是老师的名字,列名as 是teacher_name,结果就是学生表加了一个老师名字的 teacher_name 列 

如 : 查询每个老师的学生的 最大分数,最小分数,平均分数,分数总和,学生人数,老师名字

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后面

select *,
case rank

#rank是括号里列名case操作这一列的数据

when ‘A’ then ‘优’

#当a就是优

when ‘B’ then ‘良’
when ‘C’ then ‘差’
end rank_ch

#最后以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;

#把括号里的查询看作一个表来操作

--这种方法也可以实现
select *,
case 
when score < 60 then 'C' 
when score >=60 and score <80 then 'B' 
when score >=80 then 'A' 
end as rank,
case 
when score < 60 then '差' 
when score >=60 and score <80 then '良' 
when score >=80 then '优' 
end as rank2 
from student

注意 :
当位于FROM后面时，要注意
1.我们可以把子查询当成一张表
2.必须要有别名,因为子查询优先被执行,子查询的别名’a’,可以让别的查询当做表或者列去操作

where后面

在不知道teacher_id 和 老师名字的对应关系的情况下,想查询出张老师下面的所有学生信息

select * from student where teacher_id in (
select id from teacher where name='张老师'
);

当位于WHERE后面时，要注意

1. 多条数据要用in而不要用=,如果确定子查询的结果为一行一列的话,就可以用 = 等于
2. 如果返回结果为多行一列的话 要用 in , 一列是必须的,必须是一列
   3.子查询中的SELECT后面只能有一个字段（多个字段的话会报错