数据库高级操作看这里,测试人员不需要会,但是得知道

数据库设计
E-R模型
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的主键值
想一想:举些例子,满足一对一、一对多、多对多的对应关系
连接服务端
另一种方式,打开cmd程序,进入到mysql安装目录的bin目录下
1、进入mysql的bin目录

 cd C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin


2、连接mysql

 mysql -uroot -p


数据库
查看所有数据库

 show databases;


使用数据库

 use 数据库名;


查看当前使用的数据库

 select database();


创建数据库

 create database 数据库名 charset=utf8; 例: create database ceshi charset=utf8;


删除数据库

 drop database 数据库名; 例: drop database ceshi;


数据表
查看当前数据库中所有表

 show tables;


查看表结构

 desc 表名;


查看表的创建语句

 show create table 表名; 例: show create table students;


备份
以管理员身份运行cmd程序
运行mysqldump命令

 cd C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin mysqldump –uroot –p 数据库名 > ceshi.sql


# 按提示输入mysql的密码
恢复
先创建新的数据库

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


# 根据提示输入mysql密码
内置函数
字符串函数
拼接字符串concat(str1,str2...)

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


案例:体现类似"王昭君的家乡是北京"的功能.
包含字符个数length(str)

 select length('abc');


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

 left(str,len)返回字符串str的左端len个字符 right(str,len)返回字符串str的右端len个字符 substring(str,pos,len)返回字符串str的位置pos起len个字符 select substring('abc123',2,3);


去除空格

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

 大小写转换,函数如下 lower(str) upper(str) select lower('aBcD');


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

 select round(1.6);


求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');


流程控制
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;


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

 自定义函数 创建 语法如下 delimiter $$ create function 函数名称(参数列表) returns 返回类型 begin sql语句 end $$ delimiter ;


说明:delimiter用于设置分割符,默认为分号
在“sql语句”部分编写的语句需要以分号结尾,此时回车会直接执行,所以要创建存储过程前需
要指定其它符号作为分割符,此处使用//,也可以使用其它字符
示例
要求:创建函数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 ')


存储过程
存储过程,也翻译为存储程序,是一条或者多条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,
不需要重复编译
减少网络交互,减少网络访问流量
视图
对于复杂的查询,在多个地方被使用,如果需求发生了改变,需要更改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;


事务
为什么要有事务
事务广泛的运用于订单系统、银行系统等多种场景
例如: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;


索引
思考:在图书馆中是如何找到一本书的?
一般的应用系统对比数据库的读写比例在10:1左右,而且插入操作和更新操作很少出现性能问
题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询语句的优化显然
是重中之重
当数据库中数据量很大时,查找数据会变得很慢
优化方案:索引
语法
查看索引

 show index from 表名;


创建索引
方式一:建表时创建索引

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


方式二:对于已经存在的表,添加索引
如果指定字段是字符串,需要指定长度,建议长度与定义字段时的长度一致
字段类型如果不是字符串,可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度))
例:

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


删除索引:
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;


缺点
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE
和DELETE,因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件
但是,在互联网应用中,查询的语句远远大于增删改的语句,甚至可以占到80%~90%,所以也不要
太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引
外键foreign key
如果一个实体的某个字段指向另一个实体的主键,就称为外键。被指向的实体,称之为主实体
(主表),也叫父实体(父表)。负责指向的实体,称之为从实体(从表),也叫子实体(子
表)
对关系字段进行约束,当为从表中的关系字段填写值时,会到关联的主表中查询此值是否存
在,如果存在则填写成功,如果不存在则填写失败并报错
语法
查看外键
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;


在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率
修改密码
使用root登录,修改mysql数据库的user表
使用password()函数进行密码加密
注意修改完成后需要刷新权限
use mysql;

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


例:

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


刷新权限:flush privileges;

 忘记 root 账户密码怎么办 1、配置mysql登录时不需要密码,修改配置文件 Centos中:配置文件位置为/data/server/mysql/my.cnf Windows中:配置文件位置为C:\Program Files (x86)\MySQL\MySQL Server 5.1\my.ini 修改,找到mysqld,在它的下一行,添加skip-grant-tables [mysqld] skip-grant-tables 2、重启mysql,免密码登录,修改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删除,重启