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

一、常用命令

1、常用命令

mysql> show databases;
//查询数据库有哪些
mysql> use test;
//表示正在使用test数据库
mysql> create database XXX;
//创建数据库XXX
mysql> exit
//退出命令
mysql> show tables;
//查看表
mysql> mysql -uroot -p123456
//登录sql

2、语言的分类

DQL
    数据查询语言
    select
DML
    数据操作语言
    insert
    delete
    update
    这是针对数据的操作。
DDL
    数据定义语言
    create
    alter
    drop
    这是对表结构操作的。
TCL
    事务控制语言
    事务提交：rollback
    事务回滚:commit
DCL
    数据控制语言
    授权：grant,撤销权限：revoke

3、命令导入.sql文件

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

4、查询数据

select * from 表名

5、查看表结构

mysql> desc 表名;
mysql> describe 表名;

6、查看版本号

mysql> select version();

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

mysql> select database();

8、注意：不区分大小写

9、别名

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

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

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

二、数学表达式

1、字段*数字

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

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
//模糊查询,支持%或下划线匹配

%
//匹配任意字符

_
//匹配一个字符

3、and和or优先级

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

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

select * 
from 
    emp 
where
    sal > 2500 
and 
    （deptno = 10 
or 
    deptno =20）;
    //加括号，会优先执行

4、in

select 
    empno,ename,job 
from
    emp
where
    job
in
    ('manager','salesman');
    //in不是一个区间。in后面跟的是具体的值。

//查询薪资是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);

//not in ,表示不在这几个值中的数据
select 
    empno,ename,job 
from
    emp
where
    sal
not in
    (800,5000);

5、like,%,_

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

//找出名字中含有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、排序sql语句

select
    ename,sal
from
    emp
order by
    sal;//默认是升序
    
    
select
    ename,sal
from
    emp
order by
    sal desc;//默认是升序

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

2、扩展

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

3、综合

select
    ename,sal
from
    emp
where
    sal between 1250 and 3000
order by
    sal desc;
    //顺序
    select
    ...
    from
    ...
    where
    ...
    order by
    ...

四、数据处理函数

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

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

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

(一)、单行处理函数

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

2、转换列名的大小写

//lower 转换小写
select
    lower(ename)
from
    emp;

//upper 转换大写
select
    upoer(ename) as name
from
    emp;

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);

4、length取长度

select
    length(ename) enamelength
from
    emp;

5、trim去除空格

select
    *
from
    emp
where
    ename = trim('   king');

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:代表保留到百位数。

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

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

7、rand生成随机数

select rand() from emp;
//生成100内的随机数。
select
    round(rand()*100,0)
from
    emp;

8、空处理函数

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

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

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

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

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、分组函数

count 计数
sum 求和
avg 平均值
max 最大值
min 最小值

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

2、找出最高工资

select max(sal) from emp;

3、找出最低工资

select min(sal) from emp;

4、计算工资和

select sum(sal) from emp;

5、计算平均工资

select avg(sal) from emp;

6、计算员工数量总和

select count(ename) from emp;

注意：

第一点：分组函数自动忽略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

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

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

(三)、分组函数

1、关键字顺序

select
...
from
...
where
...
group by
...
oder by
...
//执行顺序
1.from
2.where
3.group by
4.select
5.order by

2、分组函数注意事项

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

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

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

//需求：按照工作岗位分组，然后对工资求和
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报错。

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

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

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

2.2、多字段分组

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

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;

2.3.1、优化

//思路：先将大于3000的找出来，然后在分组。
select
    deptno,max(sal)
from
    emp
where
    sal>3000
group by
    deptno;

优化策略：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;