A股上市公司传智教育(股票代码 003032)旗下技术交流社区北京昌平校区

 找回密码
 加入黑马

QQ登录

只需一步,快速开始

[学习交流] 冯光

© 卞建彬 中级黑马   /  2018-10-18 19:48  /  798 人查看  /  0 人回复  /   0 人收藏 转载请遵从CC协议 禁止商业使用本文

1.数据库概述
数据库概念:用于存储和管理数据的仓库
数据库特点:
1.持久化存储数据
2.方便存储和管理数据
3.使用了统一的方式(SQL)操作数据库
2.MySQL数据库
数据库的打开和关闭
net start mysql
net stop mysql
数据库的登录和退出
mysql -uroot -proot  登录
mysql --host=ip地址 -uroot -p目标密码
Exit quit 退出
数据库的软件和数据库、数据表、表记录的关系
1.现有数据库软件
2.数据库的软件上,可以创建多个数据库(文件夹)
3.数据库中可以创建多个数据表(文件)
4.数据表中有表记录(数据)
3.SQL指令(重点)
3.1 Sql概述
Sql Structured Query Language:结构化查询语言
就是一种操作所有关系型数据库的规则
Sql的通用语法:
单行注释:-- 注释内容 #注释内容
多行注释:/* 注释内容*/
3.2 sql 常见的分类:常见分类有4
DDL:数据库定义语言
作用:给创建数据库和数据表以及修改数据库和数据表的
DCL 数据库控制语言
作用:给数据库和数据表进行授权操作
DML 数据库操作语言
作用:对表中的记录(数据)进行添加、修改、删除操作
DQL:数据库查询语言
作用:对表中的记录(数据)进行查询操作
3.3 sql操作数据库和表
使用DDL创建、修改数据库和数据表(CRUD
Create :创建        Retrieve :查询
Update:修改         Delete:删除
查询所有数据库的名称
Show databases
查看某个数据库中的字符集和创建语法
Show Create database 数据库名称;
判断数据库是否存在,不存在则创建
Create database if not exists 数据库名称;
创建指定的字符集的数据库
Create database 数据库名称 character set 字符集名称;
判断数据库是否存在,不存在则创建,并指定数据库的字符集
Create database if not exists 数据库名称 character set 字符集名称;
修改指定数据库的字符集:
Alter database 数据库名称 character set 字符集名称;
删除数据库
Drop database 数据库名称;
在删除数据库之前先判断其是否存在,存在则删除
Drop database if exists 数据库名称;
使用数据库
查询当前正在使用的数据库:
Select database();
Use 数据库名称;使用数据库
操作表
查询指定数据库中的所有表的名称
Show tables
查询表结构
Desc 表名称;
创建表
Create table 表名(1 1数据类型,2 2数据类型, ..n n数据类型);
Int 整数类型 eg: age int,
Double 小数类型 eg : score double(5,2); 5代表该数共有5,小数点后边有2
Date 日期类型 只包含年月日  yyyy-MM-dd
Datetime 日期类型 包含年月日时分秒 yyyy-MM-dd HH-mm-ss
Timestamp 时间撮类型 : 包含年月日时分秒
如果不给该字段赋值,那么将使用当前系统时间自动赋值
Varchar 字符串类型 eg: name varchar(32) :括号中的数字代表字符串的最大长度是32
创建表
Create table 表名(
Age int,
Name varchar(20),
Score double(5,2),
Birthday date,
Insert_time timestamp
);
删除表
Drop table 表名;
删除之前先判断表是否存在,存在则删除
Drop table if exists 表名;
复制表
Create table 表名 like 被复制的表名
修改表名
Alter table 表名 rename to 新的表名;
修改表的字符集
Alter table 表名 character set 字符集名称;
表中添加一列:
Alter table 表名 add 列名 数据类型;
修改列名和数据类型
Alter table 表名 change 旧列名 新列名 数据类型
Alter table 表名 modify 列名 新数据类型
删除列
Alter table 表名 drop 列名;
DML 操作表记录(添加insert into , 修改 update , 删除 delete)
添加数据
Insert into 表名(列名1,列名2,..列名n) values(1,2,..n);
删除数据
Delete from 表名 [where 条件];
修改数据
Update 表名 set 列名1 = 1,列名2 = 2,...列名n=n[where 条件];
DQL数据库查询语言 : 查询表记录
查询所有表记录
Select * from 表名;
查询多字段
Select 字段1,字段2,..字段n from 表名;
去除重复的结果集
Select distinct 字段名 from 表名;
两列之间的四则运算
Select 1,2,1 + 2 as 新列名 from 表名;  -- 新列名 是列1 + 2 生成的新列的名字
Ifnull(表达式1,表达式2) : 表达式1代表可能出现的null值的列,表达式2代表替换null之后的值
Day03-mysql
1.单表查询
1.0 模糊查询
Like
占位符_ : 代表单个任意字符
占位符%: 代表多个任意字符
查找表中指定字段以张字开头的数据
Select * from 表名 where 字段名 like ‘张%;
查找表中指定字段第二个字是张的数据
Select * from 表名 where 字段名 like _%;
查找表中指定字段包含汉字是张的数据
Select * from 表名 where 字段名 like %%;
1.1条件查询
Where 子句后跟条件
运算符 <   > <=  >=  =    !=   <>
Between.. and :..之间 eg : 20 30之间  Between  20 and  30;
In(集合) : 小括号中可以填写多个值 eg :in(18,22,25);
Is null : 判断值是null
And &&
Or ||
Not !
1.2 排序查询
语法 : order by 字段1排序方式1,字段2 排序方式2...
使用方式:
Select * from 表名 order by 字段名 排序方式;
注意:如果没有写排序方式,则默认按照升序(asc)排序;如果有多个条件进行排序,则当前边条件值一样时,才会进行后边条件判断
降序排序:desc;
1.3 分组查询
基本语法: group by 字段
Select 分组字段,聚合字段
from 表名
Where 分组之前的条件判断
Group by 分组字段
Having 分组后的条件判断/聚合字段
Order by 字段 排序方式; 对分组之后的数据进行排序
Wherehaving之间的区别: where是在分组前进行判断,having是在分组之后进行判断
Where后不能使用聚合函数,having后边可以写聚合函数
1.4 分页查询
分页:数据太多,一次性把数据查询出来后,数据展示效果不好,用户体验不好
分页操作:不同数据库的分页操作是不相同的
Mysql: 使用limit进行分页,其他数据库不能使用数据库进行分页
语法:
Limit 参数1,参数2;
参数1:数据的起始位置(索引),0开始,代表第一条数据
参数2:代表每页要显示的数据的个数
参数1 = (参数2 - 1 ) * 参数2;
1.5 聚合函数查询
Sum(字段) : 求和
Avg(字段) : 计算平均值
Count(字段) :计算个数
Max(字段) : 计算最大值
Min(字段) : 计算最小值
注意: 聚合函数中的参数是表的字段
聚合函数在进行运算时,null值不参与运算
聚合函数一般对int类型进行计算,其他类型计算无意义
在使用count(字段) ,字段一般是主键字段(非空唯一)
Select sum(ifnull(null值的字段,0)) from 表名;
1.6 综合案例
2. 约束
1. 概述: 对表中的数据进行限定, 保证数据的的正确性, 有效性和完整性
一种规范(限制), 数据库约束就是对表中的数据进行规范, 让数据具有某种特点
2. 分类:
1. 主键约束 : primary key
2. 非空约束: not null
3. 唯一约束: unique
4. 外键约束: foreign key
* 非空约束: not null
1. 创建表时添加约束
CREATE TABLE 表名(
         idINT,
         NAMEVARCHAR(20) NOT NULL
);
2. 创建表完后, 添加非空约束
ALTER TABLE 表名 MODIFY NAME VARCHAR(20) NOT NULL;
3. 创建表完后, 去除非空约束
ALTER TABLE表名 MODIFY NAME VARCHAR(20);
* 唯一约束: unique, 某一列的值不能重复
1. 注意事项:
唯一约束可以有NULL, 但是只能有一条记录为null
2. 在创建表时, 添加唯一约束
CREATE TABLE 表名(
         idINT,
         phone_numberVARCHAR(20) UNIQUE -- 添加唯一约束
);
3. 删除唯一约束
ALTER TABLE 表名 DROP INDEX phone_number;
4. 在表创建完后, 添加唯一约束
ALTER TABLE 表名 MODIFY phone_number VARCHAR(20) UNIQUE;
* 主键约束: primary key
1. 注意事项:
1. 含义: 非空且唯一
2. 一张表只能有一个字段主键
3. 主键就是表中记录的唯一
2. 在创建表时, 添加主键
CREATE TABLE 表名(
         idINT PRIMARY KEY,-- id添加主键约束
         NAMEVARCHAR(20)
);
3. -- 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
4. -- 创建完表后, 添加主键
ALTER TABLE 表名 MODIFY id INT PRIMARY KEY;
5. 自动增长:
概念: 如果某一列是数值类型的, 使用 auto_increment 可以来完成值的自动增长
-- 创建表时添加自动增长
CREATE TABLE 表名(
         idINT PRIMARY KEY AUTO_INCREMENT,-- id添加主键约束
         NAMEVARCHAR(20)
);
-- 删除自动增长
ALTER TABLE 表名 MODIFY id INT;
-- 添加自动增长
ALTER TABLE 表名 MODIFY id INT AUTO_INCREMENT;
* 外键约束 : foreign key , 让表与表产生关系, 从而保证数据的正确性
1. 在创建表时, 添加外键
语法:
create table 表名 {
. . .
Constraint 外键名称 foreign key (外键列的名称)references 主表名称(主表列名称)
};
2. 删除外键:
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
3. 添加外键:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES 主表名称(主表列名称)
4. 外键的特点:
1. 外键的值不能随意写
2. 外键的值引用于另一张表的主键值
3. 外键的值可以为null , 但是为null没有任何意义
5. 外键的设计原则
主表
子表
6. 级联操作 : 只有表与表之间有关联关系, 才能进行级联操作
作用: 当主表的数据发生变化时, 那么字表的数据会级联发生变化
级联操作: 一般情况下, 操作是对修改(update)和删除(delete)
分两种情况:
1. 当主表的数据变化时, 子表的数据保持一致 on updatecascade  on update cascade
2. 当主表的数据变化时, 子表的数据(外键的数据) 设置为null
语法: on update set null     ondelete set null
1. 添加级联操作
         语法:ALTER TABLE 表名 ADDCONSTRAINT 外键名称
                           OREIGNKEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADEON DELETE CASCADE  ;
2. 分类:
         1.级联更新:ON UPDATE CASCADE
         2.级联删除:ON DELETE CASCADE
3.多表关联
多表之间的关系
1. 一对一 : (了解, 很少存在)
: 人和身份证的关系
分析: 一个人只有一个身份证, 一个身份证只能对应一个人
: 用户表 信息表
主表(子表)可以是任意一方
2. 一对多(多对一) :
: 部门表 员工表
分析: 一个部门有多个员工, 一个员工只能对应一个部门
主表: 一方表, 这里值的就是部门表
子表: 多方表, 这里指的就是员工表
外键设计在子表里面
3. 多对多: (通俗的来说, 是强化版的一对一)
: 学生表 课程表
分析: 一个学生可以选择很多门课程, 一个课程也可以被很多学生选择
主表(子表) 可以是任意一方 (不推荐这样区分和设计外键)
数据库设计的范式
1. 概念: 设计数据库时, 需要遵循的一些规范, 要遵循后边的范式要求, 必须先遵循前边的所有
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
2. 分类:
第一范式(1NF):每一列都是不可分割的原子数据项
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)
几个概念:
函数依赖: A-->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称B依赖于A
例如:学号-->姓名。  (学号,课程名称) --> 分数
完全函数依赖:A-->B如果A是一个属性组,则B属性值得确定需要依赖于A属性组中所有的属性值。
例如:(学号,课程名称) --> 分数
部分函数依赖:A-->B 如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值即可。
例如:(学号,课程名称) -- > 姓名
传递函数依赖:A-->B, B -->C . 如果通过A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值可以确定唯一C属性的值,则称 C 传递函数依赖于A
例如:学号-->系名,系名-->系主任
码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
例如:该表中码为:(学号,课程名称)
                                             *主属性:码属性组中的所有属性
                                             *非主属性:除过码属性组的属性
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
数据库的备份与还原
1. 命令行:
语法:
备份 : mysqldump -u用户名 -p密码 数据库名 > 保存路径
还原 :  
1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 执行文件。source 文件路径
2. 图形化工具:
4.数据库的设计范式
Mysql 多表查询和事务
1.多表查询(掌握)
多表查询语法
Select
列名列表
From
表明列表
Where....
Eg: select * from 1,2;
笛卡尔积:有两个集合A,B,取这两个集合的所有组成情况.
要完成多表查询,需要消除无用的数据
消除不合理的数据(无用的数据)多表查询分类:
内连接查询:获取有连接关系的两个表的交集
隐式内连接:使用where条件消除无用的数据
-- 查询所有员工信息和对应的部门信息
Select * from emp,dept where emp.dept_id =dept.id;
-- 查询员工姓名,性别和部门的名称
Select
t1.name,
T1.gender,
T2.name
From
1  t1,
2  t2,
Where
T1.dept_id = t2.id;
显式内连接
语法: select 字段列名 from 1 [inner] join 2 on 条件
消除无效的数据条件:外键的值 = 主键的值 (外键的值引用了主键的值)
-- 查询 员工和部们的信息
SELECT
         *
FROM emp,dept
WHERE emp.dept_id = dept.id;
-- 查询 员工姓名,员工的部门编号 ,部门的部门编号,部门名称
SELECT
         t1.id,
         t1.NAME,
         t2.id,
         t2.NAME
FROM
         empt1,
         deptt2
WHERE t1.dept_id = t2.id;
-- 查询 入职日期早于2011年的员工姓名 ,以及所在的部门编号,部门名称
SELECT
         t1.NAME,
         t2.id,
         t2.NAME
FROM
         empt1,
         deptt2
WHERE  t1.join_date < '2011-01-01'
AND
         t1.dept_id= t2.id;
-- 查询 不是开发部的员工姓名   部门名称
SELECT
         t1.NAME,
         t2.NAME
FROM
         empt1,
         deptt2
WHERE  
         t2.NAME!= '开发部'
AND         
         t1.dept_id= t2.id;
-- 查询 没有部门的员工姓名和部门编号
SELECT
         t1.NAME,
         t1.dept_id
FROM
         empt1
WHERE  
         t1.dept_idIS NULL;
-- 查询 有部门的员工姓名,部门编号 部门名称
SELECT
         t1.NAME,
         t1.dept_id,
         t2.NAME
FROM
         empt1,
         deptt2
WHERE  
         t1.dept_idIS NOT NULL
AND
         t1.dept_id= t2.id;
外连接查询
左外连接语法:select * from1 left [outer] join 2 on 条件
左表:1 右表:2
查询表1的所有数据以及和表2交集的部分
查询表中所有
查询的是左表所有数据以及其交集的部分
右外连接语法:select * from1 right [outer] join 2 on 条件
左表:1 右表:2
查询的是表2所有的数据以及和表1交集的部分
子查询
概念:查询中嵌套查询,称嵌套查询为子查询
子查询分类情况
第一种情况:子查询作为一个值(单行单列)
-- 查询员工工资小于平均工资的人
Select * from 表名 where (select avg(sal) from 表名);
第二种情况:子查询作为一个数组(多行单列)
-- 查询员工薪水中排名前2的员工信息
Eg:select * from 表名 where salary in(select salary from 表名 order by desc );
第三种情况:子查询作为一个表(多行多列),用在from后边
Eg:-- 查询出部门编号、部门名称、部门位置、部门人数
SELECT
         e.dept_id,COUNT(e.id)
FROM      empe
GROUP BY e.dept_id;  -- 查询每个部门的人数
SELECT
         *
FROM      deptd ,(SELECT
                                    e.dept_id ,
COUNT(e.id) total
                                    FROM      empe
                                    GROUP BY e.dept_id) t2
WHERE d.id = t2.dept_id;
2.事务(了解)
事务的基本介绍
概念:如果一个包含多个步骤的业务操作被事务管理,这些操作要么同时成功,要么同时失败
操作:
开启事务 start transaction
回滚 rollback : 把数据恢复到初始状态
提交事务 commit : 把数据持久保存到表中
mysql数据库中,事务默认自动提交
一条DML(增删改)语句会自动提交一次事务
事务提交的两种方式
手动提交:需要先开启事务在提交
Oracle数据库默认是手动提交事务
自动提交:mysql数据库中,事务默认自动提交,一条DML(增删改)语句会自动提交一次事务
修改事务的默认提交方式: set@@autocommit = 0;
查看事务的默认提交方式:select@@autocommit
1代表自动提交,0代表手动提交
事务的四大特征
原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
持久性:当事务提交或回滚后,数据库会持久化保存数据
隔离性:多个事务之间相互独立
一致性:事务操作前后,数据总量不变
事务的隔离级别
概念:多个事务之间隔离的,相互独立的,如果多个事务操作同一批数据,会引发一些问题,设置不同的隔离级别就可以解决这些问题
存在问题:
1.脏读:一个事务读取到另一个事务中没有提交的数据
2.不可重复读(虚读):在同一个事务中,两次读取到的数据不一样
3.幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
隔离级别:
1.read uncommitted : 读未提交
产生的问题:脏读  不可重复读(虚读)   幻读
2.Read committed : 读已提交
产生的问题:不可重复读(虚读)   幻读  解决脏读
3.Repeatable read : 可重复读
产生的问题: 幻读            解决脏读和不可重复读
4.Serializable :串行(xing)
                  可以解决所有的问题
注意:隔离级别从小到大安全性越来越高,效率越来越低
数据库查询隔离级别
Select @@ tx_isolation
数据库设置隔离级别
Set global transaction isolation level 级别字符串

0 个回复

您需要登录后才可以回帖 登录 | 加入黑马