数据类型
- 字符串类型
(1)char—定长字符串,最大长度为256个字节
(2)varchar—可变长字符串,最大长度为256个字节 对应Java中的String 常用
(3)tiny text—同varchar,最大长度为256个字节
(4)text—可变长字符串,最大长度为64KB
(5)mediumtext—可变长字符串,最大长度为16KB
(6)longtext—可变长字符串,最大长度为4GB
- 整数类型
(1)int或integer—4个字节 对应Java中的的int 常用
(2)tinyint—1个字节 对应Java中的byte
(3)smallint—2个字节 对应Java中的short
(4)mediumint—3个字节
(5)bigint—8个字节 对应Java中的long
- 小数类型
(1)float—4个字节 对应Java中的float
(2)double—8个字节 对应Java中的double
(3)decimal(m, n) m指定整数的位数,n指定小数的位数
- 日期时间类型
(1)date—日期 对应Java.sql中的Date
(2)time—时间 对应Java.sql中的Time
(3)datetime—日期时间 对应Java.util中的的Date 常用
(4)timestamp—时间戳 对应Java.sql中的Timestamp
- 二进制类型
(1)blob—Binary Large Object—最大长度为64KB
(2)tinyblob—最大长度为256B
(3)mediumblob—最大长度为16MB
(4)longblob------最大长度为4GB
常用函数
- 字符串类函数
(1)concat(s1, s2, s3, …) //连接字符串
(2)lower(s) //将字符串全部变成小写
(3)upper(s) //将字符串全部变成大写
(4)ltrim(s) //去除字符串左侧的空格
(5)rtrim(s) //去除字符串右侧的空格
(6)trim(s) //去除字符串左右两侧的空格
(7)lpad(s, len, pad) //用字符串pad来对字符串s的左侧进行填充,直至长度达到len
(8)rpad(s, len, pad) //用字符串pad来对字符串s的右侧进行填充,直至长度达到len
(9)repeat(s, n) //将字符串s重复n次
(10)replace(s, source, dest) //将字符串中包含source的字符替换成dest
(11)strcmp(s1, s2) //比较s1与s2,如果相同返回0,s2大于s1返回1,s2小于s1返回-1
(12)left(s, n) //返回字符串最左边的n个字符
(13)right(s, n) //返回字符串最右边的n个字符
(14)substr(s, begin, len) //返回从begin位置开始的len个字符,begin从1开始
(15)instr(s, target) //返回s中出现target子串的位置,从1开始,找不到则返回0
(16)insert(s, index, len, target) //从index位置开始的len个字符替换成target字符串
(17)length(s) //返回字符串的长度
(18)reverse(s) //返回字符串的逆序
- 数学类函数
(1)ceil(n) //返回大于n的最大整数
(2)floor(n) //返回小于n的最大整数
(3)rand() //返回0到1之间的随机小数
(4)round(n, len) //将n保留len位小数,四舍五入
(5)truncate(n, len) //将n保留len位小数,直接截断
- 日期时间类函数
(1)now()和sysdate() //返回当前日期时间 常用
(2)curdate() //返回当前日期
(3)curtime() //返回当前时间
(4)year/month/day/week/hour/minute/second(date) //返回日期中的年/月/日/周/时/分/秒
(5)date_format(date, pattern) //格式化输出日期,相当于SimpleDateFormat类
pattern参数的取值
%y 表示两位数字年份 %Y 表示四位数字年份
%m 表示两位数字月份 %M 表示月份的英文名称
%d 表示两位数字的天数
%H 表示两位数字的小时数,24小时制
%i 表示两位数字的分钟数
%s 表示两位数字的秒数
(7)datediff(date1, date2) //返回date1与date2相差的天数
(8)date_add(date, INTERVAL n type) //给日期date加上n个指定类型的值
type参数的取值—year/month/day/hour/minute/second
- 流程控制类函数
(1)if(条件, 表达式1, 表达式2) //如果条件为true,返回表达式1,否则返回表达式2
(2)case
when 条件1 then 表达式1
when 条件2 then 表达式2
when 条件n then 表达式n
else 表达式n+1
end
(3)ifnull(表达式1,表达式2) //如果表达式1的值不为null,返回表达式1,否则返回表达式2 经常用于对空值进行处理
(4)isnull(表达式) //如果表达式的值为null,则返回1,否则返回0
表操作
表约束
- primary key – 主键
- auto_increment – 自增
- not null – 非空
- default – 默认值
- foreign key(deptno) references dept(deptno) //表级外键约束
- unique(empname) //表级唯一约束
删除表 drop truncate delete 的区别
- drop删除表结构和表中所有数据,速度最快,自动提交
- truncate删除表中所有数据,保留表结构,自动提交
- delete from 表名[where 条件] 可以按行删除表内容,保留表结构,DML语句,记录到事务日志,支持回滚
修改和展示
- 添加字段 alter table 表名 add (字段名 类型 [not null] [default 默认值]) [After 指定列名]
- 删除字段 alter table 表名 drop (字段名)
- 修改字段 alter table 表名 modify (字段名 类型 [not null] [default 默认值])
- 查看数据库中所有表名 show tables
- 查看表结构 desc 表名
- 修改表名 rename table 原表名 to 新表名
数据约束
- 主键约束 — primary key 主键 = 非空 + 唯一
- 一张表只能有一个主键
- 主键可以包含多个列,称为复合主键
- 主键分为 自然主键和业务主键
- 自然主键 – 自增长(auto_increment)主键
-业务主键 – 根据业务规则和算法生成的主键
- 外键约束 — foreign key
- 非空约束—not null
唯一约束—unique
自定义检查约束—check(检查条件)
select操作
执行顺序
- from 加载,连接来自不同数据表的数据
- where 对记录行进行过滤筛选
- group by 将数据进行分组
- having 对分组进行过滤筛选,可以使用分组函数进行计算
- select 提取列行,并计算所有表达式(计算列),可以使用分组函数进行计算
- order by --排序
- limit — 分页
重点语法
- 空值的处理函数
- ifnull(表达式1,表达式2) //如果表达式1的值不为null,返回表达式1,否则返回表达式2
- isnull(表达式) //如果表达式的值为null,则返回1,否则返回0
- 去重 关键字 DISTINCT
- 分组函数不能嵌套使用,只能使用子查询实现
- 每个派生表都必须有别名
- 自连接时,一个表操作俩次,需要给表起别名,子查询用在from子句中,应该给临时表起别名
- 为了方便在其他地方使用分组函数的结果,通常需要给分组函数起别名
- where中选行条件的解析顺序是从右向左的,尽量把可能为假的放在右边
- select子句和having子句中可以使用分组函数,where子句中不能使用分组函数
- 分组函数中null值的处理
- max/min/sum/avg 忽略列值为null的行
- count分为俩种情况 count(*) 不忽略null 值的行 count(列名)忽略null值的行
- group by 子句后,查询结果是每组一条记录,select子句中的字段要么是分组字段,要么是分组函数
多表连接
- natural join 自然连接是在两张表中寻找那些数据类型和列名都相同的字段,然后自动地将他们连接起来,并返回所有符合条件按的结果。
- inner join 产生的是AB的交集
- left join 产生的是A的完全集, B匹配则有值,不匹配则null值
- right join 产生的是B的完全集,A匹配则有值,不匹配则null值
- full join 产生的是AB的并集合 MySQL中不支持full join的写法,可以使用union联合两个查询结果
- 自连接 自己与自己进行查询,需要给表起别名
union和union all的区别
(1)union会自动去掉两个集合中重复的行,同时字段按照默认规则的排序
(2)union all只将两个集合中的数据合并,不会自动去重和排序
多行子查询中的null值处理
如果 not in 语句里面有null值 会出错
原因
A in (a,b,null) 相当于 A=a or A= b or A=null
A not in (a,b,null) 相当于 A!=a and A!=b and A!=null 所以直接相当于 A is not null 所以错了
存储引擎
- 数据引擎描述了数据表在磁盘数据文件中的存储结构,描述了数据表是否需要支持外键,索引,事务等功能
- 常见的存储引擎有InnoDB, MyISAM, MEMORY三种 可以使用 show engines命令查看MySQL支持的引擎
InnoDB引擎
MyISAM引擎
索引
- 索引是对列创建索引
- 索引的存储结构是由B+Tree实现的
- 不加unique创建普通索引,加unique创建唯一索引
- 具有外键约束的列,创表时会自动为列创建普通索引
- 具有唯一约束的列,创表时会自动为列创建唯一索引
创建:
- create index 索引名 on 表(列)
- alter table 表 add index 索引名(列)
- key 索引名 列 // 建表时,创建索引
删除:
- drop index 索引名 on 表名
- alter table 表 drop index 索引名
索引的分类
聚簇索引
- 索引顺序和数据表的物理顺序相同
- 聚簇索引的数据结构采用B+Tree
- 索引表文件和数据表文件是同一个文件
- 一个表只能有一个聚簇索引,也就是主键,建表时自动为主键创建了聚簇索引
非聚簇索引
- 索引顺序和数据表的物理顺序不相同
- 索引表和数据表是不同的文件
- 具有外键约束的列,建表时会自动为该列创建普通的非聚簇索引索引
- 具有唯一约束的列,建表时会自动为该列创建唯一的非聚簇索引索引
B-Tree
B+Tree
索引的优缺点
【优点】使用索引可以显著地提供数据查询的速度,在多表连接,分组,排序时,索引也能提高性能
另外,唯一索引还能保证表中的记录不重复
【缺点】创建索引需要耗费时间,也会占用存储空间,所以索引不是越多越好
如果对表经常做插入和删除,每次都需要动态维护和更新索引,有了索引反而会影响性能
视图
- 视图封装了一个SQL查询,用于简化数据查询和限制数据更新操作
- 视图是一张虚表,只具有表结构,但不存储数据
创建视图
create view 视图名 as select语句
使用视图
- 视图的主要作用是查询
- 只有行列子集视图允许进行添加和删除操作
- 对视图的操作实际上都是对基本表中数据的操作
- 行列子集视图—对单张表只进行选行和选列操作的视图,可以执行增删改查所有操作
- 非行列子集视图—对单张表分组,或对多张表连接后得到的视图,只能查询,不能添加和删除
删除视图
drop view 名
视图的优缺点
【优点1】由于视图封装了一条复杂的查询语句,所以可以简化程序中SQL语句的书写
【优点2】只能操作视图中定义的数据,提高了数据访问的安全性
【缺点1】查询视图时,需要将其转换为对基本表的查询,所以执行效率不如直接查询基本表高
【缺点2】更新视图操作受限,只有行列子集视图允许进行增删操作
函数 了解
存储结构 了解
触发器 了解
- 对表进行insert/update/delete操作时,自动执行触发器中定义的代码
create trigger 触发器名 before|after insert|delete|update on 表名 for each row
begin
触发器代码
end