Open GenweiWu opened 3 years ago
# 1.cross join/交叉连接/笛卡尔集:排列组合所有可能性m*n
select * from t_left,t_right;
# mysql不支持cross join写法
#selcet * from t_left cross join t_right on t_left.b=t_right.b
#2.inner join
select * from t_left join t_right on t_left.b=t_right.b;
#3.left join,right join
select * from t_left left join t_right on t_left.b= t_right.b;
select * from t_left right join t_right on t_left.b= t_right.b;
#4.full join:
# mysql不支持full join,可以通过 leftjoin + union + rightjoin
select * from t_left left join t_right on t_left.b= t_right.b
union
select * from t_left right join t_right on t_left.b= t_right.b;
t_left表 | a | b |
---|---|---|
a1 | b1 | |
a2 | b2 | |
a3 | b4 |
t_right
c | b |
---|---|
c1 | b1 |
c2 | b2 |
c3 | b5 |
- 交叉连接cross join笛卡尔集 select * from t_left,t_right;
a | b | c | b(1) |
---|---|---|---|
a1 | b1 | c1 | b1 |
a2 | b2 | c1 | b1 |
a3 | b4 | c1 | b1 |
a1 | b1 | c2 | b2 |
a2 | b2 | c2 | b2 |
a3 | b4 | c2 | b2 |
a1 | b1 | c3 | b5 |
a2 | b2 | c3 | b5 |
a3 | b4 | c3 | b5 |
- inner join select * from t_left join t_right on t_left.b=t_right.b;
a | b | c | b(1) |
---|---|---|---|
a1 | b1 | c1 | b1 |
a2 | b2 | c2 | b2 |
- left join select * from t_left left join t_right on t_left.b= t_right.b;
a | b | c | b(1) |
---|---|---|---|
a1 | b1 | c1 | b1 |
a2 | b2 | c2 | b2 |
a3 | b4 | (Null) | (Null) |
- right join select * from t_left right join t_right on t_left.b= t_right.b;
a | b | c | b(1) |
---|---|---|---|
a1 | b1 | c1 | b1 |
a2 | b2 | c2 | b2 |
(Null) | (Null) | c3 | b5 |
- full join select from t_left left join t_right on t_left.b= t_right.b union select from t_left right join t_right on t_left.b= t_right.b;
a | b | c | b(1) |
---|---|---|---|
a1 | b1 | c1 | b1 |
a2 | b2 | c2 | b2 |
a3 | b4 | (Null) | (Null) |
(Null) | (Null) | c3 | b5 |
t_left表
a | b |
---|---|
a1 | b1 |
a2 | b2 |
a3 | b4 |
t_right
c | b |
---|---|
c1 | b1 |
c2 | b2 |
c3 | b5 |
1.union:合并重复行+默认排序 select t_left.b from t_left union select t_right.b from t_right;
b |
---|
b1 |
b2 |
b4 |
b5 |
2.union all:不合并默认行+不排序 select t_left.b from t_left union all select t_right.b from t_right;
b |
---|
b1 |
b2 |
b4 |
b1 |
b2 |
b5 |
Host | User | Select_Priv | … |
---|---|---|---|
% | test_mysql | Y | … |
localhost | test_mysql | N | … |
Host | Db | User | Select_priv |
---|---|---|---|
localhost | request_service | test_mysql | N |
Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
---|---|---|---|---|---|---|---|
% | performance_schema | test_mysql | replication_group_members | test_mysql@localhost | 0000-00-00 00:00:00 | Select |
Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |
---|---|---|---|---|---|---|
char(n)和varchar(n)中的n表示的是字符不是字节
varchar用于可变字符串,一般是varchar(64),varchar(128)
char是定长的,一般是char(4)这种写法 char适合存储很短的字符串,或者所有值长度相近
类型 | 占据字节 | 表示形式 |
---|---|---|
datetime | 8 字节 | yyyy-mm-dd hh:mm:ss |
timestamp | 4 字节 | yyyy-mm-dd hh:mm:ss |
一种是 MyISAM,一种是 InnoDB
MyISAM 发音为 "my-z[ei]m"; InnoDB 发音为 "in-no-db"
索引是关系型数据库中,针对1个或多个字段进行预排序处理;这样后面查询的时候,就不用去全表查询,从而加快后续的查询速度 就类似于数据的目录,可以更快的访问书中的指定内容
优点: 加速查询
缺点: 时间方面:增删改都会额外耗费时间 空间方面:索引占用物理空间
where id='xx'
中的id
oder by xxx中的xxx
join on xx中on涉及的字段
主键索引:数据列不能为NULL,不能重复,一张表只能有一个主键
primary key(column1)
primary key(column1,column2)
唯一索引:数据列可以为NULL,不能重复,一张表可以建多个唯一索引
alter table tableName1 ADD UNIQUE(column1)
# 唯一组合索引
alter table tableName1 ADD UNIQUE(column1,column2)
普通索引:数据列可以为NULL,可以重复,一张表可以建多个普通索引
alter table tableName1 ADD INDEX index_name(column1)
alter table tableName1 ADD INDEX index_name(column1,column2)
全文索引
alter table tableName1 ADD FULLTEXT(column1)
唯一索引 允许 多个NULL值存在(即不认为两个NULL为重复) https://blog.csdn.net/qq_35387940/article/details/109174748
select * from user where YEAR(birthday) < 1990
-- 建立改造成
select * from users where birthday <’1990-01-01′
CREATE TABLE user_index2 (
id varchar(32),
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
-- 主键
primary key (id),
-- 唯一索引
UNIQUE (id_card),
-- 普通索引
INDEX index_name (last_name),
-- 全文索引
FULLTEXT (first_name)
);
CREATE TABLE user_index2 (
id varchar(32),
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18)
);
-- 主键
alter table user_index2 add primary key(id);
-- 唯一索引
alter table user_index2 add unique(id_card);
-- 普通索引:索引名称是可选的
alter table user_index2 add index index_222(last_name);
-- 全文索引
alter table user_index2 add FULLTEXT(first_name);
等同于
CREATE TABLE `user_index2` ( `id` varchar(32) NOT NULL, `first_name` varchar(16) DEFAULT NULL, `last_name` varchar(16) DEFAULT NULL, `id_card` varchar(18) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id_card` (`id_card`), KEY `index_222` (`last_name`), FULLTEXT KEY `first_name` (`first_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE user_index2 (
id varchar(32),
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18)
);
-- 主键:不支持
-- XXX create primary key on user_index2(id);
-- 唯一索引:必须设置索引名
create unique index unique_name on user_index2(id_card);
-- 普通索引:必须设置索引名
create INDEX index_name on user_index2(last_name);
-- 全文索引:必须设置索引名
create fulltext index key_name4 on user_index2(first_name);
CREATE TABLE user_index2 (
id varchar(32),
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
-- 主键
primary key (id),
-- 唯一索引
UNIQUE (id_card),
-- 普通索引
INDEX index_name (last_name),
-- 全文索引
FULLTEXT (first_name)
);
-- 查看所有索引类型
show index from user_index2;
-- 删除主键
alter table user_index2 drop primary key;
-- 删除唯一索引,普通索引,全文索引
alter table user_index2 drop key id_card;
alter table user_index2 drop key index_name;
alter table user_index2 drop key first_name;
一般来说,索引可以提升查询性能,但是会导致增删改
性能的损耗
索引范围查询不一定能提升性能
范围查询(>、<、between、like)
# 索引范围查询适用于: 1. 基于范围的搜索,结果只有不到30%是在范围内的 2. 基于非唯一性索引:男女性别这种,比全表扫描好不到哪儿去
由于索引增删改耗时,可以
index(column(10))
表示使用column字段的前10个字符建立索引,而默认是使用column字段的全部内容建立索引的
前缀的标识度比较高:能够有效区分不同的字段值,如果前缀都类似则不合适
如何决定前缀索引的长度,是index(column(5)还是index(column(10)))
可以通过计算 select count(*) / select count(distinct left(column,长度))的值,长度可以从1一直递增,知道表达式的值趋向于1,表示可以通过前缀能确定对应数据了
select count(*) from table1
select count(distinct left(column,4))
联合索引是指多个字段组成的索引,比如(a,b),(a,b,c),注意(a,b)和(b,a)是不同的联合索引,因为顺序不同
就是以左边为起点,任何连续的索引都能匹配上,但是一旦遇到范围查询(>、<、between、like),之后的索引就会失效
比如索引(a,b,c)
-- 下面这些sql都会走索引
select * from table where a=1
select * from table where a=1 and b=2
select * from table where a=1 and b=2 and c=3
--即索引(a,b,c)实际也包含了索引(a)和(a,b)和(a,b,c)
-- 这些没法走索引
select * from table where c=3
select * from table where b=2 and c=3
针对联合索引(a,b,c)
-- 缺少了最左的a导致下面无法使用联合索引(a,b,c)
select * from table where b=2 and c=3
-- 此时只能使用a的索引效果,无法继续使用联合索引
select * from table where a=1 and c=3
=
和in
可以乱序比如a=1 and b=2 and c=3
,建立索引时abc的顺序可以随意,mysql查询优化器会自动优化
比如 a=1 and b in(2,3)
,建立(a,b)还是(b,a)都是可以的
比如a=1 and b=2 and c>3 and d=4
,如果建立的是(a,b,c,d)的索引则只能用到abc字段;但是如果建立的是(a,b,d,c)顺序的索引,则可以用到abcd所有字段
如果通配符不出现在开头,可以使用索引
-- 可以使用索引
like 'xxx%'`
-- 不可以使用索引
like '%xxx'
like '%xxx%'
4.1 利用索引排序了,就不用再order by
-- 这里如果sex已经利用了索引进行查询,就没必要order by了
SELECT sex, price, name FROM LOL where sex = 1 ORDER BY sex ;
Atomicity:原子性
一个事务中的所有操作,要么都成功要么都失败
Consistency:一致性
事务执行结束后,数据库完整性约束不能被破坏;
比如主键限制,字段长度限制
- Isolation隔离性:
多个事务之间的相关隔离的,不能互相影响
比如A事务修改了数据没提交呢,B事务读取了数据,这就互相影响了
- Durability:持久性
对数据库的保存是持久的:
因为数据库为了避免每次都写磁盘IO,实际上它是有内存缓存的,如果数据库突然宕机了,岂不是丢失了? 方案:数据库用redo Log保证了宕机后,会根据read log来恢复
## 脏读、不可重复读、幻读
> https://www.cnblogs.com/kismetv/p/10331633.html
### 脏读: 读取了其他事务未提交的数据
![img](https://img2018.cnblogs.com/blog/1174710/201901/1174710-20190128201003630-2050662608.png)
### 不可重复读: 一个事务中两次读取的数据不一样
![img](https://img2018.cnblogs.com/blog/1174710/201901/1174710-20190128201011603-1317894910.png)
### 幻读:一个事务中两次读取的数据返回的数量不同
![img](https://img2018.cnblogs.com/blog/1174710/201901/1174710-20190128201021606-1089980279.png)
## 事务隔离级别
|隔离级别| 脏读 | 可重复读|幻读|
|--|--|--|--|
|Read Uncommitted 读未提交|V|V|V|
|Read Committed 读已提交|x|V|V|
|Repeatable Read 可重复读|x|x|V|
|Serializable 可串行化|x|x|x|
- Mysql默认级别是 Repeat Read,oracle默认级别是 Read Commited
- 事务隔离机制的实现,是基于锁机制和并发机制,其中并发机制是依赖于MVCC
多个事务并发的时候,可能会互相影响,所以要使用锁机制。
行锁:针对当前操作的行加锁 行锁加锁开销大,单锁定粒度小,并发度高
页锁:一次锁定相邻的一组记录 页锁的开销介于行锁和表锁之间,并发度一般
表锁:针对整个数据库表加锁 表锁加锁开销小,但是并发度低
Innodb使用行锁+表锁,默认行锁 MyIsam使用表锁
共享锁 读锁,共享锁可以加上多个=读读不冲突
排他锁 写锁,写锁最多只能加一个=写写冲突,写读冲突
就是说,可以多个同时来读;但是一旦有写操作,另外的读写都不可以了
Read Uncommitted | 读数据不加共享锁 |
Read Committed | 读操作加锁,但执行完语句就释放(还没提交时区) |
Repeatable Read | 读操作加锁,事务提交之后才释放锁 |
Serializable | 一直持有锁 |
悲观锁 就是通过共享锁、排他锁对事务进行加锁
乐观锁 使用版本号或CAS算法(compare and swap)
Multiple Version Concurrency Control 多版本并发控制
数据库的表成为基础表,那么视图就是虚拟表
视图感觉就是一段select语句逻辑保存成视图,后面可以进行复用
视图本身不保存数据,而是每次使用的时候动态去查询
-- 创建视图:create view `view_name` as
create view document_view as
SELECT * FROM `t_document`
where CATA_ID='2001b'
-- 视图的使用:select
select * from document_view
where DOC_ID='1'
-- 可以更新视图数据,会影响基本表
update document_view
set FILE_TITLE='local搜索测试-视图更新'
where DOC_ID='1'
存储过程是一个预编译的sql语句,创建之后可以被多次调用
如果要多次调用sql,可以考虑用存储过程替换
游标是设置一个数据缓冲区,存放sql执行结果;游标可以通过逐行读取记录并赋值给变量。 一般用在存储过程中
对某个表执行一段增删改操作时,自动去执行一段代码。 比如文档信息和文档的附件信息,删除文档时同步删除文档附件信息
比如说用户表,ID,用户名称,用户身份证号 其中ID和身份证号都是唯一标识
主键也是候选键,候选键也是超键
超键 包含了任意唯一标识的字段组合,比如(ID,用户名称),比如(身份证号,用户名称)
候选键 ID、身份证号都是候选键
主键 候选键里选一个,作为主键,一般选择ID,跟业务无关
外键 A表的字段应该是B表的某条记录对应,比如学生表和老师表,学生表的班主任应该是老师表的某条记录
CREATE TABLE Persons
(
P_Id int NOT NULL,
name varchar(255) NOT NULL,
CHECK (P_Id>0)
)
在select语句里面嵌套使用select语句,里面的select语句就是子查询
可以使用=,<,>,>=,<=,<>和子查询结果进行比较
-- 查询年龄是部门年龄最大的员工
select * from employee
where age =
(
select max(age) from employee '
)
一般使用in,any,all
-- 查询出2010年以后新建的部门的员工
select * from employee
where d_id in
(
select d_id from department
where createDate > '2010-00-00'
)
select * from department d,(select * from employee where join_data>'2020-1-1') e where d.id = e.deparment_id
-- 等同于
select * from department d join employee e on d.id = e.department_id where e.join_data>'2020-1-1'
-- 查询出2010年以后新建的部门的员工
select * from employee
where d_id in
(
select d_id from department
where createDate > '2010-00-00'
)
-- exists
select * from employee
where exists
(
select d_id from department
where employee.d_id=department.d_id and d.createDate > '2010-00-00'
)
varchar
vs char
char | varchar | |
---|---|---|
长度 | 定长字符串,长度固定 | 可变长字符串,长度可变 |
是否填充 | 数据长度小于长度时,填充空格 | 数据长度是多少,就占用多长空间 |
性能 | 固定长度,存储速度快 | 可变长度,读取速度慢 |
空间 | 占空间 | 节省空间 |
-- 删除表,包括表结构+表数据
drop table t_table;
drop table if exists t_table1;
-- 删除表部分数据
delete from t_table1 where id>10
-- 清空表数据
truncate table t_table1
Delete | Drop | Truncate | |
---|---|---|---|
删除的内容 | 删除符合条件的行 | 删除表结构和表数据 | 清空表,保留表结构 |
是否可回滚 | 可回滚 | 不可回滚 | 不可回滚 |
删除速度 | 慢,逐行删除 | 最快 | 快 |
类型 | DML | DDL | DDL |
# 速记
DDL: D表示defination定义,比如create和drop
DML: M表示Modify修改,比如insert、update、delete
DQL: Q表示查询,比如select
DCL:C表示控制,比如commit、rollback
possiable keys
说明可能能用到的索引
key
表示实际用到的索引
key_len
表示索引长度
rows
表示实际扫描的行数,出现Using fifesort
或Using temporary
IS NULL
或IS NOT NULL
,索引无法生效age/2>10
索引失效
三大范式
第一范式
如学生(学号,姓名,性别,出生年月日),如果认为最后一列要再分成(出生年,出生月,出生日),它就不是一范式了
第二范式
比如 (学号,学生名,课程名,课程成绩)中, 学号->学生名 学号+课程名->课程成绩 这个时候主键是学号,但是成绩依赖于 学号+课程名,会导致出现学号这个主键出现多次,显然不合理
第三范式
比如员工表(工号,部门编号,部门名称)里,有依赖关系:工号->部门编号->部门名称
而部门名称依赖于部门编号,而不是直接依赖工号,违反三范式