数据库相关概念
数据库
存储数据的仓库,数据是有组织的进行存储
英文:DataBase,简称DB
数据库管理系统
管理数据库的大型软件
英文:DataBaseManagementSystem,简称DBMS
SQL
英文:StructuredQueryLanguage,简称SQL,结构化查询语言
操作关系型数据库的编程语言
定义操作所有关系型数据库的统一标准
MySQL数据库
下载
点开下面的链接:
https://downloads.mysql.com/archives/community/
配置环境变量
新建配置文件
新建一个文本文件,内容如下:
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
default-storage-engine=innodb
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
初始化MySQL
在文件夹cmd中输入以下命令,出现data文件证明成功
mysqld --initialize-insecure
tips:如果出现如下错误
是由于权限不足导致的,去c:\windows\System32下以管理员方式运行cmd.exe
注册MySQL服务
在黑框里敲入mysqld -install,回车。
现在你的计算机上已经安装好了MySQL服务了。
启动MySQL服务
在黑框里敲入net start mysql,回车。
net start mysql //启动mysql服务
net stop mysql //停止mysql服务
修改默认账户密码
在黑框里敲入mysqladmin -u root password 1234,这里的1234就是指默认管理员(即root账户)的密码,可以自行修改成你喜欢的。
mysqladmin -u root password 1234
登录MySQL
在黑框中输入,mysql-uroot-p1234,回车,出现下图且左下角为mysq1>,则登录成功。
mysql -uroot -p1234
登录参数:
mysq1-u用户名-p密码-h要连接的mysq1服务器的ip地址(默认127.0.0.1)-P端口号(默认3306)
退出mysql
exit
quit
MySQL数据模型
关系型数据库
关系型数据库是建立在关系模型基础上的数据库,简单说,关系型数据库是由多张能互相连接的二维表组成的数据库
优点
1.都是使用表结构,格式一致,易于维护。
2使用通用的SQL语言操作,使用方便,可用于复杂查询。
3.数据存储在磁盘中,安全。
SQL
SQL简介
英文:StructuredQueryLanguage,简称SQL
结构化查询语言,一门操作关系型数据库的编程语言
定义操作所有关系型数据库的统一标准
对于同一个需求,每一种数据库操作的方式可能会存在一些不一样的地方,我们称为“方言"
SQL通用语法
1.SQL语句可以单行或多行书写,以分号结尾。
2.MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
3.注释
SQL分类
DDL:操作数据库,表等
DML:对表中的数据进行增删改
DQL:对表中的数据进行查询
DCL:对数据库进行权限控制
DDL–操作数据库
1.查询
SHOW DATABASES;
2.创建
创建数据库
CREATE DATABASE 数据库名称;
创建数据库(判断,如果不存在则创建)
CREATE DATABASE IF NOT EXISTS 数据库名称;
3.删除
删除数据库
DROP DATABASE 数据库名称;
删除数据库(判断,如果存在则删除)
DROP DATABASE IF EXISTS 数据库名称;
4.使用数据库
查看当前使用的数据库
SELECT DATABASE();
使用数据库
USE 数据库名称;
DDL–操作表
1.查询表
查询当前数据库下所有表名称
SHOW TABLES;
查询表结构
DESC 表名称;
2.创建表
create table 表名(
-> 字段名1 数据类型1,
-> 字段名2 数据类型2,
-> ...
-> 字段名n 数据类型n
);
注意:最后一行末尾,不能加逗号
数据类型
MySQL支持多种类型,可以分为三类:
- 数值
- 日期
- 字符串
创建案例
删除表
1.删除表
DROP TABLE 表名;
2.删除表时判断表是否存在
DROP TABLE IF EXISTS 表名;
修改表
1.修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
2.添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
3.修改数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
4.修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
5.删除列
ALTER TABLE 表名 DROP 列名;
图形化客户端工具
Navicat
NavicatforMySQL是管理和开发MySQL或MariaDB的理想解决方案。
这套全面的前端工具为数据库管理、开发和维护提供了一款直观而强大的图形界面。
·官网:http://www.navicat.com.cn
DML–操作数据
添加数据
1.给指定列添加数据
INSERT INTO 表名(列名1,列名2,…)VALUES(值1,值2,..);
2.给全部列添加数据
INSERT INTO 表名 VALUES(值1,值2,.);
3.批量添加数据
INSERT INTO 表名(列名1,列名2,.)VALUES(值1,值2,….),(值1,值2,..),(值1,值2,…..;
INSERT INTO 表名 VALUES(值1,值2,.)),(值1,值2,.),(值1,值2,…..;
修改数据
1.修改表数据
UPDATE 表名 SET 列名1=值1,列名2=值2,….[WHERE 条件];
注意:修改语句中如果不加条件,则将所有数据都修改!
删除数据
1.删除数据
DELETE FROM 表名[ WHERE 条件 ];
注意:删除语句中如果不加条件,则将所有数据都删除!
DQL
查询语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组后条件
ORDER BY
排序字段
LIMIT
分页限定
基础查询
查询案例
-- 删除stu表
drop table if exists stu;
-- 创建stu表
CREATE TABLE Stu (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math double(5,2), -- 数学成绩
english double(5,2), -- 英语成绩
hire_date date -- 入学时间
);
-- 添加数据
INSERT INTO stu(id, name, age, sex, address, math, english, hire_date)
VALUES
(1, '马运', 55, '男', '杭州', 66, 78, '1997-09-01'),
(2, '马花疼', 45, '女', '深圳', 56, 77, '1998-09-01'),
(3, '马斯克', 45, '男', '湖南', 20, 87, '1996-09-01'),
(4, '小明', 45, '女', '香港', 96, 65, '1991-09-01'),
(5, '小红', 57, '男', '香港', 98, 65, '1992-09-01'),
(6, '鲁迅', 20, '女', '湖南', 99, 99, '1993-09-01'),
(7, '周树人', 22, '男', '湖南', 86, NULL, '1994-09-01'),
(8, '陌生人', 18, '女', '南京', 99, 99, '1995-09-01');
-- 查询数据
SELECT * FROM stu;
1.查询多个字段
SELECT 字段列表 FROM 表名;
SELECT * FROM 表名;--查询所有数据
2.去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
3.起别名
AS:AS也可以省略
条件查询
1.条件查询语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
2.条件
模糊查询
排序查询
1.排序查询语法
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1[排序方式1],排序字段名2[排序方式2]...;
排序方式:
ASC:升序排列 (默认值)
DESC:降序排列
注意:如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序
聚合函数
1. 概念:
将一列数据作为一个整体,进行纵向计算。
2.聚合函数分类:
3.聚合函数语法:
SELECT 聚合函数名(列名)FROM 表;
注意:null值不参与所有聚合函数运算
分组查询
1.分组查询语法
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名[HAVING 分组后条件过滤];
注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义
where和having区别:
- 执行时机不一样:where是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过滤。
- 可判断的条件不一样:where不能对聚合函数进行判断,having可以。
执行顺序:where>聚合函数>having
分页查询
1.分页查询语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数;
起始索引:从0开始
计算公式:起始索引 =(当前页码-1)*每页显示的条数
tips:
- 分页查询limit是MySQL数据库的方言
- Oracle分页查询使用rownumber
- SQL Server分页查询使用top
数据库
约束
约束的概念和分类
1.约束的概念
- 约束是作用于表中列上的规则,用于限制加入表的数据
- 约束的存在保证了数据库中数据的正确性、有效性和完整性
2.约束的分类
Tips:MySQL不支持检查约束
约束案例
根据需求,为表添加合适的约束
演示:
外键约束
1.概念
外键用来让两个表的数据之间建立链接,保证数据的一致性和完整性
2.语法
(1)添加约束
-- 创建表时添加外键约束
CREATE TABLE 表名(
列名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名)REFERENCES 主表(主表列名)
);
-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称)REFERENCES主表名称(主表列名称);
(2)删除约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
数据库设计
1.软件的研发步骤
2.数据库设计概念
- 数据库设计就是根据业务系统的具体需求,结合我们所选用的DBMS,为这个业务系统构造出最优的数据存储模型。
- 建立数据库中的表结构以及表与表之间的关联关系的过程。
- 有哪些表?表里有哪些字段?表和表之间有什么关系?
3.数据库设计的步骤
- 需求分析(数据是什么?数据具有哪些属性?数据与属性的特点是什么)
- 逻辑分析(通过ER图对数据库进行逻辑建模,不需要考虑我们所选用的数据库管理系统)
- 物理设计(根据数据库自身的特点把逻辑设计转换为物理设计)
- 维护设计(1.对新的需求进行建表;2.表优化)
表关系
-
一对一:
---- 如:用户和用户详情
---- 一对一关系多用于表拆分,将一个实体中经常使用的字段放一张表,不经常使用的字段放另一张表,用于提升查询性能
---- 实现方式:在任意一方加入外键,关联另一方主键,并且设置外键为唯一(UNIQUE) -
一对多(多对一):
---- 如:部门和员工
---- 一个部门对应多个员工,一个员工对应一个部门
---- 实现方式:在多的一方建立外键,指向一的一方的主键 -
多对多:
---- 如:商品和订单
---- 一个商品对应多个订单,一个订单包含多个商品
---- 实现方式:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
多表查询
简介
- 笛卡尔积:取A,B集合所有组合情况
- 多表查询:从多张表查询数据
- 连接查询
- 内连接:相当于查询AB交集数据
- 外连接:
- 左外连接:相当于查询A表所有数据和交集部分数据
- 右外连接:相当于查询B表所有数据和交集部分数据
- 子查询
- 连接查询
内连接
1.内连接查询语法
-- 隐式内连接
SELECT 字段列表 FROM 表1,表2... WHERE 条件;
-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
内连接相当于查询AB交集数据
外连接
1.外连接查询语法
-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
左外连接:相当于查询A表所有数据和交集部分数据
右外连接:相当于查询B表所有数据和交集部分数据
子查询
1.子查询概念:
- 查询中嵌套查询,称嵌套查询为子查询
2.子查询根据查询结果不同,作用不同:
- 单行单列
- 多行单列
- 多行多列
子查询根据查询结果不同,作用不同:
- 单行单列:作为条件值,使用=!=><等进行条件判断
SELECT 字段列表 FROM 表 WHERE 字段名 =(子查询);
- 多行单列:作为条件值,使用in等关键字进行条件判断
SELECT 字段列表 FROM 表 WHERE 字段名 in(子查询);
- 多行多列:作为虚拟表
SELECT 字段列表 FROM(子查询)WHERE 条件;
多表查询案例
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;
-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 职务表,职务名称,职务描述
CREATE TABLE job(
id INT PRIMARY KEY,
jname VARCHAR(20),
job_description VARCHAR(50)
);
-- 员工表
CREATE TABLE emp(
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT, -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salarygrade(
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 添加4个职务
INSERT INTO job (id, jname, job_description) VALUES
(1,'董事长','管理整个公司,接单'),
(2,'经理','管理部门员工'),
(3,'销售员','向客人推销产品'),
(4,'文员','使用办公软件');
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'岁贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李达',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;
-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 职务表,职务名称,职务描述
CREATE TABLE job(
id INT PRIMARY KEY,
jname VARCHAR(20),
job_description VARCHAR(50)
);
-- 员工表
CREATE TABLE emp(
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT, -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salarygrade(
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 添加4个职务
INSERT INTO job (id, jname, job_description) VALUES
(1,'董事长','管理整个公司,接单'),
(2,'经理','管理部门员工'),
(3,'销售员','向客人推销产品'),
(4,'文员','使用办公软件');
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'岁贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李达',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
-- 1.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述
/*
分析:
1.员工编号,员工姓名,工资信息在emp员工表中
2.职务名称,职务描述信息在job职务表中
3.job 职务表 和emp 员工表 是一对多的关系emp.job_id= job.id
*/
-- 隐式内连接
select emp.id,emp.ename,emp.salary,job.jname,job.job_description from emp,job where emp.job_id = job.id;
select * from emp;
select * from job;
-- 显式内连接
select emp.id,emp.ename,emp.salary,job.jname,job.job_description from emp inner join job on emp.job_id = job.id;
-- 2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
/*
分析:
1.员工编号,员工姓名,工资信息在emp员工表中
2.职务名称,职务描述信息在job职务表中
3.job 职务表 和emp 员工表 是一对多的关系emp.job_id= job.id
4.部门名称,部门位置来自于部门表dept
5.dept和emp一对多关系dept.id=emp.dept_id
*/
-- 隐式内连接
select emp.id,emp.ename,emp.salary,job.jname,job.job_description,dept.dname,dept.loc from emp,job,dept where emp.job_id = job.id and emp.job_id= job.id;
-- 显式内连接
select emp.id,emp.ename,emp.salary,job.jname,job.job_description,dept.dname,dept.loc from emp inner join job on emp.job_id = job.id inner join dept on dept.id = emp.dept_id;
-- 3.查询员工姓名,工资,工资等级
/*
分析:
1.员工姓名,工资信息在emp员工表中
2.工资等级 信息在 salarygrade 工资等级表中
3. emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
*/
select emp.ename,emp.salary,t2.* from emp,salarygrade t2 where emp.salary >= t2.losalary and emp.salary <= t2.hisalary
-- 4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
/*
分析:
1.员工编号,员工姓名,工资信息在emp员工表中
2.职务名称,职务描述信息在job职务表中
3.job职务表和emp员工表是一对多的关系emp·job_id=job.id
4.部门名称,部门位置来自于部门表 dept
5.dept和emp一对多关系dept.id=emp.dept_id
6.工资等级信息在 salarygrade工资等级表中
7. emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
*/
select emp.id,emp.ename,emp.salary,job.jname,job.job_description,dept.dname,dept.loc,t2.grade from emp inner join job on emp.job_id = job.id inner join dept on dept.id = emp.dept_id inner join salarygrade t2 on emp.salary between t2.losalary and t2.hisalary;
-- 5.查询出部门编号、部门名称、部门位置、部门人数
/*
分析:
1.部门编号、部门名称、部门位置来自于部门dept表
2.部门人数:在emp表中 按照dept_id进行分组,然后count(*)统计数量
3.使用子查询,让部门表和分组后的表进行内连接
4.
*/
select * from dept;
select dept_id, count(*) from emp group by dept_id;
select dept.id,dept.dname,dept.loc,t1.count from dept,(select dept_id, count(*) count from emp group by dept_id) t1 where dept.id = t1.dept_id
事务
事务简介
- 数据库的事务(Transaction)是一种机制、一个操作序列,包含了一组数据库操作命令
- 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么同时成功,要么同时失败
- 事务是一个不可分割的工作逻辑单元
-- 开启事务
START TRANSACTION;
或者 BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
MySQL事务默认自动提交
-- 查看事务的默认提交方式
SELECT @@autocommit;
-- 1 自动提交 θ 手动提交
-- 修改事务提交方式
set @@autocommit = 0;
DROP TABLE IF EXISTS aCCount;
-- 创建账户表
CREATE TABLE aCCount(
id int PRIMARY KEY auto_increment,
name varchar(10),
money double(10,2)
);
-- 添加数据
INSERT INTO account(name,money) values('张三',1000),('李四',1000);
select * from account;
UPDATE account set money = 1000;
-- 转账操作
-- 开启事务
BEGIN;
-- 1.查询李四的余额
-- 2.李四金额 -500
UPDATE account set money = money - 500 where name = '李四';
-- 出错了!
-- 3.张三金额 +500
UPDATE account set money = money + 500 where name = '张三';
-- 提交事务
COMMIT;
-- 回滚事务 :回到开启事务之前的状态
ROLLBACK;
-- 1.查询事务的默认提交方式
select @@autocommit;
-- 2.修改事务的提交方式手动提交
set @@autocommit = 0;
select * from account;
-- 2.李四金额-500
UPDATE account set money = money - 500 where name = '李四';
-- commit
commit;
事务四大特征
- 原子性(Atomicity):事务是不可分割的最小操作单位,要么同时成功,要么同时失败
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态
- 隔离性(lsolation):多个事务之间,操作的可见性
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的