网站 › 『编程语言讨论求助区』 › MySQL day 1 (基础查询)

kkkkkkkkn 发表于 2023-10-15 09:41

MySQL day 1 (基础查询)

## 数据模型
### 关系型数据库
#### 定义
关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能相互连接的**二维表**组成的数据库
`.frm`为表文件
`.MYD`为数据文件
#### 优点
- 都是使用表结构，格式一致，易于维护
- 使用通用的SQL语言操作，使用方便，可用于复杂查询
- 数据存储在磁盘中，安全(存储在内存中并不安全)
## SQL简介
- 英文名：Structured Query Lanuage
- 结构化查询语言，一门操作关系型数据库的编程语言
- 定义操作所有关系型数据库的统一标准
- 对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”

## SQL语法
1.SQL语句可以单行或多行书写，**以分号结尾**。

2.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

3.注释
直接`ctrl`+`/`即可
- 单行注释：`--`或`#`(MySQL特有)
- 多行注释：`/* 注释 */`

## SQL分类
- DDL(Data Definition Language)数据定义语言，用来定义数据库对象：数据库，表，列等
- DML(Data Manipulation Language)数据操作语言，用来对数据库中表的数据进行增删改
- DQL(Data Query Language)数据查询语言，用来查询数据库中表的记录(数据)
- DCL(Data Control Language)数据控制语言，用来定义数据库 的访问权限和安全级别，及创建用户

### DDL
#### 操作数据库
1.查询
```SQl
SHOW DATABASES
```
结果如下
```SQL
mysql> show Databases;
+--------------------+
| Database         |
+--------------------+
| information_schema |
| k                  |
| mysql            |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.02 sec)
```
`k`是我自己创建的表
`information_schema`是一种特殊的表，叫做**视图**，是逻辑表，所以在文件夹中找不到这个文件
`mysql`存储的是数据库最为核心的一些信息，权限，安全等
`performance_schema`存储的是性能相关的信息
`sys`存储的是关于系统的一些的信息

2创建数据库
```SQL
create database db1;
```

```SQL
create database if not exists db1;
```
两者等价但第二个有判断条件

3.删除数据库
```SQL
drop database db1;
```

```SQL
drop database if exists db1;
```
两者等价但第二个有判断条件

4.使用数据库
- 查看当前使用的数据库
```SQL
SELECT DATABASE();
```
- 使用数据库
```SQL
USE db1;
```

#### 操作表
1.查询表
- 查询当前数据库下所有表名称
```SQL
SHOW TABLES;
```
- 查询表结构(不是内容)
```SQL
DESC 表名称;
```
查询结构的结果
```SQL

+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name| char(64)                     | NO   | PRI |         |       |
| ret   | tinyint                      | NO   |   | 0       |       |
| dl    | char(128)                  | NO   |   |         |       |
| type| enum('function','aggregate') | NO   |   | NULL    |       |
+-------+------------------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
```

2.创建表
```SQL
CREATE TABLE 表名 (
                字段1 数据类型1，
                字段2 数据类型2，
                ···
                字段n 数据类型n
);
```
最后一行末尾不能加`逗号`

##### 数据类型

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

---

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

|类型|大小|范围（有符号）|范围（无符号）|用途|
|---|---|---|---|---|
|TINYINT|1 Bytes|(-128，127)|(0，255)|小整数值|
|SMALLINT|2 Bytes|(-32 768，32 767)|(0，65 535)|大整数值|
|MEDIUMINT|3 Bytes|(-8 388 608，8 388 607)|(0，16 777 215)|大整数值|
|INT或INTEGER|4 Bytes|(-2 147 483 648，2 147 483 647)|(0，4 294 967 295)|大整数值|
|BIGINT|8 Bytes|(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)|(0，18 446 744 073 709 551 615)|极大整数值|
|FLOAT|4 Bytes|(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)|0，(1.175 494 351 E-38，3.402 823 466 E+38)|单精度<br>浮点数值|
|DOUBLE|8 Bytes|(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)|0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)|双精度<br>浮点数值|
|DECIMAL|对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2|依赖于M和D的值|依赖于M和D的值|小数值|
- 注意
`字段名 double(总长度，小数点后保留的位数)`
比如定义0到100，小数点后保留两位`score double(5,2)`

---

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

|类型|大小<br>( bytes)|范围|格式|用途|
|---|---|---|---|---|
|DATE|3|1000-01-01/9999-12-31|YYYY-MM-DD|日期值|
|TIME|3|'-838:59:59'/'838:59:59'|HH:MM:SS|时间值或持续时间|
|YEAR|1|1901/2155|YYYY|年份值|
|DATETIME|8|'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'|YYYY-MM-DD hh:mm:ss|混合日期和时间值|
|TIMESTAMP|4|'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC<br><br>结束时间是第 **2147483647** 秒，北京时间 **2038-1-19 11:14:07**，格林尼治时间 2038年1月19日 凌晨 03:14:07|YYYY-MM-DD hh:mm:ss|混合日期和时间值，时间戳|


---

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

|类型|大小|用途|
|---|---|---|
|CHAR|0-255 bytes|定长字符串|
|VARCHAR|0-65535 bytes|变长字符串|
|TINYBLOB|0-255 bytes|不超过 255 个字符的二进制字符串|
|TINYTEXT|0-255 bytes|短文本字符串|
|BLOB|0-65 535 bytes|二进制形式的长文本数据|
|TEXT|0-65 535 bytes|长文本数据|
|MEDIUMBLOB|0-16 777 215 bytes|二进制形式的中等长度文本数据|
|MEDIUMTEXT|0-16 777 215 bytes|中等长度文本数据|
|LONGBLOB|0-4 294 967 295 bytes|二进制形式的极大文本数据|
|LONGTEXT|0-4 294 967 295 bytes|极大文本数据|

**注意**：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

VARCHAR是一种数据类型，比CHAR更加灵活，VARCHAR可以保存可变长度的字符串，而CHAR不能。就是说char(10)所占的空间就是10个字符，而varchar(10)假如存储了两个字符，存储空间就是两个字符。VARCHAR和CHAR同样用于表示字符数据。其中M代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。所以，对于那些难以估计确切长度的数据对象来说，使用VARCHAR数据类型更加明智。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。


3.修改表
- 修改表名(TABLE不能忘)
```SQL
ALTER TABLE 表名 RENAME TO 新的表名;
```

- 添加一列
```SQL
ALTER TABLE 表名 ADD 列名 数据类型;
```
- 修改数据类型
```SQL
ALTER TABLE 表名 MODIFY 列名 新数据类型;
```
- 修改列和数据类型
```SQL
ALTER TABLE 表名 CHANGE 列名 新数据类型;
```
- 删除列
```SQL
ALTER TABLE 表名 DROP 列名 ;
```
4.删除表

```SQL
DROP TABLE 表名;
```

```SQL
DROP TABLE IF EXISTS 表名;
```

### DML
#### 添加数据(insert)
```SQL
-- 查询所有数据
SELECT * FROM r;

-- 给指定列添加数据
INSERT INTO r(name,num) VALUES('张三',123);

-- 给所有列添加数据
INSERT INTO r(num,name,gender,birthday,score,email,tel,adrd) VALUES(123,'张三','男','2022-1-3',99,'djflks.cn@','123213232','曹县');

-- 省略写法但不建议
INSERT INTO r VALUES(123,'张三','男','2022-1-3',99,'djflks.cn@','123213232','曹县');

-- 批量添加数据
INSERT INTO r(num,name,gender,birthday,score,email,tel,adrd) VALUES(123,'张三','男','2022-1-3',99,'djflks.cn@','123213232','曹县'),(123,'张三','男','2022-1-3',99,'djflks.cn@','123213232','曹县'),(123,'张三','男','2022-1-3',99,'djflks.cn@','123213232','曹县');

```

#### 修改数据(update)
```SQL
UPDATE 表名 SET 列名1=值1,列名2=值2,··· ;
```
- 注意：修改语句中如果不加条件，则将所有数据都修改

#### 删除数据
```SQL
DELETE FROM 表名 ;
```
- 注意：删除语句中如果不加条件，则将所有数据都删除


### DQL
#### 聚合函数
1.概念
将一列数据作为一个整体，进行纵向运算

2.分类

| 函数名 | 功能 |
|---------|---------|
| count(列名) | 统计数量(一般选用不为null的列,会少数) |
| max(列名) | 最大值 |
| min(列名) | 最小值 |
| sum(列名) | 求和 |
| avg(列名) | 平均值 |
注意： `count()`后面的值最好是主键(因为主键值不能为null)或`*`
3.语法
```SQL
SELECT 聚合函数名(列名) FROM 表;
```
注意：null值不参加所有聚合函数运算
查询语法
#### 基础查询
```SQL
-- 基础查询
SELECT * FROM students;
-- * 最好不用，不好加注释

-- 查询两列
SELECT `name` FROM students;

-- 去除重复记录
SELECT DISTINCT region from students;

-- 起别名 AS可以省略但要有空格
SELECT region AS 地区 , age AS 年龄 FROM students;
```
#### 条件查询(where)
```SQL
-- 查询大于17岁的学生
SELECT * FROM students WHERE age > 17;

-- 查询大于等于17岁的学生
SELECT * FROM students WHERE age >= 17;

-- 查询大于等于17岁且小于18岁的学生
SELECT name FROM students WHERE age >= 17 && age < 18;
SELECT name FROM students WHERE age >= 17 AND age < 18;
SELECT name FROM students WHERE age BETWEEN 20 AND 30;

-- 查询生日在2004年间的学生
SELECT * FROM students where birthday BETWEEN '2004-01-01'AND '2004-12-31';

-- 查询年龄不等于17岁的同学
SELECT name, age FROM students where age != 17;

SELECT name, age from students WHERE age <> 17;

-- 查询年龄等于18或17或16的学生
SELECT name,age from students where age = 17 or age = 18 or age = 16;

SELECT name,age from students where age = 17 || age = 18 || age = 16;
-- 查询地区为null的学生（比较null值只能用is或is not）
SELECT name,age from students where region is NULL;
SELECT name,age from students where region is not NULL;
```
#### 分组查询(group by)
1.语法
```SQL
SELECT 字段列表 FROM GROUP BY 分组字段名 ;
```
**注意： 分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义**
where和having的区别：
执行时机不一样：where是分组前进行限定，having是分组之后进行过滤
可判断的条件不一样：where不能对聚合函数进行判断，having可以。
执行顺序： where > 聚合函数 > having
```SQL
-- 查询男女各自的数学平均分，以及各自人数，要求分数低于80的不参与分组，分组之后人数大于2人
select avg(math),gender,count(*) from students where math> 80 group by gender having count(*)>2;
```

#### 排序查询(order by)
order by 如果和where同时使用，**order by必须在where 的后面**    `ASC`升序排列是默认值
SELECT 字段列表 FROM 表名 ORDER BY 排序字段1 [排序方式1]，排序字段2 [排序方式2]...;
```SQL
mysql> -- 按照年龄升序排列
mysql> select name,age from students order by age asc;
+------+------+
| name | age|
+------+------+
| 李四 |   15 |
| 赵六 |   15 |
| 王五 |   16 |
| 陈八 |   16 |
| 王五 |   16 |
| 陈八 |   16 |
| 孙七 |   17 |
| 李四 |   17 |
| 孙七 |   17 |
| 张三 |   18 |
| 张三 |   18 |
| 赵六 |   18 |
+------+------+
12 rows in set (0.00 sec)
mysql> -- 按照年龄降序排列
mysql> select name,age from students order by age desc;
+------+------+
| name | age|
+------+------+
| 张三 |   18 |
| 张三 |   18 |
| 赵六 |   18 |
| 孙七 |   17 |
| 李四 |   17 |
| 孙七 |   17 |
| 王五 |   16 |
| 陈八 |   16 |
| 王五 |   16 |
| 陈八 |   16 |
| 李四 |   15 |
| 赵六 |   15 |
+------+------+
12 rows in set (0.00 sec)

mysql> -- 根据年龄降序排列，如果年龄一样，按照英语成绩升序排列
mysql> -- 如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序
mysql> select name,age,english_score from students order by age desc, english_score asc;
+------+------+---------------+
| name | age| english_score |
+------+------+---------------+
| 张三 |   18 |         85.50 |
| 张三 |   18 |         85.50 |
| 赵六 |   18 |         91.20 |
| 孙七 |   17 |         83.40 |
| 孙七 |   17 |         83.40 |
| 李四 |   17 |         92.30 |
| 陈八 |   16 |         77.80 |
| 陈八 |   16 |         77.80 |
| 王五 |   16 |         78.90 |
| 王五 |   16 |         78.90 |
| 赵六 |   15 |         91.20 |
| 李四 |   15 |         92.30 |
+------+------+---------------+
12 rows in set (0.00 sec)
```
#### 分页查询(limit)
1.语法
```SQL
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数;
```
- 起始索引：从零开始
- 计算公式：起始索引=(当前页码 - 1) * 每页显示的条数
- 分页查询limit是MySQL数据库的方言
- Oracle 分页查询使用 rownumber
- SQL Server分页查询使用top

```SQL
mysql> select * from students limit 0, 3;
+----+------+------+--------+--------+-------+---------------+------------+
| id | name | age| gender | region | math| chinese_score | birthday   |
+----+------+------+--------+--------+-------+---------------+------------+
|1 | 张三 |   18 | 男   | 北京   | 85.50 |         79.20 | 2003-05-12 |
|2 | 李四 |   15 | 女   | NULL   | 80.00 |         88.70 | 2004-08-25 |
|3 | 王五 |   16 | 男   | 广州   | 78.90 |         81.40 | 2005-11-07 |
+----+------+------+--------+--------+-------+---------------+------------+
3 rows in set (0.00 sec)

mysql> select * from students limit 4, 3;
+----+------+------+--------+--------+-------+---------------+------------+
| id | name | age| gender | region | math| chinese_score | birthday   |
+----+------+------+--------+--------+-------+---------------+------------+
|5 | 孙七 |   17 | 男   | 山东   | 83.40 |         90.10 | 2004-06-08 |
|6 | 陈八 |   16 | 女   | NULL   | 77.80 |         84.70 | 2005-11-30 |
|7 | 张三 |   18 | 男   | 北京   | 85.50 |         79.20 | 2003-05-12 |
+----+------+------+--------+--------+-------+---------------+------------+
3 rows in set (0.00 sec)
```

兄弟们可以给个免费的评分吗{:301_987:}

HackYike 发表于 2023-10-15 10:18

真好，我也正在学习这个，用的Navicat

sii444555666777 发表于 2023-10-15 11:50

跟着楼主一起学习下

a3859495 发表于 2023-10-15 13:43

跟着楼主一起学习下

warmlamb 发表于 2023-10-16 09:19

kkkkkkkkn 发表于 2023-10-16 14:16

HackYike 发表于 2023-10-15 10:18
真好，我也正在学习这个，用的Navicat

Navicat挺好，就是要钱{:301_999:}

wqbd 发表于 2023-10-16 18:01

感谢楼主分享，写的很全面学习了

songmingjiu8888 发表于 2023-11-7 06:26

谢谢分享！学习学习！

查看完整版本: MySQL day 1 (基础查询)