数据库 学习 day2 : 主键外键约束,条件判断,关系表达式,模糊查询,排序,分组,嵌套查询(子查询),常用函数

1.SQL

1.1        SQL

SQL，它的全称叫Structured Query Language，结构化的查询语言。之所以出现这个东西，是为了统一/屏蔽不同数据库厂商生产的数据库产品之间的差异。

SQL定义了一系列标准和规范,数据库厂商也需要按照这个规范来,当然会有一些细小的差别,相比没有规范来说,要好很多

比如 项目后期由于某些原因,需要进行数据库更换,那么操作数据库的语句就都需要更改,而SQL出现,可以避免这个问题

1.2        DDL语句

(1)        更改表名

                -- 更改表名

                -- alter table 表名 rename 新表名;

                alter table student rename t_student;

(2)        更改字段名

                -- 更改字段名

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

                alter table t_student change id no int;

(3)        添加字段

                -- 添加字段

                -- alter table 表名 add 列名 类型;  把该列添加到尾部

                alter table t_student add sex char(2);

                -- alter table 表名 add 列名 类型 after 已有列名;  把该列添加到指定列的后面

                alter table t_student add sex1 char(2) after no;

                -- alter table 表名 add 列名 类型 first;  把该列添加到首部

                alter table t_student add sex2 char(2) first;

(4)        删除字段

                -- 删除字段

                -- alter table 表名 drop 列名;

                alter table t_student drop sex2;

2.约束

 2.1        主键

我们的java类,对应的就是一张表,成员变量对应一个字段,一个类对象对应一条数据,那么对象都有一定的唯一性

比如判断对象是否相等,我们通常使用equals()方法和hashCode()方法,那么怎么在数据库中表示数据的唯一性呢?主键

主键通常用于唯一确定表中的一条记录，设置为主键的字段是不能为NULL并且不能重复的。

主键可以设置在一个字段上，也可以设置在多个字段上。（但大多数场景都是设置在一个字段上，这个字段通常是业务主键或者流水号）

主键设置可以划分为两种

第一种 : 创建表语句时,添加主键约束

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

下面是使用方式

第一种:创建表语句时,添加主键约束

 create table person(
        id int ,
        name varchar(100),
        income decimal(18,2),
        primary key (id,name) 
    );
上面代码设置了两个主键
 create table person1(
        id int ,
        name varchar(100),
        income decimal(18,2),
        primary key (id) 
    );
上面代码设置了一个主键
如果只有一列主键,也可以直接写在字段后面
 create table person2(
        id int primary key,
        name varchar(100) ,
        income decimal(18,2)

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

语法 :  alter table 表名 add primary key(列名,列名...);
create table person3(
        id int ,
        name varchar(100),
        income decimal(18,2)
      );
比如要对person3表添加id列主键
 alter table person3 add primary key(id);

主键自增:

上面我们已经对表添加了主键,主键值不能为空且不能重复,那么问题来了...

如果主键的值让客户输入的话,很容易就重复了,比如888,666等数字大家都喜欢使用,导致一直输入不正确,非常不方便

所以又有了自增的概念,所谓自增,望文知意,就是自动增加,不用我们输入值

但是自增的列,必须为主键列,关键字 auto_increment

设置自增的两种方式 :

第一种 : 建表时,添加自增

第二种 : 创建表之后,添加自增

下面是使用方式

第一种:建表时,添加自增

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

第二次,创建表之后,添加自增

语法 : alter table 表名modify 主键列名 类型 auto_increment;
create table person5(
	id int ,
	name varchar(200),
	 primary key(id)
);

设置自增的起始值

语法 : alter table 表名auto_increment=值; 
create table person6(
	id int auto_increment ,
	name varchar(200),
	 primary key(id)
);
alter table person6 auto_increment=10000; 

2.2        外键

对应java代码来说,外键就是类的关联关系(一个类的成员变量是另外一个类的对象引用)

像这种一个类的变量可以找到另外一个类对象的这种关联关系,在数据库中怎么体现呢? 外键

一个表中的外键列,需要参照另一个表的主键值生成,并且一个表的外键列的值和另一个表的主键值的数据类型必须一致,

然后就可以通过这个表中的外键 去找另一个表的主键,能找到主键就能根据主键找到对应的一行数据

常用于有关联关系的两个表中

外键列的值,必须是关联表中的已有主键值,也可以为空

具体外键中的查询,现在不考虑,到DQL的时候咱们再说,现在子查询都还没讲,所以先了解什么是外键

设置外键有两种方式:

第一种:创建表示添加外键约束

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

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

第二种:创建完表以后,添加外键约束

create table student1 (
    id int ,
    name varchar(20),
    teacher_id int,
    primary key (id)
);
create table teacher1(
    id int ,
    name varchar(20),
    primary key (id)
);

2.3        唯一约束 unique

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。

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

设置unique约束有两种方式 : 

第一种 : 创建表时,添加unique约束

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

第二种 : 创建表之后,添加unique约束

create table temp1 (
    id int ,
    `name` varchar(20)
);
alter table temp1 add unique (id);

2.4        非空约束和默认值

所有的类型的值都可以是null，包括int、float 等数据类型,设置为not null的字段,必须填入数据

经常和default一起使用,当不填写数据的时候,把默认值设置成指定的值

设置not null 与 default有两种方式 : 

第一种 : 创建表时,添加约束

create table temp2(
    id int not null,
    `name` varchar(30) default  'abc',
	sex varchar(10) not null default '男'
);

第二种 : 创建表之后,添加约束

create table temp3(
    id int,
    `name` varchar(30) ,
	sex varchar(10) 
);
alter table temp3 modify id int not null ;
alter table temp3 modify name  varchar(30)   default  'abc';
alter table temp3 modify sex varchar(10) not null  default '男';

3.条件判断

3.1        and

且,和,的意思,一般用于 必须符合两个添加的判断,等同于java中的 &&

语法 :

select 列限定 from 表限定 where A表达式 and B表达式;

如 : 查询学生表中,name是张三且成绩大于90分

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

只会查询出符合两个条件的学生

3.2        or

或的意思,一般用于 符合一个添加判断的情况下,等同于java中的 ||

语法 :

select 列限定 from 表限定 where A表达式 or B表达式;

如 : 查询学生表中,name是张三 或 成绩大于90分

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

只要符合两个条件中的任何一个条件,就可以

注意 : 如果 一个语句中,同时出现了and和or的话,and优先级高

3.3        关系表达

> , >= , <  , <= ,<>,=

> : 大于

< : 小于

>= : 大于等于

<= : 小于等于

= : 相等

<> : 不等于

注意 : = 和 <> 额外留意,和java中有所不同,java中判断相等用 == , 这里只用 = , java中判断不相等用 != , 这里使用 <>

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

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

3.5        in

在指定数据中

语法 :

select 列限定 from 表限定 where 列名 in(值1,值2....);

如 : 给出一个数据集合(1,3,10,20),获取学生id在这个数据集合中的学生信息

select * from student where id in (1,3,10,20);

等于多个or

比如批量删除功能,就可以使用in

Delete from student where id in(x,x,x,x);

4.模糊查询

我们经常会用到搜索功能,比如百度,搜索功能实现,就是使用like模糊查询技术点

其中 % 匹配任意个数的任意字符

 _ 匹配单个任意字符

-- 模糊查询 like

-- % 匹配任意个数任意字符  等于 .*

-- _ 匹配单个任意字符 等于 .

-- 语法 : select 列限定 from 表限定 where 列 like 'xxxx%_xxa';

语法 :

select 列限定 from 表限定 where 列名 like  '值' ;

如 : 把name中,把姓张的查询出来

select * from student where  name like '张%';

如 : 把 name中,姓名有两个字的查询出来

select * from student where  name like '__';

如果想要查询 _ 或者 % 需要转义  \ %   \ _

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

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

7.单表查询

MYSQL中有一类特殊的函数，用于统计，或者分组统计,

分组关键字使用 group by

常用组函数有 :

count(*) : 总条数

max(字段名) : 最大值

min(字段名) : 最小值

avg(字段名) : 平均值

sum(字段名) : 总和

示例数据

create table student (

    id int ,

    name varchar(20),

    teacher_id int,

    score decimal(18,2) ,

    primary key (id)

);

create table teacher(

    id int ,

    name varchar(20),

    primary key (id)

);

insert into teacher (id,name)values(1,'张老师');

insert into teacher (id,name)values(2,'王老师');

insert into student (id,name,teacher_id,score)values(1,'张三',1,90);

insert into student (id,name,teacher_id,score)values(2,'李四',2,88.9);

insert into student (id,name,teacher_id,score)values(3,'王五',1,45.7);

insert into student (id,name,teacher_id,score)values(4,'赵六',1,84);

insert into student (id,name,teacher_id,score)values(5,'小明',2,92.5);

insert into student (id,name,teacher_id,score)values(6,'小红',2,47);

语法 :

select  count(*),max(字段名),min(字段名)... from 表名 group by 字段名;

如 : 查看学生表共有多少学生

select  count(*)  from   student;

如 : 查看学生表中分数大于90分的有多少学生

select  count(*)  from   student where score > 90;



-- 查看有多少学生

select count(*) from student;

-- 查看大于90分的有多少学生

select count(*) from student where score>90;

select count(*) as '总人数' , sum(score) '总分数' , max(score) '最高分' , min(score) '最低分' ,avg(score) '平均分' from student;

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就不行了

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;

8.子查询

子查询又叫嵌套查询。它通常可以位于SELECT后面 FROM后面 WHERE后面,共三种使用场景。当我们查询一个表没有办法实现功能的时候,就需要使用子查询

上面我们讲到了分组查询，可以查询每个老师所带学生的最低分，

但是我们刚才查出来之后,我们只能看到teacher_id,但是我们并不知道teacher_id对应的是那个老师,这个时候我们最好是显示老师的名字是比较好的,可以用子查询实现

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

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

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

8.3        where后面的

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

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

注意 :

当位于WHERE后面时，要注意

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

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

9.Union与Union all

合并查询,合并查询的结果
Union 会去除重复项
Union all 不会去除重复项
如 : 查询出 teacher_id = 1 的所有学生信息
select * from student where teacher_id=1;
如 : 查询出 学生分数大于60的所有学生信息
select * from student where score > 60;
如 : 查询出 学生分数大于60 或 teacher_id = 1 的所有学生信息(去除重复)
// 用 or 实现 
select * from student where teacher_id=1 or score > 60;
// 用 union实现
select * from student where teacher_id=1 
    			union
select * from student where score > 60;
如 : 查询出 学生分数大于60 或 teacher_id = 1 的所有学生信息(可重复)
select * from student where teacher_id=1 
    			union all
select * from student where score > 60;

总结和注意 :

union / union all

它俩的作用是把两张表或者更多表合并成一张表

前者会去重（去重的依据是，UNION时SELECT出来的字段如果对应相等则认为是同一条记录，这的逻辑我们可以参考Java equals）

但是or 尽管两行数据每个字段都相等,也不会去重

后者则不会去重，它会保留两张表中的所有记录，但是它性能高（因为去重操作要花时间），

尽量使用union all，把去重这个工作交给代码去完成，这样可以减少MYSQL服务器的压力

使用union / union all的时候要注意：

1.参与合并的表，它们SELECT出来的字段数量必须一致（强制规则）

2.参与合并的表，它们SELECT出来的字段的类型建议一一对应（非强制，但是最好遵循这条规则）

3.参与合并的表，它们SELECT出来的字段的顺序建议一致（非强制，但是最好遵循这条规则

9.常用函数

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的 以无名氏显示
select ifnull(name,'无名氏') from teacher ;



-- 版本
select version();

-- 所占用的字节个数,mysql中 一个汉字占用3个字节
select length('张三1');
-- 占用字符个数
select char_length('张三2');
-- 字符串拼接
select concat('a','b','c');
select concat(id,name,score) from student;

-- 指定分隔符进行拼接  第一个参数是分隔符
select concat_ws('-',id,name,score) from student;

select upper('abc');
select lower('ASDasdCAS');
-- 截取字符串 1 是第几个开始 , 3是截取几个 abc
select substring('abcdeq',1,3);

-- 查询学生表 把名字改完首字母大写
1 获取第一个字符
	substring(name,1,1)
2 转换为大写
	upper(substring(name,1,1))
3 获取非第一个字符
	substring(name,2,LENGTH(name)-1)
4 转换为小写
	lower(substring(name,2,LENGTH(name)-1))
5 拼接起来
	concat(upper(substring(name,1,1)),lower(substring(name,2,LENGTH(name)-1)));
select concat(upper(substring(name,1,1)),lower(substring(name,2,LENGTH(name)-1))) from student;

update student set name = concat(upper(substring(name,1,1)),lower(substring(name,2,LENGTH(name)-1)));