MySQL基础学习笔记

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

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

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

启动：net start 服务名（例如：net start mysql80）

停止：net stop 服务名（例如：net stop mysql80）

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

退出：exit 或 ctl+c

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;

#语法
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;

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;   /*安全等于*/

#语法
select 函数名() from 表

/*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;

/*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;

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

/* 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