資料庫筆記

一、初識資料庫:

1、什么是關系型資料庫設計？
資料庫設計就是將資料庫中的資料物體及這些資料物體之間的關系，進行規劃和結構化的程序，
2、資料庫規范設計:
第一范式:原子性
第二范式:確保表中每列都跟主鍵相關
第三范式:確保表中每列都與主鍵直接相關，而不能間接相關
(A\B\C三列，A列是主鍵，C列依賴B列，B列與A列直接相關，這就是錯誤的設計，因為C列不是與主鍵直接相關)
windows+r–>services.msc

二、基本sql陳述句DDL、DML、DQL

1、創建資料庫:create database 資料庫名;
2、查看資料庫串列:show databases;
3、使用資料庫:use 資料庫名;
4、資料型別:
varchar字串型別
int整數型別
float、double小數型別
date YYYY-MM-DD年月日型別
datetime yyyy-mm-dd hh:mm:ss年月日時分秒型別
timestamp YYYYMMDDHHMMSS 精度為1秒，時間戳–格林時間、東八區(公務員考題:北京時間指的是北京這個地址么)
例:2018年某時刻~2019年某時刻
5、創建表:創建表前必須使用某個資料庫
create table [if not exists] 表名(
欄位1 資料型別[欄位屬性|約束][索引][注釋],
欄位2 資料型別[欄位屬性|約束][索引][注釋],…
欄位n 資料型別[欄位屬性|約束][索引] comment “欄位注釋”
)[表型別][表字符集] comment = “練習表”;
約束:
非空約束:not null
主鍵約束:primary key
自動增長:auto_increment
外鍵約束:foreign key references

6、洗掉資料庫:drop database 資料庫名;
7、查看所有表:show tables;
8、查看某個表:describe 表名;
9、洗掉表:drop table 表名;
10、修改表名:alter table 舊表名 rename 新表名;
11、添加欄位:alter table 表名 add 欄位名 資料型別(長度) 約束 注釋;
12、修改欄位:alter table 表名 change 原欄位名 新欄位名 資料型別(長度) 約束 注釋;
13、洗掉欄位:alter table 表名 drop 欄位名;

三、新增sql陳述句:

1、標準寫法:insert into 表名 (欄位1，欄位2…) value (對應值，對應值…)
2、簡潔寫法:insert into 表名 value (對應值，對應值…)
注:value和values都可用
3、同時新增多條資料: insert into 表名 value (列1值,列2值…),(列1值,列2值…),(列1值,列2值…)…
4、insert into 表1 select (欄位) from 表2
注:表1和表2完全相同的情況下
5、insert into 表名 value
(值1,(從別的表查出的值))

四、修改sql陳述句:

1、修改一條記錄:update 表名 set 欄位=新的值 where 條件
2、修改多條記錄:update 表名 set 欄位1=新的值,欄位2=新的值,… where 條件

五、洗掉sql陳述句:

1、洗掉一條記錄:delete from 表名 where 條件
2、洗掉表中所有資料:truncate table 表名

六:查詢sql陳述句:

1、查詢表中所有資料:
select * from 表名
2、查詢表中id為1的資料:
select * from 表名 where id=1
3、查詢表中某個欄位，條件為id=1:
select 某欄位 from 表名 where id=1
4、查詢某個范圍的記錄:
select * from 表名 where 查詢條件 limit 位置偏移量,行數
注:常作用于分頁查詢
位置偏移量:從第幾行開始查詢，從0開始
行數:查詢幾行
5、查詢表中是否存在某條資料:
select count(1) as 別名 from 表 where 條件
select count(*) as 別名 from 表 where 條件
如果存在，結果為1，否則結果為0
6、一堆函式:
avg() —回傳某欄位的平均值
max() —回傳某欄位的最大值
min() —回傳某欄位的最小值
sum() —回傳某欄位的和
例:select avg(某欄位) from 表名;
7、關聯查詢
(1)左關聯:
select 要查詢的列 from 表1 left join 表2
on 表1.關聯列 = 表2.關聯列
where 條件;
注:
右關聯:將上述sql陳述句中的left改成right
中間關聯:將上述sql陳述句中的left改成inner
(2)、直接關聯:
select 要查詢的列 from 表1,表2
where 條件 and 表1.關聯列 = 表2.關聯列;

8、子查詢:
(1)、語法:select * from 表名
where 欄位1 比較運算子 (子查詢);
例:查詢成績大于吳春雷的成績資訊
select * from mark where grade >
(select grade from mark
where username = ‘wcl’);
(2)、語法:select * from 表名
where 欄位1 in (子查詢);
例:查詢成績在50~61范圍內的成績資訊
select * from mark
where grade in
(select grade from mark
where grade <= 61 and grade >= 50);
9、模糊查詢:%可以匹配多個字符，_只匹配一個字符
語法:select * from 表 where 某欄位 like ‘%字符1%’;
注:查詢某欄位存在字符1的資料
10、排序:
語法:select * from 表 order by 欄位名;
注:查詢某表所有資料，根據某欄位進行排序，默認升序排列
order by 欄位名 desc:為降序排列
11、子查詢:exists
(1)、語法:select * from 表 where exists (子查詢)
注:如果子查詢成立，才能查出結果，否則結果為空
(2)、語法:select * from 表1 where exists
(select 1 from 表2 where 表1.欄位 = 表2.欄位)
注:exists可用于多表關聯查詢，子查詢陳述句可以直接利用主查詢陳述句的表
12、case when判斷陳述句:
語法:select case 欄位名
when 欄位值1 then 回傳值1
when 欄位值2 then 回傳值2…
else 回傳值3 end 列別名
from 表名;

七、事務、視圖、索引

1、什么是事務:
事務是一種機制、一組操作序列，要么都執行，要么都不執行，是一個不可分割的邏輯元，
2、事務四大屬性:
(1)、原子性:一組操作序列作為一個整體去提交或回滾
(2)、一致性:某個正在進行的事務導致資料可能不在一致的狀態，當此事務成功完成時，資料必須再次回到一致狀態，
(3)、隔離性:對資料進行修改的所有并發事務是彼此隔離的，事務相對都是獨立的
(4)、持久性:不管系統是否發生故障，事務處理的結果都是永久的
3、如何執行事務
(1)、開始事務:begin或start transaction
(2)、提交事務:commit
(3)、回滾(撤銷)事務:rollback
注:查詢所有行程:show processlist
找到鎖死的行程，kill 鎖死行程的id
4、什么是視圖:視圖是另一種查看資料庫中一個或多個表資料的方法，視圖是一種虛擬表
注:視圖作用:
(1)、篩選所要查詢的行、列
(2)、防止未經許可的用戶訪問敏感資料
(3)、將多個物理資料表抽象為一個邏輯資料表
使用sql創建視圖:
create view 視圖名 as select陳述句;
5、什么是索引:
給某個表創建索引后，查詢效率會提高，增刪改效率會降低，有了索引后無需對整個表進行掃描，利用索引就能找到所需資料
索引分類:
普通索引、唯一索引、主鍵索引、復合索引、全文索引、空間索引
創建索引:
(1)、create unique index 索引名 on 表名 列名(長度)
(2)、CREATE TABLE 表名(
列名1 資料型別(長度),
列名2 資料型別(長度),
INDEX [索引名字] (列名1(長度))
);
洗掉索引:drop index 索引名 on 表名;

必考單詞:

主鍵約束:primary key
自動增長:auto_increment
外鍵約束:foreign key references
索引:unique
修改表名:alter table 舊表名 rename 新表名;
添加欄位:alter table 表名 add 欄位名 資料型別(長度) 約束 注釋;
修改欄位:alter table 表名 change 原欄位名 新欄位名 資料型別(長度) 約束 注釋;
模糊查詢:%可以匹配多個字符，_只匹配一個字符
開始事務:begin或start transaction
提交事務:commit
回滾(撤銷)事務:rollback
create view 視圖名 as select陳述句;