Mysql 学习笔记

数据库

MySql

创建表、修改字段

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库

CREATE TABLE IF NOT EXISTS `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `address` varchar(100) DEFAULT NULL COMMENT '地址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 查看数据库的定义
SHOW CREATE DATABASE school;
-- 查看数据表的定义
SHOW CREATE TABLE student;
-- 显示表结构
DESC student;  -- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';

-- 修改表的名字
ALTER TABLE `student1`  RENAME AS `student`;
-- 增加表的字段
ALTER TABLE `student` ADD age1 INT(11);
-- modify修改约束
ALTER TABLE `student` MODIFY `name` VARCHAR(20);
-- change重命名：[旧名] [新名] 
ALTER TABLE `student` CHANGE `age1` `age` INT(1);
-- 删除表的字段
ALTER TABLE `student` DROP age;
-- 删除表
DROP TABLE IF EXISTS `student`;

InnoDB和MyISAM

	MyISAM	InnoDB
事务	不支持	支持
行锁	不支持	支持
外键	不支持	支持
索引	支持	不支持
大小	较小	较大，约为MyISAM两倍

经验 ( 适用场合 ) :

• 适用 MyISAM : 节约空间及相应速度

• 适用 InnoDB : 安全性 , 事务处理及多用户操作数据表

• 注意 :

• • * . frm – 表结构定义文件

  • * . MYD – 数据文件 ( data )

  • * . MYI – 索引文件 ( index )

  • InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tOukPTBT-1669000626266)(/Users/tintin/Library/Application Support/typora-user-images/image-20221108174226931.png)]

DQL语言

DQL( Data Query Language 数据查询语言 )

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 
	...
where
	...
group by
	...
having
	...
order by
	...	

-- 使用as,为查询结果取一个新名字
-- CONCAT()函数拼接字符串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

-- 作用 : 去掉SELECT查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) , 只返回一条
SELECT DISTINCT `id`,`name` FROM `user`

模糊查询

-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘%';

-- 查询姓刘的同学,后面只有一个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘_';


null和''不同
-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

查询

 SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最分,
 	MIN(StudentResult) 	AS 最低分
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVING 平均分>80;

union

select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

select ename, job from emp where job = 'MANAGER'
union
select ename, job from emp where job = 'SALESMAN';

-- union的效率更高。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。
-- 但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接

-- union在使用的时候注意事项：
-- 1.进行结果集合并的时候，要求两个结果集的列数相同。
-- 2.结果集合并时列和列的数据类型要相同。

事务

事务原则(ACID)

1. 原子性(Atomic)

• 针对同一事物，要么都成功，要么都失败

2.一致性(Consist)

• 事务前后数据一致

3.隔离性(Isolated)

• 两个失误之间操作互不影响
  • 脏读：读到一个事务未提交的数据
  • 不可重复读：同一读取操作，数据不一致
  • 虚度：读取到别人刚提交的数据，导致数据前后不一致

4.持久性 (Durable)

• 事务没有提交可以恢复到原状，事务提交数据将被持久化，不可以逆。

索引

• 主键索引（primary key） primary key(字段名)
  • 唯一标志
• 唯一索引（unique key）unique key 索引名 （字段名）
  • 避免重复列的重复出现，唯一索引可以重复，多个列都可以标注唯一索引
• 常规索引（key/index） key 索引名 （字段名）
  • 默认，index/key来设置
• 全文索引（FullText） FullText index 索引名 （字段名）
  • 在特定数据库才有，快速定位数据

REATE TABLE `result`(
   -- 省略一些代码
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)


-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

创建索引

CREATE INDEX idx_app_user_name ON app_user(name);

/*增加全文索引*/
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 版本、存储引擎和数据类型是否支持全文索引。
*/

例子

EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999' --0.343s

CREATE INDEX idx_app_user_name ON app_user(name);

EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999' --0.000s

SHOW KEYS FROM `app_user` --查询所有索引

DROP INDEX idx_app_user_name ON `app_user` --删除索引

索引失效

• 模糊匹配中以“%”开头；

• 使用or的时候，只有在or两边的字段条件都有索引，才会走索引；

• 使用复合索引的时候，没有使用左侧的列查找，索引失效；（inde复合(sal，job)，where 必须跟 sal 才行）；

• 在 where 当中索引列参加了运算，索引失效；

• 在 where 当中索引使用了函数；

数据库三大范式

数据库设计范式：数据库表的设计依据，教你怎么进行数据库表的设计。

• 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
• 第二范式：建立在第一范式的基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
• 第三范式：建立在第二范式的基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

三范式是面试官经常问的，一定要熟记于心！！！

设计数据表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

• 第一范式

  最核心，最重要的范式，所有表的设计都需要满足。

  1.有主键；2.每个字段原子性不可再分

  不满足，没有主键，联系方式可以再分

• 第二范式
  建立在第一范式的基础上。

  1.所有非主键字段必须完全依赖主键 2.不要产生部分依赖

  产生部分依赖，数据冗余了。

  为了让以上的表满足第二范式，需要三张表来表示多对多的关系！

  • 多对多的设计：三张表，关系表两个外键！

• 第三范式

  建立在第二范式的基础上。

  所有非主键字段直接依赖主键，不要产生传递依赖。

  以上表是一对多的关系，满足第二范式，因为主键不是复合主键，没有产生部分依赖。
  但不满足第三范式，班级名称依赖班级编号，班级编号依赖学生编号，产生传递依赖。造成数据冗余。

  • 一对多的设计：两张表，多的表加外键！

• 总结数据库表的设计：

​ 一对一，第二张表外键唯一（fk+unique）
​ 一对多，两张表，多的表加外键！
​ 多对多，三张表，关系表两个外键！

• 嘱咐：
  数据库设计三范式是理论上的，实践和理论有的时候有偏差。
  最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。
  因为在SQL中，表和表之间连接次数越多，效率越低。（笛卡尔积）
  有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，SQL语句的编写难度也会降低。