Mysql
1. 查看表结构命令
desc 表名;
创建表 create table 表名
删除表 drop table if exists 表名
修改表结构使用关键字 alter
2.起别名
关键字as,as可以省略,如果别名为dept name中间有空格,需要用单或双引号将其括起来’dept name’
3.字段参与数学运算
select sal*12 as yearsal from emp;
4.between …and …
闭区间,需要遵循左小右大的原则,左大右小查不出来
5.数据库属性值null
null不能用等号衡量,需要使用is null
select * from emp where comm is null;
select * from emp where comm is not null;
6.and 和or的优先级问题
and的优先级比or高
查询工资大于2500,并且部门编号为10或20部门的员工
select *
from emp
where sal > 2500 and (deptno = 10 or deptno = 20);
7.in
in不是一个区间,是具体的值
查询薪资是800和5000的员工信息
select * from emp where sal = 800 or sal = 5000;
select * from emp where sal in (800,5000);
8.not
not可以取非
- is null
- is not null
- in
- not in
9.模糊查询
%表示任意多个字符
_表示任意一个字符
关键字like
- 名字含有字母o where name like ‘%o%’;
- 名字以字母a开始 where name like ‘a%’;
- 名字以字母z结尾 where name like ‘%z’;
- 名字第二个字母为b where name like ‘_b%’;
10.转义字符
\可以将一些关键字转换为字符串
11.排序
- 单字段排序
默认是升序
指定升序:order by 字段名 asc;
指定降序:order by 字段名 desc;
- 多字段排序
在前的起主导作用,只有前面的排序条件相等的时候,才会启用后面的字段排序
按照薪资升序,如果薪资一样的话,再按照名字降序排列
select name,sal
from emp
order by sal asc,name desc;
12.单行处理函数
-
lower():将字段名转换为小写 select lower(name) from emp;
-
upper():将字段名转换为大写 select upper(name) from emp;
-
substr():取子串substr(被截取的字符串,起始下标,截取的长度) 注意:起始下标从1开始,不是从0
找出员工名字第一个字母为A的员工信息
//模糊查询
select * from emp where name like 'A%';
//substr()函数
select * from emp where substr(name,1,1) = 'A';
-
length():取某个字段的长度
-
concat():拼接字符串
-
trim():去空格
-
round():四舍五入 round(1234.567,0) 结果是 1235 round(1234.567,1)结果是1234.6
-
ifnull():空处理函数,在数据库中,只要有NULL参与的数学运算,最终结果就是NULL
ifnull()函数用法ifnull(数据,被当作哪个值),ifnull(comm,0)
- case … when … then …when …then…else … end
13.count
count(具体字段):表示统计该字段下所有不为NULL的元素的总数
count(*):统计所有行数
14.分组函数不能直接使用在where子句中
- 需要结合group by使用
15.分组查询
select… from… where… group by … order by …
如果使用了group by关键字,group by后面的字段名需要在select后面出现
16.having
having不能使用where代替,where的效率比having高,优先使用where
having需要和group by使用,对分组后的数据进行过滤
17.distinct(去重关键字)
- 对查询结果进行去重
- distinct关键字只能出现在查询字段的最前方
select distinct job,ename from emp;
18.连接查询
- 自然连接
- 左右外连接
-
左外连接(左面是主表)
SELECT r.A,r.B,r.D,s.E FROM r LEFT JOIN s ON s.A = r.A AND s.B = r.B
![在这里插入图片描述](https://img-blog.csdnimg.cn/85d821445f8a406caff6391f51bdef37.png#pic_center -
右外连接(右面是主表)
SELECT s.A,s.B,r.D,s.E FROM r RIGHT JOIN s ON s.A = r.A AND s.B = r.B
19.笛卡尔积现象
当两张表进行连接查询,没有任何条件限制的时候,最终的记录条数为两个表的记录乘积
select ename,dname from emp,dept;
- 尽量避免表的联合
20.多张表怎么连接
语法:
select…from a join b on a和b的连接条件 join c on a和c的连接条件 join d on a和d的连接条件
21.union合并查询结果集
select ename,job from emp where job in (‘MANAGER’,‘SALEMAN’);
等同于
select ename,job from emp where job = ‘MANAGER’ union select ename,job from emp where job = ‘SALESMAN’;
- union结果集合并的时候,需要两个结果集的列数相同
22.limit
limit 3 取前三条数据
limit 0,3 取前三条数据
limit 1,3 从第二条数据开始取3条数据
- 分页
每页显示3条记录
第1页:limit 0,3
第2页:limit 3,3
第3页:limit 6,3
第4页:limit 9,3
每页显示pageSize条记录
第pageNo页:limit (pageNo - 1) * pageSize,pageSize
23.DQL大总结
select … from … where … group by … having … order by … limit …
24.mysql中的数据类型
- varchar(最长255)
可变长的字符串,比较智能,节省空间,会根据实际的数据长度动态分配空间
- char(最长255)
定长字符串
- int(最长11)
等同于Java中的int
- bigint
数字中的长整型,等同于Java中的long
-
float
单精度浮点型
-
double
双精度浮点型
- date
短日期类型
- datetime
长日期类型
- clob
字符大对象,最多可以存储4G的字符串,比如一篇文章
- blob
二进制大对象
Binary Large OBject
专门用来存储图片,声音,视频等流媒体数据(通过IO流来实现)
25.插入Insert
语法格式:
insert into 表名(字段名1,字段名2,字段名3) values (值1,值2,值3);
如果表名里面的字段省略,后面values中的值需要全部写上
- insert插入多条数据
insert into user(id,name,birth,create_time) values (1,‘zs’,‘1980-10-11’,‘now()’), (2,‘ls’,‘1990-10-25’,‘now()’);
26.修改Update
语法格式:
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3 where 条件;
- 没有where条件会导致所有数据全部更新
27.删除Delete
语法格式:
delete from 表名 where 条件;( 一般条件是id = ?)
- 没有条件会导致整张表的数据全部删除
28.mysql日期格式
- mysql日期格式
%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒
- java日期格式
yyyy-MM–dd HH : mm : ss SSS
29.date和datetime两个类型的区别
date是短日期:只包括年月日信息
datetime是长日期:包括年月日时分秒信息
mysql段日期默认格式:%Y-%m-%d
mysql长日期默认格式:%Y-%m-%d %h:%i:%s
mysql获取当前系统时间?now()函数,并且获取的时间带有时分秒信息
30.删除表数据
delete from 表名;//这种删除方式比较慢
- delete删除原理:表中的数据被删除了,但是数据在硬盘上的真实存储空间不会被释放
- 优点:支持回滚
- 缺点:删除效率比较低
truncate table 表名;
-
truncate原理:表被一次截断,物理删除
-
优点:快速
-
缺点:不支持回滚
数据量特别大的表需要使用truncate
31.约束
-
约束:constraint
-
约束类别:
- 非空约束(not null)
- 唯一性约束(unique)
- 主键约束(primary key) 一张表主键约束只能有一个,primary key(id,name)这也是一个主键约束
- 外键约束(foreign key)
- 检查约束(mysql不支持,oracle支持)
-
扩展:什么时候使用表级约束呢? 需要给多个字段联合起来添加某一约束的时候,需要使用表级约束 unique(name,email):联合唯一
-
思考:子表中的外键引用父表中的某个字段,该引用的字段必须是主键么,不一定是主键,但至少有unique约束
-
测试:外键字段的字段值可以为NULL么? 答:可以
32.存储引擎
-
存储引擎这个专业术语只在mysql中才有
-
mysql支持九大存储引擎,默认为InnoDB
-
常见存储引擎:MyISAM存储引擎、InnoDb存储引擎、MEMORY存储引擎
33.MyISAM存储引擎
- 它管理的表具有以下特征:
使用三个文件表示每个表:格式文件-存储表结构(mytable.frm)
数据文件-存储表内容(mytable.MYD)
索引文件-存储表上的索引(mytable.MYI)
-
MyISAM存储引擎优点:可以转换为压缩、只读来节省空间
-
提示:对于一张表来说,只要是主键,或者加有unique约束的字段上会自动创建索引
34.InnoDb存储引擎
-
mysql默认的存储引擎,同时也是一个重量级的存储引擎
-
InnoDB支持事务,支持数据库崩溃后自动恢复机制
-
InnoDB存储引擎最重要的特点是:非常安全
-
它管理的表具有以下特征:
每个InnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间tablespace被用于存储表的内容(表空间是一个逻辑名称,表空间存储数据加索引)
提供一组用来记录事务性活动的日志文件
在mysql服务器崩溃后提供自动恢复
35.MEMORY存储引擎
数据存储在内存中,且行的长度固定,这两个特点使得MEMORY存储引擎非常快
MEMORY存储引擎以前被称为HEAP引擎
MEMORY存储引擎优点:查询效率是最高的,不需要和硬盘交互
MEMORY存储缺点:不安全,关机之后数据消失,因为数据和索引都存在内存中
36.事务
-
概念:一个事务就是一个完整的业务逻辑
-
事务的使用
- 只有DML语句才会有事务一说(insert、delete、update)
-
事务的本质:一个事务其实就是多条DML语句同时执行成功,或者同时失败
-
提交事务:commit; 默认情况下,mysql是支持自动提交事务的
-
回滚事务:rollback;
37.事务特性
-
A:原子性:事务是最小单元,不可再分
-
C:一致性:所有操作必须同时成功,或者同时失败,保证数据的一致性
-
I:隔离性:A事务和B事务具有一定的隔离
-
D:持久性:事务结束的一个保障。事务提交,相当于保存数据,对数据进行持久化操作
38.事务的隔离性
- 事务与事务的隔离级别
- 读未提交:read uncommitted(最低的隔离级别)
- 事务A可以读到事务B未提交的事务
- 存在问题:脏读,读取到了脏数据,这种隔离级别一般都是理论上的,隔离级别一般都是二档起步
- 读已提交:read committed
- 事务A只能读取到到事务B提交之后的数据
- 解决问题:解决了脏读的现象
- 存在问题:不可重复读取数据,事务B可能不断的提交数据,事务A每次读到的数据都是绝对的真实
- 可重复读:repeatable read (mysql默认隔离级别)
- 概念:事务A开启之后,不管多久,只要事务A不结束,即使事务B已经发生修改并提交,事务A读取到的数据还是没有发生改变,这就是可重复读。
- 存在问题:每一次读取到的数据都是幻象,不够真实。
- 序列化/串行化:serializable(最高的隔离级别)
- 事务A不结束,事务B就无法进行select等操作
- 读未提交:read uncommitted(最低的隔离级别)
39.索引
-
索引是在数据库字段上添加的,索引相当于一本书的目录,增加查询效率
-
主键会自动添加索引对象
-
原理:索引在mysql当中是一个树的形式存在,且排序方式为左小右大
-
索引的创建和删除
-
创建索引:create index emp_ename_index on emp(ename); 给emp表的ename字段添加索引,起名:emp_ename_index
-
删除索引:drop index emp_ename_index on emp;
-
查看是否使用索引:explain + sql语句
-
索引失效:模糊查询索引就会失效,where当中的索引列使用了函数
-
40.视图
-
创建视图对象:create view dept2_view as (只能是DQL查询语句)
-
删除视图对象:drop view 视图名;
-
视图的作用:我们可以面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作
-
视图在实际开发中的作用:假设有一条非常复杂的sql语句,而这条SQL语句需要在不同的位置上反复使用。 可以把这条很长的sql语句以视图对象的形式新建
41.数据库三范式
- 第一范式:每一张表必须有主键,每一个字段原子性不可再分
- 第二范式:要求所有非主键字段完全依赖主键,不要产生部分依赖
多对多怎么设计?三张表,关联两个外键
- 第三范式:要求所有非主键字段直接依赖主键,不要产生传递依赖