数据库笔记

2022-07-13 13:33:27

数据库

数据库操作

结构化查询语句分类

数据库操作

命令行操作数据库

创建数据库 : create database [if not exists] 数据库名;

删除数据库 : drop database [if exists] 数据库名;

查看数据库 : show databases;

使用数据库 : use 数据库名;

NULL值

理解为 "没有值" 或 "未知值"
不要用NULL进行算术运算 , 结果仍为NULL

数据字段属性

UnSigned

无符号的
声明该数据列不允许负数 .

ZEROFILL

0填充的
不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement

自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
通常用于设置主键 , 且为整数类型
可定义起始值和步长
- 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
- SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL

默认为NULL , 即没有插入该列的数值
如果设置为NOT NULL , 则该列必须有值

DEFAULT

默认的
用于设置默认值
例如,性别字段,默认为"男" , 否则为 "女" ; 若无指定该列的值 , 则默认值为"男"的值

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join 左外连接	会从左表中返回所有的值，即使右表没有
right join	会从右表中返回所有的值，即使左表没有

select查询

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}];
    --  指定查询的记录从哪条至哪条

作用：

可给数据列取一个新别名
可给表取一个新别名
可把经计算或总结的结果用另一个新名称来代替

SELECT *FROM student
SELECT *FROM result
SELECT  `studentNo`,`studentName` FROM student 

SELECT  `studentNo` AS 学号,`studentName` AS 名字 FROM student 
--函数拼接字符串 conact（a,b）
SELECT CONCAT('姓名:',studentname)AS 新名字 FROM student
--distinct 去重
SELECT *FROM result
SELECT `studentNo` FROM result
 SELECT DISTINCT `studentNo` FROM result
SELECT VERSION()
SELECT 100*3-9 AS 计算结果
SELECT `studentNo`,`studentResult`+2 AS '提分后' FROM result

========================WHERE==================
SELECT `studentNO`,`studentResult` FROM result

--查询成绩在95-100分之间的
SELECT `studentNO`,`studentResult` FROM result
WHERE `studentResult`>=95 AN D `studentResult`<=100
SELECT `studentNO`,`studentResult` FROM result
WHERE `studentResult`>=95 && `studentResult`<=100

--模糊查询（区间）
SELECT `studentNO`,`studentResult` FROM result
WHERE `studentResult`BETWEEN 90 AND 98

--查询除了1000号学生之外的同学的成绩
SELECT `studentNO`,`studentResult` FROM result
WHERE  `studentNo`!=1000 

--查询姓刘的同学 名字后面只有一个字的
SELECT `studentNo`,`studentName` FROM student
WHERE studentName LIKE '刘_'

--查询姓刘的同学 名字后面只有两个个字的
SELECT `studentNo`,`studentName` FROM student
WHERE studentName LIKE '刘__'

--查询名字中间有一字的同学  %一%
SELECT `studentNo`,`studentName` FROM student
WHERE studentName LIKE '%一%'

--===========IN （具体的一个或多个值）=============
--查询1001 1002 1003 号学生
SELECT `studentNo`,`studentName` FROM student
WHERE `studentNo` IN (1001,1002,1003);

--查询在安徽的学生
SELECT `studentNo`,`studentName` FROM student
WHERE address='安徽'

SELECT `studentNo`,`studentName` FROM student
WHERE address IN ('安徽','西安');

--=======NULL NOT NULL========

--查询地址为空的学生
SELECT `studentNo`,`studentName` FROM student
WHERE address='' OR address IS NULL;

--查询有出生日期的学生
SELECT `studentNo`,`studentName` FROM student
WHERE borndate IS NOT NULL

连表查询

--============连表查询==========
--查询参加考试的同学（学号，姓名，科目编号，分数）
SELECT *FROM `student`
SELECT *FROM `result`

/*思路
1.分析需求，分析查询的字段来自哪些表，（连表查询）
2.确定用那种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 sudentNo=成绩表studentNo
inner join  并集
*/

--join（连接的表） on（判断的条件） 连接查询
--where   等值查询


--inner JOIN  并集
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo=r.studentNo

--Right JOIN
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo=r.studentNo

--left JOIN
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo=r.studentNo

--====查询缺考的同学======
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo=r.studentNo
WHERE studentResult IS NULL

--思考题（查询了参加考试的同学信息：学号，学生姓名，科目名，分数）
/*思路
1.分析需求，分析查询的字段来自哪些表，（连表查询）
2.确定用那种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 sudentNo=成绩表studentNo
inner join  并集
*/
SELECT s.studentNo,studentName,subjectName,studentResult
FROM student AS s
RIGHT JOIN result AS r
ON r.studentNo=s.studentNo 
INNER JOIN `subject` AS sub
ON r.subjectNo=sub.subjectNo

--我要查询哪些数据 SELECT
--从哪些表查询 FROM 表 xxx JOIN 连接的表 ON 交叉条件
--假设存在多张表查询  慢慢来 先查询表两张表在慢慢增加

--from a LEFT JOIN b
--from a RIGHT JOIN b

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表

父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	美术设计

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

======自连接========

CREATE TABLE `category` (
`categoryid` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主体id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(40) NOT NULL COMMENT '主体名字',
PRIMARY KEY(`categoryid` )
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


--查询父子信息 把一张表看为两张一模一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

--查询学员所属的年级（学号，学生姓名，年级名称）

SELECT studentNo,studentName,gradeName
FROM student s
INNER JOIN grade g
WHERE s.gradeid=g.gradeid

--查询科目所属的年级（科目名称，科目年级）
SELECT subjectName,gradeName
FROM `subject` sub
INNER JOIN grade g
ON sub.gradeid=g.gradeid

----思考题（查询了参加数据库结构-1 考试的同学信息：学号，学生姓名，科目名，分数）
SELECT s.studentNo,studentName,subjectName,studentResult
FROM student s
INNER JOIN `result` r
ON s.studentNo=r.studentNo
INNER JOIN `subject` sub
ON r.subjectNo=sub.subjectNo
WHERE subjectName='Java程序设计-1'

分页和排序

-======================分页和排序 ORDER BY===================
--排序： 升序  ASC,降序 DESC
SELECT s.studentNo,studentName,subjectName,studentResult
FROM student s
INNER JOIN `result` r
ON s.studentNo=r.studentNo
INNER JOIN `subject` sub
ON r.subjectNo=sub.subjectNo
WHERE subjectName='Java程序设计-1'
ORDER BY studentResult DESC

-- 100万
-- 为什么要分页
-- 缓解数据库的压力，给人体验更好， 瀑布流

-- 分页 每页只显示五条数据
-- 语法：limit 起始值  页面的大小
--limit 0,5 1~5
--limit 1,5 2~6
--limmit 6,5 第二页
SELECT s.studentNo,studentName,subjectName,studentResult
FROM student s
INNER JOIN `result` r
ON s.studentNo=r.studentNo
INNER JOIN `subject` sub
ON r.subjectNo=sub.subjectNo
WHERE subjectName='Java程序设计-1'
ORDER BY studentResult DESC
LIMIT 4,2

-- 第一页  LIMIT 0,5    (1-1)*5
-- 第二页  LIMIT 5,5     (2-1)*5
-- 第三页  LIMIT 10,5     (3-1)*5
-- 第N页  LIMIT 0,5        (n-1)*pagesize, pagesize
--[pagesize:页面大小]
--[(n-1)*pagesize:起始值]
--[n当前页]
--[数据总数/页面大小]

-- 思考
-- 查询 java第一学年 课程成绩排名前十的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.studentNo,studentName,subjectName,studentResult
FROM student s
INNER JOIN `subject` sub
ON s.gradeid=sub.gradeid
INNER JOIN result r
ON r.subjectNo=sub.subjectNo
WHERE subjectName='Java程序设计-1' AND studentResult>=80
ORDER BY studentResult DESC

子查询

-- ================where===================================

-- 1.查询 Java程序设计-1 的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：使用连接查询
SELECT studentNo,r.subjectNo,studentResult
FROM result r
INNER JOIN `subject` sub
ON r.subjectNo=sub.subjectNo
WHERE subjectName='Java程序设计-1'
ORDER BY studentResult DESC

-- 方式二：使用子查询（）

SELECT studentNo,subjectNo,studentResult
FROM result 
WHERE subjectNo=ANY(
SELECT subjectNo FROM `subject`
WHERE subjectName='Java程序设计-1'
)
ORDER BY studentResult DESC

-- 分数不小于80分的学生的学号和姓名
-- 方式一：使用连接查询
SELECT s.studentNo,studentName
FROM student s
INNER JOIN `result` r
ON s.studentNo=r.studentNo
WHERE studentResult>=80

-- 方式二：使用子查询（）

SELECT studentNo,studentName
FROM student 
WHERE studentNo=ANY(
SELECT studentNo FROM `result`
WHERE  studentResult>=8
)
-- 在这基础上增加一个科目，C语言-3
-- 查询高等数学-2的编号
SELECT s.studentNo,studentName
FROM student s
INNER JOIN `result` r
ON s.studentNo=r.studentNo
WHERE studentResult>=80 AND subjectNo=(
SELECT subjectNo FROM `subject`
WHERE subjectName='C语言-3'
)
-- 在改造（由里及外）
SELECT studentNo,studentName FROM student WHERE studentNo IN(
SELECT studentNo FROM result WHERE studentResult>=80 AND subjectNo=(
SELECT subjectNo FROM `subject` WHERE subjectName='C语言-3'
)
)
--

分组和过滤

-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）

SELECT subjectName,AVG(studentResult) AS 平均分,MAX(studentResult) AS 最大值,MIN(studentResult) AS 最小值
FROM result r
INNER JOIN `subject` sub
ON r.subjectNo=sub.subjectNo
GROUP BY r.subjectNo   -- 通过什么字段来分组
HAVING 平均分>80

MySQL函数

-- ===========================常用函数=======================

-- 数学运算
SELECT ABS(-8)   -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR (9.4)  -- 向下取整
SELECT RAND()        -- 返回一个0~1之间的随机数
SELECT SIGN(100)       -- 判断一个数的符号 0-0 负数返回-1，正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('好好学习，休息休息') -- 字符串长度
SELECT CONCAT('我','爱','学习')   -- 拼接字符串
SELECT INSERT('我爱helloworld',1,2,'超级热爱')  -- 查询，从某个位置开始替换某个长度
SELECT LOWER('LoginServlet')  -- 小写字母 
SELECT UPPER('LoginServlet')  -- 大写字母
SELECT INSTR('LoginServlet','S') -- 返回第一次出现的字符串的索引
SELECT REPLACE("我是嘻嘻试试哈哈","嘻嘻","哈哈") -- 替换出现的指定字符串
SELECT SUBSTR("我是嘻嘻试试哈哈",4,6)  -- 返回出现的指定字符串（源字符串，截取的位置，截取的长度）
SELECT REVERSE('清晨我上马')          -- 反转

-- 查询姓刘的同学，改为周
SELECT REPLACE (studentName,"刘","周") FROM student
WHERE studentName LIKE'刘%'

-- 日期和日期函数（记住）
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 SYSTEM_USER()
SELECT USER()
SELECT VERSION()

5.1聚合函数（常用）

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
.....	....

-- ===========================聚合函数======================
-- 都能统计表中的数据（想查询一个表中有多少个记录，就是用这个count()）
SELECT COUNT(studentName) FROM student;   -- count（字段），会忽略所有的null值
SELECT COUNT(*) FROM student              -- count（*），不会忽略null·值  本质 计算行数
SELECT COUNT(1) FROM result               -- count(1), 不会忽略所有的null值 本质 计算行数


SELECT SUM(studentResult) AS 总和 FROM result
SELECT AVG(studentResult) AS  平均分 FROM result
SELECT MAX(studentResult) AS 最大值 FROM result
SELECT MIN(studentResult) AS 最小值 FROM result

5.2 数据库级别的MD5加密

什么是MD5?

主要增强算法复杂度和不可逆性

MD5不可逆，具体的值的MD5是一样的

MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值

-- =================测试MD5 加密===========
CREATE TABLE `testmd5`(
`id` INT(10) NOT NULL,
`name` VARCHAR(40) NOT NULL,
`pwd` VARCHAR(60) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `testmd5` VALUES(1,'小明','123456'),(2,'小王','123456'),(3,'小刘','123456')

-- 明文加密
UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id=1  

UPDATE `testmd5` SET pwd=MD5(pwd)  --加密全部的密码

-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4,'小he',MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行MD5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='小he' AND pwd=MD5('123456')

6.事务

什么是事务

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

事务的ACID原则

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。 1000
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability） --事务提交
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响 (事务一旦提交则不可逆，被持久化到数据库中)

隔离导致的一些问题

脏读：

指一个事务读取了另外一个事务未提交的数据

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

页面统计查询值

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）

执行事务

-- ===================事务===================

-- mysql  是默认开启事务提交的
SET autocommit=0 /* 关闭*/
SET autocommit=1 /* 打开*/

-- 手动处理事务
SET autocommit=0  -- 关闭自动提交

-- 事务开启
START TRANSACTION  -- 标记一个事务的开始，从这个之后的sql 都在同一个事务内

INSERT xx
INSERT zz

-- 提交：持久化（成功！）
COMMIT
-- 回滚：回到原来的样子
ROLLBACK

-- 事务结束
SET autocomit=1 -- 开启自动提交

-- 了解
SAVEPOINT  保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE  SAVEPOINT  保存点名  -- 撤销保存点

7.索引

索引的作用

提高查询速度
确保数据的唯一性
可以加速表和表之间的连接 , 实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
全文检索字段进行搜索优化.

基础语法

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2. 创建完毕后，增加索引

-- 显示所有索引信息
SHOW INDEX FROM student

-- 增加一个全文索引（索引名） 列名
ALTER TABLE shcool.student ADD FULLTEXT `studentName`(`studentName`);

EXPLAIN SELECT * FROM student; --非全文索引
EXPLAIN SELECT * FROM student WHERE MATH(studentName) AGAINST('刘')

分类

主键索引 (Primary Key)
唯一索引 (Unique)
常规索引 (Index)
全文索引 (FullText)

主键索引

主键 : 某一个属性组能唯一标识一条记录

特点 :

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

唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

主键索引只能有一个
唯一索引可能有多个

CREATE TABLE `Grade`(



    `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,



    `GradeName` VARCHAR(32) NOT NULL UNIQUE



    -- 或 UNIQUE KEY `GradeID` (`GradeID`)



)

常规索引

作用 : 快速定位特定数据

注意 :

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

CREATE TABLE `result`(



    -- 省略一些代码



    INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加



)
-- 创建后添加



ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

全文索引

百度搜索：全文索引

作用 : 快速定位特定数据

注意 :

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

 /*
#方法一：创建表时
    　　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;
*/
 
/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
 
/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
 
/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
 
/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/

批量插入数据：100w

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
  DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  WHILE i < num DO
   INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
    VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
   SET i = i + 1;
  END WHILE;
  RETURN i;
END;
SELECT mock_data();

索引效率测试

无索引

SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';
 
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: app_user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 992759
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

创建索引

CREATE INDEX idx_app_user_name ON app_user(name);

测试普通索引

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: app_user
   partitions: NULL
         type: ref
possible_keys: idx_app_user_name
          key: idx_app_user_name
      key_len: 203
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
 
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
 
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
 
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

索引准则

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

索引的数据结构

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
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 等索引；

8.权限管理和备份

基本命令

/* 用户和权限管理 */ ------------------
用户信息表：mysql.user
 
-- 刷新权限
FLUSH PRIVILEGES
 
-- 增加用户  CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
    -- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
    -- 只能创建用户，不能赋予权限。
    -- 用户名，注意引号：如 'user_name'@'192.168.1.1'
    -- 密码也需引号，纯数字密码也要加引号
    - -要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
 
-- 重命名用户  RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
 
-- 设置密码
SET PASSWORD = PASSWORD('密码')    -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码')    -- 为指定用户设置密码
 
-- 删除用户  DROP USER kuangshen2
DROP USER 用户名
 
-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
    -- all privileges 表示所有权限
    -- *.* 表示所有库的所有表
    -- 库名.表名 表示某库下面的某表
 
-- 查看权限   SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
    -- 查看当前用户权限
    SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
 
-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    -- 撤销所有权限

8.1MySQL备份

数据库备份必要性

保证重要数据不丢失
数据转移

MySQL数据库备份方法

mysqldump备份工具
数据库管理工具,如SQLyog
直接拷贝数据库文件和相关配置文件

mysqldump客户端

作用 :

转储数据库
搜集数据库进行备份
将数据转移到另一个SQL服务器,不一定是MySQL服务器

-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
 
可以-w携带备份条件
 
-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
　　source  备份文件
2. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件

9.规范数据库设计

为什么需要数据库设计

当数据库比较复杂时我们需要设计数据库

糟糕的数据库设计 :

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

良好的数据库设计 :

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

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

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

设计数据库步骤

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

三大范式

问题 : 为什么需要数据规范化?

不合规范的表设计会导致的问题：

信息重复
更新异常
插入异常
- 无法正确表示信息
删除异常
- 丢失有效信息

三大范式

第一范式 (1st NF)

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式(2nd NF)

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情

第三范式(3rd NF)

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范化和性能的关系

为满足某种商业目标 , 数据库性能比规范化数据库更重要

在数据规范化的同时 , 要综合考虑数据库的性能

通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间

通过在给定的表中插入计算列,以方便查询

码农公寓

数据库

数据库操作

结构化查询语句分类

数据库操作

数据字段属性

select查询

连表查询

自连接

分页和排序

子查询

分组和过滤

MySQL函数

5.1聚合函数（常用）

5.2 数据库级别的MD5加密

6.事务

7.索引

8.权限管理和备份

8.1MySQL备份

9.规范数据库设计

为什么需要数据库设计

三大范式

相关文章