数据库大讲解第七讲
基本命令使用 作者：董哥
前期比较乱主要是为了熟练有一定基础的，只需跟着文档来就可以，不懂的地方欢迎留言
创建数据库，及各数据库表
主要采用的是Mysql数据库，其中会有提到其他数据库：
- 前期·准备
前期准备创建数据库employe并创建多个表
前期是随便练习后边知识由浅入深
前边几页是练习代码所需要的各种表
里面有中文符号最好自己动手敲
创建数据库
Host: 28.5.8.60 (Version 5.7.17)
Date: 2019-09-2
Writer: 煮不烂的鸡蛋 (Build 1.26)
select emp_id/10 from employe2;
select count(emp_id)from employe1;
select count(emp_phone)from employe2;
mysql> select count(emp_phone)from employe2;
±-----------------+
| count(emp_phone) |
±-----------------+
| 1 |
±-----------------+
1 row in set (0.00 sec)
mysql> select sum(emp_id) from employe2 ;
±------------+
| sum(emp_id) |
±------------+
| 230 |
±------------+
1 row in set (0.00 sec)
mysql> select sum(emp_id) from employe2 ;
±------------+
| sum(emp_id) |
±------------+
| 348 |
±------------+
1 row in set (0.00 sec)
mysql> select sum(distinct emp_id) from employe2 ;
±----------------------+
| sum(distinct emp_id) |
±----------------------+
| 230 |
±----------------------+
1 row in set (0.00 sec)
mysql>
mysql> select sum(distinct emp_name) from employe2 ;
±------------------------+
| sum(distinct emp_name) |
±------------------------+
| 0 |
±------------------------+
1 row in set (0.00 sec)
mysql>如果结果不能转化为数值类型，结果为0
mysql> select avg(distinct emp_id) from employe2 ;
±----------------------+
| avg(distinct emp_id) |
±----------------------+
| 115 |
±----------------------+
1 row in set (0.00 sec)
mysql>mysql> select avg( emp_id) from employe2 ;
±-------------+
| avg( emp_id) |
±-------------+
| 116 |
±-------------+
1 row in set (0.00 sec)
mysql> select max(distinct emp_id) from employe2 ;
±----------------------+
| max(distinct emp_id) |
±----------------------+
| 118 |
±----------------------+
1 row in set (0.00 sec)
mysql> select *from employe2;
±-------±---------±---------±---------±----------±---------+
| emp_id | position | emp_name | emp_page | emp_phone | pay_rate |
±-------±---------±---------±---------±----------±---------+
| 112 | 2200 | q | 21112 | 212 | 99.99 |
| | | | NULL | NULL | 22.00 |
| | | | NULL | NULL | 11.00 |
| 101 | | | NULL | NULL | 0.00 |
| 101 | | | NULL | NULL | 0.00 |
±-------±---------±---------±---------±----------±---------+
5 rows in set (0.00 sec)
mysql> select max(distinct emp_id) from employe2 ;
±----------------------+
| max(distinct emp_id) |
±----------------------+
| 112 |
±----------------------+
1 row in set (0.00 sec)
mysql> select max( emp_id) from employe2 ;
±-------------+
| max( emp_id) |
±-------------+
| 101 |
±-------------+
1 row in set (0.00 sec)
mysql>
mysql> select emp_id,emp_phone
-> from employe1
-> group by emp_id,emp_phone;
±-------±----------+
| emp_id | emp_phone |
±-------±----------+
| 112 | 212 |
| 113 | NULL |
| 114 | NULL |
| 118 | 1546 |
±-------±----------+
4 rows in set (0.00 sec)
mysql> select emp_id,sum(position)
-> from employe1
-> group by position,emp_id;
±-------±--------------+
| emp_id | sum(position) |
±-------±--------------+
| 118 | 1200 |
| 114 | 1213 |
| 112 | 2200 |
| 113 | 2255 |
±-------±--------------+
4 rows in set (0.00 sec)
mysql> select sum(position) as total_position
-> from employe1;
±---------------+
| total_position |
±---------------+
| 6868 |
±---------------+
1 row in set (0.00 sec)
mysql> select sum(position) as total_position
-> from employe1
-> group by position;
±---------------+
| total_position |
±---------------+
| 1200 |
| 1213 |
| 2200 |
| 2255 |
±---------------+
mysql> select student_city,count() from student
-> group by student_city;
±-------------±---------+
| student_city | count() |
±-------------±---------+
| 上海 | 1 |
| 北京 | 2 |
| 天津 | 1 |
| 广州 | 1 |
±-------------±---------+
4 rows in set (0.02 sec) select emp_id/10 from employe2;
select count(emp_id)from employe1;
select count(emp_phone)from employe2;
mysql> select count(emp_phone)from employe2;
±-----------------+
| count(emp_phone) |
±-----------------+
| 1 |
±-----------------+
1 row in set (0.00 sec)
mysql> select sum(emp_id) from employe2 ;
±------------+
| sum(emp_id) |
±------------+
| 230 |
±------------+
1 row in set (0.00 sec)
mysql> select sum(emp_id) from employe2 ;
±------------+
| sum(emp_id) |
±------------+
| 348 |
±------------+
1 row in set (0.00 sec)
mysql> select sum(distinct emp_id) from employe2 ;
±----------------------+
| sum(distinct emp_id) |
±----------------------+
| 230 |
±----------------------+
1 row in set (0.00 sec)
mysql>
mysql> select sum(distinct emp_name) from employe2 ;
±------------------------+
| sum(distinct emp_name) |
±------------------------+
| 0 |
±------------------------+
1 row in set (0.00 sec)
mysql>如果结果不能转化为数值类型，结果为0
mysql> select avg(distinct emp_id) from employe2 ;
±----------------------+
| avg(distinct emp_id) |
±----------------------+
| 115 |
±----------------------+
1 row in set (0.00 sec)
mysql>mysql> select avg( emp_id) from employe2 ;
±-------------+
| avg( emp_id) |
±-------------+
| 116 |
±-------------+
1 row in set (0.00 sec)
mysql> select max(distinct emp_id) from employe2 ;
±----------------------+
| max(distinct emp_id) |
±----------------------+
| 118 |
±----------------------+
1 row in set (0.00 sec)
mysql> select *from employe2;
±-------±---------±---------±---------±----------±---------+
| emp_id | position | emp_name | emp_page | emp_phone | pay_rate |
±-------±---------±---------±---------±----------±---------+
| 112 | 2200 | q | 21112 | 212 | 99.99 |
| | | | NULL | NULL | 22.00 |
| | | | NULL | NULL | 11.00 |
| 101 | | | NULL | NULL | 0.00 |
| 101 | | | NULL | NULL | 0.00 |
±-------±---------±---------±---------±----------±---------+
5 rows in set (0.00 sec)
mysql> select max(distinct emp_id) from employe2 ;
±----------------------+
| max(distinct emp_id) |
±----------------------+
| 112 |
±----------------------+
1 row in set (0.00 sec)
mysql> select max( emp_id) from employe2 ;
±-------------+
| max( emp_id) |
±-------------+
| 101 |
±-------------+
1 row in set (0.00 sec)
mysql>
mysql> select emp_id,emp_phone
-> from employe1
-> group by emp_id,emp_phone;
±-------±----------+
| emp_id | emp_phone |
±-------±----------+
| 112 | 212 |
| 113 | NULL |
| 114 | NULL |
| 118 | 1546 |
±-------±----------+
4 rows in set (0.00 sec)
mysql> select emp_id,sum(position)
-> from employe1
-> group by position,emp_id;
±-------±--------------+
| emp_id | sum(position) |
±-------±--------------+
| 118 | 1200 |
| 114 | 1213 |
| 112 | 2200 |
| 113 | 2255 |
±-------±--------------+
4 rows in set (0.00 sec)
mysql> select sum(position) as total_position
-> from employe1;
±---------------+
| total_position |
±---------------+
| 6868 |
±---------------+
1 row in set (0.00 sec)
mysql> select sum(position) as total_position
-> from employe1
-> group by position;
±---------------+
| total_position |
±---------------+
| 1200 |
| 1213 |
| 2200 |
| 2255 |
±---------------+
mysql> select student_city,count() from student
-> group by student_city;
±-------------±---------+
| student_city | count() |
±-------------±---------+
| 上海 | 1 |
| 北京 | 2 |
| 天津 | 1 |
| 广州 | 1 |
±-------------±---------+
4 rows in set (0.02 sec)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
