数据库知识点
1、sql的分类
分类 | 说明 |
---|---|
数据定义语言 | 简称DDL(Data Definition Language),用来定义数据库对象:数据库,表,列等。 |
数据操作语言 | 简称DML(Data Mainpulation Language) ,用来对数据库表的记录进行更新 |
数据查询语言 | 简称DQL(Data Query Language),用来查询数据库表的记录 |
数据控制语言 | 简称DQL(Daact Control Language),用来定义数据的访问权限和安全级别。及创建用户 |
2、数据库的基本表信息
3、操作数据库表
3.1、创建表的注意事项
3.2、基本语法格式
-- 选择要使用的数据库
use test;
-- 创建分类表
CREATE TABLE category(
cid INT,
cname VARCHAR(20)
);
-- 展示该库下的所有表
show DATABASES;
-- 创建测试表
CREATE TABLE test1(
tid INT,
tdate DATE
);
-- 复制表结构
CREATE TABLE test2 LIKE test1;
-- 查看表结构
DESC test2;
-- 查看当前数据库中所有数据表名
SHOW TABLES;
-- 查看创建表的sql语句
SHOW CREATE TABLE category;
1.表的删除
-- 表的删除
drop table test1;
-- 使用先判断后删除的方式
drop table if EXISTS test2;
2.表的修改
-- 修改表的名称 语法格式:rename table 就表名 to 新表名
RENAME TABLE category to category1;
-- 修改表的字符集为dbk 语法格式:alter table 表名 CHARACTER set 字符集
ALTER TABLE category1 CHARACTER set gbk;
-- 向表中添加一个字段 关键字:add
ALTER TABLE category1 ADD cdesc VARCHAR(20);
-- 修改表中列的类型或者长度
ALTER TABLE category1 MODIFY cdesc INT(8);
-- 修改列的名字
ALTER TABLE category1 change cdesc description VARCHAR(30);
-- 删除列
ALTER TABLE category1 DROP description;
**注意事项:**删除数据的两种方式
- delete from 表;不推荐,对表中的数据进行逐条删除,效率低
- truncate table 表;推荐,删除整张表,在创建一个一模一样的新表
- where与having的区别:where 在分组前进行过滤 where后不能跟聚合函数 | having是在分组后进行条件过滤的 having后可以写聚合函数
3.3、 创建外键约束
插入错误数据
创建表之后添加外键:
外键约束的注意事项:
- 从表的外键类型必须与主表的类型保持一致
- 添加数据时,应该先添加主表的数据
- 删除数据时,应该先删除从表中的数据
3.4、删除外键约束
语法格式:alter table 从表 drop foreign key 外键约束的名称
--删除employee表中的外键
alter table employee drop foreign key emp_dept_fk
3.5、多对多的建表原则
-- 创建省表 主表 一的一方
CREATE TABLE province(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
description VARCHAR(20)
);
-- 创建市表 从表 多的一方 外键字段指向主表的主键
CREATE TABLE city(
cid INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
description VARCHAR(20),
pid INT,
FOREIGN KEY(pid) REFERENCES province(id)
);
4、sql关键字
4.1、limit
语法格式:select 字段 from 表名 limit offset,length;
参数说明:
- offset:起始行数 默认从0开始计数
- length:要返回的行数,也就是查询几条数据
分页公式:起始行数 = (当前页码-1) * 每页显示条数
5、约束
约束 | 特点 | 作用 |
---|---|---|
主键约束 | 不可重复,唯一,非空 | 用来表示数据库中的每一条记录 |
非空约束 | 某一列不允许为空 | |
唯一约束 | 表中的某一列不能够重复(对null值,不做唯一判断) |
- 创建一个带有主键的表
create table emp2(
eid int,
ename vachar(20),
sex char(1),
);
# 通过DDL语句添加主键约束
alter table emp2 add primary key(eid);
# 通过DDL语句删除主键
alter table emp2 drop primary key;
# 查看表结构
DESC emp2
主键自增的设置,起始值从1开始
非空约束的创建
唯一约束
主键约束和唯一约束的区别
- 主键约束:他是唯一且不能为空的
- 唯一约束:唯一,但是可以为空
- 一个表中只能有一个主键,但是可以有多个唯一约束
默认值赋值
6、数据库的事务
6.1、事务的概念
事务是一个由一条或者多条SQL组成的一个整体,事务中的操作,要么全部成功,要么全部失败
6.2、mysql事务操作
一、手动提交事务
- 开启事务:start transaction;或者 begin;
- 提交事务:commit;
- 回滚事务:rollback;
二、自动提交事务
mysql默认的提交方式,自动提交事务
每执行一条DML语句,都是一个单独的事务
6.3、事务的四大特性(ACID)
- 原子性:每个事务都是一个整体,不可以在拆分,事务中的所有sql要么都执行成功,要么都执行失败
- 一致性:事务在执行之前数据库的状态,与事务执行之后的状态要保持一致
- 隔离性:事务与事务之间不应该相互影响,执行时要保证隔离状态
- 持久性:一旦事务执行成功,对数据的修改是持久的
6.4、并发访问的问题
- 脏读:一个事务读取到另外一个事务没有提交的数据
- 不可重复读:一个事务中,两次读取的数据不一样
- 幻读:一个事务中,一次查询的结果,无法支撑后续业务操作
设置隔离级别
- read uncomitted:读未提交----可以防止哪些问题:无
- read committed:读已提交----可以防止:脏读(oracle默认的隔离级别)
- repeatable read:可重复读----可以防止:脏读,不可重复读(mysql默认的隔离级别)
- serializble:串行化----脏读,不可重复度,幻读
注意:隔离级别从小到大,安全性是越来越高的,但是效率是越来越低的,根据不同的情况选择对应的隔离级别
7、级联删除
概要:指的是在删除主表的同时,可以删除与之相关的从表中的数据,语法 on delete cascade
8、多表查询的分类
8.1、子查询分类
form型的子查询:
exists型的子查询
子查询的结果是单列多行,作为父查询的in函数中的条件使用
语法格式:select 字段名 from 表名 where 字段 in (子查询);
子查询总结:
- 子查询如果是一个字段(单列),那么就在where后做条件判断
- 如果是多个字段(多列)就当做一张表使用(要起别名)
9、数据库三大范式
- 第一范式(1NF): 列既有原子性,设计列要做到不可拆分
- 第二范式(2NF): 在满足第一范式的基础上,进一步满足更多的规范。一张表只能描述一个事情。
- 第三范式(3NF): 消除传递依赖。表中的信息如果能够推导出来,就不要设计一个字段来单独的记录。
**反三范式:**指的是通过增加冗余或者重复数据 来提高数据库的读性能,浪费空间,节约查询的时间(以空间换时间)
**冗余字段:**某一个字段,属于这一张表,但是,他又在多张表中都有出现
总结:
- 尽量根据三范式的设计规则去设计数据库。
- 可以合理的加冗余字段,减少join操作,让数据库执行的更快。
10、索引
10.1、索引概念
我们可以通过对数据表中的字段创建索引,来提高查询速度
10.2、常见的索引的分类
- 主键索引(primary key)主键是一个唯一性的索引,每个表中只能有一个主键
- 唯一索引(unique)索引列的所有数据只能出现一次,必须是唯一
- 普通索引(index)最常见的索引,作用就是提高对数据的访问速度
表对应的索引被保存到一个索引文件中,如果对表进行增删改查操作,那么mysql就会对这个索引文件进行更新,导致查询效率变低。
10.3、索引的创建和删除
主键索引创建
唯一索引创建
普通索引的创建方式:
删除索引
索引的总结:
11、视图
视图是由一个查询结果形成的虚拟表
11.1、视图的作用
操作视图,相当于操作一张只读表
12、存储过程
创建存储过程的方式一:
存储过程创建的方式二:
存储过程创建方式三:获取存储过程的返回值
13、触发器
当我们执行一条sql语句的时候,这条sql语句的执行会自动去触发执行其他的sql语句。
创建触发器
14、DCL(数据控制语句)
创建用户
用户的授权操作
查看用户权限