[大数据]Mysql 学习笔记

Mysql 学习笔记

做项目必备字段

基础操作

mysql -uroot -p
show databases;
flush privileges;
use database_name;
show tables;

查看表结构

describe student

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dst7ijku-1636986052052)(/home/chj/.config/Typora/typora-user-images/image-20211111140051712.png)]

创建一个数据库

create database westos;

打注释

-- 这是注释内容

/*
	多行注释
*/

数据库语言

操作数据库

• 数据库

  创建

  create database if not exists westos;
  

  删除

  drop database if exists westos;
  

  使用

  • 普通使用

    use school;
    

  • 数据库名含特殊字符

    use `school`;
    

• 数据表

  查询一个字段

  select `user` from student;
  

数据类型

• 数值

  • tinyint

    十分小的数据，一个字节

  • smallint

    小的数据，两个字节

  • int

    标准的整数，四个字节

  • mediumint

    中等大小的数据，三个字节

  • bigint

    较大的数据，八个字节

  • float

    浮点数，四个字节

  • double

    浮点数，八个字节

  • decimal

    字符串形式的浮点数，金融计算时一般使用，避免精度问题

• 字符串

  • char

    字符串固定大小的，0-255，单位是字符，无论中英文

  • varchr

    可变字符串，0-65535，常用的，类似 Java 的String，单位是字符，无论是中英文

  • tinytext

    微型文本，2^8 - 1

  • text

    文本串，2^16 - 1

• 时间日期

  • data

    YYYY-MM-DD，日期格式

  • time

    HH:mm:ss，时间格式

  • datatime

    YYYY-MM-DD HH:mm:ss，最常用的时间格式

  • timestamp

    时间戳，1970.1.1 到现在的毫秒数

  • year

    表示年份

• NULL

  • 没有值，未知
  • *** 注意，不要使用 NULL 进行运算，结果为 NULL***

数据库字段属性

• unsigned

  • 无符号整数
  • 不能写入负数，否则报错

• zerofill

  • 0 填充

  • 不足的位数，使用 0 来填充

    int(3)

    填入 5

    结果 005

• desc

  • 通常理解为自增，自动在上一条记录的基础上 + 1 （默认）
  • 通常用来设计唯一的主键 ~ index，必须是整数类型
  • 可以自定义设计主键自增的起始值和步长

• null

  • 如果不填写值。默认就是 null

• not null

  • 如果不给他赋值，就会报错

• default

  • 如果不给值的话，自动赋一个默认值

• 字段解惑

  • int(M)

    M 是指最大显示宽度，最大有限显示宽度为 255 ，显示宽度与存储大小或实际类型所包含的值无关

  • varchar(M) 或 char(M)

    这里的 M 指的是最长的字符数

数据表创建

• 规范写法

  表的名称和字段名字尽量使用反引号括起来

  字符串使用单引号引起来

  所有的语句后面加上英文逗号，最后一条语句不用

  primary key 主键，一般一个表只有一个唯一的主键

  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` datatime 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 school -- 查看 student 数据表的定义语句
  desc student -- 显示表的结构
  

数据表操作

• 所有的创建和删除操作尽量加上判，以免报错

  修改表名

  -- alter table 旧表名 rename as 新表名
  alter table teacher rename table1
  

  增加表的字段

  -- alter table 表名 add 字段名 列属性
  alter table teacher1 add age int(11)
  

  修改表的字段（重命名，修改约束）

  alter table teacher1 modify age varchar(11) -- 修改约束
  alter table teacher1 change age age1 int(1) -- 字段重命名
  

  删除表的字段

  alter table teacher1 drop age1
  

  删除表

  drop table if exists teacher1
  

MySQL 数据管理

• 外键（了解即可）

  • 创建表的时候增加约束，但是麻烦且比较复杂

    create table grade (
    	`gradeid` int(10) not null auto_increment comment `年级id`,
        `gradename` varchar(50) not null comment `年级名称`,
        primary key (`gradeid`)
    )engine=innodb default charset=utf8
    
    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` datatime default null comment '出生日期',
        `address` varchar(100) default null comment '家庭住址',
        `email` varchar(50) default null comment '邮箱',
        `gradeid` int(10) not null comment '学生的年级',
         key `FK_gradeid` (`gradeid`),
         constraint `FK_gradeid` foreign key (`gradeid`) references `grade`(`gradeid`),
         primary key(`id`)
    )engine=innodb default charset=utf8
    

    删除有外键关系的表的时候，必须先删除引用别人的表（从表），再删除被引用的表（主表）

  • 创建表成功后再添加外键约束

    create table grade (
    	`gradeid` int(10) not null auto_increment comment `年级id`,
        `gradename` varchar(50) not null comment `年级名称`,
        primary key (`gradeid`)
    )engine=innodb default charset=utf8
    
    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` datatime default null comment '出生日期',
        `address` varchar(100) default null comment '家庭住址',
        `email` varchar(50) default null comment '邮箱',
        `gradeid` int(10) not null comment '学生的年级',
         primary key(`id`)
    )engine=innodb default charset=utf8
    

    alter table `student` add constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`)
    

  • 以上操作都是物理外键，数据库级别的外键，不推荐使用（避免数据库过多造成困扰，这里了解即可）

  • 最佳实现

    • 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
    • 如果想使用多张表的数据，想使用外键（用程序来实现）

  阿里巴巴 Java 开发规范

  不得使用外键和级联，一切外键概念必须在应用层解决
  

  这是因为每次做 delete 或则 update 都必须考虑外键约束, 会导致开发的时候很痛苦, 测试数据极为不方便
  

• DML 语言（全部记住，实际上就是更删改查）- 数据库管理语言

  数据库意义： 数据存储, 数据管理
  

  • insert
  • update
  • delete

• 添加

  通常

  insert into `grade` (`gradename`) values ('大四')
  

  插入多个字段

  insert into `student` (`name`, `pwd`, `sex`) values ('张三', 'aaa', '男'), ('王五', 'bbb', '女')
  

• 修改

  带限制的修改，只会修改限制指定的记录的内容

  update `student` set `name`='狂神' where id=1;
  

  不指定限制的情况下，会修改所有的记录

  update `student` set name='长江七号';
  

  修改多个属性，用逗号隔开

  update `student` set `name`='狂神',`email`='123@qq.com' where id=1;
  

  区间 between 属性（闭区间）

  update `student` set `name`='狂神',`email`='123@qq.com' where id between 2 and 5;
  

  精华

  update `student` set `name`='长江七号' where `name`='狂神44' and sex='女';
  

  update `student` set `birthday`=CURRENT_TIME where `name`='狂神44' and sex='女'; -- CURRENT_TIME 指当前时间
  

  where 子句精讲

  操作符	含义	范围	结果
  =	等于	5=6	false
  <> 或 !=	不等于	5 <> 6	true
  >
  <
  <=
  >=
  between … and …	在某个范围内	[2, 5]
  and	&&	5 > 1 && 1 > 2	false
  or	||	5 > 1 || 1 > 2	true

• 删除

  避免这样写，这会把所有数据删除掉

  delete from `student`;
  

  删除指定数据

  delete from `student` where id = 1;
  

  对表进行删除

  delete from `test`; -- 不会影响自增
  truncate table `test` -- 自增会归零
  

• DQL 查询数据（最重点）

  Data Query Language：数据查询语言

  为什么重要

  • 所有的查询操作都用它，select
  • 简单的查询，复杂的查询它都能做
  • 数据库中最核心的语言，最重要的语句
  • 使用频率最高的语句

  查询一张表中所有的数据

  select * from `student`;
  

  查询指定字段

  select `StudentNo` as '学号', `StudentName` as '学生姓名' from `student`;
  

  给查询出来的数据加点东西

  select concat('姓名： ', StudentName) as '新名字' from `student`;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kqWNeNHB-1636986052054)(/home/chj/.config/Typora/typora-user-images/image-20211112124122491.png)]

  去重，它会干掉重复数据，只显示一条重复数据

  select distinct `StudentNo` from result;
  

  查看数据库版本

  select version()
  

  有意思的语句，给计算结果取了个列名

  select 100*3-1 as '计算结果';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NX1vaTEt-1636986052055)(/home/chj/.config/Typora/typora-user-images/image-20211112124650163.png)]

  查询自增的步长

  select @@auto_increment_increment;
  

  给查询到的成绩加一分，但它并不会改变原来数据库中的数据

  select `StudentNo`, `StudentResult` + 1 as '提分后' from result;
  

  where 条件子句，尽量使用英文字母

  运算符	语法	描述
  and &&	a and b a && b	与
  or ||	a or b a || b	或
  not !	not a !a	非

  查询成绩在 95~100 分之间

  select `Student`, `StudentResult` from result where StudentResult >= 95 and StudentResult <= 100;
  

  相同效果，使用模糊查询

  select `Student`, `StudentResult` from result where StudentResult between 95 and 100;
  

  select `Student`, `StudentResult` from result where not `StudentNo` = 1000;
  

  模糊查询

  运算符	语法	描述
  is null	a is null	操作符为null，则真
  is not null	a is not null	与上面相反
  between	a between b and c	在b和c中为真
  link	a link b	sql 匹配，如果匹配的话则为真
  in	a in (a1,a2,a3…)	在其中某个时即为真

  % 表0到任意个字符，_表示一个字符

  -- 查询姓刘的同学
  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 语句里是一个具体的值，不能是link语句

  -- 查询 1001，1002，1003 号学员
  select `StudentNo`, `StudentName` from `student` where StudentName in (1001, 1002, 1003);
  

  查询地址为空的学生

  select `StudentNo`, `StudentName` from `student` where address = '' or address is null;
  

  查询有出生日期的同学

  select `StudentNo`, `StudentName` from `student` where `BornDate` is not null;
  

连表查询

• 取交集

  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 where s.studentNo = r.studentNo;
  

• Left Join

  select s.studentNo, studentName, SubjectNo, StudentResult from student as s left join result as r where s.studentNo = r.studentNo;
  

分页与排序

• 按照某字段升序/降序 desc/asc

  select s.studentNo, studentName, SubjectNo, StudentResult from student as s inner join result as r where s.studentNo = r.studentNo order by StudentResult desc;
  select s.studentNo, studentName, SubjectNo, StudentResult from student as s inner join result as r where s.studentNo = r.studentNo order by StudentResult asc;
  

• 分页

  • limit 语法：limit 起始值，页面大小

    select s.studentNo, studentName, SubjectNo, StudentResult from student as s inner join result as r where s.studentNo = r.studentNo order by StudentResult asc limit 0, 5;
    

常用函数

• 数学运算

  -- 绝对值
  select abs(-8)
  

  -- 向上取整
  select ceiling(9.4)
  

  -- 向下取整
  select floor(9.4)
  

  -- 0 - 1 之间的随机数
  select rand()
  

  -- 判断一个数的符号，0返回0，负数返回-1，正数返回1
  select sign(10)
  

• 字符串操作

字符串长度

-- 5
select char_length('即使哈哈哈');

拼接字符串

-- 拼接字符串
select concat('我', '爱', '你们');

查询出结果后，对结果中的字符串从某个位置开始替换某个长度

-- 超级热爱hello world
select insert('我爱hello world', 1, 2, '超级热爱')

小写字母

select lower('HELLO WORLD');

大写字母

select upper('hello world');

返回第一次出现的子串的位置，注意，索引从1开始

select instr('helloworld', 'h');

替换出现的字符串

select replace('狂神说坚持就能成功', '坚持', '努力');

返回指定的子字符串（源字符串，截取的位置，截取的长度）

select substr('狂神说坚持就能成功', 4, 6);

反转字符串

select reverse('清晨我上马');

高级语句：查询姓周的同学，并把性别改成邹

select replace(`StudentName`, '周', '邹') from `student` where `StudentName` like '周%';

• 时间和日期

  获取当前日期

  -- 2020-01-13
  select current_date();
  

  或者

  select curdate()
  

  获取当前时间

  -- 2020-01-13 15:09:41
  select now();
  

  或获取本地时间，输出都一样

  select localtime();
  

  或获取系统时间

  select sysdate();
  

  单独查看年月日时分秒

  select year(now());
  

  select month(now());
  

  select day(now());
  

  select hour(now());
  

  select minute(now());
  

  select second(now());
  

• 系统函数

  获取系统用户和登陆主机

  -- root@localhost
  select system_user();
  

  -- root
  select user();
  

  -- 5.7.19
  select version();
  

• 聚合函数（常用）

  其实就是用来求最大值最小值的这些东西

  函数名称	描述
  Count()	计数
  Sum()	求和
  Avg()	平均值
  Max()	最大值
  Min()	最小值

  查看表中有多少条记录，但会忽略所有 null

  select count(`StudentName`) from `student`;
  

  或，但不会忽略 null 值

  select count(*);
  

  小使用

  select sum(`StudentResult`) as '总和' from result;
  select avg(`StudentResult`) as '平均分' from result;
  select max(`StudentResult`) as '最高分' from result;
  select min(`StudentResult`) as '最低分' from result;
  

• md5 加密

  对指定用户密码进行加密

  update testmd5 set pwd=md5(pwd) where id = 1;
  

  对所有用户密码进行加密

  select testmd5 set pwd=md5(pwd);
  

  在插入数据的时候进行加密

  insert into testmd5 values(4, 'xiaoming', md5('123456'));
  

事务

简单理解：

? 要么都成功，要么都失败

案例理解：

? A 余额 = 1000，B 余额 = 200

? A 对 B 转账 200 元，但执行到一半服务器崩了，A 的钱少了，但是 B 并没有收到钱

? 事务就是为了解决这类问题的，转账的两条语句同时成功才为真

事务实现：

? 将一组 SQL 放在一个批次中执行

注意，innodb 上才支持吧。。。

事务原则：ACID 原则 原子性，一致性，隔离性，持久性 （脏读，幻读…）

• 术语

  • 原子性

    针对同一个事务

    原子性表示，转账时两个步骤一起成功或者一起失败，不能只发生其中一个动作
    

  • 一致性

    针对一个事务操作前后的状态一样

    一致性表示，无论怎么转账，金钱总额都不变
    

  • 持久性

    表示事务结束后的数据不随着外界原因导致数据丢失，如荡机情况

    如果事务已经提交，持久化道数据库
    如果没有提交，则恢复到原状
    

  • 隔离性

    针对多个用户同时操作，主要是排斥其他事务对本次事务的影响

    事务一旦提交则不可逆，持久化到数据库中

    事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作所干预，事务间要相互隔离

    隔离所导致的一些问题

    • 脏读

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

    • 不可重复读

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

    • 虚读（幻读）

      指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致