MySQL

2022-11-22 17:29:14 浏览数 (2)

1. MySQL

1. 数据库模型
  • 层次模型
  • 网络模型
  • 关系型数据库

目前主要使用关系型数据库

2. 关系型数据库

关系型数据库模型就是把复杂的数据结构归结于简单的二元关系（即二维表格形式）。在关系数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过这些关联表的表格分类，合并，连接或选取等运算来实现数据的管理。

SQL

SQL就是结构化查询语言

用于从数据库中有结构的查询

NoSQL（非关系型数据库）

NoSQL，泛指非关系型数据库

MySQL相关命令

代码语言：javascript复制
---登录
mysql -uroot -p
---查看表
show databases;
---创建数据库
CREATE DATABASE <数据库名称>;
---退出数据库
quit

2. CRUD

1. 表的结构
1.1 表名

一般使用英文小写字母来约定表，多个单词之间使用_分隔

1.2 主键
  • 主键是一个特殊字段
  • 表格可以没有主键，但最多只能拥有一个主键
  • 主键的值不能为NULL ，必须有对应的值
  • 主键的值必须绝对唯一的，即不能出现两个相同的主键值，如名字就不可以作为主键，因为会有重名
  • 一般使用主键和其他表进行关联
1.3 SQL常用数据类型

类型

含义

VARCHAR

可变的长字符串，可以类比于Java中的String类型

INT

整形，和Java中的int类型一致

DOUBLE

浮点型，和Java中的double类型一致，一般不加长度限制

DATETIME

时间类型，长度为0，格式为YYYY-MM-DD HH:MM:SS，值为2022-12-12 12:12:12

BIGINT

长整型，和Java中的long类型一致

1.4 CRUD

英文

中文

SQL

HTTP

CREATE

创建

INSERT(插入)

POST

READ

读取

SELECT(查询)

GET

UPDATE

更新

UPDATE

POST

DELETE

删除

DELETE

DELETE

2. 创建表格
2.1 创建表格格式
代码语言：javascript复制
create table table_name (    --- 创建表
    `id` INT(10) NOT NULL.   --- 表单头部格式
    `gmt_created` datetime,
    PRIMARY KEY (`id`)       --- 设置主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8 --- 设置存储引擎和编码方式
2.2 删除表格
代码语言：javascript复制
drop table table_name;
//或者
drop table if exists table_name;
3. 插入语句(INSERT)

语法

代码语言：javascript复制
INSERT TNTO table_name(field1, field2,...,fieldN)
VALUES
(value1,value2,...,valueN);


INSERT INTO
  `user` (`id`, `mobile`, `nickname`,`gmt_created`)
VALUES
   (1,'13426069530','XXX'，now());
// 插入语句简化
INSERT INTO table_name
VALUES
(value1,value2,...valueN);
// 批量插入数据
INSERT INTO table_name
VALUES
(value1,value2,...valueN),
(value1,value2,...valueN);
4. 查询（SELECT）

语法

代码语言：javascript复制
SELECT field1,field2... FROM table_name; --- 查询指定列的信息
// 
SELECT
	id,
	hero_name
FROM
	timi_adc

// 查询所有字段
SELECT * FROM timi_adc;
WHERE子句

语法

代码语言：javascript复制
SELECT * FROM table_name WHERE condition;

// 举例
SELECT 
   *
FROM
	table_name
where
	win_rate > 0.5;
5 Limit子句

语法

代码语言：javascript复制
SELECT * FROM table_name LIMIT parameter

查询x-y行

代码语言：javascript复制
SELECT
	*
FROM
	timi_adc
LIMIT
	5,6;
	
-- 表示从第6行开始查，查6条记录

0-x行z

代码语言：javascript复制
SELECT * FROM timi_adc LIMIT 5;

查询第x行

代码语言：javascript复制
SELECT * FROM timi_adc LIMIT 4，1;

和WHERE子句联合使用

代码语言：javascript复制
SELECT 
	* 
FROM 
	timi_adc
WHERE
	appearance_rate > 0.1
LIMIT
	5;
6 排序（ORFER BY子句）

语法

代码语言：javascript复制
SELECT * FROM table_name ORDER BY field_name;
代码语言：javascript复制
SELECT 
	* 
FROM
	timi_adc
ORDER BY	
	win_rate;
	
-- 排序默认按照升序排序，对于int double 从小到大，对于varchar 从字母A-Z，对于datetime,从过去到现在

DESC关键字

代码语言：javascript复制
SELECT
	*
FROM
	timi_adc
ORDER BY
	win_rate DESC
-- DESC使排序变为逆序

和其他子句连用

代码语言：javascript复制
Select
	*
from
	timi_adc
ORDER BY
	win_rate DESC
LIMIT
	3;
7 更新/删除

更新：

语法

代码语言：javascript复制
UPDATE 表名称 SET 列名称 = WHERE 列名称 = 某值

UPDATE语句必须加入WHERE限制条件，否则的话UPDATE 语句就会对整列起作用。

删除语句（DELECT）

语法

代码语言：javascript复制
DELETE FROM table_name [WHERE Clause]

删除user表中id为4的行：

代码语言：javascript复制
delete from user where id=4;

删除user表中所有id小于20的数据：

代码语言：javascript复制
delete from `user` where id < 20;

删除user表中的所有数据：

代码语言：javascript复制
delete from user;

3 查询优化

1 LIKE查询

语法

代码语言：javascript复制
SELECT * FROM table_name where condition LIKE condition;

%

SQL LIKE 子句中，% 字符来表示任意字符，如果没有使用任何字符LIKE 就相当于=

_

如果记不清全名，可以使用_来表示

2 AND%OR

语法

代码语言：javascript复制
SELECT * FROM tble_name WHERE condition AND/OR condtionB;

有时需要加上()来分割条件

要注意分清逻辑条件

3 IN/NOT IN

语法

代码语言：javascript复制
SELECT * FROM table_name WHERE colum IN (condtionA, condtionB);

NOT IN / NOT LIKE

代码语言：javascript复制
SELECT * FROM timi_adc WHERE NOT IN(condtion);
4 NIULL 值的处理

NULL 不是空值，空值不占空间，NULL占用存储空间

如何处理空值

  1. IS NULL: 当列的空值是NULL时，这个·运算符返回true
  2. IS NOT NULL: 当列的值不为NULL，这个运算符返回true
  3. <=>: 比较操作符 =，当比较的两个值都是为NULL时或者相等时，返回为true

4 MySQL处理字符串

1. CONCAT 函数

语法

代码语言：javascript复制
select colume_name1,COMCAT(column_name2,str,column_name3),column_name4 FROM table_name;
  • CONCAT函数可以拼接列名，也可以拼接字符串
  • 在使用CONCAT函数的时候可以同时查询其他的列
  • CONCAT函数的参数之间用英文,分隔

如果拼接的值有NULL，则结果一律为NULL，NULL本身特性决定

配合WHERE语句查询

代码语言：javascript复制
SELECT
	id,
	CONCAT(hero_name,'的胜率是',win_rate)
FROM
	timi_adc
WHERE
	id = 3;

别名

查询结果列名叫result

代码语言：javascript复制
SELECT
	id,
	CONCAT(hero_name,'的胜率是',win_rate) as result --- 例如
2. TRIM 函数

为了保证数据格式的正确性，我们会使用TRIM函数来清理数据

语法

代码语言：javascript复制
TRIM(str)

示例(⊙﹏⊙)

代码语言：javascript复制
SELECT
	TRIM(hero_name),
	TRIM(fever)
FROM
	timi_adc
WHERE
	id = 20;

语法拓展

trim()函数也可以精准去掉前面或者后面的空格，或者其他的字符

代码语言：javascript复制
TRIM( BOTH|LEADING|TRALING remove_str FROM str);
  • TRIM加上LEADING来去除前面的空格
  • TRAILING来只去除后面的空格
  • 如果都不加，默认删除空格

例子(⊙﹏⊙)

代码语言：javascript复制
SELECT 
	TRIM(
        LEADING ' '
        FROM
        	hero_name
    )
FROM
	timi_adc
WHERE
	id = 21;

REPLACE()函数

TRIM()函数不能去除字符串中间的值，使用REPLACE()函数可以

代码语言：javascript复制
UPDATE table_name
SET colunm_name = 
REPLACE(colunm_name,string_find,string_to_replace)
WHERE conditions;

5 MySQL关联查询

1 左连接

语法

代码语言：javascript复制
SELECT
	*
FROM
	TableA LEFT JOIN
	TanleB
	ON condition;
  • JOIN 是查询关键词，基础结构是TableA JOIN TableB ，即表A关联表B查询，LEFT表示左连接
  • ON 是关联查询的条件
  • 左连接就是返回左表的所有数据，即使右表没有匹配的数据（右表会以NULL的形式匹配数据）

举例(⊙﹏⊙)

代码语言：javascript复制
SELECT
	*
FROM
	teacher
	LEFT JOIN course ON teacher.id = course.teacher.id;
  • teacher 是A表，course是B表
  • teacher.id 是指这张表的id字段，course.teacher.id是指teacher_id这个字段

表结构设计规范

  1. 表必须有主键
  2. 一个字段只表示一个含义
  3. 总是包含两个日期：gmt_created(创建日期)，gmt_modified(修改日期),且这两个字段不应该包含额外的业务逻辑
  4. MySQL中，gmt_created,gmt_modified 使用DATATIME类型
  5. 禁止使用复杂数据类型（数组，自定义类型等）
  6. 禁止使用物理外键，使用逻辑外键
  7. 禁止物理删除，使用逻辑删除 is_deleted
  • 逻辑删除 又称软删除，假删除，是一种数据库操作，使用标记将数据不可用，而不从数据库删除数据本身
2 右连接

语法

代码语言：javascript复制
SELECT
	*
FROM
	TableA RIGHT JOIN
	TableB
	ON condition

多表关联

语法

代码语言：javascript复制
SELECT
	*
FROM
	TableA
	LEFT JOIN TableB ON conditionA
	LEFT JOIN TableC ON conditionB;
3 内连接

INNER连接

例如两个表TableA,TableB

查找A，B同时符合某一条件的数据

代码语言：javascript复制
SELECT
	*
FROM
	TableA
	INNER JOIN TableB
ON
	TableA.id = TableB.student_id;

外连接的拓展

指除了A，B重合部分外，如A部分或者B部分

代码语言：javascript复制
SELECT
	*
FROM
	TableA
	LEFT JOIN TableB ON TableA.id = TableB.student_id
WHERE
	TableB.student_id IS NULL;

UNION关键字

代码语言：javascript复制
SELECT
	*
FROM
	TableA
	LEFT JOIN TableB
ON
	TableA.id = TableB.student_id
UNION DISTINCT
SELECT
	*
FROM
	TableA
	RIGHT JOIN TableB 0N TableA.id = TableB.student_id;

相当于AB左右连接

UNION 关键字可以将两个查询语句的结果合并，并去除重复数据

笛卡尔积

代码语言：javascript复制
cross join

6. 补充

1. 查询结果去重
代码语言：javascript复制
select distinct ... from table_name
2. 总和
代码语言：javascript复制
cout(列明);
3. 计算平均值
代码语言：javascript复制
avg(列名)  --- 计算该列的平均值
round(avg(列名), 1) --- round指定保留小数位数
4.UNION 和 UNION ALL
代码语言：javascript复制
--- 我们需要将两个select语句的结果作为一个整体输出
--- UNION 会对两个结果取并集(也就是自动去除重复的结果)
--- UNION ALL 是将所有结果输出
5.字符串截取

substring_index(str,delim,count)

  • str:要处理的字符串
  • delim:分隔符
  • count:计数(如果为负数就表示从后面开始)

例如：str = www.liqur.xyz

​ substring_index(str,’.’,1) 结果就是www

6. 窗口函数

语法

代码语言：javascript复制
<窗口函数> over (partition by <用于分组的列名>
            order by <用于排序的列明>)

<窗口函数>的位置，可以放以下两种函数：

  1. 专用窗口函数，比如rank，dense_rank，row_number等
  2. 聚集函数，如sum，avg，count，max，min等

功能

  1. 同时具有分组和排序的功能
  2. 不减少原表的行数

注意

原则只能写在select子句中

nosql 云数据库SQLServer 数据库 sql java

0 人点赞