MySQL 數(shù)據(jù)操作 DML
DML（Data Manipulation Language 數(shù)據(jù)操縱語言）
DML是用來對(duì)數(shù)據(jù)庫里的數(shù)據(jù)進(jìn)行操作的語言。DML需要 commit缨该。
插入數(shù)據(jù)（增加）
基礎(chǔ)語法
/*插入一條數(shù)據(jù)*/
insert into 表 (列名,列名...) values (值,值,值...)
/*一次插入多條數(shù)據(jù) */
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
/*以下為擴(kuò)展*/
/*方式三*/
insert into 表1 (列名,列名...) select (列名,列名...) from 表2
注意
- 按“行”插入
- “字段”和“值”之間，一一對(duì)應(yīng)
- 值的形式：數(shù)字直接寫捏萍，字符串和時(shí)間加單引號(hào)往堡，但如果是函數(shù)值，則不能加引號(hào)
- auto_increment站玄， timestamp等字段無需插入
| id | name | age | phone |
|---|---|---|---|
| 1 | 王麻子 | 32 | 13141098899 |
| 2 | 九筒 | 32 | 13721096541 |
| 3 | 六子 | 18 | 13139192657 |
| 4 | 馬邦德 | 37 | 15855996688 |
Example
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ù)
基礎(chǔ)語法
delete from 表名 [where條件] [order排序] [limit限定];
注意
- “以行為單位”刪除
- 刪除語句中按摘，where條件如果不寫包券，則就刪除了所有數(shù)據(jù)
- order排序子句用于設(shè)定刪除數(shù)據(jù)的先后順序
- limit限定子句用于限定在設(shè)定的順序情況下刪除指定的某些行
drop,delete與truncate的區(qū)別
drop 直接刪掉表
truncate 刪除表中數(shù)據(jù)纫谅，再插入時(shí)自增長(zhǎng)id又從1開始
delete 刪除表中數(shù)據(jù)，可以加where字句, 增長(zhǎng) id 會(huì)繼續(xù)增長(zhǎng)溅固。
（1）DELETE語句執(zhí)行刪除的過程是每次從表中刪除一行付秕，并且同時(shí)將該行的刪除操作作為事務(wù)記錄在日志中保存以便進(jìn)行進(jìn)行回滾操作。TRUNCATE TABLE則一次性地從表中刪除所有的數(shù)據(jù)并不把單獨(dú)的刪除操作記錄記入日志保存侍郭，刪除行是不能恢復(fù)的盹牧。并且在刪除的過程中不會(huì)激活與表有關(guān)的刪除觸發(fā)器。執(zhí)行速度快励幼。
（2） 表和索引所占空間汰寓。當(dāng)表被TRUNCATE后，這個(gè)表和索引所占用的空間會(huì)恢復(fù)到初始大小苹粟，而DELETE操作不會(huì)減少表或索引所占用的空間有滑。drop語句將表所占用的空間全釋放掉。
（3） 一般而言嵌削，drop > truncate > delete
（4） 應(yīng)用范圍毛好。TRUNCATE只能對(duì)TABLE；DELETE可以是table和view(視圖)
（5）TRUNCATE和DELETE只刪除數(shù)據(jù)苛秕，而DROP則刪除整個(gè)表（結(jié)構(gòu)和數(shù)據(jù)）肌访。
（6）truncate與不帶where的delete：只刪除數(shù)據(jù)，而不刪除表的結(jié)構(gòu)（定義）drop語句將刪除表的結(jié)構(gòu)被依賴的約束（constrain),觸發(fā)器（trigger)索引（index);依賴于該表的存儲(chǔ)過程/函數(shù)將被保留艇劫，但其狀態(tài)會(huì)變?yōu)椋篿nvalid吼驶。
（7）delete語句為DML（data maintain Language),這個(gè)操作會(huì)被放到rollback segment中,事務(wù)提交后才生效。如果有相應(yīng)的tigger,執(zhí)行的時(shí)候?qū)⒈挥|發(fā)店煞。
（8）truncate蟹演、drop是DLL（data define language),操作立即生效，原數(shù)據(jù)不放到 rollback segment中顷蟀，不能回滾
（9） 在沒有備份情況下酒请，謹(jǐn)慎使用drop與truncate。要?jiǎng)h除部分?jǐn)?shù)據(jù)行采用delete且注意結(jié)合where來約束影響范圍鸣个⌒叻矗回滾段要足夠大。要?jiǎng)h除表用drop;若想保留表而將表中數(shù)據(jù)刪除囤萤，如果于事務(wù)無關(guān)昼窗，用truncate即可實(shí)現(xiàn)。如果和事務(wù)有關(guān)阁将，或老師想觸發(fā)trigger,還是用delete膏秫。
（10）Truncate table表名 速度快,而且效率高,因?yàn)?
truncate table 在功能上與不帶 WHERE 子句的DELETE語句相同：二者均刪除表中的全部行右遭。但TRUNCATE TABLE比DELETE速度快做盅，且使用的系統(tǒng)和事務(wù)日志資源少缤削。DELETE語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)吹榴。TRUNCATE TABLE通過釋放存儲(chǔ)表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)亭敢，并且只在事務(wù)日志中記錄頁的釋放。
（11）TRUNCATE TABLE刪除表中的所有行图筹，但表結(jié)構(gòu)及其列帅刀、約束、索引等保持不變远剩。新行標(biāo)識(shí)所用的計(jì)數(shù)值重置為該列的種子扣溺。如果想保留標(biāo)識(shí)計(jì)數(shù)值，請(qǐng)改用DELETE瓜晤。如果要?jiǎng)h除表定義及其數(shù)據(jù)锥余，請(qǐng)使用DROP TABLE 語句。
（12） 對(duì)于由FOREIGN KEY約束引用的表痢掠，不能使用TRUNCATE TABLE驱犹，而應(yīng)使用不帶WHERE子句的DELETE語句。由于TRUNCATE TABLE不記錄在日志中足画，所以它不能激活觸發(fā)器雄驹。
修改數(shù)據(jù)
基本語法
修改
update 表名 set 字段名1=值表達(dá)式1，字段名2=值表達(dá)式2淹辞，....[where條件] [order排序] [limit限定];
注意
- 以“行”為單位進(jìn)行的医舆，可以指定只更新其中的部分字段
- 其他限定遵循
insert語法
復(fù)制表
方法一
這種方法可以比較完整。
create table table2 like table1; /*復(fù)制結(jié)構(gòu)*/
insert into table2 select * from table1; /*復(fù)制數(shù)據(jù)*/
方法二
這種方法可能會(huì)丟一些結(jié)構(gòu)信息象缀，比如：索引彬向，約束，自增長(zhǎng)屬性
/*同時(shí)復(fù)制結(jié)構(gòu)和數(shù)據(jù)*/
create table table2 select * from table1;
MySQL 數(shù)據(jù)庫查詢
結(jié)構(gòu)化查詢語言 SQL 介紹
SQL 是用于訪問和處理數(shù)據(jù)庫的標(biāo)準(zhǔn)的計(jì)算機(jī)語言攻冷。
什么是 SQL娃胆？
- SQL 指結(jié)構(gòu)化查詢語言
- SQL 使我們有能力訪問數(shù)據(jù)庫
- SQL 是一種 ANSI 的標(biāo)準(zhǔn)計(jì)算機(jī)語言
注：ANSI，美國(guó)國(guó)家標(biāo)準(zhǔn)化組織
SQL 能做什么等曼？
- SQL 面向數(shù)據(jù)庫執(zhí)行查詢
- SQL 可從數(shù)據(jù)庫取回?cái)?shù)據(jù)
- SQL 可在數(shù)據(jù)庫中插入新的記錄
- SQL 可更新數(shù)據(jù)庫中的數(shù)據(jù)
- SQL 可從數(shù)據(jù)庫刪除記錄
- SQL 可創(chuàng)建新數(shù)據(jù)庫
- SQL 可在數(shù)據(jù)庫中創(chuàng)建新表
- SQL 可在數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程
- SQL 可在數(shù)據(jù)庫中創(chuàng)建視圖
- SQL 可以設(shè)置表里烦、存儲(chǔ)過程和視圖的權(quán)限
SQL 是一種標(biāo)準(zhǔn) - 但是...
SQL 是一門 ANSI 的標(biāo)準(zhǔn)計(jì)算機(jī)語言，用來訪問和操作數(shù)據(jù)庫系統(tǒng)禁谦。SQL 語句用于取回和更新數(shù)據(jù)庫中的數(shù)據(jù)胁黑。SQL 可與數(shù)據(jù)庫程序協(xié)同工作，比如 MS Access州泊、DB2丧蘸、Informix、MS SQL Server遥皂、Oracle力喷、Sybase 以及其他數(shù)據(jù)庫系統(tǒng)刽漂。
不幸地是，存在著很多不同版本的 SQL 語言弟孟，但是為了與 ANSI 標(biāo)準(zhǔn)相兼容贝咙，它們必須以相似的方式共同地來支持一些主要的關(guān)鍵詞（比如 SELECT、UPDATE拂募、DELETE庭猩、INSERT、WHERE 等等）陈症。
注釋：除了 SQL 標(biāo)準(zhǔn)之外蔼水，大部分 SQL 數(shù)據(jù)庫程序都擁有它們自己的私有擴(kuò)展！
實(shí)例表創(chuàng)建
班級(jí)表
CREATE TABLE class (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(10) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
老師表
REATE TABLE teacher (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(10) DEFAULT NULL,
age int(3) DEFAULT NULL,
phone char(11) DEFAULT NULL,
PRIMARY KEY (id)
)
student學(xué)生表
create table student(
id int auto_increment primary key,
name varchar(10),
age int(3),
class_id int,
foreign key(class_id) references class(id)
);
多對(duì)多關(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)
);
實(shí)例表數(shù)據(jù)添加
class(班級(jí)表)
insert into class(name) values
("云計(jì)算1810"),
("云計(jì)算1901"),
("云計(jì)算1902");
teacher(老師表)
insert into teacher(name, age, phone) values
("晗哥", 18, "13733878989"),
("強(qiáng)哥", 28, "15633878989"),
("磊哥", 30, "13933878989"),
("閆老師", 18, "13633878989");
student(學(xué)生表)
insert into student(name,age) values
("黛玉", 18, 3), ("欽文", 19, 3),("馬邦德", 30, 1),
("九筒", 48, 1),("六子", 36, 2),("湯師爺", 18, 2),
("麻匪", 18,2),
("黛玉", 18,2),;
class2teacher(班級(jí)到老師多對(duì)多關(guān)系表)
into class2teacher(class_id,teacher_id) values
(1,1),(1,2),(2,1),(2,2),(2,3),(3,1),(3,3);
單表查詢
基礎(chǔ)查詢
select * from 表
select * from 表 where id > 2
select id,name,age as gg from 表 where id > 2
高級(jí)查詢
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開頭的所有（多個(gè)字符串）
select * from 表 where name like 'shar_' - sha開頭的所有（一個(gè)字符）
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 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;
多表查詢
image
class (班級(jí)表)
| id | class_name | create_date |
|---|---|---|
| 1 | 云計(jì)算1805 | 2018-05-16 |
| 2 | 云計(jì)算1806 | 2018-06-16 |
| 3 | 云計(jì)算1807 | 2018-07-16 |
| 4 | 云計(jì)算1808 | 2018-08-16 |
teacher (老師表)
| id | name | class_id |
|---|---|---|
| 1 | 楊哥 | 1 |
| 2 | 強(qiáng)哥 | 3 |
| 3 | 磊哥 | 2 |
創(chuàng)建表有外鍵的表
create table class(
id int not null auto_increment primary key,
class_name varchar(8),
create_date DATE
);
create table teacher(
id int not null auto_increment primary key,
name varchar(12) not null,
class_id int(2) not null,
foreign key (class_id) references class(id) /*mysql 自動(dòng)給外鍵起名字teacher_ibfk_1*/
/*給外鍵命名*/
/*constraint fk_class foreign key (class_id) references class(id)*/
);
增加數(shù)據(jù)
mysql> insert into class ( class_name, create_date ) values ( '云計(jì)算1805', '2018-05-16' );
mysql> insert into class ( class_name, create_date) values ( '云計(jì)算1806', '2018.06.16' );
mysql> insert into class ( class_name, create_date ) values ( '云計(jì)算1807', '2018_07_16' ),('云計(jì)算1808','20180816');
insert into teacher (name, class_id) values ('楊哥', 1);
insert into teacher (name, class_id) values ('強(qiáng)哥', 3);
insert into teacher (name, class_id) values ('磊哥', 02);
連表
/*無對(duì)應(yīng)關(guān)系則不顯示*/
select A.class_name, B.name
from class as A, teacher as B
Where A.id = B.class_id
/* 內(nèi)連接 無對(duì)應(yīng)關(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)中無對(duì)應(yīng)關(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)中無對(duì)應(yīng)關(guān)系，則值為 NULL*/
select A.name, B.name
from class as A right join teacher as B
on A.id = B.class_id
Example
select a.class_name,b.name
from class a,teacher b
where a.id=b.class_id;
select a.class_name,b.name
from class a inner join teacher b
on a.id=b.class_id;
select a.class_name,b.name
from class a left join teacher b
on a.id=b.class_id;
select a.class_name,b.name
from class a right join teacher b
on a.id=b.class_id;
