MySQL day 1 (基础查询)

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

数据模型

关系型数据库

定义

关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能相互连接的二维表组成的数据库
.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.查询

SHOW DATABASES

结果如下

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创建数据库

create database db1;

create database if not exists db1;

两者等价但第二个有判断条件

3.删除数据库

drop database db1;

drop database if exists db1;

两者等价但第二个有判断条件

4.使用数据库

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

操作表

1.查询表

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

+-------+------------------------------+------+-----+---------+-------+
| 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不能忘)

ALTER TABLE 表名 RENAME TO 新的表名;

添加一列

ALTER TABLE 表名 ADD 列名 数据类型;

修改数据类型

ALTER TABLE 表名 MODIFY 列名 新数据类型;

修改列和数据类型

ALTER TABLE 表名 CHANGE 列名 新数据类型;

删除列
```
ALTER TABLE 表名 DROP 列名 ;
```
4.删除表

DROP TABLE 表名;

DROP TABLE IF EXISTS 表名;

DML

添加数据(insert)

-- 查询所有数据
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)

UPDATE 表名 SET 列名1=值1,列名2=值2,··· [WHERE 条件];

注意：修改语句中如果不加条件，则将所有数据都修改

删除数据

DELETE FROM 表名 [WHERE 条件];

注意：删除语句中如果不加条件，则将所有数据都删除

DQL

聚合函数

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

2.分类

函数名	功能
count(列名)	统计数量(一般选用不为null的列,会少数)
max(列名)	最大值
min(列名)	最小值
sum(列名)	求和
avg(列名)	平均值

注意： count()后面的值最好是主键(因为主键值不能为null)或*
3.语法

SELECT 聚合函数名(列名) FROM 表;

注意：null值不参加所有聚合函数运算
查询语法

基础查询

-- 基础查询
SELECT * FROM students;
-- * 最好不用，不好加注释

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

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

-- 起别名 AS可以省略但要有空格
SELECT region AS 地区 , age AS 年龄 FROM students;

条件查询(where)

-- 查询大于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.语法

SELECT 字段列表 FROM [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
where和having的区别：
执行时机不一样：where是分组前进行限定，having是分组之后进行过滤
可判断的条件不一样：where不能对聚合函数进行判断，having可以。
执行顺序： where > 聚合函数 > having

-- 查询男女各自的数学平均分，以及各自人数，要求分数低于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]...;

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.语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数;

起始索引：从零开始
计算公式：起始索引=(当前页码 - 1) * 每页显示的条数
分页查询limit是MySQL数据库的方言
Oracle 分页查询使用 rownumber
SQL Server分页查询使用top

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挺好，就是要钱

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

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

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

谢谢分享！学习学习！

帐号		自动登录	找回密码
密码			注册[Register]

[学习记录] MySQL day 1 (基础查询)