MySQL知识学习

MySQL知识学习的思维导图，为什么要使用数据库：可以通过sql语句方便的对数据进行查找、增加、修改、删除等。

MySQL学习

• 数据库概述
  • 数据库datebase; 数据库管理系统dbms;
  • 为什么要使用数据库：可以通过sql语句方便的对数据进行查找、增加、修改、删除等
  • 数据库分类：层次数据库、网状数据库、关系型数据库、非关系型数据库 关系型数据库：Oracle、DB2（IBM）、SQL Server（微软）、PostgreSQL、MySQL（由瑞典开发，后被Oracle收购） 非关系型数据库：Redis、MongoDB、HBase、Graph
  • MySQL下载、安装、卸载
    • www.mysql.com下载
    • 安装： 1. mysql默认端口3306 命令窗口测试：mysql -uroot -p 回车（提示mysql不是内部命令） 要配置环境变量：电脑-属性-高级系统设置-高级-环境变量-path（上下都可以，上面是所有用户可用，下面是指定用户可用）-把路径添加进去-确定 进入后为mysql> 退出为exit
    • 卸载： 1、停止mysql服务，cmd，services.msc，找到mysql80，右键停止服务 2. c盘 删除相关文件夹 3.进入控制面板卸载mysql 4.进入注册表中相关信息。regedit 5.重启
  • MySQL图形化界面管理工具（如Navicat）
    • 下载地址：www.novicat.com.cn/download 配置连接 localhost 3306 root 密码 注释方法： -- # /* */
  • SQL语言分类
    • DDL数据定义语言：定义数据库的对象，如数据表、视图、索引 DML数据操纵语言：更新、增加、删除记录 TCL事务控制语言 DQL数据查询语言：查询库中数据 DCL数据控制语言：控制数据库中的用户权限
• DDL操作
  • 数据库
    • 创建数据库 create datebase [if not exists] 数据库名 指定字符集方式创建 create datebase [if not exists] 数据库名 character set 字符集 如UTF8\GBK
    • 查看有哪些数据库 show databases; 查看指定数据库 show create database 要查看的数据库名
    • 修改数据库（只能修改字符集和排序规则，不能修改数据库名） alter database 数据库名 character set 字符集 collate 排序规则
    • 删除数据库 drop database 要删除的数据库名
    • 查看当前正在使用的数据库 select database(); 切换数据库 use 要切换到的数据库名
  • 表
    • 数据类型介绍
      • 数值类型 1.整数类型 tinyint\smallint\ int \ 2.近似数类型 float\double 格式float(M,D)：M表示一直多少位，D表示小数点后多少位 默认建表是有符号的，如果要无符号，加上unsigned，如id int unsigned
        • 
      • 字符串类型
        • 
      • 日期和时间类型 data\datatime\time\year
    • 创建表（！注意，在创建表前一定要切换到数据库中！） create table 表名（ 字段1 数据类型， 字段2 数据类型， 。。。 字段n 数据类型 ）; 或写成create table 表名（字段1 数据类型，字段2 数据类型， 。。。字段n 数据类型）;
    • 查看所有表： show tables;
    • 查看表结构（罗列所有字段，并展示字段信息，如类型，主键，默认值等） DESC 表名 或 describe 表名
    • 查看创建表的语句： show create table 表名；
    • 复制表结构： create table 新表名 like 旧表名；
    • 修改表： 增加列：alter table 表名 add 字段 类型 修改列类型：alter table 表名 modify 字段 新的类型 修改列名：alter table 表名 change 旧列名 新列名 类型 删除列：alter table 表名 drop 列名 修改表名： rename table 表名 to 新表名 修改字符集：alter table 表名 character set 新字符集
    • 删除表： drop table 表名；
    • 在表中增加行数据（前提是有表）： insert into 表名 (字段1，字段2，。。。字段n) values (值1，值2，。。。值n) 注意：除了整数不用引号，其它要加引号（单/双引号都可以），如“张三”，“男” 也可以不写字段，直接写vlaues，这种情况，要与字段排序完全一致！ 蠕虫复制 insert into 表名2 select * from 表名1 （前提：两表结构必须一致）
    • 更新表记录： update 表名 set 字段1=值1，字段2=值2（这种不带条件的更新，会将所有值全更新，一般不会这样操作） 带条件更新：update 表名 set 字段1=值1，字段2=值2 where 字段X=XX； 删除行数据： delete from 表名 where 条件
• DQL操作
  • 单表查询： 1.查询所有数据：select * from 表名； 2.查询指定列：select 字段1，字段2。。。 from 表名； 3.去除重复数据：使用关键字distinct，语法：select distinct 字段名 from 表名； 4.查询结果参与运算：select 列名1+固定值（或列名2） from 表名；只是显示出来，并不影响原表中数据！
  • 比较运算符：如大于>，小于<，小于等于<= ，大于等于>=，不等于<>或!=
  • 逻辑运算符：且and，或or，不满足not
  • in，between
  • 模糊查询：like select * from 表名 where 字段名 like "通配符字串“； 通配符：两个，%和_，%表示0个或多个字符，_表示一个字符 示例：select * from student where name like '%德%';
  • 排序：order by 语法：select * from where 条件 order by 字段 【asc/desc】,asc为升序，desc为降序。默认为升序asc 组合排序：先按第一个字段进行排序，如果第一个字段相同，才按第二个字段进行排序
• 单行函数
  • 数值函数 1.abs(x)：返回x的绝对值； 2.ceil(x）：返回大于等于x的最小整数（即向上取整）； 3.floor(x)：返回小于等于x的最小整数（即向下取整）； 4.rand()：返回0到1之间的随机数； 5.round(x)：返回离x最近的整数（四舍五入取整）；
  • 字符串函数： 1.concat(s1,s2,...,s3)：将多个字符串拼接成一个； 2.locate(s1,s)：查询s1在s中出现的位置； 3.lower(s)：将字符串的所有字母变成小写字母； 4.upper(s)：将字符串的所有字母变成大写字母； 5.replace(s,s1,s2)：将字符串s2替换s当中的字符串s1； 6.substring(s,start,length)：将s字符串的start位置截取length长度的字符串 7.trim(s)：去掉字符串s开始和结尾的空格；（中间的空格不会改变） 8.reverse(s)：反转字符串（倒着写，如dog变成god）
  • 时间日期函数 1.now()：返回系统当前日期和时间； 2.sysdate()：返回系统当前日期和时间； 和now()函数的区别 3.curdate()：返回系统年月日； 4.curtime()：返回系统时分秒； 5.month(date)：返回参数date的月份；date格式 2022-3-14 6.week(date)：返回参数date是一年的第几周； 7.day(date)：返回参数date的日值；date格式 2022-3-14 8.date_add(date,interval,expr type)：为给定的date时间，加上指定的值 expr为加的时间 type为时间类型如年，月，日，时，分等等 interval直接用，不要改
  • 流程控制函数 1.if(condition,expr1,expr2)：判断condition真假，如真返回expr1，如假返回expr2； 2.ifnull(expr1,expr2)：如果expr1不为null时返回expr1，expr1为null时返回expr2； 能够有效解决某个值为空的情况，为空可在ifnull中给它赋值expr2为0,从而不影响总数。 3.version()：查看当前数据库的版本； 4.user()：查看当前使用数据库的用户； 5.md5(s)：返回将s经过md5加密后的数据； 重要！可用于将密码敏感信息进行加密
  • 聚合函数 是纵向查询！对一列的数值进行计算，然后返回一个结果。（会忽略空值） 共有五个聚合函数：count、sum、max、min、avg 1.count：统计指定列记录数（null不会统计在内） 如select count(字段名/*) from student 2.sum：计算指定列的数值之和（如果不是数值类型，则结果为0） 3.max：计算指定列的最大值； 如select max(math) from student 4.min：计算指定列的最小值； 5.avg：计算指定列的平均值（如果不是数值类型，则结果为0）。
  • 分组查询 使用group by 对查询信息进行分组，相同数据作为一组 分组的目的是为了统计，所以一般会和聚合函数一起使用 如果按照哪个字段进行分组，那么最终需要把分组字段也写在字段的位置，这样可以区分聚合函数统计出来的结果是哪一组数据 语法：select 字段1，字段2，。。。字段n from 表名 group by 分组字段 【having 条件】 如按性别进行分类 having用法：要用到查询的结果时，对分组结果进行过滤 示例：select count(*),sex from student group by sex; 查询年龄大于25岁，按性别分组，统计每组人数 select count(*),sex from student where age>25 group by sex 【having count(*)>2】;
  • limit查询 限制每页显示查询的条数（即不会一次性全部展示，分页） 语法：select * from where X group by X limit X ; 注意limit语句放在最后！ limit格式：limit 参数一offset 参数二 length; 参数一offset偏移量，表示跳过几条开始显示，可省略，默认为0； length表示显示多少条。如limit 2,5 表示跨过2条，显示第3至7共5条记录；
• select 总结
  • 完整书写顺序： select 字段 from 表名 where 条件 group by 字段 having 条件 order by 字段 limit offset，length
• 约束和策略
  • 保证数据的准确性（类似excel中的数据有效性）
  • 主键约束(非空、唯一) 用来唯一标识一条记录，每张表都应该有一个主键，并且只有一个主键；通常使用id作为主键； 语法：在创建表时，如 id int primary key; 这时，id为主键，就只能唯一了，如果添加记录时重复，将会报错，无法添加记录进去。 删除主键：alter table 表名 drop primary key; 主键自增策略：在primary key后面再加 auto_increment；这样，可以不写主键； 如果要自定义从哪开始，使用 alter table 表名 auto_increment=1000,这样从1000开始添加记录。 Truncate 截断：语法：truncate table 表名 。与delete表的区别：delete只是删除表中数据，不会改变表结构。而truncate不仅删数据，还会改变表结构。
  • 非空约束：就是不允许为null值 语法：在字段后面加上 NOT NULL，如 name varchar(20) not null 修改表结构： alter table 表名 modify 字段 类型 NOT NULL；
  • 唯一约束（不能重复） 语法：字段 类型 unique 删除唯一约束： alter table 表名 drop index 唯一约束的约束名
  • 缺省约束：在不指定值时使用缺省值 语法：列名 数据类型 default 值； 删除缺省约束：alter table 表名 change column 列名 列名 数据类型 default null；
  • 外键约束：
• 表关系
  • 包括一对多（多对一），一对一，多对多三种。
  • 一对多建表原则：在从表创建一个字段，如班级ID（1，2，3），字段为外键指向主表的主键 多对多建表原则：需要创建一张中间表，至少包括两个字段，作为外键分别指向各自表的主键
• 索引
  • 分为普通索引、唯一索引、组合索引
  • 普通索引（一个表可以有多个普通索引） 在建表时 语法：index 索引名 列名 修改表方式 语法：alter table 表名 add index 索引名（列名） 直接创建方式 语法：create index 索引名 on 表名 （列名）
  • 唯一索引（一个表只有一个索引，允许空值） 创建表时 语法： unique index 索引名 （列名） 修改表方式 语法：alter table 表名 add unique index 索引名（列名） 直接创建方式 语法：create unique index 索引名 on 表名 （列名）
  • 组合索引 在建表时 语法：index 索引名 （列名1，列名2，。。。）如姓名、年龄 这时，张三，18；与张三，19，是可以的。但是如果两个张三，18，就不能保存 修改表方式 语法：alter table 表名 add unique index 索引名（列名1,列名2，。。） 直接创建方式 语法：create unique index 索引名 on 表名 （列名1，列名2，。。）
  • 总结 1.主键自动建立唯一索引。（不允许重复） 2.频繁作为查询条件的字段应该创建索引。
• DCL
  • 创建用户 create user '用户名'@'主机名' identified by‘密码’主机名可以用通配符%，代表任意主机
  • 授权用户 使用root账户grant 权限1 权限2 on 数据库名.表名 (*.*代表所有数据库所有表) to '用户名'@'主机名' 权限有 select insert update delete alter drop create等
  • 查看权限 show grants for '用户名'@'主机名'
  • 删除用户 drop user '用户名'@'主机名'
  • 修改密码 修改管理员：mysqladmin -uroot -p password 新密码（必须在未登入的情况下） 修改普通用户：set password for '用户名'@'主机名'='新密码' 须在登入下修改
• 数据库事务
  • 为数据库提供了一个从失败中恢复到正常状态的方法。使数据即使在异常状态下仍能保持一致。另外，多个应用程序并发访问数据库时，要提供操作隔离的方法，防止彼此操作相互干扰。（事务目的：1.失败恢复；2.保持一致；3.操作隔离）
  • ACID四特性 1.原子性 Actomicity。表示一个事务内的所有操作都是一个整体，要么都成功，要么都失败。（如银行转账，甲乙两个账户的变动） 2.一致性 Consistency。表示事务前后数据的完整性必须保持一致。 3.隔离性 Isolation。表示多个用户并发访问数据库时，要提供操作隔离的方法，防止彼此操作相互干扰。查看隔离级别：select @@transaction_isolation 4.持久性 Durability。表示一个事务一旦被提交成功，对数据的改变是永久性的，之后即使发生故障，也不应该对其有任何影响。
  • 自动提交事务：直接写sql语句 手动提交事务：start transaction，xxx，commit(提交)，rollback（回滚） 手动提交的，会将操作先保存到事务日志中，如果要改变数据库，必须使用commit
• 数据库备份和还原
  • 备份 mysqldump -u 用户名 -p 密码 要备份的数据库 > 文件路径/文件名.sql
  • 还原 source 导入文件的路径（注意：要先use 数据库）
• 多表查询
  • 笛卡尔乘积：左边m*右表n；会产生很多无用数据 如何消除？ 隐匿内连接：select 字段名 from 左表，右表 where 条件 显示内连接：select 字段名 from 左表 inner join 右表 on 条件 左外连接：左表数据全部显示，右表显示和左表有关联的数据 语法：select 字段名 from 左表 left outer join 右表 on 条件 右外连接：用右表记录匹配左表记录，如匹配显示，否则null 语法：select 字段名 from 左表 right outer join 右表 on 条件
  • 子查询：查询里嵌套查询 单行子查询：如 select * from student where math=(select max(math) from student)