数据库

一:数据库

MySQL官网

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
一:数据库

概述:
1.定义:存储数据,管理数据的仓库。
2.分类:关系型数据库和非关系型数据库
关系型(传统数据库):即数据库之间有着紧密的关系,如父子关系,或采用了关系模型
(二维表格模型)来组织数据的数据库,
代表:MYSQL,Oracle,Oracle、MySQL、SQLServer、Access。
非关系型(nosql):是一种数据结构化存储方法的集合,主要用在对并发读写能力要求极高,
事物的一致性不太重要的地方如微博,facebook等地方
代表:MongoDB、Redis、Solr、ElasticSearch、Hive、HBase等。
3.MYSQL数据库:
1.服务端:用来处理具体的数据,维护和保存磁盘
2.客户端:主要操作是数据的增删改查(CRUD)
3.结构:数据库 -> 表(行和列) -> 数据
数据的存放位置可以在mysql的配置文件my.ini的datadir目录查看,端口号默认为3306 。:
1.定义:存储数据,管理数据的仓库。
2.分类:关系型数据库和非关系型数据库
关系型(传统数据库):即数据库之间有着紧密的关系,如父子关系,或采用了关系模型
(二维表格模型)来组织数据的数据库,
代表:MYSQL,Oracle,Oracle、MySQL、SQLServer、Access。
非关系型(nosql):是一种数据结构化存储方法的集合,主要用在对并发读写能力要求极高,
事物的一致性不太重要的地方如微博,facebook等地方
代表:MongoDB、Redis、Solr、ElasticSearch、Hive、HBase等。
3.MYSQL数据库:
1.服务端:用来处理具体的数据,维护和保存磁盘
2.客户端:主要操作是数据的增删改查(CRUD)
3.结构:数据库 -> 表(行和列) -> 数据
数据的存放位置可以在mysql的配置文件my.ini的datadir目录查看,端口号默认为3306 。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
二:数据表:
概述
定义:数据表是一种由标记的列和行组成的可视化工具,用于排列计算机数据库中包含的信息。
它可以用来组织不同的数据,也可以使数据易于操作和分析。数据表以网格格式显示信息。

表字段:
命名规则:字段名以字母开头,尽量不要使用拼音代替;长度不要超过30个字符(不同数据库版本长度限制可能会有不同);
不能使用SQL的保留字,如where,order,group等;只能使用字符a-z、A-Z、0-9、$ 等;
Oracle习惯全大写:USER1,mysql习惯全小写:user1;多个单词用下划线隔开。

字符:
char固定字符串长度,不足使用空格填充,最多容纳2000个字符,char(10)存储abc,占10位。
查询速度极快但浪费空间。
varchar:可变字符串长度,最多容纳4000个字符,varchar(10)存储abc,只占3位。
查询稍慢,但节省空间。Oracle为varchar2
大文本: 大量文字(不推荐使用,尽量使用varchar替代),在utf8编码中一个汉字占3个字节。

数字
tinyint,int整数类型
float,double小数类型
numeric(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
decimal和numeric表示精确的整数数字。

日期
date 包含年月日
time时分秒
datetime包含年月日和时分秒
timestamp时间戳,不是日期,而是从1970年1月1日到指定日期的毫秒数。

图片
blob 二进制数据,有4中类型,可以存放图片、声音,容量4g。早期有这样的设计。
但其缺点非常明显,数据库庞大,备份缓慢,这些内容去备份多份价值不大。
在数据库迁移时过大,迁移时间过久。所以目前主流都不会直接存储这样的数据,
而只存储其访问路径,文件则存放在磁盘上。

表字段约束(constraints); 五大约束

1.—-主键约束(Primay Key Coustraint)主键列的数据: 唯一性,非空性

2.—-唯一约束 (Unique Counstraint)唯一性,可以空,但只能有一个

3.—-检查约束 (Check Counstraint) 对该列数据的范围、格式的限制(如:年龄、性别等)

4.—-默认约束 (Default Counstraint) 该数据的默认值,如男性较多默认设置为男

5.—-外键约束 (Foreign Key Counstraint) 需要建立两表间的关系并引用主表的列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
三:表内容:
对数据进行增查改删(crud):增加(Create),查询(Retrieve),更新(Update),删除(Delete)

1:查询tb_door表中的所有记录:
1.1#select 列名 from 表名;
SELECT * FROM tb_door;#查询所有列
SELECT dname FROM tb_door; #查询时使用字段名代替了*
SELECT 一列 二列 FROM emp;#二列显示的内容被一列替换
SELECT dname,loc FROM tb_door; #查询多个字段的值时用逗号隔开
SELECT empno a FROM emp; #设置别名为a,列名后用空格隔开
SELECT ename 匹配 FROM emp;

1.2:查询tb_door表中的总记录数
select count(*) from tb_door;
select count(1) from tb_door;
SELECT COUNT(*) FROM emp WHERE job='员工';
SELECT 1 FROM table WHERE job='员工' LIMIT 1;
#改用LIMIT 1,让数据库查询时遇到一条就返回,不要再继续查


1.3:条件查询
1.3.1:去重distinct
mysql> SELECT DISTINCT loc FROM dept; #查询部门的地址


基础函数
lower
SELECT 'ABC',LOWER('ABC') from dept; --数据转小写

upper
select upper(dname) from dept --数据转大写

length
select length(dname) from dept --数据的长度

substr
SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]

concat
select dname,concat(dname,'123') X from dept --拼接数据

replace
select dname,replace(dname,'a','666') X from dept --把a字符替换成666

ifnull
select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换

round & ceil & floor
round四舍五入,ceil向上取整,floor向下取整

–直接四舍五入取整
select comm,round(comm) from emp

–四舍五入并保留一位小数
select comm,round(comm,1) from emp

–ceil向上取整,floor向下取整
select comm,ceil(comm) ,floor(comm) from emp

uuid
SELECT UUID()
返回uuid:a08528ca-741c-11ea-a9a1-005056c00001

now
select now() -- 年与日 时分秒
select curdate() --年与日
select curtime() --时分秒

year & month & day
–hour()时 minute()分 second()秒
select now(),hour(now()),minute(now()),second(now()) from emp ;

–year()年 month()月 day()日
select now(),year(now()),month(now()),day(now()) from emp ;

转义字符
'作为sql语句符号,内容中出现单撇就会乱套,进行转义即可
select 'ab'cd' -- 单引号是一个SQL语句的特殊字符
select 'ab\'cd' --数据中有单引号时,用一个\转义变成普通字符

六:拓展

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
1.事务(transaction)
概念:
数据库的事务(Database Transaction):是指作为单个逻辑工作单元执行的一系列操作,只有全都执行或者都不执行。
即:事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行,要么都执行成功,要么都执行失败,即都执行成功
才算成功,否则就会恢复到这堆SQL执行之前的状态。

事务的四个特性(ACID)
一般来说,事务是必须满足4个条件(ACID):原子性(Atomicity,或称不可分割性)、一致性(Consistency)、
隔离性(Isolation,又称独立性)、持久性(Durability)。

原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。
事务在执行过程中如果发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。

一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的
预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于
交叉执行而导致数据的不一致。

持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

隔离级别:
读未提交(Read uncommitted):安全性最差,可能发生并发数据问题,性能最好;
读提交(read committed): Oracle默认的隔离级别,性能较差,安全性较好;
可重复读(repeatable read):MySQL默认的隔离级别,安全性较好,性能一般;
串行化(Serializable): 表级锁,读写都加锁,效率低下,安全性高,不能并发。
从上到下,性能越来越差,安全性越来越好。

查询mysql的隔离级别
在默认情况下,MySQL每执行一条SQL语句,都是一个单独的事务。如果需要在一个事务
中包含多条SQL语句,那么需要手动开启事务和结束事务。

3.索引 index 定义 : 索引是一种排好序的快速查找的数据结构,它帮助数据库高效的进行数据的检索。在数据之外,数据库系统还维护着 满足特定查找算法的数据结构(额外的存储空间),这些数据结构以某种方式指向数据,这样就可以在这些数据结构 上实现高效的查找算法。这种数据结构就叫做索引。一般来说索引本身也很大,不可能全部存储在内存中,因此往往 以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。索引的作用相当于一本图书的目录, 可以根据目录中的页码快速的查找到所需要的内容。一般在创建表时就添加索引,需要经常查询索引列的数据时, 才需要在表中添加索引。 优点:提高查询的效率,原则上使用索引一定高效;加速了表和表之间的连接;创建唯一索引时保证了数据表中 每行的唯一性;使用分组或排序语句进行数据检索时,可以减少查询或分组排序的时间。 缺点:本身使用单独的空间即需要占用物理空间,不能随处添加索引,对表中的数据进行添加修改和删除时, 索引也需要动态的维护,降低了数据的维护速度。 分类: 单值索引(普通索引):一个索引只包括一个列,一个表可以有多个列; 唯一索引:索引列的值必须唯一不能重复,但允许有空值;主键会自动创建唯一索引; 主键索引:在表定义主键时会自动创建主键索引,主键索引是唯一索引的特定类型,可以获得最佳性能结果; 复合索引:一个索引同时包括多列; 候选索引,聚集索引,非聚集索引。

4.视图View 概念: 是可视化的表,视图当做是一个特殊的表,把sql执行的结果,直接缓存到了视图中。 下次还要发起相同的sql,直接查视图,现在使用的较少。

5.SQL优化

查询SQL尽量不要使用select *,而是具体字段
避免在where子句中使用or来连接条件
使用varchar代替char
尽量使用数值替代字符串类型
查询尽量避免返回大量数据,若数据量很大,通常采用分页,一页习惯10/20/50/100条。
使用explain分析你SQL执行计划,Explain主要看SQL是否使用了索引
是否使用了索引(index)及其扫描类型
创建name字段的索引,按名字查询的情况较多
优化like语句,模糊查询最好以确定的字符为开头进行查询(‘a%’),索引才会生效
字符串怪现象,数字加入单引号或双引号成为字符串之后索引才会生效
索引不宜太多,一般5个以内
索引不适合建在有大量重复数据的字段上
where限定查询的数据
避免在where中对字段进行表达式操作
避免在where子句中使用!=或<>操作符
去重distinct过滤字段要少
where中使用默认值代替null
批量插入性能提升
批量删除优化
伪删除设计
提高group by语句的效率
复合索引最左特性
排序字段创建索引
删除冗余和重复的索引
不要有超过5个以上的表连接
inner join 、left join、right join,优先使用inner join
in子查询的优化
尽量使用union all替代union

6.SQL需求:

需求 语句
查询所有记录
只查询指定列
查询id为?的记录
模糊查询记录
查询之间范围之间的所有记录
查询满足两个条件的记录
按?升序查询记录
以?升序、?降序查询记录
查询总数
聚合函数分组
max(?),min(?)
保留一位小数
合并重复内容
不合并重复内容
查询记录
从1位置(第二条)开始,总共取3条
1
2
3
4
5
6
7
8
9
10
11
12
7.SQL的执行顺序
(1) FROM [left_table] 选择表
(2) ON <join_condition> 链接条件
(3) <join_type> JOIN <right_table> 链接
(4) WHERE <where_condition> 条件过滤
(5) GROUP BY <group_by_list> 分组
(6) AGG_FUNC(column or expression),... 聚合
(7) HAVING <having_condition> 分组过滤
(8) SELECT (9) DISTINCT column,... 选择字段、去重
(9) ORDER BY <order_by_list> 排序
(10) LIMIT count OFFSET count; 分页

8.范式 定义:

在数据库当中,设计数据库时要求遵循一定的规则,该规则为了让数据库更加优化合理, 符合某种关系级别的集合,该规则即范式。

三范式: 设计表时用到的一些范式即需要遵守的规则, 范式分为六种 ,一般使用前三种

第一范式(1NF):表里的字段,不可分割,指的是字段的值就是最小单位

1NF的定义为:符合1NF的关系中的每个属性都不可再分

第二范式(2NF):在第一范式的基础上产生,指的是表里都需要设计主键/主关键字/主属性, 即非主属性完全依赖于主关键字。

2NF在1NF的基础之上,消除了非主属性对于码的部分函数依赖,也就是说,表里的每个字段都要依赖于主键
第一步:找出数据表中所有的码。
第二步:根据第一步所得到的码,找出所有的主属性。
第三步:数据表中,除去所有的主属性,剩下的就都是非主属性了。
第四步:查看是否存在非主属性对码的部分函数依赖

3NF在2NF的基础之上,消除了非主属性对于码的传递函数依赖
就是指没个属性都跟主键有直接关系而不是间接关系。
像:a–>b–>c 属性之间含有这样的关系,是不符合第三范式的。
比如Student表(学号,姓名,年龄,性别,所在院校,院校地址,院校电话)
这样一个表结构,就存在上述关系。 学号–> 所在院校 –> (院校地址,院校电话)
这样的表结构,我们应该拆开来,如下。
(学号,姓名,年龄,性别,所在院校)–(所在院校,院校地址,院校电话)

char和varchar有什么区别?
char为定长字符串,char(n),n最大为255

varchar为不定长字符串,varchar(n),n最大长度为65535

char(10)和varchar(10)存储abc,那它们有什么差别呢?

char保存10个字符,abc三个,其它会用空格补齐;而varchar只用abc三个位置。

什么是SQL攻击?如何解决

SQL注入:就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令。具体来说,它是利用现有应用程序,将(恶意)的SQL命令注入到后台数据库引擎执行的能力,它可以通过在Web表单中输入(恶意)SQL语句得到一个存在安全漏洞的网站上的数据库,而不是按照设计者意图去执行SQL语句。

防止的方法:

  1. 永远不要信任用户的输入,要对用户的输入进行校验,可以通过正则表达式,或限制长度,对单引号和双”-“进行转换等。

  2. 永远不要使用动态拼装SQL,可以使用参数化的SQL或者直接使用存储过程进行数据查询存取。

  3. 永远不要使用管理员权限的数据库连接,为每个应用使用单独的权限有限的数据库连接。

  4. 不要把机密信息明文存放,请加密或者hash掉密码和敏感的信息。

  5. 应用的异常信息应该给出尽可能少的提示,最好使用自定义的错误信息对原始错误信息进行包装,把异常信息存放在独立的表中。

2什么是数据库的事务

事物就是为了保证原有事物的完整性和一致性,通过一定的条件规定该事物的可靠性和安全性(如数据库中的事物)。

A:原子性:一个事物是不可再分的工作单位,该事物要么成功要么失败。

B:一致性:事物必须是使数据库从另外一个一致性状态到另一个一致性状态,与原子性密切相关。

C:持久性:指一个事物一旦提交后对数据库中数据的改变应该是永久不变的。

D:隔离性:一个事物的执行不能够被其他事物所干扰。

3 数据库里的字段约束有哪些,各有什么作用

  1. 主键约束:实现实体的完整性(PK:主键),值唯一,不能为空,且可以自动递增
  2. 外键约束:用于多表之间的查询该表组建作为另外张表的外键建立标语标志就的关系(FK)
  3. 非空约束:(NOT NULL)保证该字段的内容不能为空null
  4. 唯一约束:列表的唯一性约束(UNIQUE)
  5. 检查约束:检查列的值是否满足一个boolean表达式(CHECK)
  6. 默认约束:给指定的字段可以设置默认值,不设置值时使用默认值而不是null

mysql的mapper代码,ysql中#和$好像是有区别的

 1、 # 自己会带有双引号,$并不会。

 2、#的优势就在于它能很大程度的防止sql注入,而$则不行。

 3、#的性能优于$,能使用 #{ } 的地方就用 #{ }。表名作为变量时,必须使用 ${ }

 4、MyBatis排序时使用order by 动态参数时需要注意,用$而不是#。

sql预编译
sql 预编译指的是数据库驱动在发送 sql 语句和参数给 DBMS 之前对 sql 语句进行编译,这样 DBMS 执行 sql 时,就不需要重新编译。