数据库大讲解第九讲

数据库大讲解第九讲

基本命令使用 作者：董哥

前期比较乱主要是为了熟练有一定基础的，只需跟着文档来就可以，不懂的地方欢迎留言

创建数据库，及各数据库表

主要采用的是Mysql数据库，其中会有提到其他数据库：

1. 前期·准备
   前期准备创建数据库employe并创建多个表
   前期是随便练习后边知识由浅入深
   前边几页是练习代码所需要的各种表
   里面有中文符号最好自己动手敲
   创建数据库

Host: 28.5.8.60 (Version 5.7.17)

Date: 2019-09-2

Writer: 煮不烂的鸡蛋 (Build 1.26)

select city,zip,avg(pay_rate),avg(salary)
from employe_tbl e
inner join employe_pay_tbl p
on e.emp_id=p.emp_id
group by cube(city,zip);//MySQL不支持
Having 子句在select与group by中的作用
相当于where在select的作用，换句话说where子句用于设定选择字段的条件，而having子句设置group by子句形成分组的条件having必须跟在groupby
子句之后，而在orderby子句之前
mysql> select city,avg(pay_rate),avg(salary)
-> from employe_tbl e
-> inner join employe_pay_tbl p
-> on e.emp_id=p.emp_id
-> where city !=‘greenwood’
-> group by city
-> having avg(salary)>2000
-> order by 3;
Orderby3指的是按照avg(salary)大小顺序进行排序

串接函数
在数据库oracle
Select ‘john’||’son’

在sqlserver 里面是
Select ‘john’+’son’
在MySQL里面是
Select concat（‘john’，‘son’）

±-------------±--------------±-------------+
| city | avg(pay_rate) | avg(salary) |
±-------------±--------------±-------------+
| indianapolis | 13.583333 | 20000.000000 |
| whiteland | NULL | 30000.000000 |
±-------------±--------------±-------------+
2 rows in set (0.00 sec)
select city+state from employe_tbl;
select city ||’,’||state from employe_tbl;
mysql> select concat(city,state) from employe_tbl;
±-------------------+
| concat(city,state) |
±-------------------+
| whitelandin |
| indianapolisin |
| greenwoodin |
| indianapolisin |
| indianapolisin |
| indianapolisin |
±-------------------+
6 rows in set (0.00 sec)

mysql> select concat(last_name,first_name) from employe_tbl;
±-----------------------------+
| concat(last_name,first_name) |
±-----------------------------+
| glassbrandon |
| wallacemarian |
| stephenstina |
| glassjacob |
| plewtina |
| spurgeontiffany |
±-----------------------------+
6 rows in set (0.00 sec)
Translate函数搜索字符串里的字符并查找特定的字符，标记找到的位置然后用替代
字符里对应的字符替换他
select city,translate(city,‘ind’,‘abc’)from employe_tbl;
mysql> select city,replace(city,‘ind’,‘abc’)from employe_tbl;
±-------------±--------------------------+
| city | replace(city,‘ind’,‘abc’) |
±-------------±--------------------------+
| whiteland | whiteland |
| indianapolis | abcianapolis |
| greenwood | greenwood |
| indianapolis | abcianapolis |
| indianapolis | abcianapolis |
| indianapolis | abcianapolis |
±-------------±--------------------------+
6 rows in set (0.00 sec)
mysql> select city,replace(city,‘i’,‘abc’)from employe_tbl;
±-------------±------------------------+
| city | replace(city,‘i’,‘abc’) |
±-------------±------------------------+
| whiteland | whabcteland |
| indianapolis | abcndabcanapolabcs |
| greenwood | greenwood |
| indianapolis | abcndabcanapolabcs |
| indianapolis | abcndabcanapolabcs |
| indianapolis | abcndabcanapolabcs |
±-------------±------------------------+
mysql> select upper(city)from employe_tbl;
±-------------+
| upper(city) |
±-------------+
| WHITELAND |
| INDIANAPOLIS |
| GREENWOOD |
| INDIANAPOLIS |
| INDIANAPOLIS |
| INDIANAPOLIS |
±-------------+
6 rows in set (0.00 sec)
mysql> select lower(city)from employe_tbl;
±-------------+
| lower(city) |
±-------------+
| whiteland |
| indianapolis |
| greenwood |
| indianapolis |
| indianapolis |
| indianapolis |
±-------------+
6 rows in set (0.00 sec)
mysql> select emp_id,substr(emp_id,4,6)from employe_tbl;
±----------±-------------------+
| emp_id | substr(emp_id,4,6) |
±----------±-------------------+
| 213764555 | 764555 |
| 220984332 | 984332 |
| 311549902 | 549902 |
| 313782439 | 782439 |
| 442346889 | 346889 |
| 443679021 | 679021 |
±----------±-------------------+
6 rows in set (0.00 sec)
mysql> select emp_id,substring(emp_id,4,1)from employe_tbl;
±----------±----------------------+
| emp_id | substring(emp_id,4,1) |
±----------±----------------------+
| 213764555 | 7 |
| 220984332 | 9 |
| 311549902 | 5 |
| 313782439 | 7 |
| 442346889 | 3 |
| 443679021 | 6 |
±----------±----------------------+
6 rows in set (0.00 sec)
Select position ltrim(position,‘sales’)from employe_tbl;