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