数据库的学习

一口一只瑜

已于 2023-10-21 00:13:28 修改

阅读量52

点赞数 5

文章标签：数据库

于 2023-10-16 21:29:57 首次发布

本文链接：https://blog.csdn.net/m0_72199724/article/details/133870016

版权

本文详细介绍了SQL语言的基础知识，包括数据定义语言DDL（如创建、修改数据库和表）、数据操作语言DML（增删改查）、数据查询语言DQL（查询、分组、排序）、数据控制语言DCL（用户管理和权限），以及事务处理和并发问题及其解决方法。

摘要由CSDN通过智能技术生成

数据库的学习

SQL通用语法

启动

net start mysql80
停止

net stop mysql80

分类 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 表名（
  
  字段1 字段1类型 [comment 注释部分]
  
  字段2 字段2类型 [comment 注释部分]
  
  字段3 字段3类型 [comment 注释部分]
  
  字段4 字段4类型 [comment 注释部分]
  
  ......
  
  字段n 字段n类型 [comment 注释部分]
  
  ）[comment 注释部分]
数据类型

表修改
- 添加字段 alter tables 表名 add 字段名类型(长度) [comment 注释][约束];
- 修改数据类型 aletr table 表名 modify 字段名新数据类型(长度);
- 修改字段和字段类型 alter table 表名 change 旧字段名新字段名类型(长度) [comment 注释][约束];
- 删除字段 alter table 表名 drop 字段名;
- 修改表名 alter table 表名 rename to 新表名;
- 删除表 drop table [if exists] 表名;
- 删除指定表并重新创建表 truncate table 表名;

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 from 表名 [where 条件];
  - delete 语句条件可以有也可以没有，如果没有则删除整张表的数据
  - delete 语句不能删除某一字段的值(可以使用update)

DQL

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

select * from 表名;

设置别名
- select 字段1[as 别名1],字段2[as 别名2]... from 表名;
去除重复记录
- select distinct 字段列表 from 表名;
条件查询
- select 字段列表 from where 条件列表;
条件

聚合函数

聚合函数将一列数据作为一个整体，进行纵向计算。
- 语法select 聚合函数(字段列表) from 表名;
分组查询
- select 字段列表 from [where 条件] group by 分组字段名 [having 分组过滤后体条件];
  where——having的区别
  - 执行时机不同：where是分组之前过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤
  - 判断条件不同：where不能对聚合函数进行判断，而having可以
  - 执行顺序：where > 聚合函数 > having。
  - 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意思
排序查询
- select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;
  排序方式
  - asc：升序(默认值)
  - desc：降序
  如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
分页查询 select 字段列表 from 表名 limit 起始索引，查询记录数;
- 注意事项
  - 起始索引从0开始，起始索引= (查询页码-1)*每页显示记录数。
  - 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是limit。
  - 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

DCL

管理用户
- 查询 use mysql;
  
  select * from user;
- 创建 create user '用户名'@'主机名' identified by 密码;
- 修改密码 alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
- 删除 drop user '用户名'@'主机名';
  - 主机名可以使用%通配。
  - 这类SQL开发人员操作的比较少，主要是DBA ( Database Administrator 数据库管理员）使用。
权限控制
- 查询权限 show grants for '用户名'@'主机名';
- 授予权限 grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
- 撤销权限 revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

SQL函数

函数是指一段可以直接被另一段程序调用的程序或代码

用法 select 函数(参数);

字符串函数

数值函数

流程函数

日期函数

约束

约束是作用于表中字段上的规则，用于限制储存在表中的数据。

可以进行以下操作进行试验

create table user (
id int primary key auto_increment comment "主键",
name varchar(10) not null unique comment "姓名",
age int check(age > 0 && age <= 120) comment "年龄",
status char(1) default '1' comment "状态",
gender char(1)  comment "性别"
) comment "用户表";

如果id字号想重置有以下方法:

删除data: DELETE FROM user WHERE 关键值1='字段1' OR 关键值1='字段1';

TRUNCATE TABLE user;(删除所有数据)

DELETE FROM user;(有外键)

重置id:ALTER TABLE user AUTO_INCREMENT = 1;

外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

准备父表数据

create table dept(
 id int auto_increment comment 'ID',
 name varchar(50) not null comment '部门名称',
 primary key (id)
)comment = '部门表';

insert into dept (id, name) values (1, '研发部'),(2, '市场部'),(3, '财务部'),(4, '销售部'),(5, '总经办');

准备子表数据

create table emp(
id int auto_increment comment 'ID',
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID',
primary key (id)
) comment = '员工表';

dept_id是用来关联父表主键
insert into emp (id, name, age, job, salary, entrydate, managerid, dept_id) values
(1, '金庸', 66, '总裁', 28808, '2000-01-01', null, 5),
(2, '张无忌', 28, '项目经理', 12500, '2005-12-05', 1, 1),
(3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
(4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
(5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
(6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);

添加外键

语法:

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;

)

多表查询

概述:项目开发过程中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在以下各种联系：

一对多(多对一)

多对多

一对一

一对多(多对一)
- 实现两张表之间的关联，建立第三张表，中间的表有两个外键关联着两个主键

案例:
# 创建学生信息表
create table studentlc (
       id int auto_increment primary key comment '主键ID',
       name varchar(10) comment '姓名',
       no varchar(10) comment '学号'
       ) comment '学生表';
# 插入信息
insert into studentlc (name, no) values ('黛绮丝', '2000100101'), ('谢逊', '2000100102'), ('殷天正', '2000100103'), ('韦一笑', '2000100104');

# 创建课程信息表
create table course (
        id int auto_increment primary key comment '主键ID',
        name varchar(10) comment '课程名称'
        ) comment '课程表';
# 插入信息
insert into course (name) values ('Java'), ('PHP'), ('MySQL'), ('Hadoop');

# 创建关联表
create table student_course (
        id int auto_increment primary key comment '主键',
        studentid int not null comment '学生ID',
        courseid int not null comment '课程ID',
        constraint fk_courseid foreign key (courseid) references course (id),
        constraint fk_studentid foreign key (studentid) references studentlc (id)
        ) comment '学生课程中间表';
# 插入信息
insert into student_course (studentid, courseid) values (1, 1), (1, 2), (1, 3), (2, 2), (2, 3), (3, 4);

一对一

在任意一方加入外键，关联另一方外键，并且设置外键唯一的(unique) ——实现方法和理解简单不多赘述

内连接

内连接查询的是两张表的交集的部分

隐式内连接
- select 字段列表 from 表1，表2 where 条件...;
显示内连接
- select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

外连接

左外连接
- select 字段列表 from 表1 left [outer] join 表2 on 条件...;
  
  相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据
右外连接
- select 字段列表 from 表1 right [outer] join 表2 on 条件...;
  
  相当于查询表2(左表)的所有数据包含表1和表2交集部分的数据

自连接

自连接查询可以是内/外连接查询。

语法: select 字段列表 from 表A 别名B on 条件...;

联合查询-union,union all

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集，并且对查询的结果进行去重，union all则不去重。

对于此查询，查询的表的列数必须保持一致，字段类型也许保持一致。

语法: select 字段列表 from 表A... union [all] select 字段列表 from 表B...;

子查询

概念:sql语句中嵌套select语句，称为嵌套语句，又称子查询

语法:select * from t1 where column = (select coumn 1 from t2);

子查询外部语句的语句可以是insert / update /delete / select 的任何一个。

标量子查询(子查询结果为单个值)
- 常用的操作符:= <> > >= < <=
  
  案例:查询销售部的所有员工
  
  a. 查询"销售部"部门ID SELECT id FROM dept WHERE name = '销售部';
  
  b. 根据销售部部门ID，查询员工信息 SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部');
列子查询(子查询结果为一列(可多行))
- 常用操作符:in 、not in、any、some、all
  
  案例:查询销售部和市场部的员工信息
  
  a. 查询"销售部"和"市场部"的部门ID SELECT id FROM dept WHERE name = '销售部' OR name = '市场部'; ```
```
```b. 根据部门ID，查询员工信息 SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name = '销售部' OR name = '市场部');```
```
行子查询(子查询结果为一行(可多列))
- 操作符:= 、<> 、in、not in
  
  案例:查询与“张无忌”的薪资及直属领导相同的员工信息
  
  a. 查询"张无忌"的薪资及直接领导 SELECT salary, managerid FROM emp WHERE name = '张无忌';
  
  b. 查询与"张无忌"的薪资和直接上级领导相同的员工信息 SELECT * FROM emp WHERE (salary, managerid) IN (SELECT salary, managerid FROM emp WHERE name = '张无忌');
表子查询(子查询结果为多行多列)
- 操作符:in
  
  案例:查询“鹿杖客”，“宋远桥”的职位和薪资相同的员工的信息

a. 查询"鹿杖客"和"宋远桥"的职位和薪资 SELECT job, salary FROM emp WHERE name = '鹿杖客' OR name = '宋远桥';

b. 查询与"鹿杖客"和"宋远桥"的职位和薪资相同的员工信息 SELECT * FROM emp WHERE (job, salary) IN (SELECT job, salary FROM emp WHERE name = '鹿杖客' OR name = '宋远桥');

事务

事务简介

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

事务操作

数据准备

create table account( id int auto_increment primary key comment '主键ID', name varchar(10) comment '姓名', money int comment '余额' ) comment '账户表'; insert into account(id, name, money) values (null, '张三', 2000), (null, '李四', 2000);
转账操作

1.查询张三账户余额: select money from account where name = '张三';

2.将张三账户余额减去1000: update account set money = money - 1000 where name = '张三';

此处可以插入一个异常

3.将李四账户余额加上1000: update account set money = money + 1000 where name = '李四';

查看/设置事务提交方式:
- select @@autocomit; 查看事务提交方式,1是自动，0是手动
- set @@autocommit = 0;
提交事务
- commit;
回滚事务
- rollback;
开启事务
- start transaction 或 begin;

事务四大特性

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

并发事务问题

脏读:一个事务读到另外一个事务还没有提交的数据。
不可重复读:一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读:一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”

事务隔离级别

事务隔离级别越高，数据越安全，但是性能越低。

查看事务隔离级别:select @@transaction_isolation;
设置事务隔离级别:set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable};