[TOC] ## **数据库设计** **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的主键值  <br /> ### **数据库** * 查看所有数据库 ``` 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; ``` <br /> ## **函数** ### **内置函数** **字符串函数** * 拼接字符串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'); ``` <br /> ### **数学函数** * 求四舍五入值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:做出一个从学生中抽奖的功能 <br /> ### **日期时间函数** * 当前日期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'); ``` <br /> ## **索引** * 思考：在图书馆中是如何找到一本书的？ * 一般的应用系统对比数据库的读写比例在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%,所以也不要太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引 <br /> ## **外键** **外键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; ``` > 在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率