数据库基础
一、数据库的基本概念
1.什么是数据库(DataBase)
用于存储和管理数据的仓库,简称DB
2.数据库的特点***
1)持久化存储数据的(硬盘).实际上数据库就是一个文件系统
2)方便存储和管理数据(对数据进行增删改查)
-DBMS(数据库管理系统)
MySQL--->很多数据库--->很多表--->很多列--->具体数据
3)使用了统一的方式操作数据库--SQL
多种DBMS通用的语法--SQL
3.常见的数据库软件
MySQL 默认端口是3306
Oracle
db2
二、MySQL数据库-端口号3306
1.MySQL数据库的安装
详见操作手册
2.MySQL建议用360卸载
详见操作手册
3.配置
*查看状态
1.win+R services.msc
2.netstat -ano | findStr"3306"
*开启服务(管理员身份运行cmd)
net start mysql
*关闭服务(管理员身份运行cmd)
net stop mysql
4.MySQL的登陆和退出
1)本地mysql
*MySQL数据库的登陆(本地)
-mysql -u+用户名 -p
输入密码(此时密码是密文显示)
-或者mysqul -u+用户名 -p+密码
-当前用户密码 mysql -uroot -proot
*MySQL数据库的退出
-exit/quit
2)远程登陆
*MySQL数据库的登陆(远程)
mysql -h+ip -u目标的用户名 -p目标的密码
*退出
-exit/quit
5.MySQL的目录结构(了解)
1)MySQL安装目录
my.ini是配置文件,可以修改
2)MySQL数据目录
在my.ini可以查看到
#Path to the database root
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
3)几个概念:
数据库-文件夹
表-文件
列-表的一部分,相当于表头
数据-存储的数据
6.SQL语法*****
1)什么是SQL?--Structure Query Language:结构化查询语言
它定义了操作所有关系型数据库(Relational DataBase)的规则
2)SQL的通用语法
*SQL语句可以单行或多行书写,以分号结尾
*可以使用空格和缩进来增强语句的可读性
*MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
*3种注释
-单行注释: -- 注释 或 # 注释
-多行注释: /* 注释 */
注意:空格一定要打, #注释是MySQL的特有语法,可以不用空格,建议使用通用语法
3)SQL的分类*****面试可能会用到
*DDL 数据定义语言-Data Definition Language
操作数据库的语言:对数据库和表的增删改查
*DML 数据操纵语言-Data Manipulation Language
增删改表中的数据
*DQL 数据查询语言-Data Query Language
查询表中的数据
*DCL 数据控制语言-Data Control Language
对用户权限的设置
7.DDL:操作数据库、表 - 以文件夹SQL语句内容为主
1)操作数据库:CRUD
*C(Create):创建 CREATE
SELECT DATABASE();
*R(Retrieve):查询 SHOW
*U(Update):修改 ALTER
*D(Delete):删除 DROP
*使用数据库:USE 数据库名称; 不是USE DATABASE 数据库名称
2)操作表:CRUD
*C(Create):创建 CREATE
-CREATE TABLE 表名(
列名1 数据类型1,
列名2 数据类型2,
...
列名N 数据类型N
)
注意:最后一列,不需要加逗号.
-数据库类型:
**int 整数类型
**double 小数类型 double(5,2)表示长度5,小数点后保留两位
**date 日期,只包含年月日, yyyy-MM-dd;
datetime 日期,包含年月日时分秒,yyyy-MM-dd HH:mm:ss
timestamp:时间戳类型:包含年月日时分秒 *****和datatime的区别,面试
如果不给这个字段赋值,默认为当前系统时间
**字符串*****面试题
varchar:字符串 varchar(20) 表示最大20个字符
char:char(长度)
text:定义不需要给长度,保存超大文本/和文本文件
注意:char和varchar在定义的时候必须给长度 varchar(30),char(20)
char和varchar的区别:如果存入长度小于给定长度,char用空格补满
而varchar的长度根据输入字符串内容改变
-复制表 CREATE TABLE stu LIKE student;
*R(Retrieve):查询 SHOW TABLES/DESC(descraption) 表名
SHOW CREATE TABLE
*U(Update):修改 ALTER
-修改表名 ALTER TABLE 表名 RENAME TO 新的表名
RENAME TABLE 表名 TO 新表名
-修改表的字符集 ALTER TABLE 表名 CHARACTER SET 字符集名
-添加一列 ALTER TABLE 表名 ADD 列名 数据类型;
-修改一列 ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
ALTER TABLE 表名 MODIFY 列名 新数据类型;
-删除列: ALTER TABLE DROP 列名;
*D(Delete):删除 DROP
8.DML:增删改表中的数据
1)添加数据:
*语法:
INSERT INTO 表名(列名1,列名2,...,列名N) VALUES(值1,值2,...,值N),(值1,值2,...,值N)
*注意:
列名和数据要一一对应
如果表名后不写内容,则给所有列添加值
除了数字类型,其他类型需要使用引号引起来,单双引号都可以
2)删除数据
*语法
DELETE FROM 表名 WHERE 条件;
TRUNCATE TABLE 表名;
*注意
如果不加条件,则删除表中所有记录,如果要删除所有数据,建议使用TRUNCATE TABLE,效率高
truncate先删除表,再创建一张一模一样的表,效率高,推荐使用
*TRUNCATE 和 DELETE FROM 的区别*****面试题
--DELETE属于DML(可以通过事务进行还原),TRUNCATE属于DDL(不能通过事务进行欢颜)
3)修改数据
*语法
UPDATE 表名 SET 列名 = 值,列名2=值2 WHERE 条件
*注意:
如果不加条件,表内的所有数据都会被修改
9.DQL:查询表中的记录
*SELECT * from 表名;
SQL查询语句
一、DQL查询语句
1.基本查询
*IFNULL(字段,替换的值) 字段的值为NULL则改为的替换的值
* 字段 AS 别名 可以给字段写别名
AS可以省略,直接谢伟字段 别名
*公式!******
SELECT 列名 FROM 表名
WHERE 条件
GROUP BY 列名 HAVING 条件
ORDER BY ASC/DEDC
LIMIT 索引,条数
2.条件查询
*运算符
-<>不等,=等于,!=不等(不建议)
-BETWEEN 值 AND 值 是闭区间
-IN(集合)
-LIKE 模糊查询 %任意多个字符,_一个字符
-&& || ! 建议用AND 和 OR NOT
3.排序查询
*语法:
ORDER BY 排序字段1 排序方式1,排序字段2 排序方式2
*排序方式:
ASC:升序,默认的 Ascending
DESC:降序 Descending
*注意:
如果有多个排序规则,只有当前一个规则生效后后一个规则才会生效
4.聚合函数->将一列数据作为一个整体,进行纵向计算
1)COUNT:计算个数
-一般选择非空的列来计算(主键)
-COUNT(*) 也可以避免自动排除NULL的问题,但不推荐使用
2)MAX:计算最大值 数字/日期
3)MIN:计算最小值 数字/日期
4)SUM:求和 数字
5)AVG:计算平均值 数字
6)语法:
SELECT 聚合函数(字段) FROM 表名称
7)注意:聚合函数一般都是配合分组使用
注:聚合函数的计算会排除NULL值
5.分组查询
*语法:
GROUP BY 分组字段
*注意:
分组之后查询的字段必须为分组字段或聚合函数
*WHERE和HAVING的区别-******面试题可能考
-WHERE在分组之前进行限定,如果不满足条件,则不参与分组;HAVING在分组之后进行限定,如果不满足条件,则不会被查询出来
-WHERE后不可以跟聚合函数,HAVING可以进行聚合函数的判断
6.分页查询
*语法:
LIMIT 开始的索引,每页查询的条数
*公式:
开始的索引 = (页码-1)*每页显示的条数
*注意:
LIMIT语法是一个"方言",只能在MySQL中使用
二、约束
1.约束的概念
对表中的数据进行限定,保证数据的准确性、有效性和完整性
2.约束的分类
1)主键约束:PRIMARY KEY [AUTO_INCREMENT]
2)非空约束:NOT NULL
3)唯一约束:UNIQUE
4)外键约束:FOREIGN KEY
3.非空约束:NIT NULL
*语法-创建约束
CREATE TABLE 表名(字段 值 约束);
ALTER TABLE 表名 MODIFY 字段 数据类型 约束
当后面的约束为空时,可以认为改为没有约束
4.唯一约束:UNIQUE
*注意:
唯一约束可以有NULL值,但只能有一个
唯一约束必须用DROP INDEX字段来删除
-ALTER TABLE 表名 DROP INDEX 列名
如果当前表中有唯一约束,那么添加不了唯一约束
5.主键约束:PRIMARY KEY*****
*语法:PRIMARY KEY AUTO_INCREMENT;
*注意:
-主键约束要求主键非空且唯一
-一张表中只有一个字段为主键
-主键就是表中记录的唯一标识
*AUTO_INCREMENT-自动增长
如果某一列是数值类型的,使用AUTO_INCREMENT可以来完成值的自动增长
*主键的删除:
ALTER TABLE 表名称 DROP PRIMARY KEY;
6.外键约束:FOREIGN KEY*****
1)在创建表时,可以添加外键
*语法:
CREATE TABLE 表名 (
...
CONSTRAINT 外键名称 FOREIGN KEY (从表列名) REFERENCES 主表(主表主键) -- CONSTRAINT 外键名称可以省略
);
-主表:被依托的表
-从表:谁有外键谁就是从表
*删除外键
ALTER TABLE 从表名 DROP FOREIGN KEY 外键名
*创建表之后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名) REFERENCES 主表名称(主表列名称)
ALTER TABLE 表名 ADD FOREIGNKEY (外键列名) REFERENCES 主表名称(主表列名称)
CONSTRAINT 外键名称可以省略
2)级联操作(了解)-记住关键字CASCADE即可
*添加级联操作
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY(外键字段名称) REFERENCES 主表(主表列名称)
ON UPDATE CASE ON DELETE CASE;
*级联操作:
ON UPDATE CASCADE
ON DELETE CASCADE;
*级联操作比较危险,效率也不高,尽量慎重使用
三、数据库的设计
1.多表之间的关系
1)一对一(了解)
2)一对多(多对一)
如:部门和员工
分析:一个部门又多个员工,一个员工只能对应一个部门
3)多对多
如:学生和课程
分析:一个学生可以有多个课程,一个课程也可以有很多学生
2.多表关系的实现
1)一对一
在其中一方创建一个外键列(值不能重复),指向另一方的主键列
2)一对多(多对一)
在多的一方创建外键列,指向一的一方主键列
3)多对多
创建一个中间表,至少有两列,创建外键分别指向2个主表的主键
*联合主键:中间表中的主键对不重复
PRIMARY KEY(主键1,主键2)
3.数据库设计的范式(当前了解即可)-以下所有笔记应结合视频理解
1)概念:设计数据库时,要尊徐一些规范.
要遵循后边的范式要求,必须先遵循前边的所有范式要求
2)分类-第1/2/3/巴斯-科德/4/5范式,当前只学习123范式
*第一范式(1NF)
每一列都是不可分割的原子数据项
*第二范式(2NF)
在1NF的基础上,非码属性必须完全依赖于候选码
(在1NF基础上消除非主属性对主码的部分函数依赖)
*第三范式(3NF)
在2NF的基础上,任何非主属性不依赖于其他非主属性
(在2NF基础上消除传递依赖)
3)第一范式:1NF
*原子数据项:仅有一列,没有子列
数据库创建表时必然满足第一范式
*第一范式存在的问题:
-存在非常严重的数据冗余
-数据添加存在问题
-数据删除存在问题
4)第二范式:2NF
*几个概念:
-函数依赖: A-->B,如果通过A的属性(或属性组)的值可以确定唯一B属性的值
-完全函数依赖:A-->B,如果A是一个属性组,则B属性值的确定需要依赖A属性组中所有的属性值
-部分函数依赖:A-->B,如果A是一个属性组,则B属性值的确定需要依赖A属性组中部分的属性值
-传递函数依赖:A-->B, B-->C
如果通过A的属性(或属性组)的值可以确定唯一B属性的值,再通过B的属性可以确定C属性的值
-码:如果在一张表中,如果一个属性或属性组被其他所有属性组完全依赖,则称此属性或属性组为该表的码
**主属性:码属性组中的所有属性
**非主属性:除了码属性组的属性
*第二范式存在的问题:
-数据添加存在问题
-数据删除存在问题
5)第三范式:3NF
解决了传递依赖
不存在主要问题
4.数据库的备份和还原
详见 MYSQL语句-重置密码&备份还原数据库,一般用不着
5.总结公式:
SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 条件 ORDER BY ASC/DESC LIMIT index,rows
CONSTRAINT 外键名称 FOREIGN KEY (从表列名) REFERENCES 主表(主表主键)
多表
一、多表查询
1.多表查询的分类
*内连接查询
*外连接查询
*子查询
*笛卡尔积:
集合A,集合B,区多表中数据的所有组合情况
要完成多表查询,需要消除笛卡尔积中无用的数据
2.内连接查询:
1)隐式内连接:使用WHERE消除无用数据
*语法:
SELECT 字段 FROM 表1,表2 WHERE 关联条件 AND 普通条件
*例子:
SELECT t1.name, -- 注释
t1.gender,-- 注释
t2.name -- 注释
FROM
emp t1, -- 注释
dept t2 -- 注释
WHERE
t1.dept_id = t2.id;
2)显式内连接
*语法:SELECT 字段 FROM 表1
[INNER] JOIN 表2 ON 关联条件
[INNER] JOIN 表3 ON 关联条件
WHERE 普通条件
-INNER 是可选操作,可以不写
*例子:
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
3)内连接查询的思维逻辑
*从哪些表中查询数据
*查询条件是什么
*查询哪些字段
4)内连接查询的是左右两表的交集部分****
3.外链接查询
1)左外连接
*语法:SELECT 字段 FROM 表1
LEFT [OUTER] JOIN 表2 ON 关联条件
LEFT [OUTER] JOIN 表3 ON 关联条件
WHERE 普通条件
*左外连接查询的是左表所有数据及左右表交集部分****
2)右外连接
*语法:SELECT 字段 FROM 表1
RIGHT [OUTER] JOIN 表2 ON 条件
RIGHT [OUTER] JOIN 表3 ON 条件
WHERE 普通条件
*左外连接查询的是右表所有数据及左右表交集部分****
3)内连接与外链接的区别****
-内连接查询的是连接的表的交集
-外链接查询的是左(右)表的所有数据及与连接的表的交集
4.子查询
*概念:查询中嵌套查询,称嵌套的查询为子查询
*例:
SELECT *FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);
*子查询的不同情况
-标量子查询:
子查询可以作为条件,使用运算符去判断
例:查询工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
-列子查询:子查询的结果是N行1列
子查询可以作为条件,使用操作符去判断
例:查询部门是财务部或市场部的人
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN('财务部','市场部'));
-行子查询:子查询的结果是1行N列
-子查询的结果是多行多列的:
子查询可以作为虚拟表,使用内/外连接去查询
例:查询员工的入职日期是2011-11-11日之后的员工信息
SELECT *
FROM (SELECT * FROM emp WHERE join_date > '2011-11-11') t1, dept t2
WHERE t1.dept_id = t2.id;
二、事务****面试用
1.事务的基本介绍
1)概念:
如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败
2)操作
*开启事务 START TRANSACTION
*回滚 ROLL BACK --如果操作的结果和预期不符,可以使用该语句回滚
*提交 COMMIT --如果操作的结果和预期相符,可以用该语句提交事务
3)MySQL数据库中事务默认自动提交
*如果没有手动开启事务,一条增删改语句会默认开启并提交一次事务
*提交方式
-自动提交:
*MySQL数据库默认是自动提交事务
-手动提交:
*Oracle 数据库默认是手动提交事务
*手动开启事务,手动提交事务
-修改事务的默认提交方式
--查看:SELECT @@autocommit
--修改:SET @@autocommit = 0;
2.事务的四大特征*****面试题
1)原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
2)持久性:当事务提交或回滚后,数据库会持久化的保持数据
3)隔离性:多个事务之间相互独立
4)一致性:事务操作前后,数据的总量不变
3.事务的隔离级别
1)概念:多个事务之间是隔离的,相互独立的,但是如果多个事务操作同一批数据,则会引发一些问题
2)存在问题
*脏读:一个事务,读取到另一个事务中没有提交的数据
*虚读:指一个的事务读取到了另外一个事务中提交的insert的数据
*不可重复读:指一个的事务读取到了另外一个事务中提交的UPDATE的数据
*幻读:一个事务操作(DML)数据表中的所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
3)隔离级别****面试题
*read uncommitted:读未提交
-产生的问题:脏读,虚读,不可重复读,幻读
*read committed:读已提交 (Oracle默认)
-产生问题:不可重复读,幻读
*repeatable read:可重复读(MySQL默认)
-产生的问题:幻读
*serializable:串行化
-可以解决所有问题
*注意:隔离级别从小到大安全性越来越高,但是效率越来越低
*设置隔离级别
-SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别 |
|