MySQL学习日记

SQL的分类

DQL数据查询(select)
DML数据操纵(insert, delete, update)
DDL数据定义(create, drop, alter)
TCL事务控制(commit, rollback)
DCL数据控制(grant, revoke)

---

简单查询

select * from 表名;

• 显示表中所有字段,先将*号转化为所有字段名,效率低。
• select 语句永远不会进行修改操作，只会查询。
• select XXX as YYY (将XXX字段显示为YYY，只是更改显示名，不改原始表。as可省略)
• 数据库中的字符串用单引号‘’括起来，双引号不规范。
• select语句中可以使用数学运算符
  例：字段是月薪，想要知道年薪可以：

select 月薪*12 from 表名;

---

条件查询

select 字段 from 表名 where 条件;

条件部分中，查询某区间的数据可以用

＞＝ A and ＜＝ B

或

between A and B

此语句是闭区间

• 数据库中表示为null不能用=，要用is null
  不为null就是is not null
• or相当于逻辑或。
• in(X,Y,Z) 不是表示区间，相当于多个or，示例为取X,Y,Z
• and 的优先级比or高，有需要或者不确定的话用()优先执行or

where sal > 2500 and (deptno = 10 or deptno = 20);

• not 可以取非，主要用在 is 或 in 中
  is null
  is not null
  in
  not in
• like
  称为模糊查询，支持%或下划线_匹配
  %匹配任意多个字符
  _：任意单个字符。
  （%是一个特殊的符号，_ 也是一个特殊符号）
  找出第二个字每是A的？
  select ename from emp where ename like '_A%';

---

排序

单个字段排序

select 字段 from 表 order by 字段;

默认升序

指定降序：加一个desc表示指定降序

select 字段 from 表 order by 字段 desc;

指定升序：加asc

select 字段 from 表 order by 字段 asc;

---

多个字段排序

select 字段 from 表 order by 字段，字段;

可以单独指定升序降序。
只有前一个字段相等时，后一个字段的排序才生效。

---

通过列排序

select 字段A，字段B from 表 order by 2;

2是第二列的意思，在此表中为根据字段B排序。或：根据查询结果的第二列排序
开发中不建议。列的顺序很容易发生改变。

order by 总是排在最后。

---

单行处理函数

特点，输入多少就输出多少
输入5rows，输出5rows

---

• lower 转换小写
• upper 转换大写
• substr 截取子串
  (substr(被截取的字符串, 起始下标, 截取长度))
• length 取长度
• trim 去空格
• sri_to_date 字符串转日期
• date_format 格式化日期
• format 设置千分位
• round 四舍五入
• round(1245.567, 0)
  0表示保留到整数位，1保留到十分位，-1保留到十位。
• rand() 取随机数
  取0-1内的随机数，通过个round配合以取不同位数的随机数
  取100以内随机数：round(rand()*100, 0)
• ifnull 将null转化为一个具体值
  ifnull(数据, 转换值) //NULL若是直接参与运算，结果一定为NULL。
• concat 进行字符串拼接
  concat(字段, 字段)
• case ... when ... then ... when ... then ... else ... end

case 字段 when 情况 then 执行动作 else ... end

如果没有else，其他情况下的数据为NULL。

---

分组函数/多行处理函数

• 特点：输入多行，输出一行。
  输入5rows，输出1rows
• 使用时必须分组，不分组则默认整表为一组。
• 自动忽略NULL，不需要提前处理NULL。
• 不能使用在where子句中。（由于where先于分组执行）
• 所有分组函数可以组合使用

select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;

---

• count 计数

select count(字段) from 表
count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。
count(*)：统计表当中的总行数。（只要有一行数据count则++）
因为每一行记录不可能都为NULL，一行数据中有一列不为NULL，则这行数据就是有效的。

• sum 求和

select sum(字段) from 表;

• avg 平均值

select avg(字段) from 表;

• max 最大值

select max(字段) from 表;

• min 最小值

select min(字段) from 表;

---

分组查询（重要）

关键字的运行顺序：
from->where->group by->having->select->order by
因为先where再group by，所以分组函数不能放在where子句中。where在执行时，尚未分组。

having关键词与where类似。having与group by 绑定使用，having不能单独使用。

select job, max(sal) from XXX group by XXX having max(sal) > 3000;

能用where解决的问题尽量用where，用having效率低。

---

distinct关键字

只能出现在所有字段之前：

select distinct XXX, YYY from XXX;

distinct出现在多个字段之前，表示去除全部字段联合后的重复项。

---

连接查询

A表取XXX字段，B表取YYY字段，这种跨表查询就是连接查询。

连接查询的分类

• 根据语法年代
  SQL92
  SQL99
  根据表连接的方式分类
• 内连接
  • 等值连接
  • 非等值连接
  • 自连接
• 外连接
  • 左外连接（左连接）
  • 右外连接（右连接）

笛卡尔积现象

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象。

避免方法

连接时加条件，满足条件的记录被筛选出来。

内连接（完全符合连接条件的数据）

• 等值连接（连接条件为等量关系）

案例：查询每个员工所在部门名称，显示员工名和部门名？
emp e和dept d表进行连接。条件是：e.deptno = d.deptno

SQL92语法：
    select 
        e.ename,d.dname
    from
        emp e, dept d
    where
        e.deptno = d.deptno;
    
    sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。

SQL99语法：
    select 
        e.ename,d.dname
    from
        emp e
    join
        dept d
    on
        e.deptno = d.deptno;
    

    //inner可以省略（带着inner可读性更好！！！一眼就能看出来是内连接）
    select 
        e.ename,d.dname
    from
        emp e
    inner join
        dept d
    on
        e.deptno = d.deptno; // 条件是等量关系，所以被称为等值连接。

    
    sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

    SQL99语法：
        select 
            ...
        from
            a
        join
            b
        on
            a和b的连接条件
        where
            筛选条件

• 非等值连接（连接条件不是等量关系）
  自己动脑子想条件，如between...and语句。
• 内连接的自连接（在同一张表里筛选出有连接的记录）【如：人员名单里有员工和领导，筛选出员工和他的领导】
  技巧：一张表当两张表用，给一张表起两个别名 ，然后等值连接。

外连接（其中一个表是主表，显示完符合条件的结果后，还会显示主表的剩余结果）

• 左外连接（左连接）

select 
        e.ename,d.dname
    from
        emp e
    left outer join
        dept d
    on
        e.deptno = d.deptno;

select e.ename,d.dname from emp e left outer join dept d on e.deptno = d.deptno;

• 右外连接（右连接）

select 
        e.ename,d.dname
    from
        emp e
    right outer join
        dept d
    on
        e.deptno = d.deptno;

select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno;

outer可以省略，写了可读性强
外连接的查询结果条数一定>=内连接的条数

多表连接

语法：
select
  ...
from
  a
join
  b
on
  a和b的连接条件
join
  c
on
  a和c的连接条件
right join
  d
on
  a和d的连接条件

一条SQL中，内外连接都可以出现

---

子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询。

子查询都可以出现在哪里呢？
select
  ..(select).
from
  ..(select).
where
  ..(select).

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）
select后面的子查询不需要掌握。

DML语法（create，drop，insert，update，delete）

建表

create

create table 表名（字段名1 数据类型，字段名2 数据类型，字段名3 数据类型）；
表名建议以t_或者tbl_开始，可读性强。
字段名：见名知意。
create table 表名 as select语句;
将查询结果快速建表，可通过*复制表。注意as关键字，可省略。

数据类型

• varchar(最多255字符)
  智能动态分配空间
  优点：节省空间
  缺点：浪费时间，效率不如char

• char(最多255字符)
  容易浪费空间，效率高

• int(最长11字符)
  整型

• bigint
  长整型，类似JAVA的long

• loat
  单精度浮点数

• double
  双精度浮点数

• date
  短日期类型,只包括年月日。默认格式 %Y-%m-%d.

• datetime
  长日期类型，还包含时分秒。默认格式 %Y-%m-%d %h:%i:%s.
  可以使用now()函数直接获取当前datetime类型的系统时间。

• clob
  字符大对象
  最多可以存储4G的字符串。
  比如：存储一篇文章，存储一个说明。
  超过255个字符的都要采用CLOB字符大对象来存储。
  Character Large OBject:CLOB

• blob
  二进制大对象
  Binary Large OBject
  专门用来存储图片、声音、视频等流媒体数据。
  往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，
  你需要使用IO流才行。

删表

drop

drop table 表名;
如果没有对应的 表名就会报错。
drop table if exists 表名;
// 此方法没找到不会报错。建议使用。

插入数据（insert）

insert into 表名(字段名1,字段名2,字段名3...) values(值1, 值2, 值3);

字段名和值（数量、类型）一一对应。
运行一次，增加一条记录。只增不能改。

insert into 表名 values(值1, 值2, 值3);

字段名省略表示给所有字段赋值。后面的值必须按顺序，全部写上。

insert into 表名 select语句;

将查询结果插入，较少用。

修改数据(update)

udpate 表名 set 字段1 = ..., 字段2 = ... where 约束条件;

通过where条件约束修改，不然改整表所有数据。

删除数据（delete，truncata）

delete from 表名 where 约束条件;

不加where删除整表数据。

truncata bable 表名;

物理删除，快，不可恢复。（DDL操作）

约束

约束字段保证数据完整性，时效性
包括

• 非空约束：not null
  • 输入值不能为空
• 唯一性约束：unique
  • 不能重复，可为NULL。
  • 联合唯一性：create table 表名(字段 类型, 字段2 类型, unique(字段, 字段2));【表级约束】
• 主键约束：primary key
  • 可有单一主键和复合主键。
  • 有auto_increment 来使主键自增
• 外键约束：foreign key
  • foreign key(引用的字段) references 被引用表(被引用字段)
  • 被引用的字段不一定是主键，但至少具有唯一性。
• 检查约束未学