MySQL

目录

一、数据库概念

二、操作数据库

1、分类

三、MySQL数据管理

四、常用函数

五、事务

六、索引

七、规范数据库设计

2、三大范式

---

一、数据库概念

概念 : 长期存放在计算机内,有组织,可共享的大量数据的集合,是一个数据 "仓库"
作用 : 保存,并能安全管理数据(如:增删改查等),减少冗余...
数据库总览 :
关系型数据库 ( SQL )               MySQL , Oracle , SQL Server , SQLite , DB2 , ...
关系型数据库通过外键关联来建立表与表之间的关系
非关系型数据库 ( NOSQL )      Redis , MongoDB , ...
非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

二、操作数据库

1、分类

 2、数据值和列类型

数值：tinyint / smallint / mediumint / int / bigint / float / double / decimal / decimal 

字符串：char / varchar / tinytext / text

日期：date / time / datetime /timestamp / year

null值:

3、数据字段属性

unsigned / zerofill / auto_increment / null / not null /default

4、MyISAM 和InnoDB的区别
MySQL的数据表的类型 : MyISAM , InnoDB , HEAP , BOB , CSV等...

•  适用 MyISAM : 节约空间及相应速度
• 适用 InnoDB : 安全性 , 事务处理及多用户操作数据表

5、数据表的存储位置

MySQL数据表以文件方式存放在磁盘中，包括表文件 , 数据文件 , 以及数据库的选项文件
位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表

注意 : 
InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件
MyISAM类型数据表对应三个文件 :
* . frm -- 表结构定义文件
* . MYD -- 数据文件 ( data )
* . MYI -- 索引文件 ( index )

6、数据库的相关操作

 

 7、模式通配符

     _    任意单个字符
    %    任意多个字符，甚至包括零字符
    单引号需要进行转义 \'

三、MySQL数据管理

1、外键

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。
在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。
外键作用
保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。 使两张表形成关联，外键只能引用外表中的列的值或使用空值。
 

-- 创建外键方式二 : 创建子表完毕后,修改子表添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

eg:

student--->grade
gradeid作为外键，grade是主表，student是从表
删除表时，先删除从表，再删除主表

-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;

2、DML语言：

增删改：

INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3');

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

DELETE FROM 表名 [WHERE condition];

TRUNCATE [TABLE] table_name;

（1）DELETE和truncate区别？
相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
不同 :
使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
使用TRUNCATE TABLE不会对事务有影响 （事务后面会说）

（2）用DELETE清空不同引擎的数据库表数据.重启数据库服务后
-- InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
-- MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)

3、DQL语言

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
    [left | right | inner join table_name2]  -- 联合查询
    [WHERE ...]  -- 指定结果需满足的条件
    [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
    [HAVING]  -- 过滤分组的记录必须满足的次要条件
    [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
    --  指定查询的记录从哪条至哪条

distinct

SELECT DISTINCT studentno FROM result;
 -- 了解:DISTINCT 去除重复项 , (默认是ALL)

where:
逻辑操作符:

比较操作符

        like结合使用的通配符 : % (代表0到任意个字符)  _ (一个字符)
模糊查询：
        -- 查询姓名中含有特殊字符的需要使用转义符号 '\'
        -- 自定义转义符关键字:  ESCAPE ':'
连接查询：
     

 

 自连接：

SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

 排序和分页

SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5

四、常用函数

数据函数

SELECT ABS(-8);  /*绝对值*/
SELECT CEILING(9.4); /*向上取整*/
SELECT FLOOR(9.4);   /*向下取整*/
SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/
SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/

字符串函数

SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
SELECT CONCAT('我','爱','程序');  /*合并字符串,参数可以有多个*/
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');  /*替换字符串,从某个位置开始替
换某个长度*/
SELECT LOWER('KuangShen'); /*小写*/
SELECT UPPER('KuangShen'); /*大写*/
SELECT LEFT('hello,world',5);   /*从左边截取*/
SELECT RIGHT('hello,world',5);  /*从右边截取*/
SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/
SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
SELECT REVERSE('狂神说坚持就能成功'); /*反转

日期和时间函数

SELECT CURRENT_DATE();   /*获取当前日期*/
SELECT CURDATE();   /*获取当前日期*/
SELECT NOW();   /*获取当前日期和时间*/
SELECT LOCALTIME();   /*获取当前日期和时间*/
SELECT SYSDATE();   /*获取当前日期和时间*/
-- 获取年月日,时分秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

系统信息函数

SELECT VERSION();  /*版本*/
SELECT USER();     /*用户*/

聚合函数

 -- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
-- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。 
-- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
-- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 

1）在表没有主键时，count(1)比count(*)快
2）有主键时，主键作为计算条件，count(主键)效率最高；
3）若表格只有一个字段，则count(*)效率较高。

要是放在分组后面的筛选
要使用HAVING..

五、事务

1、什么是事务？
事务就是将一组SQL语句放在同一批次内去执行
如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
MySQL事务处理只支持InnoDB和BDB数据表类型

2、事务的ACID原则
原子性(Atomic)
        要么全部完成，要么全部不完成。
一致性(Consist)
        事务提交前后的数据保持一致性。
隔离性(Isolated)
        多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据干扰，事务之间要相互隔离。
持久性(Durable)
        事务一旦被提交，持久化到数据库之中。

3、脏读、不可重复读、幻读

• 脏读：一个事务读取了另一个事务未提交的数据
• 不可重复读：多次读取结果不一样，（不一定错误，比如有其他事务修改了数据）
• 虚读（幻读）：在一个事务内读到了别人新插入的数据，导致前后不一致

-- 转账实现

SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务 
# rollback;
SET autocommit = 1; -- 恢复自动提交

六、索引

1、索引定义：
索引时帮助MySQL高效获取数据的数据结构

2、索引分类
（1）主键索引 (Primary Key)： 唯一的标识，不可重复

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

（2）唯一索引 (Unique)：   
作用： 避免重复的列出现，唯一索引可以重复，多个列可以标识为唯一索引
（3）常规索引 (Index)：        默认的
 作用：快速定位特定数据

• index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

（4）全文索引 (FullText)：    
作用：在特定的数据库引擎下才有，快速定位数据

• 只能用于MyISAM类型的数据表
• 只能用于CHAR , VARCHAR , TEXT数据列类型
• 适合大型数据集

3、创建索引

#方法一：创建表时
    CREATE TABLE 表名 (
        字段名1  数据类型 [完整性约束条件…],
        字段名2  数据类型 [完整性约束条件…],
        [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
        [索引名]  (字段名[(长度)]  [ASC |DESC]) 
   );
#方法二：CREATE在已存在的表上创建索引
    CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名
    ON 表名 (字段名[(长度)]  [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
    ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
    索引名 (字段名[(长度)]  [ASC |DESC]) ;                         
                             
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: SHOW INDEX FROM student;

4、索引准侧

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

5、索引的数据结构

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，innodb默认支持它）
-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

	事务	B-tree	Full-Text	Hash	行锁/表锁
InnoDB	√	√	√	×	行锁
MyISAM	×	√	√	×	表锁
Memory	×	√	×	√	表锁
NDB	√	×	×	√	行锁
Archive	×	×	×	×	表锁

七、规范数据库设计

1、为什么需要数据库设计  
当数据库比较复杂时我们需要设计数据库
（1）糟糕的数据库设计 : 

• 数据冗余,存储空间浪费
• 数据更新和插入的异常
• 程序性能差

（2）良好的数据库设计 : 

• 节省数据的存储空间
• 能够保证数据的完整性
• 方便进行数据库应用系统的开发

（3） 软件项目开发周期中数据库设计 :

• 需求分析阶段: 分析客户的业务和数据处理需求
• 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

（4）设计数据库步骤

• 收集信息
  与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
• 标识实体[Entity]
  标识数据库要管理的关键对象或实体,实体一般是名词
• 标识每个实体需要存储的详细信息[Attribute]
• 标识实体之间的关系[Relationship]

2、三大范式

（1）为什么需要数据规范化?

（2）三大范式：

• 第一范式 (1st NF)
  第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式
• 第二范式(2nd NF)
  第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。
  第二范式要求每个表只描述一件事情
• 第三范式(3rd NF)
  如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
  第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

（3）规范化和性能的关系

为满足某种商业目标 , 数据库性能比规范化数据库更重要
在数据规范化的同时 , 要综合考虑数据库的性能
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
通过在给定的表中插入计算列,以方便查询

八、权限管理