mysql学习-mysql进阶

一.DDL操作

1.有关表的一些操作

更改表名

alter table 表名 rename 新表名;

更改字段名

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

添加字段

alter table 表名add 列名类型;

删除字段

alter table 表名 drop 列名;

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

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

查看建表语句

show create table 表名;

二.约束

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

三.条件判断

1.and

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

例子:

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

2.or

或

例子:

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

3.关系表达式

表达式	含义
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<>	不等于
is null	为空
is not null	不为空

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.组函数

常用组函数

函数名称	含义
count(*)	总条数
max(字段名)	最大值
min(字段名)	最小值
avg(字段名)	平均值
sum(字段名)	总和

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 :不会去重,而是将两个表查出来的数据合并到一起

七.常用函数

函数名称	作用
select version() ;	显示当前MySQL软件的版本
select database();	显示当前所处数据库是哪个
select char_length(‘中国’);	返回字符个数。
select length(‘中国’);	返回字符所占字节数,MySQL中，一个UTF8编码的汉字占3个字节
select concat( ‘a’, ‘b’, ‘c’, ‘d’);	返回 ‘abcd’。字符串拼接函数
select concat_ws( ‘=’, ‘a’, ‘b’, ‘c’);	返回 ‘a=b=c’。字符串拼接函数,第一个是拼接间隔符
select upper(‘abcd’);	返回ABCD。将参数中所有小写字母转换为大写
select lower(‘ABCD’);	返回abcd。将参数中所有大写字母转换为小写
select substring( ‘系统信息类’, 1, 3 );	返回 系统信。第2个参数代表从1开始的第几个字符，第3个参数代表截取字符个数
select trim(’ abc ');	返回 abc。用于删去参数左右的所有空格
select curdate();	返回当前日期
select curtime();	返回当前时间
select now();	返回当前日期时间
select unix_timestamp();	返回当前日期时间对应的时间戳（单位秒）
select unix_timestamp(‘2018-05-24 20:00:00’);	返回参数指定的日期时间对应的时间戳（单位秒）
select from_unixtime(1527163397);	返回参数指定时间戳（单位秒）对应的日期时间
select datediff( ‘2018-05-23’, now() );	返回两个参数对应日期相差的天数（用第一个参数减第二个参数）
select adddate( now(), -2 );	返回指定天数前/后的日期时间（第一个参数是日期时间，第二个参数是天数，向后加是正数，向前减是负数）
select year(‘2019-02-24’);	返回2019 获得年份
select month(‘2019-02-24’)	返回2 获得月份
select day(‘2019-02-24’)	返回 24 获取日
select if( <判断条件>, <条件为真时的返回值>, <条件为假时的返回值> );	相当于Java中的三目运算符<判断条件> ? <条件为真的返回值> : <条件为假的返回值>。
如select if(1=1, 2, 3);	返回2。
select ifnull(<表达式或者字段>, <表达式或者字段为NULL时的返回值>);	通常用于给有可能有NULL的情况下的提供默认值。
select ifnull(null,‘无名氏’) ;	null这里可以写列名 就会把该列值为null的 以无名氏显示
f(1=1, 2, 3);	返回2。
select ifnull(<表达式或者字段>, <表达式或者字段为NULL时的返回值>);	通常用于给有可能有NULL的情况下的提供默认值。
select ifnull(null,‘无名氏’) ;	null这里可以写列名 就会把该列值为null的 以无名氏显示