1.sql语言分类
DQL(数据查询语言)[DataQueryLanguage]:查询语句,select
DML(数据操作语言)[Data Manipulation Language]:insert、delete、update,对表中数据进行增删改
DDL(数据定义语言)[DataDefinitionLanguage]:create、drop、alter,对表结构的增删改
TCL(事务控制语言)[transaction control language]:commit提交事务,rollback回滚事务
DCL(数据控制语言)[Data Control Language]:grant授权、revoke撤销权限等
实际开发中,出现在代码中的sql包括:insert ,delete, update ,select
增删改查有一个术语:CRUD操作
即:create retrieve (检索)update delete
在MySQL当中,凡是标识符是可以使用飘号 ` 括起来的,但最好不要用,不通用。
MySQL默认使用的存储引擎是InnoDB方式–可以自己设定
存储引擎这个名字只有在MySQL中存在(Oracle中有对应的机制,不叫存储引擎,没有特殊的名字,就是“表的存储方式”)
默认采用的字符集是UTF8
基于wamp–cmd操作–(以下mysql命令,不是sql语言)
1.从c盘切换到d盘–d:
2.输入路径–cd: D:\wamp64\bin\mysql\mysql5.7.14\bin
3.mysql -u账户 -p密码,为防止密码隐私,可以直接mysql -u账户 -p
4.成功后,界面是:
5.展示所有数据库–show databases;
6.创建数据库–create database 数据库名;
7.使用数据库–use 数据库名字;
8.查看当前使用数据库中有哪些表–show tables;
9*(可选).初始化数据库数据–source sql脚本的路径;
10.删除数据库–drop database 数据库名;
11.展示数据库中的表–show tables;
12.查表的结构–desc 表名;
13.查看当前使用的是哪个数据库–select database();
14.查看mysql的版本号–select version();
15.结束一条语句–\G --不过用;也可以
16.退出mysql–exit
17.查看创建表的语句–show create table 表名;
18.查看引擎–show engines
19.查看sql语句的执行计划,可以看到查看了几条(rows)等等–explain sql语句
2.in
//两种写法一样
SELECT * FROM `alarm_all` WHERE `alarm_id`='2' OR `alarm_id`='4'
SELECT * FROM `alarm_all` WHERE `alarm_id` IN('2','4')
3.模糊查询like
//_表示任意一个字符,%表示多个字符
SELECT * FROM `alarm_all` WHERE `machine_id`LIKE '_3%'
//如果是收_是否含有时候,用\_
SELECT * FROM `alarm_all` WHERE `alarm_level`LIKE'%\_%'
4.排序order
//默认Order by 升序
//asc表示升序,desc表示降序
//排序优先级自左向右,如比较data5,只有data5都相同的时候才会进行deal_num
SELECT * FROM `test_sht` WHERE 1 ORDER BY`data5`ASC,`deal_num`DESC
5.别名as
select 3
from 1
where 2
order by 4
SELECT `data5` AS nihao FROM `test_sht` WHERE 1 ORDER BY nihao
//即在select中执行了as,在order by可以使用别名,当然原本的名字也可以使用
//别名使用过程中可以不用as
//注意别名不要加引号,否则没有效果
6.分组函数
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
注:所有分组函数都是对某"某一列"数据进行操作的,且自动忽略null,包括--等
但是count(*)则不考虑null,直接统计记录总条数
SELECT SUM(`data5`) FROM `test_sht` WHERE 1
数据库中进行运算时候,只要有null参与的运算结果一定为null,需要采用ifnull(可能为null的列名,设定的数字)
如SELECT `data5`+2 FROM `test_sht` WHERE 1
SELECT ifnull(`data5`,0)+2 FROM `test_sht` WHERE 1
分组函数不可以直接使用在where中,因为group by是在where执行之后才会执行,而分组函数是在分组才进行执行,现在还没有分组,所以无法在where中执行
7.group by 和having
group by :按照某个字段或者某些字段进行分组
having:having是对分组之后的数据进行再次过滤
注意:分组函数一般都会跟group by联合使用,也是为什么称之为分组函数的原因,
并且任何一个分组函数都是在group by语句执行完之后才执行;
当一条sql语句没有group by的话,整张表的数据都会自成一组;
当一条语句中有group by 的话,select后面只能跟分组函数和参与分组的字段,如自动生成的id编码无法出现在select中,否则会报错;
如果有多个group by条件,可以直接用逗号隔开,不存在优先级;
having是过滤,可以在里面增加分组函数,但要写的话必须有group by
select 5
from 1
where 2
group by 3
having 4
order by 6
8.NULL
在进行一行数据进行结构设置时候,如果设置可以空,且默认NULL的话
在where时候进行判断,无法通过=进行,需要用is null,或者is not null
SELECT * FROM `test_sht` WHERE `data5`IS NOT null
9.查询结果集的去重
SELECT DISTINCT `data5` FROM `test_sht` WHERE 1
可以去掉重复出现的数据,但是要注意distinct 只能出现在所有字段的最前面,如果后面有2个或者多个字段,则表示对满足所有字段的整体字段进行去重;
distinct的最前面可以跟分组函数进行同步使用
SELECT COUNT(DISTINCT `data5`) FROM `test_sht` WHERE 1
10.笛卡尔积现象
当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积。
避免:加条件进行过滤,但是不会减少匹配次数,依旧是记录条数乘积数量,只是显示的是有效记录
//如:给表加别名,方便where判断,且可读性强
SELECT
b.`model_id`,a.id
FROM
`test_sht` a ,`test_sht1` b
where
a.id=b.id
11.内连接之等值连接:最大特点:条件是等量关系
sql92(太老):
SELECT
b.`model_id`,
a.id
FROM
`test_sht` a,
`test_sht1` b
WHERE
a.id = 5 AND a.id = b.id
sql99(常用):
//inner可以省略,带着可读性好一点
SELECT
b.`model_id`,
a.id
FROM
`test_sht` a
inner JOIN
`test_sht1` b
ON
a.id = b.id
WHERE
a.id = 5
//内容一样,只是99语法更加清晰,join on内容是对于表的处理,where是进行接下来的过滤
//结果:2列,1列model_id,1列id,但注意如果2张表筛选后得到记录条数不同,则会出现重复的情况
内连接之非等值连接:最大特点:条件是非等量关系,比如将上面on后面的内容加个between a and b ,则是非等值连接
12.连接
自连接:最大特点是:一张表看做两张表,自己连接自己
内连接:假设a和b表进行连接,使用内连接的话,凡是a表和b表能够匹配上的记录查询出来,这就是内连接,如果没有匹配的,则不显示。ab两张表没主副之分,两张表平等
外连接:假设a和b表进行连接,使用外连接的话,ab两张表中有一张表是主表,一张表是副表,主要查询主表中的数据,捎带着查询副表,当副表中的数据没有和主表的数据进行匹配上,副表自动模拟出null与之匹配。
外连接分类?
左外连接(左连接):表示左边的这张表是主表
右外连接(右连接):表示右边的这张表是主表
外连接最重要的特点是:
主表的数据无条件的全部查询出来
//right join则表示右连接,left join左连接
//outer可以省略
SELECT
b.`model_id`,
a.id
FROM
`test_sht` a
RIGHT outer JOIN
`test_sht1` b
ON
a.id = b.id
13.三张表以上连接
//a表先和b连接,连接后得出结果,a表在和c表连接
SELECT
b.`model_id`,
a.id,
c.id
FROM
`test_sht` a
JOIN
`test_sht1` b
ON
a.id = b.id
RIGHT JOIN
`test_sht2` c
ON
a.id = c.id
where
a.id=3
14.union--可以将查询结果集相加
//注意:
//union是将结果进行拼接到同一列,
//其次,2个查询的列数必须相同,即前面查1列,后面也查1列
SELECT id FROM test_sht UNION SELECT id FROM test_sht1
15.limit--分页查询需要
limit是mysql特有的,其他数据库中没有,不通用(oracle 中有一个相同的机制,叫做rownum);
limit取结果集中的部分数据,不过注意是全部数据查出来之后再取,因此数据多的话即使limit依旧会存在超内存的情况
语法机制:
limit startIndex,length
startlndex 表示起始位置,从0开始
length表示取几个
select 5
from 1
where 2
group by 3
having 4
order by 6
limit 7
SELECT id FROM test_sht LIMIT 1,1
16.创建表--约束
建表语句的语法格式
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
。。。
)
CREATE TABLE test_sht3( id int, num int )
//default 当数据inset时候如果为空,则id列默认为1
CREATE TABLE test_sht3(id INT DEFAULT 1, num INT)
16.1加非空约束
CREATE TABLE test_sht10(id INT NOT null DEFAULT 1, num INT)
16.2加唯一约束
//一个unique
CREATE TABLE test_sht10(id INT UNIQUE , num INT)
此时如果
INSERT INTO `test_sht10`(`id`, `num`) VALUES(10, 10)
INSERT INTO `test_sht10`(`id`, `num`) VALUES(10, 10)
会报错,因为id具有唯一性
//多个unique
//单个字段一个个添加约束unique,称为:列级约束
CREATE TABLE test_sht10(id int UNIQUE,num int UNIQUE)
此时是增加2个unique约束,id或者num任何一个出现重复就会报错
//多个字段联合起来添加1个约束unique,称为:表级约束
CREATE TABLE test_sht10 (id int ,num int ,UNIQUE(id,num))
此时是增加1个unique约束,在id和num同时出现重复时候才会报错,只是一个重复不会报错
16.3加主键约束
CREATE TABLE test_sht10 (id int PRIMARY key ,num int )//列级约束
CREATE TABLE test_sht11(id INT , num INT,PRIMARY KEY(id))//表级约束
INSERT INTO `test_sht10`(`num`) VALUES(9)//报错
此时如果插入数据,如果id没有插入数据,或者id是null的话,会报错
主键相关术语:
以
CREATE TABLE test_sht10 (id int PRIMARY key ,num int )
INSERT INTO `test_sht10`(`id`, `num`) VALUES(1, 9)为例
主键约束--primary key
主键字段--id
主键值--1
主键的作用?
表的设计三范式中有要求,第一范式就要求任何一张表都应该有主键,且一张表的主键约束只有一个。
主键的作用是:主键值是这行记录在这张表当中的唯一标识(就像每个人用不同身份证号来区别人一样)
主键的分类?
根据主键字段的字段数量来划分:
单一主键(推荐使用,常用的)
复合主键(多个字段联合起来添加的一个主键约束)(复合主键不建议使用,因为复合主键违背三范式)如:CREATE TABLE test_sht11(id INT , num INT,PRIMARY KEY(id,num)) 则只有一个主键,需要id和num都不可以重复,且都不可以null,如果其中有个重复是可以的
根据主键性质来划分:
自然主键:主键值最好就是一个和业务没有任何关系的自然数
业务主键:主键值和系统的业务挂钩,例如:拿银行卡的卡号做主键,拿着身份证号码作为主键 (不推荐用),最好不要拿着和业务挂钩的字段最为主键,因为以后的业务一旦发生改变的时候,主键值可能也需要随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键值重复
mysql提供主键值自增:(非常重要)
CREATE TABLE test_sht10 (id int PRIMARY key AUTO_INCREMENT,num int )//id字段自动维护一个自增的数字,从1开始,以1递增
提示:Oracle当中也提供了一个自增机制,叫做:序列(sequence)
16.4加外键约束
关于外键约束的相关术语 :
外键约束:foreign key
外键字段:添加有外键约束的字段
外键值:外键字段中的每一个值
CREATE TABLE test_sht10 (id int PRIMARY key AUTO_INCREMENT,num int );
CREATE TABLE test_sht11(id int PRIMARY KEY ,good int ,FOREIGN KEY(good) REFERENCES test_sht10(id))
首先创建test_sht10--父表,其次创建test_sht11--字表,然后添加数据也是先添加父表的,如果子表中good的值不在父表中id的值,则会报错。
注意:外键使用2张表存储引擎都要是INNODB,默认的MyISAM不支持外键,但MyISAM引擎速度比INNODB快。因此:
CREATE TABLE test_sht10 (id int PRIMARY key AUTO_INCREMENT,num int )ENGINE INNODB;
CREATE TABLE test_sht11(id int PRIMARY KEY ,good int ,FOREIGN KEY(good) REFERENCES test_sht10(id))ENGINE INNODB
外键值可以是null?
可以为null
外键字段引用其他表的某个字段的时候,被引用的字段必须是主键吗?
不一定必须是主键,但至少具有unique约束,即:
CREATE TABLE test_sht10 (id int PRIMARY key AUTO_INCREMENT,num int UNIQUE )ENGINE INNODB;
CREATE TABLE test_sht11(id int PRIMARY KEY ,good int ,FOREIGN KEY(good) REFERENCES test_sht10(num))ENGINE INNODB
16.5
数据类型常用:
bigint 长整型
int 整数型
float 浮点型
char 定长字符串--实际开发中,当某个字段中的数据长度不发生变化时候,是定长的,比如:性别、生日等,都采用char
varchar 可变字长字符串--当一个字段的数据长度不确定,如:简介、姓名都采用varchar
data 日期类型
blob 二进制大对象(存储图片,视频等流媒体信息)
clob 字符大对象(存储较大文本,比如,可以存储4g的字符串)
约束
非空约束(not null):约束字段不能为null
唯一约束(unique):约束字段不能重复,但可以为null
主键约束(primary key):约束的字段既不能为null,也不能重复(简称pk)
外键约束(foreign key):(简称fk)
检查约束(check):注意Oracle数据库有check约束,但是mysql没有,目前mysql不支持
17.删除表
DROP TABLE IF EXISTS test_sht3
18.insert 插入数据
语法格式:
insert into 表名(字段1,字段2,。。。) values (值1,值2,。。。)
要求:字段的数量和值的数量相同,并且数据类型要对应相同
如果
insert into 表名 values (值1,值2,。。。)
则要求值和字段一一对应,且值的数量和字段的数量相同
一次插入多行数据
INSERT INTO `test_sht3`(`id`, `num`) VALUES(1,2),(3,3)
19.表的复制
语法:
create table 表名 as select 语句
将查询结果当做表创建出来
//将test_sh3的id,num列全部复制到test_sht4中,且创建表test_sh4
CREATE TABLE test_sht4 as SELECT id,num FROM test_sht3
20.将查询结果插入到一张表中
//将test_sh4中id列数据插入到test_sht5中id列
INSERT INTO `test_sht5`(`id`) SELECT id FROM `test_sht4`
21.修改数据:update
语法格式:
update 表名 set 字段名1= 值1,字段名2=值2,... where 条件
UPDATE `test_sht1` SET `id` = 7, `model_id` = 55 WHERE `id`=1
22.删除数据:delete
语法格式:
delete from 表名 where 条件//delete删除数据可恢复,可回滚,但如果数据大的话比较慢
DELETE FROM `test_sht1` WHERE `id`=2
怎么删除大表?
TRUNCATE TABLE test_sht5//表被截断,不可回滚,永久丢失,需要谨慎
23.常见存储引擎
MyISAM:最常用的引擎
它管理的表有以下特征:使用三个文件来表示每个表
格式文件–存储表结构的定义( .frm)
数据文件–存储表行的内容(.MYD)
索引文件–存储表上索引(***.MYI)
优点:可被压缩,节省存储空间,并且可以转换为只读表,提高检索效率
缺点:不支持事务
InnoDB:
优点:支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障
表的结构存储在***.frm文件中
数据存储在tablespace这样的的表空间中(逻辑概念),无法被压缩,无法转换为只读
在MySQL服务器崩溃后提供自动恢复的功能
支持级联删除和级联更新
MEMORY:
缺点:不支持事务,数据容易丢失,因为所有数据和索引都是存储在内存当中的。
优点:查询速度最快
以前叫做HEPA引擎
24.事务
要想保证2条DML语句同时成功或者失败,那么就需要使用数据库的“事务机制”
和事务相关的语句只有:DML语句(insert、delete、update)
为什么?因为他们这三个语句都是和数据库表当中的“数据”相关的,事务的存在是为了保存数据的完整性、安全性。
假设所有的业务都能使用1条DML语句搞定,还需要事务机制吗?
不需要事务。但实际情况不是这样的,通常一个"事情(事务)”需要多条DML语句共同联合完成。
事务的特性?
事务包括四大特性:
原子性:事务是最小的工作单元,不可再分
一致性:事务必须保证多条DML语句同时成功或者同时失败
隔离性:事务和事务之间是隔离的
持久性:最终数据必须持久化到硬盘文件中,事务才算成功的结束
关于事务之间的隔离性?
事务隔离性存在隔离级别,理论上隔离级别包括4个:
第一级别:读未提交(read uncommitted)–演示添加链接描述
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据;
读未提交存在脏读(Dirty Read)现象:表示读到了脏数据,即数据不稳定,一断电就没有了。
第二级别:读已提交(read committed)–演示添加链接描述
对方事务提交之后的数据我方可以读取到;
读已提交存在的问题是:不可重复读,即对方一提交就读到新的,无法做到数据一直是相同的。
但解决了:脏读现象。
第三级别:可重复读(repeatable read)–演示:添加链接描述
这种隔离级别解决了:不可重复读的问题。
但存在的问题是:读取到的数据是幻象的,只要对方提交过,即使对方已经删除或者增加,读取到的数据依旧还是原数据,类似于读取备份数据。
第四级别:序列化读/串行化读(serializable)–演示:添加链接描述
解决了所有的问题;
效率低,需要事务排队
oracle数据库默认的隔离级别是:读已提交
mysql数据库默认的隔离级别是:可重复读
mysql事务默认情况下是自动提交的
自动提交?即只要执行任意一条DML语句则提交一次。
如何关闭自动提交?用 start transaction
mysql数据库操作流程:
表示当正常执行DML时候,会自动提交,此时如果回滚,即输入 rollback ,没有任何用处,表中数据还是一样的,因为数据已经自动提交到了内存里面;
当输入start transaction后,此时会关闭自动提交,事务机制开启,假设此时的数据是a,在执行DML后,这时候如果输出,数据是DML后的数据b,如果此时回滚,即输入rollback,然后输出,数据为a,且事务机制结束。因为数据还没有提交内存里面去;
当输入start transaction后,此时会关闭自动提交,事务机制开启,假设此时的数据是a,在执行DML后,这时候如果输出,数据是DML后的数据b,如果此时commit,即数据提交,此时输出数据,则会是b。如果此时回滚,即输入rollback,则输出的数据为b,因为数据已经提交了,而回滚只能回滚到上一次的数据提交点。
25.索引
什么是索引?有什么用?
索引就相当于一本书的目录,通过目录可以快速的找到对应的资源。
在数据库方面,查询一张表的时候有2中检索方式:全表扫描,根据索引检索(效率很高)
索引为什么可以提高检索效率?
其实最根本的原理是缩小了扫描的范围
索引虽然可以提高检索效率,但是不可以随意的添加索引,因为索引也是数据库当中的对象,也需要数据库不断的维护,是有维护成本的。比如,表中的数据经常被修改这样就不合适添加索引,因为数据一旦修改,索引需要重新排序,进行维护
添加索引是给某一个字段,或者说某些字段添加索引
SELECT `id`, `data5`, `deal_num` FROM `test_sht` WHERE `data5`=5
当data5字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描data5字段中所有的值,而当添加索引的时候,以上sql语句会根据索引扫描,快速定位
什么时候考虑给字段添加索引?(满足什么条件)
数据量庞大(根据客户的需求,满足线上的环境)
该字段很少的DML操作(因为字段进行修改操作,索引也需要维护)
该字段经常出现在where子句中(经常根据哪个字段查询)
注意:主键和具有unique约束的字段会自动添加索引,所以根据主键查询效率较高,尽量根据主键检索。
增加索引:
create index 索引名字 on 需要添加索引的表名(添加索引的字段名)
create index emp_sal_index on emp(sal)
删除索引对象
drop index 索引名字 on 需要添加索引的表名
drop index emp_sal_index on emp
索引底层采用的数据结果是:B+Tree
索引实现的原理?
通过B Tree缩小扫描范围,底层索引进行了排序、分区,索引会携带数据在表中的“物理地址”,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率是最高的。
索引分类:
单一索引:给单个字段添加索引
复合索引:给多个字段联合起来添加1个索引
主键索引:主键上会自动添加索引
唯一索引:有unique约束的字段会自动添加索引
索引什么时候失效?
模糊查询的时候,第一个通配符使用的是
26.视图
什么是视图?
站在不同的角度去看到数据(同一张表的数据,通过不同的角度去看待)
怎么创建视图?怎么删除视图?
create view 视图名 as DQL语句,即select语句
drop view 视图名
对视图进行增删改查,会影响到原表数据,(通过视图影响原表数据的,不是直接操作的原表),可以对视图进行CRUD操作,但是创建视图必须用DQL语句,即select语句
视图的作用?
视图可以隐藏表的实现 细节,保密级别较高的系统,数据库只对外提供相关的视图,java程序员只对视图对象进行CRUD
27.DBA命令
数据库管理员(Database Administrator,简称DBA)
将数据库当中的数据导出
在windows的dos命令窗口中执行:(导出整个库)
mysqldump 数据库名> 输出到这个sql脚本文件的路径,其实就是保存路径+取名(假设为D:\a.sql) -u用户名 -p密码
在windows的dos命令窗口中执行:(导出指定数据库中的指定表)
mysqldump 数据库名 表名> 输出到这个sql脚本文件的路径,其实就是保存路径+取名(假设为D:\a.sql) -u用户名 -p密码
导入数据
1.create database 数据库名
2.use 数据库名
3.source 脚本文件(D
28.数据库设计三范式--重点内容,面试经常问
什么是设计范式?
设计表的依据,按照这个三范式设计的表不会出现数据冗余
提醒:在实际开发中,以满足客户的需求为主,有的时候会拿冗余换执行速度
三范式内容:
第一范式:
任何一张表都应该有主键,并且每一个字段原子性不可再分
如:
图中没有主键,其次联系方式可以再分,因此不符合第一范式
修改后:
注:原子性:指事务的不可分割性,一个事务的所有操作要么不间断地全部被执行,要么一个也没有执行。
第二范式:建立在第一范式的基础上,所有非主键字段完全依靠主键,不能产生部分依赖
如:
因此不满足第二范式,当然最好不要用复合主键。解决方案:
第三范式:建立在第二范式的基础之上,所有非主键字段直接依赖主键,不能产生传递依赖
如:
不满足第三范式,解决办法:
一对多,多对多表格设计在上面。
一对一表格设计,2种方案:
外键唯一、主键共享
外键唯一:
主键共享:
完–hello world
视频参考:
添加链接描述