数据库笔记

于 2025-05-14 16:00:24 发布
阅读量981 收藏 21
点赞数 17
CC 4.0 BY-SA版权
文章标签： 数据库 笔记 oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/hardhap/article/details/147954965

黑马程序员数据库学习笔记

通用SQL语法和分类

分类

  • DDL数据定义语言，用来定义数据库对象（数据库，表，字段）。
  • DML数据操作语言，用来对数据库表中的数据进行增删改。
  • DQL数据查询语言，用来查询数据库中表的记录。
  • DCL数据控制语言，用来创建数据库用户，控制数据库的访问权限。

DDL - 数据定义

操作数据库

# 查询所有数据库
show databases;

# 查询当前数据库
select database();

# 创建数据库
create database [if not exists] 数据库名称 [default charset 字符集] [collate 排序规则];

# 删除数据库
drop database [if exists] 数据库名称;

# 使用数据库
use 数据库名称;

创建表

# 查询当前数据库的所有表
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 表注释];

例子：创建一个这样的表
在这里插入图片描述

 create table user(
	 id int comment '编号',
	 name varchar(50) comment '姓名',
	 age int comment '年龄',
	 gender varchar(1) comment '性别'
	 ) comment '用户表';

数据类型

数值类型

在这里插入图片描述

精度：小数的总位数，比如123.45的精度是5
标度：小数部分的位数，比如123.45的标度是2
定义一个无符号的double类型的字段score：score double(4,1) unsigned

字符串类型

在这里插入图片描述

char - 定长字符串 性能较好
varchar - 变长字符串 性能较差

日期时间类型

在这里插入图片描述

修改表

# 添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

# 修改字段
	#修改字段类型
	alter table 表名 modify 字段名 新数据类型(长度);
	# 修改字段名和字段类型
	alter 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];

# 删除字段
alter table 表名 drop 字段名;

# 修改表名
alter table 表名 rename to 新表名;

# 删除表
drop table [if exists] 表名;

# 删除指定表，并重新创建该表
truncate table 表名;

DML - 数据操作

DML数据操作语言用来对数据库中表的数据记录进行增删改操作。

添加数据

# 给指定字段添加数据
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, ...);

修改数据

# 如果没写条件，默认修改整张表
update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [where 条件];

删除数据

# delete语句不能删除某一个字段的值，可以用update将某一字段的值设为null
delete from 表名 [where 条件]

DQL - 数据查询

基本查询

# 查询多个字段
select 字段1, 字段2, 字段3... from 表名;
select * from 表名;

# 设置别名(AS可以省略)
select 字段1 [AS 别名1], 字段2 [AS 别名2]... from 表名;

# 去除重复记录
select distinct 字段列表 from 表名;


#一个例子
DROP TABLE if exists emp;

CREATE table if not exists emp(
  id int COMMENT '编号',
  workno VARCHAR(10) COMMENT '工号',
  name VARCHAR(10) COMMENT '姓名',
  gender char COMMENT '性别',
  age TINYINT UNSIGNED comment '年龄',
  workaddress VARCHAR(5) COMMENT '工作地址'
) COMMENT '员工表';

INSERT INTO emp
VALUES
(1, '1', '张三', '男', 20, '北京'),
(2, '2', '李四', '男', 11, '西安'),
(3, '3', '王五', '女', 24, '上海'),
(4, '4', '赵六', '男', 25, '长沙'),
(5, '5', '杨迪', '女', 29, '西安');

# 查询指定字段
SELECT name, workno, age from emp;

# 查询所有字段
SELECT * from emp;

# 查询所有员工的工作地址
SELECT workaddress from emp;

# 查询所有员工的工作地址，并起别名(as可省略)
SELECT workaddress as '工作地址' from emp;

# 查询所有员工的工作地址，并去重
SELECT DISTINCT workaddress as '工作地址' from emp;

条件查询

条件运算符有比较运算符和逻辑运算符两种：
在这里插入图片描述
在这里插入图片描述

select 字段列表 from 表名 where 条件列表;

# 例子:
-- 条件查询
# 查询年龄符合条件的记录
SELECT * from emp WHERE age = 25;
SELECT * from emp WHERE age < 25;
# 查询工作地址为空的记录
SELECT * from emp WHERE workaddress is NULL;
# 查询工作地址不为空的记录
SELECT * from emp WHERE workaddress is not NULL;
# 查询年龄在15到25（左闭右闭）区间内的记录
SELECT * from emp WHERE age >= 15 AND age <= 25;
SELECT * from emp WHERE age BETWEEN 15 AND 25;
# 查询年龄等于20或24或29的记录
SELECT * from emp WHERE age = 20 OR age = 24 OR age = 29;
SELECT * from emp WHERE age = 20 || age = 24 || age = 29;
SELECT * from emp WHERE age in (20, 24, 29);
# 查询姓名为两个字的记录 下划线匹配单个字符 百分号匹配任意个字符
SELECT * from emp WHERE name like '__';
# 查询工作地点以“草原”结尾的记录
SELECT * from emp WHERE workaddress like '%草原';

聚合函数

将一列数据（某个字段）作为一个整体，进行纵向计算。

# 统计员工数量
SELECT COUNT(*) FROM emp; -- 返回 5

SELECT COUNT(name) FROM emp; -- 返回 5

SELECT COUNT(workaddress) FROM emp; -- 返回 4, 聚合函数不统计NULL

# 统计员工平均年龄
SELECT AVG(age) FROM emp;

# 找出员工最大年龄
SELECT MAX(age) FROM emp;

# 找出最大年龄的员工姓名
SELECT name FROM emp WHERE age = (SELECT MAX(age) FROM emp);

# 找出工作地址在西安的员工年龄之和
SELECT SUM(age) FROM emp WHERE workaddress = '西安';

分组查询

# 语法
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];
# WHERE 对分组前进行过滤，不满足 WHE RE 条件的不参与分组，HAVING 对分组后进行过滤。

# 例子
# 按照性别分组，统计男性、女性的数量
SELECT gender, COUNT(*) FROM emp GROUP BY gender;

# 按照性别分组，统计男性、女性员工的平均年龄
SELECT gender, AVG(age) FROM emp GROUP BY gender;

# 查询年龄小于 25 的员工，并按照工作地址分组，获取员工数量大于等于 2 的工作地址
SELECT workaddress, COUNT(*) address_count FROM emp WHERE age < 30 GROUP BY workaddress HAVING address_count >= 2;

排序查询

# 语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排 序方式1, 字段2 排序方式2;

# ASC 升序（默认值）
# DESC 降序

#例子
# 根据年龄进行降序排序
SELECT * FROM emp ORDER BY age DESC;
 
# 先按年龄升序排序，年龄相同，则按 id 进行降序排序
SELECT * FROM emp ORDER BY age ASC, id DESC;

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

# 注意
# 起始索引从 0 开始，起始索引 = （查询页码 - 1） * 每页显示记录数
# 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
# 如果查询的是第一页数据，起始索引可以省略，直接简写为 LIMIT 10

#例子
# 查询第 1 页员工数据，每页展示 2 条记录
SELECT * FROM emp LIMIT 0, 2;
 
# 查询第 2 页员工数据，每页展示 2 条记录
SELECT * FROM emp LIMIT 2, 2;

DQL - 编写/执行顺序

# 编写顺序
SELECT 
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数


# 执行顺序
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
SELECT 
	字段列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

DCL - 数据控制

控制 可访问数据库的用户、用户可访问哪些数据库。

用户管理

# 查询用户
USE mysql; # 用户相关信息都存储在系统数据库mysql的user表中
SELECT * FROM user;

# 创建用户
CREATE USER `用户名`@`主机名` IDENTIFIED BY `密码`;

# 修改用户密码
ALTER USER `用户名`@`主机名` IDENTIFIED WITH mysql_native_password BY `新密码`; # mysql_native_password 是指定的密码加密方式

# 删除用户
DROP USER `用户名`@`主机名`;

权限控制

# 查询权限
SHOW GRANTS FOR `用户名`@`主机名`;

# 授予权限
GRANTS 权限列表 ON 数据库名.表名 TO `用户名`@`主机名`;

# 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM `用户名`@`主机名`;

多个权限之间，使用逗号分隔。
授权时，数据库和表名可以使用 * 进行通配，代表所有。

函数

字符串函数

# 字符串拼接，将s1, s2, ..., sn拼成一个字符串
CONCAT(s1, s2, ..., sn)

# 将字符串 str 全部转为小写
LOWER(str)

# 将字符串 str 全部转为大写
UPPER(str)

# 左填充，用字符串 pad 对 str 的左边进行填充，保证长度为n
LPAD(str, n, pad)

# 右填充，用字符串 pad 对 str 的右边进行填充，保证长度为n
RPAD(str, n, pad)

# 去掉字符串头部和尾部的空格
TRIM(str)

# 返回字符串 str 从 start 位置起(含)的 len 个长度的字符串
# 索引从 1 开始
SUBSTRING(str, start, len)

数值函数

# 向上取整
CEIL(x)

# 向下取整
FLOOR(x)

# 返回 x / y 的模
MOD(x, y)

# 返回0~1内的随机数
RAND()

# 求参数 x 的四舍五入的值，保留 y 位小数
ROUND(x, y)

日期函数

# 返回当前日期
CURDATE()

# 返回当前时间
CURTIME()

# 返回当前日期和时间
NOW()

# 获取指定date的年份
YEAR(date)

# 获取指定date的月份
MONTH(date)

# 获取指定date的日期
DAY(date)

# 返回一个日期/时间值加上一个时间间隔expr后的时间值
DATE_ADD(date, INTERVAL expr type)
# DATE_ADD(date, INTERVAL 70 DAY) 往后推70天

# 返回起始时间 date1 和结束时间 date2 之间的天数
DATEDIFF(date1, date2)

流程函数

# 如果 value 为 true, 则返回 t, 否则返回 f
IF(value, t f)

# 如果 value 不为空, 则返回 value1, 否则返回 value2
IFNULL(value1, value2)

# 如果 val1 为 true, 则返回 res1, 否则返回 default 默认值
# WHEN ... ELSE ... 可以写多个
CASE WHEN [ val1 ] THEN [ res1 ] ...ELSE [ default ] END

# 如果 expr 的值等于 val1, 则返回 res1, 否则返回 default 默认值
CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] ...ELSE [ default ] END

# 例子：
SELECT
	id,
	name,
	(CASE WHEN math >= 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END) '数学'，
	(CASE WHEN english >= 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END) '英语'，
	(CASE WHEN chinese >= 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END) '语文'
from score;

# 例子：
SELECT `name`, CASE workaddress
	WHEN '北京' OR '上海' THEN
		'一线城市'
	ELSE
		'二线城市'
END
FROM emp;

约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据。约束的目的是保证数据库中数据的正确、有效性和完整性。对一个字段可以添加多个约束。
在这里插入图片描述

主键是一行数据的唯一标识，非空且唯一。

DROP TABLE IF EXISTS user;

CREATE TABLE user(
  id int PRIMARY KEY auto_increment COMMENT '主键', # id：主键、自增
  name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名', # name：非空、唯一
  age int CHECK( age > 0 && age <= 120 ) COMMENT '年龄', # age：检查约束
  status VARCHAR(1) DEFAULT '1' COMMENT '状态', # status：默认约束
  gender char(1) COMMENT '性别' # gender：无约束
) comment '用户表';

INSERT INTO user(name, age, gender) 
VALUES
('Tom', 19, '男'), # 数据库自动维护主键、无需手动插入，主键自动递增
('Bob', 25, '男'), # 没有插入 status， 默认为 '1'
('Sam', 25, '男'); # 年龄符合检查约束的条件时才能正确插入数据

外键约束

外键约束是用来让两张表之间的数据建立连接，从而保证数据的一致性和完整性。
在这里插入图片描述

# 在建表时添加外键
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
)

# 建表后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称(自己设置) FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名); 

# 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

在这里插入图片描述

# 添加外键时指定更新/删除规则
ALTER TABLE 表名 ADD CONSTRAINT 外键名称(自己设置) FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) ON UPDATE 更新规则 ON DELETE 删除规则;

多表查询

多表关系

各个表结构之间基本分为三种关系：一对多（多对一）、多对多、一对一。

  1. 一对多（多对一）：比如一个部门拥有多个员工，一个员工只属于一个部门。在数据库的实现上，在多的一方建立外键，指向”一“的一方。

  2. 多对多：一个学生选修多个课程，一个课程可供多个学生选择。在数据库的实现上，建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。在这里插入图片描述

  3. 一对一：多用于单表拆分。比如将一些人的完整信息表拆分成两个部分信息表。这两个部分信息表的数据之间就是一对一的关系。在数据库的实现上，在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

多表查询概述

# 多表查询
SELECT * FROM emp, dept;

假设表 emp 有 m 条记录，表 dept 有 n 条记录，上述代码会查询出 m × n 条记录，即两张表所有记录的所有组合情况。为了去除多余的组合记录，应加上一个 WHRER 限制条件，使子表的外键等于父表的主键使才显示这条记录。
在这里插入图片描述

连接查询

内连接

内连接返回两张表的交集部分记录。

# 隐式内连接
SELECT 字段列表 FROM 表1 [表1别名], 表2 [表2别名] WHERE 条件...;
# 例子 注意，如果给表起了别名，就不能再使用表的原名进行操作了
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;

外连接

左外连接
左外连接查询表1（左表）的所有数据 包含 表1和表2的交集部分数据

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

右外连接
右外连接查询表2（右表）的所有数据 包含 表1和表2的交集部分数据

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

自连接查询，可以是内连接查询，也可以是外连接查询。
在这里插入图片描述

# 查询员工及其所属领导的名字
SELECT a.name, b.name FROM emp a, emp b WHERE a.managerid = b.id;

# 查询所有员工及其领导的名字，如果员工没有领导也需要查询出来。（左连接）
SELECT a.name '员工', b.name '领导' FROM emp a LEFT JOIN emp b ON a.managerid = b.id;

联合查询（并、交、差操作）

联合查询（union）就是将多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

联合查询会将两次查询的结果拼接起来即，将第二次查询的结果拼在第一次查询结果的下方。
使用联合查询的条件是两次查询的列都相同。
union 和 union all 的区别是：union 会在合并后做去重，union all 不会去重。

交操作 INTERSECT
差操作 EXCEPT

子查询

在SQL语句中嵌套SELECT语句，成为嵌套查询，又称子查询。
在这里插入图片描述

  1. 标量子查询：查询结果为单个值
    根据销售部部门id，查询员工信息。
    SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部');
  2. 列子查询：查询结果是一列（可以有多行）
    常用操作符：IN（在指定的集合范围内，多选一）、NOT IN（不在指定的集合范围之内）、ANY（子查询返回列表中，有任意一个满足即可）、SOME（与ANY等同，使用SOME的地方都可以使用ANY）、ALL（子查询返回列表的所有值都必须满足）
    # 查询“销售部”和“市场部“的所有员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name = "销售部" OR name = "市场部");

# 查询比“财务部"所有人工资都高的员工信息
SELECT * FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = "财务部") );

# 查询比“研发部"所有人工资都高的员工信息
SELECT * FROM emp WHERE salary > ANY (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = "研发部") );
  3. 行子查询：查询结果是一行（可以是多列）
    # 查询薪资和直属领导都和“张无忌”相同的员工信息
SELECT * FROM emp WHERE (salary, managerid) = (SELECT salary, managerid FROM emp WHERE name = '张无忌');
  4. 表子查询：查询返回的结果是多行多列。
    # 查询职位和薪资都与“张三”或“李四”相同的员工信息
SELECT * FROM emp WHERE (job, salary) IN (SELECT job, salary FROM emp WHERE name = "张三" OR name = "李四");

# 查询入职日期是 '2006-01-01' 之后的员工信息及其部门信息
SELECT e.*, d.* FROM (SELECT * FROM emp WHERE entrydate > '2006-01-01') e LEFT JOIN dept d ON e.dept_id = d.id;

事务

事务是一组操作的集合，不可分割。事务会将所有操作作为整体一起向系统提交或者撤销，这些操作要么同时成功，要么同时失败。

START TRANSACTION 或 BEGIN 开启一个事务，顺序执行代码。如果没有报错，最后执行 COMMIT 将修改提交到数据库，如果中间出现报错，执行 ROLLBACK 回滚事务，以撤销这个事务带来的修改。

事务的四大特性（ACID）

  1. 原子性：事务时不可分割的最小操作单元，要么全部成功，要么全部失败。
  2. 一致性：事务完成时，必须使所有的数据都保持一致状态。
  3. 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  4. 持久性：事务一旦提交或回滚，他对数据库中的数据的改变就是永久的。

并发事务问题

  1. 脏读：一个事务读到另一个事务还没有提交的数据。
  2. 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同。（原因是两次查询之间有其他事务修改了数据并COMMIT）
  3. 幻读：一个事务按照条件查询数据时，没有对应的数据行，但插入数据时，又报错该数据已存在。

事务隔离级别

隔离级别脏读不可重复读幻读
Read uncommitted会出现会出现会出现
Read committed会出现不会出现不会出现
Repeatable Read(默认)会出现会出现不会出现
Serializable会出现会出现会出现

视图

视图是一个虚拟存在的表，用于封装一个已存在的表（基表）。视图并不真的存储数据，所有对视图的增删改查操作其实是作用在基表上。

可以基于一个视图来创建新的视图。

# 建立计算机系选修了1号课程的学生视图
CREATE VIEW score(Sno, Sname, Grade)
AS
SELECT Student.Sno, Sname, Grade
FROM Student, SC
WHERE Sdept = 'COMPUTER'
AND Student.Sno = SC.SNO
AND SC.Cno = '1';

在这里插入图片描述

存储过程

存储过程是实现经过编译并存储在数据库中的一段SQL语句的集合。本质上是SQL代码的封装和重用。

前城
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值