MySQL扩展知识

目录

数据库设计E-R模型：

命令行操作：

字符串函数：

 去除空格：

大小写转换：

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

 流程控制：

 存储过程和函数：

视图： 

 事务：

 索引：

外键：

 修改密码：

忘记密码：

---

数据库设计E-R模型：

1. E-R模型的基本元素是：实体、联系和属性；
2. E表示entry，实体：一个数据对象，描述具有相同特征的事物；
3. R表示relationship，联系：表示一个或多个实体之间的关联关系，关系的类型包括一对一（不经常使用的数据来维护关系）、一对多（多的一方来维护关系）、多对多（创建一个中间表来维护关系）；
4. 属性：实体的某一特性称为属性。

命令行操作：

• 连接MySQL：

mysql -u root -p

• 显示数据库：

show databases;

• 使用数据库：

use 数据库名

• 中文乱码： 

set charest gbk;

• 查询数据库：  

•  查询当前使用的数据库：

•  创建数据库：

•  删除数据库：

•  查看表：

•  查看表结构：

•  查看创建表的语句：

字符串函数：

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

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

•  包含字符个数length(str)：

select length('abc');

一个中文长度是3，其他的字符都是1：

•  截取字符串：

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

 去除空格：

1. ltrim(str)返回删除了左空格的字符串str；
2. rtrim(str)返回删除了右空格的字符串str。 

大小写转换：

lower(str)；
upper(str)。

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

select rand();

随机0-10的整数：

 随机从一个表中取一条记录：

 流程控制：

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;

 存储过程和函数：

1. 存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合；
2. 存储过程和函数都是一次编译,就会被缓存起来,下次使用就直接命中缓存中已经编译好的 sql，不需要重复编；
3. 减少网络交互,减少网络访问流量。

视图： 

1. 对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，则需要在多个地方进行修改，维护起来非常麻烦；
2. 解决：定义视图；
3. 视图本质就是对查询的封装；
4. 定义视图，建议以v_开头。

create view 视图名称 as select语句;

 事务：

        是一个操作序列，这些操作要么都执行，要么都不执行，有一条命令错误，事务就执行不成功。

1. 开始：begin；
2. 所有操作都成功：commit；
3. 任何一步失败：rollback。

 索引：

• 查看索引：

show index from 表名;

• 创建索引：

1. 建表时创建索引：

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

2. 对于已经存在的表，添加索引：如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致，字段类型如果不是字符串，可以不填写长度部分。

   create index 索引名称 on 表名(字段名称(长度))
   例：
   create index age_index on create_index(age);
   create index name_index on create_index(name(10));

•  删除索引 ：

drop index 索引名称 on 表名;

• 缺点：

1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件；

2. 但是,在互联网应用中,查询的语句远远大于增删改的语句,甚至可以占到80%~90%,所以也不要太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引。

外键：

        控制一个表的数值必须在另一个表的数值范围之类。

• 查看外键：

show create table 表名

• 设置外键约束： 

1. 创建数据表的时候设置外键约束：

   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 主表(主表字段)
   

2.  对于已经存在的数据表设置外键约束：被约束的表叫做从表，约束别人的表叫做主表。

   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;

 修改密码：

update user set password=password('***')
-- 刷新权限，修改的密码才会生效
flush privileges

忘记密码：

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='用户名';
   刷新权限：flush privileges;
3. 还原配置文件，把刚才添加的skip-grant-tables删除，重启.