RDBMS--Day02

基础查询进阶

函数

select 函数名（）;//单独使用

select 函数名（函数） from 表名;//函数嵌套函数

select 函数名（字段名） from 表名;//处理表头数据

这里进行举例，按需百度所需要的函数

字符函数

char_length()

select name name,char_length(name) leng from user where id <=10;

LENGTH(str) 返字符串长度，以字节为单位

CHAR_LENGTH(str) 返回字符串长度，以字符为单位

UPPER(str)和UCASE(str) 将字符串中的字母全部转换成大写

LOWER(str)和LCASE(str) 将str中的字母全部转换成小写

SUBSTR(s, start,end) 从s的start位置开始取出到end长度的子串

INSTR(str,str1) 返回str1参数，在str参数内的位置

TRIM(s) 返回字符串s删除了两边空格之后的字符串

数学函数

mod()

select name 名字,uid 偶数行 from user where uid is not null and mod(uid,2)=0;

ABS(x) 返回x的绝对值

PI() 返回圆周率π，默认显示6位小数

MOD(x,y) 返回x被y除后的余数

CEIL(x)、CEILING(x) 返回不小于x的最小整数 (x 是小数)

FLOOR(x） 返回不大于x的最大整数 (x 是小数)

ROUND(x) 返回最接近于x的整数，即对x进行四舍五入 (x 是小数)

ROUND(x,y) 返回最接近x的数，其值保留到小数点后面y位，若y为负 值，则将保留到x到小数点左边y位 (x 是小数)

日期时间函数

聚集函数

数据统计命令 ，输出的值只有1个

聚集函数 竖着计算

avg(表头名) //计算平均值

sum(表头名) //求和

min(表头名) //获取最小值

max(表头名) //获取最大值

count(表头名) //统计表头值个数

select count(name) from tarena.user where shell="/sbin/nologin";

数学计算

符号两边 要是数字 或数值类型的表头

运算符 横着计算

+ - * / %

查看8号员工2019年1月10 基本工资翻3倍的 值

select employee_id , basic , basic * 3 as 工资翻三倍 from tarena.salary

where employee_id=8 and date=20190110;

综合应用

查看8号员工的出生年份和年龄

select employee_id ,year(birth_date) as 出生年份 , 2022 - year(birth_date) as 年龄 from tarena.employees where employee_id = 8 ;

流程控制函数

if(条件,v1,v2) 如果条件成立，则返回v1，否则返回v2

select if(5>1,123,666);

select name , shell if(shell = "/bin/bash" , "交互用户","非交户用户") as 用户类型 from tarena.user;

ifnull语句 语法格式

ifnull(v1,v2) 如果v1不是空，则返回v1，否则返回v2

select name 姓名, ifnull(homedir,"NO home")as 家目录 from tarena.user;

case语句 语法格式 (可以有多个判断添加)

如果字段名等于某个值，则返回对应位置then后面的值并结束判断，

如果与所有值都不相等，则返回else后面的结果并结束判断

CASE 表头名              
WHEN 值1 THEN 结果 
WHEN 值2 THEN 结果  
WHEN 值3 THEN 结果 
ELSE 结果  
END 

select dept_id, dept_name,
case dept_name
when '运维部' then '技术部门'
when '开发部' then '技术部门'
when '测试部' then '技术部门'
else '非技术部门'
end as  部门类型   from  tarena.departments;

CASE   
WHEN  判断条件 THEN 结果 
WHEN  判断条件  THEN 结果  
WHEN  判断条件 THEN 结果 
ELSE 结果  
END

select dept_id,dept_name,
case
when dept_name in ("运维部","开发部","测试部") then "技术部"
else "非技术部"
end as 部门类型  from  tarena.departments;

查询结果处理

对用select 命令查找到的数据再做处理，

类似于系统命令管道 例如 ps aux | grep httpd

分组 : 表头值相同为一组，值只显示一次

注意： 分组命令只能单独使用， 或与聚集函数一起使用

group by

select count(name) , shell  from  tarena.user group by shell;

排序 对表头下的数据排序，用来数值类型表头排序

order by

Select 查询命令 order by 表头名 ；升序

Select 查询命令 order by 表头名 desc; 降序

select count(name) , shell from tarena.user group by shell order by count(name) desc;

mysql> select employee_id, sum(basic+bonus) as total from tarena.salary
->where year(date)=2018 group by employee_id order by total desc;

过滤数据： 对select 查询到的数据 做筛选

having

select  字段名列表  from   库.表  where   条件     having   筛选条件
select  dept_id ,  count(name)  as 总人数 
from   tarena.employees   
group  by  dept_id  
having 总人数 < 10;

分页 limit

limit 起始位置（0开始） ,指定的行数

select name,uid , gid  , shell  
from user  
where shell is not null  limit 3,3;

当有分组 group by 过滤 having 、limit 排序都出现的时候

按照 group by---》过滤-----》排序 顺序写

三、连接查询

把多张表通过连接条件临时组成1张新表，在临时的新表里有连接表的所有表头和数据。

3．1、连接查询分类

按功能分类

内连接

外连接

全连接

按年代分类

SQL92标准：仅支持内连接

SQL99标准：支持所功能的连接

等值连接：使用相等判断做连接条件

非等值连接：连接条件不是相等判断

自连接： 自己连接自己，把1张表当做2张表（需要给表定义别名）

给表名定义别名 ，定义别名后必须使用别名表示表名

两个表有同名表头，表头名前必须加表名

内连接-等值连接例子

select 字段名 from 表名 inner join 表名 on 条件；

select 表名.字段名 from 表名 inner join 表名 on 条件；

select a.字段名,b.字段名 from 表名 as a inner join 表名 as b on 条件 where....；

select employees.employee_id, sum(basic+bonus) as total    
from employees inner join salary on employees.employee_id=salary.employee_id   
where year(salary.date)=2018 
group by employees.employee_id 
having total > 300000
order by  total desc;

内连接-非等值连接例子

使用非相等做判断做连接条件

select grade as 工资等级, count(employee_id)  as  总人数
 from salary as s inner join wage_grade as g  
 on s.basic between g.low and g.high
 where year(date)=2018 and month(date)=12 
 group by grade;

内连接之自连接：

自己连接自己 把1张当2张表使用，实现的方法

就是查询表的时候给表定义别名来实现。

select e.name,e.employee_id , em.employee_id , e.hire_date, em.birth_date 
或者通过where筛选条件 `
select  name ,hire_date , birth_date from  employees 
where month(hire_date) = month(birth_date);

左外连接 LEFT JOIN

左边表的记录全都显示出来 右边的表只显示与条件匹配记录，右边表比左边表少的记录使用NULL匹配

SELECT 表头名列表 FROM 表1 LEFT JOIN 表2 ON 连接条件

[WHERE 筛选条件] | [GROUP BY 分组] | [HAVING 分组后筛选]|[ORDER BY 排序列表]

右外连接 RIGHT JOIN

右边表的记录全都显示出来 左边的表只显示与条件匹配记录，左表比右边表少的记录使用NULL 匹配

SELECT 表头名列表 FROM 表1 RIGHT JOIN 表2 ON 连接条件

[WHERE 筛选条件] | [GROUP BY 分组] | [HAVING 分组后筛选]|[ORDER BY 排序列表]

全外连接

也称联合查询，用来一起输出多个select查询结果

要求查询时，多个select语句查看的表头个数必须一致

UNION关键字默认去重，可以使用UNION ALL包含重复项

(select employee_id ,date,basic,bonus, basic+bonus as total 
from tarena.salary 
where date=20180110 
order by total desc limit 1) 
union 
(select employee_id ,date, basic,bonus,basic+bonus as total 
from tarena.salary 
where date=20190110 
order by total desc limit 1) 
union 
(select employee_id ,date,basic,bonus, basic+bonus as total 
from tarena.salary 
where date=20200110 
order by total desc limit 1);

你需要知道

1 简述MySQL数据库中插入、更新、查询、删除表记录的指令格式。

insert into 表名(列名...) valuse(值....)

update 表名 set 列名="新值" where 字段名="xxxx"

select 字段名from 表

delete from 表名 where 字段名='xxxx'

2 查询综合练习题，按要求写出对应查询语句。

添加记录编号字段id 在所有字段上方，字段值可以自动增长。
alter  table  userdb.userlist  add  id  int(2) primary key  auto_increment  first;
显示uid 是四位数的用户的用户名和uid号。
select  name,uid  from userdb.userlist where uid >=1000 and uid<=9999;
select  name,uid  from userdb.userlist where uid  between  1000  and  9999；
 select  name,uid  from userdb.userlist where uid regexp ‘^....$’;
显示名字是以字母r 开头 且是以字母d结尾的用户名和uid号。
select name,uid  from userdb.userlist where  name regexp ‘^r.*d$’;
select user from mysql.user where user regexp '^r' and user regexp 'd$';
查看gid 小于10的用户使用shell的种类。
select shell from userdb.userlist where uid<10 group by shell;
select distinct shell from userdb.userlist where uid<10;
查看shell不是/bin/bash用户中uid号最大用户名及uid号。
select name,uid  from userdb.userlist where shell!=”/bin/bash” order by uid desc limit 1;
6、统计uid是3位数的用户的个数。
select count(name) from userdb.userlist where uid >=100 and uid<=999;