MySQL知识整理（二）

　　SQL语言学习

SQL分类：

DQL语言（Data Query Language）数据查询语言
DML语言(Data Manipulation Language)数据操纵语言,适用范围：对数据库中的数据进行一些简单操作，如insert,delete,update,select等.
DDL语言(Data Definition Language)数据定义语言,适用范围：对数据库中的某些对象(例如，database,table)进行管理，如Create,Alter和Drop.
TCL语言,事务控制语言（TCL）

基础查询

语法：select 要查询的信息 from 表名
要查询的信息可以是：1、表中的一个字段或很多字段（中间用“,”分开）

　　　　　　　　　　　　2、常量值

　　　　　　　　　　　　3、表达式

　　　　　　　　　　　　4、函数
演示单字段查询：
```
SELECT first_name FROM employees;
```

演示查询多个字段：

SELECT first_name，last_name,....... FROM employees;

演示查询表中的所有字段：

SELECT first_name，last_name,....把所有字段写完 FROM employees;
SELECT * FROM employees; //*：通配符，表示所有字段

#另外补充：
　　SELECT
　　　 `department_id`,　
　　　`department_name`,
　　　`manager_id`
　　FROM
　　　departments ;
` 符号： 不是单引号，是着重号！用于区别关键字和字段名的，可以省略。

演示查询常量
```
select 100;

select 'john';
```
演示查询表达式
```
select 100%98；
```

演示查询函数

select version();

select ifnull(参数1,参数2); //参数1真的上null,就返回参数2,参数1不上null,就返回参数1自己.

查询到的结果：是一个虚拟的表格。
给字段起别名

　　方法一：用as关键字，如select 100%98 as 余数结果

　　好处：一个容易理解，语义化，另一个在后面的多表查询时候区分不同表里的同名字段。
　　方法二：as关键字可以换成空格
```
select salary as ‘out put’ from employment；

select salary   ‘out put’ from employment；#空格代替as
```
去重复

#查询员工表中的所有部门的编号 select distinct department_id from employment；
+号的作用
java中加号的作用：运算符，链接符（其中有字符型时）

mysql中只有一个功能：运算符

select 100+90；

select ‘123’ + 90；当有字符型的时候，它会试图将字符型转换成数值型，然后再计算。

select ‘abc’ + 90；如果字符型不能转换，它就会自动将字符型转换为0，然后再计算。

select null + 任何东西；结果为null。

基础查询练习

显示表departments的结构,查询它的全部数据
```
desc departments;
select * from departments;
```
显示employees中的全部job_id(不能重复)
```
select distinct job_id from employees;
```
显示employees的全部列(注意commission_pct有可能为null),各列之间用逗号链接,列头显示OUT_PUT
```
select concat(first_name,',',last_name,',',ifnull(commission_pct,0),......) from employees;
```

条件查询

语法：select 查询列表 from 表名 where 筛选条件
筛选方法：1、条件运算符: > , < , = , != / <>(MYSQL特有的不等号) , >= , <=

　　　　　　　　 2、逻辑运算符:支持java的写法(&& , || , ! ), 但推荐使用mysql自己的(and , not , or)

　　　　　　　　 3、模糊查询：like 、between...and ,、in 、is null

　　 3.条件查询示例

案例:查询员工工资大于1200的员工有哪些;
select * from employees where salary > 12000;

案例:查询部门编号不等于90号的员工名和部门编号:
select concat(last_name,first_name) as 姓名 , department_id
from employees where department_id <> 90;

逻辑运算符主要作用:链接表达式
&&和and : 全式true结果才式true;
||和or: 只要有一个true结构就式true;
!和not: 取反;
案例:工资在10000到20000之间到员工名,工资和奖金
select last_name,salary,commission_pct
from employees where salary>=10000 and salary<=20000;

案例:查询部门标号不式在90到110之间到,或者工资高于15000点员工信息
select * from employees where (department_id<90 and department_id>110) or salary>15000;
select * from employees where not(department_id>=90 and department_id<=110) or salary>15000;

模糊查询

关键字：
like

between and

in

is null | is not null

示例

案例:查询员工名中包含了"a"字符的所有员工的信息
select * from employees where last_name like '%a%'; // %:通配符,表示任意多个字符,也可表示0个字符, _:任意一个字符;


案例:查询第三个字符为n或第五个字符为l的员工信息
select * from employees where last_name like '__n_l%';


案例:查询员工信息表中员工名第二个字符是"_"的员工信息
select * form employees where last_name like '_\_%';//支持java的转义字符

select * from employees where last_name like '_&_' escape '&'; // escape '&':说明&这个符号是转义字符,mysql推荐这样写;

注意:一般情况下,like都是通配符连在一起使用的.

between and:
案例:查询员工工资中10000到20000之间到员工信息
select * from employees where salary>=10000 and salary<=20000;

select * from employees where salary between 10000 and 20000;

注意:使用between and
1.可以简洁sql语句
2.并且包含临界值
3.连个临界值不能调换位置,小的在左边,大的值在右边.

in:
案例:查询员工的工种编号是 it_prog,ad_vp,ad_pres中任意一个的员工信息
select * from employee where job_id='it_prog' or job_id='ad_vp' or job_id='ad_pres';

select * from employees where job_id in('it_prog','ad_vp','ad_pres')

in:判断某个字段的值是否属于in列表中的某一项
1.使用in比使用or后sql语句更简单
2.in的值必须是同一种数据类型或者兼容

is null | is not null:
案例:查询没有奖金率的员工信息
select * from employees commission_pct is null; 注意:commission_pct=null这个写法不能判断null值

查询奖金率的就取反:
select * from employees commission_pct is not null; 注意:当然这里is not也不能换成<>不等号.

PS:

安全等于号:<=> , 这个符号可以判断null值,也可以判断普通数值;

is null:只能判断null值,不能判断普通数值

=:只能判断普通数值,不能判断null值

排序查询

语法：select 查询列表 from 表名 where 条件 order by 排序字段 asc(升序) | desc (降序)

示例

案例:查询所有员工信息,要求工资从大到小排列:
select  *  from employees  order by salary desc;

反过来从小到大排列:
select * from employees order by salary asc; 


查询部门编号大于等于90的员工信息,按照入职时间的先后排序
select  *  from employees where department_id>=90 order by hiredate asc;


案例实现按表达式排序:按年薪的高低显示员工信息:
select *,salary*12*(1+isnull(commission_pct,0)) as 年薪 from employees oreder by salary*12*(1+isnull(commission_pct,0)) desc;

-----oreder by salary*12*(1+isnull(commission_pct,0)) 太长!可以用别名替代:
-----oreder by 年薪 也是可以的!

案例使用函数来排序:按姓名的长度显示员工信息
select *,length(last_name) as 姓名的长度 from employees order by length(last_name) desc;

案例实现多字段排序:查询员工信息,首先用工资高低排序,工资一样的在按员工id大到小排序
select * from employees order by salary desc , employee_id desc;

常见函数

调用函数语法：select 函数名(实参) from 表名

字符函数：
length(str) 返回参数的字节数

select lenght('abcd') ;   // 返回值:4
select length('小白abc'); // 返回值:与字符编码类型有关

show variables like '%char%'; //查看数据可客服端字符集编码

GBK:一个汉字占两个字节;UTF-8:一个汉字是占三个字节.

concat(str1,str2) 连接字符串

select concat(last_name,'--',first_name) as 姓名 from employees;

upper(str):小写字母变大写; lower(str):大写字母变小写;

select upper('zhonghao');
select lower('XIAOBEI');

substr（）和 substring（）截图字符串

案例:截取字符串,从第4个字符(包含)开始到最后
select substr('目标字符串',4) as out_put; //注意:Mysql中索引值是从1开始的,和java不同,java索引是从0开始的.


案例:截取字符串,从第二个开始接到第三个
select substr('目标字符串',2,1) as out_put; //注意:第一个数字是开始截取的索引值,第二个数数截取的长度

instr('源字符串','子字符串'):作用返回子字符串子源字符串里的起始索引.

select instr('目标字符串','字符串'); //找到就返回子字符串在目标字符串中的位置, 如果找不到返回0

trim(' 有空格的字符串 '):作用是去掉字符串前后的空格,中间空格去不掉

select lenght(trim(' a s '));
扩展用法:去掉前后两端的其他字符:select trim('a' from 'aaaaa字符aaaaa去掉aaaaa');

lpad('目标字符串',10,'填充字符'); 将填充字符填充到目标字符的左边,补足10个

select lpad('哈哈哈',10,'*'); // 如果中间的数字比目标字符长度小,就只截取左边的数字长度字符串.

rpad:与lpad只是填充方向相反,其他一样;

replace('目标字符串','被替换子串','用于替换的新串')替换

select replace('目标字符串','字符','哈哈');

其他函数

数学函数：
round():四舍五入

select round(1.45);     //结果1
select round(1.567,2); //表示小数点保留2位

ceil():向上取整

select ceil(1.3); //结果2

floor 向下取整

select floor(1.3);  //结果1

truncate 截断

select truncate(1.65,1); // 结果保留小数1位:1.6

mod 取余

select mod(10,3);  //被除数是正,结果就是正,被除数是负结果就是负数

日期函数
now 用户返回当前日期时间

select now();

curdate 返回当前系统日期,没有时间部分

curtime 返回当前系统时间,没有日期部分

可以单独获取年/月/日

select year(now()); 取得年
select year('2004-1-1') ;

select month(now()); 取得月
select monthname(now()); 取得月名..day hour minute second一样

str_to_date:将日期格式的字符转换层制定格式的日期

str_to_date('2018-1-12','%y-%m-%d');

date_format:将日期换换成字符串,或者理解为格式化日期

date_format(now(),'%y-%m-%d');

案例:查询入职日期是1992-4-3的员工信息:
select * from employees where hiredate='1992-4-3'; //能找到
select * from employees where hiredate=STR_TO_DATE('1992 4 3','%Y %m %d');

系统函数
select version(); //查看mysql版本
select datebase(); //查看当前使用的数据库
select user(); //查看当前用户

流程控制函数
if函数

select if('10>1','大','小');

case函数

case 要判断的子段或表达式

when case的结果是常量1 then 要显示的值1(或语句1;)

when case的结果是常量2 then 要显示的值2(或语句2;)

......

else case的结果都不前面的时候显示;

end 结束

案例:员工表中, 部门号是30,显示的工资是1.1倍部门号是40,显示的工资是1.2倍其他显示原工资
SELECT salary AS 原工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 50 THEN salary*1.2
ELSE salary
END AS 新工资
FROM employees;

多重case

case

when 条件1 then 要显示的值1(或语句1;)

when 条件2 then 要显示的值2(或语句2;)

......

else 前面的条件都不符合时候显示;

end 结束

SELECT salary,
CASE
WHEN salary>20000 THEN 'A级工资'
WHEN salary>10000 THEN 'B级工资'
ELSE 'C级工资'
END AS 工资等级
FROM employees;

分组函数

sum , avg , max , min , count

select sum(salary) as 单月所发总工资 from employees;
select avg(salary) as 单月所发平均工资 from employees;
select max(salary) as 单月所发最多工资 from employees;
select min(salary) as 单月所发最少工资 from employees;
select count(*) as 总员工人数 from employees; //count(*) 用来统计行数

分组函数的共同特点:会忽略null

分组函数还可以和一个关键字和用: distinct
select count(distinct salary) from employees;

MySQL连接查询之等值连接

连接查询：又称多表查询，当查询结果来自多张数据表的时候，就需要用到连接查询。
连接查询的分类：
内连接：等值连接、非等值连接、自连接
外连接：左外连接、右外连接、全外连接、交叉连接
等值连接：简单说明就是：表之间用=连接
```
案例：查询员工名和对应的部门名
select last_name, department_name from employees,departments
where employees.department_id=departments.department_id;
```
用表名来限定字段,太长,不方便,在实际运用中,我们一般使用别名来限定的!

select last_name, department_name from employees e,departments d

where e.department_id=d.department_id;

案例:查询有奖金的员工名以及所属部门名:

select last_name, department_name from employees e,departments d

where e.department_id=d.department_id and e.commission_pct is not null; #在等值查询的基础上添加查询条件!

案例:查询每个城市的部门个数

select count(*) as 个数,city from departments d,locations l

where d.location_id = l.location_id

group by city; #等值查询的基础上加分组查询

案例:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

select d.department_name,d.manager_id,min(e.salary)

from departments d,employees e

where d.department_id = e.department_id

and e.commission_pct is not null

group by d.department_name;

案例：查询每个工种的工种名和员工个数，并且按照员工个数排序降序

select job_title,count(*)

from employees e,jobs j

where e.job_id=j.job_id

group by job_title

order by count(*) desc; #和排序组合使用

案例:查询员工名,部门名和所在城市名

select last_name,department_name,city

from employees e,department d,locations l

where e.department_id=d.department_id

and

d.location_id=l.location_id;#时间三个数据表的连接查询

非等值连接：等值连接中的等号改成非等号情况

案例：查询员工的工资和工资级别
SELECT
salary,grade_level
FROM
employees e,job_grades jg
WHERE
salary BETWEEN jg.lowest_sal AND jg.`highest_sal`;
(非等值连接)

自连接:相当于等值连接,只不过是自己连接自己,不像等值连接是两个不同的表之间的;

案例:查询员工名和他的上司的名字
select e.last_name,m.last_name
from employees e, employees m #把同一张表当成两张不同表
where e.manager_id = m.employee_id;

SQL99版链接查询语法：99版SQL语法格式区别于92版的是连接不在是用“=”了
语法格式：
　　 select 查询列表
　　 from 表1 别名
[ 链接类型] join 表2 别名
on 连接条件
where 筛选条件
链接类型：内连接inner （还可以省略）
外连接：左外连接 left [outer]、右外连接 right [outer]、全外连接 full [outer]、交叉连接 cross [outer]

案例，用99版的SQL写法实现等值连接查询：

1.查询员工名和其对应所属的部门名
select last_name，department_name
from employees e
inner join departments d
on e.department_id = d.department_id;

2.查询名字中包含e字母的员工名和其对象的部门名
select last_name , department_name
from employees e
inner join departments d
on e.department_id = d.department_id
where last_name like '%e%';

3.查询所在部门个数大于3的城市名和部门个数
select count(d.department_id) count , l.city
from departments d
inner join locations l
on d.location_id = l.location_id
group by l.city
having count>3;

4.查询员工个数大于3点部门名和员工个数,降序排序
SELECT d.department_name , COUNT(e.employee_id) c
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY d.department_name
HAVING c>3
ORDER BY c DESC;

5.查询员工名以及对应的工种名和部门名,按部门名降序排序
select e.last_name,j.job_title,d.department_name
from employees e
inner join departments d
on e.department_id = d.department_id
inner join jobs j
on e.job_id=j.job_id
order by d.department_name;


案例，用99版的SQL写法实现非等值连接查询：
SELECT
salary,grade_level
FROM
employees e
INNER JOIN
job_grades g
ON
e.salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;

SQL99版外连接
1、外连接主要就是解决一边有数据对应的另一张表中没有数据！
2、外连接语法：
select 查询列表 from 表名别名
[连接类型] join 表名别名
on 连接条件 where 筛选数据条件
3、外连接相关知识：外连接的查询结果为主表中有的所有记录，外表有对应数据,结果记录上显示对应数据，外表中没有对应的数据,结果记录上填充null
4、和内连接的区别：
内连接：当从表没有记录的时候，主、从表的记录都丢掉！
外连接：当从表没有记录的时候，会保留主表的记录，对应从表null
5、左外连接:left join左边的是主表,右外连接right join 右边的是主表
```
案例：查询没有员工的部门
SELECT d.`department_id`,d.`department_name`,e.`employee_id`
FROM `departments` d
LEFT JOIN `employees` e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;
```
6、全外连接MySQL不支持，交叉连接：就两个记录做笛卡尔积！

MySQL知识整理（二）

SQL语言学习

SQL分类：

基础查询

基础查询练习

条件查询

模糊查询

排序查询

常见函数

其他函数

分组函数

MySQL连接查询之等值连接

　　SQL语言学习