一、数据库基础

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)
);

  1. 看数据
    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

  1. 查询常量值
    select 100;

5.查询表达式
select 100*98

6.查询函数
select version();

7.起别名
/*
便于理解
区别重名
/
%方式一:用as
select 100
98 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;

  1. +号的作用
    /*
    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后面
    • 按结果集的行列数不同:
      • 标量子查询(结果集一般为一行一列)
      • 列子查询(结果集一般为多行一列)
      • 行子查询(结果集一般为一行多列)
      • 表子查询(结果集一般为多行多列)