sql怎么读

关系模型和SQL

⽂章⽬录

⼀、数据库的发展：

萌芽期：⽂件管理

第⼀代：层次数据库、⽹络数据库

第⼆代：SQL、关系型数据库

第三代：⾯向对象的DBMS(OODBMS)、对象关系的DBMS(ORDBMS)

⼆、关系模型和SQL

安装

rvicerestartmariadb,重启mariadb服务

yuminstallmariadb-rver,安装

e,启动

ss-tanl,查看端⼝号

e,开机启动

mysql_cure_installation,安全设置

mysql-uroot-p<,导⼊测试脚本

mysql-uroot-p,数据库密码登录

创建⽤户并授权

‘ur’@’%’identifiedby‘password’;

授权所有的权限(all),所有库的所有表(.),给(to)ur⽤户,@所有地址上(’%’)identified‘password’

flushprivileges;刷新权限,⼀定记住,省去不必要的⿇烦

mysql>lecthost,ur,passwordfromur;查看授权信息

mysql>lectur();查看当前登录的⽤户

mysql>lectdataba();查看当前数据库

三、SQL语句:

结构化查询语⾔StructuredQueryLanguage

⼀、SQL分为：

分类含义

①DDL数据定义语⾔负责数据库定义、数据库对象定义。由create，alter，drop

②DML数据操作语⾔负责绝对数据库对象的操作，CRUD增删改查

③DCL数据控制语⾔负责数据库权限访问控制，grant和revoke两个指令组成

④TCL事务控制语⾔负责处理acid事物，⽀持commit、rollback指令

控制语⾔

①授权grant

grantallonemployees.*to‘ur’@’%’identifiedbypassword;

②撤销revoke

er;

数据定义语⾔

删除⽤户

dropurammmap删除⽤户(慎⽤!删除就删除了)

删除数据库

dropdatabaifexistsdb_name;删除数据库,`不存在则警告

创建数据库

creatdatabaifnotexisttestcharactertutf8mb4collate

utf8mb4_general_ci;

creatdatabaifnotexisttestcharactertutf8mb4;

关键字含义

character指定字符集

collate指定字符集的校对规则,⽤来做字符串的⽐较的,例如a,A谁⼤?

创建⽤户

creaturdev@ipidentifiedby'password’

创建表

mysql是⾏存数据库,数据是⼀⾏⾏存储的,列必须固定有多少列

关键字含义

⾏,row也称为record记录,元组

基数元组、⾏的个数

列,column也称字段field,属性

维数指的是关系中属性的个数,也就是列的个数

域字段的取值范围是domain

操作含义

DESCtable_name查看表的设置信息

DESCtable_name'column_name’查看表中的某⼀列的设置信息

候选键:

关系中,能唯⼀标识⼀条元组的属性或属性集合,称为候选键

习惯性的多会多加⼀个id的候选键

候选键是⼀个属性或者属性集合,这个集合下⾯的数据都不重复

主键(primarykey):

表中⼀列或者多列组成key,也就是通过这⼀个或者多个列能唯⼀的表⽰的⼀条记录。也就是被选择的候选键

主键的列不包含空值null。主键往往设置为整型、长整型，可以⾃增auto_increament字段

表中可以没有主键,但是⼀般表设计中,往往都会有主键,以避免记录重复。

外键(foreignkey):

当⼀个关系中的某个属性或属性集合与另⼀个关系(也可以是⾃⾝)的候选键匹配时,就成这个属性或者这个属性集合为外键

索引index

为了快速检索⽤的。空间换时间,显著提⾼查询效率

可以对⼀列或者多列字段设定索引

索引含义

主键索引主键会⾃动建⽴主键索引，主键本⾝就是为了快速定位唯⼀记录的

唯⼀索引表中的索引列组成的索引必须唯⼀，可以为空,⾮空必须唯⼀

普通索引没有唯⼀性的要求，就是键了⼀个字典的⽬录⽽已

在mysql中,innoDB和nyisam的索引数据结构可以使⽤hash和Btree,默认使⽤btree

索引可以提⾼查询所读,却影响增删改的效率。

约束

为了保证数据库的完整性，数据模型还必须⽀持完整性约束。

约束含义

域约束限定了表中字段的取值范围

实体完整性primaykey约束定义了主键,就定义了主键约束

引⽤完整性(外键约束)外键定义中,可以不是引⽤另⼀个张表的主键,但是往往实际只会关注引⽤主键。

外键对于增删改的规则要求：

规则含义

插⼊规则不需要指定。外键列插⼊的值必须是表a中存在的主键值

更新规则定义外键约束时指定规则即可

删除规则定义外键约束时指定规则即可

定义外键数据的操作：

级别含义

CASCADE级联，从⽗表删除或更新⾏,会⾃动删除字表中匹配的⾏

SETNULL从⽗表删除或更新⾏,会设置字表中对应的外键为Null,

RESTRICT如果⽗表删除主键,如果字表引⽤了,则拒绝⽗表的删除和更新

NOACTION拒绝对⽗表的删除和更新操作

外键约束,是为了保证数据完整性、⼀致性、杜绝数据冗余、数据错误

实体联系E-R

数据库建⽴需要收集⽤户需求，设计符合企业要求的数据模型，⽽构建这种

模型需要⽅法，这种⽅法需要称为E-R实体-联系建模，也出现了⼀种建模语⾔

UML–统⼀建模语⾔

实体间联系的类型：

关系解释

⼀对多⼀个部门多个员⼯。员⼯外键，部门主键

多对多多个部门多个员⼯。建⽴第三张表，联合主键

⼀对⼀⼀个部门,⼀个员⼯

视图:

也称为虚表,看起来像表。

视图的作⽤：

简化操作，将复杂查询SQL语句定义为视图，可以简化查询

数据安全，视图可以只显⽰真实表的部分列，或计算后的结果，隐藏真实表

的数据

数据类型:

数据类型含义

tinyint1字节,带符号的范围是-128到127,⽆符号范围是0-255

bool或者boolean就是tinyint,0表⽰假,1表⽰真

smallint2字节,带符号的范围是-32768-32767,⽆符号是0-65535

int整型,四字节,和ingeter类型相同

bigint长整型,8字节,⽆符号最⼤2**32

float单精度浮点数精确到⼤约7位⼩数

double双精度浮点数精确到⼤约15位⼩数

Data⽇期,⽀持1000-01-01到9999-12-31

DATATIME⽇期,⽀持1010-01-0100:00:00到9999-12-3113:59:59

TIMESTAMP时间戳,范围是1970-2037

char固定长度,右边填充空格已达到长度要求,范围0-255

varchar变长字符串,M表⽰最⼤列长度。

text⼤⽂本,最⼤长度为65535个字符

BLOB⼤字节,最⼤长度65535字节的blob列

关系操作:

关系就是⼆维表,关系操作就是对表的操作

选择(lection):从关系中选择出满⾜给定条件的元组(⾏)

投影(projection):选出若⼲属性组成新的关系(表)

连接(join):将不同的两个关系连接成⼀个关系

数据操作语⾔

①inrt增加:注意缺省值和⾃动递增的主键啥的

inrtintotab_name(col_name)values(values);增加⼀⾏内容

inrtintotab_name(col_name)values(values)onduplicatekeyupdatecol_name=value;如果主键或者伪意见冲突就执⾏update后边的内容

inrtignoreintotab_name(col_name)values(values);忽视警告

②update更新:

updatetab_nametcol_name=valuewherekey=?

更新⼀定要加条件,千万注意

③delete删除:

deletetab_namewherecondition

删除⼀定要加条件

④lect查找:

⾸先查询⼀个库中有⼏张表

lectcount(*)tables,table_schemafrominformation_wheretable_schema=‘test’

showtables;查看当前库中的所有表;

WHERE字句:

=、<>、<、>、<=、>=、

whereidbetweenaandb注意是闭区间

like是模糊匹配

whereidin(10016,10001,10002)

and、or：

语句含义

orderby字句可以选择多个参考进⾏排序dest降序

groupby注意分组和不分组的聚合情况

having字句⽤来过滤分组、聚合过的结果

distinct不返回重复记录

聚合函数

语句含义

count返回记录中记录的数⽬,如果指定列,则返回⾮null值的⾏数

count(distinctexpr)返回不重复的⾮null值的⾏数

avg([distict]expr)返回平均值或者不同值的平均值

min(expr),max(expr)返回最⼩值最⼤值

⼦查询(嵌套查询)

查询语句可以嵌套,内部查询就是⼦查询

⼦查询必须在⼀组⼩括号内

⼦查询不能使⽤orderby

⼦查询,如果from的是⼀个查询的得到的结果,注意使⽤asalias别名

这样lect之后就可以指定alifrom查询了,还有起别名是个好习惯

连接john:

连接含义

交叉连接crossjoin(可以省略)lect*fromacrossjoinb;

内连接innerjoin(可以省略为john)lect*fromajoinb;

等值连接onlect*fromajoinbon=;

⾃然连接,naturaljoin,是特殊的等值连接lect*fromanaturalb;

左连接leftjoin,配合on使⽤lect*fromaleftjoinbon=;

右连接rightjoin,配合on使⽤lect*fromarightjoinbon=;

连接含义

总结

内连接:获取两个表中字段匹配关系的记录

左连接:获取左表所有记录,即使右表没有对应匹配的记录

右连接:获取右表所有记录,即使坐标没有对应匹配的记录

⾃连接:⾃⼰和⾃⼰连接

正则表达式

lect*fromtestwherenameREGEXP‘^A’;

使⽤REGEXP操作符进⾏正则表达式进⾏匹配。和python⼏乎通⽤

事务控制语⾔

t[ssion|global]transactionisolationlevel{readuncommitted|readcommitted|repeatableread|rializable}

lect@@_isolation查看隔离级别

lect@@tx_isolation查看隔离级别

事务即要做的⼀系列操作；

具有事务的数据库必须具有下⾯四个属性(acid):

属性解释

原⼦性atomicity⼀个事务是不可分割的⼯作范围,要么成功要么失败回退，不会结束在中间的某个环节。

⼀致性consistency事务开始之前和结束之后,数据库的完整性约束没被坏。

隔离性isolation数据库允许多个并发事务同时对数据读写和修改的能⼒。隔离性可以防⽌多个事务并发执⾏由于交叉执⾏⽽导致数据的不⼀致。

持久性durability事务处理结束后，对数据的修改是永久性的。

隔离性带来的问题:

1.更新丢失,已被数据库修复

2.脏读

3.不可重复读

4.幻读

隔离级别:

等级含义

read

uncommitted

读未提交,未解决脏读

read

committed

读以提交,解决脏读,Oricle的默认隔离等级。A和B,A修改的这条数据,只有A提交,B才能读到提交过的这条数据,⽆论B是否提交了

repeatable

read

可重复读,解决不可重复读。A和B,A⽆论是否修改提交,B读到的都是⼀样的数据,除⾮B提交之后再读。是快照读,⽽且A事务修改的数据也会

被上锁,其他事务不能修改这条记录，读也读不到当前的，只能读取到快照的数据。

rializable可串⾏化,所有问题全部解决。A事务结束再进⾏B事务

理解幻读和可重复读的区别：

幻读和不可重复读都是指的⼀个事务范围内的操作受到其他事务的影响了。

只不过幻读是重点在插⼊和删除，不可重复读重点在修改

锁机制可以避免不可重复读,因为当事务A准备处理⼀条数据时,可以将当前的原⼦操作进⾏上锁,这样这条数据别⼈都碰不到了,但是只是针对

的当前的这条或者多条数据,其他的数据并没有被上锁,也就意味着其他的数据还是可以被修改,插⼊,删除,所以也就此可以理解不可重复读读取

的是当前对上锁的这条或者多条数据

⽽幻读是读取的⼀条或者多条数据的上下⽂,不可以通过锁来控制,除⾮是⼀把类似gil的⼤锁,我操作,⽐⼈就不可以进⾏事务了,但是这样就是

串⾏,效率极低

不可重复读是针对的某⼀⾏或者某⼀个值,⼩范围的上锁就解决了，⾏锁

幻读针对的是⼀系列值,所以不能上锁,只能通过rializable隔离级别控制，表锁

不可重复读导致的问题是:不可重复读的读取到的信息都是别的事务已经提交过的,在避免脏读的基础上,我并没有做出改进,仅仅只是避免了脏

读。

可重复读在于保证了⼀个事务不会修改已经由另⼀个事务读取但未提交的数据

对于隔离等级的理解

1、Serializable(串⾏化)：最严格的级别，事务串⾏执⾏，资源消耗最⼤;

2、REPEATABLEREAD(重复读)：保证了⼀个事务不会修改已经由另⼀个事务读取但未提交(回滚)的数据,避免了“脏读取”和“不可重

复读取”的情况，但不能避免“幻读”，但是带来了更多的性能损失。

3、READCOMMITTED(提交读)：⼤多数主流数据库的默认事务等级，保证了⼀个事务不会读到另⼀个并⾏事务已修改但未提交的数据，

避免了“脏读取”，但不能避免“幻读”和“不可重复读取”。该级别适⽤于⼤多数系统。

4、ReadUncommitted(未提交读)：

事务中的修改，即使没有提交，其他事务也可以看得到，会导致“脏读”、“幻读”和“不可重复读取”。

在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数

据！这在⼀些对于数据的时效特别敏感的业务中，就很可能出问题。

⾏锁防⽌别的事务修改或删除，GAP锁防⽌别的事务新增，⾏锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读

问题。

最后对于隔离性的理解：

1.更新丢失,

2.脏读,⼀个事务读取到另外⼀个事务为提交的数据

3.不可重复读,就是前后两次读取的单条记录不⼀样

4.幻读,前后读取的数据集不⼀样

对于四个等级的理解:

ru:解决了更新丢失

rc:解决了脏读,只读取到了以提交的那次修改。因为⼀个事务操作⼀条数据时,会对这条数据上锁,⾏级锁,单条记录别⼈只能读取这条记录被上

锁前的数据和已提交的这个记录,所以还会出现不可重复读。⽽且其他数据由于没上锁的缘故，⽐的事务都可以操作提交，所以可能出现幻

读。

rr:解决了脏读,因为如果当前事务不提交,读取的另外⼀个事务操作的那条记录永远都是上锁前的快照读,那个事务操作外的其他记录因为没上

锁,可能别的其他的事务操作了,依然是可以读取到的,所以仍然可能出现幻读的现象。

s：实现了纯粹的串⾏，⼀个⼀个事务排着队等待执⾏，表级锁

对于mysql，tautocommit=1的话,每次操作完⼀条语句都会⾃动提交,可以启两个会话，tsutocommit=1,然后设置ttransaction

isolationlevelreadcommit,通过修改提交⼀个row,理解不同的隔离等级。

常见数据库的事务隔离

数据库默认隔离等级

mysql可重复读repeatableread

oracle读提交readcommitted

sqlrver读提交readcommitted

DB2读提交readcommitted

postgresql:读提交readcommitted

数据库和数据仓库的区别:

OLTP:联机事务处理on-linetransactionprocessing

OLAP:联机分析处理on-lineanalyticalprocessing

分别对应⾏存储和列存储