資料庫基礎操作 part1
- 2022 年 9 月 5 日
- 筆記
- 資料庫的開發和優化
初識資料庫
資料庫相關概念
資料庫管理軟體: 本質就是一個C/S架構的套接字程式
服務端套接字 客戶端套接字
作業系統: Linux 作業系統: 隨意
電腦(本地文件) 電腦硬體
應用流程: 服務端發送請求語句給服務端. 服務端從本地文件中讀取數據返回給客戶端
例如:
關係型資料庫管理軟體: 優點是表之間有關係
mysql Oracle
非關係型資料庫管理軟體: 一般都是以key: value格式儲存數據. 所以查詢較快. 儲存在記憶體中
redis memcache mongodb
sql語句: 套接字管理軟體的作者為使用者規定的命名規範
資料庫核心概念:
數據: 事物的狀態
記錄: 文件中的一條資訊
表: 一個文件
庫: 文件夾
資料庫管理軟體: 套接字程式: 服務端-mysqld 客戶端-mysql
資料庫伺服器: 運行mysqld的計算器
初識sql語句
1. 庫 -> 文件夾
增
create database day01 charset utf8mb4;
改
alter database day01 charset gbk;
查
show databases;
show create database day01;
刪
drop database day01;
creat database day01;
2. 表 -> 文件
use day01;
select database();
增
create table t1(id int, name varchar(16));
改
alter table t1 rename t2;
alter table t1 modify name varchar(10);
查
show tables;
desc t2;
刪
drop table t2;
3. 記錄 -> 文件中的每一行內容
create database day02;
use day02;
create table t1(id int, name varchar(16));
增
insert t1 values
(1, "Maxs_hu"),
(2, "Mokeke"),
(3, "xiaoergu")
改
update t1 set name = "lili" where id = 2;
查
select * from t1;
select name from t1;
select name from t1 where id >= 2;
刪
delete from t1 where id = 2;
了解知識點. sql語句分三種:
1. DDL語句 資料庫定義語言: 資料庫. 表. 視圖. 索引. 儲存過程. 例如: create drop alter
2. DML語句 資料庫操縱語言: 插入數據insert. 刪除數據delete. 更新數據update. 查詢數據select
3. DCL語句 資料庫控制語言: 例如控制用戶的訪問許可權grant. revoke
資料庫表的詳細操作
表的詳細操作(增刪改查)
表----------------文件
儲存引擎engine -> show engines;
myisam 數據存在硬碟中
innodb 數據存在硬碟中(default)
blackhole 黑洞. 不存數據
memory 數據存在記憶體中. 關閉伺服器數據就消失了
create table t1(id int)engine=innodb;
innodb儲存引擎: 本質就是一段處理程式
1. 創建表
create table t1(
id int,
name varchar(8) not null
);
注意: 1. 在同一張表中. 欄位名不能相同
2. 寬度和約束條件可選
3. 欄位名和數據類型是必須的
4. 表的最後一個欄位不要加逗號
2. 修改表
1. 修改表名
alter table t1 rename t2;
2. 增加欄位
alter table t1 add age int [完整性約束條件...], -> 可以添加多條欄位
sex varchar(10) [完成性約束條件...];
alter table t1 add age int [完整性約束條件...] first; -> 將欄位添加至第一位
alter table t1 add age int [完整性約束條件...] after name; -> 將欄位添加在某個欄位後面
3. 刪除欄位
alter table t1 drop age;
4. 修改欄位
alter table t1 modify name varchar(10) [完整性約束條件...] -> modify不能修改欄位名字
alter table t1 change 舊欄位名 新欄位名 新數據類型 [完整性約束條件...] -> 可以修改任何條件
3. 複製表
create table t2 select id, name, age from test.t1; -> 將t1中查看的數據複製給t2
create table t2 select id, name, age from test.t1 where 1 < 0; -> 只創建欄位名. 沒有數據
表欄位之數據類型:
create table t3(id int); -> 整型
create table t10(x float(255, 30)); -> 常用
create table t11(y double(255, 30)); -> 常用
create table t12(z decimal(65, 30)); -> 最精準
insert into t10 values(1.11111111111111111111111111111111)
insert into t11 values(1.11111111111111111111111111111111)
insert into t12 values(1.11111111111111111111111111111111)
時間類型:
create table student(
id int,
name varchar(16),
birth date,
class_time time,
reg_time datetime,
born_year year
);
insert student values
(1, 'egon', '1993-01-23', '08:30:00', "2020-3-23 09:30:21", "1993") -> 一一對應
create table t13(x datatime); -> 範圍廣
create table t14(y timestamp); -> 節省空間. 且不傳值會自動更新current_timestamp
create table t15(
id int,
name varchar(16),
commit_time timestamp
);
枚舉和集合:
create table t18(
id int,
name varchar(16),
gender enum("男", "女", "未知"), -- 選一個插入. 如果插入值不在列表中. 則會顯示報錯
hobbies set("play", "music", "read", "movie") -- 可以多選插入. 如果插入在列表中不存在.則會報錯
);
insert into t18 values (1, "Maxs_hu", "男", "play,music,movie,program") -- set傳入數據逗號後面不可加空格
補充:
char和varchar的區別:
1.char儲存字元串會以固定長度儲存(長度不足補空格). 而varchar則會以位元組寬度+1(頭)去儲存. -> varchar減小io
相比倆說更加節省空間. 但是當字元串長度等於字元長度的時候. varchar會因為頭佔一個位元組比char多一個位元組
2. select name.char_length from t18; -- 查看字元數
3. 使用where查詢的時候. 不會在意字元後面跟的空格. 而使用like模糊查詢的時候嚴格按照字元查詢 _: 任意字元 %:任意個數字元
表的約束條件和表關係的建立
1. 表的約束條件:
# primary key: 不為空且唯一
create table t5(
id int,
name varchar(10),
primary key(id, name) -- 聯合主鍵: 不能重複
);
insert into t5 values(1, "Maxs_hu");
insert into t5 values(1, "tom");
insert into t5 values(1, "Maxs_hu"); -- 會報錯. 因為主鍵不能重複
# auto_increment: 自增
set global auto_increment_increment = 5; -- 設置全局自增步長
set global auto_increment_offset = 3; -- 設置開始第一個id為3
create table t6(
id int primary key auto_increment,
name varchar(16)
);
# not null 和 default組合: 將沒有傳值的默認為0
create table t7(
id int not null default 0,
name varchar(16)
);
# unique: 唯一的 uni
create table t8(
id int unique,
name varchar(16)
);
# not null 和 unique 組合的化學反應: 變成主鍵pri
create teble t9(
id int not null unique,
name varchar(16)
);
desc t9;
2. 表關係建立
---------------多對一: 強耦合
# foreign key: 外鍵
create table department( -- 先創建父表
id int primary key,
name varchar(20) not null
);
create table employee( -- 關聯父表(department主鍵id), 同步更新, 同步刪除
id int primary key,
name varchar(20) not null,
dpt_id int,
foreign key(dpt_id) references department(id)
on delete cascade
on update cascade
);
insert into department values -- 先往父表department中插入記錄
(1, "敘利亞戰區"),
(2, "俄羅斯戰場"),
(3, "東部戰場");
insert into employee values -- 再往字表employee中插入記錄
(1, "Maxs_hu", 1),
(2, "Mokeke", 2),
(3, "longge", 2),
(4, "xiaoergu", 3),
(5, "alex", 3);
delete from department where id = 3; -- 刪除父表department. 字表employee中對應的記錄會跟著刪除
select * from employee;
update department set name = "東部戰區" where id = 2; -- 修改父表中department. 表employee中記錄也會跟著改變
select * from employee;
--------------多對多: 常用底層架構
create table book( -- 先創建book父表
id int primary key,
name varchar(20) not null
);
create table author( -- 先創建author父表
id int primary key,
name varchar(16) not null
);
create table author2book( -- 在創建關聯表
id int primary key auto_increment,
author_id int not null,
book_id int not null,
foreign key(book_id) references book(id) -- 關聯book表格
on delete cascade
on update cascade,
foreign key(author_id) references author(id) -- 關聯author表格
on delete cascade
on update cascade
);
insert into book values -- 先向父表中插入數據
(1, "葵花寶典"),
(2, "九陰白骨抓"),
(3, "輕功水上漂"),
(4, "辟邪劍法"),
(5, "九陰真經"),
(6, "掏奶龍抓手");
insert into author values -- 先向父表中插入數據
(1, "Maxs_hu"),
(2, "mary"),
(3, "Mokeke"),
(4, "tom");
insert into author2book(author_id, book_id) values
(1, 6),
(3, 5),
(2, 4),
(1, 2),
(4, 3),
(3, 1),
(2, 6),
(3, 1),
(4, 5);
可以通過update來操作多對多表格去聯動數據
建立表關係順序: 多對多 > 多對一 > 一對一
表詳細操作知識點總結
總結:
1.約束條件
not null
default
unique
primary key
auto_increment
unsigned -- 寫在int後面. 確保數值大於0
foreign key -- 外鍵
2. 表之間建立關係
---多對多
emp dep
emp2dep -- 建立中間表存父表id
emp2dep: fk(dep_id) -> dep(id) fk(emp_id) -> emp(id)
---多對一
emp: fk(emp_id) -> dep(id)
dep -> 父表
---一對一
fk(dep_id)+uk
