黑马程序员技术交流社区

标题: 冯光 [打印本页]

作者: 卞建彬 时间: 2018-10-18 19:48
标题: 冯光

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 模糊查询

占位符_ : 代表单个任意字符

占位符%: 代表多个任意字符

查找表中指定字段以张字开头的数据

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 字段排序方式; 对分组之后的数据进行排序

Where和having之间的区别: 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 * from表1 left [outer] join 表2 on 条件

左表:表1 右表:表2

查询表1的所有数据以及和表2交集的部分

查询表中所有

查询的是左表所有数据以及其交集的部分

右外连接语法:select * from表1 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 级别字符串

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2