mysql數據庫操作命令集合

關系型數據庫概念：

Excel可以看做是一張一張的二維表。關系數據庫=多張表+各表之間的關系

sql概念
操作數據庫里的數據的工具

各個表之間建立聯系
關系數據庫中，如果一張表要關聯其他表，通過對應的列產生了關系。這個關系叫做聯結。

數據庫管理系統
來管理數據庫的計算機軟件叫做數據庫管理系統

數據庫的創建和刪除

show databases;  //查看已創建的數據庫
create databases test; //創建名為test的數據庫
create databases if not exists test; //如果不存在test數據庫再創建
create database test default charset utf8mb4; //創建test數據庫并指定字符集為utf8mb4
drop database test; //刪除test數據庫
drop database if not exists test //如果存在test數據就刪除
use test2； //使用其他數據庫，即切換另外的數據庫
select database(); //查看當前使用的是哪個數據庫

表操作

create table tb_user(
id int comment '編號',  //comment 為可選項
name varchar(50) comment '姓名',
age int   //注意最后一個不加逗號
) comment '用戶表';

#查詢表結構
desc tb_uset;  //查詢tu_user表的結構
#查詢指定表的建表語句
show create table tb_user; //會顯示創建表的sql語句，包括注釋

修改表名
ALTER TABLE 表名 RENAME TO 新表名;

刪除表并重新創建該表
TRUNCATE TABLE 表名

字段操作

字段添加
ALTER TABLE 表名 ADD 字段名 類型(長度) [注釋] [約束]; 

字段刪除
ALTER TABLE 表名 DROP 字段名；

插入數據

#給指定字段插入數據
INSERT into students(id,`name`,class,gender) VALUES(11,'張三',3,4);
#給全部字段添加數據
INSERT INTO 表名 VALUES(值1,值..);
#批量添加數據
INSERT INTO 表名(字段名1,字段名2, ..) VALUES (值1,值2, ..),(值1,值2..),(值1,值..);
INSERT INTO 表名 VALUES(值1,值...),(值1, 2值...), (值1,值...);

修改數據

UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,...[WHERE  條件];
#示例
update student set name = 'zhangsan' where id = 1; //將id=1的學生姓名修改為zhangsan
注意：如果不加where條件語句，操作的范圍為整個表

刪除數據

DELETE FROM 表名 [WHERE 條件];
注意：delete語句不加條件會刪除整張表
         delete語句不能刪除某一個字段的值（可以使用update）

查詢數據

#查詢多個字段
SELECT 字段1, 字段2,字段3... FROM 表名;
SELECT * FROM 表名;
#設置別名
SELECT 字段1 [AS 別名1],字段2 [AS 別名2] ... FROM 表名;
#去除重復記錄
SELECT DISTINCT 字段列表 FROM 表名;

#條件查詢
SELECT 字段列表 FROM 表名 WHERE 條件列表;
#條件
<>或!= 不等于
BETWEEN .. AND... 在某個范圍之內(含最小、 最大值) 在in之后的列表中的值，多選一
LIKE占位符         模糊匹配(匹配單個字符，%匹配任意個字符)
IS NULL           是NULL
#查詢用戶表名字兩個字的用戶信息
select * from tb_user where name like '__';
#查詢用戶表id以1結尾的用戶信息
select * from tb_user where id like '%1';

#聚合查詢
將一列數據作為一個整體,進行縱向計算。
#常見聚合函數
count 統計數量。
max   最大值
min   最小值
avg   平均值
sum   求和
#語法 
SELECT 聚合函數(字段列表) FROM 表名;

#分組查詢
SELECT 字段列表 FROM 表名 [WHERE條件] GROUP BY 分組字段名 [ HAVING分組后過濾條件];
#where 與having區別
1執行時機不同: where是分組之前進行過濾，不滿足where條件,不參與分組;而having是分組之后對結果進行過濾。
2判斷條件不同: where不能對聚合函數進行判斷，而having可以。
注意
執行順序: where >聚合函數> having.
分組之后，查詢的字段-般為聚合函數和分組字段，查詢其他字段無任何意義。
#查詢年齡小于45的員工,并根據工作地址分組,獲取員工數量大于等于3的工作地址
seLect workaddress, count(*) address_ count from emp where age < 45 group by workaddress having
3.查詢年齡小于45的員工,并根據工作地址分組,獲取員工數量大于等于3的工作地址
seLect workaddress, count(*) address_ count from emp where age
> 45 group by workaddress having address_ count >= 3;

DML實例

#修改表
INSERT into students(id,`name`,class,gender) VALUES(12,'張三',3,4);
#將所有人的姓名都改為李四，謹慎操作
UPDATE students SET `name` = '李四';
#刪除表中所有數據
DELETE FROM students;

#查詢students表中所有人姓名并給name起中文別名

SELECT `name` AS '姓名' FROM students;

#查詢students表中所有人姓名并給name起中文別名（不重復顯示相同姓名）

SELECT DISTINCT `name` AS '姓名' FROM students;

數據類型

INT整型 BIGINT長整型 REAL 浮點型 DOUBLE浮點型
DECIMAL(M,N)高精度小數例：DECIMAL(20,10)表示一共20位，其中小數10位
CHAR(N) 定長字符串存儲指定長度的字符串，例CHAR(100)總是存儲100個字符的字符串
VARCHAR(N) 變長字符串存儲可變長度的字符串，例如，VARCHAR(100)可以存儲0~100個字符的字符串
BOOLEAN 布爾類型存儲True或者False
DATE 日期類型存儲日期，例如，2018-06-22
TIME 時間類型存儲時間，例如，12:20:59
DATETIME 日期和時間類型存儲日期+時間，例如，2018-06-22 12:20:59

　　主鍵：關系表中任意兩條記錄不能重復。通過某個字段唯一區分出不同的記錄，這個字段被稱為主鍵
　　聯合主鍵：兩個或更多的字段都設置為主鍵，這種主鍵被稱為聯合主鍵。對于聯合主鍵，允許一列有重復，只要不是所有主鍵列都重復即可。

　　外鍵：

#以學生表為例：
id class_id name    other columns...
1    1     小明    ...
2    1     小紅    ...
5    2     小白    ...
在students表中，通過class_id的字段，可以把數據與另一張表關聯起來，這種列稱為外鍵。

外鍵通過定義外鍵約束實現：
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);
外鍵約束的名稱fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作為外鍵，REFERENCES classes (id)指定了這個外鍵將關聯到classes表的id列（即classes表的主鍵）。

通過定義外鍵約束，關系數據庫可以保證無法插入無效的數據。即如果classes表不存在id=99的記錄，students表就無法插入class_id=99的記錄。
由于外鍵約束會降低數據庫的性能，大部分互聯網應用程序為了追求速度，并不設置外鍵約束，而是僅靠應用程序自身來保證邏輯的正確性。這種情況下，class_id僅僅是一個普通的列，只是它起到了外鍵的作用而已。
要刪除一個外鍵約束，也是通過ALTER TABLE實現的：
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;
注意：刪除外鍵約束并沒有刪除外鍵這一列。刪除列是通過DROP COLUMN ...實現的。

索引：關系數據庫中對某一列或多個列的值進行預排序的數據結構

#對score列創建索引
ALTER TABLE students
ADD INDEX idx_score (score);
#索引有多列的情況
ALTER TABLE students
ADD INDEX idx_name_score (name, score);

#列添加一個唯一索引
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

#只對某一列添加一個唯一約束而不創建唯一索引
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

查詢數據

#查詢數據庫表的數據
SELECT 字段1，字段2，字段3 ... FROM 表名；//查詢所選的字段
SELECT * FROM <表名> //查詢所有字段

#設置別名
SELECT 字段1 [AS 別名1],字段2[AS 別名2] ... FROM 表名; // as可以省略
例：select workaddress '工作地址' from emp；
#去除重復記錄
SELECT DISTINCT 字段列表 FROM 表名；
#條件查詢的語法
SELECT * FROM <表名> WHERE <條件表達式>
#-- 按AND條件查詢students成績大于80性別為男:
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
#-- 按OR條件查詢students:
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
#-- 按NOT條件查詢students:
SELECT * FROM students WHERE NOT class_id = 2;
#-- 按多個條件查詢students:
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
#-- 查詢姓名為兩個字的學生（lile表示占位符，'_'匹配單個字符，%匹配任意字符）
select * from emp where name like '__';
#-- 查詢年齡等于18或20或22的學生
select * from students where age = 18 or age = 20 or age = 22；
select * from students where age in(18,20,22); //和上面語句等價

#投影查詢:SELECT 列1, 列2, 列3 FROM ...，讓結果集僅包含指定列。
SELECT id, score, name FROM students;
#-- 使用投影查詢，并將列名score重命名points：
SELECT id, score points, name FROM students;
#-- 使用投影查詢+WHERE條件：
SELECT id, score points, name FROM students WHERE gender = 'M';

#排序 ORDER BY，根據某個條件將查詢結果排序
SELECT id, name, gender, score FROM students ORDER BY score;
#倒序排序，-- 按score從高到低
SELECT id, name, gender, score FROM students ORDER BY score DESC;
#-- 按score, gender排序（成績相同時女性在前）:
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender DESC;
#-- 帶WHERE條件的ORDER BY:
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

#分頁查詢LIMIT <N> OFFSET <M>
#-- 一頁三條數據，從第一條開始(索引是從零開始的)
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

#聚合查詢
#使用聚合查詢students表一共有多少條記錄
SELECT COUNT(*) FROM students;
#使用聚合查詢并設置結果集的列名為num:
SELECT COUNT(*) num FROM students;
#-- 使用聚合查詢并設置WHERE條件:
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
#使用聚合查詢計算男生平均成績:
SELECT AVG(score) average FROM students WHERE gender = 'M';
#查詢年齡最小的學生
select min(age) from students;
#查詢二班學生年齡和
select sum(age) from students where class = 2;

#分組查詢
#語法：SELECT 字段列表 FROM 表明 [WHERE 條件] GROUP BY 分組字段名 [HAVING 分組后的過濾條件];
#根據性別分組，統計男生和女生人數
select gender,count(*) from emp group by gender; //不加gender的話不會在統計的人數前加‘男’，‘女’；
#查詢年齡小于45的員工，并根據工作地址分組，獲取學生數大于3的城市
select workaddress, count(*) address_count from students where age < 45 group by workaddress having address_count >= 3; //address_count是count(*)的別名

sql語句執行順序 1FROM 2WHERE 3GROUP BY 4HAVING 5SELECT 6 ORDER BY 7LIMIT

DCL(數據控制語言)

函數
#創建用戶tom,只能夠在當前主機localhost訪問；
create user 'tom'@'localhost' identified by '123456';
#創建用戶bob，可以在任意主機訪問
create user ‘bob’@‘%’ identified by ‘123456’；
#刪除用戶
drop user '用戶名'@'主機名';

#外鍵約束
#創建表時添加外鍵
CREATE TABLE 表名(
字段名數據類型,
[CONSTRAINT] [外鍵名稱] FOREIGN KEY(外鍵字段名）REFERENCES 主表（主表列名）//主表列名不是主鍵無法添加外鍵
）；
#創建表后添加外鍵
ALTEE TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY(外鍵字段名) REFERENCES 主表（主表列名);
#刪除外鍵
ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;

事務（Transaction）指一個操作，由多個步驟組成，要么全部成功，要么全部失敗。數據庫事務（Database Transaction）是指對數據庫的一系列操作組成的邏輯工作單元。

臟讀（Dirty Read）
讀取未提交數據。
A 事務讀取 B 事務尚未提交的數據，此時如果 B 事務發生錯誤并執行回滾操作，那么 A 事務讀取到的數據就是臟數據。
不可重復讀（Non-repeatable Read）
前后多次讀取，數據內容不一致。
A 事務在 B 事務開始前讀和 B 事務結束后讀的數據不一樣，因為數據被事務 B 給修改了。

幻讀（Phantom Read）
一個行出現在查詢結果集中，但不在較早查詢的結果集中。

事務 A 在讀取某個范圍內的記錄時，事務 B 在該范圍內插入了新記錄，事務 A 再次讀取該范圍內的記錄時，會產生幻行?；米x比不可重復讀取更難防范，因為鎖定第一個查詢結果集中的所有行并不能阻止導致幻像出現的更改。
更新丟失（Update Lost）
指更新結果被其他事務覆蓋。兩個事務同時讀取相同數據并分別修改后，一個事務的修改覆蓋了另一個事務的修改。這是因為系統沒有執行任何鎖操作，因此并發事務沒有被隔離開來。
第一類更新丟失（回滾丟失）。
比如 A 事務對某一列 +1，B 事務對某一列 +2。A 事務事務提交后，B 事務回滾了，導致 A 事務更新丟失。
第二類更新丟失（提交丟失）。
比如 A 事務對某一列 +1，B 事務對某一列 +2，A B 事務執行完成后正常預期結果是某一列值被 +3，但是 B 事務的結果覆蓋了 A 事務，導致結果只被 +2，A 事務的更新丟失了。

MySQL 可以通過以下辦法避免更新丟失。
提升隔離級別至串行化（Serializable）
使用樂觀鎖，比如版本號的 CAS（Compare And Swap）
使用悲觀鎖，比如排他鎖（X 鎖）SELECT xxx FOR UPDATE

數據庫事務的使用
把轉賬操作作為一個顯式事務

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

主動讓事務失敗。可以用 ROLLBACK 回滾事務，整個事務會失敗。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

posted @ 2023-09-07 14:50 ddup123 閱讀(68) 評論(0) 收藏舉報

刷新頁面返回頂部

ddrm

mysql數據庫操作命令集合

公告