MySQL對數據 對錶 對數據操作
- 2020 年 12 月 4 日
- 筆記
————恢復內容開始————
MySQL
資料庫的操作
| 創建 | create database 資料庫名; |
|---|---|
| 指定字符集 | create database 資料庫名 character set 字符集; |
| 刪除資料庫 | drop datebase 資料庫名; |
| 修改字符集 | alter database 資料庫名 default character set 字符集; |
| 創建查詢是否存在 | create database if not exists 資料庫名; |
| 查看資料庫 | show databases; |
| 查看在使用的資料庫 | select database(); |
| 使用資料庫 | use 資料庫名; |
| 查看資料庫創建 | show create database 資料庫名; |
單表操作 增刪改查
| 創建表 | create table 表名(欄位名 欄位類型(欄位長度),···); |
|---|---|
| 創建相同的表 | create table 新表名 like 舊錶名; |
| 添加欄位 | alter table 表名 add 欄位名 欄位類型(欄位長度); |
| 刪除表 | drop table 表名; |
| 判斷存在並刪除 | drop table if exists 表名; |
| 刪除欄位 | alter table 表名 drop 欄位名; |
| 修改欄位 | alter table 表名 change 舊欄位名 新欄位名 欄位類型(欄位長度); |
| 修改字符集 | alter table 表名 character set 字符集; |
| 修改表名 | rename table 舊錶名 to 新表名; |
| 修改欄位類型 | alter table 表名 modify 欄位名 新類型(類型長度); |
| 快捷修改字符集 | set names 字符集; |
| 查看資料庫中的表 | show tables; |
| 查看錶結構 | desc 表名; |
| 查看錶的創建 | show create table 表名; |
數據
| 插入數據 | insert into 表名(欄位名1,…)value(值1,…); |
|---|---|
| 插入全部 | insert into 表名(欄位名1,…最後欄位)value(值1,…最後值); |
| 蠕蟲複製 | create table 表A like 被複制的表; insert into 新表 select * from 舊錶; |
| 建表時添加約束 | create table 表名(欄位名 欄位類型(欄位長度) 欄位約束區,···，全欄位約束區(欄位名)); |
| 插入全部 | insert into 表名 value(值1,…最後值); |
| 有條件刪除數據 | delete from 表名 where 欄位名=值; |
| 摧毀表再創建 | truncate table 表名; |
| 無條件修改 | update 表名 set 欄位名=新值; |
| 有條件修改 | update 表名 set 欄位名=新值,欄位名=新值 where 欄位=值; |
| 查詢所有數據 | select * from 表名; |
| 查詢部分 | select 欄位名1,欄位名2… from 表名; |
| 去重查詢 | select distinct 欄位名… from 表名; |
| 結果參與運算 | select 欄位名+固定值 from 表名; |
| 欄位間運算 | select 欄位名1+欄位名2 別名 from 表名; |
| null值運算 | select 欄位名1 ifnull(欄位名2,默認值) 別名 from 表名; |
| 條件查詢 | select 欄位名 from 表名 where 條件; and or notnull |
| 範圍查詢 | select 欄位名 from 表名 where 欄位名 between 值1 and 值2; |
| 模糊查詢 | select * from 表名 where 欄位名 like “通配符欄位”; _ % |
| 組合排序 | select 欄位名 from 表名 where 條件 order by 欄位名1 asc/desc,欄位名2 asc/desc; |
| 聚合函數 | select 聚合函數(欄位名) from 表名;select 聚合函數 from 表名; |
| 聚合函數 | count() 計數 ifnull (欄位,替換為) sum()求和 max () 最大值 min() 最小值 avg(*)平均值 cast(avg(欄位名)as decimal(5,2))保留幾位小數 |
| 分組 | select 欄位名 from 表名 where 條件 group by 欄位名 having 條件; |
| limit分頁 | select 欄位名 from 表名 where語句 group by語句 having語句 order by語句 limit 開始條目，每頁條目; |
多表查詢
| 隱內連 | select 欄位名 from 表1,表2 where 條件 |
|---|---|
| 顯內連 | select 欄位名 from 表1 join 表2 on 條件 |
| 左外連 | select 欄位名 from 表1 left join 表2 on 條件(以表1為主，表2沒內容填null) |
| 右外連 | select 欄位名 from 表1 right join 表2 on 條件(以表2為主，表1沒內容填null) |
子查詢
| 單列單行 | 結果作為篩選條件，放在where後面 |
|---|---|
| 單列多行 | 結果作為篩選條件，放在where後面 |
| 多列多行 | 結果作為查詢的表，放在from後面 |
事務
| 開啟 | start transaction 開啟手動事務，不提交和回滾事務不真正執行，而是放到快取區 |
|---|---|
| 提交 | commit將快取區中的事務統一執行 |
| 回滾 | rollback回到事務語句開始時的狀態 |
| 回滾點 | savepoint 回滾點名字，回到回滾點語法：rollback to 回滾點名 |
| 查詢 | show variables like “%commit%” |
| 關閉自動 | set autocommit = 0,設置為1為自動 |
事務的特性
| 1原子性 | 事務不可分割，一個事務中的操作要麼都發生，要麼都不發生 |
|---|---|
| 2一致性 | 事務前後數據的完整性必須保持一致 |
| 3隔離性 | 多用戶並發訪問資料庫時，一個用戶的事務不能唄其他用戶的事務所干擾，多個並發事務之間要相互格力，不能互相影響 |
| 4持久性 | 一個事物一旦提交，對資料庫中的改變是永久性的，接下來資料庫發生故障也不會對其有任何影響 |
隔離級別
| 讀未提交 | read uncommitted 隔離級別最低，效率最高 |
|---|---|
| 讀已提交 | read committed 避免臟讀，Oracle 和 SQL Server |
| 可重複讀 | repeatable read 避免不可重複讀 MySQL |
| 串列化 | serializable 可避免一切並發訪問問題，效率最低 |
| 查詢 | show variables like “%isolation%” |
|---|---|
| 設置 | set global transaction isolation level 級別 |
概念
| 一對一 | 操作多的表加外鍵 |
|---|---|
| 一對多 | 多表加外鍵 |
| 多對多 | 建立中間表，兩個外鍵 |
| 外鍵約束 | 從表的某個欄位引用主表的主鍵（外鍵可以重複） |
| 建表時 | constraint 外鍵名 foreign key(外鍵欄位） references 主表名(主表主鍵) |
| 後增加 | alter table 從表 add constraint 外鍵名 foreign key(外鍵欄位)references 主表(主表主鍵) |
| 外鍵刪除 | alter table 從表 drop foreign key 外鍵名 |
| 第一 | 每個欄位不可再拆分 |
| 第二 | 表內有主鍵，一張表只描述一件事 |
| 第三 | 從表的外鍵必須使用主表的主鍵 |
| 產生 | 查詢多個表時，會把每個表數據與另一個表數據一一對應產生新的表 |
| 消除 | 找到兩個表的關聯條件並進行篩選 |
用戶許可權
| 創建用戶 | create user ‘用戶名’@’主機名’ identified by ‘密碼’; |
|---|---|
| 查詢許可權 | show grants for ‘用戶名’@’主機名’ |
| 授權用戶 | grant create,alter,drop,insert,update,delete,select on 資料庫名.表名 to ‘用戶名’@’主機名’;(可以用all代表所有許可權，*.*代表所有資料庫和所有表) |
| 撤銷授權 | revoke 許可權1,… on 資料庫名.表名 from ‘用戶名’@’主機名'(可以用all代表所有許可權，*.*代表所有資料庫和所有表) |
| 刪除用戶 | drop user ‘用戶名’@’主機名’; |
| 修改普通密碼 | set password for ‘用戶名’@’主機名’ = password(‘新密碼’); |
| 修改管理員密碼 | 先退出登錄，mysqladmin -uroot -p password 新密碼(沒有引號) 按提示輸入舊密碼 |
————恢復內容結束————
