软件测试：数据库笔记（3） 高级

高级

 命令行客户端
 
 连接
  mysql -uroot -p 回车,输入密码
 查看所有仓库
  show databases;
 使用某个仓库（重要）
  use ceshi;
 查看所有的表
 show tables;
 查看表结构
 desc students;
 show create table students;
 中文乱码
 set charset gbk;
 
 函数
 
 01  concatt 连接字符串
 例：select name,hometown,concat(name,'的家乡是',hometown) from students
 02  一个中文长度是3，其他都是1
 例：select * from students where length(name)=9
 03  向左取字符串
 select left('abc',2)   向左取两位，结果是ab
 04  向右取字符串
 select right('abc',2)     向右取字符串，结果是bc
 05   任意位置取字符串
 select substring('abc',1,2)      结果是ab
 select name,sex,concat(left(name,1),'某某') from students
 06   去除左边空格
 select ltrim('  abc  ')
 07   去除右边空格
 select rtrim('  abc  ')
 08   去除左右两边空格
 select trim('  abc  ')
 09  转为小写字母
 select lower(abCD)
 10  转为大写字母
 select upper(abCD)
 11  保留小数点位数
 select round(1.61,1)
 12  随机0-10的整数
 select round(rand()*10)
 13  随即从一个表取一条记录
 select * from 表名 order by rand() limit 1
 14  当前日期
 select current_date()
 15  当前时间
 select current_time()
 16  当前日期和时间
 select now()
 17  日期格式化
 date_format(date,format)
 参数format可选值如下
%Y 获取年，返回完整年份
%y 获取年，返回简写年份 
%m 获取月，返回月份
%d 获取日，返回天值
%H 获取时，返回24进制的小时数
%h 获取时，返回12进制的小时数 
%i 获取分，返回分钟数
%s 获取秒，返回秒数
例：select date_format(now(),'%y/%m/%d')
显示结果：19/07/02

流程控制语句
 
case语句
例：
 select name,sex,
 case sex 
 when '男'
 then concat(left(name,1),'帅比')
 when '女'
 then concat(left(name,1),'美眉')
 else concat(left(name,1),'xx')
 end as res
 from students
 
自定义函数
 
 delimiter $$ 
 create function 函数名称(参数列表) 
 returns 返回类型 
 begin sql语句 
 end $$ 
 delimiter ;
 
视图
 
 例：
 creat view v_student as
 select name 姓名,age 年龄 from students
 select * from v_student
 
事务
 
 命令行客户端操作如下
 begin;
 select * from students where name in ('大乔','小乔')
 update students set age=age-5 where name='大乔';
 update students set age=age+5 where name='小乔';
 commit;
 
 begin;
 所有操作成功
 commit;
 
 begin;
 任何一步操作失败
 rollback;   注：恢复到原始状态
 
索引
 查看索引
 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删除，重启