一.基础概念
数据库、数据库管理系统、SQL
主流数据库:
mysql的安装:略
mysql图形化界面的安装:略
二.数据模型
1). 关系型数据库(RDBMS)
概念:建立在关系模型基础上,由多张相互连接的二维表组成的数据库。 而所谓二维表,指的是由行和列组成的表,就类似于Excel表格数据,有表头、有列、有行, 还可以通过一列关联另外一个表格中的某一列数据。我们之前提到的MySQL、Oracle、DB2、 SQLServer这些都是属于关系型数据库,里面都是基于二维表存储数据的。
简单说,基于二维表存储数据的数据库就成为关系型数据库,不是基于二维表存储数据的数据库,就是非关系型数据库。
2). 数据模型 MySQL是关系型数据库,是基于二维表进行数据存储的,具体的结构图下:
我们可以通过MySQL客户端连接数据库管理系统DBMS,然后通过DBMS操作数据库。
可以使用SQL语句,通过数据库管理系统操作数据库,以及操作数据库中的表结构及数据。
一个数据库服务器中可以创建多个数据库,一个数据库中也可以包含多张表,而一张表中又可以包含多行记录。
三.SQL
全称 Structured Query Language,结构化查询语言。操作关系型数据库的编程语言,定义了 一套操作关系型数据库统一标准。
通用语法:【1】SQL语句可以单行或多行书写,以分号结尾
【2】不区分大小写,关键字建议使用大写
【3】注释:
单行注释:-- 注释内容 或 # 注释内容
多行注释:/* 注释内容 */
SQL语句,根据其功能,主要分为四类:DDL、DML、DQL、DCL。
1.DDL
Data Definition Language,数据定义语言,用来定义数据库对象(数据库,表,字段) 。
【1】对数据库的增删查
#增
create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ] ;#创建数据库
create schema ...#创建数据库的另一种写法
#删
drop database [ if exists ] 数据库名 ;#删除数据库
#查
show databases ;#查询所有数据库
use 数据库名 ;#use 数据库名 ;
select database() ;#查询当前数据库
【2】对表的增删改查
#查
show tables;#查询当前数据库所有表
desc 表名 ;#查看指定表结构
show create table 表名 ;#查询指定表的建表语句
#增[创建表]
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;
#注意: [...] 内为可选参数,最后一个字段后面没有逗号
#改
ALTER TABLE 表名 RENAME TO 新表名;##修改表名
#删
DROP TABLE [ IF EXISTS ] 表名;#删除表;注意: 在删除表的时候,表中的全部数据也都会被删除。
TRUNCATE TABLE 表名;#删除指定表, 并重新创建表
【3】对字段的增删改查
##对字段名的增
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];#添加字段
#改
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);#修改数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];#修改字段名和字段类型
#删
ALTER TABLE 表名 DROP 字段名;#删除字段
数据类型:数值类型,字符串类型,日期时间类型
1.数值类型
2.字符串类型
3.日期时间类型
2.DML--对表中数据的增删改
DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增、删、改操作。
添加数据(INSERT) 修改数据(UPDATE) 删除数据(DELETE)
#增
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);#给指定字段添加数据
INSERT INTO 表名 VALUES (值1, 值2, ...);#给全部字段添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;#批量添加数据
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;#批量添加数据
/*
注:字符串和日期型数据应该包含在引号中。插入的数据类型和大小要符合字段规定。
*/
#删
DELETE FROM 表名 [ WHERE 条件 ] ;
#改
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;
3.DQL--对表中数据的查询
DQL英文全称是Data Query Language(数据查询语言),数据查询语言,用来查询数据库中表的记 录。
查询关键字: SELECT
1.基本语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
2.语法
#基本查询
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;#查询多个字段
SELECT * FROM 表名 ;#查询所有专业,开发中尽量少用,因为无法做到覆盖索引,影响效率
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;#字段设置别名
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;#字段设置别名
SELECT DISTINCT 字段列表 FROM 表名;#去除重复记录
#条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
#聚合函数查询
SELECT 聚合函数(字段列表) FROM 表名 ;
/*
注意 : NULL值是不参与所有聚合函数运算的。
*/
#分组查询[分为不同组进行查询]
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];
/*
where与having区别
执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组
之后对结果进行过滤。
判断条件不同:where不能对聚合函数进行判断,而having可以。
注意事项:
• 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
• 执行顺序: where > 聚合函数 > having 。
• 支持多字段分组, 具体语法为 : group by columnA,columnB
*/
#排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
/*
排序方式
ASC : 升序(默认值)
DESC: 降序
注意事项:
• 如果是升序, 可以不指定排序方式ASC ;
• 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序 ;
*/
#分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
/*
注意事项:
• 起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
• 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
• 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
*/
运算符大全:
3.执行顺序
4.DCL--对用户和用户访问权限的增删改查
【1】对用户的增删改查
#创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
#删除用户
DROP USER '用户名'@'主机名' ;
#修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;
#查询用户
select * from mysql.user;
/*
注意事项:
• 在MySQL中需要通过用户名@主机名的方式,来唯一标识一个用户。
• 主机名可以使用 % 通配。
• 这类SQL开发人员操作的比较少,主要是DBA( Database Administrator 数据库管理员)使用。
*/
【2】对用户权限的增删查
MySQL中定义了很多种权限,但是常用的就以下几种:
#查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;
#授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
#撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
/*
注意事项:
• 多个权限之间,使用逗号分隔
• 授权时, 数据库名和表名可以使用 * 进行通配,代表所有。
*/
四.函数
MySQL中的函数主要分为以下四类: 字符串函数、数值函数、日期函数【处理字符串,数值,日期】
流程函数【一些判断功能】。
五.约束--规定表中字段的规则
1.概述
概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
目的:保证数据库中数据的正确、有效性和完整性。
分类:
2.外键约束
其中主要讲讲外键约束。
外键:用来让两张表的数据之间建立连接,形成关系。可以想象成指针,由一张表指向另一张表。
但是目前上述两张表,只是在逻辑上存在这样一层关系;在数据库层面,并未建立外键关联。所以需要外键约束来在数据库层面建立外键。
并且我们还可以增加外键规则来保证数据的一致性和完整性的。
外键的增加和删除语句:
#增加外键
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)
REFERENCES 主表 (主表列名) ;
例:alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);
#删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
例:alter table emp drop foreign key fk_emp_dept_id;
规定删除和更新的数据一致性:
具体语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES
主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
例:
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;
六.多表联查
1.关系型数据库的三种关系
因为是关系型数据库,表和表之间通过外键连接存在关系,可以分为3种。
【1】一对多
案例: 部门 与 员工的关系
关系: 一个部门对应多个员工,一个员工对应一个部门
实现: 在多的一方建立外键,指向一的一方的主键
【2】多对多
案例: 学生 与 课程的关系
关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
【3】一对一
案例: 用户 与 用户详情的关系
关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另 一张表中,以提升操作效率
实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
2.多表查询概述
多表查询就是指从多张表中查询关联行的数据。
连接查询分类
内连接:相当于查询A、B交集部分数据
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据
右外连接:查询右表所有数据,以及两张表交集部分数据
自连接:当前表与自身的连接查询,自连接必须使用表别名
3.内连接
内连接的语法分为两种: 隐式内连接、显式内连接。先来学习一下具体的语法结构。
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;#隐式内连接
例:select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
-- 为每一张表起别名,简化SQL编写
select e.name,d.name from emp e , dept d where e.dept_id = d.id;
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;#显式内连接
例:select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
-- 为每一张表起别名,简化SQL编写
select e.name, d.name from emp e join dept d on e.dept_id = d.id;
4.外连接
外连接分为两种,分别是:左外连接 和 右外连接。具体的语法结构为:
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;#左外连接
例:select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;#右外连接
例:select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
注意事项: 左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺 序就可以了。而我们在日常开发使用时,更偏向于左外连接。
5.自连接
自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。我们先来学习一下自连接 的查询语法:
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
例:select a.name , b.name from emp a , emp b where a.managerid = b.id;
注意事项: 在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底 是哪一张表的字段。
6.联合查询
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
例:
select * from emp where salary < 5000
union all
select * from emp where age > 50;
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。 union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
七.子查询
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
例:
select * from emp where entrydate > (select entrydate from emp where name = '方东白');
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
根据子查询结果不同,分为:
A. 标量子查询(子查询结果为单个值)
B. 列子查询(子查询结果为一列)
C. 行子查询(子查询结果为一行)
D. 表子查询(子查询结果为多行多列)
根据子查询位置,分为: A. WHERE之后 B. FROM之后 C. SELECT之后
八.事务
1.概述
事务简单说将一串操作看成一个整体,只有当这串操作全部做对时才成功,只有有一个环节出错就失败。这一串操作就是事务。
比如张三给李四转账100块这件事有三个步骤,
查询张三余额,张三自己账户先扣一百块,然后李四账户获得一百块。
如果张三扣款成功,但李四获得一百块失败,那么不是转账失败,凭空损失了100块?或者张三扣款失败,但李四获得一百块成功,那张三不是凭空赚了100块?
然后我们将这两个事情打包,规定有一件事情做错就代表失败并回滚到最初的状态,就保证了数据的一致性,解决了上面的问题。
注意: 默认MySQL的事务是自动提交的,也就是说,当执行完一条DML语句时,MySQL会立即隐 式的提交事务。简单说其实一条DML也有事务。
2.案例
-- 开启事务
start transaction
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;
3.事务四大特性【ACID】
原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
上述就是事务的四大特性,简称ACID。
4.事务并发问题
1)赃读:一个事务读到另外一个事务还没有提交的数据。
2)不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。
3)幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据 已经存在,好像出现了 "幻影"。
解决方案:事务隔离级别
SELECT @@TRANSACTION_ISOLATION;#查看事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }#设置事务隔离级别
注意:事务隔离级别越高,数据越安全,但是性能越低。