MySQL 基礎之 數(shù)據(jù) 操作（增刪改查）
老師筆記
MySQL 數(shù)據(jù)操作 DML
DML是用來對數(shù)據(jù)庫里的數(shù)據(jù)進行操作的語言。DML需要 commit扫俺。
當使用 mysql 客戶端操作數(shù)據(jù)庫時苍苞，這些語句都會自動提交，不用執(zhí)行 commit牵舵。
插入數(shù)據(jù)（增加）
基礎語法
/*插入一條數(shù)據(jù)*/
insert into 表 (列名,列名...) values (值,值,值...)
/*一次插入多條數(shù)據(jù) */
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
/*以下為擴展*/
/*方式三*/
insert into 表1 (列名,列名...) select 列名,列名... from 表2
注意
- 按“行”插入
- “字段”和“值”之間柒啤，一一對應
- 值的形式：數(shù)字直接寫，字符串和時間加單引號畸颅，但如果是函數(shù)值，則不能加引號
- auto_increment方援， timestamp等字段無需插入
例
insert into student (name, age, phone) values('王麻子', 32, '13141098899');
insert into student (
id,name, age, phone
) values(
2,'九筒', 32, '13721096541'),
(3,'六子', 18, '13139192657'),
(4,'馬邦德', 37, '15855996688');
修改數(shù)據(jù)
修改
update 表名 set 字段名1=值表達式1没炒，字段名2=值表達式2，....[where條件] [order排序] [limit限定];
注意
- 以“行”為單位進行的犯戏，可以指定只更新其中的部分字段
- 其他限定遵循insert語法
實例表創(chuàng)建
班級表
CREATE TABLE class (
id int NOT NULL AUTO_INCREMENT,
name varchar(10) ,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
老師表
CREATE TABLE teacher (
id int NOT NULL AUTO_INCREMENT,
name varchar(10) ,
age int ,
phone char(11) ,
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
student學生表
create table student(
id int auto_increment primary key,
name varchar(10),
age int,
class_id int,
foreign key(class_id) references class(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
多對多關(guān)系表
create table class2teacher(
id int auto_increment primary key,
class_id int,
teacher_id int,
foreign key(class_id) REFERENCES class(id),
foreign key(teacher_id) references teacher(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
實例表數(shù)據(jù)添加
class(班級表)
insert into class(name) values
("云計算1810"),
("云計算1901"),
("云計算1902");
teacher(老師表)
insert into teacher(name, age, phone) values
("奇哥", 18, "13733878989"),
("強哥", 28, "15633878989"),
("磊哥", 30, "13933878989"),
("閆老師", 18, "13633878989");
student(學生表)
insert into student(name, age, class_id) values
("黛玉", 18, 3), ("欽文", 19, 3),("馬邦德", 30, 1),
("九筒", 48, 1),("六子", 36, 2),("湯師爺", 18, 2),
("麻匪", 18,2),
("黛玉", 18,2);
class2teacher(班級到老師多對多關(guān)系表)
insert into class2teacher(class_id,teacher_id) values
(1,1),(1,2),(2,1),(2,2),(2,3),(3,1),(3,3);
查詢
單表查詢
**基礎查詢
select * from 表
select * from 表 where id > 2
select id,name,age as gg from 表 where id > 2
**高級查詢
a送火、條件
select * from 表 where id > 1 and name != '王麻子' and age = 18;
select * from 表 where id between 5 and 16;
select * from 表 where id in (11,22,33)
select * from 表 where id not in (11,22,33)
select * from 表 where id in (select id from 表)
b、通配符
select * from 表 where name like 'sha%' - sha開頭的所有（多個字符串）
select * from 表 where name like 'shar_' - sha開頭的所有（一個字符）
c先匪、限制
select * from 表 limit 5; - 獲取前 5 行
select * from 表 limit 0,2; - 從第 1 行開始, 取出 2 行, 包含第 1 行
select * from 表 limit 2 offset 0 - 從第 1 行開始, 取出 2 行, 包含第 1 行
d种吸、排序
select * from 表 order by 列 asc - 根據(jù) “列” 從小到大排列
select * from 表 order by 列 desc - 根據(jù) “列” 從大到小排列
select * from 表 order by 列1 desc,列2 asc - 根據(jù) “列1” 從大到小排列，如果相同則按列2從小到大排序
e呀非、分組
select age from 表 group by age
select age,id from 表 group by age,id
select age,id from 表 where id > 10 group by age,id order by id desc
select age,id,count(*),sum(age),max(age),min(age) from 表 group by age,id
select age from 表 group by age having max(id) > 10
特別的：group by 必須在where之后坚俗，order by之前
f、嵌套查詢
select * from (select name from t1 where age>18 and age < 25 order by id desc limit 2 ) as tt order by id;
多表查詢
連表
-- 查詢到 每個班級的所有學員的姓名
select class.name, student.name
from class, student
where
student.class_id=class.id;
-- 查詢到 云計算1901班級的所有學員的姓名
select class.name, student.name
from class, student
where
class.name="云計算1901"
and
student.class_id=class.id;
-- 查詢到 馬邦德 是哪個班級的
select student.name as 學員姓名,class.name as 班級姓名
from student,class
where student.name='馬邦德'
and
student.class_id=class.id;
-- 查詢老師 奇哥 都負責哪些班級
select t.name,c.name
from teacher as t, class as c ,class2teacher c2t
where t.name='奇哥'
and c2t.teacher_id=t.id
and c2t.class_id=c.id;
Mysql JOIN 語法
/*無對應關(guān)系則不顯示*/
select A.class_name, B.name
from class as A, teacher as B
Where A.id = B.class_id
/* 內(nèi)連接 無對應關(guān)系則不顯示*/
select A.class_name, B.name
from class as A inner join teacher as B
on A.id = B.class_id
/* 左連接 左邊的表(A)所有顯示岸裙，如果右邊的表(B)中無對應關(guān)系猖败，則值為null*/
select A.class_name, B.name
from class as A left join teacher as B
on A.id = B.class_id
/* 右連接 右邊的(B)表所有顯示，如果左邊的表(A)中無對應關(guān)系降允，則值為 NULL*/
select A.name, B.name
from class as A right join teacher as B
on A.id = B.class_id
例子
select class.name, student.name
from class join student
on student.class_id=class.id;
select class.name,student.name
from class inner join student
on student.class_id=class.id
where class.name="云計算1901";
select teacher.name, class.name
from class, teacher, class2teacher
where teacher.name = "奇哥"
and class2teacher.teacher_id=teacher.id
and class2teacher.class_id = class.id;
select teacher.name, class.name
from class join teacher join class2teacher
on class2teacher.teacher_id=teacher.id and class2teacher.class_id = class.id
where teacher.name = "奇哥";
刪除數(shù)據(jù)
delete from 表名 [where 條件] [order by 排序的字段 [desc 取反]] [limit 限定的行數(shù)];
注意
- “以行為單位”刪除
- 刪除語句中恩闻，where條件如果不寫，則就刪除了所有數(shù)據(jù)
- order排序子句用于設定刪除數(shù)據(jù)的先后順序
- limit限定子句用于限定在設定的順序情況下刪除指定的某些行
drop,delete與truncate的區(qū)別
drop 直接刪掉表
truncate 刪除表中數(shù)據(jù)剧董，再插入時自增長id又從1開始
delete 刪除表中數(shù)據(jù)幢尚，可以加where字句, 增長 id 會繼續(xù)增長破停。
（1）DELETE語句執(zhí)行刪除的過程是每次從表中刪除一行，并且同時將該行的刪除操作作為事務記錄在日志中保存以便進行進行回滾操作尉剩。TRUNCATE TABLE則一次性地從表中刪除所有的數(shù)據(jù)并不把單獨的刪除操作記錄記入日志保存辱挥，刪除行是不能恢復的。并且在刪除的過程中不會激活與表有關(guān)的刪除觸發(fā)器边涕。執(zhí)行速度快晤碘。
（2） 表和索引所占空間。當表被TRUNCATE后功蜓，這個表和索引所占用的空間會恢復到初始大小园爷，而DELETE操作不會減少表或索引所占用的空間。drop語句將表所占用的空間全釋放掉式撼。
（3） 一般而言童社，drop > truncate > delete
（4） 應用范圍。TRUNCATE只能對TABLE著隆；DELETE可以是table和view(視圖)
（5）TRUNCATE和DELETE只刪除數(shù)據(jù)扰楼，而DROP則刪除整個表（結(jié)構(gòu)和數(shù)據(jù)）。
（6）truncate與不帶where的delete：只刪除數(shù)據(jù)美浦，而不刪除表的結(jié)構(gòu)（定義）drop語句將刪除表的結(jié)構(gòu)被依賴的約束（constrain),觸發(fā)器（trigger)索引（index);依賴于該表的存儲過程/函數(shù)將被保留弦赖，但其狀態(tài)會變?yōu)椋篿nvalid。
（7）delete語句為DML（data maintain Language),這個操作會被放到rollback segment中,事務提交后才生效浦辨。如果有相應的tigger,執(zhí)行的時候?qū)⒈挥|發(fā)蹬竖。
（8）truncate、drop是DLL（data define language),操作立即生效流酬，原數(shù)據(jù)不放到 rollback segment中币厕，不能回滾
（9） 在沒有備份情況下，謹慎使用drop與truncate芽腾。要刪除部分數(shù)據(jù)行采用delete且注意結(jié)合where來約束影響范圍旦装。回滾段要足夠大摊滔。要刪除表用drop;若想保留表而將表中數(shù)據(jù)刪除阴绢，如果于事務無關(guān)，用truncate即可實現(xiàn)惭载。如果和事務有關(guān)旱函，或老師想觸發(fā)trigger,還是用delete。
（10）Truncate table表名 速度快,而且效率高,因為:
truncate table 在功能上與不帶 WHERE 子句的DELETE語句相同：二者均刪除表中的全部行描滔。但TRUNCATE TABLE比DELETE速度快棒妨，且使用的系統(tǒng)和事務日志資源少。DELETE語句每次刪除一行，并在事務日志中為所刪除的每行記錄一項券腔。TRUNCATE TABLE通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)伏穆，并且只在事務日志中記錄頁的釋放。
（11）TRUNCATE TABLE刪除表中的所有行纷纫，但表結(jié)構(gòu)及其列枕扫、約束、索引等保持不變辱魁。新行標識所用的計數(shù)值重置為該列的種子烟瞧。如果想保留標識計數(shù)值，請改用DELETE染簇。如果要刪除表定義及其數(shù)據(jù)参滴，請使用DROP TABLE 語句。
（12） 對于由FOREIGN KEY約束引用的表锻弓，不能使用TRUNCATE TABLE砾赔，而應使用不帶WHERE子句的DELETE語句。由于TRUNCATE TABLE不記錄在日志中青灼，所以它不能激活觸發(fā)器暴心。
更新(修改) update
update 表名 set 字段名1=值表達式1，字段名2=值表達式2杂拨，....[where條件] [order排序] [limit限定];
注意
以“行”為單位進行的专普，可以指定只更新其中的部分字段
其他限定遵循insert語法
例子
刪除表，表將消失
drop table 表名 /*整個表將不復存在*/
清空表的內(nèi)容扳躬，表本身還在
delete from 表名 /*自增列的值繼續(xù)遞增脆诉，可以加 where 子句*/
truncate table 表名 /*自增列的值重新從 1 開始*/
/*刪除表中的所有數(shù)據(jù)，自增列的值繼續(xù)遞增*/
delete from tb1;
/*刪除表中的某些數(shù)據(jù)贷币，被刪除的數(shù)據(jù)的自增列的值將不會再次出現(xiàn)，自增列的值繼續(xù)遞增*/
delete from tb1 where id < 20;
清空表數(shù)據(jù)
TRUNCATE [TABLE] 表名
例子與補充
-- 復制表結(jié)構(gòu)
CREATE TABLE 表名 LIKE 要復制的表名
-- 復制表結(jié)構(gòu)和數(shù)據(jù)
CREATE TABLE 表名 [AS] SELECT * FROM 要復制的表名
-- 檢查表是否有錯誤
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
-- 優(yōu)化表
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
-- 修復表
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
-- 分析表
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
