数据库选型sql和nosql

一、数据库选型sql和nosql：

1.1关系型数据库sql和非关系型数据库nosql

1.2关系型数据库特点：

• 数据结构化存储在二维表中
• 支持事务的原子性A,一致性C,隔离性I持久性D 特性
• 支持使用SQL语言对存储在其中的数据进行操作

1.3关系型数据库的适用场景：

• 数据之间存在着一定关系，需要关联查询数据的场景
• 需要事务支持的业务场景
• 需要使用SQL语言灵活操作数据的场景。

1.4非关系型数据库特点：

• 存储结构灵活，没有固定的结构
• 对事务的支持比较弱，但对数据的并发处理性能高
• 大多不使用SQL语言操作数据

1.5非关系型数据库使用场景：

• 数据结构不固定的场景。
• 对事务要求不高，但读写并发比较大的场景
• 对数据的处理操作比较简单的场景

1.6 选择数据库的原则：

• 数据库使用的广泛性
• 数据库的可扩展性
• 数据库的安全性和稳定性
• 数据库所支持的系统
• 数据库的使用成本

1.7MySQL数据库的可扩展性：

• 支持基于二进制日志的逻辑复制
• 存在多种第三方数据库中间层，支持读写分离及分库分表

1.8MySQL的安全性和稳定性：

• MySQL主从复制集群可达到99%的可用性
• 配合主从复制高可用架构可以达到99.99%的可用性
• 支持对存储在MySQL的数据进行分级安全控制。
• 支持Liunx系统，Windows系统

1.9 MySQL的使用成本：

• 社区版本免费
• 使用人员众多，可以方便的获取技术支持

二、数据库的设计：

逻辑设计：
1.宽表模式：把所有字段都放在一个表中存储
1.1存在问题：

• 数据插入异常：部分数据由于缺失主键信息而无法写入表中
• 数据更新异常：修改一行中某列的值时，同时修改了多行的数据
• 数据删除异常：删除某一行数据时不得不删除另一行数据
• 数据沉余：相同的数据在一个表中出现了多次
  1.2应用场景：
• 适合用在数据报表中，这样在数据量大时，不需要连表，查询数据快
  2.数据库设计范式：
• 第一范式：表中所有列属性不可以再分（比如：联系方式包含邮箱，电话，微信等等，所以联系方式不能作为表属性
• 第二范式：表中必须存在业务主键，并且非主键依赖于全部业务主键
• 第三范式：表中的非主键列之间不能相互依赖
  3.mysql常见的存储引擎：
  
  注：

存储引擎：存储数据，为存储的数据建立索引，更新，查询数据。因为在关系数据库中数据以表的形式存储，所以存储引擎也可以称为表类型。
查看当前mysql数据库支持哪些存储引擎：show engines;或者show variablkes like 'have%‘；

3.1 InnoDB存储引擎的特点:

1. 事务型存储引擎支持ACID
2. 数据按主键聚集存储
3. 支持行级锁及MVCC
4. 支持Btree和自适应Hash索引
5. 支持全文和空间索引

三、sql的数据类型：

1.整数类型：

2.浮点类型


3.时间类型：

4.字符串类型：

注：选择数据类型要注意下面几点：

1. 优先选择符合存储数据需求的最小数据类型
2. 谨慎使用enum，text字符串类型
3. 和财务相关的数值型数据，必须使用decimal类型

四、命令行命令：

1，连接mysql服务器

mysql -uroot -p -hlocalhost

2.不进去mysql服务器交互，直接显示查询结果：

mysql -uroot -p -hlocalhost -e "select user()"

五、sql语言：

1.什么是SQL：一种描述性语言
2.SQL语言的作用：对存储在RDBMS中的数据进行增删改查等操作
3.常用的SQL语言的种类：DCL（授权语句，创建用户语句）、DDL（建立表语句，修改表结构语句）、DML(操作数据语句，增删改查语句)、TCL（事务语句，开启，关闭事务语句)
4.DCL(Data Control Language)：

• 建立数据库账号：create user
• 对用户授权：grant
• 收回用户权限：revoke
  4.1建立程序使用的数据库账号：建立一个用户mc_class可以在192.168.1.下的所有网段下通过密码1233333登录

create user mc_class@'192.168.1.%' identified with 'mysql_native_password' by '1233333'

4.2.给账号授权：

• 向表中插入数据的权限：insert
• 删除表中数据的权限：delete
• 修改表中数据的权限：update
• 查询表中数据的权限：select
• 执行存储过程中的权限：execute

4.3.查看当前数据库有哪些权限：

show privileges\G

4.4.grant给数据库用户权限：

注意：

• 使用grant授权的数据库账户必须存在
• 用户使用grant命令授权必须具有grant option的权限
• 获取命令帮助 \h grant

4.5.回收用户权限：

5.DDL(data Definition language)

• 建立/修改/删除数据库：create/alter/drop database
• 建立/修改/删除表：create/alter/drop table
• 在表tableName中添加createTime字段： alter table tableName add createTime tinyint default 0 comment '创建时间'
• 查看表结构：show create table tableName
• 建立/删除索引：create/drop index
• 清空表：truncate table 表名称
• 重命名表：rename table 表名称1 to 表名称2
• 建立/修改/删除视图：create/alter/drop view

5.1创建数据库：

create database imc_db;

5.2创建数据库表：
临时表（只有当前线程可以用，退出了账号后临时表会自动被删除)

5.3 创建与删除索引（索引：

6. *DML(Data Manipulation language): *
6.1 给表新增数据: insert into

insert into 表名(属性名，属性名) values(内容，内容)；
insert into 表名(表名，表名)
select  1，id  from 表名；

6.2 删除表中的数据: delete

delete from tableName where id = 1 order by id desc limit 1

6.3 修改表中的数据: update

update tableName set age = 1,name = '李白' where id = 1 order by id desc limit 1

-- 根据tableName2中的firmId对应的name和age修改tableName1中的name和age
update tableName1 a
left join 
	(select firmId,name,age from tableName2 where id > 3)
b on firmId.name = a.id
set a.name = b.name,a.age = b.age;

6.4 查询表中的数据: select

select * from tableName where id = 1 group by id order by id desc limit 1

6.5创建唯一索引：

create unique index uqx_className on imc_class(class_name);

六、比较运算符：

注意：任何运算符与null运算结果都是null

七、多表连接：

八、sql_mode类型

sql_mode：它定义了MySQL应该支持的sql语法，对数据的校验等等。

查看MySQL sql_mode类型

show variables like 'sql_mode'

九、having 子句过滤分组结果

count(*) 是聚合函数，不能用在where后面，否则会报错，但是可以用having过滤结果。

select id,count(*) 
from tableName
group by id having count(*) > 2

十、常用的聚合函数

十一、视图：

创建vm_course 视图：

create view vm_course 
as 
 select id,name
 from imc_course
 where id > 6

查看视图vm_course （可以查到上面 select id,name from imc_course where id > 6语句查询的结果）：

select * from vm_course

十二、系统函数：

1.常用的时间函数
%Y：四位的年
%m：月份（00-12）
%d：天（00-31）
%H：小时（00-24）
%i：分钟（00-59）
%s：秒（00-59）

select SEC_TO_TIME(60),TIME_TO_SEC('1:00:00')

SELECT NOW() -- 当前时间
,DATE_ADD(NOW (),INTERVAL 1 DAY) -- 当前时间加1天
,DATE_ADD(NOW(),INTERVAL 1 YEAR), --当前时间加1年
DATE_ADD(NOW(),INTERVAL -1 DAY), --当前时间减1天
DATE ADD(NOW (),INTERVAL '-1:30' HOUR MINUTE) -- 当前时间减1: 30

SELECT NOW()
,EXTRACT(YEAR FROM NOW()) -- 提取当前时间的年分
,EXTRACT(MONTH FROM NOW()) --提取出月份
,EXTRACT(DAY FROM NOW()) --提取日期

2.常用的字符函数

select concat_ws(',',name,age) from user

select format(123456.789,4)

select left('www.baidu.com',3),right('www.baidu.com',3)

select substring_index('192.168.0.100','.',-2)

3.其他常用函数：

SELECT user_nick,
		CASE WHEN sex= 1 THEN '男',
			 when sex = 0 then '女'
			 else '未知'
		END AS '性别'
FROM userTable

4.公共表达式CTE(Common Table Expressions)
1.MySQL8.0之后的版本才可以使用
2.CTE生成一个命名临时表，并且只在查询期间有效
3.CTE临时表在一个查询中可以多次引用及自引用
基础语法：

-- CTE递归生成序列
WITH RECURSIVE test AS (
	SELECT 1 AS n
	UNION ALL 
	SELECT 1+n FROM test WHERE n<10
)
SELECT * FROM test

5.窗口函数

SELECT name,title,study
,SUM(study) OVER(PARTITION BY class name) AS total
FROM course a
JOIN class b ON b.clas_id=a.class_id

6.sql开发中的易错问题：

1. 不要使用count(*)判断是否存在符合条件的数据，因为要查询整个表。使用select … limit 1 性能更好。
2. 不要在执行一个更新语句后，使用查询方式判断此更新语句是否有执行成功。使用ROW_COUNTO 函数判断修改行数。
3. 可以在where中进行过滤吗，不要在left join on后面进行过滤数据，因为有时候在left join on后没有效果。
4. 在使用In进行子查询的判断时，在in后的select语句中，如果from的表中没有select的那一个列属性，如Select A1 from A where A1 in (select A1 fromB)这时尽管B中并不存在A1列，数据库也不会报错，而是会列出A表中的所有数据。最好使用join关联代替子查询。
5. 不能对于表中定义的具有not null和 default值的列，在插入数据时直接插入NULL值。

十三、sql优化：

1.配置mysql慢查询日志

set global slow_query_log = [ON | OFF ]
set global slow_query_log_file = /sql_log/slowlog.log  -- 把慢查询日志记录在根目录下的sql_log目录下的slowlog.log文件中
set global long_query_time = xx.xxx秒  -- sql执行时间超过设置的秒时间，就会把sql写到慢查询日志中
set global log_queries_not_using_indexes = [ON | OFF ] -- 为off时，会把没有使用到索引的sql写到慢查询日志中

2.分析mysql慢查询日志

mysql dumpslow [OPTS...] [LOGS...]

pt-query-digest[OPTIONS] [FILES] [DSN]

3.监控长时间运行的sql

select id,user,host,DB,command,time,state,info 
from information_schema.PROCESSLIST
where time >= 60

4.获取执行计划：

例子：

explain
	select * from user

结果：

id：表示查询执行的顺序，ID相同时由上到下执行，ID不同时，由大到小执行
select_type:表示查询类型如下


table:表示从那个表中获取数据。<union M,N> 由id为M，N查询union产生的结果集。/ 由id为N查询的结果集。
partitions：对于分区表，显示查询的分区id。对于非分区表，显示null。
type：

possible_keys:指出查询中可能会用到的索引。
key：指出查询时时间用到的索引。
key_len:实际使用索引的最大长度。
ref:指出那些列或常量被用于索引查找。
rows:根据统计信息预估的扫描的行数。
filtered:表示返回结果的行数占需读取行数的百分比。
Extra:

5.什么是索引：
索引告诉存储引擎如何快速地查找所需要的数据。

Innodb支持的索引l类型：
Btree索引
自适应HASH索引
全文索引
空间索引

Btree索引的特点：
1.以B+树的结构存储索引数据

2.Btree索引适用于全值匹配的查询
3.Btree索引适合处理范围查找
4.Btree索引从索引的最左侧列开始匹配查询列

应该在什么列上建立索引：
1.where 子句中的列
2.包含在order by，group by ，distinct中的字段
3.多表join的关联列

如何选择复合索引键的顺序：
1.区分度最高的列放在联合索引的最左侧
2.使用最频繁的列放在联合索引的最左侧
3.尽量把字段长度小的列放在联合索引的最左侧

索引不起效果的原因如下；
1.只能从最左侧开始按索引键的顺序使用索引，不能跳过索引键
2.not in 和 <> 操作无法使用索引
3.索引列上不能使用表达式或函数

注：索引不是越多越好

6.改写sql提高查询效率

使用join代替not in
使用CTE代替子查询
拆分复杂的大sql为多个简单的小sql
巧用计算列来查询sql

十四、事务与并发控制

1.什么是事务
1.1事务是数据库执行操作的最小逻辑单元
1.2事务可以由一个sql组成也可以由多个sql组成
1.3组成事务的sql要么全执行成功要么全执行失败
事务编写格式如下：

事务编写

BEGIN;
	SELECT course_id,title FROM imc_course WHERE score > 9.6;
ROLLBACK;

2.事务的四个特性

3.并发带来的问题
脏读：一个事务读取了另一个事务未提交的数据（第二次读取的数据是其他事务回滚或没有提交成功的数据)
不可重复读：一个事务前后两次读取同一数据不一致（第二次读取的数据是其他事务提交成功的数据)
幻读：指一个事务两次查询的结果集记录数不一致（第二次读取的数据是其他事务提交成功的数据)

4.事务的隔离性
InnoDB的隔离级别（默认是可重复读隔离级别）：

sql修改事务的隔离级别：

set SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 设置事务的隔离级别为顺序读

5.事务的阻塞：
5.1阻塞的原因:
innodb中的锁：

查询需要对资源加共享锁（S）
数据修改需要对资源加排他锁（X）

5.2什么是阻塞：
由于不同锁之间的兼容关系，造成一个事务要等待另一个事务释放其所占的资源才能运行。
5.3如何发现阻塞：

SELECT waiting_pid As '被阻塞的线程',
	waiting_query AS '被阻塞的SQL',
	blocking_pid AS '阻塞线程',
	blocking_query As '阻塞SQL',
	wait_age AS '阻塞时间',
	sql_kill_blocking_query AS '建议操作'
FROM sys.innodb_locl_waits 
WHERE
(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(wait_started))>30

5.4如何处理阻塞：

手动终至占用资源的事务
优化占用资源事务的sql，使其尽快释放资源

6.死锁
6.1什么是死锁：

并行执行的多个事务相互之间占有了对方所需要的资源

6.2发现死锁：
把死锁的事务信息放到日志中

set global innodb_print_all_deadlocks=on;

6.3如何处理死锁：

数据库自行回滚占用资源少的事务
并发事务按相同顺序占有资源文章来源地址https://www.toymoban.com/news/detail-529547.html

到了这里，关于数据库选型sql和nosql的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！