SQL总结

最新推荐文章于 2024-06-18 17:56:11 发布

TJmer

最新推荐文章于 2024-06-18 17:56:11 发布

阅读量282

点赞数

分类专栏：笔记文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/qq_41266147/article/details/111368531

版权

笔记专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、SQL定义

SQL，指结构化查询语言，全称是 Structured Query Language。
SQL 让您可以访问和处理数据库。
SQL 是一种 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言。

三、SQL语法

SQL = DDL+DML+DCL+TCL (数据定义语言+数据管理语言+数据控制语言+事务控制语言)
DDL: Data Definition Language
DML: Data Manipulation Language
DCL: Data Control Language
TCL: Transaction Control Language

（一）DDL包含（CREATE、ALTER 、DROP）

数据控制语言（DCL）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、REVOKE 、DENY等语句，在默认状态下，只有sysadmin、dbcreator、db_owner或db_securityadmin等角色的成员才有权利执行数据控制语言。

CREATE

（1）CREATE SCHEMA or CREATE DATABASE 建数据库
（2）CREATE TABLE 建数据表
（3）CREATE VIEW 建视图
（4）CREATE INDEX 建立索引

ALTER

（1） ALTER TABLE

DROP

（1）SQL DROP TABLE 语句

DROP TABLE 语句用于删除表（表的结构、属性以及索引也会被删除）：

DROP TABLE 表名称

（2）SQL DROP DATABASE 语句

DROP DATABASE 语句用于删除数据库：

DROP DATABASE 数据库名称

（3）SQL TRUNCATE TABLE 语句

如果我们仅仅需要除去表内的数据，但并不删除表本身，那么我们该如何做呢？
请使用 TRUNCATE TABLE 命令（仅仅删除表格中的数据）：

TRUNCATE TABLE 表名称

（二）DML （包含SELECT、INSERT、UPDATE、DELETE）

数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句，这些语句包括SELECT、INSERT、UPDATE、DELETE等。在默认情况下，只有sysadmin、dbcreator、db_owner或db_datawriter等角色的成员才有权利执行数据操纵语言。

SELECT

在这里插入图片描述

（2）GROUP BY 子句

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
需要根据一个或者多个列对结果集进行分组，然后列函数返回每一组的一个结果(比如返回最大值，最小值，平均值，求和等等)

（3）ORDER BY 子句

ORDER BY 关键字默认按照升序对记录进行排序。
降序： DESC
升序：ASC

（4）HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用
HAVING

（5）JOIN 子句

table1 INNER JOIN table2 ：返回 table1 和 table2 中符合 on 条件式的记录
table1 LEFT JOIN table: 返回 table1 中的所有记录和 table2中符合 on 条件式的记录
table1RIGHT JOIN table2：返回 tabel2 中的所有记录和 table1 中符合 on 条件式的记录

在这里插入图片描述（6）UNION 子句

SQL UNION 语法

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

注释：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

三、SQL约束

1、NOT NULL - 指示某列不能存储 NULL 值。

2、UNIQUE - 保证某列的每行必须有唯一的值。

3、PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

4、FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。外键

5、CHECK - 保证列中的值符合指定的条件。

6、DEFAULT - 规定没有给列赋值时的默认值。

CREATE TABLE Persons
(
    Id_P int NOT NULL UNIQUE PRIMARY KEY,   //PRIMARY KEY约束
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    Money int(13)
    FOREIGN KEY (Id_P) REFERENCES Works(W_Id),
    CHECK (Money >0)
)

四、SQL CREATE INDEX 语句

1. 语法

CREATE INDEX index_name 
ON table_name (column_name);

默认情况下，索引将允许重复的条目并按升序对条目进行排序

CREATE UNIQUE INDEX cust_name_idx 
ON customers (cust_name);

2. 有关join和索引遇到的坑

对于走索引的问题，假如A表有10行，B表有10000行。我们去join，一定是把行数最小的作为主表，然后去join行数多的，这样对于索引而言扫描的行数会少很多。
在join之后On的条件，类型不同是无法走索引的，也就是说如果on A.id = B.id，虽然A表和B表的id都设置了索引，但是A表的id是Int，而B表的id是varchar，则无法走索引。其次，字符编码也会导致无法走索引。
字符编码常见的是utf8和utf8mb4，utf8mb4是可以兼容utf8的，也就是说如果A表是utf8mb4，B表是utf8，则on A.uinstanceid = B. uinstanceid是可以走索引的，但是如果把B表当作主表，让B去join A on B.uinstanceid = A. uinstanceid则无法走索引。
如果我们A表是utf8，B表是utf8mb4，则必须拿B表作为主表去join才走索引，那么至少扫描的就是10000行，因此我们尽量让行数少的作为主表，这样扫描的行数就少很多。
可以通过explain sql来查看该sql中查询的表索引使用的字节数，特别是在联合嵌套、组合索引时，可以根据explain sql查看key_len看这条sql中哪些表用了多少索引。而explain sql中rows是该sql中查询表走的行数，就可以根据rows来判断为什么该sql跑的很慢的原因。最终这条sql走的条数是所有rows的乘积。
以上内容转自（https://blog.csdn.net/qq_22996201/article/details/97950839）

SQL 用于各种数据库的数据类型

mysql常用的几种

CHAR(size) 保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size) 保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TEXT 存放最大长度为 65,535 个字符的字符串。
MEDIUMBLOB 用于 BLOBs（Binary Large OBjects）。存放最多 16,777,215 字节的数据。
LONGTEXT 存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
DATE() 日期。格式：YYYY-MM-DD 注释：支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
DATETIME() 日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS注释：支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
TIME() 时间。格式：HH:MM:SS注释：支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR() 2 位或 4 位格式的年。注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069
- DECIMAL(size,d) 作为字符串存储的 DOUBLE 类型，允许固定的小数点。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。金融！！！

五、Mysql常见面试题

以下面试题的整理，是针对我个人的目前情况的，并且理解难度不大相对常见的一些问题的总结，想看比较全面的面试题，大家可以看看这位博主的，我也是跟着这位博主整理的一份，链接如下（Mysql知识点大全）

1. 数据库三大范式是什么

第一范式：每个列都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

2.MySQL存储引擎MyISAM与InnoDB区别

	MyISAM	InnoDB
锁支持	表级锁	行级锁和表级锁，默认为行级锁
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表
事务	不支持	事务类型的存储引擎
count(*)	MyISAM更有优势
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表

3.Mysql基于Innodb的索引类型

索引是一种数据结构，对一列或多列的值进行排序的一种结构
可快速访问数据库表中的特定信息，就像一本书的目录一样，可以加快查询速度。
InnoDB 存储引擎的索引模型底层实现数据结构为B+树，所有数据都是存储在 B+ 树中的

4.索引有哪些优缺点？

索引的优点

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。

5.索引的基本原理

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。

索引的原理很简单，就是把无序的数据变成有序的查询

把创建了索引的列的内容进行排序
对排序结果生成倒排表
在倒排表内容上拼上数据地址链
在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

6.MySQL事务有哪些特性

原子性（Atomicity）：事务作为一个整体被执行，要么全部执行，要么全部不执行；
一致性（Consistency）：保证数据库状态从一个一致状态转变为另一个一致状态；
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行；
持久性（Durability）：一个事务一旦提交，对数据库的修改应该永久保存

7.不做控制，事务并发出现的问题

丢失更新：

两个事务并发更新数据，其中一个会被覆盖

脏读：

事务A读了事务B未提交（出错进行回滚）的数据；

不可重复读：

由于另外一个事务更新了数据，导致事务前后读取的数据不一致

幻读：

事务A读的时候读出了N条记录，事务B在事务A执行的过程中增加了1条，事务A再读的时候就变成了N+1条，这种情况就叫做幻读。

8.MySQL数据库事务的隔离级别有哪些

读未提交(Read Uncommitted)：

允许脏读取。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。

读已提交(Read Committed)：

允许不可重复读取，但不允许脏读取。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

可重复读(Repeatable Read)：

禁止不可重复读取和脏读取，但是有时可能出现幻读。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。

序列化(Serializable)：

提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。

9.数据库的乐观锁和悲观锁是什么？怎么实现的？

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：一般会使用版本号机制或CAS算法实现。

两种锁的使用场景：

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

六、Mybatis面试题

七、Spring面试题

TJmer

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL总结

一、SQL定义SQL，指结构化查询语言，全称是 Structured Query Language。SQL 让您可以访问和处理数据库。SQL 是一种 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言。三、SQL语法SQL = DDL+DML+DCL+TCL (数据定义语言+数据管理语言+数据控制语言+事务控制语言)DDL: Data Definition LanguageDML: Data Manipulatio
复制链接

扫一扫

专栏目录