MySQL

数据库的特点

  • 数据先放在表中,再放入库
  • 一个数据库可以有多个表,每个表都有一个名字,用来表示自己,表明具有唯一性
  • 表具有一些特性,这些特性定义了数据在表中如何存储,类似java“类的设计”
  • 表由列组成,也成为字段,所有表都由一个或多个列组成的
  • MySQL是关系型数据库

常用命令

  • show databases
  • show tables
  • use 数据库名
  • select database() :查看所在数据库
  • desc 表明:查看表结构
  • select distinct 列名 :去重
  • show variables :查看字符
  • +号作用:只是运算符,并不是字符串拼接,+号一方不为数字,会试图转为数字,否则算0 ,如果一方为null,结果为null

条件查询

  • 按条件表达式筛选

    > , <, =, !=, <> ,>= ,<=

  • 按逻辑表达式

    && ,||, !, and, or, not

  • 模糊查询

    like,between and ,in,is null

    like ‘%字符q%’:包含字符q

    like ‘_字符q%’:第二个字符为字符q的

排序查询

order by 字段1 desc/asc,字段2 desc/asc;

常用单行函数

  • IFNULL(字段值,如果为null,返回的值) :判断是否为空

  • CONCAT(字段,字段……) :凭借字段值

  • LENGTH(字段):返回字段长度

  • UPPER(字段):大写

  • LOWER(字段):小写

  • SUBSTRING():截取字符 SQL索引从1开始

  • INSTR():子串在目标串的起始索引

  • TRIM():去左右字符 eg:trim(‘a’ from ‘aaaaaCaaaBaa’ ) 返回CaaaB

  • LPAD:指定长度字符的左填充

  • RPAD:指定长度字符的右填充,不够会截取

  • REPLACE:替换指定字符

  • ROUND:四舍五入 ,保留几位小数

  • CEIL:向上取整,返回>=该参数的最小整数

  • FLOOR:向下取整,返回<=该参数的最大整数

  • TRUNCATE:切断,保留几位小数

  • MOD:取余

  • NOW():返回当前系统日期+时间

  • CURDATE:返回当前系统日期

  • CURTIME:返回当前时间

  • YEAR() , MOTH() ,年月日时分秒都可以

  • STR_TO_DATE:将日期格式字符转换为指定日期

    str_to_date(‘2020-12-3’,’%Y’,’%m’,’%d’)

  • DATE_FORMAT(data,’%Y-%m-%d’):日期转字符串

  • 流程控制函数

    • if( condition , true返回值,false返回值) eg:IF(10<5,’小’,’大’)

    • case 字段

      when 值1 then …

      when 值2 then …

      else …

      end

    • case

      when 条件1 then 显示的值或要执行的语句

      when 条件2 then 显示的值或者要执行的语句

      else 显示的值或要执行的语句

      end

分组函数

  • sun求和 (忽略null)
  • avg平均值 (忽略null)
  • max最大值 (忽略null)
  • min最小值 (忽略null)
  • count计算个数 只计算不为null的 (忽略null)

可以陪着distinct配合使用

分组查询

select 分组函数,列(要求出现在group by 后面)

from 表

[where 分组前筛选]

group by 分组列表

[having] (分组后筛选)

[order by 字句]

查询列表必须要求是函数和group by后面的字段

group by支持一个或多个字段查询,用,隔开

连接查询

语法

select 查询列表

from 表1 别名 [连接类型]

join 表2 别名

on 连接条件

[where 筛选条件]

[group by 分组]

[having 筛选条件]

[order by 排序列表]

连接类型分类

  • 内连接 inner(inner可以省略)应用场景:查询两表交集

    • 等值
    • 非等值
    • 自连接
  • 外连接 应用场景:查询一个表有而另一个表没有的记录

    特点:外连接的查询结果为主表的所有记录,如果从表中没有和它匹配的记录则显示null,外连接查询结果=内连接结果+主表有而从表没有的记录

    • 左外 left [outer]:left join 左边的为主表
    • 右外 right [outer]:right join 右边的是主表
    • 全外 full [outer]:结果为内连接结果+表1没有但表2有的+表2 没有但表1有的结果
  • 交叉链接 cross :sql99语法实现的笛卡尔乘积

分页查询

select from table limit (pageNo-1)\pageSize ,pageSize

联合查询

Union将多条查询语句结果合并为一个查询结果,默认去重

select *from table1

union

select *from table2

要求多条查询语句的列数一样的,查询列和类最好一致

Union All 不去重

删除

truncate清空表,效率高

特点:

  • truncate情况后的表,如果出现自增的字段,是从1开始的。delete是从断点开始。

  • truncate没有返回值,truncate没有回滚,delete有回滚

表管理

创建

1
2
3
4
5
create table tablename(
列名 列类型([长度]) [约束],
列名 列类型([长度]) [约束],
列名 列类型([长度]) [约束],
)

修改列名

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 新表名 like 旧表名 select *from 旧表

数据类型

数值型

整型

  • TinyInt:1个字节
  • Smallint:2个字节
  • Mediumint:3个字节
  • Int,integer:4个字节
  • Bigint:8个字节

特点:

  • 可以设置无符号或者有符号

    1
    2
    3
    create table 表名 (
    id INT [UNSIGNED,ZERPFILL]#unsigned,设置无符号,不可以输入负数 ,zerofill如果整形长度不够用0填充
    )
  • 如果超出范围,会插入临界值

  • 整形都有默认长度,也可以指定长度

小数

  • 定点数
    • DEC(M,D) /DECIMAL(M,D):M+2个字节,范围与double一样,但精度高
  • 浮点数
    • float(M,D):4个字节
    • double(M,D):8个字节

特点

  • M和D:M表示整数位数+小数位数,D表示小数位数,如果超过返回则插入临界值
  • 省略M和D:float和double只要不超出范围即可 decimal默认M为10,D为0

字符型

较短的文本

  • char(M):M为0~255的整数
  • varchar(M):M为0~65535之间的整数

M表示字符数,一个字母是一个字符,一个汉字也是一个字符

char是固定长度字符,varchar是可变长度字符,char效率高,varchar效率低

较长的文本

  • text
  • blob (较长的二进制数据)

日期型

  • date:只保存日期
  • datetime:保存日期加时间 ,8个字节,1000-9999年,不受时区影响
  • timestamp:保存日期加时间,4个字节,1970-2038年 ,会受时区影响
  • time:保存时间
  • year:保存年份

常见约束

约束:一种限制,用于限制表里的数据,为保证表里数据的可靠性和准确性

  • not null:用于保存字段不能为空
  • default:保证字段有默认值
  • primary key:主键约束,保证该字段具有唯一性且不为空
  • unique:保证该字段值唯一,但可以为空
  • check:mysql中不支持
  • foreign key:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值

列级约束示例

1
2
3
4
5
6
7
8
create table stu(
id INT primary key, #主键约束
name VARCHAR(20) NOT NULL ,#非空约束
gender CHAR(1) check(gender = '男' or gender='女'), #检查约束
seat INT unique ,#唯一约束
age INT DEFAULT 18,#默认约束
marjorId INT REFERENCES marjor(id) #外键约束
)

表级约束示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table stu(
id INT,
name VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
marjorId int

constraint pk primary key id,#主键约束
constraint uq unique seat,#唯一键约束
constraint ck check(gender = '男' or gender='女'),#检查约束
constraint fk_stu_major foreign key majorId references marjor(id) #外键

)

一般除了外键约束用表级写法,其余约束用列级写法

主键和唯一键区别

保证唯一性 是否允许为空 一个表可以有几个 是否可以组合
主键 1个 是(不推荐)
唯一键 可以有多个 是(不推荐)

修改约束

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

添加约束

  • 列级添加

    1
    alter table 表名 modify column 列名 类型 [约束]
  • 表级添加

    1
    2
    3
    alter table 表名 add [constraint 约束名] 约束 

    eg: 添加外键 alter table stu add constraint fk_stu_major foreign key marjorid references marjor(id)

标识列

auto_increment(必须要和key搭配使用,一个表只能最多有一个标识列,只能为数值型列设置)

可是勇士 set auto_increment_increment=n,设置步长

事务

show engines查看数据库支持的引擎,其中innodb支持事务

事务的ACID特性

  • 原子性:Atomicity

    原子性指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。

  • 一致性:Consistency

    事务必须使数据库从一个一致性状态变化为另一个一致性状态,例如转账,不管怎么转账,总金额是不变的

  • 隔离性:lsolation

    事务的隔离性是指一个事务的执行不受其他事务的干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能相互干扰。

  • 持久性:Durability

    持久性是只一个事务一旦被提交,他对数据库的数据改变就是永久的,接下来的操作和数据库故障对其是不会有影响的

事务创建

  • 隐式事务:没有明显的开启和结束标记

    insert,update,delete语句

  • 显示事务:必须先设置自动提交功能为off(set autocommit =0 ,只针对当前事务有效)

    步骤:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    #开始事务
    set autocommit =0 ;
    start transaction ;#可选的
    #编写语句
    update ……
    insert ……
    delete ……
    select ……
    #结束事务
    commit;#提交事务
    rollback;#回滚

隔离级别

同时运行多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要个隔离机制,就会导致各种问题

  • 脏读:对于两个事务T1,T2,T1读取了已经被T2更新的数据,但是T2还没有提交,后面回滚了,那么T1读的内容就是无效的。
  • 不可重复读:对于两个事务T1,T2,T1读取了一个字段,然后T2更新了这个字段并且提交了,T1后面再读的时候,读的就是字段新的数据,两次读的值不一样
  • 幻读:对于两个事务T1,T2,T1读取一个字段,然后T2在这个表插入了几条新数据,T1再读一次的时候,就会发现多出了几行数据

级别

  • READ_UNCOMMITTED:读未提交,会造成脏读
  • READ_COMMITTED:读已提交,只能读已经提交事务的记录,会造成不可重复读,当一个事务在读时,另一个事物进行修改操作并提交,事务在第二次读的时候就会发现数据不一致,导致不可重复读。
  • REPEATABLE_READ:可重复读,会造成幻读,在开始读取数据(事务开启)时,不再允许修改操作。但是可以进行插入操作,导致幻读。
  • SERIALIZABLE:串行化,单线程操作,性能低,消耗大

MySQL默认是可重复读(用 show variables like ‘transaction_isolation’)查看,老版本是tx_isolation,新版本是transaction_isolation

修改隔离级别:set [session,global] transaction_isolation level [read_uncommited,read_commited,repeatable_read,serializable]

savepoint 和 rollback to savepoint 进行搭配使用

视图

描述

视图即虚拟表,和普通表一样使用,通过表动态生成的数据。行和列数据来自定义视图的查询中使用的表,并且是在使用视图动态生成的,只保存sql逻辑,不保存查询结果

应用场景

  • 多个地方用到同样的查询结果
  • 使用查询的sql比较复杂

创建

1
2
3
create view 视图名 as
select name,id from student
join major on student.majorid=major.id;

修改

  • 1
    create or replace view 视图名 as select语句
  • 1
    alter view 视图名 as select语句

删除

1
drop view 视图名,视图名,视图名……

更新

视图更新原表也会更新,可以使用insert,update,delete语句

包含以下特点的视图不允许更新

  • 包含sql关键字:分组函数,distinct,group by,having,union 或者union all
  • 常量视图
  • select 中包含子查询
  • join关键字
  • select from一个不能更新的视图
  • where的字句的子查询引用了from字句中的表

变量

系统变量

说明

系统变量由系统提供,不是用户定义

默认查看和修改都是session变量

  • 查看所有系统会话变量

    1
    show [global / SESSION] variables [like '%utf-8%'];
  • 查看指定的系统变量名

    1
    select @@系统变量名;
  • 为系统变量赋值

    1
    set global|session 系统变量名= 值

用户变量

作用域:针对当前会话(连接)有效,同于会话变量的作用域

声明并初始化

1
2
3
4
5
6
#声明
set @用户变量=值
set @用户变量:=值
select @用户变量:=值

select @用户变量值 #查看

存储过程

一组预先编译好的SQL集合,批处理语句,提高代码的重用性,简化操作

优点

减少编译次数,简化操作,减少和服务器的连接次数,提高效率

创建

1
2
3
4
5
6
7
8
9
10
11
12
13
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的sql语句)
end

##参数列表包括三部分:参数模式,参数名,参数类型
IN stuname varchar(20)
##参数模式
IN:该参数可以作为输入,需要调用方传入
OUT:该参数可以作为输出,可以作为返回值
INOUT:既可以作为输入,也可以作为输出
## 如果存储过程体只有一条,可以begin end不写
## 存储过程体每条sql以;结尾,存储过程体的结尾可以使用 delimiter 结束标记

调用

1
call 存储过程名(实参列表)

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
use `kill`;
# 增加
delimiter $
create procedure add_item ()
begin
insert into `kill`.item values (null,'我草',null,null,null,null,null,null);
end $;
# 调用
call add_item();


#根据名称查item
delimiter $
create procedure select_by_name(in name varchar(20))
begin
select *from item where item.name=name;
end $
#调用
call select_by_name('我草');

#根据名称查ID
delimiter $
create procedure selectID_by_name(in name varchar(20),out id int)
begin
select item.id into id from item where item.name=name;
end $

#调用
call selectID_by_name('我草',@result);
select @result;

函数

和存储过程的区别是,有且只有1个返回值,0个不行,多个也不行(适合处理查询SQL,存储过程适合增删改)

语法

1
2
3
4
create function 函数名(参数列表) returns 返回类型
begin
函数体
end

使用

1
select 函数名(参数列表)

示例

1
2
3
4
5
6
7
create function count_items () returns int
begin
declare c int default 0;#定义一个变量接受结果
select count(*) into c
from item;
return c;
end $

案例

有一张股票价格表shares,假设这张表只显示一个股票的所有价格,有两个列,时间time,价格price,用SQL写出价格变化过3次以上股票最后一次变化的时间。

1
2
select count(*), max(time) as time ,price from shares group by price having count(*) > 3
order by time desc ;