数据的导出和导入

数据导出

image.png

数据导入

image.png
在数据库上右键 选择【运行sql文件】
选择Sql文件的路径
image.png

练习

  1. 下载 homework.sql 文件 homework.sql
  2. 将homework.sql 的数据导入自己的数据库中 （新建一个数据库 数据库名为 homework_5500 5500 为自己的学号）

image.png

case … when …

image.png
将成绩进行归类。

  1. SELECT username, course, score,
  2. case 
  3.         when score BETWEEN 80 and 100 THEN "优秀"    -- 80-100
  4.         when score BETWEEN 60 AND 79 Then "良好"        -- 60-79
  5.         when score BETWEEN 0 AND 59 THEN "差"           -- 0-59
  6.         ELSE "缺考"                                     -- 除了上面定义的条件之外
  7. END AS 评价       -- 列名
  8. from students;

checkin 表
image.png
计算每人的考勤次数，新建统计列，列名为 “是否发全勤奖” 考勤次数 >=3 ， 结果为 “是”， 其它为 “否”。

select name,COUNT(name),
    case WHEN COUNT(name) >=3 THEN "是"
        ELSE "否"
    END as 是否发全勤奖
FROM checkin
GROUP BY name

统计 7月份的考勤数据， 考勤最多的同学 奖金1000，其它奖金为0。

select name,COUNT(checkintime),
 case WHEN COUNT(checkintime) >=(
  SELECT COUNT(checkintime) from checkin WHERE checkintime LIKE '2021-07%' GROUP BY name ORDER BY COUNT(checkintime) DESC LIMIT 1) 
 THEN '1000' ELSE '0' 
 END AS 全勤奖 

FROM checkin 
WHERE checkintime LIKE "2021-07%"
GROUP BY name
ORDER BY 全勤奖 desc

if 语句

-- if 添加判断语句
-- if(score is null, 0, score)  score is null 成立时，显示0， 否则还是显示原来的结果。

select username, score, if(score is null, 0, score) from students;

— 查询stduents 表中的数据，如果学员的年龄 >= 18 显示成年人，否则显示未成年人

 select username, age, case when age >=18 then "成年人"
 else "未成年"
 END 是否成年
 from students;




 select username, age, if(age>=18,"成年人","未成年") from students;

计算年龄 并划分成年人与未成年人。

SELECT *,
if( YEAR(CURDATE())-YEAR(birthday)>=18, "成年人", "未成年") as 是否成年
FROM student;

image.png

如何批量添加数据

批量往数据库中添加大量的数据。主要方式：

  1. 使用编程的方式
  2. 压测工具— 通过接口的方式 批量添加数据。
  3. 使用存储过程。

存储过程

-- 创建存储过程

CREATE PROCEDURE isAdult()       --   isAdult()
BEGIN

    SELECT *,
    if( YEAR(CURDATE())-YEAR(birthday)>=18, "成年人", "未成年") as 是否成年
    FROM student;

END;

----

CALL isAdult();     -- 调用存储过程

在测试服务器上 创建 一个存储过程。

create PROCEDURE 考勤()
BEGIN

    SELECT person.pno, COUNT(*) from person
    INNER JOIN checkin
    ON person.pno = checkin.pno
    GROUP BY person.pno
    HAVING COUNT(*) = (SELECT COUNT(*) from checkin
                                        GROUP BY pno
                                        ORDER BY count(*) desc
                                        LIMIT 1);

END;


CAll 考勤();

image.png创建好的存储过程可以在 函数中看到。

使用存储过程添加数据。

CREATE PROCEDURE 添加数据()
BEGIN

    INSERT INTO `huahua_5500`.`student`(`sname`, `birthday`, `sex`) VALUES ('李四', '2021-06-30 15:24:37', '男');

END;

批量添加数据

CREATE PROCEDURE 添加数据()
BEGIN
    DECLARE num INT DEFAULT 0;    -- 声明 数字 num 初始值为 0；
    REPEAT
        -- 循环执行的语句
      INSERT INTO `huahua_5500`.`student`(`sname`, `birthday`, `sex`) 
                VALUES ('李四', CURRENT_TIME(), '男');
            set num = num+1;   --  执行1次  num 值增加1

    UNTIL num=10000 END REPEAT;   -- 一直等到num值为100的时候结束循环
END;

添加索引

CREATE INDEX snameidx ON student(sname);   -- snameidx 自定义索引名， 
                                           -- student(sname) 表示在student表中的sname 字段

image.png

image.png

查询 products表，oderitems表， 统计每个商品的销售额， 输出 商品名称，商品单价，商品销售数量，商品总额。
如果某件商品没有卖出去，商品销售数量，商品总价为0.

-- 1. 先计算销售数量，销售总额
select prod_id, 
        sum(quantity) as 销售数量 , 
    sum(quantity*item_price) as 销售总额
from orderitems
GROUP BY prod_id


SELECT 
    products.prod_id as 商品id ,
    prod_name as 商品名称, 
    if(销售数量 is null, 0, 销售数量) as 销售数量, 
    if(销售总额 is null, 0, 销售总额) as 销售总额  
from products
LEFT JOIN (select prod_id, sum(quantity) as 销售数量 , sum(quantity*item_price) as 销售总额 from orderitems
                        GROUP BY prod_id) as tmp

ON products.prod_id = tmp.prod_id
ORDER BY 销售总额 desc;

image.png