数据库04

本篇目录

1.数据库设计

2.连接服务端

3.数据库

4.数据表

5.备份与恢复

6.内置函数

7.流程控制

8.自定义函数

9.存储过程

10.视图

11.事务

12.索引

13.外键foreign key

14.修改密码

高级

本节课程学习MySQL数据库的高级操作，不需要熟练操作，这部分内容作为了解，对于未来的工作是加分项

1.数据库设计

E-R模型

本节课程学习MySQL数据库的高级操作，不需要熟练操作，这部分内容作为了解，对于未来的工作是加分项

E-R模型的基本元素是：实体、联系和属性

E表示entry，实体：一个数据对象，描述具有相同特征的事物
R表示relationship，联系：表示一个或多个实体之间的关联关系，关系的类型包括包括一对一、一对多、多对多
属性：实体的某一特性称为属性

关系也是一种数据，需要通过一个字段存储在表中

1、实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值

2、实体A对实体B为1对多：在表B中创建一个字段，存储表A的主键值

3、实体A对实体B为多对多：新建一张表C，这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值

总结（哪个表维护关系）：

01：一对一

关系字段设置在不经常使用的那张表中

02：一对多

关系字段设置在多的那张表中

03：多对多

关系字段设置在第三张表中

2.连接服务端

第一种方式，打开cmd程序，进入到mysql安装目录的bin目录下

1、进入mysql的bin目录 cd C:Program Files (x86)MySQLMySQL Server 5.1in

2、连接mysql mysql -uroot -p

第二种方式：更方便

点击下面的client

输入密码：123

3.数据库

查看所有数据库

show databases;

使用数据库

use  数据库名;

查看当前使用的数据库

select database();

创建数据库

create database 数据库名 charset=utf8;

例：

create database ceshi charset=utf8;

删除数据库

drop database 数据库名;

例：

drop database ceshi;

在windows中文乱码

set charset gbk;

GBK编码：是指中国的中文字符，其它它包含了简体中文与繁体中文字符，

另外还有一种字符“gb2312”，这种字符仅能存储简体中文字符。

UTF-8编码：它是一种全国家通过的一种编码，如果你的网站涉及到多个国家的语言，那么建议你选择UTF-8编码

4.数据表

查看当前数据库中所有表

show tables;

查看表结构

desc 表名;

查看表的创建语句

show create table 表名;

例：

show create table students;

5.备份与恢复

备份

以管理员身份运行cmd程序

运行mysqldump命令

cd C:Program Files (x86)MySQLMySQL Server 5.1in

mysqldump –uroot –p 数据库名 > ceshi.sql

按提示输入mysql的密码

恢复

先创建新的数据库

mysql -uroot –p 新数据库名 < ceshi.sql

根据提示输入mysql密码

6.内置函数

字符串函数

拼接字符串：concat(str1,str2...)

select concat(12,34,'ab');

案例:体现类似"王昭君的家乡是北京"的功能.

select name,hometown,concat(name,'的家乡是',hometown) from students;

包含字符个数：length(str)

select length('abc');

length 中文3个，其他1个。

案例:查找班级里边名字为两个字的所有学生信息

select * from students where LENGTH(name)=6;

截取字符串

left(str,len)返回字符串str的左端len个字符
right(str,len)返回字符串str的右端len个字符
substring(str,pos,len)返回字符串str的位置pos起len个字符

select substring('abc123',2,3);

案例:实现王昭君叫王某某,张飞叫张某某的功能

select concat(left(name,1),'某某') from students;

substring是从1开始的。

去除空格

ltrim(str)返回删除了左空格的字符串str
rtrim(str)返回删除了右空格的字符串str

select ltrim(' bar ');

案例:实现左右空格都去掉的功能

select RTRIM(ltrim(' bar '));

大小写转换，函数如下

lower(str)
upper(str)

select lower('aBcD');

数学函数

求四舍五入值round(n,d)，n表示原数，d表示小数位置，默认为0

select round(1.6);

求0-10的数

select round(RAND()*10)

在一张表中随机取出一条数据，随机点名

select name from students ORDER BY RAND() LIMIT 1;

求x的y次幂pow(x,y)

select pow(2,3);

获取圆周率PI()

select PI();

随机数rand()，值为0-1.0的浮点数

select rand();

日期时间函数

当前日期：current_date()

select current_date();

当前时间：current_time()

select current_time();

当前日期时间:now()

select now();

日期格式化：date_format(date,format)

参数format可选值如下

%Y 获取年，返回完整年份
%y 获取年，返回简写年份
%m 获取月，返回月份
%d 获取日，返回天值
%H 获取时，返回24进制的小时数
%h 获取时，返回12进制的小时数
%i 获取分，返回分钟数
%s 获取秒，返回秒数

例：将使用-拼接的日期转换为使用空格拼接

select date_format('2016-12-21','%Y %m %d');

7.流程控制

case语法：等值判断

说明：当值等于某个比较值的时候，对应的结果会被返回；如果所有的比较值都不相等则返回

else的结果；如果没有else并且所有比较值都不相等则返回null

case 值 when 比较值1 then 结果1 when 比较值2 then 结果2 ... else 结果 end

select case 1 when 1 then 'one' when 2 then 'two' else 'zero' end as result;

案例:做出一个女同学称为美女,男同学称为帅哥的小功能

select name,sex,
case sex 
when '男' then CONCAT(LEFT(name,1),'先生')
when '女' then CONCAT(LEFT(name,1),'女士') 
end as result
from students
;

8.自定义函数

创建

语法如下

delimiter $$
create function 函数名称(参数列表) returns 返回类型
begin
sql语句
end
$$
delimiter ;

说明：delimiter用于设置分割符，默认为分号

在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符，此处使用$$，也可以使用其它字符

在navicat上，有分号敲回车没关系。在命令行客户端里，有分号敲回车就会执行语句。

所以要用delimiter更改分号。

要求：创建函数my_trim，用于删除字符串左右两侧的空格

step1：设置分割符

delimiter $$

step2：创建函数

create function my_trim(str varchar(100)) returns varchar(100)
begin
return ltrim(rtrim(str));
end
$$

step3：还原分割符

delimiter ;

使用自定义函数

select ' abc ',my_trim(' abc ')

调用函数: select 函数名

红指可以看视图

绿指可以看函数和存储过程

9.存储过程

存储过程，也翻译为存储程序，是一条或者多条SQL语句的集合

创建

语法如下

delimiter //
create procedure 存储过程名称(参数列表)
begin
sql语句
end
//
delimiter ;

说明：delimiter用于设置分割符，默认为分号

在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符，此处使用//，也可以使用其它字符

示例

要求：创建查询过程，查询学生信息

step1：设置分割符

delimiter //

step2：创建存储过程

create procedure proc_stu()
begin
select * from students;
end
//

step3：还原分割符

delimiter;

调用

语法如下

call 存储过程(参数列表);

调用存储过程proc_stu

call proc_stu();

存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合.

存储过程和函数都是一次编译,就会被缓存起来,下次使用就直接命中缓存中已经编译好的 sql不需要重复编译

减少网络交互,减少网络访问流量，隐藏字段，比较安全。

存储过程现在使用的不多了。

10.视图（重要）

对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，则需要在多个地方进行修改，维护起来非常麻烦

解决：定义视图

视图本质就是对查询的封装

定义视图，建议以v_开头

create view 视图名称 as select语句;

例：创建视图，查询学生对应的成绩信息

create view v_stu_score_course as
select
 stu.*,cs.courseNo,cs.name courseName,sc.score
from
 students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on cs.courseNo = sc.courseNo

查看视图：查看表会将所有的视图也列出来

show tables;

删除视图

drop view 视图名称;

例：

drop view v_stu_score_course;

使用：视图的用途就是查询

select * from v_stu_score_course;

理解：视图可以当作表，但不是真正的表。

11.事务（重要）

为什么要有事务

事务广泛的运用于订单系统、银行系统等多种场景

例如：A用户和B用户是银行的储户，现在A要给B转账500元，那么需要做以下几件事：

1. 检查A的账户余额>500元；
2. A 账户中扣除500元;
3. B 账户中增加500元;

正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。那如果A账户扣了钱之后，

系统出故障了呢？A白白损失了500，而B也没有收到本该属于他的500。以上的案例中，隐藏着一个前提条件：A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此

所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性

事务命令

要求：表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎

查看表的创建语句，可以看到engine=innodb

show create table students;

修改数据的命令会触发事务，包括insert、update、delete

开启事务，命令如下

开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

begin;

提交事务，命令如下

将缓存中的数据变更维护到物理表中

commit;

回滚事务，命令如下：

放弃缓存中变更的数据

rollback;

提交

为了演示效果，需要打开两个命令行窗口，使用同一个数据库，操作同一张表

step1：连接

命令行1：查询学生信息

select * from students;

step2：增加数据

命令行2：开启事务，插入数据

begin;
insert into students(studentNo,name) values ('013','我是新来的');

命令行2：查询数据，此时有新增的数据

select * from students;

step3：查询

命令行1：查询数据，发现并没有新增的数据

select * from students;

step4：提交

命令行2：完成提交

commit;

step5：查询

命令行1：查询，发现有新增的数据

select * from students;

回滚

为了演示效果，需要打开两个命令行窗口，使用同一个数据库，操作同一张表

step1：连接

命令行1

select * from students;

step2：增加数据

命令行2：开启事务，插入数据

begin;
insert into students(studentNo,name) values ('014','又来一个);

命令行2：查询数据，此时有新增的数据

select * from students;

step3：查询

命令行1：查询数据，发现并没有新增的数据

select * from students;

step4：回滚

命令行2：完成回滚

rollback;

step5：查询

命令行1：查询数据，发现没有新增的数据

select * from students;

12.索引

思考：在图书馆中是如何找到一本书的？

一般的应用系统对比数据库的读写比例在10:1左右，而且插入操作和更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重

当数据库中数据量很大时，查找数据会变得很慢

优化方案：索引

语法

查看索引

show index from 表名;

红色的是索引的名字

紫色的索引分别作用到的字段

创建索引

方式一：建表时创建索引

create table create_index(
id int primary key,
name varchar(10) unique,
age int,
key (age)
);

总结：

primary key 可以创建索引

unique 可以创建索引

key() 可以创建索引

方式二：对于已经存在的表，添加索引

如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致字段类型如果不是字符串，可以不填写长度部分

create index 索引名称 on 表名(字段名称(长度))

例：

create index age_index on create_index(age);

create index name_index on create_index(name(10));

理解：

给哪个字段添加索引，查询where的时候就用该字段，就会使用索引，增加查询速度。

删除索引：

drop index 索引名称 on 表名;

示例

导入测试表test_index

右键点击某个数据库->运行sql文件->选择test_index.sql->点击开始

查询

开启运行时间监测：

set profiling=1;

查找第1万条数据test10000

select * from test_index where title='test10000';

查看执行的时间：

show profiles;

为表title_index的title列创建索引：

create index title_index on test_index(title(10));

执行查询语句

select * from test_index where title='test10000';

再次查看执行的时间

show profiles;

另一种方法：分析查询

explain
select * from test_index where title='test10000';

key:是索引的名字。

extra:using where ,使用了where; using index,使用了索引

rows:1行就查询到了

缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件但是,在互联网应用中,查询的语句远远大于增删改的语句,甚至可以占到80%~90%,所以也不要太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引

13.外键foreign key

如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体

（主表），也叫父实体（父表）。负责指向的实体，称之为从实体（从表），也叫子实体（子表）

对关系字段进行约束，当为从表中的关系字段填写值时，会到关联的主表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并报错

上图理解：

上图：class表，是主表。

下图：stu表，是从表。class_id指向class表的id字段，填写class_id时，会被限制。如下图所示。

语法

查看外键

show create table 表名

设置外键约束

方式一：创建数据表的时候设置外键约束

create table class(
 id int unsigned primary key auto_increment,
 name varchar(10)
);

create table stu(
 name varchar(10),
 class_id int unsigned,
 foreign key(class_id) references class(id)
);

foreign key(自己的字段) references 主表(主表字段)

方式二：对于已经存在的数据表设置外键约束

alter table 从表名 add foreign key (从表字段) references 主表名(主表字段);

alter table stu add foreign key (class_id) references class(id);

删除外键

需要先获取外键约束名称

show create table stu;

红指：外键的名称

绿指：全拷贝一下，粘贴到上面查看有无外键，有的话看外键名称。

获取名称之后就可以根据名称来删除外键约束

alter table 表名 drop foreign key 外键名称;

alter table stu drop foreign key stu_ibfk_1;

在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率

14.修改密码

使用root登录，修改mysql数据库的user表

使用password()函数进行密码加密
注意修改完成后需要刷新权限
可以使用百度搜索：md5解密

use mysql;

update user set password=password('新密码') where user='用户名';

update user set password=password('123') where user='root';

刷新权限：flush privileges;

忘记 root 账户密码怎么办

配置mysql登录时不需要密码，修改配置文件

Centos中：配置文件位置为/data/server/mysql/my.cnf

Windows中：配置文件位置为C:Program Files (x86)MySQLMySQL Server 5.1my.ini

修改，找到mysqld，在它的下一行，添加skip-grant-tables

[mysqld]
skip-grant-tables

2、重启mysql服务器

01.右击计算机选择管理

02.选择服务和应该程序，如图画红线二选一，双击左键

03.双击服务选项

04.然后选择所需要开启或关闭的服务

05.点击所需服务，然后就所选服务进行操作

免密码登录，（可以在命令行中进行，也可以在navicat上登陆）

01.在命令行上操作

cmd

cd C:Program Files (x86)MySQLMySQL Server 5.1in

mysql -uroot  回车

如果在mysql中，直接exit退出，执行：

mysql -uroot  回车

02.navicat上操作

单击local:关闭连接，编辑连接。就会有下图。把密码删了，点击确定即可

这里把密码再改回来。

修改mysql数据库的user表

use mysql;

update user set password=password('新密码') where user='用户名';

例：

update user set password=password('123') where user='root';

刷新权限：flush privileges;

3、还原配置文件，把刚才添加的skip-grant-tables删除，重启