数据库表操作整理

• 表基本操作

  • 数据插入

    • insert into 表名（字段名） value（各个字段的值）

      • 字段名不写默认为该表中所有的字段，按照默认顺序排布

      • value值必须对应字段名

      • 字符和日期型数据应该包含在单引号中

    • 向表中插入空值

      • 隐式方式

        • 插入时只设置需要设置值的字段

        • insert into 表名（A,B,D） value（1,2,4）

      • 显示方式

        • insert into 表名（A，B，C，D） value（1，2，NULL，4）

    • 向其他表拷贝数据

      • insert into 表名 子查询

    • 查询的结果插入到另一张表注意点

      • 查询的字段数量应和插入表的字段数量相等

      • 查询的字段数据类型和插入表的字段数据类型匹配

  • 数据更新

    • update 表名 set 更新字段和数据 where 条件

    • 如果没有where，那么表中该字段会被全部更新为新的数据

  • 数据删除

    • delete from 表名 where条件

    • 没有where则删除整个表

  • 数据查找

    • 排序

      • asc升序，desc降序

      • 默认为asc

      • 排序时也可以按照字段的别名排序

      • 可以有多个排序字段

    • 过滤

      • where和having

        • 之后能否出现组函数

          • 不可

        • 出现在group by

          • 前后

    • 模糊查询

      • like '内容'

      • _ 任意一个字符

      • % 任意n个字符

    • 多表连接

      • 连接条件的分类：

        • 1.自连接 vs 非自连接

        • 一张表连接的是自身

        • 一张表连接的是其他表

        • 2.等值连接 vs 非等值连接

          • 连接条件是等号

          • 连接条件不是等号

            • 比如IN，between等等

        • 3.内连接 vs 外连接

          • 内连接：连接后表中内容只显示两表中匹配的内容（数学上的交集）

          • 外连接：看左/右连接，是谁则主要显示那张表，另一张只显示匹配的内容

      • union将两个查询的结果合并到一起

      • 笛卡尔乘积（所有数据合并成一个超级大表）

        • 产生条件

          • 缺省连接条件

          • 连接条件无效

          • 所有表中的所有行相互连接

    • 单行函数

      • 特点

        • 操作数据对象

        • 接收参数返回一个结果

        • 只对一行镜像数据变换

        • 每行返回一个结果

        • 可以嵌套

        • 参数可以是一列或一个值

      • 常用的函数

        • 大小写控制：

          • LOWER('SQL Course') ：将所有内容变成小写

          • UPPER('SQL Course') ：将所有内容变成大写

        • 字符控制

          • CONCAT('Hello', 'World') : 字符串拼接

          • SUBSTR('HelloWorld',1,5) : 截取子串。1指的是索引位置（从1开始）。5指的是长度

          • LENGTH('HelloWorld') ：子符串长度

          • INSTR('HelloWorld', 'W') :W首次出现的位置

          • LPAD(salary,10,'*') : 向右对齐

            • 如果长度不够10用*补齐

          • RPAD(salary, 10, '*') ：向左对齐

            • 如果长度不够10用*补齐

          • TRIM('H' FROM 'HelloWorld'):去除指定的字符

          • REPLACE('abcd','b','m') ：将字符串中的指定的字符进行替换

        • 数字函数

          • ROUND: 四舍五入

          • TRUNCATE: 截断

            • TRUNCATE(45.926) 45

          • MOD: 求余

        • 日期函数

          • 函数NOW() 获取当前时间

          • ifnull(字段，默认值) ：如果字段的内容为null那么就返回默认值

    • case

      • 格式一
        case 字段名
        when 值1 then 返回值1
        when 值2 then 返回值2
        when 值3 then 返回值3
        .......
        else 返回值n
        end

      • 格式二
        case
        when 判断式1 then 返回值1
        when 判断式2 then 返回值2
        when 判断式3 then 返回值3
        .......
        else 返回值n
        end

    • 组合函数（多行函数）

      • AVG() ：平均值

      • SUM() ：求和

      • 注意：数据只能是数值类型

      • MAX() ：最大值

      • MIN() : 最小值

      • COUNT() ：求数量

      • count(字段名)：该字段数据不为null的有多少条。

      • count(*)：该表中的数据有多少条

      • count(数值)：该表中的数据有多少条，count(数值)效率高于count(*)

    • where和having的区别

      • 1.where后面不能出现组函数，having后面可以出现组函数

      • 2.where在group by的前面，having在group by的后面。

    • 子查询

      • 特点

        • 在主查询之前执行

        • 其结果被外查询（主查询）调用

      • 注意

        • 子查询要包含在括号内

        • 要将子查询放在比较条件的右侧

        • 如果子查询的结果只返回一条

          • 可以使用=、<、>、>=等等比较操作符

        • 如果子查询结果返回一片

          • 使用下列比较操作符

          • IN 等于列表中的任意一个

          • ANY 和查询返回的某一个比较

          • ALL 和返回的所有值比较

    • 部分常用函数

      • trim 去除指定字符

      • length 中开始坐标为1,（不同于数组的下标从0开始）

      • ifnull 如果字段的内容为null，那么就返回默认值

• 事务

  • 开启事务

    • set autocommit=True/False 是否禁止自动提交#

  • 事务提交，提交后无法回滚

    • commit

  • 事务回滚

    • rollback

• 清空表

  • truncate table和delete table区别
    • truncate table无法事务回滚，delete table可以事务回滚

• 约束

  • 六种

    • 主键

      • PRIMARY KEY

    • 外键

      • FOREIGN KEY

    • 非空

      • NOT NULL

    • 唯一

      • UNIQUE

    • 检查

      • CHECK

    • 默认

      • DEFAULT

  • 列级约束

    • 只能约束一列数据

    • 跟在列的定义后面

  • 表级约束

    • 约束多列

    • 单独定义在最后

  • 主键约束

    • 唯一约束+非空约束的组合（简记为唯一且非空）

    • 可以使多列组合的主键约束，即表级约束，这样的话被约束的列都不允许为空，且组合值不能重复

    • 可以在列级也可以在表级约束

  • 外键约束

    • 保证一个或两个表之间的参照完整性，是两个表的两个字段之间的参照关系

    • 外键约束的参照列，在主表中引用的只能是主键或者唯一约束的列

  • 非空约束

    • 确保当前列部位空

    • 非空约束只能出现在表对象的列上

    • 主要特征

      • 所有类型的值都可以是null

      • 空字符串“”不等于null，0也不等于null

  • 唯一约束

    • 一个表中可以有多个唯一约束，多个组合的约束

    • 如果不设定唯一约束，则默认和列名相同。

  • 检查约束

    • MySQL没这玩意

• limit

  • 语法

    • limit 索引位置， 条数

    • 公式

      • select * from 表名 limit（页数-1）*页码大小【换种说法页码容量】，页码大小

  • 其位置必须在整个查询语句最后