插入数据（增）

  1. USE atguigudb;
  3. CREATE TABLE IF NOT EXISTS emp1(
  4.     id INT,
  5.   `name` VARCHAR(15),
  6.   hire_date DATE,
  7.   salary DOUBLE(10,2)
  8. )
  10. DESC emp1;

方式1：一条一条的添加数据

  • 没有指明添加的字段，需要按照声明的字段的先后顺序添加

    1. INSERT INTO emp1  //正确的
    2. VALUES (1,'Tom','2000-12-21',3400)
    1. INSERT INTO emp1  //错误的
    2. VALUES (2,3400,'2000-12-21','Tom')

  • 指明要添加的字段（推荐） ```sql INSERT INTO emp1(id,hire_date,salary,name) VALUE(2,’1999-12-21’,4000,’Jerry’)

说明：若没有赋值，则值为null

  2. - 同时插入多条记录
  3. ```sql
  4. INSERT INTO emp1(id,name,salary)
  5. VALUES 
  6. (4,'Jim',5000),
  7. (5,'sdf',4999)

方式2：将查询结果插入到表中

  • 查询的字段一定要与添加到的表的字段一一对应 ```sql SELECT * FROM emp1;

INSERT INTO emp1(id,name,salary,hire_date)

查询语句

SELECT employee_id,last_name,salary,hire_date # FROM employees WHERE department_id IN (70,60)

  2. - 说明：emp1表中要添加数据的字段的长度不能低于employees表中查询的字段的长度
  3. - 如果emp1表中要添加数据的字段的长度低于employees表中查询的字段的长度的话，就有添加不成功的风险
  6. <a name="Zejui"></a>
  7. # 更新数据（改）
  9. - update可以实现批量修改数据（看where）
  10. ```sql
  11. UPDATE ... SET ... WHERE ...
  1. UPDATE emp1
  2. SET hire_date = CURDATE()
  3. WHERE id = 5;
  • 同时修改一条数据的多个字段
    1. UPDATE emp1
    2. SET hire_date = CURDATE(),salary = 6000
    3. WHERE id = 4;

题目：将表中姓名中包含字符a的提薪20%

  1. UPDATE emp1
  2. SET salary = salary * 1.2
  3. WHERE name LIKE '%a%';
  • 修改数据时，可能存在不成功的情况。（可能是由于约束的影响造成的）
    1. UPDATE employees
    2. SET department_id = 10000
    3. WHERE employee_id = 102

删除数据（删）

  1. DELETE FROM ... WHERE ...
  1. DELETE emp1
  2. WHERE id = 1;

  • 在删除数据时，也有可能因为约束的影响，导致删除失败

    1. DELETE FROM departments
    2. WHERE department_id = 50

  • DML操作默认情况下，执行完后都会自动提交数据

  • 如果希望执行完后不自动提交数据，则需要使用 SET autocommit = FALSE

计算列

某一列的值时通过别的列计算得来的

  • 字段c即为计算列
    1. CREATE TABLE test1(
    2.   a INT,
    3. b INT,
    4. c    INT GENERATED ALWAYS AS (a + b) VIRTUAL
    5. );
    ```sql INSERT INTO test1(a,b) VALUES(10,20)

SELECT * FROM test1; //c = 30

UPDATE test1 SET a = 100 //相应的c也会变成 120

  2. <a name="hdIoN"></a>
  3. # 综合案例
  5. 1. 创建数据库test01_library
  6. ```sql
  7. CREATE DATABASES IF NOT EXISTS test01_library CHARACTER SET 'utf8'
  1. 创建表books ``sql CREATE TABLE IF NOT EXISTS books( id INT,nameVARCHAR(50),authors` VARCHAR(50), price FLOAT, pubdate YEAR, note VARCHAR(100), num INT )

DESC books;

  2. 3. 向books表中插入记录
  3.    1. 不指定字段名称，插入第一条记录
  4. ```sql
  5. INSERT INTO books
  6. VALUES(1,'Tal of AAA','Dickes',23,'1995','novel',11);

  1. 指定所有字段名称，插入第二条记录

    1. INSERT INTO books(id,`name`,`authors`,price,pubdate,note,num)
    2. VALUES(2,'EmmaT','Jane lura',35,'1993','joke',22);

  2. 同时插入多条记录，插入剩下所有的记录

    1. INSERT INTO books(id,`name`,`authors`,price,pubdate,note,num) 
    2. VALUES
    3. (3,'Story of Jane','Jane Tim',40,2001,'novel',0),
    4. (4,'Lovey Day','George Byron',20,2005,'novel',30),
    5. (5,'Old land','Honore Blade',30,2010,'Law',0),
    6. (6,'The Battle','Upton Sara',30,1999,'medicine',40),
    7. (7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);

  1. 将小说类型的书的价格都增加5

    1. UPDATE books
    2. SET price = price + 5
    3. WHERE note = 'novel'

  2. 将名称为 EmmaT的书的价格改成40，并将说明改为drama

    1. UPDATE books
    2. SET price = 40,note = 'drama'
    3. WHERE `name` = 'EmmaT'

  3. 删除库存为0的记录

    1. DELETE FROM books
    2. WHERE num = 0

  4. 统计书名中包含a字母的书

    1. SELECT `name`
    2. FROM books
    3. WHERE `name` LIKE '%a%';

  5. 统计书名中包含字母a的书的数量和库存总量

    1. SELECT COUNT(*),SUM(num)
    2. FROM books
    3. WHERE `name` LIKE '%a%';

  6. 找出”novel”类型的书，按照价格降序排列

    1. SELECT `name`,note
    2. FROM books
    3. WHERE note = 'novel'
    4. ORDER BY price DESC;

  7. 查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列

    1. SELECT *
    2. FROM books
    3. ORDER BY num DESC,note ASC;

  8. 按照note分类统计书的数量

    1. SELECT note,COUNT(*)
    2. FROM books
    3. GROUP BY note;

  9. 按照note分类统计书的库存量，显示库存量超过30本的

    1. SELECT note,SUM(num)
    2. FROM books
    3. GROUP BY note
    4. HAVING SUM(num) > 30;

  10. 查询所有图书，每页显示5本，显示第二页

    1. SELECT *
    2. FROM books
    3. LIMIT 5,5

  11. 按照note分类统计书的库存量，显示库存量最多的

    1. SELECT note,SUM(num) sum_num
    2. FROM books
    3. GROUP BY note
    4. ORDER BY sum_num DESC
    5. LIMIT 0,1;

  12. 查询书名达10个字符的书，不包括里面的空格 ``sql SELECT CHAR_LENGTH(REPLACE(name`,’ ‘,’’)) 先用’’来替换空格 FROM books

SELECT ‘name’ FROM books WHERE CHAR_LENGTH(REPLACE(name,’ ‘,’’)) >= 10

  2. 16. 查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
  3. ```sql
  4. SELECT `name` "书名",note,CASE note WHEN 'novel' THEN '小说'
  5.                     WHEN 'law' THEN '法律'
  6.                     WHEN 'medicine' THEN '医药'
  7.                     WHEN 'cartoon' THEN '卡通'
  8.                     WHEN 'joke' THEN '笑话'
  9.                     ELSE '其他'
  10.                     END '类型'
  11. FROM books

  1. 查询书名、库存，其中num值超过30本的，显示滞销，大于0并且低于10的显示畅销，为零的显示需要无货

    1. SELECT `name` AS "书名",num "库存",CASE WHEN num > 30 THEN '滞销'
    2.                 WHEN num > 0 AND num < 10 THEN '畅销'
    3.                 WHEN num = 0 THEN '无货'
    4.                 END "显示状态"
    5. FROM books

  2. 统计每一种note的库存量，并合计总量

    1. SELECT IFNULL(note,'合计库存总量') note,SUM(num)
    2. FROM books
    3. GROUP BY note WITH ROLLUP;

  3. 统计每一种note的数量，并合计总量

    1. SELECT IFNULL(note,'合计总量') note,COUNT(*)
    2. FROM books
    3. GROUP BY note WITH ROLLUP;

  4. 统计库存量前三名的图书

    1. SELECT *
    2. FROM books
    3. ORDER BY num DESC
    4. LIMIT 0,3

  5. 找出最早出版的一本书

    1. SELECT *
    2. FROM books
    3. ORDER BY pubdate ASC
    4. LIMIT 0,1

  6. 找出novel中价格最高的一本书

    1. SELECT *
    2. FROM books
    3. WHERE note = 'novel'
    4. ORDER BY price DESC
    5. LIMIT 0,1

  7. 找出书名中字数最多的书，不含空格

    1. SELECT *
    2. FROM books
    3. ORDER BY CHAR_LENGTH(REPLACE(`name`,' ','')) DESC
    4. LIMIT 0,1;