数据库常用命令——单表查询

一、常用命令

1、常用命令

mysql> show databases;
//查询数据库有哪些
mysql> use test;
//表示正在使用test数据库
mysql> create database XXX;
//创建数据库XXX
mysql> exit
//退出命令
mysql> show tables;
//查看表
mysql> mysql -uroot -p123456
//登录sql
1
2
3
4
5
6
7
8
9
10
11
12

2、语言的分类

DQL
	数据查询语言
	select
DML
	数据操作语言
	insert
	delete
	update
	这是针对数据的操作。
DDL
	数据定义语言
	create
	alter
	drop
	这是对表结构操作的。
TCL
	事务控制语言
	事务提交：rollback
	事务回滚:commit
DCL
	数据控制语言
	授权：grant,撤销权限：revoke
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

3、命令导入.sql文件

mysql> source D:\路径\student.sql
1

4、查询数据

select * from 表名
1

5、查看表结构

mysql> desc 表名;
mysql> describe 表名;
1
2

6、查看版本号

mysql> select version();
1

7、查看使用的是哪个数据库

mysql> select database();
1

8、注意：不区分大小写

9、别名

select 表名.列名 as 别名 from 表名
select deptno.dname as deptname from dept	
1
2

注意：只是将查询结果显示为deptname

select deptno.dname as `dept name` from dept
select deptno.dname as "dept name" from dept
//单引号是标准，Oracle不支持双引号
1
2
3

二、数学表达式

1、字段*数字

select sal*12 as yearsal from emp;
//sal字段所有值被乘12
select sal*12 as `年薪` from emp;
//sal字段所有值被乘12
1
2
3
4

2、条件查询

select empo ename from emp where sal = 5000;
<> 或 ！=
//不等于

<
//小于

<=
//小于等于

>
//大于

>=
//大于等于

between ... and ... 
>=  and <=
//两值之间,between遵循左小右大，包括二端的值

is null
is not null
//为空/不为空
select empno,ename,sal,comm from emp where comm is null;

and
//并且
select empno,ename,sal,comm from emp where comm job='manager' and sal>2500;

or
//或者
select empno,ename,sal,comm from emp where comm job='manager' or job='salesman';
//查询所有manager和salesman

in
//包含

not not
//取非,主要用在 is 或 in中

like
//模糊查询,支持%或下划线匹配

%
//匹配任意字符

_
//匹配一个字符
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

3、and和or优先级

select * 
from 
	emp 
where
	sal > 2500 
and 
	deptno = 10 
or 
	deptno =20;
	//找出工资大于2500并且部门编号为10的员工，或者20部门所有员工找出来。
1
2
3
4
5
6
7
8
9
10

and 的优先级比 or高：先执行and,然后执行or

select * 
from 
	emp 
where
	sal > 2500 
and 
	（deptno = 10 
or 
	deptno =20）;
	//加括号，会优先执行
1
2
3
4
5
6
7
8
9
10

4、in

select 
	empno,ename,job 
from
	emp
where
	job
in
	('manager','salesman');
	//in不是一个区间。in后面跟的是具体的值。
1
2
3
4
5
6
7
8
9

//查询薪资是800和5000的员工信息，这个不表示查询800-5000的所有信息。
select 
	empno,ename,job 
from
	emp
where
	sal=800 or sal=5000;
	//效果一样
select 
	empno,ename,job 
from
	emp
where
	sal
in
	(800,5000);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

//not in ,表示不在这几个值中的数据
select 
	empno,ename,job 
from
	emp
where
	sal
not in
	(800,5000);
1
2
3
4
5
6
7
8
9

5、like,%,_

like
	//称为模糊查询，支持%或下划线匹配
	%匹配任意多个字符
	下划线：任意一个字符
1
2
3
4

//找出名字中含有o的
select
	ename
from
	emp
where
	ename
like
	'%o%';
//找出名字以T结尾的
select
	ename
from
	emp
where
	ename
like
	'%T';
//找出名字以K开头的
select
	ename
from
	emp
where
	ename
like
	'k%';
//找出第二个字母是A的
select
	ename
from
	emp
where
	ename
like
	'_A%';
//找出第三个字母是A的
select
	ename
from
	emp
where
	ename
like
	'__R%';
//找出名字有下划线的，如：jack_son,加转义字符
select
	ename
from
	emp
where
	ename
like
	'%\_%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

三、排序

1、排序sql语句

select
	ename,sal
from
	emp
order by
	sal;//默认是升序
	
	
select
	ename,sal
from
	emp
order by
	sal desc;//默认是升序
1
2
3
4
5
6
7
8
9
10
11
12
13
14

//根据多个字段排序，查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，再按照名字升序排列。
//sal在前，起主导，只有sal相等带带时候，才会考虑启用ename排序。
select
	ename,sal
from
	emp
order by
	sal asc,ename asc;
1
2
3
4
5
6
7
8

2、扩展

//表示第二列，按照查询结果的第二列sal排序。
select
	ename,sal
from
	emp
order by 2;
//不建议在开发中这样写，因为不健壮。列的顺序容易改变，列顺序修改之后，2就废了。
1
2
3
4
5
6
7

3、综合

select
	ename,sal
from
	emp
where
	sal between 1250 and 3000
order by
	sal desc;
	//顺序
	select
	...
	from
	...
	where
	...
	order by
	...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

四、数据处理函数

概述：数据处理函数又被称为单行处理函数

单行处理函数特点：一个输入对应一个输出。

多行处理函数特点：多行处理函数。多个输入对应一个输出。

(一)、单行处理函数

lower 转换小写
upper 转换大写
substr 取子串（substr(被截取的字符串，起始下标，截取长度)）
length 取长度
trim 去除空格
str_to_date 将字符串转换成日期
data_format 格式化日期,为字符串
format 设置千分位
round 四舍五入
rand() 生成随机数
ifnull 可以将null 转换成一个具体值
concat 进行字符串拼接
case..when..then..when..then..else..end 
1
2
3
4
5
6
7
8
9
10
11
12
13

2、转换列名的大小写

//lower 转换小写
select
	lower(ename)
from
	emp;
1
2
3
4
5

//upper 转换大写
select
	upoer(ename) as name
from
	emp;
1
2
3
4
5

3、取子串

//substr 取子串（substr(被截取的字符串，起始下标从1开始，截取长度)）
select
	substr(ename,1,1) as ename
from
	emp;

//需求：找出员工名字抵押给字母是A的员工信息
//第一种方式：
select
	ename
from
	emp
where
	ename 
like 'A%'
//第二种方式：
select
	ename
from
	emp
where
	substr(ename,1,1)='A';
//需求：首字母大写
select
concat(upper(substr(substr(name,1,1)),substr(name,2,length(name)-1)) as result from t_student);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

4、length取长度

select
	length(ename) enamelength
from
	emp;
1
2
3
4

5、trim去除空格

select
	*
from
	emp
where
	ename = trim('   king');
1
2
3
4
5
6

6、round函数，四舍五入

select round(1236.567,0) as result from emp;
//查询的结果为了：1237
//0:代表取整数。
select round(1236.567,1) as result from emp;
//查询的结果为了：1236.6
//1:代表保留一位小数。
select round(1236.567,2) as result from emp;
//查询的结果为了：1236.57
//1:代表保留二位小数。
select round(1236.567,-1) as result from emp;
//查询的结果为了：1240
//1:代表保留到10位。
select round(1236.567,-2) as result from emp;
//查询的结果为了：1200
//1:代表保留到百位数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

结论：select后面可以跟字段名，字面值。

select 200 as num from dept;
select name as myname from dept;
1
2

7、rand生成随机数

select rand() from emp;
//生成100内的随机数。
select
	round(rand()*100,0)
from
	emp;
1
2
3
4
5
6

8、空处理函数

概述：在所有数据库当中，只要有null参与的数学运算，最终结果就是Null。

ifnull函数用法：ifnull(数据，被当作那个值)

如果“数据”为null的时候，把这个数据解构当作是那个值。

select
	ename,(sal+ifnull(comm,0)) * 12 as yearsal from 
	emp;
1
2
3

9、case…when…then…when…then…else…end

//当员工的工作岗位是manager的时候，工资上调10%，当工作岗位是salesman的时候，工资上调50%，其他正常。（注意：不修改数据库，只是将查询结果显示为工资上调。）
select
	ename,job,sal as oldsal,
	（case job
when
	'manager'
then
	sal*1.1
when
	'salesman'
then
	sal*1.5
else
	sal end）
	as newsal
from
	emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

(二)、多行处理函数

特点：输入多行，输出一行

1、分组函数

count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
1
2
3
4
5

注意：分组函数在使用的时候必须先进行分组，然后才能用。

2、找出最高工资

select max(sal) from emp;
1

3、找出最低工资

select min(sal) from emp;
1

4、计算工资和

select sum(sal) from emp;
1

5、计算平均工资

select avg(sal) from emp;
1

6、计算员工数量总和

select count(ename) from emp;
1

注意：

第一点：分组函数自动忽略Null,null不是一个值，是什么也没有。

第二点：分组函数中，count(*)和count(具体字段)的区别。

​ count(具体字段)：表示统计该字段下，所有不为null的元素的总数。

​ count(*):统计表当中的总行数。（只要有一行数据count则++）

因为每一行记录不可能都为null,一行数据中有一列不为null，则这行数据有效。

第三点：分组函数不能直接使用在where子句中。因为所有分组函数：必须先分组之后才能使用。where执行的时候，还没有分组。所以where后面不能出现分组函数。

​

//如：找出比最低工资高的员工信息
select
	ename,sal
from
	emp
where
	sal>min(sal);
	//ERROR 1111 (HY): Invalid use of group function
1
2
3
4
5
6
7
8

第四点：所有分组函数可以组合在一起用。

select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
1

(三)、分组函数

1、关键字顺序

select
...
from
...
where
...
group by
...
oder by
...
//执行顺序
1.from
2.where
3.group by
4.select
5.order by
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2、分组函数注意事项

注意：分组函数不能直接使用在where子句中。因为所有分组函数：必须先分组之后才能使用。where执行的时候，还没有分组。所以where后面不能出现分组函数。

注意：如果没有显 示写出group by,实际上group by也是存在的。默认会把整张表当作一个分组。

select sum(sal) from emp;//这个就是select已经在group by之后执行了。
1

//需求：按照工作岗位分组，然后对工资求和
select
	job,sum(sal)
from
	emp
group by
	job;
//执行顺序
1、先从emp表中查询数据
2。根据job字段进行分组
3.然后对每一组数据进行sum(sal)

select ename,job,sum(sal) from emp group by job;
//上述语句毫无意义，mysql会执行，oracle报错。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

注意：一旦分组，只能使用分组字段，后面紧接分组函数。其他的一律不能跟。

2.1、找出每个部门的最高薪资

select deptno,max(sal) from emp group by deptno;
1

2.2、多字段分组

//找出每个部门不同工作岗位的最高薪资（二个字段联合分组）
select
	deptno,job,max(sal)
from
	emp
group by
	deptno,job;
	//注意：一旦使用了group by select 后面只能跟分组的字段。
1
2
3
4
5
6
7
8

2.3、分组+having条件过滤

//需求：找出每个部门最高薪资，要求显示最高薪资大于3000的
//第一步：按照部门编号分组，求每一组最大值。
select deptno,max(sal) from emp group by deptno;
//第二步：使用having可以对分完组的数据进一步过滤，having不能单独使用,必须跟group by结合使用。
select 
	deptno,max(sal) 
from
	emp 
group by 
	deptno
having
	max(sal) > 3000;
1
2
3
4
5
6
7
8
9
10
11
12

2.3.1、优化

//思路：先将大于3000的找出来，然后在分组。
select
	deptno,max(sal)
from
	emp
where
	sal>3000
group by
	deptno;
1
2
3
4
5
6
7
8
9

优化策略：where和having，优先选择where,where实在完成不了，在选择having.

2.3.2、group by分组+having过滤条件

//需求：找出每个部门平均薪资，要求显示平均薪资高于2500的。
第一步：找出每个部门平均薪资
select
	deptno,avg(sal)
from
	emp
group by
	deptno;
第二步：要求显示平均薪资高于2500的
select
	deptno,avg(sal)
from
	emp
group by
	deptno
having
	avg(sal)>2500;
	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18