Day06_MySQLLearning

Day06_MySQLLearning

30_union面试题

• 
  

  代码：

  mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> use worker;
Database changed
mysql> create table a
-> (id char(1),
-> num int)
-> engine myisam charset utf8;
Query OK, 0 rows affected (0.16 sec)
mysql> insert into a values('a' , 5),('b' , 10),('c' , 15),('d' , 10);
Query OK, 4 rows affected (0.30 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> create table b
-> (id char(1),
-> num int)
-> engine myisam charset utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into b values('b' , 5),('c' , 15),('d' , 20),('e' , 99);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from a;
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
+------+------+
4 rows in set (0.03 sec)
mysql> select * from b;
+------+------+
| id | num |
+------+------+
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
+------+------+
4 rows in set (0.00 sec)
mysql> #可以用左连接来做
mysql> select a.* , b.* from
-> a left join b on a.id = b.id;
+------+------+------+------+
| id | num | id | num |
+------+------+------+------+
| a | 5 | NULL | NULL |
| b | 10 | b | 5 |
| c | 15 | c | 15 |
| d | 10 | d | 20 |
+------+------+------+------+
4 rows in set (0.02 sec)
mysql> select * from
-> a left join b on a.id = b.id;
+------+------+------+------+
| id | num | id | num |
+------+------+------+------+
| a | 5 | NULL | NULL |
| b | 10 | b | 5 |
| c | 15 | c | 15 |
| d | 10 | d | 20 |
+------+------+------+------+
4 rows in set (0.00 sec)
mysql> #再把上面的结果看成一张临时表，再次from型子查询，计算a.num+b.num的和
mysql> #如果遇到坑，查ifnull函数
mysql> #而且少了e，只好左连 union 右连，再子查询
mysql> #换个思路，先把2张表的数据union到一块，再利用sum()函数来相加
mysql> select * from a;
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
+------+------+
4 rows in set (0.00 sec)
mysql> select * from b;
+------+------+
| id | num |
+------+------+
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
+------+------+
4 rows in set (0.00 sec)
mysql> select * from a
-> union
-> select * from b;
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
| b | 5 |
| d | 20 |
| e | 99 |
+------+------+
7 rows in set (0.00 sec)
mysql> select id , sum(num) from (
-> select * from a
-> union
-> select * from b
-> )as temp
-> group by id;
+------+----------+
| id | sum(num) |
+------+----------+
| a | 5 |
| b | 15 |
| c | 15 |
| d | 30 |
| e | 99 |
+------+----------+
5 rows in set (0.05 sec)
mysql> #c错了，不要去重复
mysql> select id , sum(num) from (
-> select * from a
-> union all
-> select * from b
-> )
-> group by id;
ERROR 1248 (42000): Every derived table must have its own alias
mysql> #每个衍生的表都必须有自己的别名
mysql> select id , sum(num) from (
-> select * from a
-> union all
-> select * from b
-> )as temp
-> group by id;
+------+----------+
| id | sum(num) |
+------+----------+
| a | 5 |
| b | 15 |
| c | 30 |
| d | 30 |
| e | 99 |
+------+----------+
5 rows in set (0.02 sec)
mysql> exit
  

31_MySQL数学函数与字符串函数

• 
  

  代码：

  mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> use worker;
Database changed
mysql> #函数
mysql> select 3;
+---+
| 3 |
+---+
| 3 |
+---+
1 row in set (0.00 sec)
mysql> select 3-5;
+-----+
| 3-5 |
+-----+
| -2 |
+-----+
1 row in set (0.20 sec)
mysql> select abs(3-5);
+----------+
| abs(3-5) |
+----------+
| 2 |
+----------+
1 row in set (0.08 sec)
mysql> #返回一个数字的二进制表示， 7 -> 111
mysql> select bin(7);
+--------+
| bin(7) |
+--------+
| 111 |
+--------+
1 row in set (0.06 sec)
mysql> #返回一个数字的八进制表示， 19 -> 23
mysql> select oct(19);
+---------+
| oct(19) |
+---------+
| 23 |
+---------+
1 row in set (0.11 sec)
mysql> #返回一个数字的十六进制表示，20 -> 14
mysql> select hex(20);
+---------+
| hex(20) |
+---------+
| 14 |
+---------+
1 row in set (0.19 sec)
mysql> #对小数取整
mysql> #舍弃取整
mysql> select floor(3.25);
+-------------+
| floor(3.25) |
+-------------+
| 3 |
+-------------+
1 row in set (0.09 sec)
mysql> select floor(3.69);
+-------------+
| floor(3.69) |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.225481355772802 |
+-------------------+
1 row in set (0.09 sec)
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.8160481050760002 |
+--------------------+
1 row in set (0.00 sec)
mysql> select rand();
+---------------------+
| rand() |
+---------------------+
| 0.40379648879523994 |
+---------------------+
1 row in set (0.00 sec)
mysql> select rand(5);
+---------------------+
| rand(5) |
+---------------------+
| 0.40613597483014313 |
+---------------------+
1 row in set (0.00 sec)
mysql> select rand(10);
+--------------------+
| rand(10) |
+--------------------+
| 0.6570515219653505 |
+--------------------+
1 row in set (0.00 sec)
mysql> select rand()*10;
+-------------------+
| rand()*10 |
+-------------------+
| 5.708381594818441 |
+-------------------+
1 row in set (0.98 sec)
mysql> select rand()*10;
+-------------------+
| rand()*10 |
+-------------------+
| 6.428013617571456 |
+-------------------+
1 row in set (0.00 sec)
mysql> select rand()*10;
+-------------------+
| rand()*10 |
+-------------------+
| 5.014923610738408 |
+-------------------+
1 row in set (0.00 sec)
mysql> select floor(rand()*10+5);
+--------------------+
| floor(rand()*10+5) |
+--------------------+
| 10 |
+--------------------+
1 row in set (0.06 sec)
mysql> select floor(rand()*10+5);
+--------------------+
| floor(rand()*10+5) |
+--------------------+
| 8 |
+--------------------+
1 row in set (0.00 sec)
mysql> select floor(rand()*10+5);
+--------------------+
| floor(rand()*10+5) |
+--------------------+
| 7 |
+--------------------+
1 row in set (0.00 sec)
mysql> select floor(rand()*10+5);
+--------------------+
| floor(rand()*10+5) |
+--------------------+
| 14 |
+--------------------+
1 row in set (0.00 sec)
mysql> select floor(rand()*10+5);
+--------------------+
| floor(rand()*10+5) |
+--------------------+
| 14 |
+--------------------+
1 row in set (0.00 sec)
mysql> select floor(rand()*10+5);
+--------------------+
| floor(rand()*10+5) |
+--------------------+
| 13 |
+--------------------+
1 row in set (0.00 sec)
mysql> select floor(rand()*10+5);
+--------------------+
| floor(rand()*10+5) |
+--------------------+
| 8 |
+--------------------+
1 row in set (0.00 sec)
mysql> select ceiling(3.23);
+---------------+
| ceiling(3.23) |
+---------------+
| 4 |
+---------------+
1 row in set (0.08 sec)
mysql> #ceiling向上取整
mysql> #想取出第4个栏目下，所有商品的, goods_id
mysql> select goods_id , cat_id from goods where cat_id = 4;
mysql> #进一步，像把4栏目下的goods_id ，拼接起来
mysql> #group_concat 默认用‘，’帮我们拼接
mysql> select group_concat(goods_id) from goods where cat_id = 4 group by cat_id;
mysql> select group_concat(goods_id , '') from goods where cat_id = 4 group by cat_id;
mysql> #字符串函数
mysql> #计算字符的ascii码，例 A 的ascii码是65
mysql> select ascii('A');
+------------+
| ascii('A') |
+------------+
| 65 |
+------------+
1 row in set (0.00 sec)
mysql> select ascii('a');
+------------+
| ascii('a') |
+------------+
| 97 |
+------------+
1 row in set (0.01 sec)
mysql> #length计算的是字节长度 , 不同的编码方式字节长度不同
mysql> select length('中华民国');
+--------------------+
| length('中华民国') |
+--------------------+
| 8 |
+--------------------+
1 row in set (0.05 sec)
mysql> select * from t;
+------+----------+
| tid | tname |
+------+----------+
| 1 | 国安 |
| 2 | 甲花 |
| 3 | 公益联队 |
+------+----------+
3 rows in set (0.63 sec)
mysql> select * , length(tname) from t;
+------+----------+---------------+
| tid | tname | length(tname) |
+------+----------+---------------+
| 1 | 国安 | 6 |
| 2 | 甲花 | 6 |
| 3 | 公益联队 | 12 |
+------+----------+---------------+
3 rows in set (0.03 sec)
mysql> #char_length计算的是字符数
mysql> select * , length(tname) , char_length(tname) from t;
+------+----------+---------------+--------------------+
| tid | tname | length(tname) | char_length(tname) |
+------+----------+---------------+--------------------+
| 1 | 国安 | 6 | 2 |
| 2 | 甲花 | 6 | 2 |
| 3 | 公益联队 | 12 | 4 |
+------+----------+---------------+--------------------+
3 rows in set (0.03 sec)
mysql> #反转字符串
mysql> select tid , tname , reverse(tname) from t;
+------+----------+----------------+
| tid | tname | reverse(tname) |
+------+----------+----------------+
| 1 | 国安 | 安国 |
| 2 | 甲花 | 花甲 |
| 3 | 公益联队 | 队联益公 |
+------+----------+----------------+
3 rows in set (0.03 sec)
mysql> select position('@' in 'abc@sina.com');
+---------------------------------+
| position('@' in 'abc@sina.com') |
+---------------------------------+
| 4 |
+---------------------------------+
1 row in set (0.05 sec)
mysql> select right('abc@sina.com' , 8);
+---------------------------+
| right('abc@sina.com' , 8) |
+---------------------------+
| sina.com |
+---------------------------+
1 row in set (0.02 sec)
mysql> #真实案例：某网站有email字段，存邮件地址
mysql> #想调查163 ， 126 ，qq ， gmail 邮箱的比例
mysql> create table test14
-> (uname varchar(20) ,
-> email varchar(30) )
-> engine myisam charset utf8;
Query OK, 0 rows affected (0.78 sec)
mysql> insert into test14
-> values
-> ('张三' , 'zhangsan@163.com'),
-> ('lily' , 'lily@126.com'),
-> ('lilei' , 'leili@qq.com'),
-> ('mr gao' , 'gao@qq.com');
mysql> select * from test14;
+--------+------------------+
| uname | email |
+--------+------------------+
| 张三 | zhangsan@163.com |
| lily | lily@126.com |
| lilei | leili@qq.com |
| mr gao | gao@qq.com |
+--------+------------------+
4 rows in set (0.03 sec)
mysql> #问题在于，想调查邮箱后缀，但后缀是邮件地址的一部分
mysql> #得先把后缀想办法取出来
mysql> #提示：用字符串函数，长度，位置，right函数
mysql> select * , right(length(email) , position('@' in email)) from test14;
+--------+------------------+-----------------------------------------------+
| uname | email | right(length(email) , position('@' in email)) |
+--------+------------------+-----------------------------------------------+
| 张三 | zhangsan@163.com | 16 |
| lily | lily@126.com | 12 |
| lilei | leili@qq.com | 12 |
| mr gao | gao@qq.com | 10 |
+--------+------------------+-----------------------------------------------+
4 rows in set (0.03 sec)
mysql> select * , right(email , length(email)-position('@' in email)) from test14;
+--------+------------------+-----------------------------------------------------+
| uname | email | right(email , length(email)-position('@' in email)) |
+--------+------------------+-----------------------------------------------------+
| 张三 | zhangsan@163.com | 163.com |
| lily | lily@126.com | 126.com |
| lilei | leili@qq.com | qq.com |
| mr gao | gao@qq.com | qq.com |
+--------+------------------+-----------------------------------------------------+
4 rows in set (0.03 sec)
mysql> #这是通过字符串函数取出后缀
mysql> #经过这次事件之后，他们的表结构修改了，变得更加高效
mysql> #把email拆成@前后2部分，放在两个列
mysql> exit
  

32_MySQL日期时间函数与流程控制函数

• 
  

  代码：

  mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> use worker
Database changed
mysql> #接下来看日期时间s
mysql> #接下来看日期时间函数
mysql> #取当前时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2019-08-31 17:47:17 |
+---------------------+
1 row in set (0.13 sec)
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2019-08-31 |
+------------+
1 row in set (0.03 sec)
mysql> #now返回datetime格式，curdate返回date格式
mysql> #返回时间部分
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 17:52:15 |
+-----------+
1 row in set (0.00 sec)
mysql> #查询某日是那一周的第几天
mysql> select dayofweek(curdate());
+----------------------+
| dayofweek(curdate()) |
+----------------------+
| 7 |
+----------------------+
1 row in set (0.05 sec)
mysql> #注意，周日是第一天
mysql> #计算今天是2019年的第几周
mysql> select week(curdate());
+-----------------+
| week(curdate()) |
+-----------------+
| 34 |
+-----------------+
1 row in set (0.00 sec)
mysql> #一个实际的案例
mysql> #按周统计加班时间
mysql> create table jiaban(
-> num int ,
-> dt date)
-> engine myisam charset utf8;
Query OK, 0 rows affected (0.08 sec)
mysql> insert into jiaban
-> values
-> (5 , '2012-09-01'),
-> (6 , '2012-09-02'),
-> (7 , '2012-09-03'),
-> (8 , '2012-09-04'),
-> (9 , '2012-09-05'),
-> (10 , '2012-09-06'),
-> (11 , '2012-09-07'),
-> (12 , '2012-09-08'),
-> (13 , '2012-09-09'),
-> (14 , '2012-09-10'),
-> (15 , '2012-09-11'),
-> (16 , '2012-09-12');
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> select * from jiaban;
+------+------------+
| num | dt |
+------+------------+
| 5 | 2012-09-01 |
| 6 | 2012-09-02 |
| 7 | 2012-09-03 |
| 8 | 2012-09-04 |
| 9 | 2012-09-05 |
| 10 | 2012-09-06 |
| 11 | 2012-09-07 |
| 12 | 2012-09-08 |
| 13 | 2012-09-09 |
| 14 | 2012-09-10 |
| 15 | 2012-09-11 |
| 16 | 2012-09-12 |
+------+------------+
12 rows in set (0.00 sec)
mysql> #难点：group 到底group by谁？
mysql> select * , week(dt) from jiaban;
+------+------------+----------+
| num | dt | week(dt) |
+------+------------+----------+
| 5 | 2012-09-01 | 35 |
| 6 | 2012-09-02 | 36 |
| 7 | 2012-09-03 | 36 |
| 8 | 2012-09-04 | 36 |
| 9 | 2012-09-05 | 36 |
| 10 | 2012-09-06 | 36 |
| 11 | 2012-09-07 | 36 |
| 12 | 2012-09-08 | 36 |
| 13 | 2012-09-09 | 37 |
| 14 | 2012-09-10 | 37 |
| 15 | 2012-09-11 | 37 |
| 16 | 2012-09-12 | 37 |
+------+------------+----------+
12 rows in set (0.00 sec)
mysql> select sum(num) , week(dt) as wk from jiaban group by wk;
+----------+------+
| sum(num) | wk |
+----------+------+
| 5 | 35 |
| 63 | 36 |
| 58 | 37 |
+----------+------+
3 rows in set (0.33 sec)
mysql> #md5是不可逆的e
mysql> #良好的加密：
mysql> #1.不可逆
mysql> #2.碰撞性低
mysql> select md5('111111');
+----------------------------------+
| md5('111111') |
+----------------------------------+
| 96e79218965eb72c92a549dd5a330112 |
+----------------------------------+
1 row in set (0.13 sec)
mysql> select md5('forever9017');
+----------------------------------+
| md5('forever9017') |
+----------------------------------+
| 1f10f395c7d5acdf4a0eec4dafa1b778 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> create table teat15
-> (sname varchar(5),
-> gender tinyint)
-> engine myisam charset utf8;
Query OK, 0 rows affected (0.14 sec)
mysql> alter table teat15 rename to test15;
Query OK, 0 rows affected (0.06 sec)
mysql> insert into test15
-> values
-> ('张三' , 1),
-> ('韩梅梅' , 0),
-> ('李宇春' , 2);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test15;
+--------+--------+
| sname | gender |
+--------+--------+
| 张三 | 1 |
| 韩梅梅 | 0 |
| 李宇春 | 2 |
+--------+--------+
3 rows in set (0.00 sec)
mysql> #现在性别是数字表示，能否显示出男/女/春,这3个中文给客户看
mysql> #即 要做判断1->男， 0->女 , 2->春
mysql> #select sname , gender from test15
mysql> #针对gender变量 能否做分支控制？
mysql> #case 值
mysql> #when 某种可能
mysql> #then 返回值
mysql> #when 另一种可能
mysql> #then 返回值
mysql> #else 默认值
mysql> #end
mysql> select sname,
-> case gender
-> when 1
-> then '男'
-> when 0
-> then '女'
-> else '春'
-> end
-> from test15;
+--------+-------------------------------------------------------------+
| sname | case gender
when 1
then '男'
when 0
then '女'
else '春'
end |
+--------+-------------------------------------------------------------+
| 张三 | 男 |
| 韩梅梅 | 女 |
| 李宇春 | 春 |
+--------+-------------------------------------------------------------+
3 rows in set (0.06 sec)
mysql> select sname,
-> case gender
-> when 1
-> then '男'
-> when 0
-> then '女'
-> else '春'
-> end as xingbie
-> from test15;
+--------+---------+
| sname | xingbie |
+--------+---------+
| 张三 | 男 |
| 韩梅梅 | 女 |
| 李宇春 | 春 |
+--------+---------+
3 rows in set (0.01 sec)
mysql> #判断性别，让女士优先
mysql> select sname ,
-> if(gender = 0 , '优先' , '等待') as vip
-> from test15;
+--------+------+
| sname | vip |
+--------+------+
| 张三 | 等待 |
| 韩梅梅 | 优先 |
| 李宇春 | 等待 |
+--------+------+
3 rows in set (0.03 sec)
mysql> #if()相当于php中的三元运算符
mysql> #ifnull()的用法，判断第1个表达式是否为null ，如为null，返回第2个表达式的值
mysql> #如不为null，返回自身，即表达式1
mysql> select ifnull(null , 0);
+------------------+
| ifnull(null , 0) |
+------------------+
| 0 |
+------------------+
1 row in set (0.06 sec)
mysql> select ifnull('' , 0);
+----------------+
| ifnull('' , 0) |
+----------------+
| |
+----------------+
1 row in set (0.00 sec)
mysql> select ifnull('aaa' , 0);
+-------------------+
| ifnull('aaa' , 0) |
+-------------------+
| aaa |
+-------------------+
1 row in set (0.00 sec)
mysql> select nullif(1 , 1);
+---------------+
| nullif(1 , 1) |
+---------------+
| NULL |
+---------------+
1 row in set (0.00 sec)
mysql> select nullif(2 , 1);
+---------------+
| nullif(2 , 1) |
+---------------+
| 2 |
+---------------+
1 row in set (0.00 sec)
mysql> #nullif(),如果expr1 = expr2 返回null ， 否则返回expr1 , 见上例
mysql> #系统调试函数
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.05 sec)
mysql> #user函数返回“用户及所在主机”，判断自己的身份
mysql> #database（），返回当前正在操作的库名
mysql> select database();
+------------+
| database() |
+------------+
| worker |
+------------+
1 row in set (0.00 sec)
mysql> #version()判断数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.27 |
+-----------+
1 row in set (0.03 sec)
mysql> #比如有的服务器比较古老，有些功能不支持，出于兼容性考虑，要先判断一下mysql的版本，此时可以用version()函数
mysql> exit