TODO: R&D self-test DQL basically about mysql-server-mysql-8.0.30 HOT 1 OPEN

andyli029 commented on July 20, 2024

TODO: R&D self-test DQL basically

from mysql-server-mysql-8.0.30.

Comments (1)

lujiashun commented on July 20, 2024

DQL

failed：× ； pass：✓

0）prepare data

CREATE TABLE t_test1(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(10) NOT NULL,
  last_name VARCHAR(10) NOT NULL,
  sex VARCHAR(5) NOT NULL,
  score INT NOT NULL,
  copy_id INT NOT NULL,
  PRIMARY KEY (`id`)
) engine=tianmu;

DELIMITER //
    create PROCEDURE add_user(in num INT)
    BEGIN
        DECLARE rowid INT DEFAULT 0;
        DECLARE firstname CHAR(1);
        DECLARE name1 CHAR(1);
        DECLARE name2 CHAR(1);
        DECLARE lastname VARCHAR(3) DEFAULT '';
        DECLARE sex CHAR(1);
        DECLARE score CHAR(2);
        WHILE rowid < num DO
        SET firstname = SUBSTRING('赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐',FLOOR(1+21*RAND()),1); 
        SET name1 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 
        SET name2 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 
        SET sex=FLOOR(0 + (RAND() * 2));
        SET score= FLOOR(40 + (RAND() *60));
        SET rowid = rowid + 1;
        IF ROUND(RAND())=0 THEN 
        SET lastname =name1;
        END IF;
        IF ROUND(RAND())=1 THEN
        SET lastname = CONCAT(name1,name2);
        END IF;
        insert INTO t_test1(first_name,last_name,sex,score,copy_id) VALUES (firstname,lastname,sex,score,rowid);  
        END WHILE;
    END //
DELIMITER ;

call add_user(1000);

1）union/union all [✓]

select first_name from t_test1
union all
select first_name from t_test2;

2）distinct [✓]

select distinct first_name from t_test1;

3）group by [✓]

select first_name,count(*) from t_test1 group by first_name;

4）order by [✓]

select * from t_test1 order by first_name;

5）group by/order by [✓]

select first_name,count(*) from t_test1 group by first_name order by 2;

6）分页查询 [✓]

select * from t_test1 limit 10;
select * from t_test1 limit 10,10;

7）内连接 [✓]

select t1.id,t1.first_name,t2.last_name from t_test1 t1,t_test2 t2 where t1.id = t2.id;

8）左连接 [✓]

select t1.id,t1.first_name,t2.last_name from t_test1 t1 left join t_test2 t2 on t1.id = t2.id and t1.id=100;

9）右连接 [✓]

select t1.id,t1.first_name,t2.last_name from t_test1 t1 right join t_test2 t2 on t1.id = t2.id and t1.id=100;

10）查询列上使用表达式 [✓]

select * from t_test1 where id + 1 = 100;

11）负向操作符!=等 [✓]

select count(*) from t_test1 where first_name <> '周';

12）like [✓]

select id,first_name from t_test1 where first_name like '%周%';

13）having [✓]

select e.id, count(e.id), round(avg(e.score), 2)
  from t_test1 e
 group by e.id
having avg(e.score) > (select avg(score) from t_test1);

14）多个过滤条件通过or连接 [✓]

select a.id, a.first_name, b.id, b.last_name
  from t_test1 a, t_test2 b
 where a.id = b.id
    or a.id = 100
    or a.first_name = '周'
    or a.first_name = '明军'
    or b.id = 100
    or b.first_name = '王'
    or b.last_name = '七民';

15) 重复查询子句 [✓]

select t1.first_name
  from (select id,first_name from t_test1) t1,
       (select id,first_name from t_test2) t2
 where t1.id = t2.id
   and t1.first_name = '周';

16）标量子查询 [✓]

select e.id,
       e.first_name,
       (select d.first_name from t_test2 d where d.id = e.id) as first_name
  from t_test1 e;

17）内嵌视图子查询 [✓]

select a.first_name, b.last_name
  from t_test1 a, (select id,last_name from t_test2) b
 where a.id = b.id;

18）子查询 [✓]

select first_name
  from t_test1
 where id = (select max(id) from t_test2);

19）in/not in后面是常量 [✓]

select * from t_test1 where id in (1,2,3);

20） in/not in后面是子查询 [✓]

select * from t_test1 where id in(select id from t_test2);

21） exists/not exists [✓]

select * from t_test1 A where exists (select 1 from t_test2 B where B.id = A.id);

22）cross join [✓]

SELECT 
    a.first_name, b.last_name
FROM
    t_test1 AS a
        CROSS JOIN
    t_test2 AS b;