mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.7015205088085686 |
+--------------------+
1 row in set (0.00 sec)
mysql> select floor(1.23);
+-------------+
| floor(1.23) |
+-------------+
| 1 |
+-------------+
1 row in set (0.01 sec)
mysql> select rand()*4-2;
+---------------------+
| rand()*4-2 |
+---------------------+
| -1.1371785096239098 |
+---------------------+
1 row in set (0.00 sec)
mysql> select round(1.234,2);
+----------------+
| round(1.234,2) |
+----------------+
| 1.23 |
+----------------+
1 row in set (0.00 sec)
mysql> select ceil(2.3);
+-----------+
| ceil(2.3) |
+-----------+
| 3 |
+-----------+
1 row in set (0.00 sec)
mysql> select age,if(age%2=1,true,false) as new_age from student
limit 10;
+------+---------+
| age | new_age |
+------+---------+
| 22 | 0 |
| 24 | 0 |
| 22 | 0 |
| 24 | 0 |
| 22 | 0 |
| 21 | 1 |
| 23 | 1 |
| 22 | 0 |
| 21 | 1 |
| 23 | 1 |
+------+---------+
10 rows in set (0.00 sec)
mysql> select clazz
-> ,case when clazz like '%一班' then '1班'
-> when clazz like '%二班' then '2班'
-> when clazz like '%三班' then '3班'
-> else '其他班'
-> end as new_clazz
-> from student
-> limit 10;
+--------------+-----------+
| clazz | new_clazz |
+--------------+-----------+
| 文科六班 | 其他班 |
| 文科六班 | 其他班 |
| 理科六班 | 其他班 |
| 理科三班 | 3班 |
| 理科五班 | 其他班 |
| 理科二班 | 2班 |
| 文科六班 | 其他班 |
| 理科六班 | 其他班 |
| 理科一班 | 1班 |
| 理科六班 | 其他班 |
+--------------+-----------+
10 rows in set (0.00 sec)
mysql> select clazz
-> ,case clazz when '文科一班' then '1班'
-> when '理科一班' then '1班'
-> when '文科二班' then '2班'
-> when '理科二班' then '2班'
-> else '其他班'
-> end as new_clazz
-> from student
-> limit 10;
+--------------+-----------+
| clazz | new_clazz |
+--------------+-----------+
| 文科六班 | 其他班 |
| 文科六班 | 其他班 |
| 理科六班 | 其他班 |
| 理科三班 | 其他班 |
| 理科五班 | 其他班 |
| 理科二班 | 2班 |
| 文科六班 | 其他班 |
| 理科六班 | 其他班 |
| 理科一班 | 1班 |
| 理科六班 | 其他班 |
+--------------+-----------+
10 rows in set (0.00 sec)
mysql> select coalesce(2,3,4,5,6,7);
+-----------------------+
| coalesce(2,3,4,5,6,7) |
+-----------------------+
| 2 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select coalesce(2<3,3>5,4>5,3>5);
+---------------------------+
| coalesce(2<3,3>5,4>5,3>5) |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.00 sec)
mysql> select coalesce(null,2,3,4,5,6,7);
+----------------------------+
| coalesce(null,2,3,4,5,6,7) |
+----------------------------+
| 2 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select coalesce(2,3,4,null,5,6,7);
+----------------------------+
| coalesce(2,3,4,null,5,6,7) |
+----------------------------+
| 2 |
+----------------------------+
1 row in set (0.00 sec)
-
mysql> select power(2,4);
+------------+
| power(2,4) |
+------------+
| 16 |
+------------+
1 row in set (0.00 sec)
mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2022-03-05 20:25:58 |
+---------------------+
1 row in set (0.00 sec)
mysql> select str_to_date('2022年03月05日 20点29分30秒','%Y年%m月%d日 %H点%i分%s秒');
+------------------------------------------------------------------------------------+
| str_to_date('2022年03月05日 20点29分30秒','%Y年%m月%d日 %H点%i分%s秒') |
+------------------------------------------------------------------------------------+
| 2022-03-05 20:29:30 |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_sub('2022-03-01',INTERVAL 3 DAY);
+---------------------------------------+
| date_sub('2022-03-01',INTERVAL 3 DAY) |
+---------------------------------------+
| 2022-02-26 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select date_sub('2022-03-01',interval 3 day);
+---------------------------------------+
| date_sub('2022-03-01',interval 3 day) |
+---------------------------------------+
| 2022-02-26 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select date_sub(str_to_date('2022年03月05日 20点29分30秒','%Y年%m月%d日 %H点%i分%s秒'),interval 3 day);
+-------------------------------------------------------------------------------------------------------------+
| date_sub(str_to_date('2022年03月05日 20点29分30秒','%Y年%m月%d日 %H点%i分%s秒'),interval 3 day) |
+-------------------------------------------------------------------------------------------------------------+
| 2022-03-02 20:29:30 |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select t1.clazz
-> ,t1.cnt
-> from
-> (
-> select clazz
-> ,count(*) as cnt
-> from student
-> group by clazz
-> )t1
-> where t1.cnt > 80
-> ;
+--------------+-----+
| clazz | cnt |
+--------------+-----+
| 文科三班 | 94 |
| 文科二班 | 87 |
| 文科五班 | 84 |
| 文科六班 | 104 |
| 文科四班 | 81 |
| 理科六班 | 92 |
| 理科四班 | 91 |
+--------------+-----+
7 rows in set (0.00 sec)
mysql> select clazz
-> ,count(*) as cnt
-> from student
-> group by clazz
-> having cnt > 80;
+--------------+-----+
| clazz | cnt |
+--------------+-----+
| 文科三班 | 94 |
| 文科二班 | 87 |
| 文科五班 | 84 |
| 文科六班 | 104 |
| 文科四班 | 81 |
| 理科六班 | 92 |
| 理科四班 | 91 |
+--------------+-----+
7 rows in set (0.00 sec)
mysql> select id,sum(score) as sum_score from score group by id order by sum_score limit 10;
+------------+-----------+
| id | sum_score |
+------------+-----------+
| 1500100991 | 76 |
| 1500100891 | 147 |
| 1500100428 | 150 |
| 1500100589 | 155 |
| 1500100585 | 162 |
| 1500100181 | 165 |
| 1500100289 | 169 |
| 1500100383 | 169 |
| 1500100420 | 170 |
| 1500100861 | 170 |
+------------+-----------+
10 rows in set (0.01 sec)
mysql> select id,sum(score) as sum_score from score group by id order by sum_score asc limit 10;
+------------+-----------+
| id | sum_score |
+------------+-----------+
| 1500100991 | 76 |
| 1500100891 | 147 |
| 1500100428 | 150 |
| 1500100589 | 155 |
| 1500100585 | 162 |
| 1500100181 | 165 |
| 1500100289 | 169 |
| 1500100383 | 169 |
| 1500100420 | 170 |
| 1500100861 | 170 |
+------------+-----------+
10 rows in set (0.00 sec)
mysql> select id,sum(score) as sum_score from score group by id order by sum_score desc limit 10;
+------------+-----------+
| id | sum_score |
+------------+-----------+
| 1500100929 | 630 |
| 1500100080 | 628 |
| 1500100308 | 628 |
| 1500100873 | 612 |
| 1500100418 | 611 |
| 1500100258 | 604 |
| 1500100875 | 595 |
| 1500100930 | 589 |
| 1500100596 | 587 |
| 1500100834 | 586 |
+------------+-----------+
10 rows in set (0.00 sec)
mysql> select id,sum(score) as sum_score from score group by id order by sum_score desc,id desc limit 10;
+------------+-----------+
| id | sum_score |
+------------+-----------+
| 1500100929 | 630 |
| 1500100308 | 628 |
| 1500100080 | 628 |
| 1500100873 | 612 |
| 1500100418 | 611 |
| 1500100258 | 604 |
| 1500100875 | 595 |
| 1500100930 | 589 |
| 1500100596 | 587 |
| 1500100834 | 586 |
+------------+-----------+
10 rows in set (0.00 sec)
mysql> select * from ( select * from student limit 10 )t1 union ( select * from student limit 5,10 );
+------------+-----------+------+--------+--------------+
| id | name | age | gender | clazz |
+------------+-----------+------+--------+--------------+
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 |
| 1500100002 | 吕金鹏 | 24 | 男 | 文科六班 |
| 1500100003 | 单乐蕊 | 22 | 女 | 理科六班 |
| 1500100004 | 葛德曜 | 24 | 男 | 理科三班 |
| 1500100005 | 宣谷芹 | 22 | 女 | 理科五班 |
| 1500100006 | 边昂雄 | 21 | 男 | 理科二班 |
| 1500100007 | 尚孤风 | 23 | 女 | 文科六班 |
| 1500100008 | 符半双 | 22 | 女 | 理科六班 |
| 1500100009 | 沈德昌 | 21 | 男 | 理科一班 |
| 1500100010 | 羿彦昌 | 23 | 男 | 理科六班 |
| 1500100011 | 宰运华 | 21 | 男 | 理科三班 |
| 1500100012 | 梁易槐 | 21 | 女 | 理科一班 |
| 1500100013 | 逯君昊 | 24 | 男 | 文科二班 |
| 1500100014 | 羿旭炎 | 23 | 男 | 理科五班 |
| 1500100015 | 宦怀绿 | 21 | 女 | 理科一班 |
+------------+-----------+------+--------+--------------+
15 rows in set (0.00 sec)
mysql> select * from ( select * from student limit 10 )t1 union all( select * from student limit 5,10 );
+------------+-----------+------+--------+--------------+
| id | name | age | gender | clazz |
+------------+-----------+------+--------+--------------+
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 |
| 1500100002 | 吕金鹏 | 24 | 男 | 文科六班 |
| 1500100003 | 单乐蕊 | 22 | 女 | 理科六班 |
| 1500100004 | 葛德曜 | 24 | 男 | 理科三班 |
| 1500100005 | 宣谷芹 | 22 | 女 | 理科五班 |
| 1500100006 | 边昂雄 | 21 | 男 | 理科二班 |
| 1500100007 | 尚孤风 | 23 | 女 | 文科六班 |
| 1500100008 | 符半双 | 22 | 女 | 理科六班 |
| 1500100009 | 沈德昌 | 21 | 男 | 理科一班 |
| 1500100010 | 羿彦昌 | 23 | 男 | 理科六班 |
| 1500100006 | 边昂雄 | 21 | 男 | 理科二班 |
| 1500100007 | 尚孤风 | 23 | 女 | 文科六班 |
| 1500100008 | 符半双 | 22 | 女 | 理科六班 |
| 1500100009 | 沈德昌 | 21 | 男 | 理科一班 |
| 1500100010 | 羿彦昌 | 23 | 男 | 理科六班 |
| 1500100011 | 宰运华 | 21 | 男 | 理科三班 |
| 1500100012 | 梁易槐 | 21 | 女 | 理科一班 |
| 1500100013 | 逯君昊 | 24 | 男 | 文科二班 |
| 1500100014 | 羿旭炎 | 23 | 男 | 理科五班 |
| 1500100015 | 宦怀绿 | 21 | 女 | 理科一班 |
+------------+-----------+------+--------+--------------+
20 rows in set (0.01 sec)
mysql> select * from student,score limit 10;
+------------+-----------+------+--------+--------------+------------+------------+-------+
| id | name | age | gender | clazz | id | subject_id | score |
+------------+-----------+------+--------+--------------+------------+------------+-------+
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 1000001 | 98 |
| 1500100002 | 吕金鹏 | 24 | 男 | 文科六班 | 1500100001 | 1000001 | 98 |
| 1500100003 | 单乐蕊 | 22 | 女 | 理科六班 | 1500100001 | 1000001 | 98 |
| 1500100004 | 葛德曜 | 24 | 男 | 理科三班 | 1500100001 | 1000001 | 98 |
| 1500100005 | 宣谷芹 | 22 | 女 | 理科五班 | 1500100001 | 1000001 | 98 |
| 1500100006 | 边昂雄 | 21 | 男 | 理科二班 | 1500100001 | 1000001 | 98 |
| 1500100007 | 尚孤风 | 23 | 女 | 文科六班 | 1500100001 | 1000001 | 98 |
| 1500100008 | 符半双 | 22 | 女 | 理科六班 | 1500100001 | 1000001 | 98 |
| 1500100009 | 沈德昌 | 21 | 男 | 理科一班 | 1500100001 | 1000001 | 98 |
| 1500100010 | 羿彦昌 | 23 | 男 | 理科六班 | 1500100001 | 1000001 | 98 |
+------------+-----------+------+--------+--------------+------------+------------+-------+
10 rows in set (0.00 sec)
mysql> select * from student t1,score t2 where t1.id = t2.id limit 10;
+------------+-----------+------+--------+--------------+------------+------------+-------+
| id | name | age | gender | clazz | id | subject_id | score |
+------------+-----------+------+--------+--------------+------------+------------+-------+
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 1000001 | 98 |
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 1000002 | 5 |
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 1000003 | 137 |
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 1000004 | 29 |
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 1000005 | 85 |
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 1000006 | 52 |
| 1500100002 | 吕金鹏 | 24 | 男 | 文科六班 | 1500100002 | 1000001 | 139 |
| 1500100002 | 吕金鹏 | 24 | 男 | 文科六班 | 1500100002 | 1000002 | 102 |
| 1500100002 | 吕金鹏 | 24 | 男 | 文科六班 | 1500100002 | 1000003 | 44 |
| 1500100002 | 吕金鹏 | 24 | 男 | 文科六班 | 1500100002 | 1000004 | 18 |
+------------+-----------+------+--------+--------------+------------+------------+-------+
10 rows in set (0.00 sec)
mysql> select * from student t1 join score t2 on t1.id = t2.id limit 10;
+------------+-----------+------+--------+--------------+------------+------------+-------+
| id | name | age | gender | clazz | id | subject_id | score |
+------------+-----------+------+--------+--------------+------------+------------+-------+
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 1000001 | 98 |
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 1000002 | 5 |
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 1000003 | 137 |
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 1000004 | 29 |
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 1000005 | 85 |
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 1000006 | 52 |
| 1500100002 | 吕金鹏 | 24 | 男 | 文科六班 | 1500100002 | 1000001 | 139 |
| 1500100002 | 吕金鹏 | 24 | 男 | 文科六班 | 1500100002 | 1000002 | 102 |
| 1500100002 | 吕金鹏 | 24 | 男 | 文科六班 | 1500100002 | 1000003 | 44 |
| 1500100002 | 吕金鹏 | 24 | 男 | 文科六班 | 1500100002 | 1000004 | 18 |
+------------+-----------+------+--------+--------------+------------+------------+-------+
10 rows in set (0.00 sec)
mysql> select * from student t1 join( select id ,sum(score) as score_sum from score group by id )t2 on t1.id = t2.id limit
10;
+------------+-----------+------+--------+--------------+------------+-----------+
| id | name | age | gender | clazz | id | score_sum |
+------------+-----------+------+--------+--------------+------------+-----------+
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 406 |
| 1500100002 | 吕金鹏 | 24 | 男 | 文科六班 | 1500100002 | 440 |
| 1500100003 | 单乐蕊 | 22 | 女 | 理科六班 | 1500100003 | 359 |
| 1500100004 | 葛德曜 | 24 | 男 | 理科三班 | 1500100004 | 421 |
| 1500100005 | 宣谷芹 | 22 | 女 | 理科五班 | 1500100005 | 395 |
| 1500100006 | 边昂雄 | 21 | 男 | 理科二班 | 1500100006 | 314 |
| 1500100007 | 尚孤风 | 23 | 女 | 文科六班 | 1500100007 | 418 |
| 1500100008 | 符半双 | 22 | 女 | 理科六班 | 1500100008 | 363 |
| 1500100009 | 沈德昌 | 21 | 男 | 理科一班 | 1500100009 | 251 |
| 1500100010 | 羿彦昌 | 23 | 男 | 理科六班 | 1500100010 | 402 |
+------------+-----------+------+--------+--------------+------------+-----------+
10 rows in set (0.01 sec)
mysql> select * from student t1 inner join( select id ,sum(scoore) as score_sum from score group by id )t2 on t1.id = t2.id
limit 10;
+------------+-----------+------+--------+--------------+------------+-----------+
| id | name | age | gender | clazz | id | score_sum |
+------------+-----------+------+--------+--------------+------------+-----------+
| 1500100001 | 施笑槐 | 22 | 女 | 文科六班 | 1500100001 | 406 |
| 1500100002 | 吕金鹏 | 24 | 男 | 文科六班 | 1500100002 | 440 |
| 1500100003 | 单乐蕊 | 22 | 女 | 理科六班 | 1500100003 | 359 |
| 1500100004 | 葛德曜 | 24 | 男 | 理科三班 | 1500100004 | 421 |
| 1500100005 | 宣谷芹 | 22 | 女 | 理科五班 | 1500100005 | 395 |
| 1500100006 | 边昂雄 | 21 | 男 | 理科二班 | 1500100006 | 314 |
| 1500100007 | 尚孤风 | 23 | 女 | 文科六班 | 1500100007 | 418 |
| 1500100008 | 符半双 | 22 | 女 | 理科六班 | 1500100008 | 363 |
| 1500100009 | 沈德昌 | 21 | 男 | 理科一班 | 1500100009 | 251 |
| 1500100010 | 羿彦昌 | 23 | 男 | 理科六班 | 1500100010 | 402 |
+------------+-----------+------+--------+--------------+------------+-----------+
10 rows in set (0.00 sec)
mysql> select t1.id,t1.name,t1.age,t1.clazz,t2.score_sum fromstudent t1 left outer join( select id ,sum(score) as score_summ from score group by id )t2 on t1.id = t2.id limit 10;
+------------+-----------+------+--------------+-----------+
| id | name | age | clazz | score_sum |
+------------+-----------+------+--------------+-----------+
| 1500100001 | 施笑槐 | 22 | 文科六班 | 406 |
| 1500100002 | 吕金鹏 | 24 | 文科六班 | 440 |
| 1500100003 | 单乐蕊 | 22 | 理科六班 | 359 |
| 1500100004 | 葛德曜 | 24 | 理科三班 | 421 |
| 1500100005 | 宣谷芹 | 22 | 理科五班 | 395 |
| 1500100006 | 边昂雄 | 21 | 理科二班 | 314 |
| 1500100007 | 尚孤风 | 23 | 文科六班 | 418 |
| 1500100008 | 符半双 | 22 | 理科六班 | 363 |
| 1500100009 | 沈德昌 | 21 | 理科一班 | 251 |
| 1500100010 | 羿彦昌 | 23 | 理科六班 | 402 |
+------------+-----------+------+--------------+-----------+
10 rows in set (0.01 sec)
mysql> select t1.id,t1.name,t1.age,t1.clazz,t2.score_sum from
student t1 right outer join( select id ,sum(score) as score_suum from score group by id )t2 on t1.id = t2.id limit 10;
+------------+-----------+------+--------------+-----------+
| id | name | age | clazz | score_sum |
+------------+-----------+------+--------------+-----------+
| 1500100001 | 施笑槐 | 22 | 文科六班 | 406 |
| 1500100002 | 吕金鹏 | 24 | 文科六班 | 440 |
| 1500100003 | 单乐蕊 | 22 | 理科六班 | 359 |
| 1500100004 | 葛德曜 | 24 | 理科三班 | 421 |
| 1500100005 | 宣谷芹 | 22 | 理科五班 | 395 |
| 1500100006 | 边昂雄 | 21 | 理科二班 | 314 |
| 1500100007 | 尚孤风 | 23 | 文科六班 | 418 |
| 1500100008 | 符半双 | 22 | 理科六班 | 363 |
| 1500100009 | 沈德昌 | 21 | 理科一班 | 251 |
| 1500100010 | 羿彦昌 | 23 | 理科六班 | 402 |
+------------+-----------+------+--------------+-----------+
10 rows in set (0.00 sec)
mysql> select t1.id,t1.name,t1.age,t1.clazz,t2.score_sum from
student t1 inner join( select id ,sum(score) as score_sum fromm score group by id )t2 on t1.id = t2.id limit 10;
+------------+-----------+------+--------------+-----------+
| id | name | age | clazz | score_sum |
+------------+-----------+------+--------------+-----------+
| 1500100001 | 施笑槐 | 22 | 文科六班 | 406 |
| 1500100002 | 吕金鹏 | 24 | 文科六班 | 440 |
| 1500100003 | 单乐蕊 | 22 | 理科六班 | 359 |
| 1500100004 | 葛德曜 | 24 | 理科三班 | 421 |
| 1500100005 | 宣谷芹 | 22 | 理科五班 | 395 |
| 1500100006 | 边昂雄 | 21 | 理科二班 | 314 |
| 1500100007 | 尚孤风 | 23 | 文科六班 | 418 |
| 1500100008 | 符半双 | 22 | 理科六班 | 363 |
| 1500100009 | 沈德昌 | 21 | 理科一班 | 251 |
| 1500100010 | 羿彦昌 | 23 | 理科六班 | 402 |
+------------+-----------+------+--------------+-----------+
10 rows in set (0.00 sec)