一、數(shù)據(jù)庫選型sql和nosql：

1.1關(guān)系型數(shù)據(jù)庫sql和非關(guān)系型數(shù)據(jù)庫nosql

1.2關(guān)系型數(shù)據(jù)庫特點(diǎn)：

• 數(shù)據(jù)結(jié)構(gòu)化存儲(chǔ)在二維表中
• 支持事務(wù)的原子性A,一致性C,隔離性I持久性D 特性
• 支持使用SQL語言對存儲(chǔ)在其中的數(shù)據(jù)進(jìn)行操作

1.3關(guān)系型數(shù)據(jù)庫的適用場景：

• 數(shù)據(jù)之間存在著一定關(guān)系，需要關(guān)聯(lián)查詢數(shù)據(jù)的場景
• 需要事務(wù)支持的業(yè)務(wù)場景
• 需要使用SQL語言靈活操作數(shù)據(jù)的場景。

1.4非關(guān)系型數(shù)據(jù)庫特點(diǎn)：

• 存儲(chǔ)結(jié)構(gòu)靈活，沒有固定的結(jié)構(gòu)
• 對事務(wù)的支持比較弱，但對數(shù)據(jù)的并發(fā)處理性能高
• 大多不使用SQL語言操作數(shù)據(jù)

1.5非關(guān)系型數(shù)據(jù)庫使用場景：

• 數(shù)據(jù)結(jié)構(gòu)不固定的場景。
• 對事務(wù)要求不高，但讀寫并發(fā)比較大的場景
• 對數(shù)據(jù)的處理操作比較簡單的場景

1.6 選擇數(shù)據(jù)庫的原則：

• 數(shù)據(jù)庫使用的廣泛性
• 數(shù)據(jù)庫的可擴(kuò)展性
• 數(shù)據(jù)庫的安全性和穩(wěn)定性
• 數(shù)據(jù)庫所支持的系統(tǒng)
• 數(shù)據(jù)庫的使用成本

1.7MySQL數(shù)據(jù)庫的可擴(kuò)展性：

• 支持基于二進(jìn)制日志的邏輯復(fù)制
• 存在多種第三方數(shù)據(jù)庫中間層，支持讀寫分離及分庫分表

1.8MySQL的安全性和穩(wěn)定性：

• MySQL主從復(fù)制集群可達(dá)到99%的可用性
• 配合主從復(fù)制高可用架構(gòu)可以達(dá)到99.99%的可用性
• 支持對存儲(chǔ)在MySQL的數(shù)據(jù)進(jìn)行分級安全控制。
• 支持Liunx系統(tǒng)，Windows系統(tǒng)

1.9 MySQL的使用成本：

• 社區(qū)版本免費(fèi)
• 使用人員眾多，可以方便的獲取技術(shù)支持

二、數(shù)據(jù)庫的設(shè)計(jì)：

邏輯設(shè)計(jì)：
1.寬表模式：把所有字段都放在一個(gè)表中存儲(chǔ)
1.1存在問題：

• 數(shù)據(jù)插入異常：部分?jǐn)?shù)據(jù)由于缺失主鍵信息而無法寫入表中
• 數(shù)據(jù)更新異常：修改一行中某列的值時(shí)，同時(shí)修改了多行的數(shù)據(jù)
• 數(shù)據(jù)刪除異常：刪除某一行數(shù)據(jù)時(shí)不得不刪除另一行數(shù)據(jù)
• 數(shù)據(jù)沉余：相同的數(shù)據(jù)在一個(gè)表中出現(xiàn)了多次
  1.2應(yīng)用場景：
• 適合用在數(shù)據(jù)報(bào)表中，這樣在數(shù)據(jù)量大時(shí)，不需要連表，查詢數(shù)據(jù)快
  2.數(shù)據(jù)庫設(shè)計(jì)范式：
• 第一范式：表中所有列屬性不可以再分（比如：聯(lián)系方式包含郵箱，電話，微信等等，所以聯(lián)系方式不能作為表屬性
• 第二范式：表中必須存在業(yè)務(wù)主鍵，并且非主鍵依賴于全部業(yè)務(wù)主鍵
• 第三范式：表中的非主鍵列之間不能相互依賴
  3.mysql常見的存儲(chǔ)引擎：
  
  注：

存儲(chǔ)引擎：存儲(chǔ)數(shù)據(jù)，為存儲(chǔ)的數(shù)據(jù)建立索引，更新，查詢數(shù)據(jù)。因?yàn)樵陉P(guān)系數(shù)據(jù)庫中數(shù)據(jù)以表的形式存儲(chǔ)，所以存儲(chǔ)引擎也可以稱為表類型。
查看當(dāng)前mysql數(shù)據(jù)庫支持哪些存儲(chǔ)引擎：show engines;或者show variablkes like 'have%‘；

3.1 InnoDB存儲(chǔ)引擎的特點(diǎn):

1. 事務(wù)型存儲(chǔ)引擎支持ACID
2. 數(shù)據(jù)按主鍵聚集存儲(chǔ)
3. 支持行級鎖及MVCC
4. 支持Btree和自適應(yīng)Hash索引
5. 支持全文和空間索引

三、sql的數(shù)據(jù)類型：

1.整數(shù)類型：

2.浮點(diǎn)類型


3.時(shí)間類型：

4.字符串類型：

注：選擇數(shù)據(jù)類型要注意下面幾點(diǎn)：

1. 優(yōu)先選擇符合存儲(chǔ)數(shù)據(jù)需求的最小數(shù)據(jù)類型
2. 謹(jǐn)慎使用enum，text字符串類型
3. 和財(cái)務(wù)相關(guān)的數(shù)值型數(shù)據(jù)，必須使用decimal類型

四、命令行命令：

1，連接mysql服務(wù)器

mysql -uroot -p -hlocalhost

2.不進(jìn)去mysql服務(wù)器交互，直接顯示查詢結(jié)果：

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

五、sql語言：

1.什么是SQL：一種描述性語言
2.SQL語言的作用：對存儲(chǔ)在RDBMS中的數(shù)據(jù)進(jìn)行增刪改查等操作
3.常用的SQL語言的種類：DCL（授權(quán)語句，創(chuàng)建用戶語句）、DDL（建立表語句，修改表結(jié)構(gòu)語句）、DML(操作數(shù)據(jù)語句，增刪改查語句)、TCL（事務(wù)語句，開啟，關(guān)閉事務(wù)語句)
4.DCL(Data Control Language)：

• 建立數(shù)據(jù)庫賬號(hào)：create user
• 對用戶授權(quán)：grant
• 收回用戶權(quán)限：revoke
  4.1建立程序使用的數(shù)據(jù)庫賬號(hào)：建立一個(gè)用戶mc_class可以在192.168.1.下的所有網(wǎng)段下通過密碼1233333登錄

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

4.2.給賬號(hào)授權(quán)：

• 向表中插入數(shù)據(jù)的權(quán)限：insert
• 刪除表中數(shù)據(jù)的權(quán)限：delete
• 修改表中數(shù)據(jù)的權(quán)限：update
• 查詢表中數(shù)據(jù)的權(quán)限：select
• 執(zhí)行存儲(chǔ)過程中的權(quán)限：execute

4.3.查看當(dāng)前數(shù)據(jù)庫有哪些權(quán)限：

show privileges\G

4.4.grant給數(shù)據(jù)庫用戶權(quán)限：

注意：

• 使用grant授權(quán)的數(shù)據(jù)庫賬戶必須存在
• 用戶使用grant命令授權(quán)必須具有g(shù)rant option的權(quán)限
• 獲取命令幫助 \h grant

4.5.回收用戶權(quán)限：

5.DDL(data Definition language)

• 建立/修改/刪除數(shù)據(jù)庫：create/alter/drop database
• 建立/修改/刪除表：create/alter/drop table
• 在表tableName中添加createTime字段： alter table tableName add createTime tinyint default 0 comment '創(chuàng)建時(shí)間'
• 查看表結(jié)構(gòu)：show create table tableName
• 建立/刪除索引：create/drop index
• 清空表：truncate table 表名稱
• 重命名表：rename table 表名稱1 to 表名稱2
• 建立/修改/刪除視圖：create/alter/drop view

5.1創(chuàng)建數(shù)據(jù)庫：

create database imc_db;

5.2創(chuàng)建數(shù)據(jù)庫表：
臨時(shí)表（只有當(dāng)前線程可以用，退出了賬號(hào)后臨時(shí)表會(huì)自動(dòng)被刪除)

5.3 創(chuàng)建與刪除索引（索引：

6. *DML(Data Manipulation language): *
6.1 給表新增數(shù)據(jù): insert into

insert into 表名(屬性名，屬性名) values(內(nèi)容，內(nèi)容)；
insert into 表名(表名，表名)
select  1，id  from 表名；

6.2 刪除表中的數(shù)據(jù): delete

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

6.3 修改表中的數(shù)據(jù): update

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

-- 根據(jù)tableName2中的firmId對應(yīng)的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 查詢表中的數(shù)據(jù): select

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

6.5創(chuàng)建唯一索引：

create unique index uqx_className on imc_class(class_name);

六、比較運(yùn)算符：

注意：任何運(yùn)算符與null運(yùn)算結(jié)果都是null

七、多表連接：

八、sql_mode類型

sql_mode：它定義了MySQL應(yīng)該支持的sql語法，對數(shù)據(jù)的校驗(yàn)等等。

查看MySQL sql_mode類型

show variables like 'sql_mode'

九、having 子句過濾分組結(jié)果

count(*) 是聚合函數(shù)，不能用在where后面，否則會(huì)報(bào)錯(cuò)，但是可以用having過濾結(jié)果。

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

十、常用的聚合函數(shù)

十一、視圖：

創(chuàng)建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語句查詢的結(jié)果）：

select * from vm_course

十二、系統(tǒng)函數(shù)：

1.常用的時(shí)間函數(shù)
%Y：四位的年
%m：月份（00-12）
%d：天（00-31）
%H：小時(shí)（00-24）
%i：分鐘（00-59）
%s：秒（00-59）

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

SELECT NOW() -- 當(dāng)前時(shí)間
,DATE_ADD(NOW (),INTERVAL 1 DAY) -- 當(dāng)前時(shí)間加1天
,DATE_ADD(NOW(),INTERVAL 1 YEAR), --當(dāng)前時(shí)間加1年
DATE_ADD(NOW(),INTERVAL -1 DAY), --當(dāng)前時(shí)間減1天
DATE ADD(NOW (),INTERVAL '-1:30' HOUR MINUTE) -- 當(dāng)前時(shí)間減1: 30

SELECT NOW()
,EXTRACT(YEAR FROM NOW()) -- 提取當(dāng)前時(shí)間的年分
,EXTRACT(MONTH FROM NOW()) --提取出月份
,EXTRACT(DAY FROM NOW()) --提取日期

2.常用的字符函數(shù)

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.其他常用函數(shù)：

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

4.公共表達(dá)式CTE(Common Table Expressions)
1.MySQL8.0之后的版本才可以使用
2.CTE生成一個(gè)命名臨時(shí)表，并且只在查詢期間有效
3.CTE臨時(shí)表在一個(gè)查詢中可以多次引用及自引用
基礎(chǔ)語法：

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

5.窗口函數(shù)

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開發(fā)中的易錯(cuò)問題：

1. 不要使用count(*)判斷是否存在符合條件的數(shù)據(jù)，因?yàn)橐樵冋麄€(gè)表。使用select … limit 1 性能更好。
2. 不要在執(zhí)行一個(gè)更新語句后，使用查詢方式判斷此更新語句是否有執(zhí)行成功。使用ROW_COUNTO 函數(shù)判斷修改行數(shù)。
3. 可以在where中進(jìn)行過濾嗎，不要在left join on后面進(jìn)行過濾數(shù)據(jù)，因?yàn)橛袝r(shí)候在left join on后沒有效果。
4. 在使用In進(jìn)行子查詢的判斷時(shí)，在in后的select語句中，如果from的表中沒有select的那一個(gè)列屬性，如Select A1 from A where A1 in (select A1 fromB)這時(shí)盡管B中并不存在A1列，數(shù)據(jù)庫也不會(huì)報(bào)錯(cuò)，而是會(huì)列出A表中的所有數(shù)據(jù)。最好使用join關(guān)聯(lián)代替子查詢。
5. 不能對于表中定義的具有not null和 default值的列，在插入數(shù)據(jù)時(shí)直接插入NULL值。

十三、sql優(yōu)化：

1.配置mysql慢查詢?nèi)罩?/p>

set global slow_query_log = [ON | OFF ]
set global slow_query_log_file = /sql_log/slowlog.log  -- 把慢查詢?nèi)罩居涗浽诟夸浵碌膕ql_log目錄下的slowlog.log文件中
set global long_query_time = xx.xxx秒  -- sql執(zhí)行時(shí)間超過設(shè)置的秒時(shí)間，就會(huì)把sql寫到慢查詢?nèi)罩局?set global log_queries_not_using_indexes = [ON | OFF ] -- 為off時(shí)，會(huì)把沒有使用到索引的sql寫到慢查詢?nèi)罩局?

2.分析mysql慢查詢?nèi)罩?/p>

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

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

3.監(jiān)控長時(shí)間運(yùn)行的sql

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

4.獲取執(zhí)行計(jì)劃：

例子：

explain
	select * from user

結(jié)果：

id：表示查詢執(zhí)行的順序，ID相同時(shí)由上到下執(zhí)行，ID不同時(shí)，由大到小執(zhí)行
select_type:表示查詢類型如下


table:表示從那個(gè)表中獲取數(shù)據(jù)。<union M,N> 由id為M，N查詢union產(chǎn)生的結(jié)果集。/ 由id為N查詢的結(jié)果集。
partitions：對于分區(qū)表，顯示查詢的分區(qū)id。對于非分區(qū)表，顯示null。
type：

possible_keys:指出查詢中可能會(huì)用到的索引。
key：指出查詢時(shí)時(shí)間用到的索引。
key_len:實(shí)際使用索引的最大長度。
ref:指出那些列或常量被用于索引查找。
rows:根據(jù)統(tǒng)計(jì)信息預(yù)估的掃描的行數(shù)。
filtered:表示返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比。
Extra:

5.什么是索引：
索引告訴存儲(chǔ)引擎如何快速地查找所需要的數(shù)據(jù)。

Innodb支持的索引l類型：
Btree索引
自適應(yīng)HASH索引
全文索引
空間索引

Btree索引的特點(diǎn)：
1.以B+樹的結(jié)構(gòu)存儲(chǔ)索引數(shù)據(jù)

2.Btree索引適用于全值匹配的查詢
3.Btree索引適合處理范圍查找
4.Btree索引從索引的最左側(cè)列開始匹配查詢列

應(yīng)該在什么列上建立索引：
1.where 子句中的列
2.包含在order by，group by ，distinct中的字段
3.多表join的關(guān)聯(lián)列

如何選擇復(fù)合索引鍵的順序：
1.區(qū)分度最高的列放在聯(lián)合索引的最左側(cè)
2.使用最頻繁的列放在聯(lián)合索引的最左側(cè)
3.盡量把字段長度小的列放在聯(lián)合索引的最左側(cè)

索引不起效果的原因如下；
1.只能從最左側(cè)開始按索引鍵的順序使用索引，不能跳過索引鍵
2.not in 和 <> 操作無法使用索引
3.索引列上不能使用表達(dá)式或函數(shù)

注：索引不是越多越好

6.改寫sql提高查詢效率

使用join代替not in
使用CTE代替子查詢
拆分復(fù)雜的大sql為多個(gè)簡單的小sql
巧用計(jì)算列來查詢sql

十四、事務(wù)與并發(fā)控制

1.什么是事務(wù)
1.1事務(wù)是數(shù)據(jù)庫執(zhí)行操作的最小邏輯單元
1.2事務(wù)可以由一個(gè)sql組成也可以由多個(gè)sql組成
1.3組成事務(wù)的sql要么全執(zhí)行成功要么全執(zhí)行失敗
事務(wù)編寫格式如下：

事務(wù)編寫

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

2.事務(wù)的四個(gè)特性

3.并發(fā)帶來的問題
臟讀：一個(gè)事務(wù)讀取了另一個(gè)事務(wù)未提交的數(shù)據(jù)（第二次讀取的數(shù)據(jù)是其他事務(wù)回滾或沒有提交成功的數(shù)據(jù))
不可重復(fù)讀：一個(gè)事務(wù)前后兩次讀取同一數(shù)據(jù)不一致（第二次讀取的數(shù)據(jù)是其他事務(wù)提交成功的數(shù)據(jù))
幻讀：指一個(gè)事務(wù)兩次查詢的結(jié)果集記錄數(shù)不一致（第二次讀取的數(shù)據(jù)是其他事務(wù)提交成功的數(shù)據(jù))

4.事務(wù)的隔離性
InnoDB的隔離級別（默認(rèn)是可重復(fù)讀隔離級別）：

sql修改事務(wù)的隔離級別：

set SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 設(shè)置事務(wù)的隔離級別為順序讀

5.事務(wù)的阻塞：
5.1阻塞的原因:
innodb中的鎖：

查詢需要對資源加共享鎖（S）
數(shù)據(jù)修改需要對資源加排他鎖（X）

5.2什么是阻塞：
由于不同鎖之間的兼容關(guān)系，造成一個(gè)事務(wù)要等待另一個(gè)事務(wù)釋放其所占的資源才能運(yùn)行。
5.3如何發(fā)現(xiàn)阻塞：

SELECT waiting_pid As '被阻塞的線程',
	waiting_query AS '被阻塞的SQL',
	blocking_pid AS '阻塞線程',
	blocking_query As '阻塞SQL',
	wait_age AS '阻塞時(shí)間',
	sql_kill_blocking_query AS '建議操作'
FROM sys.innodb_locl_waits 
WHERE
(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(wait_started))>30

5.4如何處理阻塞：

手動(dòng)終至占用資源的事務(wù)
優(yōu)化占用資源事務(wù)的sql，使其盡快釋放資源

6.死鎖
6.1什么是死鎖：

并行執(zhí)行的多個(gè)事務(wù)相互之間占有了對方所需要的資源

6.2發(fā)現(xiàn)死鎖：
把死鎖的事務(wù)信息放到日志中

set global innodb_print_all_deadlocks=on;

6.3如何處理死鎖：

數(shù)據(jù)庫自行回滾占用資源少的事務(wù)
并發(fā)事務(wù)按相同順序占有資源文章來源地址http://www.zghlxwxcb.cn/news/detail-529547.html

到了這里，關(guān)于數(shù)據(jù)庫選型sql和nosql的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！