MySQL基础知识---第二天

太过爱你忘了你带给我的痛 2023-03-06 03:20 354阅读 0赞

# MySQL基础学习—第二天 #

## 1. SQL介绍 ##

#### 1.1 SQL介绍 ####

> SQL，它的全称叫Structured Query Language，结构化的查询语言。之所以出现这个东西，是为了统一/屏蔽不同数据库厂商生产的数据库产品之间的差异。
> 
> SQL定义了一系列标准和规范,数据库厂商也需要按照这个规范来,当然会有一些细小的差别,相比没有规范来说,要好很多

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

#### 1.2 简单DDL ####

昨天我们讲了表的创建和删除,可以规定表名是什么,可以有多少列,数据类型分别是什么,那么比如创建错了,想更改,就要涉及到一个关键字 **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增强 ####

上面讲的DDL只是一系列基础操作，它让我们有库有表可以插入数据。但是对于插入的数据是否是有效数据，并不能保证。  
比如我们可以**插入一条所有字段都是NULL**的记录：

insert into student(id,name,score) values (null,null,null);

这种记录白白地占用了我们的存储空间，但是实际上并没有用处，为了防止表中插入这种数据，MYSQL提供了一系列的完整性验证机制。

##### 约束分类 #####

![在这里插入图片描述][20200804194832393.png]

###### 实体完整性(主键) ######

我们的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)
    
    );

测试语句 :

insert into person4(name)values('测试');

并未输入id的值,但是可以自动填充

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

**语法 :**

> alter table 表名modify 主键列名 类型 auto\_increment;

create table person5(
    
           id int ,
    
           name varchar(200),
    
    primary key(id)
    
    );
    
    alter table person5 modify id int auto_increment;

测试语句 :

insert into person5 (name)values('测试');

并未输入id的值,但是可以自动填充

**设置自增的起始值**

**语法 :**

> alter table 表名auto\_increment=值;

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

测试语句 :

insert into person6 (name)values('测试');

id值从10000开始

###### 关联完整性(外键) ######

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

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

**外键**

> 一个表中的外键列,需要参照另一个表的主键值生成,并且一个表的外键列的值和另一个表的主键值的数据类型必须一致,然后就可以通过这个表中的外键,去找另一个表的主键,能找到主键就能根据主键找到对应的一行数据

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

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

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

**设置外键有两种方式 :**

> 第一种 : 创建表时添加外键约束
> 
> 第二种 : 创建完表之后,添加外键约束

下面是使用方式

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

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表才行

测试语句

***添加一个讲师***

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

***添加一个学生小明,学生通过teacher\_id可以指向张老师***

insert into  student (id,name,teacher_id) values(1,'小明',1);

***添加一个学生小红,teacher\_id没有设置值***

insert into  student (id,name) values(2,'小红');

***添加一个小黑,teacher\_id指向一个不存在的讲师,报错***

insert into student (id,name,teacher_id) values(3,'小黑',2);

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

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

**语法 :**

> alter table 表名 add foreign key (外键列列名) references 指向的表名 (主键列列名);

例如:

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

测试语句

***添加一个讲师***

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

***添加一个学生小明,学生通过teacher\_id可以指向张老师***

insert into  student1 (id,name,teacher_id) values(1,'小明',1);

***添加一个学生小红,teacher\_id没有设置值***

insert into  student1 (id,name) values(2,'小红');

***添加一个小黑,teacher\_id指向一个不存在的讲师,报错***

insert into student1 (id,name,teacher_id) values(3,'小黑',2);

###### 唯一约束unique ######

> 唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。
> 
> 唯一约束不允许出现重复的值，但是可以为多个null.

**设置unique约束有两种方式 :**

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

下面是使用方式

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

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

***添加一条没有id的数据***

insert into temp (name)values('张三');

***再添加一条没有id的数据,可以添加(唯一约束,又不是不为空约束)***

insert into temp (name)values('李四');

***添加一条id为1 的数据***

insert into temp (id,name)values(1,'王五');

***再添加一条id为1的数据,报错,因为已经有了id为1了,不可重复***

insert into temp (id,name)values(1,'赵六');

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

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

alter table temp1 add unique (id);

***添加一条没有id的数据***

insert into temp1 (name)values('张三');

***再添加一条没有id的数据,可以添加(唯一约束,又不是不为空约束)***

insert into temp1 (name)values('李四');

***添加一条id为1 的数据***

insert into temp1 (id,name)values(1,'王五');

***再添加一条id为1的数据,报错,因为已经有了id为1了,不可重复***

insert into temp1 (id,name)values(1,'赵六');

###### 非空约束 not null与 默认值 default ######

> 所有的类型的值都可以是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 '男'
    
    );

测试语句 :

***只添加id值,可以,因为name和sex都有默认值***

insert into temp2 (id) values (1);

***如果设置了值,默认值就不再设置***

insert into temp2 (id,name,sex) values (2,'张三','女');

注意:

***没有添加id的值,而id又设置不能为空,并且也没有默认值,所以报错***

insert into temp2 (name,sex) values ('李四','女');

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

**语法 :**

> alter table 表名 modify 列名 数据类型 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 '男';

测试语句 :

***只添加id值,可以,因为name和sex都有默认值***

insert into temp3 (id) values (1);

***如果设置了值,默认值就不再设置***

insert into temp3 (id,name,sex) values (2,'张三','女');

***没有添加id的值,而id又设置不能为空,并且也没有默认值,所以报错***

insert into temp3 (name,sex) values ('李四','女');

###### Check扩展约束 ######

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

#### 1.4 基础DQL ####

> DQL : Data Query Language，数据查询语言,主要用于查询表。
> 
> 它通常用来从一张表或者多张表（视图或者子查询等）中按指定的条件筛选出某此记录。涉及到的命令有select。

**语法 :**

> select 列限定 from 表限定 where 行限定;

示例代码 :

create table teacher(
    
                  id int,
    
    `name` varchar(30)
    
    );
    
    insert into teacher (id,name) values (1,'张老师');
    
    insert into teacher (id,name) values (2,'王老师');

最简单粗暴的一个DQL,会查询到teacher表中所有的数据:

select * from teacher;

如果想查看所有行的name

select name from teacher;

如果想查看id为1的讲师姓名

select name from teacher where id = 1;

## 2. 条件判断 ##

上面一个简单的DQL查询时,我们使用到了where行限定,后面写的是id=1

但是如果这一个条件不满足我们判断的条件呢?比如 id=1 且 name=’张老师’ 就怎么怎么样

所以我们的条件判断,是**用于where后面的**

示例代码 :

create table student(
           id int,
    `name` varchar(30),
           score decimal(18,2)
    );
    
    insert into student(id,name,score) values (1,'张三',99.2);
    
    insert into student(id,name,score) values (2,'李四',97.9);
    
    insert into student(id,name,score) values (3,'王五',98);
    
    insert into student(id,name) values (4,'赵六');
    
    insert into student(id,name,score) values (5,'小明',98);

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

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

\*\*

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

\*\*

#### 关系表达式 ####

###### 关系表达式 ######

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

例如 :

***查询学生表中,成绩大于90分的***

select * from student where score > 90;

例如 :

***查询学生中,成绩为空的学生***

错误 判断为空不能使用 = null ,应该使用 is null

select * from student where score = null;

select * from student where score is null;

例如 :

***查询学生中,成绩不为空的学生***

错误 判断不为空 不能使用 <>null,应该使用 is not null

select * from student where  score <> null;

select * from student where score is not  null;

注意 :

> 判断是否为空,应该使用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);

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

## 3. 单表查询(组函数) ##

> 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(\*),max(\`字段名),min(字段名)… from 表名 group by 字段名;

例如:

***查看学生表共有多少学生***