Navicat操作数据库2（微进阶）

数据库设计

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的主键值

数据库

• 查看所有数据库

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;

函数

内置函数

字符串函数

• 拼接字符串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();

案例1:实现0-10之间的随机数 案例2:做出一个从学生中抽奖的功能

日期时间函数

• 当前日期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');

索引

• 思考：在图书馆中是如何找到一本书的？
• 一般的应用系统对比数据库的读写比例在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;

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