一、数据库的基本概念
1、什么是数据库:数据库(DataBase) 就是存储和管理数据的仓库。其本质是一个文件系统, 还是以文件的方式,将数据保存在电脑上。
2、为什么使用数据库:
二、SQL
1、SQL的分类
2、SQL的基本语法
①、新增
创建数据库:
方式一(DDL):create database 数据库名;
方式二(DDL):create database 数据库名 character set 字符集;
创建表(DDL):
CREATE TABLE 表名(
字段名称1 字段类型(长度),
字段名称2 字段类型
....
);
插入数据:
方式一向指定字段名中插入数据(DML):
insert into 表名 (字段名1,字段名2...) values(字段值1,字段值2...);
方式二向所有字段中插入数据可以不写字段名(DML):
insert into 表名 values(字段值1,字段值2...);
②、删除
删除数据库(DDL):drop database 数据库名;
删除表(DDL):drop table 表名;
删除列(DDL):alter table 表名 drop 列名;
删除数据(DML):delete from 表名
指定条件删除数据(DML):delete from 表名 [where 字段名 = 值]
删除表后再创建空表(DML):truncate table 表名;
③、修改
修改数据库字符集(DDL):alter database 数据库名 character set 字符集;
修改表的字符集(DDL):alter table 表名 character set 字符集;
修改表名(DDL):rename table 旧表名 to 新表名;
向表中添加列(DDL):alert table 表名 add 字段名称 字段类型;
修改表中列的数据类型或长度(DDL):alter table 表名 modify 字段名称 字段类型
修改列名称(DDL):alter table 表名 change 旧列名 新列名 类型(长度);
修改数据(DML):update 表名 set 列名 = 值;
带条件修改数据(DML):update 表名 set 列名 = 值 where 字段名 = 值
④、查询
查看正在使用的数据库:select database();
查看所有数据库:show databases;
查看数据库定义信息:show create database 数据库名;
查看当前数据库中所有表名:show tables;
查看表的结构:desc 表名;
查询数据:select 列名 from 表名 where 条件表达式 ORDER BY 字段名 ASC / DESC
3、聚合函数
①、常用的聚合函数
②、分组查询:
SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 HAVING 条件;
③、where与having的区别:
④、limit关键字(分页查询)
SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;
4、SQL约束
①、常见约束
②、语法
主键约束:字段名 字段类型 primary key;
删除主键约束:ALTER TABLE 表名 DROP PRIMARY KEY;
非空约束:字段名 字段类型 not null;
唯一约束:字段名 字段类型 unique;
外键约束(建表时添加):CONSTRAINT 外键约束名称 FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
外键约束(已有表添加):ALTER TABLE 从表 ADD CONSTRAINT 外键约束名称 FOREIGN KEY (外键字段名) REFERENCES
删除外键约束:alter table 从表 drop foreign key 外键约束名称
5、多表查询
①、隐式内连接查询:SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;
②、显式内连接查询:SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件;
③、左外连接查询:SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件;
④、右外连接查询:SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
6、MySQL索引
建表时添加主键索引:字段名 类型 PRIMARY KEY
修改表结构添加主键索引:ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )
建表时添加唯一索引:UNIQUE [索引名称] (列名)
修改表结构添加唯一索引:create unique index 索引名 on 表名(列名(长度))
在已有表上创建普通索引:create index 索引名 on 表名(列名[长度])
修改表结构添加普通索引:ALTER TABLE 表名 ADD INDEX 索引名 (列名)
删除索引:ALTER TABLE table_name DROP INDEX index_name;
索引的优点:大大的提高查询速度 ;可以显著的减少查询中分组和排序的时间。
索引的缺点:创建索引和维护索引需要时间,而且数据量越大时间越长 ;当对表中的数据进行增加,修改,删除的时候,索引也要同时进行维护,降低了数据的维护速度。
总结:添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。
三、数据库事务
1、什么是事务:事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一 条SQL出现异常,整个操作就会回滚,整个业务执行失败。
2、语法格式
3、手动提交事务流程:
4、自动提交事务:MySQL 默认每一条 DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,语句执 行完毕 自动提交事务,MySQL 默认开始自动提交事务。
查看自动提交事务是否开启:SHOW VARIABLES LIKE 'autocommit';
取消自动提交事务:SET @@autocommit=off;
5、事务的四大特性:
6、事务的隔离级别:
查看隔离级别:select @@tx_isolation;
设置隔离级别:set global transaction isolation level 级别名称;
三、数据库设计
1、数据库三范式(空间最少)
①、第一范式:原子性, 做到列不可拆分 第一范式是最基本的范式。数据库表里面字段都是单一属性的,不可再分, 如果数据表中每个 字段都是不可再分的最小数据单元,则满足第一范式。
②、第二范式:在第一范式的基础上更进一步,目标是确保表中的每列都和主键相关。 一张表只能描述一件事.
③、第三范式:消除传递依赖表的信息,如果能够被推导出来,就不应该单独的设计一个字段来存放
2、反三范式:反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能 浪费存储空间,节省查询时间 (以空间换时间)。
四、存储过程
1、什么是存储过程:存储过程其实就是一堆 SQL 语句的合并。中间加入了一些逻辑控制。
2、存储过程的优缺点
优点:存储过程一旦调试完成后,就可以稳定运行,(前提是,业务需求要相对稳定,没有变化) 存储过程减少业务系统与数据库的交互,降低耦合,数据库交互更加快捷(应用服务器,与 数据库服务器不在同一个地区)
缺点:在互联网行业中,大量使用MySQL,MySQL的存储过程与Oracle的相比较弱,所以较少使 用,并且互联网行业需求变化较快也是原因之一 尽量在简单的逻辑中使用,存储过程移植十分困难,数据库集群环境,保证各个库之间存储 过程变更一致也十分困难。
3、语法格式:
方式一:
①、创建存储过程:
DELIMITER $$ -- 声明语句结束符,可以自定义 一般使用$$
CREATE PROCEDURE 过程名称() -- 声明存储过程
BEGIN -- 开始编写存储过程
-- 要执行的操作
END $$ -- 存储过程结束
②、 调用存储过程:
call 存储过程名
方式二:
①、创建存储过程:
DELIMITER $$ -- 声明语句结束符,可以自定义 一般使用$$
CREATE PROCEDURE 存储过程名称(IN 参数名 参数类型) -- 声明存储过程
BEGIN -- 开始编写存储过程
-- 要执行的操作
END $$ -- 存储过程结束
②、调用存储过程:
call 存储过程名
方式三:
①、创建存储过程:
DELIMITER $$ -- 声明语句结束符,可以自定义 一般使用$$
CREATE PROCEDURE 存储过程名称(IN 参数名 参数类型,OUT 变量名 数据类型) -- 声明存储过程
BEGIN -- 开始编写存储过程
-- 要执行的操作
SET @变量名=值
END $$ -- 存储过程结束
②、调用存储过程:
call 存储过程名