SQL总结

一、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、INSERTUPDATEDELETE

数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句,这些语句包括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区别

MyISAMInnoDB
锁支持表级锁行级锁和表级锁,默认为行级锁
索引的实现方式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面试题

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 点我我会动 设计师:白松林 返回首页