赞
踩
三者之间的关系:
DBMS负责执行SQL,通过执行sql语句来操作数据库(DB)中的数据
DBMS ==( 执行 )
==> SQL ==( 操作 )
==> DB
table是数据库的基本组成单元,是一种结构化的文件,所有的数据都以表格的形式存储,目的是可读性强。
一个表包括行和列:
以 .sql 为扩展名的文件,被称之为“sql脚本”,给文件中编写了大量SQL语句。
可以直接使用source命令执行该SQL脚本。
注意:当SQL脚本太大时,无法直接打开,请使用source命令进行数据初始化。
mysql> source 脚本文件地址
运算符 | 说明 |
---|---|
<> 或 != | 不等于 |
between···and··· | 两值之间,等同于 >= and <= |
注意:数据库中的null不是一个值,代表什么也没有,为空(空不是一个值,不能用等号衡量。
需要用 is null 或 is not null
)
特点:输入对行,最终输出结果为一行
函数 | 描述 |
---|---|
count() | 计数 |
sun() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
注意:
1、所有的分组函数都是对一组数据进行的操作
2、分组函数常与 group by 连用,并且都会在 group by 执行完毕之后才会被执行(当一条SQL语句中无 group by 时,整张表数据会自成一组
)
3、分组函数自动忽略NULL值
4、只要有NULL参与的运算结果一定是NULL
5、分组函数不能出现在 where 之后
特点:输入一行,输出结果也为一行
例如:ifnull()等函数
group by:按照某个字段或是某些字段进行分组
--查询 每个年龄的平均薪资
select age,avg(sal) from users group by age;
having:对分组之后的数据进行再次过滤( 只能在group by后使用
)
--查询 平均薪资大于2000的年龄
select age,avg(sal) from users group by age having avg(sal)>2000;
当一条语句中出现了 group by ,那么 select 后只能跟分组字段以及分组函数
概念:对查询结果去重
注意:1、distinct只能在查询字段的最前方,并且以distinct后的所有字段进行联合去重
select <select_list>
from <table>
join <join_table>
on <join_condition>
where <where_condition>
group by <groupby_list>
having <having_condition>
order by <order_condition>
limit <limit_number>
执行顺序:
连接查询的分类:
类型 | 连接方式 |
---|---|
内连接 | 等值连接、非等值连接、自链接 |
外连接 | 左外连接(左连接)、右外连接(右连接) |
全连接 | … |
在表连接查询方面有一种现象被称为:笛卡尔积现象
当两张表连接查询时,在没有任何条件限制下,最终的结果条数是两张表条数的乘积
如何避免出现笛卡尔积现象?
答案:给连接查询添加条件限制
思考:添加限制条件,避免了笛卡尔积现象,但还是不会减少记录的匹配次数,只不过显示的是有效数据
图示:
-- inner 可省略
···
A
inner join
B
on A.id=B.aId
···
-- inner 可省略
···
A
inner join
B
on A.sale between B.l and B.h
···
-- inner 可省略
···
A
inner join
B
on A.pid= B.id
···
内连接:AB表进行内连接,但凡能匹配上的记录都将被查询,AB表没有主副之分。
外连接:AB表进行外连接,AB表中一张为主表,另一张为副表。主要查询主表数据,顺带查询副表数据,当副表数据与主表数据未能匹配上时,附表主动模拟出NULL与之匹配。
特点:主表数据将会无条件被全部查询出来
左连接:以左边表为主表
右连接:以右边表为主表
左连接
-- outer 可省略
···
A
left outer join
B
on A.id= B.aId
···
右连接
-- outer 可省略
···
A
right outer join
B
on A.id= B.aId
···
将user查询结果创建为一张新表
create table xxx as select * from user
将user查询结果插入xxx 表中
INSERT INTO xxx SELECT u.name.u.password FROM users u;
delete FROM users;
delete删除大数据表时,效率会比较低;因为delete删除不会释放存储空间,并且可以进行数据的回滚
truncate table users;
delete删表时,表会被截断,会释放存储空间,数据不可以回滚,数据永久丢失。
为表添加约束的目的:保证表中数据的合法性、完整性、有效性。
常见约束:
约束 | 描述 |
---|---|
非空约束(not null) | 约束的字段不为NULL |
唯一约束(unique) | 约束的字段不能重复 |
主键约束(primary key) | 约束的字段不能为NULL,也不能重复(简称:PK )。创建主键约束时,系统默认会在所在的列和列组合上建立对应的唯一索引 |
外键约束(foreign key) | … (简称:FK ) |
检查约束(check) | oracle数据库右检查约束,MySQL数据库无检查约束 |
主键值是这行记录在整张表中的唯一标识(人的身份证号码一样)
主键是第一范式要求的一部分,一张表只能有一个主键
根据主键字段数量划分:
1、单一主键:主键只包含一个字段(常用)
2、复合主键:主键包含多个字段(不建议使用,违背第二范式
)
根据主键性质划分:
1、自然主键:主键是一个与业务无任何关系的值
2、业务主键:主键值与业务挂钩,不推荐使用
(例如:银行卡号、身份证号)
--语法1:
--在 create table 语句中,通过 PRIMARY KEY 关键字来指定主键。
--在定义字段的同时指定主键,语法格式如下:
create table 表名(
...
<字段名> <数据类型> primary key
...
)
--语法2:
--修改表结构添加主键,语法格式如下:
alter table <表名> add primary key(字段列表,逗号隔开);
--主键删除
alter table <表名> drop primary key;
外键是表的一个字段,不是本表的主键,是对应另一个表的主键(或是具有唯一约束(unique)的列);并且可以为NULL。
定义外键后,不允许删除另一个表中具有关联关系的行。
外键的主要作用是保持数据的一致性、完整性
删除数据,先删除子表,再删除父表
添加数据,先添加父表,再添加子表
创建表时,先创建父表,在创建子表
删除表时,先删除子表,再删除父表
--语法1:
CREATE TABLE tb (
id INT(11) PRIMARY KEY,
...
...
CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名>(<主表主键列>)
);
--语法2:
ALTER TABLE <表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<主表主键列名>);
--外键删除
alter table <表名> drop foreign key <外键约束名>;
唯一约束修饰的字段具有唯一性,不可以重复,但可以为NULL
--语法1 :
Create table <表名> (
id int unique,//列级约束
name varchar (20)
);
--语法2 :
Create table <表名> (
id int,
name varchar (20)
unique (name)//表级约束
);
--语法3 :
alter table <表名> add unique(<列名列表,逗号隔开>);
常见搜索引擎:MyISAM、InnoDB
MyISAM存储引擎是MySQL最常用的引擎,但其不支持事务
。
InnoDB 存储引擎是MySQL的缺省引擎,其支持事务、行级锁、外键等。使数据安全得到了保障
。
一个事务就是一个完整的业务逻辑单元,不可再拆分。
保证多天DML语句必须同时执行成功,或是同时执行失败,不允许出现一条成功,一条失败。
与事务有关的语句只有DML语句(insert、update、delete),因为这三个语句都是与数据库数据相关的操作语句。事务就是为了保证数据的完整性,安全性。
一个业务需要先执行一条insert,再执行一条update,然后再执行一条delete,才算是完成.
开启事务后,所有执行的语句都不会直接修改磁盘上的数据,会记录在数据库操作历史中。
当事务提交后,数据才会持久化到磁盘上,并且清空操作历史。
当事务回滚后,数据不会同步持久到磁盘上,并且清空操作历史。
事务包含四大特性:ACID
A、原子性:事务是最小的工作单元,不可再拆分
C、一致性:事务必须保证多条DML语句同时成功或同时失败。
I、隔离性:事务之间存在隔离
D、持久性:数据最终必须持久化到磁盘中,事务才算成功结束。
事务隔离性存在隔离级别,理论上存在4种隔离级别
对方未提交的数据,当前事务也可以读取到数据。
读未提交存在脏读(Dirty Read)现象:表示读到了脏的数据。
对方提交后的数据,当前事务可以读取到。
这种隔离级别解决了:脏读现象
这种隔离级别存在的问题:不可重复读
这种隔离级别解决了:不可重复读问题
这种隔离级别存在的问题:读到的数据是幻象
可重复读是指第一次读取的是实时的数据,往后读取的是第一次的快照。
这种隔离级别解决了所有问题
这种隔离级别存在的问题:效率低,需要事务排队
Oracle默认隔离级别:读已提交
MySQL默认隔离级别:可重复读
查看全局隔离级别:
SELECT @@transaction_isolation;
设置全局隔离级别:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
设置事务隔离级别,需要退出MySQL再重新登录才能看到隔离级别的变化
所以修改显示成功了,但是你在查没有变化时,需要断开连接新连接才能看到变化
MySQL事务默认为自动提交(执行一次DML语句就提交一次)
关闭事务自动提交:
start transaction;
事务提交:
commit;
事务保存点:
savepoint xxx
事务回滚:
--回滚全部
rollback;
--回滚至保存点
rollback xxx;
定义:
索引是帮助MySQL高效快速获取数据的一种数据结构(索引是数据结构)
目的:
提高数据查询效率。
索引相当于一本书的目录,通过目录可以快速访问到对应的资源。
在数据库当面,查询一条数据有两种检索方式:全表扫描
、索引检索 (效率高( 缩小了扫描范围 ))
思考:索引可以提高检索效率,那索引可以任意添加吗?
不能随意添加索引,因为索引是数据库中的对象,也需要数据库不断的维护,存在维护成本
当表中索引字段的数据被修改时,索引就需要重新排序,进行维护。
--创建索引
--方式一
create index 索引名称 on 表名(字段名);
--方式二:
alter table 表名 ADD INDEX 索引名称(字段名);
--删除索引
drop index 索引名称 on 表名;
索引底层数据结构为:B + Tree
索引通过b+tree缩小了扫描范围,底层索引会进行分区、排序,索引会携带数据在磁盘中的 “物理地址”。通过索引获取到关联的物理地址,通过物理地址定位数据。
例:为user表添加 idCard 索引,并查询数据:
原始SQL:
select * from user where idCard=‘1234567890’:
通过索引转换为:(idCard='1234567890’的物理地址为 0x3)
select * from user where 物理地址= 0x3
图文解析:
索引失效,七字口诀:模型数空运最快
模
:模糊查询,like的模糊查询以%开头,索引失效SELECT * FROM users WHERE name LIKE '%三';
型
:数据类型错误,索引也会失效。如字段类型为varchar,where条件用number-- height为varchar类型导致索引失效。
SELECT * FROM users WHERE height = 180;
数
:对索引的字段使用内部函数,索引会失效。这种情况下应该建立基于函数的索引。-- create_time字段设置索引,那就无法使用函数,否则索引失效。
SELECT * FROM users WHERE DATE(create_time) = '2020-09-03';
空
:是Null的意思。索引不存储空值,如果不限制索引列是not null,数据库会认为索引列有可能存在空值,所以不会按照索引进行计算。-- 不走索引
SELECT * FROM users WHERE address IS NULL;
-- 走索引
SELECT * FROM users WHERE address IS NOT NULL;
建议大家这设计字段的时候,如果没有必要的要求必须为NULL,那么最好给个默认值空字符串,这可以解决很多后续的麻烦。
运
:是运算的意思。对索引列进行(+,-,*,/,!, !=, <>)等运算,会导致索引失效。SELECT * FROM users WHERE age - 1 = 20;
最
:最左原则。在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找,则无法使用索引。快
:全表扫描更快的意思。如果数据库预计使用全表扫描要比使用索引快,则不使用索引。补充:查看SQL执行计划:explain
举例:explain select * from user
视图就是站在不同角度去看数据。
创建视图:
create view myview as select id,name,age from users;
删除视图:
drop view myview;
注意:
1、只有DQL语句才能以视图对象的方式创建出来
1、对视图进行crud会影响到原表数据;
视图可以隐藏表的实现细节,只展现出想要被外部看到的结构及数据。
举例:保密级别较高的系统,只对程序员提供视图,程序员只能通过视图对表数据进行CRUD
存储过程是一组为了完成特定功能的SQL语句集,它通过预编译后存储在数据库中,一次编译永久有效(后续调用,不在进行编译);存储过程是数据库中的一个重要对象。
mysql服务器端窗口创建存储过程
无参数存储过程
create procedure proc1()
as
begin
--sql语句集
select * from users
end
--调用存储过程
call proc1();
有入参数存储过程
create procedure proc1(in tel varchar(20),in sex varchar(20))
as
begin
select * from users where tel=@tel and sex=@sex
end
--调用存储过程
call proc1('15091774040','男');
有出参数存储过程
--在调用带有输出参数的存储过程之前,一定要首先定义一个变量,用来接收存储过程的输出结果
set @res = 0;
--注意两个关键点: 第一是变量名之前一定要加@符号,第二是变量名一定要赋初始值
create procedure proc1(in val int,out res int)
as
begin
select val+1 into res;
end
--调用存储过程
call proc1(11,@res);
--查询变量的值
select @res;
存储过程的参数列表
参数模式 | 参数名 | 参数类型 |
---|---|---|
in | sname | varchar(20) |
out | sid | varchar(20) |
注意:
1、存储过程关键字procedure 可以缩写为proc
2、存储过程参数在as之前,as之后的是局部变量,两种变量在存储过程中都可以使用,但命名不可以冲突。
3、begin与end中的sql语句末尾添加分号
触发器相当于一个触发装置,当满足了触发条件便会进行相应的操作。
触发器是一种特殊的存储过程,它不是由用户去触发,而是当发生某一事件后,由系统自动触发。
触发器是数据库的一种自动处理机制,主要作用是保证数据库数据的安全性。
触发器工作机制:
触发器是建在表上的,当这个表发生新增、修改、删除时,会自动调用此前设置好的触发器。在触发器执行过程中会创建一个临时表,这个临时表只有一行记录
当执行新增时,临时表名称为inserted
当执行删除时,临时表名称为deleted
当执行更新时,会产生两个临时表,一个是inserted(存放新的数据),一个是deleted(存放旧数据)
触发器分为 :
这二者的区别的就是当一条语句使得多条记录发生改变时,前者会触发多次触发器,而后者只会触发一次。
触发事件根据执行SQL的操作不同可以分为 INSERT ,UPDATE , DELETE三种,即在执行插入,更新和删除操作时会激活触发器。
注意:三种触发器的触发条件如下。
INSERT:向表中插入一条记录时会激活触发器,即insert语句。
DELETE:删除表中的记录时激活触发器,即delete语句。
UPDATE:更新表中的记录时激活触发器,即update语句。
触发器可以在语句被执行之前触发也可以在语句执行之后触发,即BEFORE 和 AFTER两种。
create trigger 触发器名称 on 表名 for insert|delete|update as begin --sql语句 end; CREATE trigger w_log #触发器的名称 AFTER INSERT #当执行插入语句之后激活触发器 ON tb_student #作用于那个表 FOR EACH ROW #触发器作用在每条记录上 # 触发器需要执行的操作 BEGIN set @t_name = new.sname; #当插入一条学生记录就向日志表插入一条记录此次操作 INSERT INTO tb_log(operation,oper_time) VALUES (concat("insert a row record:",@t_name),NOW()); END;
按照这三个范式设计的表不会出现数据冗余。
第一范式
任何一张表都应该有主键,并且每个字段原子性不可再分。
第二范式
建立在第一范式基础上,所有非主键字段完全依赖于主键,不能产生部分依赖。(复合索引可能会违反此范式
)
当出现多对多关系时,三张表,关系表两外键
student 学生表
sno(pk) | name |
---|---|
s1 | 张三 |
s2 | 李四 |
s3 | 五四 |
teacher 老师表
sno(pk) | name |
---|---|
t1 | 张三 |
t2 | 李四 |
t3 | 五四 |
学生讲师关系表
id(pk) | sno(fk) | tno(fk) |
---|---|---|
1 | s1 | t3 |
2 | s2 | t2 |
3 | s1 | t3 |
第三范式
建立在第二范式基础上,所有非主键字段直接依赖主键,不能产生依赖传递
当出现一对多关系时,两张表,多的表添加外键
class 班级表
cno(pk) | name |
---|---|
c1 | 班级1 |
c2 | 班级2 |
c3 | 班级3 |
student 学生表
sno(pk) | name | classno(fk) |
---|---|---|
s1 | 张三 | c1 |
s2 | 李四 | c2 |
s3 | 五四 | c1 |
提示:在开发中,以满足客户需求为主,有时会拿冗余换执行速度
(空间换效率
)
两种设计方案:
主键共享
商品表
id(pk) | name | money |
---|---|---|
g1 | 矿泉水 | 2$ |
g2 | 火腿 | 4$ |
g3 | 卤蛋 | 6$ |
商品明细表
id(pk+fk) | address | date |
---|---|---|
g1 | 山西 | 2022-11-23 |
g3 | 西安 | 2022-11-07 |
g2 | 杭州 | 2022-12-16 |
外键唯一
商品表延续使用1
商品明细表
id(pk) | address | date | goodid(fk+unique) |
---|---|---|---|
1 | 山西 | 2022-11-23 | g1 |
2 | 西安 | 2022-11-07 | g3 |
2 | 杭州 | 2022-12-16 | g2 |
1、导出数据:mysqldump 数据库名>文件地址 -u用户名 -p密码
mysqldump lean_database>c:\lean.sql -uroot -p123456
1、导入数据:sources 文件地址
sources c:\lean.sql
mysql数据库SQL语句不区分大小写,数据也不区分大小写
oracle数据库SQL语句不区分大小写,但数据区分大小写
oracle语法比较严格,但MySQL相对松散些
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。