MySQL学习

Posted on Edited on Symbols count in article: 16k Reading time ≈ 14 mins.

  1. 起别名

    • 优点

      • 便于理解
      • 如果要查询的字段有重名的情况，使用别名可以区分开来

    • 使用方式

      1
      2
      3
      4
      5
      6
      # 方式一
      select count(*) '总预约数' from reservation_record 
      # 方式二
      select count(*) as '总预约数' from reservation_record 
      # 如果别名是关键字，可以使用 '' 或 "" 包裹别名
      select count(*) as "select" from reservation_record

  2. 去重

    • 使用方式

      1
      select distinct hospital_id from reservation_record

  3. “+”号的作用：mysql中的 “+” 只用来作为运算符

    • 使用方式

      1
      2
      3
      4
      5
      6
      # 如果两个操作数都是数值型，做加法运算
      select 500+20
      # 如果其中一个操作数为字符型，先尝试将其转换为数值型，如果转换成功，做加法运算；否则将其当作 0 ，再做加法运算
      select "500"+20
      # 如果其中一个操作数为 null ，则结果必为 null
      select 500+null

  4. concat()函数实现拼接

    • 说明

      • 使用concat()函数可以拼接多个内容

    • 使用方式

      1
      select CONCAT('how ','are ','u') as title

  5. 比较运算符

    • 说明

      运算符 作用
      >、<、= 大于、小于、等于
      !=、<> 不等于，<>是mysql的标准写法，建议使用<>
      >=、<= 大于等于、小于等于
      <=> 严格比较两个null值是否相等，均为null时结果为1，只有一个为null时结果为0
      between、not between （不）在两个范围之间
      in、not in （不）在集合中
      is null、is not null （不）为空
      like 模糊匹配

    • 通配符

      • %：替代0个或多个字符

      • _：替代单个字符

      • 转义：

        1
        2
        3
        4
        # 通过 "\"转义符 ，这里 \ 后的 % 不再是通配符
        select * from reservation_record where title like '_\%%'
        # 通过 escape关键字 ，这里 & 之后的 % 不再是通配符
        select * from reservation_record where title like '_&%%' escape '&'

    • 使用方式

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      # 查找年龄大于1的宠物信息
      select * from pet_archives where age > 1
      # 查找年龄不等于2的宠物信息
      select * from pet_archives where age <> 2
      # 查找年龄大于等于2的宠物信息
      select * from pet_archives where age >= 2
      # 查找年龄不在1~2范围的宠物信息
      select * from pet_archives where age not between 1 and 2
      # 查找宠物主id在（1,3,5）中的宠物信息
      select * from pet_archives where pet_user_id in (1,3,5)
      # 查找品种不为null的宠物信息
      select * from pet_archives where variety is not null
      # 查找标题第三个字符为“哈”的预约信息
      select * from reservation_record where title like '__哈%'

  6. 逻辑运算符

    • 说明：用于连接条件表达式

      运算符 作用
      &&或and 逻辑与
      ||或or 逻辑或
      !或not 逻辑非
      xor 逻辑异或

    • 使用方式

      1
      2
      3
      4
      5
      6
      7
      8
      # 查找性别为m，年龄大于1的宠物信息
      select * from pet_archives where gender = 'm' and age > 1
      # 查找性别为m或者年龄大于1的宠物信息
      select * from pet_archives where gender = 'm' or age > 1
      # 查找性别不为m的宠物信息
      select * from pet_archives where not gender = 'm'
      # 查找性别为m，年龄小于等于1、性别不为m，年龄大于1的宠物信息
      select * from pet_archives where gender = 'm' xor age > 1

  7. 排序查询

    • 说明

      • select 查询列表 from 表名 [where 查询条件] order by 排序列表 asc|desc
      • 默认升序

    • 使用方式

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      # 按照年龄从大到小顺序查询宠物信息
      select * from pet_archives order by age desc
      # 按照评分（按表达式排序）从低到高顺序查询预约信息
      select *,rate*1.0 from reservation_record order by rate*1.0 asc
      # 按照评分（按别名排序）从低到高顺序查询预约信息
      select *,rate*1.0 评分 from reservation_record order by 评分 asc
      # 按照标题长度（按函数排序）从大到小顺序查询预约信息
      select * from reservation_record order by length(title) desc
      # 按照年龄从大到小，id从小到大顺序（按多字段排序）查询宠物信息
      select * from pet_archives order by age desc,id asc

  8. 字符函数

    • length(str)

      • 返回参字符串的字节个数

        1
        select length(title) 字节数 from reservation_record
    • char_length(str)

      • 返回字符串的字符个数

        1
        select char_length(title) 字符数 from reservation_record
    • concat(str1,str2,…)

      • 拼接多个字符串

        1
        select concat(title,reservation_date) from reservation_record
    • lower(str)

      • 将字符串转为小写

        1
        select lower('HELLO')
    • upper(str)

      • 将字符串转为大写

        1
        select upper('hello')
    • substr(str,pos)

      • 截取从pos位置及末尾所有字符

        1
        select SUBSTR('welcometomysql',10)
    • substr(str,pos,len)

      • 截取从pos位置开始，字符长度为len的字符

        1
        select SUBSTR('welcometomysql',8,2)
    • instr(str,substr)

      • 返回substr子串在str中出现的第一个位置，找不到返回0

        1
        select instr('HelloWorldWorld','World')
    • trim(str)

      • 去除字符串str开始和结尾的空格

        1
        select trim('    hellowor ld   ')
    • trim(remstr from str)

      • 去除字符串str开始和结尾的remstr字符

        1
        select TRIM('A' FROM 'Ahelloworld')
    • lpad(str1,len,str2)

      • 在str1字符串开始处填充字符串str2，使其长度达到len，如果len小于原长度，从右边截断

        1
        select lpad('HELLO',8,'z')
    • rpad(str1,len,str2)

      • 在str1字符串结尾处填充字符串str2，….

        1
        select rpad('HELLO',8,'z')
    • replace(str1,s,str2)

      • 使用str2字符串替换str1字符串中的s字符串

        1
        select replace('hello','e','a')

  9. 数字函数

    • round(x)
    • 对x四舍五入，等同于round(x,0)
    • round(x,d)

      • 对x四舍五入，保留d位小数，d为负数时，指定小数点左边d位整数位为0，同时小数位也为0

        1
        2
        select round(3213.1415926,-2)
        # 3200
    • ceil(x)

      • 向上取整，返回>=x的最小整数

        1
        2
        select ceil(1.23)
        # 2
    • floor(x)

      • 向下取整，返回<=x的最大整数

        1
        2
        select floor(1.01)
        # 1
    • truncate(x,d)
    • 截断x，保留d位小数，不会进行四舍五入，d为负数时，指定小数点左边d为整数位为0 
      1
      2
      select truncate(1.234,-2)
      # 0
    • mod(x,y)

      • 返回x除以y后的余数

      • 运算规则：x-x/y*y

        1
        select mod(-10,3)

  10. 日期函数

    • now()
  • 返回系统当前日期和时间
      
    1
    2
    select NOW()
    # 2019-08-12 09:35:59
    • current_date、curdate

      • 返回系统当前日期

        1
        2
        select CURRENT_DATE()
        # 2019-08-12
    • current_time、curtime

      • 返回系统当前时间

        1
        2
        select CURRENT_TIME()
        # 09:37:27
    • year(d)

      • 根据d返回年份

        1
        2
        3
        4
        select year(now())
        # 2019
        select year('2019-08-12')
        # 2019
    • month(d)

      • 根据d返回月份

        1
        2
        select month(now())
        # 8
    • day(d)

      • 根据d返回日

        1
        2
        select day(now())
        # 12
    • hour(d)

      • 根据d返回小时

        1
        2
        select hour(now())
        # 9
    • minute(d)

      • 根据d返回分钟

        1
        2
        select minute(now())
        # 41
    • second(d)

      • 根据d返回秒

        1
        2
        select second(now())
        # 50
    • str_to_date(str,format)

      • 将日期格式的字符串转换成指定格式的日期

        1
        2
        select str_to_date('2019-08-12 9:42:00','%Y-%m-%d %H:%i:%s')
        # 2019-08-12 09:42:00
    • date_format(date,format)

      • 将日期转换成字符串

        1
        2
        3
        4
        select date_format(now(),'%Y_%m_%d %H:%i:%s')
        # 2019_08_12 09:49:51
        select date_format('2019/08/12 09:51:00','%Y/%m/%d %H:%i:%s')
        # 2019/08/12 09:51:00
    • mysql日期格式
      格式 描述
      %Y 4位年份
      %y 2位年份
      %M 英文月名（January…December）
      %m 月份（01,02,03…12）
      %c 月份（1,2,3…12）
      %D 带有英文的天
      %d 天（00,01,02,03…31）
      %e 天（0,1,2,3…31）
      %H 小时，二十四小时制（00,01,02…23）
      %h 小时，十二小时制（01,02,03…12）
      %i 分钟（00,01,02…59）
      %s 秒（00,01,02…59）

  1. 其他函数

    • version()

      • 查询数据库版本

        1
        2
        select version()
        # 8.0.14

    • database()

      • 查询当前数据库名称

        1
        2
        select database()
        # pet_hospital

    • user()

      • 查看当前用户

        1
        2
        select user()
        # root@101.231.252.114

  2. 流程控制函数

    • if(expr,v1,v2)

      • 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2

        1
        2
        select IF(10%2=0,'偶数','奇数')
        # 偶数

    • case结构

      • CASE 表示函数开始，END表示函数结束。如果condition1成立，返回result1；如果condition2成立，返回result2；如果所有condition都不成立，返回result；其中一个condition成立，后面的condition不再判断

      • 语法

        1
        2
        3
        4
        5
        6
        7
        CASE expression
            WHEN condition1 THEN result1
            WHEN condition2 THEN result2
           ...
            WHEN conditionN THEN resultN
            ELSE result
        END

      • 简单case函数

        1
        2
        3
        4
        5
        6
        7
        8
        select 
        case age
        	when 1 then '一岁大的狗'
        	when 2 then '两岁大的狗'
        	when 3 then '三岁大的狗'
        	else '老狗'
        end '年龄'
        from pet_archives

      • case搜索函数

        1
        2
        3
        4
        5
        6
        7
        8
        select 
        case 
        	when age = 1 then '一岁大的狗'
        	when age = 2 then '两岁大的狗'
        	when age = 3 then '三岁大的狗'
        	else '老狗'
        end '年龄'
        from pet_archives

      • 注意：简单case函数只返回第一个符合条件的值，剩下的自动忽略

  3. 分组函数

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

    • 分类

      • 求和：sum

        1
        select sum(age) from pet_archives

      • 平均值：avg

        1
        select avg(age) from pet_archives

      • 最大值：max

        1
        select max(age) from pet_archives

      • 最小值：min

        1
        select min(age) from pet_archives

      • 统计数量：count

        1
        select count(*) from pet_archives

    • 特点

      • sum()、avg()一般用于处理数值型
      • max()、min()、count()可以处理任意类型
      • 以上函数都可以忽略null值
      • 可以和distinct关键字搭配上述函数进行去重

    • count()函数详解

      • count(*)：对行的数目进行统计，包含NULL
      • count(column)：对指定列不为NULL的行的数目进行统计
      • 效率
        • MYISAM引擎下，count(*)的效率高
        • INNODB引擎下，count(*)和count(1)的效率差不多，比count(column)要高一些
      • 注意：和分组函数一同查询的字段要求是group by后面的字段

  4. 分组查询

    • group by

      • 语法

        1
        2
        3
        4
        5
        select 分组函数,字段（需要出现在group by后面）
        from table
        [where 筛选条件]
        group by 分组列表
        [order by 字句]

    • 特点

      • 分组查询中的筛选条件分为两类

        数据源 位置 关键字
        分组前筛选 原始表 group by子句前面 where
        分组后筛选 分组后的结果集 group by子句后面 having

      • 分组函数做条件肯定是放在having子句中

      • 能用分组前筛选的，就优先考虑使用分组前筛选

    • 按函数分组

      • 示例

        1
        select count(*),length(name) lth from pet_archives group by lth

    • 按多个字段分组

      • 示例

        1
        select id,name,age lth from pet_archives group by name,id,age

    • 添加排序

      • 示例

        1
        select id,name,age from pet_archives group by id,name,age order by age desc

  5. 连接查询

    • 含义：多表查询，查询的字段来自多个表时，使用连接查询

    • 笛卡尔乘积：A表m行数据，B表n行数据，查询结果X=m*n行数据

      • 原因：没有有效的连接条件
      • 解决：添加有效的连接条件

    • 连接查询分类

      • 按年代分类
        • sql92标准：仅仅支持内连接
        • sql99标准：推荐，支持内连接+外连接（左外和内外）+交叉连接
      • 按功能分类
        • 内连接
          • 等值连接
          • 非等值连接
          • 自连接
        • 外连接
          • 左外连接
          • 右外连接
          • 全外连接
        • 交叉连接

    • sql92标准

      • 等值连接

        • 多表等值连接的结果为多表的交际部分

        • n表连接，至少需要n-1个连接条件

        • 多表的顺序没有要求

        • 一般需要给表起别名，方便书写、避免字段模棱两可

        • 可以和之前介绍的子句配合使用，如排序、分组、筛选

        • 示例

          1
          select a.name 宠物,a.age 宠物年龄,b.nickname 主人 from pet_archives a,pet_owner_user b where a.pet_user_id = b.id and a.age > 1 group by a.name,a.age,b.nickname order by a.age desc

      • 非等值连接

        • 示例

          1
          select salary,grade_level from employee a,job_grade b where salary between b.lowest_salary and highest_salary

      • 自连接

        • 示例

          1
          select a.employee_id 员工号,a.name 员工姓名,b.employee_id 主管员工号,b.name 主管姓名 from employee a,employee b where a.manager_id = b.employee_id

    • sql99标准

      • 语法

        1
        select 查询列表 from table1 [连接类型] join table2 on 连接条件 [where 筛选条件] [group by 分组列表] [having 筛选条件] [order by 排序列表]

      • 连接类型

        • 内连接：inner
        • 外连接
          • 左外：left [outer]
          • 右外：right [outer]
          • 全外：full [outer]
        • 交叉连接：cross

      • 内连接

        • 语法

          1
          select 查询列表 from table1 inner join table2 on 连接条件

        • 特点

          • 添加排序、分组、筛选
          • inner可以省略
          • 筛选条件放在where后面，连接条件放在on后面，提高阅读性
          • inner join连接和sql92中的等值连接效果一样，都是查询多表的交集

        • 等值连接

          • 示例：三表联查

            1
            select a.name,a.age,b.nickname from pet_archives a inner join pet_owner_user b on a.pet_user_id = b.id inner join visit_record c on a.id = c.pet_id group by a.name,a.age,b.nickname order by a.age desc

        • 非等值连接

          • 示例

            1
            2
            select count(*),grade_level from employee a join job_grade b on a.salary between b.lowest_salary and highest_salary group by grade_level having
            count(*) > 20 order by grade_level desc

        • 自连接

          • 示例

            1
            select a.name 员工姓名,b.name 主管姓名 from employee a join employee b on a.manager_id = b.employee_id

      • 外连接

        • 应用场景：用于查询表1有，表2没有的记录

        • 特点

          • 外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，显示匹配的值，如果从表中没有和它匹配的，显示NULL，外连接查询结果=内连接查询结果+主表中存在但从表中不存在的记录
          • 左外连接，left join左边的是主表；右外连接，right join右边的的是主表
          • 左外和右外交换两个表的顺序，可以实现同样的效果
          • 全外连接=内连接的结果+表1有但表2没有的记录+表2有但表1没有的记录

        • 左外连接

          1
          select name,nickname from pet_owner_user a left outer join pet_archives b on a.id = b.pet_user_id

        • 全外连接

          • 注意：MySQL不支持全连接，可以通过下面的sql来实现全连接
          1
          select a.name,b.nickname from pet_archives a left join pet_owner_user b on a.pet_user_id = b.id union select a.name,b.nickname from pet_archives a right join pet_owner_user b on a.pet_user_id = b.id

      • 交叉连接

        • 示例

          1
          select a.name,b.nickname from pet_archives a cross join pet_owner_user b

  6. 子查询

    • 含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询

    • 分类

      • 按子查询出现的位置
        • select后面：仅仅支持标量子查询
        • from后面：支持表子查询
        • where或having后面：标量子查询、列子查询、行子查询
        • exist后面（相关子查询）：表子查询
      • 按结果集的行列数不同
        • 标量子查询（结果集只有一行一列）
        • 列子查询（结果集只有一列多行）
        • 行子查询（结果集只有一行多列）
        • 表子查询（结果集一般为多行多列）

    • where或having后面的子查询

      • 分类

        • 标量子查询（单行子查询）

        • 列子查询（多行子查询）

        • 行子查询（一行多列）

      • 特点

        • 子查询放在小括号内
        • 子查询一般放在条件的右侧
        • 标量子查询，一般配合单行操作符使用 > < >= <= = <> ；列子查询，一般配合多行操作符使用 IN ANY/SOME ALL
        • 子查询的执行优先于主查询执行，主查询的条件需要使用子查询的结果

      • 标量子查询

        1
        select min(salary),department_id from employee group by department_id having min(salary) > (select min(salary) from employee where department_id = 50)

      • 多行比较操作符

        操作符 含义
        IN/NOT IN 等于列表中的任意一个
        ANY/SOME 和子查询返回的某个值比较
        ALL 和子查询返回的所有值比较

      • 列子查询

        1
        select id,name,pet_user_id from pet_archives where pet_user_id in (select id from pet_owner_user where age > 20)

    • select后面的子查询

      • 示例

        1
        select a.nickname 姓名,(select count(*) from pet_archives b where a.id = b.pet_user_id) 宠物总数 from pet_owner_user a

    • from后面的子查询

      • 示例

        1
        select temp.*,a.grade_level from (select avg(salary) avg_salary,department_id from employee group by department_id) temp inner join job_grade b on temp.avg_salary between lowest_sal and highest_sal

    • exist后面的子查询

      • 示例

        1
        select a.nickname from pet_owner_user a where exists (select * from pet_archives b where a.id = b.pet_user_id)

  7. 分页查询

    • 应用场景：当要显示的数据，一页显示不完，需要分页提交sql请求

    • 语法

      • offset：要显示条目的起始索引（起始索引从0开始）
      • size：要显示的条目个数
      1
      select 查询列表 from table [join type join 表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选 oder by 排序列表] limit offset,size

    • 特点

      • limit语句放在查询语句的最后

    • 示例

      1
      select id,name,pet_user_id from pet_archives group by id limit 0,5

  8. 联合查询

    • union：联合，合并，将多条查询语句的结果合并成一个结果

    • 语法

      1
      2
      3
      4
      5
      6
      查询语句1
      union
      查询语句2
      union
      查询语句3
      ...

    • 特点

      • 要求多条查询语句的查询列数是一致的
      • 多条查询语句的查询列的类型和顺序最好保持一致
      • union关键字默认去重，如果使用union all，可以包含重复项

    • 应用场景

      • 要查询的结果来自多个表，且多个表没有直接的连接，但查询的信息一致时

    • 示例

      1
      2
      3
      select * from pet_archives where name like '%哈%' 
      union 
      select * from pet_archives where age > 1

  9. DML语言

    • 数据操纵语言

      • 插入：insert
      • 删除：delete
      • 修改：update

    • 插入语句

      • 方式1

        1
        insert into table(column1,...) values(value1,...)

      • 示例

        1
        insert into reservation_type(name,total,hospital_id) values('宠物寄养',0,1001)

      • 方式2

        1
        insert into table set column1 = value1,...

      • 示例

        1
        insert into reservation_type set name = '代遛狗',total = 0,hospital_id = 1001

      • 批量插入

        1
        insert into table(column1,...) values(value,...),(value,...),(value,...)

      • 示例

        1
        insert into reservation_type(name,total,hospital_id) values('测试1',0,1001),('测试2',0,1001),('测试3',0,1001)

      • insert方式1和方式2的比较

        • 方式1支持插入多行
        • 方式1支持子查询，方式2不支持
          • insert into table(column1,…) select xxx,…

    • 修改语句

      • 修改单表的记录

        1
        update table set column1 = value1,... where 筛选条件

      • 示例

        1
        update reservation_type set name = '代撸猫' where id = 2010

      • 修改多表的记录

        • sql92语法

          1
          update table1 别名,table2 别名,... set column1 = value1,... where 连接条件 and 筛选条件

        • sql99语法

          1
          update table1 别名 inner|left|right join table2 别名 on 连接条件 set column = value1,... where 筛选条件

      • 示例

        1
        update pet_archives a left join pet_owner_user b on a.pet_user_id = b.id set a.gender = 'f',b.gender = 'f' where b.id = 18

    • 删除语句

      • 方式1：delete

        • 删除单表的记录

          1
          delete from table where 筛选条件

        • 示例

          1
          delete from reservation_type where id = 2016

        • 删除多表的记录

          • sql92语法

            1
            delete table1的别名,table2的别名 from table1 别名,table2 别名 where 连接条件 and 筛选条件

          • sql99语法

            1
            delete table1的别名,table2的别名 from table1 别名 inner|left|right join table2 别名 on 连接条件 where 筛选条件

        • 示例

          1
          delete a,b from pet_archives a left join pet_owner_user b on a.pet_user_id = b.id where b.id = 18

      • 方式2：truncate

        • 语法

          1
          truncate table 表名

      • delete和truncate的比较

        • delete可以添加where筛选条件，truncate不可以添加筛选条件
        • truncate删除效率比delete要高一些
        • 使用delete删除记录后，再插入数据，自增列的值从断点开始；truncate删除记录后，再插入数据，自增列的值从1开始
  • delete删除有返回值；truncate删除没有返回值
    - delete是DML语句，可以使用rollback进行回滚；truncate是DDL语句，需要drop权限，因此会隐式提交，不能rollback

  1. DDL语言

    • 数据定义语言

      • 库的管理
        • 创建、修改、删除
      • 表的管理
        • 创建、修改、删除
      • 创建：create
      • 修改：alter
      • 删除：drop

    • 库的管理

      • 库的创建

        • 语法

          1
          create database 库名

      • 库的修改

        • 修改库名

          1
          rename 旧库名 to 新库名

        • 修改库的字符集

          1
          alter database 库名 character set utf8

      • 库的删除

        • 语法

          1
          drop database 库名

    • 表的管理

      • 表的创建

        • 语法

          1
          2
          3
          4
          create table 表名(
          列名 列类型(长度) 约束,# 注释
          ...
          )

        • 示例

          1
          2
          3
          4
          create table testTable(
              testId int(10) primary key,# 测试id
              testName varchar(255)# 测试名称
          )

      • 表的修改

        • 语法

          1
          alter table 表名 add|drop|modify|change column 列名 [列类型 约束]

        • 修改列名

          1
          alter table 表名 change column 旧列名 新列名 类型(长度)

        • 修改列类型或约束

          1
          alter table 表名 modify column 列名 类型(长度)

        • 添加新列

          1
          alter table 表名 add column 列名 类型(长度)

        • 删除列

          1
          alter table 表名 drop column 列名

        • 修改表名

          1
          alter table 表名 rename to 新表名

      • 表的删除

        • 语法

          1
          drop table if exists 旧表名

      • 表的复制

        • 仅仅复制表的结构

          1
          create table 目标表 like 源表

        • 复制表的结构和数据

          1
          create table 目标表 select * from 源表

        • 只复制部分数据

          1
          create table 目标表 select 查询列表 from 源表 where 筛选条件

        • 只复制某些字段

          1
          create table 目标表 select 要复制的字段 from 源表 where 0

  2. 常见数据类型

    • 数值型
      • 整数：默认有符号，如果要设置无符号，使用unsigned关键字
        • tinyint：一个字节
        • smallint：两个字节
        • mediumint：三个字节
        • int/integer：四个字节
        • bigint：八个字节
      • 小数
        • 定点型
          • decimal(m,d)：如果m>d，m+2个字节，否则d+2个字节
        • 浮点型
          • fload(m,d)：四个字节，单精度
          • double(m,d)：八个字节，双精度
    • 字符型
      • 较短的文本
        • char(m)：固定长度字符串，m在0~255之间
        • varchar(m)：可变长度字符串，m在0~65535之间
      • 较长的文本
        • text：长文本数据
        • blob：二进制长文本数据
    • 日期型
      • date：最小值1000-01-01，最大值9999-12-31
      • datetime：最小值1000-01-01 00:00:00，最大值9999-12-31 23:59:59
      • timestamp：最小值1970-01-01 00:00:00，最大值2038-01-19
      • year：最小值1901，最大值2155
      • time：最小值-838:59:59，最大值838:59:59

  3. 常见约束

    • 含义：为了保证表中数据的准确性和可靠性，对表中的数据进行的一种限制

    • 分类

      • not null：非空，保证该字段的值不能为空
      • default：默认，保证该字段有默认值
      • primary key：主键，保证该字段的值在表中是唯一的，并且不为空
      • unique：唯一，保证该字段的值具有唯一性，可以为空
      • check：检查约束，mysql不支持
      • foreign key：外键，限制两个表的关系，保证该字段的值必须来自主表的关联列的值，在从表中添加外键约束，用于引用主表中某列的值

    • 列级约束和表级约束

      • 列级约束：对一个数据列建立的约束，可在列定义时声明，也可在列定义后声明，not null、default只存在列级约束
      • 表级约束：对多个数据列简历的约束，只能在列定义后声明，primary key、unique、foreign key同时存在列级约束和表级约束

    • 创建表时添加约束

      • 添加列级约束：字段名 数据类型 约束类型 

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        create table student(
        	id int(11) primary key,# 主键
        	st_name varchar(255) not null,# 非空
        	gender char(1),
        	age int default 0,# 默认
        	c_id int foreign key reference class(id)# 外键
        )

        create table class(
        	id int(11) primary key,# 主键
        	c_name varchar(255) not null# 非空
        )

      • 添加表级约束：constraint 约束名 约束类型(字段名)

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        create table student(
        	id int,
        	st_name varchar(255),
        	gender varchar(255),
        	age int,
        	c_id int,
        	
        	constraint pk primary key(id),# 主键
        	constraint uk unique(st_name),# 唯一
        	constraint fk_c_id foreign key(c_id) reference class(id),# 外键
        )

    • 主键和唯一的比较

      • 主键：保证唯一性；不允许为空；一张表中只能有一个
      • 唯一：保证唯一性；允许为空；一张表中可以有多个

    • 外键特点

      • 需要在从表中设置外键关系
      • 从表的外键列的类型和主表的关联列的类型要求一致或兼容
      • 主表的关联列必须是一个key（一般为主键或唯一）
      • 插入数据时，先插入主表，再插入从表；删除数据时正好相反

    • 修改表时添加约束

      • 添加列级约束

        1
        alter table 表名 modify column 列名 数据类型 约束

      • 添加表级约束

        1
        alter table 表名 add constraint 约束名 约束类型(列名) [主表(关联列)]

      • 添加非空约束

        1
        alter table 表名 modify column 列名 数据类型 not null

      • 添加默认约束

        1
        alter table 表名 modify column 列名 数据类型 default 默认值

      • 添加主键

        1
        2
        3
        4
        # 列级约束
        alter table 表名 modify column 列名 数据类型 primary key
        # 表级约束
        alter table 表名 add primary key(列名)

      • 添加唯一

        1
        2
        3
        4
        # 列级约束
        alter table 表名 modify column 列名 数据类型 unique
        # 表级约束
        alter table 表名 add unique(列名)

      • 添加外键

        1
        alter table 从表名 add constraint 外键名称 foreign key(列名) reference 主表名(列名)

    • 修改表时删除约束

      • 删除非空约束

        1
        alter table 表名 modify column 列名 数据类型 NULL

      • 删除默认

        1
        alter table 表名 modify column 列名 数据类型

      • 删除主键

        1
        alter table 表名 drop primary key

      • 删除唯一

        1
        alter table 表名 drop index 约束名

      • 删除外键

        1
        alter table 表名 drop foreign key 约束名

  4. 标识列

    • 含义：不需要手动设置值，系统提供默认的序列值，又叫自增序列

    • 特点

      • 自增序列必须和主键配合使用吗？
        • 不一定，但是必须是一个key（）
      • 一个表最多有几个自增序列？
        • 最多一个
      • 自增序列可以是什么类型？
        • 只能是数值型
      • 自增序列如何设置自增的步长值？
        • set auto_increment_increment = x

    • 创建表时设置自增序列

      1
      2
      3
      create table 表名(
      	id int primary key auto_increment
      )

    • 修改表时设置自增序列

      1
      alter table 表名 modify column 列名 数据类型 [约束] auto_increment

    • 修改表时删除自增序列

      1
      alter table 表名 modify column 列名 数据类型

  5. 事务

    • 含义

      • 一条或多条sql语句执行时要么全部执行成功，要么全部失败

    • 特性

      • 原子性（Atomicity）：事务中的操作像原子一样不可分割，要么全部成功，要么全部失败
      • 一致性（Consistency）：事务的执行结果必须使数据库从一个一致性状态变为另一个一致性状态（系统状态满足数据的完整性约束；系统的状态反映数据库本应描述的现实世界的真实状态）
      • 隔离性（Isolation）：并发执行的事务不会相互影响，其对数据库的影响和它们串行执行时一样
      • 持久性（Durability）：事务一旦提交，其对数据库的影响是永久性的。任何事务或系统故障都不会导致数据丢失

    • 事务的创建

      • 隐式事务：事务没有明显的开启和结束的标记，如insert、update、delete语句

      • 显式事务：事务具有明显的开启和结束的标记

        • 前提：必须设置禁用自动提交

        • 步骤

          1
          2
          3
          4
          5
          begin或start transaction
          	sql语句,
          	...
          	...
          commit

    • 事务并发问题

      • 脏读：事务A读取了被事务B更新的数据，但事务B未提交，后面如果事务B回滚，事务A之前读取到的数据就是临时且无效的
      • 不可重复读：事务A第一次读取到一行记录row1，事务B提交后，事务A第二次读取到row1，但row1的数据已经发生变化
      • 幻读：事务A第一次读取到一行记录row1，事务B提交修改后，事务A第二次读取到row1和row2…

    • 数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题

    • 事务隔离级别

      • READ UNCOMMITED：允许事务读取未被其他事务提交的变更，脏读、不可重复读、幻读都可能存在
      • READ COMMITED：只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读、幻读可能存在
      • REPEATABLE READ：一个事务在其提交之前，读取的数据都是相同的，即使其他事务作了修改，但幻读可能存在
      • SERIALIZABLE：一个事务持续期间，禁止其他事务进行任何操作，但效率很低

    • Oracle支持的事务隔离级别：READ COMMITED（默认）、SERIALIZABLE

    • MySQL支持的事务隔离级别：READ UNCOMMITED、READ COMMITED、REPEATABLE READ（默认）、SERIALIZABLE