数据库.md · Admin/xr

[TOC]

# 数据库代码

[^]: 改字体颜色代码：内容</fort>>

打字狗（https://dazigo.vip/practice）

数据库：DATABASE
常见字符集：UTF8 GBK GB2312 GB18030

## 数据类型:

### INT类型：

占用4个字节，保存范围+-21亿

### 浮点数类型:

DOUBLE(M,N)或者DECIMAL(M,N)
				M是一个数字，表示数字的总位数
				N是一个数字，表示小数位的位数
				DOUBLE(7,2) 可以保存一个7位数，其中2为是小数，整数5位
				BIGINT类型：占用8个字节
				DOUBLE(7,2) 可以保存一个7位数，其中2为是小数，整数5位

### 定长字符串：

#### CHAR类型:

CHAR(m):是一个数字，表示字符串长度，单位是字符，m最大可取值255

### 变长字符串：

#### 				VARCHAR类型:

VARCHAR(m)：是一个数字，表示字符串长度，m最大可取值21844

### 文本类型：

#### 				TXET:

存储大文本数据【64KB】

#### 				LONGTEXT:

存储大文本数据【4GB】

### 日期类型：

#### 				DATE :

用于保存年月日 2000-01-01

#### 				TIME :

用于保存时分秒 00:00:00

#### 				DATETIME :

用于保存年月日时分秒 2000-01-01 00:00:00

####     			TIMESTAMP:

时间戳

描述:COMMENT ‘字符串’针对于当前字段做一个描述，可以添加也可以不添加

BETWEEN:在两值之间;  例:BETWEEN 2000 AND 5000;
NOT BETWEEN:不在两值之间;  例：NOT BETWEEN 2000 AND 5000;

IN:在集合中;  例：SELECT 字段名 FROM 表名 WHERE 字段名 IN(1,3,5,20,25);
NOT IN:不在集合中；例：SELECT 字段名 FROM 表名 WHERE 字段名 NOT IN(1,3,5,20,25);

IS NULL:为空;  例：SELECT 字段名 FROM 表名 WHERE 字段名 IS NULL;
IS NOT NULL:不为空；例：SELECT 字段名 FROM 表名 WHERE 字段名 IS NOT NULL;
IF NOT EXISTS:库不存在则创建，如果存在则不做任何操作

## 创建:

### 创建数据库：

CREATE DATABASE IF NOT EXISTS 库名 CHARSET=字符集;

### 使用库[切换库]：

USE 库名;

### 创建表：

//一定要先切换库，再创建表    INT 代表整形,CHAR(n)代表字符串类型,n 字符宽					度

```mysql
   		CREATE TABLE IF NOT EXISTS 表名
       	(
            指定字段名   数据类型
               id       INT,
               name    CHAR(n)
        )CHARSET =字符集
```

### 约束条件：

数据库三范式（六范式）

#### 主键约束：

非空且唯一

PRIMARY KEY

#### 自增长：

AUTO_INCREMENT

#### 非空约束：

NOT NULL

#### 唯一约束：

不能重复

UNIQUE

#### 默认值：

DEFAULT  '值'

## 添加:

#### 添加字段：

ALTER TABLE 表名 ADD 字段名 数据类型 位置;

#### 在表的开头增加一个字段：

ALTER TABLE 表名 ADD 字段名 数据类型 FIRST;

#### 在表的末尾增加一个字段：

ALTER TABLE 表名 ADD 字段名 数据类型;

#### 在表的某字段之后添加一个字段：

ALTER TABLE 表名 ADD 字段名 数据类AFTER 某字段;

#### 表中新增数据， 全列插入：

INSERT INTO 表名

VALUES(新增的数据)

#### 部分字段插入数据：

INSERT INTO 表名(字段名)

VALUES(新增的数据)

## DQL基础查询:

```mysql
									执行顺序
SELECT 集合函数，字段名，字段名				6	
FROM 表名								  1
JOIN... ON...子句						  2
WHERE 过滤条件							 3
GROUP BY 字段名						  4
HAVING 过滤分组条件						5
ORDER BY 字段名 排序方式				   7
LIMIT m,n							   8
```

#### 查看所有数据库：

SHOW DATABASES;

#### 查看创建库的语句[字符集]：

SHOW CREATE DATABASE 库名;

#### 查看当前库中的所有表：

SHOW TABLES;

#### 查看创建表的语句[字符集]：

SHOW CRATE TABLE 表名;

#### 查看表结构：

DESC 表名;

#### 查询表内某字段的信息：

SELECT * FROM 表名 WHERE 条件;

#### 查询表中所有字段，以及指定字段：

SELECT * FROM ;表名最好不要用*，把所有属性名都写出来
	 字段名 空格 重命名

FROM子句用于指定数据来自那张表

### WHERE子句：

在DQL语句中，WHERE同样用于指定过滤条件，此时仅将满足该过滤条件的记录查				询出来

#### 算术运算符：

## DQL高级查询

#### 模糊查询LIKE:

LIKE
	 					 _:
						 %:
			例：LIKE '%X%'  :表示字符串中包含字符X
	     		  LIKE '_X%'  ：表示字符串中包含第二个字符是X	
                   LIKE 'X%'  ：表示字符串以X开始         LIKE '%Y'  :表示字符串以Y结束 	
	               LIKE '%X_Y'  ：表示字符串倒数第三个字符数X并且最后一个字符是Y

#### AS 别名：

##### 给查询字段其别名：

方式1：使用AS关键字
	    		SELECT 字段名 AS 姓名，字段名 AS 薪资 RFOM 表名；
        		方式2：使用AS关键字
	    		SELECT 字段名 姓名，字段名 薪资 RFOM 表名；

##### 给查询的表起别名：

方式1：
	    		SELECT 表名.字段名，表名,字段名 FROM 表名 表名；
        		方式1：
				SELECT 表名(首字母).字段名，表名(首字母).字段名 FROM 表名 表名(首字母)；

#### ORDER BY 排序：

例：查看男老师的工资排名
				SELECT name,salary,gender FROM teacher
				WHERE gender='男'
				ORDER BY salary DESC;

#### 分页查询：

LIMIT
				LIMIT m,n	     m表示跳过的记录条数，你表示显示的记录条数
				LIMIT (页数-1)*每页记录条数，每页记录条数

## 聚合：

#### 函数：

| 方法          | 功能                 |
| ------------- | -------------------- |
| avg(字段名)   | 该字段的平均值       |
| max(字段名)   | 该字段的最大值       |
| min(字段名)   | 该字段的最小值       |
| sum(字段名)   | 该字段所有记录的和   |
| count(字段名) | 统计该字段记录的个数 |

注意

- MIN、MAX、SUM、AVG是对值的统计；而COUNT是对记录数的统计
- 聚合函数忽略NULL值；这个在AVG，COUNT上尤为明显

#### GROUP BY 分组：

在SELECT子句中出现聚合函数时，那么不在聚合函数中的字段都要出现在GROUP 				BY子句中。

GROUP BY子句是配合聚合函数使用的，如果SELECT子句中没有聚合函数，通常不				写GROUP BY。

```mysql
FROM 表名
WHERE 过滤条件
GROUP BY 字段
ORDER BY 字段
LIMIT m,n;
```

##### 单字段分组：

```mysql
SELECT AVG(salary),title
FROM teacher
GROUP BY title;
```

##### 多字段分组：

**GROUP BY**后面指定多个字段时，分组方式为：这些字段值都一样的记录看作一组

##### 分组聚合排序：

```mysql
SELECT AVG(salary) avg_sal,subject_id
FROM teacher
GROUP BY subject_id
ORDER BY avg_sal DESC;
```

#### DISTINCT去重

DISTINCT可以将结果集按照指定的字段去除重复行；

DISTINCT必须紧跟在SELECT关键字之后。

```mysql
SELECT DISTINCT title
FROM teacher;
```

#### HAVING子句：

HAVING子句用于分组中的过滤条件

```mysql
SELECT AVG(salary),subject_id
FROM teacher
WHERE AVG(salary)>6000
GROUP BY subject_id;
```

**聚合函数不能在WHERE子句中使用**

##### **HAVING**子句应用

HAVING子句是紧跟在GOURP BY子句之后，**用于对分组进行过滤的子句**。

##### **HAVING**和**WHERE**的区别

WHERE是在**第一次检索表数据时**用于添加过滤条件，**确定结果集**

HAVING是在**GROUP BY之后**(将结果集分组之后)添加过滤条件的，**用于确定分组**。

```mysql
								子句执行顺序
SELECT AVG(salary),subject_id		# 4	符合要求的分组统计对应信息
FROM teacher						# 1	数据来源，数据从teacher表查询
GROUP BY subject_id					# 2 确定分组，按科目分组(比如6组)
HAVING AVG(salary)>6000;			# 3 过滤分组，比如只有3组符合要求
```

### 子查询：

嵌套在一个SQL语句中的DQL语句，该DQL被称为子查询

- DQL中使用子查询
  - 在SELECT子句中，将当前子查询结果作为一个字段展示
  - 在WHERE子句中，将当前子查询结果作为过滤条件使用(最常用的场景)
- DML中使用:将一个查询结果集用于增删改操作

#### 单行单列查询：

查询的结果集只有一个值

在数据库中，被嵌套的DQL需要使用()括起来

```mysql
SELECT name,salary 
  FROM teacher 
  WHERE salary>(SELECT salary FROM teacher WHERE name='范传奇');
```

#### 多行单列查询：

多行单列子查询是可以检索出若干个值。因此作为过滤条件使用时，一般配合:**IN,NOT IN**使用。

因为等于是不能同时等于好几个值的，只能同于其中之一

查看与"祝雷"和"李费水"在同一个班的学生都有谁?

```sql
例：查看与"祝雷"和"李费水"在同一个班的学生都有谁?
1:未知条件:"祝雷"和"李费水"的班级号是多少
SELECT class_id FROM student WHERE name IN('祝雷','李费水');

2:查看与他们班级号相同的学生
SELECT name,class_id
FROM student
WHERE class_id=(SELECT class_id FROM student WHERE name IN('祝雷','李费水'))
```

### 关联查询：

联合多张表查询数据，查询结果集中的字段来自与多张表；

**表与表中的记录会产生对应关系**，用于我们联合查询。

#### 关联关系分类：

- 一对一

A表的一条记录仅唯一对应B表中的一条记录，反过来B表中的一条记录也仅唯一对应A表中的一条记录

- 一对多

A表中的一条记录对应B表中的多条记录，但是反过来B表中的一条记录仅能唯一对应A表中的一条记录

- 多对多

A表中的一条记录能够对应B表中的多条记录；

同时B表中的一条记录也能对应A表中的多条记录

#### 关于链接的条件：

在DQL中我们会指定连接条件，用来让数据库在查询中知道两张表中记录与记录的对应关系，从而查询出对应的记录。

关联查询中连接条件通常不可以忽略或缺失，否则会产生**笛卡尔积**

#### 语法：

```sql
  SELECT 字段,...
  FROM 表A,表B[,表C,...]
  WHERE A表与B表的连接条件
  AND   [其他表的连接条件]
  AND   过滤条件
  
  注意:连接条件要同时满足，且如果还有过滤条件时，也要与过滤条件同时满足
  ```

##### **笛卡尔积的产生**：

**当不指定连接条件时，数据库在进行关联查询时，仍然会用A表一条记录与B表每条记录连接一次，并产生结果集中的一条记录；此时的数据量为A表记录数与B表记录数的乘积。**

**当表中数据量大时，这样的结果集开销巨大，甚至可能导致服务器宕机，因此要尽量避免。**

#### N张表关联：

N张表关联查询，至少要有N-1个连接条件

```sql
例：查看"范传奇"所带班级的学生都有谁?要列出:学生名字，年龄，班级名称，老师名字
1:数据来自哪些表
  student s,class c,teacher t
  
2:连接条件
  3张表关联至少要有2个连接条件
  班级表与学生表的关系:c.id=s.class_id
  班级表与老师表的关系:c.teacher_id=t.id
  
3:过滤条件  
  老师的名字"范传奇"
  
  SELECT s.name,s.age,c.name,t.name
  FROM student s,class c,teacher t
  WHERE s.class_id=c.id
  AND c.teacher_id=t.id
  AND t.name='范传奇';
```

## 修改:

#### 修改表字段：

ALTER TABLE 表名 MODIFY 字段名 新的数据类型;

#### 修改表记录：

UPDATE 表名 SET 字段名=新的数据1，字段名=新的数据2 WHERE 条件

## 删除:

#### 删除库：

DROP DATABASE IF EXISTS 库名;

#### 删除表：

DROP TABLE IF EXISTS 表名;

#### 删除字段：

ALTER TABLE 表名 DROP 字段名;

#### 删除表记录：

DELETE FROM 表名 WHERE 条件;

Admin/xr

简介

发行版

贡献者

近期动态

Admin/xr .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

Admin/xr