MySQL 基础1

MySQL-学习笔记1

进阶1 基础查询 select

一、语法

select  [list]  from  [table]

二、特点

1、查询列表可以是：表中字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格

三、简单查询的类型

1、查询表中的单个字段

select last_name from employees;

2、查询表中多个字段，使用逗号【,】分隔字段

select last_name, fisrt_name from employees;

3、查询全部字段，使用符号【*】

select * from employees;

4、起别名，使用关键字【as】

select last_name as 姓名 from employees;

5、去重，使用关键字【distinct】

select distinct job_id from jobs;

6、拼接不同的字段，使用关键字【concat】

select concat(first_name, last_name) as 姓名 form employees;

进阶2 条件查询 where

一、语法

select [list] from [table] where 筛选条件;

二、条件查询的类型

1、按照条件表达式筛选

• 条件运算符：> < = != <> <= >=

eg.1 查询工资大于12000的员工信息。

select *
from employees
where salary > 12000;

eg.2 查询部门id不等于90的员工姓名和部门id。

select first_name, department_id
from employees
where department_id != 90;

2、按照逻辑表达式筛选

• 逻辑运算符 & || ! and or not

eg.1 查询工资在10000到20000之间的员工姓名、工资和奖金。

select first_name, salary, commission_pct
from employees
where salary >= 10000 and salary<= 20000;

eg.2 查询部门编号不是90到100之间，或者工资高于15000的员工信息

#方法一
select *
from employees
where department_id <90
or department_id >100
or salary > 15000;

#方法二
select *
from employees
where not (department_id >= 90 and department_id<= 110)  
or salary>15000;

3、模糊查询

• 模糊查询符号【Like 】 一般和通配符搭配 【% 或 _ 】
• 任意多个字符 【%】
• 任意单一个字符【_】
• 范围查询【between and 】（数值范围的顺序不能调换）
• 判断某字段的值是否属于in列表中的某一项【in】(列表里的类型必须一致、不可以使用通配符)
• 非空值判断【is null 】（= <> 不能用于判断null值）

eg.1 【like 搭配 %】
查询员工姓名中含有英文字母a的员工信息

select *
from employees
where first_name like '%a%';

eg.2 【like 搭配 _】
查询员工名中第三个英文字母为n,第五个英文字母为l的员工名和工资

select last_name, salary
from employees
where first_name like '__a_l%';

eg.3 【转义符: \ 或者 escape】
查询员工名中第二个字符为_的员工名

#方法一
select last_name
from employees
where last_name like '_\_%';

#方法二
select last_name
from employees
where last_name like '_$_%' escape $;

eg.4 【between and】
查询员工编号在100到120之间的员工信息

select *
from employees
where employee_id between 100 and 120;

eg.5 【in 的使用】
查询员工的工种编号式IT_PROG/ AD_VP/ AD_PRES中的一个的员工名和工资

select first_name, salary
from employees
where job_id in ("IT_PROG", "AD_VP", "AD_PRES");

eg.6 【is null 和 <=>(安全等于）】
查询没有奖金的员工名和工资

#方法一
select first_name, salary
from employees
where commission_pct is null;

#方法二
select first_name, salary
from employees
where commission_pct <=> null;
#注释：= 不可以判断null值，但是<=> 安全等于可以判断null值

进阶3 排序查询

一、语法

select [list]
from [table]
where 筛选条件
order by 排序条件 asc(升序) / desc（降序）【默认升序】;

二、排序查询的类型

1、降序查询【desc】
eg.1 查询员工的信息，要求工资从高到低排序

select *
from employees
order salary desc;

2、升序查询【asc】

eg.1查询部门编号>=90的员工信息，按入职时间的先后进行排序

select *
from employees
where department_id >=90
order by hiredate asc;

3、按别名排序
eg.1 按照年薪的高低显示员工的信息和年薪

select *,salary*12*(1+IFNULL(commission_pct,0)) as 年薪
from employees
order by 年薪 desc;

4、按函数排序
eg.1 按姓名的长度显示员工的姓名和工资

select  concat(first_name, " ", last_name), salary
from employees
order by length(concat(first_name," ",last_name)) desc;

5、按多个字段排序(排序条件用逗号分隔)
eg.1 查询员工信息，要求按照工资排序，再按员工编号排序

select *
from employees
order by salary asc, employee_id desc;

进阶4 常见函数

一、概念

类似于java 的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

二、特点

• 隐藏了实现细节，提高代码的重用性
• 叫什么，做什么

三、调用方法

select 函数名（实际参数） 
from [table]

四、常见函数类型

1、字符函数

• 判断长度的函数： length()
• 拼接字符的函数：concat()
• 判断空值的函数：ifnull（）
• 改变英文大小写的函数：upper（）/ lower（）
• 返回截取从指定索引处后面所有字符 substr()
• 返回截取从指定索引处指定字符长度的字符: substring()
• 返回子字符在字符中的起始索引，如果找不到则返回0: instr()
• 返回截取指定字符后的字符： trim ()
• 应指定字符实现左填充指定长度:lpad()
• 应指定字符实现右填充指定长度: rpad()
• 使用特定字符特换指定字符的函数：replace（）

2、日期函数

• 返回当前系统日期+时间: now()
• 返回当前系统日期，不包含时间: curdate()
• 返回当前时间,不包含日期：curtime()
• 获取指定部分，年、月、日、小时、分钟、秒

#获取年
select year(now());

#获取月
select month(now());

#获取日
select date(now());

#获取小时
select hour(now());

#获取分
select minute(now());

• 将字符通过指定的格式转化成日期 : str-to-date()

select str_to_date('1995-3-02','%Y-%m-%d')

• 将日期转化成字符: date_format()

select date_format(now(),'%m月%d日-%Y年');

eg.1 查询有奖金的员工名和入职日期（xx月/xx日 xx年）

select last_name, date_format(hiredate,'%m月/%d日  %Y年')
from employees  
where commission_pct is not null;

3、数学函数

• 四舍五入 round()

select round(1.456,2);

• 向上取整 ,返回>=该参数的最小整数: ceil()

select ceil(1.2);
#返回2

• 向下取整,返回<=该参数的最大整数: floor()

select floor(1.2)
#返回1

• 截断，返回保留输入参数指定小数个数的值：truncate（）

select truncate(1.69999,2)

• 取余, 余数的正负号与被除数的正负有关

select mod(10,3)

select 10%3

4、分组函数 / 统计函数 / 聚合函数 / 组函数

• 统计个数函数：count()

select count(salary) from emplyees;

• 求和函数：sum()

select sum(salary) from employees;

• 最小值函数：min()

select min(salary) from emplyees;

• 最大值函数：max()

select max(salary) from emplyees;

• 平均值：avg()

select avg(salary) from emplyees;

5、流程控制函数

• 条件函数：if（）

select if (commission_pct is not null, '有奖金，嘻嘻','无奖金，呵呵')
from  employees

• case （switvh的效果）

语法

case [要求判断的字段名或表达式]；
when 常量1  then 要显示的值1或语句1；
when 常量2  then 要显示的值2或语句2；
when 常量3  then 要显示的值3或语句3；
when 常量4  then 要显示的值4或语句4；
else 要显示的值n或语句n
end as 别名
from 表名；

eg.1 查询员工的工资，要求:
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资。

select department_id, salary as 原工资, 
         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）

语法

case
when 条件1  then 要显示的值1或语句1
when 条件2  then 要显示的值2或语句2
...
else  要显示的值n或语句n
end as 别名
from [table]

eg.1 查询员工工资的情况，要求：
如果工资>20000, 显示A级别
如果工资>15000，显示B级别
如果工资>10000, 显示C级别
否则，显示D级别。

select salary as 工资，
         case 
         when salary > 20000 then 'A'
         when salary > 15000 then 'B'
         when salary > 10000 then 'C'
         else 'D'
         end as 工资级别
         from employees;

进阶5 分组查询【group by】

一、语法

select 查询字段
from 表
where 筛选条件 (分组前）
group by 分组字段
having 筛选条件（分组后）
order by 排序字段

二、特点

1、分组查询中的筛选条件分为两类

• 分组查询前筛选
  位置：group by 之前
  关键字： where and
• 分组查询后筛选
  位置：group by 之后
  关键字： having and

三、分组查询的类型

1、统计平均值
eg.查询每个部门的平均工资

select avg(salary), department_id
from employees
group by department_id;

2、统计最大值
eg.查询每个工种的最高工资

select max(salary), job_id
from employees
group by job_id;

3、添加筛选条件的分组统计
eg.查询邮箱中包含a字符的部门的平均工资

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

4、分组前筛选
eg.查询每个领导有奖金的手下员工的最高工资

select max(salary), manager_id, commission_pct
from employees
where commission_pct is not null
group by manager_id;

5、分组后查询
eg. 查询员工个数>2的部门

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

6、查询每个工种有奖金的员工的最高工资>12000的工种编号和其最高工资
第一步：查询每个工种有奖金的员工的最高工资
第二步：基于第一步的结果筛选最高工资>12000

select job_id, max(salary)
from employees
where commission_pct is not null 
grooup by job_id
having max(salary)>12000;

7、查询手下员工最低工资>5000且编号>102的领导编号，以及手下的最低工资

select manager_id, min(salary)
from employees
where manager_id >102
group by manager_id
having min(salary)>5000;

8、按表达式/函数分组查询
eg.按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(1)>5

9、按多个字段查询
eg.查询每个部门每个工种的员工的平均工资

select avg(salary),department_id,job_id
from employees
group by department_id, job_id;

10、多次分组查询
eg.查询每个部门每个工种的员工的平均工资，按平均工资由高到低显示

select department_id, job_id, avg(salary) as 平均工资
from employees
group by department_id, job_id
order by avg(salary)

进阶6 连接查询

笛卡尔现象：表1 有m行，表2 有n行，结果=n*m行

一、连接类型：

1、内连接

• 等值连接

select 查询字段
from 表名1，表名2
where 表1.字段=表2.字段
and 筛选条件
group by 分组字段
order by 排序字段

• 非等值连接
  eg.1查询员工的工资和工资级别

select salary, grade_level
from employees e, job_grades g
where e.`salary` between g.lowest_sal and g.highest_sal;

2、自连接【同一张表需要查询两次】
eg.1查询员工名及其上司的姓名

select a.last_name as 员工名, b.last_name as 上司名
from employees a, employees b
where a.`manager_id`=b.`employee_id`;

SQL99 语法

1、自连接

• 语法

select 查询字段
from 表1
inner join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 筛选条件

2、外连接

• 特点
  （1）外连接的查询结果为主表中所有的记录：

  • 如果从表中有和它匹配的，则显示匹配的值；
  • 如果从表中没有和它匹配的，则显示null
  • 外连接查询结果=内连接结果+主表有而从表没有的记录

  （2）左外连接：left join 左边的是主表
  （3）右外连接：right jion 右边的是主表

• 语法

左外连接

select 查询字段
from 主表 a1
left outer join from a2 从表 
on a1.连接字段 = a2.连接字段
where 筛选条件

右外连接

select 查询字段
from 主表 a1
right outer join from a2 从表 
on a1.连接字段 = a2.连接字段
where 筛选条件