数据库增删改查II

墨蓝 2024-03-26 16:01 68阅读 0赞

**目录**

1.约束

1.1null约束

1.2唯一约束

1.3默认值约束

1.4主键约束

1.5外键约束

2.新增

3.查询

3.1聚合查询

3.1.1.聚合函数

3.1.2group by子句

3.1.3 having

3.2联合查询

3.2.1内连接

3.2.2外连接

3.2.3 自连接

3.2.4子查询

3.2.5合并查询

--------------------

## 1.约束 ##

### 1.1null约束 ###

语法

> create table 表名( 列名 类型 not null);

指定某列不能存储null值--必填项

create table student(id int not null,sn int,name varchar(20));

### ![b08a3401ee9e4b3a8cbd52039eaefd85.png][] ###

### 1.2唯一约束 ###

语法

> create table 表名( 列名 类型 unique);

先查询,看数据是否存在,如果存在,则插入/删除失败,如果不存在,则能插入/删除

create table student(id int not null,sn int unique,name varchar(20));

sn用unique约束,sn的值则唯一,已经插入sn=1时,再次插入sn=1会插入失败

mysql> insert into student values(1,1,'wang');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into student values(2,1,'wan');
    ERROR 1062 (23000): Duplicate entry '1' for key 'sn'

### 1.3默认值约束 ###

语法

> create table 表名( 列名 类型 default '默认值')

insert指定列插入,其他未被指定到的列就按默认值填充

create table student(id int not null,sn int unique,name varchar(20) default'unkrown');

name用默认值约束,当name列为空时,用默认值unkrown填充

insert into student(id,sn) values(2,2);

\+----+------+---------+  
| id | sn | name |  
\+----+------+---------+  
| 1 | 1 | wang |  
| 2 | 2 | unkrown |  
\+----+------+---------+

### 1.4主键约束 ###

语法

> create table 表名( 列名 类型 primary key);

特点:

1.唯一,不为空, 一个表一个主键

2.可以是一个列,可以是多个列

3.主键是not null和unique的结合,可以不用not null

create table student(id int not null primary key,sn int unique,name varchar(20));

\+-------+-------------+------+-----+---------+-------+  
| Field | Type | Null | Key | Default | Extra |  
\+-------+-------------+------+-----+---------+-------+  
| id | int(11) | NO | PRI | NULL | |  
| sn | int(11) | YES | UNI | NULL | |  
| name | varchar(20) | YES | | NULL | |  
\+-------+-------------+------+-----+---------+-------+

自增主键:整数类型的主键,可以搭配auto\_increment使用.当插入的数据对应字段没有值时,为最大值+1

列名 类型 primary key auto_increment;

mysql> insert into student values(1,1,'wang');
    
    mysql> insert into student(sn,name) values(2,'li');

\+----+------+------+  
| id | sn | name |  
\+----+------+------+  
| 1 | 1 | wang |  
| 2 | 2 | li |  
\+----+------+------+

### 1.5外键约束 ###

外键用于关联其他表的主键或者唯一键

语法

foreign key(字段名) references 主表(列);

创建班级表

CREATE TABLE classes (id INT PRIMARY KEY auto_increment,name VARCHAR(20),`desc` VARCHAR(100));

学生表,学生班级是一对多的关系,id为主键,classes\_id为外键,关联班级表id

create table student(id int primary key auto_increment,sn int unique,name varchar(20) default'unkrown',classes_id int,foreign key(classes_id) references classes(id));

student表中每一个记录的classid在classes表的id都存在

student收到classes的约束,把classes叫做student的父表,student是class的子表

![84c0c4f108094e62aad6fd67e3999c24.png][]

## 2.新增 ##

插入查询结果:查询结果,得到的列数,类型需要和插入表的列数,类型相同.(列名可以不同)

语法

insert into 表名A(...) select...from 表名B;

把表名b中的信息复制到表名a中

## 3.查询 ##

### 3.1聚合查询 ###

#### 3.1.1.聚合函数 ####

1.count:计数

字段名中为null的数据不会计入结果

select count(字段名) from 表名;

2.sum:求和

只对数字列有效,自动跳过结果为null的行

select sum(字段名) from 表名 (where ...);

3.avg:平均值

select avg(字段名+..) (as) 别名 from 表名;

4.max:最大值

select max(字段名) from 表名;

5.min:最小值

select min(字段名) from 表名;

#### 3.1.2group by子句 ####

对指定列先分组在查询

注:指定字段必须是分组依据字段,如果要有其他字段,必须要有聚合函数

select 字段名1, 聚合函数(字段名2),... from 表名 group by 字段名1,...;

#### 3.1.3 having ####

group by 分组后,不能用where ,要用having代替where

select 字段名1, 聚合函数(字段名2),... from 表名 group by 字段名1,...having ...;

### 3.2联合查询 ###

多表查询的步骤

1.分析清除需求中,涉及的信息在哪些表中

2.针对多个表进行笛卡尔积

3.筛选出其中有效数据(此处用学生id做关联条件)

4.结合需求中的条件,进行加强条件

5.针对列进行

#### 3.2.1内连接 ####

语法

1.join对两张表

> select 字段 from 表1 (别名1) join 表2 (别名2) on 连接条件1;

select * from student join score on stsudent.id=score.student_id ;

2.join对多张表

> select 字段 from 表1 (别名1) join 表2 (别名2) on 连接条件1 join 表3 (别名3)  
> on 连接条件2)...;

select * from student join score on stsudent.id=score.student_id join course on course.id=score.course_id;

#### 3.2.2外连接 ####

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

语法

1.join对两张表

> 左外连接:select 字段 from 表1 (别名1) left join 表2 (别名2) on 连接条件1;
> 
> 右外连接:select 字段 from 表1 (别名1) right join 表2 (别名2) on 连接条件1;

左连接:select * from student left join score on stsudent.id=score.student_id ;
    右连接:select * from student right join score on stsudent.id=score.student_id ;

2.join对多张表

> 左外连接:select 字段 from 表1 (别名1) left join 表2 (别名2) on 连接条件1 join 表3 (别名3) on 连接条件2)...;
> 
> 右外连接:select 字段 from 表1 (别名1) right join 表2 (别名2) on 连接条件1 join 表3 (别名3) on 连接条件2)...;

左外连接:select * from student left join score on stsudent.id=score.student_id left join course on course.id=score.course_id;
    右外连接:select * from student right join score on stsudent.id=score.student_id right join course on course.id=score.course_id;

#### 注:内外连接的区别 ####

1.内连接-->两个表中都体现的数据

select name,score from student join score on student.id=score.student_id;
    +------+-------+
    | name | score |
    +------+-------+
    | 张三 |    90 |
    | 李四 |    80 |
    +------+-------+

2.左外连接,以左侧表为准,左侧表中的所有数据都能体现出来

select name,score from student left join score on student.id=score.student_id;
    +------+-------+
    | name | score |
    +------+-------+
    | 张三 |    90 |
    | 李四 |    80 |
    | 王五 |  NULL |
    +------+-------+

3.右外连接,以右侧表为准,右侧表中的所有数据都能体现出来

select name,score from student right join score on student.id=score.student_id;
    +------+-------+
    | name | score |
    +------+-------+
    | 张三 |    90 |
    | 李四 |    80 |
    | NULL |    70 |
    +------+-------+

#### 3.2.3 自连接 ####

自连接是指在同一张表连接自身进行查询。(本质把行转换成列)-->实现行中的比较

自连接要指定表的别名

> select \* from 表名 as 别名1,表名 as 别名2(where...);

#### 3.2.4子查询 ####

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

*  单行子查询：返回一行记录的子查询

> select 字段名1 from 表名1 where 字段名2 =(select 字段名3 from 表名2 where... )

字段名2和字段名3对应

字段名1,字段名2属于表名1

字段名3属于表名2

1.(not)in关键字

> select 字段名1 from 表名1 where 字段名2 (not)in(select 字段名3 from 表名2 where... )

字段名2和字段名3对应

字段名1,字段名2属于表名1

字段名3属于表名2

2.(not) exists关键字

> select 字段名1 from 表名1 where (not) exists(select 字段名3 from 表名2 where... )

字段名2和字段名3对应

字段名1属于表名1

字段名3属于表名2

*  在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。

#### 3.2.5合并查询 ####

为了合并多个select的执行结果，可以使用集合操作符 union，union all.

使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。

1.union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

> select \* from 表名1 where... union select \* from 表名2 where...;

可以用or实现

> select \* from 表名1 where... or select \* from 表名2 where...;

2.union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

> select \* from 表名1 where... union all select \* from 表名2 where...;

[b08a3401ee9e4b3a8cbd52039eaefd85.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/e5a6887c52104903bfca891568905e75.png
[84c0c4f108094e62aad6fd67e3999c24.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/2031318864aa4b8397c34f1603700623.png