java036
子查询
可以将子查询放在许多的 SQL 子句中,包括:
WHERE 子句、HAVING 子句 、FROM 子句
使用子查询的原则
1)子查询放在圆括号中。
2)将子查询放在比较条件的右边。
3)在单行子查询中用单行运算符,在多行子查询中用多行运算符。
MySQL 中的正则表达式
1)MySQL 中允许使用正则表达式定义字符串的搜索条件,性能要高于 like。
2)MySQL 中的正则表达式可以对整数类型或者字符类型检索。
3)使用 regexp 关键字表示正则匹配。
4)默认忽略大小写,如果要区分大小写,使用 binary 关键字 。
正则表达式的模式及其含义
索引
索引作用:MySQL 索引的建立对于 MySQL 的高效运行是很重要的,索引可以大大提高 MySQL的检索速度。
MySQL 中的索引类型
普通索引 、 唯一索引 、主键索引 、组合索引 、全文索引
普通索引
1)是最基本的索引,它没有任何限制。
2)在创建索引时,可以指定索引长度。length 为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度,如果是 BLOB 和 TEXT 类型,必须指定 length。
创建索引时需要注意:
如果指定单列索引长度,length 必须小于这个字段所允许的最大字符个数。
查询索引:SHOW INDEX FROM 索引名;
直接创建索引
create index 索引名 on 表名(列名);
示例:
需求:为 emp3 表中的 name 创建一个索引,索引名为 emp3_name_index
create index emp3_name_index on emp3(name);
修改表添加索引
alter table表名 add index 索引名 (列命(length));
示例:
需求:修改 emp3 表,为 addrees 列添加索引,索引名为 emp3_address_index
alter table emp3 add index emp3_address_index(address);
删除索引
drop index 索引名 on 表名;
示例:
需求:删除 mep3 表中索引名为 emp3_address_index 的索引
drop index emp3_address_index on emp3 ;
用户管理
使用语句创建用户:
create user 用户名 identified by '密码';
查看用户:
select user,nost from 用户名;
分配权限:
grant 限权 on 数据库.表 to 用户名@登录主机 identified by '密码';
新用户创建完后是无法登陆的,需要分配权限。
GRANT 权限 ON 数据库.表 TO 用户名@登录主机 IDENTIFIED BY "密码"
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password'
登陆主机:
% 匹配所有主机 ``
localhost localhost 不会被解析成 IP 地址,直接通过 UNIXsocket 连接
127.0.0.1 会通过 TCP/IP 协议连接,并且只能在本机访问;
::1 ::1 就是兼容支持 ipv6 的,表示同 ipv4 的 127.0.0.1
ALTER: 修改表和索引。
CREATE: 创建数据库和表。
DELETE: 删除表中已有的记录。
DROP: 删除数据库和表。
INDEX: 创建或删除索引。
INSERT: 向表中插入新行。
SELECT: 检索表中的记录。
UPDATE: 修改现存表记录。FILE: 读或写服务器上的文件。
PROCESS: 查看服务器中执行的线程信息或杀死线程。
RELOAD: 重载授权表或清空日志、主机缓存或表缓存。
SHUTDOWN: 关闭服务器。
ALL: 所有权限,ALL PRIVILEGES 同义词。
USAGE: 特殊的 "无权限" 权限
删除用户:
drop user 用户名@登录主机;
MySQL 分页查询
MySQL使用 limit 关键字实现分页。
limit 偏离量,展示的条数(分页单位) 偏移量= (当前页-1)*分页单位。
MySQL 分页查询原则
1)在 MySQL 数据库中使用 LIMIT 子句进行分页查询。
2)MySQL 分页中开始位置为 0。
3)分页子句在查询语句的最后侧。
语法格式
SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY LIMIT 开始位置,查询数量。
示例 :
需求:查询雇员表中所有数据按 id 排序,实现分页查询,每次返回两条结果。
select * from employees order by employees_id limit 0,2
MySQL 中的执行计划
在 MySQL 中可以通过 explain 关键字模拟优化器执行 SQL 语句,从而知道 MySQL 是如何处理 SQL 语句的。
MySQL 整个查询执行过程
1)客户端向 MySQL 服务器发送一条查询请求 。
2)服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段 。
3)服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划 。
4)MySQL 根据执行计划,调用存储引擎的 API 来执行查询 。
5)将结果返回给客户端,同时缓存查询结果。
启动执行计划
EXPLAIN SELECT 投影列 FROM 表名 WHERE 条件 ;
MySQL 数据库引擎
ISAM(Indexed Sequential Access Method)
ISAM 是一个定义明确且历经时间考验的数据表格管理方法,它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此,ISAM 执行读取操作的速度很快,而且不占用大量的内存和存储资源。ISAM 的两个主要不足之处在于,它不支持事务处理,也不能够容错。如果你的硬盘崩溃了,那么数据文件就无法恢复了。
MyISAM
MYISAM 强调了快速读取操作,这可能就是为什么 MySQL 受到了 WEB 开发如此青睐的主要原因:在 WEB 开发中你所进行的大量数据操作都是读取操作。
如果使用该数据库引擎,会生成三个文件:
.frm:表结构信息
.MYD:数据文件
.MYI:表的索引信息
InnoDB
1)InnoDB 数据库引擎都是造就 MySQL 灵活性的技术的直接产品,这项技术就是 MYSQL++ API。
2)在使用 MYSQL 的时候,你所面对的每一个挑战几乎都源于 ISAM 和 MyISAM 数据库引擎不支持事务处理(transaction process)也不支持外键。
3)MySQL 官方对 InnoDB 是这样解释的:InnoDB 给 MySQL 提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID 兼容)存储引擎。
innodb 与 myisam 区别
1. InnoDB 支持事务,MyISAM 不支持,对于 InnoDB 每一条 SQL 语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条 SQL 语言放在 begin 和 commit 之间,组成一个事务;
2. InnoDB 支持外键,而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM会失败;
3. InnoDB 是聚集索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。而 MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB 不保存表的具体行数,执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;
5. Innodb 不支持全文索引,而 MyISAM 支持全文索引,查询效率上 MyISAM 要高;(在 MySQL5.7 版本中已经支持全文索引)
如何选择:
1. 是否要支持事务,如果要请选择 innodb,如果不需要可以考虑 MyISAM
2. 如果表中绝大多数都只是读查询,可以考虑 MyISAM,如果既有读写也挺频繁,请使用 InnoDB。
3. 系统奔溃后,MyISAM 恢复起来更困难,能否接受;
4. MySQL5.5 版本开始 Innodb 已经成为 Mysql 的默认引擎(之前是 MyISAM),说明其优势是有目共睹的,如果你不知道用什么,那就用 InnoDB,至少不会差。
数据库设计范式
数据库设计范式:就是数据库的设计规范。
数据库设计范式作用:数据库的规范设计可以提高开发效率和运行效率。
数据库设计范式:
第一范式:对表中的列进行规范的约束。
最基本的范式,数据库表每一列都是不可分割的基本数据项,需要确保每列保持原子性,第一范式的合理遵循需要根据系统的实际需求来定。
第三范式:对 多对多表关系进行规范的约束。
第二范式需要确保数据库表中的每一列都和主键相关,而不能只和主键的某一部分相关。
第三范式:对 一对多表关系进行规范的约束。
确保数据表中的每一列数据都和主键直接相关而不能间接相关。