MySQL第九周

## 备份

`mysqldump -h 主机名 -u 用户名 -p --databbases 数据库名 > 文件路径`

```
mysqldump -h 127.0.0.1 -u root -p --databases gzsp > D:/mysqldymp/01.sql
```

## 恢复

---

`mysql -h 主机名 -u 用户名 -p < 文件路径`

```
mysql -h 127.0.0.1 -u root -p < D:/mysqldymp/01.sql
```

## 约束

### 非空

not null

### 默认值

default 和 not null 一起使用

### 唯一

unique，字段值不能重复

### 自增

auto_increment 和 unique 一起使用的

## MySQL函数

- count() 统计数据的条数
- sum() 求和
- max() 最大值
- min() 最小值
- avg() 平均值  average

```
create database gzsp;
```

```
use gzsp;
```

```
create table student(
    student_id int unsigned not null auto_increment unique comment "学生ID",
    student_name varchar(50) not null comment "学生名",
    student_score float not null default 0 comment '学生分数'
);
```

```
insert into student values
    (null, "姥爷", 100),
    (null, "七七", 60),
    (null, "公子", 90),
    (null, "雷神", 80),
    (null, "散兵", 0),
    (null, "钟离", 59);
```

```
select * from student;
```

```
+------------+--------------+---------------+
| student_id | student_name | student_score |
+------------+--------------+---------------+
|          1 | 姥爷         |           100 |
|          2 | 七七         |            60 |
|          3 | 公子         |            90 |
|          4 | 雷神         |            80 |
|          5 | 散兵         |             0 |
|          6 | 钟离         |            59 |
+------------+--------------+---------------+
```

统计数据条数 count(字段名), 非空的字段(not null)

```
select count(student_name) from student;
```

```
+---------------------+
| count(student_name) |
+---------------------+
|                   6 |
+---------------------+
1 row in set (0.00 sec)
```

```
select max(student_score) from student;
```

+--------------------+
| max(student_score) |
+--------------------+
|                100 |
+--------------------+

```
select min(student_score) from student;
```

+--------------------+
| min(student_score) |
+--------------------+
|                  0 |
+--------------------+

```
select sum(student_score) from student;
```

+--------------------+
| sum(student_score) |
+--------------------+
|                389 |
+--------------------+

```
select avg(student_score) from student;
```

+--------------------+
| avg(student_score) |
+--------------------+
|  64.83333333333333 |
+--------------------+

## 别名

`字段1 as 别名1, 字段2 as 别名2,`
`字段1 别名1, 字段2 别名2,`

```
select student_id, student_name, student_score from student;
```

+------------+--------------+---------------+
| student_id | student_name | student_score |
+------------+--------------+---------------+
|          1 | 姥爷         |           100 |
|          2 | 七七         |            60 |
|          3 | 公子         |            90 |
|          4 | 雷神         |            80 |
|          5 | 散兵         |             0 |
|          6 | 钟离         |            59 |
+------------+--------------+---------------+

```
select student_id as sid, student_name, student_score from student;
```

+-----+--------------+---------------+
| sid | student_name | student_score |
+-----+--------------+---------------+
|   1 | 姥爷         |           100 |
|   2 | 七七         |            60 |
|   3 | 公子         |            90 |
|   4 | 雷神         |            80 |
|   5 | 散兵         |             0 |
|   6 | 钟离         |            59 |
+-----+--------------+---------------+

```
select student_id as sid, student_name as sn, student_score as ss from student;
```

+-----+--------+-----+
| sid | sn     | ss  |
+-----+--------+-----+
|   1 | 姥爷   | 100 |
|   2 | 七七   |  60 |
|   3 | 公子   |  90 |
|   4 | 雷神   |  80 |
|   5 | 散兵   |   0 |
|   6 | 钟离   |  59 |
+-----+--------+-----+

```
select sum(student_score) from student;
```

+--------------------+
| sum(student_score) |
+--------------------+
|                389 |
+--------------------+

```
select sum(student_score) as student_total_score from student;
```

```
+---------------------+
| student_total_score |
+---------------------+
|                 389 |
+---------------------+
```

省略 as, 直接写别名

```
select avg(student_score) student_average_score from student;
```

+-----------------------+
| student_average_score |
+-----------------------+
|     64.83333333333333 |
+-----------------------+

## 条件查询

### 基础

`where 条件表达式`

- 相等  =
- 不等 !=
- 大于 >
- 大于等于 >=
- 小于 <
- 小于等于 <=

查询不及格学生

```
select * from student where student_score < 60;
```

+------------+--------------+---------------+
| student_id | student_name | student_score |
+------------+--------------+---------------+
|          5 | 困困         |             0 |
|          6 | 旺旺         |            59 |
+------------+--------------+---------------+

查询学生名为 笨笨 的信息

```
select * from student where student_name = '笨笨';
```

+------------+--------------+---------------+
| student_id | student_name | student_score |
+------------+--------------+---------------+
|          1 | 笨笨         |           100 |
+------------+--------------+---------------+

### where 配合 聚合函数

统计不及格学生的人数

```
select count(student_id) from student where student_score < 60;
```

+-------------------+
| count(student_id) |
+-------------------+
|                 2 |
+-------------------+

统计成绩优秀的学生人数 (80 - 90)

and (和、且)

```
select count(student_id) from student where student_score >= 80 and student_score <= 90;
```

between A and B (在 A 和 B 之间, 包含A和B)

```
select count(student_id) from student where student_score between 80 and 90;
```

or (或者) 满足任意一个条件即可

```
select * from student where student_score >= 80  or student_id > 2;
```

## 排序

`order by 字段 排序方式`

排序方式

1. 升序 (从小到大) asc ascending 上升
2. 降序 (从大到小) desc descending 下降

```
select * from student order by student_score desc;
```
+------------+--------------+---------------+
| student_id | student_name | student_score |
+------------+--------------+---------------+
|          1 | 笨笨         |           100 |
|          3 | 静静         |            90 |
|          4 | 甜甜         |            80 |
|          2 | 忘忘         |            60 |
|          6 | 旺旺         |            59 |
|          5 | 困困         |             0 |
+------------+--------------+---------------+

```
select * from student order by student_score asc;
```

+------------+--------------+---------------+
| student_id | student_name | student_score |
+------------+--------------+---------------+
|          5 | 困困         |             0 |
|          6 | 旺旺         |            59 |
|          2 | 忘忘         |            60 |
|          4 | 甜甜         |            80 |
|          3 | 静静         |            90 |
|          1 | 笨笨         |           100 |
+------------+--------------+---------------+

```
select * from student where student_score >= 60 order by student_score desc;
```

+------------+--------------+---------------+
| student_id | student_name | student_score |
+------------+--------------+---------------+
|          1 | 笨笨         |           100 |
|          3 | 静静         |            90 |
|          4 | 甜甜         |            80 |
|          2 | 忘忘         |            60 |
+------------+--------------+---------------+