1.基础概念

1.1 相关概念与常用命令

数据库的好处

1.持久化数据到本地
2.可以实现结构化查询,方便管理

常见概念

1、DB:数据库,保存一组有组织的数据的容器
2、DBMS:数据库管理系统,又称为数据库软件(产品),用于管理DB中的数据
3、SQL: 结构化查询语言,用于和DBMS通信的语言

数据库存储数据的特点

1、将数据放到表中,表再放到库中
2、一个数据库中可以有多个表,每个表都有一个的名字,用来标识自己。表名具有唯一性。
3、表具有一些特性,这些特性定义了数据在表中如何存储,类似java中“类”的设计。
4、表由列组成,我们也称为字段。所有表都是由一个或多个列组成的,每一列类似java 中的”属性”
5、表中的数据是按行存储的,每一行类似于java中的“对象”

MySQL的启动和停止

启动:net start 服务名(例如:net start mysql80)

停止:net stop 服务名(例如:net stop mysql80)

MySQL的登录和退出

登录:mysql -h 主机名 -P 端口号 -u 用户名 -p密码 
	(注意:-p和密码中间不能加空格)
	(例如:mysql 【-h localhost P 3306】 -u root -p564445 ,若是登录本地的,【】内的可以不写)

退出:exit 或 ctl+c

MySQL常见命令

show databases #查看所有的数据库
use 库名; #打开指定 的库
show tables ; #显示库中的所有表
show tables from 库名; #显示指定库中的所有表
create table 表名(
	字段名 字段类型,	
	字段名 字段类型
); 创建表

desc 表名; #查看指定表的结构
select * from 表名; #显示表中的所有数据

查询语句

select 字段名 from 表名;
select 字段名1,字段名2...... from 表名;
select * from 表名;
select 常量值;(注意:字符型和日期型的常量值要用单引号括起来,数值型不需要)
select 函数名;
select 表达式;
select 数值+数值; 	/*结果为数值*/
select 字符+数值;	/*试图将字符转换成数值,转换成功则继续运算,转换不成功则把字符当成成0再运算*/
select null+;	/*结果都为null*/

#别名
select 字段名 as 别名 from 表名;
select 字段名 别名 from 表名;	/*可直接用空格代替as*/

#去重
select distinct 字段名 from 表名;

#concat函数
select concat(字符1,字符2...... from 表名;

#ifnull函数
#判断某字段或表达式是否为null,如果为null,返回指定的值(比如指定0),否则返回原本的值
select ifnull(字符, 0) from 表名;

#isnull
#判断某字段或表达式是否为null,是则返回1,不是则返回0
select 字符1 from 表名 where 字符1 is null;
select 字符1 from 表名 where 字符1 is not null;

条件运算符

>
<
>= 
<= 
= ,<=> 安全等于
!= ,<>

逻辑运算符

and(&&):两个条件如果同时成立,结果为true,否则为false
or(||):两个条件只要有一个成立,结果为true,否则为false
not(!):如果条件成立,则not后为false,否则为true

模糊查询

like 
between and
in
is null

排序查询

#语法
select
	要查询的东西
from
	
where 
	条件

order by 排序的字段|表达式|函数|别名 asc|desc
(默认升序)

#实践代码-----------------------------
select * from employees order by salary desc;
select * from employees order by salary asc;

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

#按年薪的高低显示员工的信息和年薪(按表达式排序)
select *, salary*12*(1+ifnull(commission_pct, 0)) as 年薪
from employees
#order by salary*12*(1+ifnull(commission_pct, 0)) desc;
order by 年薪 desc;

#按姓名长度显示员工的姓名和工资【按函数排序】
select length(last_name) as 字节长度, last_name, salary
from employees
order by 字节长度 desc;

#查询员工信息,先按工资什序,再按员工编号降序【按多个字段排序】
select *
from employees
order by salary asc, employee_id desc;

1.2 实践代码

use myemployees;

select * from employees;
select first_name from employees; 
select first_name as hhh from employees; 
select first_name hhh from employees; 
select distinct department_id from employees; 
select ifnull(commission_pct, 0)  as '奖金率', commission_pct from employees;
select concat(first_name,'___' ,last_name,'___',ifnull(commission_pct, 0)) as output from employees; 
select 100;
select 'xym';
select 100*20 as resulet;
select version();

/*
select 
		查询列表
from
		表名
where
		筛选条件;
*/

select * from employees where salary>12000;
select last_name, department_id from employees where department_id <> 90;

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

select *
from employees
where not(department_id < 90 or department_id>110) 
	  or salary > 15000;

/*通配符:%任意多个字符
		  _任意单个字符
*/
select * from employees where last_name like '%a%';
select * from employees where last_name like '_e_l';

select * 
from employees 
where last_name 
like '_$_%' escape '$';*/  /*查询第二个字符为_的员工,转义*/

select * 
from employees 
where employee_id 
between 100 and 120;  /*包含临界值(闭区间),不能颠倒大小*/

select last_name, job_id
from employees 
#where job_id='IT_PROT' or  job_id= 'AD_VP' or job_id = 'AD_PRES';
where job_id in('IT_PROT','AD_VP','AD_PRES')

/*
is null:仅可以判断 null
<=>:既可以判断 null,又可以判断数值
*/

select last_name, commission_pct
from employees 
#where commission_pct is null;
where commission_pct is not null;

select last_name, commission_pct
from employees 
where commission_pct <=> null;   /*安全等于*/

select last_name, commission_pct, salary
from employees 
where salary <=> 12000;   /*安全等于*/

2.函数

#语法
select 函数名() from 

2.1 单行函数

字符函数

/*concat拼接
substr截取子串
upper转换成大写
lower转换成小写
trim去前后指定的空格和字符
ltrim去左边空格
rtrim去右边空格
replace替换
lpad左填充
rpad右填充
instr返回子串第一次出现的索引
length 获取字节个数
*/

select length('length');
select upper('select');
select lower('WERTY');
select concat(upper(last_name), lower(first_name)) 姓名 from employees;
select substr('李莫愁神雕侠侣', 4) as aa;	#索引从1开始
select substr('李莫愁神雕侠侣', 4, 2) as aa;	#后面的2表示字符长度
select concat(substr(last_name, 1, 1), '_', lower(substr(last_name, 2))) aa from employees;
select instr('倚天屠龙记屠龙记', '屠龙记') as aa;  #找不到返回0
select length(trim('     子怡     ')) as aa;
select trim('a' from 'aaa子怡aaaa') as aa;
select lpad('你好', 10, '*') output;
select rpad('你好', 10, '*') output;
select replace('张无忌_周芷若_周芷若_周芷若', '周芷若', '赵敏') aa;

数学函数

/*round 四舍五入
rand 随机数
floor向下取整
ceil向上取整
mod取余
truncate截断*/


select round(-1.35);
select round(1.5664, 2);
select ceil(1.564);		#2
select ceil(1);		#1
select floor(1.564);	#1
select floor(1);	#1
select truncate(1.6932, 2);
select mod(10, 3);		#1
select mod(-10, 3);		#-1
select mod(10, -3);  	#1
select mod(-10, -3);	#-1  总结:符号和被除数保持一致

日期函数

/*now当前系统日期+时间
curdate当前系统日期
curtime当前系统时间
datediff(date1, date2)日期之差date1-date2
str_to_date 将字符转换成日期
date_format将日期转换成字符
*/

select now();
select curdate();
select curtime();
select year(now()) , month(now()) ;
select year('1998-8-18') ;
select monthname(now()) ;
select datediff(now(), '1998-10-8');

#str_to_data:将日期格式的字符转换成指定格式日期
select str_to_date('1992-4-3', '%Y-%m-%d') output;
#查询入职日期为1992-4-3的员工信息
select * from employees where hiredate='1992-4-3';
select * from employees where hiredate=str_to_date('4-3-1992', '%m-%d-%Y');

#date-format:将日期转换成字符
select date_format(now(), '%Y年%m月%d日');
#查询有奖金的员工名和入职日期
select last_name, date_format(hiredate, '%m月/%d日 %Y年') 入职日期
from employees
where commission_pct is not null;

其他函数

/*version版本
database当前库
user当前连接用户*/

select version();
select database();
select user();

流程控制函数

/*if 处理双分支
case语句 处理多分支
		情况1:处理等值判断
		情况2:处理条件判断*/
		
select if(10 > 5, '大', '小') aa;
select last_name, commission_pct,  if(commission_pct is null, '没奖金', '有奖金') aa
from employees;

/*
case的使用法一:

case 要判断的字段或表达式
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
when 条件3 then 要显示的值3或语句3
......
else 要显示的值n或语句n
end 【as 别名】
*/
select salary 原始工资, 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的使用法二:

case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
when 条件3 then 要显示的值3或语句3
......
else 要显示的值n或语句n
end 【as 别名】
*/
select salary 原始工资, department_id,
case
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 工资级别
from employees;

2.2 分组函数

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

特点:
	1、以上五个分组函数都忽略null值且不计入总数,
	2、sum和avg一般用于处理数值型
		max、min、count可以处理任何数据类型
    3、都可以搭配distinct使用,用于统计去重后的结果
	4、count的参数可以支持:字段、*、常量值,一般放1
		count(*),计算行数时null的也会包含进去,效率高,最常用
		count(1),计算行数时null的也会包含进去,效率高
		count(字段名),得到的结果将是除去值为null和重复数据后的结果,效率低
	5、和分组函数一同查询的字段要求是group by后的字段
*/

select sum(salary) from employees;
select max(salary) from employees;
select min(salary) from employees;
select avg(salary) from employees;
select count(salary) from employees;
#组合
select sum(salary), max(salary), round(avg(salary)) from employees;
#查询部门编号为90的员工个数
select count(*) as 个数
from employees
where department_id = 90;

分组查询

/*语法:
	select 查询的字段,分组函数
	from 表
	【where 筛选条件】
	group by 分组的字段
	【order by 子句】
*/
/*
特点:
	1、可以按单个字段分组
	2、和分组函数一同查询的字段最好是分组后的字段
	3、分组筛选
			            针对的表	         位置			    关键字
	      分组前筛选:	原始表		      group by的前面		where
	      分组后筛选:	分组后的结果集	   group by的后面		 having

	4、可以按多个字段分组,字段之间用逗号隔开
	5、可以支持排序
	6、having后可以支持别名
*/


#查询每个工种的最高工资
select max(salary), job_id
from employees
group by job_id;

#查询每个位置的部门个数
select count(*), location_id
from departments
group by location_id;

#查询邮箱中包含a字符的,每个部门的平均工资
select avg(salary), department_id, email
from employees
where email like '%a%'
group by department_id;

#查询有奖金的每个领导手下员工的最高工资
select max(salary), manager_id
from employees
where commission_pct is not null
group by manager_id;

#查询哪个部门的员工个数大于2
select count(*), department_id
from employees
group by department_id
having count(*) > 2;

#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select job_id, max(salary)
from employees
where commission_pct is not null
group by job_id
having max(salary) > 12000;

#查询领导编号大于102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
select manager_id, min(salary)
from employees
where manager_id > 102
group by manager_id
having min(salary) > 5000;

#按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
select length(concat(first_name, last_name)) 长度, count(*)
from employees
group by length(concat(first_name, last_name))
having count(*) > 5;

#查询每个部门,每个工种员工的平均工资
select avg(salary), department_id, job_id
from employees
group by department_id, job_id;

#查询每个部门,每个工种员工的平均工资,平均工资大于10000,并且按平均工资的高低显示出来
select avg(salary), department_id, job_id
from employees
where department_id is not null
group by department_id, job_id
having avg(salary) > 10000
order by avg(salary) desc;

3.多表连接查询

/*
按功能分类:
	内连接:
		等值连接
		非等值连接
		自连接
	外连接:
		左外连接
		右外连接
		全外连接
	交叉连接
*/

3.1 内连接

/* sql99语法

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件

分类:
	等值
	非等值
	自连接
*/

等值连接

#等值连接
#查询员工名和对应的部门名
select last_name, department_name
from employees, departments
where employees.`department_id` = departments.`department_id`;

#为表起别名:提高语句的简洁度,区分重名字段(注意:起了别名后不能用原来的表名)
#查询员工名、工种号、工种名
select last_name, e.job_id, job_title
from employees as e, jobs as j
where e.job_id = j.job_id;

#查询有奖金的员工名、部门名
select last_name, department_name, commission_pct
from employees e, departments d
where e.department_id = d.department_id and e.commission_pct is not null;

#查询城市名中第二个字符为o的部门名和城市名
select department_name, city
from departments d, locations l
where d.location_id = l.location_id and city like '_o%';

#查询每个城市的部门个数
select count(*) 个数, city
from departments d, locations l
where d.location_id = l.location_id
group by city;

#查询每个工种的工种名和员工的个数,并且按员工个数降序
select job_title, count(*)
from employees e, jobs j
where e.job_id = j.job_id
group by job_title
order by count(*) desc;

#三表连接 sql99语法
#查询员工名、部门名、所在城市
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;

#查询员工名、部门名
select last_name, department_name
from employees e
inner join departments d
on e.department_id = d.department_id;

#查询名字中包含e的员工名和工种名
select last_name, job_title
from employees e
inner join jobs j
on e.job_id = j.job_id
where e.last_name like '%e%';

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

#查询员工名、部门名、工种名,并按部门名降序
select last_name, department_name, job_title
from employees e
inner join departments d on d.department_id = e.department_id
inner join jobs j on j.job_id = e.job_id
order by department_name desc;

非等值连接

#查询员工的工资和工资级别
select salary, grade_level
from employees e, job_grades j
where salary between j.lowest_sal and j.highest_sal;

#查询员工的工资级别 sql99语法
select salary, grade_level
from employees e
join job_grades