MySQL基本介绍

数据库的好处

1. 实现了数据的持久化
2. 使用完整的管理系统统一管理数据，易于查询

数据库的相关概念

DB

数据库（database）：存储数据的仓库。它保存了一些列有组织的数据。

DBMS

数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器。
场景的数据库管理系统有：MySQL、Oracle、DB2、SqlServer等。
DBMS分类：

• 基于共享文件系统的DBMS - Access
• 基于客户机-服务器的DBMS - MySQL、Oracle、SqlServer

SQL

结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言。
SQL的优点：

1. 不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL。
2. 简单易学
3. 虽然简单，但是一种强有力语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

数据库存储数据的特点

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

MySQL产品的特点

1. 成本低：开放源代码，一般可以免费使用。
2. 性能高：执行很快。
3. 简单：很容易安装和使用。

MySQL的版本

• 社区版（免费）
• 企业版（收费）

MYSQL常见命令

1. 登录登mysql出命令

   mysql -h host -u username -P port -p #登录mysql,-h后面指定host，-u后面指定用户名 -P 端口号 -p后面密码
   exit 退出
   

2. 显示数据库

   show databases
   

3. 进入数据库

   use databasename #进入指定的数据，指定数据的名字就可以
   

4. 显示库中的表

   show tables;  #显示当前库中的表
   show tables from databasename; #显示指定库中的所有表
   

5. 查看当前处于哪个数据库

   select database();
   

6. 创建表并查看表结构

   create table stuinfo(id int, name varchar(20)); #创建表
   desc stuinfo; #显示表结构
   show create table stuinfo; #显示见表脚本
   select * from  stuinfo; #查询表中数据
   insert into stuinfo(id, name) values(1,'john'),(2'rose'); #将数据插入表中
   

7. 查看mysql版本

   mysql version();
   mysql -V 或者 mysql --version #这个命令在数据库外执行，不用登录数据库。
   

SQL语法规范

1. 不区分大小写，但建议关键字大写，表明，列名小写。
2. 每条命令建议用分号结尾。
3. 每条命令，根据需要进行缩进和换行。
4. 注释
   单行注释： #或者–
   多行注释：/*开头*/结束

DQL 语言

基础查询

语法：select 查询列表 from 表名;
查询列表可以是：表中的字段、常量值、表达式、函数。
查询结果：虚拟表格

1. 查询表中的字段、常量值、表达式、函数

   select employee_id, first_name, last_name, email, phone_number, job_id, salary, commission_pct, manager_id, department_id, hiredate from employees; #查询表中的字段
   
   select * from employees; # 查询表中的字段
   select 100; #查询常量值
   select 'john'; #查询常量值
   select 100/90; #查询表达式
   select version(); #查询函数
   

2. 给字段起别名

   select 100/90 as result; # 方式一
   select 100/90 result; # 方式二
   

   起别名的好处：
   • 便于理解
   • 解决重名问题（一般是表关联时出现，不同的表中字段重名）
3. 去重 distinct

   select distinct department_id from employees;
   

4. MySQL中的+运算符

   select 100+90; #两个操作数都为数值型，则做加法运算
   select '123' + 90; #只要有一方时字符型数据，那么就会视图将字符型转换数值型，进行运算
   select 'john' + 90; # 字符型数据转换数值型数据失败后，会转换成0，再做运算
   select null + 90; # 只要表达式中有null，结果就是null;
   

5. MySQL中的字符串拼接函数 concat

   select concat('a','b','c') as result;
   

6. MySQL中判断是否为NULL的函数 ifnull

   select ifnull(commission_pct, 0) as commission_pct from employees;
   

条件查询

语法：select 查询列表 from 表名 where 查询条件;

1. 按照条件表达式筛选

   select * from employees where salary > 12000; # 工资大于12000的员工信息
   select * from employees where department_id != 90; #查询部门id不是90的员工信息
   select * from employees where department_id <> 90;
   

2. 按照逻辑表达式筛选

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

3. 模糊查询

   select * from employees where last_name like '%a%'; # 员工姓名包含字符a
   select last_name from employees where last_name like '_\_%'; # 查询员工姓名中第二个字符是_的员工
   select last_name from employees where last_name like '_$_%' escape '$'; # 默认转义字符是\，这里我们自定义转义字符为$
   

4. between 范围查询

   select * from employees where department_id between 100 and 120; # 员工编号在100到120之间的
   

5. in 查询

   select last_name, job_id from employees where job_id in('IT_PROT', 'AD_VP','AD_PRES'); # in 表示查询的字段值是否属于列表中的某一项，是则列出来
   

6. is null

   select last_name, commission_pct from employees where commission_pct is null; # 判读字段是否为null，应该用is null 或 is not null 而不是用 = 或者!=、<>
   

7. 安全等于 <=>，可以判断NULL，但是可读性差

   select last_name, commission_pct from employees where commission_pct <=> NULL; 
   select last_name, salary from employees where salary <=> 12000;
   

排序查询

语法：
select 查询列表 from 表名 [where 筛选条件] order by 排序列表 [asc | desc]

1. 查询员工信息，工资由高到底排序

   select * from employees order by salary desc;
   

2. 查询部门编号大于90，按照入职日期先后进行排序

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

3. 按照表达式排序

   # 按照年薪降序排列，年薪是用表达式计算出来的
   select *, salary*12*(1+IFNULL(commission_pct, 0)) as annual_salary
     from employees
    order by  salary*12*(1+IFNULL(commission_pct, 0)) desc;
   # 按照别名进行排序
   select *, salary*12*(1+IFNULL(commission_pct, 0)) as annual_salary
     from employees
    order by  annual_salary desc;
   

4. 按照函数排序

   select LENGTH(last_name) as name_length, last_name, salary 
     from employees 
    order by LENGTH(last_name) desc;
   

5. 按照多个字段排序

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

常见函数

• 单行函数

  • 字符函数

    1. length 函数

       select length('john'); #获取参数的字节个数
       select length('张无忌');
       

    2. concat 拼接字符串

       select concat(last_name,'_',first_name) from employees;
       

    3. uppper，lower函数

       select upper('john'); #转大写
       select lower('JohN'); #转小写
       

    4. substr，substring 截取字符串

       select substr('I Love You', 7); # 从第七个字符开始截取后面所有
       select substr('I Love You', 7, 3); # 从第七个字符开始截取后面三个字符
       

    5. instr 返回子串第一次出现的为止

       select instr('I Love You', 'You'); # 返回子串在字符串中的起始位置
       

    6. trim 清除字符串前后的特定字符

       select trim('  ssss   '); # 去除字符串前后的空格
       select trim('a' from 'aaaassssaaaa'); # 去除字符前后的a
       

    7. lpad 左填充，用指定的字符左填充到指定的长度

       select lpad('乔峰',10,'*');
       

    8. rpad 右填充

       select lpad('乔峰',10,'*');
       

    9. replace 替换

       select replace('张无忌爱上了周芷若','周芷若','赵敏');
       

  • 数学函数

    1. round 四舍五入

       select round(-1.55);
       select round(1.567, 2);
       

    2. ceil 向上取整

       select ceil(1.00)
       

    3. 向下取整

       select floor(-9.99);
       

    4. truncate 截断

       select truncate(1.999,1);
       

    5. mod 求余

       select mod(10, 3);
       

  • 日期函数

    1. now() 返回当前系统日期

       select now();
       

    2. curdate 返回当前系统日期

       select curdate();
       

    3. curtime 返回当前系统时间，不包含日期

       select curtime();
       

    4. year(),month(),monthname(),day(),hour(),minute(),second()

       select year(now());
       select month(now());
       select monthname(now());
       select day(now());
       select hour(now());
       select minute(now());
       select second(now());
       

    5. str_to_date 将日期格式的字符串转换成指定格式的日期

       select str_to_date('9-13-2000','%m-%d-%Y');
       

    6. date_format 将日期转换成字符

       select DATE_FORMAT('2019/7/7', '%Y年%m月%d日')
       

    7. datediff 求两个日期之间的天数

    日期格式：

    格式符	功能
    %Y	四位的年份
    %y	两位的年份
    %m	月份(01,02,…,11,12)
    %c	月份(1,2,…,11,12)
    %d	日(01,02,…)
    %H	小时(24小时制)
    %h	小时(12小时制)
    %i	分钟(00,01,…59)
    %s	秒(00,01,…59)

  • 流程控制函数

    1. if 函数

       select if(10<5,'大','小');
       

    2. case

       /*
       查询员工的工资，要求
       部门号30，显示工资的1.1倍
       部门号40，显示工资的1.2倍
       部门号50，显示工资的1.3倍
       其他部门，显示元素工资
       */
       select  
       case department_id 
       when 30 then salary*1.1
       when 40 then salary*1.2
       when 50 then salary*1.3
       else salary
       end salary
       from employees;
       
       select 
       case when department_id = 30 then salary*1.1 
            when department_id = 40 then salary*1.2 
            when department_id = 50 then salary*1.3 
            else salary 
       end salary
       from employees;
       

  • 其他函数

    1. 查看版本号

       select version();
       

    2. 查看数据库

       select database();
       

    3. 查看用户

       select user();
       

• 分组函数：

  • 基础统计
  1. sum 求和

     select sum(salary) from employees;
     

  2. avg 平均值

     select avg(salary) from employees;
     

  3. max 最大值

     select max(salary) from employees;
     

  4. min 最小值

     select min(salary) from employees;
     

  5. count 计算个数

     select count(salary) from employees;
     

     MYISAM存储引擎下，count()的效率高，因为该存储引擎下，会有一个计数器。使用count()直接返回计数器的值；INNODB存储引擎下，count(*)和count(1)效率差不多，但是比count(字段)的效率要高。因为前者省去了判断空的操作。
  • 分组统计

  1. 分组查询 group by 子句语法

     语法：
     select 查询列表，分组函数
     from 表名
     where 条件
     [group by 分组表达式]
     [order by 排序的列]

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

  2. 分组前后过滤
     分组前过滤：where 条件中的过滤，是针对元素表的过滤。过滤完成后，进行分组统计。
     分组后过滤：having 条件中的过滤，是针对分组后的结果集进行过滤。

     select max(salary) max_salary, job_id 
       from employees 
      group by job_id
      having max_salary > 20000;
     

连接查询

1. 内连接：主要是做交集运算
   • 等值连接

     select name, boyname from boys a, beauty b where a.boyfriend_id = b.id;
     

   • 非等值连接

     select name, boyname from boys a, beauty b where a.boyfriend_id != b.id;
     

   • 自连接

     # 查询员工的领导
     select a.last_name, b.last_name from employees a, employees b
     where a.manager_id = b.employee_id;
     

2. 外连接：
   • 左外连接：左边和右表可以关联到的数据 + 左边中有，右表中以NULL填充的数据

     select a.*, b.* from a left join b on a.id = b.id;
     

   • 右外连接：与左外连接相反

     select a.*, b.* from a right join b on a.id = b.id;
     

   • 全外连接：内连接 + 左表中有，右表填充NULL的数据 + 左表填充NULL的数据，右表中有

     # mysql数据库中没有实现这种连接
     select a.*, b.* from a full join b on a.id = b.id;
     

3. 交叉连接：返回两个表的笛卡儿积

   select a.*, b.* from a cross join b;
   

子查询

出现在其他语句中的select查询，称为子查询或内查询
根据子查询放的位置分为：

1. select 后面

    # 统计每个部门的员工个数
    select d.*, (select count(*) 
                   from employees e 
                  where e.department_id = d.department_id) 
      from departments d ;
   

2. from 后面

    select ag_dep.*, g.grade_level FROM ( 
      select avg(salary) ag, department_id
        from employees
        group by department_id
     ) ag_dep
     inner join job_grades g
     on ag_dep.ag between lowest_sal and highest_sal;
   

3. where 或 having后面

   1. 标量子查询（单行子查询）

       # 查询工资大于Abel工资的所有员工信息，需保证子查询只返回一个数据（单行单列）
       select * from employees
       where salary > ( select salary from employees where last_name ='Abel');
      

   2. 列子查询（多行一列查询）

      # 查询 location_id 是1400 或 1700 的部门标号
      select last_name 
        from employees 
       where department_id in (
          select distinct department_id 
            from departments 
           where location_id in(1400, 1700)
       );
      # 查询小于 任意IT_PROG 部门员工的工资的员工的信息（相当于查询小于IT_PROG部门员工最低工资的员工信息）
      select * from employees 
      where salary < any(select salary from employees where job_id='IT_PROG');
      # 查询大于 所有IT_PROG部门员工工资的员工信息	
      select * from employees 
       where salary < all(select salary from employees where job_id='IT_PROG');
      # 查询工资等于 IT_PROG部门员工的工资信息
      select * from employees 
       where salary = any(select salary from employees where job_id='IT_PROG');
      

   3. 行子查询（多列多行）

       select * from employees
       where (employee_id, salary) = (select min(employee_id), max(salary) from employees);
      

4. exists 后面（相关子查询）

    # 查询有员工的部门名
    select department_name
     from departments d 
    where exists(select * from employees e where d.department_id = e.department_id);
   

分页查询

语法：
select 查询列表
from 表
[join_type join 表2]
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段
limit offset, size; 分页相关

# 显示前五条数据
select * from employees limit 0,5;
select * from employees limit 10, 15;

联合查询（union）

将多条查询语句的结构合并成一个结果。

语法：
查询语句1
union
查询语句2

select id,name,sex from t_ca where set = '男'
union 
select t_id, t_name, t_gender from t_ua where t_gender='male';

特点：

1. 要求多条查询语句的查询列数是一致的。
2. 使用 union 关键字默认的查询结果是去重的。union all 不会去重查询结果。