一、插入数据
-- 方式一：一条一条的添加
-- 没有指明添加的字段，一定要按照声明的字段的先后顺序添加
INSERT INTO 表名 VALUES (字段值1,字段值2,...);
-- 指明要添加的字段：没有进行复制的字段值为默认值
INSERT INTO 表名(字段名1,字段名2,...) VALUES (字段值1,字段值2,...);
-- 方式三：将查询结果插入到表中（查询的字段一定要与添加到表的字段一一对应）
INSERT INTO 表名2(字段名1,字段名2,...)
SELECT 字段名1,字段名2,... FROM 表1;
example：
CREATE TABLE IF NOT EXISTS emp (
id INT,
NAME VARCHAR(15),
hire_date DATE,
salary DOUBLE(10,2)
);
-- 方式一：一条一条的添加
-- 没有指明添加的字段，一定要按照声明的字段的先后顺序添加
INSERT INTO emp VALUES (1,'Sakura','2000-01-24',3700);
-- 方式二：指明要添加的字段：没有进行复制的字段值为默认值
INSERT INTO emp(id,hire_date,salary,NAME) VALUES (2,'1999-09-09',3100,'Mikoto');
INSERT INTO emp(id,NAME,hire_date) VALUES (3,'Akame','1997-01-21');
-- 同时添加多组记录
INSERT INTO emp(id,NAME,hire_date)
VALUES (4,'Kurome','2000-01-10'),
(5,'Shana','2012-03-07');
-- 方式三：将查询结果插入到表中（查询的字段一定要与添加到表的字段一一对应）
INSERT INTO emp(id,NAME,salary,hire_date)
SELECT employee_id,last_name,salary,hire_date FROM atguigudb.employees;
- 一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中，效率更高；
- 要添加数据的表中的字段不能低于查询表中查询的字段的长度，如果低于，就有添加不成功的风险；
-
VALUES也可以写成VALUE，但VALUES是标准写法；
二、更新数据
UPDATE 表名
SET 字段名1 = 字段值1,
字段名2 = 字段值2,
...
WHERE 过滤条件;
- 可以实现批量修改数据；
- 在修改数据时，可能存在不成功的情况的
- 可能由于约束条件造成的
example：
UPDATE emp SET salary = 3900 WHERE NAME = 'Akame';
UPDATE emp
SET hire_date = '2013-03-07',
salary = 3100
WHERE id = 5;
三、删除数据
DELETE FROM 表名 WHERE 过滤条件;
- 在删除数据时，可能存在不成功的情况的
- 可能由于约束条件造成的
- DML操作默认情况下，执行以后会自动提交数据
- 如果希望执行完以后不自动提交数据，则需使用
SET autocommit = FALSE
- 如果希望执行完以后不自动提交数据，则需使用
example：
DELETE FROM emp WHERE id = 4;
四、MySQL新特性：计算列
某一列的值通过别的列计算得来的。在MySQL 8.0中，CREATE TABLE和ALTER TABLE 中都支持增加计算列。
CREATE TABLE test (
a INT,
b INT,
c INT GENERATED ALWAYS AS (a+b) VIRTUAL
);
INSERT INTO test(a,b) VALUES(10,20);
SELECT * FROM test;
UPDATE test SET a = 100;
SELECT * FROM test;
五、练习题
-- 1、创建数据库test01_library
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';
-- 2、创建表 books，表结构如下：
/*
| 字段名 | 字段说明 | 数据类型 |
| ------- | -------- | ------------ |
| id | 书编号 | INT |
| name | 书名 | VARCHAR(50) |
| authors | 作者 | VARCHAR(100) |
| price | 价格 | FLOAT |
| pubdate | 出版日期 | YEAR |
| note | 说明 | VARCHAR(100) |
| num | 库存 | INT |
*/
CREATE TABLE IF NOT EXISTS books (
id INT,
NAME VARCHAR(50),
AUTHORS VARCHAR(100),
price FLOAT,
pubdate YEAR,
note VARCHAR(100),
num INT
);
-- 3、向books表中插入记录
-- 1）不指定字段名称，插入第一条记录
-- 2）指定所有字段名称，插入第二记录
-- 3）同时插入多条记录（剩下的所有记录）
/*
| id | name | authors | price | pubdate | note | num |
| ---- | ------------- | --------------- | ----- | ------- | -------- | ---- |
| 1 | Tal of AAA | Dickes | 23 | 1995 | novel | 11 |
| 2 | EmmaT | Jane lura | 35 | 1993 | joke | 22 |
| 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 |
*/
INSERT INTO books VALUES(1,'Tal of AAA','Dickes',23,'1995','novel',11);
INSERT INTO books(id,NAME,AUTHORS,price,pubdate,note,num)
VALUES (2,'EmmaT','Jane lura',35,'1993','joke',22);
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、将小说类型(novel)的书的价格都增加5。
UPDATE books SET price = price + 5 WHERE note = 'novel';
-- 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。
UPDATE books
SET price = 40,note = 'drama'
WHERE NAME = 'EmmaT';
-- 6、删除库存为0的记录。
DELETE FROM books WHERE num = 0;
-- 7、统计书名中包含a字母的书
SELECT NAME FROM books WHERE NAME LIKE '%a%';
-- 8、统计书名中包含a字母的书的数量和库存总量
SELECT COUNT(*),SUM(num) FROM books WHERE NAME LIKE '%a%';
-- 9、找出“novel”类型的书，按照价格降序排列
SELECT NAME,note,price FROM books WHERE note = 'novel' ORDER BY price DESC;
-- 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
SELECT * FROM books ORDER BY num DESC,note ASC;
-- 11、按照note分类统计书的数量
SELECT note,COUNT(*) FROM books GROUP BY note;
-- 12、按照note分类统计书的库存量，显示库存量超过30本的
SELECT note,SUM(num) FROM books GROUP BY note HAVING SUM(num) > 30;
-- 13、查询所有图书，每页显示5本，显示第二页
SELECT * FROM books LIMIT 5,5;
-- 14、按照note分类统计书的库存量，显示库存量最多的
SELECT note,SUM(num) FROM books GROUP BY note ORDER BY SUM(num) DESC LIMIT 1;
-- 15、查询书名达到10个字符的书，不包括里面的空格
SELECT NAME FROM books WHERE CHAR_LENGTH(REPLACE(NAME,' ','')) > 10;
-- 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
SELECT NAME AS "书名",note,
CASE note WHEN 'novel' THEN '小说'
WHEN 'law' THEN '法律'
WHEN 'medicine' THEN '医药'
WHEN 'cartoon' THEN '卡通'
WHEN 'joke' THEN '笑话'
ELSE '其它'
END "类型"
FROM books;
-- 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货
SELECT NAME AS "书名",num AS "库存" ,
CASE WHEN num > 30 THEN '滞销'
WHEN num>0 AND num<10 THEN '畅销'
WHEN num = 0 THEN '无货'
ELSE '正常'
END "显示状态"
FROM books;
-- 18、统计每一种note的库存量，并合计总量
SELECT IFNULL(note,'合计库存总量') AS "note",SUM(num) FROM books GROUP BY note WITH ROLLUP;
-- 19、统计每一种note的数量，并合计总量
SELECT IFNULL(note,'合计总量') AS "note",COUNT(*) FROM books GROUP BY note WITH ROLLUP;
-- 20、统计库存量前三名的图书
SELECT * FROM books ORDER BY num DESC LIMIT 3;
-- 21、找出最早出版的一本书
SELECT * FROM books ORDER BY pubdate ASC LIMIT 1;
-- 22、找出novel中价格最高的一本书
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC LIMIT 1;
-- 23、找出书名中字数最多的一本书，不含空格
SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(NAME,' ','')) DESC LIMIT 1;
-- 24.创建数据库dbtest11
CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8';
-- 25. 运行以下脚本创建表my_employees
USE dbtest11;
CREATE TABLE my_employees(
id INT(10),
first_name VARCHAR(10),
last_name VARCHAR(10),
userid VARCHAR(10),
salary DOUBLE(10,2)
);
CREATE TABLE users(
id INT,
userid VARCHAR(10),
department_id INT
);
-- 26. 显示表my_employees的结构
DESC my_employees;
-- 27. 向my_employees表中插入下列数据
/*
ID FIRST_NAME LAST_NAME USERID SALARY
1 patel Ralph Rpatel 895
2 Dancs Betty Bdancs 860
3 Biri Ben Bbiri 1100
4 Newman Chad Cnewman 750
5 Ropeburn Audrey Aropebur 1550
*/
INSERT INTO my_employees(id,first_name,last_name,userid,salary)
VALUES (1,'patel','Ralph','Rpatel',895),
(2,'Dances','Betty','Bdanss',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
-- 28. 向users表中插入数据
/*
1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40
*/
INSERT INTO users(id,userid,department_id)
VALUES (1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40);
-- 29. 将3号员工的last_name修改为“drelxer”
UPDATE my_employees SET last_name = 'drelxer' WHERE id = 3;
-- 30. 将所有工资少于900的员工的工资修改为1000
UPDATE my_employees SET salary = 1000 WHERE salary < 900;
-- 31. 将userid为Bbiri的users表和my_employees表的记录全部删除
DELETE FROM my_employees WHERE userid = 'Bbiri';
DELETE FROM users WHERE userid = 'Bbiri';
-- 32. 删除my_employees、users表所有数据
DELETE FROM my_employees;
DELETE FROM users;
-- 33. 检查所作的修正
SELECT * FROM my_employees;
SELECT * FROM users;
-- 34. 清空表my_employees
TRUNCATE TABLE my_employees;
-- 35. 使用现有数据库dbtest11
USE dbtest11;
-- 36. 创建表格pet
/*
字段名 字段说明 数据类型
name 宠物名称 VARCHAR(20)
owner 宠物主人 VARCHAR(20)
species 种类 VARCHAR(20)
sex 性别 CHAR(1)
birth 出生日期 YEAR
death 死亡日期 YEAR
*/
CREATE TABLE IF NOT EXISTS pet(
NAME VARCHAR(20),
OWNER VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth YEAR,
death YEAR
);
-- 37. 添加记录
/*
name owner species sex birth death
Fluffy harold Cat f 2003 2010
Claws gwen Cat m 2004
Buffy Dog f 2009
Fang benny Dog m 2000
bowser diane Dog m 2000 2009
Chirpy Bird f 2008
*/
INSERT INTO pet(NAME,OWNER,species,sex,birth,death)
VALUE ('Fluffy','harold','Cat','f','2003','2010'),
('Claws','gwen','Cat','m','2004',NULL),
('Buffy',NULL,'Dog','f','2009',NULL),
('Fang','benny','Dog','m','2000',NULL),
('bowser','diane','Dog','m','2000','2009'),
('Chirpy',NULL,'Bird','f','2008',NULL);
-- 38. 添加字段:主人的生日owner_birth DATE类型。
ALTER TABLE pet ADD COLUMN owner_birth DATE;
-- 39. 将名称为Claws的猫的主人改为kevin
UPDATE pet SET OWNER = 'kevin' WHERE NAME = 'Claws' AND species = 'Cat';
-- 40. 将没有死的狗的主人改为duck
UPDATE pet SET OWNER = 'duck' WHERE death IS NULL AND species = 'Dog';
-- 41. 查询没有主人的宠物的名字；
SELECT NAME FROM pet WHERE OWNER IS NULL;
-- 42. 查询已经死了的cat的姓名，主人，以及去世时间；
SELECT NAME,OWNER,death FROM pet WHERE death IS NOT NULL;
-- 43. 删除已经死亡的狗
DELETE FROM pet WHERE death IS NOT NULL AND species = 'Dog';
-- 44. 查询所有宠物信息
SELECT * FROM pet;
-- 45. 使用已有的数据库dbtest11
USE dbtest11;
-- 46. 创建表employee，并添加记录
/*
id name sex tel addr salary
10001 张一一 男 13456789000 山东青岛 1001.58
10002 刘小红 女 13454319000 河北保定 1201.21
10003 李四 男 0751-1234567 广东佛山 1004.11
10004 刘小强 男 0755-5555555 广东深圳 1501.23
10005 王艳 女 020-1232133 广东广州 1405.16
*/
CREATE TABLE IF NOT EXISTS employee(
id INT,
NAME VARCHAR(10),
sex CHAR(1),
tel VARCHAR(15),
addr VARCHAR(30),
salary DOUBLE(10,2)
);
INSERT INTO employee(id,NAME,sex,tel,addr,salary)
VALUES (10001,'张一一','男','13456789000','山东青岛',1001.58),
(10002,'刘小红','女','13454319000','河北保定',1201.21),
(10003,'李四','男','0751-1234567','广东佛山',1004.11),
(10004,'刘小强','男','0755-5555555','广东深圳',1501.23),
(10005,'王艳','女','020-1232133','广东广州',1405.16);
-- 47. 查询出薪资在1200~1300之间的员工信息。
SELECT * FROM employee WHERE salary BETWEEN 1200 AND 1300;
-- 48. 查询出姓“刘”的员工的工号，姓名，家庭住址。
SELECT id,NAME,addr FROM employee WHERE NAME LIKE '刘%';
-- 49. 将“李四”的家庭住址改为“广东韶关”
UPDATE employee SET addr = '广东韶关' WHERE NAME = '李四';
-- 50. 查询出名字中带“小”的员工
SELECT * FROM employee WHERE NAME LIKE '%小%';