MySQL基础操作

一、数据库基础

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

二、MySQL常用操作

1.
show database
2.
use database(name)
3.
show tables
4.
select databse()
5.创建表
create table xxxx(
-> id int,
-> name varchar(20)
);

6. 看数据
   select * from xxx(表名)

7、插入数据
insert into xxx(表名) (id, name) values(2,'rose')

三、基础语法

1、基础查询

语法：
select 查询列表 from 表名

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

select
1.查询表中的单个字段
select last_name from employees

2.查询表中的多个字段
select last_name， salary from employees

3.查询表中的所有字段
select * from employees

4. 查询常量值
   select 100；

5.查询表达式
select 100*98

6.查询函数
select version();

7.起别名
/*
便于理解
区别重名
/
%方式一：用as
select 10098 as 结果；
select last_name as姓， frist_name as 名 from employee
%方式二： 使用空格
select last_name 姓， frist_name 名 from employee

8.去重
￥案例：查询员工表中所有 部门编号
select department_id from employees；
去重后
select distinct department_id from employees；

9. +号的作用
   /*
   mysql中的加号仅仅是：运算符（数学）

*/
￥案例：查询员工名和姓 连接成一个字段 并显示为姓名
select
10.concat符号
select concat(last_name, frist_name) as 姓名 from employees;
IFNULL(commissiom_pct, 0)

2、条件查询

/*
语法：
select 查询列表
from 表名
where 筛选条件；

分类：
一、 按条件表达式筛选
条件运算符：> < = != >= <=
二、按逻辑表达式筛选
逻辑运算符
&& || ！
and or not
三 模糊查询
like、 in、 is null
*/

一、按条件表达式筛选
案例一： 查询资>10000的员工信息
select *
from employee_id
where salary > 100000

案例二、查询部门标号不等于90号的员工名和部门编号
select last_name， department_id
from employees
where department_id != 90;

二、按逻辑表达式筛选：
案例一：查询工资在10000-20000之间的员工名，工资 以及 奖金
select last_name，salary， commission_pct
from employees
where salary >=10000 and salart <=20000

模糊查询

关键字：

• like
  特点：一般和通配符搭配使用
  %：任意多个字符，包含0个字符
  _: 单个字符

  案例一：查询员工名中班包含‘a’的员工名
  select *
  from employees
  where last_name like '%a%';

案例二：查询员工名
select *
from employees
where last_name like '__a_b%';

• between and
  特点：
  使用between and 简洁
  包含临界值
  两个临界值不可颠倒

  案例一：查询员工编号100-120之间的员工信息
  select *
  from employees
  where employee_id between 100 and 120

• in
  含义：判断某字段的值是否属于in列表的中的某一项
  特点：
  提到语句简洁度
  in列表的数据类型必须相同

案例：查询员工的工种编号是 IT_PROG、AD_VIP中的一个员工名和工种编号
select last_name， job_id
from employees
where job_id in (IT_PROG、AD_VIP);

• is null|is not null

案例：查询没有奖金的员工名
select last_name
from employees
where commision_pct is not null;

3、排序查询

语法:
select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc|desc】
特点：
1、desc：降序 不写默认升序
2、order by 字句可以支持单个字段，多个字段，表达式，函数，别名；
3、order by字句一般放在查询语句的最后面；

案例一：查询员工信息，要求工资降序
select *
from employees
order by salary desc

案例二：查询部门编号>= 90的员工信息，按入职时间排序【添加筛选条件】
select *
from employees
where department_id >=90
order by hiredate asc;

案例三：按照年薪的高低显示员工的信息和年薪【按表达式排序】
select ， salary12 年薪
from employees
order by salary*12 desc;

案例四：按年薪的高低显示员工信息和年薪 【按别名排序】
select ， salary12 年薪
from employees
order by 年薪 desc;

案例五：按姓名的长度显示员工名字和工资【按函数排序】
select length（last_name） 字节长度， last_name， salary
from employees
order by 字节长度 desc；

案例六：查询员工信息，要求先按 salary排序，后按员工编号排序
select *
from employees
order by salary asc， employee_id desc；

4、常见函数的学习

• 功能： 将一组逻辑语句封装咋方法体中，
  好处：1、隐藏实现细节 2、提高代码重用性
  调用： select 函数名(实参列表) 【from 表】；
  特点：
  ①：叫什么（函数名）
  ②：干什么（函数功能）

• 分类：
  1、单行函数：
  如 concat，length
  2、分组函数：
  功能：做统计使用

• 单行函数：

  • 字符函数
  • 数学函数：

一、字符函数

• 1、length 获取参数值的字节个数
  select length（‘john’）；

• 2、concat 拼接字符串
  select concat （last_name， ‘_’, frist_name）；

• 3、upper、lower 大写， 小写
  select upper(‘john’)；
  select lower(‘joHn’)；

• 4、substr 截取字符串
  select substr（‘李莫愁爱上了陆展元’，7） output；#陆展元
  select substr（‘李莫愁爱上了陆展元’，1,3） output； #李莫愁

• 5、instr 返回子串第一次出现的索引 如果找不到，返回0
  select instr('杨不悔爱上了殷六侠'，‘殷六侠’) as out_put；

• 6、trim 去掉前后指定字符
  select length(trim(' 张翠山 ')) as out_put；
  select length(‘a’, trim('aaaaaaa张aaaa翠山aaaaaaa')) as out_put；

• 7、lpad用指定字符实现左填充指定长度
  select lpad('殷素素'，2，‘*’)；

• 8、lpad用指定字符实现右填充指定长度
  select rpad(‘殷素素’，12，‘ab’)

• 9、replace 替换
  select replace('张无忌爱上了周芷若'，‘周芷若’，‘赵敏’) as out_put；

二、数学函数

• 1、round 四舍五入
  select round(1.55) ;

• 2、ceil 向上取整
  select ceil(1.52);

• 3、floor 向下取整

• 4、truncate 截断
  select truncate（1.69999,1）； //1.6

• 5、mod 取余

三、日期函数

• 1、now 返回当前系统时间

• 2、str_to_date：日期格式的字符装换为指定格式
  select str_to_date('1998-3-2','%Y-%c-%d') as put;
  查询入职日期为1992-4-3的员工信息
  select * from employees where hiredate = ‘1992-4-3’；
  select * from employees where hiredate = str_to_date（‘4-3 1992’，‘%c-%d %Y’）；

• 3、date_format（）

四、流程控制函数

• 1、if函数： if else 效果
  select if（10>5, ‘大’，‘小’）；
  select last_name，commission_pct，if(commission_pct is null， ‘没奖金，呵呵’， ‘有奖金，嘻嘻’) from employees；

• 2、case函数使用一：switch case 效果
  case 要判断的字段或表达式
  when 常量一 then 要显示的值一或语句一；
  when 常量二 then 要显示的值二或语句二；
  ...
  else 要显示的值或语句；
  end；

• 3、case函数的使用二：类似于多重 if
  case
  when 条件一 then要显示的值1或者语句1
  when 条件二 then要显示的值2或者语句2
  ...
  else 要显示的值n或语句n
  end；

4.2、分组函数

• 功能：用作统计使用，又称聚合函数或统计函数或组函数

• 分类：
  sum 求和
  avg 平均值
  max 最大值
  min 最小值
  count 计算个数

• 1、简单使用
  select sum(salary) from employees；
  select avg(salary) from employees；
  select sum(salary) ，avg(salary) from employees；

• 2、参数支持哪些类型
  sum()、avg()：数值型
  max()、min()：数值型、日期、字符型（任何类型）
  count()： 任何类型

• 3、是否忽略null值：
  所有分组函数都忽略 null值

• 4、可以和distinct搭配使用
  select sum(distinct salary), sum(salary) from employees；
  select count(distinct salary), count(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) 效率高

• 6、和分组函数一同查询的字段有限制
  要求是 group by 后的字段

5、分组查询

可以利用group by 将表中的数据分成若干组

• 语法：
  select 分组函数，列（要求出现在 group by 的后面）
  from 表
  【where 筛选条件】
  group by 分组的列表
  【order by 字句】

• 注意：
  查询列表必须特殊，要求是分组函数和group by后出现的字段

• 特点：

• 1、分组查询中的筛选条件分为两类：(数据源不一样)

  • 分组前筛选: 数据源 为 原始表； 放在group by字句的前面；关键字where
  • 分组后筛选：数据源 为 分组后的结果集； 放在group by字句的后面；关键字having
  • 分组函数做条件肯定放在having字句中
    能用分组前筛选的就用分组前筛选（性能问题）

• 2、group by子句支持单个字段分组，多个字段分组(多个字段用逗号分块，没有顺序要求)，表达式或函数

• 3、也可以添加排序（排序放在最后）

案例一：查询每个工种的最高工资
select max(salary)， job_id
from employees
group by job_id；

案例二 、查询每个位置上的部门个数
select count(*), location_id
from departments
group by location_id

• 添加复杂的筛选条件（分组后的筛选）

  案例三：查询哪个部门的员工数>2
  ①查询各部门员工数
  ②从上述结果中筛选 >2
  select count(),department_id
  from employees
  group by department_id
  having count() > 2

案例四：查询每个工种有奖金的员工的最高工资 >12000 的工种编号和最高工资
①查询每个工种有奖金的员工的最高工资
select max(salary), job_id
from employees
where commission_pct is not null
group by job_id
②根据①的结果继续筛选
select max(salary), job_id
from employees
where commission_pct is not null //where 放在分组前
group by job_id
having max(salary) > 12000 // having 放在分组后

按表达式或函数分组

案例一：按照员工姓名的长度分组，查询每一组员工的个数，筛选员工数>5的有哪些；
①查询每个长度的员工
select count(), length(last_name) len_name
from emplyees
group by length(last_name);
②添加筛选条件
select count(), length(last_name) len_name
from emplyees
group by length(last_name);
having count(*) > 5;

按多个字段分组

案例：查询每个部门每个工种的员工的平均工资
select avg(salary)，department_id， job_id
from employees
group by department_id， job_id；

添加排序

案例：查询每个部门每个工种的员工的平均工资，并按平均工资的高低排序
select avg(salary)，department_id， job_id
from employees
group by department_id， job_id；
order by avg(salary) desc；

6、连接查询

含义:多表查询，当查询的子段来自于多个表时，就会用到连接查询

分类：

• 按年代分类：
• 按功能分类：
  • 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接：
    • 左外连接
    • 右外连接
    • 全外连接
  • 交叉连接

一、基础连接

1、等值连接

-案例：

• 案例一:
  slelct name, boyname
  from boys, beauty
  where boys.id = beauty.boyfriend_id；

• 案例二：查询员工名和对应的部门名
  select last_name,department_name
  from employees，departments
  where employees.department_id = departments.department_id;

• 2、为表起别名
  select last_name,department_name
  from employees e，departments d
  where e.department_id = d.department_id;

2、自连接

• 案例： 查询员工名和上级的名称
  select e.employee_id, e.last_name, m.employee_id, m.last_name
  from employees e, employees m
  where e.manager_id = m.employee_id;

二、sql99语法 连接

• select 查询列表
  from 表1 别名 【连接类型】
  join 表2
  on 连接条件
  【where 筛选条件】
  【group by 分组】
  【having 筛选条件】
  【order by排序列表】

• 连接类型：

  • 内连接：inner
  • 外连接：用于查询一个表有，另一个表没有的记录
    特点：
    1、外连接的查询结果为主表中的所有记录
    如果从表有和它匹配的，则显示匹配的值
    如果从表没有匹配的，则显示null；
    外连接查询结果 = 内连接查询结果 + 主表中有而从表中没有的记录
    2、左外连接，left join左边的是主表
    右外连接，right join 右边的是主表
    • left 【outer】
    • right 【outer】
    • full【outer】
  • 交叉连接：cross

7、子查询

• 定义：出现在其他语句内部的select语句，称为子查询；

• 分类：

  • 按子查询出现的位置：
    • select后面
    • from后面
    • where 或 having 后面
    • exists后面
  • 按结果集的行列数不同：
    • 标量子查询（结果集一般为一行一列）
    • 列子查询（结果集一般为多行一列）
    • 行子查询（结果集一般为一行多列）
    • 表子查询（结果集一般为多行多列）