MySQL第十周

最新推荐文章于 2024-10-17 17:40:04 发布

ZHANG y_

最新推荐文章于 2024-10-17 17:40:04 发布

阅读量47

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/ZHANG_____LE/article/details/130487857

版权

## 函数

聚合函数（统计）

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

## 排序

order by 字段名排序方式

1. 升序 asc
2. 降序 desc

## 在 A 和 B 之间

between A and B 包含 A 和 B, [A, B]

## 条件

- and 和（且）所有的条件都要成立
- or 或满足任意一个条件即可

## MySQL 远程连接

1. 主机（IP 地址）
2. 用户名（MySQL的用户名）
3. 用户密码（MySQL的用户密码）
4. 端口（默认： 3306）

## like

- `like "字符串"` 精确匹配
- `like "%字符串"` 匹配指定字符结尾
- `like "字符串%"` 匹配指定字符开头的
- `like "%字符串%"` 匹配包含指定字符的

### show databases

准备测试数据库

```
create database db01;
create database db02;
create database tb01;
create database tb02;
```

```
show databases;
```

查找 db01 数据库是否存在

```
show databases like "db01";
```

查找数据库名以 db 开头的数据库

```
show databases like "db%";
```

查找数据库名以 01 结尾的数据库

```
show databases like "%01";
```

查找数据库名包含 0 数据库

```
show databases like "%0%";
```

### show tables

```
create database gzsp;
```

```
use gzsp;
```

```
create table student_01(id int);
create table student_02(id int);
create table student_03(id int);
create table teacher_01(id int);
create table teacher_02(id int);
create table teacher_03(id int);
```

```
show tables;
```

```
show tables like "student";
```

```
show tables like "student%";
```

### select

```
use gzsp;
```

```
create table phone(
id int,
phone_name varchar(255)
);
```

```
insert into phone values
(1, "OPPO K10x 极光 8GB+128GB 67W超级闪充 5000mAh长续航 120Hz高帧屏 6400"),
(2, "OPPO A36 6GB+128GB 晴川蓝高通骁龙680 5000mAh长续航 90Hz炫彩屏"),
(3, "荣耀X40 120Hz OLED硬核曲屏 5100mAh 快充大电池 7.9mm轻薄设计");
```

```
select * from phone where phone_name like "%OPPO%";
```

```
select * from phone where phone_name like "%OPPO%" and phone_name like "%8G%";
```

```
select * from phone where phone_name like "%OPPO%" or phone_name like "%120Hz%";
```

### show variables

```
show variables;
```

```
show variables like "default_storage_engine";
```

```
show variables like "%engine%";
```

## show variables

查看当前 MySQL Server 内置所有变量以及变量的值

### default_storage_engine

默认存储引擎: MyISAM

## 引擎

不同的引擎数据存储、查询的方式不同

产看当前的 MySQL 版本支持的引擎

```
show engines;
```

```
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
```

- InnoDB 支持事务、行锁、外键
- MyISAM 表锁、不支持事务
- MEMORY 基于Hash结构、存储在内存中、适用于临时表

### 表引擎

引擎配置在数据表中

查看表的引擎

方法1：查看建表语句

```
show create table phone \G;
```

方法2：查看表状态

```
show table status like 'phone' \G;
```

### 修改引擎

`alter table 表名 engine = 引擎名`

```
alter table phone engine = InnoDB;
```

### 创建表，指定引擎

```
create table engine_test_01 (
id int
) engine = InnoDB;
```

```
show table status like 'engine_test_01' \G;
```

```
create table engine_test_02 (
id int
) engine = MEMORY;
```

```
show table status like 'engine_test_02' \G;
```

```
create table engine_test_03 (
id int
) engine = innodb;
```

```
show table status like 'engine_test_03' \G;
```

## 索引

常见的索引

- 主键 primary key
- 唯一 unique
- 普通 index

1. 为什么要添加索引？

加快查询速度

2. 索引添加位置

索引添加给某个字段

3. 能够给所有的字段添加索引，来加快查询速度？

不能，有的字段不适合添加索引，甚至会降低查询速度

4. 什么样的字段适合添加索引

经常用与条件查询的字段、字段值取值范围要广(不重复的值比较多)

比如：

姓名适合添加索引
性别不适合添加索引，男、女

5. 索引速度

主键 >= 唯一 >= 普通索引

测试表

```
use gzsp;
```

```
create table student(
   id int not null auto_increment unique,
   student_num char(12) not null,
   student_name varchar(50) not null,
   student_birthday date not null
)engine = InnoDB;
```

### 主键

1. 一张表只能有一个主键
2. 主键通常用于数字自增非空字段

添加主键

`ALTER TABLE 表名 ADD PRIMARY KEY (字段名);`

```
ALTER TABLE student ADD PRIMARY KEY (id);
```

删除主键

`ALTER TABLE 表名 DROP PRIMARY KEY;`

```
ALTER TABLE student DROP PRIMARY KEY;
```

### 唯一索引

1. 百分百不重复的字段

```
ALTER TABLE student ADD UNIQUE INDEX(student_num);
```

```
ALTER TABLE student DROP INDEX student_num;
```

### 普通索引

```
ALTER TABLE student ADD INDEX(student_birthday);
```

```
ALTER TABLE student DROP INDEX student_birthday;
```

## 查看表的索引

`show indexes from 表名`

```
show indexes from student;
```

```
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES |
| student | 0 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
```

- Table 表名
- Key_name 索引的名字
- Column_name 字段名
- Index_type 索引类型（存储结构类型）