1.插入數(shù)據(jù)(INSERT語(yǔ)句)

1.1 問(wèn)題引入

向departments表中，新增一條數(shù)據(jù)。

解決方式，使用insert語(yǔ)句向表中插入數(shù)據(jù)。

1.2 方式1：values的方式添加

情況1：為表的所有字段按默認(rèn)順序插入數(shù)據(jù)

INSERT INTO 表名
VALUES (value1,value2,...);

值列表中需要為表的每一個(gè)字段指定值，并且值的順序必須和數(shù)據(jù)表中字段定義時(shí)的順序相同。

舉例

INSERT INTO departments
VALUES(70,'Pub',100,1700);

INSERT INTO departments
VALUES(100,'Finance',NULL,NULL);

情況2：為表的指定字段插入數(shù)據(jù)

INSERT INTO 表名(column1[,column2,...])
VALUES(value1 [,value2,...])

為表的指定字段插入數(shù)據(jù)，就是在INSERT語(yǔ)句中只向部分字段中插入值，而其他字段的值為表定義時(shí)的默認(rèn)值。沒(méi)有默認(rèn)值的列，如果可以為null，會(huì)賦null值，但是如果未給未設(shè)定默認(rèn)值，且非空的列設(shè)置值的話(huà)，會(huì)報(bào)錯(cuò)。Field 'empNum' doesn't have a default value

在INSERT子句中可以隨意列出列名，但是一旦列出，VALUES中要插入的value1,value2....需要于column1,column2...一一對(duì)應(yīng)。如果類(lèi)型不同，將無(wú)法完成數(shù)據(jù)的插入，并且MySQL會(huì)報(bào)錯(cuò)。

舉例

INERT INTO departments(department_id,department_name)
VALUES(70,'IT');

情況3：同時(shí)插入多條記錄

INSERT語(yǔ)句可以同時(shí)向數(shù)據(jù)表中插入多條記錄，插入時(shí)指定多個(gè)值列表，每個(gè)值列表之間用逗號(hào)分隔開(kāi)，基本語(yǔ)法格式如下：

INSERT INTO table_name
VALUES
(value1[,value2,....valuen]),
(value1[,value2,....valuen]),
(value1[,value2,....valuen]),
...
(value1[,value2,....valuen]);

-- OR
INSERT INTO table_name(column1[,column2,...columnn])
VALUES
(value1[,value2,....valuen]),
(value1[,value2,....valuen]),
(value1[,value2,....valuen]),
...
(value1[,value2,....valuen]);

舉例

INSERT INTO emp01(employee_id,last_name,email,hire_date,job_id)
VALUES (101,'wind','wind1215@126.com','2022-06-18','0001'),
(102,'flower','wind1232@126.com','2022-02-18','0001'),
(103,'snow','wind1223@126.com','2022-12-13','0001');

使用INSERT同時(shí)插入多條記錄時(shí)，MySQL會(huì)返回一些在執(zhí)行單行插入時(shí)沒(méi)有的額外信息，這些信息的含義如下：

• Records：表明插入的記錄條數(shù)。

• Duplicates：表明插入時(shí)被忽略的記錄，原因可能是這些記錄包含了重復(fù)的主鍵值。

• Warnings：表名有問(wèn)題的數(shù)據(jù)值，例如發(fā)生數(shù)據(jù)類(lèi)型轉(zhuǎn)換。
  

小結(jié)

• VALUES也可以寫(xiě)成VALUE，但是VALUES是標(biāo)準(zhǔn)寫(xiě)法。
• 字符和日期型數(shù)據(jù)應(yīng)包含在單引號(hào)中。

1.3 方式2：將查詢(xún)結(jié)果插入列表中

INSERT還可以將SELECT語(yǔ)句查詢(xún)到的結(jié)果插入到表中，此時(shí)不需要把每一條記錄的值一個(gè)一個(gè)輸入，只需要使用一條INSERT語(yǔ)句和一條SELECT語(yǔ)句組成的組合語(yǔ)句，即可快速的從一個(gè)或多個(gè)表中向一個(gè)表中插入多行。

基本格式

INSERT INTO table_name
(tar_column1[,tar_column2,...tar_columnn])
SELECT 
(tar_column1[,tar_column2,...tar_columnn])
FROM src_table_name
[WHERE condition];

• 在INSERT 語(yǔ)句中加入子查詢(xún)

• 不必書(shū)寫(xiě)VALUES子句

• 子查詢(xún)中的值列表與INSERT子句中的列名對(duì)應(yīng)

舉例

INSERT INTO emp02
SELECT * FROM atguigudb.`employees`;

INSERT INTO salas_reps(id,name,salary,commission_pct)
SELECT employee_id,last_name,salar,commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

2.更新數(shù)據(jù)(UPDATE語(yǔ)句)

2.1 問(wèn)題引入

修改表中的數(shù)據(jù)。

解決方式，使用UPDATE語(yǔ)句更新數(shù)據(jù)。

2.2 語(yǔ)法格式

UPDATE table_name
SET column1=value1,column2=value2,....
[WHERE condition];

• 可以一次更新多條數(shù)據(jù)。
• 如果需要回滾數(shù)據(jù)，需要保證執(zhí)行DML語(yǔ)句前，進(jìn)行設(shè)置：SET AUTOCOMMIT = false;
• 使用WHERE子句指定需要更新的數(shù)據(jù)

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

• 如果省略WHERE子句，則表中的所有數(shù)據(jù)都將被更新。

UPDATE emp01
SET department_id = 70;

• 更新中的數(shù)據(jù)完整性錯(cuò)誤

mysql> UPDATE employees
    -> SET department_id = 55
    -> WHERE department_id = 110;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`atguigudb`.`employees`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`))

說(shuō)明，不存在55號(hào)部門(mén)。這是存在外鍵約束的情況下，才出現(xiàn)的。如果但對(duì)于department_id沒(méi)有外鍵約束，則不會(huì)報(bào)錯(cuò)。

3.刪除數(shù)據(jù)(DELETE語(yǔ)句)

3.1 問(wèn)題引入

使用DELETE語(yǔ)句從表中刪除數(shù)據(jù)

3.2 語(yǔ)法格式

DELETE FROM table_name
[WHERE condition];

table_name指定要?jiǎng)h除的表；[WHERE]為可選子句，指定刪除條件，如果沒(méi)有WHERE子句，DELETE語(yǔ)句將刪除表中所有的數(shù)據(jù)。

• 使用WHERE子句刪除指定的記錄。

DELETE FROM departments
WHERE department_name = 'Finance';

• 如果省略WHERE子句，則表中的全部數(shù)據(jù)將被刪除。

DELETE FROM emp01;

• 刪除中的數(shù)據(jù)完整性錯(cuò)誤

mysql> DELETE FROM departments
    -> WHERE department_id = 60;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`atguigudb`.`employees`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`))

說(shuō)明，如果刪除的列，使用了外鍵，需要先從外鍵表中刪除，才能刪除本表中的記錄

4.MySQL8新特性：計(jì)算列

什么叫計(jì)算列呢？簡(jiǎn)單來(lái)說(shuō)就是某一列的值是通過(guò)別的列計(jì)算得來(lái)的。例如，a列值為1、b列值為2，c列不需要手動(dòng)插入，定義a+b的結(jié)果為c的值，那么c就是計(jì)算列，是通過(guò)別的列計(jì)算得來(lái)的。

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加計(jì)算列。下面以CREATE TABLE為例進(jìn)行講解。

舉例：定義數(shù)據(jù)表tb1，然后定義字段id、字段a、字段b和字段c，其中字段c為計(jì)算列，用于計(jì)算a+b的

值。 首先創(chuàng)建測(cè)試表tb1，語(yǔ)句如下：

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

插入演示數(shù)據(jù)，語(yǔ)句如下：

INSERT INTO tb1(a,b) VALUES (100,200);

查詢(xún)數(shù)據(jù)表tb1中的數(shù)據(jù)，結(jié)果如下：

mysql> SELECT * FROM tb1;
+------+------+------+------+
| id | a | b | c |
+------+------+------+------+
| NULL | 100 | 200 | 300 |
+------+------+------+------+
1 row in set (0.00 sec)

更新數(shù)據(jù)中的數(shù)據(jù)，語(yǔ)句如下：

mysql> UPDATE tb1 SET a = 500;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

5.綜合案例

# 1、創(chuàng)建數(shù)據(jù)庫(kù)test01_library
CREATE DATABASE test01_library CHARSET utf8;

USE test01_library;
# 2、創(chuàng)建表 books，表結(jié)構(gòu)如下：
CREATE TABLE books(
    id INT,
    `name` VARCHAR(50),
    `authors` VARCHAR(100),
    price FLOAT,
    pubdate YEAR,
    note VARCHAR(100),
    num INT
);

# 3、向books表中插入記錄

# 1）不指定字段名稱(chēng)，插入第一條記錄
INSERT INTO books
VALUES(100,'小灰的漫畫(huà)算法','小灰',30.0,'2023','算法入門(mén)書(shū)籍',001);

SELECT * FROM books;

# 2）指定所有字段名稱(chēng)，插入第二記錄
INSERT INTO books(id,`name`,`authors`,price,pubdate,note,num)
VALUES(101,'小王子','圣?？怂古謇?,27.0,'2023','童話(huà)書(shū)籍',002);

# 3）同時(shí)插入多條記錄（剩下的所有記錄）
INSERT INTO books (id,NAME,`authors`,price,pubdate,note,num) VALUES
(3,'Story of Jane','Jane Tim',40,2001,'novel',0),
(4,'Lovey Day','George Byron',20,2005,'novel',30),
(5,'Old land','Honore Blade',30,2010,'Law',0),
(6,'The Battle','Upton Sara',30,1999,'medicine',40),
(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);

# 4、將小說(shuō)類(lèi)型(novel)的書(shū)的價(jià)格都增加5。
UPDATE books SET price = price + 5 

# 5、將名稱(chēng)為EmmaT的書(shū)的價(jià)格改為40，并將說(shuō)明改為drama。
UPDATE SET price = 40,note = 'drama' WHERE `name` = 'EmmaT';

# 6、刪除庫(kù)存為0的記錄。
DELETE FROM books WHERE num = 0;

# 7、統(tǒng)計(jì)書(shū)名中包含a字母的書(shū)
SELECT  * FROM books 
WHERE `name` REGEXP 'a';

# 8、統(tǒng)計(jì)書(shū)名中包含a字母的書(shū)的數(shù)量和庫(kù)存總量
SELECT COUNT(*),SUM(num) AS 'all_num'
FROM books
WHERE `name` REGEXP 'a';

# 9、找出“novel”類(lèi)型的書(shū)，按照價(jià)格降序排列
SELECT * FROM books
WHERE note = 'novel'
ORDER BY price DESC;

# 10、查詢(xún)圖書(shū)信息，按照庫(kù)存量降序排列，如果庫(kù)存量相同的按照note升序排列
SELECT * FROM books
ORDER BY num DESC,note;

# 11、按照note分類(lèi)統(tǒng)計(jì)書(shū)的數(shù)量
SELECT COUNT(*),note FROM books
GROUP BY note;

# 12、按照note分類(lèi)統(tǒng)計(jì)書(shū)的庫(kù)存量，顯示庫(kù)存量超過(guò)30本的
SELECT SUM(num),note FROM books
GROUP BY note;
HAVING SUM(num) > 30

# 13、查詢(xún)所有圖書(shū)，每頁(yè)顯示5本，顯示第二頁(yè)
SELECT * FROM books LIMIT 5,5;

# 14、按照note分類(lèi)統(tǒng)計(jì)書(shū)的庫(kù)存量，顯示庫(kù)存量最多的  
SELECT SUM(num) AS sum_num,note
FROM books
GROUP BY note
ORDER BY sum_num DESC
LIMIT 0,1

# 15、查詢(xún)書(shū)名達(dá)到10個(gè)字符的書(shū)，不包括里面的空格
SELECT * FROM books
WHERE CHAR_LENGTH(REPLACE(`name`,' ','')) >= 10
# 16、查詢(xún)書(shū)名和類(lèi)型，其中note值為novel顯示小說(shuō)，law顯示法律，medicine顯示醫(yī)藥，cartoon顯示卡通，
-- joke顯示笑話(huà)
SELECT `name`,(CASE WHEN note = 'novel' THEN '小說(shuō)'
                     WHEN note = 'law' THEN '法律'
                     WHEN note = 'medicine' THEN '醫(yī)藥'
                     WHEN note = 'cartoon' THEN '卡通'
                     WHEN note = 'joke' THEN '笑話(huà)'
                     ELSE '其他' END) AS '類(lèi)型'
 FROM books;

# 17、查詢(xún)書(shū)名、庫(kù)存，其中num值超過(guò)30本的，顯示滯銷(xiāo)，大于0并低于10的，顯示暢銷(xiāo)，為0的顯示需要無(wú)貨
SELECT `name`,num,(CASE WHEN num > 30 THEN '滯銷(xiāo)'
                        WHEN 10 > num AND num > 0 THEN '暢銷(xiāo)'
                        WHEN num = 0 THEN '無(wú)貨' END) AS '銷(xiāo)售狀態(tài)'
FROM books
# 18、統(tǒng)計(jì)每一種note的庫(kù)存量，并合計(jì)總量
SELECT SUM(num) FROM books
GROUP BY note WITH ROLLUP;

# 19、統(tǒng)計(jì)每一種note的數(shù)量，并合計(jì)總量
SELECT COUNT(*) FROM books
GROUP BY note WITH ROLLUP;

# 20、統(tǒng)計(jì)庫(kù)存量前三名的圖書(shū)
SELECT * FROM books
ORDER BY num DESC
LIMIT 0,3;
# 21、找出最早出版的一本書(shū)
SELECT * FROM books
ORDER BY pubdate
LIMIT 0,1;

# 22、找出novel中價(jià)格最高的一本書(shū)
SELECT * 
FROM books
WHERE note = 'novel'
ORDER BY price DESC
LIMIT 0,1;

# 23、找出書(shū)名中字?jǐn)?shù)最多的一本書(shū)，不含空格
SELECT * FROM books
ORDER BY CHAR_LENGTH(REPLACE(`name`,' ','')) DESC
LIMIT 0,1;

只是為了記錄自己的學(xué)習(xí)歷程，且本人水平有限，不對(duì)之處，請(qǐng)指正。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-777097.html

到了這里，關(guān)于MySQL-11.數(shù)據(jù)處理之增刪改的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！