数据库大讲解第十讲

数据库大讲解第十讲

基本命令使用 作者：董哥

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

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

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

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

Host: 28.5.8.60 (Version 5.7.17)

Date: 2019-09-2

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

mysql> Select address,length(address)from employe_tbl ;
±-----------------------±----------------+
| address | length(address) |
±-----------------------±----------------+
| 1710 main st | 12 |
| 7889 keystone ave | 17 |
| rr 3 box 17 a | 13 |
| 3789 white river blvd | 22 |
| 3301 beacon | 11 |
| 5 george court | 14 |
±-----------------------±----------------+
6 rows in set (0.00 sec)

mysql> Select pager,ifnull(pager,9999999999)from employe_tbl;
±-----------±-------------------------+
| pager | ifnull(pager,9999999999) |
±-----------±-------------------------+
| 3175709980 | 3175709980 |
| NULL | 9999999999 |
| NULL | 9999999999 |
| 8887345678 | 8887345678 |
| NULL | 9999999999 |
| NULL | 9999999999 |
±-----------±-------------------------+
6 rows in set (0.00 sec)
mysql> Select emp_id,coalesce(bonus,salary,pay_rate)from employe_pay_tbl;
±----------±--------------------------------+
| emp_id | coalesce(bonus,salary,pay_rate) |
±----------±--------------------------------+
| 213764555 | 2000.00 |
| 220984332 | 11.00 |
| 311549902 | 40000.00 |
| 313782439 | 1000.00 |
| 442346889 | 14.75 |
| 443679021 | 15.00 |
±----------±--------------------------------+
6 rows in set (0.00 sec)
mysql> Select lpad(position,15,’&&&’) a from employe_pay_tbl;
±----------------+
| a |
±----------------+
| &&sales manager |
| &&&&&&&&shipper |
| &&&&&&marketing |
| &&&&&&&salesman |
| &&&&team leader |
| &&&&&&&&shipper |
±----------------+
6 rows in set (0.00 sec)
mysql> Select rpad(position,15,’&&&’) a from employe_pay_tbl;
±----------------+
| a |
±----------------+
| sales manager&& |
| shipper&&&&&&&& |
| marketing&&&&&& |
| salesman&&&&&&& |
| team leader&&&& |
| shipper&&&&&&&& |
±----------------+
6 rows in set (0.00 sec)

Select emp_id,To_number(emp_id) from employe_tbl;
Select pay=pay_rate,new_pay=str(pay_rate) from employe_pay_tbl
where pay_rate is not null;
mysql> select concat(last_name,’,’,first_name)name,
-> concat(substr(emp_id,1,3),’-’,substr(emp_id,4,2),’-’,substr(emp_id,6,4))as id
-> from employe_tbl;
±-----------------±------------+
| name | id |
±-----------------±------------+
| glass,brandon | 213-76-4555 |
| wallace,marian | 220-98-4332 |
| stephens,tina | 311-54-9902 |
| glass,jacob | 313-78-2439 |
| plew,tina | 442-34-6889 |
| spurgeon,tiffany | 443-67-9021 |
±-----------------±------------+
mysql> select sum(length(last_name)+length(first_name))total
-> from employe_tbl;
±------+
| total |
±------+
| 70 |
±------+
1 row in set (0.00 sec)
mysql> select date_hire,date_add(date_hire,interval 1 day),date_hire + 1
-> from employe_pay_tbl
-> where emp_id=‘311549902’;
±-----------±-----------------------------------±--------------+
| date_hire | date_add(date_hire,interval 1 day) | date_hire + 1 |
±-----------±-----------------------------------±--------------+
| 1999-05-23 | 1999-05-24 | 19990524 |
±-----------±-----------------------------------±--------------+
1 row in set (0.00 sec)

l> select str_to_date(‘01/01/2010 12:00:00 AM ‘,’%m/%d/%y %h:%i:%s %p’)as format_date
-> from employe_pay_tbl;
±------------+
| format_date |
±------------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
±------------+
6 rows in set, 6 warnings (0.00 sec)
mysql> select str_to_date(‘08/14/2004 12:00:00 AM’,’%m/%d/%Y %h:%i:%s %p’)as format_date
-> from employe_pay_tbl;
±--------------------+
| format_date |
±--------------------+
| 2004-08-14 00:00:00 |
| 2004-08-14 00:00:00 |
| 2004-08-14 00:00:00 |
| 2004-08-14 00:00:00 |
| 2004-08-14 00:00:00 |
| 2004-08-14 00:00:00 |
±--------------------+
6 rows in set (0.00 sec)
mysql> select employe_tbl.emp_id,employe_pay_tbl.date_hire
-> from employe_tbl,employe_pay_tbl
-> where employe_tbl.emp_id=employe_pay_tbl.emp_id;
±----------±-----------+
| emp_id | date_hire |
±----------±-----------+
| 213764555 | 2004-08-14 |
| 220984332 | 2006-07-22 |
| 311549902 | 1999-05-23 |
| 313782439 | 2007-06-28 |
| 442346889 | 2000-06-17 |
| 443679021 | 2001-01-14 |
±----------±-----------+
6 rows in set (0.00 sec)