mysql 基础

运用dos命令行登录远程Linux mysql数据库
使用命令

C:\WINDOWS\system32>mysql -h ip地址 -P 3306 -u root

显示所有数据库

show databases;

在test库里面查询mysql 库的所有的表

show tables from 库名;

查询当前所在的数据库

select database();

查看表结构

desc  表名;

mysql语法规范

1.不区分大小写，建议关键字大写，表名列名小写。
2.每条命令用分号结尾。
3.每条命令根据需要，进行缩进换行。
4.注释
单行注释：#注释文字
单行注释：-- 注释文字
多行注释：/注释文字/

DQL语言学习（查询）

dql :data query language

基础查询

语法： select 查询列表 from 表名；
类似于 ： system.out.println(打印东西);
特点：
1.查询列表可以是表中的字段、常量、表达式、函数。
2.查询出来的结果是一个虚拟的表格
#1.查询表中的单个字段
select last_name from employees;
#2.查询表中多个字段
select last_name ,salary,email from employees;
#3.查询常量值
#4.查询表达式
#5.查询函数
SELECT VERSION();
#7.起别名
1.使用AS

SELECT  last_name  AS 姓，first_name AS 名 FROM employees;

2使用空格

SELECT  last_name   姓，first_name  名 FROM employees;

#8.去重
使用关键字（distinct）
select distinct department_id from employees;
#9.+号的作用
将两个字段连接成一个字段，两个字段进行拼接
在mysql中+号只有一个功能：运算符
两个字段为字符型，则作加法运算。
两个字段其中一方为字符型：它会将先进行转换数值，在进行运算，但是有些字符不能转换成功，直接转换为0，
select ‘join’+90;
select null+10;
其中有一方为null,则结果为null;

可以使用mysql 函数cancat();不过注意当其中字段有空的时候，会导致其它字段也为空。可以使用ifnull();函数

select concat(last_name,first_name,ifnull(commission_pct,0)) as out_put form employees;

条件查询

语法：
select 查询列表from 表名 where 筛选条件
分类：
1.按条件表达式筛选
条件运算符：> < = != <> <= >=
2.按逻辑表达式筛选
逻辑运算符 ：
&& || !
and or not
3.模糊查询
like
between and
in
is null
<=>(安全等于)它其实就是判断等于和null的情况，

排序查询

引入：
select * from employees;
语法：
select 查询列表
from 表
[where 筛选条件]
order by 排序列表 （asc|desc降序）;

select * from employees order by salary desc;

1.asc 升序 desc 降序
2.order by 子句中可以支持单个字段，多个字段，表达式，函数，别名
3.order by 子句在查询的最后面，limit除外。

常见的函数

概念：类似于Java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1.隐藏了实现细节 2.提高代码重用性
分类：
单行函数：concat .length .ifnull等
2.分组函数
功能：做统计使用，又称为统计函数。聚合函数。组函数。

一.字符函数

length :
select LENGTH(‘john’);
select LENGTH(‘张三丰haah’)
获取字符集

show variales liek'%char%'  

concat 拼接字符串

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

upper lower 变大写变小写

select concat(upper(last_name),lower(first_name))姓名 from employees;

substr , substring 截取
注意：索引从1开始
从索引位置截取后面的所有字符

select substr(‘小龙女爱上杨过’，5) out_put;

substr:从指定索引位置截取指定的长度字符

select substr('小龙女爱上杨过',1,3) out_put;

instr 返回字串第一次的索引，找不到返回0

select instr('abc123123','12')

trim 去掉字符
select trim(’ sfsfd ')

select TRIM('1' from'111111111111111111lsjfdl 111 1111    ');

#lpad 用指定的字符实现左填充指定长度
select lpad(‘111111111111111111111’,10,’*’) as out_put;
#rpad 右填充
#replace替换

select replace('11111111111111aaaaa111sssaaaa','a','***') as out_put;

数学函数

#round 四舍五入

select round(1.55)
select round(1.56456,2);//保留两位小数

#ceil 向上取整 返回该参数》=该参数的最小整数
#floor向下取整
truncate 截断小数点后截取
select truncate(1.323,1)
mod 取余
select mod(10,-3); 运算方法 a-a/b*b;

日期函数

#now 返回当前系统日期+时间
#curdate 返回当前系统日期，不包含时间
#curtime 返回当前系统时间，不包含日期
#可以获取指定部分，年、月、日、
select year(now()) 年；
select year(‘1998-1-1’);
select month(now());
#str_to_date 将字符通过指定格式转换成日期
select str_to_date(‘1998-3-2’,’%y-%c-%d’) as out_put;
#date_format 将日期转换成字符
select Date_format(now(),’%y-%m-%d’) as out_put;
#datediff(‘2020-02-03’,‘1997-03-13’)时间相差的天数

其它函数

show version();
show database();
show user();

流程控制参数

1.if函数 if else 的效果
select if(10>5,‘大’，‘小’)；
2.case 函数的使用一 ：switch case 的效果
case 要判断的字段或者表达式
when 常量1 then 要显示的值或者语句1；
when 常量2 then 要显示的值或者语句2；
…
else 要显示的值n,或者语句n;
end
案例 ：查询员工工资，要求
部门号=30，显示工资为1.1倍
部门号=40，显示工资为1.2倍
部门号=50，显示工资为1.3倍

SELECT salary as 原始工资,department_id,
CASE department_id
	WHEN 30 THEN
		salary*1.1 
		WHEN 40 then 
		salary*1.2
		when 50 then
		salary*1.3
	ELSE
		salary
END as 新工资 from employees;

case 函数的使用二 类似于多重if
if (条件){
}else if(体哦就){
}
case
when 条件1 then 要显示的值或语句1
when 条件2 then 要显示的值2或语句2
…
else 要显示的值或语句n
end
查询员工的工资情况
如果工资>20000 显示A级别
如果工资>15000 显示B级别
如果工资>10000 显示C 级别
否则 显示D级别

select salary,
case 
when salary>20000 then 'A级别'
when salary>15000 then 'B级别'
when salary>10000 then 'C级别'
else 'D级别'
END AS 级别 from employees;

二、分组函数

用作统计使用，又称为聚合函数或统计函数或组函数
1分类：
sum 求和 avg 平均值 max 最大值 min 最小值 count 计算个数

select sum(salary) from employees;
select avg(salary) from employees;
select max(salary) from employees;
select min(salary) from employees;
select count(salary) from employees;

2.参数支持哪类类型
sum avg 一般用于处理数值类型
max min count 任何字段都可以
3.是否忽略null
sum avg 忽略null
4.可以和关键字搭配 distinct

select sum(distinct salary),sum(salary) from employees;

5.count函数的详细介绍
select count(salary) from employees;
select count(*) from employees;
select count(1) from employees; 统计个数，常量

效率：
MYISAM 存储引擎， count()效率高
INNODB 存储引擎 count()和count(1)差不多，比count(‘字段’)的效率高
6.和分组函数一同查询的字段有限制 后面要用group by

分组查询

1.语法：
select 分组函数，列（要求出现在group by 的后面）
from 表
（where 筛选条件）
group by 分组的列表
（order by 子句）
案列：查询每个工种的最高工资

select max(salary),job_id from employees group by job_id;
select avg(salary),department_id from employees where email like'%a%' GROUP BY department_id ;

添加分组后的筛选
查询哪个部门员工数>2

select count(*),department_id from employees group by depardment_id having count(*)>2;

分组查询筛选可以分为两类
1.分组前筛选 原始表 group by 前面 where
2.分组后筛选 结果集 group by后面 having
1.分组查询的条件一般都放在having 后面
2.能用分组前的筛选条件尽量在分组前把数据筛选好，（性能好一点）

#按表达式或函数分组

#按多个字段进行分组

连接查询

含义：连接查询又叫多表查询，当查询的数据来自多个表的时候，就会用到连接查询。
笛卡尔积现象，当表一有m行数据，表二有n行数据结果=m*n行 ；
分类：
按年代分类：
sql 1992 标准 仅仅支持内连接
sql 1998 标准（推荐）支持内连接+外连接
按功能分类：
内连接：
1.等值连接：
a:多表等值连接结果为多表的交集部分
b:n表连接，至少要n-1个连接条件
c:多表的顺序没有要求
d:一般要为表起别名
e:可以搭配筛选条件使用。
2.非等值连接：
3.自连接：
外连接：
1.左外连接：
2.右外连接：
3.全外连接：
交叉连接：
1.等职连接
a. 查询员工名和对应的部门名

select last_name,department_name from employees,departments where employees.department_id=departments.department_id;

b.为表起别名
好处：提高代码简洁度，区分重名多个字段。
注意：表起了别名之后就不能用，原来的表名了
查询员工名，工种号，工种名，

select e.last_name,e.job_id,j.job_title from employees e,jobs j where e.job_id=j.job_id;

c.可以加筛选条件
查询城市名中第二个字符为o的部门名和城市名

select department_name,city from departments d,locations l where d.location_id=l.location_id and l.city like '_o%';

d.可以加分组
查询每个城市的部门个数

select count(*),city from departments d,locations l where d.location_id=l.location_id GROUP BY city;

e.可以加排序
查询每个工种的工种名和员工的个数，按员工个数降序

select job_title,COUNT(*)from jobs j,employees e where j.job_id=e.job_id 
GROUP BY j.job_id
ORDER BY COUNT(*) desc;

f.可以实现三表连接
查询员工名，部门名，所在的城市

select last_name,department_name,city from employees e,departments d,locations l where e.department_id=d.department_id and d.location_id=l.location_id;

2.非等值连接
查询员工工资和工资级别

select salary,grade_level from employees e,job_grades j where e.salary BETWEEN j.lowest_sal and j.highest_sal;

3.自连接（和等值连接类似，自己连接自己）
查询员工姓名和领导名

select e.last_name,l.last_name from employees e,employees l where e.manager_id=l.employee_id;

sql1999标准
语法：
select 查询列表
from 表1 别名（连接类型）
join 表2 别名
on 连接条件
（where 筛选条件）
（group by 分组）
（having 筛选条件）
（order by 排序）
内连接：inner
外连接：
左外连接：（left outer）
右外连接： (right outer)
全外连接：(full outer)
交叉连接：(cross)
sql1999语法

select last_name,department_name from employees e inner join departments d  on e.employee_id=d.department_id ;

select last_name,job_title from employees e inner join jobs j on e.job_id=j.job_id where last_name like '%e%'

select department_name,count(*)from employees e inner join departments d on e.department_id=d.department_id
GROUP BY department_name HAVING COUNT(*)>3;

select last_name,department_name,job_title 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 department_name desc;

外连接：
应用场景：用于查询一个表中有，另一张表没有的记录
特点：
1.外连接的查询结果为主表中的所有记录
如果从 中有和它匹配的的值，则显示，
如果从表中没有和他匹配的值，则显示null
2.左外连接，left join 左边的是主表
3.右外连接 ，right join 右边的是主表
4.全外连接，在MySQL中不支持，：
它的语法是这样的

交叉连接：
他查询的是笛卡尔积。

select b.*,bo.* from bauty b cross join boys bo;